如何在R中筛选同时存在于2021Q4与2022Q1的特定NAICS账户组合？

需要从样本数据中筛选满足以下条件的记录：

• 属于2021年第四季度且NAICS编码为454的acct-run组合；
• 这些acct-run组合同时存在于2021年第四季度和2022年第一季度。

纠结是否要通过pivot_wider转换为宽表，将季度值转为列后过滤2022Q1中为NA的记录。

样本数据 #

year <- c(2021,2021,2021,2021,2021,2021,2021,2022,2022,2022,2022,2022,2022,2022)
qtr <- c(4,4,4,4,4,4,4,1,1,1,1,1,1,1)
acct <- c("001234","001245","001256","001267","001278","001314","001314","001234","001245","001256","001200","001201","001314","001314")
run <- c(1,1,1,1,1,1,2,1,1,1,1,1,1,2)
naics <- c("454","454","454","451","451","451","451","441","442","441","451","451","451","451")
employment <- c(120,130,110,500,501,502,503,105,125,109,498,499,500,506)

test <- data.frame(year,qtr,acct,run,naics,employment)

修正后期望结果 #

（注：原示例中最后两行year值存在笔误，已修正为2022）

acct run employment year qtr
1 001234   1        120 2021   4
2 001245   1        130 2021   4
3 001256   1        110 2021   4
4 001314   1        502 2021   4
5 001314   2        503 2021   4
6 001234   1        105 2022   1
7 001245   1        125 2022   1
8 001256   1        109 2022   1
9 001314   1        500 2022   1
10 001314   2        506 2022   1

解决方案 #

方法一：无需转宽表，直接筛选目标组合 #

这种方法无需转换表结构，步骤简洁高效：

library(dplyr)

# 提取2021Q4中NAICS为454的所有acct-run唯一组合
target_pairs <- test %>%
  filter(year == 2021, qtr == 4, naics == "454") %>%
  select(acct, run) %>%
  distinct()

# 筛选这些组合在2021Q4和2022Q1的所有记录
final_result <- test %>%
  inner_join(target_pairs, by = c("acct", "run")) %>%
  filter((year == 2021 & qtr == 4) | (year == 2022 & qtr == 1))

print(final_result)

方法二：转宽表后过滤 #

如果坚持使用宽表方式，也可以实现：

library(tidyr)
library(dplyr)

# 将数据转为宽表，按季度拆分employment列
wide_data <- test %>%
  mutate(quarter = paste0(year, "Q", qtr)) %>%
  pivot_wider(
    id_cols = c(acct, run, naics),
    names_from = quarter,
    values_from = employment
  )

# 筛选符合条件的acct-run组合
valid_pairs <- wide_data %>%
  filter(naics == "454", !is.na(`2021Q4`), !is.na(`2022Q1`)) %>%
  select(acct, run)

# 转回长表并提取目标记录
final_result_wide <- test %>%
  inner_join(valid_pairs, by = c("acct", "run")) %>%
  filter((year == 2021 & qtr == 4) | (year == 2022 & qtr == 1))

print(final_result_wide)

两种方法都能得到符合要求的结果，方法一无需额外表结构转换，流程更简洁，推荐优先使用。

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