Imputation model for time series missing data in R

Question

Time series data consists of:

Product (categorical); ProductGroup (categorical); Country (categorical); YearSinceProductLaunch (numeric); SalesAtLaunchYear (numeric)

Only "SalesAtLaunchYear" data has some missing values which needs to be imputed.

For some products, there is complete data, i.e. sales data exists for the launch year 1,2 and up to now.

Some other products, however, contain missing sales data only for the early years since launch. Products have different age, therefore sometimes there are 2 years since launch that are missing, sometimes there are 10 years, which depends on the product.

I am interested to find a model in R that can impute the missing time series data gaps. I have tried MICE by setting the model for "SalesAtLaunchYear" as random forest, but I am still getting some very high values of sales especially at the beginning of the product's launch. I am ensuring that at Year 0, all sales are 0 to avoid negative values. The data frame has 20000 rows with 300 unique products.

testdf = tibble::tribble(
  ~Country,   ~ProductGroup,   ~Product, ~YearSinceProductLaunch, ~SalesAtLaunchYear,
      "CA", "ProductGroup1", "Product1",                      0L,                  0,
      "CA", "ProductGroup1", "Product1",                      1L,                 NA,
      "CA", "ProductGroup1", "Product1",                      2L,                 NA,
      "CA", "ProductGroup1", "Product1",                      3L,                 NA,
      "CA", "ProductGroup1", "Product1",                      4L,                 NA,
      "CA", "ProductGroup1", "Product1",                      5L,        206034.9814,
      "CA", "ProductGroup1", "Product1",                      6L,        170143.2623,
      "CA", "ProductGroup1", "Product1",                      7L,        212541.9306,
      "CA", "ProductGroup1", "Product1",                      8L,         270663.199,
      "CA", "ProductGroup1", "Product1",                      9L,        736738.3755,
      "CA", "ProductGroup1", "Product1",                     10L,        2579723.981,
      "CA", "ProductGroup1", "Product1",                     11L,        4964319.496,
      "CA", "ProductGroup1", "Product1",                     12L,         6864985.16,
      "CA", "ProductGroup1", "Product1",                     13L,        8793292.386,
      "CA", "ProductGroup1", "Product1",                     14L,        11416033.38,
      "IT", "ProductGroup2", "Product2",                      0L,                  0,
      "IT", "ProductGroup2", "Product2",                      1L,                 NA,
      "IT", "ProductGroup2", "Product2",                      2L,                 NA,
      "IT", "ProductGroup2", "Product2",                      3L,                 NA,
      "IT", "ProductGroup2", "Product2",                      4L,                 NA,
      "IT", "ProductGroup2", "Product2",                      5L,                 NA,
      "IT", "ProductGroup2", "Product2",                      6L,                 NA,
      "IT", "ProductGroup2", "Product2",                      7L,                 NA,
      "IT", "ProductGroup2", "Product2",                      8L,                 NA,
      "IT", "ProductGroup2", "Product2",                      9L,                 NA,
      "IT", "ProductGroup2", "Product2",                     10L,                 NA,
      "IT", "ProductGroup2", "Product2",                     11L,                 NA,
      "IT", "ProductGroup2", "Product2",                     12L,                 NA,
      "IT", "ProductGroup2", "Product2",                     13L,        30806222.96,
      "IT", "ProductGroup2", "Product2",                     14L,           31456272,
      "IT", "ProductGroup2", "Product2",                     15L,        31853476.78,
      "IT", "ProductGroup2", "Product2",                     16L,           30379818,
      "IT", "ProductGroup2", "Product2",                     17L,        29765448.87,
      "IT", "ProductGroup2", "Product2",                     18L,           31376234,
      "IT", "ProductGroup2", "Product2",                     19L,        32628514.81,
      "IT", "ProductGroup2", "Product2",                     20L,           32732196,
      "IT", "ProductGroup2", "Product2",                     21L,        33503784.25,
      "IT", "ProductGroup2", "Product2",                     22L,           35163372,
      "DE", "ProductGroup3", "Product3",                      0L,                  0,
      "DE", "ProductGroup3", "Product3",                      1L,         161884.081,
      "DE", "ProductGroup3", "Product3",                      2L,        7876925.474,
      "DE", "ProductGroup3", "Product3",                      3L,        12948209.55,
      "DE", "ProductGroup3", "Product3",                      4L,        13304401.76
  )


testdf$Country = as.factor(testdf$Country)
testdf$ProductGroup   = as.factor(testdf$ProductGroup)
testdf$Product  = as.factor(testdf$Product)

Could you make your problem reproducible by sharing a sample of your data so others can help (please do not use `str()`, `head()` or screenshot)? You can use the [`reprex`](https://reprex.tidyverse.org/articles/articles/magic-reprex.html) and [`datapasta`](https://cran.r-project.org/web/packages/datapasta/vignettes/how-to-datapasta.html) packages to assist you with that. See also [Help me Help you](https://speakerdeck.com/jennybc/reprex-help-me-help-you?slide=5) & [How to make a great R reproducible example?](https://stackoverflow.com/q/5963269) — Tung, Nov 19 '18 at 19:50

Steffen Moritz · Answer 1 · 2018-11-24T07:08:18.417

0

Probably using mice will not give you the desired results. Since it mostly uses inter-variable correlations. You are looking more for correlations in time.

My recommendation for this specific example would be to split the dataset into Country, ProductGroup, Product groups and perform imputation on these with a time series imputation package.

Looking at your data I think something like the function na.interpolation from package imputeTS would already do a good job.

That is how you call it:

library("imputeTS")
na.interpolation(yourTimeSeries)

You would have to call it multiple times for each time series you created out of the each Country, ProductGroup, Product.

You can also just run

 na.interpolation(testdf$SalesAtLaunchYear)

On your whole dataset which is easier - in the example you showed this would also work. (might lead to problems if the rest is structured differently or you are using a different algorithm from imputeTS package)

edited Nov 24 '18 at 07:08

answered Nov 24 '18 at 06:59

Steffen Moritz

7,277
11
36
55

Thanks, I was already looking into this package, too. I will try and report results. I was wondering how to control within this library or using some other models the country effect and product group effect. To clarify, there is also some products for which sales for all years since launch are available, from which country growth can be learned. – aza07 Nov 25 '18 at 19:46
I see, so there might be some inter-variable correlations you also can employ. You could try the package AMELIAII then. Chapter 4.6 / p.20 in the manual gives some hints on how to also consider the time aspects: https://cran.r-project.org/web/packages/Amelia/vignettes/amelia.pdf . It's only setting some parameters. I still would compare against results form e.g. imputeTS - often when the time correlations are way stronger than your inter-variable correlations you fare better with a sole time series imputation method. – Steffen Moritz Nov 26 '18 at 15:23

Imputation model for time series missing data in R

1 Answers1