CODEWITHSUNDEEP
r
Blake Shurtz
Blake Shurtz

Reputation: 331

Display cumulative sum by year in R

I have data that looks like this.

Month_Yr revenue year mo
2016-01    1200  2016 01
2016-02    7826  2016 02
2016-03   11892  2016 03
2016-05   11376  2016 05
2016-06    9055  2016 06
2016-07    5000  2016 07

I'd like to create a column that contains the cumulative sum of revenue for each year, but have it listed by month. So it would look like this:

Month_Yr revenue year mo cumsum
2016-01    1200  2016 01 1200 
2016-02    7826  2016 02 9026
2016-03   11892  2016 03 20918
2016-05   11376  2016 05 32294
2016-06    9055  2016 06 41349
2016-07    5000  2016 07 46349

This data extends through 2018, and some months (such as April 2016) do not have any values so they're excluded. Thanks!

Upvotes: 0

Views: 5473

Answers (2)

Onyambu
Onyambu

Reputation: 79348

library(tidyverse)
df%>%
separate(Month_Yr,c("year","month"),remove = F)%>%
group_by(year)%>%
mutate(cumsum=cumsum(Revenue))

in base R you can do

transform(df,year=y<-sub("-.*","",Month_Yr),
          month=sub(".*-","",Month_Yr),revenue=ave(Revenue,y,FUN=cumsum))

Upvotes: 4

The Lyrist
The Lyrist

Reputation: 444

You can try:

library(dplyr)
df <- data.frame("Month_Yr" = c("2016-01","2016-02","2016-03","2016-05","2016-06","2016-07","2017-01","2017-02","2017-03","2017-05","2017-06","2017-07","2018-01","2018-02","2018-03","2018-05","2018-06","2018-07"), "Revenue" = c(1200,7826,11892,11376,9055,5000))
df$year <- substr(df$Month_Yr,0,4)
df$mo <- substr(df$Month_Yr,6,7)

df <- df %>%
  arrange(year,mo) %>%
  group_by(year) %>%
  mutate(cumsum = cumsum(Revenue))

Updated the answer.

Upvotes: 1

Related Questions