pyspark: the number of entries in a column which are null or not

There's were a handful of resources describing how to count the number of null values in a pyspark dataframe. For example this Stack Overflow thread which asks for the number of null (and nan) values for each column in a pyspark dataframe. Thus given the total number of entries in the dataframe, one could indirectly compute the number of not null values (by taking the difference). However, I sought after a solution which would simultaneously output both the number of null and not null values (for a single column).

Eventually, I wrote the following possible solution:
(
  df
  .select(df.MY_COLUMN_NAME.isNull().alias(MY_COLUMN_NAME))
  .groupby(MY_COLUMN_NAME)
  .count()
  .show()
)

In contrast, the following is the solution with just the count of null values:
(
  df
  .select(count(when(df.MY_COLUMN_NAME.isNull()).alias(MY_COLUMN_NAME))
  .show()
)

Of course the advantage of the latter was it's ability to summarize across every column:
(
  df
  .select([count(when(col(c).isNull()).alias(c) for c in df.columns])
  .show()
)

[20200813][20200923 Edit]

Comments

  1. Devi.AngularjsAugust 6, 2026 at 12:22 AM

    nice

    ReplyDelete
  2. Devi.AngularjsAugust 6, 2026 at 12:23 AM

    The article demonstrates a simple PySpark technique for counting both null and non-null values in a DataFrame column using isNull(), groupBy(), and count(). This approach helps data engineers and analysts quickly evaluate data quality by identifying missing values before performing data transformation, analytics, or machine learning. Understanding how to validate datasets efficiently is an essential part of building reliable big data processing pipelines.

    PySpark is a powerful framework for distributed data processing, enabling organizations to analyze massive datasets efficiently across clusters. Its DataFrame API provides scalable operations for filtering, aggregation, data validation, and ETL workflows, making it a key technology for modern big data analytics. Students interested in implementing enterprise-scale analytics solutions can explore Big Data Projects, featuring practical implementations involving Apache Spark, Hadoop, distributed computing, ETL pipelines, and cloud-based data processing.

    ReplyDelete

Post a Comment

Popular posts from this blog

Getting to know... D3

Today I was looking into ObservableHQ and their Quickstart page had many suggested resources. The second of these resources was titled "Learn D3." Since it's been several years since I've worked with D3, I felt it would be appropriate to go through it. In addition, at the time I thought the resource would lean heavily towards learning D3 within the context of an Observable notebook. Instead, the series was dominantly a comprehensive introduction on D3, delivered via a series of Observables, and occasionally touched on some of the benefits of using an Observable notebook. In hindsight, I should have expected the relationship between an Observable notebook and D3 to be more like that between a Jupyter notebook and a Python module: learning to use the features of Jupyter and learning to use a particular Python module are mutually exclusive activities. In any case, below you'll find my notes to each of the sections in the Observable series "Learn D3" by ...
Read more

Parquet + PySpark (= Speed)

It was mentioned in passing in my previous post that I needed to setup `winutils.exe` in order to save my dataframe to a parquet file. It was not, however, mentioned why I wanted to save the dataframe as a parquet file. In short, the answer is for speed. First, let's load a dataset using pyspark, save it as a parquet file, load it back, and compare the time it takes to count the number of items in the file. Select your favorite medium-sized data file. I happened to be working with a CSV file that was about 7 GB, "data.csv" (composed of about 6.5 million rows and 300 columns). My data had a header, so I ran `df = spark.read.option("header", "true").csv("data.csv")` The default headings had characters not allowed by parquet when saving, so I looped through the columns and use regex to find and replace the set of illegal characters (" ,;{}()\n\t="): import re df2 = df for column in df.columns: new_column = re.sub(...
Read more

Observable HQ: dropdown input, d3 transition, and viewof

Over the past few days, I wanted to isolate my understanding of the relationship between the selection made in a dropdown form input and other cells which depend on that selection. This exploration started with drawing a rectangle and using code from example notebooks with dropdown selections to cause a transition in the chart to occur (see Arc Diagram ). One added complexity with Mike Bostock's Arc Diagram notebook is a timeout that's added which will automatically trigger a change in the dropdown selection. Originally this code block had a bug which caused me some confusion, but it has been a valuable learning experience: Mike Bostock commented on the bug and fixed the bug, so I was able to update my notebook accordingly. In any case, contained in the notebook are three charts and each chart behaves different based on whether I refer to the dropdown form object by `myNumber` or `viewof myNumber.value` as well as where I make such references. In the first chart, I referenc...
Read more