在R语言中按条件生成唯一组合变量并补全缺失数据

先加载原数据：

df <- structure(list(model = c("A1", "A1", "B4", "B4", "B4", "A4", 
"A4", "A4", "G4", "G4"), category = c("X", "Y", "X", "Y", "Z", 
"X", "Y", "Z", "X", "Z"), sale = c(194L, 0L, 59L, 29L, 0L, 176L, 
88L, 0L, 87L, 44L)), class = "data.frame", row.names = c(NA, 
-10L))

用tidyverse工具链实现需求，代码如下：

library(tidyverse)

# 生成model与category的所有可能组合，匹配原数据的sale值
full_df <- df %>%
  expand(model, category = c("X", "Y", "Z")) %>%
  left_join(df, by = c("model", "category"))

# 按规则填充缺失的sale数据
full_df <- full_df %>%
  group_by(model) %>%
  mutate(
    # 缺失Z类时，取同model的Y类sale值
    sale = ifelse(category == "Z" & is.na(sale), first(sale[category == "Y"]), sale),
    # 缺失Y类时，取同model的X类sale值
    sale = ifelse(category == "Y" & is.na(sale), first(sale[category == "X"]), sale)
  ) %>%
  ungroup() %>%
  arrange(model, category)

# 输出结果
print(full_df)

代码说明： #

• expand(model, category = c("X", "Y", "Z"))：生成每个型号与三类品类的全量组合，确保没有遗漏
• left_join：将原数据的销量匹配到全组合中，缺失的组合销量会显示为NA
• 分组处理每个型号的缺失值：
  • 若某型号缺Z类数据，直接复用该型号Y类的销量
  • 若某型号缺Y类数据，直接复用该型号X类的销量
• 最后按型号和品类排序，得到与预期一致的结果

运行后输出结果：

model category sale
1     A1        X  194
2     A1        Y    0
3     A1        Z    0
4     A4        X  176
5     A4        Y   88
6     A4        Z    0
7     B4        X   59
8     B4        Y   29
9     B4        Z    0
10    G4        X   87
11    G4        Y   87
12    G4        Z   44

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