H-2B Visa Analysis by Job Categories

Published

May 5, 2023

Code

import pandas as pd
import numpy as np
import altair as alt
import openpyxl

Code

h2b2011 = pd.read_excel('../../data/h2b_certification_decisions/H-2B_FY2011.xlsx')
h2b2011 = h2b2011[['CASE_STATUS','SOC_CODE','SOC_NAME','EMPLOYER_STATE']]
h2b2011 = h2b2011.dropna()
h2b2011['SOC_CODE'] = h2b2011['SOC_CODE'].astype(int)

h2b2012 = pd.read_excel('../../data/h2b_certification_decisions/H-2B_FY2012.xlsx')
h2b2012 = h2b2012[['CASE_STATUS','SOC_CODE','SOC_NAME','EMPLOYER_STATE']]
h2b2012 = h2b2012.dropna()
h2b2012['SOC_CODE'] = h2b2012['SOC_CODE'].astype(int)

h2b2013 = pd.read_excel('../../data/h2b_certification_decisions/H-2B_FY2013.xls')
h2b2013 = h2b2013[['CASE_STATUS','SOC_CODE','SOC_NAME','EMPLOYER_STATE']]
h2b2013 = h2b2013.dropna()
h2b2013['SOC_CODE'] = h2b2013['SOC_CODE'].astype(int)
h2b2013['CASE_STATUS'] = h2b2013['CASE_STATUS'].str.upper()
h2b2013.drop(h2b2013.loc[h2b2013['CASE_STATUS']=='WITHDRAWN'].index, inplace=True)

h2b2014 = pd.read_excel('../../data/h2b_certification_decisions/H-2B_FY14_Q4.xlsx')
h2b2014 = h2b2014[['CASE_STATUS','SOC_CODE','SOC_NAME','EMPLOYER_STATE']]
h2b2014 = h2b2014.dropna()
h2b2014['SOC_CODE'] = h2b2014['SOC_CODE'].astype(int)
h2b2014['CASE_STATUS'] = h2b2014['CASE_STATUS'].str.upper()
h2b2014.drop(h2b2014.loc[h2b2014['CASE_STATUS']=='CERTIFICATION EXPIRED'].index, inplace=True)
h2b2014.drop(h2b2014.loc[h2b2014['CASE_STATUS']=='PARTIAL CERTIFICATION EXPIRED'].index, inplace=True)
h2b2014.drop(h2b2014.loc[h2b2014['CASE_STATUS']=='WITHDRAWN'].index, inplace=True)
h2b2014['CASE_STATUS'] = h2b2014['CASE_STATUS'].replace('CERTIFICATION','CERTIFIED')
h2b2014['CASE_STATUS'] = h2b2014['CASE_STATUS'].replace('PARTIAL CERTIFICATION','PARTIAL CERTIFIED')

h2b2015 = pd.read_excel('../../data/h2b_certification_decisions/H-2B_Disclosure_Data_FY15_Q4.xlsx')
h2b2015 = h2b2015[['CASE_STATUS','SOC_CODE','SOC_TITLE','EMPLOYER_STATE']]
h2b2015 = h2b2015.dropna()
h2b2015['SOC_CODE'] = h2b2015['SOC_CODE'].astype(int)
h2b2015['CASE_STATUS'] = h2b2015['CASE_STATUS'].str.upper()
h2b2015.drop(h2b2015.loc[h2b2015['CASE_STATUS']=='CERTIFICATION EXPIRED'].index, inplace=True)
h2b2015.drop(h2b2015.loc[h2b2015['CASE_STATUS']=='PARTIAL CERTIFICATION EXPIRED'].index, inplace=True)
h2b2015.drop(h2b2015.loc[h2b2015['CASE_STATUS']=='WITHDRAWN'].index, inplace=True)
h2b2015['CASE_STATUS'] = h2b2015['CASE_STATUS'].replace('CERTIFICATION','CERTIFIED')
h2b2015['CASE_STATUS'] = h2b2015['CASE_STATUS'].replace('PARTIAL CERTIFICATION','PARTIAL CERTIFIED')
h2b2015 = h2b2015.rename(columns={"SOC_TITLE": "SOC_NAME"})

