<jupyter_start><jupyter_text>Data preparation for the SDG Indicators by (1) UN and (2) WorldBank  (1) UN data set**We use UN SDG's data set and convert this data set, so every country, continent, etc. is in a separate csv file.**To get started, we download the entire available data from https://unstats.un.org/sdgs/indicators/database/ and call it un_data.csv.Let's load the data set and look at its columns and rows to figure out how it is structured.**We aim to have one pandas data frame per country, with all indicators. We save them as separate csv files.**  Let's start with the usual imports and loading the data set.<jupyter_code>import numpy as np
import pandas as pd
import math
import os
import pickle
import copy
import matplotlib.pyplot as plt
from sklearn.preprocessing import scale
import warnings
warnings.filterwarnings('ignore')
# loading data set
all_data = pd.read_csv('utils/data/data.csv', dtype=object)
# the percentage of targets we have data far
print(round(len(all_data.Target.unique())/169, 3)*100, '%')
# UN data for SDG 13
SDG13_data = pd.read_csv('utils/SDG13_data.csv', dtype=object)
# check
SDG13_data.head()
# take out data not belonging to SDG 13
sdg13_data = SDG13_data[SDG13_data['Goal']=='13']<jupyter_output><empty_output><jupyter_text>The data set is structured by indicators and years in rows in one large data frame with all countries. We would like to have one data frame per country. Hence, we first extract the names of *regional groupings*, i.e. countries, continents, etc., and the names of so-called *other groupings*.According to the UN Statistics Division, other groupings include Least Developed Countries (LDC), Land Locked Developing Countries (LLDC), Small Island Developing States (SIDS), Developed Regions, and Developing Regions. Developing Regions are Latin America and the Caribbean, South-Eastern Asia, Southern Asia, Southern Asia (excluding India), Caucasus and Central Asia, Eastern Asia (excluding Japan and China), Western Asia (exc. Armenia, Azerbaijan, Cyprus, Israel and Georgia), Eastern Asia (excluding Japan), Oceania (exc. Australia and New Zealand), Sub-Saharan Africa (inc. Sudan), and Northern Africa (exc. Sudan).**All these groupings can be subject to separate network analyses of the indicators later on.**Let's first see all different columns of our data frame before we only see these different groupings.<jupyter_code>list(all_data)<jupyter_output><empty_output><jupyter_text>We have even lots of information on a sub-indicator level and this might be subject to more detailed analyses later on. We could, e.g., indicator 4.6.1* explore by disparate age goups and by sex.\* *Indicator 4.6.1: Proportion of population in a given age group achieving at least a fixed level of proficiency in functional (a) literacy and (b) numeracy skills, by sex.*We keep this possibility open, but now, let's not go further into a sub-indicator level and see the different groupings.<jupyter_code>groupings_UN = list(SDG13_data['GeoAreaName'].unique())
groupings_UN
#all_data.replace({"Democratic People's Republic of Korea": "Korea, Dem. People's Rep.", 'Gambia': 'Gambia, The', 'United Kingdom of Great Britain and Northern Ireland': 'United Kingdom', 'Congo': 'Congo, Rep.', 'Democratic Republic of the Congo': 'Congo, Dem. Rep.', 'Czechia': 'Czech Republic', 'Iran (Islamic Republic of)': 'Iran, Islamic Rep.', "Côte d'Ivoire": "Cote d'Ivoire", 'Kyrgyzstan': 'Kyrgyz Republic', "Lao People's Democratic Republic": 'Lao PDR', 'Republic of Moldova': 'Moldova', 'Micronesia (Federated States of)': 'Micronesia, Fed. Sts.', 'Slovakia': 'Slovak Republic', 'Viet Nam': 'Vietnam', 'Egypt': 'Egypt, Arab Rep.', 'United Republic of Tanzania': 'Tanzania','United States of America': 'United States', 'Venezuela (Bolivarian Republic of)': 'Venezuela, RB', 'Yemen': 'Yemen, Rep.', 'Bahamas': 'Bahamas, The', 'Bolivia (Plurinational State of)': 'Bolivia'}, inplace=True)
sdg13_data.replace({"Republic of Korea": "Korea, Rep.", "Democratic People's Republic of Korea": "Korea, Dem. People's Rep.", 'Gambia': 'Gambia, The', 'United Kingdom of Great Britain and Northern Ireland': 'United Kingdom', 'Congo': 'Congo, Rep.', 'Democratic Republic of the Congo': 'Congo, Dem. Rep.', 'Czechia': 'Czech Republic', 'Iran (Islamic Republic of)': 'Iran, Islamic Rep.', "Côte d'Ivoire": "Cote d'Ivoire", 'Kyrgyzstan': 'Kyrgyz Republic', "Lao People's Democratic Republic": 'Lao PDR', 'Republic of Moldova': 'Moldova', 'Micronesia (Federated States of)': 'Micronesia, Fed. Sts.', 'Slovakia': 'Slovak Republic', 'Viet Nam': 'Vietnam', 'Egypt': 'Egypt, Arab Rep.', 'United Republic of Tanzania': 'Tanzania','United States of America': 'United States', 'Venezuela (Bolivarian Republic of)': 'Venezuela, RB', 'Yemen': 'Yemen, Rep.', 'Bahamas': 'Bahamas, The', 'Bolivia (Plurinational State of)': 'Bolivia'}, inplace=True)
# only take World Bank countries
c = pd.read_csv('utils/countries_wb.csv', dtype=str, delimiter=';', header=None)
countries = list(c[0])
countries
# list of keys to delete
delete_groups = []

