Pandas实现单值与列表匹配：手机号与域名关联查询需求

代码示例 #

df = pd.read_pickle('domain_list.pkl')
cc_base= pd.read_pickle('cc_base.pkl')

数据集单条记录展示 #

cc_base数据集示例： #

print(cc_base.iloc[100])

Domain                  kinresto.com
Phone                    12395550108
Alternative phone 2    13195550115.0
Alternative phone 3    12525550126.0
Alternative phone 4    13075550133.0
Alternative phone 5              NaN

print(cc_base.iloc[102])

Domain                       msg.com
Phone                  13075550133.0
Alternative phone 2    13195550115.0
Alternative phone 3    12395550108.0
Alternative phone 4              NaN
Alternative phone 5              NaN

df数据集示例： #

print(df.iloc[44556])

Phone                            12395550108
counts                                     2
Domain_list    ["['msg.com'", " 'kinresto.com']"]

需求说明 #

需要以df中的Phone为查询依据，在cc_base中找出该手机号作为主号码对应的域名，以及作为备用号码对应的域名，分别存入Main_phone_for_domain和Alternative_for列。最终期望输出格式如下：

Phone                                             12395550108
counts                                                      2
Domain_list                ["['msg.com'", " 'kinresto.com']"]
Main_phone_for_domain                        ["kinresto.com"]
Alternative_for                                   ["msg.com"]

已知Domain_list格式混乱，可通过.replace("'", "").replace(']', '').replace('[', '').replace('"', '').replace(' ', '')处理，最长的Domain_list包含3000个条目。

解决方案 #

步骤1：预处理cc_base，建立手机号-域名映射 #

先把cc_base里的主号码、备用号码分别和域名做关联，方便后续快速查询：

import pandas as pd

# 读取数据
df = pd.read_pickle('domain_list.pkl')
cc_base = pd.read_pickle('cc_base.pkl')

# 主号码映射：键是主号码，值是对应域名
main_phone_map = cc_base.set_index('Phone')['Domain'].to_dict()

# 备用号码映射：键是备用号码，值是对应域名列表
alt_phone_map = {}
alt_cols = [col for col in cc_base.columns if 'Alternative phone' in col]
for _, row in cc_base.iterrows():
    domain = row['Domain']
    for col in alt_cols:
        phone = row[col]
        if pd.notna(phone):
            alt_phone_map.setdefault(phone, []).append(domain)

步骤2：处理df的Domain_list并匹配结果 #

清洗混乱的域名列表，再根据映射关系筛选出目标域名：

def process_single_row(row):
    phone = row['Phone']
    # 清洗Domain_list，转成干净的域名列表
    raw_str = row['Domain_list']
    clean_domains = raw_str.replace("'", "").replace(']', '').replace('[', '').replace('"', '').replace(' ', '').split(',')
    
    # 筛选该手机号作为主号码的域名
    main_domains = []
    if phone in main_phone_map and main_phone_map[phone] in clean_domains:
        main_domains.append(main_phone_map[phone])
    
    # 筛选该手机号作为备用号码的域名
    alt_domains = []
    if phone in alt_phone_map:
        alt_domains = [d for d in alt_phone_map[phone] if d in clean_domains]
    
    return pd.Series({
        'Main_phone_for_domain': main_domains,
        'Alternative_for': alt_domains
    })

# 合并结果到原df
df = pd.concat([df, df.apply(process_single_row, axis=1)], axis=1)

验证结果 #

运行后查看指定行：

print(df.iloc[44556])

输出将与期望格式一致。

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