filing missing time series in python

Question

In R, it has fill_gaps which can fill the time series as well as key such as fruit in the below. I was wondering if there's anything similar in python.

library(tribble)
harvest <- tsibble(
  year = c(2010, 2011, 2013, 2011, 2012, 2014),
  fruit = rep(c("kiwi", "cherry"), each = 3),
  kilo = sample(1:10, size = 6),
  key = fruit, index = year
)

# gaps as default `NA`
fill_gaps(harvest, .full = TRUE)

   year  fruit   kilo
      
 1  2010 cherry    NA
 2  2011 cherry     5
 3  2012 cherry     7
 4  2013 cherry    NA
 5  2014 cherry     8
 6  2010 kiwi       9
 7  2011 kiwi      10
 8  2012 kiwi      NA
 9  2013 kiwi       4
10  2014 kiwi      NA

The python code is shown below, I have 3 years of data and with 1000 different products.

import pandas as pd
from datetime import datetime
db = pd.DataFrame(
    [[datetime(2019, 2, 7), 'a' , 1,2,3],
     [datetime(2019, 2, 7), 'b', 2, 3, 3],
      [datetime(2019, 2, 8), 'a', 2, 3, 3],
       [datetime(2019, 2, 9), 'a', 12, 13, 3],
        [datetime(2019, 2, 9), 'b', 10, 2, 3]
        ]
)
db.columns = ['date', 'product', 'x','y','z']

db       
 date          product x   y  z
0 2019-02-07       a   1   2  3
1 2019-02-07       b   2   3  3
2 2019-02-08       a   2   3  3
3 2019-02-09       a  12  13  3
4 2019-02-09       b  10   2  3

The expected outcome is:

    date     product   x   y  z
0 2019-02-07       a   1   2  3
1 2019-02-07       b   2   3  3
2 2019-02-08       a   2   3  3
3 2019-02-08       b   0   0  0
4 2019-02-09       a  12  13  3
5 2019-02-09       b  10   2  3

user_7175713 · Answer

Couple of steps:

Set the index

df = db.copy()

df = db.set_index(['date', 'product'])

Create an index that has all possible combinations of date and product:

reindexer = pd.MultiIndex.from_product(df.index.levels)
reindexer
MultiIndex([('2019-02-07', 'a'),
            ('2019-02-07', 'b'),
            ('2019-02-08', 'a'),
            ('2019-02-08', 'b'),
            ('2019-02-09', 'a'),
            ('2019-02-09', 'b')],
           names=['date', 'product'])

Reindex df, fill nulls with 0 and reset the index:

df.reindex(reindexer, fill_value = 0).reset_index()

        date product   x   y  z
0 2019-02-07       a   1   2  3
1 2019-02-07       b   2   3  3
2 2019-02-08       a   2   3  3
3 2019-02-08       b   0   0  0
4 2019-02-09       a  12  13  3
5 2019-02-09       b  10   2  3

You could abstract the steps (and take care of duplicate indices, if any) using complete from pyjanitor:

# pip install pyjanitor
import janitor
db.complete(['date', 'product']).fillna(0)
 
        date product     x     y    z
0 2019-02-07       a   1.0   2.0  3.0
1 2019-02-07       b   2.0   3.0  3.0
2 2019-02-08       a   2.0   3.0  3.0
3 2019-02-08       b   0.0   0.0  0.0
4 2019-02-09       a  12.0  13.0  3.0
5 2019-02-09       b  10.0   2.0  3.0

Note that with the reindex the data type is preseved when nulls are filled; complete doesnt do fills, it passes that duty to fillna.

So, for your use case, I think reindex is sufficient (you also may not be too keen to have another library dependency)

filing missing time series in python

1 Answers