python查找excel中内容_python-递归Excel文件以从树结构中查找顶级项目

我正在尝试对数据集进行递归以找到最高级别的项目,即没有父项的项目.

结构如下：

╔════════════╦════════════╗

║ Item ║ Material ║

╠════════════╬════════════╣

║ 2094-00003 ║ MHY00007 ║

║ 2105-0001 ║ 2105-0002 ║

║ 2105-0002 ║ 2105-1000 ║

║ 2105-1000 ║ 2105-1003 ║

║ 2105-1003 ║ 7547-122 ║

║ 7932-00001 ║ 7932-00015 ║

║ 7932-00002 ║ 7932-00015 ║

║ 7932-00010 ║ MHY00007 ║

║ 7932-00015 ║ 7932-05000 ║

║ 7932-05000 ║ MHY00007 ║

╚════════════╩════════════╝

因此,例如,如果我选择7547-122,则该函数将返回2105-0001.因此,函数向上递归地跟随树,7547-122-> 2105-1003-> 2105-1000-> …-> 2105-0001.

如我从MHY00007案例所见,运行代码时,我只能使它返回一个顶层.有时,有多个顶层.如何返回给定材料具有的所有顶层的列表？

我的代码：

import pandas as pd

class BillOfMaterials:

def __init__(self, bom_excel_path):

self.df = pd.read_excel(bom_excel_path)

self.df = self.df[['Item', 'Material']]

def find_parents(self, part_number):

material_parent_search = self.df[self.df.Material == part_number]

parents = list(set(material_parent_search['Item']))

return parents

def find_top_levels(self, parents):

top_levels = self.__ancestor_finder_([parents])

print(f'{parents} top level is {top_levels}')

return {parents: top_levels}

def __ancestor_finder_(self, list_of_items):

for ancestor in list_of_items:

print(f'Searching for ancestors of {ancestor}')

ancestors = self.find_parents(ancestor)

print(f'{ancestor} has ancestor(s) {ancestors}')

if not ancestors:

return ancestor

else:

highest_level = self.__ancestor_finder_(ancestors)

return highest_level

BOM = BillOfMaterials(bom_excel_path="Path/To/Excel/File/BOM.xlsx")

ItemsToSearch = ['7547-122', 'MHY00007']

top_levels = []

for item in ItemsToSearch:

top_levels.append(BOM.find_top_levels(item))

解决方法:

是的,您可以递归执行此操作,例如：

import pandas as pd

class BillOfMaterials:

def __init__(self, bom_excel_path):

self.df = pd.read_excel(bom_excel_path)

self.df = self.df[['Item', 'Material']]

def find_parents(self, part_number):

return list(set(self.df[self.df.Material == part_number]['Item']))

def find_top_levels(self, item):

parents = self.find_parents(item)

if not parents:

# there are no parent items => this item is a leaf

return [item]

else:

# there are parent items => recursively find grandparents

grandparents = []

for parent in parents:

grandparents = grandparents + self.find_top_levels(parent)

return grandparents

if __name__ == '__main__':

BOM = BillOfMaterials(bom_excel_path="testdata.xlsx")

ItemsToSearch = ['7547-122', 'MHY00007']

for i in ItemsToSearch:

print('')

print('The top levels of ' + i + ' are: ')

print(BOM.find_top_levels(i))

注意self.find_top_levels(parent)的递归调用.

这将给出输出

The top levels of 7547-122 are:

['2105-0001']

The top levels of MHY00007 are:

['2094-00003', '7932-00001', '7932-00002', '7932-00010']

标签：pandas,python-3-x,tree,python,recursion

来源： https://codeday.me/bug/20191109/2012259.html