如何用Python和Pandas生成按日KPI趋势表并合并数据？

原始输入DataFrame #

主业务数据 #

Period start time Segment Name  KPI_1  KPI_2
       04.13.2023      AI4301A  51.49   3.85
       04.13.2023      AI4301B 193.79   1.56
       04.13.2023      AI4301C  95.69   1.25
       04.14.2023      AI4301A  59.47   2.42
       04.14.2023      AI4301B 193.11   1.32
       04.14.2023      AI4301C  81.90   1.94
       04.15.2023      AI4301A  46.73   3.11
       04.15.2023      AI4301B 179.33   1.47
       04.15.2023      AI4301C  78.95   1.51

待合并的Segment属性数据 #

segmentName ID_1    ID_2
AI4301A     AI_05   2741
AI4301B     AI_05   2742
AI4301C     AI_05   2743

需求目标 #

转换为如下格式（Segment Name行与数据行无空行，支持任意数量KPI、Segment、日期）：

|           KPI_1                |            KPI_2            |
Segment Name 04.13.2023 04.14.2023  04.15.2023  04.13.2023  04.14.2023  04.15.2023
AI4301A         51.49   59.47           46.73   3.85        2.42            3.11
AI4301B         193.79  193.11          179.33  1.56        1.32            1.47
AI4301C         95.69   81.90           78.95   1.25        1.94            1.51

同时解决两个核心问题：

1. 透视后表头下方的空行问题
2. 基于Segment Name合并属性DataFrame

解决方案 #

步骤1：数据透视与格式优化 #

原groupby+unstack的写法会产生冗余列和多级表头空行，改用pivot更简洁，同时整理列顺序和命名：

import pandas as pd

# 转换日期列格式为datetime（确保后续排序、透视正常）
df['Period start time'] = pd.to_datetime(df['Period start time'], format='%m.%d.%Y')

# 透视数据：以Segment Name为行索引，日期为列维度，KPI为值
pivot_df = df.pivot(index='Segment Name', 
                    columns='Period start time', 
                    values=['KPI_1', 'KPI_2'])

# 整理列顺序：先按KPI分组，再按日期排序
kpi_list = pivot_df.columns.get_level_values(0).unique()
date_list = pivot_df.columns.get_level_values(1).unique()
sorted_cols = []
for kpi in kpi_list:
    sorted_cols.extend([(kpi, date) for date in date_list])
pivot_df = pivot_df.reindex(columns=sorted_cols)

# 将多级列名转为单级格式（如KPI_1_04.13.2023）
pivot_df.columns = [f'{kpi}_{date.strftime("%m.%d.%Y")}' for kpi, date in pivot_df.columns]
pivot_df = pivot_df.reset_index()

步骤2：合并Segment属性数据 #

统一列名后，使用merge完成关联：

# 初始化Segment属性DataFrame（实际场景可从文件读取）
df_segment = pd.DataFrame({
    'segmentName': ['AI4301A', 'AI4301B', 'AI4301C'],
    'ID_1': ['AI_05', 'AI_05', 'AI_05'],
    'ID_2': [2741, 2742, 2743]
})

# 对齐列名
df_segment = df_segment.rename(columns={'segmentName': 'Segment Name'})

# 按Segment Name合并数据
merged_df = pd.merge(pivot_df, df_segment, on='Segment Name', how='left')

步骤3：自定义打印消除空行 #

通过自定义打印逻辑完全匹配目标格式，消除默认打印的表头空行：

# 提取KPI和日期列表用于构建表头
kpis = pivot_df.columns[1:].str.split('_').str[0].unique()
dates = pivot_df.columns[1:].str.split('_').str[1].unique()

# 打印第一行分组表头
header_line1 = '       |'
for kpi in kpis:
    header_line1 += f'           {kpi}                |'
print(header_line1)

# 打印第二行列名表头
header_line2 = 'Segment Name'
for kpi in kpis:
    for date in dates:
        header_line2 += f' {date:>12}'
# 追加合并的ID列
header_line2 += '  ID_1    ID_2'
print(header_line2)

# 打印数据行
for _, row in merged_df.iterrows():
    line = f'{row["Segment Name"]}'
    for kpi in kpis:
        for date in dates:
            line += f' {row[f"{kpi}_{date}"]:>12}'
    line += f' {row["ID_1"]:>6} {row["ID_2"]:>6}'
    print(line)

完整代码示例 #

import pandas as pd

# 初始化主业务数据
data = {
    'Period start time': ['04.13.2023', '04.13.2023', '04.13.2023',
                          '04.14.2023', '04.14.2023', '04.14.2023',
                          '04.15.2023', '04.15.2023', '04.15.2023'],
    'Segment Name': ['AI4301A', 'AI4301B', 'AI4301C',
                     'AI4301A', 'AI4301B', 'AI4301C',
                     'AI4301A', 'AI4301B', 'AI4301C'],
    'KPI_1': [51.49, 193.79, 95.69, 59.47, 193.11, 81.90, 46.73, 179.33, 78.95],
    'KPI_2': [3.85, 1.56, 1.25, 2.42, 1.32, 1.94, 3.11, 1.47, 1.51]
}
df = pd.DataFrame(data)

# 处理日期格式
df['Period start time'] = pd.to_datetime(df['Period start time'], format='%m.%d.%Y')

# 透视数据
pivot_df = df.pivot(index='Segment Name', 
                    columns='Period start time', 
                    values=['KPI_1', 'KPI_2'])

# 整理列顺序和命名
kpi_list = pivot_df.columns.get_level_values(0).unique()
date_list = pivot_df.columns.get_level_values(1).unique()
sorted_cols = []
for kpi in kpi_list:
    sorted_cols.extend([(kpi, date) for date in date_list])
pivot_df = pivot_df.reindex(columns=sorted_cols)
pivot_df.columns = [f'{kpi}_{date.strftime("%m.%d.%Y")}' for kpi, date in pivot_df.columns]
pivot_df = pivot_df.reset_index()

# 合并Segment属性数据
df_segment = pd.DataFrame({
    'segmentName': ['AI4301A', 'AI4301B', 'AI4301C'],
    'ID_1': ['AI_05', 'AI_05', 'AI_05'],
    'ID_2': [2741, 2742, 2743]
})
df_segment = df_segment.rename(columns={'segmentName': 'Segment Name'})
merged_df = pd.merge(pivot_df, df_segment, on='Segment Name', how='left')

# 自定义打印输出
header1 = '       |'
for kpi in kpi_list:
    header1 += f'           {kpi}                |'
print(header1)

header2 = 'Segment Name'
for kpi in kpi_list:
    for date in date_list:
        header2 += f' {date.strftime("%m.%d.%Y"):>12}'
header2 += '  ID_1    ID_2'
print(header2)

for _, row in merged_df.iterrows():
    line = f'{row["Segment Name"]}'
    for kpi in kpi_list:
        for date in date_list:
            line += f' {row[f"{kpi}_{date.strftime("%m.%d.%Y")}"]:>12}'
    line += f' {row["ID_1"]:>6} {row["ID_2"]:>6}'
    print(line)

关键说明 #

• 使用pivot替代groupby+unstack，避免冗余列和多级表头空行
• 列排序逻辑确保KPI按组聚合，日期顺序符合需求
• 合并时统一列名，保证关联匹配准确
• 自定义打印逻辑完全消除表头空行，且支持任意数量的KPI、Segment和日期扩展

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