<jupyter_start><jupyter_text>Mod 1 Project - <NAME>__Objectives:__- Our first girl is to create a map the establishes a border between south king county cities and the rest of the cities.    - Based on documentation, we know that the cities in south county include: __Auburn, Burien, Covington, Des Moines, Enumclaw, Federal Way, Kent, Maple Valley, Normandy Park, Renton, Tukwila, SeaTac, White Center/Boulevard Park, and Vashon Island.__    - We used this website as a referrence: https://www.kingcounty.gov/depts/health/data/community-health-indicators/definitions.aspx    - we know there are two zip codes that overlap north and south: (98056, 98059).    - 98056 is assigned to south and 98059 is assigned to east so we will disclude this one.- We then need to establish a definition for Opportunity Youth and query our database based on this definition.    - Our definition is __young folks between the age 16 through 24 who are disengaged from both work and school__  Creating our Map: List of the libraries and modules we will import and use for this project:<jupyter_code>%matplotlib inline
import geopandas as gpd
from matplotlib import pyplot as plt 
import seaborn as sns
import pandas as pd
from IPython.display import Image<jupyter_output><empty_output><jupyter_text>we will be doing all of our queries in postico and exporting them here  Establish the mapping parameters: We will begin by establishing a border around the entire washington<jupyter_code># Import Washington PUMA Data with GeoPandas Shape data in Geometry column 
wa_puma = gpd.read_file("../../data/raw/tl_2017_53_puma10.shp") 

# Converts PUMACE10 Column from string to integer since we are reading directly from the file and not through pandas (pandas would have done this conversion automatically)
wa_puma.loc[:,'PUMACE10'] = wa_puma['PUMACE10'].astype(int)
wa_puma.loc[:,'GEOID10'] = wa_puma['GEOID10'].astype(int)

wa_puma.plot() # can add additional code beyond this line to the object to make it prettier, but not needed right now in this stage.
plt.show() # runs the object in its whole<jupyter_output><empty_output><jupyter_text>__Establishing south king county parameters:__ We queried out a list of puma id's that based off their description we consider to be south  - we used the government website as a reference point: https://www.ofm.wa.gov/washington-data-research/population-demographics/gis-data/census-geographic-files/supplemental-information-gis-files/public-use-microdata-area-maps We ran this query in sequal in order to define the south king county parameters. We will use the list of puma id values as a filter for creating our maps borders.<jupyter_code>Image('../../img_files/puma_id_query.png')
# import postico query we ran to filter out the state to washington to show all of king county's puma id's 
df_puma_id = pd.read_csv('../queries/Puma_Ids.csv') 
# We were able to query out all of king county's puma's and ran into an issue with further querying it by south puma names
# inside of our software so we will take just one extra step with pandas to query out our southern puma's
# we referenced a map on puma locations found on: https://www.ofm.wa.gov/washington-data-research/population-demographics/gis-data/census-geographic-files/supplemental-information-gis-files/public-use-microdata-area-maps
# to understand that some puma locations described as west, central still included south county cities.
# We included 
df_south_pumas = df_puma_id.sort_values('puma').query('puma_name.str.contains("South") and puma!= 11606 or puma==11610 or puma==11611')
df_south_pumas
# We utilized the loc function to define that we want all rows ':' and only the column puma and created it as a list to be able to refer to
# later in order to paint our borders
skc_puma_list= list(df_south_pumas.loc[:, 'puma'])
skc_puma_list
# Selects all PUMA codes for King County (including Seattle) PUMAs and puts into Subset
kc_puma = wa_puma.loc[(wa_puma.PUMACE10 >= 11601 ) & (wa_puma.PUMACE10 <= 11616)]

