Dynamic Merge between two data frames

Question

I have two data frames. The first (ERdata) is about earnings releases by companies. It has the columns "companyname", "date" (date of the earnings release) and "timing" (BeforeOpen or AfterClose). The second (companydata) has "companyname", "date" and "closingvalue" (the closing stock price for every day in the time frame).

I want to merge the tables, so that the closing stock price from BEFORE the earnings release is shown. This would be a simple merge command:

 merge(ERdata, companydata)

However, if the earnings release is BeforeOpen, it must show the closing stock price of the day before (date-1). This means I somehow have to include an IF clause or something into the merge command.

Example:

ERdata:

 companyname date timing
 AXP 2016-04-21 AfterClose
 BA 2016-04-27 BeforeOpen

companydata:

 companyname date closingvalue
 AXP 2016-04-20 50
 AXP 2016-04-21 60
 BA 2016-04-26 30
 BA 2016-04-27 35

Outcome:

 companyname date timing closingvalue
 AXP 2016-04-21 AfterClose 60
 BA 2016-04-27 BeforeOpen 30

As can be seen, for rows with AfterClose it is just like the simple merge command. For the rows with BeforeOpen, it has to use the closingvalue for "date-1". Is there a way to do this using a merge command? Or should I rather circumvent it somehow?

Thanks in advance!! :-)

Answer 1

You'd have to give a few more lines of your data to be sure .. but this works as far as I can tell from what you have shown (ie. so long as there are only mutually exclusive before and after dates)..

library(tidyverse)
ERdata %>% 
  inner_join(companydata, by = "companyname") %>%
  filter((timing == "AfterClose" & date.x == date.y) | 
           (timing == "BeforeOpen" & date.x != date.y))


  companyname     date.x     timing     date.y closingvalue
1         AXP 2016-04-21 AfterClose 2016-04-21           60
2          BA 2016-04-27 BeforeOpen 2016-04-26           30