如何在R语言中按不同模式将宽表转换为长表？

问题背景 #

现有如下宽格式数据框：

df_mock <- structure(list(`Company name Latin alphabet` = c("A", "B", "C"
), `NACE Rev. 2, core code (4 digits)` = c("2014", "1041", "2910"
), `Number of employees
2022` = c(NA_real_, NA_real_, NA_real_
), `Number of employees
2021` = c(3433, 779, 5744), `Number of employees
2020` = c(3342, 
737, 5664), `Number of employees
2019` = c(3228, 693, 5528), 
    `Number of employees
2018` = c(3109, 665, 5284), `Number of employees
2017` = c(3021, 
    661, 5242), `Number of employees
2016` = c(3007, 519, 5100
    ), `Operating revenue (Turnover)
th EUR 2022` = c(NA_real_, 
    NA_real_, NA_real_), `Operating revenue (Turnover)
th EUR 2021` = c(7529599, 
    5414117, 5170430), `Operating revenue (Turnover)
th EUR 2020` = c(4374804, 
    4187030, 5074135), `Operating revenue (Turnover)
th EUR 2019` = c(5214974, 
    3717059, 5044307), `Operating revenue (Turnover)
th EUR 2018` = c(6827041, 
    3133256, 4473499), `Operating revenue (Turnover)
th EUR 2017` = c(6437798, 
    2920410, 4891161), `Operating revenue (Turnover)
th EUR 2016` = c(5294858, 
    2944949, 5078559), region = c("X", "X", "X"), unique_id = c("1", 
    "2", "3")), row.names = c("1:1", "1:2", "1:3"), class = "data.frame")

需要转换为长格式，保留Company name Latin alphabet、NACE Rev. 2, core code (4 digits)、region、unique_id列，将剩余列拆分为year、employment、revenue三列，目标格式如下：

df_desired <- structure(list(Company_name_Latin_alphabet = c("A", "A", "A", 
"A", "A", "A", "A", "B", "B", "B", "B", "B", "B", "B", "C", "C", 
"C", "C", "C", "C", "C"), NACE_Rev_2_core_code_4_digits = c("2014", 
"2014", "2014", "2014", "2014", "2014", "2014", "1041", "1041", 
"1041", "1041", "1041", "1041", "1041", "2910", "2910", "2910", 
"2910", "2910", "2910", "2910"), region = c("X", "X", "X", "X", 
"X", "X", "X", "X", "X", "X", "X", "X", "X", "X", "X", "X", "X", 
"X", "X", "X", "X"), unique_id = c("1", "1", "1", "1", "1", "1", 
"1", "2", "2", "2", "2", "2", "2", "2", "3", "3", "3", "3", "3", 
"3", "3"), year = c("2022", "2021", "2020", "2019", "2018", "2017", 
"2016", "2022", "2021", "2020", "2019", "2018", "2017", "2016", 
"2022", "2021", "2020", "2019", "2018", "2017", "2016"), employment = c("NA", 
"3433", "3342", "3228", "3109", "3021", "3007", "NA", "779", 
"737", "693", "665", "661", "519", "NA", "5744", "5664", "5528", 
"5284", "5242", "5100"), revenue = c("NA", "7529599", "4374804", 
"5214974", "6827041", "6437798", "5294858", "NA", "5414117", 
"4187030", "3717059", "3133256", "2920410", "2944949", "NA", 
"5170430", "5074135", "5044307", "4473499", "4891161", "5078559"
)), class = "data.frame", row.names = c(NA, -21L))

用户尝试用reshape函数但未得到预期结果，代码如下：

reshape(belgium_orbis, direction='long', 
        varying=c('Number of employees\n2022', 
        'Number of employees\n2021', 
        'Number of employees\n2020', 
        'Number of employees\n2019', 
        'Number of employees\n2018', 
        'Number of employees\n2017', 
        'Number of employees\n2016', 
        'Operating revenue (Turnover)\nth EUR 2022', 
        'Operating revenue (Turnover)\nth EUR 2021', 
        'Operating revenue (Turnover)\nth EUR 2020', 
        'Operating revenue (Turnover)\nth EUR 2019', 
        'Operating revenue (Turnover)\nth EUR 2018', 
        'Operating revenue (Turnover)\nth EUR 2017', 
        'Operating revenue (Turnover)\nth EUR 2016'), 
        timevar='Year',
        times=c('2022', '2021', '2020', '2019', '2018', '2017', '2016'),
        v.names=c('employment', 'revenue'),
        idvar='id')

错误原因分析 #

1. 数据框名称错误：代码中使用belgium_orbis，但实际数据框是df_mock；
2. idvar参数错误：idvar应指定所有需要保留的非时间序列列，而不是不存在的id列；
3. varying参数格式错误：当v.names有多个值时，varying需要按变量类型分组（员工数列一组，营收列一组），而非将所有列按顺序排列；
4. 列名匹配问题：手动列所有带换行符的列名容易出错，建议用正则匹配批量提取。

解决方案 #

方案1：修正base R的reshape函数调用 #

# 批量提取员工数和营收的列名
emp_cols <- grep("Number of employees", names(df_mock), value = TRUE)
rev_cols <- grep("Operating revenue", names(df_mock), value = TRUE)

# 执行reshape转换
df_long_reshape <- reshape(df_mock,
                          direction = "long",
                          varying = list(employment = emp_cols, revenue = rev_cols),
                          timevar = "year",
                          times = c("2022", "2021", "2020", "2019", "2018", "2017", "2016"),
                          v.names = c("employment", "revenue"),
                          idvar = c("Company name Latin alphabet", "NACE Rev. 2, core code (4 digits)", "region", "unique_id"),
                          new.row.names = 1:21)

# 可选：标准化列名以匹配目标格式
names(df_long_reshape) <- gsub(" |\\.|,|\\(", "_", names(df_long_reshape))
names(df_long_reshape) <- gsub("_+$", "", names(df_long_reshape))

方案2：使用tidyverse的pivot_longer（更简洁直观） #

推荐使用tidyverse包的pivot_longer，语法清晰，处理特殊列名更便捷：

library(tidyverse)

df_long_pivot <- df_mock %>%
  pivot_longer(
    cols = matches("Number of employees|Operating revenue"),
    names_to = c(".value", "year"),
    names_pattern = "(.*?)\\n.*?(\\d{4})",
    values_transform = as.character  # 转换为字符型以匹配目标中的NA格式
  ) %>%
  # 重命名列
  rename(
    employment = `Number of employees`,
    revenue = `Operating revenue (Turnover)\nth EUR`
  ) %>%
  # 调整列顺序匹配目标
  select(`Company name Latin alphabet`, `NACE Rev. 2, core code (4 digits)`, region, unique_id, year, employment, revenue) %>%
  # 可选：标准化列名
  rename_with(~gsub(" |\\.|,|\\(", "_", .x)) %>%
  rename_with(~gsub("_+$", "", .x))

两种方案都能生成符合要求的长格式数据，其中pivot_longer更易维护和扩展。

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