如何在Pandas中按ID汇总收支列并将结果分配至对应列

数据集定义 #

import pandas as pd

data = pd.DataFrame({
    'ID': [1, 2, 3, 4, 5, 2, 3, 1],
    'Debit': [0, 5000, 0, 5000, 3000, 0, 2000, 1000],
    'Credit': [-100, 0, -700, 0, 0, -8000, 0, 0]
})

names_index = pd.DataFrame({
    'ID': [1, 2, 3, 4, 5, 6, 7, 8],
    'names': ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H'],
    'state1': [1, 2, 3, 4, 5, 6, 7, 8],
    'state2': [9, 10, 11, 12, 13, 14, 15, 16],
})

需求说明 #

• 按ID对Debit和Credit列的数值分别求和，再计算每个ID的总余额
• 将总余额按正负分配到对应列：正数存入Debit，Credit设为0；负数存入Credit，Debit设为0
• 根据最终有值的列匹配对应状态：Debit有值时取state1，Credit有值时取state2，生成state列

当前尝试的代码与错误输出 #

balance = data.groupby('ID')[['Debit', 'Credit']].sum()
result = balance.merge(names_index, on='ID', how='inner')
result = result[['ID', 'names', 'Debit', 'Credit', 'state1', 'state2']]
result['state'] = result.pop('state1').where(result['Debit'].ne(0), result.pop('state2'))
print(result)

输出结果：

ID names  Debit  Credit  state
0  1     A   1000    -100      1
1  2     B   5000   -8000      2
2  3     C   2000    -700      3
3  4     D   5000       0      4
4  5     E   3000       0      5

期望输出 #

ID names  Debit  Credit state
0  1     A   900        0     1
1  2     B   0      -3000    10
2  3     C   1300       0     3
3  4     D   5000       0     4
4  5     E   3000       0     5

解决方案 #

核心是先计算每个ID的总余额，再根据余额正负重分配Debit和Credit列，最后匹配状态列：

# 1. 按ID聚合Debit和Credit的总和并重置索引
balance = data.groupby('ID')[['Debit', 'Credit']].sum().reset_index()
# 2. 计算每个ID的总收支净额
balance['total'] = balance['Debit'] + balance['Credit']
# 3. 根据净额正负重分配Debit和Credit列
balance['Debit'] = balance['total'].apply(lambda x: x if x > 0 else 0)
balance['Credit'] = balance['total'].apply(lambda x: x if x < 0 else 0)
# 4. 合并状态信息数据集
result = balance.merge(names_index, on='ID', how='inner')
# 5. 根据Debit是否非零选择对应的state列
result['state'] = result['state1'].where(result['Debit'] != 0, result['state2'])
# 6. 整理目标列并输出
result = result[['ID', 'names', 'Debit', 'Credit', 'state']]
print(result)

执行后输出：

ID names  Debit  Credit  state
0   1     A    900       0      1
1   2     B      0    -3000     10
2   3     C   1300       0      3
3   4     D   5000       0      4
4   5     E   3000       0      5

步骤说明 #

• 先聚合求和再计算总净额，确保得到每个ID的最终收支结果
• 通过apply函数根据净额正负重新赋值列，实现二选一的分配逻辑
• 合并状态数据后，用where方法根据Debit的取值状态匹配对应state列，逻辑直接清晰

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