CODEWITHSUNDEEP
rsumrow
Vianey Rueda
Vianey Rueda

Reputation: 45

Sum rows in R and store the new value in a new dataframe

I have a dataframe that looks like this:

Year Month total_volume us_assigned
1953 October 55154 18384.667
1953 November 22783 7594.333
1953 December 20996 6998.667
1954 January 22096 7365.333
1954 February 18869 6289.667
1954 March 11598 3866.000
1954 April 37051 12350.333
1954 May 105856 35285.333
1954 June 61320 20440.000
1954 July 44084 14694.667
1954 August 175152 58384.000
1954 September 80071 26690.333

The dataframe goes to the year 2021 with monthly observations as shown in the table above. I am trying to sum up 12 months (i.e., rows) at a time (from Oct. to Sept.) for the column "us_assigned" and save this value in a new dataframe, which would look like this:

Year us_assigned
1 218343
2
3

Year 2 would have the sum of the next 12 months (i.e., the next Oct.-Sept.) and so on and so forth. I have thought of simply summing the rows by specifying them, like below, but this seems too tedious.

sum(us_volume[1:12,4])

I am sure there is a much easier way to do this. I am not too proficient with R so I appreciate any help.

Upvotes: 1

Views: 113

Answers (1)

jared_mamrot
jared_mamrot

Reputation: 26225

This is relatively straightforward using group_by() and summarise() from the dplyr package, e.g.

library(dplyr)

df <- read.table(text = "Year   Month   total_volume    us_assigned
                 1953   October 55154   18384.667
                 1953   November    22783   7594.333
                 1953   December    20996   6998.667
                 1954   January 22096   7365.333
                 1954   February    18869   6289.667
                 1954   March   11598   3866.000
                 1954   April   37051   12350.333
                 1954   May 105856  35285.333
                 1954   June    61320   20440.000
                 1954   July    44084   14694.667
                 1954   August  175152  58384.000
                 1954   September   80071   26690.333", header = TRUE)
df2 <- df %>%
  mutate(Year_int = cumsum(Month == "October")) %>% # every Oct add 1 to Year_int
  group_by(Year_int) %>%
  summarise(us_assigned = sum(us_assigned))

df2
#> # A tibble: 1 × 2
#>   Year_int us_assigned
#>      <int>       <dbl>
#> 1        1     218343.

Created on 2023-01-23 with reprex v2.0.2

Upvotes: 2

Related Questions