import pandas as pd

"""
Explanation: <p style="font-family: Arial; font-size:3.75em;color:purple; font-style:bold"><br>
Pandas</p>
<br>
pandas is a Python library for data analysis. It offers a number of data exploration, cleaning and transformation operations that are critical in working with data in Python. 
pandas build upon numpy and scipy providing easy-to-use data structures and data manipulation functions with integrated indexing.
The main data structures pandas provides are Series and DataFrames. After a brief introduction to these two data structures and data ingestion, the key features of pandas this notebook covers are:
* Generating descriptive statistics on data
* Data cleaning using built in pandas functions
* Frequent data operations for subsetting, filtering, insertion, deletion and aggregation of data
* Merging multiple datasets using dataframes
* Working with timestamps and time-series data
Additional Recommended Resources:
* pandas Documentation: http://pandas.pydata.org/pandas-docs/stable/
* Python for Data Analysis by Wes McKinney
* Python Data Science Handbook by Jake VanderPlas
Let's get started with our first pandas notebook!
<p style="font-family: Arial; font-size:1.75em;color:#2462C0; font-style:bold"><br>

Import Libraries
</p>
End of explanation
"""


ser = pd.Series([100, 'foo', 300, 'bar', 500], ['tom', 'bob', 'nancy', 'dan', 'eric'])

ser

ser.index

ser.loc[['nancy','bob']]

ser[[4, 3, 1]]

ser.iloc[2]

'bob' in ser

ser

ser * 2

ser[['nancy', 'eric']] ** 2

"""
Explanation: <p style="font-family: Arial; font-size:1.75em;color:#2462C0; font-style:bold">
Introduction to pandas Data Structures</p>
<br>
pandas has two main data structures it uses, namely, Series and DataFrames. 
<p style="font-family: Arial; font-size:1.75em;color:#2462C0; font-style:bold">
pandas Series</p>

pandas Series one-dimensional labeled array.
End of explanation
"""


d = {'one' : pd.Series([100., 200., 300.], index=['apple', 'ball', 'clock']),
     'two' : pd.Series([111., 222., 333., 4444.], index=['apple', 'ball', 'cerill', 'dancy'])}

d

df = pd.DataFrame(d)
print(df)

df.index

df.columns

pd.DataFrame(d, index=['dancy', 'ball', 'apple'])

pd.DataFrame(d, index=['dancy', 'ball', 'apple'], columns=['two', 'five'])

"""
Explanation: <p style="font-family: Arial; font-size:1.75em;color:#2462C0; font-style:bold">
pandas DataFrame</p>

pandas DataFrame is a 2-dimensional labeled data structure.
<p style="font-family: Arial; font-size:1.25em;color:#2462C0; font-style:bold">
Create DataFrame from dictionary of Python Series</p>
End of explanation
"""


data = [{'alex': 1, 'joe': 2}, {'ema': 5, 'dora': 10, 'alice': 20}]

pd.DataFrame(data)

pd.DataFrame(data, index=['orange', 'red'])

pd.DataFrame(data, columns=['joe', 'dora','alice'])

"""
Explanation: <p style="font-family: Arial; font-size:1.25em;color:#2462C0; font-style:bold">
Create DataFrame from list of Python dictionaries</p>
End of explanation
"""


df

df['one']

df['three'] = df['one'] * df['two']
df

df['flag'] = df['one'] > 250
df

three = df.pop('three')

three

df

del df['two']

df

df.insert(2, 'copy_of_one', df['one'])
df

df['one_upper_half'] = df['one'][:2]
df

"""
Explanation: <p style="font-family: Arial; font-size:1.25em;color:#2462C0; font-style:bold">
Basic DataFrame operations</p>
End of explanation
"""


path = r'/Users/Harish/Documents/HK_Work/Python/ml-20m/'

# Note: Adjust the name of the folder to match your local directory

!ls /Users/Harish/Documents/HK_Work/Python/ml-20m/

!cat /Users/Harish/Documents/HK_Work/Python/ml-20m/movies.csv | wc -l

!head -5 /Users/Harish/Documents/HK_Work/Python/ml-20m/ratings.csv

"""
Explanation: <p style="font-family: Arial; font-size:1.75em;color:#2462C0; font-style:bold">
Case Study: Movie Data Analysis</p>
<br>This notebook uses a dataset from the MovieLens website. We will describe the dataset further as we explore with it using pandas. 
Download the Dataset
Please note that you will need to download the dataset. Although the video for this notebook says that the data is in your folder, the folder turned out to be too large to fit on the edX platform due to size constraints.
Here are the links to the data source and location:
* Data Source: MovieLens web site (filename: ml-20m.zip)
* Location: https://grouplens.org/datasets/movielens/
Once the download completes, please make sure the data files are in a directory called movielens in your Week-3-pandas folder. 
Let us look at the files in this dataset using the UNIX command ls.
End of explanation
"""


