admin管理员组文章数量:1336633
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.
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.
Share asked Nov 19, 2024 at 20:09 MangluManglu 2662 silver badges11 bronze badges 2- 1 can you provide an example of what you mean? – iBeMeltin Commented Nov 19, 2024 at 20:23
- I am allocating orders to the coils considering the order min and max weight. – Manglu Commented Nov 20, 2024 at 2:43
1 Answer
Reset to default 0The issue is in the splitting and allocation logic. You can solve this by improving the splitNumber function to return the correct number of parts within the specified min-max range. It updates the main allocation loop to handle scenarios in which coils are split across multiple orders while still meeting the 10% overage limit. I would suggest doing this:
import numpy as np
import pandas as pd
def splitNumber(n, a, b):
lb = int(np.ceil(n / b))
ub = int(np.floor(n / a))
for i in range(lb, ub + 1):
if a <= n / i <= b:
return i
return 1
dfo = pd.DataFrame({
'order': ['o1', 'o2', 'o3', 'o4', 'o5', 'o6', 'o7', 'o8', 'o9', 'o10', 'o11', 'o12'],
'bct': ['bct2', 'bct2', 'bct2', 'bct2', 'bct1', 'bct1', 'bct1', 'bct1', 'bct1', 'bct1', 'bct1', 'bct1'],
'TOP_MIN_COIL_WT': [3, 3, 3, 3, 4, 5, 3, 3, 4, 5, 3, 4],
'TOP_MAX_COIL_WT': [25, 25, 25, 25, 7, 7, 5, 5, 6, 7, 5, 7],
'demand': [12, 11.34, 9.46, 6.54, 51, 51, 34, 22.26, 17, 17, 17, 6.605]
})
dfmc = pd.DataFrame({
'mc': ['c1', 'c2', 'c3', 'c4', 'c5', 'c6', 'c7', 'c8', 'c9', 'c10', 'c11', 'c12', 'c13', 'c14', 'c15'],
'bct': ['bct2', 'bct2', 'bct1', 'bct1', 'bct1', 'bct1', 'bct1', 'bct1', 'bct1', 'bct1', 'bct1', 'bct1', 'bct1', 'bct1', 'bct1'],
'coiltons': [24.45, 24.1, 17.08, 17.04, 17.03, 17.01, 16.98, 16.98, 15.88, 15.76, 15.72, 15.65, 15.59, 13.16, 13.14]
})
outputRows = []
for _, order_row in dfo.iterrows():
order = order_row['order']
bct = order_row['bct']
minton = order_row['TOP_MIN_COIL_WT']
maxton = order_row['TOP_MAX_COIL_WT']
dem = order_row['demand']
max_allowable = dem * 1.1
matched_coils = dfmc[dfmc['bct'] == bct].sort_values('coiltons', ascending=False)
for coil_idx, coil_row in matched_coils.iterrows():
coil = coil_row['mc']
coilt = coil_row['coiltons']
while coilt > 0 and dem > 0:
if minton <= coilt <= maxton:
alloc = min(dem, coilt)
outputRows.append([order, bct, coil, alloc, coilt, 0])
dem -= alloc
coilt -= alloc
else:
noParts = splitNumber(coilt, minton, maxton)
if noParts > 1:
part_weight = coilt / noParts
alloc = min(dem, part_weight)
for part in range(noParts):
outputRows.append([order, bct, f"{coil}_{part+1}", alloc, part_weight, 1])
dem -= alloc
coilt -= alloc
if dem <= 0:
break
else:
break
dfmc.at[coil_idx, 'coiltons'] = coilt
if dem <= 0:
break
if dem <= 0:
break
outputColumns = ['order', 'typebc', 'coil', 'allocatedQuantity', 'ton', 'flag']
df_output = pd.DataFrame(outputRows, columns=outputColumns)
which gives
order typebc coil allocatedQuantity ton flag
0 o1 bct2 c1 12.000000 24.450000 0
1 o2 bct2 c2 11.340000 24.100000 0
2 o3 bct2 c2 9.460000 12.760000 0
3 o4 bct2 c1 6.540000 12.450000 0
4 o5 bct1 c3_1 5.693333 5.693333 1
5 o5 bct1 c3_2 5.693333 5.693333 1
6 o5 bct1 c3_3 5.693333 5.693333 1
7 o5 bct1 c4_1 5.680000 5.680000 1
8 o5 bct1 c4_2 5.680000 5.680000 1
9 o5 bct1 c4_3 5.680000 5.680000 1
10 o5 bct1 c5_1 5.676667 5.676667 1
11 o5 bct1 c5_2 5.676667 5.676667 1
12 o5 bct1 c5_3 5.676667 5.676667 1
13 o6 bct1 c6_1 5.670000 5.670000 1
14 o6 bct1 c6_2 5.670000 5.670000 1
15 o6 bct1 c6_3 5.670000 5.670000 1
16 o6 bct1 c7_1 5.660000 5.660000 1
17 o6 bct1 c7_2 5.660000 5.660000 1
18 o6 bct1 c7_3 5.660000 5.660000 1
19 o6 bct1 c8_1 5.660000 5.660000 1
20 o6 bct1 c8_2 5.660000 5.660000 1
21 o6 bct1 c8_3 5.660000 5.660000 1
22 o6 bct1 c9_1 0.030000 5.293333 1
23 o7 bct1 c9_1 3.962500 3.962500 1
...
44 o11 bct1 c14_1 4.745000 4.745000 1
45 o11 bct1 c14_2 4.745000 4.745000 1
46 o11 bct1 c13_1 3.897500 3.897500 1
47 o11 bct1 c13_2 3.897500 3.897500 1
本文标签： pandasAllocating orders to resources joining two python dataframeStack Overflow
版权声明：本文标题：pandas - Allocating orders to resources joining two python dataframe - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1742401233a2467920.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论