CODEWITHSUNDEEP
pythonpandas
jf328
jf328

Reputation: 7351

python pandas multiIndex Dataframe, how to select one level based on iloc

df = pd.DataFrame(np.random.rand(100, 2),
    columns=pd.Index(['A', 'B'], name='bar'),
    index=pd.date_range('20160101',
periods=100, freq='D', name='foo'))

corr = df.rolling(12).corr()

In old version, this returns a Panel, so the existing code uses

corr[0,:,:]

which returns the corr matrix of the first date.

However, in the new version, corr is a multiIndex DataFrame and the above code fails. How can I achieve the same output with minimal change?

corr.iloc[0] # only returns first row
corr.iloc[0,:,:] # error

Edit:

The desired output is to get the same return as below, but instead of using .loc with value, use something like .iloc[11] (ie the correlation matrix corresponding to the 12th value in date)

corr.loc['2016-01-12']

bar                    A         B
foo        bar                    
2016-01-12 A    1.000000 -0.115059
           B   -0.115059  1.000000

Upvotes: 3

Views: 2610

Answers (2)

Brad Solomon
Brad Solomon

Reputation: 40918

The issue as alluded to by @MaxU is that .iloc is not "MultiIndex-aware"--see here for a discussion.

An alternate solution for your case:

dates = corr.index.get_level_values(0).drop_duplicates()
corr.loc[dates[12]]  # correl. matrix for 12th date (0-indexed)

To retain as a DataFrame:

corr.loc[[dates[12]]]

Upvotes: 4

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210972

Is that what you want?

In [236]: x = corr.dropna()

In [237]: x.loc[pd.IndexSlice[x.index[0][0], :], :]
Out[237]:
bar                    A         B
foo        bar
2016-01-12 A    1.000000  0.158424
           B    0.158424  1.000000

Upvotes: 3

Related Questions