<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Exploring the H-2B Visa Programme
H-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.
Step1:  1. How many requests did the Office of Foreign Labor Certification (OFLC) receive in 2015?
Step2:  2. How many jobs did that regard in total? And how many full time positions?
Step3:  3. How many jobs did the ETA National Processing Center actually certify?
Step4:  **4. What was the average pay?
Step5:  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?
Step6:  This table displays the lowest payed jobs for which no workers were certified.
Step7:  And this table shows that landscape laborers are the ones that are earning the least.
Step8:  6. What was the most common unit of pay (daily, weekly, monthly)?
Step9:  7. Work our total pay amount payed to H-2B laborers?
Step10:  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.
Step11:  9. Most common job title. Graph this.
Step12:  10. Which US states have the largest need for unskilled workers? Make a graph of this.
Step13:  11. Which industries had the largest need?
Step14:  Importing the NAIC_Codes from here.
Step15:  12. Which companies had the largest need? Compare acceptance/denials of each company.
Step16:  BONUS Looking into Silver Bay Seafoods and UK International Soccer Campus. 
Silver Bay's claim
<ASSISTANT_TASK:>
Python 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()
Explanation: Exploring the H-2B Visa Programme
H-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.
End of explanation
df['CASE_NUMBER'].count()
Explanation: 1. How many requests did the Office of Foreign Labor Certification (OFLC) receive in 2015?
End of explanation
df['NBR_WORKERS_REQUESTED'].sum()
df.groupby('FULL_TIME_POSITION')['NBR_WORKERS_REQUESTED'].sum()
Explanation: 2. How many jobs did that regard in total? And how many full time positions?
End of explanation
df['NBR_WORKERS_CERTIFIED'].sum()
df.groupby('FULL_TIME_POSITION')['NBR_WORKERS_CERTIFIED'].sum()
Explanation: 3. How many jobs did the ETA National Processing Center actually certify?
End of explanation
df.groupby('BASIC_UNIT_OF_PAY')['PREVAILING_WAGE'].mean()
df.groupby('BASIC_UNIT_OF_PAY')['BASIC_UNIT_OF_PAY'].count()
Explanation: **4. What was the average pay?
End of explanation
worst_wage = df[df['BASIC_UNIT_OF_PAY'] == 'Hour'].sort_values(by='PREVAILING_WAGE', ascending=True).head()
Explanation: 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?
End of explanation
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()
Explanation: This table displays the lowest payed jobs for which no workers were certified.
End of explanation
lowest_wages_accepted[['BASIC_UNIT_OF_PAY', 'PREVAILING_WAGE', 'EMPLOYER_NAME', 'JOB_TITLE', 'WORKSITE_CITY', 'NBR_WORKERS_REQUESTED', 'NBR_WORKERS_CERTIFIED']]
Explanation: And this table shows that landscape laborers are the ones that are earning the least.
End of explanation
df.groupby('BASIC_UNIT_OF_PAY')['BASIC_UNIT_OF_PAY'].count()
Explanation: 6. What was the most common unit of pay (daily, weekly, monthly)?
End of explanation
#df.groupby('BASIC_UNIT_OF_PAY')['PREVAILING_WAGE'].describe()
#df.groupby('PREVAILING_WAGE').count()
Explanation: 7. Work our total pay amount payed to H-2B laborers?
End of explanation
df.groupby('EMPLOYER_COUNTRY')['EMPLOYER_COUNTRY'].count()
Explanation: 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.
End of explanation
#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.?
Explanation: 9. Most common job title. Graph this.
End of explanation
#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")
Explanation: 10. Which US states have the largest need for unskilled workers? Make a graph of this.
End of explanation
#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?
Explanation: 11. Which industries had the largest need?
End of explanation
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")
Explanation: Importing the NAIC_Codes from here.
End of explanation
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")
Explanation: 12. Which companies had the largest need? Compare acceptance/denials of each company.
End of explanation
SILVER_BAY_SEAFOODS = df[df['EMPLOYER_NAME'] == 'SILVER BAY SEAFOODS, 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']]
df_unfaelle.groupby('Jahr')[(df_unfaelle['Lichtverhältnis UAP'] == 622)]
Explanation: BONUS Looking into Silver Bay Seafoods and UK International Soccer Campus. 
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?
End of explanation
<END_TASK>