Reindex sublevel in pandas multiindexed dataframe

Question

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.

jezrael · Accepted Answer · 2018-03-15 14:29:28Z

2

Use:

first reshape by unstack
add mising days by reindex
forward fill by ffill
replace first NaNs by fillna
reshape by by stack
sort_index for expected output

df = (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

edited Mar 15, 2018 at 14:29

answered Mar 15, 2018 at 14:22

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nico Over a year ago

You beautiful genius creature. Thank you :)

jezrael Over a year ago

@Nico - You get nice question, it is very important for answering ;)

BENY · Accepted Answer · 2018-03-15 14:27:21Z

1

I reorder your index

test.set_index(['position', 'day']).reindex(pd.MultiIndex.from_product([['A','B'],list(range(1,6))])).sort_index().groupby(level=0).ffill().fillna(0)
Out[30]: 
     value
A 1   20.0
  2   20.0
  3   20.0
  4   20.0
  5    3.0
B 1    0.0
  2    0.0
  3   45.0
  4   45.0
  5   45.0

answered Mar 15, 2018 at 14:27

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Reindex sublevel in pandas multiindexed dataframe

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related