如何在R中合并名称相似的列并对其数值内容求和

我有一个包含多个种群（行）观测计数的DataFrame，需要根据列名规则合并列并求和，具体分为两个场景：

场景1：按前缀合并列求和 #

示例输入DataFrame：

df <- data.frame (pop = c("pop1", "pop2", "pop3", "pop4","pop5"),
                  L1a  = c(0,0,0,1,2),
                  L1b = c(4,2,5,0,0),
                  L1c = c(1,3,0,3,4),
                  L2a = c(0,3,1,2,0),
                  L2b = c(3,0,2,1,3)
)
df

需求：合并所有名称包含指定前缀（如L1、L2）的列，对每行计数求和，目标结果：

df_merged <- data.frame (pop = c("pop1", "pop2", "pop3", "pop4","pop5"),
                  L1  = c(5,5,5,4,6),
                  L2 = c(3,3,3,3,3)
)
df_merged

场景2：混合保留与合并规则 #

示例输入DataFrame：

df <- data.frame (pop = c("pop1", "pop2", "pop3", "pop4","pop5"),
                  L1a  = c(0,0,0,1,2),
                  L1a2  = c(0,0,0,1,2),
                  L1a2b  = c(0,0,0,1,2),
                  L1a2b3  = c(0,0,0,1,2),
                  L1b = c(4,2,5,0,0),
                  L1b1 = c(4,2,5,0,0),
                  L1b1b = c(4,2,5,0,0),
                  L1b1b10 = c(4,2,5,0,0),
                  L1c = c(1,3,0,3,4),
                  L2a = c(0,3,1,2,0),
                  L2a1d = c(0,0,0,1,2),
                  L2a1d2c = c(0,0,0,1,2),
                  L2b = c(3,0,2,1,3),
                  L3a3d  = c(0,0,0,1,2),
                  L3a3d3  = c(0,0,0,1,2),
                  L3f  = c(0,0,0,1,2),
                  L3f1a  = c(0,0,0,1,2),
                  L3f1a1  = c(0,0,0,1,2),
                  L3f1a1b  = c(0,0,0,1,2),
                  L3d3a  = c(0,0,0,1,2),
                  L3d3a3  = c(0,0,0,1,2)
)
df

需求：

1. 保留所有列名长度小于4的列
2. 对列名长度≥4的列，按前5个字符分组，合并同组列并求和，目标结果：

df_merged <- data.frame (pop = c("pop1", "pop2", "pop3", "pop4","pop5"),
              L1a  = c(0,0,0,1,2),
              L1a2  = c(0,0,0,1,2),
              L1a2b  = c(0,0,0,2,4),
              L1b = c(4,2,5,0,0),
              L1b1 = c(4,2,5,0,0),
              L1b1b = c(8,4,10,0,0),
              L1c = c(1,3,0,3,4),
              L2a = c(0,3,1,2,0),
              L2a1d = c(0,0,0,2,4),
              L2b = c(3,0,2,1,3),
              L3a3d  = c(0,0,0,2,4),
              L3f  = c(0,0,0,1,2),
              L3f1a  = c(0,0,0,3,6)
)
df_merged

使用tidyverse工具包（dplyr+tidyr）可高效实现上述需求，以下是对应代码：

场景1：按前缀合并列求和 #

核心逻辑是提取列名前缀，分组后求和再转回宽格式：

library(tidyverse)

df_merged1 <- df %>%
  # 转长格式便于分组
  pivot_longer(-pop, names_to = "col", values_to = "count") %>%
  # 提取L开头加数字的前缀（如L1、L2）
  mutate(prefix = str_extract(col, "^L\\d")) %>%
  # 按种群和前缀分组求和
  group_by(pop, prefix) %>%
  summarise(total = sum(count), .groups = "drop") %>%
  # 转回宽格式
  pivot_wider(names_from = prefix, values_from = total)

df_merged1

若需自定义前缀匹配规则，可替换mutate部分为：

mutate(prefix = case_when(
  str_detect(col, "^L1") ~ "L1",
  str_detect(col, "^L2") ~ "L2"
))

场景2：混合保留与合并规则 #

核心逻辑是先拆分保留列与待合并列，对合并列按前5字符分组求和，最后合并结果：

library(tidyverse)

# 1. 筛选保留列（列名长度<4，排除pop）
keep_cols <- names(df)[str_length(names(df)) < 4 & names(df) != "pop"]
# 2. 处理待合并列：按前5字符分组求和
merged_part <- df %>%
  select(pop, -all_of(keep_cols)) %>%
  pivot_longer(-pop, names_to = "col", values_to = "count") %>%
  # 提取前5字符作为分组键（长度不足5时取完整列名）
  mutate(group_key = str_sub(col, 1, 5)) %>%
  group_by(pop, group_key) %>%
  summarise(total = sum(count), .groups = "drop") %>%
  pivot_wider(names_from = group_key, values_from = total)
# 3. 合并保留列与处理后的合并列
df_merged2 <- df %>%
  select(pop, all_of(keep_cols)) %>%
  left_join(merged_part, by = "pop")

# 可选：调整列顺序匹配示例结果
df_merged2 <- df_merged2 %>%
  select(pop, L1a, L1a2, L1a2b, L1b, L1b1, L1b1b, L1c, L2a, L2a1d, L2b, L3a3d, L3f, L3f1a)

df_merged2

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