<jupyter_start><jupyter_text>Pandas***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 <NAME>* *Python Data Science Handbook* by <NAME> Difference between loc and iloc:* loc: select data by row* iloc: select data by index  Import libraries<jupyter_code>import pandas as pd
import os

os.chdir('C:\\Users\\Matheus\\Documents\\GitHub\\Data-Science-Micromasters\\Python for Data Science\\Week 4 - Pandas')<jupyter_output><empty_output><jupyter_text>Introduction to pandas data structurespandas has two main data structures it uses, namely, ***Series*** and ***DataFromes**** DataFrames store heterogeneous data types  pandas SeriesOne-dimensional labeled array<jupyter_code>ser = pd.Series(data = [100,True,300,"foo",500],
                index=['tom', 'bob', 'nancy', 'dan', 'eric'])
ser
ser.index
ser[['nancy','bob']]
ser[[4,3,1]]
ser.iloc[2]
'bob' in ser
ser
ser * 2
ser[['tom','nancy','eric']] ** 2<jupyter_output><empty_output><jupyter_text>pandas DataFrame2-dimensional labeled data structure Create a DataFrame from dictionary of Python Series<jupyter_code>d = {'one' : pd.Series([100., 200., 300.], index=['apple', 'ball', 'clock']),
     'two' : pd.Series([111., 222., 333., 4444.], index=['apple', 'ball', 'cerill', 'dancy'])}
df = pd.DataFrame(d)
df
df.index
df.columns
pd.DataFrame(d,index=['apple','ball','clock'])
pd.DataFrame(d,index=['dancy','ball','apple'],columns=['two','five'])<jupyter_output><empty_output><jupyter_text>Create DataFrame from list of Python dictionaries<jupyter_code>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'])<jupyter_output><empty_output><jupyter_text>Basic DataFrame operations<jupyter_code>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']) # 2 is the position where the new column will be
df
df['one_upper_half'] = df['one'][:2]
df<jupyter_output><empty_output><jupyter_text>Case Study: Movie Data AnalysisThis notebook uses a dataset from the MovieLens website. We will describe the dataset further as we explore with it using *pandas*.  Download the DatasetHere are the links to the data source and location:* **Data Source:** MovieLens web site (filename: ml-20m.zip)* **Location:** https://grouplens.org/datasets/movielens/  Use Pandas to Read the DatasetIn 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* Using the ***read_csv*** function in pandas, we will ingest these three files.<jupyter_code>movies = pd.read_csv('Files/movielens/movies.csv',sep=',')
print(type(movies))
movies.head()
# timestamps represent seconds since midnight coordinated universal time (UTC)
tags = pd.read_csv('Files/movielens/tags.csv',sep=',')
tags.head()
ratings = pd.read_csv('Files/movielens/ratings.csv',sep=',',parse_dates=['timestamp'])
ratings.head()
# removing timestamps
del ratings['timestamp']
del tags['timestamp']<jupyter_output><empty_output><jupyter_text>Data Structures Series<jupyter_code># extract 0th row

