R语言中基于月、年、纬度带匹配合并DataFrame的技术问询

我有两个DataFrame：父级数据集Norway_MM4包含57043行，另一个Norway_averageweather的month、year和Latitude_bands字段组合为唯一值，但Norway_MM4的该组合并非唯一。需要将天气数据中的Avg_temp、avg_precip等信息匹配到父级DataFrame对应的日期和纬度带中。

数据示例 #

Norway_MM4 <- 
  dplyr::tribble(
    ~date, ~month, ~year, ~Latitude, ~Longitude, ~Latitude_bands,
    "2018-03-07", 03, 2018, 33.21580, -81.52677, 32, 
    "2020-04-07", 04, 2020, 33.21608, -81.52699, 32, 
    "2018-03-07", 03, 2018, 34.21592, -81.52706, 34, 
    "2023-07-09", 07, 2023, 34.21521, -81.53458, 34, 
    "2018-03-10", 03, 2018, 36.21737, -81.53036, 36, 
    "2010-08-11", 08, 2010, 36.21171, -81.53041, 36, 
    "2010-08-11", 08, 2010, 38.21171, -71.53041, 38, 
    "2010-08-11", 08, 2010, 40.21167, -91.53041, 40
  )

Norway_averageweather <- 
  dplyr::tribble(
    ~date, ~month, ~year, ~Avg_temp, ~avg_precip, ~Latitude_bands,
    "2018-03-07", 03, 2018, 33.21, 81.52 ,32 ,
    "2020-04-07", 04, 2020, 33.21, 61.50 ,32 ,
    "2018-05-07", 03, 2018, 34.41, 181.52, 34, 
    "2023-07-09", 07, 2023, 34.21, 61.34 ,34 ,
    "2018-03-10", 03, 2018, 36.31, 810.36, 36, 
    "2010-08-11", 08, 2010, 36.21, 81.41 ,36 ,
    "2010-08-11", 08, 2010, 37.21, 90.41 ,38 ,
    "2010-08-11", 08, 2010, 38.21, 91.41 ,40
  )

尝试的代码 #

merged_df <- merge(unique(Norway_MM4), 
                   Norway_averageweather, 
                   by = c("Latitude_Bands", "month", "year"), 
                   all.x = TRUE)

问题分析 #

原代码存在两个问题：

• 拼写错误：Latitude_Bands应改为Latitude_bands（大小写不匹配）
• 错误使用unique(Norway_MM4)：会丢失原数据中的重复行，而我们需要保留父级数据集的所有行，仅匹配对应维度的天气数据

正确解决方案 #

方法1：Base R merge() #

merged_df <- merge(Norway_MM4, 
                   # 仅选择天气数据中需要匹配和合并的列，避免date列重复
                   Norway_averageweather[, c("month", "year", "Latitude_bands", "Avg_temp", "avg_precip")],
                   by = c("month", "year", "Latitude_bands"),
                   all.x = TRUE)

方法2：dplyr left_join()（更符合tidyverse风格） #

library(dplyr)

merged_df <- Norway_MM4 %>%
  left_join(
    Norway_averageweather %>% select(month, year, Latitude_bands, Avg_temp, avg_precip),
    by = c("month", "year", "Latitude_bands")
  )

最终结果 #

运行上述代码后，将得到符合预期的合并结果：

merged_df <-
  dplyr::tribble(
    ~date, ~month, ~year,  ~Latitude, ~Longitude, ~Latitude_bands, ~Avg_temp, ~avg_precip,
    "2018-03-07", 03, 2018, 33.21580, -81.52677, 32, 33.21, 81.52 ,
    "2020-04-07", 04, 2020, 33.21608, -81.52699, 32, 33.21, 61.50,
    "2018-03-07", 03, 2018, 34.21592, -81.52706, 34, 34.41, 181.52,
    "2023-07-09", 07, 2023, 34.21521, -81.53458, 34, 34.21, 61.34 ,
    "2018-03-10", 03, 2018, 36.21737, -81.53036, 36, 36.31, 810.36,
    "2010-08-11", 08, 2010, 36.21171, -81.53041, 36, 36.21, 81.41 ,
    "2010-08-11", 08, 2010, 38.21171, -71.53041, 38, 37.21, 90.41 ,
    "2010-08-11", 08, 2010, 40.21167, -91.53041, 40, 38.21, 91.41
  )

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