Pivot tables – the Swiss Army Knife of data analysis I love how quickly I can analyze data using pivot tables.
With one click of my mouse, I can drill down into the granular details about a certain product category, or zoom out and get a high-level overview of the data at hand.
Pivot tables offer a ton of flexibility for me as a data scientist.
I’ll be honest – I rely on them a lot during the exploratory data analysis phase in a data science project.
Excel users will be intimately familiar with these pivot tables.
They’re the most used feature of Excel, and it’s easy to see why! But did you know that you can build these pivot tables using Pandas in Python? That’s right! The wonderful Pandas library offers a function called pivot_table that summarized a feature’s values in a neat two-dimensional table.
We’ll see how to build such a pivot table in Python here.
Trust me, you’ll be using these pivot tables in your own projects very soon! Please note that this tutorial assumes basic Pandas and Python knowledge.
If you’re new to these topics, you can pick them up in the below free courses: (adsbygoogle = window.
adsbygoogle || []).
push({}); Python for Data Science Pandas for Data Analysis in Python Table of Contents Exploring the Titanic Dataset with Pandas in Python Build a Pivot Table using Pandas How to group data using index in pivot table? How to run a pivot with a multi-index? Different aggregation function for different features Aggregate on specific features with values parameter Find the relationship between features with columns parameter Handling missing data Exploring the Titanic Dataset using Pandas in Python I’m sure you’ve come across the Titanic dataset in your data science journey.
It’s among the first datasets we pick up when we’re ready to explore a project.
I’ll be using that to show you the efficacy of the pivot_table function.
Let’s import the relevant libraries: import pandas as pd import numpy as np import matplotlib.
pyplot as plt plt.
style.
use(ggplot) For all those who forgot what the Titanic dataset looks like, I present to you the dataset! df = pd.
read_csv(drive/My Drive/AV/train.
csv) df.
head() I will be dropping a few features to make it easier to analyze the data and demonstrate the capabilities of the pivot_table function: (adsbygoogle = window.
adsbygoogle || []).
push({}); df.
drop([PassengerId,Ticket,Name],inplace=True,axis=1) Building a Pivot Table using Pandas Time to build a pivot table in Python using the awesome Pandas library! We will explore the different facets of a pivot table in this article and build an awesome, flexible pivot table from scratch.
How to group data using index in a pivot table? pivot_table requires a data and an index parameter data is the Pandas dataframe you pass to the function index is the feature that allows you to group your data.
The index feature will appear as an index in the resultant table I will be using the ‘Sex’ column as the index for now: #a single index table = pd.
pivot_table(data=df,index=[Sex]) table We can instantly compare all the feature values for both the genders.
Now, let’s visualize the finding: table.
plot(kind=bar); Well, the female passengers paid remarkably more for the tickets than the male passengers.
(adsbygoogle = window.
adsbygoogle || []).
push({}); You can learn more about how to visualize your data here.
How to run a pivot with a multi-index? You can even use more than one feature as an index to group your data.
This increases the level of granularity in the resultant table and you can get more specific with your findings: #multiple indexes table = pd.
pivot_table(df,index=[Sex,Pclass]) table Using multiple indexes on the dataset enables us to concur that the disparity in ticket fare for female and male passengers was valid across every Pclass on Titanic.
Different aggregation function for different features The values shown in the table are the result of the summarization that aggfunc applies to the feature data.
aggfunc is an aggregate function that pivot_table applies to your grouped data.
By default, it is np.
mean(), but you can use different aggregate functions for different features too! Just provide a dictionary as an input to the aggfunc parameter with the feature name as the key and the corresponding aggregate function as the value.
I will be using np.
mean() for the ‘Age’ feature and np.
sum() for the ‘Survived’ feature: #different aggregate functions table = pd.
pivot_table(df,index=[Sex,Pclass],aggfunc={Age:np.
mean,Survived:np.
sum}) table The resultant table makes more sense on using different aggregating functions for different features.
Aggregate on specific features with values parameter But what are you aggregating on? You can tell Pandas the feature(s) to apply the aggregate function on, in the value parameter.
value parameter is where you tell the function which features to aggregate on.
It is an optional field and if you don’t specify this value, then the function will aggregate on all the numerical features of the dataset: table = pd.
pivot_table(df,index=[Sex,Pclass],values=[Survived], aggfunc=np.
sum) table table.
plot(kind=bar); Female passengers aboard the Titanic had better chances of survival than male passengers.
Find the relationship between features with columns parameter Using multiple features as indexes is fine, but using some features as columns will help you to intuitively understand the relationship between them.
Also, the resultant table can always be better viewed by incorporating the columns parameter of the pivot_table.
This columns parameter is optional and displays the values horizontally on the top of the resultant table.
Both columns and the index parameters are optional, but using them effectively will help you to intuitively understand the relationship between the features.
#columns table = pd.
pivot_table(df,index=[Sex],columns=[Pclass],values=[Survived],aggfunc=np.
sum) table Using Pclass as a column is easier to understand than using it as an index: table.
plot(kind=bar); Handling missing data pivot_table even allows you to deal with the missing values through the parameters dropna and fill_value: dropna allows you to drop the null values in the grouped table whose all values are null fill_value parameter can be used to replace the NaN values in the grouped table with the values that you provide here #display null values table = pd.
pivot_table(df,index=[Sex,Survived,Pclass],columns=[Embarked],values=[Age],aggfunc=np.
mean) table I will be replacing the NaN values with the mean value from the ‘Age’ column: #handling null values table = pd.
pivot_table(df,index=[Sex,Survived,Pclass],columns=[Embarked],values=[Age],aggfunc=np.
mean,fill_value=np.
mean(df[Age])) table End Notes In this article, we explored the different parameters of the awesome pivot_table function and how it allows you to easily summarise the features in your dataset through a single line of code.
If you are new to Python programming and want to learn more about data analysis with Python, I highly recommend you explore our free Python for Data Science and Pandas for Data Analysis in Python courses.
You can also read this article on Analytics Vidhyas Android APP Share this:Click to share on LinkedIn (Opens in new window)Click to share on Facebook (Opens in new window)Click to share on Twitter (Opens in new window)Click to share on Pocket (Opens in new window)Click to share on Reddit (Opens in new window) Related Articles (adsbygoogle = window.
adsbygoogle || []).
push({});.