erDiagram
Jobs ||--o{ Job_Skills : requires
Skills ||--o{ Job_Skills : includes
Jobs {
string job_link PK
string job_title
string company
string job_location
string job_level
string job_type
}
Skills {
int skill_id PK
string skill_name
}
Job_Skills {
string job_link PK,FK
int skill_id PK,FK
}
Most Valued Data Science Skills
1 Introduction
The goal of this project is to investigate which data science skills are most valued in the job market.
Our main research question is: Which data science skills are most frequently requested by employers, and how do these requirements differ across data science roles?
We will analyze job postings to identify the most frequently requested skills and compare skill demand across Data Analyst, Data Engineer, and Data Scientist positions, as well as different job levels.
We will use the frequency of skills mentioned in job postings as our measure of skill demand. This will help us understand which skills employers commonly request.
Group Members: Ozge Gundogan, Antik Salimullah, Andre Thomson
2 Planned Approach
We will use the “Data Science Job Postings & Skills (2024)” dataset from Kaggle for this project.
Data Source: https://www.kaggle.com/datasets/asaniczka/data-science-job-postings-and-skills
We selected this dataset because it contains information about job postings, required skills, job titles, companies, and locations. The dataset consists of three CSV files: job_postings.csv, job_skills.csv, and job_summary.csv. We will primarily use job_postings.csv and job_skills.csv for our analysis.
We will download the CSV files from Kaggle and load them into R using the read.csv() function. We will then examine the structure of the datasets and identify missing values, empty fields, and duplicate records. We will separate comma-separated skills into individual rows, standardize skill names, and remove duplicate job-skill combinations. We will also examine the number of skills listed in each job posting to identify potential outliers.
We will use SQLite to organize the cleaned data into three related tables:
- Jobs: Stores information about job postings.
- Skills: Stores unique skills.
- Job_Skills: Connects job postings with their required skills.
We will use job_link and skill_id to connect the three tables and represent the many-to-many relationship between jobs and skills. The ER diagram below illustrates our proposed database structure and key relationships.
We will use SQL to identify the ten most frequently requested skills. We will also compare skill demand across different job levels and three job categories: Data Analyst, Data Engineer, and Data Scientist. We will calculate percentages when comparing groups with different numbers of job postings and use the ggplot2 package in R to visualize our findings.
We plan to use Gmail for group communication and GitHub for sharing code, documentation, and version control.
3 Anticipated Challenges
One possible challenge is handling missing or incomplete data. Some job postings may contain empty fields or missing skill information.
Another challenge is cleaning and standardizing skill names. Since multiple skills are stored in a single column, we will need to separate them into individual records, standardize capitalization, and remove duplicate job-skill combinations.
4 Code Base
4.1 Required Packages
We will use R packages to load, clean, transform, and analyze the datasets.
library(tidyr)
library(dplyr)
Attaching package: 'dplyr'The following objects are masked from 'package:stats':
filter, lagThe following objects are masked from 'package:base':
intersect, setdiff, setequal, unionlibrary(ggplot2)4.2 Load the Datasets
We will load the two CSV files from GitHub into R using read.csv().
job_postings_url <- "https://raw.githubusercontent.com/OzgeG01/DATA607-Assignment/refs/heads/main/job_postings.csv"
job_skills_url <- "https://raw.githubusercontent.com/OzgeG01/DATA607-Assignment/refs/heads/main/job_skills.csv"
job_postings <- read.csv(job_postings_url)
job_skills <- read.csv(job_skills_url)
head(job_postings) job_link
1 https://www.linkedin.com/jobs/view/senior-machine-learning-engineer-at-jobs-for-humanity-3804053819
2 https://www.linkedin.com/jobs/view/principal-software-engineer-ml-accelerators-at-aurora-3703455068
3 https://www.linkedin.com/jobs/view/senior-etl-data-warehouse-specialist-at-adame-services-llc-3765023888
4 https://www.linkedin.com/jobs/view/senior-data-warehouse-developer-architect-at-morph-enterprise-3794602483
5 https://www.linkedin.com/jobs/view/lead-data-engineer-at-dice-3805948138
6 https://www.linkedin.com/jobs/view/senior-data-engineer-at-university-of-chicago-3798206502
last_processed_time last_status got_summary got_ner
1 2024-01-21 08:08:48.031964+00 Finished NER t t
2 2024-01-20 04:02:12.331406+00 Finished NER t t
3 2024-01-21 08:08:31.941595+00 Finished NER t t
4 2024-01-20 15:30:55.796572+00 Finished NER t t
5 2024-01-21 08:08:58.312124+00 Finished NER t t
6 2024-01-21 07:14:11.378097+00 Finished NER t t
is_being_worked job_title
1 f Senior Machine Learning Engineer
2 f Principal Software Engineer, ML Accelerators
3 f Senior ETL Data Warehouse Specialist
4 f Senior Data Warehouse Developer / Architect
5 f Lead Data Engineer
6 f Senior Data Engineer
company job_location first_seen search_city
1 Jobs for Humanity New Haven, CT 2024-01-14 East Haven
2 Aurora San Francisco, CA 2024-01-14 El Cerrito
3 Adame Services LLC New York, NY 2024-01-14 Middletown
4 Morph Enterprise Harrisburg, PA 2024-01-12 Lebanon
5 Dice Plano, TX 2024-01-14 McKinney
6 University of Chicago Chicago, IL 2024-01-14 East Chicago
search_country search_position job_level job_type
1 United States Agricultural-Research Engineer Mid senior Onsite
2 United States Set-Key Driver Mid senior Onsite
3 United States Technical Support Specialist Associate Onsite
4 United States Architect Mid senior Onsite
5 United States Maintenance Data Analyst Mid senior Onsite
6 United States Data Base Administrator Mid senior Onsitehead(job_skills) job_link
1 https://www.linkedin.com/jobs/view/senior-machine-learning-engineer-at-jobs-for-humanity-3804053819
2 https://www.linkedin.com/jobs/view/principal-software-engineer-ml-accelerators-at-aurora-3703455068
3 https://www.linkedin.com/jobs/view/senior-etl-data-warehouse-specialist-at-adame-services-llc-3765023888
4 https://www.linkedin.com/jobs/view/senior-data-warehouse-developer-architect-at-morph-enterprise-3794602483
5 https://www.linkedin.com/jobs/view/lead-data-engineer-at-dice-3805948138
6 https://www.linkedin.com/jobs/view/senior-data-engineer-at-university-of-chicago-3798206502
job_skills
1 Machine Learning, Programming, Python, Scala, Java, Data Engineering, Distributed Computing, Statistical Modeling, Optimization, Data Pipelines, Cloud Computing, DevOps, Software Development, Data Gathering, Data Preparation, Data Visualization, Machine Learning Frameworks, scikitlearn, PyTorch, Dask, Spark, TensorFlow, Distributed File Systems, Multi node Database Paradigms, Open Source ML Software, Responsible AI, Explainable AI
2 C++, Python, PyTorch, TensorFlow, MXNet, CUDA, OpenCL, OpenVX, Halide, SIMD programming models, MLspecific accelerators, Linux/unix environments, Deep learning frameworks, Computer vision deep learning models, ML software and hardware technology, Inference on edge platforms, Cloud ML training pipelines, HPC experience, Performance troubleshooting, Profiling, Roofline model, Analytical skills, Communication skills
3 ETL, Data Integration, Data Transformation, Data Warehousing, Business Intelligence, Data Modeling, Data Architecture, Data Quality, Data Validation, Data Cleansing, Performance Optimization, Performance Tuning, Troubleshooting, Documentation, Reporting, Data Analysis, Collaboration, Communication, SQL, Informatica, Talend, Apache NiFi, AWS Redshift, Azure SQL Data Warehouse, Financial/Banking, CloudBased Data Platforms, Regulatory Compliance
4 Data Lakes, Data Bricks, Azure Data Factory Pipelines, Spark, Python, Business Intelligence, Data Warehouse, SQL Server, Azure, ETL/ELT, SQL Server Integration Services, TSQL, Data Formatting, Data Capture, Data Search, Data Retrieval, Data Extraction, Data Classification, Information Filtering, Data Mining Architectures, Modeling Standards, Reporting, Data Analysis Methodologies, Data Engineering, Database File Systems Optimization, API's, Analytics as a Service, Relational Databases, Dimensional Databases, Entity Relationships, Data Warehousing, Facts, Dimensions, Star Schema Concepts, Star Schema Terminology, Project Management, Organizational Skills, Collaboration, Communication, Technical Presentaion Skills, 12+ Years of Relevant Experience
5 Java, Scala, Python, RDBMS, NoSQL, Redshift, Snowflake, Unit testing, Agile engineering, Big data technologies, Cloud computing (AWS Microsoft Azure Google Cloud), Distributed data/computing tools (MapReduce Hadoop Hive EMR Kafka Spark Gurobi MySQL), Realtime data and streaming applications, NoSQL implementation (Mongo Cassandra), Data warehousing (Redshift Snowflake), UNIX/Linux, Shell scripting
6 Data Warehouse (DW), Extract/Transform/Load (ETL), Oracle, VPD, MuleSoft, Cloudera Apache Hadoop Ecosystem, Hadoop, Java, Python, R, AI/ML, Predictive Analytics, Business Objects, Tableau, OBIA/OBIEE, Oracle Cloud Financials, Power Designer, Erwin, UNIX, ODBC, JDBC, Perl DBI, Shell Scripting, PL/SQL, SQL Developer, SQL Plus, SQL Loader, TOAD, Data Profiling, SourceTarget Mapping, Transformations, Business Rules, OWB, ODI, Data Stage, Informatica, SQL, Unix Server, Windows Workstation, Windows Server, Microsoft Office, Excel, Analytic Skills, ProblemSolving, Communication Skills, Teamwork, Accountability, Attention to Detail, Accuracy4.3 Examining Dataset Structure
Before cleaning the data, we will examine the structure of both datasets using the str() function. This will help us understand the number of observations, variable names, data types, and sample values.
str(job_postings)'data.frame': 12217 obs. of 15 variables:
$ job_link : chr "https://www.linkedin.com/jobs/view/senior-machine-learning-engineer-at-jobs-for-humanity-3804053819" "https://www.linkedin.com/jobs/view/principal-software-engineer-ml-accelerators-at-aurora-3703455068" "https://www.linkedin.com/jobs/view/senior-etl-data-warehouse-specialist-at-adame-services-llc-3765023888" "https://www.linkedin.com/jobs/view/senior-data-warehouse-developer-architect-at-morph-enterprise-3794602483" ...
$ last_processed_time: chr "2024-01-21 08:08:48.031964+00" "2024-01-20 04:02:12.331406+00" "2024-01-21 08:08:31.941595+00" "2024-01-20 15:30:55.796572+00" ...
$ last_status : chr "Finished NER" "Finished NER" "Finished NER" "Finished NER" ...
$ got_summary : chr "t" "t" "t" "t" ...
$ got_ner : chr "t" "t" "t" "t" ...
$ is_being_worked : chr "f" "f" "f" "f" ...
$ job_title : chr "Senior Machine Learning Engineer" "Principal Software Engineer, ML Accelerators" "Senior ETL Data Warehouse Specialist" "Senior Data Warehouse Developer / Architect" ...
$ company : chr "Jobs for Humanity" "Aurora" "Adame Services LLC" "Morph Enterprise" ...
$ job_location : chr "New Haven, CT" "San Francisco, CA" "New York, NY" "Harrisburg, PA" ...
$ first_seen : chr "2024-01-14" "2024-01-14" "2024-01-14" "2024-01-12" ...
$ search_city : chr "East Haven" "El Cerrito" "Middletown" "Lebanon" ...
$ search_country : chr "United States" "United States" "United States" "United States" ...
$ search_position : chr "Agricultural-Research Engineer" "Set-Key Driver" "Technical Support Specialist" "Architect" ...
$ job_level : chr "Mid senior" "Mid senior" "Associate" "Mid senior" ...
$ job_type : chr "Onsite" "Onsite" "Onsite" "Onsite" ...The job_postings dataset contains 12,217 observations and 15 variables. All variables are stored as character values. Key variables for our analysis include job links, job titles, companies, job locations, job levels, and job types.
str(job_skills)'data.frame': 12217 obs. of 2 variables:
$ job_link : chr "https://www.linkedin.com/jobs/view/senior-machine-learning-engineer-at-jobs-for-humanity-3804053819" "https://www.linkedin.com/jobs/view/principal-software-engineer-ml-accelerators-at-aurora-3703455068" "https://www.linkedin.com/jobs/view/senior-etl-data-warehouse-specialist-at-adame-services-llc-3765023888" "https://www.linkedin.com/jobs/view/senior-data-warehouse-developer-architect-at-morph-enterprise-3794602483" ...
$ job_skills: chr "Machine Learning, Programming, Python, Scala, Java, Data Engineering, Distributed Computing, Statistical Modeli"| __truncated__ "C++, Python, PyTorch, TensorFlow, MXNet, CUDA, OpenCL, OpenVX, Halide, SIMD programming models, MLspecific acce"| __truncated__ "ETL, Data Integration, Data Transformation, Data Warehousing, Business Intelligence, Data Modeling, Data Archit"| __truncated__ "Data Lakes, Data Bricks, Azure Data Factory Pipelines, Spark, Python, Business Intelligence, Data Warehouse, SQ"| __truncated__ ...The job_skills dataset contains 12,217 observations and 2 variables: job_link and job_skills. Both variables are stored as character values. The job_skills column contains multiple skills separated by commas, which will need to be separated into individual records during data cleaning.
4.4 Checking Missing Values
Before cleaning the datasets, we will check for missing values in each column. We will examine both NA values and empty strings because missing information may be represented in different ways.
colSums(is.na(job_postings)) job_link last_processed_time last_status got_summary
0 0 0 0
got_ner is_being_worked job_title company
0 0 0 0
job_location first_seen search_city search_country
0 0 0 0
search_position job_level job_type
0 0 0 colSums(is.na(job_skills)) job_link job_skills
0 0 The results show that neither dataset contains any NA values.
colSums(job_postings == "", na.rm = TRUE) job_link last_processed_time last_status got_summary
0 0 0 0
got_ner is_being_worked job_title company
0 0 0 0
job_location first_seen search_city search_country
1 0 0 0
search_position job_level job_type
0 0 0 colSums(job_skills == "", na.rm = TRUE) job_link job_skills
0 5 The empty string check identified one missing job location in the job_postings dataset and five missing skill entries in the job_skills dataset. All other columns contain no empty strings.
4.5 Handling Missing Values
We identified one empty value in the job_location column. Since our analysis focuses on the skills requested by employers rather than job locations, this missing value does not affect our main analysis. Therefore, instead of removing the job posting and losing its skill information, we will replace the empty location with “Unknown”.
job_postings$job_location[job_postings$job_location == ""] <- "Unknown"We identified five job postings with missing skill information. Since our analysis focuses on identifying the most frequently requested skills, we will exclude these five postings from both datasets used for analysis. Before removing these records, we will identify their corresponding job_link values. This will allow us to locate the same job postings in the job_postings dataset and remove them from both datasets.
empty_skills <- job_skills[job_skills$job_skills == "", ]
empty_skills job_link
671 https://www.linkedin.com/jobs/view/mltc-nurse-care-manager-brooklyn-office-at-riverspring-living-3781600873
7899 https://www.linkedin.com/jobs/view/high-school-speech-language-pathologist-fmla-at-anthromed-education-3805736339
9599 https://uk.linkedin.com/jobs/view/notam-aviation-data-operator-at-nats-3800905866
10841 https://www.linkedin.com/jobs/view/mltc-nurse-care-manager-bilingual-russian-brooklyn-office-at-riverspring-living-3718500026
10847 https://www.linkedin.com/jobs/view/medical-lab-technician-mlt-prn-at-lifepoint-health%C2%AE-3651076194
job_skills
671
7899
9599
10841
10847 After identifying the five records with missing skill information, we will use their job_link values to remove the corresponding job postings from both datasets. We will create cleaned copies to preserve the original data.
job_skills_clean <- job_skills[
!(job_skills$job_link %in% empty_skills$job_link),
]
job_postings_clean <- job_postings[
!(job_postings$job_link %in% empty_skills$job_link),
]After removing the five job postings with missing skill information, we will check both cleaned datasets to verify that no empty values remain.
colSums(job_postings_clean == "", na.rm = TRUE) job_link last_processed_time last_status got_summary
0 0 0 0
got_ner is_being_worked job_title company
0 0 0 0
job_location first_seen search_city search_country
0 0 0 0
search_position job_level job_type
0 0 0 colSums(job_skills_clean == "", na.rm = TRUE) job_link job_skills
0 0 The results confirm that no empty strings remain in either cleaned dataset.
4.6 Checking Duplicate Records
We will check for duplicate job_link values in both cleaned datasets because job_link will be used to connect job postings with their required skills. Duplicate links could cause some skills to be counted more than once and affect the accuracy of our analysis.
sum(duplicated(job_postings_clean$job_link))[1] 0sum(duplicated(job_skills_clean$job_link))[1] 0The results show that there are no duplicate job_link values in either cleaned dataset, each job posting has a unique link.
4.7 Separating Job Skills
The job_skills column contains multiple skills separated by commas. We will separate these skills into individual rows so that each row represents one skill associated with a job posting. This will allow us to count and analyze the frequency of each skill.
job_skills_long <- job_skills_clean %>%
separate_rows(job_skills, sep = ",")
head(job_skills_long, 10)# A tibble: 10 × 2
job_link job_skills
<chr> <chr>
1 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… "Machine …
2 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… " Program…
3 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… " Python"
4 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… " Scala"
5 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… " Java"
6 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… " Data En…
7 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… " Distrib…
8 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… " Statist…
9 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… " Optimiz…
10 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… " Data Pi…The results show that the skills were successfully separated into individual rows. Each row now contains one job link and one skill.
4.8 Fixing UTF-8 Encoding Issues
While standardizing skill names, we encountered an error caused by invalid UTF-8 characters in the dataset. To resolve this issue, we will convert the text to UTF-8 encoding and remove any characters that cannot be converted.
job_skills_long <- job_skills_long %>%
mutate(
job_skills = iconv(job_skills, from = "", to = "UTF-8", sub = ""),
job_skills = tolower(trimws(job_skills))
)
head(job_skills_long, 10)# A tibble: 10 × 2
job_link job_skills
<chr> <chr>
1 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… machine l…
2 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… programmi…
3 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… python
4 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… scala
5 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… java
6 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… data engi…
7 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… distribut…
8 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… statistic…
9 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… optimizat…
10 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… data pipe…4.9 Standardizing Skill Names
We will remove extra spaces at the beginning and end of skill names and convert all skill names to lowercase to ensure accurate counting. This will prevent the same skill from being counted separately due to differences in capitalization or spacing.
job_skills_long <- job_skills_long %>%
mutate(job_skills = tolower(trimws(job_skills)))
head(job_skills_long, 10)# A tibble: 10 × 2
job_link job_skills
<chr> <chr>
1 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… machine l…
2 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… programmi…
3 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… python
4 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… scala
5 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… java
6 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… data engi…
7 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… distribut…
8 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… statistic…
9 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… optimizat…
10 https://www.linkedin.com/jobs/view/senior-machine-learning-engine… data pipe…The results show that the skill names were successfully converted to lowercase and extra spaces were removed.