<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 <p style="font-family
Step1:  <p style="font-family
Step2:  <p style="font-family
Step3:  <p style="font-family
Step4:  <p style="font-family
Step5:  <p style="font-family
Step6:  <p style="font-family
Step7:  <h1 style="font-size
Step8:  <h1 style="font-size
Step9:  <h1 style="font-size
Step10:  <h1 style="font-size
Step11:  Thats nice ! No NULL values !
Step12:  Thats nice ! No NULL values !
Step13:  We have some tags which are NULL.
Step14:  Thats nice ! No NULL values ! Notice the number of lines have reduced.
<h1 style="font-size
Step15:  <h1 style="font-size
Step16:  <h1 style="font-size
Step17:  <h1 style="font-size
Step18:  <h1 style="font-size
Step19:  More examples
Step20:  <h1 style="font-size
Step21:  <p style="font-family
Step22:  <p style="font-family
Step23:  <p style="font-family
Step24:  <p style="font-family
Step25:  <p style="font-family
Step26:  <p style="font-family
Step27:  <p style="font-family
Step28:  <p style="font-family
Step29:  <h1 style="font-size
<ASSISTANT_TASK:>
Python Code:
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
<END_TASK>