# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Prediction of CO2 emissions from country-specific data
# # A Machine Learning project 
# ### by <NAME>
#
# ***
#
# # Stage 1: Data cleaning and preparation
#
# ***
#
# ### Notebook Contents:
# 0. Introduction - project and notebook summary, notes on the data source
# 1. Notebook setup - libraries and data import
# 2. Global data overview
# 3. Definition of the initial project goals
# 3. Data cleaning
#     - dealing with missing values
#     - transformation of the columns into a numerical data type
#     - renaming of features
#     - removing empty columns and rows
# 4. Data frame transformation
#     - melting of the data for each variable
#     - integration of the data into a suitable data frame format
# 5. Removal of missing values
#     - detection of missing values
#     - removal of missing values by filtering the columns and rows, so that minimal amount of features and rows are lost
# 5. Export the clean data frame to a file
#
# ***
#
# ## 0. Introduction
#
# ### Project summary
# **Aim of the project**:
# Analysis of country-specific data and development of machine learning models in order to predict CO2 emissions from country parameters. The project uses the publicly available dataset Climate Change Data from the World Bank Group, which provides data on the vast majority of countries over a range of years for parameters such as:
#
# * country: the vast majority of countries worldwide
# * year: ranging from 1990 to 2011
# * various emissions of greenhouse gases such as CO2, CH4, N2O, others
# * population-specific parameters: population count, urban population, population growth, etc.
# * country economic indicators: GDP, GNI, Foreign Direct Investment, etc.
# * land-related parameters: cereal yield, agricultural land, Nationally terrestrial protected areas, etc.
# * climate data: precipitations, national disasters, etc.
# * energy use
# * counts of certain types of medical personnel
# * etc.
#
# The project is divided into three stages:
#
# 1. Data cleaning and preparation
# 2. Data exploration and visualization
# 3. Predictive analysis with the Random Forest machine learning algorithm
#
# Each of the stages is described in a separate Jupyter Notebook(.ipynp file) and a derived pdf file.
#
# ***
#
# ### Notebook summary - Stage 1: Data cleaning and preparation
# **Aim of this notebook**: The subject of this particular notebook is to explain the first stage of the project - the cleaning and transformation of the available data in order to prepare it for the visualization and analysis (described in further notebooks).
#
# **Input**: excel data file from the original online source
#
# **Output**: comma separated values (CSV) file containing the cleaned data ready for visualization and analysis
#
# **Programming language**: Python 3.7
#
# **Libraries used in this notebook**: pandas, numpy
#
# ***
#
# ### Data source
#
# The used data comes from the Climate Change Data of the World Bank Group, which provides country-specific data on parameters such as CO2 emissions, energy use, population count, urban population, cereal yield, nationally terrestrial protected areas, GDP, GNI, etc.
#
#
# The dataset is publicly available at https://datacatalog.worldbank.org/dataset/climate-change-data and licenced under the <a href="https://datacatalog.worldbank.org/public-licenses#cc-by">Creative Commons Attribution 4.0 International license</a>.
#
# ***
#

# ## 1. Notebook Setup
# Import all needed libraries:

import pandas as pd
import numpy as np

# The complete Climate Change Dataset is imported into a pandas DataFrame from the downloaded file "climate_change_download_0.xls":

# +
# define the file name and the data sheet
orig_data_file = r"climate_change_download_0.xls"
data_sheet = "Data"

# read the data from the excel file to a pandas DataFrame
data_orig = pd.read_excel(io=orig_data_file, sheet_name=data_sheet)
# -

# ***
#
# ## 2. Global data overview
#
# A global overview of the imported data yields the following insights:

print("Shape of the original dataset:")
data_orig.shape

print("Available columns:")
data_orig.columns

print("Column data types:")
data_orig.dtypes

print("Overview of the first 5 rows:")
data_orig.head()

print("Descriptive statistics of the columns:")
data_orig.describe()

# In order to better understand the nature of the columns "Series code", "Series name', "SCALE" and "Decimals", it is necessary to examine their values.
#
# The following snippet prints the contents of the column *'Series name'*:

data_orig['Series name'].unique()

# Contents of the column *'Series code'*:

data_orig['Series code'].unique()

# Contents of the column *'SCALE'*:

data_orig['SCALE'].unique()

# Contents of the column *'Decimals'*:

