优化DataFrame嵌套循环提速：匹配更新出勤数据

原代码采用双层iterrows()逐行迭代，在数据量较大时效率极低——这是因为iterrows()会将每一行转换为Series，逐行操作完全脱离了pandas的向量化优化特性。以下是两种高效优化方案：

方案1：预处理匹配字典+向量化操作 #

核心思路是先构建一个快速查询的匹配字典，再用pandas的向量化方法批量更新，避免逐行循环。

def giveCredit(dataframe, dictNewNames, reporting):
    # 1. 向量化生成searchName列，替代循环映射
    dataframe['searchName'] = dataframe['Session Title'].map(dictNewNames)
    
    # 2. 预处理reporting，构建(邮箱, 简化标题) -> 完成日期的映射字典
    email_search_map = {}
    target_searches = set(dictNewNames.values())  # 去重减少重复判断
    
    for _, row in reporting.iterrows():
        email = row['Email']
        pathway_title = row['Pathway Title']
        date_completed = row['Date Completed']
        
        # 找出当前路径标题包含的所有目标简化标题
        matched_searches = [s for s in target_searches if s in pathway_title]
        for s in matched_searches:
            email_search_map[(email, s)] = date_completed
    
    # 3. 向量化匹配并更新字段
    dataframe['match_key'] = list(zip(dataframe['Attendee Email'], dataframe['searchName']))
    dataframe['Date Completed'] = dataframe['match_key'].map(email_search_map).fillna('')
    dataframe['Completed'] = dataframe['Date Completed'].apply(lambda x: 'Yes' if x else 'No')
    
    # 清理临时辅助列
    dataframe.drop(columns=['searchName', 'match_key'], inplace=True)
    
    return dataframe

方案2：pandas Merge+模糊匹配 #

利用pandas的merge能力，结合字符串包含判断实现批量匹配，代码更简洁直观。

import pandas as pd

def giveCredit(dataframe, dictNewNames, reporting):
    # 1. 给出勤表添加简化标题列
    dataframe['searchName'] = dataframe['Session Title'].map(dictNewNames)
    
    # 2. 给参训表匹配对应的简化标题
    def extract_search_title(title):
        for s in dictNewNames.values():
            if s in title:
                return s
        return None
    
    reporting['searchName'] = reporting['Pathway Title'].apply(extract_search_title)
    reporting = reporting.dropna(subset=['searchName'])  # 过滤无匹配的记录
    
    # 3. 按邮箱+简化标题左连接两张表
    merged_df = dataframe.merge(
        reporting[['Email', 'searchName', 'Date Completed']],
        left_on=['Attendee Email', 'searchName'],
        right_on=['Email', 'searchName'],
        how='left'
    )
    
    # 4. 批量更新状态和日期
    dataframe['Date Completed'] = merged_df['Date Completed_y'].fillna('')
    dataframe['Completed'] = merged_df['Date Completed_y'].apply(lambda x: 'Yes' if pd.notna(x) else 'No')
    
    # 清理临时列
    dataframe.drop(columns=['searchName'], inplace=True)
    
    return dataframe

效率对比 #

• 原代码时间复杂度：O(M*N)（M为出勤表行数，N为参训表行数）
• 优化方案时间复杂度：O(M + N*K)（K为简化标题的去重数量，远小于N），数据量越大，效率提升越明显

注意事项 #

1. 如果同一个(邮箱, 简化标题)在参训表中有多条记录，方案1会保留最后一条的日期，方案2会保留merge时匹配到的第一条，可根据业务需求调整
2. 确保dictNewNames的键与出勤表Session Title完全匹配，否则map会生成NaN，可添加fillna处理异常情况

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