R: Create a column of averages based upon groups of four rows

Question

>head(df)
      person   week target actual drop_out  organization agency
1:       QJ1    1     30     19    TRUE       BB           LLC
2:       GJ2    1     30     18    FALSE      BB           LLC
3:       LJ3    1     30     22    TRUE       CC           BBR
4:       MJ4    1     30     24    FALSE      CC           BBR
5:       PJ5    1     35     55    FALSE      AA           FUN
6:       EJ6    1     35     50    FALSE      AA           FUN

There are around ~30 weeks in the dataset with a repeating Person ID each week.

I want to look at each person's values FOUR weeks at a time (so week 1-4, 5-9, 10-13, and so on). For each of these chunks, I want to add up all the "actual" columns and divide it by the sum of the "target" columns. Then we could put that value in a column called "monthly percent."

As per Shape's recommendation I've created a month column like so

fullReshapedDT$month <- with(fullReshapedDT, ceiling(week/4))

Trying to figure out how to iterate over the month column and calculate averages now. Trying something like this, but it obviously doesn't work:

fullReshapedDT[,.(monthly_attendance = actual/target,by=.(person_id, month)]

Where exactly are you getting stuck? It would help if you provided a [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) (perhaps with fake data) to better illustrate the problem. Give the desired output for the sample input so possible solutions can be tested. If you've made an attempt to do this yourself, include your code and describe where exactly you are getting stuck. — MrFlick, Jul 06 '16 at 20:18
Create a new column using something along the lines of `ceiling(week/4)*4` and then group over that — Shape, Jul 06 '16 at 20:20

score 1 · Accepted Answer · answered Jul 06 '16 at 20:33

1

Have you tried creating a group variable? It will allow you to group operations by the four-week period:

setDT(df1)[,grps:=ceiling(week/4)      #Create 4-week groups 
           ][,sum(actual)/sum(target), .(person, grps)  #grouped operations
             ][,grps:=NULL][]     #Remove unnecessary columns
#     person        V1
# 1:     QJ1 1.1076923
# 2:     GJ2 1.1128205
# 3:     LJ3 0.9948718
# 4:     MJ4 0.6333333
# 5:     PJ5 1.2410256
# 6:     EJ6 1.0263158
# 7:     QJ1 1.2108108
# 8:     GJ2 0.6378378
# 9:     LJ3 0.9891892
# 10:    MJ4 0.8564103
# 11:    PJ5 1.1729730
# 12:    EJ6 0.8666667

answered Jul 06 '16 at 20:33

Pierre L

28,203
6
47
69

Wow. This is incredible. I wasn't familiar with this syntax or grouped operations. Do you know of any good resources to learn about this? – Parseltongue Jul 06 '16 at 20:39
1

Here are some vignettes https://github.com/Rdatatable/data.table/wiki/Getting-started – Pierre L Jul 06 '16 at 20:41
Thanks, Pierre. It looks like you're chaining operations to the data.table using successive [ ]... how does that work? – Parseltongue Jul 06 '16 at 21:08
1

Chaining is in section C of the first vignette – Pierre L Jul 06 '16 at 21:10

R: Create a column of averages based upon groups of four rows

1 Answers1