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

Question

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.

akrun · Accepted Answer · 2020-01-19T20:27:03.623

4

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))

edited Jan 19 '20 at 20:27

answered Jan 19 '20 at 20:20

akrun

874,273
37
540
662

1

This is exactly what I was looking for, very straightforward! Thanks! I will accept your answer when I am allowed to. – plausibly_exogenous Jan 19 '20 at 20:24
Who is downvoting the solution. I came with the `cummax` first here – akrun Jan 19 '20 at 20:30
@sindri_baldur The example shows the data is ordered – akrun Jan 20 '20 at 16:17

score 0 · Answer 2 · answered Jan 20 '20 at 10:09

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

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

2 Answers2