pandas sort multilevel columns with mixed datatypes

Idea is find all rows with all before column (D, E) first to mask, then filter rows to df1 which not match, sorting and extract index values used for mapping original index values and last sorting:

print (df)
mask = (df.iloc[:, : df.columns.get_loc(('D','E'))]
          .apply(lambda x: x.astype(str).str.contains('all'))
print (mask)
0    False
1     True
2     True
3    False
4    False
5    False
dtype: bool

df1 = df[~mask].copy()
df1['tmp'] = pd.to_numeric(df1[('D','E')].str.get('value'), errors='coerce')
idx = df1.sort_values('tmp', ascending=False).index
print (idx)
Int64Index([0, 4, 3, 5], dtype='int64')

d = dict(zip(df.index[~mask], idx))
print (d)
{0: 0, 3: 4, 4: 3, 5: 5}

df = df.set_index(df.rename(d).index).sort_index()
print (df)
         A                                             D
         E                                             E
0        a  {'value': '126', 'perc': None, 'unit': None}
1  b:all:c    {'value': 324, 'perc': None, 'unit': None}
2  all:1:3  {'value': 'N/A', 'perc': None, 'unit': None
3        d  {'value': '100', 'perc': None, 'unit': None}
4        c                                            {}
5        e                                           NaN

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top