# Creates a new column, KCS and assigns a 1 if the PUMA is in S. King Country/S. Seattle, or 0 otherwise. This is
# used to map the gray portion of the figure
skc_puma = kc_puma.assign(SKC = [1 if row in skc_puma_list else 0 for row in kc_puma['PUMACE10']])
skc_puma.head(2) # we will use this to allow geopandas to draw our south county border<jupyter_output><empty_output><jupyter_text>The Map end product:<jupyter_code># Plots a map of King County, with South King County and South Seattle in color
fig, ax = plt.subplots(1, figsize=(20, 16))
ax = skc_puma.plot(ax=ax, 
              column='SKC', cmap='Accent_r', alpha = .5, linewidth=.5, edgecolor='black',  legend = False)
ax.set_title('Map of King County (South King County in Color)', fontsize = 20)
ax.set_axis_off()
fig.tight_layout
plt.show();<jupyter_output><empty_output><jupyter_text>What is considered opportunity youth? opportunity youth is classified as: young people between the age 16 through 24 who are disengaged from both work and school. In particular, we want these youth who are within the south county borders.To meet the guidelines of these parameters, we have run some queries on a few tables. __Preface:__ *due to postico technical difficulties, we could not fully query out what we needed so we did what postico would allow us to do and then we will proceed to finish off the querying and cleaning in pandas.* This was what our query syntax looked like:<jupyter_code>Image('../../img_files/pums_2017_south_county.png') # filtered based on puma's to get only the south king county cities
skc_puma_list
# we read the exported sql query we made with pandas in order to query further with pandas
df_oy_no_age = pd.read_csv('../queries/pums_2017_based_on_puma.csv')
df_opportunity_youth_south_county= df_oy_no_age.query('(agep > 15 and agep < 25) and (sch == 1) and (esr == 3 or esr == 6)').loc[:, ['puma','agep','esr', 'sch', 'schl']]
df_opportunity_youth_south_county['sch'].replace(1, "not in school", inplace=True)
df_opportunity_youth_south_county['esr'].replace((3,6),('unemployed','not in labor force'), inplace=True)

# need to filter esr
df_opportunity_youth_south_county.sort_values('puma')
print('Number of opportunity youth in south king county =', len(df_opportunity_youth_south_county))<jupyter_output>Number of opportunity youth in south king county = 391<jupyter_text>Updated Descriptive statistics on OY 2016 report1. An update of the table “Opportunity Youth Status by Age” located on page 2 of the 2016 report “Opportunity Youth in the Road Map Project Region”; and2. A visualization that highlights a trend between the 2016 report and current data. Our goal is to update this table with our 2019 data:<jupyter_code>Image("../../img_files/OY_2016_report.png")
# This data set we queryed from postico and only limited it by puma ID to ensure it 
# that only covers individuals who are apart of south king county
df_pums = pd.read_csv('../queries/pums_2017_based_on_puma.csv') 
df_pums<jupyter_output><empty_output><jupyter_text>Working without a diploma:<jupyter_code># To determine this category we queryed based on the age ranges needed 16-24.
# We then queryed based on employment status being true by accepting all esr values that don't include 3 or 6
# We finalized by setting our schl to query out not having a diploma. We defined that as college diploma so highschool diploma's are still included in the sample.
df_working_without_diploma = df_pums.loc[:,['puma','agep','esr', 'sch','schl']].query('(agep>15 and agep<25) and (esr!=3 and esr!=6) and (schl>1 and schl<16)')
df_working_without_diploma<jupyter_output><empty_output><jupyter_text>Not part of Opportunity Youth:  Group 1<jupyter_code># To create this categorical sample, we set our age boundaries 16-24
# Then we had to create two groups that qualify as not part of opportunity youth
# Group 1 are the ones who are in school but not employed 
# Group 2 are the ones who are employed and not in school
# These are the only two potential categories that can exist to disqualify someone from Opportunity Youth based on our selected age ranges
df_group1= df_pums.loc[:,['puma','agep','esr', 'sch','schl']].query('(agep>15 and agep<25) and (esr==6 or esr==3) and (sch!=1)')
df_group1.reset_index(drop=True).sort_values('agep')<jupyter_output><empty_output><jupyter_text>Group 2<jupyter_code># Group 2 are the ones who are employed and not in school
df_group2= df_pums.loc[:,['puma','agep','esr', 'sch','schl']].query('(agep>15 and agep<25) and (sch==1) and (esr!=6 or esr!=3)')
df_group2.reset_index(drop=True).sort_values('agep')<jupyter_output><empty_output><jupyter_text>Group 1 and 2 combined<jupyter_code># The concat method will literally put both tables ontop of each other this is perfect since all the columns are the same
all_dfs = [df_group1, df_group2]

