主要原材料与标准BOM计算理论耗用量的匹配性

#!/usr/bin/python3
# -*- coding:UTF-8 -*-
# Author: nigo
import pandas as pd

class Node:
    def __init__(self, name, amount=1):
        self.name = name # 节点名称
        self.amount = amount # 数值
        self.father = None # 父节点
        self.children = [] # 子节点

    def add_child(self, obj):
        self.children.append(obj) # 添加子节点

    def add_father(self, obj):
        self.father = obj # 添加父节点

    def terminal(self,name=True):
        """末级节点"""
        terminal_list = [] # 创建末级节点列表
        for node in self.children: # 遍历子节节点
            if node.children: # 若子节点有子节点
                terminal_list += node.terminal(name) # 递归函数
            else:
                if name:
                    terminal_list.append([name,node.name,node.amount]) # 添加到末级节点列表
                else:
                    terminal_list.append([node.name,node.amount]) # 添加到末级节点列表
        return terminal_list

class Tree:
    def __init__(self, df,tops=[]):
        self.trees = []
        self.init(df,tops) # 初始化

    def init(self, df,tops):
        father_list = df.iloc[:, 0] # df第一列为父节点名称
        child_list = df.iloc[:, 1] # df第二列为子节点名称
        if not tops:
        # 父节点中未出现在子节点的为顶级节点
            tops = [i for i in father_list if i not in list(child_list)]
        # 指定了父节点就用指定的。
        tops = list(set(tops))
        relation = {} # 层级对应关系
        for index, row in df.iterrows(): # 循环dataframe每一行
            father = row[0] # 父节点名称
            child = row[1] # 子节点名称
            amount = row[2] # 子节点值
            if father in relation.keys():
                children = relation[father]
                children.append({'name':child,'amount':amount})
                relation[father] = children
            else:
                relation[father] = [{'name':child,'amount':amount}]
        self.relation = relation
        for top in tops:
            if top in self.relation.keys():
                self.trees.append(self.make_tree(top,1))

    def make_tree(self, top, amount):
        """根据顶级节点建立树状关系"""
        father_node = Node(top,amount) # 创建父节点
        for child in self.relation[top]: # 遍历父节点对应的所有下一层级
            if child['name'] in self.relation.keys(): # 如果child有下一层级
                child_node = self.make_tree(child['name'],child['amount']*amount) # 调用递归函数
                child_node.add_father(father_node) # 给子节点添加父节点
                father_node.add_child(child_node) # 给父节点添加子节点
            else: # 如果child没有下一层级
                child_node = Node(child['name'],child['amount']*amount) # 创建子节点
                child_node.add_father(father_node) # 给子节点添加父节点
                father_node.add_child(child_node) # 给父节点添加子节点
        return father_node


if __name__ == "__main__":
    df = pd.read_excel('BOM表的路径.xlsx',converters={'父组件':str,'组件':str})
    # 指定生成哪些物料就用下面这两行 ，自动生成最终产成品就注释这两行
    df_product = pd.read_excel('指定物料号的文件（可以是生产订单）.xlsx',converters={ '物料号':str})
    tops = df_product['物料号'].to_list()
    # 指定生成哪些物料就用上面这两行 ，自动生成最终产成品就注释这两行
    code2name = {} # 物料编码与物料名称的映射
    for index,row in df.iterrows():
        code2name[row['父组件']] = row['父物料描述']
        code2name[row['组件']] = row['物料描述']
    df = df.loc[:,['父组件','组件','净数量']] # 截取三列
    # 实例化Tree类
    tree = Tree(df,tops) # 指定生成哪些物料就用 Tree(df,tops) ，自动生成最终产成品就用 Tree(df)
    df_result = pd.DataFrame()
    for top in tree.trees: # 循环每一个根节点
        # 求每个根节点的末级节点
        df_tmp = pd.DataFrame(top.terminal(top.name),columns=['父组件','组件','数量'])
        # 合并dataframe
        df_result = pd.concat([df_result,df_tmp])
    # 根据物料代码添加物料名称
    df_result['父组件名称'] = ''
    df_result['组件名称'] = ''
    df_result = df_result.reset_index(drop=True)
    for index,row in df_result.iterrows():
        if row['父组件'] in code2name.keys():
            df_result.loc[index,'父组件名称'] = code2name[row['父组件']]
        df_result.loc[index,'组件名称'] = code2name[row['组件']]
    df_result.to_excel('你需要生成文件的路径.xlsx',index=False)
    print(df_result)