for g in list(groupings_UN):
    if g not in countries:
        delete_groups.append(g)
        
# delete
for dg in delete_groups:
    groupings_UN.remove(dg)
# loading World Bankd data set
wb_data = pd.read_csv('utils/data/wb_data.csv', dtype=object)
wb_data.drop(wb_data.tail(5).index,inplace=True)    # 5 last rows are blank / have other info
wb_data.tail()
columns = list(wb_data.columns)
for column in columns[4:]:
    columns.append(column[:4])
    columns.remove(column)

wb_data.columns = columns
years = columns[4:]<jupyter_output><empty_output><jupyter_text>Let's now save a data frame with all of the meta-information. We delete the columns which are specific in area and time, and of course we do not want to have the values in this data frame. In the end, we delete all duplicate entries in the column **SeriesCode**. So, we are left with the information we wanted: mapping the series codes to the indicators, the Source for the data, the Units measured in, etc.<jupyter_code>info = sdg13_data.drop(columns=['GeoAreaCode', 'GeoAreaName', 'TimePeriod', 'Value', 'Time_Detail']).drop_duplicates(subset=['Indicator', 'SeriesCode'])
# check
info.head()
# list of all series codes of SDG 13
seriescodes_13 = set(list(info['SeriesCode']))
seriescodes_13
# count how many we have
len(seriescodes_13)<jupyter_output><empty_output><jupyter_text>We convert the data set into multiple small data sets by creating a dictionary that contains the groupings' names as keys. First, we create empty data frames for each key.<jupyter_code>#dict_all = {c: pd.DataFrame() for c in countries}
dict_13 = {c: pd.DataFrame() for c in countries}
# check, should be empty
#dict_all.get('Belize')
dict_13.get('Belize')<jupyter_output><empty_output><jupyter_text>Second, we replace each of the empty data frames with the data we have available for them. Note, that our dictionary will be the ensamble of all groupings.<jupyter_code>for c in countries:    # memory-intensive
    #dict_all[c] = all_data[all_data['GeoAreaName'].isin(['{}'.format(c)])]
    dict_13[c] = sdg13_data[sdg13_data['GeoAreaName'].isin(['{}'.format(c)])]
# check
dict_13['France']<jupyter_output><empty_output><jupyter_text>Now, we have one data frame per country. The next step is to have years as columns.The next cell gives us the series codes in the rows and the years in the columns. These series codes are unique descriptions of the sub-indicators and we match these series codes to indicators and all other information in a different data frame.<jupyter_code>for c in countries:
    #dict_all[c] = dict_all.get(c).pivot_table(values='Value', index='SeriesCode', columns='TimePeriod', dropna=False, aggfunc='first')
    if c not in groupings_UN:
        dict_13[c] = pd.DataFrame(index=seriescodes_13, columns=years)
    else:
        dict_13[c] = dict_13.get(c).pivot_table(values='Value', index='SeriesCode', columns='TimePeriod', dropna=False, aggfunc='first')
