import pandas as pd             # data package
import matplotlib.pyplot as plt # graphics 
import sys                      # system module, used to get Python version 
import os                       # operating system tools (check files)
import datetime as dt           # date tools, used to note current date  

# these are new 
import requests, io             # internet and input tools  
import zipfile as zf            # zip file tools 
import shutil                   # file management tools 

%matplotlib inline 

print('\nPython version: ', sys.version) 
print('Pandas version: ', pd.__version__)
print('Requests version: ', requests.__version__)
print("Today's date:", dt.date.today())

"""
Explanation: Pandas 4:  Combining data
Sometimes we need to combine data from two or more dataframes.  That's colloquially known as a merge or a join. There are lots of ways to do this.  We do a couple but supply references to more at the end.  
Along the way we take an extended detour to review methods for downloading and unzipping compressed files.  The tools we use here have a broad range of other applications, including web scraping.  
Outline:  

MovieLens data.  A collection of movies and individual ratings.  
Automate file download.  Use the requests package to get a zipped file, then other tools to unzip it and read in the contents.  
Merge movie names and ratings.  Merge information from two dataframes with Pandas' merge function.  
UN population data.  We merge ("concatenate") estimates from the past with projections of the future.  

Note: requires internet access to run.  
This IPython notebook was created by Dave Backus, Chase Coleman, Brian LeBlanc, and Spencer Lyon for the NYU Stern course Data Bootcamp.  
<a id=prelims></a>
Preliminaries
Import packages, etc.
End of explanation
"""


# get "response" from url 
url = 'http://files.grouplens.org/datasets/movielens/ml-latest-small.zip'
r = requests.get(url) 

# describe response 
print('Response status code:', r.status_code)
print('Response type:', type(r))
print('Response .content:', type(r.content)) 
print('Response headers:\n', r.headers, sep='')

# convert bytes to zip file  
mlz = zf.ZipFile(io.BytesIO(r.content)) 
print('Type of zipfile object:', type(mlz))

# what's in the zip file?
mlz.namelist()

# extract and read csv's
movies  = pd.read_csv(mlz.open(mlz.namelist()[2]))
ratings = pd.read_csv(mlz.open(mlz.namelist()[3]))

# what do we have? 
for df in [movies, ratings]:
    print('Type:', type(df))
    print('Dimensions:', df.shape)
    print('Variables:', list(df))
    print('First few rows', df.head(3), '\n')

"""
Explanation: <a id=movielens></a>
MovieLens data
The data comes as a zip file that contains several csv's.  We get the details from the README inside.  (It's written in Markdown, so it's easier to read if we use a browser to format it.  Or we could cut and paste into a Markdown cell in an IPython notebook.)  
The file descriptions are:  

ratings.csv:  each line is an individual film rating with the rater and movie id's and the rating.  Order:  userId, movieId, rating, timestamp. 
tags.csv:  each line is a tag on a specific film.  Order:  userId, movieId, tag, timestamp. 
movies.csv:  each line is a movie name, its id, and its genre.  Order:  movieId, title, genres.  Multiple genres are separated by "pipes" |.   
links.csv:  each line contains the movie id and corresponding id's at IMBd and TMDb.  

The easy way to input this data is to download the zip file onto our computer, unzip it, and read the individual csv files using read.csv().  But anyone can do it the easy way.  We want to automate this, so we can redo it without any manual steps.  This takes some effort, but once we have it down we can apply it to lots of other data sources.  
<a id=requests></a>
Automate file download
We're looking for an automated way, so that if we do this again, possibly with updated data, the whole process is in our code.  Automated data entry involves these steps: 

Get the file.  We use the requests package, which handles internet files and comes pre-installed with Anaconda. This kind of thing was hidden behind the scenes in the Pandas read_csv function, but here we need to do it for ourselves. The package authors add:  
Recreational use of other HTTP libraries may result in dangerous side-effects, including: security vulnerabilities, verbose code, reinventing the wheel, constantly reading documentation, depression, headaches, or even death.


Convert to zip.   Requests simply loads whatever's at the given url. The io module's io.Bytes reconstructs it as a file, here a zip file.  
Unzip the file.  We use the zipfile module, which is part of core Python, to extract the files inside.   
Read in the csv's.  Now that we've extracted the csv files, we use read_csv as usual.  

We found this Stack Overflow exchange helpful. 
Digression.  This is probably more than you want to know, but it's a reminder of what goes on behind the scenes when we apply read_csv to a url.  Here we grab whatever is at the url.  Then we get its contents, convert it to bytes, identify it as a zip file, and read its components using read_csv.  It's a lot easier when this happens automatically, but a reminder what's involved if we ever have to look into the details.
End of explanation
"""


# experiment via http://stackoverflow.com/a/18043472/804513
with open('test.zip', 'wb') as out_file:
    shutil.copyfileobj(io.BytesIO(r.content), out_file)   

"""
Explanation: Exercise. Something to do together.  suppose we wanted to save the files on our computer.  How would we do it? Would we prefer individual csv's or a single zip?
End of explanation
"""


ratings.head(3)

movies.head(3)

"""
Explanation: <a id=merge-movies></a>
Merging ratings and movie titles
The movie ratings in the dataframe ratings give us individual opinions about movies, but they don't include the name of the movie.   Why not?  Rather than include the name every time a movie is rated, the MovieLens data associates each rating with a movie code, than stores the names of movies associatd with each movie code in the dataframe movies.  We run across this a lot:  some information is in one data table, other information is in another.  
Our want is therefore to add the movie name to the ratings dataframe.  We say we merge the two dataferames.  There are lots of ways to merge.  Here we do one as an illustration.  
Let's start by reminding ourselves what we have.
End of explanation
"""


