I'm trying to reindex a dataframe's multiindex at one sublevel. The df in question looks like this:
test = pd.DataFrame({
'day':[1,3,5],
'position':['A', 'B', 'A'],
'value':[20, 45, 3]
})
test.set_index(['day', 'position'])
>> value
day position
1 A 20
3 B 45
5 A 3
And my goal is to reindex the day
level to transform the dataframe into the following:
>>>
value
day position
1 A 20.0
2 A 20.0
3 A 20.0
4 A 20.0
5 A 3.0
1 B 0.0
2 B 0.0
3 B 45.0
4 B 45.0
5 B 45.0
So essentially I need to reindex day
to days 1 through 5 for every position group and then forwardfill and fillna with 0.
Use:
unstack
reindex
ffill
NaN
s by fillna
stack
sort_index
for expected outputdf = (test.set_index(['day', 'position'])
.unstack()
.reindex(range(1,6))
.ffill()
.fillna(0)
.stack()
.sort_index(level=[1,0]))
print (df)
value
day position
1 A 20.0
2 A 20.0
3 A 20.0
4 A 20.0
5 A 3.0
1 B 0.0
2 B 0.0
3 B 45.0
4 B 45.0
5 B 45.0