You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

如何优化Python Pandas中批量替换Job-Title列值的低效代码？

avatar
AI 小北
2026-7-17
招聘数据集Job-Title列标准化优化方案

问题背景

学习Python数据分析时，处理一份包含job title、country、job description三列的招聘数据集，需要将Job-Title列中类似“Urgently Data Analyst needed - one year contract”的非标准化内容替换为可聚合的标准值（如“Data Analyst”）。最初采用含大量if语句的循环处理4万行数据，不仅代码冗长，运行速度也很慢，需要优化方案。

原代码

导入模块（Google Jupyter Notebook） 

import numpy as np
import pandas as pd
from google.colab import drive
drive.mount('/content/drive')

读取CSV并删除部分列 

jobscsv = pd.read_csv("/content/drive/MyDrive/allJobs.csv")

jobscsv.head

jobscsv_modified = jobscsv.drop(['Date-Posted', 'Salary', 'Company', 'Job-Type'], axis=1)

jobscsv_modified.head

性能瓶颈代码 

success_counter = 0

for i in range(0, len(jobscsv_modified)):

    if "Data Analyst" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Data Analyst'
      success_counter = success_counter + 1

    if "Data Analysis" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Data Analyst'
      success_counter = success_counter + 1

    if "Data Analytics" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Data Analyst'
      success_counter = success_counter + 1
##
    if "Data Scientist" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Data Scientist'
      success_counter = success_counter + 1

    if "Data Science" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Data Scientist'
      success_counter = success_counter + 1

    if "Data Engineer" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Data Scientist'
      success_counter = success_counter + 1

    if "Data Architect" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Data Scientist'
      success_counter = success_counter + 1

    if "Machine Learning" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Data Scientist'
      success_counter = success_counter + 1

    if " AI " in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Data Scientist'
      success_counter = success_counter + 1

    if "Artificial Intelligence" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Data Scientist'
      success_counter = success_counter + 1

    if "Database" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Database Engineer'
      success_counter = success_counter + 1

    if "Data Entry" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Data Entry'
      success_counter = success_counter + 1
##
    if "Customer Service" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Customer Service'
      success_counter = success_counter + 1

    if "Sales" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Sales'
      success_counter = success_counter + 1
##
    if "Software" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Programmer'
      success_counter = success_counter + 1

    if "Web Development" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Programmer'
      success_counter = success_counter + 1

    if "Stack" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Programmer'
      success_counter = success_counter + 1
##
    if "Volunteer" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Volunteer Work'
      success_counter = success_counter + 1

    if "Laboratory" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Research'
      success_counter = success_counter + 1

    if "Research" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Research'
      success_counter = success_counter + 1

    if "PhD" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'PhD'
      success_counter = success_counter + 1

    if "Teacher" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Education'
      success_counter = success_counter + 1

    if "Lecturer" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Education'
      success_counter = success_counter + 1

    if "Principal" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Education'
      success_counter = success_counter + 1

    if "Dean" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Education'
      success_counter = success_counter + 1

    if "School" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Education'
      success_counter = success_counter + 1

    if "Student" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Education'
      success_counter = success_counter + 1

    if "Instructor" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Education'
      success_counter = success_counter + 1

    if "Postdoctoral" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Research'
      success_counter = success_counter + 1

##

    if "Mechanical Engineer" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Mechanical Engineer'
      success_counter = success_counter + 1

    if "Industrial Engineer" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Industrial Engineer'
      success_counter = success_counter + 1

    if "Mechatronics Engineer" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Mechatronics Engineer'
      success_counter = success_counter + 1

    if "Electrical Engineer" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Electrical Engineer'
      success_counter = success_counter + 1

    if "Civil Engineer" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Civil Engineer'
      success_counter = success_counter + 1

    if "Chemical Engineer" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Chemical Engineer'
      success_counter = success_counter + 1

    if "Process Engineer" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Process Engineer'
      success_counter = success_counter + 1

    if "Lab Engineer" in jobscsv_modified['Job-Title'][i]:
      jobscsv_modified['Job-Title'][i] = 'Research'
      success_counter = success_counter + 1

优化方案

核心思路

利用Pandas的向量化字符串操作替代Python循环，通过定义匹配规则字典批量处理Job-Title列的标准化。向量化操作由底层优化实现，处理4万行数据的速度会比循环快几个数量级，同时代码更简洁易维护。

优化代码 

# 定义标准化规则：键是目标值，值是需要匹配的关键词列表
title_mapping = {
    'Data Analyst': ['Data Analyst', 'Data Analysis', 'Data Analytics'],
    'Data Scientist': ['Data Scientist', 'Data Science', 'Data Engineer', 'Data Architect', 
                      'Machine Learning', ' AI ', 'Artificial Intelligence'],
    'Database Engineer': ['Database'],
    'Data Entry': ['Data Entry'],
    'Customer Service': ['Customer Service'],
    'Sales': ['Sales'],
    'Programmer': ['Software', 'Web Development', 'Stack'],
    'Volunteer Work': ['Volunteer'],
    'Research': ['Laboratory', 'Research', 'Postdoctoral', 'Lab Engineer'],
    'PhD': ['PhD'],
    'Education': ['Teacher', 'Lecturer', 'Principal', 'Dean', 'School', 'Student', 'Instructor'],
    'Mechanical Engineer': ['Mechanical Engineer'],
    'Industrial Engineer': ['Industrial Engineer'],
    'Mechatronics Engineer': ['Mechatronics Engineer'],
    'Electrical Engineer': ['Electrical Engineer'],
    'Civil Engineer': ['Civil Engineer'],
    'Chemical Engineer': ['Chemical Engineer'],
    'Process Engineer': ['Process Engineer']
}

# 初始化新列（或直接修改原列）
jobscsv_modified['Standardized-Job-Title'] = 'Other'

# 遍历规则字典，批量匹配赋值
for target, keywords in title_mapping.items():
    # 用|连接关键词，构造正则表达式，忽略大小写（可选）
    pattern = '|'.join(keywords)
    mask = jobscsv_modified['Job-Title'].str.contains(pattern, case=False, na=False)
    jobscsv_modified.loc[mask, 'Standardized-Job-Title'] = target

# 统计匹配数量
success_counter = len(jobscsv_modified[jobscsv_modified['Standardized-Job-Title'] != 'Other'])
print(f"成功标准化 {success_counter} 条记录")

关键优化点

  1. 向量化操作：str.contains一次性处理整列数据，避免逐行循环的开销。
  2. 规则集中管理：所有匹配规则集中在字典里，新增或修改规则只需调整字典，代码更易维护。
  3. 匹配顺序：按原逻辑的优先级定义字典顺序（比如先匹配更具体的关键词，避免被宽泛关键词覆盖）。
  4. 自动计数：通过筛选非"Other"的行直接统计匹配数量，无需手动累加。

额外建议

  • 如果需要更精确的匹配（比如匹配完整词而非子串），可以在正则表达式中添加边界符，例如pattern = r'\b(' + '|'.join(keywords) + r')\b'。
  • 若存在大小写不一致的情况，添加case=False参数统一忽略大小写匹配。

内容的提问来源于stack exchange，提问作者Hesham M Omar

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.07.17 21:38:11