跳到合并面板的下一个可用日期 [英] Jumping to the next available date when merging panels
问题描述
我想按日期合并两个data.frames。数据包含股票每个交易日的股票数据。活动包含有关该公司的消息。有些消息在非交易日发布,所以目前没有股票数据。例如04.01.2000发布了一些公司A的消息。我想把这篇文章与NEXT交易日的回报合并,在这种情况下是06.01.2000的回报。那么当面对面情况合并的时候,我怎么能跳到下一个交易日呢?
date1< - c(01.01.2000,02.01.2000,03.01.2000,06.01 .2000,07.01.2000,09.01.2000,01.01.2000,02.01.2000,03.01.2000,06.01.2000,07.01.2000,09.01.2000 )
/ pre>
ret1 < - c(-2.0,1.1,3,1.4,-0.2,0.6,0.1,-0.21,-1.2,0.9,0.3,-0.1)
company1 < - c (A,A,A,A,A,A,B,B,B,B,B,B b $ b df< - data.frame(date1,ret1,company1)
df
#date1 ret1 company1
#1 01.01.2000 -2.00 A
#2 02.01.2000 1.10 A
#3 03.01.2000 3.00 A
#4 06.01.2000 1.40 A
#5 07.01.2000 -0.20 A
#6 09.01.2000 0.60 A
#7 01.01.2000 0.10 B
#8 02.01.2000 -0.21 B
#9 03.01.2000 -1.20 B
#10 06.01.2000 0.90 B
#11 07.01.2000 0.30 B
#12 09.01.2000 -0.10 B
date2< - c(02.01.2000,03.01.2000,04.01。 2000,08.01.2000,05.01.2000,08.01.2000,09.01.2000)
news2< - c(blabla11,blabla12,blabla13,blabla14,blabla21,blabla22,blabla23)
company2 ,A,A,B,B,B)
事件< - data.frame(date2,news2,company2)
event
#date2 news2 company2
#1 02.01.2000 blabla11 A
#2 03.01.2000 blabla12 A
#3 04.01.2000 blabla13 A
#4 08.01.2000 blabla14 A
#5 05.01.2000 blabla21 B
#6 08.01.2000 blabla22 B
#7 09.01.2000 blabla23 B
输出应如下所示:
#date2 news2 company2 date1 ret
#1 02.01.2000 blabla11 A 02.01.2000 1.10
#2 03.01.2000 blabla12 A 03.01.2000 3.00
#3 04.01.2000 blabla13 A 06.01.2000 1.40
#4 08.01.2000 blabla14 A 09.01.2000 0.60
#5 05.01.2000 blabla21 B 06.01.2000 0.90
#6 08.01.2000 blabla22 B 09.01.2000 -0.10
#7 09.01.2000 blabla23 B 09.01.2000 -0.10
解决方案它或多或少与您的其他问题。唯一的改变是,您必须设置公司,日期作为执行加入的关键列(请注意,订单很重要 - 首先按公司排序,然后按日期排序)。
require(data.table)## 1.9.2
setDT(df)
setDT(event)
setkey(df,company1,date1)
setkey(event,company2,date2)
df [,date = = date1]
df [event,roll = -Inf]
company1 date1 ret1 date news2
1:A 02.01.2000 1.1 02.01.2000 blabla11
2:A 03.01.2000 3.0 03.01.2000 blabla12
3:A 04.01.2000 1.4 06.01.2000 blabla13
4:A 08.01.2000 0.6 09.01.2000 blabla14
5:B 05.01.2000 0.9 06.01.2000 blabla21
6:B 08.01.2000 -0.1 09.01.2000 blabla22
7:B 09.01.2000 -0.1 09.01.2000 blabla23
I asked a very similar question just before. The difference is, before it was a situation with only one company. Now it's a panel data structure. I want to merge two data.frames by date. Data contains stock data for each trading day of a stock. Events contains news about the company. Some news were published on non-trading days, so there are no stock data for this day. For example on 04.01.2000 some news for company "A" were published. I want to merge this article with the return of the NEXT trading day, in this case the return on the 06.01.2000. So how can I jump to the next trading day when merging in a panel situation?
date1 <- c("01.01.2000","02.01.2000","03.01.2000","06.01.2000","07.01.2000","09.01.2000","01.01.2000","02.01.2000","03.01.2000","06.01.2000","07.01.2000","09.01.2000") ret1 <- c(-2.0,1.1,3,1.4,-0.2, 0.6, 0.1, -0.21, -1.2, 0.9, 0.3, -0.1) company1 <- c("A","A","A","A","A","A","B","B","B","B","B","B") df <- data.frame(date1, ret1, company1) df # date1 ret1 company1 # 1 01.01.2000 -2.00 A # 2 02.01.2000 1.10 A # 3 03.01.2000 3.00 A # 4 06.01.2000 1.40 A # 5 07.01.2000 -0.20 A # 6 09.01.2000 0.60 A # 7 01.01.2000 0.10 B # 8 02.01.2000 -0.21 B # 9 03.01.2000 -1.20 B # 10 06.01.2000 0.90 B # 11 07.01.2000 0.30 B # 12 09.01.2000 -0.10 B date2 <- c("02.01.2000","03.01.2000","04.01.2000","08.01.2000","05.01.2000","08.01.2000","09.01.2000") news2 <- c("blabla11", "blabla12","blabla13","blabla14","blabla21","blabla22","blabla23") company2 <- c("A","A","A","A","B","B","B") event <- data.frame(date2, news2, company2) event # date2 news2 company2 # 1 02.01.2000 blabla11 A # 2 03.01.2000 blabla12 A # 3 04.01.2000 blabla13 A # 4 08.01.2000 blabla14 A # 5 05.01.2000 blabla21 B # 6 08.01.2000 blabla22 B # 7 09.01.2000 blabla23 B
the output should look like this:
# date2 news2 company2 date1 ret # 1 02.01.2000 blabla11 A 02.01.2000 1.10 # 2 03.01.2000 blabla12 A 03.01.2000 3.00 # 3 04.01.2000 blabla13 A 06.01.2000 1.40 # 4 08.01.2000 blabla14 A 09.01.2000 0.60 # 5 05.01.2000 blabla21 B 06.01.2000 0.90 # 6 08.01.2000 blabla22 B 09.01.2000 -0.10 # 7 09.01.2000 blabla23 B 09.01.2000 -0.10
解决方案It's more or less the same as your other question. The only change is that you'll have to set "company,date" as the key columns to perform the join on (note that the order is important - it'll first sort by company and then by date).
require(data.table) ## 1.9.2 setDT(df) setDT(event) setkey(df, company1, date1) setkey(event, company2, date2) df[, date := date1] df[event, roll=-Inf] company1 date1 ret1 date news2 1: A 02.01.2000 1.1 02.01.2000 blabla11 2: A 03.01.2000 3.0 03.01.2000 blabla12 3: A 04.01.2000 1.4 06.01.2000 blabla13 4: A 08.01.2000 0.6 09.01.2000 blabla14 5: B 05.01.2000 0.9 06.01.2000 blabla21 6: B 08.01.2000 -0.1 09.01.2000 blabla22 7: B 09.01.2000 -0.1 09.01.2000 blabla23
这篇关于跳到合并面板的下一个可用日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!