R: adding together previous rows in a dataframe

Question

df

   patient.ID Index.admission.  adm_date dish_date bi
1         124            FALSE  2/7/2009  2/8/2009  0
2         124             TRUE  3/5/2009 3/15/2009  1
3         124            FALSE  4/5/2011  4/7/2011  0
4         124            FALSE 3/25/2012 3/27/2012  0
5         124             TRUE  5/5/2012 5/20/2012  1
6         124             TRUE  9/8/2013 9/15/2013  1
7         124            FALSE  1/5/2014 1/15/2014  0
8         233            FALSE  1/1/2010  1/8/2010  0
9         233            FALSE  1/1/2011  1/5/2011  0
10        233             TRUE  2/2/2011 2/25/2011  1
11        233            FALSE 1/25/2012 1/28/2012  0
12        542             TRUE  3/5/2015 3/15/2015  1
13       1243             TRUE  2/5/2009  2/8/2009  1
14       1243             TRUE  2/5/2011 2/19/2011  1

I need to create a new column that adds up bi grouped by the patients.

My data should look like this:

   patient.ID Index.admission.  adm_date dish_date bi  num_index_ad
1         124            FALSE  2/7/2009  2/8/2009  0  0
2         124             TRUE  3/5/2009 3/15/2009  1  1
3         124            FALSE  4/5/2011  4/7/2011  0  1
4         124            FALSE 3/25/2012 3/27/2012  0  1
5         124             TRUE  5/5/2012 5/20/2012  1  2
6         124             TRUE  9/8/2013 9/15/2013  1  3
7         124            FALSE  1/5/2014 1/15/2014  0  3
8         233            FALSE  1/1/2010  1/8/2010  0  0
9         233            FALSE  1/1/2011  1/5/2011  0  0
10        233             TRUE  2/2/2011 2/25/2011  1  1
11        233            FALSE 1/25/2012 1/28/2012  0  1
12        542             TRUE  3/5/2015 3/15/2015  1  1
13       1243             TRUE  2/5/2009  2/8/2009  1  1
14       1243             TRUE  2/5/2011 2/19/2011  1  2

using dplyri have:

df1 <- df %>%
  group_by(patient.ID) %>%
   for (i in df) {
    mutate(num_index_ad = bi[lag(i),] +bi[i,])
    }

This gives error: "Error in .subset2(x, i, exact = exact) : subscript out of bounds"

Thanks in advance:

> dput(df)
structure(list(patient.ID = c(124L, 124L, 124L, 124L, 124L, 124L, 
124L, 233L, 233L, 233L, 233L, 542L, 1243L, 1243L), Index.admission. = c(FALSE, 
TRUE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, TRUE, FALSE, 
TRUE, TRUE, TRUE), adm_date = structure(c(8L, 10L, 12L, 9L, 13L, 
14L, 4L, 1L, 2L, 5L, 3L, 11L, 6L, 7L), .Label = c("1/1/2010", 
"1/1/2011", "1/25/2012", "1/5/2014", "2/2/2011", "2/5/2009", 
"2/5/2011", "2/7/2009", "3/25/2012", "3/5/2009", "3/5/2015", 
"4/5/2011", "5/5/2012", "9/8/2013"), class = "factor"), dish_date = structure(c(7L, 
8L, 11L, 10L, 12L, 13L, 1L, 4L, 3L, 6L, 2L, 9L, 7L, 5L), .Label = c("1/15/2014", 
"1/28/2012", "1/5/2011", "1/8/2010", "2/19/2011", "2/25/2011", 
"2/8/2009", "3/15/2009", "3/15/2015", "3/27/2012", "4/7/2011", 
"5/20/2012", "9/15/2013"), class = "factor"), bi = c(0, 1, 0, 
0, 1, 1, 0, 0, 0, 1, 0, 1, 1, 1)), .Names = c("patient.ID", "Index.admission.", 
"adm_date", "dish_date", "bi"), row.names = c(NA, -14L), class = "data.frame")

Possible duplicate of [Subscript out of bounds - general definition and solution?](http://stackoverflow.com/questions/15031338/subscript-out-of-bounds-general-definition-and-solution) — Brett DeWoody, Jan 27 '16 at 16:43
Try `with(df, ave(bi, patient.ID, FUN = cumsum))` OR `df %>% group_by(patient.ID) %>% mutate(num_index_ad = cumsum(bi))` if you want `dplyr` solution. OR see [here](http://stackoverflow.com/questions/18925600/r-cumulative-sum-by-condition) for a `data.table` one — David Arenburg, Jan 27 '16 at 16:52
@ David, thanks that worked. I am still unsure why my solution did not work... but thanks for your help! — Mitchell, Jan 27 '16 at 16:59
There might be some more errors in subsetting `bi` (it seems you treat `df$bi` as having two dimensions) but when using `for(i in df)` "i" gets the value of each column of "df" in each iteration; see `for(i in df) print(i)`. — alexis_laz, Jan 27 '16 at 19:54

score 3 · Accepted Answer · answered Jan 27 '16 at 17:07

I didn't find a general dupe so here are some additional solutions

df$num_index_ad <- with(df, ave(bi, patient.ID, FUN = cumsum))

OR

library(dplyr)
df %>% 
  group_by(patient.ID) %>%
  mutate(num_index_ad = cumsum(bi))

OR

library(data.table)
setDT(df)[, num_index_ad := cumsum(bi), by = patient.ID]

score 1 · Answer 2 · answered Jan 27 '16 at 16:59

Try this:

for (k in unique(df$patient.ID)){
  df$num_index_ad[df$patient.ID == k] = cumsum(df$bi[df$patient.ID == k])
}

This gives (I did not include the other columns):

> df
   patient.ID Index.admission bi cs
1         124           FALSE  0  0
2         124            TRUE  1  1
3         124           FALSE  0  1
4         124           FALSE  0  1
5         124            TRUE  1  2
6         124            TRUE  1  3
7         124           FALSE  0  3
8         233           FALSE  0  0
9         233           FALSE  0  0
10        233            TRUE  1  1
11        233           FALSE  0  1
12        542            TRUE  1  1
13       1243            TRUE  1  1
14       1243            TRUE  1  2

Maybe someone thought I copied your answer/comment. There is a difference of 7 minutes, but that was the time it took me to copy the df to R and produce the answer. This already happened to me in another answer where the difference was less than a minute. It is not important. — R. Schifini, Jan 27 '16 at 17:44
This solution wasn't present in my comment. (+)1 anyway. I think it's a good solution. — David Arenburg, Jan 27 '16 at 18:49

R: adding together previous rows in a dataframe

2 Answers2