如何合并结构不同的宽格式与长格式面板DataFrame？

数据背景 #

现有两个R数据框：

df1 <- data.frame("region" = c("Acre", "Espyrito Santo", "Amapah", "Goiahs"),
                  "time1" = c(1, 4, 5, 7),
                  "time2" = c(7, 4, 2, 6),
                  "time3" = c(5, 2, 5, 0))

df2 <- data.frame("region" = c("ACRE", "ACRE", "ACRE", 
                               "ESPIRITO SANTO","ESPIRITO SANTO","ESPIRITO SANTO",
                               "AMAPA","AMAPA","AMAPA", 
                               "GOIAS","GOIAS","GOIAS"),
                  "date" = c("time1", "time2", "time3", 
                             "time1", "time2", "time3", 
                             "time1", "time2", "time3", 
                             "time1", "time2", "time3"))

• df1为宽格式：region列存储地区，time1-time3列对应不同时段，值为谋杀数
• df2为长格式：region列大小写与df1不一致，部分地区拼写也有差异；date列存储时段，需新增murder列，根据地区和时段匹配df1的数据填充

解决步骤 #

1. 统一地区名称格式

先修正大小写不一致和拼写差异问题，确保两地数据的地区标识完全匹配：

# 安装并加载tidyverse工具包（包含dplyr和tidyr）
install.packages("tidyverse")
library(tidyverse)

# 统一大小写为小写，同时修正拼写差异
df1 <- df1 %>%
  mutate(region = tolower(region)) %>%
  mutate(region = case_when(
    region == "amapah" ~ "amapa",
    region == "goiahs" ~ "goias",
    region == "espyrito santo" ~ "espirito santo",
    TRUE ~ region
  ))

df2 <- df2 %>%
  mutate(region = tolower(region))

2. 将df1转为长格式

把宽格式的df1转换为长格式，让结构和df2对齐，方便后续匹配：

df1_long <- df1 %>%
  pivot_longer(
    cols = starts_with("time"),  # 选择所有以time开头的列
    names_to = "date",           # 原列名转为date列
    values_to = "murder"         # 原列值转为murder列
  )

3. 合并数据填充murder列

使用left_join按region和date匹配，将df1的谋杀数填充到df2中：

df2_final <- df2 %>%
  left_join(df1_long, by = c("region", "date"))

查看最终结果 #

执行以下代码查看填充后的df2：

print(df2_final)

输出示例：

region  date murder
1           acre time1      1
2           acre time2      7
3           acre time3      5
4  espirito santo time1      4
5  espirito santo time2      4
6  espirito santo time3      2
7           amapa time1      5
8           amapa time2      2
9           amapa time3      5
10          goias time1      7
11          goias time2      6
12          goias time3      0

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