Replacing multiple dataframe headers with a single header with both header information

Question

I have used pivot table in pandas and have got the desired format of dataframe but now I have two rows of header. The resultant dataframe after pivot table is as follows:

scenario     Actual               Plan
         LY_USD_AMT USD_AMT LY_USD_AMT USD_AMT
package
Africa            3       3          0       0
Brazil            1       1          1       1
Canada            1       1          1       1
Mexico            0       0          1       1

I have managed to delete the last row of the header using the following:

pd_piv.columns = pd_piv.columns.droplevel(-1)

But at this point, it becomes difficult to identify which row is which as it renders column names like

LY_USD_AMT     USD_AMT     LY_USD_AMT     USD_AMT

Is there anyway to resolve this issue, maybe combine the two headers and get a simpler tabular dataframe like the one below. I need a simple table since I am going to feed this to an external system which recognises only one header line.

ACTUAL_LY_USD_AMT     ACTUAL_USD_AMT     Plan_LY_USD_AMT     Plan_USD_AMT

Answer 1

You can combine both the headers:

df.columns = [c[0] + "_" + c[1] for c in df.columns]

This would change the multiple headers to a combined header.

Eg.:

My dataframe with multiple headers:

    location                            location2
    S1          S2          S3          S1          S2          S3
a   -1.268587   0.014928    0.121195    -1.250765   0.321319    0.017481

Output from the above code:

    location_S1 location_S2 location_S3 location2_S1    location2_S2    location2_S3
a   -1.268587   0.014928    0.121195    -1.250765   0.321319    0.017481