R sum a variable by two groups [duplicate]

Question

I have a data frame in R that generally takes this form:

ID     Year     Amount  
3       2000      45  
3       2000      55  
3       2002      10  
3       2002      10  
3       2004      30  
4       2000      25  
4       2002      40  
4       2002      15  
4       2004      45  
4       2004      50

I want to sum the Amount by ID for each year, and get a new data frame with this output.

ID      Year     Amount  
3       2000      100  
3       2002       20  
3       2004       30  
4       2000       25  
4       2002       55  
4       2004       95 

This is an example of what I need to do, in reality the data is much larger. Please help, thank you!

Chicken

aggregate(Amount ~ ID + Year, DF, sum) – Gambrinus

Answer 1

You can group_by ID and Year then use sum within summarise

library(dplyr)

txt <- "ID Year Amount
3 2000 45
3 2000 55
3 2002 10
3 2002 10
3 2004 30
4 2000 25
4 2002 40
4 2002 15
4 2004 45
4 2004 50"

df <- read.table(text = txt, header = TRUE)

df %>% 
  group_by(ID, Year) %>% 
  summarise(Total = sum(Amount, na.rm = TRUE))
#> # A tibble: 6 x 3
#> # Groups:   ID [?]
#>      ID  Year Total
#>   <int> <int> <int>
#> 1     3  2000   100
#> 2     3  2002    20
#> 3     3  2004    30
#> 4     4  2000    25
#> 5     4  2002    55
#> 6     4  2004    95

If you have more than one Amount column & want to apply more than one function, you can use either summarise_if or summarise_all

df %>% 
  group_by(ID, Year) %>% 
  summarise_if(is.numeric, funs(sum, mean))
#> # A tibble: 6 x 4
#> # Groups:   ID [?]
#>      ID  Year   sum  mean
#>   <int> <int> <int> <dbl>
#> 1     3  2000   100  50  
#> 2     3  2002    20  10  
#> 3     3  2004    30  30  
#> 4     4  2000    25  25  
#> 5     4  2002    55  27.5
#> 6     4  2004    95  47.5

df %>% 
  group_by(ID, Year) %>% 
  summarise_all(funs(sum, mean, max, min))
#> # A tibble: 6 x 6
#> # Groups:   ID [?]
#>      ID  Year   sum  mean   max   min
#>   <int> <int> <int> <dbl> <dbl> <dbl>
#> 1     3  2000   100  50      55    45
#> 2     3  2002    20  10      10    10
#> 3     3  2004    30  30      30    30
#> 4     4  2000    25  25      25    25
#> 5     4  2002    55  27.5    40    15
#> 6     4  2004    95  47.5    50    45

^{Created on 2018-09-19 by the reprex package (v0.2.1.9000)}

Collagen

Answer 2

With data.table

library("data.table")

D <- fread(
"ID     Year     Amount  
3       2000      45  
3       2000      55  
3       2002      10  
3       2002      10  
3       2004      30  
4       2000      25  
4       2002      40  
4       2002      15  
4       2004      45  
4       2004      50"
)
D[, .(Amount=sum(Amount)), by=.(ID, Year)]

and with base R:

aggregate(Amount ~ ID + Year, data=D, FUN=sum)

(as commented by @markus)

Hargrave