<jupyter_start><jupyter_text>Exploring the H-2B Visa ProgrammeH-2B visas are nonimmigrant visas, which allow foreign nationals to enter the U.S. temporarily and engage in nonagricultural employment which is seasonal, intermittent, a peak load need, or a one-time occurrence.**Summary** It turns out that Texas has the highest need for foreign unskilled employees. However, it is a Salmon farm  in Alaska that has requested the most and only offering them a wage of 10$ an hour.<jupyter_code>import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
df = pd.read_excel("H-2B_Disclosure_Data_FY15_Q4.xlsx")
df.head()
#df.info()<jupyter_output><empty_output><jupyter_text>**1. How many requests did the Office of Foreign Labor Certification (OFLC) receive in 2015?**<jupyter_code>df['CASE_NUMBER'].count()<jupyter_output><empty_output><jupyter_text>**2. How many jobs did that regard in total? And how many full time positions?**<jupyter_code>df['NBR_WORKERS_REQUESTED'].sum()
df.groupby('FULL_TIME_POSITION')['NBR_WORKERS_REQUESTED'].sum()<jupyter_output><empty_output><jupyter_text>**3. How many jobs did the ETA National Processing Center actually certify?**<jupyter_code>df['NBR_WORKERS_CERTIFIED'].sum()
df.groupby('FULL_TIME_POSITION')['NBR_WORKERS_CERTIFIED'].sum()<jupyter_output><empty_output><jupyter_text>**4. What was the average pay?<jupyter_code>df.groupby('BASIC_UNIT_OF_PAY')['PREVAILING_WAGE'].mean()
df.groupby('BASIC_UNIT_OF_PAY')['BASIC_UNIT_OF_PAY'].count()<jupyter_output><empty_output><jupyter_text>The majority of the jobs are payed hourly at an average rate of 12.65 $ an hour. ** 5. Who earned the least? And where are these people actually doing? **<jupyter_code>worst_wage = df[df['BASIC_UNIT_OF_PAY'] == 'Hour'].sort_values(by='PREVAILING_WAGE', ascending=True).head()<jupyter_output><empty_output><jupyter_text>This table displays the lowest payed jobs for which no workers were certified.<jupyter_code>worst_wage[['BASIC_UNIT_OF_PAY', 'PREVAILING_WAGE', 'EMPLOYER_NAME', 'JOB_TITLE', 'WORKSITE_CITY', 'NBR_WORKERS_REQUESTED', 'NBR_WORKERS_CERTIFIED']]

