Create variable on whether lagged condition holds in R

Question

I have a dataset that looks like this:

df <- data.frame (
  party=c("A", "B", "C", "A", "B", "C", "A", "B", "C", "D",
           "E", "F", "G", "H", "I","E", "F", "G", "H", "I", "E", "F", "G", "H", "I",
           "J", "K", "L", "J", "K", "L", "J", "K", "L"),
  edate = c(1991, 1991, 1991, 1995, 1995, 1995, 1998, 1998, 1998, 1998,
           2000, 2000, 2000, 2000, 2000, 2005, 2005, 2005, 2005, 2005, 2010, 2010, 2010, 2010, 2010,
           1999, 1999, 1999, 2001, 2001, 2001, 2006, 2006, 2006),
  RRP = c(1,0,0,1,0,0,1,0,0,0,
         0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,
         0,0,0,0,0,0, 0,0,0),
  absseats = c(0, 20, 30, 5, 25, 20, 0, 10, 28, 12,
              100, 50, 50, 25, 0, 120, 30, 75, 0, 15, 90, 60, 70, 5, 15,
              10, 20, 40, 30, 30, 10, 50, 10, 10),
  country=c(11,11,11,11,11,11,11,11,11,11,
             10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10,
             43, 43, 43, 43, 43, 43, 43, 43, 43))

I tried different things like this:

treat <- df%>%
  filter(RRP == "1")%>%
  mutate(treat = ifelse(lag(absseat)>= 1, 1, 0))%>%
  ungroup()

But I never quite get what I'm look for. All help is much appreciated!

Thanks!

What I am trying to get at is to create a new column that = 1 IF and only if the previous date for each country there is 1) one observation with RRP == 1 and 2) that observation has a value of 1 or higher for absseats. So something like this:

treat <- c(NA, NA, NA, 0, 0, 0, 1, 1, 1, 1,
           NA, NA, NA,NA, NA, 0,0,0,0,0, 1,1,1,1,1,
           NA, NA, NA,0,0,0,0,0,0)

ideal.df<- cbind(df,treat)

Here's a dput of the first 100 observations:

