Recipe: How to create crosstabs from a Dictionary in Python?
DATA MUNGING DATA CLEANING PYTHON

How to create crosstabs from a Dictionary in Python?

This recipe helps you create crosstabs from a Dictionary in Python
In [1]:
## How to create crosstabs from a Dictionary in Python
def Kickstarter_Example_83():
    print()
    print(format('How to create crosstabs from a Dictionary in Python','*^82'))

    import warnings
    warnings.filterwarnings("ignore")

    # load libraries
    import pandas as pd
    raw_data = {'regiment': ['Nighthawks', 'Nighthawks', 'Nighthawks', 'Nighthawks', 'Dragoons', 'Dragoons', 'Dragoons', 'Dragoons', 'Scouts', 'Scouts', 'Scouts', 'Scouts'],
                'company': ['infantry', 'infantry', 'cavalry', 'cavalry', 'infantry', 'infantry', 'cavalry', 'cavalry','infantry', 'infantry', 'cavalry', 'cavalry'],
                'experience': ['veteran', 'rookie', 'veteran', 'rookie', 'veteran', 'rookie', 'veteran', 'rookie','veteran', 'rookie', 'veteran', 'rookie'],
                'name': ['Miller', 'Jacobson', 'Ali', 'Milner', 'Cooze', 'Jacon', 'Ryaner', 'Sone', 'Sloan', 'Piger', 'Riani', 'Ali'],
                'preTestScore': [4, 24, 31, 2, 3, 4, 24, 31, 2, 3, 2, 3],
                'postTestScore': [25, 94, 57, 62, 70, 25, 94, 57, 62, 70, 62, 70]}

    df = pd.DataFrame(raw_data, columns = ['regiment', 'company', 'experience', 'name', 'preTestScore', 'postTestScore'])
    print(); print(df)

    # Create a crosstab table by company and regiment
    df1 = pd.crosstab(df.regiment, df.company, margins=True)
    print(); print(df1)

    # Create more crosstabs
    df2 = pd.crosstab([df.company, df.experience], df.regiment,  margins=True)
    print(); print(df2)

    df3 = pd.crosstab([df.company, df.experience, df.preTestScore], df.regiment,  margins=True)
    print(); print(df3)

Kickstarter_Example_83()
***************How to create crosstabs from a Dictionary in Python****************

      regiment   company experience      name  preTestScore  postTestScore
0   Nighthawks  infantry    veteran    Miller             4             25
1   Nighthawks  infantry     rookie  Jacobson            24             94
2   Nighthawks   cavalry    veteran       Ali            31             57
3   Nighthawks   cavalry     rookie    Milner             2             62
4     Dragoons  infantry    veteran     Cooze             3             70
5     Dragoons  infantry     rookie     Jacon             4             25
6     Dragoons   cavalry    veteran    Ryaner            24             94
7     Dragoons   cavalry     rookie      Sone            31             57
8       Scouts  infantry    veteran     Sloan             2             62
9       Scouts  infantry     rookie     Piger             3             70
10      Scouts   cavalry    veteran     Riani             2             62
11      Scouts   cavalry     rookie       Ali             3             70

company     cavalry  infantry  All
regiment
Dragoons          2         2    4
Nighthawks        2         2    4
Scouts            2         2    4
All               6         6   12

regiment             Dragoons  Nighthawks  Scouts  All
company  experience
cavalry  rookie             1           1       1    3
         veteran            1           1       1    3
infantry rookie             1           1       1    3
         veteran            1           1       1    3
All                         4           4       4   12

regiment                          Dragoons  Nighthawks  Scouts  All
company  experience preTestScore
cavalry  rookie     2                    0           1       0    1
                    3                    0           0       1    1
                    31                   1           0       0    1
         veteran    2                    0           0       1    1
                    24                   1           0       0    1
                    31                   0           1       0    1
infantry rookie     3                    0           0       1    1
                    4                    1           0       0    1
                    24                   0           1       0    1
         veteran    2                    0           0       1    1
                    3                    1           0       0    1
                    4                    0           1       0    1
All                                      4           4       4   12


Stuck at work?
Can't find the recipe you are looking for. Let us know and we will find an expert to create the recipe for you. Click here
Companies using this Recipe
1 developer from HvH
1 developer from KPMG
1 developer from Vodafone
1 developer from Altimetrik
1 developer from ICFAI
1 developer from LTI
1 developer from Wipro
1 developer from ANAC
1 developer from ICU Medical
1 developer from MudraCircle