如何利用Pandas基于唯一标识与条件高效修改数据？

问题背景 #

现有如下DataFrame：

identifier loan_identifier cashflow_date        cashflow_type   amount
0           1            a111    15/07/2023              funding  -195.71
1           2            a111    01/07/2023   interest_repayment     3.11
2           3            a111    15/07/2023   interest_repayment     0.04
3           4            a111    20/07/2023   interest_repayment     0.04
4           5            a111    11/06/2023  principal_repayment   195.33
5           6            b222    10/07/2023              funding -3915.45
6          13            b222    10/07/2023   interest_repayment     0.73
7          14            b222    10/07/2023   interest_repayment     0.73
8          15            b222    13/06/2023  principal_repayment  3906.50

业务规则：

• 每个loan_identifier对应的cashflow_type为funding的cashflow_date是该贷款的基准日期
• 对于cashflow_type≠funding且cashflow_date早于对应贷款基准日期的行，需将其cashflow_date替换为该贷款的基准日期

转换后的目标DataFrame如下：

identifier loan_identifier cashflow_date        cashflow_type   amount
0           1            a111    15/07/2023              funding  -195.71
1           2            a111    15/07/2023   interest_repayment     3.11
2           3            a111    15/07/2023   interest_repayment     0.04
3           4            a111    20/07/2023   interest_repayment     0.04
4           5            a111    15/07/2023  principal_repayment   195.33
5           6            b222    10/07/2023              funding -3915.45
6          13            b222    10/07/2023   interest_repayment     0.73
7          14            b222    10/07/2023   interest_repayment     0.73
8          15            b222    10/07/2023  principal_repayment  3906.50

数据字典：

data = {
    'identifier': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5, 5: 6, 6: 13, 7: 14, 8: 15},
    'loan_identifier': {0: 'a111', 1: 'a111', 2: 'a111', 3: 'a111', 4: 'a111', 5: 'b222', 6: 'b222', 7: 'b222', 8: 'b222'},
    'cashflow_date': {0: '15/07/2023', 1: '01/07/2023', 2: '15/07/2023', 3: '20/07/2023', 4: '11/06/2023', 5: '10/07/2023', 6: '10/07/2023', 7: '10/07/2023', 8: '13/06/2023'},
    'cashflow_type': {0: 'funding', 1: 'interest_repayment', 2: 'interest_repayment', 3: 'interest_repayment', 4: 'principal_repayment', 5: 'funding', 6: 'interest_repayment', 7: 'interest_repayment', 8: 'principal_repayment'},
    'amount': {0: -195.71, 1: 3.11, 2: 0.04, 3: 0.04, 4: 195.33, 5: -3915.45, 6: 0.73, 7: 0.73, 8: 3906.5}
}

解决方案 #

步骤拆解 #

1. 转换日期为可比较类型
   字符串格式的日期无法直接比较大小，先转换成Pandas的datetime类型：

   import pandas as pd
   
   df = pd.DataFrame(data)
   # 按DD/MM/YYYY格式解析日期
   df['cashflow_date'] = pd.to_datetime(df['cashflow_date'], format='%d/%m/%Y')
   

2. 提取每个贷款的基准funding日期
   筛选出所有funding类型的行，按loan_identifier映射到原DataFrame：

   # 生成贷款ID到funding日期的映射字典
   funding_dates = df[df['cashflow_type'] == 'funding'].set_index('loan_identifier')['cashflow_date'].to_dict()
   # 给每行添加对应贷款的基准日期
   df['funding_date'] = df['loan_identifier'].map(funding_dates)
   

3. 替换不符合规则的日期
   使用条件判断，将满足要求的行替换为基准日期：

   import numpy as np
   
   # 定义替换条件：非funding类型 + 现金流日期早于基准日期
   condition = (df['cashflow_type'] != 'funding') & (df['cashflow_date'] < df['funding_date'])
   # 执行替换
   df['cashflow_date'] = np.where(condition, df['funding_date'], df['cashflow_date'])
   

4. 还原日期格式并清理临时列
   将datetime类型转回原字符串格式，删除临时生成的funding_date列：

   # 转换回DD/MM/YYYY格式的字符串
   df['cashflow_date'] = df['cashflow_date'].dt.strftime('%d/%m/%Y')
   # 删除临时列
   df.drop('funding_date', axis=1, inplace=True)
   

完整代码 #

import pandas as pd
import numpy as np

data = {
    'identifier': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5, 5: 6, 6: 13, 7: 14, 8: 15},
    'loan_identifier': {0: 'a111', 1: 'a111', 2: 'a111', 3: 'a111', 4: 'a111', 5: 'b222', 6: 'b222', 7: 'b222', 8: 'b222'},
    'cashflow_date': {0: '15/07/2023', 1: '01/07/2023', 2: '15/07/2023', 3: '20/07/2023', 4: '11/06/2023', 5: '10/07/2023', 6: '10/07/2023', 7: '10/07/2023', 8: '13/06/2023'},
    'cashflow_type': {0: 'funding', 1: 'interest_repayment', 2: 'interest_repayment', 3: 'interest_repayment', 4: 'principal_repayment', 5: 'funding', 6: 'interest_repayment', 7: 'interest_repayment', 8: 'principal_repayment'},
    'amount': {0: -195.71, 1: 3.11, 2: 0.04, 3: 0.04, 4: 195.33, 5: -3915.45, 6: 0.73, 7: 0.73, 8: 3906.5}
}

# 1. 转换日期格式
df = pd.DataFrame(data)
df['cashflow_date'] = pd.to_datetime(df['cashflow_date'], format='%d/%m/%Y')

# 2. 获取每个贷款的基准funding日期
funding_dates = df[df['cashflow_type'] == 'funding'].set_index('loan_identifier')['cashflow_date']
df['funding_date'] = df['loan_identifier'].map(funding_dates)

# 3. 调整不符合规则的日期
condition = (df['cashflow_type'] != 'funding') & (df['cashflow_date'] < df['funding_date'])
df['cashflow_date'] = np.where(condition, df['funding_date'], df['cashflow_date'])

# 4. 还原日期格式并清理临时列
df['cashflow_date'] = df['cashflow_date'].dt.strftime('%d/%m/%Y')
df.drop('funding_date', axis=1, inplace=True)

print(df)

运行上述代码后，即可得到符合要求的目标DataFrame。

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