app.py

import pandas as pd
import streamlit as st
import plotly.express as px

st.markdown(
'''
# LinkedIn Job Postings Analysis
- **Author:** Andhika Wibawa ([AndhikaWB](https://github.com/AndhikaWB))
- **Dataset:** [LinkedIn Job Postings](https://www.kaggle.com/datasets/arshkon/linkedin-job-postings) (v8)
'''
)

##############################

df_1 = pd.read_csv('processed/job_skill_count.csv')
fig_1 = px.pie(
    data_frame = df_1,
    names = 'skill_name',
    values = 'count',
    # Skills here are actually job functions
    title = 'Most Desired Skills for Applying Jobs',
    labels = {
        'skill_name': 'Skill Name',
        'skill_abr': 'Skill Abbreviation',
        'count': 'Open Job Count'
    },
    # Extra data to show
    hover_data = ['skill_abr']
)
# https://plotly.com/python/reference/pie/
fig_1.update_traces(texttemplate = '<b>%{customdata[0]}</b><br>%{percent}', textposition = 'inside')
# https://plotly.com/python/text-and-annotations/
fig_1.update_layout(uniformtext_minsize = 8, uniformtext_mode = 'hide')
st.plotly_chart(fig_1)

##############################

df_2 = pd.read_csv('processed/job_emp_type_count.csv')
# https://plotly.github.io/plotly.py-docs/generated/plotly.graph_objects.Sunburst.html
fig_2 = px.sunburst(
    data_frame = df_2,
    path = ['formatted_work_type', 'state'],
    values = 'count',
    title = 'Job Oppportunity Based on Employment Type',
    color = 'count',
    color_continuous_scale = px.colors.sequential.algae,
    labels = {
        'labels': 'Label',
        'parent': 'Parent',
        'id': 'Path',
        'formatted_work_type': 'Employment Type',
        'state': 'State',
        # "count_sum" is auto-generated by Plotly sunburst, while "sum_count" is from SQL
        # "count_sum" can be state level and country level, but "sum_count" is always country level
        'count_sum': 'Open Job Count',
        # 'sum_count': 'Job Count (Whole Country)',
        'med_salary': '[Median] Monthly Salary (USD)'
    },
    hover_data = {
        'count': False,
        # 'sum_count': True,
        'med_salary': ':.0f'
    }
)
fig_2.update_traces(
    textinfo = 'label+percent parent'
)
# Uniform text may disable animation
fig_2.update_layout(
    uniformtext = {
        'minsize': 8,
        'mode': 'hide'
    }
)
st.plotly_chart(fig_2)


df_3 = pd.read_csv('processed/job_emp_type_salary_dist.csv')
# Alternatives: scatter, line, dist, ridgeline (violin)
fig_3 = px.histogram(
    df_3,
    x = 'med_salary',
    color = 'formatted_work_type',
    title = 'Salary Distribution Based on Employment Type',
    labels = {
        'formatted_work_type': 'Employment Type',
        # 'count': 'Job Count', # Doesn't work
        'med_salary': 'Monthly Salary (USD)'
    },
    hover_data = {
        'med_salary': False
    }
)
# Replace hovertemplate without manually writing from beggining
# https://github.com/plotly/plotly.py/issues/2876#issuecomment-1111339561
fig_3.for_each_trace(lambda t: t.update(hovertemplate = t.hovertemplate.replace('count', 'Open Job Count')))
fig_3.update_layout(hovermode = 'x unified', yaxis_title='Open Job Count')
st.plotly_chart(fig_3)

##############################

df_4 = pd.read_csv('processed/job_experience_count.csv')
# https://plotly.github.io/plotly.py-docs/generated/plotly.graph_objects.Sunburst.html
fig_4 = px.sunburst(
    data_frame = df_4,
    path = ['formatted_experience_level', 'state'],
    values = 'count',
    title = 'Job Oppportunity Based on Experience Level',
    color = 'count',
    color_continuous_scale = px.colors.sequential.algae,
    labels = {
        'labels': 'Label',
        'parent': 'Parent',
        'id': 'Path',
        'formatted_experience_level': 'Experience Level',
        'state': 'State',
        # "count_sum" is auto-generated by Plotly sunburst, while "sum_count" is from SQL
        # "count_sum" can be state level and country level, but "sum_count" is always country level
        'count_sum': 'Open Job Count',
        # 'sum_count': 'Job Count (Whole Country)',
        'med_salary': '[Median] Monthly Salary (USD)'
    },
    hover_data = {
        'count': False,
        # 'sum_count': True,
        'med_salary': ':.0f'
    }
)
fig_4.update_traces(
    textinfo = 'label+percent parent'
)
# Uniform text may disable animation
fig_4.update_layout(
    uniformtext = {
        'minsize': 8,
        'mode': 'hide'
    }
)
st.plotly_chart(fig_4)


