Daniel Tan Daniel Tan - 3 months ago 17
R Question

Using Reshape to build a wide matrix from a table of stacked time series

My original data is a table of stacked time-series. Each row records the weekly sales for a particular store and department pair.

An example with 3 unique store-dept pairings:

Store Dept Date Weekly_Sales
------- ------ ----------- --------------
1 1 5/2/2010 1
1 1 12/2/2010 2
1 1 19/2/2010 3
1 2 5/2/2010 4
1 2 12/2/2010 5
1 2 19/2/2010 6
2 1 5/2/2010 7
2 1 12/2/2010 8
2 1 19/2/2010 9


I wish to use the melt and cast methods under the Reshape2 package to transform my table into this format.

Date Store1_Dept1 Store1_Dept2 Store2_Dept1
----------- -------------- -------------- --------------
5/2/2010 1 4 7
12/2/2010 2 5 8
19/2/2010 3 6 9


I have 2 questions to seek help with:


  1. My approach is to melt my original data frame with Date as my id.

    melt.df <- melt.data.frame(df, id.vars='Date')



This approach yields an error, which I do not understand.


Error in as.Date.numeric(value) : 'origin' must be supplied



  1. I am unsure of how to phrase the formula when using the dcast() method to achieve the result that I need.


Answer

We can use dcast from reshape2

library(reshape2)
dcast(train, Date ~ Store + Dept, value.var = "Weekly_Sales") 
Comments