组合数据帧以将贷方分配给借方（以Pandas为单位）

Question

我有两个数据框架：贷款人和借款人。我想结合贷款人和借款人的价值，如下所示

LenderDF = pd.DataFrame({
'LenderID':['Lender 1', 'Lender 2','Lender 3','Lender 4', 'Lender 5'],
'AmountHave': [300, 150, 500, 100, 200]
})
borrowerDF = pd.DataFrame({
'borrowerID': ['Borrower 1', 'Borrower 2','Borrower 3','Borrower 4'],
'AmountNeed': [100,200,150,300]
 })

print(LenderDF)
print(borrowerDF)

   LenderID  AmountHave
0  Lender 1         300
1  Lender 2         150
2  Lender 3         500
3  Lender 4         100
4  Lender 5         200

   borrowerID  AmountNeed
0  Borrower 1         100
1  Borrower 2         200
2  Borrower 3         150
3  Borrower 4         300

我试图得到的最终结果是：

   BorrowerID  AmountNeeded  LenderID  LenderHave(after lending amount)  RemainingAmount
0  Borrower 1           100  Lender 1                                     300              200
1  Borrower 2           200  Lender 1                                     200                0
2  Borrower 3           150  Lender 2                                     150                0
3  Borrower 4           300  Lender 3                                     500              200

例如：

• 第一贷款人有300英镑，因此他们可以贷款给（比如）借款人1和借款人2：
• 300（第一贷款人）>；=100（第一借款人所需金额）
• 因此，将第一贷款人“贷款人1”的价值分配或设定给 取下钱并设置剩余值200的第一个值 AmountLenderHave将是300，因为这是该人第一次 是贷款金额
• 200（第一贷款人）>；=200（第二借款人所需金额）

像这样，迭代每个贷方行，以此类推，直到没有贷方剩余

Answer 1

我不认为使用pandas join函数有什么好的方法可以做到这一点，这确实是一个问题，你应该迭代一个借贷者列表。以下是您所追求的：

import pandas as pd

LenderDF = pd.DataFrame({
'LenderID':['Lender 1', 'Lender 2','Lender 3','Lender 4', 'Lender 5'],
'AmountHave': [300, 150, 500, 100, 200]
})
borrowerDF = pd.DataFrame({
'borrowerID': ['Borrower 1', 'Borrower 2','Borrower 3','Borrower 4'],
'AmountNeed': [100,200,150,300]
 })

lenders = LenderDF.to_dict(orient='records')
borrowers = borrowerDF.to_dict(orient='records')

for borrower in borrowers:
    # get next lender who can pay
    amount_needed = borrower['AmountNeed']
    lender = next(lender for lender in lenders if lender['AmountHave'] >= amount_needed)
    borrower['LenderID'] = lender['LenderID']
    borrower['LenderHave'] = lender['AmountHave']
    borrower['LenderRemaining'] = lender['AmountHave'] - amount_needed
    # Update lenders amount
    lender['AmountHave'] -= amount_needed


print(pd.DataFrame(borrowers))

   borrowerID  AmountNeed  LenderID  LenderHave  LenderRemaining
0  Borrower 1         100  Lender 1         300              200
1  Borrower 2         200  Lender 1         200                0
2  Borrower 3         150  Lender 2         150                0
3  Borrower 4         300  Lender 3         500              200