Skill Gap Analysis

Purpose

A market-wide list of in-demand skills is not informative until it is compared against an existing sample person or persons. This analysis performs that comparison by measuring what employers in NAICS 523 ask for against records of what each member of the project team can actually do, and reports the distance between the two.

Method

Demand is identified from 7,074 job postings, all of which list skills. The comparison skills come from an internal self-assessment: each team member rated their own proficiency on nine skill areas, on a five-point scale from beginner to expert. These rankings were then checked against those nine areas from the postings to determine which frequently requested skills fall outside them.

The gap for a given skill is the difference between the level the market requires and the level the individual holds. The required level was identified by how often the skill appears in postings.

Status Code

This is to establish the python script to create the team matrix in skill gap analysis, and will lead into a comparison of the teams skills against the market demand for each skill area. The code will read in the cleaned panel of postings, extract the relevant skill information, and then create visualizations to show where the team stands in relation to market expectations.

import re
from collections import Counter
from pathlib import Path

import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
import plotly.io as pio
import json
from PIL import Image, ImageDraw

#setting pallete for consistent visualizations across all pages
SOLARIZED_BASE3 = "#fdf6e3"   
SOLARIZED_BASE2 = "#eee8d5"   
SOLARIZED_BASE1 = "#93a1a1"   
SOLARIZED_BASE00 = "#657b83"  
SOLARIZED_BASE01 = "#586e75"  
SOLARIZED_BASE02 = "#073642"  

SOLARIZED_ACCENTS = [
    "#268bd2",  
    "#cb4b16",  
    "#2aa198",  
    "#859900",  
    "#d33682",  
    "#b58900",  
    "#6c71c4",  
    "#dc322f",  
]


pio.templates["group1"] = go.layout.Template(
    layout=dict(
        font=dict(family="Arial, sans-serif", size=13, color=SOLARIZED_BASE00),
        title=dict(
            x=0.5,
            xanchor="center",
            font=dict(size=18, color=SOLARIZED_BASE02, family="Arial, sans-serif"),
        ),
        colorway=SOLARIZED_ACCENTS,
        paper_bgcolor=SOLARIZED_BASE3,
        plot_bgcolor=SOLARIZED_BASE3,

        margin=dict(l=120, r=40, t=90, b=60),
        xaxis=dict(
            automargin=True,
            gridcolor=SOLARIZED_BASE2,
            linecolor=SOLARIZED_BASE1,
            zerolinecolor=SOLARIZED_BASE2,
            tickfont=dict(color=SOLARIZED_BASE01),
            title=dict(font=dict(color=SOLARIZED_BASE01)),
        ),
        yaxis=dict(
            automargin=True,
            gridcolor=SOLARIZED_BASE2,
            linecolor=SOLARIZED_BASE1,
            zerolinecolor=SOLARIZED_BASE2,
            tickfont=dict(color=SOLARIZED_BASE01),
            title=dict(font=dict(color=SOLARIZED_BASE01)),
        ),
        legend=dict(font=dict(color=SOLARIZED_BASE01)),
    )
)
pio.templates.default = "group1"
pd.set_option("display.max_colwidth", None)

Path("figures").mkdir(exist_ok=True)

def save_fig(fig, name):
    """Save a PNG copy to figures/ if possible; otherwise skip quietly."""
    try:
        path = f"figures/{name}.png"
        fig.write_image(path, scale=2, width=900, height=550)
        img = Image.open(path).convert("RGB")
        draw = ImageDraw.Draw(img)
        border_width = 3
        w, h = img.size
        draw.rectangle(
            [0, 0, w - 1, h - 1],
            outline=(7, 54, 66),
            width=border_width,
        )
        img.save(path)
    except Exception:
        pass

Team Skill Matrix

Each member of the team rated themselves on nine skill areas relevant to a Business Intelligence or Data Analyst roles. On a 1–5 scale (1 = Beginner, 2 = Basic, 3 = Intermediate, 4 = Advanced, 5 = Expert).

ratings = pd.DataFrame(
    {
        "Fuad":     [2, 1, 2, 1, 4, 2, 4, 2, 3],
        "Jonathan": [2, 2, 1, 1, 4, 2, 3, 2, 4],
        "Katie":    [3, 3, 3, 2, 4, 2, 4, 3, 3],
    },
    index=[
        "Python", "SQL", "Machine Learning", "Cloud Computing", "Communication",
        "AWS", "Dashboard/Reporting", "Git", "Financial Domain Knowledge",
    ],
)
# rows = members, columns = skills
df_skills = ratings.T
ratings

	Fuad	Jonathan	Katie
Python	2	2	3
SQL	1	2	3
Machine Learning	2	1	3
Cloud Computing	1	1	2
Communication	4	4	4
AWS	2	2	2
Dashboard/Reporting	4	3	4
Git	2	2	3
Financial Domain Knowledge	3	4	3

Market Demand for Each Skill Area

The team searched SKILLS_NAME, the full skill list, rather than SOFTWARE_SKILLS_NAME to allow a more standard comparison as two of the selected areas- Communication and Financial Domain Knowledge- are not software and appear only in the full skills list. The other seven areas are software-related, and the full list is more complete than the software-only list.

