How to prepare month by month time series data in R from aggregated data containing part wise sales vs month?

0
votes

I am having a dataframe which consists of month wise sales data for many parts: For eg

Partno Month Qty

Part 1 June 2019 20

Part 1 July 2019 25

Part 1 Sep 2019 30

Part 2 Mar 2019 45

Part 3 Aug 2019 40

Part 3 Nov 2019 21

I want to convert this data into a month by month time series, which makes it easier for time series forecasting, Once I make it into a ts object

Month Part1 Part 2 Part 3

Jan 0 0 0

Feb 0 0 0

Mar 0 45 0

Apr 0 0 0

May 0 0 0

June 20 0 0

July 25 0 0

Aug 0 0 0

Sept 0 30 0

Oct 0 0 20

Nov 0 0 21

Dec 0 0 0

I am quite baffled as to how this can be carried out in R. Any solutions for the same would be highly useful, as I plan build some forecasting models in R.

Looking forward to hearing from you all!

1
dataframetime-seriesdata-sciencedata-cleaningforecasting
Pictures of text are less helpful. Please undo your last edit and follow the path of appropriate formatting as outlined by a helpful user in the preceeding edit. – Yunnosch
Can some one help me do this? I am new to stack overflow. – Akhilnandh Ramesh
Please read the instructions at the top of the r tag on how to ask a question. – G. Grothendieck

1 Answers

0
votes

Assume the data DF shown reproducibly in the Note at the end.

First convert DF to zoo splitting it by the first column and converting the Month column to yearmon class. Then convert that to ts class, extend it to Jan to Dec, and set any NAs to 0. (If you don't need the 0 months at the beginning and end omit the yrs and window lines.)

library(zoo)

z <- read.zoo(DF, split = 1, index = 2, FUN = as.yearmon, format = "%b %Y")
tt <- as.ts(z)
yrs <- as.integer(range(time(tt))) # start and end years
tt <- window(tt, start = yrs[1], end = yrs[2] + 11/12, extend = TRUE)
tt[is.na(tt)] <- 0
tt

giving:

         Part 1 Part 2 Part 3
Jan 2019      0      0      0
Feb 2019      0      0      0
Mar 2019      0     45      0
Apr 2019      0     20      0
May 2019      0      0      0
Jun 2019     20      0      0
Jul 2019     25      0      0
Aug 2019      0      0      0
Sep 2019     30      0      0
Oct 2019      0      0     20
Nov 2019      0      0     21
Dec 2019      0      0      0

Note

Lines <- "Partno, Month, Qty
Part 1, Jun 2019, 20
Part 1, Jul 2019, 25
Part 1, Sep 2019, 30
Part 2, Mar 2019, 45
Part 2, Apr 2019, 20
Part 3, Oct 2019, 20
Part 3, Nov 2019, 21"
DF <- read.csv(text = Lines, strip.white = TRUE)