CODEWITHSUNDEEP
rdplyrdata.tabledtplyr
plausibly_exogenous
plausibly_exogenous

Reputation: 512

Updating column values based on previous values (once treated always treated)

I am wondering if there was a much faster way using data.table/dplyr to replace values based on previous values by group.

Suppose my original data table looks like: 

DT_orig <- data.table(name = c("A", "A", "A", "B", "B", "B"), 
                      year = c("2001", "2002", "2003", "2001", "2002", "2003"),
                      treat = c(1,0,0, 0,0,1))

This looks as follows:

 name year treat
1:    A 2001     1
2:    A 2002     0
3:    A 2003     0
4:    B 2001     0
5:    B 2002     0
6:    B 2003     1

Here, for each individual(name) and time period (year), there is a column (treat) which indicates whether or not they have been assigned a treatment.

I am considering an alternative treatment where once an individual is treated, the individual remains treated. Thus, the modified data table should look like:

   name year treat
1:    A 2001     1
2:    A 2002     1
3:    A 2003     1
4:    B 2001     0
5:    B 2002     0
6:    B 2003     1

Notice that for person A, being treated in 2001 implies that they are "treated" in the following years as well.

Because I have a very large data table, I was wondering if there was a very quick way of modifying achieving this.

Upvotes: 1

Views: 69

Answers (2)

s_baldur
s_baldur

Reputation: 33498

I would use cummax() but here is an alternative illustrating data.table's join syntax:

DT_orig[, year := as.integer(year)]
DT_orig[DT_orig[treat == 1], on = .(year >= year, name), treat := 1L]

   name year treat
1:    A 2001     1
2:    A 2002     1
3:    A 2003     1
4:    B 2001     0
5:    B 2002     0
6:    B 2003     1

Upvotes: 0

akrun
akrun

Reputation: 886948

May be we can use cummax (from base R)

DT_orig[, treat := cummax(treat), name]
DT_orig
#   name year treat
#1:    A 2001     1
#2:    A 2002     1
#3:    A 2003     1
#4:    B 2001     0
#5:    B 2002     0
#6:    B 2003     1

Or the same can be done with dplyr

library(dplyr)
DT_orig %>%
    group_by(name) %>%
    mutate(treat = cummax(treat))

Or using base R

DT_orig$treat <- with(DT_orig, ave(treat, name, FUN = cummax))

Upvotes: 4

Related Questions