r - Cumulative sum by group in sqldf?

Question

I have a data frame with 3 variables: place, time, and value (P, T, X). I want to create a fourth variable which will be the cumulative sum of X. Normally I like to do grouping calculations with sqldf, but can't seem to find an equivalent for cumsum. That is:

sqldf("select P,T,X, cumsum(X) as X_CUM from df group by P,T") 

doesn't work. Is this even possible with sqldf? I tried doBy, but that doesn't all cumsum either.

Answer 1

Set up some test data:

DF <- data.frame(t = 1:4, p = rep(1:3, each = 4), value = 1:12)

and now we have three solutions. First we use sqldf, as requested, using the default SQLite database. Next we do it with sqldf again but this time with PostgreSQL using RPostgreSQL or RpgSQL driver. PostgreSQL supports analytical windowing functions which simplify the SQL. You will need to set up a PostgreSQL database first to do that one. Finally we show a pure R solution which only uses the core of R.

1) sqldf/RSQLite

library(sqldf)

sqldf("select a.*, sum(b.value) as cumsum 
    from DF a join DF b 
    using (p)
    where a.t >= b.t
    group by p, a.t"
)

2) sqldf/RPostgreSQL

library(RPostgreSQL)
library(sqldf)

sqldf('select *,
    sum(value) over (partition by p order by t) as cumsum 
    from "DF"'
)

(This also works with the RpgSQL PostgreSQL driver. To use that you must have Java installed and a PostgreSQL database set up and then in place of the above use: 1ibrary(RpgSQL); sqldf(...) where the same SQL string is used except there should be no quotes around DF.)

3) Plain R

transform(DF, cumsum = ave(value, p, FUN = cumsum))