I have the ser_apl pandas.Series with 2-level MultiIndex:
Date Team
2013-01-31 Man Utd 11
2013-02-28 Man Utd 12
2013-03-29 Man Utd 13
2013-04-30 Man Utd 14
2013-05-31 Man Utd 15
2013-06-28 Man Utd 16
2013-07-31 Man Utd 17
2013-08-30 Man Utd 18
2013-09-30 Man Utd 19
2013-10-31 Man Utd 20
2013-11-29 Man Utd 21
2013-12-31 Man Utd 22
2013-07-31 Liverpool 37
2013-08-30 Liverpool 38
2013-09-30 Liverpool 39
2013-10-31 Liverpool 40
2013-11-29 Liverpool 41
2013-12-31 Liverpool 42
2013-01-31 Chelsea 51
2013-02-28 Chelsea 52
2013-03-29 Chelsea 53
2013-04-30 Chelsea 54
2013-05-31 Chelsea 55
2013-06-28 Chelsea 56
Name: Result, dtype: int64
I need to reindex Date level for all teams to receive:
Date Team
2013-01-31 Chelsea 51.0
2013-02-28 Chelsea 52.0
2013-03-29 Chelsea 53.0
2013-04-30 Chelsea 54.0
2013-05-31 Chelsea 55.0
2013-06-28 Chelsea 56.0
2013-07-31 Chelsea NaN
2013-08-30 Chelsea NaN
2013-09-30 Chelsea NaN
2013-10-31 Chelsea NaN
2013-11-29 Chelsea NaN
2013-12-31 Chelsea NaN
2013-01-31 Liverpool NaN
2013-02-28 Liverpool NaN
2013-03-29 Liverpool NaN
2013-04-30 Liverpool NaN
2013-05-31 Liverpool NaN
2013-06-28 Liverpool NaN
2013-07-31 Liverpool 37.0
2013-08-30 Liverpool 38.0
2013-09-30 Liverpool 39.0
2013-10-31 Liverpool 40.0
2013-11-29 Liverpool 41.0
2013-12-31 Liverpool 42.0
2013-01-31 Man Utd 11.0
2013-02-28 Man Utd 12.0
2013-03-29 Man Utd 13.0
2013-04-30 Man Utd 14.0
2013-05-31 Man Utd 15.0
2013-06-28 Man Utd 16.0
2013-07-31 Man Utd 17.0
2013-08-30 Man Utd 18.0
2013-09-30 Man Utd 19.0
2013-10-31 Man Utd 20.0
2013-11-29 Man Utd 21.0
2013-12-31 Man Utd 22.0
dtype: float64
First of all I prepared index:
idx_level_date = ser_apl.index.get_level_values('Date').unique()
Then I tried a couple of ways:
ser_apl.reindex(idx_level_date, level = 'Date')
and as alternative:
ser_apl.groupby('Team').apply(lambda iter_team: iter_team.reindex(idx_level_date, level = 'Date'))
Both was unsuccessful: result was the same as original ser_apl.
So I need help for reindexing.
P.S. I found the alternative unstack/stack trick here: Filling in date gaps in MultiIndex Pandas Dataframe
But the question is why I can't do the same thing with appropriate method?
Create all possible datetimes by MultiIndex.from_product and pass to Series.reindex, if necessary, change ordering by Series.sort_index:
mux = pd.MultiIndex.from_product(ser_apl.index.levels, names=ser_apl.index.names)
s = ser_apl.reindex(mux).sort_index(level=[1,0])
print (s)
Date Team
2013-01-31 Chelsea 51.0
2013-02-28 Chelsea 52.0
2013-03-29 Chelsea 53.0
2013-04-30 Chelsea 54.0
2013-05-31 Chelsea 55.0
2013-06-28 Chelsea 56.0
2013-07-31 Chelsea NaN
2013-08-30 Chelsea NaN
2013-09-30 Chelsea NaN
2013-10-31 Chelsea NaN
2013-11-29 Chelsea NaN
2013-12-31 Chelsea NaN
2013-01-31 Liverpool NaN
2013-02-28 Liverpool NaN
2013-03-29 Liverpool NaN
2013-04-30 Liverpool NaN
2013-05-31 Liverpool NaN
2013-06-28 Liverpool NaN
2013-07-31 Liverpool 37.0
2013-08-30 Liverpool 38.0
2013-09-30 Liverpool 39.0
2013-10-31 Liverpool 40.0
2013-11-29 Liverpool 41.0
2013-12-31 Liverpool 42.0
2013-01-31 Man Utd 11.0
2013-02-28 Man Utd 12.0
2013-03-29 Man Utd 13.0
2013-04-30 Man Utd 14.0
2013-05-31 Man Utd 15.0
2013-06-28 Man Utd 16.0
2013-07-31 Man Utd 17.0
2013-08-30 Man Utd 18.0
2013-09-30 Man Utd 19.0
2013-10-31 Man Utd 20.0
2013-11-29 Man Utd 21.0
2013-12-31 Man Utd 22.0
Name: Result, dtype: float64