data_orig['Decimals'].unique()

# At this point, it is unclear what are the values marked with the label *'Text'* in the *'SCALE'* and *'Decimals'* columns. These are shown in the following tow outputs:

data_orig[data_orig['SCALE']=='Text']

data_orig[data_orig['Decimals']=='Text']

# ### Findings from the global overview
#
# This global overview gives away the following facts about the available data:
# * shape: 28 columns, 13512 rows
# * all columns are of type "object" - neither numeric, nor string/text values
# * A certain amount of missing values, denoted both as NaN (not a number values) and as the string ".."
# * The rows marked as *'Text'* in the columns *'SCALE'* and *'Decimals'* do not contain any information, almost completely composed of NaN values
# * The columns represent key values such as country, but also the corresponding years and the series code/name
# * The columns *'Country name'*, *'Series code'*, *'SCALE'* and *'Decimals'* do not give any information and are therefore obsolete
# * The column *'Series name'* contains the country-specific features required for the analysis
# * The names of the features in the column *'Series name'* are clear but too long
#
#
# ***

# ## 3. Define the initial project goals
#
# The first overview of the raw data allows to define initial goals and objectives of the machine learning project. These will be refined in the future as more information insight is gained from the data. However, this initial goal definition will help develop a strategy and organize the data cleaning, transformation and visualization.
#
# The data series available can be summarized into the following country-specific parameter/feature categories:
#
# * various emissions of greenhouse gases such as CO2, CH4, N2O, others
# * population-specific parameters: population count, urban population, population growth, etc.
# * country economic indicators: GDP, GNI, Foreign Direct Investment, etc.
# * land-related parameters: cereal yield, agricultural land, Nationally terrestrial protected areas, etc.
# * climate data: precipitations, national disasters, etc.
# * energy use
# * counts of certain types of medical personnel
# * etc.
#
# Such a dataset would suggest to investigate the influence of country-specific parameters such as economic parameres, population, energy use, land use and others on climate-related data or the factors affecting the climate like emissions, precipitations, etc.
#
# **Initial goal of the machine learning project:** Analyze the relationships among these variable categories and evaluate the contribution of factors like country economy, energy use, land use, etc. on greenhouse gas emissions, precipitations, etc. Finally, develop a machine learning model capable of predicting climate-related data or emissions from the other country-specific parameters.
#
# As more data insight will be gained with along the course of the project, the definition of these goals will be refined in more detail.
#
# ***

# ## 4. Data cleaning
#
# ### Organization of the data cleaning and transformation
#
# The main aim of the data cleaning and transformation is to represent the features (the country parameters contained in the column *'Series name'*) as separate columns and to make each row identifiable by a country and a year. At the same time, it would make sense to transform the years into a single column.
#
# Additionally, it is necessary to get rid of empty rows or columns and deal with the remaining cells with missing values.
#
# For these purposes, the following tasks have to be undertaken:
#
# 1. Remove rows marked as "Text" in the "SCALE" and "Decimals" columns
# 2. Remove the unnecessary columns "Country name", "Series code", "SCALE", "Decimals"
# 3. Transform the ".." strings and emplty cells ("") into NaN values for easier recognission as missing values
# 4. Transform all data columns into a numerical data type
# 5. Rename the features in column "Series name"

# ### 4.1 Removing rows marked as "Text" in the "SCALE" and "Decimals" columns

# +
# assign the data to a new DataFrame, which will be modified
data_clean = data_orig

print("Original number of rows:")
print(data_clean.shape[0])

# remove rows characterized as "Text" in the SCALE column
data_clean = data_clean[data_clean['SCALE']!='Text']

print("Current number of rows:")
print(data_clean.shape[0])
# -

# ### 4.2 Removing the unnecessary columns "Country name", "Series code", "SCALE", "Decimals"

# +
print("Original number of columns:")
print(data_clean.shape[1])

data_clean = data_clean.drop(['Country name', 'Series code', 'SCALE', 'Decimals'], axis='columns')

print("Current number of columns:")
print(data_clean.shape[1])
# -

# ### 4.3 Transform the ".." strings and emplty cells ("") into NaN values for easier recognission as missing values

data_clean.iloc[:,2:] = data_clean.iloc[:,2:].replace({'':np.nan, '..':np.nan})

# ### 4.4 Transform all data columns into a numerical data type