# Give all df's common column names
for df in all_dfs:
    df.columns = ['puma', 'agep', 'esr', 'sch', 'schl']

df_not_oy = pd.concat(all_dfs).sort_values('agep').reset_index(drop=True)
print("There are:", len(df_not_oy),"people not classified as Opportunity Youth")<jupyter_output>There are: 2803 people not classified as Opportunity Youth<jupyter_text>Seperating Age groups: 16-18, 19-21, 22-24  working without a diploma 16-18 group:<jupyter_code># We will make an aggregation of this table in order to sum up age categories of people
# Who do not have a diploma but are working
# Our categories will be: 16-18, 19-21, 22-24
df_working_without_diploma.head()
# After aggregating each age category we need to group the catergories 
df_working_without_diploma_age_breakdown = df_working_without_diploma.sort_values('agep').groupby('agep').agg(sum_of_ages=('agep', 'count')).reset_index()
df_working_without_diploma_age_breakdown
# to create a seperate column with a list of labels with age ranges, we will use the pd.cut method and define our labels for those ages
# we set the age ranges by using a list of numbers that act as boundary points
# 15,18 - means dont include 15 but do include all the numbers between, including 18, so: 16-18
# 18,21 - means don't include 18 but do include all the numbers between including 21, so: 18-21
# 21,24 - means don't include 21 but do include all the numbers between including 24, so: 22-24

df_working_without_diploma['agep_categories'] = pd.cut(df_working_without_diploma['agep'], [15, 18, 21, 24],
                                                       labels=['16-18','19-21','22-24'])

# We then proceeded to aggregate our categories using the group by and agg functions
# We are required to reset our index to keep everything in better order.
df_age_categories= df_working_without_diploma.groupby('agep_categories').agg(age_totals=('agep', 'count'))
df_age_categories.reset_index(inplace=True)
df_age_categories<jupyter_output><empty_output><jupyter_text>Final table segment:<jupyter_code># We then created a total row at the bottom to give it a somewhat microsoft excel look
# We did this by appending  a summation of our above rows to the dataframe
# Since it is an append it will always be at the bottom of the list since a dataframes columns are technically lists
# The summation attempts to sum our agep_categories column and fails since its not an int or float so it returns a NaN value
# This is perfect as we can just fill that NaN value with Total to better visualize our goal

df_age_cat_total= df_age_categories.append(df_age_categories.sum(numeric_only=True), ignore_index=True) # add a total row to the bottom of my table
df_age_cat_total.fillna('Total:')<jupyter_output><empty_output><jupyter_text>Seperating no OY group ages: 16-18, 19-21, 22-24 Our next goal is to segregate our non opportunity youth candidates into our established age ranges.<jupyter_code>df_not_oy
# grouping this set by age:

df_not_oy_age_totals= df_not_oy.groupby('agep').agg(sum_of_ages = ('agep', 'count')).reset_index()
df_not_oy_age_totals
# now to section them by age groups:
df_not_oy['agep_categories'] = pd.cut(df_not_oy['agep'], [15, 18, 21, 24],
                                                       labels=['16-18','19-21','22-24'])