h2b2016 = pd.read_excel('../../data/h2b_certification_decisions/H-2B_Disclosure_Data_FY16.xlsx')
h2b2016 = h2b2016[['CASE_STATUS','SOC_CODE','SOC_TITLE','EMPLOYER_STATE']]
h2b2016 = h2b2016.dropna()
h2b2016['SOC_CODE'] = h2b2016['SOC_CODE'].astype(int)
h2b2016['CASE_STATUS'] = h2b2016['CASE_STATUS'].str.upper()
h2b2016.drop(h2b2016.loc[h2b2016['CASE_STATUS']=='CERTIFICATION EXPIRED'].index, inplace=True)
h2b2016.drop(h2b2016.loc[h2b2016['CASE_STATUS']=='PARTIAL CERTIFICATION EXPIRED'].index, inplace=True)
h2b2016.drop(h2b2016.loc[h2b2016['CASE_STATUS']=='WITHDRAWN'].index, inplace=True)
h2b2016['CASE_STATUS'] = h2b2016['CASE_STATUS'].replace('CERTIFICATION','CERTIFIED')
h2b2016['CASE_STATUS'] = h2b2016['CASE_STATUS'].replace('PARTIAL CERTIFICATION','PARTIAL CERTIFIED')
h2b2016 = h2b2016.rename(columns={"SOC_TITLE": "SOC_NAME"})


h2b2017 = pd.read_excel('../../data/h2b_certification_decisions/H-2B_FY2017.xlsx')
h2b2017 = h2b2017[['CASE_STATUS','SOC_CODE','SOC_NAME','EMPLOYER_STATE']]
h2b2017 = h2b2017.dropna()
h2b2017['SOC_CODE'] = h2b2017['SOC_CODE'].astype(int)
h2b2017['CASE_STATUS'] = h2b2017['CASE_STATUS'].str.upper()
h2b2017.drop(h2b2017.loc[h2b2017['CASE_STATUS']=='CERTIFICATION EXPIRED'].index, inplace=True)
h2b2017.drop(h2b2017.loc[h2b2017['CASE_STATUS']=='PARTIAL CERTIFICATION EXPIRED'].index, inplace=True)
h2b2017.drop(h2b2017.loc[h2b2017['CASE_STATUS']=='WITHDRAWN'].index, inplace=True)
h2b2017['CASE_STATUS'] = h2b2017['CASE_STATUS'].replace('CERTIFICATION','CERTIFIED')
h2b2017['CASE_STATUS'] = h2b2017['CASE_STATUS'].replace('PARTIAL CERTIFICATION','PARTIAL CERTIFIED')

h2b2018 = pd.read_excel('../../data/h2b_certification_decisions/H-2B_Disclosure_Data_FY2018_EOY.xlsx')
h2b2018 = h2b2018[['CASE_STATUS','SOC_CODE','SOC_TITLE','EMPLOYER_STATE']]
h2b2018 = h2b2018.dropna()
h2b2018['SOC_CODE'] = h2b2018['SOC_CODE'].astype(int)
h2b2018['CASE_STATUS'] = h2b2018['CASE_STATUS'].str.upper()
h2b2018.drop(h2b2018.loc[h2b2018['CASE_STATUS']=='CERTIFICATION EXPIRED'].index, inplace=True)
h2b2018.drop(h2b2018.loc[h2b2018['CASE_STATUS']=='PARTIAL CERTIFICATION EXPIRED'].index, inplace=True)
h2b2018.drop(h2b2018.loc[h2b2018['CASE_STATUS']=='WITHDRAWN'].index, inplace=True)
h2b2018['CASE_STATUS'] = h2b2018['CASE_STATUS'].replace('CERTIFICATION','CERTIFIED')
h2b2018['CASE_STATUS'] = h2b2018['CASE_STATUS'].replace('PARTIAL CERTIFICATION','PARTIAL CERTIFIED')
h2b2018['CASE_STATUS'] = h2b2018['CASE_STATUS'].replace('REJECTED','DENIED')
h2b2018 = h2b2018.rename(columns={"SOC_TITLE": "SOC_NAME"})


h2b2019 = pd.read_excel('../../data/h2b_certification_decisions/H-2B_Disclosure_Data_FY2019.xlsx')
h2b2019 = h2b2019[['CASE_STATUS','SOC_CODE','SOC_TITLE','EMPLOYER_STATE']]
h2b2019 = h2b2019.dropna()
h2b2019['SOC_CODE'] = h2b2019['SOC_CODE'].astype(int)
h2b2019['CASE_STATUS'] = h2b2019['CASE_STATUS'].str.upper()
h2b2019.drop(h2b2019.loc[h2b2019['CASE_STATUS']=='DETERMINATION ISSUED  CERTIFICATION (RETURNED)'].index, inplace=True)
h2b2019.drop(h2b2019.loc[h2b2019['CASE_STATUS']=='DETERMINATION ISSUED  PARTIAL CERTIFICATION (RETURNED)'].index, inplace=True)
h2b2019.drop(h2b2019.loc[h2b2019['CASE_STATUS']=='WITHDRAWN'].index, inplace=True)
h2b2019['CASE_STATUS'] = h2b2019['CASE_STATUS'].replace('DETERMINATION ISSUED  CERTIFICATION','CERTIFIED')
h2b2019['CASE_STATUS'] = h2b2019['CASE_STATUS'].replace('DETERMINATION ISSUED  PARTIAL CERTIFICATION','PARTIAL CERTIFIED')
h2b2019['CASE_STATUS'] = h2b2019['CASE_STATUS'].replace('DETERMINATION ISSUED  REJECTED','DENIED')
h2b2019['CASE_STATUS'] = h2b2019['CASE_STATUS'].replace('DETERMINATION ISSUED  DENIED','DENIED')
h2b2019 = h2b2019.rename(columns={"SOC_TITLE": "SOC_NAME"})

