如何编写一个有效的函数来计算给定时间段内不同账户的平均期末余额

Question

我在一家金融机构工作。在我们的交易表中，我们只在客户进行交易时跟踪他们的余额。例如，如果客户在10月1日开立了一个账户，账户金额为200美元，然后在10月8日提取了50美元，那么他在交易表中只有两个条目，一个用于2020/10/01，另一个用于2020/10/8。现在，这个问题的焦点是期末余额。按照这个类比，如果我们使用今天作为截止日期，您会同意客户7天（2020/10/8-2020/10/1）的期末余额为200美元，其余29天的期末余额为150美元

现在，我不知道如何编写这个函数。我一直遇到错误，如果有人能帮我解决python代码和相应的注释，我将不胜感激，以便这成为我一次有效的学习经验

这是我拥有的数据集示例：

sample_df = pd.DataFrame({'ID': [15, 16, 15, 15, 16, 17, 17, 16],
                         'Calendar_Date': ['2020-10-10', '2020-10-12', '2020-10-12', '2020-10-22', '2020-10-28', '2020-10-30', '2020-11-03', '2020-11-04'] ,
                         'Closing_Balance': [10000, 3000, 6000, 5100, 14500, 25000, 13000, 9000]}) 

这就是我所期望的结果：

result_df = pd.DataFrame({'ID':[15, 16, 17],
                         'Total_Days': [26, 24, 6],
                         'Average_Account_Balance': [5823.08, 6375.00, 19000]})

为清楚起见：我就是这样得出结果的：

When ID = 15, Total_Days = (2+10+15) = 27; Average_Account_Balance = ((10000 * 2) + (6000 * 10) + (5100 * 15))/27 = 156500/27 = 5796.3

when ID = 16, Total_Days = (16+7+2) = 25; Average_Account_Balance = ((3000 * 16) + (14500 * 7) + (9000 * 2))/25 = 167500/25 = 6700.00

when ID = 17, Total_Days = (4+3) = 7;
Average_Account_Balance = ((25000 * 4) + (13000 * 3))/7 = 139000/7 = 19857.14

我需要的解决方案是计算效率高，因为你可以猜出多少交易，我们在我们的数据库。如果您对此处所述或暗示的内容不清楚，请随时提问。谢谢大家!

Answer 1

你可以把这个问题分解成几个步骤。首先，我们需要在dataframe中创建一些新列：

1. 查找从每个日期到结束日期的天数（在您的示例中为今天）
2. 在每个"ID"组中，获取先前计算列之间的差值，以获取交易之间的天数。然后，我们使用fillna方法来填充剩余的日期差异（例如，通过使用diff我们可以得到行之间的差异，但是我们忽略了"ID"内最近日期与今天日期之间的差异）。这为我们构建了一个适当的"days between transaction"列
3. 计算加权余额列：只需将"Closing_Balance" by the newly created “交易之间的天数”乘以`列即可

sample_df["days_from_today"] = (pd.to_datetime("11/06/2020").normalize() - sample_df["Calendar_Date"]).dt.days

sample_df["days_between_transactions"] = (sample_df.groupby("ID")["days_from_today"]
                                          .diff(-1)
                                          .fillna(sample_df["days_from_today"])
                                          .astype(int))

sample_df["weighted_balance"] = sample_df["Closing_Balance"] * sample_df["days_between_transactions"]

print(sample_df)
   ID Calendar_Date  Closing_Balance  days_from_today  days_between_transactions  weighted_balance
0  15    2020-10-10            10000               27                          2             20000
1  16    2020-10-12             3000               25                         16             48000
2  15    2020-10-12             6000               25                         10             60000
3  15    2020-10-22             5100               15                         15             76500
4  16    2020-10-28            14500                9                          7            101500
5  17    2020-10-30            25000                7                          4            100000
6  17    2020-11-03            13000                3                          3             39000
7  16    2020-11-04             9000                2                          2             18000

现在我们已经创建了额外的列，我们可以执行groupby -> aggregation操作来获取"weighted_balance"列的sum，并将其除以"days_from_today"的max以获得每个唯一的"ID"

aggregated_df = sample_df.groupby("ID").agg(
    weighted_total_account_balance=("weighted_balance", "sum"), 
    total_days=("days_from_today", "max")
)

aggregated_df["average_account_balance"] = aggregated_df["weighted_total_account_balance"] / aggregated_df["total_days"]

print(aggregated_df)
    weighted_total_account_balance  total_days  average_account_balance
ID                                                                     
15                          156500          27              5796.296296
16                          167500          25              6700.000000
17                          139000           7             19857.142857

我注意到我们的结果有轻微的差异，我相信这可能是由于我们的时区不同（我今天是2020年11月6日，不确定您的时间/天），所以我们的“总天数”可能不同

另外，如果您的数据非常大，我建议使用DataFrame.eval来执行算术运算