按组根据最近时间点用另一数据框值填充NA

问题 #

现有两个数据框df1和df2，均包含分组变量ID、日期类型的Timepoint变量以及数值变量Values。其中df1的Values字段存在NA值，需要按相同ID，用df2中时间点最接近的Values值填充这些NA。

示例数据：

df1 <- data.frame(
  ID = c(1,1,1,1,2,2,3,3),
  Timepoint = as.Date(c("2019-05-21", "2019-05-23", "2019-05-26", "2019-05-27", "2018-07-03", "2018-08-03", "2018-06-04", "2018-06-10")),
  Values = c(NA, 23, NA, NA, NA, 35, 23, 42)
)

df2 <- data.frame(
  ID = c(1,1,2,2,3),
  Timepoint = as.Date(c("2019-05-20", "2019-05-24", "2018-09-03", "2018-06-04", "2018-06-10")),
  Values = c(11, 30, 35, 23, 42)
)

期望结果：

ID  Timepoint Values
1  1 2019-05-21     11
2  1 2019-05-23     23
3  1 2019-05-26     30
4  1 2019-05-27     30
5  2 2018-07-03     23
6  2 2018-08-03     35
7  3 2018-06-04     23
8  3 2018-06-10     42

注：ID为1的两条NA值均用df2中2019-05-24对应的30填充，因其时间点最近。曾尝试使用data.table的roll = "nearest"进行连接，但发现df2中的每个值仅能被使用一次。优先选用lubridate、tidyverse包，也可接受其他合适的包。

解决方案 #

方法一：分组匹配最近时间点 #

使用tidyverse结合lubridate包，按ID分组后，对每个NA值匹配同组内时间差最小的df2数据：

library(tidyverse)
library(lubridate)

df_filled <- df1 %>%
  group_by(ID) %>%
  mutate(
    Values = ifelse(is.na(Values),
                    # 针对每个NA的时间点，从df2同组数据中找最近时间对应的Values
                    map_dbl(Timepoint, ~ {
                      df2_sub <- df2 %>% filter(ID == cur_group()$ID)
                      time_diff <- abs(.x - df2_sub$Timepoint)
                      df2_sub$Values[which.min(time_diff)]
                    }),
                    Values)
  ) %>%
  ungroup()

# 输出结果
print(df_filled)

代码说明

• 按ID分组，确保仅在同组内进行时间匹配；
• 对df1中每个NA值，计算其时间点与df2同组所有时间点的绝对差值，选取差值最小的Values填充；
• 非NA的Values保持原始值不变。

方法二：全组合筛选后合并 #

通过生成同ID下的所有时间组合，计算时间差后筛选最近匹配，再合并回df1：

library(tidyverse)
library(lubridate)

df_filled_alt <- df1 %>%
  # 按ID合并两个数据框
  left_join(df2, by = "ID", suffix = c("_df1", "_df2")) %>%
  # 计算时间差绝对值
  mutate(time_diff = abs(Timepoint_df1 - Timepoint_df2)) %>%
  # 按ID和df1的时间点分组，筛选出时间差最小的记录
  group_by(ID, Timepoint_df1) %>%
  filter(time_diff == min(time_diff)) %>%
  # 填充NA值
  mutate(Values = ifelse(is.na(Values_df1), Values_df2, Values_df1)) %>%
  # 保留目标列并去重
  select(ID, Timepoint = Timepoint_df1, Values) %>%
  distinct() %>%
  ungroup()

# 输出结果
print(df_filled_alt)

代码说明

• 先通过left_join按ID合并数据，生成所有可能的时间组合；
• 计算每个组合的时间差，筛选出每组内时间差最小的记录；
• 用df2的Values填充df1的NA值，最后去重得到最终结果。

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