<jupyter_start><jupyter_text>Transform Task<jupyter_code>%matplotlib inline
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import scrapbook as sb
import seaborn as sns
pd.options.display.max_rows=1000
pd.options.display.max_columns=100
plt.rcParams['figure.figsize'] = [9, 6]<jupyter_output><empty_output><jupyter_text>Read in data set limited to just teachers:<jupyter_code>teachers = pd.read_csv('/home/pybokeh/Downloads/teachers.csv')<jupyter_output><empty_output><jupyter_text>Looks like teachers for the most part work about 6 months out of the year:<jupyter_code>teachers['DAYS_WORKED'].value_counts()
teachers['DAYS_WORKED'].hist()
sns.despine();
sns.distplot(teachers['DAYS_WORKED'].values)
sns.despine()<jupyter_output><empty_output><jupyter_text>Also curious about hours worked per day:<jupyter_code>teachers['HOURS_WORKED_PER_DAY'].value_counts()
teachers['HOURS_WORKED_PER_DAY'].hist()
sns.despine();
sns.distplot(teachers['HOURS_WORKED_PER_DAY'].values)
sns.despine()<jupyter_output><empty_output><jupyter_text>So it appears most teachers work at least 7 hours per day  Based on these findings, we should probably limit our data set to "full-time" teachers.  We will define full-time to mean having worked at least 180 days out of the year and at least 7 hours per day:  Limit to just full-time teachers:<jupyter_code>full_time = teachers.query("HOURS_WORKED_PER_DAY >=7 and DAYS_WORKED >= 180")<jupyter_output><empty_output><jupyter_text>Let's calculate hourly rate:<jupyter_code>full_time = full_time.assign(TOTAL_HOURS_WORKED = full_time['DAYS_WORKED'] * full_time['HOURS_WORKED_PER_DAY'])
full_time = full_time.assign(HRLY_RATE = full_time['PAY_AMOUNT'] / full_time['TOTAL_HOURS_WORKED'])
full_time.shape
full_time.head()
full_time.info()<jupyter_output><empty_output><jupyter_text>Let's now make pivot table to obtain median pay and median hourly rate:<jupyter_code>teachers_by_district = full_time.pivot_table(index=['DISTRICT_NAME'], values=['PAY_AMOUNT','HRLY_RATE'],
                                            aggfunc=['median'])
teachers_by_district<jupyter_output><empty_output><jupyter_text>Let's remove the unnecessary column row:<jupyter_code>teachers_by_district.columns
teachers_by_district.columns.set_levels(['MEDIAN_HRLY_RATE', 'MEDIAN_PAY'], level=1, inplace=True)
teachers_by_district
teachers_by_district.columns = teachers_by_district.columns.droplevel(level=0)
teachers_by_district<jupyter_output><empty_output><jupyter_text>Let's add district name and county to our data set:<jupyter_code>district_info = full_time[['DISTRICT_NAME', 'COUNTY']].drop_duplicates()
district_info.head()
teachers_by_district = pd.merge(teachers_by_district, district_info, how='left', left_index=True,
                               right_on=['DISTRICT_NAME'])
teachers_by_district.head()<jupyter_output><empty_output><jupyter_text>Finally, let's add ```COUNT``` column:<jupyter_code>counts_by_district = full_time.pivot_table(index='DISTRICT_NAME', values=['PAY_AMOUNT'], aggfunc='count')
counts_by_district.rename({'PAY_AMOUNT': 'COUNT'}, axis='columns', inplace=True)
counts_by_district
teachers_by_district = pd.merge(teachers_by_district, counts_by_district, how='left', left_on=['DISTRICT_NAME'],
                               right_index=True)
teachers_by_district<jupyter_output><empty_output><jupyter_text>Let's view our final data, sorted by median pay in descending order:<jupyter_code>teachers_by_district.sort_values(by='MEDIAN_PAY', ascending=False)<jupyter_output><empty_output><jupyter_text>and view our data, sorted by median hourly rate in descending order:<jupyter_code>teachers_by_district.sort_values(by='MEDIAN_HRLY_RATE', ascending=False)
top_median_salary = teachers_by_district.sort_values(by='MEDIAN_PAY', ascending=False)
top_median_hrly_rate = teachers_by_district.sort_values(by='MEDIAN_HRLY_RATE', ascending=False)
top_median_salary[:20]<jupyter_output><empty_output><jupyter_text>Let's modify our index label to be 1 to n:<jupyter_code>top_median_salary = top_median_salary[['DISTRICT_NAME', 'COUNTY', 'MEDIAN_HRLY_RATE', 'MEDIAN_PAY', 'COUNT']]
top_median_salary.index = list(range(1, len(top_median_salary.index) +1))
top_median_salary[:20]
top_median_hrly_rate = top_median_hrly_rate[['DISTRICT_NAME', 'COUNTY', 'MEDIAN_HRLY_RATE', 'MEDIAN_PAY', 'COUNT']]
top_median_hrly_rate.index = list(range(1, len(top_median_hrly_rate.index) +1))
top_median_hrly_rate[:20]
top_20_median_salary = top_median_salary[:20].reset_index(drop=True)
top_20_median_salary.index += 1
top_20_median_salary<jupyter_output><empty_output><jupyter_text>Persist the top 20 median salary html report source data by "glue"ing it to this notebook:<jupyter_code>sb.glue("HTML_Report", top_20_median_salary.to_html())<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11331385642737897, 'fraction_numerical': 0.009390651085141904, 'mean_word_length': 8.964656964656964, 'pattern_counts': {'":': 0, '<': 57, '<?xml version=': 0, '>': 59, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '659941', 'license': 'MIT', 'n_tokens_mistral': 1824, 'n_tokens_neox': 1701, 'n_words': 284, 'path': 'jupyter_notebooks/papermill/etl/output/Transform.ipynb', 'repo_name': 'manual123/Nacho-Jupyter-Notebooks', 'size': '4792'}