Assign day (1 through n) by mathcing date in other data frame

user_13051131 0
Viewed 116

Overall aim: Create a variable in a data frame of daily stock prices that indicates how many days have passed since the firm presented earnings. This should be done by looking up the date in another data frame.

I have two data frames: One containing daily stock prices (df1) and another containing quarterly observations with reported earnings by the firm (df2). In df1, I aim to create a new variable which is days from reported earnings i.e. the day earnings is reported is day 0, and the following day is 1 etc. untill it reaches next reporting date, where it should start over from 0.

How do I match the date of the stock price in df1 with the nearest date of reported earnings in df2 and assign it to a variable in df1? I have multiple firms in my dataset.

Example: Ideally, my final result in df1 should look like this where the last variable indicates that the earnings announcement of the firm was 2019/01/30:

date          stock price   days from earnings announcement
2019/01/30    4,4           0
2019/01/31    4,2           1
2019/02/01    4,5           2
2019/02/02    4,6           3
...

Now, assume that the firm presents new earnings announcement on 2019/04/30. If so, it should look like this:

date          stock price   days from earnings announcement
2019/01/30    x             0
2019/01/31    x             1
2019/02/01    x             2
2019/02/02    x             3
...
2019/04/29    x             89
2019/04/30    x             0
2019/05/01    x             1
...

Thus, it is indicated that 2019/04/29 is 89 days after the latest earnings announcement and on 2019/04/30 new earnings announcement was presented. The relevant files (including first steps of the code) can be found on this link to dropbox

stackoverflow.r:

setwd("~/R")
setwd("~/R/stackoverflow")
library(readr)
df2 <- read_delim("eps_forecasted_clean.csv", 
                  ";", escape_double = FALSE, col_types = cols(date = col_date(format = "%d-%m-%Y")), 
                  trim_ws = TRUE)
View(df2) #use "date" to lookup
df1 <- read_delim("~/R/stackoverflow/stock_prices.csv", 
                  ";", escape_double = FALSE, trim_ws = TRUE)
View(df1)
stock finance r
3 Answers

Here is a data.table approach.

Edit: This is based on the actual data from the link OP provided.

  • df1 with GVKEY and reportdate
  • df2 with gvkey and date

where gvkey/GVKEY represents each firm.

A common join_date is created in both data tables. An index is created with a rolling join which will identify the most recent report date.

The number of days from reported earnings is then the date - join_date after the rolling join.

library(readr)
library(data.table)

df2 <- read_delim("eps_forecasted_clean.csv", 
                  ";", escape_double = FALSE, col_types = cols(date = col_date(format = "%d-%m-%Y")), 
                  trim_ws = TRUE)

# Changed filepath here
df1 <- read_delim("stock_prices.csv", 
                  ";", escape_double = FALSE, trim_ws = TRUE)

# Reportdate needs to be in date format
df1$reportdate <- as.Date(df1$reportdate, format = "%d-%m-%Y")

setDT(df1)[ , join_date := reportdate]
setDT(df2)[ , join_date := date]

# Joining by gvkey as ID for firm, correct?
idx <- df2[df1, on = c(gvkey = "GVKEY", "join_date"), roll = TRUE, which = TRUE]

df1[ , numDays := reportdate - df2[idx, join_date]]
df1

Output

              X1 GVKEY LIID LINKTYPE   LINKDT iid datadate reportdate   tic         conm prccd trfd year price_adjusted  join_date numDays
      1:       1  1004    1       LU 19720424   1 19831230 1983-12-30   AIR     AAR CORP  1725    1 1983           1725 1983-12-30 30 days
      2:       2  1004    1       LU 19720424   1 19840103 1984-01-03   AIR     AAR CORP    17    1 1984             17 1984-01-03 34 days
      3:       3  1004    1       LU 19720424   1 19840104 1984-01-04   AIR     AAR CORP  1725    1 1984           1725 1984-01-04 35 days
      4:       4  1004    1       LU 19720424   1 19840105 1984-01-05   AIR     AAR CORP  1725    1 1984           1725 1984-01-05 36 days
      5:       5  1004    1       LU 19720424   1 19840106 1984-01-06   AIR     AAR CORP    18    1 1984             18 1984-01-06 37 days
     ---                                                                                                                                  
1048571: 1048571  4394    1       LU 19820129   1 19880629 1988-06-29 EEE.2 ENSOURCE INC  7188    1 1988           7188 1988-06-29 90 days
1048572: 1048572  4394    1       LU 19820129   1 19880630 1988-06-30 EEE.2 ENSOURCE INC  7375    1 1988           7375 1988-06-30 91 days
1048573: 1048573  4394    1       LU 19820129   1 19880701 1988-07-01 EEE.2 ENSOURCE INC  7375    1 1988           7375 1988-07-01 92 days
1048574: 1048574  4394    1       LU 19820129   1 19880705 1988-07-05 EEE.2 ENSOURCE INC  7375    1 1988           7375 1988-07-05 96 days
1048575: 1048575  4394    1       LU 19820129   1 19880706 1988-07-06 EEE.2 ENSOURCE INC   725    1 1988            725 1988-07-06 97 days
user_34606700

I think this should do what you're looking for.

df1 <- data.frame(id = c(1,1,1,1,2,2,2,2),
                  date = rep(as.Date(c("2020-01-01", "2020-01-02", "2020-01-03", "2020-01-04")),2))
df1                  
df2 <- data.frame(id = c(1,1,2,2),
                  reportDate = rep(as.Date(c("2019-10-01","2020-01-01", "2019-10-03", "2020-01-03"))))
df2                  

for(i in seq_len(nrow(df1))){
  refDate <- max(df2$reportDate[df2$reportDate<= df1$date[i] & df2$id == df1$id[i]])
  df1$numDays[i] <- df1$date[i] - refDate
}

(There's probably a more efficient way to do this without the loop, but I'm not really sure what that is.)

user_131107770

To get the most recent date match, you need a fuzzy join with the fuzzyjoin package.

So with fuzzyjoin, tidyverse and lubridate, you could do:


library(tidyverse)
library(lubridate)
library(fuzzyjoin)

df1 %>%
    fuzzyjoin::fuzzy_left_join(
        df2, 
        by = c("tic" = "tic", "reportdate" = "date"),
        match_fun = list(`==`, `>=`)) %>%
    mutate(daysFromWhatever = difftime(reportdate, date, units = "days"))

Note that after downloading your files from dropbox I had to mutate the date fields, and that they were in different formats, which might be giving you trouble as well.

If I misunderstand which date you want to lag which, you can change the >= to <=.

user_56966260
Related