df_not_oy_ages_sumed= df_not_oy.groupby('agep_categories').agg(age_totals=('agep', 'count'))
df_not_oy_ages_sumed.reset_index(inplace=True)
df_not_oy_ages_sumed<jupyter_output><empty_output><jupyter_text>Final table segment:<jupyter_code># now to add a total column
df_not_oy_total= df_not_oy_ages_sumed.append(df_not_oy_ages_sumed.sum(numeric_only=True), ignore_index=True) # add a total row to the bottom of my table
df_not_oy_total.fillna('Total:')<jupyter_output><empty_output><jupyter_text>Opportunity youth : These values provided by our documentation will help us define each of these parameters:1. no diploma is defined as: `schl = 15`2. hs diploma  or GED is defined as: `schl = 16 and 17`3. some college, no degree is defined as: `schl =  18 and 19`4. degree(associate or higher) is defined as: `schl = 20, 21, 22, 23, 24`<jupyter_code># For clarity purposes lets rename these values to make more sense instead of just have them as numbers
# To do so we will use the replace method so in our future queries we can query by their names and not numbers

df_opportunity_youth_south_county['schl'].replace((15.0, 16.0,17.0,18.0,19.0,20.0,21.0,22.0,23.0,24.0), ('no diploma', 'HS diploma/GED', 'HS diploma/GED', 'Some College no Degree', 'Some College no Degree', 'Degree Associate or higher', 'Degree Associate or higher','Degree Associate or higher','Degree Associate or higher','Degree Associate or higher'),inplace=True)
df_oy_credentials = df_opportunity_youth_south_county.sort_values('agep').reset_index(drop=True)
df_oy_credentials<jupyter_output><empty_output><jupyter_text>Group 1 - no diploma:<jupyter_code>df_oy_no_diploma = df_oy_credentials.query('schl=="no diploma"')
df_oy_no_diploma['agep_categories'] = pd.cut(df_oy_credentials['agep'], [15, 18, 21, 24],
                                                       labels=['16-18','19-21','22-24'])
df_oy_no_diploma.head()
df_oy_no_diploma_agg = df_oy_no_diploma.groupby('agep_categories').agg(no_diploma_totals=('schl', 'count'))
df_oy_no_diploma_agg.reset_index(inplace=True)
df_oy_no_diploma_agg<jupyter_output><empty_output><jupyter_text>final table segment:<jupyter_code># This is the final segment output of our no diplomas category
df_oy_no_diploma_total= df_oy_no_diploma_agg.append(df_oy_no_diploma_agg.sum(numeric_only=True), ignore_index=True) # add a total row to the bottom of my table
df_oy_no_diploma_total.fillna('Total:')<jupyter_output><empty_output><jupyter_text>Group 2 - HS diploma or GED:<jupyter_code>df_hs_dip_or_ged = df_oy_credentials.query('schl=="HS diploma/GED"')
df_hs_dip_or_ged['agep_categories'] = pd.cut(df_hs_dip_or_ged['agep'], [15, 18, 21, 24],
                                                       labels=['16-18','19-21','22-24'])
df_hs_dip_or_ged.head()
df_hs_dip_or_ged_agg = df_hs_dip_or_ged.groupby('agep_categories').agg(diploma_or_GED_totals=('schl', 'count'))
df_hs_dip_or_ged_agg.reset_index(inplace=True)
df_hs_dip_or_ged_agg<jupyter_output><empty_output><jupyter_text>Final table segment:<jupyter_code># This is the final segment output of our high school diploma/GED category
df_hs_dip_or_ged_total = df_hs_dip_or_ged_agg.append(df_hs_dip_or_ged_agg.sum(numeric_only=True), ignore_index=True) # add a total row to the bottom of my table
df_hs_dip_or_ged_total.fillna('Total:')<jupyter_output><empty_output><jupyter_text>Group 3 - Some college, no degree:<jupyter_code>df_some_college_no_degree = df_oy_credentials.query('schl=="Some College no Degree"')
df_some_college_no_degree['agep_categories'] = pd.cut(df_oy_credentials['agep'], [15, 18, 21, 24],
                                                       labels=['16-18','19-21','22-24'])
