如何用R语言基于DataFrame分组对应值填充缺失列值？

现有一个DataFrame，其中Var1与Var2应保持一一对应关系，但Var2存在缺失值（示例中为?，代码中为NA）。需要实现规则：当Var2为缺失值时，用相同Var1对应的非缺失Var2值填充。因数据量巨大无法逐个设置规则，尝试用dplyr包生成映射表，但缺少最后填充部分的代码，寻求完整实现方案。

示例原始数据 #

Var1    Var2
123     ADH6
123     ADH6
123     ADH6
123     ?
56756   CYP4V2
56756   ?
56756   ?
rs98    NUP62
rs98    ?
rs98    NUP62
rs98    NUP62

目标结果 #

Var1    Var2
123     ADH6
123     ADH6
123     ADH6
123     ADH6
56756   CYP4V2
56756   CYP4V2
56756   CYP4V2
rs98    NUP62
rs98    NUP62
rs98    NUP62
rs98    NUP62

已编写的R代码 #

library(dplyr)

first_column <- c("rs1","rs1","rs1","rs2","rs2","rs2","rs2","rs2","rs3","rs3","rs3","rs3","rs3")
second_column <- c("APOA","APOA",NA,"SIGMA",NA,NA,"SIGMA","SIGMA","SIGMA","ALPHA","ALPHA",NA,NA)

 
df <- data.frame(first_column, second_column)
df_noNa <- df[!is.na(df$second_column),]

df1 <- df_noNa %>%
  group_by(second_column) %>%
  count(first_column, second_column) %>%
  slice(which.max(n)) %>%
  select(-n)

你之前的代码存在分组逻辑错误——应该按first_column（即Var1）分组生成映射，而非按second_column分组。以下是修正后的完整实现流程：

步骤说明 #

1. 生成正确的映射表：为每个Var1找到对应的唯一非缺失Var2值，若同一Var1对应多个非缺失Var2，则选择出现次数最多的那个（和你原代码的频次优先逻辑一致）。
2. 填充缺失值：通过匹配映射表，将缺失的Var2值替换为对应Var1的映射值。

完整代码 #

library(dplyr)

# 构造原始数据
first_column <- c("rs1","rs1","rs1","rs2","rs2","rs2","rs2","rs2","rs3","rs3","rs3","rs3","rs3")
second_column <- c("APOA","APOA",NA,"SIGMA",NA,NA,"SIGMA","SIGMA","SIGMA","ALPHA","ALPHA",NA,NA)
df <- data.frame(first_column, second_column)

# 步骤1：生成Var1-Var2的映射表
var_mapping <- df %>%
  filter(!is.na(second_column)) %>%  # 过滤非缺失值
  group_by(first_column) %>%         # 按Var1分组
  count(second_column) %>%           # 统计每个Var1下各Var2的出现次数
  slice(which.max(n)) %>%            # 选择频次最高的Var2
  ungroup() %>%                      # 取消分组
  select(first_column, mapped_var2 = second_column)  # 重命名列方便匹配

# 步骤2：填充缺失值
df_filled <- df %>%
  left_join(var_mapping, by = "first_column") %>%  # 匹配映射值
  mutate(
    # 缺失值用映射值替换，非缺失值保留原值
    second_column = ifelse(is.na(second_column), mapped_var2, second_column)
  ) %>%
  select(-mapped_var2)  # 移除临时映射列

# 查看填充后的结果
print(df_filled)

补充处理（针对?类型缺失值） #

如果你的原始数据中缺失值是?而非NA，需要先将?转换为NA，可在处理前添加以下代码：

df$second_column[df$second_column == "?"] <- NA

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