<jupyter_start><jupyter_text>Opportunity Youth Table Visulization   Outcome:     1. Opportunity youth table    2. Opportunity youth table visualization     3. Calculate change of opportunity youth status from 2014 to 2017  Import libraty<jupyter_code>from sqlalchemy import create_engine
import pandas as pd
import matplotlib.pyplot as plt
# Set plot space as inline for inline plots and qt for external plots
%matplotlib inline<jupyter_output><empty_output><jupyter_text>Create engine<jupyter_code>engine = create_engine("postgresql:///opportunity_youth")<jupyter_output><empty_output><jupyter_text>Read SQL query the create dataframe (three age range and total)<jupyter_code>sqlpath1618 = 'sql/oy_16_18.sql'
scriptFile1618 = open(sqlpath1618,'r')
script1618 = scriptFile1618.read()
df1618 = pd.read_sql(script1618, con = engine)

sqlpath1921 = 'sql/oy_19_21.sql'
scriptFile1921 = open(sqlpath1921,'r')
script1921 = scriptFile1921.read()
df1921 = pd.read_sql(script1921, con = engine)

sqlpath2224 = 'sql/oy_22_24.sql'
scriptFile2224 = open(sqlpath2224,'r')
script2224 = scriptFile2224.read()
df2224 = pd.read_sql(script2224, con = engine)

sqlpath_total = 'sql/oy_total.sql'
scriptFile_total = open(sqlpath_total,'r')
script_total = scriptFile_total.read()
df_total = pd.read_sql(script_total, con = engine)<jupyter_output><empty_output><jupyter_text>Combine four dataframe using pd.concat, set index, and change columns names<jupyter_code>df_oy_2017 = pd.concat([df1921.iloc[:,0:1], df1618.iloc[:,1:], df1921.iloc[:,1:], df2224.iloc[:,1:], df_total.iloc[:,1:]], axis=1)
# fill N/A value 0
df_oy_2017 = df_oy_2017.fillna(0) 
# set educational attainment as index
df_oy_2017['index'] = ['No diploma', 'HS diploma or GED', 'Some college, no degree', 'Degree (Associate or higher)']
df_oy_2017 = df_oy_2017.set_index('index')
del df_oy_2017['degree']
# change columns names
df_oy_2017.rename(columns={"popluation_16_18":"16-18 Population",
                           "ratio_16_18":"16-18 100%",
                           "popluation_19_21":"19-21 Population",
                           "ratio_19_21":"19-21 100%",
                           "popluation_22_24":"22-24 Population",
                           "ratio_22_24":"22-24 100%",
                           "popluation_total":"Total Population",
                           "ratio_total":"Total 100%",
                          }, inplace = True)
df_oy_2017<jupyter_output><empty_output><jupyter_text>Export opportunity youth table as csv file<jupyter_code># df_oy_2017.to_csv(r'data/oy_table_2017.csv')
temp = df_oy_2017.T.copy()
temp.drop(index=['16-18 100%','19-21 100%','22-24 100%','Total 100%'],inplace=True)
temp['Sum'] = temp.sum(axis=1)
for i in range(len(temp.keys())):
    for j in range(len(temp.index)):
        perc = 100 * temp.iloc[j,i] / temp.iloc[len(temp.index)-1,len(temp.keys())-1]
        temp.iloc[j,i] =round(perc, 0)
df_oy_2017_percent = temp.T.copy()
df_oy_2017_percent.rename(columns={"16-18 Population":"16-18",
                           "19-21 Population":"19-21",
                           "22-24 Population":"22-24",
                           "Total Population":"Total",
                          }, inplace = True)
df_oy_2017_percent<jupyter_output><empty_output><jupyter_text>Export percentage change in 2017 as csv file<jupyter_code>#df_oy_2017_percent.to_csv(r'data/oy_table_2017_percent.csv')<jupyter_output><empty_output><jupyter_text>Create population and opportunity youth table of 2014  Total population table<jupyter_code># key in table values
total_2014 = {'16-18': [2805, 587, 46661], 
       '19-21': [7284, 2049, 32318], 
       '22-24': [8728, 2877, 36426]}
df_total_2014 = pd.DataFrame(data=total_2014, index = ['Opportunity Youth', 'Working Without Diploma', 'Not An Opportunity Youth'])
# Calculate total values
df_total_2014['Total'] = df_total_2014['16-18'] + df_total_2014['19-21'] + df_total_2014['22-24']
# Calculate percentage
df_total_2014['16-18 100%'] = round(df_total_2014['16-18']*100 / df_total_2014['16-18'].sum(),0)
df_total_2014['19-21 100%'] = round(df_total_2014['19-21']*100 / df_total_2014['19-21'].sum(),0)
df_total_2014['22-24 100%'] = round(df_total_2014['22-24']*100 / df_total_2014['22-24'].sum(),0)
df_total_2014['Total 100%'] = round(df_total_2014['Total']*100 / df_total_2014['Total'].sum(),0)
df_total_2014<jupyter_output><empty_output><jupyter_text>Export total population table of 2014 to json and csv file<jupyter_code># df_total_2014.to_json(r'data/total_population_2014.json')
# df_total_2014.to_csv(r'data/total_population_2014.csv')<jupyter_output><empty_output><jupyter_text>Create opportunity youth table of 2014<jupyter_code>oy_2014 = {'16-18': [1610, 985, 179, 31], 
       '19-21': [2048, 3349, 1666, 221], 
       '22-24': [1981, 3067, 1763, 1917]}
