How to Calculate Distinct Column Counts and Percentages in SQL and PySpark

A comprehensive guide on calculating distinct column counts and row-wise percentages using SQL and PySpark.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: count of distinct columns using group by and calculating percentage

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Calculate Distinct Column Counts and Percentages in SQL and PySpark

When dealing with data, situations often arise where you need to analyze distinct counts of certain columns and also represent that information in a percentage format. If you find yourself needing to compute the counts of distinct entries while also deriving percentages, you might have come across challenges in your coding journey. This post will help clarify the process of leveraging SQL and PySpark to achieve your desired outcome effectively.

The Problem

Suppose you have a dataset where you wish to count unique identifiers (let’s call them tid) by a category (indicator). Beyond merely counting them, you desire to convert these counts into a percentage of the total unique identifiers. This would allow for better insights into the dataset distribution.

For instance, consider you have executed a SQL query that summarizes the counts by indicator:

[[See Video to Reveal this Text or Code Snippet]]

The output might look something like this:

[[See Video to Reveal this Text or Code Snippet]]

But now you want each tidcount presented as a percentage of the total counts, like so:

[[See Video to Reveal this Text or Code Snippet]]

This article outlines how you can achieve this using both SQL and PySpark.

Solution with SQL

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the SQL Query

COUNT(DISTINCT tid): Counts the number of distinct tid values per indicator.

SUM(COUNT(DISTINCT tid)) OVER (): Computes the total count of distinct tid values across all rows to calculate the percentage.

Percentage Calculation: Each distinct count is divided by the total count of distinct tid, multiplied by 100 to get the percentage.

Solution with PySpark

For those utilizing PySpark, the approach is slightly different but follows a similar logic. Here’s how you can implement it:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the PySpark Code

groupby('indicator'): Groups the DataFrame by indicator values.

agg(F.count_distinct('tid')): Aggregates to count the distinct tid values.

withColumn: Adds a new column PCT that calculates the percentage of total distinct counts with respect to the entire DataFrame.

Example Demonstration

To illustrate how it works, let's examine a sample DataFrame:

[[See Video to Reveal this Text or Code Snippet]]

Running either of the provided solutions will yield results such as:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

Calculating distinct counts and their corresponding percentages is straightforward once you understand how to leverage SQL and PySpark effectively. By applying the techniques shared here, one can gain meaningful insights into their data. Whether using SQL queries or PySpark DataFrames, the goal is to clarify the data while ensuring it’s represented in an intuitive format for analysis.

In your data analytics journey, be sure to experiment and find a solution that best fits your needs!