# +
data_clean2 = data_clean.applymap(lambda x: pd.to_numeric(x, errors='ignore'))
# Errors are ignored in order to avoid error messages about the first two columns, which don't need to be transformed
# into numeric type anyway

print("Print the column data types after transformation:")
data_clean2.dtypes
# -

# ### 4.5 Rename the features in column "Series name"
#
# The variable/feature names in the column *'Series name'* are clear, but too long and not practical to use in the code. In order to improve that, the most relevant feature names will be renamed with shorter labels as indicated in the following table:
#
# <table>
#     <tr>
#         <td> <b>Variable name</b> </td>
#         <td> <b>Description</b> </td>
#         <td> <b>Unit</b> </td>
#     </tr>
#     <tr>
#         <td> cereal_yield </td>
#         <td> Cereal yield </td>
#         <td> kg per hectare </td>
#     </tr>
#     <tr>
#         <td> fdi_perc_gdp </td>
#         <td> Foreign direct investment, net inflows </td>
#         <td> % of GDP </td>
#     </tr>
#     <tr>
#         <td> elec_access_perc </td>
#         <td> Access to electricity </td>
#         <td> % of total population </td>
#     </tr>
#     <tr>
#         <td> en_per_gdp </td>
#         <td> Energy use per units of GDP </td>
#         <td> kg oil eq./\$1,000 of 2005 PPP \$ </td>
#     </tr>
#     <tr>
#         <td> en_per_cap </td>
#         <td> Energy use per capita </td>
#         <td> kilograms of oil equivalent </td>
#     </tr>
#     <tr>
#         <td> co2_ttl </td>
#         <td> CO2 emissions, total </td>
#         <td> KtCO2 </td>
#     </tr>
#     <tr>
#         <td> co2_per_cap </td>
#         <td> CO2 emissions, total </td>
#         <td> metric tons </td>
#     </tr>
#     <tr>
#         <td> co2_per_gdp </td>
#         <td> CO2 emissions per units of GDP </td>
#         <td> kg/\$1,000 of 2005 PPP \$ </td>
#     </tr>
#     <tr>
#         <td> other_ghg_ttl </td>
#         <td> Other GHG emissions, total </td>
#         <td> KtCO2e </td>
#     </tr>
#     <tr>
#         <td> ch4_ttl </td>
#         <td> Methane (CH4) emissions, total </td>
#         <td> KtCO2 </td>
#     </tr>
#     <tr>
#         <td> n2o_ttl </td>
#         <td> Nitrous oxide (N2O) emissions, total </td>
#         <td> KtCO2 </td>
#     </tr>
#     <tr>
#         <td> nat_emerg </td>
#         <td> Droughts, floods, extreme temps </td>
#         <td> % pop. avg. 1990-2009 </td>
#     </tr>
#     <tr>
#         <td> pop_urb_aggl_perc </td>
#         <td> Population in urban agglomerations >1million </td>
#         <td> % </td>
#     </tr>
#     <tr>
#         <td> prot_area_perc </td>
#         <td> Nationally terrestrial protected areas </td>
#         <td> % of total land area </td>
#     </tr>
#     <tr>
#         <td> gdp </td>
#         <td> Gross Domestic Product (GDP) </td>
#         <td> \$ </td>
#     </tr>
#     <tr>
#         <td> gni_per_cap </td>
#         <td> GNI per capita </td>
#         <td> Atlas \$ </td>
#     </tr>
#     <tr>
#         <td> under_5_mort_rate </td>
#         <td> Under-five mortality rate </td>
#         <td> per 1,000 </td>
#     </tr>
#     <tr>
#         <td> pop_growth_perc </td>
#         <td> Population growth </td>
#         <td> annual % </td>
#     </tr>
#     <tr>
#         <td> pop </td>
#         <td> Population </td>
#         <td> 1 </td>
#     </tr>
#     <tr>
#         <td> urb_pop_growth_perc </td>
#         <td> Urban population growth </td>
#         <td> annual % </td>
#     </tr>
#     <tr>
#         <td> urb_pop </td>
#         <td> Urban population </td>
#         <td> 1 </td>
#     </tr>
#     
# </table>