df_oy_2014 = pd.DataFrame(data=oy_2014, index = ['No diploma', 'HS diploma or GED', 'Some college, no degree', 'Degree (Associate or higher)'])
df_oy_2014['Total'] = df_oy_2014['16-18'] + df_oy_2014['19-21'] + df_oy_2014['22-24']
df_oy_2014['16-18 100%'] = round(df_oy_2014['16-18']*100 / df_oy_2014['16-18'].sum(),0)
df_oy_2014['19-21 100%'] = round(df_oy_2014['19-21']*100 / df_oy_2014['19-21'].sum(),0)
df_oy_2014['22-24 100%'] = round(df_oy_2014['22-24']*100 / df_oy_2014['22-24'].sum(),0)
df_oy_2014['Total 100%'] = round(df_oy_2014['Total']*100 / df_oy_2014['Total'].sum(),0)
df_oy_2014<jupyter_output><empty_output><jupyter_text>Calculate percentage change divided by population of opportunity youth in 2014<jupyter_code>temp = pd.DataFrame()
temp = df_oy_2014.T.copy()
temp.drop(index=['16-18 100%','19-21 100%','22-24 100%','Total 100%'],inplace=True)
temp['Sum'] = temp.sum(axis=1)
for i in range(len(temp.keys())):
    for j in range(len(temp.index)):
        perc = 100 * temp.iloc[j,i] / temp.iloc[len(temp.index)-1,len(temp.keys())-1]
        temp.iloc[j,i] =round(perc, 0)
df_oy_2014_percent = temp.T.copy()

df_oy_2014_percent<jupyter_output><empty_output><jupyter_text>Export percentage change in 2014 as csv file<jupyter_code># df_oy_2014_percent.to_csv(r'data/oy_table_2014_percent.csv')<jupyter_output><empty_output><jupyter_text>Export opportunity youth table of 2014 to json and csv file<jupyter_code># df_oy_2014.to_json(r'data/oy_table_2014.json')
# df_oy_2014.to_csv(r'data/oy_table_2014.csv')<jupyter_output><empty_output><jupyter_text>Visualize opportunity youth 2014 and 2017 (Percentage)<jupyter_code>plt.style.use('ggplot')
fig, axes = plt.subplots(nrows=2, ncols=2, figsize=(20,20))
fig.suptitle('Opportunity Youth Status by Age (Percentage)', fontsize=30)
plt.subplots_adjust(wspace=0.2, hspace=0.7)

for i in range(0,4):
#     subplot row and column
    row = (i)//2
    col = i%2
    ax = axes[row][col]
    
#     import 2014 and 2017 table
    df = pd.concat([df_oy_2014.iloc[:,i+4],df_oy_2017.iloc[:,2*i+1]], axis=1)
    df.plot.bar(ax=ax, fontsize=16)
    
#     age group title
    title = df_oy_2014.columns[i]
#     set title of each axes in different age 
    ax.set_title("Age:"+title.format(n), fontsize=16)
#     set label and legend
    ax.set_xlabel('Educational attainment', fontsize=20)
    ax.set_ylabel('Percent(%)', fontsize=20)
    ax.legend(['2014','2017'], title='Year', fontsize=16)
#     rotate x labels
    for tick in ax.get_xticklabels():
        tick.set_rotation(45)
    ax.set_ylim(0, 100)
    
fig.savefig('visualization/Oy_fig_percentage.png')<jupyter_output><empty_output><jupyter_text>Export each plot if needed<jupyter_code># i = 3 # 0:16-18 1:19-21 2:22-24 3:16-24
# df = pd.concat([df_oy_2014.iloc[:,i+4],df_oy_2017.iloc[:,2*i+1]], axis=1)
# df.plot.bar(figsize=(8,6))
# plt.title('Opportunity Youth Status \nAge: 16-24 (Percentage)',fontsize=22)
# plt.xlabel('Educational attainment', fontsize=16)
# plt.ylabel('Percent(%)', fontsize=16)
# plt.legend(['2014','2017'], title='Year', fontsize=16)
# plt.xticks(rotation=45)
# plt.ylim(0, 100)
# plt.savefig('visualization/Oy_fig_percentage_1624.png', bbox_inches='tight');
# ;<jupyter_output><empty_output><jupyter_text>Visualize opportunity youth 2014 and 2017 (Population)<jupyter_code>plt.style.use('ggplot')
fig, axes = plt.subplots(nrows=2, ncols=2, figsize=(20,20))
fig.suptitle('Opportunity Youth Status by Age (Population)', fontsize=30)
plt.subplots_adjust(wspace=0.2, hspace=0.7)

for i in range(0,4):
#     subplot row and column
    row = (i)//2
    col = i%2
    ax = axes[row][col]
    