combo = pd.merge(ratings, movies,   # left and right df's
                 how='left',        # add to left 
                 on='movieId'       # link with this variable/column 
                ) 

print('Dimensions of ratings:', ratings.shape)
print('Dimensions of movies:', movies.shape)
print('Dimensions of new df:', combo.shape)

combo.head(20)

# save as csv file for future use 
combo.to_csv('mlcombined.csv')

print('Current directory:\n', os.getcwd(), sep='')
print('List of files:', os.listdir(), sep='\n')

"""
Explanation: Merging
Here's roughly what's involved in what we're doing.  We take the movieId variable from ratings and look it up in movies.  When we find it, we look up the title and add it as a column in ratings.  The variable movieId is common, so we can use it to link the two dataframes.
End of explanation
"""


"""
fig, ax = plt.subplots()
bins = [bin/100 for bin in list(range(25, 575, 50))]
print(bins)
combo['rating'].plot(kind='hist', ax=ax, bins=bins, color='blue', alpha=0.5)
ax.set_xlim(0,5.5)
ax.set_ylabel('Number')
ax.set_xlabel('Rating')
"""

"""
Explanation: Exercise. Some of these we know how to do, the others we don't.  For the ones we know, what is the answer?  For the others, what (in loose terms) do we need to be able to do to come up with an answer?  

What is the overall average rating?  
What is the overall distribution of ratings?  
What is the average rating of each movie?  
How many ratings does each movie get?
End of explanation
"""


url1 = 'http://esa.un.org/unpd/wpp/DVD/Files/'
url2 = '1_Indicators%20(Standard)/EXCEL_FILES/1_Population/'
url3 = 'WPP2015_POP_F07_1_POPULATION_BY_AGE_BOTH_SEXES.XLS'
url = url1 + url2 + url3 

cols = [2, 5] + list(range(6,28))
est = pd.read_excel(url, sheetname=0, skiprows=16, parse_cols=cols, na_values=['…'])
prj = pd.read_excel(url, sheetname=1, skiprows=16, parse_cols=cols, na_values=['…'])

print('Dimensions and dtypes of estimates: ', est.shape, '\n', est.dtypes.head(), sep='')
print('\nDimensions and dtypes of projections: ', prj.shape, '\n', prj.dtypes.head(), sep='')

"""
Explanation: <a id=population></a>
Population "estimates" and "projections"
We look (again) at the UN's  population data, specifically the age distribution of the population.  The data comes in two sheets:  estimates that cover the period 1950-2015 and projections that cover 2016-2100.  Our mission is to combine them.  
Load data
We start, as usual, by loading the data.  This takes a minute or so.
End of explanation
"""


list(est)[15:]

list(prj)[15:]

"""
Explanation: Comment. Note that they have different numbers of columns.  Let's see where that comes from.
End of explanation
"""


def cleanpop(df, countries, years): 
    """
    take df as input and select countries and years 
    """
    # rename first two columns 
    names = list(df) 
    df = df.rename(columns={names[0]: 'Country', names[1]: 'Year'}) 

    # select countries and years
    newdf = df[df['Country'].isin(countries) & df['Year'].isin(years)] 
    
    return newdf

countries = ['Japan']
past      = [1950, 2000]
future    = [2050, 2100]

e = cleanpop(est, countries, past) 
p = cleanpop(prj, countries, future)

# make copie sfor later use 
ealt = e.copy()
palt = p.copy()

# fix top-coding in estimates
e['80-84'] = e['80-84'].fillna(0.0) + e['80+'].fillna(0.0)
e = e.drop(['80+'], axis=1)

# check dimensions again
print('Dimensions of cleaned estimates: ', e.shape)
print('Dimensions of cleaned projections: ', p.shape)

# check to see if we have the same variables 
list(e) == list(p)

"""
Explanation: Clean data
Pick a useable subset and fix extra column so that we can combine them.  The problem here is that until 1990, the highest age category was '80+.  From 1990 on, we have a finer breakdown.  
We fix this by reassigning '80+' to '80-84' and not worrying that some of these people are 85 or older.  Note that df.fillna(0.0) replaces missing values with zeros.
End of explanation
"""


pop = pd.concat([e, p], axis=0).fillna(0.0)
pop

"""
Explanation: Merge estimates and projections
If we have two blocks of data, and want just want to put them on top of each other, we use the Pandas' concatenate function.  Ditto two blocks next to each other.  
But first we need to fix the difference in the columns of the two dataframes.
End of explanation
"""


"""
popalt = pd.concat([ealt, palt], axis=0)
popalt
"""

"""
Explanation: Exercise. What happens if we try to merge the original dataframes, including the one with the extra 80+ column?  Run the code below and comment on what you get.
End of explanation
"""


pop = pop.drop('Country', axis=1)
popi = pop.set_index('Year')
popi 

popi.columns.name = 'Age'
popt = popi.T
popt.head()

ax = popt.plot(kind='bar', color='blue', alpha=0.5, 
               subplots=True, 
               sharey=True, 
               figsize=(8,12)) 

"""
Explanation: Shape data
We want age categories in the index (the default x axis in a plot) and the years in the columns.  The country we don't care about because there's only one.
End of explanation
"""


