Separate multiindex dataframe into multiple single-index dataframes in pandas

Question

I have a multiindex dataframe describing stock movements (OHLC). The indices are date and symbol. The dataframe looks like this:

                             open   high    low  close  volume
date                symbol
2017-12-03 07:00:00 4005    38.75  38.75  38.75  38.75    2518
2018-01-04 09:58:00 4333    12.06  12.06  12.06  12.06    2000
2018-01-22 10:06:00 4338     9.22   9.22   9.22   9.22      10
2018-01-22 11:23:00 4338     9.22   9.22   9.22   9.22    1000
2017-12-14 08:41:00 4334     9.28   9.28   9.28   9.28       2
2018-01-02 10:19:00 4332    10.30  10.30  10.30  10.30     100
2018-01-09 08:56:00 4332    10.28  10.30  10.28  10.30    1500

Is there a way to separate the dataframe by symbol to produce a single-index dataframe with date as the index? Something like this:

In [1]: df.symbol('4338')
Out[1]:
                      open   high    low  close  volume
date               
2018-01-22 10:06:00   9.22   9.22   9.22   9.22      10
2018-01-22 11:23:00   9.22   9.22   9.22   9.22    1000

Any guidance to right direction would be greatly appreciated.

jezrael · Accepted Answer · 2018-01-30 09:24:34Z

3

Simpliest is use xs:

print (df.xs('4338', level=1))
                     open  high   low  close  volume
date                                                
2018-01-22 10:06:00  9.22  9.22  9.22   9.22      10
2018-01-22 11:23:00  9.22  9.22  9.22   9.22    1000

If values are numeric, use:

print (df.xs(4338, level=1))

edited Jan 30, 2018 at 9:24

answered Jan 30, 2018 at 9:14

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

4 Comments

Abdul Over a year ago

I'm getting KeyError when I do that.

jezrael Over a year ago

Maybe need dfs['4338'] change to dfs[4338] - if values are numeric, not string.

Abdul Over a year ago

This worked beautifully after you expanded your answer. Thanks!

jezrael Over a year ago

I change answer again, with simpliest solution ;)

Collectives™ on Stack Overflow

Separate multiindex dataframe into multiple single-index dataframes in pandas

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related