#     import data needed
    df = pd.concat([df_oy_2014.iloc[:,i],df_oy_2017.iloc[:,2*i]], axis=1)
    df.plot.bar(ax=ax, fontsize=16)
#     ax.invert_yaxis()
    
#     age group title
    title = df_oy_2014.columns[i]
#     set title of each axes in different age 
    ax.set_title("Age:"+title.format(n), fontsize=16)
#     set x and y label
    ax.set_xlabel('Educational attainment', fontsize=20)
    ax.set_ylabel('Population', fontsize=20)
#     legend
    ax.legend(['2014','2017'], title='Year', fontsize=16)
#     rotate x labels
    for tick in ax.get_xticklabels():
        tick.set_rotation(45)
    
#     y axis limit
    ax.set_ylim(0, 8000)
fig.savefig('visualization/Oy_fig_population.png')<jupyter_output><empty_output><jupyter_text>Export each plot if needed<jupyter_code># i = 3 # 0:16-18 1:19-21 2:22-24 3:16-24

# df = pd.concat([df_oy_2014.iloc[:,i],df_oy_2017.iloc[:,2*i]], axis=1)
# df.plot.bar(figsize=(8,6))
# plt.title('Opportunity Youth Status \nAge: 16-24 (Population)',fontsize=22)
# plt.xlabel('Educational attainment', fontsize=16)
# plt.ylabel('Population', fontsize=16)
# plt.legend(['2014','2017'], title='Year', fontsize=16)
# plt.xticks(rotation=45)
# plt.ylim(0, 8000)
# plt.savefig('visualization/Oy_fig_population_1624.png', bbox_inches='tight');
# ;<jupyter_output><empty_output><jupyter_text>Calculate percentage change of opportunity youth status between 2014 and 2017<jupyter_code>df_oy_diff_percent = pd.DataFrame()
col_list =['16-18', '19-21', '22-24', 'Total']
for col in col_list:
    df_oy_diff_percent[col] = df_oy_2017_percent[col] - df_oy_2014_percent[col]
df_oy_diff_percent.drop(index='Sum', inplace=True)
df_oy_diff_percent<jupyter_output><empty_output><jupyter_text>Export percentage change of opportunity youth status to csv file.<jupyter_code># df_oy_diff_percent.to_csv(r'data/oy_diff_percent.csv')<jupyter_output><empty_output><jupyter_text>Visualize percentage change of opportunity youth status<jupyter_code>df_oy_diff_percent.plot.bar(figsize=(8,6))
plt.title('Change of Opportunity Youth Status',fontsize=22)
plt.xlabel('Educational attainment', fontsize=16)
plt.ylabel('Percent(%)', fontsize=16)
plt.legend(title='Age')
plt.xticks(rotation=45)
plt.savefig('visualization/ChangeOfOY_Percentage.png', bbox_inches='tight');<jupyter_output><empty_output><jupyter_text>Caculate percentage change of opportunity youth status by educational attainment<jupyter_code>df_edu = pd.concat([df_oy_2014_percent['Total'],df_oy_2017_percent['Total']], axis=1)
df_edu.columns = [2014,2017]
df_edu.drop(index='Sum', inplace=True)
df_edu.plot.bar(figsize=(8,6))
plt.title('Opportunity Youth Status by Educational Attainment',fontsize=22)
plt.xlabel('Educational attainment', fontsize=16)
plt.ylabel('Percent(%)', fontsize=16)
plt.legend(['2014','2017'], title='Year', fontsize=16)
plt.xticks(rotation=45)
plt.ylim(0, 50)
plt.savefig('visualization/ChangeOfOY_ByEdu_Percentage.png', bbox_inches='tight')
;<jupyter_output><empty_output><jupyter_text>Caculate percentage change of opportunity youth status by ages<jupyter_code># age educattion 
df_age = pd.concat([df_oy_2014_percent.loc['Sum'],df_oy_2017_percent.loc['Sum']], axis=1)
df_age.columns = [2014,2017]
df_age.drop(index='Total', inplace=True)
df_age.plot.bar(figsize=(8,6))
plt.title('Opportunity Youth Status by Age',fontsize=22)
plt.xlabel('Age', fontsize=16)
plt.ylabel('Percent(%)', fontsize=16)
plt.legend(['2014','2017'], title='Year', fontsize=16)
plt.xticks(rotation=45)
plt.ylim(0, 50)
plt.savefig('visualization/ChangeOfOY_ByAge_Percentage.png', bbox_inches='tight')
;<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15627597672485452, 'fraction_numerical': 0.10182876142975894, 'mean_word_length': 5.531487513572204, 'pattern_counts': {'":': 12, '<': 85, '<?xml version=': 0, '>': 85, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '21', 'dir': 'jupyter-structured-clean-dedup', 'id': '624658', 'license': 'MIT', 'n_tokens_mistral': 5417, 'n_tokens_neox': 4504, 'n_words': 780, 'path': 'notebooks/exploratory/oy_table_2017.ipynb', 'repo_name': 'aabrahamson3/opportunity_youth', 'size': '12030'}