R语言——excel多个sheet批量读取导出、剔除离群值以及作箱型图

 

#批量读取Excel表
install.packages("tidyverse")
library(tidyverse)
#install.packages("xlsx")
install.packages("readxl")
#library(xlsx)
library(readxl)
#a <- read_excel("genexpress.xlsx")
list1 <- list()
for (i in 8:36){
  list1[i-7] <- list(read_excel("genexpress.xlsx", sheet = i, range = cell_cols("A:B")))
}

#批量去除空值
for (i in 1:29){
  list1[[i]] <- na.omit(list1[[i]])
}

#批量去除极端值
x = {}
for (i in 1:27){
  x[i] = colnames(list1[[i]])[2]
}

for (i in 1:27){
  colnames(list1[[i]])[2] = "geneexpression"
}
##Q1-1.5*IQR
list2 <- list()
for (i in 1:27){
  list2[[i]] <- 
    list1[[i]] %>%
    group_by(list1[[i]][1]) %>%
    filter((geneexpression> quantile(geneexpression)[2] - 1.5 * IQR(geneexpression)) &
             (geneexpression < quantile(geneexpression)[4] + 1.5 * IQR(geneexpression))) %>%
    ungroup()
}

## 作图，箱型图
library(gridExtra)
library(agricolae)
library(ggplot2)
p_list <- list()

for(i in 1:6) {
  p_list[[i]] <- ggplot(list2[[i]], aes(x = !!sym(y[i]), y = geneexpression , fill = !!sym(y[i]))) +
    geom_boxplot() +
    # guides(fill = guide_legend(title = "generation"))+
    #geom_col(show.legend = T, alpha = 0.5) +
    scale_fill_brewer(palette = "Set2") +
    #scale_fill_manual(values = c("black", "brown", "pink"，“black”), )+
    #coord_flip() + # 交换横纵坐标
    #abs(x ="Value", y ="Frequency") +
    #ggtitle(as.character(var))+
    scale_x_discrete(limits = c("L", "P")) +   # 选择L，P
    theme(axis.text.x = element_blank())       # 去除坐标轴
}
#p_list[[1]]
grid.arrange(grobs = p_list, ncol = 3,grob_wrap = FALSE)

### list2输出到excel
install.packages("openxlsx")
library(openxlsx)
listnames = list()
n = length(list2)
names(list2) = paste0(x[1:n])
write.xlsx(list2, "筛选.xlsx")