组合数据帧以将贷方分配给借方（以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

这是我的解决办法。我也将数据帧转换为列表。本准则还考虑了借款人必须向多个贷款人借款的情况，如下例所示：

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,300,150,300]
 })

lender_have = lenderDF["AmountHave"].tolist()
borrower_need = borrowerDF["AmountNeed"].tolist()

borrowerID = []
lenderID = []
borrower_remaining_need = [borrower_need[0]]
lender_before = [lender_have[0]]
lender_after = []
l = 0
b = 0
while b < len(borrower_need):
    borrowerID.append(f'Borrower {b + 1}')
    lenderID.append(f'Lender {l + 1}')
    remaining_funds = lender_before[-1] - borrower_remaining_need[-1]

    if remaining_funds > 0:
        lender_after.append(remaining_funds)
        lender_before.append(remaining_funds)
        b += 1
        if b< len(borrower_need):
            borrower_remaining_need.append(borrower_need[b])

    elif remaining_funds < 0:
        lender_after.append(0)
        borrower_remaining_need.append(borrower_remaining_need[-1] - lender_before[-1])
        if l+1 < len(lender_have):
            lender_before.append(lender_have[l+1])
        l += 1

    elif remaining_funds == 0:
        lender_after.append(0)
        if l+1 < len(lender_have):
            lender_before.append(lender_have[l+1])
        if b+1 < len(borrower_need):
            borrower_remaining_need.append(borrower_need[b+1])
        b += 1
        l += 1

if len(lender_before) > len(borrowerID):
    lender_before.pop()

result_df = pd.DataFrame({"BorrowrID": borrowerID, "Amount Needed": borrower_remaining_need,
                          "LenderID": lenderID, "Lender Before": lender_before, "Lender After":lender_after},)

print(result_df)

    BorrowrID  Amount Needed  LenderID  Lender Before  Lender After
0  Borrower 1            100  Lender 1            300           200
1  Borrower 2            300  Lender 1            200             0
2  Borrower 2            100  Lender 2            150            50
3  Borrower 3            150  Lender 2             50             0
4  Borrower 3            100  Lender 3            500           400
5  Borrower 4            300  Lender 3            400           100

Answer 2

我不认为使用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