list.files()

library(readxl)

sheets <- excel_sheets("Fig S2 Source Data.xlsx")

for(s in sheets){

  dat <-

    read_excel("Fig S2 Source Data.xlsx", sheet = s) %>%

    mutate(Color = cut(P, breaks=c(0,0.05,1), labels=c("red3","gray")))

  colnames(dat)[1] <- "Var"

  library(ggplot2)

  dat$Var <- factor(dat$Var, levels = rev(dat$Var))

  dat$Color <- as.character(dat$Color)

  p <- ggplot(dat) +

    geom_col(aes(x=R2, y=Var, fill=Color), width = 0.6) +

    scale_fill_manual(values = unique(dat$Color)[order(unique(dat$Color))]) +

    theme_classic() +  scale_x_continuous(position = "top") +

    xlab(s) + ylab("") + 

    theme(legend.position = "none")

  assign(paste("P_", s,sep = ""),p, envir = .GlobalEnv)

}

library(ggpubr)

ggarrange(ggarrange(P_Taxonomy, P_Function, P_Metabolome, ncol = 3),

          ggarrange(P_Transcriptome,`P_Sputum Proteome`, `P_Serum Proteome`,ncol = 3),

          nrow = 2)