I’m having some issues joining 2 dataframes using pandas.merge(), based on certain conditions. I would appreciate some advice

In the example below, I wish to join the 2 dataframes on customerId. However, I’m interested in only the EARLIEST record in loans_df which matches the condition:

1. Customers.EndDate < Loans.Date
2. Loans.amount > 100

This means it’s a one-to-one join (or one to zero if no loans exists for a customer). Note: I’m assuming the date convention is 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]})

Tried pandas merge() without success