How to add a column to a multi-indexed DataFrame?

Question

I have a DataFrame like this:

upper    level1    level2 
lower    name      name
1        Mary      Tom
2        ...       ...

What should I do if I want to add another column under level1? For example

upper    level1       level2 
lower    name    age  name
1        Mary    13   Tom
2        ...    ...    ...

I can access data with df['level1'].loc[:,'name'], but I don't know how to add/remove a column.

If I just use df.level1['age']=1, Python returns a copy warning and nothing changed in the DataFrame:

SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
if __name__ == '__main__':

piRSquared · Accepted Answer · 2016-07-06 19:09:25Z

5

Try this:

df.insert(1, ('level1', 'age'), pd.Series([13]))

answered Jul 6, 2016 at 19:09

piRSquared

295k68 gold badges509 silver badges654 bronze badges

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

4 Comments

MaxU - stand with Ukraine Over a year ago

it's so easy and elegant!

Arthur Over a year ago

Thanks it works! Could you please explain why my code is incorrect?

piRSquared Over a year ago

@Arthur see stackoverflow.com/a/38147527/2336654 for a good example of what is happening with SettingWithCopyWarning. In your case, df.level1 is returning a slice or view of df and you are trying to alter df via the slice. Pandas will make a copy of df.level1 and perform the alteration on the copy instead. Often this isn't what you want and pandas is warning you.

Arthur Over a year ago

@piRSquared Thank you.

Andy Hayden · Accepted Answer · 2016-07-06 19:11:22Z

4

You can use tuple in the assignment:

In [11]: df[('level1', 'age')] = 13  # or a Series here, rather than a number

In [12]: df
Out[12]:
  upper level1 level2 level1
  lower   name   name    age
0     1   Mary    Tom     13
1     2    ...    ...     13

answered Jul 6, 2016 at 19:11

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

2 Comments

Arthur Over a year ago

Thank you! But could you please tell me why my code is wrong? I'm new to Python.

Andy Hayden Over a year ago

@Arthur when doing df.level1 it creates a view which is the subdataframe which has only the level1 columns. Trying to create a new column on the view (with df.level1['age']=1) doesn't work, as you can't edit the "view" (it's readonly).

Collectives™ on Stack Overflow

How to add a column to a multi-indexed DataFrame?

2 Answers 2

4 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Linked

Related