# check
dict_13['France']<jupyter_output><empty_output><jupyter_text>Cleaning up and transforming all country data frames into the same dimensionsWe have a couple of things to do to make our data frames workable:1. We have some values in the data frames which we do not want, as e.g. ,,  = , N, etc. We replace them with appropriate values, i.e. 0, or simply a space. 2. Some data frames have data from **1990** to **2018**, some others from **1992** to **2018**. We want to have all data frames having data from **1990** to **2018**, i.e. an equal amount of columns. The additional columns are filled with NaNs.3. Some data frames have not all indicators and sub-indicators listed, but we would like to have all of them in all data frames. These additional rows are filled with NaNs. Let's start with the first task, i.e. cleaning up the data frames.We first need to define lists for all years, i.e. **1990** to **2018** and all indicators and sub-indicators, i.e. series codes. Change  'Haiti'  in the cell below to a few other countries and you'll see that they can have different lengths. We need to bring all on the same length. We agree on having data for the **years 1990 to 2019**.Now, we insert the missing years for all groupings. We want to have NaNs in those columns.<jupyter_code># example
list(dict_13['Germany'])<jupyter_output><empty_output><jupyter_text>Firstly, we insert the missing years as columns for all groupings.<jupyter_code>for c in countries:    # memory-intensive
    for year in years:
        if year not in list(dict_13[c]):
            dict_13[c]['{}'.format(year)] = np.nan
    # having the years in order
    dict_13[c] = dict_13[c][years]
# check
dict_13['Germany']<jupyter_output><empty_output><jupyter_text>Secondly, we insert the missing series codes as rows.Let's first see how many rows do we have for  France ?<jupyter_code>len(list(dict_13['Nicaragua'].index))<jupyter_output><empty_output><jupyter_text>Let's have all $J$ sub-indicators we want for each country as rows. We fill these rows with NaNs.<jupyter_code>for c in countries:
    for seriescode in seriescodes_13:
        if seriescode not in list(dict_13[c].index):
            dict_13[c].loc[seriescode] = np.nan    # fill these rows with NaNs
# check: do we have J many?
len(list(dict_13['Nicaragua'].index))
# convert all to floats
for c in countries:
    for year in years:    
        for seriescode in seriescodes_13:
            if not isinstance(dict_13[c].loc[seriescode, year], float):
                dict_13[c].loc[seriescode, year] = float(dict_13[c].loc[seriescode, year].replace(',', '').replace('<', '').replace('>', '').replace('=', '').replace('N', '0').replace(' -   ', '0').replace('0V', '0').replace('. . .', '0'))
# double-check: are all series codes as rows?
len(list(dict_13['Nicaragua'].index))<jupyter_output><empty_output><jupyter_text>Finally, we can save all countries as different csv files and as one `dict`.<jupyter_code>if not os.path.exists('csv_original'):
    os.mkdir('csv_original')
for c in countries:
    dict_13[c].to_csv(r'csv_original/{}.csv'.format(c))<jupyter_output><empty_output><jupyter_text>Having the information file might also be helpful.<jupyter_code>if not os.path.exists('utils'):
    os.mkdir('utils')
    
info.to_csv(r'utils/info.csv')
# as one pickle file
dict13 = open('utils/data/dict_13.pkl', 'wb')
pickle.dump(dict_13, dict13)
dict13.close()
# as one pickle file
dictall = open('utils/data/dict_all.pkl', 'wb')
pickle.dump(dict_all, dictall)
dictall.close()
# CHECKPOINT
dictall = open('utils/data/dict_all.pkl', 'rb')
dict_all = pickle.load(dictall)
dictall.close()<jupyter_output><empty_output><jupyter_text>Visualising time-seriesWe quickly visualise the time-series to get a better idea of the characteristics of our data set.<jupyter_code>f, (ax, ax2) = plt.subplots(2, 1, sharex=True)
ax2.plot(list(range(2000, 2020)), dict_all['Bolivia'].loc['DC_ODA_BDVL'], color='#42B24C', linewidth=5)
ax.plot(list(range(2000, 2020)), dict_all['Bolivia'].loc['DC_TRF_TOTL'], color='#DE0E68', linewidth=5)

ax2.set_ylim(0, 251)  # biodiversity ODA
ax.set_ylim(250, 2501)  # total ODA

# hide the spines between ax and ax2
ax.spines['bottom'].set_visible(False)
ax2.spines['top'].set_visible(False)
ax.xaxis.tick_top()
ax.tick_params(labelsize=20, labeltop='off')  # don't put tick labels at the top
ax2.xaxis.tick_bottom()

plt.xticks(np.arange(2000, 2019, step=2), size=20)
ax2.tick_params(labelsize=20)

f.set_figheight(8)
f.set_figwidth(12)

