Pandas同release同分组数据查找缺失路径，如何替代低效嵌套迭代？

我对Python/Pandas非常陌生，手上有一个约150万行且还在持续增长的DataFrame，需要找出同release、同group下，缺失了其他主机共有的path的主机。目前采用的遍历数据的方案效率很低，求其他实现思路或者现有方案的性能优化建议。

预期输出样例 #

原始数据样例 #

当前实现代码 #

# 第一步：获取唯一的release列表
list_releases = df['Release'].dropna().unique().tolist()

# 第二步：获取唯一的group列表
list_groups = df['Group'].dropna().unique().tolist()

# 第三步：构建group对应主机列表的字典
lists_hosts = hosts_by_group(list_groups, df)

# 第四步：检测缺失文件
audit_missing = find_missing_files(list_releases, lists_hosts, df)

overview = {"Release": [], "Group": [], "SubjectHost": [], "FileMissing": [], "ReferenceHosts": [], "Ref1Domain": [], "Ref2Domain": [], "SubjDomain": [], "Extension": []}

def generate_overview(grp, hst, ref, hi, ref1_domain, ref2_domain, subj_domain, df,release,hosts, checking_host, idx2):
    df1 = df[(df.Hostname == hosts[idx2]) & (df.Release == release)]
    df2 = df[(df.Hostname == hosts[hi]) & (df.Release == release)]
    merge = pd.merge(df1, df2, how="inner", on=["Path"]).dropna()
    merge2 = pd.merge(checking_host, merge, how="inner", on=["Path"]).dropna()

    files_not_found = merge[~merge["Path"].isin(merge2["Path"])].dropna()
    iter = files_not_found['Path'].tolist()
    count = files_not_found['Path'].count()
        
    if files_not_found.count().sum() > 0:
        for file in iter:
            ext = files_not_found.loc[files_not_found['Path'] == file, 'Extension_x'].item()            
            overview["Release"].append(release)
            overview["Group"].append(grp)
            overview["SubjectHost"].append(hst)
            overview["FileMissing"].append(file)
            overview["ReferenceHosts"].append(ref) 
            overview["Ref1Domain"].append(ref1_domain) 
            overview["Ref2Domain"].append(ref2_domain) 
            overview["SubjDomain"].append(subj_domain) 
            overview["Extension"].append(ext) 

def missing_file_process(hosts,df, group, release):
    for idx1, host in enumerate(hosts):
        checking_host = df[(df.Hostname == host)]
        subj_domain = (checking_host.Domain.unique())[0]

        for idx2, host2 in enumerate(hosts):
            num_hosts = len(hosts)             
            ref = ''
            hosts_index = 0
            ref1_domain = ''
            ref2_domain = ''

            if num_hosts - idx2 < 2:
                ref = hosts[idx2] + ":" + hosts[0]
                hosts_index = 0
                ref1_domain = (df[(df.Hostname == hosts[idx2])].Domain.unique())[0]
                ref2_domain = (df[(df.Hostname == hosts[0])].Domain.unique())[0]
            if num_hosts - idx2 > 1:
                ref = hosts[idx2] + ":" + hosts[idx2+1]
                hosts_index = idx2+1
                ref1_domain = (df[(df.Hostname == hosts[idx2])].Domain.unique())[0]
                ref2_domain = (df[(df.Hostname == hosts[idx2+1])].Domain.unique())[0]
            
            generate_overview( group, host, ref, hosts_index, ref1_domain, ref2_domain, subj_domain, df, release,hosts, checking_host, idx2)

def find_missing_files(releases, lists_hosts, df):
    for release in releases:
        for idx, (group, hosts) in enumerate(lists_hosts.items()):
            if len(hosts) > 2:
                missing_file_process(hosts,df, group, release)
    return pd.DataFrame(data=overview)

核心逻辑 #

当前代码性能瓶颈是多层嵌套循环，主机数量多的时候计算量会指数级增长，替换成Pandas向量化操作后效率能提升几十到上百倍：

1. 先算每个release+group分组下的全量唯一path集合，直接和该分组下每个主机的path做差集就能得到缺失路径，不需要两两主机对比
2. 提前把主机和域名、路径和扩展名的对应关系做成字典，避免重复查询DataFrame
3. 用Pandas内置的groupby方法做分组计算，不要手动遍历分组

优化后代码 #

import pandas as pd
import numpy as np

# 1. 预处理元数据，避免重复查询
# 主机对应域名映射
host_domain_map = df.drop_duplicates('Hostname').set_index('Hostname')['Domain'].to_dict()
# 路径对应扩展名映射
path_ext_map = df.drop_duplicates('Path').set_index('Path')['Extension'].to_dict()

# 2. 计算每个release+group下的全量path和对应主机列表
group_full_paths = df.groupby(['Release', 'Group'])['Path'].unique().reset_index(name='all_paths')
group_hosts = df.groupby(['Release', 'Group'])['Hostname'].unique().reset_index(name='hosts')
group_meta = pd.merge(group_full_paths, group_hosts, on=['Release', 'Group'])
# 只保留主机数大于2的分组，和原逻辑一致
group_meta = group_meta[group_meta['hosts'].map(len) > 2].copy()

# 3. 计算每个release+group+host对应的自有path集合
host_paths = df.groupby(['Release', 'Group', 'Hostname'])['Path'].unique().reset_index(name='host_paths')
all_data = pd.merge(host_paths, group_meta, on=['Release', 'Group'])

# 4. 计算每个主机的缺失路径，展开为一行对应一个缺失路径
all_data['missing_paths'] = all_data.apply(lambda x: np.setdiff1d(x['all_paths'], x['host_paths']), axis=1)
res = all_data.explode('missing_paths').dropna(subset=['missing_paths'])

# 5. 补充其他要求字段
def get_ref_hosts(host_list):
    n = len(host_list)
    # 按原逻辑生成参考主机拼接规则
    ref_list = []
    for idx2 in range(n):
        if n - idx2 < 2:
            ref_list.append(f"{host_list[idx2]}:{host_list[0]}")
        else:
            ref_list.append(f"{host_list[idx2]}:{host_list[idx2+1]}")
    # 取第一个存在的参考主机，可按需调整
    return ref_list[0]

res['ReferenceHosts'] = res['hosts'].map(get_ref_hosts)
res['SubjDomain'] = res['Hostname'].map(host_domain_map)
res['Ref1Domain'] = res['ReferenceHosts'].map(lambda x: host_domain_map.get(x.split(':')[0], ''))
res['Ref2Domain'] = res['ReferenceHosts'].map(lambda x: host_domain_map.get(x.split(':')[1], ''))
res['Extension'] = res['missing_paths'].map(path_ext_map)

# 6. 输出和原逻辑一致的格式
final_res = res.rename(columns={
    'Hostname': 'SubjectHost',
    'missing_paths': 'FileMissing'
})[['Release', 'Group', 'SubjectHost', 'FileMissing', 'ReferenceHosts', 'Ref1Domain', 'Ref2Domain', 'SubjDomain', 'Extension']].reset_index(drop=True)

额外性能优化技巧 #

• Release、Group、Hostname这几个重复值多的字段可以转成category类型，内存占用能降70%以上，计算速度也会更快
• 如果后续数据量超过千万行，可以换用Polars库处理，大表性能比Pandas高很多

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