<jupyter_start><jupyter_text>World Bank Ed Stats Model BuildingThis data has been collected and encoded by the World Bank as indicators for educational performance and attainment, as well as expenditure on education, since 1970. The data spans the countries of the world and aggregates some regions and socio-economic distinctions. The dataset is sparse, however, with a majority of null values. As I prepare the dataset for modeling, I have three objectives: Arrange the Data  Context and Visualizations Null Handling and Feature Selection<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import difflib
from collections import Counter

%matplotlib inline

#Open and read the CSV file to a Dataframe
data = pd.read_csv('EdStatsData.csv')

# Save a dictionary matching the indicator code to its indicator name
ind_codes = data['Indicator Code'].unique().tolist()
ind_names = data['Indicator Name'].unique().tolist()
indicator_dict = {k:v for (k,v) in zip(ind_codes, ind_names)}<jupyter_output><empty_output><jupyter_text>Arrange the DataFirst, we will remove some of the unnecessary columns, like country codes, indicator names, and future years. There are far too many indicators to check the missing values for each, so we will isolate the indicator group from the prefix to the indicator code. Then we will separate the data frame into countries, regions, and socio-economic levels. Our immediate interest is the set of countries.<jupyter_code># Remove columns for years that have yet to happen
data.drop(['Country Code', 'Indicator Name', '2020', '2025', '2030', '2035', '2040', '2045', '2050', '2055',
          '2060', '2065', '2070', '2075', '2080', '2085', '2090', '2095', '2100', 'Unnamed: 69'], axis=1, inplace=True)
data.head()
# Create a column to separate out the group from the indicator
data['Indicator Group'] = [s.split('.')[0] for s in data['Indicator Code']] 
# Separate data frame by country, region, or socio-economic distinction
regions = ['Arab World', 'East Asia & Pacific', 'East Asia & Pacific (excluding high income)', 'Euro area', 'Europe & Central Asia', 
 'Europe & Central Asia (excluding high income)', 'European Union', 'Latin America & Caribbean', 'Latin America & Caribbean (excluding high income)', 
  'Middle East & North Africa', 'Middle East & North Africa (excluding high income)', 'Middle income', 'North America', 'South Asia', 
           'Sub-Saharan Africa', 'Sub-Saharan Africa (excluding high income)', 'OECD members', 'World']

income_levels = ['Heavily indebted poor countries (HIPC)', 'High income', 'Least developed countries: UN classification', 'Low & middle income', 
 'Low income', 'Lower middle income', 'Upper middle income']

reg_df = data[data['Country Name'].isin(regions)]

inc_df = data[data['Country Name'].isin(income_levels)]

cntry_df = data[~data['Country Name'].isin(regions)]
cntry_df = cntry_df[~cntry_df['Country Name'].isin(income_levels)]

# Remove initial data frame from working memory  
del data

cntry_df.head()<jupyter_output><empty_output><jupyter_text>ReindexingNow that we have the data frame with just the countries of the world for years that have actually occurred, it is time to get the indicators set as the columns, grouped by their indicator group.<jupyter_code># Set the desired columns as indices
cntry_df.set_index(['Country Name', 'Indicator Group', 'Indicator Code'], inplace=True)
# Transpose to get the row index to be the year
df_t = cntry_df.transpose()
# Stack the Country Name column as a secondary index for the rows
df_t = df_t.stack('Country Name')
df_t.head()<jupyter_output><empty_output><jupyter_text>How much sparsity is there?Below I will look into what percentage of each indicator group contains null values to determine next steps in finding feasible indicators with which to work.<jupyter_code># Create a list of indicator groups and iterate through that list to determine the percent of null values for that group
cols = df_t.columns
lst = [e[0] for e in cols]
col_lst = list(set(lst))

# Create a dictionary with the group as the key and the percent sparsity as the value in a list for the key
group_dict = {}

for group in col_lst:
    na_pct = np.sum(df_t[group].isna().sum()) / df_t[group].size
    group_dict[group] = []
    group_dict[group].append(na_pct)
    print('{} Null Percentage: {:2f}'.format(group, na_pct))<jupyter_output>NY Null Percentage: 0.375971
