How to Create Pandas Pivot Table Count

  • Post author:
  • Post category:Pandas
  • Post last modified:November 7, 2023

In Pandas pivot table can be used to display the count values of certain columns. Pandas pivot() or pivot_table() function is used to make a spreadsheet-style pivot table from a given DataFrame. In Python Pandas library produces many functions that give us more flexibility to create and analyze the pivot table among all of these pivot() or pivot_table() is one that groups, summarize, and aggregates the data. In this article, I will explain how to create count values in a pivot table over column values with examples.

1. Quick Examples of Getting Count of Pandas Pivot Table

If you are in hurry below are some quick examples of how to get the count of a pivot table.


# Below are the quick examples

# Example 1 : Create count value in a pivot table
p_table = pd.pivot_table(df, index= ['Gender'], columns = ['Courses'], values=['Discount'], aggfunc = 'count' )

# Example 2 : Create unique count values
p_table = pd.pivot_table(df, index= ['Gender'], columns = ['Courses'], values=['Discount'], aggfunc=pd.Series.nunique )

2. Syntax of Pandas Pivot Table

Following is the syntax of the Pandas.pivot_table().


# Syntax of Pandas pivot table.
pandas.pivot_table(data, values=None, index=None, columns=None, aggfunc=’mean’, fill_value=None, margins=False, dropna=True, margins_name=’All’, observed=False)

# Another syntax
DataFrame.pivot(index=None, columns=None, values=None)

2.1 Parameters of Pivot Table

Below are the parameters of the pivot table

  • values : Are the numeric data in a given DataFrame, that are be aggregated.
  • index : Defines the rows of the pivot table
  • columns : Defines the columns of the pivot table

2.2 Return Value

It returns pivot table with count values.

3. Create Pandas DataFrame

Python pandas is widely used for data science/data analysis and machine learning applications. It is built on top of another popular package named NumPy, which provides scientific computing in Python. pandas DataFrame is a 2-dimensional labeled data structure with rows and columns (columns of potentially different types like integers, strings, float, None, Python objects e.t.c). You can think of it as an excel spreadsheet or SQL table.

We can create pandas DataFrame in many ways here, I will create DataFrame using Python Dictionary.


import pandas as pd
# Create a DataFrame
df = pd.DataFrame({'Gender' : ['Female', 'Male', 'Male', 'Male', 'Female', 'Male', 'Female'],
                  'Courses': ['Java', 'Spark', 'PySpark','Java','C', 'PySpark', 'Java'],
                   'Fee': [15000, 17000, 27000, 29000, 12000, 29000, 15000],
                   'Discount': [1100, 800, 1000, 1600, 600, 1000, 1100]})
print(df)

Yields below output.


# Output:
   Gender  Courses    Fee  Discount
0  Female     Java  15000      1100
1    Male    Spark  17000       800
2    Male  PySpark  27000      1000
3    Male     Java  29000      1600
4  Female        C  12000       600
5    Male  PySpark  29000      1000
6  Female     Java  15000      1100

4. Create Pandas Pivot Table with Count Value

Pass count() statistical function as aggfunc into a pivot table against column values, it will return the count values of pivot table over a specified column. For example,


# Create count value in a pivot table
p_table = pd.pivot_table(df, index= ['Gender'], columns = ['Courses'], values=['Discount'], aggfunc = 'count' )
print(p_table)

Yields below output


# Output:
        Discount                   
Courses        C Java PySpark Spark
Gender                             
Female       1.0  2.0     NaN   NaN
Male         NaN  1.0     2.0   1.0

5. Create Pandas Pivot Table With Unique Counts

Moreover, we can count the unique presences of a particular observation (row) in a pivot table using aggfunc= pd.Series.nunique function that will allow us to count only the distinct rows in the DataFrame. For example,


# Create unique count values
p_table = pd.pivot_table(df, index= ['Gender'], columns = ['Courses'], values=['Discount'], aggfunc=pd.Series.nunique )
print(p_table)

Yields below output.


# Output:
       Discount                   
Courses        C Java PySpark Spark
Gender                             
Female       1.0  1.0     NaN   NaN
Male         NaN  1.0     1.0   1.0

We can replace the NaN Values with specified values by using the fill_value parameter of the pivot_table() function. For example,


p_table = pd.pivot_table(df, index= ['Gender'], columns = ['Courses'], values=['Discount'], aggfunc=pd.Series.nunique , fill_value = '-')
print(p_table)

Yields below output.


# Output:
        Discount                   
Courses        C Java PySpark Spark
Gender                             
Female       1.0  1.0       -     -
Male           -  1.0     1.0   1.0

6. Conclusion

In this article, I have explained how to create Pandas count values in a pivot table over column values with examples. also learned how to unique counts of a column.

References

Naveen

I am a Data Engineer with 20+ years of experience in transforming data into actionable insights. Over the years, I have honed my expertise in designing, implementing, and maintaining data pipelines with frameworks like Apache Spark, PySpark, Pandas, R, Hive and Machine Learning. My journey in the field of data engineering has been a continuous learning, innovation, and a strong commitment to data integrity. I have started this SparkByExamples.com to share my experiences with the data as I come across. You can learn more about me at LinkedIn

Leave a Reply

You are currently viewing How to Create Pandas Pivot Table Count