简单一次分组
data()
getwd()
setwd("e:/R/data")
rm(O)
ri<-merge(r1,r2,by="SJ")
rii<-merge(ri,r3,by="SJ")
write.csv(a1b1p,"a1b1p.csv",row.names=FALSE)
remove(o)
setwd("e:/R/data")
library("readxl")
library("dplyr")
library("plyr")
load(".RData")
r1<-read_excel("r1.xlsx",sheet = 1)
cd<-read_excel("code.xlsx",sheet = 1)
r1<-anti_join(r1,cd,by="Stkcd")
r2<-r1%>%filter(Mretwd!="NA"&Mretwd!=0)%>%filter(Msmvosd!="NA"&Msmvosd!=0)
BM<-read_excel("BM.xlsx",sheet = 1)
OP<-read_excel("OP.xlsx",sheet = 2)
INV<-read_excel("INV.xlsx",sheet = 2)
r3<-left_join(r2,BM,by="SJ")%>%left_join(OP,by="SJ")%>%left_join(INV,by="SJ")
BV<-read_excel("BV.xlsx",sheet = 1)
BV<-select(BV,3:4)
BV1<-filter(BV,BV<0)%>%select(1)
r4<-anti_join(r3,BV1,by="SJ")
r8<-arrange(r4,desc(Mretwd))
write.csv(r8,"r8.csv",row.names = FALSE)
r9<-select(arrange(filter(r8,AC==199606),Msmvosd), -(BM:INV))
a1<-slice(r9,1:(nrow(r9)/5))
a2<-slice(r9,(nrow(r9)/5):(nrow(r9)/5*2))
a3<-slice(r9,(nrow(r9)/5*2):(nrow(r9)/5*3))
a4<-slice(r9,(nrow(r9)/5*3):(nrow(r9)/5*4))
a5<-slice(r9,(nrow(r9)/5*4):n())
r10<-select(filter(r8,AC==199512), -(AC:Markettype))
a11<-left_join(a1,r10,by="Stkcd")
a1b0<-arrange(a11,BM)
a1b0<-filter(a1b0,BM!="NA")
a1b1<-slice(a1b0,1:(nrow(a1b0)/5))
a1b2<-slice(a1b0,(nrow(a1b0)/5):(nrow(a1b0)/5*2))
a1b3<-slice(a1b0,(nrow(a1b0)/5*2):(nrow(a1b0)/5*3))
a1b4<-slice(a1b0,(nrow(a1b0)/5*3):(nrow(a1b0)/5*4))
a1b5<-slice(a1b0,(nrow(a1b0)/5*4):n())
r11<-filter(r8,AC>199506)
r11<-filter(r11,AC<199607)
a1b1<-semi_join(r11,a1b1,by ="Stkcd")
a1b1p<-ddply(a1b1,.(AC),summarize,SY=weighted.mean(Mretwd, Msmvosd))
滚动循环分组
for(i in 0:19){assign(paste("a1b1",i+1,sep=""),
ddply(semi_join(filter(filter(r8,AC>(199506+100*i)),(AC<199607+100*i)),
slice(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==(199606+100*i)),Msmvosd), -(BM:INV))
,1:(nrow(select(arrange(filter(r8,AC==(199606+100*i)),Msmvosd), -(BM:INV)))/5)),
select(filter(r8,AC==(199512+100*i)), -(AC:Markettype)),by="Stkcd"),
OP),OP!="NA"),1:(nrow(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==(199606+100*i)),Msmvosd),
-(BM:INV)),1:(nrow(select(arrange(filter(r8,AC==(199606+100*i)),Msmvosd), -(BM:INV)))/5)),
select(filter(r8,AC==(199512+100*i)), -(AC:Markettype)),by="Stkcd"),OP)
,OP!="NA"))/5)),by ="Stkcd"),.(AC),summarize,SY=weighted.mean(Mretwd, Msmvosd)))}
a1b121<-ddply(semi_join(filter(filter(r8,AC>201506),(AC<201600)),
slice(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV))
,1:(nrow(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)))/5)),
select(filter(r8,AC==201412), -(AC:Markettype)),by="Stkcd"),
OP),OP!="NA"),1:(nrow(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==201506),Msmvosd),
-(BM:INV)),1:(nrow(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)))/5)),
select(filter(r8,AC==201412), -(AC:Markettype)),by="Stkcd"),OP)
,OP!="NA"))/5)),by ="Stkcd"),.(AC),summarize,SY=weighted.mean(Mretwd, Msmvosd))
a1b1<-do.call("rbind",lapply(paste0("a1b1", 1:21), function(a1b1) eval(as.name(a1b1))))
for( o in 2:5){
for(i in 0:19){assign(paste("a1b",o,i+1,sep=""),
ddply(semi_join(filter(filter(r8,AC>(199506+100*i)),(AC<199607+100*i)),
slice(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==(199606+100*i)),Msmvosd), -(BM:INV))
,1:(nrow(select(arrange(filter(r8,AC==(199606+100*i)),Msmvosd), -(BM:INV)))/5)),
select(filter(r8,AC==(199512+100*i)), -(AC:Markettype)),by="Stkcd"),
OP),OP!="NA"),(nrow(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==(199606+100*i)),Msmvosd),
-(BM:INV)),1:(nrow(select(arrange(filter(r8,AC==(199606+100*i)),Msmvosd), -(BM:INV)))/5)),
select(filter(r8,AC==(199512+100*i)), -(AC:Markettype)),by="Stkcd"),OP),OP!="NA"))/5*(o-1)):
(nrow(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==(199606+100*i)),Msmvosd), -(BM:INV)),
1:(nrow(select(arrange(filter(r8,AC==(199606+100*i)),Msmvosd), -(BM:INV)))/5)),
select(filter(r8,AC==(199512+100*i)), -(AC:Markettype)),by="Stkcd"),OP),OP!="NA"))/5*o)),by ="Stkcd"),.(AC),
summarize,SY=weighted.mean(Mretwd, Msmvosd)))}
assign(paste("a1b",o,"21",sep=""),
ddply(semi_join(filter(filter(r8,AC>(201506)),(AC<201600)),
slice(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==(201506)),Msmvosd), -(BM:INV))
,1:(nrow(select(arrange(filter(r8,AC==(201506)),Msmvosd), -(BM:INV)))/5)),
select(filter(r8,AC==(201412)), -(AC:Markettype)),by="Stkcd"),
OP),OP!="NA"),(nrow(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==(201506)),Msmvosd),
-(BM:INV)),1:(nrow(select(arrange(filter(r8,AC==(201506)),Msmvosd), -(BM:INV)))/5)),
select(filter(r8,AC==(201412)), -(AC:Markettype)),by="Stkcd"),OP),OP!="NA"))/5*(o-1)):
(nrow(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==(201506)),Msmvosd), -(BM:INV)),
1:(nrow(select(arrange(filter(r8,AC==(201506)),Msmvosd), -(BM:INV)))/5)),
select(filter(r8,AC==(201412)), -(AC:Markettype)),by="Stkcd"),OP),OP!="NA"))/5*o)),by ="Stkcd"),.(AC),
summarize,SY=weighted.mean(Mretwd, Msmvosd)))}
a1b2<-do.call("rbind",lapply(paste0("a1b2", 1:21),
function(a1b2) eval(as.name(a1b2))))
a1b3<-do.call("rbind",lapply(paste0("a1b3", 1:21),
function(a1b3) eval(as.name(a1b3))))
a1b4<-do.call("rbind",lapply(paste0("a1b4", 1:21),
function(a1b4) eval(as.name(a1b4))))
a1b5<-do.call("rbind",lapply(paste0("a1b5", 1:21),
function(a1b5) eval(as.name(a1b5))))
for(p in 2:5){
for(i in 0:19){assign(paste("a",p,"b1",i+1,sep=""),
ddply(semi_join(filter(filter(r8,AC>(199506+100*i)),(AC<199607+100*i)),
slice(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==199606+100*i),Msmvosd), -(BM:INV)),
(nrow(select(arrange(filter(r8,AC==199606+100*i),Msmvosd), -(BM:INV)))/5*(p-1)):(nrow(select(arrange(filter(r8,AC==199606+100*i),Msmvosd), -(BM:INV)))/5*p)),
select(filter(r8,AC==(199512+100*i)), -(AC:Markettype)),by="Stkcd"),
OP),OP!="NA"),1:(nrow(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==199606+100*i),Msmvosd), -(BM:INV)),
(nrow(select(arrange(filter(r8,AC==199606+100*i),Msmvosd), -(BM:INV)))/5*(p-1)):(nrow(select(arrange(filter(r8,AC==199606+100*i),Msmvosd), -(BM:INV)))/5*p)),
select(filter(r8,AC==(199512+100*i)), -(AC:Markettype)),by="Stkcd"),OP)
,OP!="NA"))/5)),by ="Stkcd"),.(AC),summarize,SY=weighted.mean(Mretwd, Msmvosd)))}
assign(paste("a",p,"b121",sep=""),
ddply(semi_join(filter(filter(r8,AC>(201506)),(AC<201600)),
slice(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)),
(nrow(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)))/5*(p-1)):(nrow(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)))/5*p)),
select(filter(r8,AC==(201412)), -(AC:Markettype)),by="Stkcd"),
OP),OP!="NA"),1:(nrow(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)),
(nrow(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)))/5*(p-1)):(nrow(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)))/5*p)),
select(filter(r8,AC==(201412)), -(AC:Markettype)),by="Stkcd"),OP)
,OP!="NA"))/5)),by ="Stkcd"),.(AC),summarize,SY=weighted.mean(Mretwd, Msmvosd)))}
a2b1<-do.call("rbind",lapply(paste0("a2b1", 1:21),
function(a2b1) eval(as.name(a2b1))))
a3b1<-do.call("rbind",lapply(paste0("a3b1", 1:21),
function(a3b1) eval(as.name(a3b1))))
a4b1<-do.call("rbind",lapply(paste0("a4b1", 1:21),
function(a4b1) eval(as.name(a4b1))))
a5b1<-do.call("rbind",lapply(paste0("a5b1", 1:21),
function(a5b1) eval(as.name(a5b1))))
for(o in 2:5){
for(p in 2:5){
for(i in 0:19){assign(paste("a",p,"b",o,i+1,sep=""),
ddply(semi_join(filter(filter(r8,AC>(199506+100*i)),(AC<199607+100*i)),
slice(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==199606+100*i),Msmvosd), -(BM:INV)),
(nrow(select(arrange(filter(r8,AC==199606+100*i),Msmvosd), -(BM:INV)))/5*(p-1)):(nrow(select(arrange(filter(r8,AC==199606+100*i),Msmvosd), -(BM:INV)))/5*p)),
select(filter(r8,AC==(199512+100*i)), -(AC:Markettype)),by="Stkcd"),
OP),OP!="NA"),(nrow(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==199606+100*i),Msmvosd), -(BM:INV)),
(nrow(select(arrange(filter(r8,AC==199606+100*i),Msmvosd), -(BM:INV)))/5*(p-1)):(nrow(select(arrange(filter(r8,AC==199606+100*i),Msmvosd), -(BM:INV)))/5*p)),
select(filter(r8,AC==(199512+100*i)), -(AC:Markettype)),by="Stkcd"),OP)
,OP!="NA"))/5*(o-1)):(nrow(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==199606+100*i),Msmvosd), -(BM:INV)),
(nrow(select(arrange(filter(r8,AC==199606+100*i),Msmvosd), -(BM:INV)))/5*(p-1)):(nrow(select(arrange(filter(r8,AC==199606+100*i),Msmvosd), -(BM:INV)))/5*p)),
select(filter(r8,AC==(199512+100*i)), -(AC:Markettype)),by="Stkcd"),OP)
,OP!="NA"))/5*o)),by ="Stkcd"),.(AC),summarize,SY=weighted.mean(Mretwd, Msmvosd)))}
assign(paste("a",p,"b",o,"21",sep=""),
ddply(semi_join(filter(filter(r8,AC>(201506)),(AC<201600)),
slice(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)),
(nrow(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)))/5*(p-1)):(nrow(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)))/5*p)),
select(filter(r8,AC==(201412)), -(AC:Markettype)),by="Stkcd"),
OP),OP!="NA"),(nrow(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)),
(nrow(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)))/5*(p-1)):(nrow(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)))/5*p)),
select(filter(r8,AC==(201412)), -(AC:Markettype)),by="Stkcd"),OP)
,OP!="NA"))/5*(o-1)):(nrow(filter(arrange(left_join(slice(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)),
(nrow(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)))/5*(p-1)):(nrow(select(arrange(filter(r8,AC==201506),Msmvosd), -(BM:INV)))/5*p)),
select(filter(r8,AC==(201412)), -(AC:Markettype)),by="Stkcd"),OP)
,OP!="NA"))/5*o)),by ="Stkcd"),.(AC),summarize,SY=weighted.mean(Mretwd, Msmvosd)))}}
a2b2<-do.call("rbind",lapply(paste0("a2b2", 1:21), function(a2b2) eval(as.name(a2b2))))
a2b3<-do.call("rbind",lapply(paste0("a2b3", 1:21), function(a2b3) eval(as.name(a2b3))))
a2b4<-do.call("rbind",lapply(paste0("a2b4", 1:21), function(a2b4) eval(as.name(a2b4))))
a2b5<-do.call("rbind",lapply(paste0("a2b5", 1:21), function(a2b5) eval(as.name(a2b5))))
a3b2<-do.call("rbind",lapply(paste0("a3b2", 1:21), function(a3b2) eval(as.name(a3b2))))
a3b3<-do.call("rbind",lapply(paste0("a3b3", 1:21), function(a3b3) eval(as.name(a3b3))))
a3b4<-do.call("rbind",lapply(paste0("a3b4", 1:21), function(a3b4) eval(as.name(a3b4))))
a3b5<-do.call("rbind",lapply(paste0("a3b5", 1:21), function(a3b5) eval(as.name(a3b5))))
a4b2<-do.call("rbind",lapply(paste0("a4b2", 1:21), function(a4b2) eval(as.name(a4b2))))
a4b3<-do.call("rbind",lapply(paste0("a4b3", 1:21), function(a4b3) eval(as.name(a4b3))))
a4b4<-do.call("rbind",lapply(paste0("a4b4", 1:21), function(a4b4) eval(as.name(a4b4))))
a4b5<-do.call("rbind",lapply(paste0("a4b5", 1:21), function(a4b5) eval(as.name(a4b5))))
a5b2<-do.call("rbind",lapply(paste0("a5b2", 1:21), function(a5b2) eval(as.name(a5b2))))
a5b3<-do.call("rbind",lapply(paste0("a5b3", 1:21), function(a5b3) eval(as.name(a5b3))))
a5b4<-do.call("rbind",lapply(paste0("a5b4", 1:21), function(a5b4) eval(as.name(a5b4))))
a5b5<-do.call("rbind",lapply(paste0("a5b5", 1:21), function(a5b5) eval(as.name(a5b5))))
分组加权平均:
a2b3111<-a2b31%>%group_by(AC)%>%summarise(SY=weighted.mean(Mretwd, Msmvosd))
a<-paste0("a",1,"b",2)
t<-list(a1b1,a1b2,a1b3,a1b4,a1b5,a2b1,a2b2,a2b3,a2b4,a2b5,a3b1,a3b2,a3b3,a3b4,a3b5,a4b1,a4b2,a4b3,a4b4,a4b5,a5b1,a5b2,a5b3,a5b4,a5b5)
w<-"a1b1,a1b2,a1b3,a1b4,a1b5,a2b1,a2b2,a2b3,a2b4,a2b5,a3b1,a3b2,a3b3,a3b4,a3b5,a4b1,a4b2,a4b3,a4b4,a4b5,a5b1,a5b2,a5b3,a5b4,a5b5"
w<-gsub("b","d",w)
tt<-list(a1c1,a1c2,a1c3,a1c4,a1c5,a2c1,a2c2,a2c3,a2c4,a2c5,a3c1,a3c2,a3c3,a3c4,a3c5,a4c1,a4c2,a4c3,a4c4,a4c5,a5c1,a5c2,a5c3,a5c4,a5c5)
ttt<-list(a1d1,a1d2,a1d3,a1d4,a1d5,a2d1,a2d2,a2d3,a2d4,a2d5,a3d1,a3d2,a3d3,a3d4,a3d5,a4d1,a4d2,a4d3,a4d4,a4d5,a5d1,a5d2,a5d3,a5d4,a5d5)
aop<-do.call(cbind,t)
LY<-read_excel("LY.xlsx",sheet = 1)
aop<-cbind(t,LY)
write.csv(aop,"e:/R/data/aop.csv",row.names = FALSE)
aop<-read_excel("e:/R/data/aop.xlsx",sheet = 1)
LY<-read_excel("e:/R/data/aop.xlsx",sheet = 2)
AC<-read_excel("e:/R/data/aop.xlsx",sheet = 3)
aop<-cbind(AC,aop*100-LY)
apply(aop,2,mean)