%pip install binpacking
Collecting binpacking Downloading binpacking-1.5.2.tar.gz (8.7 kB) Preparing metadata (setup.py) ... done Requirement already satisfied: future in /usr/local/lib/python3.10/dist-packages (from binpacking) (0.18.3) Building wheels for collected packages: binpacking Building wheel for binpacking (setup.py) ... done Created wheel for binpacking: filename=binpacking-1.5.2-py3-none-any.whl size=10093 sha256=5a467383fc82014966c382597a99ab24e030377cddacf223de133b8f2c8fcc8a Stored in directory: /root/.cache/pip/wheels/4f/09/07/93d7c3a8acc3f39fc972dd12b8b8131fdd00f9e61ca09ed723 Successfully built binpacking Installing collected packages: binpacking Successfully installed binpacking-1.5.2
# Check the csv data
import pandas as pd
CSV_URL = 'https://raw.githubusercontent.com/inigmat/exupery/main/files/bbs.csv'
data = pd.read_csv(CSV_URL)
data.head(5)
index | Unique Mark | Dia | Length | Qty | |
---|---|---|---|---|---|
0 | 0 | 100 | 10 | 2350 | 96 |
1 | 1 | 101 | 10 | 2800 | 24 |
2 | 2 | 102 | 10 | 3030 | 336 |
3 | 3 | 103 | 10 | 4230 | 144 |
4 | 4 | 200 | 12 | 450 | 320 |
from binpacking import to_constant_volume
# Constants
BAR_LENGTH = 11700
CSV_URL = CSV_URL
def calculate_required_bars(bar_schedule: pd.DataFrame, bar_length: int) -> tuple:
"""
Calculates the cutting schemes based on input data (bar schedule)
containing the length and quantity of bars and the length of the bars in stock.
"""
items = []
# Extracting data from DataFrame
for length, quantity in zip(bar_schedule['Length'], bar_schedule['Qty']):
items.extend([length] * quantity)
# Calculating the cutting schemes using bin packing algorithm
cutting_scheme = to_constant_volume(items, bar_length)
return cutting_scheme
def generate_cutting_table(cutting_data: dict) -> pd.DataFrame:
"""
Generates a dataframe (cutting chart) with quantity,
cuts, utilization, scrap, and diameter columns
based on the cutting data (cutting schemes).
"""
cutting_chart = pd.DataFrame(columns=["Qty", "Cuts", "Utilization", "Scrap", "Dia"])
# Processing cutting data for each diameter
for dia, cutting_schemes in cutting_data.items():
df_temp = pd.DataFrame(zip(cutting_schemes), columns=["Cuts"])
# Calculate the quantity of each cutting scheme
df_temp["Qty"] = df_temp["Cuts"].apply(lambda x: f"{cutting_schemes.count(x)}x")
# Calculate the total length used by each cutting scheme
df_temp["Utilization"] = df_temp["Cuts"].apply(sum)
# Calculate the amount of scrap for each cutting scheme
df_temp["Scrap"] = BAR_LENGTH - df_temp["Utilization"]
# Add the diameter column
df_temp["Dia"] = dia
# Change the type of values in the Cuts column to drop duplicates
df_temp["Cuts"] = df_temp["Cuts"].astype(str)
# Concatenate df_temp with cutting_scheme
cutting_chart = pd.concat([cutting_chart, df_temp], ignore_index=True)
# Drop duplicate rows
cutting_chart = cutting_chart.drop_duplicates()
# Reset the index
cutting_chart = cutting_chart.reset_index(drop=True)
return cutting_chart
def main() -> pd.DataFrame:
"""
Executes the main workflow to calculate cutting schemes and generate a cutting chart.
"""
# Read the data from CSV
data = pd.read_csv(CSV_URL)
cutting_data = {}
# Process data for each unique diameter
for dia in data['Dia'].unique():
data_dia = data[data['Dia'] == dia]
# Calculate the cutting schemes and store them in a dictionary
cutting_patterns = calculate_required_bars(data_dia, BAR_LENGTH)
cutting_data[dia] = cutting_patterns
# Print results
print(f'Required number of reinforcement bars with diameter {dia} mm: {len(cutting_patterns)} pcs.')
percent = ((data_dia['Qty'] * data_dia['Length']).sum()/(len(cutting_patterns) * BAR_LENGTH)) * 100
print(f'Total scrap: {round(100 - percent, 2)}%')
# Generate the cutting table
cutting_chart = generate_cutting_table(cutting_data)
return cutting_chart
if __name__ == "__main__":
# Execute the main function and store the resulting cutting table
cutting_chart = main()
Required number of reinforcement bars with diameter 10 mm: 168 pcs. Total scrap: 2.32% Required number of reinforcement bars with diameter 12 mm: 138 pcs. Total scrap: 1.47% Required number of reinforcement bars with diameter 16 mm: 143 pcs. Total scrap: 3.76% Required number of reinforcement bars with diameter 20 mm: 759 pcs. Total scrap: 4.82%
cutting_chart
Qty | Cuts | Utilization | Scrap | Dia | |
---|---|---|---|---|---|
0 | 72x | [4230, 4230, 3030] | 11490 | 210 | 10 |
1 | 88x | [3030, 3030, 3030, 2350] | 11440 | 260 | 10 |
2 | 6x | [2800, 2800, 2800, 2800] | 11200 | 500 | 10 |
3 | 2x | [2350, 2350, 2350, 2350] | 9400 | 2300 | 10 |
4 | 32x | [5280, 5280, 870] | 11430 | 270 | 12 |
5 | 42x | [3720, 3720, 3720, 450] | 11610 | 90 | 12 |
6 | 1x | [3720, 3720, 2300, 870, 870] | 11480 | 220 | 12 |
7 | 15x | [2300, 2300, 2300, 2300, 2300] | 11500 | 200 | 12 |
8 | 1x | [2300, 2300, 2300, 2300, 870, 870, 450] | 11390 | 310 | 12 |
9 | 3x | [870, 870, 870, 870, 870, 870, 870, 870, 870, ... | 11310 | 390 | 12 |
10 | 1x | [870, 870, 870, 870, 870, 840, 840, 840, 840, ... | 11520 | 180 | 12 |
11 | 11x | [840, 840, 840, 840, 840, 840, 840, 840, 840, ... | 11685 | 15 | 12 |
12 | 1x | [840, 840, 840, 840, 840, 840, 840, 840, 840, ... | 11385 | 315 | 12 |
13 | 20x | [765, 765, 765, 765, 765, 765, 765, 765, 765, ... | 11475 | 225 | 12 |
14 | 1x | [765, 765, 765, 765, 450, 450, 450, 450, 450, ... | 11610 | 90 | 12 |
15 | 9x | [450, 450, 450, 450, 450, 450, 450, 450, 450, ... | 11700 | 0 | 12 |
16 | 1x | [450, 450, 450, 450, 450, 450, 450, 450, 450, ... | 10350 | 1350 | 12 |
17 | 8x | [10340, 870] | 11210 | 490 | 16 |
18 | 8x | [9120, 870, 870] | 10860 | 840 | 16 |
19 | 8x | [7320, 870, 870, 870, 870, 870] | 11670 | 30 | 16 |
20 | 8x | [6235, 5240] | 11475 | 225 | 16 |
21 | 24x | [4840, 4840, 870, 870] | 11420 | 280 | 16 |
22 | 86x | [870, 870, 870, 870, 870, 870, 870, 870, 870, ... | 11310 | 390 | 16 |
23 | 1x | [870, 870] | 1740 | 9960 | 16 |
24 | 192x | [9085, 1900] | 10985 | 715 | 20 |
25 | 60x | [8885, 2800] | 11685 | 15 | 20 |
26 | 68x | [8885, 1900] | 10785 | 915 | 20 |
27 | 24x | [4000, 4000, 3585] | 11585 | 115 | 20 |
28 | 68x | [3985, 3985, 3585] | 11555 | 145 | 20 |
29 | 50x | [3885, 3885, 3885] | 11655 | 45 | 20 |
30 | 1x | [3885, 3885, 3785] | 11555 | 145 | 20 |
31 | 14x | [3785, 3785, 3785] | 11355 | 345 | 20 |
32 | 1x | [3785, 3585, 3585] | 10955 | 745 | 20 |
33 | 115x | [3585, 3585, 3585] | 10755 | 945 | 20 |
34 | 1x | [3585, 3385, 3385] | 10355 | 1345 | 20 |
35 | 15x | [3385, 3385, 3385] | 10155 | 1545 | 20 |
36 | 1x | [3385, 3185, 3185, 1900] | 11655 | 45 | 20 |
37 | 78x | [3185, 3185, 3185, 2100] | 11655 | 45 | 20 |
38 | 9x | [3185, 3185, 3185, 1900] | 11455 | 245 | 20 |
39 | 1x | [3185, 1900, 1900, 1900, 1900] | 10785 | 915 | 20 |
40 | 10x | [1900, 1900, 1900, 1900, 1900, 1900] | 11400 | 300 | 20 |
41 | 1x | [1900, 1900, 1740, 1740, 1740, 1740] | 10760 | 940 | 20 |
42 | 50x | [1740, 1740, 1740, 1740, 1740, 1740] | 10440 | 1260 | 20 |
from google.colab import files
# determining the name of the file
file_name = 'cutting_chart.xlsx'
# saving the excel
cutting_chart.to_excel(file_name)
# downloading the file
files.download('cutting_chart.xlsx')
print('DataFrame is written to Excel File successfully. Save the file')
DataFrame is written to Excel File successfully. Save the file