Using data with a hierarchal index, is there a way to easily select a range of values? All the method's I've seen, including xs and .loc, seem to be limited to a single value, see Benefits of panda's multiindex?. Using this example data,
from pandas import *
from numpy import *
import itertools as it
M = 100 # Number of rows to generate
# Create some test data with multiindex
df = DataFrame(randn(M, 10))
df.index = [randint(4, size=M), randint(8, size=M)]
df.index.rename(['a', 'b'])
I'd like to be able to select everything where the first index is 1 or two and the second index is 3 or 4. The closest I've come is using .loc with a list of tuples
# Now extract a subset
part = df.loc[[(1, 3), (1,4), (2,3), (2,4)]]
But this gives some odd behavior,
# The old indices are still shown for some reason
print(part.index.levels)
# Good indexing
print("correct:\n", part.loc[(1, 1)])
# No keyerror, although the key wasn't included
print("wrong:\n", part.loc[[(0, 3)]])
# Indexing of first index, and then a column, very odd
print("odd:\n", part.loc[(1, 9)])
# But there is an error accessing the original this way
print("Expected error:\n", df.loc[(1, 9)])
Output:
In [436]: [[0, 1, 2, 3], [0, 1, 2, 3, 4, 5, 6, 7]]
correct:
0 1 2 3 4 5 6 \
1 3 -0.183667 0.578867 -0.944514 0.026295 0.778354 0.603845 0.636486
3 -0.337596 0.018084 -0.654721 -1.121475 -0.561706 0.695095 -0.512936
3 -0.670779 -0.425093 1.262278 -1.806815 0.855900 -0.230683 -0.225658
3 -0.274808 -0.529901 1.265333 0.559646 -1.418687 0.492577 0.141648
7 8 9
1 3 1.109179 -1.569236 -0.617408
3 -0.659310 1.249105 0.032657
3 0.315601 1.100192 -0.389736
3 -0.267462 -0.025189 0.069047
odd:
3 -0.617408
3 0.032657
3 -0.389736
3 0.069047
4 0.217577
4 -0.232357
Name: 9, dtype: float64
wrong:
0 1 2 3 4 5 6 7 8 9
0 3 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
---------------------------------------------------------------------------
KeyError Traceback (most recent call last)
(truncated)
So is there a better way than a list of tuples to access multiple parts of a hierarchal index? If not, is there a way to clean up the result after indexing with tuples so that sensible errors are given, rather than NaN?
