R语言dataframe高级重塑与pivoting透视问题求解

问题概述 #

你遇到的是典型的非规范复合表头数据重塑问题，原始数据前3行存储的是指标属性、统计口径、年份三类元信息，第4行开始才是分国家的实际观测值，无法直接判定长宽表格式的原因是原始数据不符合Tidy Data的基本标准，元数据和观测值混杂存储。

• 给定原始数据结构：

df_given <- data.frame(
  first_column  = c("NA", "NA", "NA", "Country1", "Country2", "Country3"),
  second_column  = c("Consumption", "real", "2021", 10, 11, 23),
  third_column = c("Consumption", "real", "2022", 20, 22, 12),
  fourth_column = c("Inflation", "expected", "2021", 1, 1.2, 2.5),
  fifth_column = c("Inflation", "expected", "2022", 5, 3, 2)
)

• 目标输出结构：

df_target <- data.frame(
  first_column = c("type", "Country1 Consumption real", "Country2 Consumption real", 
                   "Country3 Consumption real", "Country1 Inflation expected", 
                   "Country2 Inflation expected","Country3 Inflation expected"),
  second_column = c(2021, 10, 11, 23, 1, 1.2, 2.5),
  third_column = c(2022, 20, 22, 12, 5, 3, 2)
)

实现思路 #

• 第一步：拆分元数据与观测值，先提取前3行内容，解析出每一列对应的「指标+统计口径」、「年份」两个属性
• 第二步：提取第4行及以后的实际观测值，保留第一列的国家维度信息
• 第三步：将观测值转换为规范长表，每一行对应「国家+指标+年份+数值」的单条观测，关联之前解析的元数据
• 第四步：以年份为维度做长转宽操作，让2021、2022各自成为独立列，拼接国家名和指标名生成第一列的内容
• 第五步：补充目标表第一行的type标识行，重命名列后对齐目标结构

可复现代码 #

依赖dplyr和tidyr（属于tidyverse生态），运行后可直接得到目标结果：

library(dplyr)
library(tidyr)

# 解析每列对应的元信息
col_info <- df_given[1:3, ] |> 
  summarise(across(-first_column, ~paste(.x[1], .x[2])))
year_info <- as.character(df_given[3, -1])
col_names <- c("col2", "col3", "col4", "col5")

# 提取实际观测行
obs_data <- df_given[4:nrow(df_given), ]
colnames(obs_data) <- c("country", col_names)

# 转换为规范长表并关联元数据
long_data <- obs_data |> 
  pivot_longer(cols = all_of(col_names), values_to = "value") |> 
  mutate(
    indicator = recode(name, !!!setNames(as.character(col_info), col_names)),
    year = recode(name, !!!setNames(year_info, col_names)),
    value = as.numeric(value)
  ) |> 
  select(country, indicator, year, value)

# 年份维度转宽列，拼接国家与指标名
wide_data <- long_data |> 
  pivot_wider(names_from = year, values_from = value) |> 
  mutate(first_column = paste(country, indicator)) |> 
  select(first_column, `2021`, `2022`)

# 补充首行标识，对齐目标结构
final_df <- tibble(
  first_column = "type",
  `2021` = 2021,
  `2022` = 2022
) |> 
  bind_rows(wide_data) |> 
  rename(second_column = `2021`, third_column = `2022`)

补充说明 #

长宽表转换函数（pivot_longer/pivot_wider）仅适用于已经符合Tidy Data标准的结构化数据，处理这类复合表头、元数据和观测值混杂的表格时，必须先做结构清洗拆分出变量和观测，再进行转换操作，否则会出现维度匹配错误。

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