I have a pandas dataframe with a multiindex of 2 levels, and four data columns, and I want to select, first some indices of both levels, and then certain columns. However, I do not find a way to do it. Let me illustrate with an example:
>>> import numpy as np
>>> import pandas as pd
>>> a = pd.MultiIndex.from_arrays(np.array([np.repeat([1, 2], 6), np.tile(np.repeat(["a", "b", "c"], 2), 2)] ) )
>>> df = pd.DataFrame(np.random.randn(12,4), index=a, columns=list('ABCD'))
>>> df
A B C D
1 a 0.688481 -2.380630 1.586150 0.008736
a 0.481300 1.276254 -0.159889 0.568995
b -0.250297 -0.161532 -1.099416 0.362548
b -0.588712 0.403857 -0.573104 1.420252
c -1.026832 -1.297500 -0.084957 0.072306
c -0.262771 0.865048 -0.278121 -1.406952
2 a -0.873142 -0.871211 -1.790412 1.073330
a -2.252715 1.772763 1.784698 1.716706
b 1.108562 -0.622361 0.036408 1.170411
b -0.157282 -0.660653 -1.230480 -0.295144
c -0.241862 -0.471965 0.363945 0.125543
c -0.396109 0.245829 0.544339 -0.957016
So I am able to do this:
>>> df.loc["1","a"]
A B C D
1 a 0.688481 -2.380630 1.586150 0.008736
a 0.481300 1.276254 -0.159889 0.568995
And this:
>>> df.loc[:,["A","B"]]
A B
1 a 0.688481 -2.380630
a 0.481300 1.276254
b -0.250297 -0.161532
b -0.588712 0.403857
c -1.026832 -1.297500
c -0.262771 0.865048
2 a -0.873142 -0.871211
a -2.252715 1.772763
b 1.108562 -0.622361
b -0.157282 -0.660653
c -0.241862 -0.471965
c -0.396109 0.245829
And even this:
>>> df.loc["1","a"]["A"]
1 a 0.688481
a 0.481300
Name: A, dtype: float64
But when I do either of these:
>>> df.loc["1","a",["A","B"]]
>>> df.loc["1","a"]["A","B"]
I get an error. What I want to obtain is this:
A B
1 a 0.688481 -2.380630
a 0.481300 1.276254
I am new to pandas, and I am sure this is quite a basic question, but I cannot find an answer.
Thanks!
Use slicers for complicated selecting:
idx = pd.IndexSlice
a = df.loc[idx['1','a'],idx['A','B']]
print (a)
A B
1 a 0.290874 0.279663
a 0.435876 -0.318957
a = df.loc[idx['1',['a', 'b']],idx['A','B']]
print (a)
A B
1 a -1.329079 0.278034
a 0.955832 -0.877614
b 1.875801 0.415695
b 0.765055 -0.828989
a = df.loc[idx['1',['a', 'c']],:]
print (a)
A B C D
1 a 0.142426 0.541231 1.340099 -1.569256
a -0.510343 -0.447771 0.937850 -0.356663
c -0.900009 -0.931002 -1.222737 -0.393311
c -0.957582 2.056467 -1.888492 -1.128331