How to count distinct by group in Pyspark

Aggregation of fields is one of the basic necessity for data analysis and data science. Pyspark provide easy ways to do aggregation and calculate metrics. Finding distinct count value for each group can also be achieved while doing the group by. The function that is helpful for finding the distinct count value is nunique(). The below article explains with the help of an example How to calculate distinct count value by Group in Pyspark.

John has store sales data available for analysis. There are five columns present in the data, Geography (country of store), Department (Industry category of the store), StoreID (Unique ID of each store), Time Period (Month of sales), Revenue (Total Sales for the month). John is looking forward to calculate distinct count of stores for each department. As there are duplicates in Store ID due to multiple rows for each store, unique or distinct count will be needed.

Find Total Number of Stores in Each Department

• Step 1: Firstly, Import all the necessary modules.

import pandas as pd
import findspark
findspark.init()
import pyspark
from pyspark import SparkContext
from pyspark.sql import SQLContext 
sc = SparkContext("local", "App Name")
sql = SQLContext(sc)
import pyspark.sql.functions as func 

• Step 2: Use count(distinct ..) function along with groupby operation. As we are looking forward to group by each Department, “Department” works as groupby parameter. The StoreID field contains the ID of each store. To find the distinct count value, we will be using “StoreID” for distinct count value calculation. For the current example, syntax is:

df1.groupby('Department').agg(func.expr('count(distinct StoreID)')\
                              .alias('Distinct_Stores')).show()

Example 2: Calculate Total Number of Stores for Each Geograpphy

• Here we are looking forward to calculate the distinct count value across each Geography. So, the field in groupby operation will be “Geography”

df1.groupby('Geography').agg(func.expr('count(distinct StoreID)')\
                              .alias('Distinct_Stores')).show()

Thus, John is able to calculate value as per his requirement in Pyspark. This kind of extraction can be a requirement in many scenarios and use cases. This example talks about one of the use case.

To get top certifications in Pyspark and build your resume visit here. Additionally, you can read books listed here to build strong knowledge around Pyspark. 

Visit us below for video tutorial: