converting dataframe to time series in R

Question

I have a dataframe :

 > dsa[1:20]
           Ordered.Item       date      Qty
 1: 2011001FAM002025001 2019-06-01 19440.00
 2: 2011001FAM002025001 2019-05-01 24455.53
 3: 2011001FAM002025001 2019-04-01 16575.06
 4: 2011001FAM002025001 2019-03-01   880.00
 5: 2011001FAM002025001 2019-02-01  5000.00
 6: 2011001FAM002035001 2019-04-01   175.00
 7: 2011001FAM004025001 2019-06-01  2000.00
 8: 2011001FAM004025001 2019-05-01  2500.00
 9: 2011001FAM004025001 2019-04-01  3000.00
10: 2011001FAM012025001 2019-06-01  1200.00
11: 2011001FAM012025001 2019-04-01  1074.02
12: 2011001FAM022025001 2019-06-01   350.00
13: 2011001FAM022025001 2019-05-01   110.96
14: 2011001FAM022025001 2019-04-01   221.13
15: 2011001FAM022035001 2019-06-01   500.00
16: 2011001FAM022035001 2019-05-01    18.91
17: 2011001FAM027025001 2019-06-01   210.00
18: 2011001FAM028025001 2019-04-01   327.21
19: 2011001FBK005035001 2019-05-01   500.00
20: 2011001FBL001025001 2019-06-01 15350.00



 >str(dsa)
Classes ‘data.table’ and 'data.frame':  830 obs. of  3 variables:
 $ Ordered.Item: Factor w/ 435 levels "2011001FAM002025001",..: 1 1 1 1 1 2 3 3 3 4 ...
 $ date        : Date, format: "2019-06-01" "2019-05-01" "2019-04-01" ...
 $ Qty         : num  19440 24456 16575 880 5000 ...
 - attr(*, ".internal.selfref")=<externalptr> 

this data contains sku and it's quantity sold per month

Because i plan to use ARIMA forecasting i am trying to convert the dataframe to time series but i get a weird output

> timesr<-ts(data=dsa,start=c(12,2018),frequency = 12)
> head(timesr)
     Ordered.Item  date      Qty
[1,]            1 18048 19440.00
[2,]            1 18017 24455.53
[3,]            1 17987 16575.06
[4,]            1 17956   880.00
[5,]            1 17928  5000.00
[6,]            2 17987   175.00

Answer 1

You might try something like this for your sku ARIMA modeling.

# Create dataframe
dsa = read.table(text = '
  ID        Ordered.Item       date      Qty
 1 2011001FAM002025001 2019-06-01 19440.00
 2 2011001FAM002025001 2019-05-01 24455.53
 3 2011001FAM002025001 2019-04-01 16575.06
 4 2011001FAM002025001 2019-03-01   880.00
 5 2011001FAM002025001 2019-02-01  5000.00
 6 2011001FAM002035001 2019-04-01   175.00
 7 2011001FAM004025001 2019-06-01  2000.00
 8 2011001FAM004025001 2019-05-01  2500.00
 9 2011001FAM004025001 2019-04-01  3000.00
10 2011001FAM012025001 2019-06-01  1200.00
11 2011001FAM012025001 2019-04-01  1074.02
12 2011001FAM022025001 2019-06-01   350.00
13 2011001FAM022025001 2019-05-01   110.96
14 2011001FAM022025001 2019-04-01   221.13
15 2011001FAM022035001 2019-06-01   500.00
16 2011001FAM022035001 2019-05-01    18.91
17 2011001FAM027025001 2019-06-01   210.00
18 2011001FAM028025001 2019-04-01   327.21
19 2011001FBK005035001 2019-05-01   500.00
20 2011001FBL001025001 2019-06-01 15350.00
', header = T)
dsa$ID <- NULL

# Reshape 
dsa2 <- reshape(data=dsa,idvar="date", v.names = "Qty", timevar = "Ordered.Item", direction="wide")
dsa2 <- dsa2[order(as.Date(dsa2$date, "%Y-%m-%d")),] # Sort by date

# Predict for sku 2011001FAM002025001
fit <- auto.arima(ts(dsa2$Qty.2011001FAM002025001))
fcast <- forecast(fit, h=60) # forecast 60 periods ahead
plot(fcast)