<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Analyzing the Annual Disclosure Data on the H-1B Program
Data Bootcamp Final Project, Spring 2017
Jinho Huh & Yifan Ma
Introduction
The H-1B program applies to employers seeking to hire nonimmigrant aliens as workers in specialty occupations. The number of H-1B visa applications filed has increased significantly for the past five years. International students who want to work in the United States after graduation may wonder what positions are associated with most H-1B visas and what states have most employees who are on H-1B visas. Therefore, we would like to look into the latest annual disclosure data and address a few meaningful questions that will hopefully provide some insights regarding career choices for international students.
Data Sources
The annual disclosure data for different programs are easily accessible from the official website of United States Department of Labor, Employment & Training Administration, Office of Foreign Labor Certification.
We are going to analyze the annual disclosure data on LCA programs (H-1B, H-1B1, E-3) for the fiscal year of 2016.
Anyone interested in other related datasets can go to OFLC Performance Data.
2014 Regional Price Parities by State published in July, 2016 are retrieved through U.S. Department of Commerce, Bureau of Economic Analysis' interactive data application and will be used when addressing questions related to cost of living. To download the exact same data set, please visit BEA Interactive Data
Step1:  Data Input
1. Annual Disclosure Data
The annual disclosure data on LCA programs for the fiscal year of 2016 are stored in an Excel file and will be accessed directly from the web. (Note
Step2:  Office of Foreign Labor Certification (OFLC) publishes a supplementary document that lists variable names and provides a detailed description of each variable. Click here to learn more about the structure of the dataset.
2. Regional Price Parities
Data on 2014 Regional Price Parities by State are stored in an Excel file and will be accessed from our own computers.
Step3:  Working with Subsets of the Annual Disclosure Data
Step4:  This dataset has more than half a million rows. It is more feasible to work with its subsets. Status associated with a particular case has four possible values
Step5:  Visa class associated with a particular case also has possible values as there are three variants of the H-1B visa. The E-3 visa is for Australian nationals while the H-1B1 visa is for citizens of Singapore and Chile.
Step6:  Of the 65,000 visas allocated to the capped H-1B program, a total of 6800 are reserved for the H-1B1
Step7:  Unit of pay is different across companies. Lacking appropriate conversion factors, we decide to only look at cases with 'Year' as unit of pay.
Step8:  What states have most employers that provide visa sponsorship?
Step9:  There are over 10,000 companies in California that provide visa sponsorship. New York comes in second with 7,093 companies that provide visa sponsorship. International students may want to pay more attention to companies based in California, New York, Texas, New Jersey and Illinois.
What positions are associated with most H-1B visas?
Step10:  Job titles may be different across companies while main responsibilities are similar. Two-thirds of the positions listed above require basic or advanced knowledge of computer science. Consulting is another area that demands a considerably large number of foreign professionals. International students who aim to work in the United States on H-1B visas after graduation may want to develop skill sets that are necessary to perform these jobs.
Among the jobs associted with most H-1B visas, we select four for further analysis
Step11:  There is a huge spread in wages even when the position is the same. For example, one business analyst gets paid 720k when another only gets paid 30k. On average, software engineer is the highest paying job in the subgroup while programmer analyst turns out to be the lowest paying.
What companies are paying significantly more than the prevailing wage?
Step12:  A company is considered paying significantly more than the prevailing wage if the difference between its proposed wage and the prevailing wage exceeds $5,0000.
Companies with most instances of paying significantly more are
Step13:  Case1
Step14:  Merging Average Wage by State & Regional Price Parities
Values in the 'WORKSITE_STATE' column above are abbreviations while values in the 'State' column of the regional price parities dataframe are full names. We find a state abbreviations table online to solve for the discrepancy.
Step15:  Since District of Columbia is not in the table, we append a row to the dataframe.
Step16:  Another discrepancy that we need to deal with is to convert values in the 'State' column of the regional price parities dataframe so that they are all in uppercase letters.
Step17:  Time to Merge & Adjust for Cost of Living!
Step18:  Based on the average wage by state without adjusting for cost of living, we may be attempted to conclude that working as a business analyst in California and Washington are not bad at all. However, California and Washington are no longer in the graph after we adjust for cost of living.
Working as a business analyst in West Virginia, Vermont and Oregon may be the best choices as these three states appear in both graphs.
Case2
<ASSISTANT_TASK:>
Python Code:
import sys
import pandas as pd                   
import matplotlib.pyplot as plt      
import datetime as dt                  
import numpy as np
import requests
from bs4 import BeautifulSoup
import seaborn as sns
import ssl
%matplotlib inline
print('Python version: ', sys.version)
print('Pandas version: ', pd.__version__)
print('Today: ', dt.date.today())
Explanation: Analyzing the Annual Disclosure Data on the H-1B Program
Data Bootcamp Final Project, Spring 2017
Jinho Huh & Yifan Ma
Introduction
The H-1B program applies to employers seeking to hire nonimmigrant aliens as workers in specialty occupations. The number of H-1B visa applications filed has increased significantly for the past five years. International students who want to work in the United States after graduation may wonder what positions are associated with most H-1B visas and what states have most employees who are on H-1B visas. Therefore, we would like to look into the latest annual disclosure data and address a few meaningful questions that will hopefully provide some insights regarding career choices for international students.
Data Sources
The annual disclosure data for different programs are easily accessible from the official website of United States Department of Labor, Employment & Training Administration, Office of Foreign Labor Certification.
We are going to analyze the annual disclosure data on LCA programs (H-1B, H-1B1, E-3) for the fiscal year of 2016.
Anyone interested in other related datasets can go to OFLC Performance Data.
2014 Regional Price Parities by State published in July, 2016 are retrieved through U.S. Department of Commerce, Bureau of Economic Analysis' interactive data application and will be used when addressing questions related to cost of living. To download the exact same data set, please visit BEA Interactive Data: Regional and follow the steps outlined below:
Click on 'Begin using the data'->'Real Personal Income and Regional Price Parities'->'Regional Price Parities (RPP1)', Choose 'States'->'All areas'&'RPPs: All items'->'2014'
We download the table in Excel format.
End of explanation
ssl._create_default_https_context = ssl._create_unverified_context
url = 'https://www.foreignlaborcert.doleta.gov/docs/Performance_Data/Disclosure/FY15-FY16/H-1B_Disclosure_Data_FY16.xlsx'
cols = list(range(5)) + [7, 10, 19, 25, 30, 32, 37]
h1b = pd.read_excel(url, usecols = cols)
h1b.head()
Explanation: Data Input
1. Annual Disclosure Data
The annual disclosure data on LCA programs for the fiscal year of 2016 are stored in an Excel file and will be accessed directly from the web. (Note: it takes a few minutes to import the entire dataset)
End of explanation
path = '/Users/TiffanyM/Downloads/RPPs2014.xls'
cols2 = [1, 2]
rpps = pd.read_excel(path,
                     skiprows = 5,
                     usecols = cols2,
                     skip_footer = 3)
rpps.columns = ['State', 'RPP']
rpps
Explanation: Office of Foreign Labor Certification (OFLC) publishes a supplementary document that lists variable names and provides a detailed description of each variable. Click here to learn more about the structure of the dataset.
2. Regional Price Parities
Data on 2014 Regional Price Parities by State are stored in an Excel file and will be accessed from our own computers.
End of explanation
h1b.shape
Explanation: Working with Subsets of the Annual Disclosure Data
End of explanation
h1bcopy = h1b.copy() #make a copy of the original dataframe
h1bcopy = h1bcopy.set_index('CASE_NUMBER')
h1b_c = h1bcopy[h1bcopy['CASE_STATUS'] == 'CERTIFIED']
h1b_c.shape
Explanation: This dataset has more than half a million rows. It is more feasible to work with its subsets. Status associated with a particular case has four possible values: Withdrawn, Certified-Withdrawn, Certified and Denied. We are only going to look at cases that have a status of 'Certified'.
End of explanation
h1b_c['VISA_CLASS'].value_counts()
Explanation: Visa class associated with a particular case also has possible values as there are three variants of the H-1B visa. The E-3 visa is for Australian nationals while the H-1B1 visa is for citizens of Singapore and Chile.
End of explanation
h1b_c2 = h1b_c[h1b_c['VISA_CLASS'] == 'H-1B']
h1b_c2['WAGE_UNIT_OF_PAY'].value_counts()
Explanation: Of the 65,000 visas allocated to the capped H-1B program, a total of 6800 are reserved for the H-1B1: 1,400 for Chile and 5,400 for Singapore. There is a separate annual quota of 10,500 E-3 visas.
Our analysis will focus on H-1B visas since E-3 and H-1B1 visas only account for 2% of the certified cases.
End of explanation
h1b_c3 = h1b_c2[h1b_c2['WAGE_UNIT_OF_PAY'] == 'Year'] #subset used for our analysis
h1b_c3.head()
Explanation: Unit of pay is different across companies. Lacking appropriate conversion factors, we decide to only look at cases with 'Year' as unit of pay.
End of explanation
states = h1b_c3.groupby('EMPLOYER_STATE')['EMPLOYER_NAME'].nunique().sort_values(ascending = False)
states.head()
Explanation: What states have most employers that provide visa sponsorship?
End of explanation
jobs = h1b_c3['JOB_TITLE'].value_counts()
jobs.head(15)
Explanation: There are over 10,000 companies in California that provide visa sponsorship. New York comes in second with 7,093 companies that provide visa sponsorship. International students may want to pay more attention to companies based in California, New York, Texas, New Jersey and Illinois.
What positions are associated with most H-1B visas?
End of explanation
job_titles = ['PROGRAMMER ANALYST', 'SOFTWARE ENGINEER', 'BUSINESS ANALYST', 'CONSULTANT']
subset1 = h1b_c3.loc[h1b_c3['JOB_TITLE'].isin(job_titles)]
wage = subset1[['JOB_TITLE', 'WAGE_RATE_OF_PAY_FROM']].sort_values('WAGE_RATE_OF_PAY_FROM', ascending = False)
wage.head()
wage.tail()
subset1.groupby('JOB_TITLE')['WAGE_RATE_OF_PAY_FROM'].mean().sort_values(ascending = False)
job_group = subset1[['JOB_TITLE', 'WAGE_RATE_OF_PAY_FROM']].groupby('JOB_TITLE')
av_wage = job_group['WAGE_RATE_OF_PAY_FROM'].agg([np.mean])
fig, ax = plt.subplots(figsize = (8, 5))
fig.suptitle('Average Wage', fontsize=14, fontweight='bold')
av_wage['mean'].plot(kind = 'barh',
                     ax=ax,
                     color = 'blue',
                     alpha = 0.6
                     )
Explanation: Job titles may be different across companies while main responsibilities are similar. Two-thirds of the positions listed above require basic or advanced knowledge of computer science. Consulting is another area that demands a considerably large number of foreign professionals. International students who aim to work in the United States on H-1B visas after graduation may want to develop skill sets that are necessary to perform these jobs.
Among the jobs associted with most H-1B visas, we select four for further analysis: Programmer Analyst, Software Engineer, Business Analyst and Consultant
Which one is the highest paying?
End of explanation
subset1_c = subset1.copy()
subset1_c['DIFFERENCE'] = subset1_c['WAGE_RATE_OF_PAY_FROM'] - subset1_c['PREVAILING_WAGE']
more = subset1_c.loc[subset1_c['DIFFERENCE'] > 50000]
top5companies = more['EMPLOYER_NAME'].value_counts().head()
fig, ax = plt.subplots(figsize = (8, 5))
fig.suptitle('Instances of Paying Significantly More', fontsize=14, fontweight='bold')
top5companies.plot(kind = 'bar',
                   ax = ax,
                   color = 'red',
                   alpha = 0.6
                   )
topjobs = more['JOB_TITLE'].value_counts()
fig, ax = plt.subplots(figsize = (8, 5))
fig.suptitle('Instances of Getting Paid Significantly More', fontsize=14, fontweight='bold')
topjobs.plot(kind = 'bar',
                   ax = ax,
                   color = 'red',
                   alpha = 0.6
                   )
Explanation: There is a huge spread in wages even when the position is the same. For example, one business analyst gets paid 720k when another only gets paid 30k. On average, software engineer is the highest paying job in the subgroup while programmer analyst turns out to be the lowest paying.
What companies are paying significantly more than the prevailing wage?
End of explanation
subset2 = subset1[['JOB_TITLE', 'WAGE_RATE_OF_PAY_FROM', 'WORKSITE_STATE']]
av_wage_state = subset2.groupby(['JOB_TITLE', 'WORKSITE_STATE'])['WAGE_RATE_OF_PAY_FROM'].mean()
av_wage_state.to_frame()
Explanation: A company is considered paying significantly more than the prevailing wage if the difference between its proposed wage and the prevailing wage exceeds $5,0000.
Companies with most instances of paying significantly more are: Google, Cedent Consulting, Microsoft, YourPeople (Zenefits) and Uber. Three of them are technology firms while two are consulting firms.
Software engineers are more likely to earn wages that well exceed the prevailing wage. Business analysts and consultants are less likely to be paid 50k more than the prevailing wage.
Adjust for Cost of Living - Case of Business Analyst & Software Engineer
End of explanation
ba = av_wage_state['BUSINESS ANALYST'].to_frame()
ba = ba.reset_index()
ba
Explanation: Case1: Business Analyst
End of explanation
url2 = 'http://www.softschools.com/social_studies/state_abbreviations/'
cl = requests.get(url2)
cl.status_code
cl_soup = BeautifulSoup(cl.content, 'html.parser')
Explanation: Merging Average Wage by State & Regional Price Parities
Values in the 'WORKSITE_STATE' column above are abbreviations while values in the 'State' column of the regional price parities dataframe are full names. We find a state abbreviations table online to solve for the discrepancy.
End of explanation
sa = pd.read_html(str(cl_soup.find_all('table')[0]), header=0)[2]
sa.loc[len(sa.index)] = ['DISTRICT OF COLUMBIA', 'DC']
sa = sa.sort_values('State')
sa = sa.reset_index(drop = True)
sa
Explanation: Since District of Columbia is not in the table, we append a row to the dataframe.
End of explanation
rpps_c = rpps.copy()
rpps_c['State'] = list(map(str.upper, rpps_c['State']))
sarpps = pd.merge(sa, rpps_c, how = 'left', on = 'State')
sarpps = sarpps.rename(columns = {'Abbreviation': 'WORKSITE_STATE'})
sarpps
Explanation: Another discrepancy that we need to deal with is to convert values in the 'State' column of the regional price parities dataframe so that they are all in uppercase letters.
End of explanation
blend = pd.merge(ba, sarpps, how = 'right', on = 'WORKSITE_STATE')
blend['Adjusted Wage'] = blend['WAGE_RATE_OF_PAY_FROM'] / blend['RPP'] * 100
blend
top5 = blend[['State', 'WAGE_RATE_OF_PAY_FROM']].sort_values('WAGE_RATE_OF_PAY_FROM', ascending = False).head()
top5 = top5.set_index('State')
fig, ax = plt.subplots(figsize = (8, 5))
fig.suptitle('Business Analyst - Average Wage by State', fontsize=14, fontweight='bold')
top5.plot(kind = 'bar',
          ax = ax,
          color = 'mediumslateblue',
          alpha = 0.8,
          legend = False
          )
top5adjust = blend[['State', 'Adjusted Wage']].sort_values('Adjusted Wage', ascending = False).head()
top5adjust = top5adjust.set_index('State')
fig, ax = plt.subplots(figsize = (8, 5))
fig.suptitle('Business Analyst - Adjusted Average Wage by State', fontsize=14, fontweight='bold')
top5adjust.plot(kind = 'bar',
                ax = ax,
                color = 'mediumslateblue',
                alpha = 0.8,
                legend = False
                )
Explanation: Time to Merge & Adjust for Cost of Living!
End of explanation
se = av_wage_state['SOFTWARE ENGINEER'].to_frame()
se = se.reset_index()
se
blend2 = pd.merge(se, sarpps, how = 'right', on = 'WORKSITE_STATE')
blend2['Adjusted Wage'] = blend2['WAGE_RATE_OF_PAY_FROM'] / blend2['RPP'] * 100
blend2
top5_ = blend2[['State', 'WAGE_RATE_OF_PAY_FROM']].sort_values('WAGE_RATE_OF_PAY_FROM', ascending = False).head()
top5_ = top5_.set_index('State')
fig, ax = plt.subplots(figsize = (8, 5))
fig.suptitle('Software Engineer - Average Wage by State', fontsize=14, fontweight='bold')
top5_.plot(kind = 'bar',
          ax = ax,
          color = 'orange',
          alpha = 0.5,
          legend = False
          )
top5adjust_ = blend2[['State', 'Adjusted Wage']].sort_values('Adjusted Wage', ascending = False).head()
top5adjust_ = top5adjust_.set_index('State')
fig, ax = plt.subplots(figsize = (8, 5))
fig.suptitle('Software Engineer - Adjusted Average Wage by State', fontsize=14, fontweight='bold')
top5adjust_.plot(kind = 'bar',
                ax = ax,
                color = 'orange',
                alpha = 0.5,
                legend = False
                )
Explanation: Based on the average wage by state without adjusting for cost of living, we may be attempted to conclude that working as a business analyst in California and Washington are not bad at all. However, California and Washington are no longer in the graph after we adjust for cost of living.
Working as a business analyst in West Virginia, Vermont and Oregon may be the best choices as these three states appear in both graphs.
Case2: Software Engineer
End of explanation
<END_TASK>