Pandas父子关系数据映射异常：两种方法均存在问题

问题说明 #

处理Pandas父子关系数据时，部分数据的父子映射无法正确实现：

• 尝试ffill、fillna方法无效
• 使用merge方法时，除string1和string42的parent_string外，其余结果正确
• 使用map结合set_index的方法时，出现无效索引错误
• 需要得到指定格式的输出结果

原代码 #

import pandas as pd 

df = pd.DataFrame(
        {
            "child_string": ["string42","string23","string23","string54","string28","string86","string15","string1"], 
            "child": [None, 8675, 8675, 8676, 2048, 5442, 1942, 3185], 
            "parent": [None, 2048, 2048, 2048, 1942, 1942, 3185, None],
            "interesting": ["some_unique_field1", "some_unique_field2", "some_unique_field3", "some_unique_field4", "some_unique_field5", "some_unique_field6", "some_unique_field7", "some_unique_field8"]
        }
)

# 除string1和string42的parent_string外结果正确
print(df.merge(
    df[['child', 'child_string']].rename(columns={"child":"parent", "child_string": "parent_string"}), 
    on='parent', 
    how='left'
))

# 报无效索引错误
df['parent_string'] = df['parent'].map(df.set_index('child').child_string)
print(df)

问题分析 #

1. merge方法的问题：
   原merge使用的右表包含了child为None的行（对应string42），当主表中parent为None时（string1和string42），会错误匹配到这一行，导致string1的parent_string显示为string42，而非预期的NaN。

2. map方法的问题：
   原df.set_index('child')后，索引存在重复值（8675出现两次），使用map时会返回Series而非标量，触发无效索引错误。

修正方案 #

方案一：修正merge方法 #

过滤右表中child为None的行，避免parent为None时的错误匹配：

import pandas as pd 

df = pd.DataFrame(
        {
            "child_string": ["string42","string23","string23","string54","string28","string86","string15","string1"], 
            "child": [None, 8675, 8675, 8676, 2048, 5442, 1942, 3185], 
            "parent": [None, 2048, 2048, 2048, 1942, 1942, 3185, None],
            "interesting": ["some_unique_field1", "some_unique_field2", "some_unique_field3", "some_unique_field4", "some_unique_field5", "some_unique_field6", "some_unique_field7", "some_unique_field8"]
        }
)

# 过滤child为None的行，避免错误匹配
result = df.merge(
    df[df['child'].notna()][['child', 'child_string']].rename(columns={"child":"parent", "child_string": "parent_string"}), 
    on='parent', 
    how='left'
)
print(result)

方案二：修正map方法 #

先对child列去重，确保索引唯一后再进行映射：

import pandas as pd 

df = pd.DataFrame(
        {
            "child_string": ["string42","string23","string23","string54","string28","string86","string15","string1"], 
            "child": [None, 8675, 8675, 8676, 2048, 5442, 1942, 3185], 
            "parent": [None, 2048, 2048, 2048, 1942, 1942, 3185, None],
            "interesting": ["some_unique_field1", "some_unique_field2", "some_unique_field3", "some_unique_field4", "some_unique_field5", "some_unique_field6", "some_unique_field7", "some_unique_field8"]
        }
)

# 去重后再设置索引，避免重复索引导致的错误
df['parent_string'] = df['parent'].map(df.drop_duplicates(subset='child').set_index('child')['child_string'])
print(df)

预期输出 #

child_string   child   parent          interesting parent_string
0     string42     NaN     NaN  some_unique_field1           NaN
1     string23  8675.0  2048.0  some_unique_field2      string28
2     string23  8675.0  2048.0  some_unique_field3      string28
3     string54  8676.0  2048.0  some_unique_field4      string28
4     string28  2048.0  1942.0  some_unique_field5      string15
5     string86  5442.0  1942.0  some_unique_field6      string15
6     string15  1942.0  3185.0  some_unique_field7       string1
7      string1  3185.0     NaN  some_unique_field8           NaN

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