%matplotlib inline
%config InlineBackend.figure_format='retina'
import matplotlib as mpl
from matplotlib import pyplot as plt
import seaborn as sns
import numpy as np
import pandas as pd
import os, sys
import warnings
warnings.filterwarnings('ignore')
sns.set_context("poster", font_scale=1.3)

"""
Explanation: Table of Contents
<p><div class="lev1"><a href="#The-dataset"><span class="toc-item-num">1&nbsp;&nbsp;</span>The dataset</a></div><div class="lev1"><a href="#Question:-Is-water-availability-and-water-use-related-to-GDP-per-capita?"><span class="toc-item-num">2&nbsp;&nbsp;</span>Question: <em>Is water availability and water use related to GDP per capita?</em></a></div><div class="lev1"><a href="#Our-plan"><span class="toc-item-num">3&nbsp;&nbsp;</span>Our plan</a></div><div class="lev1"><a href="#To-do:-Develop-investigation-themes-for-exploration"><span class="toc-item-num">4&nbsp;&nbsp;</span><strong>To do</strong>: Develop investigation themes for exploration</a></div><div class="lev1"><a href="#Wrangle"><span class="toc-item-num">5&nbsp;&nbsp;</span>Wrangle</a></div><div class="lev2"><a href="#Things-to-consider-doing"><span class="toc-item-num">5.1&nbsp;&nbsp;</span>Things to consider doing</a></div><div class="lev2"><a href="#Load-the-data"><span class="toc-item-num">5.2&nbsp;&nbsp;</span>Load the data</a></div><div class="lev2"><a href="#Research-the-variables"><span class="toc-item-num">5.3&nbsp;&nbsp;</span>Research the variables</a></div><div class="lev2"><a href="#Describe-the-panel"><span class="toc-item-num">5.4&nbsp;&nbsp;</span>Describe the panel</a></div><div class="lev2"><a href="#Ways-to-look-at-this-data"><span class="toc-item-num">5.5&nbsp;&nbsp;</span>Ways to look at this data</a></div><div class="lev2"><a href="#Slicing"><span class="toc-item-num">5.6&nbsp;&nbsp;</span>Slicing</a></div><div class="lev3"><a href="#For-a-given-time-slice"><span class="toc-item-num">5.6.1&nbsp;&nbsp;</span>For a given time slice</a></div><div class="lev3"><a href="#For-a-given-country"><span class="toc-item-num">5.6.2&nbsp;&nbsp;</span>For a given country</a></div><div class="lev3"><a href="#By-variable"><span class="toc-item-num">5.6.3&nbsp;&nbsp;</span>By variable</a></div><div class="lev3"><a href="#Time-series-for-given-country-and-variable"><span class="toc-item-num">5.6.4&nbsp;&nbsp;</span>Time series for given country and variable</a></div><div class="lev2"><a href="#By-region"><span class="toc-item-num">5.7&nbsp;&nbsp;</span>By region</a></div><div class="lev2"><a href="#Exercises"><span class="toc-item-num">5.8&nbsp;&nbsp;</span>Exercises</a></div><div class="lev1"><a href="#To-do:-Update-investigation-themes"><span class="toc-item-num">6&nbsp;&nbsp;</span><strong>To do</strong>: Update investigation themes</a></div>
End of explanation
"""


data = pd.read_csv('../../data/aquastat/aquastat.csv.gzip', compression='gzip')

data.head()

data.info()

"""
Explanation: The dataset
<img src="figures/fao.jpg" alt="FAO" width="290" align="right">
We will be using the Food and Agriculture Organization (FAO) of the United Nation's AQUASTAT dataset. 
From FAO: 
FAO's three main goals are:
1. The eradication of hunger, food insecurity and malnutrition
2. The elimination of poverty and the driving forward of economic and social progress for all
3. The sustainable management and utilization of natural resources, including land, water, air, climate and genetic resources for the benefit of present and future generations. 
To support these goals, Article 1 of its constitution requires FAO to "collect, analyse, interpret and disseminate information related to nutrition, food and agriculture". Thus AQUASTAT started, with the aim to contribute to FAO's goals through the collection, analysis and dissemination of information related to water resources, water uses and agricultural water management, with an emphasis on countries in Africa, Asia, Latin America, and the Caribbean.
FAO offers data, metadata, reports, country profiles, river basin profiles, regional analyses, maps, tables, spatial data, guidelines, and other tools on:
* Water resources: internal, transboundary, total
* Water uses: by sector, by source, wastewater
* Irrigation: location, area, typology, technology, crops
* Dams: location, height, capacity, surface area
* Water-related institutions, policies and legistation
http://www.fao.org/nr/water/aquastat/data/query/index.html
Question: Is water availability and water use related to GDP per capita?
Our plan
<img src="figures/branches.jpg" alt="Crisp-DM" width="390" align="right">
Exploratory data analysis consists of the following major tasks, which we present linearly here because each task doesn't make much sense to do without the ones prior to it. However, in reality, you are going to constantly jump around from step to step.  You may want to do all the steps for a subset of the variables first. Or often, an observation will bring up a question you want to investigate and you'll branch off and explore to answer that question before returning down the main path of exhaustive EDA.

Form hypotheses/develop investigation themes to explore 
Wrangle data 
Assess quality of data 
Profile data 
Explore each individual variable in the dataset 
Assess the relationship between each variable and the target 
Assess interactions between variables 
Explore data across many dimensions 

Throughout the entire analysis you want to:
* Capture a list of hypotheses and questions that come up for further exploration.
* Record things to watch out for/ be aware of in future analyses. 
* Show intermediate results to colleagues to get a fresh perspective, feedback, domain knowledge. Don't do EDA in a bubble! Get feedback throughout especially from people removed from the problem and/or with relevant domain knowledge. 
* Position visuals and results together. EDA relies on your natural pattern recognition abilities so maximize what you'll find by putting visualizations and results in close proximity. 
To do: Develop investigation themes for exploration
Write down hypotheses, things you need to find out to answer the question.
Wrangle
Things to consider doing
Make your data tidy
1. Each variable forms a column
2. Each observation forms a row
3. Each type of observational unit forms a table
Transform data
Sometimes you will need to transform your data to be able to extract information from it. This step will usually occur after some of the other steps of EDA unless domain knowledge can inform these choices beforehand. Transforms include:  

Log: when data is highly skewed (versus normally distributed like a bell curve), sometimes it has a log-normal distribution and taking the log of each data point will normalize it. 
Binning of continuous variables: Binning continuous variables and then analyzing the groups of observations created can allow for easier pattern identification. Especially with non-linear relationships. 
Simplifying of categories: you really don't want more than 8-10 categories within a single data field. Try to aggregate to higher-level categories when it makes sense. 

Load the data
End of explanation
"""


