如何将带有父子层级的dataframe转换为父级名称分独立列存储的dataframe

你可以用pandas通过层级匹配实现转换，以下是两种可直接运行的实现方式：

方法1：递归匹配（适合小数据量，逻辑灵活） #

思路：先标记每个节点的所属层级，从最底层的Taluk节点向上递归匹配所有父级节点信息，拼接为宽表。

import pandas as pd

# 预先构建编码-名称、编码-父节点的映射字典
name_mapping = df.set_index('Code')['Name'].to_dict()
parent_mapping = df.set_index('Code')['Parent'].to_dict()

# 标记每个节点的层级：1=国家 2=省/州 3=区县 4=乡镇/街道
def calc_level(code):
    level = 1
    current_parent = parent_mapping.get(code)
    while pd.notna(current_parent) and current_parent.strip() != '':
        level += 1
        current_parent = parent_mapping.get(current_parent)
    return level

df['level'] = df['Code'].apply(calc_level)

# 提取最底层Taluk节点作为基准，向上拉取所有层级信息
def get_full_hierarchy(taluk_code):
    info = {}
    current = taluk_code
    for col_prefix in ['Taluk', 'District', 'State', 'Country']:
        info[f'{col_prefix}_Name'] = name_mapping.get(current)
        info[f'{col_prefix}_Code'] = current
        current = parent_mapping.get(current)
    return pd.Series(info)

result = df[df['level'] == 4]['Code'].apply(get_full_hierarchy)
# 调整列顺序为目标格式
result = result[['Country_Name', 'Country_Code', 'State_Name', 'State_Code', 'District_Name', 'District_Code', 'Taluk_Name', 'Taluk_Code']]

方法2：多表合并（适合大数据量，性能更高） #

思路：先拆分出各层级的子表，再通过父编码逐层关联得到全量层级信息。

import pandas as pd

# 同上先计算每个节点的层级
name_mapping = df.set_index('Code')['Name'].to_dict()
parent_mapping = df.set_index('Code')['Parent'].to_dict()
def calc_level(code):
    level = 1
    current_parent = parent_mapping.get(code)
    while pd.notna(current_parent) and current_parent.strip() != '':
        level += 1
        current_parent = parent_mapping.get(current_parent)
    return level
df['level'] = df['Code'].apply(calc_level)

# 拆分各层级子表
country = df[df['level'] == 1][['Code', 'Name']].rename(columns={'Code':'Country_Code', 'Name':'Country_Name'})
state = df[df['level'] == 2][['Code', 'Name', 'Parent']].rename(columns={'Code':'State_Code', 'Name':'State_Name', 'Parent':'Country_Code'})
district = df[df['level'] == 3][['Code', 'Name', 'Parent']].rename(columns={'Code':'District_Code', 'Name':'District_Name', 'Parent':'State_Code'})
taluk = df[df['level'] == 4][['Code', 'Name', 'Parent']].rename(columns={'Code':'Taluk_Code', 'Name':'Taluk_Name', 'Parent':'District_Code'})

# 逐层关联得到结果
result = taluk.merge(district, on='District_Code', how='left')\
              .merge(state, on='State_Code', how='left')\
              .merge(country, on='Country_Code', how='left')\
              [['Country_Name', 'Country_Code', 'State_Name', 'State_Code', 'District_Name', 'District_Code', 'Taluk_Name', 'Taluk_Code']]

如果你的数据存在层级不完整的情况，调整calc_level函数的判断逻辑和层级字段即可适配。

内容的提问来源于stack exchange，提问作者question.it