Retrieve set values for each group variable in panel data
I have a panel dataset with a lot of groups. I have calculated the set values for each group and I would like to combine all set values into a new dataset. I am looking for a possible shortcut to avoid having to do this manually.
The next dataset is similar to the one I'm working on (much smaller scale by group).
set.seed(999)
dt <- data.frame("Group"=rep((LETTERS[1:10]), each=15),
"Year"=2001:2015,"value"=5+rnorm(150, 3,1))
names(dt)
head(dt)
table(dt$Year, dt$Group)
library(reshape2)
dt_tbl1 <- dcast(dt,Year~Group)
dt_tbl1
library(forecast)
tsMat <- ts(dcast(dt, Year ~ Group), start=2001, freq=1)
dt_ses <- lapply(tsMat, function(x) ses(x))
I am looking for some help to automate the next step. Add all other groups to the data frame.
dt_tbl2 <- data.frame("Year"=2001:2015,
data.frame(dt_ses$A$fitted),
data.frame(dt_ses$B$fitted),
data.frame(dt_ses$C$fitted))
And rename the variables in the new dataset to be in the original groups
names(dt_tbl2)[2:4] <- c("A_hat", "B_hat", "C_hat")
Once this is complete, dt_tbl2 should have the same format as dt_tbl1.
I tried using sapply () and lapply () but nothing seems to work. thanks to TCS
source to share
dt_tbl = data.frame(Year = c(tsMat[,1]),
sapply(colnames(tsMat)[-1], function(col) {dt_ses[[col]]$fitted}))
names(dt_tbl)[-1] = paste0(names(dt_tbl)[-1], "_hat")
Year A_hat B_hat C_hat D_hat E_hat F_hat G_hat H_hat I_hat J_hat
1 2001 7.618084 7.521736 8.709448 7.967254 8.096049 7.932307 7.997542 7.552510 7.855070 8.136634
2 2002 7.662074 7.521647 9.150131 7.967285 8.095947 7.932320 7.997708 7.552295 7.855037 8.136680
3 2003 7.234079 7.521702 9.005576 7.967083 8.096054 7.932381 7.997711 7.552369 7.855063 8.136535
4 2004 7.919614 7.521760 8.787972 7.967088 8.096149 7.932181 7.997613 7.552552 7.854865 8.136433
5 2005 8.073512 7.521898 8.865025 7.967149 8.096250 7.932232 7.997742 7.552446 7.854703 8.136329
6 2006 7.919455 7.521738 8.572195 7.967149 8.096238 7.932222 7.997629 7.552423 7.854840 8.136423
7 2007 7.706265 7.521663 7.864789 7.967141 8.096114 7.932165 7.997734 7.552438 7.854872 8.136405
8 2008 7.010270 7.521775 7.802812 7.967079 8.095963 7.932270 7.997677 7.552331 7.854913 8.136533
9 2009 6.888603 7.521787 7.992457 7.967257 8.095926 7.932154 7.997648 7.552284 7.854947 8.136511
10 2010 6.951684 7.521864 8.130820 7.967297 8.095874 7.932169 7.997624 7.552245 7.855069 8.136587
11 2011 6.919762 7.521800 7.726980 7.967294 8.095964 7.932056 7.997646 7.552268 7.855106 8.136625
12 2012 7.976188 7.521912 7.670982 7.967325 8.095944 7.932025 7.997481 7.552206 7.855076 8.136553
13 2013 8.045479 7.521849 7.759875 7.967385 8.095928 7.932168 7.997529 7.552346 7.855074 8.136648
14 2014 8.437745 7.521808 7.485442 7.967260 8.096013 7.932149 7.997449 7.552503 7.855118 8.136644
15 2015 8.321283 7.521701 7.328202 7.967307 8.095962 7.932257 7.997381 7.552420 7.855088 8.136548
source to share
You can try transpose
from a package purrr
that transports nested lists.
library(purrr)
t_dt_ses <- transpose(dt_ses)
dt_tbl3 <- data.frame(t_dt_ses$fitted)
# update the year column
dt_tbl3$Year<- 2001:2015
head(dt_tbl3)
# Year A B C D E F G H I J
# 1 2001 7.618084 7.521736 8.709448 7.967254 8.096049 7.932307 7.997542 7.552510 7.855070 8.136634
# 2 2002 7.662074 7.521647 9.150131 7.967285 8.095947 7.932320 7.997708 7.552295 7.855037 8.136680
# 3 2003 7.234079 7.521702 9.005576 7.967083 8.096054 7.932381 7.997711 7.552369 7.855063 8.136535
# 4 2004 7.919614 7.521760 8.787972 7.967088 8.096149 7.932181 7.997613 7.552552 7.854865 8.136433
# 5 2005 8.073512 7.521898 8.865025 7.967149 8.096250 7.932232 7.997742 7.552446 7.854703 8.136329
# 6 2006 7.919455 7.521738 8.572195 7.967149 8.096238 7.932222 7.997629 7.552423 7.854840 8.136423
source to share