Multiple new aggregates for each row in big dataframe from a dependent subset

user_8720601 0
Viewed 83

I have a big dataframe in R, and I want to make some new columns based on existing columns. However, for each row, the new value depends also on some other rows.

Here is some dummy data

colnames <- c('date', 'docnr', 'clientid', 'values')
docnr <- c(1,2,3,4,5,6)
dates <- c('2017-01-01', '2017-02-01', '2017-03-01', '2017-04-01','2017-01-05', '2017-02-05')
clients <- c(1,1,1,1,2,2)
values <- c(10,14,4,7,9,19)
df <- data.frame(cbind(dates, docnr, clients, values))
names(df) <- colnames
df$date <- as.Date(df$date, format = "%Y-%m-%d")

df
        date docnr clientid values
1 2017-01-01     1        1     10
2 2017-02-01     2        1     14
3 2017-03-01     3        1      4
4 2017-04-01     4        1      7
5 2017-01-05     5        2      9
6 2017-02-05     6        2     19

What I want to do, is for every row (uniquely identified by docnr) to take the date and the client id, and find all of the other rows that have the same clientid, and an earlier date.

Then, I want to calculate some things from this subset. For example, I want the total number of rows in this subset, and the total of all values of this subset.

So for this example data, I would expect:

        date docnr clientid values counts totals
1 2017-01-01     1        1     10      0      0
2 2017-02-01     2        1     14      1     10
3 2017-03-01     3        1      4      2     24
4 2017-04-01     4        1      7      3     28
5 2017-01-05     5        2      9      0      0
6 2017-02-05     6        2     19      1      9

At the moment, I use a for loop:

counts <- numeric(0)
totals <- numeric(0)
for (i in 1:nrow(df)) {
  tmp <- df[df$date< df$date[i] & df$clientid== df$clientid[i],
               c( "date", "docnr","value")]
  cnt <- nrow(tmp)
  tot <- sum(tmp$value)
  counts[i] <- res
  totals[i] <- tot
}
df$counts <- counts
df$totals <- totals

This loop is obviously very slow for a dataframe of 700k rows (have not run it to completion yet). A parallel implementation with doSNOW does not seem to scale much better.

I've tried using a sql query with sqldf, but subqueries can only return 1 value at a time, which would mean running the query all over for every new column I want to define (and I want to add many more derivative columns later).

I came across a solution with SQL objects (Is it possible to get multiple values from a subquery?), but objects didn't work in R's sqldf. Using joins doesn't work because the second query needs to have information from the first query.

I just started in R (and am also not very familiar with sql), so I would be much obliged if someone knew a more efficient way to do this.

grouping dataframe r
2 Answers

Here are two lines of base R code using ave for grouping.

# get counts
df$counts <- ave(df$docnr, df$clientid, FUN=seq_along) - 1L
# get lagged cumulative sum
df$totals <- ave(df$values, df$clientid, FUN=function(x) c(0, head(cumsum(x), -1)))

This returns

df
        date docnr clientid values counts totals
1 2017-01-01     1        1     10      0      0
2 2017-02-01     2        1     14      1     10
3 2017-03-01     3        1      4      2     24
4 2017-04-01     4        1      7      3     28
5 2017-01-05     5        2      9      0      0
6 2017-02-05     6        2     19      1      9

I suspect that the above code wil perform quickly enough for the data that you described. However, data.table is a recommended package for working with datases that may have billions of rows. An analog to the above code in data.table would be

library(data.table)
setDT(df)[, c("counts", "totals") := .(seq_len(.N) - 1L, shift(cumsum(values), fill=0)),
          by=clientid]

where seq_len(.N) fills the role of seq_along and shift fills the role of c(0, head(cumsum(x), -1)) in the previous code.

This returns a data.table with the same values as above.

df
         date docnr clientid values counts totals
1: 2017-01-01     1        1     10      0      0
2: 2017-02-01     2        1     14      1     10
3: 2017-03-01     3        1      4      2     24
4: 2017-04-01     4        1      7      3     28
5: 2017-01-05     5        2      9      0      0
6: 2017-02-05     6        2     19      1      9

data

df <- 
structure(list(date = structure(c(17167, 17198, 17226, 17257, 
17171, 17202), class = "Date"), docnr = c(1, 2, 3, 4, 5, 6), 
    clientid = c(1, 1, 1, 1, 2, 2), values = c(10, 14, 4, 7, 
    9, 19)), .Names = c("date", "docnr", "clientid", "values"
), row.names = c(NA, -6L), class = "data.frame")
user_48957250

This works easily with dplyr

This works

df$values <- as.numeric(as.character(df$values))
df1  <- df %>% 
  arrange(clientid, date) %>% 
  group_by(clientid) %>% 
  mutate(counts = row_number()-1,
         total = lag(cumsum(values),k=1, default=0)) %>% 
  ungroup()

df1 now contains the data you want

user_36524500
Related