Blue Moon
Blue Moon

Reputation: 4681

how to create columns out of a multi-index in a pandas dataframe?

I have the following df:

import numpy as np
import pandas as pd
from pandas import *


arrays = [np.array(['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux']),
      np.array(['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two'])]


s = pd.Series(np.random.randn(8), index=arrays)

df = pd.DataFrame(np.random.randn(8, 4), index=arrays)

this looks like:

                0         1         2         3
bar one -0.986089 -0.501170  1.635501 -0.789489
    two  1.890491 -0.022640 -1.649097  0.984925
baz one -0.759930 -1.640487 -0.763909 -0.554997
    two  1.636005  0.037158  0.567383  0.770314
foo one  0.709847  0.048332 -0.676660  1.059454
    two  0.588063  0.568405  1.619102  0.393631
qux one -0.735497 -0.589282  1.015266  0.934877
    two -0.380719  0.822213  0.295152 -0.838549

What I'd like to obtain is two have two columns representing the index as:

                0         1         2         3 col1 col2
bar one -0.986089 -0.501170  1.635501 -0.789489  bar  one
    two  1.890491 -0.022640 -1.649097  0.984925  bar  two
baz one -0.759930 -1.640487 -0.763909 -0.554997  baz  one
    two  1.636005  0.037158  0.567383  0.770314  baz  two
foo one  0.709847  0.048332 -0.676660  1.059454  foo  one
    two  0.588063  0.568405  1.619102  0.393631  foo  two
qux one -0.735497 -0.589282  1.015266  0.934877  qux  one
    two -0.380719  0.822213  0.295152 -0.838549  qux  two

the usual code for this if I had only a one level index would have been:

df['col'] = df.index

How it is possible to do this on a multilevel index?

Upvotes: 2

Views: 2386

Answers (1)

chrisb
chrisb

Reputation: 52286

If you just want to shove the different levels into columns, you can reset the index like this:

df = df.reset_index()

To get what you showed, you can access the values for each level with get_level_values like this:

In [69]: df['col1'] = df.index.get_level_values(0)

In [70]: df['col2'] = df.index.get_level_values(1)

In [71]: df
Out[71]: 
                0         1         2         3 col1 col2
bar one  0.523779  0.391620  0.726137  0.025270  bar  one
    two  0.569344  2.199075 -1.280942 -0.703693  bar  two
baz one  0.347541 -0.423759 -1.010009 -0.349585  baz  one
    two -0.894432 -0.335773 -0.550428  0.217038  baz  two
foo one  0.688120 -1.123873  0.784451  0.482187  foo  one
    two  0.062910 -0.705614  0.205807 -0.723899  foo  two
qux one -0.304601  0.130234  0.303403  1.348833  qux  one
    two -0.931551  0.655013  0.622796 -0.738110  qux  two

Upvotes: 6

Related Questions