Code — Data Cleaning, Transformation, and Exploratory Analysis
Author
Patricio Romero
Published
October 23, 2026
1 Project Overview
This project explores which technical skills employers request in AI-related job postings, how skills relate to salary, and how job categories and experience levels differ. The findings describe associations in the dataset, not causal effects.
2 Research Questions
Which technical skills are most frequently requested?
Which skills are associated with higher salaries?
How do skill requirements vary across job categories?
How does experience level relate to salary?
3 Data Source and Tools
The dataset is AI Jobs Market 2025–2026 | Salaries from Kaggle. I use PostgreSQL for relational storage, R and SQL for analysis, RStudio and Quarto for reporting, and GitHub for version control. The original database is not modified by this report. This is an individual project, as permitted by the instructor in class.
4 Database Design
The database contains job categories, job postings, skills, and a bridge table connecting jobs to skills. This allows a job to require multiple skills and a skill to appear in multiple jobs.
This report uses live PostgreSQL queries. Rendering stops if the database is unavailable, rather than displaying estimated values. The database must already contain the four tables shown in the ERD. Install packages once with install.packages(c("DBI", "RPostgres", "knitr", "ggplot2", "kableExtra")) before rendering.
relationship_validation <-data.frame(Check =c("Job–Skill Rows Without a Job", "Job–Skill Rows Without a Skill","Jobs Without a Valid Category"),Count =c(sum(!job_skills_data$job_id %in% jobs$job_id),sum(!job_skills_data$skill_id %in% skills_data$skill_id),sum(is.na(jobs$category_name))))show_table(relationship_validation, "Relationship Integrity Check",labels =c("Validation Check", "Count"), count_cols ="Count")
Relationship Integrity Check
Validation Check
Count
Job–Skill Rows Without a Job
0
Job–Skill Rows Without a Skill
0
Jobs Without a Valid Category
0
7 Data Transformation
I standardize whitespace and keep valid positive salaries for salary analysis. I do not delete or update original database records. Skill frequency analyses continue to use all job–skill relationships.
Each row in the combined dataset represents one job–skill relationship. A job may appear multiple times. Salary summaries by job use jobs_clean, not the repeated rows of analysis_data.
ggplot(jobs_clean, aes(x = annual_salary_usd)) +geom_histogram(aes(y =after_stat(density)), binwidth =10000,boundary =0, fill ="steelblue", color ="white", alpha = .55) +geom_density(color ="darkred", linewidth =1.15, na.rm =TRUE) +scale_x_continuous(breaks = salary_breaks, labels = dollar_axis) +labs(title ="Distribution of Annual Salaries",subtitle ="Histogram ($10,000 bins) and smoothed density curve",x ="Annual Salary (USD)", y ="Density") +theme_minimal(base_size =12) +theme(panel.grid.major.x =element_line(color ="gray80"))
The blue bars show how salaries are distributed, while the red curve provides a smoothed view of where salaries are concentrated. A long right tail would suggest that a smaller number of jobs offer unusually high salaries. The shape must be interpreted from the rendered chart, not assumed in advance.
Potential outliers are flagged, not automatically removed: high salaries may be valid in this market.
8.4 Salary Boxplot
Show code
ggplot(jobs_clean, aes(x ="All Job Postings", y = annual_salary_usd)) +geom_boxplot(fill ="#A6D5F2", color ="#215B86", width = .35,outlier.color ="#C0392B", outlier.size =2) +scale_y_continuous(breaks = salary_breaks, labels = dollar_axis) +labs(title ="Annual Salary Boxplot",subtitle ="Median, middle 50% of salaries, and potential outliers",x =NULL, y ="Annual Salary (USD)") +theme_minimal(base_size =12) +theme(panel.grid.major.y =element_line(color ="gray82"),plot.title =element_text(face ="bold"))
How to read the salary boxplot: The line inside the blue box is the median salary: half of the valid job salaries are below it and half are above it. The box extends from the first quartile (25th percentile) to the third quartile (75th percentile), so it contains the middle 50% of salaries. The whiskers extend to the most extreme observations within 1.5 times the interquartile range (IQR). Red dots beyond the whiskers are potential outliers. They are not necessarily errors, and they remain in this descriptive analysis. A long upper whisker or many high outliers can help explain why the mean may be higher than the median.
8.5 Job Postings by Category
Show code
category_distribution <-as.data.frame(table(jobs_clean$category_name, useNA ="ifany"))names(category_distribution) <-c("Category", "Job_Count")show_table(category_distribution, "Job Postings by Category",labels =c("Job Category", "Number of Job Postings"), count_cols ="Job_Count")
Job Postings by Category
Job Category
Number of Job Postings
AI Engineering
736
Architecture
52
Business
62
Data Engineering
51
Data Science
127
Governance
122
Infrastructure
55
ML Operations
51
Product
70
Research
50
Robotics
74
Security
50
Show code
ggplot(category_distribution,aes(x =reorder(Category, Job_Count), y = Job_Count, fill = Category)) +geom_col(width = .72, show.legend =FALSE) +geom_text(aes(label =count_fmt(Job_Count)), hjust =-.12, size =3.4) +coord_flip(clip ="off") +scale_fill_brewer(palette ="Set3") +scale_y_continuous(labels = count_axis, expand =expansion(mult =c(0, .20))) +labs(title ="Job Postings by Category", x ="Job Category",y ="Number of Job Postings") +chart_theme()
9 Research Question 1: Most Requested Skills
9.1 Top 10 Requested Skills
Show code
top_skills <- DBI::dbGetQuery(con, " SELECT s.skill_name, COUNT(DISTINCT js.job_id) AS job_count FROM skills s JOIN job_skills js ON s.skill_id = js.skill_id GROUP BY s.skill_id, s.skill_name ORDER BY job_count DESC, s.skill_name LIMIT 10")top_skills$Rank <-seq_len(nrow(top_skills))top_skills$pct_postings <-round(100*as.numeric(top_skills$job_count) /nrow(jobs), 1)show_table(top_skills[c("Rank", "skill_name", "job_count", "pct_postings")],"Top 10 Most Requested Technical Skills",labels =c("Rank", "Skill", "Job Postings Requiring Skill", "Percent of All Jobs"),count_cols =c("Rank", "job_count"))
Top 10 Most Requested Technical Skills
Rank
Skill
Job Postings Requiring Skill
Percent of All Jobs
1
Python
942
62.8
2
SQL
422
28.1
3
Cloud
402
26.8
4
Communication
365
24.3
5
Leadership
363
24.2
6
Research
363
24.2
7
Statistics
341
22.7
8
Agile
340
22.7
9
Linux
320
21.3
10
Problem Solving
314
20.9
Show code
ggplot(top_skills,aes(x =reorder(skill_name, job_count), y = job_count, fill = skill_name)) +geom_col(width = .72, show.legend =FALSE) +geom_text(aes(label =count_fmt(job_count)), hjust =-.12, size =3.4) +coord_flip(clip ="off") +scale_fill_brewer(palette ="Paired") +scale_y_continuous(labels = count_axis, expand =expansion(mult =c(0, .20))) +labs(title ="Top 10 Most Requested Technical Skills",x ="Technical Skill", y ="Number of Job Postings") +chart_theme()
Each job is counted once per skill. Percentages use all postings as denominator. The dataset also lists professional skills such as Communication and Leadership, so this is a ranking of requested skills, not exclusively technical skills. The most requested skill is Python, appearing in 942 job postings.
10 Research Question 2: Skills Associated with Higher Salaries
10.1 Average Salary by Technical Skill
Show code
# Use the validated salary subset so invalid salary values do not enter the mean.skill_salary_data <- analysis_dataskill_salary <-aggregate(annual_salary_usd ~ skill_name, data = skill_salary_data, FUN = mean)names(skill_salary)[2] <-"average_salary"skill_counts <-aggregate(job_id ~ skill_name, data = skill_salary_data,FUN =function(x) length(unique(x)))names(skill_counts)[2] <-"job_count"skill_salary <-merge(skill_salary, skill_counts, by ="skill_name")skill_salary <- skill_salary[skill_salary$job_count >=10, , drop =FALSE]skill_salary <- skill_salary[order(-skill_salary$average_salary, skill_salary$skill_name), , drop =FALSE]skill_salary <-head(skill_salary, 10)skill_salary$Rank <-seq_len(nrow(skill_salary))show_table(skill_salary[c("Rank", "skill_name", "job_count", "average_salary")],"Top 10 Skills Associated with Higher Average Salaries",labels =c("Rank", "Technical Skill", "Job Postings", "Average Salary (USD)"),count_cols =c("Rank", "job_count"), money_cols ="average_salary")
Top 10 Skills Associated with Higher Average Salaries
Rank
Technical Skill
Job Postings
Average Salary (USD)
86
1
System Design
96
$248,947.92
67
2
Prompt Engineering
55
$246,381.82
49
3
LLM Fine-tuning
42
$242,633.33
71
4
RAG
63
$241,190.48
32
5
Enterprise Architecture
31
$240,612.90
57
6
MLOps
88
$230,352.27
90
7
Tool Use
44
$228,568.18
44
8
LangChain
77
$227,987.01
37
9
Fine-tuning
164
$226,628.05
79
10
Search Systems
40
$226,225.00
Show code
ggplot(skill_salary,aes(x =reorder(skill_name, average_salary), y = average_salary, fill = skill_name)) +geom_col(width = .72, show.legend =FALSE) +geom_text(aes(label =money(average_salary)), hjust =-.08, size =3.2) +coord_flip(clip ="off") +scale_fill_brewer(palette ="Paired") +scale_y_continuous(breaks = salary_breaks, labels = dollar_axis,expand =expansion(mult =c(0, .36))) +labs(title ="Highest Average Salaries by Technical Skill",subtitle ="Skills appearing in at least 10 job postings",x ="Technical Skill", y ="Average Annual Salary (USD)") +chart_theme()
Only skills appearing in at least 10 postings are included. The highest average salary among these skills is associated with System Design, at $248,947.92 across 96 postings. This is an association, not evidence that the skill causes higher pay.
11 Research Question 3: Skills Across Job Categories
11.1 Top Three Skills in Each Category
Show code
category_skills <- DBI::dbGetQuery(con, " WITH skill_counts AS ( SELECT jc.category_name, s.skill_name, COUNT(DISTINCT js.job_id) AS job_count FROM job_categories jc JOIN job_postings jp ON jc.category_id = jp.category_id JOIN job_skills js ON jp.job_id = js.job_id JOIN skills s ON js.skill_id = s.skill_id GROUP BY jc.category_name, s.skill_name ), ranked AS ( SELECT category_name, skill_name, job_count, ROW_NUMBER() OVER (PARTITION BY category_name ORDER BY job_count DESC, skill_name) AS skill_rank FROM skill_counts ) SELECT skill_rank, category_name, skill_name, job_count FROM ranked WHERE skill_rank <= 3 ORDER BY category_name, skill_rank")show_table(category_skills, "Top Three Skills by Job Category",labels =c("Rank", "Job Category", "Technical Skill", "Job Postings"),count_cols =c("skill_rank", "job_count"))
Top Three Skills by Job Category
Rank
Job Category
Technical Skill
Job Postings
1
AI Engineering
Python
565
2
AI Engineering
PyTorch
263
3
AI Engineering
SQL
180
1
Architecture
System Design
44
2
Architecture
Cloud
42
3
Architecture
Leadership
42
1
Business
Business Analysis
52
2
Business
Communication
52
3
Business
Python
51
1
Data Engineering
SQL
39
2
Data Engineering
Feature Stores
38
3
Data Engineering
Airflow
36
1
Data Science
Python
97
2
Data Science
Statistics
66
3
Data Science
Leadership
64
1
Governance
Research
60
2
Governance
Auditing
54
3
Governance
Communication
53
1
Infrastructure
Distributed Systems
48
2
Infrastructure
Cloud
42
3
Infrastructure
Networking
42
1
ML Operations
Cloud
41
2
ML Operations
Kubernetes
40
3
ML Operations
Docker
37
1
Product
Agile
63
2
Product
Data Analysis
61
3
Product
Stakeholder Mgmt
61
1
Research
Publishing
41
2
Research
Mathematics
39
3
Research
PyTorch
39
1
Robotics
Simulation
58
2
Robotics
C++
54
3
Robotics
Control Systems
54
1
Security
Risk Analysis
44
2
Security
Cybersecurity
43
3
Security
ML Security
43
Show code
data_science_skills <-subset(category_skills, category_name =="Data Science")if (nrow(data_science_skills) >0) {show_table(data_science_skills, "Top Three Skills for Data Science",labels =c("Rank", "Job Category", "Technical Skill", "Job Postings"),count_cols =c("skill_rank", "job_count"))} else {cat("No exact 'Data Science' category label was found. Review the category names above.")}
Top Three Skills for Data Science
Rank
Job Category
Technical Skill
Job Postings
13
1
Data Science
Python
97
14
2
Data Science
Statistics
66
15
3
Data Science
Leadership
64
11.2 Job Opportunities by Category
Show code
category_jobs <- DBI::dbGetQuery(con, " SELECT jc.category_name, COUNT(DISTINCT jp.job_id) AS job_postings FROM job_categories jc JOIN job_postings jp ON jc.category_id = jp.category_id GROUP BY jc.category_id, jc.category_name ORDER BY job_postings DESC, jc.category_name")category_jobs$Rank <-seq_len(nrow(category_jobs))show_table(category_jobs[c("Rank", "category_name", "job_postings")],"Number of Job Postings by Category",labels =c("Rank", "Job Category", "Number of Job Postings"),count_cols =c("Rank", "job_postings"))
Number of Job Postings by Category
Rank
Job Category
Number of Job Postings
1
AI Engineering
736
2
Data Science
127
3
Governance
122
4
Robotics
74
5
Product
70
6
Business
62
7
Infrastructure
55
8
Architecture
52
9
Data Engineering
51
10
ML Operations
51
11
Research
50
12
Security
50
Show code
ggplot(category_jobs,aes(x =reorder(category_name, job_postings), y = job_postings,fill = category_name)) +geom_col(width = .72, show.legend =FALSE) +geom_text(aes(label =count_fmt(job_postings)), hjust =-.12, size =3.4) +coord_flip(clip ="off") +scale_fill_brewer(palette ="Set3") +scale_y_continuous(labels = count_axis, expand =expansion(mult =c(0, .20))) +labs(title ="Number of Job Postings by Category",x ="Job Category", y ="Number of Job Postings") +chart_theme()
The top skills may differ by job category. Comparing counts within categories helps identify skills commonly requested for specific roles. The category with the largest number of postings is AI Engineering, with 736 postings.
12 Research Question 4: Experience Level and Salary
12.1 Salary by Experience Level
Show code
experience_salary <-aggregate(annual_salary_usd ~ experience_level,data = jobs_clean, FUN = mean)names(experience_salary)[2] <-"average_salary"experience_counts <-aggregate(job_id ~ experience_level, data = jobs_clean,FUN =function(x) length(unique(x)))names(experience_counts)[2] <-"job_count"experience_salary <-merge(experience_salary, experience_counts, by ="experience_level")experience_salary <- experience_salary[order(-experience_salary$average_salary), ]show_table(experience_salary[c("experience_level", "job_count", "average_salary")],"Average Salary by Experience Level",labels =c("Experience Level", "Number of Job Postings", "Average Salary (USD)"),count_cols ="job_count", money_cols ="average_salary")
The table and chart compare salary levels across experience groups. The results are descriptive and do not establish that experience alone causes salary differences. The highest average salary in this sample is for Lead (10+ yrs), at $240,055.12.
13 Conclusion
This analysis addresses all four research questions using verified PostgreSQL data.
Most requested skill: Python is requested in 942 of 1,500 postings (62.8%). SQL and Cloud follow with 422 and 402 postings.
Salary by skill: The preceding salary table compares mean annual pay for skills listed in at least ten postings. These are associations, not causal effects.
Job categories: AI Engineering has 736 postings, the largest category. Different category sizes must be considered when comparing skill frequencies.
Experience and salary: Mean salaries rise from approximately $150,039 for Entry roles to $240,055 for Lead roles in this sample.
Limitations: This is one Kaggle dataset of AI-related roles, not a representative sample of the entire labor market. Skills include technical and professional competencies. Differences in salaries may also reflect occupation, geography, and employer. No causal conclusions are justified.
14 Reproducibility and Limitations
This report requires a local PostgreSQL database named data607_project3 containing the four documented tables and the DBI, RPostgres, knitr, ggplot2, and kableExtra R packages. Connection credentials are not embedded in the report. A failed database connection stops rendering instead of displaying invented data. Salary cleaning affects salary analyses; original database records are unchanged. The dataset source and collection methodology should be checked before generalizing results. To reproduce the report, import the source data into the four relational tables using the database setup SQL and provide PostgreSQL access through local credentials (.pgpass or environment variables). The source CSV and database setup instructions must be included with the submission when permitted by the dataset license.
15 AI Use
ChatGPT helped organize the Quarto report, review the R and SQL code, and improve the English and chart formatting. I reviewed the data source, database design, transformations, research questions, and code, and I am responsible for verifying the executed results before submission.
Show code
# Close the PostgreSQL connection after all calculations and inline results.if (DBI::dbIsValid(con)) DBI::dbDisconnect(con)
