在R与Stata中按ID保留dummy=1前后3条观测值的实现方法

问题描述 #

针对每个id分组，仅保留dummy=1的观测值及其前3条、后3条记录，删除窗口外的所有观测。

R语言实现 #

示例数据集 #

structure(list(id = c(1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 
2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3), month = c(201701, 201702, 
201703, 201704, 201705, 201706, 201707, 201708, 201809, 201810, 
201811, 201812, 201901, 201902, 201903, 201705, 201706, 201707, 
201708, 201709, 201710, 201711, 201712, 201801, 201809), dummy = c(0, 
0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 
0, 0, 0)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-25L))

解决方案代码 #

使用dplyr分组处理，定位dummy=1的行号后筛选窗口内记录：

library(dplyr)

# 加载数据集（替换为你的数据对象）
df <- structure(...)

df_filtered <- df %>%
  group_by(id) %>%
  mutate(row_num = row_number()) %>%
  # 标记dummy=1的行号
  mutate(target_row = ifelse(dummy == 1, row_num, NA)) %>%
  # 统一分组内的目标行号（假设每个id仅一个dummy=1）
  mutate(target_row = max(target_row, na.rm = TRUE)) %>%
  # 保留目标行前后3条及本身
  filter(row_num >= target_row - 3 & row_num <= target_row + 3) %>%
  select(-row_num, -target_row) %>%
  ungroup()

# 查看结果
print(df_filtered, n = 22)

预期输出 #

> print(df_filtered, n = 22)
# A tibble: 21 × 3
      id  month dummy
   <dbl>  <dbl> <dbl>
 1     1 201701     0
 2     1 201702     0
 3     1 201703     0
 4     1 201704     1
 5     1 201705     0
 6     1 201706     0
 7     1 201707     0
 8     2 201809     0
 9     2 201810     0
10     2 201811     0
11     2 201812     1
12     2 201901     0
13     2 201902     0
14     2 201903     0
15     3 201707     0
16     3 201708     0
17     3 201709     0
18     3 201710     1
19     3 201711     0
20     3 201712     0
21     3 201801     0

Stata实现（无外部程序） #

示例数据集 #

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id long month byte dummy float mdate
1 201701 0 684
1 201702 0 685
1 201703 0 686
1 201704 1 687
1 201705 0 688
1 201706 0 689
1 201707 0 690
1 201708 0 691
2 201809 0 704
2 201810 0 705
2 201811 0 706
2 201812 1 707
2 201901 0 708
2 201902 0 709
2 201903 0 710
3 201705 0 688
3 201706 0 689
3 201707 0 690
3 201708 0 691
3 201709 0 692
3 201710 1 693
3 201711 0 694
3 201712 0 695
3 201801 0 696
3 201809 0 704
end

解决方案代码 #

通过分组生成行号，定位目标行后筛选窗口内记录：

* 按id和月份排序，生成组内行号
sort id month
by id: gen row_num = _n

* 标记dummy=1的行号，统一分组内的目标行
by id: gen target_row = row_num if dummy == 1
by id: replace target_row = target_row[_N] if missing(target_row)

* 保留目标行前后3条及本身
keep if row_num >= target_row - 3 & row_num <= target_row + 3

* 删除辅助变量
drop row_num target_row

* 查看结果
list, clean noobs

预期输出 #

id   month   dummy   mdate
 1   201701       0     684
 1   201702       0     685
 1   201703       0     686
 1   201704       1     687
 1   201705       0     688
 1   201706       0     689
 1   201707       0     690
 2   201809       0     704
 2   201810       0     705
 2   201811       0     706
 2   201812       1     707
 2   201901       0     708
 2   201902       0     709
 2   201903       0     710
 3   201707       0     690
 3   201708       0     691
 3   201709       0     692
 3   201710       1     693
 3   201711       0     694
 3   201712       0     695
 3   201801       0     696

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