R语言pivot_longer实现重名列宽表转为带单位列的规范长表

问题说明 #

现有数据集存在三类格式问题：

• 存在重复列名：Real_GDP、Unemployment各出现2次，分别对应不同年份的指标值
• 元数据未结构化存储：指标单位存于第一行指标列、年份存于第二行指标列
• 有效预测数据从第三行才开始

原始数据样例：

Forecaster                   Real_GDP         Real_GDP   Unemployment       Unemployment   Country 

NA                        Variation in %         NA      % of active pop.      NA         USA
Individual Forecasts           2022             2023        2022               2023       USA
Forecaster_1                    3.3              4.1        1.3                1.6        USA 
Forecaster_2                    2.5              3.9        0.9                1.3        USA

目标输出结构：

Forecaster       Unit             Indicator     2022    2023  Country

Forecaster_1     Variation in %   Real_GDP      3.3     4.1   USA
Forecaster_2     Variation in %   Real_GDP      2.5     3.9   USA
Forecaster_1     % of active pop. Unemployment  1.3     1.6   USA
Forecaster_2     % of active pop. Unemployment  0.9     1.3   USA

测试数据构造代码：

test2 <- data.frame(Forecaster=c(NA,"Individual Forecasts", "Forecaster_1", "Forecaster_2"),
                   Real_GDP=c("Variation in %", "2022", 3.3, 2.5),
                   Real_GDP=c(NA, "2023", 4.1, 3.9),
                   Unemployment=c("% of active pop.", "2022", 1.3, 0.9),
                   Unemployment=c(NA, "2023", 1.6, 1.3),
                   Country=c("USA","USA", "USA", "USA"))
colnames(test2) <- c("Forecaster", "Real_GDP", "Real_GDP", "Unemployment", "Unemployment", "Country")

处理逻辑 #

• 先为重复列名添加唯一后缀，避免列索引错误
• 分别提取存储在行中的元数据：指标单位、对应年份，生成列名和元数据的映射表
• 过滤掉存储元数据的前两行，保留有效预测数据
• 先转长表关联元数据，再将年份维度转回宽表，调整列顺序得到最终结果

完整实现代码 #

依赖dplyr和tidyr（tidyverse生态包）：

library(dplyr)
library(tidyr)

# 1. 为重复列名添加唯一后缀
colnames(test2) <- make.unique(colnames(test2), sep = "_")

# 2. 提取各列对应指标单位
unit_map <- test2 |> 
  slice(1) |> 
  select(starts_with(c("Real_GDP", "Unemployment"))) |> 
  pivot_longer(cols = everything(), names_to = "col_name", values_to = "Unit") |> 
  mutate(Indicator = sub("_\\d+$", "", col_name)) |> 
  fill(Unit, .direction = "down")

# 3. 提取各列对应年份
year_map <- test2 |> 
  slice(2) |> 
  select(starts_with(c("Real_GDP", "Unemployment"))) |> 
  pivot_longer(cols = everything(), names_to = "col_name", values_to = "Year")

# 4. 合并列元数据映射表
col_meta <- unit_map |> 
  left_join(year_map, by = "col_name") |> 
  select(col_name, Unit, Indicator, Year)

# 5. 提取有效预测数据
valid_data <- test2 |> 
  slice(-c(1,2))

# 6. 长宽转换+结构整理
final_result <- valid_data |> 
  pivot_longer(
    cols = starts_with(c("Real_GDP", "Unemployment")),
    names_to = "col_name",
    values_to = "value"
  ) |> 
  left_join(col_meta, by = "col_name") |> 
  mutate(value = as.numeric(value)) |> 
  pivot_wider(
    names_from = Year,
    values_from = value
  ) |> 
  select(Forecaster, Unit, Indicator, `2022`, `2023`, Country)

输出结果 #

运行后final_result输出完全匹配目标结构：

# A tibble: 4 × 6
  Forecaster  Unit             Indicator   `2022` `2023` Country
  <chr>       <chr>            <chr>        <dbl>  <dbl> <chr>  
1 Forecaster_1 Variation in %   Real_GDP      3.3    4.1 USA    
2 Forecaster_1 % of active pop. Unemployment  1.3    1.6 USA    
3 Forecaster_2 Variation in %   Real_GDP      2.5    3.9 USA    
4 Forecaster_2 % of active pop. Unemployment  0.9    1.3 USA

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