Search code examples
pythonpandastabularheading

Grouping Python Panda table with additional heading

I have the following table:

NSRCODE CM        CM % of LSA   LBH    LBH % of LSA     Total Area  Total Area % of LSA
PBL_AWI                     
BFNN    0.000000    0.000000    28.9207     0.338512    28.920700   0.323014
BONS    4.470549    1.090581    914.0085    10.698329   918.479049  10.258446
BTNI    0.000000    0.000000    3.3713      0.039461    3.371300    0.037654
BTNN    25.385459   6.192732    1974.8005   23.114736   2000.185959 22.339976
FONG    4.162559    1.015448    168.7122    1.974750    172.874759  1.930829
FONS    1.681416    0.410178    516.9960    6.051359    518.677416  5.793082
FTNI    0.000000    0.000000    31.7252     0.371339    31.725200   0.354337
FTNN    0.000000    0.000000    653.6473    7.650841    653.647300  7.300554
SONS    58.395844   14.245548   437.9098    5.125667    496.305644  5.543213
STNN    4.560384    1.112496    0.0000      0.000000    4.560384    0.050935

I'm looking for a way to group the table so that it looks like this. Where each two columns are grouped under a heading, but the NSRCODE still appears as is.

             Central M                Lower BH                 Summary
NSRCODE CM        CM % of LSA   LBH    LBH % of LSA     Total Area  Total Area % of LSA
PBL_AWI                     
BFNN    0.000000    0.000000    28.9207     0.338512    28.920700   0.323014
BONS    4.470549    1.090581    914.0085    10.698329   918.479049  10.258446
BTNI    0.000000    0.000000    3.3713      0.039461    3.371300    0.037654
BTNN    25.385459   6.192732    1974.8005   23.114736   2000.185959 22.339976
FONG    4.162559    1.015448    168.7122    1.974750    172.874759  1.930829
FONS    1.681416    0.410178    516.9960    6.051359    518.677416  5.793082
FTNI    0.000000    0.000000    31.7252     0.371339    31.725200   0.354337
FTNN    0.000000    0.000000    653.6473    7.650841    653.647300  7.300554
SONS    58.395844   14.245548   437.9098    5.125667    496.305644  5.543213
STNN    4.560384    1.112496    0.0000      0.000000    4.560384    0.050935

[EDIT] As per @Chrisb

after doing the mappings I get this:

Total Area   Total Area % of LSA    LBH % of LSA    LBH       CM          CM % of LSA
Summary            Summary          Lower BH        Lower BH  Central M   Central M

I'm trying to get the Labels that are currently on the second row on the first, and so that they appear only once of each set.

For example:

        Summary
Total Area   Total Area % of LSA

Where Summary covers both values below it.

Solution

  • First, define your mapping:

    col_mapping = {'Central M': ['CM', 'CM % of LSA'], 
               'Lower BH': ['LBH % of LSA', 'LBH'], 
                'Summary': ['Total Area', 'Total Area % of LSA']}

    Unpack that into a list of tuples:

    cols = [(k,v) for k,sublist in col_mapping.items() 
              for v in sublist]

    Assign as a MultiIndex

    df.columns = pd.MultiIndex.from_tuples(cols)