python - 解析Excel报告

Question

我有一个 Excel 工作簿报告(abc.xlsx)，我正在尝试解析该报告以从几列中获取数据，我想知道如何在 Python/Pandas 中完成此操作。该报告采用报告格式，因此它的格式不像行列配置那样很好，它看起来像这样:

                                 My Report                                 ABC LLC                        from 06/01/2015 to 06/30/2015 Instrument                Identifier    Quantity    Start    End     Total North America                            XYZ           100         0      0        (1,893.52)North America Subtotal                                                (1,893.52)Europe                            ABC            50         10     20        (4,653.21)Europe Subtotal                                                       (4,653.21     

我有兴趣获取的数据是标识符(在我的 Excel 中，它位于 B 列和 C 列中，并且它们被合并)和总计(G 列)。这里的挑战是这种格式有点不规则并且带有小计行。我可以想到的一种方法是使用 read_excel 通过 Pandas 读取此内容，然后迭代行并只考虑填充 B&C 和 G 列的行，但不确定如何处理标题加上合并单元格的情况。这种方法也很丑陋，所以我想知道是否有人有在 Excel 中解析类似报告的经验。

Answer 1

这就是我处理这个问题的方式。当然，它并不适用于所有数据处理情况，但它似乎适用于您提供的示例。

将使用pd.read_excel，然后在导入时跳过前几行以将其移开。

In [1]: import pandas as pd

In [2]: df = pd.read_excel("abc.xls", skiprows=4)

In [3]: df
Out[3]:
      Instrument Identifier  Quantity  Start       End    Total
0            NaN        NaN       NaN    NaN       NaN      NaN
1  North America        NaN       NaN    NaN       NaN      NaN
2            NaN        XYZ       100      0         0  1893.52
3  North America        NaN       NaN    NaN  Subtotal  1893.52
4            NaN        NaN       NaN    NaN       NaN      NaN
5         Europe        NaN       NaN    NaN       NaN      NaN
6            NaN        ABC        50     10        20  4653.21
7         Europe        NaN       NaN    NaN  Subtotal  4653.21

在这里，我将使用 fillna 方法的 ffill 参数来转发 Instruments 列中 NaN 值的填充。

In [4]: df.Instrument.fillna(method="ffill", inplace=True)

In [5]: df
Out[5]:
      Instrument Identifier  Quantity  Start       End    Total
0            NaN        NaN       NaN    NaN       NaN      NaN
1  North America        NaN       NaN    NaN       NaN      NaN
2  North America        XYZ       100      0         0  1893.52
3  North America        NaN       NaN    NaN  Subtotal  1893.52
4  North America        NaN       NaN    NaN       NaN      NaN
5         Europe        NaN       NaN    NaN       NaN      NaN
6         Europe        ABC        50     10        20  4653.21
7         Europe        NaN       NaN    NaN  Subtotal  4653.21

现在只需从 Identifier 列中过滤掉空值即可。

In [6]: df[df.Identifier.notnull()]
Out[6]:
      Instrument Identifier  Quantity  Start End    Total
2  North America        XYZ       100      0   0  1893.52
6         Europe        ABC        50     10  20  4653.21