R语言：基于另一数据框填充缺失值并扩展日期范围

需求 #

• 将df1扩展至1948-01-01至2016-12-31的完整日期范围，新增行的数值列填充NA
• 用df2中对应日期的单元格值替换df1中的所有NA

用户尝试的无效代码 #

seq_df <- data_frame(Date = seq.Date(as.Date("1948-01-01"), 
                                     as.Date("2016-12-31"), 
                                     by="day"))

data1=full_join(df1, seq_df) %>% arrange(Date)

示例数据 #

df1结构 #

df1=structure(list(Date = structure(c(3804, 3805, 3806, 3807, 3808, 
3809, 3810, 3811, 3812, 3813, 3814, 3815, 3816, 3817, 3818, 3819, 
3820, 3821, 3822, 3823, 3824, 3825, 3826, 3827, 3828, 3829, 3830, 
3831, 3832, 3833), class = "Date"), Pr = c(NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_
), Tmax = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, 13.5, 13, 12, 11.2, 8.5, 12, 13, 17.5, 17.1, 
17.1, 9.5, 6.5, NA), Tmin = c(NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 10.5, 11, 12, 12.5, 7, 
10, 11.5, 7, 5, 4.5, 1, 2)), row.names = c(NA, 30L), class = "data.frame")

df2结构 #

df2=structure(list(Date = structure(c(3804, 3805, 3806, 3807, 3808, 
3809, 3810, 3811, 3812, 3813, 3814, 3815, 3816, 3817, 3818, 3819, 
3820, 3821, 3822, 3823, 3824, 3825, 3826, 3827, 3828, 3829, 3830, 
3831, 3832, 3833), class = "Date"), Pr = c(0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
1.985656032, 0, 0), Tmax = c(16.7579, 16.425, 16.8751, 15.9877, 
15.3752, 15.4138, 15.6038, 14.194, 15.9599, 17.0566, 13.6396, 
15.8548, 15.9682, 17.4073, 17.9238, 10.1966, 10.7029, 13.6226, 
12.7758, 15.0315000000001, 14.5553, 10.0413, 12.5804, 11.7263, 
13.9642, 20.8888, 19.1779, 6.32770000000005, 8.32380000000001, 
10.3664), Tmin = c(2.38980000000004, 3.73170000000005, 2.37440000000004, 
0.744400000000041, 1.34430000000003, 0.914200000000051, -0.107699999999966, 
-0.11099999999999, 1.29250000000002, 2.44490000000002, -0.0418999999999983, 
2.48950000000002, 2.7414, 2.8424, 0.513500000000022, -5.2158, 
-9.0718, 0.44380000000001, -1.51529999999997, -2.18459999999999, 
2.36690000000004, 0.0375000000000227, -3.572, -2.93179999999995, 
-2.54819999999995, 0.20700000000005, 5.50260000000003, 1.28820000000002, 
-2.12149999999997, -7.39839999999998)), row.names = 11841:11870, class = "data.frame")

步骤1：扩展df1的日期范围 #

创建完整日期序列后，用left_join合并df1，确保所有目标日期都被保留，新增日期对应的数值列自动填充NA：

library(dplyr)

# 生成完整日期序列
seq_df <- tibble(Date = seq.Date(as.Date("1948-01-01"), 
                                 as.Date("2016-12-31"), 
                                 by="day"))

# 合并得到扩展后的df1
expanded_df1 <- left_join(seq_df, df1, by = "Date")

步骤2：用df2的值替换NA #

将df2合并到扩展后的df1，使用coalesce函数优先保留df1的非NA值，用df2的值填充NA：

# 合并df2并完成NA替换
final_df <- expanded_df1 %>%
  left_join(df2, by = "Date", suffix = c("_df1", "_df2")) %>%
  mutate(
    Pr = coalesce(Pr_df1, Pr_df2),
    Tmax = coalesce(Tmax_df1, Tmax_df2),
    Tmin = coalesce(Tmin_df1, Tmin_df2)
  ) %>%
  select(Date, Pr, Tmax, Tmin) # 保留目标列

关键说明 #

• left_join(seq_df, df1)避免了full_join可能产生的重复行问题，确保日期序列完整
• coalesce函数会依次取第一个非NA值，精准实现"保留df1已有数据，用df2补全NA"的需求
• 若df2未覆盖全部目标日期，未覆盖部分的NA会保留，可根据实际情况补充处理

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