DATA MUNGING PYTHON PANDAS DATAFRAME PANDAS CHEATSHEET PANDAS DATAFRAME TUTORIAL

How to create Pivot table using a Pandas DataFrame?

This recipe helps you create Pivot table using a Pandas DataFrame
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


Companies using this Recipe
1 employee of Altimetrik
1 employee of KPMG
1 employee of Scotiabank
1 employee of ANAC
1 employee of LTI
1 employee of Ericsson
1 employee of Renovite Technologies
1 employee of YASH Technologies
1 employee of ICU Medical
1 employee of S&P Global