修改Python脚本：正确导入树形结构文本数据至Excel并设置父节点路由

需求说明：

• 从文本文件读取树形结构数据并写入Excel
• 遍历方向为从叶子节点到根节点
• 文本中每个树以空行分隔，节点信息用逗号分隔
• 子节点的Routing列填写父节点的Part code，根节点Routing为空

现有脚本错误示例：Part code为TT001HU的节点Routing被错误设置为1002011，正确值应为C002L0

文本文件示例 #

100201F, Part name, Part description, 1, False
├── C002L0, Part name, Part description, 1, False
    ├── V3fd1, Part name, Part description ,  1, True
    |-─ 1002011, Part name, Part description:, 1,  True
    ├── TT001HU, Part name, Part description, 1, True


134545F,Part name, Part description, 1, False
├── ...
...

现有错误脚本 #

import pandas as pd
import re


# define the column names for the excel file
column_names = ["Routing", "Part code", "Part name", "Part description", "Batch size","Is material"]

# read the trees from the text file
with open("trees.txt","r",encoding="utf-8") as file:
    trees = file.read().strip().split("\n\n")

# create an empty dataframe to store the results
df = pd.DataFrame(columns=column_names)

# loop through each tree
for tree in trees:
    # split the tree into nodes and reverse the order
    nodes = tree.strip().split("\n")[::-1]

    # loop through each node
    for i, node in enumerate(nodes):
        # split the node into its data
        data = node.strip().split(";")

        # add the data to a dictionary
        row_data = {
            "Routing": re.sub(r'[^a-zA-Z0-9]', '', data[0]) if i != len(nodes)-1 else "",
            "Part code": re.sub(r'[^a-zA-Z0-9]', '', data[0]),
            "Part name": data[1],
            "Part description": data[2],
            "Batch size": data[3],
            "Is material": data[4]
        }

        # add the child node to the "Routing" column if the current node is not the root
        if i < len(nodes)-1:
            child_data = nodes[i+1].strip().split(";")
            row_data["Routing"] = re.sub(r'[^a-zA-Z0-9]', '', child_data[0])

        # add the row to the dataframe
        df = df.append(row_data, ignore_index=True)

# write the dataframe to an Excel file
df.to_excel("trees.xlsx", index=False)

预期Excel格式 #

Parent code    Part name    Part description    Batch size    Routing    Is material
TT001HU        Part name    Part description    1             C002L0     True
1002011        Part name    Part description    1             C002L0     True
V3fd1          Part name    Part description    1             C002L0     True
C002L0         Part name    Part description    1             100201F    False
100201F        Part name    Part description    1                        False
...

修改后的正确脚本 #

import pandas as pd
import re

# 定义Excel列名
column_names = ["Routing", "Part code", "Part name", "Part description", "Batch size", "Is material"]

# 读取文本文件中的树形数据，按空行分割不同树
with open("trees.txt", "r", encoding="utf-8") as file:
    trees = file.read().strip().split("\n\n")

# 创建空DataFrame存储结果
df = pd.DataFrame(columns=column_names)

# 处理每一棵树
for tree in trees:
    nodes = tree.strip().split("\n")
    node_info_list = []
    
    # 先解析每个节点的缩进层级和核心数据
    for node in nodes:
        # 计算缩进量：统计开头的空格数，判断层级
        indent = len(node) - len(node.lstrip())
        # 移除树形符号（├── |-─ 等）和前后空格，提取节点数据
        cleaned_node = re.sub(r'^[├|-]+─\s*', '', node).strip()
        # 按逗号分割数据，处理可能的空格
        data = [item.strip() for item in cleaned_node.split(",")]
        # 提取Part code（第一个字段）
        part_code = re.sub(r'[^a-zA-Z0-9]', '', data[0])
        # 保存节点信息：缩进层级、Part code、完整数据
        node_info_list.append({
            "indent": indent,
            "part_code": part_code,
            "data": data
        })
    
    # 遍历节点，为每个节点找到父节点
    # 用栈来维护当前层级的父节点链
    parent_stack = []
    parent_map = {}
    for idx, node_info in enumerate(node_info_list):
        current_indent = node_info["indent"]
        current_code = node_info["part_code"]
        
        # 弹出栈中缩进大于等于当前缩进的节点，找到父节点
        while parent_stack and parent_stack[-1]["indent"] >= current_indent:
            parent_stack.pop()
        
        # 根节点没有父节点
        if parent_stack:
            parent_map[current_code] = parent_stack[-1]["part_code"]
        else:
            parent_map[current_code] = ""
        
        # 将当前节点压入栈，作为后续子节点的可能父节点
        parent_stack.append(node_info)
    
    # 按叶子到根的顺序遍历节点（反转节点列表）
    for node_info in reversed(node_info_list):
        part_code = node_info["part_code"]
        data = node_info["data"]
        
        row_data = {
            "Routing": parent_map[part_code],
            "Part code": part_code,
            "Part name": data[1],
            "Part description": data[2],
            "Batch size": data[3],
            "Is material": data[4]
        }
        df = pd.concat([df, pd.DataFrame([row_data])], ignore_index=True)

# 写入Excel文件
df.to_excel("trees.xlsx", index=False)

关键修改说明 #

• 修复节点分割错误：原脚本误用分号;分割节点数据，实际文本为逗号,分割，已修正为按逗号分割并去除字段前后空格
• 新增缩进层级解析：通过统计节点开头的空格数判断层级，配合栈结构正确识别每个节点的父节点，解决原脚本错误关联父节点的问题
• 构建父节点映射表：遍历节点时用栈维护父节点链，确保每个子节点能匹配到正确的父节点Part code
• 实现正确遍历顺序：反转节点列表实现从叶子到根的写入逻辑，通过父节点映射表自动填充Routing列，根节点自动为空

内容的提问来源于stack exchange，提问作者David