Summing all columns by group

Question

I'm positive that this is an incredibly easy answer but I can't seem to get my head around aggregating or casting with Multiple conditions

I have a table that looks like this:

> head(df, n=10L)
   STATE  EVTYPE FATALITIES INJURIES
1     AL TORNADO          0       15
3     AL TORNADO          0        2
4     AL TORNADO          0        2
5     AL TORNADO          0        2
6     AL TORNADO          0        6
7     AL TORNADO          0        1
9     AL TORNADO          1       14
11    AL TORNADO          0        3
12    AL TORNADO          0        3
13    AL TORNADO          1       26

Obviously this goes on... What I want to do is to collapse by STATE and EVTYPE Summing Fatalities and Injuries as I go so if these 10 rows were my ful dataset the result would be a single row data frame of:

   STATE  EVTYPE FATALITIES INJURIES
1     AL TORNADO          2       74

My Complete frame has many States and many EVTYPES

Try dplyr `df %>% group_by(STATE, EVTYPE) %>% summarise_each(funs(sum))` or `aggregate(.~STATE+EVTYPE, df, sum)` — akrun, Jan 22 '15 at 13:37
Or `setDT(df)[, lapply(.SD, sum), .(STATE, EVTYPE)]` with `data.table` — David Arenburg, Jan 22 '15 at 13:40
@docendodiscimus, I also thought so at the beginning, but I think the difference here is that the OP wants to summarise *all* of his columns by group, which I couldn't find a good dupe for. — David Arenburg, Jan 22 '15 at 13:43
@DavidArenburg, I'll see if I can find one. Feel free to answer in the meantime (I might not find any) — talat, Jan 22 '15 at 13:46
A few related questions are: [data.table](http://stackoverflow.com/questions/16513827/r-summarizing-multiple-columns-with-data-table), [base](http://stackoverflow.com/questions/17287968/r-summing-columns-based-on-multiple-other-factor-columns), [dplyr](http://stackoverflow.com/questions/21295936/can-dplyr-summarise-over-several-variables-without-listing-each-one). I'll see what else I can find. [one more](http://stackoverflow.com/questions/17407506/sum-multiple-columns) — talat, Jan 22 '15 at 13:52
David and akrun, I retracted my close vote so you are welcome to answer the question. — talat, Jan 22 '15 at 14:05
Thanks All, @akrun your Aggregate solution worked great, appreciate all your help. I pivoted the data in seconds in Excel, Just couldn't master the syntax in R (~. and +) — NoobMat, Jan 22 '15 at 14:48

score 12 · Answer 1 · edited Jan 27 '21 at 10:51

12

You can try

library(dplyr)
df %>% 
    group_by(STATE, EVTYPE) %>% 
    summarise_each(list(sum))

Or

aggregate(.~STATE+EVTYPE, df, sum)

edited Jan 27 '21 at 10:51

Bill

44,502
24
122
213

answered Jan 22 '15 at 16:38

akrun

874,273
37
540
662

1

This solved my problem - thanksa! `funs` is deprecated now. You can just use `list(sum)` – Bill Jan 27 '21 at 10:51
@Bill Inn the new version it is `summarise(across(everything(), sum), .groups = 'drop')` – akrun Jan 27 '21 at 20:31
That doesn't work for me if I have any non-numeric columns. – Bill Jan 29 '21 at 22:48
1

@Bill You can use `summarise(across(where(is.numeric), sum), .groups = 'drop')` – akrun Jan 29 '21 at 22:49

score 2 · Answer 2 · answered Jan 22 '15 at 14:44

Try ddply, e.g. example below sums explicitly typed columns, but I'm almost sure there can be used a wildcard or a trick to sum all columns. Grouping is made by "STATE".

library(plyr)
df <- read.table(text = "STATE  EVTYPE FATALITIES INJURIES
1     AL TORNADO          0       15
3     AL TORNADO          0        2
4     AL TORNADO          0        2
5     AL TORNADO          0        2
6     AL TORNADO          0        6
7     AL TORNADO          0        1
9     AL TORNADO          1       14
11    AL TORNADO          0        3
12    AL TORNADO          0        3
13    AL TORNADO          1       26
14    IL FLOOD            0       15
15    IL FLOOD            0       20
16    IL FIRE             1        1", header = TRUE, sep = "")

c = ddply(df,.(STATE),summarise,val1 = sum(FATALITIES), val = sum(INJURIES))
print(c)

Result:

  STATE val1 val
1    AL    2  74
2    IL    1  36

Summing all columns by group

2 Answers2

Linked

Related