R: Smoothing Time Series Data by Item

Question

I have a data series that displays purchases over time by item ID. Purchasing habits are irregular, so I would like to smooth this data out over time and by item ID.

If items had orders placed more regularly (i.e. Every day) we could better plot/evaluate our ordering and set stocking levels. However, some people will purchase excess of an item so they don't have to restock. This then is skewing our par level data (Since a 1 day total could really be a week's worth of product since they could only be ordering once per week.

Reproducible Example:

POData <- structure(list(a = structure(c(1499918400, 1499918400, 1499918400, 
1499918400, 1499918400, 1499918400, 1496376000, 1497412800, 1497412800, 
1497412800, 1497412800, 1497412800, 1497240000, 1497412800, 1497412800, 
1497412800, 1501214400, 1496376000, 1496376000, 1496376000, 1496289600, 
1496289600, 1496289600, 1496289600, 1496289600, 1496289600, 1501214400, 
1501214400, 1501214400, 1501214400), class = c("POSIXct", "POSIXt"
), tzone = ""), b = c(446032L, 101612L, 37740L, 482207L, 152360L, 
4483L, 482207L, 141729L, 81192L, 482207L, 85273L, 142955L, 460003L, 
142955L, 17752L, 29763L, 309189L, 361905L, 17396L, 410762L, 437420L, 
17752L, 18002L, 150698L, 163342L, 433332L, 150587L, 44159L, 433332L, 
446032L), c = c(4, 1, 25, 1, 1, 1, 3, 12, 12, 1, 1, 1, 300, 1, 
1, 2, 6, 6, 2, 1, 1, 1, 1, 1, 1, 1, 40, 2, 1, 2)), .Names = c("PO Date", 
"PS Item ID", "PO Qty"), row.names = c(NA, 30L), class = "data.frame")

This is probably a simple question, but I hope someone has a simple way to do this.

Answer 1

You could use something like this

require(zoo)
require(dply)

    df2 = POData %>%
  arrange(`PS Item ID`,`PO Date`)%>%
  group_by(`PS Item ID`)%>%
  mutate(temp_lag1 = lag( `PO Qty`))%>%
  mutate(temp.5.previous = rollapply(data = temp_lag1, 
                                     width = 2, 
                                     FUN = mean, 
                                     align = "left", 
                                     fill =  `PO Qty`, 
                                     na.rm = T))

It essentially groups by PS Item ID and arranges by PS Item ID and PO Date. The width argument in mutate specifies how far you would like to go back for a moving average. As of now its set to 1 because your data is not that extensive by product ID.