Home > OS >  Custom grouping of Pandas MultiIndex levels
Custom grouping of Pandas MultiIndex levels

Time:09-26

Minimal reproducible example:

import pandas as pd
data = {('Percent', 'Female'): {'Parents': 26.489226869455006, 'Spouse': 31.875792141951838}, ('Percent', 'Male'): {'Parents': 34.49901768172888, 'Spouse': 20.903732809430256}, ('N', 'Female'): {'Parents': 1578.0, 'Spouse': 1578.0}, ('N', 'Male'): {'Parents': 2545.0, 'Spouse': 2545.0}, ('Count', 'Female'): {'Parents': 418.0, 'Spouse': 503.0}, ('Count', 'Male'): {'Parents': 878.0, 'Spouse': 532.0}}
df = pd.DataFrame.from_dict(data)
df

df:

                    Percent                N                Count
        Female      Male        Female  Male      Female    Male
Parents 26.489227   34.499018   1578.0  2545.0    418.0     878.0
Spouse  31.875792   20.903733   1578.0  2545.0    503.0     532.0

I want to group my columns by sex, not by statistic, like in this desired output:

                                   Female                             Male  
                 N     Count      Percent           N     Count    Percent
 Parents    1578.0     418.0      26.48..      2545.0     878.0     34.49..     
 Spouse     1578.0     503.0      31.87..      2545.0     532.0     20.90..

I know how to change the level order using reorder_levels(). That said I do not only want to change their order, but also group them differently, as in the desired output above.

How do I do this?

CodePudding user response:

swap column levels then sort

df.swaplevel(axis=1).sort_index(axis=1)

for custom ordering use reindex on level=0

order = ['Male', 'Female']
df.swaplevel(axis=1).reindex(order, axis=1, level=0)

        Female                      Male                   
         Count       N    Percent  Count       N    Percent
Parents  418.0  1578.0  26.489227  878.0  2545.0  34.499018
Spouse   503.0  1578.0  31.875792  532.0  2545.0  20.903733
  • Related