Data Detox: A Guide to Cleaning Raw Data With Python.

Introduction

Data Cleaning

Welcome to the second instalment of a four part series exploring how a salary prediction model was constructed, using job descriptions from LinkedIn.

All the jobs in the dataset were based in the United Kingdom.

In this part of the series, lets explore the process of cleaning up the data with some handy packages in python.

The following packages were used:

import re
import spacy
import requests
import warnings
import pandas as pd
from bs4 import BeautifulSoup
warnings.filterwarnings('ignore')

There were 2 separate datasets, one containing:

 #   Column            Non-Null Count  Dtype 
---  ------            --------------  ----- 
 0   job_title         3253 non-null   object
 1   job_detail_url    3253 non-null   object
 2   job_listed        3253 non-null   object
 3   company_name      3253 non-null   object
 4   company_link      3253 non-null   object
 5   company_location  3253 non-null   object

and the other containing:

#   Column           Non-Null Count  Dtype 
---  ------           --------------  ----- 
 0   job_details      2709 non-null   object
 1   seniority_level  2709 non-null   object
 2   employment_type  2709 non-null   object
 3   job_function     2709 non-null   object
 4   industry         2709 non-null   object
 5   source_url       2709 non-null   object

The datasets were imported and merged on job_detail_url for table one and source_url for table two.

The resulting dataset had the following schema

#   Column            Non-Null Count  Dtype 
---  ------            --------------  ----- 
 0   job_title         2696 non-null   object contains title of role
 1   job_detail_url    2696 non-null   object contains url to job post
 2   job_listed        2696 non-null   object contains date of job post
 3   company_name      2696 non-null   object contains name of the company hiring
 4   company_link      2696 non-null   object contains link to company profile
 5   company_location  2696 non-null   object contains company location 
 6   job_details       2696 non-null   object contains job description information
 7   seniority_level   2696 non-null   object contains level being hired for
 8   employment_type   2696 non-null   object tells if the job is full time or not 
 9   job_function      2696 non-null   object contains details on the type of work to be performed in this role 
 10  industry          2696 non-null   object contains the industry being hired for
 11  source_url        2696 non-null   object same as job_detail_url

Not all columns needed cleaning, company_location and job_details were cleaned, salary and technical tools were extracted and new columns were added to the dataset.

Technical tool values allowed for tracking the technical concepts and programming languages employers mentioned in their job post description.

Company Location

Company location values were split into two separate columns, one for city and one for region.

A function built with regular expressions was used to split up the company locations, a comma separated cities from regions, the Regular Expressions (regex) string split the values, based on the comma.

Values before the comma were cities and after the comma were regions.

def locate(df, df_column, new_column, regex):
    df[new_column] = df[df_column].str.extract(regex)
    return df

jobs_with_cities = locate(jobs, "company_location", "company_city" , r'^\s*([^,]*)')

jobs_with_regions = locate(jobs_with_cities, "company_location", "company_region", r'[^,]*,\s*([^,]*)')

Job Details

The Job_details column contained job description text, the text also contained some HTML elements which needed to be removed. The values in the job_details column were parsed with beautifulsoups html parser to strip away the HTML elements.

def remove_tags(text):
    soup = BeautifulSoup(text, 'html.parser')
    return soup.get_text()

jobs_with_regions["job_details"] = jobs_with_regions["job_details"].apply(remove_tags)

Salary Values

Salary values were in the job description text and they needed to be extracted. To grab salary information from the job details, we went through an iterative process.

First a function was constructed using a regex string specifically designed to identify monetary amounts in British pounds sterling (£) notation.

pattern = r'£(\d{1,2}(?:,\d{3})*)(\.\d{2})?([kK])?'

Here’s a breakdown:

£: Matches the pound sterling symbol.

(\d{1,2}(?:,\d{3})*): Captures one or two digits followed by zero or more groups of three digits separated by commas, representing pounds.

(.\d{2})?: Captures an optional decimal point followed by exactly two digits, representing pence.

([kK])?: Matches an optional ‘k’ or ‘K’, denoting thousand.

This regex pattern helps identify and capture salary amounts in British pounds sterling notation.

Next, a for loop iterated through each salary in the ‘salaries’ list. It verified whether any salary contained a ‘k’ or ‘K’ character and was three characters or fewer in length.

If such a salary was found, it’s was considered to represent a thousand (like ‘10k’ for £10,000). Consequently, the ‘k’ or ‘K’ character was removed, and ‘,000’ was appended to the end of the salary.

cleaned_salary = []


for salary_list in jobs_with_salaries['salaries']:
    modification_needed = False
    modified_list = []


    for salary in salary_list:
        if 'k' in salary.lower() and len(salary) <= 3:
            modification_needed = True
            value_no_k = salary.replace('k', '').replace('K', '')
            new_value = value_no_k + ',000'
            modified_list.append(new_value)
        else:
            modified_list.append(salary)
    if modification_needed:
        cleaned_salary.append(modified_list)
    else:
        cleaned_salary.append(salary_list)




jobs_with_salaries['salaries_three'] = cleaned_salary

Additional loops were created to clean and filter out invalid values leaving proper annual salary values.

Technical Tools and Programming Languages

To extract technical tools and programming languages, a list containing technology tools and programming languages of interest was created. The list was run against job_details text to find matches, any matches found were added to a new column in the dataframe.

tools = []


for desc in jobs_with_salaries['job_details']:
    desc_lower = desc.lower()
    desc_list = []
   
    for item in tools_with_alternatives_unique:
        if item in  desc_lower:
            desc_list.append(item)


    tools.append(desc_list)




jobs_with_salaries['tech_stack_analytics'] = tools

With the extracted technical tools and programming languages, the next step involved constructing sub-tables to track specific languages, tools, and concepts required for each role.

Job Titles

Finally, job roles were categorized based on keywords found in the job titles:

Data Analyst
Business Data Analyst
Business Intelligence Analyst
Intelligence Analyst
Data Scientist
Data Engineer
Quantitative Analyst
Financial Analyst

This helped organize the roles into meaningful categories for analysis.

job_category =[]


for position in jobs_with_salaries['job_title']:
    position = str(position)
    if 'data' in position.lower() and ('analyst' in position.lower() or 'analysis' in position.lower()) and 'business' not in position.lower():
        job_category.append('Data Analyst')
    elif 'business' and 'data' and 'analyst' in position.lower():
        job_category.append('Business Data Analyst')
    elif 'insight' and 'analyst' in position.lower():
        job_category.append('Insight Analyst')
    elif 'business' and 'intelligence' in position.lower():
        job_category.append('Business Intelligence Analyst')
    elif 'bi' in position.lower():
        job_category.append('Business Intelligence Analyst')
    elif 'data' and 'scientist' in position.lower():
        job_category.append('Data Scientist')
    elif ('data' and 'engineer') in position.lower() or ('engineer') in position.lower():
        job_category.append('Data Engineer')
    elif ('quantitative' or 'quant') in position.lower() and 'analyst' in position.lower():
        job_category.append('Quantitative Analyst')
    elif ('financial' or 'finance') in position.lower() and 'analyst' in position.lower():
        job_category.append('Financial Analyst')
    else:
        job_category.append('Other')




jobs_with_salaries['job_category'] = job_category