R Programming - Sum Elements of Rows with Common Values

Jonathan Charlton picture Jonathan Charlton · Apr 10, 2013 · Viewed 10.5k times · Source

Hello and thank you in advance for your assistance,

(PLEASE Note Comments section for additional insight: i.e. the cost column in the example below was added to this question; Simon, provides a great answer, but the cost column itself is not represented in the data response from him, although the function he provides works with the cost column)

I have a data set, lets call it 'data' which looks like this

NAME     DATE     COLOR   PAID    COST
Jim      1/1/2013 GREEN   150     100
Jim      1/2/2013 GREEN   50      25
Joe      1/1/2013 GREEN   200     150
Joe      1/2/2013 GREEN   25      10

What I would like to do is sum the PAID (and COST) elements of the records with the same NAME value and reduce the number of rows (as in this example) to 2, such that my new data frame looks like this:

NAME     DATE     COLOR   PAID    COST
Jim      1/2/2013 GREEN   200     125
Joe      1/2/2013 GREEN   225     160

As far as the dates are concerned, I don't really care about which one survives the summation process.

I've gotten as far as rowSums(data), but I'm not exactly certain how to use it. Any help would be greatly appreciated.

Answer

Simon O'Hanlon picture Simon O'Hanlon · Apr 10, 2013

aggregate is the function you are looking for:

aggregate( cbind( PAID , COST ) ~ NAME + COLOR , data = data , FUN = sum )
# NAME PAID
# 1  Jim  200
# 2  Joe  225