在R中检查DataFrame是否包含国家、年份、场景的必要组合并查找缺失项

核心思路 #

先基于预定义的国家、年份、场景列表生成所有应存在的完整组合，再将其与现有DataFrame对比，筛选出未出现的组合即可。

方法1：Base R原生实现 #

# 预定义的必要列表
country_list = c("AFG", "BWA", "CIV")
year_list = c(2020, 2021, 2022)
scenario_list = c("Const", "Decr", "Incr")

# 生成所有预期的组合
all_combinations <- expand.grid(
  ISO3 = country_list,
  Year = year_list,
  Scenario = scenario_list,
  stringsAsFactors = FALSE
)

# 加载示例数据
ISO3 = c("AFG", "AFG","AFG","AFG", "AFG","AFG","AFG", "AFG","AFG","BWA", "BWA","BWA","BWA", "BWA","BWA","BWA", "BWA","BWA","CIV","CIV","CIV","CIV","CIV","CIV","CIV","CIV","CIV")
Year = c(2020, 2021, 2022, 2020, 2021, 2022, 2020, 2021, 2022, 2020, 2021, 2022, 2020, 2021, 2022, 2020, 2021, 2022, 2020, 2021, 2023, 2020, 2021, 2023, 2020, 2021, 2023)
Scenario = c("Const", "Const","Const","Decr", "Decr","Decr","Incr","Incr","Incr","Const", "Const","Const","Decr", "Decr","Decr","Incr","Incr","Incr","Const", "Const","Const","Decr", "Decr","Decr","Incr","Incr","Incr")
df = data.frame(ISO3, Year, Scenario, stringsAsFactors = FALSE)

# 筛选缺失的组合
missing_combinations <- all_combinations[!duplicated(rbind(all_combinations, df))[seq_len(nrow(all_combinations))], ]

# 打印结果
print(missing_combinations)

方法2：tidyverse工具链实现（可读性更强） #

如果日常使用tidyverse，这种写法更直观：

library(dplyr)
library(tidyr)

# 预定义的必要列表
country_list = c("AFG", "BWA", "CIV")
year_list = c(2020, 2021, 2022)
scenario_list = c("Const", "Decr", "Incr")

# 加载示例数据
ISO3 = c("AFG", "AFG","AFG","AFG", "AFG","AFG","AFG", "AFG","AFG","BWA", "BWA","BWA","BWA", "BWA","BWA","BWA", "BWA","BWA","CIV","CIV","CIV","CIV","CIV","CIV","CIV","CIV","CIV")
Year = c(2020, 2021, 2022, 2020, 2021, 2022, 2020, 2021, 2022, 2020, 2021, 2022, 2020, 2021, 2022, 2020, 2021, 2022, 2020, 2021, 2023, 2020, 2021, 2023, 2020, 2021, 2023)
Scenario = c("Const", "Const","Const","Decr", "Decr","Decr","Incr","Incr","Incr","Const", "Const","Const","Decr", "Decr","Decr","Incr","Incr","Incr","Const", "Const","Const","Decr", "Decr","Decr","Incr","Incr","Incr")
df = data.frame(ISO3, Year, Scenario, stringsAsFactors = FALSE)

# 生成全组合并筛选缺失项
missing_combinations <- expand_grid(
  ISO3 = country_list,
  Year = year_list,
  Scenario = scenario_list
) %>%
  anti_join(df, by = c("ISO3", "Year", "Scenario"))

# 打印结果
print(missing_combinations)

输出结果 #

运行代码后会得到示例中缺失的组合：

ISO3 Year Scenario
1  CIV 2022    Const
2  CIV 2022     Decr
3  CIV 2022     Incr

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