# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3.7 - Spark (local)
#     language: python
#     name: spark-3-python
# ---

# # 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 <code>csv</code> file.**
#
# To get started, we download the entire available data from https://unstats.un.org/sdgs/indicators/database/ and call it <code>un_data.csv</code>.
#
#
# 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 <code>csv</code> files.** 

# Let's start with the usual imports and loading the data set.

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']

# 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.

list(all_data)

# 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.

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:]

# 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.

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)

# 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.

#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')

# 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.

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']

# 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.

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']

# ## Cleaning up and transforming all country data frames into the same dimensions
#
# We 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. <code>,</code>, <code> = </code>, <code>N</code>, etc. We replace them with appropriate values, i.e. <code>0</code>, 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 <code>NaNs</code>.
# 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 <code>NaNs</code>.

# 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 <span style="color:red"> 'Haiti' </span> 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.

# example
list(dict_13['Germany'])

# Firstly, we insert the missing years as columns for all groupings.

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']

# Secondly, we insert the missing series codes as rows.
#
# Let's first see how many rows do we have for <span style="color:red"> France </span>?

len(list(dict_13['Nicaragua'].index))

# Let's have all $J$ sub-indicators we want for each country as rows. We fill these rows with NaNs. 

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))

# Finally, we can save all countries as different <code>csv</code> files and as one `dict`.

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))

# Having the information file might also be helpful.

# +
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()

# ## Visualising time-series
#
# We quickly visualise the time-series to get a better idea of the characteristics of our data set.

# +
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')

# ## Data standardisation
# We 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.

# 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'])

# We better save `dict_all_std`...

# +
# 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()
# -

# ## (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 <code>csv</code> file.**
#
# To get started, we download the entire available data from http://datatopics.worldbank.org/sdgs/ and call it <code>wb_data.csv</code>.
#
#
# 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 <code>csv</code> files.** 

# +
# 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()
# -

# ## Data standardisation

# +
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'])

# We better save `dict_all_wb_std`.

# +
# 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()
# -



StarcoderdataJupyterScriptsDedupFiltered