检查R数据集分组内类别完整性并补全缺失记录

需求说明 #

针对数据集中var2kreuz的每个分组与每个cohort组合，检查var2use是否包含negative、neutral、positive三个取值；若某组合缺少任一取值，需添加对应行，缺失行的n值设为0。例如：

• AAA分组 + 2013/14 cohort需补充var2use = "negative"、n=0的行
• BBB分组 + 2012/13 cohort需补充var2use = "positive"、n=0的行

原始数据集 #

df <-
data.frame (
var2kreuz  = c(rep(c("AAA"), 8), rep(c("BBB"), 8)),
cohort  = c(
  rep(c("2012/13"), 3),
  rep(c("2013/14"), 2),
  rep(c("2014/15"), 3),
  rep(c("2012/13"), 2),
  rep(c("2013/14"), 3),
  rep(c("2014/15"), 3)
),
var2use  = c(
  "negative",
  "neutral",
  "positive",
  "neutral",
  "positive",
  "negative",
  "neutral",
  "positive",
  "negative",
  "neutral",
  "negative",
  "neutral",
  "positive",
  "negative",
  "neutral",
  "positive"
),
n = round(runif(8, min = 0, max = 101))
)

实现代码（tidyverse方案） #

# 加载tidyverse包
library(tidyverse)

# 定义var2use的所有可能取值
all_var2use <- c("negative", "neutral", "positive")

# 生成所有分组-时间-取值的全组合框架
full_grid <- expand.grid(
  var2kreuz = unique(df$var2kreuz),
  cohort = unique(df$cohort),
  var2use = all_var2use,
  stringsAsFactors = FALSE
)

# 左连接原数据，将缺失的n值替换为0
df_complete <- full_grid %>%
  left_join(df, by = c("var2kreuz", "cohort", "var2use")) %>%
  mutate(n = replace_na(n, 0))

验证结果 #

执行上述代码后，可通过以下命令查看目标组合的补全情况：

# 查看AAA-2013/14的所有行
df_complete %>% filter(var2kreuz == "AAA", cohort == "2013/14")

# 查看BBB-2012/13的所有行
df_complete %>% filter(var2kreuz == "BBB", cohort == "2012/13")

会看到缺失的取值已被补充，且n值为0。

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