R - Fastest way to loop subsets and get outputs using datatables (calculating monthly measures)

user_13051480 0
Viewed 84

I have a problem where I am hoping to calculate some monthly measures for different entities but the code I am currently using appears to be very slow. I am wondering if perhaps you may know of a better solution.

A simplified version of my dataset is below. The problem is that one of datasets contains some 6m individual daily observations and my current method appears to be very slow.

  date     event  id return
2000-07-06     2  1   0.1
2000-07-07     1  1   0.2
2000-07-09     0  1   0.6
2000-07-10     0  1   0.4
2000-07-15     2  1   0.7
2000-07-16     1  1   0.3
2000-07-20     0  1   0.1
2000-07-21     1  1   0.2
2000-07-06     1  2   0.3
2000-07-07     2  2   0.4
2000-07-15     0  2   0.6
2000-07-16     0  2   0.8
2000-07-17     2  2   0.9
2000-07-18     1  2   0.1

To calculate these measures I am running code that looks like the following:


for (j in 1:length(list.of.ids)) {
  for (i in 1:(number.of.months) {
    temp <- subset(data, data$date < FirstDayMonth[i+1] & data$date >= FirstDayMonth[i] & data$id == list.of.ids[j])
    total[i,j+1] <- sum(temp$return, na.rm = TRUE)
  }
}

Note: total[,] is a matrix with a time column and one column for each id and the number of rows equals every month in the dataset. I am hoping to have a matrix that stores all my monthly measures for ids and months. This loop allows me to calculate the monthly sum of returns by id and then store it in that matrix.

Again, the code above allows me to subset on a month period (by restricting my observations to be between the first day of two consecutive months) and on ids. The problem is, for my larger datasets this is very slow.

Are there any improvements to the code that will allow me to get my desired output faster?

subset dataframe r loops performance
3 Answers

Improvements that should yield speedup:

for (j in 1:length(list.of.ids)) {
  id1 <- data$id == list.of.ids[j]
  # outside 2nd loop so no redundant operations wont be made
  for (i in 1:(number.of.months)) {
    id2 <- data$date < FirstDayMonth[i+1] & data$date >= FirstDayMonth[i]
    total[i, j+1] <- sum(data$return[id1 & id2], na.rm = TRUE)
  }
}

(probably big improvements, as we do not need to create new data.frame object each time, we just get indexes for which elements we need to calculate the sum)

But I would use data.table:

require(data.table)
data <- as.data.table(data)
data[, ym := format(date, '%Y-%m')]
res <- data[, sum(return, na.rm = T), keyby = .(ym, id)]
res
#         ym id  V1
# 1: 2000-07  1 2.6
# 2: 2000-07  2 3.1

if needed the end result can be transformed to matrix:

m <- matrix(res$V1, nrow = length(unique(res$ym)))
m
#      [,1] [,2]
# [1,]  2.6  3.1

Update:

Faster version:

setDT(data) # converts data to data.table
x <- data[, .(date = unique(date))][, .(date, ym = format(date, '%Y-%m'))]
data[x, ym := i.ym, on = 'date']
res <- data[, sum(return, na.rm = T), keyby = .(ym, id)]
res

(format(date, '%Y-%m') is slow, so we take only unique dates and calculate ym for them, then merge that to data. This should be quite faster, if you have lots of duplicated dates.)

Update 2:

Conversion to matrix can be obtained with:

resdt <- dcast(res, ym ~ id, value.var = 'V1') # change data structure
resdt[1:2, 1:3]
#         ym        1        2
# 1: 2000-01 6.824182 2.535805
# 2: 2000-02 3.825659 6.769578
resdt[, ym := NULL] # delets ym
setcolorder(resdt, neworder = list.of.ids) # reorder columns
m <- as.matrix(resdt)
m[1:2, 1:2]
#             1        2         3
# [1,] 6.824182 2.535805 -1.193692
# [2,] 3.825659 6.769578 -1.117223
user_70633750

Using aggregate. The year-month variable ym we can create with the substring of the first to the seventh character of the date column.

m <- with(dat, aggregate(list(return=return),
                         by=list(ym=substr(date, 1, 7), id=id), sum))
m
#        ym id return
# 1 2000-07  1    2.6
# 2 2000-07  2    3.1

Or tapply.

m <- with(dat, tapply(return, list(ym=substr(date, 1, 7), id=id), sum))
m
#          id
# ym          1   2
#   2000-07 2.6 3.1

Data

dat <- structure(list(date = c("2000-07-06", "2000-07-07", "2000-07-09", 
"2000-07-10", "2000-07-15", "2000-07-16", "2000-07-20", "2000-07-21", 
"2000-07-06", "2000-07-07", "2000-07-15", "2000-07-16", "2000-07-17", 
"2000-07-18"), event = c(2L, 1L, 0L, 0L, 2L, 1L, 0L, 1L, 1L, 
2L, 0L, 0L, 2L, 1L), id = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 
2L, 2L, 2L, 2L, 2L), return = c(0.1, 0.2, 0.6, 0.4, 0.7, 0.3, 
0.1, 0.2, 0.3, 0.4, 0.6, 0.8, 0.9, 0.1)), row.names = c(NA, -14L
), class = "data.frame")
user_65740380

This should be considerably faster:

for(i in 1:length(number.of.months)) {
  inds <- dat$date < FirstDayMonth[i+1] & dat$date >= FirstDayMonth[i]
  total[i,] <- rowsum(dat$result[inds], dat$id[inds], na.rm=TRUE)
}
user_19537180
Related