Fix DataFrame columns when reading an Excel file with a header with merged cells

Question

I want to read with Python Pandas an Excel file which looks like this:

https://www.dropbox.com/s/1usfr3fxfy2qlpp/header_with_merged_cells.xlsx?dl=0

We can see that this Excel file have a header with merged cells

I did

import pandas as pd

df = pd.read_excel("header_with_merged_cells.xlsx", skiprows=3)

print(df)
print(df.dtypes)
print(df.columns)

it returns a DataFrame like:

        ColA ColB ColC  Unnamed: 3           Unnamed: 4 ColD
0        NaT  NaN    1         2.0                    3  NaN
1 2010-01-01    A    A         2.1  2010-02-01 00:00:00    E
2 2010-01-02    B    C         2.2  2010-02-02 00:00:00    F

dtypes like:

ColA          datetime64[ns]
ColB                  object
ColC                  object
Unnamed: 3           float64
Unnamed: 4            object
ColD                  object

columns like:

Index(['ColA', 'ColB', 'ColC', 'Unnamed: 3', 'Unnamed: 4', 'ColD'], dtype='object')

Is there a way to fix columns to get ColA, ColB, ColC.1, ColC.2, ColC.3, ColD or MultiIndex columns ?

One issue is that D5 cell is considered as float (instead of int or str) an other issue is that E column should be considered as datetime64[ns]

header parameter of `read_excel can help:

df = pd.read_excel("header_with_merged_cells.xlsx", skiprows=3, header=[0,1])

but we get a DataFrame like:

ColA                     ColB ColC                               ColD
           Unnamed: 0_level_1    1    2          3 Unnamed: 4_level_1
2010-01-01                  A    A  2.1 2010-02-01                  E
2010-01-02                  B    C  2.2 2010-02-02                  F

dtypes like:

ColA
ColB  Unnamed: 0_level_1            object
ColC  1                             object
      2                            float64
      3                     datetime64[ns]
ColD  Unnamed: 4_level_1            object
dtype: object

columns like:

MultiIndex(levels=[['ColB', 'ColC', 'ColD'], [1, 2, 3, 'Unnamed: 0_level_1', 'Unnamed: 4_level_1']],
           labels=[[0, 1, 1, 1, 2], [3, 0, 1, 2, 4]],
           names=['ColA', None])

That's odd to see columns such as Unnamed: 0_level_1, Unnamed: 4_level_1. Isn't there a way to fix it?

Answer 1

It is not easy.

First add parameter header for creating MultiIndex and then rename Unnamed column names to empty strings.

df = pd.read_excel("header_with_merged_cells.xlsx", skiprows=3, header=[0,1])
df = df.reset_index()
df = df.rename(columns=lambda x: x if not 'Unnamed' in str(x) else '')
df = df.rename(columns={'index':'ColA'})
df.columns.names = (None, None)
print(df)
        ColA ColB ColC                 ColD
                     1    2          3     
0 2010-01-01    A    A  2.1 2010-02-01    E
1 2010-01-02    B    C  2.2 2010-02-02    F