如何调整Pandas DataFrame多级列头以实现周维度统计展示

拒签数据表格 #

需求与问题 #

需编写Python脚本计算三类拒签统计数据：

• 各代理每周的拒签总数
• 各代理每周因Time out的拒签数
• 各代理每周因Busy的拒签数

原脚本计算逻辑正确，但生成的多级列头结构错误：拒签统计项位于周数上方，导致周数重复显示。需要调整为周数在顶层，下方嵌套三类统计项，同时表格需带单元格边框。

原脚本 #

import pandas as pd

# Load the CSV file into a DataFrame
df = pd.read_csv('Rejection Report.csv')

# Convert 'wir_sys_created_on' column to datetime
df['wir_sys_created_on'] = pd.to_datetime(df['wir_sys_created_on'])

# Extract week numbers from the datetime column starting from 1 and format with ISO week number and the date of the Monday
df['week_number'] = df['wir_sys_created_on'] - pd.to_timedelta(df['wir_sys_created_on'].dt.dayofweek, unit='d')
df['week_number'] = 'Week ' + df['week_number'].dt.strftime('%V') + ' (' + df['week_number'].dt.strftime('%Y-%m-%d') + ')'

# Group by agent, week number, and rejection reason
grouped = df.groupby(['wir_rejected_by', 'week_number', 'wir_reason'])

# Calculate rejection count by reason per week
rejection_by_reason = grouped.size().unstack(fill_value=0)

# Calculate total rejection count per week
weekly_rejection_count = df.groupby(['wir_rejected_by', 'week_number']).size().unstack(fill_value=0)

# Filter rejection counts based on reasons 'Time out' and 'Busy'
rejection_timeout = rejection_by_reason['Time out'].unstack(fill_value=0)
rejection_busy = rejection_by_reason['Busy'].unstack(fill_value=0)

# Concatenate DataFrames with a multi-level column index
df_with_multiindex = pd.concat(
    [weekly_rejection_count, rejection_timeout, rejection_busy],
    axis=1,
    keys=['Total Rejections', 'Rejections due to Time out', 'Rejections due to Busy'],
    names=['', '']
)

# Ensure weeks are ordered chronologically
df_with_multiindex = df_with_multiindex.reindex(sorted(df_with_multiindex.columns), axis=1)

# Apply some formatting
styled_df = df_with_multiindex.style.format("{:.0f}")
styled_df = styled_df.set_table_styles([
    {'selector': 'th', 'props': [('text-align', 'center')]},
    {'selector': 'td', 'props': [('text-align', 'center')]},
    {'selector': 'caption', 'props': [('caption-side', 'bottom')]}
])

# Set the caption
styled_df = styled_df.set_caption('Rejections Report')

# Display the styled DataFrame
styled_df.set_properties(**{'border-collapse': 'collapse', 'border': '1px solid black'})

修改后的脚本 #

import pandas as pd

# Load the CSV file into a DataFrame
df = pd.read_csv('Rejection Report.csv')

# Convert 'wir_sys_created_on' column to datetime
df['wir_sys_created_on'] = pd.to_datetime(df['wir_sys_created_on'])

# Extract week numbers from the datetime column starting from 1 and format with ISO week number and the date of the Monday
df['week_number'] = df['wir_sys_created_on'] - pd.to_timedelta(df['wir_sys_created_on'].dt.dayofweek, unit='d')
df['week_number'] = 'Week ' + df['week_number'].dt.strftime('%V') + ' (' + df['week_number'].dt.strftime('%Y-%m-%d') + ')'

# Calculate total rejection count per week
weekly_total = df.groupby(['wir_rejected_by', 'week_number']).size().unstack(fill_value=0)

# Calculate rejection count by reason per week
weekly_reason = df.groupby(['wir_rejected_by', 'week_number', 'wir_reason']).size().unstack(fill_value=0)
weekly_timeout = weekly_reason['Time out'].unstack(fill_value=0)
weekly_busy = weekly_reason['Busy'].unstack(fill_value=0)

# 为每个统计结果的列添加子层级标签，构建(周数, 统计项)的多级索引
total_with_level = weekly_total.T.set_index(pd.MultiIndex.from_tuples([(col, 'Total Rejections') for col in weekly_total.columns])).T
timeout_with_level = weekly_timeout.T.set_index(pd.MultiIndex.from_tuples([(col, 'Rejections due to Time out') for col in weekly_timeout.columns])).T
busy_with_level = weekly_busy.T.set_index(pd.MultiIndex.from_tuples([(col, 'Rejections due to Busy') for col in weekly_busy.columns])).T

# 合并三个DataFrame，自动对齐周数和代理
df_final = pd.concat([total_with_level, timeout_with_level, busy_with_level], axis=1)

# 调整列层级顺序：周数在顶层，统计项在下层；按周数时间顺序排序
df_final = df_final.swaplevel(axis=1).sort_index(axis=1)

# 应用表格样式
styled_df = df_final.style.format("{:.0f}")
styled_df = styled_df.set_table_styles([
    # 设置表头和单元格居中
    {'selector': 'th', 'props': [('text-align', 'center')]},
    {'selector': 'td', 'props': [('text-align', 'center')]},
    # 设置标题位置在底部
    {'selector': 'caption', 'props': [('caption-side', 'bottom')]},
    # 顶层表头添加加粗底部边框，区分层级
    {'selector': 'th.level0', 'props': [('border-bottom', '2px solid black')]}
])

# 设置表格标题
styled_df = styled_df.set_caption('Rejections Report')

# 为所有单元格添加边框
styled_df = styled_df.set_properties(**{'border-collapse': 'collapse', 'border': '1px solid black'})

# 显示表格
display(styled_df)

关键修改说明 #

1. 列层级重构：

   • 给每个统计结果的列添加(周数, 统计项)的多级索引，合并后通过swaplevel(axis=1)将周数切换为顶层列，统计项作为子列，彻底解决周数重复的问题。
   • 使用sort_index(axis=1)确保周数按时间顺序排列。

2. 样式优化：

   • 给顶层表头添加加粗底部边框，更清晰地区分列层级。
   • 保留原有的居中对齐、边框合并和标题设置，保证表格可读性。

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