lowest_wages_accepted = df[df['NBR_WORKERS_CERTIFIED'] != 0].sort_values(by='PREVAILING_WAGE', ascending=True).head()<jupyter_output><empty_output><jupyter_text>And this table shows that landscape laborers are the ones that are earning the least.<jupyter_code>lowest_wages_accepted[['BASIC_UNIT_OF_PAY', 'PREVAILING_WAGE', 'EMPLOYER_NAME', 'JOB_TITLE', 'WORKSITE_CITY', 'NBR_WORKERS_REQUESTED', 'NBR_WORKERS_CERTIFIED']]<jupyter_output><empty_output><jupyter_text>**6. What was the most common unit of pay (daily, weekly, monthly)?**<jupyter_code>df.groupby('BASIC_UNIT_OF_PAY')['BASIC_UNIT_OF_PAY'].count()<jupyter_output><empty_output><jupyter_text>**7. Work our total pay amount payed to H-2B laborers?**<jupyter_code>#df.groupby('BASIC_UNIT_OF_PAY')['PREVAILING_WAGE'].describe()
#df.groupby('PREVAILING_WAGE').count()<jupyter_output><empty_output><jupyter_text>**Approx.** Count * Mean (Year, Week, Month, Hour(8)(33 Million, Bi-Weekly (180'000) 40 million $. **8. Were there any foreign companies hiring foreign workers in the US? If yes, work out averages by nation.**<jupyter_code>df.groupby('EMPLOYER_COUNTRY')['EMPLOYER_COUNTRY'].count()<jupyter_output><empty_output><jupyter_text>**9. Most common job title. Graph this.**<jupyter_code>#x = df.groupby('JOB_TITLE')['JOB_TITLE'].value_counts()
df['JOB_TITLE'].value_counts().head(10)
plt.style.use('ggplot')
df['JOB_TITLE'].value_counts(ascending=True).tail(10).plot(kind='barh')
plt.savefig("Top_Jobs.svg")
##Is there an efficient way for Pandas to clean the data? Merge "Landscape Laborer" with "LANDSCAPE LABORER" etc.?<jupyter_output><empty_output><jupyter_text>**10. Which US states have the largest need for unskilled workers? Make a graph of this.**<jupyter_code>#x = df['EMPLOYER_STATE'].value_counts(ascending=False).head(10) * df['NBR_WORKERS_REQUESTED'].sum()
df['EMPLOYER_STATE'].value_counts(ascending=False).head(10).plot(kind='bar')
plt.savefig("semand_in_states.svg")
#x = df['EMPLOYER_STATE'].value_counts(ascending=False).head(10) * df['NBR_WORKERS_REQUESTED'].sum()
df['EMPLOYER_STATE'].value_counts(ascending=True).head(10).plot(kind='bar')
plt.savefig("demand_in_states.svg")
Workers_in_state_count = df.groupby('EMPLOYER_STATE')['NBR_WORKERS_REQUESTED'].sum()
Workers_in_state_count.sort_values(ascending=True).tail(10).plot(kind='barh', legend='NBR_WORKERS_REQUESTED')
plt.savefig("workers_requestet_in_states.svg")<jupyter_output><empty_output><jupyter_text>**11. Which industries had the largest need?**<jupyter_code>#changing df['NAIC_CODE'] from non_null object into int
#This .fillna(0.0) is magic. I found it here: 
#http://stackoverflow.com/questions/21291259/convert-floats-to-ints-in-pandas
#df['NAIC_CODE'] = df['NAIC_CODE'].fillna(0.0).astype(int)
#But it turns out, it only works for my one fill. Not on the other. Why?<jupyter_output><empty_output><jupyter_text>Importing the NAIC_Codes from [here](http://www.census.gov/cgi-bin/sssd/naics/naicsrch?chart=2012).<jupyter_code>NAIC_CODEs = pd.read_excel("6-digit_2012_Code.xls")
NAIC_CODEs.info()
#Changing the NAIC_Codesfrom non-null object into float64
#NAIC_CODEs['NAICS12'] = df['NAIC_CODE'].fillna(0.0).astype(int)
NAIC_CODEs.head()
#And now reimporting the original file. 
df = pd.read_excel("H-2B_Disclosure_Data_FY15_Q4.xlsx")
#now in the NAIC_CODE is a Float64 in the cells we want to merge.
df_merged = df.merge(NAIC_CODEs, how = 'left', left_on = 'NAIC_CODE', right_on ='NAICS2012') 
#df_merged.info()
df_merged['Industry'].value_counts().head(10)
workers_by_industry = df_merged.groupby('Industry')['NBR_WORKERS_REQUESTED'].sum()
workers_by_industry.sort_values(ascending=True).tail(10).plot(kind='barh', legend='NBR_WORKERS_REQUESTED')
plt.savefig("workers_by_industry.svg")<jupyter_output><empty_output><jupyter_text>**12. Which companies had the largest need? Compare acceptance/denials of each company.**<jupyter_code>df['EMPLOYER_NAME'].value_counts().head(5)
company_workers_demand = df.groupby('EMPLOYER_NAME')['NBR_WORKERS_REQUESTED'].sum()
company_workers_demand.sort_values(ascending=True).tail(10).plot(kind='barh')
plt.savefig("company_workers_demand.svg")
company_workers_demand = df.groupby('EMPLOYER_NAME')['NBR_WORKERS_CERTIFIED'].sum()
company_workers_demand.sort_values(ascending=True).tail(10).plot(kind='barh')
plt.savefig("company_workers_demand.svg")<jupyter_output><empty_output><jupyter_text>***BONUS*** Looking into [Silver Bay Seafoods](http://www.silverbayseafoods.com/about.html) and [UK International Soccer Campus](http://www.uksocca.com/).  Silver Bay's claim: [Silver Bays's "...primary strength is in its combination of having a state of the art processing plant and favorable logistics to support its operations; competent management and key personnel; an established fish buying system; and ownership by fishermen who represent over 80% of the committed fishing effort."] How much does the company pay it's workers on average?<jupyter_code>SILVER_BAY_SEAFOODS = df[df['EMPLOYER_NAME'] == '<NAME>, LLC']
SILVER_BAY_SEAFOODS[['JOB_TITLE', 'PREVAILING_WAGE', 'HOURLY_WORK_SCHEDULE_AM', 'HOURLY_WORK_SCHEDULE_PM', 'OVERTIME_RATE_FROM', 'OVERTIME_RATE_TO', 'NATURE_OF_TEMPORARY_NEED', 'NBR_WORKERS_REQUESTED', 'NBR_WORKERS_CERTIFIED']]
SOCCER_CAMPS = df[df['EMPLOYER_NAME'] == 'UK International Soccer Camps']
SOCCER_CAMPS[['JOB_TITLE', 'PREVAILING_WAGE', 'HOURLY_WORK_SCHEDULE_AM', 'HOURLY_WORK_SCHEDULE_PM', 'OVERTIME_RATE_FROM', 'OVERTIME_RATE_TO', 'NATURE_OF_TEMPORARY_NEED', 'NBR_WORKERS_REQUESTED', 'NBR_WORKERS_CERTIFIED']]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.137845267705197, 'fraction_numerical': 0.01387616180128289, 'mean_word_length': 9.324324324324325, 'pattern_counts': {'":': 0, '<': 70, '<?xml version=': 0, '>': 70, 'https://': 0, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '555273', 'license': 'MIT', 'n_tokens_mistral': 2966, 'n_tokens_neox': 2806, 'n_words': 521, 'path': '08/H2B_Applications/.ipynb_checkpoints/H-2B_Visa_Programme_2015-checkpoint.ipynb', 'repo_name': 'barjacks/foundations-homework', 'size': '7652'}