如何在R中折叠除指定列外的多组重复列

问题场景 #

现有一个宽格式数据集，仅country和state两列无重复，其余列（如name、family、votes等）均以...N（N为数字）作为后缀重复出现数百次，需要将其转换为指定的长格式。

示例数据 #

dummydf <- data.frame(country = c("USA", "USA"),
                      state = c("Oregon", "California"),
                      name = c("anne", "paul"),
                      family = c("stevens", "williams"),
                      votes = c(10, 50.2),
                      city = c("london", "berlin"),
                      age = c(10, 50),
                      `name...2` = c("joseph", "vincent"),
                      `family...2` = c("ramos", "williams"),
                      `votes...2` = c(15, 62),
                      `city...2` = c("lisbon", "berlin"),
                      `age...2` = c(77, 43),
                      `name...3` = c("johanna", "paul"),
                      `family...3` = c("santos", "ramos"),
                      `votes...3` = c(.61, 54.2),
                      `city...3` = c("london", "berlin"),
                      `age...3` = c(56, 54),
                      `name...4` = c("sara", "edith"),
                      `family...4` = c("stevens", "sanchez"),
                      `votes...4` = c(2.9, 54.1),
                      `city...4` = c("lisbon", "paris"),
                      `age...4` = c(20, 25),
                      `name...5` = c("thomas", "paul"),
                      `family...5` = c("santos", "ramos"),
                      `votes...5` = c(1.2, 5.2),
                      `city...5` = c("lisbon", "toronto"),
                      `age...5` = c(45, 80))

原始数据展示 #

country      state name   family votes   city age name...2 family...2 votes...2 city...2 age...2 name...3 family...3 votes...3 city...3 age...3 name...4 family...4 votes...4
1     USA     Oregon anne  stevens  10.0 london  10   joseph      ramos        15   lisbon      77  johanna     santos      0.61   london      56     sara    stevens       2.9
2     USA California paul williams  50.2 berlin  50  vincent   williams        62   berlin      43     paul      ramos     54.20   berlin      54    edith    sanchez      54.1
  city...4 age...4 name...5 family...5 votes...5 city...5 age...5
1   lisbon      20   thomas     santos       1.2   lisbon      45
2    paris      25     paul      ramos       5.2  toronto      80

目标长格式 #

name   family votes    city age      state country
1     anne  stevens 10.00  london  10     Oregon     USA
2     paul williams 50.20  berlin  50 California     USA
3   joseph    ramos 15.00  lisbon  77     Oregon     USA
4  vincent williams 62.00  berlin  43 California     USA
5  johanna   santos  0.61  london  56     Oregon     USA
6     paul    ramos 54.20  berlin  54 California     USA
7     sara  stevens  2.90  lisbon  20     Oregon     USA
8    edith  sanchez 54.10   paris  25 California     USA
9   thomas   santos  1.20  lisbon  45     Oregon     USA
10    paul    ramos  5.20 toronto  80 California     USA

解决方案 #

方法1：使用tidyverse（推荐，直观易读） #

借助tidyr包的pivot_longer函数，通过正则表达式匹配列名前缀与后缀，自动完成宽转长：

library(tidyverse)

long_df <- dummydf %>%
  pivot_longer(
    cols = -c(country, state),  # 排除不需要转换的固定列
    names_to = c(".value", "group"),  # .value保留原列名前缀，group存储后缀数字
    names_pattern = "(.*)\\.{3}(\\d+)?",  # 正则匹配前缀、三个点、可选数字后缀
    values_drop_na = FALSE
  ) %>%
  select(-group)  # 移除临时的group列

代码说明：

• cols = -c(country, state)：指定不参与转换的列，其余列全部纳入宽转长流程
• names_to = c(".value", "group")：.value是特殊参数，告诉函数将列名的前缀作为新的列名；group用来暂存后缀的数字标识
• names_pattern：正则表达式(.*)\\.{3}(\\d+)?精准匹配列名结构：(.*)捕获前缀（如name），\\.{3}匹配三个点，(\\d+)?匹配可选的数字后缀（兼容第一组无后缀的列）
• 运行后即可得到与目标格式完全一致的长表

方法2：使用data.table（适合超大数据集，效率更高） #

如果数据集规模极大，可使用data.table包的melt函数，通过匹配列名前缀实现转换：

library(data.table)

setDT(dummydf)
long_dt <- melt(dummydf, 
                id.vars = c("country", "state"),  # 固定列
                measure.vars = patterns("^name", "^family", "^votes", "^city", "^age"),  # 按列名前缀分组
                value.name = c("name", "family", "votes", "city", "age")  # 指定分组后的列名
              ) %>%
  select(-variable)  # 移除临时标识列

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