Data Cleaning & Preprocessing

This notebook performs data cleaning on the Lightcast job postings dataset, preparing it for exploratory analysis and machine learning models.

Code

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import missingno as msno
import re
import warnings
warnings.filterwarnings('ignore')


df = pd.read_csv('data/lightcast_job_postings.csv', 
                 low_memory=False,
                 dtype={'ID': str}) 

print(f"Original dataset shape: {df.shape}")


pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', 100)  
pd.set_option('display.width', None)
display(df.head())

Original dataset shape: (72498, 131)

	ID	LAST_UPDATED_DATE	LAST_UPDATED_TIMESTAMP	DUPLICATES	POSTED	EXPIRED	DURATION	SOURCE_TYPES	SOURCES	URL	ACTIVE_URLS	ACTIVE_SOURCES_INFO	TITLE_RAW	BODY	MODELED_EXPIRED	MODELED_DURATION	COMPANY	COMPANY_NAME	COMPANY_RAW	COMPANY_IS_STAFFING	EDUCATION_LEVELS	EDUCATION_LEVELS_NAME	MIN_EDULEVELS	MIN_EDULEVELS_NAME	MAX_EDULEVELS	MAX_EDULEVELS_NAME	EMPLOYMENT_TYPE	EMPLOYMENT_TYPE_NAME	MIN_YEARS_EXPERIENCE	MAX_YEARS_EXPERIENCE	IS_INTERNSHIP	SALARY	REMOTE_TYPE	REMOTE_TYPE_NAME	ORIGINAL_PAY_PERIOD	SALARY_TO	SALARY_FROM	LOCATION	CITY	CITY_NAME	COUNTY	COUNTY_NAME	MSA	MSA_NAME	STATE	STATE_NAME	COUNTY_OUTGOING	COUNTY_NAME_OUTGOING	COUNTY_INCOMING	COUNTY_NAME_INCOMING	MSA_OUTGOING	MSA_NAME_OUTGOING	MSA_INCOMING	MSA_NAME_INCOMING	NAICS2	NAICS2_NAME	NAICS3	NAICS3_NAME	NAICS4	NAICS4_NAME	NAICS5	NAICS5_NAME	NAICS6	NAICS6_NAME	TITLE	TITLE_NAME	TITLE_CLEAN	SKILLS	SKILLS_NAME	SPECIALIZED_SKILLS	SPECIALIZED_SKILLS_NAME	CERTIFICATIONS	CERTIFICATIONS_NAME	COMMON_SKILLS	COMMON_SKILLS_NAME	SOFTWARE_SKILLS	SOFTWARE_SKILLS_NAME	ONET	ONET_NAME	ONET_2019	ONET_2019_NAME	CIP6	CIP6_NAME	CIP4	CIP4_NAME	CIP2	CIP2_NAME	SOC_2021_2	SOC_2021_2_NAME	SOC_2021_3	SOC_2021_3_NAME	SOC_2021_4	SOC_2021_4_NAME	SOC_2021_5	SOC_2021_5_NAME	LOT_CAREER_AREA	LOT_CAREER_AREA_NAME	LOT_OCCUPATION	LOT_OCCUPATION_NAME	LOT_SPECIALIZED_OCCUPATION	LOT_SPECIALIZED_OCCUPATION_NAME	LOT_OCCUPATION_GROUP	LOT_OCCUPATION_GROUP_NAME	LOT_V6_SPECIALIZED_OCCUPATION	LOT_V6_SPECIALIZED_OCCUPATION_NAME	LOT_V6_OCCUPATION	LOT_V6_OCCUPATION_NAME	LOT_V6_OCCUPATION_GROUP	LOT_V6_OCCUPATION_GROUP_NAME	LOT_V6_CAREER_AREA	LOT_V6_CAREER_AREA_NAME	SOC_2	SOC_2_NAME	SOC_3	SOC_3_NAME	SOC_4	SOC_4_NAME	SOC_5	SOC_5_NAME	LIGHTCAST_SECTORS	LIGHTCAST_SECTORS_NAME	NAICS_2022_2	NAICS_2022_2_NAME	NAICS_2022_3	NAICS_2022_3_NAME	NAICS_2022_4	NAICS_2022_4_NAME	NAICS_2022_5	NAICS_2022_5_NAME	NAICS_2022_6	NAICS_2022_6_NAME
