4

How do I manage to use a date I have in a dataframe, let's say dataframe 1, as reference for selecting a value that is in other dataframe, dataframe2, when my date in dataframe 1 is between a start date variable and an end date variable in dataframe 2?

For example, I have two dataframes. The first one is a dataframe that only has dates, we will call it "dates". 

library(lubridate)

date <-  ymd(c("2017-06-01", "2013-01-01", "2014-03-01", "2008-01-01","2011-03-01","2009-03-01","2012-03-01","2015-08-01","2008-08-01"))

date <- as.data.frame(date)

> date
        date
1 2017-06-01
2 2013-01-01
3 2014-03-01
4 2008-01-01
5 2011-03-01
6 2009-03-01
7 2012-03-01
8 2015-08-01
9 2008-08-01

My other dataframe,"df2" , contains the start and end dates and a value that is to be assigned to the dataframe"dates" in case a date$date falls between the start date and the end date of the dataframe "df2" .

start_date <- dmy(c("1/6/2001","1/6/2002","1/6/2003","1/10/2011","1/11/2015","1/1/2016","1/1/2017","1/1/2018"))

end_date <-dmy(c("1/5/2002","1/5/2003","1/9/2011","1/10/2015","1/12/2015","1/12/2016","1/12/2017","1/12/2018"))

value <- c(2400,3600,4800,7000,7350,7717.5,8103.38,8508.54)

df2 <- data.frame(start_date, end_date, value)


> df2
  start_date     end_date     value
1 2001-06-01   2002-05-01   2400.00
2 2002-06-01   2003-05-01   3600.00
3 2003-06-01   2011-09-01   4800.00
4 2011-10-01   2015-10-01   7000.00
5 2015-11-01   2015-12-01   7350.00
6 2016-01-01   2016-12-01   7717.50
7 2017-01-01   2017-12-01   8103.38
8 2018-01-01   2018-12-01   8508.54

In the end i would have this result :

        date     value
1 2017-06-01   8103.38
2 2013-01-01   7000.00
3 2014-03-01   7000.00
4 2008-01-01   4800.00
5 2011-03-01   4800.00
6 2009-03-01   4800.00
7 2012-03-01   7000.00
8 2015-08-01   7000.00
9 2008-08-01   4800.00
Dennis Aguilar
  • 113
  • 1
  • 7
  • Wouldn't call it a duplicate, but this post might help: https://stackoverflow.com/questions/44398318/how-to-perform-a-join-based-on-intervals-with-dplyr – camille May 03 '18 at 19:55

1 Answers1

0

Using data.table, you can specify the join condition of the fly:

library(data.table)
setDT(date1)  # date data frame
setDT(df1)

date1[df2, on = .(date >= start_date, date <= end_date), value := i.value]
print(date1)

         date   value
1: 2008-01-01 4800.00
2: 2008-08-01 4800.00
3: 2009-03-01 4800.00
4: 2011-03-01 4800.00
5: 2012-03-01 7000.00
6: 2013-01-01 7000.00
7: 2014-03-01 7000.00
8: 2015-08-01 7000.00
9: 2017-06-01 8103.38
YOLO
  • 20,181
  • 5
  • 20
  • 40