How to add a new row and new column to a multiindex Pandas dataframe?

Question

I try to use .loc to create a new row and a new column to a multiindex Pandas dataframe, by specifying all the axis. The problem is that it creates the new index without the new column, and at the same time throws an obscur KeyError: 6.

How could I do that ? A one line solution whould be much appreciated.

> df
                   side    total    value
city   code type                             
NaN    NTE  urban  ouest   0.01949  391.501656

> df.loc[(np.nan, 'NTE', 'rural'), 'population'] = 1000
KeyError: 6

> df
                   side    total    value
city   code type                             
NaN    NTE  urban  ouest   0.01949  391.501656
NaN    NTE  rural    NaN       NaN         NaN

Now, when I try the same command again it complains the index doesn't exist.

> df.loc[(np.nan, 'NTE', 'rural'), 'population'] = 1000
KeyError: (nan, 'NTE', 'rural')

The desired output would be this dataframe:

                   side    total    value        population
city   code type                             
NaN    NTE  urban  ouest   0.01949  391.501656          NaN
NaN    NTE  rural    NaN       NaN         NaN         1000

jezrael · Accepted Answer · 2022-10-26 09:18:03Z

3

Here is problem with missing values, possible hack solution with assign empty string and rename:

df.loc[('', 'NTE', 'rural'), 'population'] = 1000
print (df.index)
MultiIndex([(nan, 'NTE', 'urban'),
            ( '', 'NTE', 'rural')],
           names=['city', 'code', 'type'])

df = df.rename({'':np.nan}, level=0)

print (df.index)

MultiIndex([(nan, 'NTE', 'urban'),
            (nan, 'NTE', 'rural')],
           names=['city', 'code', 'type'])

print (df)
                  side    total       value  population
city code type                                         
NaN  NTE  urban  ouest  0.01949  391.501656         NaN
          rural    NaN      NaN         NaN      1000.0

edited Oct 26, 2022 at 9:18

answered Oct 26, 2022 at 9:13

jezrael

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

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

2 Comments

Florent Over a year ago

Thank you, looks like there is no need to rename. The new index 0 is allocated with NaN directly.

jezrael Over a year ago

@Florent - Added print (df.index) for see difference

Collectives™ on Stack Overflow

How to add a new row and new column to a multiindex Pandas dataframe?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related