Pandas透视表格式优化：跑步统计数据垂直布局需求

问题描述 #

我有一份存储在Excel中的多年跑步统计数据集，希望使用Python Pandas进行处理，生成以距离分组为行、年份为列的表格，每个单元格需包含count、mean pace、min pace。目前已实现部分功能，但输出格式不符合预期。

原代码 #

import pandas as pd
pd.set_option('display.max_rows', 1000)
pd.set_option('display.max_columns', 100)
# set max width to 100
pd.set_option('display.width', 100)
# Replace "file_path.xlsx" with the actual path to your Excel file
df = pd.read_excel(r"C:\xxxxxx\OneDrive\Documents\garmin running data1.xlsx",
                   sheet_name='jun 15 2018 to apr 19 2023')
df = df[df['Activity Type'] == 'Running']

# Define the bins for grouping distances and labels for each bin

bins = [0, .5, 2.4, 3.3, 4.6, 5.4, 6.5, 13, 19.5, 20.2, 30]
labels = ['false start', '2 miles', '5k', '4 miles', '5 miles', '10K', 'Half', 'Long Train', '20', 'Marathon']

# Extract the year from the date field

df['Year'] = df['Date'].dt.year

# Create a new column that assigns each distance to a bin with named labels

df['Distances'] = pd.cut(df['Distance'], bins=bins, labels=labels)
    
df['Avg Pace'] = pd.to_numeric(df['Avg Pace'], errors='coerce')
df = df.dropna(subset=['Avg Pace'])

# Create a pivot table that shows the average of average_paces for each distance bin

pivot_table = pd.pivot_table(df, values='Avg Pace', index=['Distances'], columns=['Year'], aggfunc=('mean', 'count', 'min'))
#pivot_table['Avg Pace'] = pd.to_datetime(pivot_table['Avg Pace'] / 60 * 86400, unit='s').dt.strftime('%M:%S.%f')

# Divide all the values in the pivot table by 60 and multiply by 86400

pivot_table = pivot_table / 60 * 86400

# Format the values as mm:ss.0

pivot_table = pivot_table.applymap(lambda x: pd.to_datetime(x, unit='s').strftime('%M:%S.%S') if not pd.isna(x) else '')

期望输出格式 #

Year           2018      2019      2020      2021    
Distances                                                                                     
           mean        8:59.59      7:45.45    8:15.22  8:34.36
    5k     min         8:05.15      7:13.45    8:05.22  8:04.36
           count         50     75        85        35    

           mean        8:59.59      7:45.45    8:15.22  8:34.36
    10k    min         8:05.15      7:13.45    8:05.22  8:04.36
           count         50     75        85        35    

当前实际输出（简化展示） #

count                                                        mean            
Year             2018      2019      2020      2021      2022      2023      2018      2019   
Distances                                                                                     
false start  00:00.00  00:00.00  00:00.00  00:00.00  00:00.00  00:00.00                        
2 miles      00:00.00  24:00.00  12:00.00  23:59.59  36:00.00  24:00.00  09:14.14  19:00.00   
...

解决方案 #

问题核心是透视表的层级结构和格式处理逻辑错误。当前输出将统计类型（count/mean/min）作为列的顶层索引，我们需要将其转为行的二级索引，配合距离分组形成垂直布局。调整后的代码如下：

import pandas as pd
pd.set_option('display.max_rows', 1000)
pd.set_option('display.max_columns', 100)
pd.set_option('display.width', 100)

# 读取并过滤跑步数据
df = pd.read_excel(r"C:\xxxxxx\OneDrive\Documents\garmin running data1.xlsx",
                   sheet_name='jun 15 2018 to apr 19 2023')
df = df[df['Activity Type'] == 'Running']

# 定义距离分组区间和标签
bins = [0, .5, 2.4, 3.3, 4.6, 5.4, 6.5, 13, 19.5, 20.2, 30]
labels = ['false start', '2 miles', '5k', '4 miles', '5 miles', '10K', 'Half', 'Long Train', '20', 'Marathon']

# 提取年份和分配距离分组
df['Year'] = df['Date'].dt.year
df['Distances'] = pd.cut(df['Distance'], bins=bins, labels=labels)

# 清理配速数据
df['Avg Pace'] = pd.to_numeric(df['Avg Pace'], errors='coerce')
df = df.dropna(subset=['Avg Pace'])

# 生成透视表，指定聚合函数映射
pivot_table = pd.pivot_table(
    df,
    values='Avg Pace',
    index=['Distances'],
    columns=['Year'],
    aggfunc={'mean': 'mean', 'count': 'count', 'min': 'min'}
)

# 交换列层级，将统计类型转为行的二级索引
pivot_table = pivot_table.stack(level=0).unstack(level=0)

# 重排行索引顺序，确保每个距离分组下按mean→min→count显示
pivot_table = pivot_table.reindex(['mean', 'min', 'count'], level=1)

# 格式化函数：区分配速（时间格式）和计数（整数）
def format_value(x, stat_type):
    if pd.isna(x):
        return ''
    if stat_type == 'count':
        return int(x)
    # 将分钟/英里的配速转为mm:ss格式
    total_sec = x * 60
    mins = int(total_sec // 60)
    secs = total_sec % 60
    return f"{mins}:{secs:05.2f}"

# 按统计类型分别格式化数据
for stat in ['mean', 'min', 'count']:
    pivot_table[stat] = pivot_table[stat].apply(lambda col: col.apply(lambda x: format_value(x, stat)))

# 优化索引和列名显示
pivot_table.columns.name = 'Year'
pivot_table.index.names = ['Distances', '']

# 打印最终结果
print(pivot_table)

关键调整说明 #

1. 层级重组：通过stack(level=0).unstack(level=0)将统计类型从列的顶层索引移至行的二级索引，实现垂直布局。
2. 索引排序：使用reindex确保每个距离分组下的统计项顺序符合期望。
3. 格式分离：针对count（整数）和配速（时间格式）分别处理，避免计数被错误转为时间字符串。
4. 显示优化：将二级索引名称设为空，让输出更贴近目标格式。

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