Gradual filling of NA values depending on the auxiliary rule table

Question

I need to iterate through a big data frame with millions or rows to fill the missing data. And it takes me ages to iterate through. Thinking to update my code by converting data frame to multi-dimensional array or to a dictionary.

Now the question is which one is faster to iterate through.

My code is as below:

# Loop through the inventory and production dataframes to fill the missing values in the inventory dataframe
for index, prod_row in summ_prod.iterrows():
    for i in range(0,prod_row[2]):
        for inv_index in inv_df1.index:
            if  ((inv_df1.iloc[inv_index, 3] == prod_row[1]) and (pd.isna(inv_df1.iloc[inv_index, 8])):
                inv_df1.iloc[inv_index, 6] = prod_row[4] #Sales Order
                inv_df1.iloc[inv_index, 8] = prod_row[5] #Customer
                inv_df1.iloc[inv_index, 9] = prod_row[6] #Market
                inv_df1.iloc[inv_index, 10] = prod_row[7] * float(inv_df1.iloc[inv_index,4]) #Total value
                inv_df1.iloc[inv_index, 11] = prod_row[7] #AUD/kg
                break

Sample dfs: 'inv'

,Lot No.,Slaughter Date,Item No.,Quantity,Status,Sales Order,Sales Invoice,Customer,Market,Total Value,$/kg 
0,20230630-003001,2023-06-30,4161,10.2,SOH,,,,,, 
1,20230630-003002,2023-06-30,4161,11.15,SOH,,,,,, 
2,20230630-003003,2023-06-30,4161,11.95,SOH,,,,,,
3,20230630-003004,2023-06-30,4161,10.3,SOH,,,,,, 
4,20230630-003005,2023-06-30,4161,10.85,SOH,,,,,, 
5,20230630-003006,2023-06-30,4161,10.25,SOH,,,,,, 
6,20230630-003007,2023-06-30,4161,11.2,SOH,,,,,, 
7,20230630-003010,2023-06-30,4161,11.05,SOH,,,,,, 
8,20230630-003008,2023-06-30,4161,11.05,SOH,,,,,,
9,20230630-003009,2023-06-30,4161,10.85,SOH,,,,,, 
10,20230630-003011,2023-06-30,4161,10.95,SOH,,,,,, 
11,20230630-003012,2023-06-30,4161,9.8,SOH,,,,,, 
12,20230703-001001,2023-06-30,14101,13.1,Sale,10004505.0,10006247.0,TCI,USA,689.03,52.59770992366412
13,20230703-001002,2023-06-30,14101,11.3,Sale,10004505.0,10006247.0,TCI,USA,594.35,52.59734513274336 
14,20230703-001003,2023-06-30,14101,11.8,Sale,10004505.0,10006247.0,TCI,USA,620.65,52.59745762711864 
15,20230703-001004,2023-06-30,14101,11.7,Sale,10004505.0,10006247.0,AWM,DOMESTIC,538.2,46.00000000000001
16,20230703-003001,2023-06-30,14101,12.2,Sale,10004505.0,10006247.0,TCI,USA,641.69,52.59754098360656 
17,20230703-001005,2023-06-30,14101,11.55,Sale,10004505.0,10006247.0,TCI,USA,607.5,52.59740259740259 
18,20230703-001006,2023-06-30,14101,11.55,Sale,10004505.0,10006247.0,TCI,USA,607.5,52.59740259740259
19,20230703-001007,2023-06-30,14101,12.0,Sale,10004505.0,10006247.0,TCI,USA,631.17,52.5975 
20,20230703-007001,2023-06-30,14101,12.05,Sale,10004505.0,10006247.0,TCI,USA,633.8,52.59751037344397 
21,20230703-007002,2023-06-30,14101,10.0,Sale,10004505.0,10006247.0,TCI,USA,525.97,52.597
22,20230703-007003,2023-06-30,14101,11.95,Sale,10004505.0,10006247.0,TCI,USA,628.54,52.59748953974896 
23,20230703-003002,2023-06-30,14101,9.95,Sale,10004505.0,10006247.0,TCI,USA,523.34,52.59698492462312 
24,20230703-001008,2023-06-30,14101,11.25,Sale,10004505.0,10006247.0,TCI,USA,591.72,52.59733333333334
25,20230703-003003,2023-06-30,14101,10.4,Sale,10004505.0,10006247.0,AWM,DOMESTIC,478.4,45.99999999999999 
26,20230703-001009,2023-06-30,14101,10.95,Sale,10004505.0,10006247.0,TCI,USA,575.94,52.59726027397261 
27,20230703-007004,2023-06-30,14101,12.15,Sale,10004505.0,10006247.0,TCI,USA,639.06,52.59753086419752
28,20230703-007005,2023-06-30,14101,11.9,Sale,10004505.0,10006247.0,TCI,USA,625.91,52.59747899159663 
29,20230703-007006,2023-06-30,14101,11.75,Sale,10004505.0,10006247.0,TCI,USA,618.02,52.59744680851064 
30,20230703-007007,2023-06-30,14101,11.65,Sale,10004505.0,10006247.0,TCI,USA,612.76,52.59742489270386
31,20230703-007008,2023-06-30,14101,13.05,Sale,10004505.0,10006247.0,TCI,USA,686.4,52.59770114942528 
32,20230703-007009,2023-06-30,14101,13.2,Sale,10004505.0,10006247.0,TCI,USA,694.29,52.59772727272728 
33,20230703-007010,2023-06-30,14101,12.3,Sale,10004505.0,10006247.0,TCI,USA,646.95,52.59756097560976
34,20230703-007011,2023-06-30,14101,12.1,Sale,10004505.0,10006247.0,TCI,USA,636.43,52.59752066115702 
35,20230703-001010,2023-06-30,14101,8.55,Sale,10004505.0,10006247.0,TCI,USA,449.71,52.59766081871344 
36,20230710-001032,2023-07-07,4141,12.2,Sale,10004505.0,10006247.0,PPT,DOMESTIC,134.2,11.0
37,20230710-007012,2023-07-07,4141,11.45,Sale,10004505.0,10006247.0,PPT,DOMESTIC,125.95,11.0 
38,20230710-001033,2023-07-07,4141,12.6,Sale,10004505.0,10006247.0,PPT,DOMESTIC,138.6,11.0 
39,20230710-003006,2023-07-07,4141,11.75,Sale,10004505.0,10006247.0,PPT,DOMESTIC,129.25,11.0
40,20230710-001010,2023-07-07,4143,13.55,Sale,10004505.0,10006247.0,PPT,DOMESTIC,213.41,15.74981549815498 
41,20230710-001011,2023-07-07,4143,15.4,Sale,10004505.0,10006247.0,PPT,DOMESTIC,242.55,15.75 
42,20230710-001012,2023-07-07,4143,15.75,Sale,10004505.0,10006247.0,PPT,DOMESTIC,248.06,15.74984126984127
43,20230710-001001,2023-07-07,14100,10.7,SOH,,,,,, 
44,20230710-001002,2023-07-07,14100,11.4,SOH,,,,,, 
45,20230710-001003,2023-07-07,14100,10.45,SOH,,,,,, 
46,20230710-001004,2023-07-07,14100,10.1,SOH,,,,,, 
47,20230710-003001,2023-07-07,14100,10.95,SOH,,,,,,
48,20230710-001005,2023-07-07,14100,11.05,SOH,,,,,, 
49,20230710-007001,2023-07-07,14100,10.3,SOH,,,,,, 
50,20230710-001006,2023-07-07,14100,9.35,SOH,,,,,, 
51,20230710-001007,2023-07-07,14100,11.7,SOH,,,,,, 
52,20230710-001008,2023-07-07,14100,10.55,SOH,,,,,,
53,20230710-001009,2023-07-07,14100,10.2,Sale,10004505.0,10006247.0,AWM,DOMESTIC,339.35,33.26960784313726 
54,20230710-007002,2023-07-07,14100,11.6,Sale,10004505.0,10006247.0,AWM,DOMESTIC,385.93,33.2698275862069 
55,20230710-007003,2023-07-07,14100,11.15,Sale,10004505.0,10006247.0,AWM,DOMESTIC,370.96,33.26995515695067
56,20230710-007004,2023-07-07,14100,10.2,Sale,10004505.0,10006247.0,AWM,DOMESTIC,339.36,33.27058823529412 
57,20230710-001010,2023-07-07,14100,9.7,Sale,10004505.0,10006247.0,AWM,DOMESTIC,322.72,33.27010309278351 
58,20230710-003002,2023-07-07,14100,10.6,Sale,10004505.0,10006247.0,AWM,DOMESTIC,352.66,33.26981132075472
59,20230710-001011,2023-07-07,14100,10.6,Sale,10004549.0,10006263.0,AWM,DOMESTIC,352.66,33.26981132075472 
60,20230710-001012,2023-07-07,14100,9.65,Sale,10004549.0,10006263.0,AWM,DOMESTIC,321.06,33.27046632124352 
61,20230710-001001,2023-07-07,14101,12.4,SOH,,,,,,
62,20230710-001002,2023-07-07,14101,11.75,SOH,,,,,, 
63,20230710-001003,2023-07-07,14101,11.15,SOH,,,,,, 
64,20230710-001004,2023-07-07,14101,12.15,SOH,,,,,, 
65,20230710-001005,2023-07-07,14101,12.8,SOH,,,,,, 
66,20230710-001006,2023-07-07,14101,12.35,SOH,,,,,,
67,20230710-001007,2023-07-07,14101,12.5,SOH,,,,,, 
68,20230710-001008,2023-07-07,14101,10.9,Sale,10004549.0,10006263.0,TCI,USA,567.74,52.08623853211009 
69,20230710-001009,2023-07-07,14101,11.9,SOH,,,,,, 
70,20230710-001010,2023-07-07,14101,11.6,Sale,10004549.0,10006263.0,TCI,USA,604.2,52.08620689655173
71,20230710-001011,2023-07-07,14101,12.95,SOH,,,,,673.2646241791044,51.98954626865672 
72,20230710-001012,2023-07-07,14101,11.25,SOH,,,,,, 
73,20230710-001014,2023-07-07,14101,12.65,Sale,10004549.0,10006263.0,TCI,USA,658.89,52.08616600790513 
74,20230710-001015,2023-07-07,14101,11.95,SOH,,,,,,
75,20230710-001013,2023-07-07,14101,13.3,Sale,10004549.0,10006263.0,TCI,USA,692.75,52.08646616541353 
76,20230710-001016,2023-07-07,14101,11.2,Sale,10004549.0,10006263.0,TCI,USA,583.37,52.08660714285715 
77,20230710-001017,2023-07-07,14101,12.8,Sale,10004549.0,10006263.0,TCI,USA,666.71,52.08671875
78,20230710-001018,2023-07-07,14101,11.2,Sale,10004549.0,10006263.0,TCI,USA,583.37,52.08660714285715 
79,20230710-001019,2023-07-07,14101,11.05,Sale,10004549.0,10006263.0,TCI,USA,575.55,52.08597285067872 
80,20230710-003001,2023-07-07,14101,11.75,Sale,10004549.0,10006263.0,TCI,USA,612.01,52.08595744680851
81,20230710-001020,2023-07-07,14101,12.6,Sale,10004549.0,10006263.0,TCI,USA,656.29,52.08650793650794 
82,20230710-001021,2023-07-07,14101,11.6,Sale,10004549.0,10006263.0,TCI,USA,604.2,52.08620689655173 
83,20230710-001022,2023-07-07,14101,13.55,SOH,,,,,,
84,20230710-001023,2023-07-07,14101,11.65,Sale,10004549.0,10006263.0,TCI,USA,606.81,52.08669527896995 
85,20230710-001024,2023-07-07,14101,12.75,Sale,10004549.0,10006263.0,TCI,USA,664.1,52.08627450980392 
86,20230710-007001,2023-07-07,14101,12.0,Sale,10004549.0,10006263.0,TCI,USA,625.04,52.08666666666667
87,20230710-003002,2023-07-07,14101,12.05,SOH,,,,,, 
88,20230710-007002,2023-07-07,14101,11.85,Sale,10004549.0,10006263.0,TCI,USA,617.22,52.08607594936709 
89,20230710-007003,2023-07-07,14101,12.75,Sale,10004549.0,10006263.0,TCI,USA,664.1,52.08627450980392
90,20230710-003003,2023-07-07,14101,12.7,Sale,10004549.0,10006263.0,TCI,USA,661.5,52.08661417322835 
91,20230710-001025,2023-07-07,14101,11.35,Sale,10004549.0,10006263.0,TCI,USA,591.18,52.0863436123348 
92,20230710-001026,2023-07-07,14101,10.75,Sale,10004549.0,10006263.0,TCI,USA,559.93,52.08651162790697
93,20230710-001027,2023-07-07,14101,12.95,SOH,,,,,, 
94,20230710-001028,2023-07-07,14101,10.5,Sale,10004549.0,10006263.0,TCI,USA,546.9,52.08571428571428 
95,20230710-007004,2023-07-07,14101,11.45,Sale,10004549.0,10006263.0,TCI,USA,596.39,52.08646288209607
96,20230710-007005,2023-07-07,14101,12.2,Sale,10004549.0,10006263.0,TCI,USA,635.45,52.0860655737705 
97,20230710-001029,2023-07-07,14101,11.9,Sale,10004549.0,10006263.0,TCI,USA,619.83,52.08655462184874 
98,20230710-001030,2023-07-07,14101,11.85,SOH,,,,,,
99,20230710-001031,2023-07-07,14101,12.65,Sale,10004549.0,10006263.0,TCI,USA,658.89,52.08616600790513 
100,20230710-001032,2023-07-07,14101,13.6,Sale,10004549.0,10006263.0,TCI,USA,708.37,52.08602941176471 
101,20230710-001001,2023-07-07,14104,14.4,SOH,,,,,,
102,20230710-001002,2023-07-07,14104,17.0,SOH,,,,,, 
103,20230710-001003,2023-07-07,14104,15.5,SOH,,,,,, 
104,20230710-001004,2023-07-07,14104,14.2,SOH,,,,,, 
105,20230710-001005,2023-07-07,14104,16.3,SOH,,,,,, 
106,20230710-001006,2023-07-07,14104,17.75,SOH,,,,,,
107,20230710-001007,2023-07-07,14104,15.75,SOH,,,,,, 
108,20230710-001008,2023-07-07,14104,15.8,SOH,,,,,, 
109,20230710-001009,2023-07-07,14104,16.7,SOH,,,,,, 
110,20230710-001010,2023-07-07,14104,15.65,SOH,,,,,, 
111,20230710-001011,2023-07-07,14104,16.75,SOH,,,,,,
112,20230710-001012,2023-07-07,14104,15.7,SOH,,,,,, 
113,20230710-001013,2023-07-07,14104,15.6,SOH,,,,,, 
114,20230710-001014,2023-07-07,14104,16.05,SOH,,,,,, 
115,20230710-001015,2023-07-07,14104,18.1,SOH,,,,,, 
116,20230710-001016,2023-07-07,14104,15.85,SOH,,,,,,
117,20230710-001017,2023-07-07,14104,18.7,SOH,,,,,, 
118,20230710-001018,2023-07-07,14104,18.85,SOH,,,,,, 
119,20230710-001019,2023-07-07,14104,16.7,SOH,,,,,, 
120,20230710-001020,2023-07-07,14104,16.05,SOH,,,,,, 
121,20230710-001021,2023-07-07,14104,16.85,SOH,,,,,,
122,20230710-001022,2023-07-07,14104,17.3,SOH,,,,,, 
123,20230710-001023,2023-07-07,14104,17.35,SOH,,,,,, 
124,20230710-001024,2023-07-07,14104,16.2,SOH,,,,,, 
125,20230710-001025,2023-07-07,14104,16.9,SOH,,,,,, 
126,20230710-001026,2023-07-07,14104,16.45,SOH,,,,,,
127,20230710-001027,2023-07-07,14104,13.9,SOH,,,,,, 
128,20230710-001028,2023-07-07,14104,16.4,SOH,,,,,, 
129,20230710-001029,2023-07-07,14104,20.05,SOH,,,,,, 
130,20230710-007001,2023-07-07,14104,17.6,SOH,,,,,, 
131,20230710-001030,2023-07-07,14104,13.4,SOH,,,,,,
132,20230710-001031,2023-07-07,14104,17.9,SOH,,,,,, 
133,20230710-001032,2023-07-07,14104,16.75,SOH,,,,,, 
134,20230710-001033,2023-07-07,14104,15.5,SOH,,,,,, 
135,20230710-001034,2023-07-07,14104,14.85,SOH,,,,,, 
136,20230710-001035,2023-07-07,14104,19.5,SOH,,,,,,
137,20230710-001036,2023-07-07,14104,17.6,SOH,,,,,, 
138,20230710-001037,2023-07-07,14104,16.2,SOH,,,,,, 
139,20230710-001038,2023-07-07,14104,16.45,SOH,,,,,, 
140,20230710-001039,2023-07-07,14104,16.35,SOH,,,,,, 
141,20230710-001040,2023-07-07,14104,15.95,SOH,,,,,,
142,20230710-001041,2023-07-07,14104,15.45,SOH,,,,,, 
143,20230710-001042,2023-07-07,14104,16.4,Sale,10004549.0,10006263.0,AWM,DOMESTIC,836.4,51.0 
144,20230710-007002,2023-07-07,14104,17.55,Sale,10004549.0,10006263.0,AWM,DOMESTIC,895.05,50.99999999999999
145,20230710-007003,2023-07-07,14104,15.8,Sale,10004549.0,10006263.0,AWM,DOMESTIC,805.8,50.99999999999999 
146,20230710-001043,2023-07-07,14104,17.45,Sale,10004549.0,10006263.0,AWM,DOMESTIC,889.95,51.00000000000001 
147,20230710-001044,2023-07-07,14104,16.8,Sale,10004549.0,10006263.0,AWM,DOMESTIC,856.8,50.99999999999999
148,20230710-001045,2023-07-07,14104,16.4,Sale,10004549.0,10006263.0,AWM,DOMESTIC,836.4,51.0 
149,20230710-001046,2023-07-07,14104,17.15,Sale,10004549.0,10006263.0,AWM,DOMESTIC,874.65,51.0 
150,20230710-007004,2023-07-07,14104,17.4,SOH,,,,,, 
151,20230710-003001,2023-07-07,14104,5.5,SOH,,,,,,
152,20230717-001001,2023-07-14,4141,11.9,SOH,,,,,, 
153,20230717-001002,2023-07-14,4141,11.9,SOH,,,,,, 
154,20230717-001003,2023-07-14,4141,11.75,SOH,,,,,, 
155,20230717-001004,2023-07-14,4141,12.55,SOH,,,,,, 
156,20230717-001005,2023-07-14,4141,12.55,SOH,,,,,,
157,20230717-007001,2023-07-14,4141,12.1,SOH,,,,,, 
158,20230717-001006,2023-07-14,4141,12.05,SOH,,,,,, 
159,20230717-001007,2023-07-14,4141,10.45,SOH,,,,,, 
160,20230717-001008,2023-07-14,4141,11.05,SOH,,,,,, 
161,20230717-001009,2023-07-14,4141,10.5,SOH,,,,,,
162,20230717-001010,2023-07-14,4141,10.35,SOH,,,,,, 
163,20230717-001011,2023-07-14,4141,11.45,SOH,,,,,, 
164,20230717-001012,2023-07-14,4141,11.1,SOH,,,,,, 
165,20230717-003001,2023-07-14,4141,9.55,SOH,,,,,, 
166,20230717-003002,2023-07-14,4141,11.05,SOH,,,,,,
167,20230717-001013,2023-07-14,4141,8.9,SOH,,,,,, 
168,20230717-001014,2023-07-14,4141,10.9,SOH,,,,,, 
169,20230717-003003,2023-07-14,4141,10.9,SOH,,,,,, 
170,20230717-001015,2023-07-14,4141,9.5,SOH,,,,,, 
171,20230717-007002,2023-07-14,4141,10.5,SOH,,,,,,
172,20230717-001016,2023-07-14,4141,12.75,SOH,,,,,, 
173,20230717-003004,2023-07-14,4141,10.5,SOH,,,,,, 
174,20230717-001017,2023-07-14,4141,12.05,SOH,,,,,, 
175,20230717-001018,2023-07-14,4141,9.95,SOH,,,,,, 
176,20230717-001019,2023-07-14,4141,11.6,SOH,,,,,,
177,20230717-003005,2023-07-14,4141,9.45,SOH,,,,,, 
178,20230717-001020,2023-07-14,4141,12.35,SOH,,,,,, 
179,20230717-003006,2023-07-14,4141,10.9,SOH,,,,,, 
180,20230717-001021,2023-07-14,4141,7.25,SOH,,,,,, 
181,20230717-001022,2023-07-14,4141,9.85,SOH,,,,,,
182,20230717-003007,2023-07-14,4141,10.45,SOH,,,,,, 
183,20230717-001023,2023-07-14,4141,9.75,SOH,,,,,, 
184,20230717-001024,2023-07-14,4141,11.2,SOH,,,,,, 
185,20230717-001025,2023-07-14,4141,8.25,SOH,,,,,, 
186,20230717-003008,2023-07-14,4141,11.4,SOH,,,,,,
187,20230717-001026,2023-07-14,4141,9.7,SOH,,,,,, 
188,20230717-001027,2023-07-14,4141,10.35,SOH,,,,,, 
189,20230717-001028,2023-07-14,4141,10.2,SOH,,,,,, 
190,20230717-001029,2023-07-14,4141,11.7,SOH,,,,,, 
191,20230717-001030,2023-07-14,4141,11.85,SOH,,,,,,
192,20230717-001031,2023-07-14,4141,10.9,SOH,,,,,, 
193,20230717-001032,2023-07-14,4141,11.55,SOH,,,,,, 
194,20230717-001033,2023-07-14,4141,11.05,SOH,,,,,, 
195,20230717-001034,2023-07-14,4141,12.9,SOH,,,,,, 
196,20230717-001035,2023-07-14,4141,12.85,SOH,,,,,,
197,20230717-001036,2023-07-14,4141,13.1,SOH,,,,,, 
198,20230717-001037,2023-07-14,4141,10.7,SOH,,,,,, 
199,20230717-001038,2023-07-14,4141,11.4,SOH,,,,,, 
200,20230717-001039,2023-07-14,4141,9.8,SOH,,,,,, 
201,20230717-001001,2023-07-14,4143,13.75,SOH,,,,,,
202,20230717-001002,2023-07-14,4143,13.6,SOH,,,,,, 
203,20230717-001003,2023-07-14,4143,13.3,SOH,,,,,, 
204,20230717-001004,2023-07-14,4143,11.5,SOH,,,,,, 
205,20230717-001005,2023-07-14,4143,14.55,SOH,,,,,, 
206,20230717-001006,2023-07-14,4143,12.75,SOH,,,,,,
207,20230717-003001,2023-07-14,4143,12.95,SOH,,,,,, 
208,20230717-001007,2023-07-14,4143,12.95,SOH,,,,,, 
209,20230717-001008,2023-07-14,4143,13.75,SOH,,,,,, 
210,20230717-001009,2023-07-14,4143,11.8,SOH,,,,,, 
211,20230717-003002,2023-07-14,4143,12.9,SOH,,,,,,
212,20230717-001010,2023-07-14,4143,12.25,SOH,,,,,, 
213,20230717-001001,2023-07-14,14101,10.9,SOH,,,,,, 
214,20230717-001002,2023-07-14,14101,10.8,Repacked,,,,,, 
215,20230717-001003,2023-07-14,14101,11.65,SOH,,,,,, 
216,20230717-001004,2023-07-14,14101,11.15,Repacked,,,,,,
217,20230717-001005,2023-07-14,14101,11.15,SOH,,,,,, 
218,20230717-001006,2023-07-14,14101,11.3,SOH,,,,,, 
219,20230717-001007,2023-07-14,14101,13.15,SOH,,,,,, 
220,20230717-001008,2023-07-14,14101,11.8,SOH,,,,,, 
221,20230717-001009,2023-07-14,14101,11.0,SOH,,,,,,
222,20230717-001010,2023-07-14,14101,12.35,SOH,,,,,, 
223,20230717-001011,2023-07-14,14101,11.85,SOH,,,,,, 
224,20230717-001012,2023-07-14,14101,12.05,SOH,,,,,, 
225,20230717-003001,2023-07-14,14101,10.85,Repacked,,,,,, 
226,20230717-001013,2023-07-14,14101,10.9,SOH,,,,,,
227,20230717-001014,2023-07-14,14101,11.6,SOH,,,,,, 
228,20230717-001015,2023-07-14,14101,11.95,Repacked,,,,,, 
229,20230717-007001,2023-07-14,14101,11.95,SOH,,,,,, 
230,20230717-007002,2023-07-14,14101,11.75,SOH,,,,,, 
231,20230717-007003,2023-07-14,14101,10.9,SOH,,,,,,
232,20230717-001016,2023-07-14,14101,11.35,SOH,,,,,, 
233,20230717-007004,2023-07-14,14101,11.85,SOH,,,,,, 
234,20230717-007005,2023-07-14,14101,10.9,SOH,,,,,, 
235,20230717-001017,2023-07-14,14101,10.7,SOH,,,,,, 
236,20230717-001018,2023-07-14,14101,10.8,SOH,,,,,,
237,20230717-007006,2023-07-14,14101,11.5,SOH,,,,,, 
238,20230717-007007,2023-07-14,14101,11.05,SOH,,,,,, 
239,20230717-001019,2023-07-14,14101,11.35,SOH,,,,,, 
240,20230717-007008,2023-07-14,14101,12.15,SOH,,,,,, 
241,20230717-007009,2023-07-14,14101,11.5,Repacked,,,,,,
242,20230717-001020,2023-07-14,14101,11.55,SOH,,,,,, 
243,20230717-003002,2023-07-14,14101,12.75,SOH,,,,,, 
244,20230717-001021,2023-07-14,14101,10.6,SOH,,,,,, 
245,20230717-003003,2023-07-14,14101,11.95,SOH,,,,,, 
246,20230717-001022,2023-07-14,14101,11.9,SOH,,,,,,
247,20230717-003004,2023-07-14,14101,11.4,SOH,,,,,, 
248,20230717-001023,2023-07-14,14101,11.0,SOH,,,,,, 
249,20230717-001024,2023-07-14,14101,10.4,SOH,,,,,, 
250,20230717-003005,2023-07-14,14101,12.25,SOH,,,,,, 
251,20230717-003006,2023-07-14,14101,12.0,SOH,,,,,,
252,20230717-001025,2023-07-14,14101,11.9,SOH,,,,,, 
253,20230717-001026,2023-07-14,14101,11.45,SOH,,,,,, 
254,20230717-007010,2023-07-14,14101,12.25,SOH,,,,,, 
255,20230717-001027,2023-07-14,14101,11.0,SOH,,,,,, 
256,20230717-007011,2023-07-14,14101,10.9,SOH,,,,,,
257,20230717-001028,2023-07-14,14101,9.9,SOH,,,,,, 
258,20230717-007012,2023-07-14,14101,10.25,SOH,,,,,, 
259,20230717-003007,2023-07-14,14101,10.2,SOH,,,,,, 
260,20230717-001029,2023-07-14,14101,10.25,SOH,,,,,, 
261,20230717-001030,2023-07-14,14101,9.9,SOH,,,,,,
262,20230717-007013,2023-07-14,14101,10.1,SOH,,,,,, 
263,20230717-007014,2023-07-14,14101,12.4,SOH,,,,,, 
264,20230717-001033,2023-07-14,14101,12.05,SOH,,,,,, 
265,20230717-001034,2023-07-14,14101,10.55,SOH,,,,,, 
266,20230717-001031,2023-07-14,14101,11.95,SOH,,,,,,
267,20230717-001032,2023-07-14,14101,10.8,SOH,,,,,, 
268,20230717-003008,2023-07-14,14101,11.2,SOH,,,,,, 
269,20230717-001035,2023-07-14,14101,10.95,SOH,,,,,, 
270,20230717-001036,2023-07-14,14101,12.15,SOH,,,,,, 
271,20230717-007015,2023-07-14,14101,11.6,SOH,,,,,,
272,20230717-003009,2023-07-14,14101,11.45,SOH,,,,,, 
273,20230717-001037,2023-07-14,14101,12.45,SOH,,,,,, 
274,20230717-001040,2023-07-14,14101,10.5,SOH,,,,,, 
275,20230717-001041,2023-07-14,14101,10.85,SOH,,,,,, 
276,20230717-001039,2023-07-14,14101,11.8,SOH,,,,,,
277,20230717-001042,2023-07-14,14101,11.9,SOH,,,,,, 
278,20230717-001043,2023-07-14,14101,12.2,SOH,,,,,, 
279,20230717-001044,2023-07-14,14101,11.3,SOH,,,,,, 
280,20230717-001038,2023-07-14,14101,11.4,SOH,,,,,, 
281,20230717-003010,2023-07-14,14101,12.7,SOH,,,,,,
282,20230717-001045,2023-07-14,14101,12.2,SOH,,,,,, 
283,20230717-001046,2023-07-14,14101,12.1,SOH,,,,,, 
284,20230717-001047,2023-07-14,14101,10.6,SOH,,,,,, 
285,20230717-003011,2023-07-14,14101,10.7,SOH,,,,,, 
286,20230717-007016,2023-07-14,14101,11.45,SOH,,,,,,
287,20230717-001048,2023-07-14,14101,12.6,SOH,,,,,, 
288,20230717-001049,2023-07-14,14101,11.3,SOH,,,,,, 
289,20230717-003012,2023-07-14,14101,12.1,SOH,,,,,, 
290,20230717-001050,2023-07-14,14101,11.4,SOH,,,,,, 
291,20230717-007017,2023-07-14,14101,13.7,SOH,,,,,,
292,20230717-001001,2023-07-14,14104,17.05,SOH,,,,,, 
293,20230717-001002,2023-07-14,14104,16.9,SOH,,,,,, 
294,20230717-007001,2023-07-14,14104,16.25,SOH,,,,,, 
295,20230717-001003,2023-07-14,14104,17.7,SOH,,,,,, 
296,20230717-001004,2023-07-14,14104,17.65,SOH,,,,,,
297,20230717-001005,2023-07-14,14104,18.85,SOH,,,,,, 
298,20230717-001006,2023-07-14,14104,18.15,SOH,,,,,, 
299,20230717-001007,2023-07-14,14104,17.6,SOH,,,,,, 
300,20230717-001008,2023-07-14,14104,16.5,SOH,,,,,, 
301,20230717-001009,2023-07-14,14104,17.1,Repacked,,,,,,
302,20230717-001010,2023-07-14,14104,19.75,SOH,,,,,, 
303,20230717-001011,2023-07-14,14104,19.0,SOH,,,,,, 
304,20230717-001012,2023-07-14,14104,19.55,SOH,,,,,, 
305,20230717-003001,2023-07-14,14104,17.55,SOH,,,,,, 
306,20230717-001013,2023-07-14,14104,17.9,SOH,,,,,,
307,20230717-001014,2023-07-14,14104,16.5,SOH,,,,,, 
308,20230717-001015,2023-07-14,14104,17.15,SOH,,,,,, 
309,20230717-001016,2023-07-14,14104,17.45,SOH,,,,,, 
310,20230717-001018,2023-07-14,14104,15.75,SOH,,,,,, 
311,20230717-001017,2023-07-14,14104,16.75,SOH,,,,,,
312,20230717-001019,2023-07-14,14104,15.15,SOH,,,,,, 
313,20230717-001020,2023-07-14,14104,15.1,SOH,,,,,, 
314,20230717-001021,2023-07-14,14104,18.35,SOH,,,,,, 
315,20230717-001022,2023-07-14,14104,17.8,SOH,,,,,, 
316,20230717-001023,2023-07-14,14104,17.45,SOH,,,,,,
317,20230717-001024,2023-07-14,14104,16.05,SOH,,,,,, 
318,20230717-001025,2023-07-14,14104,16.95,SOH,,,,,, 
319,20230717-001026,2023-07-14,14104,16.65,SOH,,,,,, 
320,20230717-001027,2023-07-14,14104,17.05,SOH,,,,,, 
321,20230717-001028,2023-07-14,14104,15.25,SOH,,,,,,
322,20230717-001029,2023-07-14,14104,12.7,SOH,,,,,,

'prod'

,Item No.,Cartons,KG,SO No.,Customer,Market,AUD/kg
0,4141,27,328.05,SO-4529,PPT,DOMESTIC,11.0
1,4141,22,274.9,SO-4529,UMC,DOMESTIC,11.0
2,4143,12,164.85,SO-4529,PPT,DOMESTIC,15.75
3,4161,12,61.54761904761905,AWMS-1707,AWS,DOMESTIC,24.8
4,14101,16,196.95238095238096,SO-4548,AWS,DOMESTIC,24.8
5,14101,55,97.9453125,SO-4548,AWS,DOMESTIC,47.9
6,14101,18,65.296875,SO-4522,FSP,SINGAPORE,47.5
7,14104,72,119.7109375,SO-4547,GP,TAIWAN,51.5
8,14104,2,65.296875,SO-4552,UMC,DOMESTIC,47.9

Answer 1

I'm gonna assume that you're working with Pandas or a similar tool.

So here's the main problem which I can see in your code: you're not using labels and as a result you get easily confused about column indexes. I'd say, there's even more serious problem which can blow in your face in some future: you're mixing hard indexing and labels, as in this part:

for inv_index in inv_df1.index:
    if ((inv_df1.iloc[inv_index, 3] ...

As far as you're iterating over indexes, it's dangerous to use them in .iloc because your actual index may be different from the row's positional number. IMO in the above excerption, it would be safer to use something like for inv_index in range(len(inv_df1)): ....

Next problem, you are only using Pandas as a data container. Let's see if we can improve the code using DataFrame's features:

inv = pd.read_csv(inv_csv_file, index_col=0)
prod = pd.read_csv(prod_csv_file, index_col=0)

for i in prod.index:
    eq_Item_No = inv['Item No.'] == prod.at[i, 'Item No.']      # mark required "Item No."
    has_NA_Customer = inv['Customer'].isna()                    # mark void Customers
    selected = eq_Item_No & has_NA_Customer                     # select required "Item No" with empty Customer
    selected &= selected.cumsum() <= prod.at[i, 'Cartons']      # restrict selection to the first prod[Cartons] number of selected rows
    
    inv.loc[selected, 'Sales Order'] = prod.at[i, 'SO No.']     
    inv.loc[selected, 'Customer'] = prod.at[i, 'Customer']
    inv.loc[selected, 'Market'] = prod.at[i, 'Market']
    inv.loc[selected, '$/kg'] = prod.at[i, 'AUD/kg'] 
    inv.loc[selected, 'Total Value'] = \
        prod.at[i, 'AUD/kg'] * inv.loc[selected, 'Quantity']    # broadcast multiplication

On my hardware, the execution of this code will take about 18 ms, while the original code on the proposed data will take 3.25 s. I believe this result can be improved by eliminating the handling of the "Cartons" column, which resembles intermediate calculations, probably related to the "Slaughter Date" values previously featured in the source code.

---

P.S.

Let's see how the following line works:

selected &= selected.cumsum() <= prod.at[i, 'Cartons']

First, I'd like to rewrite it like this:

series &= series.cumsum() <= limit

where series is pandas.Series of boolean type, and limit is some number (to make sense, some non-negative integer, but not necessarily).

• series &= something is augmented assignment statement meaning series = series & something
• series.cumsum() is a cumulative sum, which in case of boolean values increments with each True value
• series & (series.cumsum() <= limit) returns all the values of series with only first limit number of True

For example, let's say limit = 4; series = pd.Series([0,1,1,0,1,0,1,1,1], dtype=bool):

series	cumsum	cumsum <= 4	series & (cumsum <= 4)
False	0	True	False
True	1	True	True
True	2	True	True
False	2	True	False
True	3	True	True
False	3	True	False
True	4	True	True
True	5	False	False
True	6	False	False

When we use transformed series in the above example as boolean indexing, we only get records placed where the first 4 True values of the original series occur.