movies = pd.read_csv(path+'movies.csv', sep=',')
print(type(movies))
movies.head(15)

# Timestamps represent seconds since midnight Coordinated Universal Time (UTC) of January 1, 1970

tags = pd.read_csv(path+'tags.csv', sep=',')
tags.head()

ratings = pd.read_csv(path+'ratings.csv', sep=',', parse_dates=['timestamp'])
ratings.head()

# For current analysis, we will remove timestamp (we will come back to it!)

del ratings['timestamp']
del tags['timestamp']

"""
Explanation: <p style="font-family: Arial; font-size:1.75em;color:#2462C0; font-style:bold">
Use Pandas to Read the Dataset<br>
</p>
<br>
In this notebook, we will be using three CSV files:
* ratings.csv : userId,movieId,rating, timestamp
* tags.csv : userId,movieId, tag, timestamp
* movies.csv : movieId, title, genres <br>
Using the read_csv function in pandas, we will ingest these three files.
End of explanation
"""


#Extract 0th row: notice that it is infact a Series

row_0 = tags.iloc[0]
type(row_0)

print(row_0)

row_0.index

row_0['userId']

'rating' in row_0

row_0.name

row_0 = row_0.rename('first_row')
row_0.name

"""
Explanation: <h1 style="font-size:2em;color:#2467C0">Data Structures </h1>

<h1 style="font-size:1.5em;color:#2467C0">Series</h1>
End of explanation
"""


tags.head()

tags.index

tags.columns

# Extract row 0, 11, 2000 from DataFrame

tags.iloc[ [0,11,2000] ]

"""
Explanation: <h1 style="font-size:1.5em;color:#2467C0">DataFrames </h1>
End of explanation
"""


ratings['rating'].describe()

ratings.describe()

ratings['rating'].mean()

ratings.mean()

ratings['rating'].min()

ratings['rating'].max()

ratings['rating'].std()

ratings['rating'].mode()

ratings.corr()

filter_1 = ratings['rating'] > 5
print(filter_1)
filter_1.any()

filter_2 = ratings['rating'] > 0
filter_2.all()

"""
Explanation: <h1 style="font-size:2em;color:#2467C0">Descriptive Statistics</h1>

Let's look how the ratings are distributed!
End of explanation
"""


movies.shape

#is any row NULL ?

movies.isnull().any()

"""
Explanation: <h1 style="font-size:2em;color:#2467C0">Data Cleaning: Handling Missing Data</h1>
End of explanation
"""


ratings.shape

#is any row NULL ?

ratings.isnull().any()

"""
Explanation: Thats nice ! No NULL values !
End of explanation
"""


tags.shape

#is any row NULL ?

tags.isnull().any()

"""
Explanation: Thats nice ! No NULL values !
End of explanation
"""


tags = tags.dropna()

#Check again: is any row NULL ?

tags.isnull().any()

tags.shape

"""
Explanation: We have some tags which are NULL.
End of explanation
"""


%matplotlib inline

ratings.hist(column='rating', figsize=(15,10))

ratings.boxplot(column='rating', figsize=(15,20))

"""
Explanation: Thats nice ! No NULL values ! Notice the number of lines have reduced.
<h1 style="font-size:2em;color:#2467C0">Data Visualization</h1>
End of explanation
"""


tags['tag'].head()

movies[['title','genres']].head()

ratings[-10:]

tag_counts = tags['tag'].value_counts()
tag_counts[-10:]

tag_counts[:10].plot(kind='bar', figsize=(15,10))

"""
Explanation: <h1 style="font-size:2em;color:#2467C0">Slicing Out Columns</h1>
End of explanation
"""


is_highly_rated = ratings['rating'] >= 4.0

ratings[is_highly_rated][30:50]

is_animation = movies['genres'].str.contains('Animation')

movies[is_animation][5:15]

movies[is_animation].head(15)

"""
Explanation: <h1 style="font-size:2em;color:#2467C0">Filters for Selecting Rows</h1>
End of explanation
"""


ratings_count = ratings[['movieId','rating']].groupby('rating').count()
ratings_count

average_rating = ratings[['movieId','rating']].groupby('movieId').mean()
average_rating.head()

movie_count = ratings[['movieId','rating']].groupby('movieId').count()
movie_count.head()

movie_count = ratings[['movieId','rating']].groupby('movieId').count()
movie_count.tail()

