How to create Pivot table using a Pandas DataFrame?
DATA MUNGING DATA CLEANING PYTHON MACHINE LEARNING RECIPES PANDAS CHEATSHEET     ALL TAGS

How to create Pivot table using a Pandas DataFrame?

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.

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

Relevant Projects

Ecommerce product reviews - Pairwise ranking and sentiment analysis
This project analyzes a dataset containing ecommerce product reviews. The goal is to use machine learning models to perform sentiment analysis on product reviews and rank them based on relevance. Reviews play a key role in product recommendation systems.

Build OCR from Scratch Python using YOLO and Tesseract
In this deep learning project, you will learn how to build your custom OCR (optical character recognition) from scratch by using Google Tesseract and YOLO to read the text from any images.

Time Series Analysis Project in R on Stock Market forecasting
In this time series project, you will build a model to predict the stock prices and identify the best time series forecasting model that gives reliable and authentic results for decision making.

PySpark Tutorial - Learn to use Apache Spark with Python
PySpark Project-Get a handle on using Python with Spark through this hands-on data processing spark python tutorial.

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.

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.

Expedia Hotel Recommendations Data Science Project
In this data science project, you will contextualize customer data and predict the likelihood a customer will stay at 100 different hotel groups.

Build a Music Recommendation Algorithm using KKBox's Dataset
Music Recommendation Project using Machine Learning - Use the KKBox dataset to predict the chances of a user listening to a song again after their very first noticeable listening event.

Census Income Data Set Project - Predict Adult Census Income
Use the Adult Income dataset to predict whether income exceeds 50K yr based on census data.

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.