PANEL_PATH = Path("data/processed/career_market_panel.csv")
SKILL_FIELD = "SKILLS_NAME"

# Each skill area pattens 

AREA_PATTERNS = {
    "Python":                    r"\bpython\b",
    "SQL":                       r"\bsql\b|mysql|postgresql",
    "Machine Learning":          r"machine learning|deep learning|predictive model|scikit|tensorflow|pytorch",
    "Cloud Computing":           r"cloud|\bazure\b|snowflake|databricks",
    "Communication":             r"communication|presentation|public speaking|storytelling",
    "AWS":                       r"\baws\b|amazon web services|amazon redshift|amazon s3",
    "Dashboard/Reporting":       r"dashboard|\breporting\b|tableau|power bi|looker|qlik|business intelligence|data visuali",
    "Git":                       r"\bgit\b|github|gitlab|version control",
    "Financial Domain Knowledge": r"financ|investment|securities|portfolio|accounting|asset management|risk management|trading|fixed income|derivative|valuation",
}
assert set(AREA_PATTERNS) == set(df_skills.columns), "Area names must match the ratings table"

panel = pd.read_csv(PANEL_PATH, usecols=[SKILL_FIELD], dtype=str)
n_postings = len(panel)

def clean_skill(name):
    return re.sub(r"\s*\([^)]*\)", "", name).strip().casefold()

skills_per_posting = (
    panel[SKILL_FIELD].fillna("[]")
    .apply(json.loads)
    .apply(lambda items: {clean_skill(x) for x in items if str(x).strip()})
)

# Match each distinct skill name once, then map postings to areas
skill_freq = Counter(s for skills in skills_per_posting for s in skills)
skill_to_areas = {
    s: {a for a, p in AREA_PATTERNS.items() if re.search(p, s)} for s in skill_freq
}
areas_per_posting = skills_per_posting.apply(
    lambda skills: set().union(*(skill_to_areas[s] for s in skills))
)

demand = pd.DataFrame({
    "Postings": {a: int(areas_per_posting.apply(lambda x: a in x).sum()) for a in AREA_PATTERNS}
})
demand["Share"] = demand["Postings"] / n_postings * 100
demand = demand.sort_values("Share", ascending=False)

(
    demand.style
    .format({"Share": "{:.1f}%", "Postings": "{:,}"})
    .set_table_attributes('style="width: auto;"')
    .set_properties(**{"text-align": "right", "padding": "0.5rem 2.5rem"})
    .set_table_styles([
        {"selector": "th.col_heading",
         "props": [("text-align", "right"), ("padding", "0.5rem 2.5rem")]},
        {"selector": "th.row_heading",
         "props": [("text-align", "left"), ("padding", "0.5rem 2.5rem 0.5rem 0.75rem")]},
    ])
)

	Postings	Share
Financial Domain Knowledge	4,720	66.7%
Communication	1,662	23.5%
Dashboard/Reporting	1,580	22.3%
SQL	1,303	18.4%
Python	863	12.2%
Cloud Computing	258	3.6%
Machine Learning	200	2.8%
Git	66	0.9%
AWS	32	0.5%

fig = px.bar(
    demand.reset_index(names="Skill area").sort_values("Share"),
    x="Share", y="Skill area", orientation="h",
    title=f"<b>Share of {n_postings:,} Postings Requiring Each Skill Area</b>",
    labels=dict(Share="% of postings", **{"Skill area": ""}),
    hover_data={"Postings": ":,"},
)
fig.update_layout(margin=dict(l=220, b=80))
fig.update_yaxes(automargin=True)
save_fig(fig, "sga_market_demand")
fig.show()

Figure 1

Insights:

Financial Domain Knowledge, is the most demanded skills in our dataset with 66.7%. Followed by Communication at 23.5%, and Dashboard/Reporting at 22.3%. Following that are SQL and Python with SQL at 18.4% and Python at 12.2%. Cloud computing, machine learning, git, and AWS are each under 5%.

Top Skills by Area

The below table lists the most frequently occurring skills that each area captured. It shows exactly what each area counts so a pattern can be checked for correctness.

pd.DataFrame({
    "Most frequent matched skills": {
        a: ", ".join(
            sorted((s for s, areas in skill_to_areas.items() if a in areas),
                   key=lambda s: -skill_freq[s])[:6]
        )
        for a in demand.index
    }
})

	Most frequent matched skills
Financial Domain Knowledge	finance, financial analysis, risk management, financial services, accounting, financial modeling
Communication	verbal communication skills, communication, effective communication, presentations, professional communication, business communication
Dashboard/Reporting	tableau, power bi, reporting tools, business intelligence, management reporting, reporting and analysis
SQL	sql, pl/sql, postgresql, sql server management studio, db2 sql, teradata sql
Python	python, python for data analysis
Cloud Computing	databricks, oracle cloud, cloud infrastructure, cloud services, cloud computing, cloud technologies
Machine Learning	machine learning, tensorflow, machine learning algorithms, predictive modeling, machine learning methods, deep learning
Git	version control, github, github copilot, gitlab
AWS	amazon web services, amazon redshift

