R时间序列数据框按id分组匹配变量生成dummy哑变量的方法

我手中有一个存储女性生育史的大型面板数据框，每位女性对应18行数据，用于重构其过去18年的生育记录。数据框的dput输出如下：

df <- structure(list(id = c(32, 32, 32, 32, 32, 32, 32, 12668031110,12668031110, 12668031110), survey_date = structure(c(17167, 17167,17167, 17167, 17167, 17167, 17167, 15034, 15034, 15034), class = "Date"),survey_year = c(2017, 2017, 2017, 2017, 2017, 2017, 2017,2011, 2011, 2011), mom_dob = c(1991, 1991, 1991, 1991, 1991,1991, 1991, 1987, 1987, 1987), date = structure(c(10592,10957, 11323, 11688, 12053, 12418, 12784, 14304, 14669, 15034), class = "Date"), date_year = c(1999, 2000, 2001, 2002,2003, 2004, 2005, 2009, 2010, 2011), mom_age = c(7, 8, 9,10, 11, 12, 13, 21, 22, 23), newborn = c(0, 0, 0, 0, 0, 0,0, 0, 0, 0), stock = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1), family500_year = c(1,1, 1, 1, 1, 1, 1, 0, 0, 0), nchild1 = c(2015, 2015, 2015,2015, 2015, 2015, 2015, NA, NA, NA), nchild2 = c(NA, NA,NA, NA, NA, NA, NA, 2010, 2010, 2010), nchild3 = c(NA_real_,NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,NA_real_, NA_real_, NA_real_), nchild4 = c(NA_real_, NA_real_,NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,NA_real_, NA_real_), nchild5 = c(NA_real_, NA_real_, NA_real_,NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,NA_real_), nchild6 = c(NA_real_, NA_real_, NA_real_, NA_real_,NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), nchild7 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), nchild8 = c(NA,NA, NA, NA, NA, NA, NA, NA, NA, NA), nchild9 = c(NA, NA,NA, NA, NA, NA, NA, NA, NA, NA), nchild10 = c(NA, NA, NA,NA, NA, NA, NA, NA, NA, NA), educcat = c(2, 2, 2, 2, 2, 2,2, 3, 3, 3), educcat_college = c(0, 0, 0, 0, 0, 0, 0, 1,1, 1), hh_income_net = c(3410, 3410, 3410, 3410, 3410, 3410,3410, 7978.7001953125, 7978.7001953125, 7978.7001953125),hh_income_annual_usd = c(10912, 10912, 10912, 10912, 10912,10912, 10912, 25531.840625, 25531.840625, 25531.840625),hh_income_annual_log = c(9.29761838008324, 9.29761838008324,9.29761838008324, 9.29761838008324, 9.29761838008324, 9.29761838008324,9.29761838008324, 10.1476816041898, 10.1476816041898, 10.1476816041898), marital_stat = c(10, 10, 10, 10, 10, 10, 10, 20, 20, 20), maritalcat = c(0, 0, 0, 0, 0, 0, 0, 1, 1, 1), rural = c(1,1, 1, 1, 1, 1, 1, 0, 0, 0), age_sq = c(625, 625, 625, 625,625, 625, 625, 529, 529, 529), emp_stat = c(3, 3, 3, 3, 3,3, 3, 6, 6, 6), occupation = c("98", "98", "98", "98", "98","98", "98", "48", "48", "48"), disability_stat = c(2, 2,2, 2, 2, 2, 2, 2, 2, 2), weight = c(1039, 1039, 1039, 1039,1039, 1039, 1039, 1457, 1457, 1457), region = c(2, 2, 2,2, 2, 2, 2, 12, 12, 12), birth_country = c(1, 1, 1, 1, 1,1, 1, 1, 1, 1), birth_citizenship = c(1, 1, 1, 1, 1, 1, 1,1, 1, 1)), row.names = c(NA, -10L), class = c("tbl_df", "tbl","data.frame"))

处理需求 #

• 生成newborn哑变量：当行的date_year值与nchild1到nchild10中任意非空值匹配时，newborn赋值为1，其余为0
• 生成stock累计子女数变量：按id分组对newborn做累计求和，第一个孩子出生年stock变为1，后续随新增子女递增并保持

数据示例 #

现有数据 #

## example df of what I have now: 
id   date_year  newborn   stock   nchild1  nchild2  nchchild3   
62       1996      0        2      2004      2005       NA     
62       1997      0        2      2004      2005       NA     
62       1998      0        2      2004      2005       NA      
62       1999      0        2      2004      2005       NA     
62       2000      0        2      2004      2005       NA     
62       2001      0        2      2004      2005       NA     
62       2002      0        2      2004      2005       NA      
62       2003      0        2      2004      2005       NA       
62       2004      0        2      2004      2005       NA     
62       2005      0        2      2004      2005       NA     
62       2006      0        2      2004      2005       NA     
62       2007      0        2      2004      2005       NA     
62       2008      0        2      2004      2005       NA     
62       2009      0        2      2004      2005       NA     
62       2010      0        2      2004      2005       NA      
62       2011      0        2      2004      2005       NA
62       2012      0        2      2004      2005       NA     
62       2013      0        2      2004      2005       NA
62       2014      0        2      2004      2005       NA

目标数据 #

## desired df
id   date_year  newborn   stock   nchild1  nchild2  nchchild3   
62       1996      0        0      2004      2005       NA     
62       1997      0        0      2004      2005       NA     
62       1998      0        0      2004      2005       NA      
62       1999      0        0      2004      2005       NA     
62       2000      0        0      2004      2005       NA     
62       2001      0        0      2004      2005       NA     
62       2002      0        0      2004      2005       NA      
62       2003      0        0      2004      2005       NA       
62       2004      1        1      2004      2005       NA     
62       2005      1        2      2004      2005       NA     
62       2006      0        2      2004      2005       NA     
62       2007      0        2      2004      2005       NA     
62       2008      0        2      2004      2005       NA     
62       2009      0        2      2004      2005       NA     
62       2010      0        2      2004      2005       NA      
62       2011      0        2      2004      2005       NA
62       2012      0        2      2004      2005       NA     
62       2013      0        2      2004      2005       NA
62       2014      0        2      2004      2005       NA 

原有尝试的问题 #

最初仅针对单个nchild列做判断，无法覆盖所有子女出生年份的匹配逻辑，错误代码如下：

df$newborn <- ifelse(df$newborn == 1, df$nchild1==df$date_year, df$newborn)

可行解决方案 #

使用dplyr的按行处理和分组累计求和逻辑即可实现需求，代码如下：

library(dplyr)
df <- df %>% 
  rowwise() %>%
  mutate(newborn = ifelse(date_year %in% c(nchild1, nchild2, nchild3, nchild4, nchild5, nchild6, nchild7, nchild8, nchild9, nchild10), 1, 0)) %>%
  group_by(id) %>%
  mutate(stock = cumsum(newborn))

逻辑说明 #

1. rowwise()实现按行运算，逐行判断当前记录的年份是否属于该女性的所有子女出生年份集合
2. %in%运算符直接匹配date_year与10个nchild列的取值，自动忽略NA值
3. 按id分组后调用cumsum()对newborn做累计求和，得到逐年累计的子女数

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