如何在无规范列名的R数据框中使用pivot函数规整数据

我有一组非常规的数据框列表，每个数据框本质上包含约6个小数据框，以下是列表中某一数据框的示例：

structure(list(...1 = c("Alternative", "Original Value", "123456123456", 
"1234561234564", "1234561234564113", "Rebased", "123456123456", 
"1234561234564", "1234561234564113", "Percentage change", "Rebased", 
"123456123456", "1234561234564", "1234561234564113", "Traditional", 
"Original Value", "123456123456", "1234561234564", "1234561234564113", 
"Rebased", "123456123456", "1234561234564", "1234561234564113", 
"Percentage change", "Rebased", "123456123456", "1234561234564", 
"1234561234564113"), ...2 = c("", "42309", "146.27640572779359", 
"145.73830637395969", "102.3547042789086", "42309", "100", "100", 
"100", NA, "42309", "0", "0", "0", NA, "42309", "143.226999814094", 
"142.23723117012401", "100.31728657348999", "42309", "100", "100", 
"100", NA, "42309", "0", "0", "0"), ...3 = c(NA, "42339", "147.73691615252909", 
"147.6091928121399", "104.1688534718035", "42339", "100.99845933284234", 
"101.28373005335986", "101.77241408265074", NA, "42339", "9.9845933284234967E-3", 
"1.2837300533598661E-2", "1.7724140826507417E-2", NA, "42339", 
"143.230372056791", "142.243794165965", "99.957579183447805", 
"42339", "100.00235447415737", "100.00461411951498", "99.641430303461533", 
NA, "42339", "2.3544741573733319E-5", "4.6141195149784764E-5", 
"-3.5856969653846882E-3")), row.names = c(NA, -28L), class = c("tbl_df", 
"tbl", "data.frame"))

我需要对列表中的每个数据框执行数据规整，最终得到4列：

• code：值为类似123456....的编码
• type：值为Alternative、Traditional
• month：值为42309、42339这类（实际是日期，但R读取格式有误）
• metric：值为Original Value、Rebased、Percentage Change等

我尝试了以下代码但都报错：

pivot_longer(df, cols = df$...1, names_to = c("type", "metric", "code", "month"), values_to = "value")

报错信息：

Error in `pivot_longer()`:
! Can't select columns that don't exist.
✖ Columns `Alternative`, `Original Value`, `123456123456`, `1234561234564`, `1234561234564113`, etc. don't exist.

又尝试：

pivot_wider(df, names_from = `...1`)

报错信息：

Error in `pivot_wider()`:
! Can't select columns that don't exist.
✖ Column `value` doesn't exist.

我的目标是结合pivot_wider/pivot_longer和lapply批量处理列表中的每个数据框，期望输出类似（以Alternative为例）：

structure(list(code = c(123456123456, 123456123456, 123456123456, 
1234561234564, 1234561234564, 1234561234564, 1234561234564110, 
1234561234564110, 1234561234564110, 123456123456, 123456123456, 
123456123456, 1234561234564, 1234561234564, 1234561234564, 1234561234564110, 
1234561234564110, 1234561234564110), type = c("Alternative", 
"Alternative", "Alternative", "Alternative", "Alternative", "Alternative", 
"Alternative", "Alternative", "Alternative", "Alternative", "Alternative", 
"Alternative", "Alternative", "Alternative", "Alternative", "Alternative", 
"Alternative", "Alternative"), month = c("11/1/2015", "11/1/2015", 
"11/1/2015", "11/1/2015", "11/1/2015", "11/1/2015", "11/1/2015", 
"11/1/2015", "11/1/2015", "12/1/2015", "12/1/2015", "12/1/2015", 
"12/1/2015", "12/1/2015", "12/1/2015", "12/1/2015", "12/1/2015", 
"12/1/2015"), metric = c("Original Value", "Rebased", "Percentage Change", 
"Original Value", "Rebased", "Percentage Change", "Original Value", 
"Rebased", "Percentage Change", "Original Value", "Rebased", 
"Percentage Change", "Original Value", "Rebased", "Percentage Change", 
"Original Value", "Rebased", "Percentage Change"), value = c(146.28, 
100, 0, 145.74, 100, 0, 102.35, 100, 0, 147.74, 101, 0.01, 147.61, 
101.28, 0.01, 104.17, 101.77, 0.02)), class = "data.frame", row.names = c(NA, 
-18L))

这个数据的核心问题是行分组存储：每个type对应一组行，每组内包含不同metric和code的信息，而...2/...3这类列对应不同的month值。需要分步骤规整：

步骤1：标记并填充type分组 #

先识别每行所属的type，并向下填充缺失值：

library(tidyverse)

clean_df <- df %>%
  mutate(type = case_when(
    ...1 %in% c("Alternative", "Traditional") ~ ...1,
    TRUE ~ NA_character_
  )) %>%
  fill(type, .direction = "down")

步骤2：标记并填充metric分组 #

每个type下包含Original Value、Rebased、Percentage change三个metric组，同样标记并填充：

clean_df <- clean_df %>%
  mutate(metric = case_when(
    ...1 %in% c("Original Value", "Rebased", "Percentage change") ~ ...1,
    TRUE ~ NA_character_
  )) %>%
  fill(metric, .direction = "down")

步骤3：筛选code行并规整列 #

筛选出...1为纯数字的code行，然后将...2/...3这类列转换为month和value：

clean_df <- clean_df %>%
  filter(str_detect(...1, "^\\d+$")) %>%
  rename(code = ...1) %>%
  pivot_longer(cols = starts_with("..."), names_to = "month_col", values_to = "value") %>%
  mutate(month = case_when(
    month_col == "...2" ~ df$...2[2],
    month_col == "...3" ~ df$...3[2]
  )) %>%
  select(code, type, month, metric, value) %>%
  mutate(
    value = as.numeric(value),
    # 转换month为日期格式（Excel日期起始为1899-12-30，42309对应2015-11-01）
    month = as.Date(as.numeric(month), origin = "1899-12-30") %>% format("%m/%d/%Y")
  )

步骤4：批量处理列表中的所有数据框 #

用lapply封装上述逻辑，批量处理整个数据框列表：

dflist_clean <- lapply(dflist, function(df) {
  df %>%
    mutate(type = case_when(
      ...1 %in% c("Alternative", "Traditional") ~ ...1,
      TRUE ~ NA_character_
    )) %>%
    fill(type, .direction = "down") %>%
    mutate(metric = case_when(
      ...1 %in% c("Original Value", "Rebased", "Percentage change") ~ ...1,
      TRUE ~ NA_character_
    )) %>%
    fill(metric, .direction = "down") %>%
    filter(str_detect(...1, "^\\d+$")) %>%
    rename(code = ...1) %>%
    pivot_longer(cols = starts_with("..."), names_to = "month_col", values_to = "value") %>%
    mutate(month = case_when(
      month_col == "...2" ~ df$...2[2],
      month_col == "...3" ~ df$...3[2]
    )) %>%
    select(code, type, month, metric, value) %>%
    mutate(
      value = as.numeric(value),
      month = as.Date(as.numeric(month), origin = "1899-12-30") %>% format("%m/%d/%Y")
    )
})

补充说明 #

• 用str_detect(...1, "^\\d+$")筛选code行，因为code均为纯数字字符串
• 日期转换逻辑适配Excel日期格式，若你的日期编码规则不同，可调整origin参数
• 若存在更多类似...4的列，只需在case_when中补充对应的month提取规则即可

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