是否可以将带有滚动窗口的函数应用于此数据帧:
Name Date market_return stock_return RESULT
AAL 1.1.15 3% 5%
AAL 2.1.15 2% 1%
...
AAPL 1.1.15 3% 4%
AAPL 2.1.15 2% 3%
...
但是,只有在匹配这些特定日期和名称时:
Name Date
AAL 4.4.15
AAL 15.6.15
...
AAPL 5.6.15
AAPL 5.7.15
...
结果应该看起来像:
Name Date market_return stock_return RESULT
AAL 1.1.15 3% 5% NA
AAL 2.1.15 2% 1% NA
...
AAL 4.4.15 3% 5% xxx
...
AAL 15.6.15 3% 5% xxx
...
AAPL 1.1.15 3% 4% NA
AAPL 2.1.15 2% 3% NA
...
AAPL 5.6.15 2% 5% xxx
...
AAPL 5.7.15 2% 5% xxx
...
谢谢!
这个问题并不是关于要执行的操作的具体问题,所以我们假设它是sum。
在最后的注释中,我们首先将数据读取为数据帧,将Date
列转换为Date
类,将_return
列转换为数字。
在Name和Date上左联接DF到DF2,在Date range和Name上左联接DF2到DF,然后求和DF的第二个实例中联接到DF的第一个实例的每一行的所有stock_returns。
library(sqldf)
sqldf("select a.*, sum(b.stock_return) as RESULT
from DF a
left join DF2 c on a.Name = c.Name and a.Date = c.Date
left join DF b on b.Date between c.Date - 185 and c.Date + 185 and
c.Name = b.Name
group by a.rowid")
给予:
Name Date market_return stock_return RESULT
1 AAL 2015-01-01 3 3 NA
2 AAL 2015-01-02 2 2 NA
3 AAL 2015-04-04 3 3 11
4 AAL 2015-06-15 3 3 11
5 AAPL 2015-01-01 3 3 NA
6 AAPL 2015-01-02 2 2 NA
7 AAPL 2015-06-05 2 2 9
8 AAPL 2015-07-05 2 2 9
备注
Lines <- "Name Date market_return stock_return
AAL 1.1.15 3% 5%
AAL 2.1.15 2% 1%
AAL 4.4.15 3% 5%
AAL 15.6.15 3% 5%
AAPL 1.1.15 3% 4%
AAPL 2.1.15 2% 3%
AAPL 5.6.15 2% 5%
AAPL 5.7.15 2% 5%"
DF <- read.table(text = Lines, header = TRUE, as.is = TRUE)
DF <- transform(DF, Date = as.Date(Date, "%d.%m.%y"),
market_return = as.numeric(sub("%", "", market_return)),
stock_return = as.numeric(sub("%", "", stock_return)))
Lines <- "Name Date
AAL 4.4.15
AAL 15.6.15
AAPL 5.6.15
AAPL 5.7.15"
DF2 <- read.table(text = Lines, header = TRUE, as.is = TRUE)
DF2$Date <- as.Date(DF2$Date, "%d.%m.%y")
更新
简化。