python - 如何将 Excel PivotCache 提取到 Pandas Data Frame 中？

Question

第一次在这里发帖，如果之前有人问过这个问题，我深表歉意 - 我找不到任何适用的内容。

有没有办法将底层数据从 Excel 数据透视表读取到 Pandas 数据框中？多年来，我一直使用 Excel Auto_Open 宏，它可以下载多个 Excel 文件并双击“总计”行以提取所有数据，这些数据最终会导入到数据库中。这样做是因为源数据的所有者拒绝授予对数据库本身的访问权限。

这个宏从来都不是理想的场景，我们需要尽快将其移至更好的方法。我拥有丰富的 SQL 知识，但最近才开始学习 Python。

我已经能够使用 OpenPyXl 读取工作表，但默认情况下这些文件不包含单独工作表上的源数据 - 数据透视缓存必须先提取到新工作表。如果可能的话，我想做的是从 Excel PivotCache 读取到 Pandas 数据框中，然后将该输出保存为 CSV 文件或将其直接加载到我们的数据库中。这似乎不适用于 OpenPyXl，我可能需要使用 win32com.client。

有没有人对此有任何经验，并且知道这是否可能？关于我可以从哪里开始的任何指示？我尝试了 Excel 对象模型中的几个项目(PivotCache、GetData 等)，但要么我不知道如何使用它们，要么它们没有返回我需要的内容。

任何帮助将不胜感激。谢谢!

Answer 1

这个答案很晚，但我在努力解决同样的问题时想到了它，上面的一些评论帮助我确定了它。

本质上，用 openpyxl 解决这个问题的步骤是:

1. 使用 openpyxl 从所需的数据透视表中获取 openpyxl.pivot.table.TableDefinition 对象(我们称它为 my_pivot_table)<
2. 从 my_pivot_table.cache.cacheFields 获取缓存字段及其值
3. 通过两个子步骤将行数据作为 dict 获取:
   • 3.1) 从 my_pivot_table.cache.records.r 中获取所有缓存行及其值。这些记录中的缓存字段存储为 my_pivot_table.cache.cacheFields
   中的索引
   • 3.2) 通过“连接”cache.records.r 和 cache.cacheFields
   ，用实际值替换每条记录的缓存字段
4. 将包含行的 dict 转换为 pandas DataFrame

您将在下面找到实现此类解决方案的代码副本。由于这些 Excel 对象的结构有些复杂，因此代码可能看起来非常晦涩(对此深表歉意)。为了解决这个问题，我在下面进一步添加了被操纵的主要对象的最小示例，以便人们可以更好地了解正在发生的事情、返回的对象是什么等等。

这是我能找到的实现此目标的最简单方法。我希望它对某些人仍然有用，尽管个别情况可能需要进行一些调整。

“裸”代码

import numpy as np
import pandas as pd
from openpyxl import load_workbook
from openpyxl.pivot.fields import Missing

file_path = 'path/to/your/file.xlsx'

workbook = load_workbook(file_path)
worksheet = workbook['Plan1']

# Name of desired pivot table (the same name that appears within Excel)
pivot_name = 'Tabela dinâmica1'

# Extract the pivot table object from the worksheet
pivot_table = [p for p in worksheet._pivots if p.name == pivot_name][0]

# Extract a dict of all cache fields and their respective values
fields_map = {}
for field in pivot_table.cache.cacheFields:
    if field.sharedItems.count > 0:
        fields_map[field.name] = [f.v for f in field.sharedItems._fields]

# Extract all rows from cache records. Each row is initially parsed as a dict
column_names = [field.name for field in pivot_table.cache.cacheFields]
rows = []
for record in pivot_table.cache.records.r:
    # If some field in the record in missing, we replace it by NaN
    record_values = [
        field.v if not isinstance(field, Missing) else np.nan for field in record._fields
    ]

    row_dict = {k: v for k, v in zip(column_names, record_values)}

    # Shared fields are mapped as an Index, so we replace the field index by its value
    for key in fields_map:
        row_dict[key] = fields_map[key][row_dict[key]]

    rows.append(row_dict)

df = pd.DataFrame.from_dict(rows)

结果:

>>> df.head(2)

            FUEL    YEAR   REGION     STATE  UNIT       Jan       Feb  (...)
0  GASOLINE (m3)  2000.0        S     TEXAS    m3  9563.263  9563.263  (...)
1  GASOLINE (m3)  2000.0       NE  NEW YORK    m3  3065.758  9563.263  (...)

部分对象详情

对象数据透视表

这是一个 openpyxl.pivot.table.TableDefinition 类型的对象。这是相当复杂的。一瞥:

<openpyxl.pivot.table.TableDefinition object>
Parameters:
name='Tabela dinâmica1', cacheId=36, dataOnRows=True, dataPosition=None, (A LOT OF OMITTED STUFF...)
Parameters:
ref='B52:W66', firstHeaderRow=1, firstDataRow=2, firstDataCol=1, rowPageCount=2, colPageCount=1, pivotFields=[<openpyxl.pivot.table.PivotField object>
Parameters: (A LOT OF OMITTED STUFF...)

对象fields_map(来自cache.cacheFields)

这是一个包含列名及其可用值的 dict:

{'YEAR': [2000.0, 2001.0, 2002.0, 2003.0, 2004.0, 2005.0, 2006.0, 2007.0, 2008.0,
          2009.0, 2010.0, 2011.0, 2012.0, 2013.0, 2014.0, 2015.0, 2016.0, 2017.0,
          2018.0, 2019.0, 2020.0],
 'FUEL': ['GASOLINE (m3)', 'AVIATION GASOLINE (m3)', 'KEROSENE (m3)'],
 'STATE': ['TEXAS', 'NEW YORK', 'MAINE', (...)],
 'REGION': ['S', 'NE', 'N', (...)]}

对象row_dict(映射前)

每一行都是一个包含列名及其值的dict。缓存字段的原始值不存储在这里。在这里它们由它们在 cache.cacheFields 中的索引表示(见上文)

{'YEAR': 0,  # <<<--- 0 stands for index in fields_map
 'Jan': 10719.983,
 'Feb': 12482.281,
 'FUEL': 0,  # <<<--- index in fields_map
 'Dec': 10818.094,
 'STATE': 0,  # <<<--- index in fields_map
 (...)
 'UNIT': 'm3'}

对象row_dict(映射后)

从缓存字段的索引中提取原始值后，我们有一个 dict 表示一行的所有值:

{'YEAR': 2000.0,  # extracted column value from index in fields_map
 'Jan': 10719.983,
 'Feb': 12482.281,
 'FUEL': 'GASOLINE (m3)',  # extracted from fields_map
 'Dec': 10818.094,
 'STATE': 'TEXAS',  # extracted from fields_map
 (...)
 'UNIT': 'm3'}