CODEWITHSUNDEEP
pythonpandas
user3556757
user3556757

Reputation: 3609

MultiIndex Slicing with a Timeseries Row Index

I used the answer to this question to try to make a similar slice on my dataframe. But it doesn't seem to work because my row index is a TimeSeries. I am not sure how to rephrase the slice to work.

The df I'm using has a single TimeSeries index, and the columns are a two-level MultiIndex. I'm attempting to, for an arbitrary row, to return a series of consisting of the "px" subcolumn of each major column. enter image description here

The first attempt: df.loc[0,(slice(None), 'px')] throws a TypeError,

TypeError: cannot do index indexing on <class 'pandas.tseries.index.DatetimeIndex'> with these indexers [0] of <type 'int'>

So I also have tried to feed it a DateTime for the index, instead of an int:

useIndex = sdf.index[0]
return df.loc[useIndex,(slice(None), 'px')]

Which gives a:

KeyError: 'MultiIndex Slicing requires the index to be fully lexsorted tuple len (2), lexsort depth (0)'

Postscript...

If I just do a simple,

useIndex = sdf.index[0]
useIndex
sdf.iloc[useIndex]

I get the failure:

TypeError: cannot do label indexing on <class 'pandas.tseries.index.DatetimeIndex'> with these indexers [2015-10-08 00:00:00] of <class 'pandas.tslib.Timestamp'>

So maybe the problem is that I'm not really passing a valid index to the MultiIndex slice?

================

Here are two examples: The first df ('df') I'm able to pull out the data I want. The second df, ('df2') throws a Type Error.

import pandas as pd
import numpy as np

cols = [['col_1', 'col_2'], ['delta', 'px']]
multi_idx = pd.MultiIndex.from_product(cols, names= ["level_0", "level_1"])
df = pd.DataFrame(np.random.rand(20).reshape(5, 4), index=range(5), columns=multi_idx)

row_number =1 

print df.loc[df.index[row_number], pd.IndexSlice[:, 'px']]

rng = pd.date_range('1/1/2011', periods=5, freq='H')
df2 = pd.DataFrame(np.random.rand(20).reshape(5, 4), index=rng, columns=multi_idx)

#print df2.loc[df.index[row_number], pd.IndexSlice[:, 'px']]
useIndex = df2.index[0] 

print df2.loc[useIndex, pd.IndexSlice[:, 'px']]

Upvotes: 0

Views: 987

Answers (1)

Alexander
Alexander

Reputation: 109546

Using IndexSlice should help get your desired results. Note that the columns first need to be lex sorted:

cols = [['col_1', 'col_2'], ['delta', 'px']]
multi_idx = pd.MultiIndex.from_product(cols, names= ["level_0", "level_1"])
df = pd.DataFrame(np.random.rand(20).reshape(5, 4), index=range(5), columns=multi_idx)

>>> df
level_0     col_1               col_2          
level_1     delta        px     delta        px
0        0.891758  0.071693  0.629897  0.693161
1        0.772542  0.022781  0.684584  0.892641
2        0.925957  0.794940  0.146950  0.134798
3        0.159558  0.842898  0.677927  0.028675
4        0.436268  0.989759  0.471879  0.101878

row_number = 3
>>> df.loc[df.index[row_number], pd.IndexSlice[:, 'px']]
level_0  level_1
col_1    px         0.842898
col_2    px         0.028675
Name: 3, dtype: float64

Upvotes: 1

Related Questions