python - 读取带有合并单元格标题的 Excel 文件时修复 DataFrame 列

Question

我想用 Python Pandas 读取一个如下所示的 Excel 文件:

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

我们可以看到这个 Excel 文件有一个包含合并单元格的标题

我做了

import pandas as pd

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

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

它返回一个 DataFrame 如下:

        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 像:

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

列如:

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

有没有办法修复列以获得 ColA、ColB、ColC.1、ColC.2、ColC.3、ColD 或 MultiIndex 列？

一个问题是 D5 单元格被视为 float(而不是 int 或 str)另一个问题是 E 列应被视为 datetime64[ns]

`read_excel 的

header 参数可以帮助:

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

但是我们得到了一个像这样的 DataFrame:

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 像:

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

列如:

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])

看到诸如 Unnamed: 0_level_1、Unnamed: 4_level_1 这样的列很奇怪。没有办法解决吗？

Answer 1

这并不容易。

首先添加用于创建MultiIndex的参数header，然后将Unnamed列名重命名为空字符串。

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