Adding a new nested level value to a MultiIndex DataFrame

Question

How can I add another level value to a MultiIndex Initialized to a certain value (for example None). Hard to describe with words, better graphically, how to add the new value level:

df_before
          a   b   c   d
l1  l2                 
bar one  24  13   8   9
    two  11  30   7  23
baz one  21  31  12  30
    two   2   5  19  24
foo one  15  18   3  16
    two   2  24  28  11
qux one  23   9   6  12
    two  29  28  11  21

df_after
             a   b   c   d
l1  l2                 
bar one     24  13   8   9
    two     11  30   7  23
    new     None  None   None  None
baz one     21  31  12  30
    two     2   5  19  24
    new     None  None   None  None
foo one     15  18   3  16
    two     2  24  28  11
    new     None  None   None  None
qux one     23   9   6  12
    two     29  28  11  21
    new     None  None   None  None

Note: my DataFrame indeed has three levels, so a solution that could generalize to more levels would be appreciated. My best attempt was getting the unique values for the old level, append a new value and set the new level, but it didn't produce my desired result

# this is a failed attempt of what I wanted to do
new_level_values = [*list(df.index.get_level_values(2).unique()), "new"]
df.index = df.index.set_levels(levels=new_level_values, level=2)
df

Please can you provide the constructor for your dataframe with multi-level index? — Corralien
– Corralien, Commented Jan 9, 2023 at 13:36

jezrael · Accepted Answer · 2023-01-09 13:46:33Z

6

Use DataFrame.reindex with append new value to second level and recreate MultiIndex by MultiIndex.from_product:

v = [*list(df.index.levels[1]), "new"]

df = df.reindex(pd.MultiIndex.from_product([df.index.levels[0],v]))
print (df)
            a     b     c     d
l1                             
bar one  24.0  13.0   8.0   9.0
    two  11.0  30.0   7.0  23.0
    new   NaN   NaN   NaN   NaN
baz one  21.0  31.0  12.0  30.0
    two   2.0   5.0  19.0  24.0
    new   NaN   NaN   NaN   NaN
foo one  15.0  18.0   3.0  16.0
    two   2.0  24.0  28.0  11.0
    new   NaN   NaN   NaN   NaN
qux one  23.0   9.0   6.0  12.0
    two  29.0  28.0  11.0  21.0
    new   NaN   NaN   NaN   NaN

edited Jan 9, 2023 at 13:46

answered Jan 9, 2023 at 13:38

jezrael

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

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

Collectives™ on Stack Overflow

Adding a new nested level value to a MultiIndex DataFrame

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related