How to count values in pivot tables in Pandas?

In one of our previous tutorials we learnt about how to sum multiple values in Pandas pivot tables. Today we’ll look specifically into the usage of the Pandas pivot_table module to display a cross tabular representation that shows both unique and non-unique occurrence counts.

Pivot table all and unique count in Pandas – Example

Example data

We’ll start by defining some sample data for our example. Feel free to use the code below to generate a simple data set for you to follow along.

import pandas as pd

# Define dataset
language = ['R', 'C#', 'Python', 'C#', 'C#', 'Python', 'C#', 'C#', 'C#', 'C#', 'Java', 'Java', 'Python', 'Java', 'C#', 'Python']
office = ['NYC', 'SFO', 'NYC', 'SFO', 'SFO', 'ATL', 'SFO', 'ATL', 'ATL', 'SFO', 'SFO', 'NYC', 'SFO', 'NYC', 'ATL', 'ATL']
salary = [115.0, 136.0, 116.0, 129.0, 136.0, 129.0, 115.0, 133.0, 132.0, 132.0, 115.0, 133.0, 136.0, 141.0, 98.0, 118.0]

#Intitialize DataFrame from Python dictionary
hr = dict( language=language, office=office, salary = salary)
sal_df = pd.DataFrame(data=hr)

Pandas pivot table with count aggregate function

The Pandas library delivers several method that allows to pivot data for easier analysis and presentation. Today we will use the pivot_table method to quickly define a cross tabular summary of our data.

The pivot_table parameters are self explanatory:

  • values => specify the numeric data that will be aggregated.
  • index => represents the pivot table rows
  • columns => represents the pivot table columns

If we want to aggregate our data in a way that it will show a count of all instances of a value, we’ll use the aggfunc=’count’ parameter as shown below:

sal_df.pivot_table(values='salary', index = 'office', \
                     columns= 'language', aggfunc= 'count')

Here’s the result:

Counting distinct values in Pandas pivot

If we want to count the unique occurrences of a specific observation (row) we’ll need to use a somewhat different aggregation method. aggfunc= pd.Series.nunique will allow us to count only the distinct rows in the DataFrame that we pivoted.

sal_df.pivot_table(values='salary', index = 'office', \
                     columns= 'language', \
                     aggfunc= pd.Series.nunique)

Here’s the result:

Note that only unique occurrences of each row were counted.

Count and sum in one Pivot table

Pandas pivot_tables support multiple aggregation functions. In the next example we show how we can pass a list into the aggfunc parameter to calculate bothunique occurrences count and sum in the same pivot table:

sal_df.pivot_table(values='salary', index = 'office', \
                     columns= 'language', \ 
                     aggfunc= [pd.Series.nunique, 'sum'],  \
                     fill_value = '-')

Here’s our result:

Note: the fill_value parameter, allows to specify a filling character that replaces NAN values in the table.

Additional Learning

How to count values in a groupby object in Pandas?