df_5 = pd.read_csv('processed/job_experience_salary_dist.csv')
# Alternatives: scatter, line, dist, ridgeline (violin)
fig_5 = px.histogram(
    df_5,
    x = 'med_salary',
    color = 'formatted_experience_level',
    title = 'Salary Distribution Based on Experience Level',
    labels = {
        'formatted_experience_level': 'Experience Level',
        # 'count': 'Job Count', # Doesn't work
        'med_salary': 'Monthly Salary (USD)'
    },
    hover_data = {
        'med_salary': False
    },
    # https://plotly.com/python/legend/#legend-order
    category_orders = {
        'formatted_experience_level': [
            'Internship',
            'Entry level',
            'Associate',
            'Mid-Senior level',
            'Director',
            'Executive'
        ]
    }
)
# Replace hovertemplate without manually writing from beggining
# https://github.com/plotly/plotly.py/issues/2876#issuecomment-1111339561
fig_5.for_each_trace(lambda t: t.update(hovertemplate = t.hovertemplate.replace('count', 'Open Job Count')))
fig_5.update_layout(hovermode = 'x unified', yaxis_title='Open Job Count')
st.plotly_chart(fig_5)

##############################

df_6 = pd.read_csv('processed/job_industry_count.csv')
fig_6 = px.bar(
    data_frame = df_6,
    x = 'industry_name',
    y = 'count',
    title = ''.join([
        'Job Opportunity Based on Industry<br>',
        '<sup>Ordered by the highest job count (whole country)</sup>'
    ]),
    color = 'state',
    # text_auto = True,
    color_discrete_sequence = px.colors.qualitative.Pastel,
    labels = {
        'industry_name': 'Industry',
        'count': 'Open Job Count',
        'state': 'State'
    },
    # Extra data to show
    hover_data = [
        'state',

        # 'count',
        'sum_count',

        'med',
        'avg_med',
        'sum_med'
    ]
)
fig_6.update_traces(
    hovertemplate = '<br>'.join([
        '<b>%{x}</b><br>',

        '<b>Selected State (%{customdata[0]})</b>',
        'Job Count=%{y:.0f}',
        '[Median] Monthly Salary (USD)=%{customdata[2]:.0f}<br>',

        '<b>Whole Country (US)</b>',
        'Total Job Count=%{customdata[1]:.0f}',
        '[Median] Monthly Salary (USD)=%{customdata[3]:.0f}',
        'Total Monthly Salary (USD)=%{customdata[4]:.0f}',
        '<extra></extra>'
    ])
)
# https://plotly.com/python/dropdowns/
button_list = []
for i in fig_6.data:
    button_list.append(
        dict(
            label = i.name, # type: ignore
            method = 'restyle',
            args = [{
                # Filter jobs based on state
                'visible': [ i.name == j.name for j in fig_6.data ] # type: ignore
            }]
        )
    )
# Sort dropdown list alphabetically
# button_list = sorted(button_list, key = lambda i: i['label'])
# Add whole country at the beginning of dropdown
button_list.insert(
    0,
    dict(
        label = 'Whole Country',
        method = 'restyle',
        args = [{
            # Show jobs on all states
            'visible': [ True for i in fig_6.data ]
        }],
    )
)
fig_6.update_layout(
    # https://plotly.com/python/reference/layout/xaxis/
    xaxis = {
        'showticklabels': False,
        # Zoom to the first 20 data
        'range': [-0.5, 20.5],
        # Doesn't seem to work
        'side': 'bottom'
    },
    # https://plotly.com/python/reference/layout/updatemenus/
    updatemenus = [{
        'buttons': button_list
    }],
    # https://plotly.com/python/reference/#layout-legend
    legend = {
        'traceorder': 'reversed'
    }
)
st.plotly_chart(fig_6)


