Aggregate rows by shared values in a variable

Question

I have a somewhat dumb R question. If I have a matrix (or dataframe, whichever is easier to work with) like:

Year  Match
2008   1808
2008 137088
2008      1
2008  56846
2007   2704
2007 169876
2007  75750
2006   2639
2006 193990
2006      2

And I wanted to sum each of the match counts for the years (so, e.g. the 2008 row would be 2008 195743, how would I go about doing this? I've got a few solutions in my head but they are all needlessly complicated and R tends to have some much easier solution tucked away somewhere.

You can generate the same matrix above with:

structure(c(2008L, 2008L, 2008L, 2008L, 2007L, 2007L, 2007L, 
2006L, 2006L, 2006L, 1808L, 137088L, 1L, 56846L, 2704L, 169876L, 
75750L, 2639L, 193990L, 2L), .Dim = c(10L, 2L), .Dimnames = list(
NULL, c("Year", "Match")))

Thanks for any help you can offer.

Nearly identical to http://stackoverflow.com/questions/10180132/consolidate-duplicate-rows — Ben Bolker, Apr 18 '12 at 21:48
Yup. I'm almost certain my infelicitous use of the word merge confounded my attempts to search for an answer. — Adam Hyland, Apr 19 '12 at 02:49

score 5 · Accepted Answer · answered Apr 18 '12 at 03:48

5

aggregate(x = df$Match, by = list(df$Year), FUN = sum), assuming df is your data frame above.

answered Apr 18 '12 at 03:48

Jubbles

4,450
8
35
47

Thanks! I'm glad it is that simple. – Adam Hyland Apr 18 '12 at 04:52
1

you can also use the `data` argument to avoid writting `df` each time, furthermore, you can also use the formula style: `aggregate(Match ~ Year, FUN = sum, data=df)` ;) – Jilber Urbina Dec 08 '13 at 14:50

score 3 · Answer 2 · edited Mar 19 '14 at 21:30

You may also want to use 'ddply' function from 'plyr' package.

# install plyr package
install.packages('plyr')
library(plyr)
# creating your data.frame
foo <- as.data.frame(structure(c(2008L, 2008L, 2008L, 2008L, 2007L, 2007L, 2007L, 
            2006L, 2006L, 2006L, 1808L, 137088L, 1L, 56846L, 2704L, 169876L, 
            75750L, 2639L, 193990L, 2L), .Dim = c(10L, 2L), .Dimnames = list(
              NULL, c("Year", "Match"))))

# here's what you're looking for
ddply(foo,.(Year),numcolwise(sum))

  Year  Match
1 2006 196631
2 2007 248330
3 2008 195743

By the way, the total sum for 2008 should be 195743 (1808+137088+1+56846) instead of 138897 you forgot add 56846 up.

Thanks. I figured there was a way to do it w/ plyr so I tagged it as such. — Adam Hyland, Apr 19 '12 at 02:48

score 3 · Answer 3 · answered Apr 21 '16 at 13:40

3

As it is explained above, you can use aggregate to do it as follows. but in a much simpler way

aggregate(. ~ Year, df, sum)
#  Year  Match
#1 2006 196631
#2 2007 248330
#3 2008 195743

You can also use the Dplyr to solve this as follows

library(dplyr)
df %>% group_by(Year) %>% summarise(Match = sum(Match))
#  Year  Match
#  (int)  (int)
#1  2008 195743
#2  2007 248330
#3  2006 196631

answered Apr 21 '16 at 13:40

I have dozens of columns, so this is really succinct in situations where you want to do all columns instead of having to list each one. – Belinda-Jane Netan Feb 22 '17 at 17:35

Aggregate rows by shared values in a variable

3 Answers3

Linked