CODEWITHSUNDEEP
pythonpython-3.xpandasdataframesorting
Mayank Porwal
Mayank Porwal

Reputation: 34086

Pandas: Sort innermost column group-wise based on a multilevel column excluding one row which matches a certain substring

This is an extension to my previous question:

Below df:

In [225]: df = pd.DataFrame({'A': ['a'] * 12,
     ...:                'B': ['b'] * 12,
     ...:                'C': ['C1', 'C1', 'C2','C1', 'C3', 'C3', 'C2', 'C3', 'C3', 'C2', 'C2', 'C1'],
     ...:                'D': ['D1', 'D2', 'D1', 'D3', 'D3', 'D2', 'D4', 'all', 'D1', '0:1:all', 'D3', 'x:all:1'],
     ...:                'E': [{'value': '4', 'percentage': None}, {'value': 5, 'percentage': None}, {'value': 12, 'percentage': None}, {'value': 9, 'percentage': None}, {'value': '12', 'percentage': None}, {'value': 'N/A', 'percentage': None}, {}, {'val
     ...: ue': 24, 'percentage': None}, {'value': 12, 'percentage': None}, {'value': 33, 'percentage': None}, {'value': 11, 'percentage': None}, {'value': '9', 'percentage': None}]})
     ...:

Pivot of above df:

In [227]: x = df.pivot(['B', 'C', 'D'], 'A', ['E'])

In [228]: x
Out[228]: 
                                                 E
A                                                a
B C  D                                            
b C1 D1         {'value': '4', 'percentage': None}
     D2           {'value': 5, 'percentage': None}
     D3           {'value': 9, 'percentage': None}
     x:all:1    {'value': '9', 'percentage': None}
  C2 0:1:all     {'value': 33, 'percentage': None}
     D1          {'value': 12, 'percentage': None}
     D3          {'value': 11, 'percentage': None}
     D4                                         {}
  C3 D1          {'value': 12, 'percentage': None}
     D2       {'value': 'N/A', 'percentage': None}
     D3        {'value': '12', 'percentage': None}
     all         {'value': 24, 'percentage': None}

I want to sort the innermost column which is D for each group of outer columns B and C based on the multi-level column with index (E, a) in asc/desc order based on value key from dict.

But, for every group there would be a row with the total value of all other rows. The row with total is identified by the value which has all in substring. I always need to keep that row at the last irrespective of sorting order(asc or desc).

Expected output in case of asc:

Out[228]: 
                                                 E
A                                                a
B C  D                                            
b C1 D1         {'value': '4', 'percentage': None}
     D2           {'value': 5, 'percentage': None}
     D3           {'value': 9, 'percentage': None}
     x:all:1    {'value': '9', 'percentage': None}
  C2 D3          {'value': 11, 'percentage': None}
     D1          {'value': 12, 'percentage': None}
     D4                                         {}
     0:1:all     {'value': 33, 'percentage': None}
  C3 D1          {'value': 12, 'percentage': None}
     D3        {'value': '12', 'percentage': None}
     D2       {'value': 'N/A', 'percentage': None}
     all         {'value': 24, 'percentage': None}

Expected output in case of desc:

Out[228]: 
                                                 E
A                                                a
B C  D                                            
b C1 D3           {'value': 9, 'percentage': None}
     D2           {'value': 5, 'percentage': None}
     D1         {'value': '4', 'percentage': None}
     x:all:1    {'value': '9', 'percentage': None}
  C2 D1          {'value': 12, 'percentage': None}
     D3          {'value': 11, 'percentage': None}
     D4                                         {}
     0:1:all     {'value': 33, 'percentage': None}
  C3 D1          {'value': 12, 'percentage': None}
     D3        {'value': '12', 'percentage': None}
     D2       {'value': 'N/A', 'percentage': None}
     all         {'value': 24, 'percentage': None}

Upvotes: 2

Views: 38

Answers (1)

jezrael
jezrael

Reputation: 863226

Use Index.get_level_values with str.contains for test all:

lvls = list(x.index.names[:-1])
print (lvls)
['B', 'C']

x[('tmp', 'tmp')] = pd.to_numeric(x[('E','a')].str.get('value'), errors='coerce')

x[('max','tmp')] = x.index.get_level_values(-1).str.contains('all')


x1 = x.sort_values(lvls + [('max','tmp'), ('tmp','tmp')])
print (x1)
                                                 E   tmp    max
A                                                a   tmp    tmp
B C  D                                                         
b C1 D1         {'value': '4', 'percentage': None}   4.0  False
     D2           {'value': 5, 'percentage': None}   5.0  False
     D3           {'value': 9, 'percentage': None}   9.0  False
     x:all:1    {'value': '9', 'percentage': None}   9.0   True
  C2 D3          {'value': 11, 'percentage': None}  11.0  False
     D1          {'value': 12, 'percentage': None}  12.0  False
     D4                                         {}   NaN  False
     0:1:all     {'value': 33, 'percentage': None}  33.0   True
  C3 D1          {'value': 12, 'percentage': None}  12.0  False
     D3        {'value': '12', 'percentage': None}  12.0  False
     D2       {'value': 'N/A', 'percentage': None}   NaN  False
     all         {'value': 24, 'percentage': None}  24.0   True

And:

x2 = x.sort_values(lvls + [('max','tmp'), ('tmp','tmp')],
                   ascending=[True] * len(lvls) + [True, False])
print (x2)
                                                 E   tmp    max
A                                                a   tmp    tmp
B C  D                                                         
b C1 D3           {'value': 9, 'percentage': None}   9.0  False
     D2           {'value': 5, 'percentage': None}   5.0  False
     D1         {'value': '4', 'percentage': None}   4.0  False
     x:all:1    {'value': '9', 'percentage': None}   9.0   True
  C2 D1          {'value': 12, 'percentage': None}  12.0  False
     D3          {'value': 11, 'percentage': None}  11.0  False
     D4                                         {}   NaN  False
     0:1:all     {'value': 33, 'percentage': None}  33.0   True
  C3 D1          {'value': 12, 'percentage': None}  12.0  False
     D3        {'value': '12', 'percentage': None}  12.0  False
     D2       {'value': 'N/A', 'percentage': None}   NaN  False
     all         {'value': 24, 'percentage': None}  24.0   True

Last remove helper columns:

x1 = x1.drop([('max','tmp'), ('tmp','tmp')], axis=1)
x2 = x2.drop([('max','tmp'), ('tmp','tmp')], axis=1)

Upvotes: 2

Related Questions