plt.show()
plt.figure(figsize=(12,8))
plt.plot(list(range(2000, 2020)), dict_all['Bolivia'].loc['DC_ODA_BDVL'], color='#42B24C', linewidth=5)
plt.xticks(np.arange(2000, 2019, step=2), size=20)
plt.yticks(np.arange(0, 251, step=25), size=20)
plt.show()
plt.figure(figsize=(12,8))
plt.plot(list(range(2000, 2020)), dict_all['Bolivia'].loc['DC_TRF_TOTL'], color='#DE0E68', linewidth=5)
plt.xticks(np.arange(2000, 2019, step=2), size=20)
plt.yticks(np.arange(0, 2501, step=250), size=20)
plt.show()
plt.figure(figsize=(12,8))
plt.yticks(np.arange(0, 2001, step=250), size=20)
plt.xticks(np.arange(0, 251, step=25), size=20)
plt.plot(dict_all['Bolivia'].loc['DC_ODA_BDVL'], dict_all['Bolivia'].loc['DC_TRF_TOTL'], '--bo'); #, s=100, color='black')<jupyter_output><empty_output><jupyter_text>Data standardisationWe have saved the original data set, but it is often useful to have the data standardised. Standardising a data set involves rescaling the distribution of values so that the mean of observed values is 0 and the standard deviation is 1. Standardisation is often required by machine learning algorithms when your time series data has input values with differing scales. We create a new dictionary `dict_all_std` to keep the standardised values separate to the original ones.<jupyter_code># CHECKPOINT (we don't want to re-run the entire script every time we continue working on it)
dict_all = pickle.load(open('utils/data/dict_all.pkl', 'rb'))
dict_all_std = pickle.load(open('utils/data/dict_all_std.pkl', 'rb'))
# ~15 minutes computing time
#dict_all_std = copy.deepcopy(dict_all)
dict_13_std = copy.deepcopy(dict_13)   

for country in countries:
    for seriescode in seriescodes_13:
        dict_13_std[country].loc[seriescode] = scale(dict_13[country].loc[seriescode])
        #dict_all_std[country].loc[seriescode] = scale(dict_all[country].loc[seriescode])
#check
print('Original value', dict_13['France'].loc['VC_DSR_DAFF'])
print('-------')
print('Standardised value', dict_13_std['France'].loc['VC_DSR_DAFF'])<jupyter_output>Original value TimePeriod
1990        NaN
1991        NaN
1992        NaN
1993        NaN
1994        NaN
1995        NaN
1996        NaN
1997        NaN
1998        NaN
1999        NaN
2000        NaN
2001        NaN
2002        NaN
2003        NaN
2004        NaN
2005        NaN
2006        NaN
2007        NaN
2008        NaN
2009        NaN
2010        NaN
2011        NaN
2012        NaN
2013        NaN
2014        NaN
2015        NaN
2016    6.54773
2017     17.342
2018    12.7462
2019        NaN
Name: VC_DSR_DAFF, dtype: object
-------
Standardised value TimePeriod
1990         NaN
1991         NaN
1992         NaN
1993         NaN
1994         NaN
1995         NaN
1996         NaN
1997         NaN
1998         NaN
1999         NaN
2000         NaN
2001         NaN
2002         NaN
2003         NaN
2004         NaN
2005         NaN
2006         NaN
2007         NaN
2008         NaN
2009         NaN
2010         NaN
2011         NaN
2012         NaN
2013         NaN
2014         Na[...]<jupyter_text>We better save `dict_all_std`...<jupyter_code># as csv files per grouping
if not os.path.exists('csv_standardised'):
    os.mkdir('csv_standardised')
    
for group in groupings:
    dict_13_std[group].to_csv(r'csv_standardised/{}.csv'.format(group))

# as one pickle file
stand = open('utils/data/dict_13_std.pkl', 'wb')
pickle.dump(dict_all_std, stand)
stand.close()<jupyter_output><empty_output><jupyter_text>(2) World Bank data set**We use World Bank's data set and convert this data set, so every country, continent, etc. is in a separate csv file.**To get started, we download the entire available data from http://datatopics.worldbank.org/sdgs/ and call it wb_data.csv.Let's load the data set and look at its columns and rows to figure out how it is structured.**We aim to have one pandas data frame per country, with all indicators. We save them as separate csv files.**<jupyter_code># only execute when new indicators are added -> call new metadata file wb_info_new.csv

wb_info_new = pd.read_csv('utils/wb_info.csv', header=None, dtype=object)
print(len(wb_info_new))
# the percentage of targets we have data far
print(round(len(wb_info_new[4].unique())/169, 4)*100, '%')
# are any indicators double?
wb_info_new[wb_info_new.duplicated(subset=[2])==True]
# drop this indicator
wb_info_new.drop_duplicates(subset=[2], inplace=True)
print(len(wb_info_new))
i = 0