Gap Analysis

Market share is converted into a target proficiency. The cut-offs are based on team assumptions. Because an area counts a posting if any of its skills is listed, broad areas would score higher shares than narrow ones, and the targets should be read with that in mind.

Share of postings	Target level
50% or more	5
30–50%	4
15–30%	3
5–15%	2
under 5%	1

target = pd.cut(
    demand["Share"], bins=[-0.1, 5, 15, 30, 50, 100], labels=[1, 2, 3, 4, 5]
).astype(int)

order = list(demand.index)                     # 

#most to least demanded
df_skills = df_skills[order]
gap = (target - df_skills).clip(lower=0)

fig = px.imshow(
    df_skills,
    text_auto=True, zmin=0, zmax=5, aspect="auto",
    color_continuous_scale="Blues",
    title="<b>Team Skill Levels Heat Map (1 = beginner, 5 = expert)</b>",
    labels=dict(x="", y="", color="Gap"),
)
fig.update_layout(
    title=dict(
        text="<b>Team Skill Levels Heat Map (1 = beginner, 5 = expert)</b>",
        x=0.5,
        xanchor="center",
        font=dict(size=18, color=SOLARIZED_BASE02, family="Arial, sans-serif"),
        subtitle=dict(
            text="Skill area (most → least in demand)",
            font=dict(size=12, color=SOLARIZED_BASE01),
        ),
    )
)
save_fig(fig, "sga_team_matrix")
fig.show()

fig = px.imshow(
    gap,
    text_auto=True, zmin=0, zmax=5, aspect="auto",
    color_continuous_scale="Reds",
    labels=dict(x="", y="", color="Gap"),
)

fig.update_layout(
    title=dict(
        text="<b>Skill Gap: Levels Below Market Target</b>",
        x=0.5,
        xanchor="center",
        font=dict(size=18, color=SOLARIZED_BASE02, family="Arial, sans-serif"),
        subtitle=dict(
            text="Skill area (most → least in demand)",
            font=dict(size=12, color=SOLARIZED_BASE01),
        ),
    )
)
save_fig(fig, "sga_gap_matrix")
fig.show()

team_view = pd.DataFrame({
    "Market target": target,
    "Team average": df_skills.mean(),
    "Team best": df_skills.max(),
}).loc[order]

fig = px.bar(
    team_view.reset_index(names="Skill area"),
    x="Skill area", y=["Market target", "Team average", "Team best"],
    barmode="group",
    title="<b>Team Proficiency vs. Market Target</b>",
    labels=dict(value="Proficiency (1–5)", variable="", **{"Skill area": ""}),
)
fig.update_yaxes(range=[0, 5.5])
save_fig(fig, "sga_team_vs_market")
fig.show()

uncovered = team_view.index[team_view["Team best"] < team_view["Market target"]].tolist()
print("No team member meets the target for:", ", ".join(uncovered) or "none")

Figure 2

No team member meets the target for: Financial Domain Knowledge

Learning Order

Below is a table of team member learning priorities for each area, where each member’s priority is determined by their gap weighted by the area’s market share.

priority = gap.mul(demand["Share"] / 100, axis=1)

rows = []
for member, scores in priority.iterrows():
    top3 = scores[scores > 0].sort_values(ascending=False).head(3)
    rows.append({
        "Member": member,
        "Learning order (levels to gain)": " → ".join(
            f"{s} (+{gap.loc[member, s]})" for s in top3.index
        ) or "Meets every target",
    })
pd.DataFrame(rows).set_index("Member")

	Learning order (levels to gain)
Member
Fuad	Financial Domain Knowledge (+2) → SQL (+2)
Jonathan	Financial Domain Knowledge (+1) → SQL (+1)
Katie	Financial Domain Knowledge (+2)

Analysis

The team meets expectations in seven of the 9 areas Communication and dashboard/reporting but Financial domain is the one where every group member should focus on improving due it being the most demanded skill. Fuad and Katie are two levels behind while Jonathan is one level behind. The second skill to focus for the group would be SQL. In the group Fuad is below two levels and Jonathan is below one level, while Katie meets the expectations as illustrated by the learning chart. However, the team is doing well in Python, Machine Learning, Cloud Computing, AWS, and Git.

How to Close the Skill Gap

The way the team can close the gaps is a combination of peer learning and external courses. The following are some suggestions to start the improvement process:

External resources can be utilized such as Coursera learning paths, Udemycourses, and other online resources to fill in the gaps. The team can also consider pairing sessions where members with higher proficiency in certain areas can mentor others. For example, Katie can run short pairing sessions for Fuad and Jonathan in SQL. Jonathan can help with Financial Domain Knowledge by walking the team through topics such as financial analysis and risk management. However, no member meets the target in this area, so the whole group needs outside learning as well. We can set goals with a time frame, for example completing a Coursera course on finance or investments within the next three months.