R语言中基于name和idA匹配填充dataframe的idB列NA值问题

问题 #

通过name和idA两列合并两个数据框后，得到包含name、idA、idB的长格式数据框，每个个体对应多条观测。由于原部分数据集没有idB列，当前idB存在缺失值。需求是：按name和idA分组，用组内非缺失的idB值填充组内的缺失值；若组内没有非缺失的idB，则保留缺失状态。

尝试用tidyverse的group_by()+mutate()方案实现，代码如下：

df1_rev <- df1 %>%
  group_by(name, idA) %>%
  mutate(idB = unique(idB[!is.na(idB)]))

但在实际的tbl_df类数据集中报错：

Error in mutate():
ℹ In argument: idB = unique(idB[!is.na(idB)]).
ℹ In group 2: name = "XXXX" and idA = "XXXX".
Caused by error:
! idB must be size 1, not 0.

示例数据 #

注意：示例数据中idB列的"NA"是字符串，不是R原生的缺失值NA：

df1 <- data.frame(
  name = c("Sachin","Sachin","Sourav","Sourav","Dravid", "Sehwag", "Dhoni", "Ronin",   "Ronin", "Paul", "jorge", "Anke", "Krystal", "Katy", "Katy","Josh", "Paul"),
  idA = c("1234", "1234", "9876", "9876", "3456", "8888", "4032", "1093", "1093", "3452", "3332", "7656", "0023", "4244", "4244","4244","1111"),
  idB = c(1,"NA",2,2,3,"NA",5,6,"NA","NA",8,9,10,"NA",11,"NA","NA"),
  var_a = c(4,5,1.6,4.4,2,2,4,9,2,34,5,6,7,2,0,4,8),
  var_b = c("d","h","g","w","g","p","ads","f","a","iu","n","lag", "lo", "t", "v","y","k"),
  stringsAsFactors = FALSE)

期望结果 #

df1_rev <- data.frame(
  name = c("Sachin","Sachin","Sourav","Sourav","Dravid", "Sehwag", "Dhoni", "Ronin", "Ronin", "Paul", "jorge", "Anke", "Krystal", "Katy", "Katy","Josh","Paul"),
  idA = c("1234", "1234", "9876", "9876", "3456", "8888", "4032", "1093", "1093", "3452", "3332", "7656", "0023", "4244", "4244","4244","1111"),
  idB = c(1,1,2,2,3,"NA",5,6,6,"NA",8,9,10,11,11,"NA","NA"),
  var_a = c(4,5,1.6,4.4,2,2,4,9,2,34,5,6,7,2,0,4,8),
  var_b = c("d","h","g","w","g","p","ads","f","a","iu","n","lag", "lo", "t", "v","y","k"),
  stringsAsFactors = FALSE)

解决方案 #

错误原因 #

原代码报错是因为当某个分组内没有非缺失的idB值时，unique(idB[!is.na(idB)])会返回长度为0的向量，而mutate要求新列长度必须和组内行数一致，导致不匹配。另外，示例数据中的idB是字符串类型的"NA"，需要先转换成R原生的NA才能正确识别缺失值。

正确实现步骤 #

方法1：使用fill函数（推荐）

library(tidyverse)

df1_rev <- df1 %>%
  # 先把字符串"NA"转为R原生缺失值
  mutate(idB = ifelse(idB == "NA", NA_character_, idB)) %>%
  group_by(name, idA) %>%
  # 双向填充：先向下再向上，确保组内所有缺失值都被覆盖
  fill(idB, .direction = "downup") %>%
  # 把缺失值转回字符串"NA"，匹配期望结果格式
  mutate(idB = ifelse(is.na(idB), "NA", idB)) %>%
  ungroup()

方法2：使用first(na.omit(idB))

library(tidyverse)

df1_rev <- df1 %>%
  mutate(idB = ifelse(idB == "NA", NA_character_, idB)) %>%
  group_by(name, idA) %>%
  # 取组内第一个非缺失值，无有效值则返回NA
  mutate(idB = first(na.omit(idB))) %>%
  mutate(idB = ifelse(is.na(idB), "NA", idB)) %>%
  ungroup()

补充说明 #

• 如果你的实际数据中idB是数值类型的缺失值（即真正的NA），可以跳过字符串转换的步骤，直接进行分组填充。
• 两种方法都能满足需求：fill通过双向填充覆盖缺失值，first(na.omit(idB))直接提取组内有效取值，无有效值时保留缺失状态。

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