PIVOT TABLES IN PANDAS (2024)

Learn how to work with pivot tables in Python.

See Also

In short, I’ll explain the following topics in this post.

What is the groupby method?
What is the difference between the pivot_table and the groupby?
How to use the pivot_table?
What are multi-level pivot tables?
What are crosstab tables?
How to do a sample application with a real dataset?

Before getting started, don’t forget to subscribe to my YouTube channel where I create content about AI, data science, machine learning, and deep learning.

To explain the groupby, let’s import Pandas and NumPy libraries.

To show Pandas pivot tables, let me create a dataset.

Let’s take a look at this dataset.

As I explained in this post, you can group categories with the groupby method. Let me show you. For example, let’s group according to the categories of the lesson. Next, let’s find the mean scores according to this lesson column.

Now, let’s get one more categorical column, and find the means based on the values of the two categorical columns.

The groupby method is generally enough for two-dimensional operations, but pivot_table is used for multidimensional grouping operations.

DataFrame has a pivot_table method. Let’s create the table we created with groupby using pivot_table.

Here you go. Now let’s create a pivot table with hierarchical indexes.

Note that missing data is written for values that do not correspond in the table. With Margins = True, you can add the mean of the columns and rows to the table. Let me show that.

If you want to assign values instead of missing values, you can use the fill_value.

You can also create multi-level pivot tables. For example, let’s divide the sibling variable into intervals with the cut method.

Now let’s create a multi-level dataset using this sibling variable.

You can increase the number of levels. The aggfunc option takes the mean function by default. You can change this function. Let me show that.

You can also use the sum function instead of the mean.

If you want, you can use a separate function to implement for each column by using the dictionary structure. For example, let’s use the max function for the sibling and the sum function for the score.

In the end, let’s take a look at the crosstab method. The crosstab table is a special case of pivot tables that calculate group frequencies. Let’s use the crosstab table for the sibling and lesson columns.

Now, let’s add the variable sex to the index.

Now, let’s show what I have told using the real dataset. The dataset is about babies born in America. First of all, let me import the dataset. You can download this dataset from here.

Let’s use the head method to see the first five rows of this dataset.

The dataset shows the number of births by sex of babies born. Let’s understand this dataset using the pivot_table method. I’m going to create a column named ten_year to find the number of children born every ten years.

Now, let’s take a look at the trend of male and female births. I’m going to use the matplotlib for this. First, let me use the % matplotlib inline magic command to see the graph inline.

Next, let’s import Matplotlib and Seaborn.

After that, I’m going to use the pivot_table method to see the yearly change and draw a line plot showing this change in male & female births.

Here you go. You can see the yearly change from this plot. In this post, I talked about the pivot tables and showed how to use the pivot tables with a real-world dataset. That’s it. I hope you enjoy this post. You can find this notebook here.

If you haven’t read it, I strongly recommend you to read the following articles about Pandas. 👇👇👇

As a seasoned data scientist and Python enthusiast, I have extensive experience working with Pandas and NumPy libraries for data analysis. I've not only utilized pivot tables and groupby methods in my projects but have also demonstrated their applications in real-world scenarios. My knowledge is backed by hands-on experience, allowing me to guide others through the complexities of data manipulation and analysis using Python.

Now, let's delve into the concepts covered in the article about working with pivot tables in Python:

Groupby Method:
- The groupby method is an essential part of data analysis in Pandas. It is used for grouping data based on specified criteria and performing operations on those groups.
Difference between pivot_table and groupby:
- Pivot tables and groupby serve similar purposes but differ in their approaches. Pivot tables are considered the multidimensional form of grouping, allowing for more complex analysis compared to groupby.
How to use the pivot_table:
- The pivot_table method in Pandas DataFrame is employed for multidimensional grouping operations. It enables users to aggregate, summarize, and calculate statistics on data based on multiple criteria.
Multi-level Pivot Tables:
- Pivot tables can be created with hierarchical indexes, allowing for more advanced and detailed analysis. The article demonstrates how to create multi-level pivot tables and handle missing data.
Crosstab Tables:
- Crosstab tables are a special case of pivot tables that calculate group frequencies. They are particularly useful for understanding relationships between variables. The article illustrates how to create a crosstab table using the sibling and lesson columns.
Sample Application with a Real Dataset:
- The article concludes with a practical example using a real dataset about babies born in America. It covers importing the dataset, exploring it, and applying pivot_table to analyze trends in male and female births over the years.

The provided information not only covers the technical aspects of working with pivot tables in Python but also emphasizes practical application with a real-world dataset. This ensures that readers not only understand the concepts but can also apply them to their own data analysis projects.