how to aggregate this data in R

Question

Hello,

I have a data frame in R with the following structure.

> testData
            date exch.code comm.code     oi
1     1997-12-30       CBT         1 468710
2     1997-12-23       CBT         1 457165
3     1997-12-19       CBT         1 461520
4     1997-12-16       CBT         1 444190
5     1997-12-09       CBT         1 446190
6     1997-12-02       CBT         1 443085
....
    77827 2004-10-26      NYME       967  10038
    77828 2004-10-19      NYME       967   9910
    77829 2004-10-12      NYME       967  10195
    77830 2004-09-28      NYME       967   9970
    77831 2004-08-31      NYME       967   9155
    77832 2004-08-24      NYME       967   8655

What I want to do is produce a table the shows for a given date and commodity the total oi across every exchange code. So, the rows would be made up of

unique(testData$date)

and the columns would be

unique(testData$comm.code)

and each cell would be the total oi over all exch.codes on a given day.

Thanks,

Answer 1

The plyr package is good at this, and you should get this done with a single ddply() call. Something like (untested)

ddply(testData, .(date,comm.code), function(x) sum(x$oi))

should work.

Answer 2

with(testData, aggregate(oi, list(date = date, commodity = comm.code, exch.code = exch.code), sum))

This will be much much faster than the equivalent plyr command. The critical thing here is the aggregate command. I used with() because the name of your data frame is long enough that with() will make the line more concise.

Answer 3

Thanks, guys! I think the ddply is a little better for this application. Both are great for me to put in my toolbox. Here are the results (temp is the ddply output, tempp is the with output):

> t.ddply
   user  system elapsed 
 37.784  18.131  68.065 
> t.with
   user  system elapsed 
106.858   1.310 121.895 
> head(temp)
        date comm.code     V1
1 1986-01-15         1 278050
2 1986-01-15         2 601935
3 1986-01-15         4  15885
4 1986-01-15         5 379595
5 1986-01-15         7  47418
6 1986-01-15        20 322195
> head(tempp)
        date commodity exch.code      x
1 1986-01-15         1       CBT 175645
2 1986-01-31         1       CBT 167350
3 1986-02-14         1       CBT 157985
4 1986-02-28         1       CBT 162775
5 1986-03-14         1       CBT 163495
6 1986-03-31         1       CBT 164120