reshape - R - Pivot the Forecast Result -

i have following source data :

based on past 156 weeks, forecast next 52 weeks. , following code works fine

my.ds <- myds[1, -c(3,4,5,6)] #reading source file my.start <- myds[1, c(3)] my.product <- myds[1, c("product")] my.product <- myds[1, c("location")] my.result <- melt(my.ds, id = c("product","location")) my.result[order(my.result$variable),] my.ts <- ts(my.result$value, frequency=52, start=c(my.start,1)) my.fc <- forecast(my.ts, h=52) my.fc 

forecast gives me following output :

         point forecast        lo 80     hi 80       lo 95     hi 95 2003.000      1637.7675    -8.610502  3284.146  -880.15039  4155.685 2003.019      1453.9059  -195.169681  3102.981 -1068.13753  3975.949 2003.038      8668.6921  7016.923492 10320.461  6142.53000 11194.854 2003.058      5851.0741  4196.616771  7505.531  3320.79997  8381.348 2003.077      4333.9240  2676.782333  5991.066  1799.54453  6868.303 2003.096      4284.5899  2624.768291  5944.412  1746.11178  6823.068 

what :

1. add product & location result set
2. add calculated column : (hi 95) - (point forecast) (i need point forecast column)
3. pivot table following

tried reshape here, not sure how perform since result doesn't seem in table format.

link download source file in csv

following give desired output:

library(forecast) library(reshape) library(plyr)  #exclude non required columns my.ds <- myds[, -c(3,4,5,6)] #set required date, product, location my.start <- myds[1, c(3)] my.product <- myds[1, c("product")] my.location <- myds[1, c("location")] #unpivot table my.result <- melt(my.ds, id = c("product","location")) #run forecasting # set cis want use here, can reuse vector cis <- c(80, 95) # generate forecast using ci levels my.ts <- ts(my.result$value, frequency=52, start=c(my.start,1)) f <- forecast(my.ts, h=52, level=cis) # make data frame containing forecast information, including index z <- as.data.frame(cbind(seq(1:52),                      f$mean,                      reduce(cbind, lapply(seq_along(cis), function(i)   cbind(f$lower[,i], f$upper[,i]))))) # give columns better names names(z) <- c("index", "mean", paste(rep(c("lower", "upper"), times =   length(cis)), rep(cis, each = 2), sep = ".")) # manipulate results describe zw <- z %>%   # keep variable want , index   mutate(sssf = upper.95 - mean) %>%   select(index, mean, sssf) %>%   # add product , location info   mutate(product = my.product,      location = my.location) %>% # rearrange columns it's easier read select(product, location, index, mean, sssf) zw <- melt(zw, id.vars = c("product", "location", "index"), measure.vars = c("mean","sssf")) data.set <- cast(zw, product + location ~ index + variable, value = "value")