pandas df iterate over one df column qty and allocate qty from another df column with fifo basis

DilumKri 0
Viewed 38

Thank you for your time!!!

I have 2 dfs, The first one has a location item and a quantity.

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'])

2nd df has item, location, ID and Qty.

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'])

I need to allocate the quantities for df1 from df2. subject to location and item number. and need to create a new column call "ID" (the objective is to identify the relevant IDs) in the new df based on the allocation.

My desired outcome is as follows, red high light output is enough

my attempt is as follows (partly correct), but I think there has to be a simpler way and (up to index 2 it's working perfectly).

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
iteration dataframe pandas loops python
1 Answers

Given

# df1

  location Item     Qty
0       L1  AAA  681.47
1       L1  AAA    1.00
2       L1  AAA  576.00
3       L1  AAA  387.00
4       L1  AAA  581.00
5       L2  AAA   28.00
6       L3  AAA   44.00
7       L3  AAA   85.00

# df2

  Item location   ID     Qty
0  AAA       L1  ID1    5.00
1  AAA       L1  ID2    7.50
2  AAA       L1  ID3  750.00
3  AAA       L1  ID4   28.41
4  AAA       L2  ID5   22.70
5  AAA       L2  ID6  500.70

Doing:

df = df1.merge(df2, on=['Item', 'location'])
print(df)

# Output:

   location Item   Qty_x   ID   Qty_y
0        L1  AAA  681.47  ID1    5.00
1        L1  AAA  681.47  ID2    7.50
2        L1  AAA  681.47  ID3  750.00
3        L1  AAA  681.47  ID4   28.41
4        L1  AAA    1.00  ID1    5.00
5        L1  AAA    1.00  ID2    7.50
6        L1  AAA    1.00  ID3  750.00
7        L1  AAA    1.00  ID4   28.41
8        L1  AAA  576.00  ID1    5.00
9        L1  AAA  576.00  ID2    7.50
10       L1  AAA  576.00  ID3  750.00
11       L1  AAA  576.00  ID4   28.41
12       L1  AAA  387.00  ID1    5.00
13       L1  AAA  387.00  ID2    7.50
14       L1  AAA  387.00  ID3  750.00
15       L1  AAA  387.00  ID4   28.41
16       L1  AAA  581.00  ID1    5.00
17       L1  AAA  581.00  ID2    7.50
18       L1  AAA  581.00  ID3  750.00
19       L1  AAA  581.00  ID4   28.41
20       L2  AAA   28.00  ID5   22.70
21       L2  AAA   28.00  ID6  500.70

Then apply the rest of your convoluted logic...

BeRT2me0
Related