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
In [1]:
## How to create Pivot table using a Pandas DataFrame
def Kickstarter_Example_97():
    print()
    print(format('How to create Pivot table using a Pandas DataFrame','*^82'))

    import warnings
    warnings.filterwarnings("ignore")

    # load libraries
    import pandas as pd

    # Create 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(); print(df)

    # Create a pivot table of group means, by company and regiment
    df1 = pd.pivot_table(df, index=['regiment','company'], aggfunc='mean')
    print(); print(df1)

    # Create a pivot table of group score counts, by company and regimensts
    df2 = df.pivot_table(index=['regiment','company'], aggfunc='count')
    print(); print(df2)

    # Create a pivot table of group score max, by company and regimensts
    df3 = df.pivot_table(index=['regiment','company'], aggfunc='max')
    print(); print(df3)

    # Create a pivot table of group score min, by company and regimensts
    df4 = df.pivot_table(index=['regiment','company'], aggfunc='min')
    print(); print(df4)

Kickstarter_Example_97()
****************How to create Pivot table using a Pandas DataFrame****************

      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

Predict Churn for a Telecom company using Logistic Regression
Machine Learning Project in R- Predict the customer churn of telecom sector and find out the key drivers that lead to churn. Learn how the logistic regression model using R can be used to identify the customer churn in telecom dataset.

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.

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.

Learn to prepare data for your next machine learning project
Text data requires special preparation before you can start using it for any machine learning project.In this ML project, you will learn about applying Machine Learning models to create classifiers and learn how to make sense of textual data.

Predict Macro Economic Trends using Kaggle Financial Dataset
In this machine learning project, you will uncover the predictive value in an uncertain world by using various artificial intelligence, machine learning, advanced regression and feature transformation techniques.

Choosing the right Time Series Forecasting Methods
There are different time series forecasting methods to forecast stock price, demand etc. In this machine learning project, you will learn to determine which forecasting method to be used when and how to apply with time series forecasting example.

German Credit Dataset Analysis to Classify Loan Applications
In this data science project, you will work with German credit dataset using classification techniques like Decision Tree, Neural Networks etc to classify loan applications using R.

Data Science Project on Wine Quality Prediction in R
In this R data science project, we will explore wine dataset to assess red wine quality. The objective of this data science project is to explore which chemical properties will influence the quality of red wines.

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.

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.