如何高效比对两个DataFrame中的邮箱列并匹配关联记录？

我有两个数据集，各包含邮箱地址和记录ID。需要找出两个数据集中邮箱匹配的记录，提取双方的记录ID、匹配列名及对应值，整合成新数据集输出。目前用嵌套循环实现，但效率极低——当两个数据集各有15万条记录时，预计耗时数千小时。求优化建议。

原实现代码：

import pandas as pd

# Load the first CSV file (contact1_Contacts)
contacts_1_df = pd.read_csv("C:/temp/Contacts1.csv")

# Load the second CSV file (contact2_Contacts)
contacts_2_df = pd.read_csv("C:/temp/contacts2.CSV")


contacts_1_df.dropna(subset=['ContactId'], inplace=True)
contacts_2_df.dropna(subset=['ContactID'], inplace=True)

contacts_1_df.drop(['Full Name', 'Source Ref', 'Last Name', 'First Name', 'Birthdate', 'Mobile Phone', 'Date of Birth'], axis='columns', inplace=True)
contacts_2_df.drop(['Title', 'First Name','Middle Name', 'Surname', 'Maiden Name', 'Known As', 'Gender', 'DOB', 'Deceased?', 'Deceased Date', 'Phone Number', 'Nationality'], axis='columns', inplace=True)

#convert dataframes to lower
contacts_1_df = contacts_1_df.apply(lambda x: x.astype(str).str.lower())
contacts_2_df = contacts_2_df.apply(lambda x: x.astype(str).str.lower())

matches_df = pd.DataFrame(columns = ['Contact1ContactGUID','Contact1ContactEmailType','Contact1ContactEmailValue','Contact2ContactID','Contact2EmailType','Contact2EmailValue'])

contact1_record_count = 0
contact2_record_count = 0


#for each contact1 contact
for contacts_1_index, contact1_row in contacts_1_df[['Preferred Email', 'Company Email', 'Email Address 2', 'Email 3', 'ContactId']].iterrows():
    #variable for columnindex so i can get the column name
    contact1_colindex = 0

    for contact1_email_col_value in contact1_row:

        #increment the column index
        contact1_colindex = contact1_colindex + 1
      
        #dont test for nan values
        if(contact1_email_col_value != 'nan'):

        
            for contact2_index, contact2_row in contacts_2_df[['Email'  ,'Email_1'  ,'Email_2'  ,'Email_3'  ,'Email_4','ContactID']].iterrows():

                #variable to hold the col index so i can get the column name
                contact2_colindex = 0

                for contact2_email in contact2_row:
 
                    #dont test for nan values
                    if(contact2_email != 'nan'):

                        if(contact2_email == contact1_email_col_value):
                            #print('****************MATCH****************')
                            match_row = {'Contact1ContactGUID': contact1_row[4], 'Contact1ContactEmailType': contact1_row.index[contact1_colindex], 'Contact1ContactEmailValue': contact1_email_col_value, 'Contact2ContactID': contact2_row[5], 'Contact2EmailType': contact2_row.index[contact2_colindex], 'Contact2EmailValue': contact2_email }
                            matches_df = pd.concat([matches_df, pd.DataFrame([match_row])], ignore_index=True)

                        contact2_colindex = contact2_colindex + 1


matches_df.to_csv('C:/temp/output.csv', encoding='utf-8', index=False)

嵌套循环的时间复杂度是O(nmk*l)，完全无法处理十万级数据。改用Pandas的向量化操作和长格式转换，能把运行时间压缩到分钟级。

核心思路 #

• 将每个数据集的多列邮箱转成长格式（每行对应一个邮箱+记录ID+邮箱类型），让每个邮箱成为独立行，方便后续合并。
• 对两个长格式数据集按邮箱值做内连接，直接获取所有匹配结果。
• 过滤无效的"nan"邮箱值，避免无效匹配。

优化后代码 #

import pandas as pd

# 加载数据
contacts_1_df = pd.read_csv("C:/temp/Contacts1.csv")
contacts_2_df = pd.read_csv("C:/temp/contacts2.CSV")

# 清理无效记录：仅保留有记录ID的行
contacts_1_df = contacts_1_df.dropna(subset=['ContactId']).copy()
contacts_2_df = contacts_2_df.dropna(subset=['ContactID']).copy()

# 筛选需要的列：记录ID + 所有邮箱列
contact1_email_cols = ['Preferred Email', 'Company Email', 'Email Address 2', 'Email 3']
contact2_email_cols = ['Email', 'Email_1', 'Email_2', 'Email_3', 'Email_4']

contacts_1_df = contacts_1_df[['ContactId'] + contact1_email_cols]
contacts_2_df = contacts_2_df[['ContactID'] + contact2_email_cols]

# 统一转换为小写字符串
contacts_1_df = contacts_1_df.apply(lambda x: x.astype(str).str.lower())
contacts_2_df = contacts_2_df.apply(lambda x: x.astype(str).str.lower())

# --------------------------
# 转换为长格式：每个邮箱单独一行
# --------------------------
# 处理第一个数据集
df1_long = contacts_1_df.melt(
    id_vars=['ContactId'],
    value_vars=contact1_email_cols,
    var_name='Contact1ContactEmailType',
    value_name='Contact1ContactEmailValue'
)
# 过滤空邮箱（nan字符串）
df1_long = df1_long[df1_long['Contact1ContactEmailValue'] != 'nan']

# 处理第二个数据集
df2_long = contacts_2_df.melt(
    id_vars=['ContactID'],
    value_vars=contact2_email_cols,
    var_name='Contact2EmailType',
    value_name='Contact2EmailValue'
)
df2_long = df2_long[df2_long['Contact2EmailValue'] != 'nan']

# --------------------------
# 按邮箱值内连接，得到所有匹配结果
# --------------------------
matches_df = pd.merge(
    df1_long,
    df2_long,
    left_on='Contact1ContactEmailValue',
    right_on='Contact2EmailValue',
    how='inner'
)

# 重命名列以匹配输出需求
matches_df = matches_df.rename(columns={
    'ContactId': 'Contact1ContactGUID',
})

# 调整列顺序为需求格式
output_cols = [
    'Contact1ContactGUID',
    'Contact1ContactEmailType',
    'Contact1ContactEmailValue',
    'ContactID',
    'Contact2EmailType',
    'Contact2EmailValue'
]
matches_df = matches_df[output_cols]

# 输出结果
matches_df.to_csv('C:/temp/output.csv', encoding='utf-8', index=False)

效率提升原因 #

• 向量化操作：Pandas的melt和merge都是底层优化的C级实现，比Python循环快几个数量级。
• 时间复杂度降低：转换长格式是O(n)和O(m)，合并操作是O(n+m)（基于哈希表），十万级数据几分钟即可完成。
• 避免低效操作：原代码每次匹配都用concat追加行，这是极其耗时的操作；新代码直接通过合并生成完整结果集，避免了频繁的内存操作。

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