Pandas in Python, with Examples – Part III – Pivot_table

You can download the project for this post by clicking HERE.

Pivot_table

The "Pivot_table" feature is very popular and widely used in well-known spreadsheet applications such as OpenOffice, LibreOffice, Excel, Lotus, etc. It lets us group, sort, calculate and handle data in a way very similar to how it is done in spreadsheets.

This pandas function accepts the following parameters, which you can check in more detail by clicking HERE:

DataFrame.pivot_table(values=None, index=None, columns=None, aggfunc='mean', fill_value=None, margins=False, dropna=True)

The main purpose of "Pivot_table" is grouping data, usually applying mathematical functions to the groups, such as sums, averages, etc. If we don't specify in the 'aggfunc' parameter which operation we want to perform, by default it will calculate the mean of all the numeric columns.

Let's look at a first example, using the MovieLens dataset with 100K ratings. To do so, we will read the data from the files that hold the information about users, movies and ratings, just as we did in the post "DataFrame: Reading and Writing, Merging DataFrames and GroupBy":

import pandas as pd
import numpy as np

# Load Data
userHeader = ['user_id', 'gender', 'age', 'ocupation', 'zip']
users = pd.read_table('dataSet/users.txt', engine='python', sep='::', header=None, names=userHeader)

movieHeader = ['movie_id', 'title', 'genders']
movies = pd.read_table('dataSet/movies.txt', engine='python', sep='::', header=None, names=movieHeader)

ratingHeader = ['user_id', 'movie_id', 'rating', 'timestamp']
ratings = pd.read_table('dataSet/ratings.txt', engine='python', sep='::', header=None, names=ratingHeader)

# Merge data
mergeRatings = pd.merge(pd.merge(users, ratings), movies)

# Clone DataFrame
def cloneDF(df):
    return pd.DataFrame(df.values.copy(), df.index.copy(), df.columns.copy()).convert_objects(convert_numeric=True)

The first example will be to set the 'movie_id' and 'title' columns as indexes. This way, it will group by movie title and identifier. We do it like this:

# Simple pivot (Groupby + avg)
df_1 = cloneDF(mergeRatings)
df_1 = df_1.pivot_table(index=['movie_id', 'title'])
print 'Columns(movie_id + title) to Index: \n%s' % df_1[:5]

As a result, we get the first 5 elements of this grouping of the DataFrame, and since we didn't specify the 'aggfunc' parameter, it calculates the mean of all the numeric values:

                                                   age  ocupation    rating  ....
movie_id title                                                                
1        Toy Story (1995)                    27.700530   8.067886  4.146846  .... 
2        Jumanji (1995)                      27.800285   7.680456  3.201141  .... 
3        Grumpier Old Men (1995)             29.276151   7.826360  3.016736  ....
4        Waiting to Exhale (1995)            27.788235   6.752941  2.729412  ....
5        Father of the Bride Part II (1995)  27.425676   7.506757  3.006757  ....

We can do this same operation using the 'values' parameter to say which columns we want the mean calculated on, since, as you can see, it doesn't make much sense to calculate the mean of the "ocupation" column, which is an identifier of each user's profession. We tell it (with "values=[‘rating’, ‘age’]") to calculate only the mean of the grouping for the 'rating' and 'age' columns:

df_2 = cloneDF(mergeRatings)
df_2 = df_2.pivot_table(index=['movie_id', 'title'], values=['rating', 'age'])
print 'Columns(movie_id + title) to Index and avg by values \n%s' % df_2[:5]

This is the result:

Columns(movie_id + title) to Index and avg by values 
                                                   age    rating
movie_id title                                                  
1        Toy Story (1995)                    27.700530  4.146846
2        Jumanji (1995)                      27.800285  3.201141
3        Grumpier Old Men (1995)             29.276151  3.016736
4        Waiting to Exhale (1995)            27.788235  2.729412
5        Father of the Bride Part II (1995)  27.425676  3.006757

The next step is to tell it which operations we want to perform and on which column. In this case we are going to "pivot" on the movie title and its identifier, and we will apply the sum, count and mean operations to the 'rating' column. To do these calculations we will use the 'numpy' library:

df_3 = cloneDF(mergeRatings)
df_3 = df_3.pivot_table(index=['movie_id', 'title'], values=['rating'], aggfunc=[np.sum, np.size, np.mean])
print 'Columns(movie_id + title) to Index and specific functions by values \n%s' % df_3[:5]

This is the result:

Columns(movie_id + title) to Index and specific functions by values 
                                               sum   size      mean
                                            rating rating    rating
movie_id title                                                     
1        Toy Story (1995)                     8613   2077  4.146846
2        Jumanji (1995)                       2244    701  3.201141
3        Grumpier Old Men (1995)              1442    478  3.016736
4        Waiting to Exhale (1995)              464    170  2.729412
5        Father of the Bride Part II (1995)    890    296  3.006757

For the last example, we will keep grouping by movie title and identifier and apply the mean function to the 'rating' column through the 'aggfunc' parameter. But this time we will use the 'columns' parameter to display, in separate columns, all the distinct values of the 'gender' column (columns=[‘gender’]), and with the 'margins=True' parameter we will tell it to add a new column with the calculations over all the values. In short, the result will be a grouping by movie and identifier in which we see the average rating given by men and women separately and (with the 'margins=True' parameter) the overall rating of the movie. We do it like this:

df_4 = cloneDF(mergeRatings)
df_4 = df_4.pivot_table(index=['movie_id', 'title'], values=['rating'], columns=['gender'], aggfunc=[np.mean],
                        fill_value=-1, margins=True)
print 'Columns(movie_id + title) to Index and avg rating applied by gender \n%s' % df_4[:5]

This is the result:

Columns(movie_id + title) to Index and avg rating applied by gender 
                                                 mean                    
                                               rating                    
gender                                              F         M       All
movie_id title                                                           
1        Toy Story (1995)                    4.187817  4.130552  4.146846
2        Jumanji (1995)                      3.278409  3.175238  3.201141
3        Grumpier Old Men (1995)             3.073529  2.994152  3.016736
4        Waiting to Exhale (1995)            2.976471  2.482353  2.729412
5        Father of the Bride Part II (1995)  3.212963  2.888298  3.006757

We can see how differently men and women vote; for example, it seems that men like "Waiting to Exhale" somewhat more than women do, since there is almost a 0.5-point difference in the average.

CONCLUSIONS:

Pandas is a data analysis library widely used by 'data scientists'. As you have seen in this tutorial, we were able to analyze data on the ratings people gave to movies, and thanks to the "Pivot_table" operations we extracted some potentially relevant insights. Since this is an introductory tutorial, we have only covered a few "Pivot_table" operations, but we have seen enough to start working with this feature, and once you feel comfortable with it, you will surely be able to do very thorough and precise data analysis, since this is a library that professional data scientists use today.