This is the dataframe I have:
iterables = [['bar', 'baz', 'foo', 'qux'], ['one', 'two', 'three', 'four']]
mindex = pd.MultiIndex.from_product(iterables, names=['first', 'second'])
df = pd.DataFrame(np.random.randn(16, 3), index=mindex)
And it looks like this:
0 1 2
first second
bar one -0.445212 -2.208192 -1.297759
two 1.521942 0.592622 -1.677931
three 0.709292 0.348715 -0.766430
four -1.812516 -0.982077 -1.155860
baz one -0.375230 -0.267912 2.621249
two -1.041991 -0.752277 -0.494512
three -1.029389 -0.331234 0.950335
four -1.357269 0.653581 1.289331
foo one 0.980196 0.865067 -0.780575
two -1.641748 0.220253 2.141745
three 0.272158 -0.320238 0.787176
four -0.265425 -0.767928 0.695651
qux one -0.117099 1.089503 -0.692016
two -0.203240 -0.314236 0.010321
three 1.425749 0.268420 -0.886384
four 0.181717 -0.268686 1.186988
I would like to select a sub-set of the dataframe for each element in the first index, such that the only the one and three index values from the second level of the multiindex are used.
I have checked this out in the advanced indexing section of the documentation, but without much success. One can sub-select a specific index value from the second index level:
df.loc['bar','one']
Out[74]:
0 -0.445212
1 -2.208192
2 -1.297759
Name: (bar, one), dtype: float64
But not a tuple of values, because this:
df.loc[('bar',('one','three'))]
results in an error:
KeyError: "None of [('one', 'three')] are in the [columns]"
I expected .loc to basically deliver bar and then rows that have second-level index values of one and three by this command.
How can I perform this kind of a sub-selection based on multi-index level sub-sets?
Add : for select all columns:
a = df.loc[('bar',('one','three')), :]
print (a)
0 1 2
first second
bar one -0.902444 2.115037 -0.065644
three 2.095998 0.768128 0.413566
Similar solution with IndexSlice:
idx = pd.IndexSlice
a = df.loc[idx['bar', ('one','three')], :]
print (a)
0 1 2
first second
bar one -0.515183 -0.858751 0.854838
three 2.315598 0.402738 -0.184113
As @Brad Solomon mentioned if want all values of first level:
df1 = df.loc[(slice(None), ['one', 'three']), :]
idx = pd.IndexSlice
df1 = df.loc[idx[:, ('one','three')], :]
print (df1)
0 1 2
first second
bar one -0.266926 1.105319 1.768572
three -0.632492 -1.642508 -0.779770
baz one -0.380545 -1.632120 0.435597
three 0.018085 2.114032 0.888008
foo one 0.539179 0.164681 1.598194
three 0.051494 0.872987 -1.882287
qux one -1.361244 -1.520816 2.678428
three 0.323771 -1.691334 -1.826938