0	1f57d95acf4dc67ed2819eb12f049f6a5c11782c	9/6/2024	2024-09-06 20:32:57.352 Z	0.0	6/2/2024	6/8/2024	6.0	[\n "Company"\n]	[\n "brassring.com"\n]	[\n "https://sjobs.brassring.com/TGnewUI/Sear...	[]	NaN	Enterprise Analyst (II-III)	31-May-2024\n\nEnterprise Analyst (II-III)\n\n...	6/8/2024	6.0	894731.0	Murphy USA	Murphy USA	False	[\n 2\n]	[\n "Bachelor's degree"\n]	2.0	Bachelor's degree	NaN	NaN	1.0	Full-time (> 32 hours)	2.0	2.0	False	NaN	0.0	[None]	NaN	NaN	NaN	{\n "lat": 33.20763,\n "lon": -92.6662674\n}	RWwgRG9yYWRvLCBBUg==	El Dorado, AR	5139.0	Union, AR	20980.0	El Dorado, AR	5.0	Arkansas	5139.0	Union, AR	5139.0	Union, AR	20980.0	El Dorado, AR	20980.0	El Dorado, AR	44.0	Retail Trade	441.0	Motor Vehicle and Parts Dealers	4413.0	Automotive Parts, Accessories, and Tire Retailers	44133.0	Automotive Parts and Accessories Retailers	441330.0	Automotive Parts and Accessories Retailers	ET29C073C03D1F86B4	Enterprise Analysts	enterprise analyst ii iii	[\n "KS126DB6T061MHD7RTGQ",\n "KS126706DPFD3...	[\n "Merchandising",\n "Mathematics",\n "Pr...	[\n "KS126DB6T061MHD7RTGQ",\n "KS128006L3V0H...	[\n "Merchandising",\n "Predictive Modeling"...	[]	[]	[\n "KS126706DPFD3354M7YK",\n "KS1280B68GD79...	[\n "Mathematics",\n "Presentations",\n "Re...	[\n "KS440W865GC4VRBW6LJP",\n "KS13USA80NE38...	[\n "SQL (Programming Language)",\n "Power B...	15-2051.01	Business Intelligence Analysts	15-2051.01	Business Intelligence Analysts	[\n "45.0601",\n "27.0101"\n]	[\n "Economics, General",\n "Mathematics, Ge...	[\n "45.06",\n "27.01"\n]	[\n "Economics",\n "Mathematics"\n]	[\n "45",\n "27"\n]	[\n "Social Sciences",\n "Mathematics and St...	15-0000	Computer and Mathematical Occupations	15-2000	Mathematical Science Occupations	15-2050	Data Scientists	15-2051	Data Scientists	23.0	Information Technology and Computer Science	231010.0	Business Intelligence Analyst	23101011.0	General ERP Analyst / Consultant	2310.0	Business Intelligence	23101011.0	General ERP Analyst / Consultant	231010.0	Business Intelligence Analyst	2310.0	Business Intelligence	23.0	Information Technology and Computer Science	15-0000	Computer and Mathematical Occupations	15-2000	Mathematical Science Occupations	15-2050	Data Scientists	15-2051	Data Scientists	[\n 7\n]	[\n "Artificial Intelligence"\n]	44.0	Retail Trade	441.0	Motor Vehicle and Parts Dealers	4413.0	Automotive Parts, Accessories, and Tire Retailers	44133.0	Automotive Parts and Accessories Retailers	441330.0	Automotive Parts and Accessories Retailers
1	0cb072af26757b6c4ea9464472a50a443af681ac	8/2/2024	2024-08-02 17:08:58.838 Z	0.0	6/2/2024	8/1/2024	NaN	[\n "Job Board"\n]	[\n "maine.gov"\n]	[\n "https://joblink.maine.gov/jobs/1085740"\n]	[]	NaN	Oracle Consultant - Reports (3592)	Oracle Consultant - Reports (3592)\n\nat SMX i...	8/1/2024	NaN	133098.0	Smx Corporation Limited	SMX	True	[\n 99\n]	[\n "No Education Listed"\n]	99.0	No Education Listed	NaN	NaN	1.0	Full-time (> 32 hours)	3.0	3.0	False	NaN	1.0	Remote	NaN	NaN	NaN	{\n "lat": 44.3106241,\n "lon": -69.7794897\n}	QXVndXN0YSwgTUU=	Augusta, ME	23011.0	Kennebec, ME	12300.0	Augusta-Waterville, ME	23.0	Maine	23011.0	Kennebec, ME	23011.0	Kennebec, ME	12300.0	Augusta-Waterville, ME	12300.0	Augusta-Waterville, ME	56.0	Administrative and Support and Waste Managemen...	561.0	Administrative and Support Services	5613.0	Employment Services	56132.0	Temporary Help Services	561320.0	Temporary Help Services	ET21DDA63780A7DC09	Oracle Consultants	oracle consultant reports	[\n "KS122626T550SLQ7QZ1C",\n "KS123YJ6KVWC9...	[\n "Procurement",\n "Financial Statements",...	[\n "KS122626T550SLQ7QZ1C",\n "KS123YJ6KVWC9...	[\n "Procurement",\n "Financial Statements",...	[]	[]	[]	[]	[\n "BGSBF3F508F7F46312E3",\n "ESEA839CED378...	[\n "Oracle Business Intelligence (BI) / OBIA...	15-2051.01	Business Intelligence Analysts	15-2051.01	Business Intelligence Analysts	[]	[]	[]	[]	[]	[]	15-0000	Computer and Mathematical Occupations	15-2000	Mathematical Science Occupations	15-2050	Data Scientists	15-2051	Data Scientists	23.0	Information Technology and Computer Science	231010.0	Business Intelligence Analyst	23101012.0	Oracle Consultant / Analyst	2310.0	Business Intelligence	23101012.0	Oracle Consultant / Analyst	231010.0	Business Intelligence Analyst	2310.0	Business Intelligence	23.0	Information Technology and Computer Science	15-0000	Computer and Mathematical Occupations	15-2000	Mathematical Science Occupations	15-2050	Data Scientists	15-2051	Data Scientists	NaN	NaN	56.0	Administrative and Support and Waste Managemen...	561.0	Administrative and Support Services	5613.0	Employment Services	56132.0	Temporary Help Services	561320.0	Temporary Help Services
2	85318b12b3331fa490d32ad014379df01855c557	9/6/2024	2024-09-06 20:32:57.352 Z	1.0	6/2/2024	7/7/2024	35.0	[\n "Job Board"\n]	[\n "dejobs.org"\n]	[\n "https://dejobs.org/dallas-tx/data-analys...	[]	NaN	Data Analyst	Taking care of people is at the heart of every...	6/10/2024	8.0	39063746.0	Sedgwick	Sedgwick	False	[\n 2\n]	[\n "Bachelor's degree"\n]	2.0	Bachelor's degree	NaN	NaN	1.0	Full-time (> 32 hours)	5.0	NaN	False	NaN	0.0	[None]	NaN	NaN	NaN	{\n "lat": 32.7766642,\n "lon": -96.7969879\n}	RGFsbGFzLCBUWA==	Dallas, TX	48113.0	Dallas, TX	19100.0	Dallas-Fort Worth-Arlington, TX	48.0	Texas	48113.0	Dallas, TX	48113.0	Dallas, TX	19100.0	Dallas-Fort Worth-Arlington, TX	19100.0	Dallas-Fort Worth-Arlington, TX	52.0	Finance and Insurance	524.0	Insurance Carriers and Related Activities	5242.0	Agencies, Brokerages, and Other Insurance Rela...	52429.0	Other Insurance Related Activities	524291.0	Claims Adjusting	ET3037E0C947A02404	Data Analysts	data analyst	[\n "KS1218W78FGVPVP2KXPX",\n "ESF3939CE1F80...	[\n "Management",\n "Exception Reporting",\n...	[\n "ESF3939CE1F80C10C327",\n "KS120GV6C72JM...	[\n "Exception Reporting",\n "Data Analysis"...	[\n "KS683TN76T77DQDVBZ1B"\n]	[\n "Security Clearance"\n]	[\n "KS1218W78FGVPVP2KXPX",\n "BGS1ADAA36DB6...	[\n "Management",\n "Report Writing",\n "In...	[\n "KS126HY6YLTB9R7XJC4Z"\n]	[\n "Microsoft Office"\n]	15-2051.01	Business Intelligence Analysts	15-2051.01	Business Intelligence Analysts	[]	[]	[]	[]	[]	[]	15-0000	Computer and Mathematical Occupations	15-2000	Mathematical Science Occupations	15-2050	Data Scientists	15-2051	Data Scientists	23.0	Information Technology and Computer Science	231113.0	Data / Data Mining Analyst	23111310.0	Data Analyst	2311.0	Data Analysis and Mathematics	23111310.0	Data Analyst	231113.0	Data / Data Mining Analyst	2311.0	Data Analysis and Mathematics	23.0	Information Technology and Computer Science	15-0000	Computer and Mathematical Occupations	15-2000	Mathematical Science Occupations	15-2050	Data Scientists	15-2051	Data Scientists	NaN	NaN	52.0	Finance and Insurance	524.0	Insurance Carriers and Related Activities	5242.0	Agencies, Brokerages, and Other Insurance Rela...	52429.0	Other Insurance Related Activities	524291.0	Claims Adjusting
3	1b5c3941e54a1889ef4f8ae55b401a550708a310	9/6/2024	2024-09-06 20:32:57.352 Z	1.0	6/2/2024	7/20/2024	48.0	[\n "Job Board"\n]	[\n "disabledperson.com",\n "dejobs.org"\n]	[\n "https://www.disabledperson.com/jobs/5948...	[]	NaN	Sr. Lead Data Mgmt. Analyst - SAS Product Owner	About this role:\n\nWells Fargo is looking for...	6/12/2024	10.0	37615159.0	Wells Fargo	Wells Fargo	False	[\n 99\n]	[\n "No Education Listed"\n]	99.0	No Education Listed	NaN	NaN	1.0	Full-time (> 32 hours)	3.0	NaN	False	NaN	0.0	[None]	NaN	NaN	NaN	{\n "lat": 33.4483771,\n "lon": -112.0740373\n}	UGhvZW5peCwgQVo=	Phoenix, AZ	4013.0	Maricopa, AZ	38060.0	Phoenix-Mesa-Chandler, AZ	4.0	Arizona	4013.0	Maricopa, AZ	4013.0	Maricopa, AZ	38060.0	Phoenix-Mesa-Chandler, AZ	38060.0	Phoenix-Mesa-Chandler, AZ	52.0	Finance and Insurance	522.0	Credit Intermediation and Related Activities	5221.0	Depository Credit Intermediation	52211.0	Commercial Banking	522110.0	Commercial Banking	ET2114E0404BA30075	Management Analysts	sr lead data mgmt analyst sas product owner	[\n "KS123QX62QYTC4JF38H8",\n "KS7G6NP6R6L1H...	[\n "Exit Strategies",\n "Reliability",\n "...	[\n "KS123QX62QYTC4JF38H8",\n "KS441PQ64HT13...	[\n "Exit Strategies",\n "User Story",\n "H...	[]	[]	[\n "KS7G6NP6R6L1H1SKFTSY",\n "KS1218W78FGVP...	[\n "Reliability",\n "Management",\n "Strat...	[\n "KS4409D76NW1S5LNCL18",\n "ESC7869CF7378...	[\n "SAS (Software)",\n "Google Cloud Platfo...	15-2051.01	Business Intelligence Analysts	15-2051.01	Business Intelligence Analysts	[]	[]	[]	[]	[]	[]	15-0000	Computer and Mathematical Occupations	15-2000	Mathematical Science Occupations	15-2050	Data Scientists	15-2051	Data Scientists	23.0	Information Technology and Computer Science	231113.0	Data / Data Mining Analyst	23111310.0	Data Analyst	2311.0	Data Analysis and Mathematics	23111310.0	Data Analyst	231113.0	Data / Data Mining Analyst	2311.0	Data Analysis and Mathematics	23.0	Information Technology and Computer Science	15-0000	Computer and Mathematical Occupations	15-2000	Mathematical Science Occupations	15-2050	Data Scientists	15-2051	Data Scientists	[\n 6\n]	[\n "Data Privacy/Protection"\n]	52.0	Finance and Insurance	522.0	Credit Intermediation and Related Activities	5221.0	Depository Credit Intermediation	52211.0	Commercial Banking	522110.0	Commercial Banking
4	cb5ca25f02bdf25c13edfede7931508bfd9e858f	6/19/2024	2024-06-19 07:00:00.000 Z	0.0	6/2/2024	6/17/2024	15.0	[\n "FreeJobBoard"\n]	[\n "craigslist.org"\n]	[\n "https://modesto.craigslist.org/sls/77475...	[]	NaN	Comisiones de $1000 - $3000 por semana... Comi...	Comisiones de $1000 - $3000 por semana... Comi...	6/17/2024	15.0	0.0	Unclassified	LH/GM	False	[\n 99\n]	[\n "No Education Listed"\n]	99.0	No Education Listed	NaN	NaN	3.0	Part-time / full-time	NaN	NaN	False	92500.0	0.0	[None]	year	150000.0	35000.0	{\n "lat": 37.6392595,\n "lon": -120.9970014\n}	TW9kZXN0bywgQ0E=	Modesto, CA	6099.0	Stanislaus, CA	33700.0	Modesto, CA	6.0	California	6099.0	Stanislaus, CA	6099.0	Stanislaus, CA	33700.0	Modesto, CA	33700.0	Modesto, CA	99.0	Unclassified Industry	999.0	Unclassified Industry	9999.0	Unclassified Industry	99999.0	Unclassified Industry	999999.0	Unclassified Industry	ET0000000000000000	Unclassified	comisiones de por semana comiensa rapido	[]	[]	[]	[]	[]	[]	[]	[]	[]	[]	15-2051.01	Business Intelligence Analysts	15-2051.01	Business Intelligence Analysts	[]	[]	[]	[]	[]	[]	15-0000	Computer and Mathematical Occupations	15-2000	Mathematical Science Occupations	15-2050	Data Scientists	15-2051	Data Scientists	23.0	Information Technology and Computer Science	231010.0	Business Intelligence Analyst	23101012.0	Oracle Consultant / Analyst	2310.0	Business Intelligence	23101012.0	Oracle Consultant / Analyst	231010.0	Business Intelligence Analyst	2310.0	Business Intelligence	23.0	Information Technology and Computer Science	15-0000	Computer and Mathematical Occupations	15-2000	Mathematical Science Occupations	15-2050	Data Scientists	15-2051	Data Scientists	NaN	NaN	99.0	Unclassified Industry	999.0	Unclassified Industry	9999.0	Unclassified Industry	99999.0	Unclassified Industry	999999.0	Unclassified Industry

1. Select Relevant Columns

Code

columns_to_keep = [
    'POSTED', 'EXPIRED','TITLE_NAME', 'BODY', 'COMPANY_NAME','SOURCE_TYPES', 'COMPANY_IS_STAFFING', 
    'SALARY', 'SALARY_FROM', 'SALARY_TO','STATE_NAME', 'CITY_NAME','REMOTE_TYPE_NAME', 
    'EMPLOYMENT_TYPE_NAME','MIN_YEARS_EXPERIENCE','MIN_EDULEVELS_NAME','SKILLS_NAME', 'SOFTWARE_SKILLS_NAME',
    'LOT_V6_OCCUPATION_NAME','NAICS_2022_2_NAME'
]
columns_to_keep = [col for col in columns_to_keep if col in df.columns]
df = df[columns_to_keep].copy()

print(f"Shape after column selection: {df.shape}")
print(f"\nColumns kept ({len(df.columns)}):")
print(df.columns.tolist())

Shape after column selection: (72498, 20)

Columns kept (20):
['POSTED', 'EXPIRED', 'TITLE_NAME', 'BODY', 'COMPANY_NAME', 'SOURCE_TYPES', 'COMPANY_IS_STAFFING', 'SALARY', 'SALARY_FROM', 'SALARY_TO', 'STATE_NAME', 'CITY_NAME', 'REMOTE_TYPE_NAME', 'EMPLOYMENT_TYPE_NAME', 'MIN_YEARS_EXPERIENCE', 'MIN_EDULEVELS_NAME', 'SKILLS_NAME', 'SOFTWARE_SKILLS_NAME', 'LOT_V6_OCCUPATION_NAME', 'NAICS_2022_2_NAME']

2. Create Duration Feature

Duration = EXPIRED - POSTED (in days)

Code

df = df.dropna(subset=['EXPIRED'])
df['POSTED'] = pd.to_datetime(df['POSTED'], errors='coerce')
df['EXPIRED'] = pd.to_datetime(df['EXPIRED'], errors='coerce')

df['DURATION'] = (df['EXPIRED'] - df['POSTED']).dt.days

print("Duration statistics:")
print(df['DURATION'].describe())

df.drop(columns=['EXPIRED'], inplace=True)

print(f"\nShape: {df.shape}")

Duration statistics:
count    64654.000000
mean        35.296811
std         23.961129
min          0.000000
25%         14.000000
50%         31.000000
75%         60.000000
max        119.000000
Name: DURATION, dtype: float64

Shape: (64654, 20)

3. Explore LOT_V6_OCCUPATION_NAME for Filtering

Using LOT_V6_OCCUPATION_NAME provides more accurate job classification than TITLE_NAME.

Code

print("LOT_V6_OCCUPATION_NAME value counts:\n")
print(df['LOT_V6_OCCUPATION_NAME'].value_counts())

LOT_V6_OCCUPATION_NAME value counts:

LOT_V6_OCCUPATION_NAME
Data / Data Mining Analyst                                              26809
Business Intelligence Analyst                                           26550
Computer Systems Engineer / Architect                                    7188
Business / Management Analyst                                            3729
Clinical Analyst / Clinical Documentation and Improvement Specialist      228
Market Research Analyst                                                   132
Name: count, dtype: int64

Code

occupations_to_keep = [
    'Data / Data Mining Analyst',
    'Business Intelligence Analyst',
    'Business / Management Analyst',
    'Market Research Analyst'

]
df = df[df['LOT_V6_OCCUPATION_NAME'].isin(occupations_to_keep)].copy()

print(f"Shape after filtering: {df.shape}")
print(f"\nOccupations kept:")
print(df['LOT_V6_OCCUPATION_NAME'].value_counts())

Shape after filtering: (57220, 20)

Occupations kept:
LOT_V6_OCCUPATION_NAME
Data / Data Mining Analyst       26809
Business Intelligence Analyst    26550
Business / Management Analyst     3729
Market Research Analyst            132
Name: count, dtype: int64

4. Clean String Formatting

Remove JSON-like formatting: [\n "value"\n] → value1, value2

Also remove empty arrays [].

Code

def clean_json_string(value):
  
    if pd.isna(value):
        return np.nan
    
    if not isinstance(value, str):
        return value
    
    value = value.strip()
    
    # Handle empty list or [None]
    if value in ['[]', '[None]', '[\n]', '', '[ ]']:
        return np.nan
    
    # Check if it's a JSON-like array
    if value.startswith('[') and value.endswith(']'):
        # Remove brackets
        cleaned = value[1:-1]
        
        # Remove \n, extra spaces, quotes
        cleaned = re.sub(r'\\n', '', cleaned)
        cleaned = re.sub(r'\n', '', cleaned)
        cleaned = re.sub(r'"', '', cleaned)
        
        # Split by comma and clean each item
        items = [item.strip() for item in cleaned.split(',') if item.strip()]
        
        if not items:
            return np.nan
        
        return ', '.join(items)
    
    return value.strip()


# Columns to clean
json_columns = [
    'SKILLS_NAME', 
    'SOFTWARE_SKILLS_NAME',
    'SOURCE_TYPES',
    'REMOTE_TYPE_NAME',
    'EMPLOYMENT_TYPE_NAME',
    'MIN_EDULEVELS_NAME'
]

# Apply cleaning
for col in json_columns:
    if col in df.columns:
        print(f"Cleaning: {col}")
        df[col] = df[col].apply(clean_json_string)

Cleaning: SKILLS_NAME
Cleaning: SOFTWARE_SKILLS_NAME
Cleaning: SOFTWARE_SKILLS_NAME
Cleaning: SOURCE_TYPES
Cleaning: SOURCE_TYPES
Cleaning: REMOTE_TYPE_NAME
Cleaning: EMPLOYMENT_TYPE_NAME
Cleaning: MIN_EDULEVELS_NAME
Cleaning: REMOTE_TYPE_NAME
Cleaning: EMPLOYMENT_TYPE_NAME
Cleaning: MIN_EDULEVELS_NAME

4.1 Clean BODY Column

Remove newlines, HTML artifacts, and junk descriptions that are scraped webpage content rather than actual job descriptions.

Code

# Clean BODY column
def clean_body(text):
    if pd.isna(text):
        return np.nan
    
    if not isinstance(text, str):
        return text
    
    # Remove \n and excessive whitespace
    cleaned = re.sub(r'\n+', ' ', text)
    cleaned = re.sub(r'\s+', ' ', cleaned)
    cleaned = cleaned.strip()
    
    return cleaned if cleaned else np.nan

# Junk patterns to filter out (scraped webpage artifacts, not real job descriptions)
junk_patterns = [
    r'^Find jobs\s*Search\s*Enter any',  # Scraped job board UI
    r'^Search for jobs',
    r'^Loading\.\.\.',
    r'^Please wait',
    r'^Click here to',
    r'^Apply now',
]

# Apply cleaning
print(f"BODY column before cleaning: {df['BODY'].notna().sum()} non-null values")

df['BODY'] = df['BODY'].apply(clean_body)

# Remove junk descriptions
for pattern in junk_patterns:
    mask = df['BODY'].str.contains(pattern, case=False, na=False, regex=True)
    junk_count = mask.sum()
    if junk_count > 0:
        print(f"Removing {junk_count} rows matching junk pattern: {pattern[:40]}...")
        df.loc[mask, 'BODY'] = np.nan

# Remove very short descriptions (likely incomplete)
short_mask = df['BODY'].str.len() < 50
short_count = short_mask.sum()
if short_count > 0:
    print(f"Setting {short_count} very short descriptions (<50 chars) to NaN")
    df.loc[short_mask, 'BODY'] = np.nan

print(f"\nBODY column after cleaning: {df['BODY'].notna().sum()} non-null values")
print(f"\nSample cleaned BODY (first 200 chars):")
sample = df['BODY'].dropna().iloc[0]
print(sample[:200] + "..." if len(str(sample)) > 200 else sample)

BODY column before cleaning: 57220 non-null values
Removing 207 rows matching junk pattern: ^Find jobs\s*Search\s*Enter any...
Removing 4 rows matching junk pattern: ^Loading\.\.\....
Removing 207 rows matching junk pattern: ^Find jobs\s*Search\s*Enter any...
Removing 4 rows matching junk pattern: ^Loading\.\.\....
Setting 29 very short descriptions (<50 chars) to NaN

BODY column after cleaning: 56980 non-null values

Sample cleaned BODY (first 200 chars):
31-May-2024 Enterprise Analyst (II-III) Merchandising El Dorado Arkansas Job Posting GENERAL DESCRIPTION OF POSITION Performs business analysis using various techniques, e.g. statistical analysis, exp...
Setting 29 very short descriptions (<50 chars) to NaN

BODY column after cleaning: 56980 non-null values

Sample cleaned BODY (first 200 chars):
31-May-2024 Enterprise Analyst (II-III) Merchandising El Dorado Arkansas Job Posting GENERAL DESCRIPTION OF POSITION Performs business analysis using various techniques, e.g. statistical analysis, exp...

5. Excluding Unknown Values

Code

df = df[~df['NAICS_2022_2_NAME'].str.contains('Unclassified', na=False)].copy()
df = df[~df['COMPANY_NAME'].str.contains('Unclassified', case=False, na=False)].copy()
df = df[~df['TITLE_NAME'].str.contains('Unclassified', case=False, na=False)].copy()

6. Handling Missing Values

6.1 Salary

Code


print("Missing salary values BEFORE imputation:")
print(f"  SALARY: {df['SALARY'].isna().sum()}")
print(f"  SALARY_FROM: {df['SALARY_FROM'].isna().sum()}")
print(f"  SALARY_TO: {df['SALARY_TO'].isna().sum()}")

salary_cols = ['SALARY', 'SALARY_FROM', 'SALARY_TO']

for col in salary_cols:
    if col in df.columns:
        median_by_occupation = df.groupby('LOT_V6_OCCUPATION_NAME')[col].transform('median')
        
        df[col] = df[col].fillna(median_by_occupation)



for col in salary_cols:
    if col in df.columns:
        remaining_na = df[col].isna().sum()
        if remaining_na > 0:
            overall_median = df[col].median()
            df[col] = df[col].fillna(overall_median)
            print(f"\nFilled {remaining_na} remaining {col} NaN with overall median: {overall_median}")

print("\nFinal missing salary values:")
print(f"  SALARY: {df['SALARY'].isna().sum()}")
print(f"  SALARY_FROM: {df['SALARY_FROM'].isna().sum()}")
print(f"  SALARY_TO: {df['SALARY_TO'].isna().sum()}")

Missing salary values BEFORE imputation:
  SALARY: 26716
  SALARY_FROM: 25621
  SALARY_TO: 25621

Final missing salary values:
  SALARY: 0
  SALARY_FROM: 0
  SALARY_TO: 0

6.2 SKILLS

Code

before = len(df)

df = df.dropna(subset=['SKILLS_NAME'])

after = len(df)
print(f"Removed {before - after} rows with no skills data")
print(f"Remaining rows: {after}")

Removed 197 rows with no skills data
Remaining rows: 47513

We are dropping these rows since we are going to be analyze the skills required for these jobs.

6.3 REMOTE_TYPE_NAME AND SOFTWARE_SKILLS_NAME

Code

categorical_fills = {
    'REMOTE_TYPE_NAME': 'Not Specified',
    'SOFTWARE_SKILLS_NAME': 'Not Listed'
}

for col, fill_value in categorical_fills.items():
    if col in df.columns:
        df[col] = df[col].fillna(fill_value)

6.4 MIN_YEARS_EXPERIENCE

Code

#  indicator for missing experience
df['EXPERIENCE_SPECIFIED'] = df['MIN_YEARS_EXPERIENCE'].notna().astype(int)


df['MIN_YEARS_EXPERIENCE'] = df['MIN_YEARS_EXPERIENCE'].fillna(0)

We are not dropping thes rows, instead we are using another column to specify whether the experience was given or not, this method helps us to preserve our data since there are a lot of data points where minimum experience is not specified.

7. Table Order

Code

column_order = [
    'TITLE_NAME', 'COMPANY_NAME', 'BODY', 'POSTED', 'DURATION',
    'SALARY', 'SALARY_FROM', 'SALARY_TO',
    'STATE_NAME', 'CITY_NAME',
    'REMOTE_TYPE_NAME', 'EMPLOYMENT_TYPE_NAME',
    'MIN_YEARS_EXPERIENCE','EXPERIENCE_SPECIFIED', 'MIN_EDULEVELS_NAME',
    'SKILLS_NAME', 'SOFTWARE_SKILLS_NAME',
    'SOURCE_TYPES', 'LOT_V6_OCCUPATION_NAME', 'NAICS_2022_2_NAME'
]
df = df[column_order]

8. Cleaned Dataset summary

Code

df.shape

(47513, 20)

Code

df.isna().sum()

TITLE_NAME                  0
COMPANY_NAME                0
BODY                      156
POSTED                      0
DURATION                    0
SALARY                      0
SALARY_FROM                 0
SALARY_TO                   0
STATE_NAME                  0
CITY_NAME                   0
REMOTE_TYPE_NAME            0
EMPLOYMENT_TYPE_NAME        0
MIN_YEARS_EXPERIENCE        0
EXPERIENCE_SPECIFIED        0
MIN_EDULEVELS_NAME          0
SKILLS_NAME                 0
SOFTWARE_SKILLS_NAME        0
SOURCE_TYPES                0
LOT_V6_OCCUPATION_NAME      0
NAICS_2022_2_NAME           0
dtype: int64

Code

df.describe()

	POSTED	DURATION	SALARY	SALARY_FROM	SALARY_TO	MIN_YEARS_EXPERIENCE	EXPERIENCE_SPECIFIED
count	47513	47513.000000	47513.000000	47513.000000	47513.000000	47513.000000	47513.000000
mean	2024-07-10 14:42:13.251952128	35.637426	111674.426936	87664.599562	132083.203544	3.494580	0.692400
min	2024-05-01 00:00:00	0.000000	20583.000000	10230.000000	11148.000000	0.000000	0.000000
25%	2024-06-04 00:00:00	14.000000	96008.000000	76960.000000	109100.000000	0.000000	0.000000
50%	2024-07-09 00:00:00	31.000000	105000.000000	81000.000000	122000.000000	3.000000	1.000000
75%	2024-08-16 00:00:00	60.000000	125900.000000	96000.000000	156800.000000	5.000000	1.000000
max	2024-09-30 00:00:00	119.000000	500000.000000	800000.000000	950000.000000	15.000000	1.000000
std	NaN	24.027774	30031.592449	25934.636196	43204.011452	3.464785	0.461505

Code

display(df.head())

df.reset_index(drop=True, inplace=True)
df.to_csv('data/lightcast_cleaned.csv', index=False)

	TITLE_NAME	COMPANY_NAME	BODY	POSTED	DURATION	SALARY	SALARY_FROM	SALARY_TO	STATE_NAME	CITY_NAME	REMOTE_TYPE_NAME	EMPLOYMENT_TYPE_NAME	MIN_YEARS_EXPERIENCE	EXPERIENCE_SPECIFIED	MIN_EDULEVELS_NAME	SKILLS_NAME	SOFTWARE_SKILLS_NAME	SOURCE_TYPES	LOT_V6_OCCUPATION_NAME	NAICS_2022_2_NAME
0	Enterprise Analysts	Murphy USA	31-May-2024 Enterprise Analyst (II-III) Mercha...	2024-06-02	6	125900.0	96000.0	156800.0	Arkansas	El Dorado, AR	Not Specified	Full-time (> 32 hours)	2.0	1	Bachelor's degree	Merchandising, Mathematics, Presentations, Pre...	SQL (Programming Language), Power BI	Company	Business Intelligence Analyst	Retail Trade
1	Oracle Consultants	Smx Corporation Limited	Oracle Consultant - Reports (3592) at SMX in A...	2024-06-02	60	125900.0	96000.0	156800.0	Maine	Augusta, ME	Remote	Full-time (> 32 hours)	3.0	1	No Education Listed	Procurement, Financial Statements, Oracle Busi...	Oracle Business Intelligence (BI) / OBIA, Orac...	Job Board	Business Intelligence Analyst	Administrative and Support and Waste Managemen...
2	Data Analysts	Sedgwick	Taking care of people is at the heart of every...	2024-06-02	35	96008.0	76960.0	109100.0	Texas	Dallas, TX	Not Specified	Full-time (> 32 hours)	5.0	1	Bachelor's degree	Management, Exception Reporting, Report Writin...	Microsoft Office	Job Board	Data / Data Mining Analyst	Finance and Insurance
3	Management Analysts	Wells Fargo	About this role: Wells Fargo is looking for a ...	2024-06-02	48	96008.0	76960.0	109100.0	Arizona	Phoenix, AZ	Not Specified	Full-time (> 32 hours)	3.0	1	No Education Listed	Exit Strategies, Reliability, User Story, Mana...	SAS (Software), Google Cloud Platform (GCP)	Job Board	Data / Data Mining Analyst	Finance and Insurance
5	Lead Data Analysts	Lumen Technologies	About Lumen Lumen connects the world. We are i...	2024-06-02	10	110155.0	94420.0	125890.0	Arkansas	[Unknown City], AR	Remote	Full-time (> 32 hours)	0.0	0	Bachelor's degree	Power BI, Presentations, Data Reporting, Qlik ...	Power BI, Qlik Sense (Data Analytics Software)...	Job Board	Data / Data Mining Analyst	Information