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

Jingwei Li

Graduate Research assistance at Stony Brook University
linkedin profile url

ProjectPro is an awesome platform that helps me learn much hands-on industrial experience with a step-by-step walkthrough of projects. There are two primary paths to learn: Data Science and Big Data.... Read More

Relevant Projects

Deploying Machine Learning Models with Flask for Beginners
In this MLOps on GCP project you will learn to deploy a sales forecasting ML Model using Flask.

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.

Learn to Build a Polynomial Regression Model from Scratch
In this Machine Learning Regression project, you will learn to build a polynomial regression model to predict points scored by the sports team.

Multilabel Classification Project for Predicting Shipment Modes
Multilabel Classification Project to build a machine learning model that predicts the appropriate mode of transport for each shipment, using a transport dataset with 2000 unique products. The project explores and compares four different approaches to multilabel classification, including naive independent models, classifier chains, natively multilabel models, and multilabel to multiclass approaches.

Build a Credit Default Risk Prediction Model with LightGBM
In this Machine Learning Project, you will build a classification model for default prediction with LightGBM.

Machine Learning project for Retail Price Optimization
In this machine learning pricing project, we implement a retail price optimization algorithm using regression trees. This is one of the first steps to building a dynamic pricing model.

Customer Market Basket Analysis using Apriori and Fpgrowth algorithms
In this data science project, you will learn how to perform market basket analysis with the application of Apriori and FP growth algorithms based on the concept of association rule learning.

Build a Text Generator Model using Amazon SageMaker
In this Deep Learning Project, you will train a Text Generator Model on Amazon Reviews Dataset using LSTM Algorithm in PyTorch and deploy it on Amazon SageMaker.

NLP Project for Multi Class Text Classification using BERT Model
In this NLP Project, you will learn how to build a multi-class text classification model using using the pre-trained BERT model.

Hands-On Approach to Causal Inference in Machine Learning
In this Machine Learning Project, you will learn to implement various causal inference techniques in Python to determine, how effective the sprinkler is in making the grass wet.