Insert index to level in multiindex dataframe [duplicate]

Question

Is it possible to add one index to a level in multiindex dataframe? For example, I am trying to add 'new_index' to level 1 with nan value.

#Sample data
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6], 'C': [7, 8, 9]})
df = df.set_index([['one', 'two', 'three'], [1, 2, 3]])
df.index.names = ['first', 'second']
df

#Output
                   A    B   C
first   second          
one       1        1    4   7
two       2        2    5   8
three     3        3    6   9

#Desired Output
                   A    B   C
first   second          
one        1       1    4   7
       new_index   NaN  NaN NaN
two        2       2    5   8
       new_index   NaN  NaN NaN
three      3       3    6   9
       new_index   NaN  NaN NaN

Thank you very much.

Sungmin Son · Accepted Answer · 2023-01-17 07:33:11Z

0

This is what I found.

df = df.unstack("second").stack(level=0)
df["new_index"] = "NA"
df.stack().unstack(level=1)
df

#output
                        A          B          C
first second                                    
one   1         1.00000000 4.00000000 7.00000000
      new_index         NA         NA         NA
three 3         3.00000000 6.00000000 9.00000000
      new_index         NA         NA         NA
two   2         2.00000000 5.00000000 8.00000000
      new_index         NA         NA         NA

For NA is actually just string "NA", it can not rigorously be answer. But replacing it with np.nan will make 'new_index' disappear since pd.stack() will dropna.

Any other idea?

answered Jan 17, 2023 at 7:33

Sungmin Son

651 silver badge6 bronze badges

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

Collectives™ on Stack Overflow

Insert index to level in multiindex dataframe [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related