在R中将复杂宽长混合数据集转换为值与范围两类长格式数据集

步骤1：加载依赖包 #

使用tidyverse工具链处理数据：

library(tidyverse)

步骤2：修复复合表头并清洗数据 #

原始数据的表头分为两行（地区+指标类型），先合并表头，再填充类别并过滤无效行：

# 提取前两行生成复合表头
header_rows <- df1[1:2, ]
col_names <- map2_chr(header_rows[1, ], header_rows[2, ], function(area, type) {
  if(type == "" || is.na(type)) area else paste(area, type, sep = " - ")
})
col_names[3] <- "Notes" # 保留Notes列原名

# 清洗数据：设置新列名、填充类别、过滤分隔行
df_clean <- df1[-c(1,2), ] %>%
  set_names(col_names) %>%
  mutate(Category = `Canada > London`, .before = 1) %>%
  fill(Category, .direction = "down") %>%
  filter(Category != `Canada > London`)

步骤3：转换为长格式并拆分数据集 #

通过pivot_longer转长格式，再按指标类型拆分出值数据集（Price）和范围数据集（Range）：

# 转长格式
df_long <- df_clean %>%
  pivot_longer(
    cols = starts_with("Canada") | starts_with("United Kingdom"),
    names_to = c("Location", "Metric"),
    names_sep = " - ",
    values_to = "Value"
  ) %>%
  drop_na(Value)

# 生成值数据集（Price）
value_df <- df_long %>%
  filter(Metric == "Price") %>%
  select(Category, Item = `Canada > London`, Location, Price = Value) %>%
  mutate(Price = as.numeric(Price))

# 生成范围数据集（Range）
range_df <- df_long %>%
  filter(Metric == "Range") %>%
  select(Category, Item = `Canada > London`, Location, Range = Value)

最终结果示例 #

值数据集（value_df）：

value_df
#> # A tibble: 8 × 4
#>   Category     Item        Location               Price
#>   <chr>        <chr>       <chr>                  <dbl>
#> 1 Restaurant   Fried beef  Canada > London          27
#> 2 Restaurant   Fried beef  United Kingdom > London  29
#> 3 Restaurant   Fried potato Canada > London          5
#> 4 Restaurant   Fried potato United Kingdom > London  8
#> 5 Bar          Beer Lager  Canada > London           5
#> 6 Bar          Beer Lager  United Kingdom > London   6
#> 7 Bar          Beer Dark   Canada > London           4
#> 8 Bar          Beer Dark   United Kingdom > London   5

范围数据集（range_df）：

range_df
#> # A tibble: 8 × 4
#>   Category     Item        Location               Range  
#>   <chr>        <chr>       <chr>                  <chr>  
#> 1 Restaurant   Fried beef  Canada > London        25-30  
#> 2 Restaurant   Fried beef  United Kingdom > London 25 - 35
#> 3 Restaurant   Fried potato Canada > London        3 - 8  
#> 4 Restaurant   Fried potato United Kingdom > London 3 - 8  
#> 5 Bar          Beer Lager  Canada > London         4 - 8  
#> 6 Bar          Beer Lager  United Kingdom > London 4 - 8  
#> 7 Bar          Beer Dark   Canada > London         3 - 7  
#> 8 Bar          Beer Dark   United Kingdom > London 3 - 7

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