[Python] Allocating orders to resources joining two python dataframe

I have a dataframe question. I have two dataframes. 1st dataframe contains orders details and 2nd dataframe contains coil details. I need to assign orders to coils to satisfy the demand of the orders. Every orders have a minimum and maximum coil weight range, so when allocating the orders, I need to maintain the minimum and maximum tons. For example, say an order has a demand of 14 and min and max range is 10 and 14 respectively, the available coil weight is 24, then I need to divide the 24 tons coils into two parts, 12 each and then from one 12, I'll assign 12 and then from 2nd 12, I'll assign 2 to satisfy the demand of 14. My 1st dataframe (order denotes unique order ids) is

order bct TOP_MIN_COIL_WT TOP_MAX_COIL_WT demand
0 o1 bct2 3 25 12.000
1 o2 bct2 3 25 11.340
2 o3 bct2 3 25 9.460
3 o4 bct2 3 25 6.540
4 o5 bct1 4 7 51.000
5 o6 bct1 5 7 51.000
6 o7 bct1 3 5 34.000
7 o8 bct1 3 5 22.260
8 o9 bct1 4 6 17.000
9 o10 bct1 5 7 17.000
10 o11 bct1 3 5 17.000
11 o12 bct1 4 7 6.605


2nd datafarme (mc denotes unique coil number) is

mc bct coiltons
0 c1 bct2 24.45
1 c2 bct2 24.10
2 c3 bct1 17.08
3 c4 bct1 17.04
4 c5 bct1 17.03
5 c6 bct1 17.01
6 c7 bct1 16.98
7 c8 bct1 16.98
8 c9 bct1 15.88
9 c10 bct1 15.76
10 c11 bct1 15.72
11 c12 bct1 15.65
12 c13 bct1 15.59
13 c14 bct1 13.16
14 c15 bct1 13.14


To address parting, I have defined a function. I have written the code, but allocation is not coming right. Also note that, we can satisfy more than the demand (10% extra) to utilize a coil fully. My code is below

import numpy as np
import pandas as pd

def splitNumber(n, a, b):
if n < a or n < b:
return 1
lb = int(np.floor(n/a))
ub = int(np.floor(n/b))
for i in range(ub, lb+1):
s = n/i
if (s > a) and (s < b):
return i
return 1

outputRows = []
for index, row in dfo.iterrows():
order = row['order']
bct = row['bct']
minton = row['TOP_MIN_COIL_WT']
maxton = row['TOP_MAX_COIL_WT']
dem = row['demand']

matchedcoil = dfmc[dfmc['bct'] == bct]

for index, row in matchedcoil.iterrows():
coil = row['mc']
coilt = row['coiltons']
if (coilt > minton) and (coilt < maxton):
alloc = min(dem, coilt)
outputRows.append([order, bct, coil, alloc, coilt, 0])
dem = dem - alloc
coilt = coilt - alloc
dfmc.at[index, 'coiltons'] = coilt
if dem == 0:
break
else:
noParts = splitNumber(coilt, minton, maxton)
if noParts > 0:
alloc = min(dem, coilt / noParts)
for part in range(1, noParts + 1):
outputRows.append([order, bct, f"{coil}_{part}", alloc, coilt, 1])
dem = dem - alloc
coilt = coilt - alloc
dfmc.at[index, 'coiltons'] = coilt
if dem == 0:
break

outputColumns = ['order', 'typebc', 'coil', 'allocatedQuantity', 'ton', 'flag']
df_output = pd.DataFrame(outputRows, columns=outputColumns)


Pls help.

Continue reading...