CODEWITHSUNDEEP
pandaspython-3.8
Zanam
Zanam

Reputation: 4807

Display pandas dataframe in excel file with split level column and merged cells

I have a large dataframe df as:

Col1    Col2    ATC_Dzr ATC_Last    ATC_exp Op_Dzr2 Op_Last2
1Loc    get1    0.26    3.88        3.73    0.16    3.15
2Loc    get2    0.4     -0.85      -0.86    0.1     -0.54
3Loc    get3    -0.59   1.47        2.01    -0.53   1.29

I need to dump this to excel so that it looks as following:

enter image description here

where ATC and Op are in a merged cells

I am not sure how to approach this?

Upvotes: 1

Views: 75

Answers (1)

anky
anky

Reputation: 75080

You can set the first 2 columns as index and split the rest and expand to create a Multiindex:

df1 = df.set_index(['Col1','Col2'])
df1.columns = df1.columns.str.split('_',expand=True)
print(df1)

        ATC                Op      
        Dzr  Last   exp  Dzr2 Last2
Col1 Col2                              
1Loc get1  0.26  3.88  3.73  0.16  3.15
2Loc get2  0.40 -0.85 -0.86  0.10 -0.54
3Loc get3 -0.59  1.47  2.01 -0.53  1.29

Then export df1 into excel.

As per coments by @Datanovice , you can also use Pd.MultiIndex.from_tuples:

df1 = df.set_index(['Col1','Col2'])
df1.columns = pd.MultiIndex.from_tuples([(col.split('_')[0], col.split('_')[1])
                                        for col in df1.columns])
print(df1)
          ATC                Op      
          Dzr  Last   exp  Dzr2 Last2
Col1 Col2                              
1Loc get1  0.26  3.88  3.73  0.16  3.15
2Loc get2  0.40 -0.85 -0.86  0.10 -0.54
3Loc get3 -0.59  1.47  2.01 -0.53  1.29

Upvotes: 3

Related Questions