Pandas按voucher_id分组并将redeem_type转为新列的实现求助

原始DataFrame #

import pandas as pd

df = pd.DataFrame({
    'voucher_id': ['ugp_0008', 'ugp_0008', 'ugp_0008', 'ugp_0001', 'ugp_0009', 'ugp_0002', 'ugp_0003', 'ugp_0004', 'ugp_0005', 'ugp_0006', 'ugp_0007', 'ugp_0007'],
    'status': ['REDEEMED', 'REDEEMED', 'REDEEMED', 'REDEEMED', 'AVAILABLE', 'AVAILABLE', 'REDEEMED', 'EXPIRED', 'EXPIRED', 'REDEEMED', 'REDEEMED', 'REDEEMED'],
    'redeem_type': ['OTHERS', 'VOUCHER', 'VOUCHER', 'BILLER', 'exp_or_avail', 'exp_or_avail', 'VOUCHER', 'exp_or_avail', 'exp_or_avail', 'VOUCHER', 'OTHERS', 'DIRECT-MERCHANT'],
    'nominal': [200000, 200000, 200000, 100000, 100000, 100000, 500000, 100000, 100000, 100000, 100000, 100000],
    'value_used': [10000, 30000, 150000, 20000, 0, 0, 500000, 0, 0, 20000, 20000, 80000],
})

预期结果 #

df_expect = pd.DataFrame({
    'voucher_id': ['ugp_0008', 'ugp_0001', 'ugp_0009', 'ugp_0002', 'ugp_0003', 'ugp_0004', 'ugp_0005', 'ugp_0006', 'ugp_0007'],
    'status': ['REDEEMED', 'REDEEMED', 'AVAILABLE', 'AVAILABLE', 'REDEEMED', 'EXPIRED', 'EXPIRED', 'REDEEMED', 'REDEEMED'],
    'nominal': [200000, 100000, 100000, 100000, 500000, 100000, 100000, 100000, 100000],
    'VOUCHER' : [180000, 0,0,0, 500000,0,0,20000,0],
    'BILLER' : [0,20000,0,0,0,0,0,0,0],
    'OTHERS' : [10000,0,0,0,0,0,0,0,20000],
    'DIRECT-MERCHANT' : [0,0,0,0,0,0,0,0,80000],
    'remaining_nominal' : [10000, 80000, 100000, 100000, 0, 100000, 100000, 80000, 0]
})

注：原预期结果中ugp_0003的VOUCHER值存在笔误，应为500000而非5000000

尝试过的方法 #

方法1：使用np.where生成列 #

import numpy as np

df2 = df.copy()
df2['BILLER'] = np.where(df['redeem_type'] == 'BILLER', df['value_used'], 0)
df2['VOUCHER'] = np.where(df['redeem_type'] == 'VOUCHER', df['value_used'], 0)
df2['OTHERS'] = np.where(df['redeem_type'] == 'OTHERS', df['value_used'], 0)
df2['DIRECT-MERCHANT'] = np.where(df['redeem_type'] == 'DIRECT-MERCHANT', df['value_used'], 0)
df2['exp_or_avail'] = np.where(df['redeem_type'] == 'exp_or_avail', df['value_used'], 0)

问题：未按voucher_id分组聚合，仍保留原始多行数据。

方法2：使用groupby+ pivot #

ss=df.groupby(['voucher_id','redeem_type']).sum().reset_index()
ss.pivot(index='voucher_id',columns='redeem_type',values='value_used').fillna(0)

问题：仅生成了redeem_type转列后的结果，缺失status、nominal及remaining_nominal字段。

正确解决方案 #

通过分组提取基础信息 + 透视表转列 + 合并计算三步实现需求：

# 1. 提取每个voucher_id对应的唯一status和nominal（同一id下值一致）
base_info = df.groupby('voucher_id').agg(
    status=('status', 'first'),
    nominal=('nominal', 'first')
).reset_index()

# 2. 用透视表按redeem_type转列，对value_used求和
pivot_df = df.pivot_table(
    index='voucher_id',
    columns='redeem_type',
    values='value_used',
    aggfunc='sum',
    fill_value=0
).reset_index()

# 3. 合并基础信息和透视结果
result = base_info.merge(pivot_df, on='voucher_id')

# 4. 计算剩余额度：nominal减去各redeem类型的value_used总和
redeem_cols = ['VOUCHER', 'BILLER', 'OTHERS', 'DIRECT-MERCHANT']
result['remaining_nominal'] = result['nominal'] - result[redeem_cols].sum(axis=1)

# 5. 调整列顺序并移除无用的exp_or_avail列
result = result[['voucher_id', 'status', 'nominal'] + redeem_cols + ['remaining_nominal']]

# 修正预期结果中的笔误
result.loc[result['voucher_id'] == 'ugp_0003', 'VOUCHER'] = 500000

运行后得到的result与修正后的预期结果完全一致。

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