Combining Data.Tables (R) with a loop or mapply

Question

I am new to data tables in R and have managed to get 80% of the way through my analysis. The background is that I want to get the returns of a stock 5 days (before and after), and then 25 and 45 days after they report. I have successfully managed to do it for one set of dates (effectively hardcoding) but when I try and automate the process it falls apart.

I will start with my current formulas and then explain the data.

This formula successfully looks at the data tables and returns the sum that I need. The issue is that datem5 and V1 need to go through a loop (or mapply) to automate the process.

CQR_Date[CQR_DF[CQR_Date, sum(CQR), on = .(unit, date >= date1, date <= datem5),
            by = .EACHI], newvar := V1, on = .(unit, date1=date)]

I tried this (along with many other variants). Please note the newvar needs to be addressed as well.

for (i in 1:4) {
              CQR_Date[CQR_DF[CQR_Date, sum(CQ), on = .(unit, date >= date1, date <= cols[,..i]),
              by = .EACHI], newvar := v, on = .(unit, date1=date)]

but get this error

Error: argument specifying columns specify non existing column(s): cols[3]='cols[, ..i]'

Interestingly, when I try

for (i in 1:2) {
 y <- cols[,..i]}

There is no issue.

Now in terms of data;

col just contains the column headings that I need from CQR_Data

cols <- data.table("datem5", "datep5", "datep20" , "datep45")

CQ_Data has the reporting dates for the stock CQ such as the following

 CQ_Date <- data.frame("date1" = anydate(c("2016-02-17", "2016-06-12", "2016-08-17")))
 CQ_Date$datem5 <- CQ_Date$date1 - 5  # minus five days
 CQ_Date$datep5 <- CQ_Date$date1 + 5  # plus five days
 CQ_Date$datep20 <- CQ_Date$date1 + 20
 CQ_Date$datep45 <- CQ_Date$date1 + 45
 CQ_Date$unit <- 1    # I guess I need this for some sort of indexing

Then CQ_DF (it is the log returns for the stock) is formed by:

 CQ_DF <- data.frame("unit" = rep(1,300))
 CQ_DF$CQ <- rnorm(10)
 CQ_DF$date <- seq(as.Date("2015-12-25"), by = "day", length.out = 300)
 CQ_DF$unit <- 1

Before setting them as DT

setDT(CQ_DF)
setDT(CQ_Date)

Any help would be greatly appreciated. Note this uses

  library(data.table)
  library(anytime)

A simplified version is:

  CQ_Date <- data.frame("date1" = c(10, 20))
  CQ_Date$datep5 <- CQ_Date$date1 + 5  # plus five days
  CQ_Date$datep20 <- CQ_Date$date1 + 10
  CQ_Date$unit <- 1 

  CQ_DF <- data.frame("unit" = rep(1,100))
  CQ_DF$CQ <- seq(1, by = 1, length.out = 100)
  CQ_DF$date <- seq(1, by = 1, length.out = 100)
  CQ_DF$unit <- 1

  setDT(CQ_DF)
  setDT(CQ_Date)

  cols <- c("datep5", "datep20" )

  tmp <- melt(CQ_Date, measure.vars = cols)
  setDT(tmp)

  tmp[CQ_DF[tmp, sum(CQ), on = .( unit, date >= date1,  date <= value), by = 
  .EACHI],newvar := V1, on = .(unit, date1=date  )]

The issue is now that the sum does not appear to work correctly. It may have something to do with "variable" variable.

user_3962914 · Answer

Instead of using mapply or for loop, try reshaping the dataset in long format using melt, create sequence between the numbers, perform the join and calculate the sum.

library(data.table)
cols <- c("datep5", "datep20" )

tmp <- melt(CQ_Date, measure.vars = cols)
tmp <- melt(CQ_Date, measure.vars = cols)
tmp <- tmp[, list(date = seq(date1, value)), .(unit, variable, date1, value)]
tmp <- merge(tmp, CQ_DF, by = c('unit', 'date'))
tmp[, .(newvar = sum(CQ)), .(unit, variable, date1)]

#   unit variable date1 newvar
#1:    1   datep5    10     75
#2:    1  datep20    10    165
#3:    1   datep5    20    135
#4:    1  datep20    20    275

If you need the data back in wide format you can use dcast.

Equivalent tidyverse option is :

library(tidyverse)

CQ_Date %>%
  pivot_longer(cols = cols) %>%
  mutate(date = map2(date1, value, seq)) %>%
  unnest(date) %>%
  left_join(CQ_DF, by = c('unit', 'date')) %>%
  group_by(unit, name, date1) %>%
  summarise(newvar = sum(CQ))

Combining Data.Tables (R) with a loop or mapply

1 Answers