df_7 = pd.read_csv('processed/job_industry_salary.csv')
fig_7 = px.bar(
    data_frame = df_7,
    x = 'industry_name',
    y = 'med',
    title = ''.join([
        'Monthly Salary (USD) Based on Industry<br>',
        '<sup>Ordered by the highest median salary (whole country)</sup>'
    ]),
    color = 'state',
    # text_auto = True,
    color_discrete_sequence = px.colors.qualitative.Pastel,
    labels = {
        'industry_name': 'Industry',
        'med': 'Monthly Salary (USD)',
        'state': 'State'
    },
    # Extra data to show
    hover_data = [
        'state',

        'count',
        'sum_count',

        # 'med',
        'avg_med',
        'sum_med'
    ]
)
fig_7.update_traces(
    hovertemplate = '<br>'.join([
        '<b>%{x}</b><br>',

        '<b>Selected State (%{customdata[0]})</b>',
        'Job Count=%{customdata[1]:.0f}',
        '[Median] Monthly Salary (USD)=%{y:.0f}<br>', # median

        '<b>Whole Country (US)</b>',
        'Total Job Count=%{customdata[2]:.0f}',
        '[Median] Monthly Salary (USD)=%{customdata[3]:.0f}', # median
        'Total Monthly Salary (USD)=%{customdata[4]:.0f}', # total of median
        '<extra></extra>'
    ])
)
# https://plotly.com/python/dropdowns/
button_list = []
for i in fig_7.data:
    button_list.append(
        dict(
            label = i.name, # type: ignore
            method = 'restyle',
            args = [{
                # Filter jobs based on state
                'visible': [ i.name == j.name for j in fig_7.data ] # type: ignore
            }]
        )
    )
# Sort dropdown list alphabetically
# button_list = sorted(button_list, key = lambda i: i['label'])
# Add whole country at the beginning of dropdown
button_list.insert(
    0,
    dict(
        label = 'Whole Country',
        method = 'restyle',
        args = [{
            # Show jobs on all states
            'visible': [ True for i in fig_7.data ]
        }],
    )
)
fig_7.update_layout(
    # https://plotly.com/python/reference/layout/xaxis/
    xaxis = {
        'showticklabels': False,
        # Zoom to the first 20 data
        'range': [-0.5, 20.5],
        # Doesn't seem to work
        'side': 'bottom'
    },
    # https://plotly.com/python/reference/layout/updatemenus/
    updatemenus = [{
        'buttons': button_list
    }],
    # https://plotly.com/python/reference/#layout-legend
    legend = {
        'traceorder': 'reversed'
    }
)
st.plotly_chart(fig_7)

##############################

df_8 = pd.read_csv('processed/job_title_count.csv')
fig_8 = px.bar(
    data_frame = df_8,
    x = 'title',
    y = 'count',
    title = ''.join([
        'Job Opportunity Based on Job Title<br>',
        '<sup>Ordered by the most common job title (whole country)</sup>'
    ]),
    color = 'state',
    # text_auto = True,
    color_discrete_sequence = px.colors.qualitative.Pastel,
    labels = {
        'title': 'Job Title',
        'count': 'Open Job Count',
        'state': 'State',
    },
    # Extra data to show
    hover_data = [
        'state',

        # 'count',
        'sum_count',

        'med',
        'avg_med',
        'sum_med',
    ]
)
fig_8.update_traces(
    hovertemplate = '<br>'.join([
        '<b>%{x}</b><br>',

        '<b>Selected State (%{customdata[0]})</b>',
        'Job Count=%{y:.0f}',
        '[Median] Monthly Salary (USD)=%{customdata[2]:.0f}<br>',

        '<b>Whole Country (US)</b>',
        'Total Job Count=%{customdata[1]:.0f}',
        '[Median] Monthly Salary (USD)=%{customdata[3]:.0f}',
        'Total Monthly Salary (USD)=%{customdata[4]:.0f}',
        '<extra></extra>'
    ])
)
# https://plotly.com/python/dropdowns/
button_list = []
for i in fig_8.data:
    button_list.append(
        dict(
            label = i.name, # type: ignore
            method = 'restyle',
            args = [{
                # Filter jobs based on state
                'visible': [ i.name == j.name for j in fig_8.data ] # type: ignore
            }]
        )
    )
