Skip to content

Instantly share code, notes, and snippets.

@kubrick06010

kubrick06010/gui_lmmp.py

Last active April 5, 2025 13:09
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save kubrick06010/70be9ecfd459195719ea6f7e9b85ab37 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save kubrick06010/70be9ecfd459195719ea6f7e9b85ab37 to your computer and use it in GitHub Desktop.
Download ZIP
Script para calcular la explosión de cantidades partiendo del BOM plano, recorriendo un árbol recursivo para agrupar todos los niveles de cada referencia final
Raw
gui_lmmp.py
# Interfaz gráfica del Script para realizar la explosión de la lista de materiales multinivel
import pandas as pd
import tkinter as tk
from tkinter import filedialog, messagebox
from tkinter import ttk
from collections import defaultdict
class BOMApp:
def __init__(self, root):
self.root = root
self.root.title("BOM Multinivel con Scrap")
self.root.geometry("800x600")
self.create_widgets()
def create_widgets(self):
# Cargar archivo
self.load_button = tk.Button(self.root, text="Cargar archivo SAP BI", command=self.load_file)
self.load_button.pack(pady=10)
# Tabla para mostrar los datos procesados
self.tree = ttk.Treeview(self.root, columns=("Nivel", "Final Ref SAG", "Material", "Material description",
"Component1", "Component Description1", "Quantity1", "Unidades1",
"Scrap1", "COCE1", "Comp HR1"), show="headings")
self.tree.pack(pady=20, fill="both", expand=True)
# Botón de exportar
self.export_button = tk.Button(self.root, text="Exportar archivo", command=self.export_file)
self.export_button.pack(pady=10)
def load_file(self):
# Abrir el diálogo para seleccionar archivo
file_path = filedialog.askopenfilename(filetypes=[("Excel Files", "*.xlsx")])
if not file_path:
return
try:
# Procesar el archivo
df = pd.read_excel(file_path, sheet_name="SAP BI")
df = df[["Material", "Material description", "Component", "Component Description",
"Component quantity", "base quantity", "Component scrap", "Unit of measure"]].dropna(subset=["Material", "Component"])
# Convertir columnas a tipo adecuado
df["Component quantity"] = pd.to_numeric(df["Component quantity"], errors="coerce")
df["base quantity"] = pd.to_numeric(df["base quantity"], errors="coerce")
df["Component scrap"] = pd.to_numeric(df["Component scrap"], errors="coerce").fillna(0)
# Construcción del diccionario BOM
bom_dict = defaultdict(list)
for _, row in df.iterrows():
parent = str(row["Material"]).strip()
child = str(row["Component"]).strip()
qty_per_unit = row["Component quantity"] / row["base quantity"] if row["base quantity"] else 1
scrap = row["Component scrap"]
bom_dict[parent].append({
"child": child,
"qty": qty_per_unit,
"scrap": scrap,
"uom": row["Unit of measure"],
"desc": row["Component Description"],
"parent_desc": row["Material description"]
})
# Expansión recursiva con scrap
self.results = []
def expand_bom(material, final_ref, nivel, parent_material, acc_qty):
if material not in bom_dict:
return
for item in bom_dict[material]:
total_qty = acc_qty * item["qty"] * (1 + item["scrap"] / 100)
self.results.append({
"Nivel": nivel,
"Final Ref SAG": final_ref,
"Material": final_ref,
"Material description": bom_dict[final_ref][0]["parent_desc"] if final_ref in bom_dict else "",
"Component1": item["child"],
"Component Description1": item["desc"],
"Quantity1": total_qty,
"Unidades1": item["uom"],
"Scrap1": item["scrap"],
"COCE1": "",
"Comp HR1": parent_material
})
expand_bom(item["child"], final_ref, nivel + 1, item["child"], total_qty)
all_parents = set(df["Material"].astype(str))
all_children = set(df["Component"].astype(str))
root_materials = all_parents - all_children
# Ejecutar la expansión para cada material raíz
for root in root_materials:
expand_bom(root, root, 1, root, 10000)
# Actualizar la tabla con los resultados
self.update_table(self.results)
except Exception as e:
messagebox.showerror("Error", f"No se pudo procesar el archivo. Error: {str(e)}")
def update_table(self, results):
# Limpiar la tabla
for item in self.tree.get_children():
self.tree.delete(item)
# Insertar las filas procesadas
for row in results:
self.tree.insert("", "end", values=(row["Nivel"], row["Final Ref SAG"], row["Material"],
row["Material description"], row["Component1"],
row["Component Description1"], row["Quantity1"],
row["Unidades1"], row["Scrap1"], row["COCE1"],
row["Comp HR1"]))
def export_file(self):
# Guardar el archivo procesado
file_path = filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel Files", "*.xlsx")])
if file_path:
try:
# Verificar si hay resultados para exportar
if not hasattr(self, 'results') or not self.results:
messagebox.showerror("Error", "No hay datos para exportar.")
return
# Convertir la lista de resultados a un DataFrame
results_df = pd.DataFrame(self.results)
# Guardar el DataFrame a Excel
results_df.to_excel(file_path, index=False)
messagebox.showinfo("Éxito", "Archivo exportado correctamente")
except Exception as e:
messagebox.showerror("Error", f"No se pudo exportar el archivo. Error: {str(e)}")
# Crear la aplicación
root = tk.Tk()
app = BOMApp(root)
root.mainloop()
Raw
lmmp.py
import pandas as pd
from collections import defaultdict
# Ruta del archivo
file_path = "SAP BI.xlsx" # Asegúrate de que el archivo esté en el mismo directorio o cambia la ruta
# Lote de producción
lote_base = 10000
# Leer datos
df = pd.read_excel(file_path, sheet_name="SAP BI")
# Limpieza de datos
df = df[[
"Material", "Material description", "Component", "Component Description",
"Component quantity", "base quantity", "Component scrap", "Unit of measure"
]].dropna(subset=["Material", "Component"]).copy()
df["Component quantity"] = pd.to_numeric(df["Component quantity"], errors="coerce")
df["base quantity"] = pd.to_numeric(df["base quantity"], errors="coerce")
df["Component scrap"] = pd.to_numeric(df["Component scrap"], errors="coerce").fillna(0)
# Diccionario padre-hijo
bom_dict = defaultdict(list)
for _, row in df.iterrows():
parent = str(row["Material"]).strip()
child = str(row["Component"]).strip()
qty_per_unit = row["Component quantity"] / row["base quantity"] if row["base quantity"] else 1
scrap = row["Component scrap"]
bom_dict[parent].append({
"child": child,
"qty": qty_per_unit,
"scrap": scrap,
"uom": row["Unit of measure"],
"desc": row["Component Description"],
"parent_desc": row["Material description"]
})
# Expansión recursiva
results = []
def expand_bom(material, final_ref, nivel, parent_material, acc_qty):
if material not in bom_dict:
return
for item in bom_dict[material]:
total_qty = acc_qty * item["qty"] * (1 + item["scrap"] / 100)
results.append({
"Nivel": nivel,
"Final Ref SAG": final_ref,
"Material": final_ref,
"Material description": bom_dict[final_ref][0]["parent_desc"] if final_ref in bom_dict else "",
"Component1": item["child"],
"Component Description1": item["desc"],
"Quantity1": total_qty,
"Unidades1": item["uom"],
"Scrap1": item["scrap"],
"COCE1": "",
"Comp HR1": parent_material
})
expand_bom(item["child"], final_ref, nivel + 1, item["child"], total_qty)
# Detectar raíces
all_parents = set(df["Material"].astype(str))
all_children = set(df["Component"].astype(str))
root_materials = all_parents - all_children
# Ejecutar con lote base
for root in root_materials:
expand_bom(root, root, 1, root, lote_base)
# Guardar en Excel
df_result = pd.DataFrame(results)
df_result.to_excel("BOM_multinivel_con_scrap_lote10000.xlsx", index=False)
print("✅ Archivo generado: BOM_multinivel_con_scrap_lote10000.xlsx")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment