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

0

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

Zillow’s Home Value Prediction (Zestimate)
Data Science Project in R -Build a machine learning algorithm to predict the future sale prices of homes.

Music Recommendation System Project using Python and R
Machine Learning Project - Work with KKBOX's Music Recommendation System dataset to build the best music recommendation engine.

Data Science Project in Python on BigMart Sales Prediction
The goal of this data science project is to build a predictive model and find out the sales of each product at a given Big Mart store.

Time Series Forecasting with LSTM Neural Network Python
Deep Learning Project- Learn to apply deep learning paradigm to forecast univariate time series data.

Deep Learning with Keras in R to Predict Customer Churn
In this deep learning project, we will predict customer churn using Artificial Neural Networks and learn how to model an ANN in R with the keras deep learning package.

Identifying Product Bundles from Sales Data Using R Language
In this data science project in R, we are going to talk about subjective segmentation which is a clustering technique to find out product bundles in sales data.

Sequence Classification with LSTM RNN in Python with Keras
In this project, we are going to work on Sequence to Sequence Prediction using IMDB Movie Review Dataset​ using Keras in Python.

Perform Time series modelling using Facebook Prophet
In this project, we are going to talk about Time Series Forecasting to predict the electricity requirement for a particular house using Prophet.

Predict Employee Computer Access Needs in Python
Data Science Project in Python- Given his or her job role, predict employee access needs using amazon employee database.

Credit Card Fraud Detection as a Classification Problem
In this data science project, we will predict the credit card fraud in the transactional dataset using some of the predictive models.