Pandas DataFrame匹配手机号邮箱后新增对应结果列的实现问题

核心思路 #

• 抽离邮箱、手机号的正则规则，编写提取函数直接返回匹配到的内容，避免重复判定
• 遍历所有文本类型列，逐行记录匹配项所属的列名、原始单元格内容、提取到的具体联系方式
• 按ID分组聚合后和原有判定结果合并，得到符合要求的输出表

完整可运行代码 #

import pandas as pd, re

# 预定义正则规则，修正原代码中的HTML转义问题
email_re = r'(?:[a-z0-9!#$%&\'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&\'*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9]))\.){3}(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9])|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])'
phone_re = r'(?:(?:\+?1\s*(?:[.-]\s*)?)?(?:\(\s*([2-9]1[02-9]|[2-9][02-8]1|[2-9][02-8][02-9])\s*\)|([2-9]1[02-9]|[2-9][02-8]1|[2-9][02-8][02-9]))\s*(?:[.-]\s*)?)?([2-9]1[02-9]|[2-9][02-9]1|[2-9][02-9]{2})\s*(?:[.-]\s*)?([0-9]{4})(?:\s*(?:#|x\.?|ext\.?|extension)\s*(\d+))?'

# 测试数据集
df = pd.DataFrame({
    'ID':[1,2,5,25,26],
    'Lineage':['apple', 'square', 'please Gino Mcneill gm@yahoo.com', 'ball', '888-555-5556 Ryan Parkes rp@abc.io'],
    'ShortDesc':['618-552-2255','Gino Mcneill gm@github.com','',' please call now','if you have trouble you should call 816-455-5599 ASAP' ],
    'LongDesc':['Eesha Hinton', 'for help with product 56789, call 618-578-0055 immediately, or email Gino Mcneill gm@yahoo.com', 'maybe six five today for ever','more random text that could be really long and annoying','over the hills and through the woods']
})

# 初始化结果存储字典
res = {
    'ID': [],
    'E_Col': [],
    'E_Col_Dat': [],
    'Extracted_Email': [],
    'P_Col': [],
    'P_Col_Dat': [],
    'Extracted_Phone': []
}

str_cols = df.select_dtypes(object).columns
for idx, row in df.iterrows():
    e_cols, e_dats, e_extract = [], [], []
    p_cols, p_dats, p_extract = [], [], []
    for col in str_cols:
        val = str(row[col])
        # 匹配邮箱
        emails = re.findall(email_re, val)
        if emails:
            e_cols.append(col)
            e_dats.append(val)
            e_extract.extend(emails)
        # 匹配手机号
        phones = re.findall(phone_re, val)
        if phones:
            # 正则分组拼接成完整手机号
            full_phones = [''.join(p) for p in phones]
            p_cols.append(col)
            p_dats.append(val)
            p_extract.extend(full_phones)
    # 仅保留有匹配结果的行
    if e_cols or p_cols:
        res['ID'].append(row['ID'])
        res['E_Col'].append(','.join(e_cols) if e_cols else None)
        res['E_Col_Dat'].append(','.join(f"'{d}'" for d in e_dats) if e_dats else None)
        res['Extracted_Email'].append(','.join(e_extract) if e_extract else None)
        res['P_Col'].append(','.join(p_cols) if p_cols else None)
        res['P_Col_Dat'].append(','.join(f"'{d}'" for d in p_dats) if p_dats else None)
        res['Extracted_Phone'].append(','.join(p_extract) if p_extract else None)

# 转成DataFrame，和你原有的Email/Phone标记列合并
result_df = pd.DataFrame(res)
result_df['Email'] = result_df['E_Col'].notna().map({True:'x', False:None})
result_df['Phone'] = result_df['P_Col'].notna().map({True:'x', False:None})
# 调整列顺序为你需要的格式
result_df = result_df[['ID','Email','Phone','E_Col','E_Col_Dat','Extracted_Email','P_Col','P_Col_Dat','Extracted_Phone']]

print(result_df)

补充说明 #

如果不需要关联列名和原始内容，只需要所有匹配到的手机号/邮箱列表，直接提取Extracted_Email、Extracted_Phone两列，拆分后去重即可。

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