dput(df2[1:100,])
structure(list(country = c(11, 11, 11, 11, 11, 11, 11, 11, 11, 
11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 
11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 
11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 
11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 
11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 
11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11), RRP = c(0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), party = structure(c(2L, 
3L, 4L, 6L, 8L, 2L, 3L, 4L, 6L, 8L, 2L, 3L, 4L, 6L, 8L, 2L, 3L, 
4L, 6L, 8L, 2L, 3L, 4L, 6L, 8L, 2L, 3L, 4L, 6L, 8L, 2L, 3L, 4L, 
6L, 8L, 2L, 3L, 4L, 6L, 8L, 2L, 3L, 4L, 6L, 8L, 2L, 3L, 4L, 6L, 
8L, 2L, 3L, 4L, 6L, 8L, 2L, 3L, 4L, 6L, 8L, 2L, 3L, 4L, 6L, 8L, 
2L, 3L, 4L, 5L, 6L, 8L, 1L, 2L, 3L, 4L, 6L, 8L, 1L, 2L, 3L, 4L, 
5L, 6L, 8L, 9L, 1L, 2L, 3L, 4L, 5L, 6L, 8L, 1L, 2L, 3L, 4L, 5L, 
6L, 8L, 1L), .Label = c("11110", "11220", "11320", "11420", "11520", 
"11620", "11710", "11810", "11951", "12110", "12220", "12221", 
"12230", "12320", "12410", "12420", "12520", "12620", "12810", 
"12951", "13001", "13110", "13210", "13220", "13221", "13229", 
"13230", "13320", "13330", "13410", "13420", "13421", "13422", 
"13520", "13620", "13720", "13951", "13952", "13953", "14110", 
"14221", "14222", "14223", "14310", "14320", "14420", "14430", 
"14440", "14520", "14620", "14810", "14820", "14901", "15111", 
"15220", "15320", "15321", "15322", "15323", "15328", "15420", 
"15430", "15440", "15450", "15620", "15621", "15630", "15710", 
"15810", "15951", "15952", "15953", "21111", "21112", "21221", 
"21230", "21320", "21321", "21322", "21330", "21420", "21421", 
"21422", "21423", "21424", "21425", "21426", "21430", "21520", 
"21521", "21522", "21911", "21912", "21913", "21914", "21915", 
"21916", "21917", "22110", "22210", "22220", "22230", "22310", 
"22320", "22321", "22330", "22420", "22430", "22521", "22522", 
"22523", "22524", "22525", "22526", "22527", "22528", "22710", 
"22711", "22720", "22722", "22730", "22951", "22952", "22953", 
"22954", "22955", "23111", "23112", "23113", "23220", "23230", 
"23320", "23420", "23520", "23951", "31021", "31110", "31111", 
"31220", "31230", "31240", "31320", "31421", "31425", "31430", 
"31521", "31522", "31523", "31529", "31621", "31622", "31623", 
"31624", "31625", "31626", "31630", "31631", "31720", "32021", 
"32022", "32031", "32051", "32055", "32061", "32090", "32110", 
"32111", "32210", "32211", "32212", "32213", "32220", "32221", 
"32230", "32310", "32320", "32321", "32322", "32329", "32330", 
"32410", "32420", "32421", "32440", "32450", "32451", "32460", 
"32520", "32521", "32522", "32528", "32529", "32530", "32610", 
"32611", "32629", "32630", "32640", "32710", "32720", "32901", 
"32902", "32903", "32904", "32906", "32951", "32952", "32953", 
"32955", "32956", "33020", "33025", "33091", "33092", "33093", 
"33095", "33096", "33097", "33098", "33099", "33210", "33220", 
"33320", "33420", "33430", "33438", "33439", "33440", "33512", 
"33610", "33611", "33612", "33710", "33901", "33902", "33903", 
"33904", "33905", "33906", "33907", "33908", "33909", "33910", 
"33911", "33912", "33913", "33914", "34020", "34210", "34211", 
"34212", "34213", "34214", "34313", "34314", "34340", "34410", 
"34510", "34511", "34512", "34610", "34710", "34720", "34730", 
"35060", "35110", "35120", "35210", "35211", "35220", "35229", 
"35310", "35311", "35312", "35313", "35315", "35316", "35317", 
"35520", "35710", "35951", "41111", "41112", "41113", "41220", 
"41221", "41222", "41223", "41320", "41420", "41521", "41522", 
"41620", "41711", "41712", "41911", "41912", "41951", "41952", 
"41953", "42110", "42120", "42220", "42320", "42420", "42421", 
"42430", "42520", "42710", "42951", "43020", "43110", "43120", 
"43220", "43320", "43321", "43420", "43520", "43530", "43531", 
"43540", "43710", "43711", "43810", "43811", "43901", "43902", 
"43951", "51110", "51210", "51320", "51330", "51340", "51420", 
"51421", "51430", "51620", "51621", "51901", "51902", "51903", 
"51951", "52320", "52620", "52710", "53021", "53110", "53220", 
"53221", "53230", "53231", "53240", "53250", "53320", "53321", 
"53420", "53520", "53620", "53714", "53810", "53951", "53981", 
"54320", "54620", "55110", "55321", "55322", "55340", "55421", 
"55422", "55423", "55430", "55610", "55711", "55720", "61320", 
"61620", "61621", "61622", "61911", "62110", "62320", "62420", 
"62620", "62621", "62622", "62623", "62901", "62951", "63110", 
"63320", "63321", "63330", "63410", "63620", "63621", "63622", 
"63710", "63810", "63901", "64110", "64320", "64321", "64420", 
"64421", "64422", "64620", "64621", "64901", "64902", "64951", 
"71110", "71111", "71220", "71320", "71321", "71322", "71410", 
"71420", "71430", "71530", "71620", "71621", "71622", "71623", 
"71624", "71625", "71626", "71630", "71640", "71650", "71660", 
"71710", "71951", "71952", "71953", "72091", "72092", "72220", 
"72221", "72222", "72223", "72224", "72225", "72310", "72311", 
"72312", "72320", "72322", "72323", "72324", "72325", "72326", 
"72327", "72328", "72410", "72411", "72412", "72413", "72414", 
"72415", "72420", "72425", "72427", "72428", "72430", "72431", 
"72440", "72450", "72530", "72531", "72532", "72533", "72534", 
"72535", "72610", "72611", "72612", "72613", "72614", "72615", 
"72620", "72621", "72622", "72623", "72624", "72625", "72701", 
"72901", "72903", "72951", "73310", "73330", "74210", "74321", 
"74322", "74323", "74324", "74325", "74326", "74621", "74622", 
"74623", "74624", "74625", "74626", "74627", "74628", "74640", 
"74710", "74711", "74712", "74714", "74715", "74716", "74717", 
"75220", "75320", "75421", "75621", "75622", "75623", "75624", 
"75721", "75722", "75810", "75951", "76041", "76222", "76321", 
"76322", "76420", "76421", "76430", "76521", "76610", "76620", 
"76711", "76712", "76901", "76902", "76903", "76904", "77220", 
"77221", "77420", "77430", "77710", "77951", "78211", "78212", 
"78213", "78214", "78410", "78430", "78710", "78901", "79030", 
"79090", "79221", "79222", "79223", "79321", "79322", "79421", 
"79422", "79423", "79440", "79610", "79721", "79722", "79723", 
"79730", "79901", "79951", "79953", "79954", "79955", "79956", 
"79957", "80051", "80061", "80062", "80071", "80110", "80220", 
"80221", "80228", "80229", "80320", "80330", "80410", "80411", 
"80510", "80610", "80620", "80630", "80640", "80710", "80810", 
"80811", "80812", "80813", "80901", "80902", "80951", "80952", 
"81021", "81022", "81032", "81041", "81042", "81043", "81061", 
"81091", "81111", "81112", "81220", "81221", "81222", "81229", 
"81230", "81410", "81411", "81420", "81430", "81440", "81450", 
"81460", "81711", "81712", "81713", "81810", "81899", "81910", 
"81951", "81952", "81953", "81954", "81955", "81956", "81957", 
"81959", "81960", "81961", "82110", "82220", "82221", "82320", 
"82410", "82412", "82413", "82419", "82420", "82421", "82430", 
"82520", "82521", "82523", "82524", "82529", "82530", "82610", 
"82710", "82720", "82721", "82901", "82951", "82952", "82953", 
"83110", "83220", "83320", "83410", "83411", "83421", "83430", 
"83440", "83610", "83611", "83612", "83709", "83710", "83711", 
"83712", "83713", "83719", "83720", "83810", "83901", "83951", 
"83952", "84041", "84042", "84095", "84110", "84220", "84221", 
"84222", "84223", "84321", "84322", "84323", "84411", "84421", 
"84422", "84423", "84424", "84425", "84426", "84427", "84428", 
"84429", "84430", "84431", "84432", "84440", "84450", "84521", 
"84530", "84710", "84711", "84712", "84713", "84714", "84715", 
"84716", "84720", "84730", "84810", "84951", "84952", "85111", 
"85112", "85210", "85221", "85320", "85421", "85422", "85423", 
"85424", "85429", "85521", "85522", "85523", "85524", "85901", 
"86110", "86220", "86221", "86320", "86340", "86421", "86422", 
"86521", "86522", "86620", "86710", "86810", "86811", "87021", 
"87041", "87042", "87061", "87062", "87071", "87110", "87220", 
"87310", "87311", "87320", "87340", "87410", "87411", "87420", 
"87421", "87422", "87423", "87430", "87431", "87520", "87521", 
"87610", "87611", "87620", "87630", "87640", "87710", "87721", 
"87722", "87723", "87730", "87810", "87811", "87901", "87951", 
"88031", "88041", "88042", "88220", "88320", "88321", "88410", 
"88420", "88421", "88422", "88430", "88440", "88450", "88460", 
"88521", "88522", "88523", "88529", "88620", "88621", "88630", 
"88710", "88810", "88820", "88951", "88952", "89031", "89221", 
"89222", "89223", "89224", "89227", "89228", "89229", "89230", 
"89320", "89321", "89330", "89420", "89430", "89511", "89710", 
"89810", "89910", "89911", "89912", "89916", "89920", "89930", 
"89940", "89951", "89952", "89953", "89959", "90220", "90221", 
"90231", "90320", "90321", "90330", "90410", "90420", "90430", 
"90520", "90610", "90620", "90810", "90951", "90952", "91020", 
"91030", "91060", "91220", "91222", "91223", "91224", "91229", 
"91320", "91321", "91322", "91323", "91324", "91330", "91420", 
"91421", "91430", "91440", "91610", "91710", "91711", "91712", 
"91910", "91920", "91930", "91951", "91952", "91953", "91959", 
"92021", "92022", "92210", "92211", "92212", "92320", "92321", 
"92322", "92410", "92420", "92431", "92432", "92433", "92434", 
"92435", "92436", "92440", "92450", "92520", "92521", "92522", 
"92523", "92530", "92620", "92621", "92622", "92710", "92711", 
"92712", "92713", "92720", "92810", "92811", "92901", "92952", 
"92953", "93001", "93002", "93031", "93041", "93061", "93111", 
"93112", "93221", "93222", "93223", "93320", "93322", "93411", 
"93420", "93430", "93440", "93521", "93523", "93524", "93529", 
"93530", "93540", "93711", "93712", "93713", "93714", "93719", 
"93810", "93951", "93981", "94221", "94222", "94223", "94224", 
"94225", "94310", "94320", "94321", "94421", "94422", "94423", 
"94424", "94425", "94426", "94427", "94620", "94621", "94622", 
"94710", "94711", "94712", "94713", "94901", "94951", "95021", 
"95040", "95041", "95042", "95043", "95044", "95045", "95060", 
"95065", "95066", "95070", "95071", "95091", "95100", "95221", 
"95222", "95223", "95229", "95420", "95421", "95422", "95423", 
"95430", "95431", "95440", "95450", "95451", "95710", "95711", 
"95712", "95720", "95810", "95901", "95902", "95903", "95951", 
"95952", "95954", "95955", "96111", "96210", "96220", "96221", 
"96222", "96420", "96421", "96422", "96423", "96424", "96430", 
"96431", "96440", "96521", "96522", "96523", "96610", "96620", 
"96630", "96710", "96711", "96712", "96720", "96725", "96951", 
"96952", "96953", "96954", "96955", "97020", "97110", "97220", 
"97223", "97230", "97320", "97321", "97322", "97330", "97340", 
"97341", "97421", "97430", "97440", "97450", "97460", "97461", 
"97520", "97521", "97522", "97710", "97810", "97951", "97952", 
"98081", "98111", "98221", "98321", "98322", "98323", "98324", 
"98325", "98421", "98422", "98423", "98424", "98426", "98427", 
"98428", "98429", "98521", "98611", "98612", "98613", "98614", 
"98615", "98616", "98617", "98630", "98711", "98712", "98713", 
"98720", "98810", "98811", "98951", "98952", "113320", "113321", 
"113322", "113420", "113421", "113422", "113430", "113440", "113441", 
"113442", "113620", "113621", "113622", "113630", "113650", "113651", 
"171031", "171032", "171033", "171034", "171035", "171101", "171201", 
"171202", "171203", "171210", "171301", "171302", "171303", "171304", 
"171305", "171306", "171307", "171308", "171309", "171310", "171311", 
"171601", "171611", "181210", "181310", "181410", "181411", "181420", 
"181910"), class = "factor"), edate = structure(c(-9237, -9237, 
-9237, -9237, -9237, -7774, -7774, -7774, -7774, -7774, -6311, 
-6311, -6311, -6311, -6311, -4845, -4845, -4845, -4845, -4845, 
-4232, -4232, -4232, -4232, -4232, -3392, -3392, -3392, -3392, 
-3392, -1929, -1929, -1929, -1929, -1929, -473, -473, -473, -473, 
-473, 262, 262, 262, 262, 262, 1354, 1354, 1354, 1354, 1354, 
2453, 2453, 2453, 2453, 2453, 3545, 3545, 3545, 3545, 3545, 4644, 
4644, 4644, 4644, 4644, 5736, 5736, 5736, 5736, 5736, 5736, 6835, 
6835, 6835, 6835, 6835, 6835, 7927, 7927, 7927, 7927, 7927, 7927, 
7927, 7927, 9026, 9026, 9026, 9026, 9026, 9026, 9026, 10490, 
10490, 10490, 10490, 10490, 10490, 10490, 11945), class = "Date"), 
    absseats = c(15, 115, 26, 39, 35, 8, 112, 57, 23, 30, 5, 
    110, 58, 31, 26, 6, 106, 58, 42, 19, 5, 111, 38, 45, 32, 
    5, 114, 40, 39, 34, 8, 113, 42, 32, 33, 3, 125, 32, 29, 37, 
    17, 163, 58, 41, 71, 19, 156, 34, 51, 90, 17, 152, 39, 55, 
    86, 20, 154, 38, 73, 64, 20, 166, 21, 86, 56, 19, 159, 51, 
    1, 76, 43, 20, 21, 156, 44, 66, 42, 0, 16, 138, 33, 26, 80, 
    31, 25, 18, 22, 161, 26, 15, 80, 27, 16, 43, 131, 17, 42, 
    82, 18, 17)), row.names = c(NA, -100L), class = c("tbl_df", 
"tbl", "data.frame"))

Answer 1

You can do this by first calculating for each country & edate whether the next year should receive treat = 1. Then, drag that over whenever the year changes and use zoo::na.locf() to fill this data down the column.

library(dplyr)
library(zoo)

df2 <- df %>%
  group_by(country, edate) %>%
  mutate(
    treat_next = any(RRP == 1 & absseats > 0, na.rm = T)
  ) %>%
  group_by(country) %>%
  arrange(edate) %>%
  mutate(
    treat = na.locf(case_when(
      lag(edate) < edate ~ as.integer(lag(treat_next)),
      T ~ NA_integer_
    ), na.rm = F)
  ) %>%
  ungroup() %>%
  arrange(country, edate) %>%
  select(-treat_next)

I've arranged the df2 above by country & edate, so the order of rows is different. Data in treat is as expected, though :)