Turn Pandas Multi-Index into column

Question

I have a dataframe with 2 index levels:

                         value
Trial    measurement
    1              0        13
                   1         3
                   2         4
    2              0       NaN
                   1        12
    3              0        34

Which I want to turn into this:

Trial    measurement       value

    1              0        13
    1              1         3
    1              2         4
    2              0       NaN
    2              1        12
    3              0        34

How can I best do this?

I need this because I want to aggregate the data as instructed here, but I can't select my columns like that if they are in use as indices.

Duplicate: stackoverflow.com/questions/18624039/… You want the first suggestion. .reset_index() — TomAugspurger
– TomAugspurger, Commented Nov 21, 2013 at 1:51
many thanks, I actually browsed around for this a lot, but "make multiindex to column" and similar queries always got me threads which wanted to pivot their dataframes... — TheChymera
– TheChymera, Commented Nov 21, 2013 at 3:49

cs95 · Accepted Answer · 2021-10-07 09:56:05Z

340

The reset_index() is a pandas DataFrame method that will transfer index values into the DataFrame as columns. The default setting for the parameter is drop=False (which will keep the index values as columns).

All you have to do call .reset_index() after the name of the DataFrame:

df = df.reset_index()

edited Oct 7, 2021 at 9:56

cs95

406k106 gold badges744 silver badges797 bronze badges

answered Sep 8, 2014 at 21:42

CraigSF

3,4321 gold badge16 silver badges5 bronze badges

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

4 Comments

Gorkem Over a year ago

For my case where I had 3 index levels inplace reset did not work. Alternative is assigning newly resetted dataframe to a new one: df2 = df.reset_index()

cs95 Over a year ago

To reset only a particular level(s), use df.reset_index(level=[...])

Owen Over a year ago

Or the side-effect (probably quicker) way: df.reset_index(inplace=True)

kva1966 Over a year ago

df.reset_index(names=['a', 'b']) to provide names/alternative names to the produced columns.

Karl Anka · Accepted Answer · 2020-02-03 07:37:47Z

39

This doesn't really apply to your case but could be helpful for others (like myself 5 minutes ago) to know. If one's multindex have the same name like this:

                         value
Trial        Trial
    1              0        13
                   1         3
                   2         4
    2              0       NaN
                   1        12
    3              0        34

df.reset_index(inplace=True) will fail, cause the columns that are created cannot have the same names.

So then you need to rename the multindex with df.index = df.index.set_names(['Trial', 'measurement']) to get:

                           value
Trial    measurement       

    1              0        13
    1              1         3
    1              2         4
    2              0       NaN
    2              1        12
    3              0        34

And then df.reset_index(inplace=True) will work like a charm.

I encountered this problem after grouping by year and month on a datetime-column(not index) called live_date, which meant that both year and month were named live_date.

edited Feb 3, 2020 at 7:37

answered Nov 17, 2017 at 17:46

Karl Anka

2,9092 gold badges21 silver badges34 bronze badges

1 Comment

Rich Over a year ago

How to have your Trial values to repeat themselves? I had the same problem and it works except my values don't repeat themselves.

Alex · Accepted Answer · 2020-10-27 08:39:24Z

27

There may be situations when df.reset_index() cannot be used (e.g., when you need the index, too). In this case, use index.get_level_values() to access index values directly:

df['Trial'] = df.index.get_level_values(0)
df['measurement'] = df.index.get_level_values(1)

This will assign index values to individual columns and keep the index.

See the docs for further info.

answered Oct 27, 2020 at 8:39

Alex

3,6012 gold badges39 silver badges55 bronze badges

1 Comment

Zizzipupp Over a year ago

This is soooooooooo useful! It should be possible to do this using much clearer language, e.g. df['measurement'] = df.index.values(1).

sameagol · Accepted Answer · 2019-05-20 00:06:01Z

19

As @cs95 mentioned in a comment, to drop only one level, use:

df.reset_index(level=[...])

This avoids having to redefine your desired index after reset.

answered May 20, 2019 at 0:06

sameagol

6431 gold badge8 silver badges16 bronze badges

Comments

kevin_theinfinityfund · Accepted Answer · 2020-09-28 16:37:48Z

5

I ran into Karl's issue as well. I just found myself renaming the aggregated column then resetting the index.

df = pd.DataFrame(df.groupby(['arms', 'success'])['success'].sum()).rename(columns={'success':'sum'})

df = df.reset_index()

answered Sep 28, 2020 at 16:37

kevin_theinfinityfund

2,20519 silver badges19 bronze badges

Comments

whitetiger1399 · Accepted Answer · 2022-05-16 13:42:24Z

3

Short and simple

df2 = pd.DataFrame({'test_col': df['test_col'].describe()})
df2 = df2.reset_index()

answered May 16, 2022 at 13:42

whitetiger1399

6735 silver badges8 bronze badges

Comments

Rafal Plaza · Accepted Answer · 2023-01-17 13:07:05Z

1

A solution that might be helpful in cases when not every column has multiple index levels:

df.columns = df.columns.map(''.join)

answered Jan 17, 2023 at 13:07

Rafal Plaza

112 bronze badges

Comments

kho · Accepted Answer · 2023-08-14 11:18:44Z

1

Similar to Alex solution in a more generalized form. It keeps the indexes untouched and adds the index levels as new columns with its name.

for i in df.index.names:
    df[i] = df.index.get_level_values(i)

which gives the new columns 'Trial' and 'measurement'

                   value Trial    measurement
Trial measurement             
    1           0     13     1              0     
                1      3     1              1     
                2      4     1              2     
  ...

edited Aug 14, 2023 at 11:18

answered Oct 30, 2022 at 0:13

kho

1,3517 silver badges10 bronze badges

Collectives™ on Stack Overflow

Turn Pandas Multi-Index into column

8 Answers 8

4 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

4 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related