How to select a set of multiindex column based on top index

user_6446053 0
Viewed 71

Given a df with the following column,

col=['id','nval',('psd','F1_b0'),('psd','F2_b0'),('psd','F3_b0'),('psd','F1_b2'),('psd','F2_b2'),
('hj','F1'),('hj','F2'),('hj','F3'),('hj','C1'),('hj','C2'),('hj','C3')]

The objective is to select only these columns

('psd','F1_b0'),('psd','F2_b0'),('psd','F3_b0'),('psd','F1_b2'),('psd','F2_b2')

But, accessing via

df.loc[:,  ('psd', slice(None))]

Return an error

TypeError: unhashable type: 'slice'

May I know how to properly slice the multiindex column

The following snippet can be used to reproduce the error

import pandas as pd
import numpy as np
col=['id','nval',('psd','F1_b0'),('psd','F2_b0'),('psd','F3_b0'),('psd','F1_b2'),('psd','F2_b2'),
('hj','F1'),('hj','F2'),('hj','F3'),('hj','C1'),('hj','C2'),('hj','C3')]

df=pd.DataFrame(np.random.rand(10,len(col)),columns=col)

h=df.loc[:,  ('psd', slice(None))]

Update on another attemp:

df.loc[:,  'psd']

Return

KeyError: 'psd'

Pandas version

pandas 1.3.5

pandas slice python
3 Answers

Add pd.MultiIndex.from_tuples to define the columns, to make it MultiIndex:

import pandas as pd
import numpy as np

col = pd.MultiIndex.from_tuples([('psd','F1_b0'),('psd','F2_b0'),('psd','F3_b0'),('psd','F1_b2'),('psd','F2_b2'),
('hj','F1'),('hj','F2'),('hj','F3'),('hj','C1'),('hj','C2'),('hj','C3')])

df = pd.DataFrame(np.random.rand(10,len(col)), columns=col)

df.loc[:, ('psd', slice(None))]
user_81702150

Lets try filter

 df.filter(regex='_b', axis=1)

Or use the loc accessor with integer columns.

df.iloc[:,2:7]
user_89869750

Your column index is not a MultiIndex so you cannot use slice. An alternative:

cols = df.columns[df.columns.map(lambda x: isinstance(x, tuple) and x[0] == 'psd')]
print(df[cols])

# Output
   (psd, F1_b0)  (psd, F2_b0)  (psd, F3_b0)  (psd, F1_b2)  (psd, F2_b2)
0      0.833580      0.957044      0.368044      0.494838      0.339509
1      0.523737      0.864436      0.388843      0.212192      0.475181
2      0.372662      0.964883      0.081386      0.042451      0.296796
3      0.782194      0.036656      0.267184      0.205224      0.258894
4      0.546433      0.603225      0.988794      0.092446      0.064287
5      0.096330      0.966162      0.322264      0.293168      0.701630
6      0.452430      0.194375      0.944863      0.417981      0.078965
7      0.284814      0.078355      0.915552      0.642981      0.817181
8      0.241247      0.841468      0.020932      0.894918      0.436130
9      0.159180      0.988080      0.726285      0.600094      0.158365
user_152399510
Related