df_some_college_no_degree.head()
df_some_college_no_degree_agg = df_some_college_no_degree.groupby('agep_categories').agg(Some_college_no_Deg_total=('schl', 'count'))
df_some_college_no_degree_agg.reset_index(inplace=True)
df_some_college_no_degree_agg<jupyter_output><empty_output><jupyter_text>Final table segment<jupyter_code># This is the final segment output of our some college but no degree category
df_some_college_no_degree_agg_total = df_some_college_no_degree_agg.append(df_some_college_no_degree_agg.sum(numeric_only=True), ignore_index=True) # add a total row to the bottom of my table
df_some_college_no_degree_agg_total.fillna('Total:')<jupyter_output><empty_output><jupyter_text>Group 4 - Degree Associate or higher:<jupyter_code>df_degree_associate_or_higher = df_oy_credentials.query('schl=="Degree Associate or higher"')
df_degree_associate_or_higher['agep_categories'] = pd.cut(df_oy_credentials['agep'], [15, 18, 21, 24],
                                                       labels=['16-18','19-21','22-24'])

df_degree_associate_or_higher.head()
df_degree_associate_or_higher_agg = df_degree_associate_or_higher.groupby('agep_categories').agg(Associates_or_higher_total=('schl', 'count'))
df_degree_associate_or_higher_agg.reset_index(inplace=True)
df_degree_associate_or_higher_agg<jupyter_output><empty_output><jupyter_text>Final table segment<jupyter_code># This is the final segment output of our college degree at associates level or higher category
# This category makes sense as there would be very little to none at the 16-18 range with an associates degree or higher
df_degree_associate_or_higher_agg_total = df_degree_associate_or_higher_agg.append(df_degree_associate_or_higher_agg.sum(numeric_only=True), ignore_index=True) # add a total row to the bottom of my table
df_degree_associate_or_higher_agg_total.fillna('Total:')<jupyter_output><empty_output><jupyter_text>Comparing updated data with last years data visualizations:  Last years data:age group 16-18<jupyter_code>opp_youth_2016 = 2805
work_without_diploma_2016 = 587
not_opp_youth_2016 = 46661
column_names = ['opp_youth', 'work_without_diploma', 'not_opp_youth']
rows = [opp_youth_2016, work_without_diploma_2016, not_opp_youth_2016]
df_2016 = pd.DataFrame(data=[rows], columns=column_names, dtype=int)
df_2016<jupyter_output><empty_output><jupyter_text>Current Year data:age group 16-18<jupyter_code>opp_youth_2018 = len(df_oy_credentials)
work_without_diploma_2018 = 187
not_opp_youth_2018 = len(df_not_oy)
column_names2 = ['opp_youth', 'work_without_diploma', 'not_opp_youth']
rows2 = [opp_youth_2018, work_without_diploma_2018, not_opp_youth_2018]
df_2018 = pd.DataFrame(data=[rows2], columns=column_names2, dtype=int)
df_2018<jupyter_output><empty_output><jupyter_text>Visuals:<jupyter_code>x = merged_df['index']
y = merged_df['2018']
z = merged_df['totals']
fig = plt.figure(figsize=(15,6))
ax1 = fig.add_subplot(121)
ax2 = fig.add_subplot(122)
ax1.bar(x, y, color='red')
ax2.bar(x,z)
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0931098696461825, 'fraction_numerical': 0.029582335727587124, 'mean_word_length': 5.739333094299032, 'pattern_counts': {'":': 0, '<': 110, '<?xml version=': 0, '>': 110, 'https://': 3, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '26', 'dir': 'jupyter-structured-clean-dedup', 'id': '199101', 'license': 'MIT', 'n_tokens_mistral': 6696, 'n_tokens_neox': 6143, 'n_words': 1931, 'path': 'notebooks/report/index.ipynb', 'repo_name': 'PaulWill92/opportunity_youth', 'size': '18807'}