合并DataFrame按供应商、年份分组汇总发票总额及阶段对比计算问题

处理方案 #

步骤1：合并DataFrame并生成分年度汇总（对应预期输出1） #

你之前合并丢失单边数据大概率是误用了内连接的merge操作，两个字段完全一致的DataFrame纵向拼接直接用pd.concat即可，不会丢失任何数据：

import pandas as pd

# 合并两个DataFrame，ignore_index重置索引避免重复
df_all = pd.concat([df1, df2], ignore_index=True)

# 转换日期格式，提取发票年份
df_all['InvoiceDate'] = pd.to_datetime(df_all['InvoiceDate'])
df_all['InvoiceYear'] = df_all['InvoiceDate'].dt.year

# 按公司代码、供应商、年份分组汇总金额
output1 = df_all.groupby(
    ['Company Code', 'VendorName', 'InvoiceYear'], 
    as_index=False
)['InvoiceAmount'].sum().rename(columns={'InvoiceAmount': 'Invoice Total For Year'})

# 可选：按预期格式添加千分位分隔符
output1['Invoice Total For Year'] = output1['Invoice Total For Year'].apply(lambda x: f"{x:,}")

步骤2：按时间段汇总并计算增减比例（对应预期输出2） #

# 给每行数据打时间段标签
df_all['period'] = pd.cut(
    df_all['InvoiceYear'],
    bins=[2016, 2018, 2020],
    labels=['InvoiceTotal2017-2018', 'InvoiceTotal2019-2020']
)

# 按公司、供应商、时间段汇总，转成宽表格式
period_agg = df_all.groupby(
    ['Company Code', 'VendorName', 'period'],
    as_index=False
)['InvoiceAmount'].sum()
output2 = period_agg.pivot(
    index=['Company Code', 'VendorName'],
    columns='period',
    values='InvoiceAmount'
).fillna(0).reset_index().rename_axis(columns=None)

# 计算增减百分比
def get_change_pct(row):
    prev = row['InvoiceTotal2017-2018']
    curr = row['InvoiceTotal2019-2020']
    if prev == 0:
        return '+100%' if curr > 0 else '0%'
    return f"{(curr - prev)/prev:+.0%}"

output2['% Increase/Decrease'] = output2.apply(get_change_pct, axis=1)

运行上述代码后得到的output1、output2与给出的预期输出完全一致。

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