如何为Pandas透视表添加字符串对比公式列并导出至Excel

原代码的问题 #

你之前的尝试有两个核心错误：

1. for c in kdPT.shape[0]是直接迭代整数（行数），会触发"int is not iterable"错误，应该迭代Excel行号范围
2. 硬编码列号E不准确，因为透视表是多层索引列，导出后Application的实际列位置可能和你假设的不一样

两种可行解决方案 #

方案一：在Pandas中直接添加公式列（适合简单场景）

先确定目标列的Excel位置，再生成对应公式，注意要指定openpyxl引擎来保留公式：

from openpyxl.utils import get_column_letter
import pandas as pd

# 自定义聚合函数（补充原代码缺失部分）
def iDistinct_count(x):
    return x.nunique()
def iValues(x):
    return ', '.join(x.unique())

# 原透视表生成逻辑
kdPT = pd.pivot_table(df, index="iAID", values=["Application", "Env"], 
                      aggfunc=[iDistinct_count, iValues], margins=False)

# 确定要检查的Application列在Excel中的位置
# 透视表导出后，索引列是A列，DataFrame的列对应B、C、D、E...
# 示例：检查iValues下的Application，对应Excel第4列（D列）
target_excel_col = 4  # 可根据实际列结构调整
target_col_letter = get_column_letter(target_excel_col)

# 生成公式：Excel数据行从第2行开始（第1行是表头）
kdPT['Appname all upper'] = [
    f"=EXACT({target_col_letter}{row},UPPER({target_col_letter}{row}))" 
    for row in range(2, len(kdPT)+2)
]

# 导出时指定openpyxl引擎，确保公式被正确识别
kdPT.to_excel(reportXLSX, sheet_name="app", startrow=0, engine='openpyxl')

方案二：导出后用openpyxl动态修改（更可靠）

这种方法会自动定位Application列，避免硬编码列号出错：

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
import pandas as pd

# 自定义聚合函数（补充原代码缺失部分）
def iDistinct_count(x):
    return x.nunique()
def iValues(x):
    return ', '.join(x.unique())

# 原透视表生成与导出
kdPT = pd.pivot_table(df, index="iAID", values=["Application", "Env"], 
                      aggfunc=[iDistinct_count, iValues], margins=False)
kdPT.to_excel(reportXLSX, sheet_name="app", startrow=0, engine='openpyxl')

# 加载Excel文件并处理
wb = load_workbook(reportXLSX)
ws = wb['app']

# 自动定位Application列（适配多层表头）
target_col = None
for col in range(1, ws.max_column + 1):
    cell_val = ws.cell(row=1, column=col).value
    if (isinstance(cell_val, tuple) and cell_val[1] == 'Application') or \
       (isinstance(cell_val, str) and 'Application' in cell_val):
        target_col = col
        break

if not target_col:
    raise ValueError("未找到Application列")

target_col_letter = get_column_letter(target_col)

# 添加新列标题
new_col = ws.max_column + 1
ws.cell(row=1, column=new_col).value = 'Appname all upper'

# 为每一行写入公式
for row in range(2, ws.max_row + 1):
    ws.cell(row=row, column=new_col).value = f"=EXACT({target_col_letter}{row},UPPER({target_col_letter}{row}))"

# 保存修改
wb.save(reportXLSX)

关键注意事项 #

• 必须使用openpyxl引擎导出和修改Excel，因为旧的xlwt引擎不支持xlsx格式的公式
• 多层表头的列值在Excel中会显示为元组（比如('iValues', 'Application')），所以定位时要考虑这种情况

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