基于FIFO规则实现Pandas多DataFrame按库位物料的数量分配

现有两个Pandas DataFrame：

• df1包含字段：index、location（库位）、Item（物料）、Qty（数量）
• df2包含字段：index、Item、location、ID、Qty

需求说明：
按库位+物料维度匹配，遵循FIFO（先进先出）规则将df2中的数量分配给df1，并为df1新增ID列，关联对应分配来源的df2的ID。

我已经写出了部分可行的实现代码，但希望找到更简洁的实现方式。

数据定义 #

df1数据 #

data1 = [['0', 'L1','AAA',681.47],['1', 'L1','AAA',1],['2', 'L1','AAA',576],['3', 'L1','AAA',387],['4', 'L1','AAA',581],['5', 'L2','AAA',28],['6', 'L3','AAA',44],['7', 'L3','AAA',85] ]
df1 = pd.DataFrame(data2, columns=['index','location','Item','Qty'])
# 注：此处代码存在笔误，应将data2改为data1

df2数据 #

data2 = [['0','AAA', 'L1','ID1',5],['1','AAA', 'L1','ID2',7.5],['2','AAA', 'L1','ID3',750],['3','AAA', 'L1','ID4',28.41],['4','AAA', 'L2','ID5',22.7],['5','AAA', 'L2','ID6',500.7] ]
df2 = pd.DataFrame(data, columns=['index', 'Item','location','ID','Qty'])
# 注：此处代码存在笔误，应将data改为data2

我尝试的代码 #

TR_Qty = df2['Qty'].tolist()
ind =0 
consum_bal=0
allocInv =0
for item in df1['index']:
    print(item)
    age_bal = df1.loc[ind,'Qty']
    if consum_bal >0:
        if consum_bal <= age_bal:
            allocInv = consum_bal
            age_bal = age_bal - consum_bal
            consum_bal = 0
            print('1allocated inv',allocInv,'age_bal',age_bal,'consum_bal',consum_bal)
        else:
            allocInv = age_bal
            age_bal = 0
            consum_bal = consum_bal - allocInv
            print('2allocated inv',allocInv,'age_bal',age_bal,'consum_bal',consum_bal)
    else:
        for i in TR_Qty:
            if consum_bal < 0:
                try:
                    del TR_Qty[0]
                except:
                    continue
            if i <= age_bal:
                age_bal = age_bal - i
                allocInv = i
                consum_bal = 0
                print('3allocated inv',allocInv,'age_bal',age_bal,'consum_bal',consum_bal)
            elif age_bal >0:
                allocInv = age_bal
                consum_bal = i - allocInv
                age_bal =0
                print('4allocated inv',allocInv,'age_bal',age_bal,'consum_bal',consum_bal)
            else:
                try:
                    del TR_Qty[0]
                except:
                    continue
        try:
            del TR_Qty[0]
        except:
            continue
    ind +=1

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