# Sort dropdown list alphabetically
# button_list = sorted(button_list, key = lambda i: i['label'])
# Add whole country at the beginning of dropdown
button_list.insert(
    0,
    dict(
        label = 'Whole Country',
        method = 'restyle',
        args = [{
            # Show jobs on all states
            'visible': [ True for i in fig_8.data ]
        }],
    )
)
fig_8.update_layout(
    # https://plotly.com/python/reference/layout/xaxis/
    xaxis = {
        'showticklabels': False,
        # Zoom to the first 20 data
        'range': [-0.5, 20.5],
        # Doesn't seem to work
        'side': 'bottom'
    },
    # https://plotly.com/python/reference/layout/updatemenus/
    updatemenus = [{
        'buttons': button_list
    }],
    # https://plotly.com/python/reference/#layout-legend
    legend = {
        'traceorder': 'reversed'
    }
)
st.plotly_chart(fig_8)


df_9 = pd.read_csv('processed/job_title_salary.csv')
fig_9 = px.bar(
    data_frame = df_9,
    x = 'title',
    y = 'med',
    title = ''.join([
        'Monthly Salary (USD) Based on Job Title<br>',
        '<sup>Ordered by highest median salary (whole country)</sup>'
    ]),
    color = 'state',
    # text_auto = True,
    color_discrete_sequence = px.colors.qualitative.Pastel,
    labels = {
        'title': 'Job Title',
        'med': 'Monthly Salary (USD)',
        'state': 'State',
    },
    # Extra data to show
    hover_data = [
        'state',
        'count',
        'sum_count',
        # 'med',
        'avg_med',
        'sum_med',
    ]
)
fig_9.update_traces(
    hovertemplate = '<br>'.join([
        '<b>%{x}</b><br>',

        '<b>Selected State (%{customdata[0]})</b>',
        'Job Count=%{customdata[1]:.0f}',
        '[Median] Monthly Salary (USD)=%{y:.0f}<br>',

        '<b>Whole Country (US)</b>',
        'Total Job Count=%{customdata[2]:.0f}',
        '[Median] Monthly Salary (USD)=%{customdata[3]:.0f}',
        'Total Monthly Salary (USD)=%{customdata[4]:.0f}',
        '<extra></extra>'
    ])
)
# https://plotly.com/python/dropdowns/
button_list = []
for i in fig_9.data:
    button_list.append(
        dict(
            label = i.name, # type: ignore
            method = 'restyle',
            args = [{
                # Filter jobs based on state
                'visible': [ i.name == j.name for j in fig_9.data ] # type: ignore
            }]
        )
    )
# Sort dropdown list alphabetically
# button_list = sorted(button_list, key = lambda i: i['label'])
# Add whole country at the beginning of dropdown
button_list.insert(
    0,
    dict(
        label = 'Whole Country',
        method = 'restyle',
        args = [{
            # Show jobs on all states
            'visible': [ True for i in fig_9.data ]
        }],
    )
)
fig_9.update_layout(
    # https://plotly.com/python/reference/layout/xaxis/
    xaxis = {
        'showticklabels': False,
        # Zoom to the first 20 data
        'range': [-0.5, 20.5],
        # Doesn't seem to work
        'side': 'bottom'
    },
    # https://plotly.com/python/reference/layout/updatemenus/
    updatemenus = [{
        'buttons': button_list
    }],
    # https://plotly.com/python/reference/#layout-legend
    legend = {
        'traceorder': 'reversed'
    }
)
st.plotly_chart(fig_9)

##############################

df_10 = pd.read_csv('processed/job_titles_applicants_hicount.csv')
df_11 = pd.read_csv('processed/job_titles_applicants_hicount_loapp.csv')
df_12 = pd.read_csv('processed/job_titles_applicants_hiview_loapp.csv')