# +
# define shorter names corresponding to most relevant variables in a dictionary
chosen_vars = {'Cereal yield (kg per hectare)': 'cereal_yield',
               'Foreign direct investment, net inflows (% of GDP)': 'fdi_perc_gdp',
               'Access to electricity (% of total population)': 'elec_access_perc',
               'Energy use per units of GDP (kg oil eq./$1,000 of 2005 PPP $)': 'en_per_gdp',
               'Energy use per capita (kilograms of oil equivalent)': 'en_per_cap',
               'CO2 emissions, total (KtCO2)': 'co2_ttl',
               'CO2 emissions per capita (metric tons)': 'co2_per_cap',
               'CO2 emissions per units of GDP (kg/$1,000 of 2005 PPP $)': 'co2_per_gdp',
               'Other GHG emissions, total (KtCO2e)': 'other_ghg_ttl',
               'Methane (CH4) emissions, total (KtCO2e)': 'ch4_ttl',
               'Nitrous oxide (N2O) emissions, total (KtCO2e)': 'n2o_ttl',
               'Droughts, floods, extreme temps (% pop. avg. 1990-2009)': 'nat_emerg',
               'Population in urban agglomerations >1million (%)': 'pop_urb_aggl_perc',
               'Nationally terrestrial protected areas (% of total land area)': 'prot_area_perc',
               'GDP ($)': 'gdp',
               'GNI per capita (Atlas $)': 'gni_per_cap',
               'Under-five mortality rate (per 1,000)': 'under_5_mort_rate',
               'Population growth (annual %)': 'pop_growth_perc',
               'Population': 'pop',
               'Urban population growth (annual %)': 'urb_pop_growth_perc',
               'Urban population': 'urb_pop'
                }

# rename all variables in the column "Series name" with comprehensible shorter versions
data_clean2['Series name'] = data_clean2['Series name'].replace(to_replace=chosen_vars)
# -

# ***
#
# ## 5. Data frame transformation
#
# This is how the current data frame looks like:

data_clean2.head()

# As mentioned previously, it is necessary to transform the data frame into a suitable format - the features from the *'Series name'* column into separate columns and the years into a single column. This is done by picking the corresponding values of each feature together with their countries and years, melting these into a single column and then combine them into a new data frame with all features (basing on the same countries and years):

# +
# save the short feature names into a list of strings
chosen_cols = list(chosen_vars.values())

# define an empty list, where sub-dataframes for each feature will be saved
frame_list = []

# iterate over all chosen features
for variable in chosen_cols:
    
    # pick only rows corresponding to the current feature
    frame = data_clean2[data_clean2['Series name'] == variable]
    
    # melt all the values for all years into one column and rename the columns correspondingly
    frame = frame.melt(id_vars=['Country code', 'Series name']).rename(columns={'Country code': 'country', 'variable': 'year', 'value': variable}).drop(['Series name'], axis='columns')
    
    # add the melted dataframe for the current feature into the list
    frame_list.append(frame)


# merge all sub-frames into a single dataframe, making an outer binding on the key columns 'country','year'
from functools import reduce
all_vars = reduce(lambda left, right: pd.merge(left, right, on=['country','year'], how='outer'), frame_list)
# -

# After this transformation, the new data frame has the following layout:

all_vars.head()

# ***
#
# ## 6. Remove the remaining missing values in an optimal way
#
# Although some columns and rows with empty cells have already been deleted, there are still remaining missing values:

print("check the amount of missing values in each column")
all_vars.isnull().sum()

# The objective here is to remove as many empty cells as possible, while preserving the highest possible amount of columns and rows. Missing values can originate from missing data for certain years, countries or features (columns). For this reason, it is necessary to filter the data by these three categories and remove rows or columns with NaNs by starting with the row/column with the highest amount of missing cells. This way to clean the dataset will preserve the maximal amount of possibly valuable information in columns and rows rather than just deleting all rows with any number of missing values.
#
# Since there are multiple appearances of the unique values of countries and years, it is necessary to count the number of NaN values for each unique year and country.

# ### 6.1 Filtering the years by missing values
#
# Checking the amount of missing values for each year:

# +
all_vars_clean = all_vars

#define an array with the unique year values
years_count_missing = dict.fromkeys(all_vars_clean['year'].unique(), 0)
for ind, row in all_vars_clean.iterrows():
    years_count_missing[row['year']] += row.isnull().sum()

