Combining rows of data in R; summing a column by other columns

Question

This is the current structure of my data:

Area	Item	Element	Year1	Year2
Africa	Enteric	CH4	2	3
Africa	Enteric	C02	1.5	2
Africa	Manure	CH4	3	1
Africa	Manure	CO2	0.5	2
Europe	Enteric	CH4	2.5	2
Europe	Enteric	C02	1.7	2.3
Europe	Manure	CH4	3	4
Europe	Manure	CO2	1.5	1.5

I however want to sum "Years" by "Area" and "Item" such that my new that structure is like below:

Area	Item	Year1	Year2
Africa	Enteric	3.5	5
Africa	Manure	3.5	3
Europe	Enteric	4.2	4.3
Europe	Manure	4.5	5.5

I presume you meant "Year1" and "Year2" not "Element". – Jakob Gepp Feb 24 '21 at 15:14 — Jakob Gepp, Feb 24 '21 at 15:14

score 0 · Answer 1 · answered Feb 24 '21 at 15:08

I guess aggregate from base R could help

> aggregate(cbind(Year1, Year2) ~ Area + Item, df, sum)
    Area    Item Year1 Year2
1 Africa Enteric   3.5   5.0
2 Europe Enteric   4.2   4.3
3 Africa  Manure   3.5   3.0
4 Europe  Manure   4.5   5.5

data

> dput(df)
structure(list(Area = c("Africa", "Africa", "Africa", "Africa", 
"Europe", "Europe", "Europe", "Europe"), Item = c("Enteric",
"Enteric", "Manure", "Manure", "Enteric", "Enteric", "Manure",
"Manure"), Element = c("CH4", "C02", "CH4", "CO2", "CH4", "C02",
"CH4", "CO2"), Year1 = c(2, 1.5, 3, 0.5, 2.5, 1.7, 3, 1.5), Year2 = c(3,
2, 1, 2, 2, 2.3, 4, 1.5)), class = "data.frame", row.names = c(NA,
-8L))

score 0 · Accepted Answer · answered Feb 24 '21 at 15:09

dplyr solution. This is one of the most basic use case of group_by and summarise

library(tidyverse)

df <- read.table(text = "Area   Item    Element Year1   Year2
Africa  Enteric CH4 2   3
Africa  Enteric C02 1.5 2
Africa  Manure  CH4 3   1
Africa  Manure  CO2 0.5 2
Europe  Enteric CH4 2.5 2
Europe  Enteric C02 1.7 2.3
Europe  Manure  CH4 3   4
Europe  Manure  CO2 1.5 1.5", header = T) %>% 
  as_tibble()

df %>% 
  group_by(Area, Item) %>% 
  summarise(Year1 = sum(Year1),
            Year2 = sum(Year2))
#> `summarise()` regrouping output by 'Area' (override with `.groups` argument)
#> # A tibble: 4 x 4
#> # Groups:   Area [2]
#>   Area   Item    Year1 Year2
#>   <chr>  <chr>   <dbl> <dbl>
#> 1 Africa Enteric   3.5   5  
#> 2 Africa Manure    3.5   3  
#> 3 Europe Enteric   4.2   4.3
#> 4 Europe Manure    4.5   5.5

^{Created on 2021-02-24 by the reprex package (v0.3.0)}

Combining rows of data in R; summing a column by other columns

2 Answers2