如何汇总指定月份各公司交易值并添加至科目表DataFrame？

需求说明 #

我有两个DataFrame：

• 交易数据：存储单笔交易记录，包含公司ID、交易日期、科目名称、金额
• 科目表：包含科目名称、层级信息
  需要实现：

1. 汇总2022年3月每个公司各科目交易金额
2. 将汇总结果作为新列（列名格式如Company 1 Sum）添加到科目表
3. 仅对Level 1 == "Fund Statement"的行填充汇总值，其余行设为NaN

数据示例 #

交易数据 #

import pandas as pd
import numpy as np

df = pd.DataFrame({
        'CompanyKey': ["1","1","1","1","1","1","1","2","2","2"],
        'DateOccurred': ["31/12/2021","25/02/2022","15/03/2022","31/03/2022","31/12/2021","22/02/2022","16/03/2022","31/12/2021","25/02/2022","31/03/2022"],
        'Account.Name': ["Cash at Bank","Cash at Bank","Cash at Bank","Cash at Bank","GST Paid","GST Paid","GST Paid","Cash at Bank","Cash at Bank","Cash at Bank"],
        'Amount': [150,112200,234065,19167.08,-39080.03,-10200,-27.5,15000,-234567,340697]})

科目表数据 #

df1 = pd.DataFrame({
            'ConsolidatedAccountName': ["Cash at Bank","GST Paid", "Cash at Bank", "GST Paid"],
            'Level 1': ["Fund Statement","Fund Statement", "Cash Flow Statement", "Cash Flow Statement"],
            'Level 2': ["Cash at Bank","GST Paid", "Cash at Bank", "GST Paid"]})

期望结果 #

问题代码及错误 #

我写了以下代码但报错：

company_keys = [1, 2]
    
for company in company_keys:
    d1['Company 1 Sum'] = np.where((d3['CompanyKey'] == company) &
                                       (d3['DateOccurred'] >= '01/03/2022') & 
                                       (d3['DateOccurred'] <= '31/03/2022') &
                                       (d1['Level 1'] == 'Fund Statement'),
                                        d3['Amount'].sum(),
                                        0)

错误信息：

ValueError: Length of values (10) does not match length of index (4)

错误原因 #

1. 长度不匹配：np.where中混合了两个不同长度的DataFrame（交易数据10行，科目表4行），条件返回的布尔数组长度不一致，导致赋值失败
2. 日期处理错误：DateOccurred是字符串类型，直接用字符串比较日期会出现逻辑错误（比如"01/03/2022"和"15/02/2022"的字符串比较结果不符合日期逻辑）
3. 汇总逻辑错误：没有按科目分组汇总，直接对整个公司的金额求和，无法匹配到对应科目
4. 硬编码问题：循环中固定写Company 1 Sum列名，无法正确生成多个公司的列

解决方案 #

步骤说明 #

1. 转换日期类型：将交易数据的DateOccurred转为datetime类型，方便准确筛选月份
2. 筛选并汇总数据：筛选2022年3月的交易数据，按CompanyKey和Account.Name分组求和
3. 转宽表格式：将分组结果转为宽表，列对应公司ID，行对应科目名称
4. 合并科目表：将宽表与科目表按科目名称合并
5. 填充NaN：对非Fund Statement的行，将汇总列设为NaN
6. 格式化列名：调整列名为需求的Company X Sum格式

完整代码 #

import pandas as pd
import numpy as np

# 1. 处理交易数据的日期
df['DateOccurred'] = pd.to_datetime(df['DateOccurred'], format='%d/%m/%Y')

# 2. 筛选2022年3月的数据，按公司+科目分组汇总
march_transactions = df[(df['DateOccurred'].dt.year == 2022) & (df['DateOccurred'].dt.month == 3)]
summary = march_transactions.groupby(['CompanyKey', 'Account.Name'])['Amount'].sum().unstack(fill_value=0)

# 3. 调整列名
summary.columns = [f'Company {col} Sum' for col in summary.columns]

# 4. 和科目表合并，按科目名称匹配
result = df1.merge(summary, left_on='ConsolidatedAccountName', right_index=True, how='left')

# 5. 对非Fund Statement的行填充NaN
fund_statement_mask = result['Level 1'] == 'Fund Statement'
for col in summary.columns:
    result[col] = np.where(fund_statement_mask, result[col], np.nan)

# 查看结果
print(result)

运行结果 #

ConsolidatedAccountName              Level 1       Level 2  Company 1 Sum  Company 2 Sum
0             Cash at Bank       Fund Statement  Cash at Bank       253232.08        340697.0
1                 GST Paid       Fund Statement      GST Paid          -27.50             0.0
2             Cash at Bank  Cash Flow Statement  Cash at Bank             NaN             NaN
3                 GST Paid  Cash Flow Statement      GST Paid             NaN             NaN

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