如何在R或Stata中基于观测窗口为每个id生成哑变量？

问题说明 #

针对每个id，生成名为desired_output的哑变量：当某条观测的月份处于包含自身的7个连续观测窗口时（即该观测前至少有3条同id观测，后至少有3条同id观测），哑变量取值为1，否则为0。实际应用中窗口为25个观测（前12条、后12条），示例用7个观测简化数据集。

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), desired_output = c(0, 
0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 
0, 0, 0)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-25L))

Stata语言示例数据集 #

* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 A str6 B str14 C
"id" "month " "desired_output"
"1"  "201701" "0"             
"1"  "201702" "0"             
"1"  "201703" "0"             
"1"  "201704" "1"             
"1"  "201705" "1"             
"1"  "201706" "0"             
"1"  "201707" "0"             
"1"  "201708" "0"             
"2"  "201809" "0"             
"2"  "201810" "0"             
"2"  "201811" "0"             
"2"  "201812" "1"             
"2"  "201901" "0"             
"2"  "201902" "0"             
"2"  "201903" "0"             
"3"  "201705" "0"             
"3"  "201706" "0"             
"3"  "201707" "0"             
"3"  "201708" "1"             
"3"  "201709" "1"             
"3"  "201710" "1"             
"3"  "201711" "1"             
"3"  "201712" "0"             
"3"  "201801" "0"             
"3"  "201809" "0"             
end

解决方案 #

R语言实现 #

使用dplyr分组处理，通过观测在组内的位置判断前后是否满足数量要求：

library(dplyr)

# 加载示例数据
df <- 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), desired_output = c(0, 
0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 
0, 0, 0)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-25L))

# 生成目标变量
df <- df %>%
  group_by(id) %>%
  mutate(
    obs_num = row_number(),
    total_obs = n(),
    # 判断前3后3的条件
    desired_output = as.integer(obs_num > 3 & (total_obs - obs_num) >= 3)
  ) %>%
  ungroup() %>%
  select(-obs_num, -total_obs)

print(df)

若实际使用25个观测窗口（前12后12），只需将条件改为obs_num > 12 & (total_obs - obs_num) >= 12。

Stata语言实现 #

通过bysort分组，结合_n（组内序号）和_N（组内总观测数）判断：

* 加载示例数据
clear
input str2 id str6 month str14 desired_output
"1"  "201701" "0"             
"1"  "201702" "0"             
"1"  "201703" "0"             
"1"  "201704" "1"             
"1"  "201705" "1"             
"1"  "201706" "0"             
"1"  "201707" "0"             
"1"  "201708" "0"             
"2"  "201809" "0"             
"2"  "201810" "0"             
"2"  "201811" "0"             
"2"  "201812" "1"             
"2"  "201901" "0"             
"2"  "201902" "0"             
"2"  "201903" "0"             
"3"  "201705" "0"             
"3"  "201706" "0"             
"3"  "201707" "0"             
"3"  "201708" "1"             
"3"  "201709" "1"             
"3"  "201710" "1"             
"3"  "201711" "1"             
"3"  "201712" "0"             
"3"  "201801" "0"             
"3"  "201809" "0"             
end

* 转换变量为数值型
destring id month desired_output, replace

* 生成目标变量
bysort id: gen desired_output_new = (_n > 3 & (_N - _n) >= 3)
replace desired_output_new = desired_output_new * 1

* 查看结果
list id month desired_output desired_output_new

若使用25个观测窗口，将条件改为(_n > 12 & (_N - _n) >= 12)即可。

内容的提问来源于Stack Exchange，提问作者R novice