如何在R中合并三个数据集并匹配对应年份填充关联数据？

原始数据集 #

dataset_1 <- data.frame(
  Name = rep(c("Guyle", "Porter", "Frank", "Kojo"), c(5L, 5L, 5L, 1L)),
  Year = c(
    2014, 2015, 2016, 2017, 2018, 2014, 2015, 2016, 2017, 2018, 2014, 2015, 2016,
    2017, 2018, 2016
  ),
  Net_profit = c(
    2970L, 2798L, 2769L, 2686L, 2806L, 3096L, 2776L, 2993L, 2829L,
    3090L, 2536L, 2604L, 2984L, 2881L, 3100L, 2825L
  )
)

insurance <- data.frame(
  Names = c("Guyle", "Porter", "Frank"),
  `2014` = c(158, 154, 160),
  `2015` = c("169", "185", "NA"),
  `2016` = c("221", "NA", "NA"),
  `2017` = c("NA", "450", "691"),
  `2018` = c(200, 101, 321),
  check.names = FALSE
)

dataset3 <- data.frame(
  Name = c("Guyle", "Porter", "Frank"),
  Year = rep(2016, 3L),
  Risk_attitude = c("averse", "seeking", "seeking"),
  Acreage_ha = c(17.63, 26, 12),
  Adoption = c("late", "middle", "early")
)

需求 #

• 为dataset_1新增insurance列，填充对应Name和Year的保险金额
• 将dataset3中的Risk_attitude、Acreage_ha等列整合到dataset_1，仅在Year=2016的行填充对应值，其余行留空

解决方案 #

使用dplyr和tidyr工具完成数据转换与合并，步骤如下：

1. 转换insurance数据集格式
   把宽格式的insurance转成长格式，统一数据类型并修正列名，方便后续匹配：

   library(dplyr)
   library(tidyr)
   
   insurance_long <- insurance %>%
     rename(Name = Names) %>%
     pivot_longer(cols = -Name, names_to = "Year", values_to = "insurance") %>%
     mutate(
       Year = as.integer(Year),
       insurance = ifelse(insurance == "NA", "", as.character(insurance))
     )
   

2. 合并insurance到dataset_1
   通过left_join按Name和Year匹配，保留dataset_1的所有行：

   temp_data <- dataset_1 %>%
     left_join(insurance_long, by = c("Name", "Year")) %>%
     mutate(insurance = ifelse(is.na(insurance), "", insurance))
   

3. 合并dataset3到临时数据集
   同样按Name和Year匹配，仅2016年的行会填充dataset3的内容，其余空值替换为空白字符串：

   overall_dataset <- temp_data %>%
     left_join(dataset3, by = c("Name", "Year")) %>%
     rename(Acreage = Acreage_ha) %>%
     mutate(
       Acreage = ifelse(is.na(Acreage), "", as.character(Acreage)),
       Risk_attitude = ifelse(is.na(Risk_attitude), "", Risk_attitude),
       Adoption = ifelse(is.na(Adoption), "", Adoption)
     ) %>%
     select(Name, Year, Net_profit, insurance, Acreage, Risk_attitude, Adoption)
   

最终结果示例 #

运行代码后，overall_dataset与需求结构一致，部分行展示如下：

head(overall_dataset)
#>     Name Year Net_profit insurance Acreage Risk_attitude Adoption
#> 1  Guyle 2014       2970       158                         
#> 2  Guyle 2015       2798       169                         
#> 3  Guyle 2016       2769       221   17.63        averse    late
#> 4  Guyle 2017       2686                         
#> 5  Guyle 2018       2806       200                         
#> 6 Porter 2014       3096       154                         

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