I have a double Multiindex dataframe as follows. I slice the rows with idx = pd.IndexSlice but I don't know how to do the same with the columns so provided this data:
df = pd.DataFrame(data=pd.DataFrame(data=np.random.randint(0, 10, size=(9, 5))))
# rows
list1 = ['2021-01-01','2022-02-01','2022-03-01']
list2 = ['PHOTO', 'QUE','TXR']
combinations = [(x, y) for x in list1 for y in list2]
df.index = pd.MultiIndex.from_tuples(combinations, names = ["DATE","DB"])
df.index.set_names(["DATE","DB"], inplace=True)
#columns
list1c = [('AB30','ACTIVE','A2'),('CD55','ACTIVE','A1'),('ZT52','UNACTIVE','A2'),('MIKE','PENSIONER','A2'),('ZZ00001','ACTIVE','A1')]
df.columns = pd.MultiIndex.from_tuples(list1c, names = ["UserID","KIND","DEPARTMENT"])
I don't understand why the following does not work:
idx_cols = (slice(None, None, None), slice(None, ['ACTIVE', 'UNACTIVE'], None), slice(None, ['A1'], None))
df.loc[:, idx_cols]
gives the error:
UnsortedIndexError: 'MultiIndex slicing requires the index to be lexsorted: slicing on levels [1, 2], lexsort depth 0'
If I try:
df.columns.levels
I get:
FrozenList([['AB30', 'CD55', 'MIKE', 'ZT52', 'ZZ00001'], ['ACTIVE', 'PENSIONER', 'UNACTIVE'], ['A1', 'A2']])
so level 0 are the names, level 1 ['ACTIVE', 'PENSIONER', 'UNACTIVE'] and level 2 ['A1', 'A2']
How can I solve this problem?
Try using:
idx_cols = pd.IndexSlice[:, ['ACTIVE', 'UNACTIVE'], ["A1"]]
# or
idx_cols = pd.IndexSlice[slice(None), ['ACTIVE', 'UNACTIVE'], ["A1"]]
df.loc[:, idx_cols]
Outputs:
UserID AB30 CD55 ZT52 MIKE ZZ00001
KIND ACTIVE ACTIVE UNACTIVE PENSIONER ACTIVE
DEPARTMENT A2 A1 A2 A2 A1
DATE DB
2021-01-01 PHOTO 2 0 0 2 0
QUE 8 8 8 5 4
TXR 1 9 2 5 3
2022-02-01 PHOTO 9 5 1 6 8
QUE 1 4 3 1 0
TXR 9 5 1 9 9
2022-03-01 PHOTO 0 9 8 5 9
QUE 9 0 8 6 6
TXR 8 4 8 0 0