for code in wb_info_new[0]:
    if code not in list(wb_info[0]):
        print(code)
        i += 1

print()
print('# added indicators:', i)
print()

j = 0

for code in wb_info[0]:
    if code not in list(wb_info_new[0]):
        print(code)
        j += 1

print()
print('# deleted indicators:', j)
all_countries = list(wb_data['Country Name'].unique())
# save countries
np.savetxt('utils/countries_wb_all.csv', all_countries, delimiter=';', fmt='%s')
dict_all_wb = {country: pd.DataFrame() for country in countries}
for country in countries:
    print(country)
    dict_all_wb[country] = wb_data[wb_data['Country Name'].isin(['{}'.format(country)])]
    dict_all_wb[country] = dict_all_wb[country].drop(columns=['Country Name', 'Country Code', 'Series Name'])
    dict_all_wb[country].set_index('Series Code', inplace=True)
    dict_all_wb[country] = pd.concat([dict_all_wb[country], dict_13[country]])    # adding series codes for SDG 13
    dict_all_wb[country] = dict_all_wb[country].replace('..', np.nan).astype(float)
    dict_all_wb[country] = dict_all_wb[country].drop(index='DT.ODA.ODAT.CD')
seriescodes_wb = set(list(dict_all_wb['Germany'].index))
# saving data
for country in countries: 
    dict_all_wb[country].to_csv(r'csv_original/{}_wb.csv'.format(country))
    
# as one pickle file
dictall = open('utils/data/dict_all_wb.pkl', 'wb')
pickle.dump(dict_all_wb, dictall)
dictall.close()<jupyter_output><empty_output><jupyter_text>Data standardisation<jupyter_code>dict_all_wb_std = copy.deepcopy(dict_all_wb)

for country in countries:
    for seriescode in seriescodes_wb:
        # adding noise as representative for measurement errors
        #noise = np.random.normal(scale=0.1, size=len(dict_all_wb[country].loc[seriescode]))
        
        #dict_all_wb[country].loc[seriescode] = dict_all_wb[country].loc[seriescode] + noise
        
        dict_all_wb_std[country].loc[seriescode] = scale(dict_all_wb[country].loc[seriescode])
#check
print('Original value', dict_all_wb['Belgium'].loc['ER.H2O.FWTL.ZS'])
print('-------')
print('Standardised value', dict_all_wb_std['Belgium'].loc['ER.H2O.FWTL.ZS'])<jupyter_output>Original value 1990          NaN
1991          NaN
1992          NaN
1993          NaN
1994          NaN
1995          NaN
1996          NaN
1997    64.083333
1998          NaN
1999          NaN
2000          NaN
2001          NaN
2002    56.125000
2003          NaN
2004          NaN
2005          NaN
2006          NaN
2007    51.783333
2008          NaN
2009          NaN
2010          NaN
2011          NaN
2012    50.016667
2013          NaN
2014          NaN
2015          NaN
2016          NaN
2017          NaN
2018          NaN
2019          NaN
Name: ER.H2O.FWTL.ZS, dtype: float64
-------
Standardised value 1990         NaN
1991         NaN
1992         NaN
1993         NaN
1994         NaN
1995         NaN
1996         NaN
1997    1.580306
1998         NaN
1999         NaN
2000         NaN
2001         NaN
2002    0.114715
2003         NaN
2004         NaN
2005         NaN
2006         NaN
2007   -0.684838
2008         NaN
2009         NaN
2010         NaN
2011         NaN
2012   [...]<jupyter_text>We better save `dict_all_wb_std`.<jupyter_code># as csv files per grouping
if not os.path.exists('csv_standardised'):
    os.mkdir('csv_standardised')
    
for country in countries:
    dict_all_wb_std[country].to_csv(r'csv_standardised/{}_wb.csv'.format(country))

# as one pickle file
stand = open('utils/data/dict_all_wb_std.pkl', 'wb')
pickle.dump(dict_all_wb_std, stand)
stand.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11766715470961445, 'fraction_numerical': 0.04299658369936554, 'mean_word_length': 4.2420056280378615, 'pattern_counts': {'":': 7, '<': 76, '<?xml version=': 0, '>': 77, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '261058', 'license': 'MIT', 'n_tokens_mistral': 7388, 'n_tokens_neox': 6525, 'n_words': 2139, 'path': '1_data_preparation.ipynb', 'repo_name': 'felix-laumann/SDG-dataset', 'size': '20490'}