Cross table in python pandas (cross tab)

In this section we will learn how to create cross table in python pandas  ( 2 way cross table or  3 way cross table or contingency table) with example. We will learn how to create.

  • 2 way cross table or contingency table in python pandas
  • 3 way cross table or contingency table in python pandas

 

Create dataframe :

import pandas as pd
import numpy as np

#Create a DataFrame
d = {
    'Name':['Alisa','Bobby','Cathrine','Alisa','Bobby','Cathrine',
            'Alisa','Bobby','Cathrine','Alisa','Bobby','Cathrine'],
    'Exam':['Semester 1','Semester 1','Semester 1','Semester 1','Semester 1','Semester 1',
            'Semester 2','Semester 2','Semester 2','Semester 2','Semester 2','Semester 2'],
    
    'Subject':['Mathematics','Mathematics','Mathematics','Science','Science','Science',
               'Mathematics','Mathematics','Mathematics','Science','Science','Science'],
   'Result':['Pass','Pass','Fail','Pass','Fail','Pass','Pass','Fail','Fail','Pass','Pass','Fail']}

df = pd.DataFrame(d,columns=['Name','Exam','Subject','Result'])
df

so the resultant dataframe will be

2 way 3 way Cross tab (cross table) in python pandas 1

 

2 Way Cross table in python pandas:

We will calculate the cross table of subject and result as shown below


# 2 way cross table

pd.crosstab(df.Subject, df.Result,margins=True)

margin=True displays the row wise and column wise sum of the cross table  so the output will be

2 way 3 way Cross tab (cross table) in python pandas 2

 

 

Two way frequency table or cross table: Get proportion using crosstab() function:

STEP 1 : Rename to get row total and column total

To get the over all proportion lets first rename the two way cross table. The columns and index of the two way cross table  is renamed to get the row total and column total as shown below

 
#### Rename the index and columns

my_crosstab.columns = ["Fail" , "Pass" , "rowtotal"] 
my_crosstab.index= ["Mathematics","Science","coltotal"]
my_crosstab

so the renamed frequency table will be

cross table in pandas python 1

 

Step 2: Get over all proportion of the frequency table

the cross table is divided by row total and column total to get the proportion as shown below

 
#### Get the over all proportion

my_crosstab/my_crosstab.ix["coltotal","rowtotal"]

so the cross table with proportion will be

cross table in pandas python 2

 

 

Two way frequency table : Get column wise proportion using crosstab() function

the cross table is divided by column total to get the column wise proportion as shown below

 
#### Get the column proportion

my_crosstab/my_crosstab.ix["coltotal"]

so the cross table with column wise proportion will be

cross table in pandas python 3

 

 

Two way frequency table : Get row wise proportion using crosstab() function

the cross table is divided by row total to get the row wise proportion as shown below

 
#### Get the row proportion

my_crosstab.div(my_crosstab["rowtotal"],axis=0)

so the row table with row wise proportion will be

cross table in pandas python 4

 

3 Way Cross table in python pandas:

We will calculate the cross table of subject, Exam and result as shown below


# 3 way cross table

pd.crosstab([df.Subject, df.Exam],df.Result, margins=True)

the result will be

2 way 3 way Cross tab (cross table) in python pandas 3


Other related topics:

to read on more you refer the documentation.

 

previous-small Cross tab in python pandas (cross table)                                                                                                           next_small Cross tab in python pandas (cross table)

Author

  • Sridhar Venkatachalam

    With close to 10 years on Experience in data science and machine learning Have extensively worked on programming languages like R, Python (Pandas), SAS, Pyspark.

    View all posts