# sort the years by missing values
years_missing_sorted = dict(sorted(years_count_missing.items(), key=lambda item: item[1]))

# print the missing values for each year
print("missing values by year:")
for key, val in years_missing_sorted.items():
    print(key, ":", val)
# -

# The purpose of the filtering is to delete rows with a significant amount of missing values for certain countries without removing too many years. So it is important to choose the proper limit for NaN values allowed per year. The previous output suggests to pick the years between 1991 and 2008 for the further analysis:

# +
print("number of missing values in the whole dataset before filtering the years:")
print(all_vars_clean.isnull().sum().sum())
print("number of rows before filtering the years:")
print(all_vars_clean.shape[0])

# filter only rows for years between 1991 and 2008 (having less missing values)
all_vars_clean = all_vars_clean[(all_vars_clean['year'] >= 1991) & (all_vars_clean['year'] <= 2008)]

print("number of missing values in the whole dataset after filtering the years:")
print(all_vars_clean.isnull().sum().sum())
print("number of rows after filtering the years:")
print(all_vars_clean.shape[0])
# -

# ### 6.2 Filtering the countries by missing values
#
# The same procedure is applied to the filtering of countries with missing values. The following snippet shows the number of NaNs for each country.

# +
# check the amount of missing values by country

# define an array with the unique country values
countries_count_missing = dict.fromkeys(all_vars_clean['country'].unique(), 0)

# iterate through all rows and count the amount of NaN values for each country
for ind, row in all_vars_clean.iterrows():
    countries_count_missing[row['country']] += row.isnull().sum()

# sort the countries by missing values
countries_missing_sorted = dict(sorted(countries_count_missing.items(), key=lambda item: item[1]))

# print the missing values for each country
print("missing values by country:")
for key, val in countries_missing_sorted.items():
    print(key, ":", val)
# -

# This output would suggest to remove rows for countries with more than 90 missing values:

# +
print("number of missing values in the whole dataset before filtering the countries:")
print(all_vars_clean.isnull().sum().sum())
print("number of rows before filtering the countries:")
print(all_vars_clean.shape[0])


# filter only rows for countries with less than 90 missing values
countries_filter = []
for key, val in countries_missing_sorted.items():
    if val<90:
        countries_filter.append(key)

all_vars_clean = all_vars_clean[all_vars_clean['country'].isin(countries_filter)]

print("number of missing values in the whole dataset after filtering the countries:")
print(all_vars_clean.isnull().sum().sum())
print("number of rows after filtering the countries:")
print(all_vars_clean.shape[0])
# -

# ### 6.3 Checking the features (columns) for missing values
#
# The NaN values count in each column is:

all_vars_clean.isnull().sum()

# After having filtered the years and countries with the most missing values, the features *elec_access_perc*, *other_ghg_ttl*, *ch4_ttl*, *n20_ttl* and *nat_emerg* still contain a significant number of missing values, removing which will reduce the amount of observations very drastically. Therefore, the next step is to remove these columns:

# +
# remove features with more than 20 missing values

from itertools import compress

# create a boolean mapping of features with more than 20 missing values
vars_bad = all_vars_clean.isnull().sum()>20

# remove the columns corresponding to the mapping of the features with many missing values
all_vars_clean2 = all_vars_clean.drop(compress(data = all_vars_clean.columns, selectors = vars_bad), axis='columns')

print("Remaining missing values per column:")
print(all_vars_clean2.isnull().sum())
# -

# Removing the rows with the remainin missing values will not impair the size of the dataset significantly, so these rows will be deleted:

# +
# delete rows with any number of missing values
all_vars_clean3 = all_vars_clean2.dropna(axis='rows', how='any')

print("Remaining missing values per column:")
print(all_vars_clean3.isnull().sum())

print("Final shape of the cleaned dataset:")
print(all_vars_clean3.shape)
# -

# ***
#
# ## 7. Export of the cleaned data frame to a file
#
# Now that the dataset has been rearranged and cleaned of missing values, it can be exported to a csv file (without the row index) for further analysis:

# export the clean dataframe to a csv file
all_vars_clean3.to_csv('data_cleaned.csv', index=False)

# The further stages of the project are Data Visualization and Predictive Analysis, which can be found in the corresponding notebooks.
#
# ***
#
# Copyright (c) 2020 <NAME>

StarcoderdataJupyterScriptsDedupFiltered