如何基于指定条件合并两个Pandas DataFrame并匹配最早贷款记录

需要使用pandas.merge()按条件合并两个DataFrame，具体需求如下：

• 按CustomerId关联Customer_df与Loans_df
• 仅匹配Loans_df中满足以下条件的最早记录：
  • Customer_df.EndDate < Loans_df.Date
  • Loans_df.Amount > 100
• 一对一关联（客户无符合条件贷款则为一对零）
• 日期格式为yyyymmdd

示例数据：

import pandas as pd 

Customer_df = pd.DataFrame({ 'CustomerId': [1,2,3], 'End date ': ['20240101', '20220101', '20250101'] }) 
Loans_df = pd.DataFrame({ 'LoanId': [1,2,3], 'CustomerId': [1,2,2], 'Date': ['20240112', '20230101', '20240101'], 'Amount': [1000,2000,4000]})

尝试直接使用pandas.merge()未达到预期效果。

直接调用merge()无法同时完成筛选条件和取最早记录的需求，需分步骤处理：

1. 预处理：修正列名与转换日期格式 #

首先修正Customer_df的列名（原列名含空格），并将字符串日期转为datetime类型，方便后续日期比较：

# 修正列名
Customer_df = Customer_df.rename(columns={'End date ': 'EndDate'})
# 转换日期格式
Customer_df['EndDate'] = pd.to_datetime(Customer_df['EndDate'], format='%Y%m%d')
Loans_df['Date'] = pd.to_datetime(Loans_df['Date'], format='%Y%m%d')

2. 筛选符合条件的关联记录 #

先通过左关联将两个表合并，再筛选出满足日期和金额条件的记录：

# 左关联保留所有客户
merged_temp = pd.merge(Customer_df, Loans_df, on='CustomerId', how='left')
# 筛选符合条件的记录
filtered_records = merged_temp[(merged_temp['EndDate'] < merged_temp['Date']) & (merged_temp['Amount'] > 100)]

3. 按客户分组取最早贷款记录 #

对筛选结果按CustomerId分组，排序后保留每个客户的最早贷款记录：

# 按日期排序后取每组第一条（最早记录）
earliest_loans = filtered_records.sort_values('Date').groupby('CustomerId').first().reset_index()

4. 关联回原客户表得到最终结果 #

将筛选出的最早贷款记录左关联回Customer_df，确保所有客户都被保留：

final_result = pd.merge(Customer_df, earliest_loans, on='CustomerId', how='left')

完整可运行代码 #

import pandas as pd 

Customer_df = pd.DataFrame({ 'CustomerId': [1,2,3], 'End date ': ['20240101', '20220101', '20250101'] }) 
Loans_df = pd.DataFrame({ 'LoanId': [1,2,3], 'CustomerId': [1,2,2], 'Date': ['20240112', '20230101', '20240101'], 'Amount': [1000,2000,4000]})

# 预处理
Customer_df = Customer_df.rename(columns={'End date ': 'EndDate'})
Customer_df['EndDate'] = pd.to_datetime(Customer_df['EndDate'], format='%Y%m%d')
Loans_df['Date'] = pd.to_datetime(Loans_df['Date'], format='%Y%m%d')

# 筛选符合条件的记录
merged_temp = pd.merge(Customer_df, Loans_df, on='CustomerId', how='left')
filtered_records = merged_temp[(merged_temp['EndDate'] < merged_temp['Date']) & (merged_temp['Amount'] > 100)]

# 取每个客户最早的贷款
earliest_loans = filtered_records.sort_values('Date').groupby('CustomerId').first().reset_index()

# 生成最终结果
final_result = pd.merge(Customer_df, earliest_loans, on='CustomerId', how='left')
print(final_result)

输出结果 #

CustomerId    EndDate  LoanId       Date  Amount
0           1 2024-01-01     1.0 2024-01-12  1000.0
1           2 2022-01-01     2.0 2023-01-01  2000.0
2           3 2025-01-01     NaN        NaT     NaN

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