R入门：计算细菌与蛋白质占比并导出至Excel

我已经用以下R代码处理了蛋白质数据，拆分得到包含Bacteria、Protein及各样本Log2quantity列的数据框：

library(readxl)
Data <- read_excel("20240214.xlsx")

library(tidyverse)
Data_frame <- separate(Data, col = "FUN", into = c("Bacteria", "Protein"), remove = TRUE, sep = ";" )
View(Data_frame)

数据示例如下：

Bacteria       Protein          Log2quantity Sample 1   Log2quantity Sample 2  Log2quantity Sample 3 ...
clostridium    ABC transporter   15.2                     5.2                   2.1
clostridium    Kinase1            8.2                     1.2                   8.2
bacillus       ABC transporter    5.5                     8.8                   24.2
bacillus       Oxidoreductase     3.2                     10.2                  12.2
bacillus       Kinase1            2.1                     1.2                   42.2
firmicutes     Kinase1            9.9                     9.2                   22.2
...            ...                ...                     ...                   ...

我需要实现以下需求：

• 按每个样本统计细菌和蛋白质的占比；
• 筛选每个样本中丰度排名前20的细菌和蛋白质；
• 将结果导出至Excel文件，方便查看大量数据。

我是R入门学习者，只会一点ggplot2，猜能用dplyr但不知道怎么写，另外想知道R能不能导出大数据量到Excel。

1. 先把数据转成「长格式」 #

宽格式数据不方便分组统计，先用pivot_longer转成长格式：

# 转换为长格式，统一样本列和丰度列
long_data <- Data_frame %>%
  pivot_longer(
    cols = starts_with("Log2quantity"), # 选中所有样本的丰度列
    names_to = "Sample",                # 新列存样本名称
    values_to = "Log2_abundance"        # 新列存丰度值
  ) %>%
  mutate(Sample = str_remove(Sample, "Log2quantity ")) # 清理样本名称，去掉前缀

2. 统计细菌/蛋白质的丰度占比 #

细菌水平占比计算

按样本+细菌分组求和，再计算该细菌在样本中的占比：

bacteria_ratio <- long_data %>%
  group_by(Sample, Bacteria) %>%
  summarise(total_abundance = sum(Log2_abundance, na.rm = TRUE), .groups = "drop") %>% # 按细菌求和
  group_by(Sample) %>%
  mutate(ratio = total_abundance / sum(total_abundance, na.rm = TRUE) * 100) %>% # 计算百分比占比
  arrange(Sample, desc(total_abundance)) # 按样本内丰度降序排

蛋白质水平占比计算

逻辑和细菌一致，换分组维度即可：

protein_ratio <- long_data %>%
  group_by(Sample, Protein) %>%
  summarise(total_abundance = sum(Log2_abundance, na.rm = TRUE), .groups = "drop") %>%
  group_by(Sample) %>%
  mutate(ratio = total_abundance / sum(total_abundance, na.rm = TRUE) * 100) %>%
  arrange(Sample, desc(total_abundance))

3. 筛选每个样本前20的细菌/蛋白质 #

用slice_max按样本分组后取丰度最高的20个：

# 每个样本前20的细菌
top20_bacteria <- bacteria_ratio %>%
  group_by(Sample) %>%
  slice_max(n = 20, order_by = total_abundance) %>%
  ungroup()

# 每个样本前20的蛋白质
top20_protein <- protein_ratio %>%
  group_by(Sample) %>%
  slice_max(n = 20, order_by = total_abundance) %>%
  ungroup()

4. 导出结果到Excel #

推荐用openxlsx包，支持多工作表写入，处理大数据更高效：

# 首次使用先安装包
# install.packages("openxlsx")
library(openxlsx)

# 创建工作簿，添加多个工作表
wb <- createWorkbook()
addWorksheet(wb, "全量细菌占比")
addWorksheet(wb, "全量蛋白质占比")
addWorksheet(wb, "样本前20细菌")
addWorksheet(wb, "样本前20蛋白质")

# 写入数据到对应工作表
writeData(wb, "全量细菌占比", bacteria_ratio)
writeData(wb, "全量蛋白质占比", protein_ratio)
writeData(wb, "样本前20细菌", top20_bacteria)
writeData(wb, "样本前20蛋白质", top20_protein)

# 保存文件
saveWorkbook(wb, "蛋白质细菌丰度统计结果.xlsx", overwrite = TRUE)

关于大数据量导出Excel的说明 #

R完全支持导出大数据量到Excel，但要注意：

• 优先用.xlsx格式（单工作表最多支持1048576行），别用老旧的.xls（仅65536行上限）；
• openxlsx比基础包或writexl更适合处理大数据，内存占用低、导出速度快；
• 如果数据量远超单工作表上限，可以按样本拆分到不同工作表，或者直接导出为CSV格式（无行数限制，用write.csv即可）。

内容的提问来源于stack exchange，提问作者Dan