如何在R的长格式数据集中检测特定组的缺失季节字符串值

我有一个R中的长格式数据集，包含多个分组（dataset列标识），还有一列termSeason_year存储季节字符串（如Fall_2020、Winter_2021）。预期每个分组都包含2013年秋季至2023年冬季的所有季节，需要找出每个分组缺失的具体季节。数据集每条记录对应一个季节，缺失的季节不会以NA形式存在，而是直接没有对应行，因此用is.na()无法检测到。

数据集示例 #

library(tidyverse)

# 创建示例数据集
server <-  rep(c("group1", "group2"), each = 11)
var2 <- c(letters[1:11], letters[1:11])
dataset <- paste(var2, server, sep = "_")

termSeason_year <- rep(c(paste0("Fall_", seq(2013, 2023, 1)),
                         paste0("Winter_", seq(2013, 2023, 1)), 
                         paste0("Summer_", seq(2013, 2023, 1))), each = 10)
    
df <- data.frame(server, var2, dataset, termSeason_year)

# 模拟数据缺失：部分分组删除特定季节记录
df <- df %>% 
    dplyr::mutate(
        termSeason_year = case_when(
            dataset == "a_group1" & termSeason_year == "Fall_2013" ~ NA,
            dataset == "a_group1" & termSeason_year == "Fall_2017" ~ NA,
            dataset == "a_group1" & termSeason_year == "Summer_2021" ~ NA,
            dataset == "a_group1" & termSeason_year == "Fall_2016" ~ NA,
            dataset == "j_group2" & termSeason_year == "Winter_2015" ~ NA,
            dataset == "j_group2" & termSeason_year == "Summer_2017" ~ NA,
            .default = termSeason_year
        )) %>% 
    drop_na(termSeason_year)

# 此时缺失的季节已被删除，is.na()无法检测
sum(is.na(df["termSeason_year"]))

我尝试的代码 #

# 定义预期的所有季节值
expected_termSeason_year_values <- c(paste0("Fall_", seq(2013, 2023, 1)),
                                     paste0("Winter_", seq(2013, 2023, 1)), 
                                     paste0("Summer_", seq(2013, 2023, 1)))

# 创建结果数据框
output_rows <- unique(df$dataset)
output_columns <- expected_termSeason_year_values
data_check_df <- data.frame(matrix(nrow = length(output_rows), ncol = length(output_columns))) 
colnames(data_check_df) <- output_columns
data_check_df$dataset <- output_rows

# 双层循环检查每个季节是否存在
for (term_season in output_columns) {
    print(term_season)
    for (dataset in  output_rows){
        print(dataset)
        unique_termSeason_year <- unique(subset(df, dataset == dataset, termSeason_year)$termSeason_year)
        to_save <- 
            ifelse(term_season %in% setdiff(expected_termSeason_year_values, unique_termSeason_year),
                   "missing",
                   ifelse(term_season %in% unique_termSeason_year,
                          "complete", 
                          "not_implemented"))
        data_check_df[dataset == data_check_df["dataset"], term_season] <- to_save
        print(data_check_df[dataset == data_check_df["dataset"], term_season])
    }
}

data_check_df

解决方案：使用tidyverse高效识别缺失季节 #

无需复杂循环，用tidyverse的函数可以更简洁准确地实现需求，提供两种常用方式：

方式1：生成完整组合并标记状态 #

生成所有预期的分组-季节组合，与原数据左连接后标记每个季节的存在/缺失状态，结果为长格式，便于后续处理：

# 定义所有预期季节
expected_termSeason_year_values <- c(paste0("Fall_", seq(2013, 2023, 1)),
                                     paste0("Winter_", seq(2013, 2023, 1)), 
                                     paste0("Summer_", seq(2013, 2023, 1)))

# 生成完整的分组-季节笛卡尔积
full_season_df <- expand_grid(
    dataset = unique(df$dataset),
    termSeason_year = expected_termSeason_year_values
)

# 左连接原数据，标记缺失/存在
missing_seasons <- full_season_df %>%
    left_join(df %>% distinct(dataset, termSeason_year), by = c("dataset", "termSeason_year")) %>%
    mutate(status = ifelse(is.na(termSeason_year.y), "missing", "complete")) %>%
    select(dataset, termSeason_year = termSeason_year.x, status)

# 查看结果
head(missing_seasons)

方式2：汇总每个分组的缺失季节列表 #

直接按分组汇总缺失的季节，输出紧凑的结果，适合快速查看缺失情况：

# 定义所有预期季节
expected_termSeason_year_values <- c(paste0("Fall_", seq(2013, 2023, 1)),
                                     paste0("Winter_", seq(2013, 2023, 1)), 
                                     paste0("Summer_", seq(2013, 2023, 1)))

# 按分组汇总缺失季节
missing_seasons_summary <- df %>%
    group_by(dataset) %>%
    summarize(
        missing_seasons = list(setdiff(expected_termSeason_year_values, unique(termSeason_year)))
    ) %>%
    unnest(missing_seasons) %>%
    filter(missing_seasons != "") # 过滤无缺失的分组（可选）

# 查看结果
missing_seasons_summary

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