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:
Additional Recommended Resources:
Let's get started with our first pandas notebook!
Import Libraries
In [1]:
import pandas as pd
Introduction to pandas Data Structures
pandas Series
pandas Series one-dimensional labeled array.
In [2]:
ser = pd.Series([100, 'foo', 300, 'bar', 500], ['tom', 'bob', 'nancy', 'dan', 'eric'])
In [3]:
ser
Out[3]:
In [4]:
ser.index
Out[4]:
In [5]:
ser.loc[['nancy','bob']]
Out[5]:
In [6]:
ser[[4, 3, 1]]
Out[6]:
In [7]:
ser.iloc[2]
Out[7]:
In [8]:
'bob' in ser
Out[8]:
In [9]:
ser
Out[9]:
In [10]:
ser * 2
Out[10]:
In [11]:
ser[['nancy', 'eric']] ** 2
Out[11]:
pandas DataFrame
pandas DataFrame is a 2-dimensional labeled data structure.
Create DataFrame from dictionary of Python Series
In [12]:
d = {'one' : pd.Series([100., 200., 300.], index=['apple', 'ball', 'clock']),
'two' : pd.Series([111., 222., 333., 4444.], index=['apple', 'ball', 'cerill', 'dancy'])}
In [13]:
d
Out[13]:
In [14]:
df = pd.DataFrame(d)
print(df)
In [15]:
df.index
Out[15]:
In [16]:
df.columns
Out[16]:
In [17]:
pd.DataFrame(d, index=['dancy', 'ball', 'apple'])
Out[17]:
In [18]:
pd.DataFrame(d, index=['dancy', 'ball', 'apple'], columns=['two', 'five'])
Out[18]:
Create DataFrame from list of Python dictionaries
In [19]:
data = [{'alex': 1, 'joe': 2}, {'ema': 5, 'dora': 10, 'alice': 20}]
In [20]:
pd.DataFrame(data)
Out[20]:
In [21]:
pd.DataFrame(data, index=['orange', 'red'])
Out[21]:
In [22]:
pd.DataFrame(data, columns=['joe', 'dora','alice'])
Out[22]:
Basic DataFrame operations
In [23]:
df
Out[23]:
In [24]:
df['one']
Out[24]:
In [25]:
df['three'] = df['one'] * df['two']
df
Out[25]:
In [26]:
df['flag'] = df['one'] > 250
df
Out[26]:
In [27]:
three = df.pop('three')
In [28]:
three
Out[28]:
In [29]:
df
Out[29]:
In [30]:
del df['two']
In [31]:
df
Out[31]:
In [32]:
df.insert(2, 'copy_of_one', df['one'])
df
Out[32]:
In [33]:
df['one_upper_half'] = df['one'][:2]
df
Out[33]:
Case Study: Movie Data Analysis
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:
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.
In [34]:
path = r'/Users/Harish/Documents/HK_Work/Python/ml-20m/'
In [35]:
# Note: Adjust the name of the folder to match your local directory
!ls /Users/Harish/Documents/HK_Work/Python/ml-20m/
In [36]:
!cat /Users/Harish/Documents/HK_Work/Python/ml-20m/movies.csv | wc -l
In [37]:
!head -5 /Users/Harish/Documents/HK_Work/Python/ml-20m/ratings.csv
Use Pandas to Read the Dataset
Using the read_csv function in pandas, we will ingest these three files.
In [38]:
movies = pd.read_csv(path+'movies.csv', sep=',')
print(type(movies))
movies.head(15)
Out[38]:
In [39]:
# Timestamps represent seconds since midnight Coordinated Universal Time (UTC) of January 1, 1970
tags = pd.read_csv(path+'tags.csv', sep=',')
tags.head()
Out[39]:
In [40]:
ratings = pd.read_csv(path+'ratings.csv', sep=',', parse_dates=['timestamp'])
ratings.head()
Out[40]:
In [41]:
# For current analysis, we will remove timestamp (we will come back to it!)
del ratings['timestamp']
del tags['timestamp']
In [42]:
#Extract 0th row: notice that it is infact a Series
row_0 = tags.iloc[0]
type(row_0)
Out[42]:
In [43]:
print(row_0)
In [44]:
row_0.index
Out[44]:
In [45]:
row_0['userId']
Out[45]:
In [46]:
'rating' in row_0
Out[46]:
In [47]:
row_0.name
Out[47]:
In [48]:
row_0 = row_0.rename('first_row')
row_0.name
Out[48]:
In [49]:
tags.head()
Out[49]:
In [50]:
tags.index
Out[50]:
In [51]:
tags.columns
Out[51]:
In [52]:
# Extract row 0, 11, 2000 from DataFrame
tags.iloc[ [0,11,2000] ]
Out[52]:
Let's look how the ratings are distributed!
In [53]:
ratings['rating'].describe()
Out[53]:
In [54]:
ratings.describe()
Out[54]:
In [55]:
ratings['rating'].mean()
Out[55]:
In [56]:
ratings.mean()
Out[56]:
In [57]:
ratings['rating'].min()
Out[57]:
In [58]:
ratings['rating'].max()
Out[58]:
In [59]:
ratings['rating'].std()
Out[59]:
In [60]:
ratings['rating'].mode()
Out[60]:
In [61]:
ratings.corr()
Out[61]:
In [62]:
filter_1 = ratings['rating'] > 5
print(filter_1)
filter_1.any()
Out[62]:
In [63]:
filter_2 = ratings['rating'] > 0
filter_2.all()
Out[63]:
In [64]:
movies.shape
Out[64]:
In [65]:
#is any row NULL ?
movies.isnull().any()
Out[65]:
Thats nice ! No NULL values !
In [66]:
ratings.shape
Out[66]:
In [67]:
#is any row NULL ?
ratings.isnull().any()
Out[67]:
Thats nice ! No NULL values !
In [68]:
tags.shape
Out[68]:
In [69]:
#is any row NULL ?
tags.isnull().any()
Out[69]:
We have some tags which are NULL.
In [70]:
tags = tags.dropna()
In [71]:
#Check again: is any row NULL ?
tags.isnull().any()
Out[71]:
In [72]:
tags.shape
Out[72]:
Thats nice ! No NULL values ! Notice the number of lines have reduced.
In [73]:
%matplotlib inline
ratings.hist(column='rating', figsize=(15,10))
Out[73]:
In [74]:
ratings.boxplot(column='rating', figsize=(15,20))
Out[74]:
In [75]:
tags['tag'].head()
Out[75]:
In [76]:
movies[['title','genres']].head()
Out[76]:
In [77]:
ratings[-10:]
Out[77]:
In [78]:
tag_counts = tags['tag'].value_counts()
tag_counts[-10:]
Out[78]:
In [79]:
tag_counts[:10].plot(kind='bar', figsize=(15,10))
Out[79]:
In [80]:
is_highly_rated = ratings['rating'] >= 4.0
ratings[is_highly_rated][30:50]
Out[80]:
In [81]:
is_animation = movies['genres'].str.contains('Animation')
movies[is_animation][5:15]
Out[81]:
In [82]:
movies[is_animation].head(15)
Out[82]:
In [83]:
ratings_count = ratings[['movieId','rating']].groupby('rating').count()
ratings_count
Out[83]:
In [84]:
average_rating = ratings[['movieId','rating']].groupby('movieId').mean()
average_rating.head()
Out[84]:
In [85]:
movie_count = ratings[['movieId','rating']].groupby('movieId').count()
movie_count.head()
Out[85]:
In [86]:
movie_count = ratings[['movieId','rating']].groupby('movieId').count()
movie_count.tail()
Out[86]:
In [87]:
tags.head()
Out[87]:
In [88]:
movies.head()
Out[88]:
In [89]:
t = movies.merge(tags, on='movieId', how='inner')
t.head()
Out[89]:
More examples: http://pandas.pydata.org/pandas-docs/stable/merging.html
Combine aggreagation, merging, and filters to get useful analytics
In [103]:
avg_ratings = ratings.groupby('movieId', as_index=False).mean()
del avg_ratings['userId']
avg_ratings.head()
Out[103]:
In [104]:
box_office = movies.merge(avg_ratings, on='movieId', how='inner')
box_office.tail()
Out[104]:
In [105]:
is_highly_rated = box_office['rating'] >= 4.0
box_office[is_highly_rated][-5:]
Out[105]:
In [106]:
is_comedy = box_office['genres'].str.contains('Comedy')
box_office[is_comedy][:5]
Out[106]:
In [107]:
box_office[is_comedy & is_highly_rated][-5:]
Out[107]:
In [108]:
movies.head()
Out[108]:
Split 'genres' into multiple columns
In [109]:
movie_genres = movies['genres'].str.split('|', expand=True)
In [110]:
movie_genres[:10]
Out[110]:
Add a new column for comedy genre flag
In [98]:
movie_genres['isComedy'] = movies['genres'].str.contains('Comedy')
In [111]:
movie_genres[:10]
Out[111]:
Extract year from title e.g. (1995)
In [112]:
movies['year'] = movies['title'].str.extract('.*\((.*)\).*', expand=True)
In [113]:
movies.tail()
Out[113]:
More here: http://pandas.pydata.org/pandas-docs/stable/text.html#text-string-methods
Timestamps are common in sensor data or other time series datasets. Let us revisit the tags.csv dataset and read the timestamps!
In [115]:
tags = pd.read_csv(path+r'tags.csv', sep=',')
In [116]:
tags.dtypes
Out[116]:
Unix time / POSIX time / epoch time records
time in seconds
since midnight Coordinated Universal Time (UTC) of January 1, 1970
In [117]:
tags.head(5)
Out[117]:
In [118]:
tags['parsed_time'] = pd.to_datetime(tags['timestamp'], unit='s')
Data Type datetime64[ns] maps to either
In [119]:
tags['parsed_time'].dtype
Out[119]:
In [120]:
tags.head(2)
Out[120]:
Selecting rows based on timestamps
In [121]:
greater_than_t = tags['parsed_time'] > '2015-02-01'
selected_rows = tags[greater_than_t]
tags.shape, selected_rows.shape
Out[121]:
Sorting the table using the timestamps
In [122]:
tags.sort_values(by='parsed_time', ascending=True)[:10]
Out[122]:
In [123]:
average_rating = ratings[['movieId','rating']].groupby('movieId', as_index=False).mean()
average_rating.tail()
Out[123]:
In [124]:
joined = movies.merge(average_rating, on='movieId', how='inner')
joined.head()
joined.corr()
Out[124]:
In [125]:
yearly_average = joined[['year','rating']].groupby('year', as_index=False).mean()
yearly_average[:10]
Out[125]:
In [126]:
yearly_average[-20:].plot(x='year', y='rating', figsize=(15,10), grid=True)
Out[126]:
Do some years look better for the boxoffice movies than others?
Does any data point seem like an outlier in some sense?