for idx, df in enumerate([df_10, df_11, df_12]):
    if idx == 0: subtitle = 'Ordered by the highest number of applicants (whole country)'
    elif idx == 1: subtitle = 'Ordered by the highest job count (whole country), but lowest number of applicants'
    else: subtitle = 'Ordered by the highest views (whole country), but lowest number of applicants'
    subtitle = f'<br><sup>{subtitle}</sup>'
    fig = px.bar(
        data_frame = df,
        x = 'title',
        y = f'avg_applies',
        title = 'Average Applicants Based on Job Title' + subtitle,
        color = 'state',
        # text_auto = True,
        color_discrete_sequence = px.colors.qualitative.Pastel,
        labels = {
            'title': 'Job Position',
            'state': 'State',
            f'avg_applies': f'Number of Applicants'
        },
        # Extra data to show
        hover_data = [
            'state',

            'count',
            'sum_count',

            'salary',
            'avg_salary',

            'applies',
            # f'avg_applies',

            'views',
            f'avg_views',

            'ratio_count_appl',
            'avg_ratio_count_appl',

            'ratio_views_appl',
            'avg_ratio_views_appl',
        ]
    )
    fig.update_traces(
        hovertemplate = '<br>'.join([
            '<b>%{x}</b><br>',

            '<b>Selected State (%{customdata[0]})</b>',
            'Job Count=%{customdata[1]:.0f}',
            'Average Monthly Salary (USD)=%{customdata[3]:.0f}',
            f'Average Number of Applicants=' + '%{customdata[5]:.0f}',
            f'Average Number of Views=' + '%{customdata[6]:.0f}',
            'Job Count-Applicants Ratio=%{customdata[8]:.5f}',
            'Views-Applicants Ratio=%{customdata[10]:.5f}<br>',

            '<b>Whole Country (US)</b>',
            'Total Job Count=%{customdata[2]:.0f}',
            'Average Monthly Salary (USD)=%{customdata[4]:.0f}',
            f'Average Number of Applicants=' + '%{y:.0f}',
            f'Average Number of Views=' + '%{customdata[7]:.0f}',
            'Job Count-Applicants Ratio=%{customdata[9]:.5f}',
            'Views-Applicants Ratio=%{customdata[11]:.5f}',
            '<extra></extra>'
        ])
    )
    # https://plotly.com/python/dropdowns/
    button_list = []
    for i in fig.data:
        button_list.append(
            dict(
                label = i.name, # type: ignore
                method = 'restyle',
                args = [{
                    # Filter jobs based on state
                    'visible': [ i.name == j.name for j in fig.data ] # type: ignore
                }]
            )
        )
    # Sort dropdown list alphabetically
    # button_list = sorted(button_list, key = lambda i: i['label'])
    # Add whole country at the beginning of dropdown
    button_list.insert(
        0,
        dict(
            label = 'Whole Country',
            method = 'restyle',
            args = [{
                # Show jobs on all states
                'visible': [ True for i in fig.data ]
            }],
        )
    )
    fig.update_layout(
        # https://plotly.com/python/reference/layout/xaxis/
        xaxis = {
            'showticklabels': False,
            # Zoom to the first 20 data
            'range': [-0.5, 20.5],
            # Doesn't seem to work
            'side': 'bottom'
        },
        # https://plotly.com/python/reference/layout/updatemenus/
        updatemenus = [{
            'buttons': button_list
        }],
        # https://plotly.com/python/reference/#layout-legend
        legend = {
            'traceorder': 'reversed'
        }
    )
    st.plotly_chart(fig)

##############################

df_13 = pd.read_csv('processed/salary_companies_highest_job_count.csv')
df_14 = pd.read_csv('processed/salary_companies_highest_top_salary.csv')

for idx, df in enumerate([df_13, df_14]):
    if idx == 0: subtitle = 'Ordered by the highest number of open jobs and average salary'
    else: subtitle = 'Ordered by the highest salary (top job position only)'
    subtitle = f'<br><sup>{subtitle}</sup>'
    fig = px.box(
        df,
        x = 'company_name',
        y = 'salary',
        color = 'company_name',
        points = 'all',
        title = 'Companies That Pay The Most' + subtitle,
        labels = {
            'company_name': 'Company',
            'salary': 'Monthly Salary (USD)'
        },
        hover_data = [
            'title',
            'applies',
            'job_count',
            'min_salary',
            'med_salary',
            'max_salary'
        ]
    )
    # https://plotly.com/python/reference/box/
    fig.update_traces(
        boxmean = True,
        # Jarak minimum antara titik berdekatan
        jitter = 0.3,
        hovertemplate = '<br>'.join([
            '<b>%{x}</b><br>',

            '<b>Selected Job</b>',
            'Job Position=%{customdata[0]}',
            'Monthly Salary (USD)=%{y:.0f}',
            'Number of Applicants=%{customdata[1]:.0f}<br>',

            '<b>Selected Company</b>',
            'Open Job Count=%{customdata[2]:.0f}',
            'Minimum Monthly Salary (USD)=%{customdata[3]:.0f}',
            'Median Monthly Salary (USD)=%{customdata[4]:.0f}',
            'Maximum Monthly Salary (USD)=%{customdata[5]:.0f}',
            '<extra></extra>'
        ])
    )
    if idx == 0: y_range = [-1000, 30000]
    else: y_range = [-1000, 100000]
    fig.update_layout(
        # https://plotly.com/python/reference/layout/xaxis/
        xaxis = {
            'showticklabels': True,
            # Zoom to the first 20 data
            'range': [-0.5, 5.5]
        },
        yaxis_range = y_range,
        showlegend = False
    )
    st.plotly_chart(fig)

