CODEWITHSUNDEEP
rdata-sciencedata-cleaning
user10605319
user10605319

Reputation:

Cumulative sums by month in R

I want to transform my data from this

Month   Expenditures
1       1
1       2
2       3
2       6
3       2
3       5

to this:

Month   Cumulative_expenditures
1       3
2       12
3       19

, but can't seem to figure out how to do it.

I tried using the cumsum() function, but it counts each observation - it doesn't distinguish between groups.

Any help would be much appreciated!

Upvotes: 2

Views: 1075

Answers (4)

akrun
akrun

Reputation: 886948

We can use base R

out <- with(df1, rowsum(Expenditures, Month))
data.frame(Month = row.names(out), Expenditure = cumsum(out))
#  Month Expenditure
#1     1           3
#2     2          12
#3     3          19

Or more compactly

with(df1, stack(cumsum(rowsum(Expenditures, Month)[,1])))[2:1]

data

df1 <- structure(list(Month = c(1L, 1L, 2L, 2L, 3L, 3L), Expenditures = c(1L, 
2L, 3L, 6L, 2L, 5L)), class = "data.frame", row.names = c(NA, 
-6L))

Upvotes: 0

ThomasIsCoding
ThomasIsCoding

Reputation: 101064

Here is another base R option using subset + ave

subset(
  transform(df, Expenditures = cumsum(Expenditures)),
  ave(rep(FALSE, nrow(df)), Month, FUN = function(x) seq_along(x) == length(x))
)

which gives

  Month Expenditures
2     1            3
4     2           12
6     3           19

Upvotes: 0

Allan Cameron
Allan Cameron

Reputation: 173793

Using dplyr:

library(dplyr)

df %>% 
  group_by(Month) %>% 
  summarise(Expenditures = sum(Expenditures), .groups = "drop") %>% 
  mutate(Expenditures = cumsum(Expenditures))

#> # A tibble: 3 x 2
#>   Month Expenditures
#>   <int>        <int>
#> 1     1            3
#> 2     2           12
#> 3     3           19

Or in base R:

data.frame(Month = unique(df$Month), 
           Expenditure = cumsum(tapply(df$Expenditure, df$Month, sum)))
#>   Month Expenditure
#> 1     1           3
#> 2     2          12
#> 3     3          19

Upvotes: 1

Duck
Duck

Reputation: 39595

A two steps base R solution would be:

#Code
df1 <- aggregate(Expenditures~Month,data=mydf,sum)
#Create cum sum
df1$Expenditures <- cumsum(df1$Expenditures)

Output:

  Month Expenditures
1     1            3
2     2           12
3     3           19

Some data used:

#Data
mydf <- structure(list(Month = c(1L, 1L, 2L, 2L, 3L, 3L), Expenditures = c(1L, 
2L, 3L, 6L, 2L, 5L)), class = "data.frame", row.names = c(NA, 
-6L))

Upvotes: 2

Related Questions