"""
Explanation: <h1 style="font-size:2em;color:#2467C0">Group By and Aggregate </h1>
End of explanation
"""


tags.head()

movies.head()

t = movies.merge(tags, on='movieId', how='inner')
t.head()

"""
Explanation: <h1 style="font-size:2em;color:#2467C0">Merge Dataframes</h1>
End of explanation
"""


avg_ratings = ratings.groupby('movieId', as_index=False).mean()
del avg_ratings['userId']
avg_ratings.head()

box_office = movies.merge(avg_ratings, on='movieId', how='inner')
box_office.tail()

is_highly_rated = box_office['rating'] >= 4.0

box_office[is_highly_rated][-5:]

is_comedy = box_office['genres'].str.contains('Comedy')

box_office[is_comedy][:5]

box_office[is_comedy & is_highly_rated][-5:]

"""
Explanation: More examples: http://pandas.pydata.org/pandas-docs/stable/merging.html
<p style="font-family: Arial; font-size:1.75em;color:#2462C0; font-style:bold"><br>


Combine aggreagation, merging, and filters to get useful analytics
</p>
End of explanation
"""


movies.head()

"""
Explanation: <h1 style="font-size:2em;color:#2467C0">Vectorized String Operations</h1>
End of explanation
"""


movie_genres = movies['genres'].str.split('|', expand=True)

movie_genres[:10]

"""
Explanation: <p style="font-family: Arial; font-size:1.35em;color:#2462C0; font-style:bold"><br>

Split 'genres' into multiple columns

<br> </p>
End of explanation
"""


movie_genres['isComedy'] = movies['genres'].str.contains('Comedy')

movie_genres[:10]

"""
Explanation: <p style="font-family: Arial; font-size:1.35em;color:#2462C0; font-style:bold"><br>

Add a new column for comedy genre flag

<br> </p>
End of explanation
"""


movies['year'] = movies['title'].str.extract('.*\((.*)\).*', expand=True)

movies.tail()

"""
Explanation: <p style="font-family: Arial; font-size:1.35em;color:#2462C0; font-style:bold"><br>

Extract year from title e.g. (1995)

<br> </p>
End of explanation
"""


tags = pd.read_csv(path+r'tags.csv', sep=',')

tags.dtypes

"""
Explanation: <p style="font-family: Arial; font-size:1.35em;color:#2462C0; font-style:bold"><br>

More here: http://pandas.pydata.org/pandas-docs/stable/text.html#text-string-methods
<br> </p>

<h1 style="font-size:2em;color:#2467C0">Parsing Timestamps</h1>

Timestamps are common in sensor data or other time series datasets.
Let us revisit the tags.csv dataset and read the timestamps!
End of explanation
"""


tags.head(5)

tags['parsed_time'] = pd.to_datetime(tags['timestamp'], unit='s')

"""
Explanation: <p style="font-family: Arial; font-size:1.35em;color:#2462C0; font-style:bold">

Unix time / POSIX time / epoch time records 
time in seconds <br> since midnight Coordinated Universal Time (UTC) of January 1, 1970
</p>
End of explanation
"""



tags['parsed_time'].dtype

tags.head(2)

"""
Explanation: <p style="font-family: Arial; font-size:1.35em;color:#2462C0; font-style:bold">

Data Type datetime64[ns] maps to either <M8[ns] or >M8[ns] depending on the hardware

</p>
End of explanation
"""


greater_than_t = tags['parsed_time'] > '2015-02-01'

selected_rows = tags[greater_than_t]

tags.shape, selected_rows.shape

"""
Explanation: <p style="font-family: Arial; font-size:1.35em;color:#2462C0; font-style:bold">

Selecting rows based on timestamps
</p>
End of explanation
"""


tags.sort_values(by='parsed_time', ascending=True)[:10]

"""
Explanation: <p style="font-family: Arial; font-size:1.35em;color:#2462C0; font-style:bold">

Sorting the table using the timestamps
</p>
End of explanation
"""


average_rating = ratings[['movieId','rating']].groupby('movieId', as_index=False).mean()
average_rating.tail()

joined = movies.merge(average_rating, on='movieId', how='inner')
joined.head()
joined.corr()

yearly_average = joined[['year','rating']].groupby('year', as_index=False).mean()
yearly_average[:10]

yearly_average[-20:].plot(x='year', y='rating', figsize=(15,10), grid=True)

"""
Explanation: <h1 style="font-size:2em;color:#2467C0">Average Movie Ratings over Time </h1>
Are Movie ratings related to the year of launch?
End of explanation
"""