##############################

df_15 = pd.read_csv('processed/job_benefits_companies.csv')
fig_15 = px.bar(
    data_frame = df_15,
    x = 'company_name',
    y = 'benefit',
    title = ''.join([
        'Companies That Provide Many Job Benefits<br>',
        '<sup>With the assumption that you are a full-time employee</sup>'
    ]),
    color = 'type',
    # text_auto = True,
    color_discrete_sequence = px.colors.qualitative.Pastel,
    labels = {
        'company_name': 'Company',
        'benefit': 'Total Benefit',
        'type': 'Benefit Type'
    },
    hover_data = [
        'type',
        'sum_benefit',
        'job_count',
        'min_salary',
        'med_salary',
        'max_salary'
    ]
)
fig_15.update_traces(
    hovertemplate = '<br>'.join([
        '<b>%{x}</b><br>',

        '<b>Benefit Related</b>',
        'Total Benefit=%{customdata[1]:.0f}',
        'Benefit Type=%{customdata[0]}<br>',

        '<b>Company Related</b>',
        'Open Job Count=%{customdata[2]:.0f}',
        'Minimum Monthly Salary (USD)=%{customdata[3]:.0f}',
        'Median Monthly Salary (USD)=%{customdata[4]:.0f}',
        'Maximum Monthly Salary (USD)=%{customdata[5]:.0f}',
        '<extra></extra>'
    ])
)
fig_15.update_layout(
    # https://plotly.com/python/reference/layout/xaxis/
    xaxis = {
        'showticklabels': False,
        # Zoom to the first 20 data
        'range': [-0.5, 20.5],
        # Doesn't seem to work
        'side': 'bottom'
    }
)
st.plotly_chart(fig_15)


df_16 = pd.read_csv('processed/job_benefits_most_common.csv')
fig_16 = px.pie(
    df_16,
    names = 'type',
    values = 'count',
    title = 'The Most Common Types of Job Benefits',
    labels = {
        'type': 'Benefit Type',
        'count': 'Open Job Count'
    }
)
st.plotly_chart(fig_16)

##############################

df_17 = pd.read_csv('processed/salary_target_jobs.csv')
fig_17 = px.box(
    df_17,
    x = 'med_salary',
    title = 'Salary Distribution of Data Related Jobs',
    points = 'all',
    labels = {
        'med_salary': 'Monthly Salary (USD)'
    },
    hover_data = [
        'company_name',
        'title',
        'formatted_work_type',
        'formatted_experience_level',
        'location',
        'remote_allowed',
        'applies',
        'views'
    ]
)
# https://plotly.com/python/box-plots/
fig_17.update_traces(
    boxmean = True,
    jitter = 0.3,
    hovertemplate = '<br>'.join([
        '<b>%{customdata[1]}</b><br>',

        'Company=%{customdata[0]}',
        'Location=%{customdata[4]}',
        'Remote Allowed=%{customdata[5]}<br>',

        'Monthly Salary (USD)=%{x:.0f}',
        'Employment Type=%{customdata[2]}',
        'Experience Level=%{customdata[3]}<br>',

        'Number of Applicants=%{customdata[6]:.0f}',
        'Number of Views=%{customdata[7]:.0f}',
        '<extra></extra>'
    ])
)
st.plotly_chart(fig_17)

##############################

st.stop()