如何填补多轮调查数据中ID变量的缺失值

我有一个包含两轮调查数据的数据框，通过wave列区分调查轮次。数据里有student_id和student_id_room两个ID列，每一列都能唯一识别学生。第一轮调查（wave_1）里，部分学生只填了student_id，另一部分只填了student_id_room；第二轮调查（wave_2）里所有学生都同时提供了两个ID。现在需要把这两个ID列里的NA值替换成对应的正确值。

原始数据代码 #

student_id <- c(1718,NA,1901)
student_id_room <- c(NA,12,NA)
var1 <- c(17,18,41)
wave_1 <- data.frame(student_id,
                     student_id_room,
                     var1)

rm(student_id,
   student_id_room,
   var1)

student_id <- c(1718,1801,1901)
student_id_room <- c(24,12,6)
var1 <- c(31,32,43)
wave_2 <- data.frame(student_id,
                     student_id_room,
                     var1)

rm(student_id,
   student_id_room,
   var1)

all_waves <- lst(wave_1,
                 wave_2) %>% 
  bind_rows(.id="wave")

期望得到的数据框 #

wave <- c("wave_1","wave_1","wave_1","wave_2","wave_2","wave_2")
student_id <- c(1718,1801,1901,1718,1801,1901)
student_id_room <- c(24,12,6,24,12,6)
var1 <- c(17,18,41,31,32,43)
all_waves_revised <- data.frame(wave,
                                student_id,
                                student_id_room,
                                var1)

解决方案 #

核心思路是利用wave_2中完整的ID对应关系作为映射表，去填补wave_1中的缺失值。具体步骤如下：

1. 从wave_2提取唯一的ID映射关系：

id_map <- wave_2 %>%
  select(student_id, student_id_room) %>%
  distinct()

2. 处理wave_1的缺失值：
   通过两次左连接，分别根据已有的ID匹配缺失的对应ID，再用coalesce函数替换NA值：

wave_1_fixed <- wave_1 %>%
  # 根据student_id匹配对应的room ID
  left_join(id_map, by = "student_id") %>%
  mutate(student_id_room = coalesce(student_id_room.x, student_id_room.y)) %>%
  select(-student_id_room.x, -student_id_room.y) %>%
  # 根据room ID匹配对应的student ID
  left_join(id_map, by = "student_id_room") %>%
  mutate(student_id = coalesce(student_id.x, student_id.y)) %>%
  select(-student_id.x, -student_id.y)

3. 合并处理后的wave_1和原始wave_2，得到最终结果：

all_waves_revised <- bind_rows(
  wave_1_fixed %>% mutate(wave = "wave_1"),
  wave_2 %>% mutate(wave = "wave_2")
) %>%
  select(wave, student_id, student_id_room, var1)

运行上述代码后，得到的all_waves_revised就和期望的数据框完全一致了。

内容的提问来源于stack exchange，提问作者marc.th