BAR Null Percentage: 0.872629
PRJ Null Percentage: 0.967372
OECD Null Percentage: 0.961929
LO Null Percentage: 0.994228
XGDP Null Percentage: 0.882260
UIS Null Percentage: 0.869046
SE Null Percentage: 0.680125
HH Null Percentage: 0.988921
SH Null Percentage: 0.405946
SP Null Percentage: 0.466671
IT Null Percentage: 0.645111
SL Null Percentage: 0.801463
SABER Null Percentage: 0.998478<jupyter_text>Context That was a lot of missing data. I figured there had to be some reason for that many null values, so I did some investigation into the programs that collected the data. There were several programs that collected data in which I was uninterested or over a scope which did not meet the scope of my project.   Next StepsThe research I did is not exhaustive, but provides a good first look to help me reduce features in which I am uninterested. I want to work with data that has 10 or more years of history and could be descriptive of most countries (not just one or two regions or the set of OECD nations). This exploration also indicated where I could look to combine features or reduce them for redundancy. So now I will work on the next steps indicated in the last column of the above table.<jupyter_code># Drop columns not of interest
df_t.drop(['IT', 'SABER', 'PRJ', 'XGDP', 'OECD', 'SL', 'SH'], axis=1, inplace=True)
# Drop Learning Outcomes that are note the literacy indicator
df_t.drop([e for e in df_t['LO'] if not e.startswith('LO.EGRA')], axis=1, level=1, inplace=True)
# Create a data frame where null values are indicated by 0 all other values by 1
lo_sparsity = df_t['LO']
lo_sparsity = lo_sparsity.applymap(lambda x: 1 if -100<x<100 else 0)
lo_sparsity = lo_sparsity.T
lo_sparsity.head()
# Remove columns where there are no values and put the year in the index
lo_sparsity = lo_sparsity[['2008', '2009', '2010', '2011', '2012', '2013', '2014']]
lo_sparsity = lo_sparsity.stack(0)
lo_sparsity = lo_sparsity.unstack(0).stack()
# Drop countries with no values and create a dictionary to show which countries had how many values during this period
sp_dict = {}
for column in lo_sparsity.columns:
    if lo_sparsity[column].sum() == 0:
        lo_sparsity.drop(column, axis=1, inplace=True)
    else:
        sp_dict[column] = lo_sparsity[column].sum()
sp_dict
del lo_sparsity<jupyter_output><empty_output><jupyter_text>For over 400 indicators through the course of 7 years, I have values for 15 countries ranging from 6 values to 96 values. These indicators do not meet the criteria I was hoping for in this analysis. Therefore I will be dropping them from the data frame.<jupyter_code># Drop all LO columns as they are no longer of interest
df_t.drop('LO', axis=1, inplace=True)
# Drop the Africa Dataset, since we are interested in data for global comparison
df_t.drop([e for e in df_t['UIS'] if e.startswith('UIS.AFR')], axis=1, level=1, inplace=True)
# Create dictionaries of indicators to names and names to indicators for groups that may have common indicators
usb_dict = {k:v for k, v in indicator_dict.items() if k.startswith('UIS') or k.startswith('BAR') or k.startswith('SE')}
usb_dict = {k:v for k, v in usb_dict.items() if not k.startswith('UIS.AFR')}
reversed_usb = {v:k for k,v in usb_dict.items()}
# Create a dictionary of indicator codes of potentially similar indicators based on close matches of indicator names
u = [v for k, v in usb_dict.items() if k.startswith('UIS')]
s = [v for k, v in usb_dict.items() if k.startswith('SE')]
b = [v for k, v in usb_dict.items() if k.startswith('BAR')]
usb_list = [u, s, b]
match_dict = {}
for i in range(len(usb_list) - 1):
    for name in usb_list[i]:
        matches = difflib.get_close_matches(name, usb_list[i+1], cutoff=0.8)
        if len(usb_list) - i > 2:
            matches.extend(difflib.get_close_matches(name, usb_list[i+2], cutoff=0.8))
        for match in matches:
            if reversed_usb[match] not in match_dict.keys():
                match_dict[reversed_usb[match]] = reversed_usb[name]
len(match_dict)
# Create a data frame to compare the indicators that are potential matches by their names, the total countries polled over
# which years, and the mean value of the measurements taken by each

def matches_to_df(df, matches, indicator_dict):
    '''A function designed to take a data frame, a dictionary of close matches for column names, and a dictionary matching
    codes to long form names in order to output a data frame that compares the values of the matching columns.'''
    df_list = []
    
    for k, v in matches.items():
        code_a, name_a, code_b, name_b = k, indicator_dict[k], v, indicator_dict[v]
        
        index_1 = df.index[df['SE', k] > 0].tolist()
        index_1 = [i[0] for i in index_1]
        index_1 = Counter(index_1)
        
        fyear_a, lyear_a = min(index_1.keys()), max(index_1.keys())
        cntry_a = np.mean(list(index_1.values()))
        mean_a = np.nanmean(df['SE', k])
        
        index_2 = df.index[df['UIS', v] > 0].tolist()
        index_2 = [i[0] for i in index_2]
        index_2 = Counter(index_2)
        
        fyear_b, lyear_b = min(index_2.keys()), max(index_2.keys())
        cntry_b = np.mean(list(index_2.values()))
        mean_b = np.nanmean(df['UIS', v])
        
        df_list.append([code_a, name_a, fyear_a, lyear_a, cntry_a, mean_a, code_b, name_b, fyear_b, lyear_b, cntry_b, mean_b])
        
    new_df = pd.DataFrame(df_list, columns=['Code1', 'Name1', 'Start_Year1', 'End_Year1', 'Countries1', 'Mean1', 
                                   'Code2', 'Name2', 'Start_Year2', 'End_Year2', 'Countries2', 'Mean2'])
    return new_df

