如何在Pandas透视表中按页面分组并按点击量排序？

问题核心 #

需要实现两个目标：

• 按页面分组，同一页面内的查询按点击量降序排序
• 导出Excel时，同一页面的重复单元格显示为空（仅保留每组首个页面名称）

修改后的完整代码 #

import pandas as pd

# 获取GSC数据并命名列（保留原逻辑）
report = webproperty.query.range(DATA).get().to_dataframe()
report.columns=['zoekwoord','pagina','klikken','vertoningen','ctr','positie']

# 创建透视表（若数据为单条记录无重复，也可直接使用原report排序）
pivot = report.pivot_table(index=['pagina','zoekwoord'], values=['klikken','vertoningen','ctr','positie'])

# 1. 核心排序：先按页面分组，组内按点击量降序排列
pivot = pivot.sort_values(by=['pagina', 'klikken'], ascending=[True, False])

# 2. 处理重复页面单元格为空
pivot = pivot.reset_index()  # 将多级索引转为普通列，方便修改
# 仅保留每个页面第一次出现的名称，后续重复行设为空字符串
pivot['pagina'] = pivot['pagina'].where(~pivot['pagina'].duplicated(), '')

# 导出到Excel
writer = pd.ExcelWriter(r'~/Downloads/gsc_output.xlsx', engine='xlsxwriter')
report.to_excel(writer, sheet_name='Data', index=False)
pivot.to_excel(writer, sheet_name='Draaitabel', index=False)

# 可选：优化Excel显示格式
workbook = writer.book
worksheet = writer.sheets['Draaitabel']
# 设置列宽和居中对齐
align_format = workbook.add_format({'align': 'center'})
worksheet.set_column('A:F', 18, align_format)

writer.close()  # 新版Pandas推荐用close()替代save()

关键步骤说明 #

1. 正确排序逻辑
   之前直接按点击量排序会导致不同页面的高点击记录混排，改用sort_values(by=['pagina', 'klikken'], ascending=[True, False])：

   • 先按页面名称升序，确保同一页面的记录聚集
   • 再按点击量降序，实现组内从高到低排序

2. 重复页面单元格处理

   • 用reset_index()将透视表的多级索引转为普通列，便于修改pagina列
   • where(~pivot['pagina'].duplicated(), '')会保留每个页面第一次出现的名称，后续重复行的页面列设为空字符串

3. Excel格式优化（可选）
   借助xlsxwriter设置列宽和对齐方式，让导出的表格更贴近预期的视觉效果

最终效果示例 #

处理后的表格结构与你的预期一致：

内容的提问来源于stack exchange，提问作者Thijs van Hal