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

Question

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].

AChampion · Accepted Answer · 2017-05-23 18:41:08Z

57

You just need to unstack your series:

>>> s.unstack(level=1)
second       one       two
first                     
bar    -0.713374  0.556993
baz     0.523611  0.328348
foo     0.338351 -0.571854
qux     0.036694 -0.161852

answered May 23, 2017 at 18:41

AChampion

30.5k4 gold badges63 silver badges79 bronze badges

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

1 Comment

s5s Over a year ago

I also did "s.columns = s.columns.droplevel(0)" after this

Chaoste · Accepted Answer · 2018-12-17 12:11:47Z

4

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

answered Dec 17, 2018 at 12:11

Chaoste

5641 gold badge6 silver badges18 bronze badges

Comments

Divakar · Accepted Answer · 2017-05-23 18:58:38Z

3

Here's a solution using array reshaping -

>>> idx = s.index.levels
>>> c = len(idx[1])
>>> pd.DataFrame(s.values.reshape(-1,c),index=idx[0].values, columns=idx[1].values)
          one       two
bar  2.225401  1.624866
baz  1.067359  0.349440
foo -0.468149 -0.352303
qux  1.215427  0.429146

If you don't care about the names appearing on top of indexes -

>>> pd.DataFrame(s.values.reshape(-1,c), index=idx[0], columns=idx[1])
second       one       two
first                     
bar     2.225401  1.624866
baz     1.067359  0.349440
foo    -0.468149 -0.352303
qux     1.215427  0.429146

Timings for the given dataset size -

# @AChampion's solution
In [201]: %timeit s.unstack(level=1)
1000 loops, best of 3: 444 µs per loop

# Using array reshaping step-1
In [199]: %timeit s.index.levels
1000000 loops, best of 3: 214 ns per loop

# Using array reshaping step-2    
In [202]: %timeit pd.DataFrame(s.values.reshape(-1,2), index=idx[0], columns=idx[1])
10000 loops, best of 3: 47.3 µs per loop

edited May 23, 2017 at 18:58

answered May 23, 2017 at 18:48

Divakar

222k19 gold badges273 silver badges374 bronze badges

1 Comment

piRSquared Over a year ago

This is an option I'd always consider. It get's dicey if not all indices are present at all levels. Said another way, the multi-index does not represent a cartesian product.

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related