Continuous dates for products in Pandas

user_10785666 0
Viewed 212

I started to work with Pandas and I have some issues that I don't really know how to solve.

I have a dataframe with date, product, stock and sales. Some dates and products are missing. I would like to get a timeseries for each product in a range of dates.

For example:

           product  udsStock udsSales
date        
2019-12-26  14      161      848
2019-12-27  14      1340     914
2019-12-30  14      856      0
2019-12-25  4       3132     439
2019-12-27  4       3177     616
2020-01-01  4       500      883

It has to be the same range for all products even if one product doesn't appear in one date in the range.

If I want the range 2019-12-25 to 2020-01-01, the final dataframe should be like this one:

           product  udsStock udsSales
date
2019-12-25  14      NaN      NaN      
2019-12-26  14      161      848
2019-12-27  14      1340     914
2019-12-28  14      NaN      NaN
2019-12-29  14      NaN      NaN      
2019-12-30  14      856      0
2019-12-31  14      NaN      NaN
2020-01-01  14      NaN      NaN   
2019-12-25  4       3132     439
2019-12-26  4       NaN      NaN  
2019-12-27  4       3177     616
2019-12-28  4       NaN      NaN  
2019-12-29  4       NaN      NaN
2019-12-30  4       NaN      NaN
2019-12-31  4       NaN      NaN
2020-01-01  4       500      883

I have tried to reindex by the range but it doesn't work because there are identical indexes.

idx = pd.date_range('25-12-2019', '01-01-2020')
df = df.reindex(idx)

I also have tried to index by date and product and then reindex, but I don't know how to put the product that is missing.

Any more ideas?

Thanks in advance

pandas time-series dataframe python
2 Answers

We can use pd.date_range and groupby.reindex to achieve your result:

date_range = pd.date_range(start='2019-12-25', end='2020-01-01', freq='D')
df = df.groupby('product', sort=False).apply(lambda x: x.reindex(date_range))
df['product'] = df.groupby(level=0)['product'].ffill().bfill()
df = df.droplevel(0)

            product  udsStock  udsSales
2019-12-25     14.0       NaN       NaN
2019-12-26     14.0     161.0     848.0
2019-12-27     14.0    1340.0     914.0
2019-12-28     14.0       NaN       NaN
2019-12-29     14.0       NaN       NaN
2019-12-30     14.0     856.0       0.0
2019-12-31     14.0       NaN       NaN
2020-01-01     14.0       NaN       NaN
2019-12-25      4.0    3132.0     439.0
2019-12-26      4.0       NaN       NaN
2019-12-27      4.0    3177.0     616.0
2019-12-28      4.0       NaN       NaN
2019-12-29      4.0       NaN       NaN
2019-12-30      4.0       NaN       NaN
2019-12-31      4.0       NaN       NaN
2020-01-01      4.0     500.0     883.0
user_90812670

Convert index to datetime object :

df2.index = pd.to_datetime(df2.index)

Create unique combinations of date and product :

import itertools

idx = pd.date_range("25-12-2019", "01-01-2020")
product = df2["product"].unique()
temp = itertools.product(idx, product)
temp = pd.MultiIndex.from_tuples(temp, names=["date", "product"])
temp

MultiIndex([('2019-12-25', 14),
            ('2019-12-25',  4),
            ('2019-12-26', 14),
            ('2019-12-26',  4),
            ('2019-12-27', 14),
            ('2019-12-27',  4),
            ('2019-12-28', 14),
            ('2019-12-28',  4),
            ('2019-12-29', 14),
            ('2019-12-29',  4),
            ('2019-12-30', 14),
            ('2019-12-30',  4),
            ('2019-12-31', 14),
            ('2019-12-31',  4),
            ('2020-01-01', 14),
            ('2020-01-01',  4)],
           names=['date', 'product'])

Reindex dataframe :

df2.set_index("product", append=True).reindex(temp).sort_index(
    level=1, ascending=False
).reset_index(level="product")


          product   udsStock    udsSales
    date            
2020-01-01  14       NaN    NaN
2019-12-31  14       NaN    NaN
2019-12-30  14      856.0   0.0
2019-12-29  14      NaN     NaN
2019-12-28  14      NaN     NaN
2019-12-27  14      1340.0  914.0
2019-12-26  14      161.0   848.0
2019-12-25  14      NaN     NaN
2020-01-01  4       500.0   883.0
2019-12-31  4       NaN     NaN
2019-12-30  4       NaN     NaN
2019-12-29  4       NaN     NaN
2019-12-28  4       NaN     NaN
2019-12-27  4       3177.0  616.0
2019-12-26  4       NaN     NaN
2019-12-25  4       3132.0  439.0

In R, specifically tidyverse, it can be achieved with the complete method. In Python, the pyjanitor package has something similar, but a few kinks remain to be ironed out (A PR has been submitted already for this).

user_71757130
Related