Home > OS >  How do i add rows according to pandas.Multindex?
How do i add rows according to pandas.Multindex?

Time:05-10

I am working with a data frame looks like this

            0     1     2     3     4 
one   y   8.0   0.0   9.0  11.0   5.0 
      z  12.0  18.0   7.0   1.0  18.0 
two   x  14.0  15.0   9.0   9.0  18.0 
      y  10.0   7.0  13.0  14.0   1.0 
      z  19.0  16.0  13.0  13.0   6.0 
three x  15.0  15.0   5.0  14.0  18.0 
      y   3.0  15.0   7.0   5.0   1.0 

I would like to add rows to 'one' and 'three', allowing every first-level index have same number of second-level rows like this:

            0     1     2     3     4 
one   x   NaN   NaN   NaN   NaN   NaN 
      y   8.0   0.0   9.0  11.0   5.0 
      z  12.0  18.0   7.0   1.0  18.0 
two   x  14.0  15.0   9.0   9.0  18.0 
      y  10.0   7.0  13.0  14.0   1.0 
      z  19.0  16.0  13.0  13.0   6.0 
three x  15.0  15.0   5.0  14.0  18.0 
      y   3.0  15.0   7.0   5.0   1.0 
      z   NaN   NaN   NaN   NaN   NaN 

Is there any efficient ways to implement this?

CodePudding user response:

Try this:

idx = pd.MultiIndex.from_product(df.index.levels)

df.reindex(idx)

Output:

            0     1     2     3     4
one   x   NaN   NaN   NaN   NaN   NaN
      y   8.0   0.0   9.0  11.0   5.0
      z  12.0  18.0   7.0   1.0  18.0
three x  15.0  15.0   5.0  14.0  18.0
      y   3.0  15.0   7.0   5.0   1.0
      z   NaN   NaN   NaN   NaN   NaN
two   x  14.0  15.0   9.0   9.0  18.0
      y  10.0   7.0  13.0  14.0   1.0
      z  19.0  16.0  13.0  13.0   6.0

CodePudding user response:

Let us do unstack stack

df.unstack().stack(dropna=False)

            0     1     2     3     4
one   x   NaN   NaN   NaN   NaN   NaN
      y   8.0   0.0   9.0  11.0   5.0
      z  12.0  18.0   7.0   1.0  18.0
three x  15.0  15.0   5.0  14.0  18.0
      y   3.0  15.0   7.0   5.0   1.0
      z   NaN   NaN   NaN   NaN   NaN
two   x  14.0  15.0   9.0   9.0  18.0
      y  10.0   7.0  13.0  14.0   1.0
      z  19.0  16.0  13.0  13.0   6.0
  • Related