如何使用pandas实现带下拉筛选功能的类Excel透视表？

首先明确：pandas生成的透视表是内存中的DataFrame结构，本身不携带交互筛选控件，要实现Excel式的下拉筛选，可根据需求选择以下两种方案：

方案1：pandas生成透视表+导出Excel时添加表头下拉筛选 #

适合仅需要基础下拉筛选、不需要透视表原生刷新/调整功能的场景，实现逻辑最简单：

1. 生成透视表后将索引转为普通列，方便导出后识别为表头
2. 导出Excel时调用Excel引擎的自动筛选接口，给所有表头添加下拉筛选
   示例代码：

import pandas as pd

# 你的原有透视表生成逻辑
pivot = pd.pivot_table(
    df2,
    index=['Sold-To Name'],
    values=['Order Qty (L)',],
    aggfunc='sum'
)

# 重置索引把行字段转为普通列
pivot = pivot.reset_index()

# 导出到Excel并添加下拉筛选
with pd.ExcelWriter('透视表结果.xlsx', engine='xlsxwriter') as writer:
    pivot.to_excel(writer, sheet_name='透视表', index=False)
    # 获取工作表对象
    worksheet = writer.sheets['透视表']
    # 设置全表表头自动筛选，参数分别是：起始行、起始列、结束行、结束列
    worksheet.autofilter(0, 0, pivot.shape[0], pivot.shape[1]-1)

导出后打开Excel即可看到所有表头都带下拉筛选选项。

方案2：直接生成Excel原生透视表（与手动创建的Excel透视表功能完全一致） #

如果需要和Excel原生透视表完全一致的体验（包括筛选器区域、行/列字段下拉、支持刷新调整字段等），可以直接用openpyxl在Excel文件中创建原生透视表，完美对齐Excel的行、列、值、筛选器四个字段区域的配置：
示例代码：

import pandas as pd
from openpyxl import load_workbook
from openpyxl.pivot.table import TableDefinition

# 第一步：先把原始数据源写入Excel的专用sheet
with pd.ExcelWriter('原生透视表.xlsx', engine='openpyxl') as writer:
    df2.to_excel(writer, sheet_name='数据源', index=False)
    wb = writer.book
    # 新建透视表展示sheet
    pivot_sheet = wb.create_sheet('透视表')
    
    # 定义数据源范围
    max_row = df2.shape[0] + 1
    max_col = df2.shape[1]
    data_ref = f'数据源!A1:{chr(ord("A") + max_col -1)}{max_row}'
    
    # 创建透视表，参数完全对齐Excel透视表配置
    pivot_table = TableDefinition(
        name="销售透视表",
        cache=wb._pivots.add(wb['数据源'][data_ref]),
        # 对应Excel透视表的「行」区域
        rows=['Sold-To Name'],
        # 对应Excel透视表的「值」区域，同时指定聚合方式
        values=['Order Qty (L)'],
        values_aggregation={'Order Qty (L)': 'sum'},
        # 对应Excel透视表的「筛选器」区域，你要添加的全局筛选字段放这里即可
        filters=['省份', '销售日期'] # 替换为你实际需要的筛选字段
    )
    
    # 将透视表挂载到指定位置
    pivot_sheet.add_pivot(pivot_table, 'A3')

导出后打开Excel得到的就是完全原生的透视表，所有筛选下拉、字段调整功能和你手动在Excel中插入的透视表没有任何区别。

内容的提问来源于stack exchange，提问作者play_something_good