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 requested most often in the supplied job-posting dataset.
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 technical skills and compare skill demand across Data Analyst, Data Engineer, and Data Scientist positions.
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
Collaboration: We plan to use Gmail for group communication and GitHub for sharing code, documentation, and version control.
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. We will use two CSV files from this dataset for our analysis: job_postings.csv and job_skills.csv.
We will download the CSV files from Kaggle, upload them to GitHub, and load them into R using the read.csv() function. We will then examine the structure of the datasets and identify missing values and duplicate records. We will separate comma-separated skills stored in the same row into individual rows, standardize skill names, and check for 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 analyze the frequency of technical skills mentioned in job postings to identify the most frequently requested skills. We will also compare skill demand across three job categories: Data Analyst, Data Engineer, and Data Scientist. We will calculate the percentage of job postings requiring each skill within each job category to make comparisons more meaningful. We will then use the ggplot2 package in R to visualize the results.
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)
library(DBI)
library(RSQLite)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 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.
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…We will further standardize skill names by combining different expressions that refer to the same skill. For example, “powerbi” and “power bi” will be treated as one skill. This will help prevent duplicate categories and improve the accuracy of our skill frequency analysis.
job_skills_long <- job_skills_long %>%
mutate(
job_skills = case_when(
job_skills %in% c("powerbi", "power-bi", "power bi") ~ "power bi",
job_skills %in% c("communication skills", "communication") ~ "communication",
job_skills %in% c("ms excel", "microsoft excel", "excel") ~ "excel",
job_skills %in% c("ms sql server", "microsoft sql server") ~ "sql server",
job_skills %in% c("java script", "javascript") ~ "javascript",
job_skills %in% c("scikitlearn", "scikit learn", "scikit-learn") ~ "scikit-learn",
job_skills %in% c("data bricks", "databricks") ~ "databricks",
job_skills %in% c("tsql", "t-sql") ~ "t-sql",
job_skills %in% c("cloudbased data platforms", "cloud-based data platforms") ~ "cloud-based data platforms",
job_skills %in% c("mlspecific accelerators", "ml-specific accelerators") ~ "ml-specific accelerators",
job_skills %in% c("problem solving", "problemsolving", "problem-solving") ~ "problem solving",
TRUE ~ job_skills
)
)4.9 Removing Non-Skill Entries
Some entries in the skills dataset describe job requirements rather than actual skills, such as years of experience, work shifts, and educational qualifications. We will remove these entries from the skill-level data to improve the accuracy of our analysis.
rows_before <- nrow(job_skills_long)
job_skills_long <- job_skills_long %>%
filter(
!grepl("\\b(year|years)\\b.*\\bexperience\\b|\\bexperience\\b.*\\b(year|years)\\b",
job_skills),
!grepl("\\b(hour|hours)\\b.*\\bshift\\b|\\bshift\\b.*\\b(hour|hours)\\b",
job_skills),
!grepl(
"degree|diploma|bachelor|phd|doctorate|doctoral|baccalaureate|\\bmasters\\b|\\bged\\b",
job_skills
)
)
rows_before - nrow(job_skills_long)[1] 5331A total of 5,331 entries related to experience requirements, work shifts, and educational qualifications were removed from the skill-level dataset. This filtering step helps focus the analysis on actual skills rather than general job requirements.
4.10 Checking and Removing Duplicate Job-Skill Pairs After Transformation
We will check whether the same skill appears more than once for a single job posting. Duplicate job-skill pairs could cause some skills to be counted multiple times and affect the accuracy of our analysis.
sum(duplicated(job_skills_long))[1] 2749We identified 2,749 duplicate job-skill pairs. We will remove these duplicates to ensure that each skill is counted only once per job posting.
job_skills_long <- job_skills_long %>%
distinct(job_link, job_skills, .keep_all = TRUE)
sum(duplicated(job_skills_long))[1] 0After removing the duplicate job-skill pairs, the verification returned 0. This confirms that each skill now appears only once per job posting.
4.11 Checking Outliers in the Number of Skills per Job Posting
We will calculate the number of skills listed in each job posting and examine unusually high values. This will help us evaluate the quality of our data.
skills_per_job <- job_skills_long %>%
count(job_link, name = "skill_count")
summary(skills_per_job$skill_count) Min. 1st Qu. Median Mean 3rd Qu. Max.
1.00 17.00 23.00 25.13 30.00 218.00 The results show that job postings contain an average of 25.13 skills. The maximum is 218 skills, which is unusually high compared to the typical number of skills per posting. We will investigate potential outliers.
We will use the Interquartile Range (IQR) method to identify potential outliers. IQR measures the spread of the middle 50% of the data (Q3 - Q1). We will calculate the upper limit using Q3 + 1.5 * IQR and count how many job postings exceed this limit.
Q1 <- quantile(skills_per_job$skill_count, 0.25)
Q3 <- quantile(skills_per_job$skill_count, 0.75)
upper_limit <- unname(Q3 + 1.5 * (Q3 - Q1))
upper_limit[1] 49.5Using the IQR method, we calculated an upper limit of 49.5 skills per job posting. Job postings with more than 49.5 skills will be considered potential outliers. We will count how many job postings exceed the upper limit of 49.5 skills to determine the number of potential outliers in our dataset.
sum(skills_per_job$skill_count > upper_limit)[1] 468We identified 468 job postings with more than 49.5 skills. These postings are considered potential outliers based on the IQR method. However, a high number of skills does not necessarily indicate incorrect data, so we will examine these records before deciding whether to remove them.
skills_per_job %>%
arrange(desc(skill_count)) %>%
head(10)# A tibble: 10 × 2
job_link skill_count
<chr> <int>
1 https://www.linkedin.com/jobs/view/data-engineer-iv-max-digital-… 218
2 https://www.linkedin.com/jobs/view/manager-data-center-operation… 175
3 https://www.linkedin.com/jobs/view/manager-data-center-operation… 175
4 https://www.linkedin.com/jobs/view/sr-manager-data-access-monito… 157
5 https://www.linkedin.com/jobs/view/it-delivery-manager-%E2%80%93… 156
6 https://www.linkedin.com/jobs/view/critical-facilities-operation… 138
7 https://www.linkedin.com/jobs/view/u103-mls-field-mechanic-b-at-… 138
8 https://www.linkedin.com/jobs/view/data-reporting-and-analytics-… 134
9 https://www.linkedin.com/jobs/view/data-scientist-at-presbyteria… 124
10 https://www.linkedin.com/jobs/view/geospatial-intelligence-integ… 122The 10 job postings with the highest skill counts contain between 122 and 218 skills, which is considerably higher than the median of 23. We will examine these postings further before deciding whether to keep or remove them.
top_job <- skills_per_job %>%
arrange(desc(skill_count)) %>%
slice(1)
job_postings_clean %>%
filter(job_link == top_job$job_link) %>%
select(job_title, company, job_level) job_title company job_level
1 Data Engineer IV - Max Digital (Data Engineering) ACV Auctions Mid seniorjob_skills_long %>%
filter(job_link == top_job$job_link) %>%
select(job_skills) %>%
head(50)# A tibble: 50 × 1
job_skills
<chr>
1 computer science
2 information technology
3 computer information systems
4 management information systems
5 sql
6 etl
7 aws
8 mongodb
9 dbt
10 airflow
# ℹ 40 more rowsWe examined the job posting with the highest skill count (218 skills). The position is Data Engineer IV at ACV Auctions. The displayed skills include relevant technical skills such as SQL, AWS, and ETL. This suggests that the high skill count may reflect the broad technical requirements of the position rather than a data error.
We decided to retain all 468 potential outliers because unusually high skill counts do not necessarily indicate data errors. Removing these postings could result in the loss of potentially valuable information about skill demand.
4.12 Creating a Relational Database with SQLite
We will connect to a SQLite database to store our cleaned data in three normalized tables: Jobs, Skills, and Job_Skills. To avoid errors when running the code multiple times, we will remove any existing versions of these tables before creating them again with the updated data.
con <- dbConnect(RSQLite::SQLite(), "data_science_skills.sqlite")
dbExecute(con, "DROP TABLE IF EXISTS Job_Skills")[1] 0dbExecute(con, "DROP TABLE IF EXISTS Skills")[1] 0dbExecute(con, "DROP TABLE IF EXISTS Jobs")[1] 0dbListTables(con)character(0)The database was successfully prepared for rebuilding. The result character(0) confirms that no tables remain before creating the updated Jobs, Skills, and Job_Skills tables.
4.12.1 Enabling Foreign Key Constraints
Before creating the relational tables, we will enable foreign key constraints in SQLite to maintain referential integrity between the tables.
dbExecute(con, "PRAGMA foreign_keys = ON;")[1] 0The command executed successfully and returned 0, indicating that no rows were affected.
We will verify that foreign key constraints are enabled in our SQLite database.
dbGetQuery(con, "PRAGMA foreign_keys;") foreign_keys
1 1The result returned 1, confirming that foreign key constraints are enabled in our SQLite database.
4.12.2 Preparing the Jobs Data
We will prepare the Jobs table using the cleaned job postings dataset. This table will contain the job link, job title, company, location, job level, and job type. The job link will serve as the primary key because each job posting has a unique link.
jobs <- job_postings_clean %>%
select(job_link, job_title, company,
job_location, job_level, job_type)
head(jobs) 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_title company
1 Senior Machine Learning Engineer Jobs for Humanity
2 Principal Software Engineer, ML Accelerators Aurora
3 Senior ETL Data Warehouse Specialist Adame Services LLC
4 Senior Data Warehouse Developer / Architect Morph Enterprise
5 Lead Data Engineer Dice
6 Senior Data Engineer University of Chicago
job_location job_level job_type
1 New Haven, CT Mid senior Onsite
2 San Francisco, CA Mid senior Onsite
3 New York, NY Associate Onsite
4 Harrisburg, PA Mid senior Onsite
5 Plano, TX Mid senior Onsite
6 Chicago, IL Mid senior Onsite4.12.3 Creating the Jobs Table in SQLite
We will create the Jobs table in SQLite and define job_link as the primary key to ensure that each job posting is uniquely identified. Then, we will insert the cleaned job posting records into the table.
dbExecute(con, "
CREATE TABLE Jobs (
job_link TEXT PRIMARY KEY,
job_title TEXT,
company TEXT,
job_location TEXT,
job_level TEXT,
job_type TEXT
)
")[1] 0dbAppendTable(con, "Jobs", jobs)[1] 12212dbGetQuery(con, "SELECT COUNT(*) AS total_jobs FROM Jobs") total_jobs
1 12212The Jobs table was successfully created in SQLite with job_link as the primary key. A total of 12,212 job postings were inserted into the table.
4.12.4 Preparing the Skills Data
We will create the Skills table by extracting unique skill names from job_skills_long. Each skill will be assigned a unique skill_id, which will serve as the primary key in our relational database.
skills_table <- data.frame(
skill_id = seq_along(unique(job_skills_long$job_skills)),
skill_name = unique(job_skills_long$job_skills)
)
head(skills_table, 10) skill_id skill_name
1 1 machine learning
2 2 programming
3 3 python
4 4 scala
5 5 java
6 6 data engineering
7 7 distributed computing
8 8 statistical modeling
9 9 optimization
10 10 data pipelines4.12.5 Creating the Skills Table in SQLite
We will create the Skills table in SQLite using skill_id as the primary key. Then, we will insert the unique skill names into the table.
dbExecute(con, "
CREATE TABLE Skills (
skill_id INTEGER PRIMARY KEY,
skill_name TEXT NOT NULL UNIQUE
)
")[1] 0dbAppendTable(con, "Skills", skills_table)[1] 62752dbGetQuery(con, "SELECT COUNT(*) AS total_skills FROM Skills") total_skills
1 62752The Skills table was successfully created in SQLite with skill_id as the primary key. A total of 62,752 unique skills were inserted into the table.
4.12.6 Preparing the Job_Skills Data
We will create the Job_Skills table by matching each skill in job_skills_long with its corresponding skill_id from the Skills table. This table will connect job postings and skills using their identifiers.
job_skills_table <- job_skills_long %>%
left_join(skills_table, by = c("job_skills" = "skill_name")) %>%
select(job_link, skill_id)
head(job_skills_table, 10)# A tibble: 10 × 2
job_link skill_id
<chr> <int>
1 https://www.linkedin.com/jobs/view/senior-machine-learning-engineer… 1
2 https://www.linkedin.com/jobs/view/senior-machine-learning-engineer… 2
3 https://www.linkedin.com/jobs/view/senior-machine-learning-engineer… 3
4 https://www.linkedin.com/jobs/view/senior-machine-learning-engineer… 4
5 https://www.linkedin.com/jobs/view/senior-machine-learning-engineer… 5
6 https://www.linkedin.com/jobs/view/senior-machine-learning-engineer… 6
7 https://www.linkedin.com/jobs/view/senior-machine-learning-engineer… 7
8 https://www.linkedin.com/jobs/view/senior-machine-learning-engineer… 8
9 https://www.linkedin.com/jobs/view/senior-machine-learning-engineer… 9
10 https://www.linkedin.com/jobs/view/senior-machine-learning-engineer… 10The Job_Skills table was successfully created. Each row contains a job link and its corresponding skill ID, allowing us to connect job postings with their required skills.
4.12.7 Creating the Job_Skills Table in SQLite
We will create the Job_Skills table in SQLite using job_link and skill_id as foreign keys referencing the Jobs and Skills tables. Together, these columns will form a composite primary key to prevent duplicate job-skill pairs.
dbExecute(con, "
CREATE TABLE Job_Skills (
job_link TEXT NOT NULL,
skill_id INTEGER NOT NULL,
PRIMARY KEY (job_link, skill_id),
FOREIGN KEY (job_link) REFERENCES Jobs(job_link),
FOREIGN KEY (skill_id) REFERENCES Skills(skill_id)
)
")[1] 0dbAppendTable(con, "Job_Skills", job_skills_table)[1] 306870dbGetQuery(con, "SELECT COUNT(*) AS total_job_skills FROM Job_Skills") total_job_skills
1 306870The Job_Skills table was successfully created with a composite primary key and foreign keys referencing the Jobs and Skills tables. A total of 306,870 job-skill relationships were inserted.
4.12.8 Verifying Referential Integrity
We will verify referential integrity to ensure that all job links and skill IDs in the Job_Skills table correctly reference existing records in the Jobs and Skills tables.
dbGetQuery(con, "PRAGMA foreign_key_check;")[1] table rowid parent fkid
<0 rows> (or 0-length row.names)The foreign key check returned 0 rows, confirming that there are no foreign key violations in the database. All job-skill relationships correctly reference existing records in the Jobs and Skills tables.
4.13 Exploratory Data Analysis
4.13.1 Most Frequently Requested Technical Skills
To identify the most frequently requested technical skills, we will first exclude non-technical skills and general competencies from the dataset.
non_technical_skills <- c(
"communication",
"problem solving",
"project management",
"teamwork",
"analytical skills",
"attention to detail",
"collaboration",
"leadership",
"agile",
"troubleshooting",
"data science",
"computer science",
"time management",
"engineering",
"interpersonal skills"
)We will load the Jobs, Skills, and Job_Skills tables from our SQLite database into R.
jobs_r <- dbReadTable(con, "Jobs")
skills_r <- dbReadTable(con, "Skills")
job_skills_r <- dbReadTable(con, "Job_Skills")
total_jobs <- nrow(jobs_r)We will join the job-skill relationships with the skill names and exclude non-technical skills. Then, we will count the number of unique job postings requesting each skill, calculate their percentages, and select the 10 most frequently requested technical skills.
technical_skills <- job_skills_r %>%
left_join(skills_r, by = "skill_id") %>%
filter(!skill_name %in% non_technical_skills) %>%
group_by(skill_name) %>%
summarise(
job_count = n_distinct(job_link),
.groups = "drop"
) %>%
mutate(
percentage = round(100 * job_count / total_jobs, 2)
) %>%
arrange(desc(job_count), skill_name) %>%
slice_head(n = 10)
technical_skills %>%
mutate(percentage = sprintf("%.2f", percentage))# A tibble: 10 × 3
skill_name job_count percentage
<chr> <int> <chr>
1 python 4755 38.94
2 sql 4520 37.01
3 data analysis 3227 26.42
4 machine learning 2632 21.55
5 data visualization 2267 18.56
6 aws 1714 14.04
7 data engineering 1698 13.90
8 tableau 1671 13.68
9 r 1528 12.51
10 data modeling 1498 12.27 The results show that Python is the most frequently requested technical skill (38.94%), followed by SQL (37.01%), data analysis (26.42%), machine learning (21.55%), and data visualization (18.56%). Other frequently requested skills include AWS, data engineering, Tableau, R, and data modeling. Overall, these findings suggest that employers place strong emphasis on programming, data analysis, and data management capabilities.
We will use a horizontal bar chart to visualize the 10 most frequently requested technical skills. The percentages represent the proportion of job postings that mention each skill.
ggplot(
technical_skills,
aes(x = reorder(skill_name, percentage),
y = percentage,
fill = skill_name)
) +
geom_col() +
scale_fill_viridis_d(option = "turbo", guide = "none") +
geom_text(
aes(label = sprintf("%.2f%%", percentage)),
hjust = -0.2,
size = 3.5
) +
coord_flip() +
scale_y_continuous(
limits = c(0, 45)
) +
labs(
title = "Top 10 Most Requested Technical Skills",
x = "Technical Skill",
y = "Job Postings (%)"
) +
theme_minimal()
4.13.2 Technical Skill Demand by Job Role
We will classify job postings into three groups based on their job titles: Data Analyst, Data Scientist, and Data Engineer. Job titles containing these terms will be included regardless of experience level, such as Junior, Senior, or Lead. We will then count the number of job postings in each group.
role_counts <- jobs_r %>%
mutate(
job_role = case_when(
grepl("data analyst", job_title, ignore.case = TRUE) ~ "Data Analyst",
grepl("data scientist", job_title, ignore.case = TRUE) ~ "Data Scientist",
grepl("data engineer", job_title, ignore.case = TRUE) ~ "Data Engineer",
TRUE ~ NA_character_
)
) %>%
filter(!is.na(job_role)) %>%
count(job_role, name = "job_count") %>%
arrange(desc(job_count))
role_counts job_role job_count
1 Data Analyst 1888
2 Data Engineer 1751
3 Data Scientist 807The results show that Data Analyst has the highest number of job postings (1,888), followed by Data Engineer (1,751) and Data Scientist (807). We will use these three groups to compare technical skill requirements across different job roles.
4.13.2.1 Top 10 Technical Skills for Data Analyst
We will identify the 10 most frequently requested technical skills in Data Analyst job postings. Skill demand will be measured as the percentage of Data Analyst postings mentioning each skill, including Junior and Senior positions.
data_analyst_jobs <- jobs_r %>%
filter(grepl("data analyst", job_title, ignore.case = TRUE))
data_analyst_skills <- job_skills_r %>%
semi_join(data_analyst_jobs, by = "job_link") %>%
left_join(skills_r, by = "skill_id") %>%
filter(!skill_name %in% non_technical_skills) %>%
group_by(skill_name) %>%
summarise(
job_count = n_distinct(job_link),
.groups = "drop"
) %>%
mutate(
percentage = round(
100 * job_count / nrow(data_analyst_jobs), 2
)
) %>%
arrange(desc(job_count), skill_name) %>%
slice_head(n = 10)
data_analyst_skills %>%
mutate(percentage = sprintf("%.2f", percentage))# A tibble: 10 × 3
skill_name job_count percentage
<chr> <int> <chr>
1 data analysis 1307 69.23
2 sql 1157 61.28
3 data visualization 977 51.75
4 python 813 43.06
5 tableau 731 38.72
6 power bi 577 30.56
7 r 562 29.77
8 excel 519 27.49
9 data mining 366 19.39
10 data management 363 19.23 The results show that data analysis (69.23%) is the most frequently requested skill for Data Analyst positions, followed by SQL (61.28%) and data visualization (51.75%). Python (43.06%) and Tableau (38.72%) are also among the top five. Power BI, R, and Excel are frequently mentioned, highlighting the importance of data analysis, querying, and visualization skills for this role.
We will create a horizontal bar chart to visualize the 10 most frequently requested technical skills for Data Analyst positions. Each bar represents the percentage of Data Analyst job postings mentioning a particular skill.
ggplot(
data_analyst_skills,
aes(x = reorder(skill_name, percentage),
y = percentage,
fill = skill_name)
) +
geom_col() +
scale_fill_viridis_d(option = "turbo", guide = "none") +
geom_text(
aes(label = sprintf("%.2f%%", percentage)),
hjust = -0.1,
size = 3.5
) +
coord_flip() +
scale_y_continuous(
limits = c(0, 80),
expand = c(0, 0)
) +
labs(
title = "Top 10 Technical Skills for Data Analyst",
x = "Technical Skill",
y = "Data Analyst Job Postings (%)"
) +
theme_minimal()
4.13.2.2 Top 10 Technical Skills for Data Scientist
We will identify the 10 most frequently requested technical skills in Data Scientist job postings. Skill demand will be calculated as the percentage of Data Scientist postings mentioning each skill, including Junior and Senior positions.
data_scientist_jobs <- jobs_r %>%
filter(grepl("data scientist", job_title, ignore.case = TRUE))
data_scientist_skills <- job_skills_r %>%
semi_join(data_scientist_jobs, by = "job_link") %>%
left_join(skills_r, by = "skill_id") %>%
filter(!skill_name %in% non_technical_skills) %>%
group_by(skill_name) %>%
summarise(
job_count = n_distinct(job_link),
.groups = "drop"
) %>%
mutate(
percentage = round(
100 * job_count / nrow(data_scientist_jobs), 2
)
) %>%
arrange(desc(job_count), skill_name) %>%
slice_head(n = 10)
data_scientist_skills %>%
mutate(percentage = sprintf("%.2f", percentage))# A tibble: 10 × 3
skill_name job_count percentage
<chr> <int> <chr>
1 python 675 83.44
2 machine learning 594 73.42
3 sql 469 57.97
4 r 394 48.70
5 data visualization 307 37.95
6 statistics 295 36.46
7 data analysis 255 31.52
8 data mining 180 22.25
9 spark 179 22.13
10 tableau 175 21.63 The results show that Python (83.44%) is the most frequently requested technical skill for Data Scientist positions, followed by machine learning (73.42%) and SQL (57.97%). R (48.70%) and data visualization (37.95%) complete the top five. Statistics (36.46%) is also frequently requested, highlighting the importance of programming, machine learning, and statistical analysis for this role.
We will create a horizontal bar chart to visualize the 10 most frequently requested technical skills for Data Scientist positions. Each bar represents the percentage of Data Scientist job postings mentioning a particular skill.
ggplot(
data_scientist_skills,
aes(x = reorder(skill_name, percentage),
y = percentage,
fill = skill_name)
) +
geom_col() +
scale_fill_viridis_d(option = "turbo", guide = "none") +
geom_text(
aes(label = sprintf("%.2f%%", percentage)),
hjust = -0.1,
size = 3.5
) +
coord_flip() +
scale_y_continuous(
limits = c(0, 100),
expand = c(0, 0)
) +
labs(
title = "Top 10 Technical Skills for Data Scientist",
x = "Technical Skill",
y = "Data Scientist Job Postings (%)"
) +
theme_minimal()
4.13.2.3 Top 10 Technical Skills for Data Engineer
We will identify the 10 most frequently requested technical skills in Data Engineer job postings. Skill demand will be calculated as the percentage of Data Engineer postings mentioning each skill, including Junior and Senior positions.
data_engineer_jobs <- jobs_r %>%
filter(grepl("data engineer", job_title, ignore.case = TRUE))
data_engineer_skills <- job_skills_r %>%
semi_join(data_engineer_jobs, by = "job_link") %>%
left_join(skills_r, by = "skill_id") %>%
filter(!skill_name %in% non_technical_skills) %>%
group_by(skill_name) %>%
summarise(
job_count = n_distinct(job_link),
.groups = "drop"
) %>%
mutate(
percentage = round(
100 * job_count / nrow(data_engineer_jobs), 2
)
) %>%
arrange(desc(job_count), skill_name) %>%
slice_head(n = 10)
data_engineer_skills %>%
mutate(percentage = sprintf("%.2f", percentage))# A tibble: 10 × 3
skill_name job_count percentage
<chr> <int> <chr>
1 python 1288 73.47
2 sql 1106 63.09
3 data engineering 905 51.63
4 spark 715 40.79
5 java 662 37.76
6 aws 639 36.45
7 scala 635 36.22
8 snowflake 621 35.42
9 data warehousing 558 31.83
10 hadoop 554 31.60 The results show that Python (73.47%) is the most frequently requested technical skill for Data Engineer positions, followed by SQL (63.09%) and data engineering (51.63%). Spark (40.79%) and Java (37.76%) complete the top five. AWS, Scala, Snowflake, data warehousing, and Hadoop are also frequently requested, highlighting the importance of programming, cloud technologies, and large-scale data processing for this role.
We will create a horizontal bar chart to visualize the 10 most frequently requested technical skills for Data Engineer positions. Each bar represents the percentage of Data Engineer job postings mentioning a particular skill.
ggplot(
data_engineer_skills,
aes(x = reorder(skill_name, percentage),
y = percentage,
fill = skill_name)
) +
geom_col() +
scale_fill_viridis_d(option = "turbo", guide = "none") +
geom_text(
aes(label = sprintf("%.2f%%", percentage)),
hjust = -0.1,
size = 3.5
) +
coord_flip() +
scale_y_continuous(
limits = c(0, 90),
expand = c(0, 0)
) +
labs(
title = "Top 10 Technical Skills for Data Engineer",
x = "Technical Skill",
y = "Data Engineer Job Postings (%)"
) +
theme_minimal()
4.13.3 Comparing Technical Skills Across Job Roles
The results show both similarities and differences in technical skill demand across Data Analyst, Data Scientist, and Data Engineer positions.
Python and SQL are among the most frequently requested skills across all three roles. Python is particularly common in Data Scientist (83.44%) and Data Engineer (73.47%) postings, while SQL is frequently requested in Data Analyst (61.28%), Data Scientist (57.97%), and Data Engineer (63.09%) positions.
Data Analyst positions place greater emphasis on data analysis (69.23%), SQL (61.28%), data visualization (51.75%), Python (43.06%), and Tableau (38.72%). These are the five most frequently requested technical skills for this role, highlighting the importance of analyzing, querying, and visualizing data.
Data Scientist positions show the highest demand for Python (83.44%), followed by machine learning (73.42%), SQL (57.97%), R (48.70%), and data visualization (37.95%). These findings highlight the importance of programming, machine learning, and data analysis for this role.
Data Engineer positions most frequently request Python (73.47%), SQL (63.09%), data engineering (51.63%), Spark (40.79%), and Java (37.76%). These findings reflect the importance of programming, data infrastructure, and large-scale data processing for this role.
Overall, the findings suggest that while all three roles share important technical skills, their priorities differ. Data Analysts focus more on analysis and visualization, Data Scientists on machine learning and statistical methods, and Data Engineers on data infrastructure and processing technologies.
5 Limitations
This analysis has several limitations. First, the dataset contains job postings from 2024, so the results may not reflect current employer requirements.
Second, skill names were standardized during data cleaning, but some variations may still remain. Additionally, the classification of technical and non-technical skills was based on a predefined exclusion list, which may not capture every non-technical skill.
Finally, job postings were grouped by job title, including different experience levels. Therefore, the analysis does not distinguish between Junior, Senior, and other experience levels within each role.
6 Conclusion
This project explored which technical data science skills are most valued by employers and how skill requirements differ across job roles.
The overall analysis showed that Python and SQL were the most frequently requested technical skills, followed by data analysis, machine learning, and data visualization.
The role-specific analysis revealed differences in employer requirements. Data Analyst positions emphasized data analysis, SQL, and visualization tools. Data Scientist positions focused more on Python, machine learning, and statistical skills, while Data Engineer positions placed greater emphasis on programming, data infrastructure, and large-scale data processing technologies.
Overall, the findings suggest that Python and SQL are valuable across multiple data-related careers, while other technical skill requirements depend on the specific job role. Understanding these differences can help job seekers prioritize the skills most relevant to their career goals.
7 Citation
We used ChatGPT (GPT-5.6 Luna), developed by OpenAI, to help us understand some of the functions we used in this project and to check our work.
OpenAI. (2026). ChatGPT (GPT-5.6 Luna) [Large language model]. [https://chatgpt.com/ Accessed October 22, 2026].