Join dataframes where the dates are matches

Question

I have two dataframes, one with ITM JPM Puts from 2011, and another with interest rates, dividends etc. A small example of the two frames are;

Symbol  ExpirationDate  StrikePrice  DataDate
JPM     2011-01-07      54           2011-01-05
JPM     2011-01-22      55           2011-01-05
JPM     2011-01-28      44           2011-01-10



Date        Rf   Div
04/01/2011  0.2  3
05/01/2011  0.1  3
10/01/2011  0.2  4

I am trying to join the two frames so that the df1$DataDate is == df2$Date;

Symbol  ExpirationDate  StrikePrice  DataDate     Rf   Div
JPM     2011-01-07      54           2011-01-05   0.1  3
JPM     2011-01-22      55           2011-01-05   0.1  3
JPM     2011-01-28      44           2011-01-10   0.2  4

I'm at a loss at how to do this however. The first step will involve formatting the dates in the two separate dataframes to be the same?

Have a look at - https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right — Ronak Shah, May 19 '21 at 11:45

ThomasIsCoding · Accepted Answer · 2021-05-19T11:41:54.177

You can try the code below with merge + as.Date

merge(
  transform(
    df1,
    DataDate = as.Date(DataDate)
  ),
  transform(
    df2,
    Date = as.Date(Date, format = "%d/%m/%Y")
  ),
  by.x = "DataDate",
  by.y = "Date"
)

which gives

    DataDate Symbol ExpirationDate StrikePrice  Rf Div
1 2011-01-05    JPM     2011-01-07          54 0.1   3
2 2011-01-05    JPM     2011-01-22          55 0.1   3
3 2011-01-10    JPM     2011-01-28          44 0.2   4

Or we can use match

cbind(
  df1,
  df2[match(
    df1$DataDate,
    as.character(as.Date(df2$Date, format = "%d/%m/%Y"))
  ), -1]
)

which gives

    Symbol ExpirationDate StrikePrice   DataDate  Rf Div
2      JPM     2011-01-07          54 2011-01-05 0.1   3
2.1    JPM     2011-01-22          55 2011-01-05 0.1   3
3      JPM     2011-01-28          44 2011-01-10 0.2   4

Data

> dput(df1)
structure(list(Symbol = c("JPM", "JPM", "JPM"), ExpirationDate = c("2011-01-07", 
"2011-01-22", "2011-01-28"), StrikePrice = c(54L, 55L, 44L),
    DataDate = c("2011-01-05", "2011-01-05", "2011-01-10")), class = "data.frame", row.names = c(NA,
-3L))
> dput(df2)
structure(list(Date = c("04/01/2011", "05/01/2011", "10/01/2011"
), Rf = c(0.2, 0.1, 0.2), Div = c(3L, 3L, 4L)), class = "data.frame", row.names = c(NA,
-3L))

Hi Thomas, the match worked. Thanks so much! – LukeAJN May 19 '21 at 12:24 — LukeAJN, May 19 '21 at 12:24

Chris Ruehlemann · Answer 2 · 2021-05-19T11:43:47.430

0

Try this: first format the dates, then merge the dataframes:

df2$Data <- format(as.Date(df2$Data), format = "%Y-%m-%d")
merge(df1, df2, by.x = "DataDate", by.y = "Data")

Result:

    DataDate Symbol     XY
1 2011-01-05    JPM   blah
2 2011-01-06    JPM   bluh
3 2011-07-05    JPM blahha

Data:

df1 <- data.frame(
  Symbol = c("JPM", "JPM", "JPM", "AAPL"),
  DataDate = c("2011-01-05", "2011-01-06", "2011-07-05", "2011-05-21")
)
df2 <- data.frame(
  XY = c("blah", "bluh", "blahha"),
  Data = c("2011/01/05", "2011/01/06", "2011/07/05")
)

edited May 19 '21 at 11:43

answered May 19 '21 at 11:28

Chris Ruehlemann

20,321
4
12
34

Hi Chris, this would merge the entire dataframes together would it not? I'm only looking to add the info from dataframe 2 into dataframe 1 when the two dates match – LukeAJN May 19 '21 at 11:34
Updated again (had overlooked that you have distinct date formats and column names.) – Chris Ruehlemann May 19 '21 at 11:44

Join dataframes where the dates are matches

2 Answers2

Data