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?
当数据帧1中的日期位于开始日期变量和结束日期变量之间时,如何设置使用数据框中的日期,比如数据帧1,作为选择其他数据框中的值dataframe2的参考在数据帧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" .
我的其他数据框“df2”包含开始日期和结束日期以及在日期$ date落在数据框“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
1 个解决方案
#1
0
Using data.table, you can specify the join condition of the fly:
使用data.table,您可以指定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
#1
0
Using data.table, you can specify the join condition of the fly:
使用data.table,您可以指定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