%matplotlib inline 

import numpy as np                     # foundation for Pandas   
import pandas as pd                    # data package
import matplotlib.pyplot as plt        # graphics module  

"""
Explanation: Advanced Pandas:  Multi-axis indexing (loc and iloc methods)
In this notebook we are discussing the loc, iloc and ix methods, which are relatively newly implemented in pandas but have become the primary means of multi-axis indexing. The official pandas docs is here. 
Imagine the following standard situation: we have a dataset where

Each row represents an observation
Each column is a named variable.
and we want: an easy, quick way to select relevant rows/columns (i.e. subsamples from dataset)

This notebook borrows examples from this blog post.
Note: requires internet access to run.  
<!-- 
internal links http://sebastianraschka.com/Articles/2014_ipython_internal_links.html
-->

This Jupyter notebook was created by Balint Szoke for the NYU Stern course Data Bootcamp.
End of explanation
"""


# we need some extra tools to download and handle zip files 
import zipfile as zf    
import requests, io

url = "https://www.briandunning.com/sample-data/us-500.zip"
r = requests.get(url) 
file = zf.ZipFile(io.BytesIO(r.content)) 
file

file.namelist()    # there is one csv file inside

file_csv = file.open(file.namelist()[0])          # compare the type with file above (this is readable by pandas)
file_csv

df = pd.read_csv(file_csv)
# What do we have?
print("Variables and types:\n\n", df.dtypes, sep='')

df.head()

# Sometimes this one-liner works too
#pd.read_csv(url, compression='zip')    # not in this case

"""
Explanation: To illustrate the main ideas, we are going to use a fake data set from this website. This file contains artificial names, addresses, companies, phone numbers etc. for fictitious US characters. Here is the complete list of variables
The main purpose of this dataset is testing. Straight from the website

"Always test your software with a "worst-case scenario" amount of sample data, to get an accurate sense of its performance in the real world."

The only issue is that the samples are stored in zipped csv files, i.e. we do not have url for the csv file directly.
How to download a zipfile and read the csv file inside
End of explanation
"""


dff = df.set_index(['last_name'])
dff.head()

"""
Explanation: (1) Selecting data using loc
loc is primarily label-location based indexer. That is, 

it selects rows and columns by their labels (variable names for columns, index values for rows). 
also works with a boolean array.

The syntax is 
python
data.loc[&lt;row selection&gt;, &lt;column selection&gt;]
First, set an arbitrary index variable
End of explanation
"""


dff.loc['Butt']

# multiple rows
dff.loc[['Butt', 'Venere']]

# select a subset of the data (subDataFrame)
dff.loc[['Butt', 'Foller'], ['city', 'email']]

# ranges of index labels
dff.loc[['Butt', 'Foller'], 'address':'phone2']


"""
Explanation: Now we can directly select rows by their index (last_name) values (just like we do with columns)
End of explanation
"""


dff.loc[dff['city'] == 'New Orleans']

"""
Explanation: Boolean indexing using loc
The most common method to work with data
Pass an array of True/False values to the .loc to select the rows/columns with True values.
End of explanation
"""


dff[dff['city'] == 'New Orleans']

"""
Explanation: In fact, we don't need the loc indexer for this kind of task
End of explanation
"""


dff.loc[dff['city'] == 'New Orleans', ['company_name', 'zip']]

"""
Explanation: But what if we don't want all variables?
End of explanation
"""


dff[dff['city'] == 'New Orleans'][['company_name', 'zip']]         # matter of taste

"""
Explanation: How would you get the same dataframe without loc?
End of explanation
"""


dff[dff['email'].str.endswith("gmail.com")].head()

"""
Explanation: Recall the string methods applicable to DataFrames
End of explanation
"""


dff.loc[dff['city'].isin(['New Orleans', 'New York'])]   

# intersection fo the two?

gmails = dff['email'].str.endswith("gmail.com")
NYNO = (dff['city'] == 'New Orleans') | (dff['city'] =='New York')
dff[gmails & NYNO]

"""
Explanation: and the isin method?
End of explanation
"""


def short_company_name(x):
    """
    returns True if x contains less than 2 words
    """
    return len(x.split(' ')) < 2

dff.loc[dff['company_name'].apply(short_company_name)] 

"""
Explanation: A tricky one: we can pass a function that returns True/False values to .apply() and evaluate it at each row
End of explanation
"""


# Rows:
df.iloc[0]             # first row 

df.iloc[-1]            # last row

# Columns:
df.iloc[:, 0]          # first column = first variable (first_name)

df.iloc[:, -1]         # last column (web)

"""
Explanation: (2) Selecting data using iloc
iloc is primarily used for integer position based indexing. That is, it selects rows and columns by number, in the order that they appear in the data frame. Numbers are from $0$ to df.shape-1 of both axes. 
The syntax is 
python
data.iloc[&lt;row selection&gt;, &lt;column selection&gt;]
End of explanation
"""


df.iloc[:5]                        # first five rows

df.iloc[:, :2]                      # first two columns

df.iloc[[0, 4, 7, 25],                  # 1st, 5th, 6th, 26th row
        [0, 5, 6]]                      # 1st 6th 7th columns.

df.iloc[:5, 5:8]                          # first 5 rows and 5th, 6th, 7th colum

"""
Explanation: For multiple columns and rows, use slicer
End of explanation
"""


# ix indexing works just the same as loc when passed labels
dff.ix['Butt', 'city'] == dff.loc['Butt', 'city']

# ix indexing works the same as iloc when passed integers.
dff.ix[33, 7] == dff.iloc[33, 7]

"""
Explanation: (3) Selecting data using ix
ix is hybrid of loc and iloc. In general, 
1. it is label-location based and acts just like loc
2. However, it also supports integer-location based selection just like iloc where passed an integer
Second option only works where the index of the DataFrame is NOT an integer.
The syntax is 
python
data.ix[&lt;row selection&gt;, &lt;column selection&gt;]
Explicit usage of loc and iloc is preferred
End of explanation
"""


url_weo = 'http://www.imf.org/external/pubs/ft/weo/2016/02/weodata/WEOOct2016all.xls'

# (1) define the column indices 
col_indices = [1, 2, 3] + list(range(9, 46))

# (2) download the dataset
weo = pd.read_csv(url_weo, 
                  sep = '\t',
                  usecols=col_indices,
                  skipfooter=1, engine='python',
                  na_values=['n/a', '--'],
                  thousands =',')

# (3) change column labels to something more intuitive 
weo = weo.rename(columns={'WEO Subject Code': 'Variable', 
                          'Subject Descriptor': 'Description'})

# (4) create debt and deficits dataframe 
variables = ['GGXWDG_NGDP', 'GGXCNL_NGDP']
data = weo[weo['Variable'].isin(variables)]

data['Variable'] = data['Variable'].replace(to_replace=['GGXWDG_NGDP', 'GGXCNL_NGDP'], value=['Debt', 'Surplus'])

data1 = data.set_index(['ISO', 'Variable'])
data1.head()

data1.ix[('AFG', slice(None)), '1980':'1983']

"""
Explanation: Hierarchical indexing with loc and iloc
Multi-level indexing allows us to work with higher dimensional data while storing info in lower dimensional data structures like 2D DataFrame or 1D Series. More on this here.
Read the WEO dataset that we have already used a couple of times
End of explanation
"""


