How to create Pivot table using a Pandas DataFrame?

This recipe helps you create Pivot table using a Pandas DataFrame

Recipe Objective

A Pivot Table is used to summarise, sort, reorganise, group, count, total or average data stored in a table. So Pivot Table can be created by python.

So this is the recipe on how we can create Pivot table using a Pandas DataFrame.

Learn to Build a Hybrid Recommender System in Python

Step 1 - Import the library

import pandas as pd

We have only imported pandas which is needed.

Step 2 - Creating DataFrame

We have created a dictionary and passed it through pd.DataFrame to create a Dataframe raw_data = {"regiment": ["Nighthawks", "Nighthawks", "Nighthawks", "Nighthawks", "Dragoons", "Dragoons", "Dragoons", "Dragoons", "Scouts", "Scouts", "Scouts", "Scouts"], "company": ["1st", "1st", "2nd", "2nd", "1st", "1st", "2nd", "2nd","1st", "1st", "2nd", "2nd"], "TestScore": [4, 24, 31, 2, 3, 4, 24, 31, 2, 3, 2, 3]} df = pd.DataFrame(raw_data, columns = ["regiment", "company", "TestScore"]) print(df)

Step 3 - Making Pivot Table

For better understanding we have created various Pivot Table with different features and parameters

We have created a pivot table between regiment and company. we have passed mean in parameter aggfunc to create a pivot table containg mean of data. df1 = pd.pivot_table(df, index=["regiment","company"], aggfunc="mean") print(df1) Now, We have created a pivot table between regiment and company. we have passed count in parameter aggfunc to create a pivot table containg number of data values in the feature. df2 = df.pivot_table(index=["regiment","company"], aggfunc="count") print(df2) We have created a pivot table between regiment and company. we have passed max in parameter aggfunc to create a pivot table containg maximum vaule of the features. df1 = pd.pivot_table(df, index=["regiment","company"], aggfunc="max") print(df1) We have created a pivot table between regiment and company. we have passed min in parameter aggfunc to create a pivot table containg minimum value of the features. df4 = df.pivot_table(index=["regiment","company"], aggfunc="min") print(df4) So the output comes as

      regiment company  TestScore
0   Nighthawks     1st          4
1   Nighthawks     1st         24
2   Nighthawks     2nd         31
3   Nighthawks     2nd          2
4     Dragoons     1st          3
5     Dragoons     1st          4
6     Dragoons     2nd         24
7     Dragoons     2nd         31
8       Scouts     1st          2
9       Scouts     1st          3
10      Scouts     2nd          2
11      Scouts     2nd          3

                    TestScore
regiment   company           
Dragoons   1st            3.5
           2nd           27.5
Nighthawks 1st           14.0
           2nd           16.5
Scouts     1st            2.5
           2nd            2.5

                    TestScore
regiment   company           
Dragoons   1st              2
           2nd              2
Nighthawks 1st              2
           2nd              2
Scouts     1st              2
           2nd              2

                    TestScore
regiment   company           
Dragoons   1st              4
           2nd             31
Nighthawks 1st             24
           2nd             31
Scouts     1st              3
           2nd              3

                    TestScore
regiment   company           
Dragoons   1st              3
           2nd             24
Nighthawks 1st              4
           2nd              2
Scouts     1st              2
           2nd              2

Download Materials

What Users are saying..

profile image

Ed Godalle

Director Data Analytics at EY / EY Tech
linkedin profile url

I am the Director of Data Analytics with over 10+ years of IT experience. I have a background in SQL, Python, and Big Data working with Accenture, IBM, and Infosys. I am looking to enhance my skills... Read More

Relevant Projects

Image Classification Model using Transfer Learning in PyTorch
In this PyTorch Project, you will build an image classification model in PyTorch using the ResNet pre-trained model.

Hands-On Approach to Master PyTorch Tensors with Examples
In this deep learning project, you will learn how to perform various operations on the building block of PyTorch : Tensors.

LLM Project to Build and Fine Tune a Large Language Model
In this LLM project for beginners, you will learn to build a knowledge-grounded chatbot using LLM's and learn how to fine tune it.

Avocado Machine Learning Project Python for Price Prediction
In this ML Project, you will use the Avocado dataset to build a machine learning model to predict the average price of avocado which is continuous in nature based on region and varieties of avocado.

Build a Multi ClassText Classification Model using Naive Bayes
Implement the Naive Bayes Algorithm to build a multi class text classification model in Python.

Loan Eligibility Prediction in Python using H2O.ai
In this loan prediction project you will build predictive models in Python using H2O.ai to predict if an applicant is able to repay the loan or not.

MLOps AWS Project on Topic Modeling using Gunicorn Flask
In this project we will see the end-to-end machine learning development process to design, build and manage reproducible, testable, and evolvable machine learning models by using AWS

Time Series Classification Project for Elevator Failure Prediction
In this Time Series Project, you will predict the failure of elevators using IoT sensor data as a time series classification machine learning problem.

Build a Graph Based Recommendation System in Python -Part 1
Python Recommender Systems Project - Learn to build a graph based recommendation system in eCommerce to recommend products.

Build Classification Algorithms for Digital Transformation[Banking]
Implement a machine learning approach using various classification techniques in Python to examine the digitalisation process of bank customers.