基于多条件创建/填充DataFrame新列：合并收货与账单地址

需求 #

我有一个包含收货（Ship to）和账单（Bill to）两类地址的DataFrame，需要按以下逻辑合并出可用地址：

1. 若收货邮编与账单邮编相等，从两者中选已填充的地址行1（默认内容一致）
2. 若邮编不同，优先使用收货地址行1；若收货地址行1为空，则用账单地址行1

示例数据 #

import pandas as pd
import numpy as np

# 构造示例数据
customers_staging = pd.DataFrame({
    'CID': ['CUST1', 'CUST2', 'CUST3'],
    'Ship Line 1': [np.nan, np.nan, 'Cust3 Addres Line 1'],
    'Ship Postcode': [10001, 39458, 20002],
    'Bill Line 1': ['Cust1 Address Line 1', 'Cust2 Address Line 1', np.nan],
    'Bill Postcode': [10001.0, np.nan, 20004.0]
})

你尝试的代码问题 #

你写的函数存在语法错误（赋值语句不能作为返回值），且未覆盖邮编为空的场景，也没实现地址行1的选取逻辑：

def addresschoice(row):
    if row['Ship Postcode'] ==  row['Bill Postcode']:
        #identify which address will be chosen
        return row['Address Selected'] = 'Ship To'  # 语法错误：赋值不能作为返回值

    else:
        return row['Address Selected'] = 'Bill To'

customers_staging.apply(addresschoice, axis=1)

正确实现方案 #

方案1：逐行处理（apply） #

适合逻辑复杂的场景，可读性强：

def merge_address(row):
    ship_post = row['Ship Postcode']
    bill_post = row['Bill Postcode']
    
    # 判断邮编是否相等（需排除NaN的情况，因为NaN != NaN）
    post_equal = pd.notna(ship_post) and pd.notna(bill_post) and (ship_post == bill_post)
    
    if post_equal:
        # 邮编相等时选非空的地址行1，优先收货地址
        if pd.notna(row['Ship Line 1']):
            return (row['Ship Line 1'], 'Ship To')
        else:
            return (row['Bill Line 1'], 'Bill To')
    else:
        # 邮编不同时，优先收货地址行1，空则用账单
        if pd.notna(row['Ship Line 1']):
            return (row['Ship Line 1'], 'Ship To')
        else:
            return (row['Bill Line 1'], 'Bill To')

# 应用函数并合并结果
merged_cols = customers_staging.apply(merge_address, axis=1, result_type='expand')
merged_cols.columns = ['Merged Address Line1', 'Address Selected']
customers_staging = pd.concat([customers_staging, merged_cols], axis=1)

方案2：向量运算（numpy.where） #

适合大数据量场景，运算效率更高：

# 标记邮编相等的行（排除NaN）
postcode_equal = (pd.notna(customers_staging['Ship Postcode']) & 
                  pd.notna(customers_staging['Bill Postcode']) & 
                  (customers_staging['Ship Postcode'] == customers_staging['Bill Postcode']))

# 生成合并地址行1
customers_staging['Merged Address Line1'] = np.where(
    postcode_equal,
    np.where(pd.notna(customers_staging['Ship Line 1']), 
             customers_staging['Ship Line 1'], 
             customers_staging['Bill Line 1']),
    np.where(pd.notna(customers_staging['Ship Line 1']), 
             customers_staging['Ship Line 1'], 
             customers_staging['Bill Line 1'])
)

# 生成地址选择类型
customers_staging['Address Selected'] = np.where(
    postcode_equal,
    np.where(pd.notna(customers_staging['Ship Line 1']), 'Ship To', 'Bill To'),
    np.where(pd.notna(customers_staging['Ship Line 1']), 'Ship To', 'Bill To')
)

最终结果 #

处理后的数据：

CID          Ship Line 1  Ship Postcode           Bill Line 1  Bill Postcode Merged Address Line1 Address Selected
0  CUST1                  NaN          10001  Cust1 Address Line 1        10001.0  Cust1 Address Line 1          Bill To
1  CUST2                  NaN          39458  Cust2 Address Line 1            NaN  Cust2 Address Line 1          Bill To
2  CUST3  Cust3 Addres Line 1          20002                   NaN        20004.0  Cust3 Addres Line 1           Ship To

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