Spaces:
Sleeping
Sleeping
File size: 5,752 Bytes
be50789 fd90912 be50789 fd90912 be50789 fd90912 be50789 fd90912 be50789 fd90912 be50789 fd90912 be50789 fd90912 be50789 fd90912 be50789 fd90912 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 |
import pandas as pd
import numpy as np
from io import StringIO
import matplotlib.pyplot as plt
def generate_intelligent_sourcing_excel(
output_filename,
num_of_warehouses,
num_of_products,
num_of_orders,
weightage_Cost,
weightage_Priority,
weightage_distance,
weightage_days,
range_priority,
range_prod_stock,
range_order,
range_cost,
range_distance,
range_days
):
"""
Generates an Excel file containing warehouse, order, and shipping data.
Parameters:
output_filename (str): The name of the output Excel file.
num_of_warehouses (int): Number of warehouses.
num_of_products (int): Number of products.
num_of_orders (int): Number of orders.
weightage_Cost (float): Weightage for cost.
weightage_Priority (float): Weightage for priority.
weightage_distance (float): Weightage for distance.
weightage_days (float): Weightage for days.
range_priority (tuple): Range for priority values.
range_prod_stock (tuple): Range for product stock values.
range_order (tuple): Range for order quantities.
range_cost (tuple): Range for cost values.
range_distance (tuple): Range for distance values.
range_days (tuple): Range for delivery days values.
"""
rng = np.random.default_rng(seed=42) # Random number generator
# Create weightage DataFrame
weightage_df = pd.DataFrame({
'Variable': ['Cost', 'Priority', 'Distance', 'Days'],
'Weightage': [weightage_Cost, weightage_Priority, weightage_distance, weightage_days]
})
# Generate priority data
priority_df = pd.DataFrame({
'Warehouse': [f'Warehouse#{w}' for w in range(1, num_of_warehouses + 1)],
'Priority': rng.integers(*range_priority, size=num_of_warehouses)
})
# Generate warehouse stock data
warehouse_df = pd.DataFrame({
'Warehouse': [f'Warehouse#{w}' for w in range(1, num_of_warehouses + 1)]
})
for p in range(1, num_of_products + 1):
warehouse_df[f'Product#{p}'] = rng.integers(*range_prod_stock, size=num_of_warehouses)
# Generate order data
order_df = pd.DataFrame({
'Order': [f'Order#{o}' for o in range(1, num_of_orders + 1)]
})
for p in range(1, num_of_products + 1):
order_df[f'Product#{p}'] = rng.integers(*range_order, size=num_of_orders)
# Function to generate shipping data
def generate_shipping_data(metric_name, value_range):
return pd.DataFrame([
(w, o, p, rng.integers(*value_range))
for w in warehouse_df["Warehouse"]
for o in order_df["Order"]
for p in warehouse_df.columns[1:]
], columns=["Warehouse", "Order", "Product", metric_name])
# Generate cost, distance, and days DataFrames
cost_df = generate_shipping_data("Cost", range_cost)
distance_df = generate_shipping_data("Distance", range_distance)
days_df = generate_shipping_data("Days", range_days)
# Write data to Excel
with pd.ExcelWriter(output_filename, engine='xlsxwriter') as writer:
weightage_df.to_excel(writer, sheet_name='Weightage', index=False)
priority_df.to_excel(writer, sheet_name='Priority Data', index=False)
warehouse_df.to_excel(writer, sheet_name='Warehouse Data', index=False)
order_df.to_excel(writer, sheet_name='Order Data', index=False)
cost_df.to_excel(writer, sheet_name='Cost Data', index=False)
distance_df.to_excel(writer, sheet_name='Distance Data', index=False)
days_df.to_excel(writer, sheet_name='Days Data', index=False)
print(f"Excel file '{output_filename}' has been successfully created.")
return
def plot_histograms(excel_filename):
"""
Reads the generated Excel file and plots separate histograms for relevant data columns.
"""
xls = pd.ExcelFile(excel_filename)
data_sheets = {
"Priority": pd.read_excel(xls, "Priority Data")["Priority"],
"Product Stock": pd.read_excel(xls, "Warehouse Data").iloc[:, 1:].values.flatten(),
"Order Quantity": pd.read_excel(xls, "Order Data").iloc[:, 1:].values.flatten(),
"Cost": pd.read_excel(xls, "Cost Data")["Cost"],
"Distance": pd.read_excel(xls, "Distance Data")["Distance"],
"Days": pd.read_excel(xls, "Days Data")["Days"]
}
fig, axes = plt.subplots(2, 3, figsize=(15, 10))
axes = axes.flatten()
for i, (label, data) in enumerate(data_sheets.items()):
axes[i].hist(data, bins=20, alpha=0.7, color='blue', edgecolor='black')
axes[i].set_title(label)
axes[i].set_xlabel("Value")
axes[i].set_ylabel("Frequency")
axes[i].grid(True)
plt.tight_layout()
plt.savefig("histogram_ranges.png")
plt.close()
print("Histogram saved as 'histogram_ranges.png'")
if __name__ == "__main__":
# Define parameters for execution
output_filename = "Intelligent_Sourcing.xlsx"
num_of_warehouses = 4
num_of_products = 10
num_of_orders = 2
weightage_Cost = 1
weightage_Priority = 0.8
weightage_distance = 0.6
weightage_days = 0.4
range_priority = (1, 10)
range_prod_stock = (1, 100)
range_order = (1, 10)
range_cost = (1, 300)
range_distance = (1, 200)
range_days = (1, 7)
# Run function
generate_intelligent_sourcing_excel(
output_filename,
num_of_warehouses,
num_of_products,
num_of_orders,
weightage_Cost,
weightage_Priority,
weightage_distance,
weightage_days,
range_priority,
range_prod_stock,
range_order,
range_cost,
range_distance,
range_days
)
plot_histograms(excel_filename=output_filename)
|