基于Client_ID与Year的数据集缺失值处理及重复行清理方案咨询

数据集定义 #

df1 <- tibble::tribble(
  ~Client, ~Client_ID, ~Year, ~NPurchases, ~Age, ~Sex, ~Hobby,
  "Alex Smith",   88888,  2022,   NA, 30, "Male", "",
  "Alex Smith",   88888,  2022,   45, 30, "Male", "Stamps",
  "Alex Smith",   88888,  2021,   32, 29, "Male", "Stamps",
  "Eugene Hugs",  788272, 2022,   2,  19, "Male", "Cats"
)

注：处理前先统一缺失值定义——将空字符串""转为NA，保证缺失判断一致性。

场景1：移除缺失值数量较多的行 #

核心逻辑：按Client_ID和Year分组，保留每组内缺失值最少的行（若多行缺失数相同则全部保留）。

Base R 实现 #

# 统一空字符串为NA
df1_clean <- df1
df1_clean[df1_clean == ""] <- NA

# 计算每行缺失值数量
df1_clean$na_count <- rowSums(is.na(df1_clean))

# 分组筛选缺失最少的行
result1 <- do.call(rbind, by(df1_clean, list(df1_clean$Client_ID, df1_clean$Year), function(x) {
  x[x$na_count == min(x$na_count), ]
}))

# 清理临时列
result1 <- result1[, !names(result1) %in% "na_count"]

data.table 实现 #

library(data.table)
setDT(df1)

# 空字符串转NA
df1[Hobby == "", Hobby := NA]

# 分组计算缺失数并筛选
result1_dt <- df1[, na_count := rowSums(is.na(.SD)), .SDcols = names(df1)][
  , .SD[na_count == min(na_count)], by = .(Client_ID, Year)
][, na_count := NULL]

场景2：识别差异重复行，移除数据量更少的行并记录删除信息 #

核心逻辑：分组后对比行的有效数据量（非缺失列数），保留数据量最多的行，同时单独存储被删除的行用于核查。

Base R 实现 #

# 统一空字符串为NA
df1_clean <- df1
df1_clean[df1_clean == ""] <- NA

# 计算每行有效数据量
df1_clean$valid_count <- rowSums(!is.na(df1_clean))

# 初始化删除行存储容器
deleted_rows <- list()

# 分组处理：保留有效数据最多的行，记录删除行
result2 <- do.call(rbind, by(df1_clean, list(df1_clean$Client_ID, df1_clean$Year), function(x) {
  if (nrow(x) > 1) {
    keep_idx <- which.max(x$valid_count)
    deleted_rows[[paste(x$Client_ID[1], x$Year[1], sep = "_")]] <<- x[-keep_idx, ]
    return(x[keep_idx, ])
  } else {
    return(x)
  }
}))

# 整理删除行数据框
deleted_df <- do.call(rbind, deleted_rows)

# 清理临时列
result2 <- result2[, !names(result2) %in% "valid_count"]
deleted_df <- deleted_df[, !names(deleted_df) %in% "valid_count"]

data.table 实现 #

library(data.table)
setDT(df1)

# 空字符串转NA
df1[Hobby == "", Hobby := NA]

# 计算每行有效数据量
df1[, valid_count := rowSums(!is.na(.SD)), .SDcols = names(df1)]

# 分组排名：有效数据量越高排名越靠前
df1[, rank := rank(-valid_count, ties.method = "first"), by = .(Client_ID, Year)]

# 保留排名第一的行
result2_dt <- df1[rank == 1, ][, c("valid_count", "rank") := NULL]

# 获取所有被删除的行
deleted_df_dt <- df1[rank > 1, ][, c("valid_count", "rank") := NULL]

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