Search code examples
rmergerowaveragemean

Merging two rows by taking the mean, combining names and keeping other columns the same – R

I have a time-series-cross-sectional data frame with circa 400 observations like this:

country    year    value
Belgium    2015    3
Belgium    2016    5
Belgium    2017    8
Belgium    2018    10
Malta      2015    1
Malta      2016    3
...        ...     ...

But I need a transformed version with two years combined (odd number first) by taking the mean of the values, combining the years and keeping the name of the country the same. Like this:

country    year         value(mean)
Belgium    2015_2016    4
Belgium    2017_2018    9
Malta      2015_2016    2
...        ...          ...

I've tried all the solution to this question (How to average every two rows of dataframe in R) but I wasn't able to adjust these in the right way.

Solution

  • You can do:

    library(tidyverse)

df %>%
  mutate(id = rep(1:n(), length.out = n(), each = 2)) %>%
  group_by(id) %>%
  summarize(country    = unique(country),
            year       = paste0(year[1], '_', year[2]),
            value_mean = mean(value))

    This assumes that you will always have an even number of rows per country and that your data is already sorted by year.