如何将带年份后缀的同名前缀多列重塑为长格式数据

如何从当前数据转换为目标数据？两者的区别在于目标数据新增了Year列，该列填充的是以“Operating”开头的列名末尾的四位年份数字。我希望保留指标列名称，将年份作为独立变量，并将所有Operating类列的值合并到单独一列中。

当前数据 #

当前为宽表结构，包含注册编号、行业代码、员工数，以及各年份的Operating Profit列。

目标数据 #

转换为长表结构，新增Year列存储年份，所有Operating Profit值合并到单独一列，基础列（注册编号、行业代码、员工数）对应每行年份重复显示。

数据代码 #

data <- data.frame(
  "Registered number" = c("03600726", "13243384", "09884706", "09884706", "03600726", "03600726", "13243384", "13243384"),
  "Primary UK SIC (2007) code" = c(46719, 64191, 35140, 35140, 46719, 46719, 64191, 64191),
  "Number of employees Last avail. yr" = c(249, 198, 91, 91, 249, 249, 198, 198),
  "Year" = c(2023, 2023, 2022, 2021, 2022, 2021, 2022, 2021),
  "Operating Profit th GBP" = c(2023, 2023, -5000, 11000, 153021, -14397, 5, NA)
)

尝试代码 #

data_long <- pivot_longer(data, 
                           cols = starts_with("202"), 
                           names_to = c(".value", "Year"), 
                           names_pattern = "(\\d{4})_(.*)", 
                           values_drop_na = TRUE)


data_long$"Operating Profit th GBP" <- gsub(",", "", as.character(data_long$"Operating Profit th GBP"))

通用场景需求：将包含多个带年份后缀的指标列（如Operating Profit、Net Interest各年份列）的宽表转换为长表，新增Year列存储年份，每个指标保留为单独一列，同时保留所有非指标类的基础列。

当前数据 #

宽表结构，每个指标对应多个年份列，缺失值以"n.a."标记：

data <- data.frame(
  "Registered number" = c("09884706", "03600726", "13243384"),
  "Primary UK SIC (2007) code" = c(35140, 46719, 64191),
  "Number of employees Last avail. yr" = c(91, 249, 198),
  "Operating Profit th GBP 2023" = c("n.a.", "n.a.", "313,417"),
  "Operating Profit th GBP 2022" = c("-5,000", "153,021", "5"),
  "Operating Profit th GBP 2021" = c("11,000", "-14,397", "n.a."),
  "Net Interest th GBP 2023" = c("n.a.", "n.a.", "-313,227"),
  "Net Interest th GBP 2022" = c("-4,000", "-776", "n.a."),
  "Net Interest th GBP 2021" = c("-4,000", "1,449", "n.a.")
)

目标数据 #

长表结构，Year列提取自原列名末尾的四位数字，每个指标合并为一列，基础列对应每行年份重复显示：

data <- data.frame(
  "Registered number" = c("09884706", "03600726", "13243384", "09884706", "03600726", "13243384", "09884706", "03600726", "13243384"),
  "Primary UK SIC (2007) code" = c(35140, 46719, 64191, 35140, 46719, 64191, 35140, 46719, 64191),
  "Number of employees Last avail. yr" = c(91, 249, 198, 91, 249, 198, 91, 249, 198),
  "Year" = c(2023, 2023, 2023, 2022, 2022, 2022, 2021, 2021, 2021),
  "Operating Profit th GBP" = c("n.a.", "n.a.", "313,417", "-5,000", "153,021", "5", "11,000", "-14,397", "n.a."),
  "Net Interest th GBP" = c("n.a.", "n.a.", "-313,227", "-4,000", "-776", "0", "-4,000", "1,449", "n.a.")
)

通用解决方案代码 #

使用tidyverse包的pivot_longer实现自动适配任意数量的指标列和基础列：

library(tidyverse)

# 定义通用转换函数
convert_wide_to_long <- function(data) {
  # 自动识别不需要转换的基础列（列名末尾无" 四位年份"格式）
  id_columns <- colnames(data)[!str_detect(colnames(data), "\\s\\d{4}$")]
  
  # 执行宽表转长表
  data_long <- data %>%
    pivot_longer(
      cols = -all_of(id_columns),
      names_to = c(".value", "Year"),
      names_pattern = "(.*) (\\d{4})$",
      values_transform = list(Year = as.integer)
    ) %>%
    # 可选：将"n.a."转换为标准NA值，方便后续处理
    mutate(across(where(is.character), ~ifelse(.x == "n.a.", NA_character_, .x)))
  
  return(data_long)
}

# 测试函数（使用通用案例的当前数据）
test_data <- data.frame(
  "Registered number" = c("09884706", "03600726", "13243384"),
  "Primary UK SIC (2007) code" = c(35140, 46719, 64191),
  "Number of employees Last avail. yr" = c(91, 249, 198),
  "Operating Profit th GBP 2023" = c("n.a.", "n.a.", "313,417"),
  "Operating Profit th GBP 2022" = c("-5,000", "153,021", "5"),
  "Operating Profit th GBP 2021" = c("11,000", "-14,397", "n.a."),
  "Net Interest th GBP 2023" = c("n.a.", "n.a.", "-313,227"),
  "Net Interest th GBP 2022" = c("-4,000", "-776", "n.a."),
  "Net Interest th GBP 2021" = c("-4,000", "1,449", "n.a.")
)

result <- convert_wide_to_long(test_data)
print(result)

代码说明 #

• id_columns：自动筛选出不需要转换的基础列，无需手动指定，适配任意数量的基础列。
• names_pattern = "(.*) (\\d{4})$"：正则表达式匹配列名，(.*)捕获指标名称（最后一个空格前的所有内容），(\\d{4})捕获末尾的四位年份。
• .value：指定将捕获的指标名称作为新的列名，年份存入Year列。
• 可选的mutate步骤：将自定义缺失值标记"n.a."转换为R标准NA值，便于后续数据分析。
• 该函数支持任意数量的指标列，只要列名符合“指标名称 四位年份”的格式即可。

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