Creating a conditional dummy variable column in R

January 18, 2022

I’m working with a cross-country panel dataset, one of my variables (cc_dummy) takes the value of 1 & 0 (there are also missing values indicated by NAs). I want to create a new column such that if cc_dummy takes the value 1 for three consecutive years for each country, I want only the first year of each three-year window to take the value 1 whereas the other years take the value 0. A snapshot of my data is given below. Any help would be appreciated. If possible, I would like to create the column using dplyr.

structure(list(country = c("Argentina", "Argentina", "Argentina", 
"Argentina", "Argentina", "Argentina", "Argentina", "Argentina", 
"Argentina", "Argentina", "Argentina", "Argentina", "Argentina", 
"Argentina", "Argentina", "Argentina", "Argentina", "Argentina", 
"Argentina", "Argentina", "Argentina", "Argentina", "Argentina", 
"Argentina", "Argentina", "Argentina", "Argentina", "Argentina", 
"Argentina", "Argentina", "Argentina", "Argentina", "Argentina", 
"Argentina", "Argentina", "Argentina", "Argentina", "Argentina", 
"Argentina", "Argentina", "Argentina", "Argentina", "Argentina", 
"Argentina", "Argentina", "Argentina", "Brazil", "Brazil", "Brazil", 
"Brazil", "Brazil", "Brazil", "Brazil", "Brazil", "Brazil", "Brazil", 
"Brazil", "Brazil", "Brazil", "Brazil", "Brazil", "Brazil", "Brazil", 
"Brazil", "Brazil", "Brazil", "Brazil", "Brazil", "Brazil", "Brazil", 
"Brazil", "Brazil", "Brazil", "Brazil", "Brazil", "Brazil", "Brazil", 
"Brazil", "Brazil", "Brazil", "Brazil", "Brazil", "Brazil", "Brazil", 
"Brazil", "Brazil", "Brazil", "Brazil", "Brazil", "Brazil", "Brazil", 
"Brazil", "Brazil"), year = c(1975, 1976, 1977, 1978, 1979, 1980, 
1981, 1982, 1983, 1984, 1985, 1986, 1987, 1988, 1989, 1990, 1991, 
1992, 1993, 1994, 1995, 1996, 1997, 1998, 1999, 2000, 2001, 2002, 
2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 
2014, 2015, 2016, 2017, 2018, 2019, 2020, 1975, 1976, 1977, 1978, 
1979, 1980, 1981, 1982, 1983, 1984, 1985, 1986, 1987, 1988, 1989, 
1990, 1991, 1992, 1993, 1994, 1995, 1996, 1997, 1998, 1999, 2000, 
2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 
2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021), 
    cc_dummy = c(NA, NA, 0, 0, 0, 0, 1, 1, 0, 1, 0, 0, 1, 1, 
    1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, NA, NA, 0, 0, 1, 0, 
    1, 0, 1, 0, 0, 0, 1, 1, 1, 1, 0, 1, 1, 0, 0, 0, 0, 0, 1, 
    0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 
    0, 0, 0)), row.names = c(NA, -93L), class = c("tbl_df", "tbl", 
"data.frame"))

>Solution :

Here’s a way to do it using data.table::rleid and dplyr grammar:

library(dplyr)
df %>% 
  group_by(country, m = data.table::rleid(cc_dummy)) %>% 
  mutate(newcol = ifelse(n() >= 3 & cumsum(cc_dummy) == 1, 1, 0))

# A tibble: 93 x 5
# Groups:   country, m [32]
   country    year cc_dummy     m newcol
   <chr>     <dbl>    <dbl> <int>  <dbl>
 1 Argentina  1975       NA     1      0
 2 Argentina  1976       NA     1      0
 3 Argentina  1977        0     2      0
 4 Argentina  1978        0     2      0
 5 Argentina  1979        0     2      0
 6 Argentina  1980        0     2      0
 7 Argentina  1981        1     3      0
 8 Argentina  1982        1     3      0
 9 Argentina  1983        0     4      0
10 Argentina  1984        1     5      0
11 Argentina  1985        0     6      0
12 Argentina  1986        0     6      0
13 Argentina  1987        1     7      1
14 Argentina  1988        1     7      0
15 Argentina  1989        1     7      0
16 Argentina  1990        0     8      0
17 Argentina  1991        0     8      0
18 Argentina  1992        0     8      0
19 Argentina  1993        0     8      0
20 Argentina  1994        0     8      0
21 Argentina  1995        0     8      0
22 Argentina  1996        0     8      0
23 Argentina  1997        0     8      0
24 Argentina  1998        0     8      0
25 Argentina  1999        0     8      0
26 Argentina  2000        0     8      0
27 Argentina  2001        0     8      0
28 Argentina  2002        1     9      0