Reputation: 997
I'm trying to reindex a 2-level multiindex pandas dataframe. Data struct looks like this:
In [1]: df.head(5)
Out [1]: arrivals departs
station datetime
S1 2014-03-03 07:45:00 1 1
2014-03-03 09:00:00 2 1
2014-03-03 11:45:00 1 1
2014-03-04 08:45:00 1 1
2014-03-04 09:45:00 2 1
I want to fill datetime
gaps with 15 minute intervals, but when I call
In [2]: df.reindex(pd.date_range(start='2014-03-03 07:45:00',
end='2014-03-04 07:45:00', freq='15min'), level=1)
I get the exact same dataframe. I expected something like the following
Out [2]: arrivals departs
station datetime
S1 2014-03-03 07:45:00 1 1 <-- original row
2014-03-03 08:00:00 0 0 <-- filled in row
2014-03-03 08:15:00 0 0 <-- filled in
2014-03-03 08:30:00 0 0 <-- filled in
2014-03-03 08:45:00 0 0 <-- filled in
2014-03-03 09:00:00 2 1 <-- original
etc...
Any ideas?
Upvotes: 2
Views: 3190
Reputation: 32125
Turn it back into a simple datetimeindex and fill the gaps:
df = (df.unstack(level=0)
.reindex(pd.date_range(start='2014-03-03 07:45:00',
end='2014-03-04 07:45:00', freq='15min')))
df = df.fillna(0) # for the data, 0 is the desired value
df.stack('station').swaplevel(0,1).sort_index()
Upvotes: 4