matches_df = matches_to_df(df_t, match_dict, indicator_dict)
matches_df.head()<jupyter_output><empty_output><jupyter_text>Knowing when to change approachesAfter much trial and error surrounding the above approach to algorithmically get similar indicators for combining, I have come to the realization that whatever cutoff pecentage I set, I will be left with a mix of good matches and bad matches. I cannot rely on python to decide which indicators could be combined. I will review the documentation in the EdStatsSeries.csv file to find indicators of interest to me and produce a fitting research question to investigate.  Question 1: Does increased expenditure on secondary education lead to increased graduation from secondary school?**Indicators:** Expenditure on education as a percent of GDP, expenditure on secondary as a percentage of education expenditure, graduation rate (dropout rate in secondary and survival rate primary to secondary) Question 2: Does increased expenditure on secondary education lead to increases in GDP growth?**Indicators:** Expenditure on education as % of GDP,  expenditure on secondary, tertiary enrollment, expenditure on secondary as a percentage of education expenditure, secondary graduation rate, GDP<jupyter_code># Create a copy of the data frame with only the indicators of interest based on research questions
features = df_t[['UIS', 'SE', 'NY']].copy()
features = features.droplevel(level=0, axis=1)
features = features.filter(items=['UIS.XPUBP.2', 'UIS.XPUBP.3', 'SE.XPD.SECO.ZS', 'SE.XPD.TOTL.GD.ZS',
      'UIS.DR.2.GPV.T', 'UIS.SR.2.GPV.GLAST.CP.T', 'SE.TOT.ENRR', 'UIS.GER.1T6.GPI', 'NY.GDP.MKTP.PP.KD', 'NY.GDP.MKTP.KD'])
features.describe(include=[np.number])<jupyter_output><empty_output><jupyter_text>This data frame does not have balanced density for all indicators. I will start by trying to impute values from similar columns. SE.XPD.SECO.ZS and the two UIS.XPUBP columns are all measuring the percentage of education spending dedicated to secondary education.<jupyter_code># Create a dataframe to determine where there are values and where there are nulls in these similar indicators
mask = features.filter(['UIS.XPUBP.2', 'UIS.XPUBP.3', 'SE.XPD.SECO.ZS']).applymap(lambda x: 1 if 0<x<100 else 0)
mask['values'] = mask.sum(axis=1)
mask.head()<jupyter_output><empty_output><jupyter_text>Now that we have a data frame showing where there are values and where there are nulls, let's find where the SE column has nulls but the other columns have values. We can use the indices to impute values in the features data frame.<jupyter_code># Create a list of indices where the predominant indicator is missing values but its counterparts have values
temp = mask[mask['values']>0].copy()
temp.drop(temp[temp['SE.XPD.SECO.ZS']==1].index, inplace=True)
indexes = list(zip(temp.index.levels[0][temp.index.codes[0]], temp.index.levels[1][temp.index.codes[1]]))
# Using the indices we found, we can now impute the values from the UIS columns into the SE column in features
features['SE.XPD.SECO.ZS'][indexes] = np.nanmean((features['UIS.XPUBP.2'][indexes], features['UIS.XPUBP.3'][indexes]), axis=0)
# Delete the temporary data frames we used and drop columns we no longer need
del mask, temp
features.drop(['UIS.XPUBP.2', 'UIS.XPUBP.3'], axis=1, inplace=True)
features.describe()<jupyter_output><empty_output><jupyter_text>Now that we have imputed values from similar columns, we can work to interpolate values to flesh out our features a bit more. This assumes an essentially linear relationship between points in a given feature where there are missing values between those points.<jupyter_code># Rearrange the data frame so columns are country, indicator; rows are year
features = features.unstack(0).transpose().unstack(0).copy()
# Interpolate null values in a linear fashion with a maximum of 12 nulls filled in a row
features.interpolate(method='linear', axis=0, limit=12, inplace=True)
# Nest country back under year in the row indices
features = features.stack('Country Name')
# Drop rows with nulls
features.dropna(how='any', axis=0, inplace=True)

features.describe()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09856378484933821, 'fraction_numerical': 0.022106448887637284, 'mean_word_length': 4.781440781440781, 'pattern_counts': {'":': 0, '<': 44, '<?xml version=': 0, '>': 44, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '367236', 'license': 'Apache-2.0', 'n_tokens_mistral': 4584, 'n_tokens_neox': 4243, 'n_words': 1730, 'path': 'Ed Stats Model.ipynb', 'repo_name': 'tjeffkessler/thinkful_dsflex', 'size': '14204'}