CODEWITHSUNDEEP
pythonpandas
LPG
LPG

Reputation: 842

Python Pandas: How fill date ranges in a multiindex

Suppose I was trying to organize sales data for a membership business.

I only have the start and end dates. Ideally sales between the start and end dates appear as 1, instead of missing.

I can't get the 'date' column to be filled with in-between dates. That is: I want a continuous set of months instead of gaps. Plus I need to fill missing data in columns with ffill.

I have tried different ways such as stack/unstack and reindex but different errors occur. I'm guessing there's a clean way to do this. What's the best practice to do this?

Suppose the multiindexed data structure:

                 variable     sales
vendor date                 
a      2014-01-01  start date 1
       2014-03-01    end date 1
b      2014-03-01  start date 1
       2014-07-01    end date 1

And the desired result

                   variable   sales
vendor date                 
a      2014-01-01  start date 1
       2014-02-01  NaN        1
       2014-03-01    end date 1
b      2014-03-01  start date 1
       2014-04-01  NaN        1
       2014-05-01  NaN        1
       2014-06-01  NaN        1 
       2014-07-01    end date 1

Upvotes: 6

Views: 2068

Answers (2)

behzad.nouri
behzad.nouri

Reputation: 77951

you can do:

>>> f = lambda df: df.resample(rule='M', how='first')
>>> df.reset_index(level=0).groupby('vendor').apply(f).drop('vendor', axis=1)
                     variable  sales
vendor date                         
a      2014-01-31  start date      1
       2014-02-28         NaN    NaN
       2014-03-31    end date      1
b      2014-03-31  start date      1
       2014-04-30         NaN    NaN
       2014-05-31         NaN    NaN
       2014-06-30         NaN    NaN
       2014-07-31    end date      1

and then just .fillna on sales column if needed.

Upvotes: 5

roman
roman

Reputation: 117345

I have a solution, but it's not really simple:

so, here's your DataFrame:

>>> df
                   sales date variable
vendor date                           
a      2014-01-01      1    start date
       2014-01-03      1      end date
b      2014-01-03      1    start date
       2014-01-07      1      end date

first, I want to create data for new MultiIndex:

>>> df2 = df.set_index('date variable', append=True).reset_index(level='date')['date']
>>> df2
vendor  date variable
a         start date    2014-01-01
          end date      2014-01-03
b         start date    2014-01-03
          end date      2014-01-07
>>> df2 = df2.unstack()
>>> df2
date variable   end date   start date
vendor                               
a             2014-01-03   2014-01-01
b             2014-01-07   2014-01-03

now, create tuples for new MultiIndex:

>>> tuples = [(x[0], d) for x in df3.iterrows() for d in pd.date_range(x[1]['start date'], x[1]['end date'])]
>>> tuples
[('a', '2014-01-01'), ..., ('b', '2014-01-07)]

and create MultiIndex and reindex():

>>> mi = pd.MultiIndex.from_tuples(tuples,names=df.index.names)
>>> df.reindex(mi)
                   sales date variable
vendor date                           
a      2014-01-01      1    start date
       2014-01-02    NaN           NaN
       2014-01-03      1      end date
b      2014-01-03      1    start date
       2014-01-04    NaN           NaN
       2014-01-05    NaN           NaN
       2014-01-06    NaN           NaN
       2014-01-07      1      end date

Upvotes: 0

Related Questions