Python Pandas: how to fill date ranges in multi-index
Let's say I was trying to organize sales data for a membership business.
I only have start and end dates. Ideally, sales between the start and end date appear as 1 rather than disappear.
I cannot get the date column to be populated with intermediate dates. That is: I want a continuous set of months instead of spaces. Also, I need to fill in missing data in columns using ffill.
I tried different ways like stack / unstack and reindex but different errors occur. I assume there is a clean way to do this. What's the best practice for doing this?
Suppose a structure with multiple indexes:
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
source to share
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
in a column sales
if needed.
source to share
I have a solution, but it's not very simple:
So here's yours 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 a new one 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 the new one 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
source to share