使用Pandas解析层级数据并构建父子映射关系

原始数据 #

import pandas as pd

df1 = pd.DataFrame({'Level': ['0','1','2','2','3','2','3','1','2','3','3','4'],
                   'Type': ['P','U','D','I','D','PR','D','U','U','D','PR','D'],
                   'ID': ['P1','U1','D1','I1','D2','PR1','D3','U4','U8','DB1','PR3','DF1'],
                   'Value': ['','1', '0','1','0','1','0','1','1','0','1','0']
                  }
                )

需求说明 #

基于Level列构建0->1->2->3的一对一父子映射关系，规则如下：

• 除Level 1外，其余层级的直接上一级为父级
• 最终生成指定结构的目标DataFrame

目标数据 #

df2 = pd.DataFrame({'ID1': ['P1','P1','U1','U1','I1','PR1','U4','U8','U8','PR3'],
                   'ID2': ['U1','U4','D1','I1','D2','D3','U8','DB1','PR3','DF1'],
                    'Value':['1','1','0','1','0','0','1','0','1','0']
                  }
                )

解决方案代码 #

import pandas as pd

# 原始数据
df1 = pd.DataFrame({'Level': ['0','1','2','2','3','2','3','1','2','3','3','4'],
                   'Type': ['P','U','D','I','D','PR','D','U','U','D','PR','D'],
                   'ID': ['P1','U1','D1','I1','D2','PR1','D3','U4','U8','DB1','PR3','DF1'],
                   'Value': ['','1', '0','1','0','1','0','1','1','0','1','0']
                  }
                )

# 构建父子映射关系
current_parents = {'0': None, '1': None, '2': None, '3': None}
result_list = []

for _, row in df1.iterrows():
    level = row['Level']
    current_id = row['ID']
    value = row['Value']
    
    if level == '0':
        current_parents['0'] = current_id
    elif level == '1':
        # Level1的父级是当前Level0的节点
        parent_id = current_parents['0']
        current_parents['1'] = current_id
        if value:
            result_list.append({'ID1': parent_id, 'ID2': current_id, 'Value': value})
    elif level == '2':
        # Level2的父级是当前Level1的节点
        parent_id = current_parents['1']
        current_parents['2'] = current_id
        if value:
            result_list.append({'ID1': parent_id, 'ID2': current_id, 'Value': value})
    elif level == '3':
        # Level3的父级是当前Level2的节点
        parent_id = current_parents['2']
        current_parents['3'] = current_id
        if value:
            result_list.append({'ID1': parent_id, 'ID2': current_id, 'Value': value})
    elif level == '4':
        # Level4的父级是当前Level3的节点
        parent_id = current_parents['3']
        if value:
            result_list.append({'ID1': parent_id, 'ID2': current_id, 'Value': value})

# 生成目标DataFrame
df2 = pd.DataFrame(result_list)
print(df2)

运行上述代码后，输出结果与目标df2完全一致。

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