如何用for循环计算多列中各组值占总和的百分比

我需要根据scloc列对数据框分组，计算每组在mean_row_R80080、mean_row_M80080、mean_row_E40040、mean_row_Sr80080这几列的总和占对应列整体总和的比例，最后把结果汇总到新的数据框里。我写了一段for循环代码，但没法正常运行。

示例数据 #

PG.UniProtIds                                 scloc mean_row_R80080 mean_row_M80080 mean_row_E40040 mean_row_Sr80080
1         Q9UHI8                         Extracellular        15.56592              NA              NA         15.07543
2         P48643                             Cytoplasm        14.32417        18.77053        18.24379               NA
3         P00734                         Extracellular        25.51741        24.65632        25.91294         25.15333
4         P60201                         Cell membrane              NA        13.63354              NA               NA
5  P01036;P01037                                  <NA>              NA        11.88245              NA               NA
6         P38646                         Mitochondrion              NA        17.28343              NA               NA
7         Q9BVK6 Endoplasmic reticulum|Golgi apparatus              NA        15.23676              NA               NA
8         P13686                         Extracellular        13.39949        13.99092              NA               NA
9         P31939                             Cytoplasm        15.64904        16.90990        16.51679               NA
10        Q8IUX7                         Extracellular        17.85344        21.22067        20.26607         16.21366

我写的无法运行的代码 #

library(dplyr)
df_ <- NULL

for (i in c("R", "M", "E", "Sr")) {
  j <- ifelse(i == "E", "400", "800")
  k <- ifelse(i == "E", "40", "80")
  
  dfdata <- dfsc %>%
    select(contains("PG"), matches(paste0("^mean_row_", i, j, k, "$")), scloc)
  
    dfdata %>%  
    group_by(scloc) %>%
    summarise(sum = sum(c_across(where(is.numeric)), na.rm = TRUE)) %>% #tried many combinations from here
    mutate(frak = i)
  
  if (is.null(df_)) {
    df_ <- dfdata
  } else {
    df_ <- full_join(df_, dfdata)
  }
}

期望输出 #

scloc frak perc #should sum to 1 with each "frak"
1                                            Cytoplasm    R 0.15
2                                        Extracellular    R 0.05
3         Extracellular|Cell membrane|Lysosome/Vacuole    R 0.10
4                                        Cell membrane    R 0.08
5 Extracellular|Endoplasmic reticulum|Lysosome/Vacuole    R 0.12
6                                              Nucleus    R 0.07
7                                    Cytoplasm|Nucleus    R 0.10
8                                                   NA    R 0.09
9                              Cytoplasm|Cell membrane    R 0.24

实际数据 #

dfsc <- 
structure(list(PG.UniProtIds = c("Q14195", "Q6ZTR7", "P52907", 
"P06310", "Q9P2P6", "Q8TCZ2", "P55011", "P04180", "O75351", "Q9Y3Z3", 
"Q04323", "O00560;Q8IVU3;Q9H190;Q9UII4", "P19652", "O75173", 
"Q5JY77", "P35268", "O43491", "P01111;P01116", "Q8WYK1", "Q5JZY3"
), scloc = c("Cytoplasm", "Cytoplasm", "Cytoplasm", "Extracellular", 
"Cytoplasm", "Extracellular|Cell membrane|Lysosome/Vacuole", 
"Cell membrane", "Extracellular|Endoplasmic reticulum|Lysosome/Vacuole", 
"Cytoplasm", "Nucleus", "Cytoplasm|Nucleus", NA, "Extracellular", 
"Extracellular", "Cytoplasm", "Cytoplasm", "Cytoplasm|Cell membrane", 
NA, "Cell membrane", "Cell membrane"), mean_row_R80080 = c(19.6306684829868, 
NA, 17.2895161634528, 19.4250092205753, NA, 20.117751088096, 
NA, 20.5292180784938, NA, NA, NA, 16.2518076533184, 22.530625412224, 
16.3396081224665, 15.8306614261866, NA, 11.9174064165071, 11.865825570229, 
13.2777622148836, 13.4016030819706), mean_row_M80080 = c(21.9301990460461, 
NA, 18.0559671254489, 16.3279816747423, NA, 14.41753373341, 16.3655313271373, 
18.3355440802636, NA, 16.6044098066449, NA, NA, 16.112024925506, 
13.3846412620776, NA, 17.7952994131055, 18.6626293542089, NA, 
NA, NA), mean_row_E40040 = c(21.3779843457729, 12.6939238702458, 
18.6188367607403, 20.4974200360208, 10.7600476279889, 14.6783675991137, 
NA, 19.7946878082238, 14.9857897533375, 16.5373969851768, 12.8042679773528, 
NA, 18.8525408958313, 12.4521848366364, NA, 15.7339178984839, 
17.9755941567014, NA, NA, NA), mean_row_Sr80080 = c(19.1049545779682, 
NA, NA, NA, NA, 19.6827289816135, NA, 20.3132062790538, NA, NA, 
NA, NA, 23.085168299633, 16.4188217290661, NA, NA, 9.24995136945289, 
NA, 15.3831902811252, NA)), class = "data.frame", row.names = c(NA, 
-20L))

问题分析与修正代码 #

你的代码主要问题有两个：

1. 分组汇总后的结果没有赋值回dfdata，导致后续拼接的是原始筛选数据集，不是汇总结果
2. 用full_join拼接会造成数据结构混乱，应该用列表存储循环结果后再合并

下面是更简洁的dplyr+tidyr实现方案，无需for循环：

library(dplyr)
library(tidyr)

# 转长格式统一处理
df_long <- dfsc %>%
  pivot_longer(cols = starts_with("mean_row_"), 
               names_to = "frak", 
               values_to = "value",
               names_pattern = "mean_row_(.*)") %>%
  # 提取frak前缀（R/M/E/Sr）
  mutate(frak = sub("(R|M|E|Sr).*", "\\1", frak))

# 计算每组占比
result <- df_long %>%
  group_by(frak, scloc) %>%
  summarise(group_sum = sum(value, na.rm = TRUE), .groups = "drop") %>%
  group_by(frak) %>%
  mutate(perc = group_sum / sum(group_sum, na.rm = TRUE)) %>%
  select(scloc, frak, perc) %>%
  arrange(frak, scloc)

print(result)

代码说明 #

• pivot_longer把宽格式转成长格式，所有数值列统一为一列，方便按frak和scloc分组
• 正则表达式自动提取frak前缀，适配列名格式
• 先算每个scloc在对应frak下的总和，再除以该frak的整体总和得到占比
• 最后整理为目标列顺序

如果一定要用for循环修正，代码如下：

library(dplyr)
df_ <- list()

for (i in c("R", "M", "E", "Sr")) {
  j <- ifelse(i == "E", "400", "800")
  k <- ifelse(i == "E", "40", "80")
  
  # 筛选列并分组计算占比
  dfdata <- dfsc %>%
    select(scloc, matches(paste0("^mean_row_", i, j, k, "$"))) %>%
    group_by(scloc) %>%
    summarise(group_sum = sum(c_across(where(is.numeric)), na.rm = TRUE), .groups = "drop") %>%
    mutate(frak = i) %>%
    mutate(perc = group_sum / sum(group_sum, na.rm = TRUE)) %>%
    select(scloc, frak, perc)
  
  df_[[i]] <- dfdata
}

# 合并所有结果
df_final <- bind_rows(df_)

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