3

I have a panel data set with a large number of groups. I calculated fitted values for each group and I would like to combine all fitted values into a new data set. I am looking for a possible shortcut to avoid having to do this manually.

The following data set is similar to the one I am working on (much smaller scale in terms of groups though).

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 following step. Add all remaining groups in 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 data set to relate to the original groups

names(dt_tbl2)[2:4] <- c("A_hat", "B_hat", "C_hat")

Once this is completed the dt_tbl2 should have the same format like the dt_tbl1.

I have tried to use sapply() and lapply() but nothing seems to be working. Thanks TCS

TCS
  • 127
  • 1
  • 11

2 Answers2

1
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
eipi10
  • 91,525
  • 24
  • 209
  • 285
1

You can try transpose from purrr package, which transposes 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
shaojl7
  • 565
  • 4
  • 13