在R中识别全年份分组均出现的关联地区行的方法

示例数据集 #

先构造符合喜剧演出场景的测试数据，包含多值地区拆分的情况：

set.seed(123)
comedy_shows <- data.frame(
  year = rep(c(2021, 2022, 2023), each = 5),
  original_district = sample(c("朝阳区", "海淀区", "东城区", "西城区", "丰台区"), 15, replace = TRUE),
  current_district = sample(c("朝阳区", "海淀区;昌平区", "东城区", "西城区", "丰台区;亦庄新区"), 15, replace = TRUE),
  show_name = paste0("喜剧专场_", 1:15)
)

核心代码实现 #

依赖tidyverse工具链，步骤清晰可复用：

library(tidyverse)

# 1. 拆解多值地区，统一整理所有可能的地区标识
district_summary <- comedy_shows %>%
  # 拆分current_district的分号多值，展开为单行
  mutate(current_district = str_split(current_district, ";")) %>%
  unnest(current_district) %>%
  # 清理地区名前后空格，避免匹配误差
  mutate(across(c(original_district, current_district), str_trim)) %>%
  # 将original和current合并为单列，统一统计年份覆盖情况
  pivot_longer(
    cols = c(original_district, current_district),
    values_to = "district",
    names_to = NULL
  ) %>%
  distinct(year, district) # 去重，同一年份同一地区只留一条记录

# 2. 筛选出所有年份都出现的地区
total_years <- length(unique(comedy_shows$year))
target_districts <- district_summary %>%
  group_by(district) %>%
  summarise(covered_year_count = n_distinct(year)) %>%
  filter(covered_year_count == total_years) %>%
  pull(district)

# 3. 匹配回原数据，保留包含目标地区的行
final_result <- comedy_shows %>%
  mutate(
    across(c(original_district, current_district), str_trim),
    # 检查当前行是否命中目标地区：original直接匹配，或current拆分后有匹配项
    is_target = case_when(
      original_district %in% target_districts ~ TRUE,
      map_lgl(str_split(current_district, ";"), ~ any(str_trim(.x) %in% target_districts)) ~ TRUE,
      TRUE ~ FALSE
    )
  ) %>%
  filter(is_target) %>%
  select(-is_target)

# 查看结果
print(final_result)

代码关键点 #

• 多值处理：用str_split+unnest拆分分号分隔的地区，确保每个地区单独参与统计
• 统一匹配维度：将original_district和current_district合并为同一列，避免遗漏两种地区标识的匹配可能
• 精准筛选：通过统计地区覆盖的年份数，筛选出在所有年份都出现的地区，再反向匹配原数据行

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