dfs = [h2b2011,h2b2012,h2b2013,h2b2014,h2b2015,h2b2016,h2b2017,h2b2018,h2b2019]
for i, df in enumerate(dfs): 
    df['YEAR'] = 2011 + i
    
h2b = pd.concat([h2b2011,h2b2012,h2b2013,h2b2014,h2b2015,h2b2016,h2b2017,h2b2018,h2b2019], ignore_index=True)
h2b.drop(h2b.loc[h2b['CASE_STATUS']=='DENIED'].index, inplace=True)
h2b.drop(h2b.loc[h2b['CASE_STATUS']=='PARTIAL CERTIFIED'].index, inplace=True)
h2b = h2b.reset_index(drop=True)

Code

counts1 = h2b.groupby('SOC_NAME').size().reset_index(name='count')
sorted_counts1 = counts1.sort_values(by='count', ascending=False)
top_ten = sorted_counts1.head(10) #Top ten SOC_NAME and counts
filtered_h2b = h2b[h2b['SOC_NAME'].isin(top_ten['SOC_NAME'])] #Dataset with only top ten SOC_NAME

counts = filtered_h2b.groupby('EMPLOYER_STATE').size().reset_index(name='count')
sorted_counts = counts.sort_values(by='count', ascending=False)
top_20 = sorted_counts.head(20)
final_filtered_h2b = filtered_h2b[filtered_h2b['EMPLOYER_STATE'].isin(top_20['EMPLOYER_STATE'])] #Dataset with only top ten SOC_NAME & top 20 states

year_state_soc_counts = final_filtered_h2b.groupby(['YEAR', 'EMPLOYER_STATE', 'SOC_NAME'])['CASE_STATUS'].count()
top_soc_names = year_state_soc_counts.groupby(['YEAR', 'EMPLOYER_STATE']).nlargest(30).reset_index(level=[0, 1], drop=True)
top_soc_names = top_soc_names.to_frame(name='H2B_Visa_Num').reset_index()

Code

area=(alt.Chart(top_soc_names
                   ).mark_area(opacity=0.6).encode(
    x=alt.X('YEAR:O',
            axis=alt.Axis(labelAngle=0
                          )),y='sum(H2B_Visa_Num):Q'
                          ,color=alt.Color('SOC_NAME:N',
                                           scale=alt.Scale(scheme='category20c'),
                                           legend=alt.Legend(title='Job Category')),
                                           tooltip=[alt.Tooltip('SOC_NAME:N', title="H-2B Job Types"),alt.Tooltip('YEAR:O', title="Year"
                                                                                                                  ),alt.Tooltip('sum(H2B_Visa_Num):Q', title="Summary of number of H-2B Issued"
                                                                                                                                )]).properties(width=500,height=400
                                                                                                                                               ))


area.title ="H-2B Visa Job Category Trend, 2011-2019"
area.encoding.x.title = 'Year'
area.encoding.y.title = 'Number of H-2B Visa Issued'

bar=(alt.Chart(top_soc_names
               ).mark_bar().encode(
    y='sum(H2B_Visa_Num):Q',
    x=alt.X('EMPLOYER_STATE:N',
            axis=alt.Axis(labelAngle=0),
            sort=alt.EncodingSortField(field='H2B_Visa_Num',order='descending')
            ),color=alt.Color('SOC_NAME:N',
                              scale=alt.Scale(scheme='category20c')
                              ),tooltip=[alt.Tooltip('SOC_NAME:N', title="H-2B Job Types"),alt.Tooltip('YEAR:O', title="Year"),alt.Tooltip('H2B_Visa_Num:Q', title="Number of H-2B Issued")])).properties(width=500,
                                                                                              height=400)
bar.title ="H-2B Visa by State, 2011-2019"
bar.encoding.x.title = 'State'
bar.encoding.y.title = 'Number of H-2B Visa Issued'

area|bar

# Figure 3: Left: H-2B program job category statstics by year. Right: H-2B program job category statistics by state. Both plots include tooltips