Create Frequency table of column in Pandas python

In order to Create Frequency table of column in pandas python we will be using value_counts() function. crosstab() function in pandas used to get the cross table or frequency table.  Let’s see how to create frequency matrix or frequency table of column in pandas.

  • Frequency table in pandas python using value_count() function
  • Frequency table in pandas python using crosstab() function
  • groupby() count function is used to get the frequency count of the dataframe
  • two way frequency table using crosstab() function
  • two way frequency of table using proportion / row proportion and column proportions.

First let’s create a dataframe


import pandas as pd
import numpy as np

data = {'Product':['Box','Bottles','Pen','Markers','Bottles','Pen','Markers','Bottles','Box','Markers','Markers','Pen'],
       'State':['Alaska','California','Texas','North Carolina','California','Texas','Alaska','Texas','North Carolina','Alaska','California','Texas'],
       'Sales':[14,24,31,12,13,7,9,31,18,16,18,14]}

df1=pd.DataFrame(data, columns=['Product','State','Sales'])

df1

df1 will be

Groupby count in pandas python 1

 

Get frequency table of column in pandas python: Method 1

Frequency table of column in pandas for State column can be created using value_counts() as shown below.

df1.State.value_counts()

So the frequency table will be

Create Frequency table of column in pandas 2

 

Get frequency table of column in pandas python: Method 2

Frequency table of column in pandas for State column can be created using value_counts() as shown below.

df1['State'].value_counts()

So the frequency table will be

Frequency table of column in pandas python 2

 

Get frequency table of column in pandas python : Method 3 crosstab()

Frequency table of column in pandas for State column can be created using crosstab()  function as shown below. crosstab() function takes up the column name as argument counts the frequency of occurrence of its values

### frequency table using crosstab()function

import pandas as pd
my_tab = pd.crosstab(index=df1["State"], columns="count") 
my_tab

So the frequency table will be

Frequency table of column in pandas python 2

 

 

Get frequency table of column in pandas python : Method 4 Groupby count()

groupby() function takes up the column name as argument followed by count() function as shown below which is used to get the frequency table of the column in pandas


#### Get frequency table of the column using Groupby count()

df1.groupby(['State'])['Sales'].count()

so the result with frequency table will be
Groupby count in pandas python 2

 

Two way frequency table using crosstab() function:

Two way Frequency table of column in pandas for “State” column and “Product” column can be created using crosstab()  function as shown below. crosstab() function takes up the column names “State” to index and “Product” to column  as argument counts the frequency of the cross tabulations

### frequency table using crosstab()function

import pandas as pd
my_crosstab = pd.crosstab(index=df1["State"], 
                            columns=df1["Product"],
                             margins=True)   # Include row and column totals
my_crosstab

So the resultant two way frequency table will be

Frequency table of column in pandas python 4

 

Two way frequency 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 = ["Bottles" , "Box" , "Markers" , "Pen" ,"rowtotal"]
my_crosstab.index= ["Alaska","California","North Carolina","Texas","coltotal"]
my_crosstab

so the renamed frequency table will be

Frequency table of column in pandas python 4b

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

Frequency table of column in pandas python 5

 

 

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

Frequency table of column in pandas python 6

 

 

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

Frequency table of column in pandas python 7


Other related topics:

to read on more you refer the documentation.

p Create Frequency table of column in Pandas python                                                                                                           n Create Frequency table of column in Pandas python

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