For each year, I need to sum two months total in different years-CodePudding

For each year, I need to "sum two months total". I would prefer a dplyr solution.

df <- data.frame(city=c("A","A","A","A","B","B","B","B"),
                 year=c(2015,2015,2015,2015,2015,2015,2015,2015),
                 month=c(1,2,3,4,1,2,3,4),
                 total=c(100,50,70,50,60,50,70,80))

In R, I know how to work with groups and to use a variety of dplyr functions, but I need help doing what this post is about.

I would like the output to look like this:

city Year month newtotal
A    2015 1     150
A    2015 3     120
B    2015 1     110
B    2015 3     150

CodePudding user response：

You could use a temporary variable which keeps track of two-monthly blocks and summarize according to it.

library(dplyr)

df %>% mutate(block = (month - 1) %/% 2) %>% 
  group_by(city, year, block) %>% 
  summarise(month = month[1], total = sum(total))%>%
  select(-block)

#> # A tibble: 4 x 4
#> # Groups:   city, year [2]
#>   city   year month total
#>   <chr> <dbl> <dbl> <dbl>
#> 1 A      2015     1   150
#> 2 A      2015     3   120
#> 3 B      2015     1   110
#> 4 B      2015     3   150

CodePudding user response：

You can use the rollapply function from the zoo package to get the sum of every total and the following one.

df %>% 
  group_by(city, year) %>% 
  summarise(month = month[seq(1, n(), by = 2)],
            newtotal = zoo::rollapply(total, 2, sum, by = 2))

# A tibble: 4 x 4
# Groups:   city, year [2]
  city   year month newtotal
  <chr> <dbl> <dbl>    <dbl>
1 A      2015     1      150
2 A      2015     3      120
3 B      2015     1      110
4 B      2015     3      150

CodePudding user response：

You can use the following:

library(dplyr)
df %>% 
  mutate(month2 = ceiling(month / 2)) %>% #for combining months two by two
  group_by(city, year, month2) %>% 
  summarise(month = month[1], total = sum(total)) %>% 
  dplyr::select(-month2)

# # A tibble: 4 x 4
# # Groups:   city, year [2]
# city   year month total
# <chr> <dbl>  <dbl> <dbl>
# 1 A      2015      1   150
# 2 A      2015      3   120
# 3 B      2015      1   110
# 4 B      2015      3   150