row_0 = tags.iloc[0]
print(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     # becames the index<jupyter_output><empty_output><jupyter_text>DataFrames<jupyter_code>tags = pd.read_csv('Files/movielens/tags.csv',sep=',')
tags.head()
tags.index
tags.columns
# extract row 0, 11 and 2000 from DataFrame
tags.iloc[[0,11,2000]]<jupyter_output><empty_output><jupyter_text>Descriptive Statistics<jupyter_code>ratings.columns
ratings.describe()
ratings['rating'].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(type(filter_1))
print('-'*35)
print(filter_1.any())
print('-'*35)
print(filter_1)
filter_2 = ratings['rating'] > 0
filter_2.all()<jupyter_output><empty_output><jupyter_text>Data Cleaning: Handling Missing Data We see that real world data is messy.* Missing values* Outliers* Invalid data (e.g. negative values for age)* NaN value (np.nan)* None value Handling Data Quality Issues* Replace the value* Fill gaps forward/backward* Drop fields* Interpolation<jupyter_code>movies.shape
movies.isnull().any()
ratings.shape
ratings.isnull().any()
tags.shape
tags.isnull().any()
sum(tags['tag'].isnull())
tags = tags.dropna()
print("Dropped lines with NULL values")
tags.isnull().any()
tags.shape<jupyter_output><empty_output><jupyter_text>The number of lines have reduced  Data VisualizationExplore here: http://pandas.pydata.org/pandas-docs/stable/api.htmlapi-dataframe-plotting  Importing libraries<jupyter_code>import matplotlib.pyplot as plt
%matplotlib inline<jupyter_output><empty_output><jupyter_text>Plotting a Histogram for Rating<jupyter_code>ratings.hist(column='rating',figsize=(15,10))
ratings.boxplot(column='rating',figsize=(10,10))<jupyter_output><empty_output><jupyter_text>Slicing Out Columns<jupyter_code>tags['tag'].head()
movies[['title','genres']].head()
# last 10
ratings[-10:]
# first 10
ratings[:10]
# from 1000 to 1009
ratings[1000:1010]
tag_counts = tags['tag'].value_counts()
tag_counts[:10]
tag_counts[:10].plot(kind='bar',figsize=(15,10))<jupyter_output><empty_output><jupyter_text>Filters for Selecting Rows<jupyter_code>is_highly_rated = ratings['rating']>=4.0
ratings[is_highly_rated][:10]
is_animation = movies['genres'].str.contains('Animation')

movies[is_animation][:10]<jupyter_output><empty_output><jupyter_text>Group By and Aggregate<jupyter_code>rating_count = ratings[['movieId','rating']].groupby('rating').count()
rating_count
average_rating = ratings[['movieId','rating']].groupby('movieId').mean()
#average_rating.sort_values(by='rating')[:10]
average_rating.tail()
movie_count = ratings[['movieId','rating']].groupby('movieId').count()
movie_count.head()
movie_count.tail()<jupyter_output><empty_output><jupyter_text>Merge DataframesMore examples: http://pandas.pydata.org/pandas-docs/stable/merging.html<jupyter_code>tags.head()
movies.head()
t = movies.merge(tags,on='movieId',how='inner')
del t['timestamp']
t.head()<jupyter_output><empty_output><jupyter_text>Combine aggregation, merging and filters to get useful analytics<jupyter_code>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.head()
is_highly_rated = box_office['rating'] >= 4.0
box_office[is_highly_rated][:10]
is_comedy = box_office['genres'].str.contains('Comedy')
box_office[is_comedy][:10]
box_office[is_highly_rated & is_comedy].head()<jupyter_output><empty_output><jupyter_text>Vectorized String OperationsMore examples: http://pandas.pydata.org/pandas-docs/stable/text.htmltext-string-methods<jupyter_code>movies.head()<jupyter_output><empty_output><jupyter_text>Split 'genres' into multiple columns<jupyter_code>movie_genres = movies['genres'].str.split("|",expand=True)
movie_genres[:10]<jupyter_output><empty_output><jupyter_text>Add a new column for comedy genre flag<jupyter_code>movie_genres['isComedy'] = movies['genres'].str.contains('Comedy')
movie_genres[:10]<jupyter_output><empty_output><jupyter_text>Extract year from title<jupyter_code>movies['year'] = movies['title'].str.extract('.*\((.*)\).*',expand=True)
movies[:10]<jupyter_output><empty_output><jupyter_text>Parsing TimestampsTimestamps are commom in sensor data or other time series datasets. Let us revisit the *tags.csv* dataset and read the timestamps<jupyter_code>tags = pd.read_csv('./Files/movielens/tags.csv',sep=",")
tags.dtypes<jupyter_output><empty_output><jupyter_text>Unix time / POSIX Time / epoch time records time in seconds since midnight Coordinated Universal Time (UTC) of January 1, 1970<jupyter_code>tags.head()
tags['parsed_time'] = pd.to_datetime(tags['timestamp'], unit='s')<jupyter_output><empty_output><jupyter_text>Data Type datetime64[ns] maps to eithrt M8[ns] depending on the hardware<jupyter_code>tags['parsed_time'].dtype
tags.head()<jupyter_output><empty_output><jupyter_text>Selecting rows based on timestamps<jupyter_code>greater_than_t = tags['parsed_time'] > '2015-02-01'
selected_rows = tags[greater_than_t]
tags.shape, selected_rows.shape<jupyter_output><empty_output><jupyter_text>Sorting the table using the timestamps<jupyter_code>tags.sort_values(by = 'parsed_time', ascending = True)[:10]<jupyter_output><empty_output><jupyter_text>Average Movie Ratings Over Time Are movie ratings related to the year of launch?<jupyter_code>average_ratings = ratings[['movieId','rating']].groupby('movieId', as_index=False).mean()
average_ratings.head()
joined = movies.merge(average_ratings,on='movieId',how='inner')
joined.corr()
joined.head()
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)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15356362914433194, 'fraction_numerical': 0.017669247567996822, 'mean_word_length': 8.595238095238095, 'pattern_counts': {'":': 0, '<': 111, '<?xml version=': 0, '>': 117, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '27', 'dir': 'jupyter-structured-clean-dedup', 'id': '400422', 'license': 'Apache-2.0', 'n_tokens_mistral': 3618, 'n_tokens_neox': 3426, 'n_words': 711, 'path': 'Python for Data Science/Week 4 - Pandas/Introduction to Pandas.ipynb', 'repo_name': 'mattssilva/Data-Science-Micromasters', 'size': '10089'}