i have dataset looks this:
set.seed(1234) dt<-data.table(id=c(rep(c("a","b","c","d"),5)), year=rep(seq(from = 2010.5,to=2012.5,by = .5),each=4), value=rnorm(20,10,1)) dt id year value 1: 2010.5 8.792934 2: b 2010.5 10.277429 3: c 2010.5 11.084441 4: d 2010.5 7.654302 5: 2011.0 10.429125 6: b 2011.0 10.506056 7: c 2011.0 9.425260 8: d 2011.0 9.453368 9: 2011.5 9.435548 10: b 2011.5 9.109962 11: c 2011.5 9.522807 12: d 2011.5 9.001614 13: 2012.0 9.223746 14: b 2012.0 10.064459 15: c 2012.0 10.959494 16: d 2012.0 9.889715 17: 2012.5 9.488990 18: b 2012.5 9.088805 19: c 2012.5 9.162828 20: d 2012.5 12.415835 i want add 3 columns quite alike, value_previous_6m, value_previous_y , value_next_y each id. row 10 should this:
id year value value_previous_6m value_previous_y value_next_y b 2011.5 9.109962 10.50606 10.27743 9.088805 i avoid plyr function because total dataset quite large.
many in advance, tim
edit: know can done merge function:
set.seed(1234) dt<-data.table(id=c(rep(c("a","b","c","d"),5)), year=rep(seq(from = 2010.5,to=2012.5,by = .5),each=4), value=rnorm(20,10,1)) dt6mp <- copy(dt) dt12mp <- copy(dt) dt6mp[,year:=year-.5] setkey(dt6mp,id,year);setkey(dt,id,year);setnames(dt6mp,"value","value6mp") dt <- merge(dt,dt6mp,all.x=t,all.y=f,allow.cartesian=t) dt12mp[,year:=year-1] setkey(dt12mp,id,year);setkey(dt,id,year);setnames(dt12mp,"value","value12mp") dt <- merge(dt,dt12mp,all.x=t,all.y=f,allow.cartesian=t) dt but think there should nicer method.
you can use approach ( voluntary did not aggregated 3 columns addition make clearer):
dt[,c('value_previous_6m'):=.sd[match(year-0.5, .sd$year),value], id][ ,c('value_previous_y'):= .sd[match(year-1, .sd$year),value], id][ ,c('value_next_y'):=.sd[match(year+1, .sd$year),value], id][] # id year value value_previous_6m value_previous_y value_next_y # 1: 2010.5 8.792934 na na 9.435548 # 2: b 2010.5 10.277429 na na 9.109962 # 3: c 2010.5 11.084441 na na 9.522807 # 4: d 2010.5 7.654302 na na 9.001614 # 5: 2011.0 10.429125 8.792934 na 9.223746 # 6: b 2011.0 10.506056 10.277429 na 10.064459 # 7: c 2011.0 9.425260 11.084441 na 10.959494 # 8: d 2011.0 9.453368 7.654302 na 9.889715 # 9: 2011.5 9.435548 10.429125 8.792934 9.488990 #10: b 2011.5 9.109962 10.506056 10.277429 9.088805 #11: c 2011.5 9.522807 9.425260 11.084441 9.162828 #12: d 2011.5 9.001614 9.453368 7.654302 12.415835 #13: 2012.0 9.223746 9.435548 10.429125 na #14: b 2012.0 10.064459 9.109962 10.506056 na #15: c 2012.0 10.959494 9.522807 9.425260 na #16: d 2012.0 9.889715 9.001614 9.453368 na #17: 2012.5 9.488990 9.223746 9.435548 na #18: b 2012.5 9.088805 10.064459 9.109962 na #19: c 2012.5 9.162828 10.959494 9.522807 na #20: d 2012.5 12.415835 9.889715 9.001614 na 
Comments
Post a Comment