data[['variable','variable_full']].drop_duplicates()

"""
Explanation: Research the variables
End of explanation
"""


data.country.nunique()

countries = data.country.unique()

"""
Explanation: Describe the panel
199 unique countries involved
End of explanation
"""


data.time_period.nunique()

"""
Explanation: For 12 time periods
End of explanation
"""


time_periods = data.time_period.unique()
print(time_periods)

mid_periods = range(1960,2017,5)

"""
Explanation: Each 5 years in length since 1958
End of explanation
"""


data[data.variable=='total_area'].value.isnull().sum()

"""
Explanation: Dataset is unbalanced because there is not data for every country at every time period (more on missing data in the next notebook).
End of explanation
"""


def time_slice(df, time_period):

    # Only take data for time period of interest
    df = df[df.time_period==time_period] 

    # Pivot table 
    df = df.pivot(index='country', columns='variable', values='value')
    
    df.columns.name = time_period
    
    return df

time_slice(data, time_periods[0]).head()

"""
Explanation: Ways to look at this data
We can look at this data set in a number of ways: 
* Cross-section: all countries during a single time period
* Time-series: a single country over time 
* Panel data: all countries over time (as data is given)
* Geospatial: all countries in geographical relation to each other 
Slicing
For a given time slice
End of explanation
"""


def country_slice(df, country):
    
    # Only take data for country of interest
    df = df[df.country==country] 

    # Pivot table 
    df = df.pivot(index='variable', columns='time_period', values='value')
    
    df.index.name = country
    return df
    

country_slice(data, countries[40]).head()

"""
Explanation: For a given country
End of explanation
"""


def variable_slice(df, variable):
    
    # Only data for that variable
    df = df[df.variable==variable]
    
    # Get variable for each country over the time periods 
    df = df.pivot(index='country', columns='time_period', values='value')
    return df

variable_slice(data, 'total_pop').head()

"""
Explanation: By variable
End of explanation
"""


def time_series(df, country, variable):
    
    # Only take data for country/variable combo 
    series = df[(df.country==country) & (df.variable==variable)]
    
    # Drop years with no data 
    series = series.dropna()[['year_measured', 'value']]
    
    # Change years to int and set as index 
    series.year_measured = series.year_measured.astype(int)
    series.set_index('year_measured', inplace=True)
    series.columns = [variable]
    return series

time_series(data, 'Belarus', 'total_pop')

"""
Explanation: Time series for given country and variable
End of explanation
"""


data.region.unique()

"""
Explanation: By region
We may want to look at subsets of the data for certain assessments. Region is an intuitive way to subdivide the data.
End of explanation
"""


simple_regions ={
    'World | Asia':'Asia',
    'Americas | Central America and Caribbean | Central America': 'North America',
    'Americas | Central America and Caribbean | Greater Antilles': 'North America',
    'Americas | Central America and Caribbean | Lesser Antilles and Bahamas': 'North America',
    'Americas | Northern America | Northern America': 'North America',
    'Americas | Northern America | Mexico': 'North America',
    'Americas | Southern America | Guyana':'South America',
    'Americas | Southern America | Andean':'South America',
    'Americas | Southern America | Brazil':'South America',
    'Americas | Southern America | Southern America':'South America', 
    'World | Africa':'Africa',
    'World | Europe':'Europe', 
    'World | Oceania':'Oceania'
}

data.region = data.region.apply(lambda x: simple_regions[x])

print(data.region.unique())

"""
Explanation: Reducing the number of regions will help for pattern assessment. 
Create a dictionary to look up new, more simple region (Asia, North America, South America, Africa, Europe, Oceania)
End of explanation
"""


def subregion(data, region):
    return data[data.region==region]

"""
Explanation: Function for extracting a single region:
End of explanation
"""


