Converting a pandas multi-index series to a dataframe by using second index as columns

user_1030287 0
Viewed 18489

Hi I have a DataFrame/Series with 2-level multi-index and one column. I would like to take the second-level index and use it as a column. For example (code taken from multi-index docs):

import pandas as pd
import numpy as np

arrays = [['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux'],
          ['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two']]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])
s = pd.DataFrame(np.random.randn(8), index=index, columns=["col"])

Which looks like:

first  second
bar    one      -0.982656
       two      -0.078237
baz    one      -0.345640
       two      -0.160661
foo    one      -0.605568
       two      -0.140384
qux    one       1.434702
       two      -1.065408
dtype: float64

What I would like is to have a DataFrame with index [bar, baz, foo, qux] and columns [one, two].

pandas scipy numpy python
3 Answers

Another powerful solution is using .reset_index and .pivot:

levels= [['bar', 'baz'], ['one', 'two', 'three']]
index = pd.MultiIndex.from_product(levels, names=['first', 'second'])
series = pd.Series(np.random.randn(6), index)

df = series.reset_index()
# Shorthand notation instead of explicitly naming index, columns and values
df = df.pivot(*df.columns)

Result:

second       one     three       two
first                               
bar     1.047692  1.209063  0.891820
baz     0.083602 -0.303528 -1.385458
user_48169300
Related