按权重为Dataframe填充联系电话时后续填充失效问题求助

需求说明 #

以account为主键，按家庭电话>办公电话1>手机>办公电话2的权重顺序，为dataDF的Phone列填充联系电话：当高权重号码为空时，依次查找下一级DataFrame中的号码。

原代码 #

import pandas as pd

def addContact(dataDF, emailDF, homeDF, office1DF, cellDF, office2DF) :
    
    dataDF = pd.merge(dataDF, emailDF, on = "account", how = "left")
    
    dataDF = pd.merge(dataDF, homeDF, on = "account", how = "left")

    for index, row in dataDF.iterrows() :
        if(pd.isnull(row['Phone'])) :
            find = office1DF[office1DF['account'] == row['account']]
            if (pd.isnull(row['Phone'])) :
                dataDF.at[index, 'Phone'] = find['Phone']
            else :
                find = cellDF[cellDF['account'] == row['account']]
                if (pd.isnull(row['Phone'])) :
                    dataDF.at[index, 'Phone'] = find['Phone']
                else :
                    find = office2DF[office2DF['account'] == row['account']]
                    if (pd.isnull(row['Phone'])) :
                        dataDF.at[index, 'Phone'] = find['Phone']

    return dataDF

当前问题 #

大部分家庭电话可正常填充，但后续的办公电话1、手机、办公电话2填充失效，出现类似1193 NaN Name: Phone, dtype: object的异常值。

问题分析 #

1. 逻辑判断完全颠倒：原代码嵌套的else分支逻辑错误，应该是当前权重级别的号码仍为空时才尝试下一级，而非进入else分支执行。
2. Series直接赋值异常：find['Phone']返回的是Pandas Series对象，直接赋值会把整个Series的结构（包括索引、类型信息）写入单元格，导致出现非预期的异常值。
3. 未处理无匹配account的情况：当子DF中没有对应account时，find为空，直接取find['Phone']会返回空Series，赋值后触发异常。
4. 循环遍历效率低下：iterrows()逐行遍历的方式不符合Pandas矢量化操作的理念，数据量大时性能极差。

解决方案 #

方案1：修复原循环逻辑 #

import pandas as pd

def addContact(dataDF, emailDF, homeDF, office1DF, cellDF, office2DF):
    # 合并email和家庭电话数据
    dataDF = pd.merge(dataDF, emailDF, on="account", how="left")
    dataDF = pd.merge(dataDF, homeDF, on="account", how="left")
    
    # 确保Phone列存在（兼容初始无Phone列的场景）
    if 'Phone' not in dataDF.columns:
        dataDF['Phone'] = pd.NA
    
    for index, row in dataDF.iterrows():
        # 跳过已填充有效号码的行
        if pd.notna(row['Phone']):
            continue
        
        # 按权重依次查找号码
        # 1. 尝试办公电话1
        office1_match = office1DF[office1DF['account'] == row['account']]
        if not office1_match.empty and pd.notna(office1_match.iloc[0]['Phone']):
            dataDF.at[index, 'Phone'] = office1_match.iloc[0]['Phone']
            continue
        
        # 2. 尝试手机
        cell_match = cellDF[cellDF['account'] == row['account']]
        if not cell_match.empty and pd.notna(cell_match.iloc[0]['Phone']):
            dataDF.at[index, 'Phone'] = cell_match.iloc[0]['Phone']
            continue
        
        # 3. 尝试办公电话2
        office2_match = office2DF[office2DF['account'] == row['account']]
        if not office2_match.empty and pd.notna(office2_match.iloc[0]['Phone']):
            dataDF.at[index, 'Phone'] = office2_match.iloc[0]['Phone']
    
    return dataDF

方案2：矢量化操作（更高效推荐） #

利用Pandas的merge+combine_first方法完全避免循环，性能更优：

import pandas as pd

def addContact(dataDF, emailDF, homeDF, office1DF, cellDF, office2DF):
    # 合并email数据
    dataDF = pd.merge(dataDF, emailDF, on="account", how="left")
    
    # 清理各电话DF，仅保留account和Phone列并重命名避免合并冲突
    home_clean = homeDF[['account', 'Phone']].rename(columns={'Phone': 'home_phone'})
    office1_clean = office1DF[['account', 'Phone']].rename(columns={'Phone': 'office1_phone'})
    cell_clean = cellDF[['account', 'Phone']].rename(columns={'Phone': 'cell_phone'})
    office2_clean = office2DF[['account', 'Phone']].rename(columns={'Phone': 'office2_phone'})
    
    # 依次合并所有电话列
    dataDF = dataDF.merge(home_clean, on="account", how="left")
    dataDF = dataDF.merge(office1_clean, on="account", how="left")
    dataDF = dataDF.merge(cell_clean, on="account", how="left")
    dataDF = dataDF.merge(office2_clean, on="account", how="left")
    
    # 按权重顺序填充Phone列：优先用家庭电话，为空则依次降级
    dataDF['Phone'] = dataDF['home_phone'].combine_first(
        dataDF['office1_phone']
    ).combine_first(
        dataDF['cell_phone']
    ).combine_first(
        dataDF['office2_phone']
    )
    
    # 清理临时生成的权重列（可选）
    dataDF = dataDF.drop(columns=['home_phone', 'office1_phone', 'cell_phone', 'office2_phone'])
    
    return dataDF

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