Image by Author | Canva

Handling missing data in PySpark means choosing the right method — drop what’s unnecessary, fill gaps smartly, or predict missing values to keep analysis accurate.

Data gaps may cause analysis to fail silently, resulting in incorrect conclusions. Despite these gaps, PySpark brings highly effective tools for data cleaning, conversion, and analysis on massive data sets. I will also show you how to handle missing values when you solve real-world problems, so let’s get started!

Applying Missing Data Techniques to a Real-world Problem

We will use the “Highest Payment” question, which the City of San Francisco has asked during an interview.

Highest Payment
Make a pivot table to find the highest payment in each year for each employee.
Find payment details for 2011, 2012, 2013, and 2014.
Output payment details along with the corresponding employee name.
Order records by the employee name in ascending order

Here is the link to this question.

In this question, we have to create a pivot table to find the highest annual payment for each employee, but there are also other conditions. For instance, we should discover payment details between 2011 and 2014, and the output should include details along with the corresponding employee names.

The order should be in ascending order by the employee name. This question uses the sf_public_salaries dataset, so let’s explore this dataset first. Here are the column names and data types of these columns;

Let’s preview the dataset.

That’s good. Now, we are familiar with the dataset. If you look closely, you will see the missing values.

Curious about PySpark? Take a look at this post to learn more about what PySpark is.

Handling Missing Data

Inaccurate results can be derived from the messy data. This is where PySpark’s methods to clean up, transform, and manage missing values come in. It ensures our results are always true and accurate. Below, I’ll illustrate several key methods and put each that fits this data into practice.

Removing Missing Values

Columns such as notes (which have 200 missing values)s are non-essential. This column is unnecessary for salary analysis and, as such, can be dropped from the table completely.

To see the better shape, we will add .to Pandas() at the end of each code.

df_cleaned = sf_public_salaries.drop('notes').toPandas()

Here is the output.

When to use?

• If you don’t need the column in the analysis and there are too many missing values.
• It is best not to do so when this will affect the insights.

⚠️ Limitation: If the data is missing and we delete it, it will also remove some helpful information.

Filling Missing Values (Imputation with Default Values)

Columns such as status (131 missing values) indicate whether an employee is full-time (FT) or part-time (PT). The vast majority of employees are therefore deemed to have some kind of status, so we can replace missing values with “UNKNOWN.”

Once this has been completed, no confusion will arise due to misinterpretation.

df_filled = sf_public_salaries.fillna({'status': 'UNKNOWN'})

Here is the output.

When to use?

• When categorical fields such as “Job Title” or “Department” have nulls.
• If missing values exist because of data entry errors and need a sign.

⚠️ Limitation: Use of “UNKNOWN” might cause bias in grouping results and affect filtering accuracy.

Filling Missing Values (Mean, Median, Mode Imputation)

Statistical values such as mean, median, and mode help maintain consistency in numerical columns. PySpark’s mean(), median(), and mode() assist in filling in the missing numerical values.

from pyspark.sql.functions import mean

mean_basepay = sf_public_salaries.select(mean('basepay')).collect()[0][0]
mean_benefits = sf_public_salaries.select(mean('benefits')).collect()[0][0]

df_imputed = sf_public_salaries.fillna({'basepay': mean_basepay, 'benefits': mean_benefits}).toPandas()

Here is the output.

When to use?

• Use when the data is continuous and numeric.
• If the missing values occur randomly and without any particular bias towards a certain group

⚠️ Limitation: Mean imputation may be impacted by outlying cases like this, leading to filled values dependent on the data distribution.

Predictive Imputation (Using Machine Learning Models)

When simple imputation methods fail, machine learning algorithms can predict missing values. For example, regression models or k-nearest Neighbors (KNN) might use known patterns to estimate values in addition to missing entries such as those found here in our first table.

from pyspark.ml.feature import Imputer

# Define imputer for missing values
imputer = Imputer(inputCols=['numeric_column'], outputCols=["numeric_column_filled"]).setStrategy("mean")

# Apply imputer
df_imputed = imputer.fit(sf_public_salaries).transform(sf_public_salaries)

When to use?

• Use when missing data follows a pattern with respect to other attributes.
• It is particularly appropriate for structured datasets

⚠️ Limitation: Computationally expensive and requires labeled training data.

Which Methods Are Applied?

• Dropped Non-Essential Column (notes)
• Filled Missing Status with Default Value (status → ‘UNKNOWN’)
• Used Mean Imputation for Salary-Related Fields (basepay, benefits)

Solving the Challenge – Highest Payment

Good, now we have learned how to handle missing values, so let’s solve this question, too.
But first, let’s understand the task:

We need to:

1. Filter the dataset
   1. To take only the names of the employees in the most recent years
2. Standardize employee names
   1. To ensure case-insensitive comparisons.
3. Pivot the data into a table with one row for each employee.
   1. This will be called an unpivoted table. In the next step, something similar can also happen.
4. Fill in missing values with 0.
   1. Ensuring we account for all years, even if an employee was absent in a given year.

Here is the entire code:

import pandas as pd
import numpy as np
from pyspark.sql.functions import upper
from pyspark.sql import functions as F

years = [2011, 2012, 2013, 2014]
year_range = sf_public_salaries.filter(sf_public_salaries['year'].isin(years))
year_range = year_range.withColumn('employeename', upper(year_range['employeename']))
result = year_range.groupBy('employeename').pivot('year', years).agg(F.first('totalpay')).fillna(0).toPandas()
result.columns.name = None
result = result
result

Here is the output.

Check this to discover more PySpark interview questions.

Conclusion

We have solved missing data issues by discovering different methods, such as removing data from the entire dataset or filling it out because if you have a data issue, you should find a way to solve it.

In the end, we solved a real-world data question the City of San Francisco asked, so we learned and applied!

FAQ’s

How do you handle missing values in PySpark?
Missing data can wreck analysis, but PySpark makes fixing it easy. You’ve got .dropna() to ditch rows, .fillna() to add placeholders, and .na.replace() for swapping values. Choosing wisely keeps datasets intact without throwing away good info.

What is the best way to handle missing data?
No single answer works every time. If gaps are small, dropping rows is fine. When categorical fields have blanks, filling them with “UNKNOWN” keeps structure. Missing numbers? Mean, or median imputation smooths things out. If patterns exist, machine learning can even predict missing values.

How do you handle blank values in PySpark?
Blanks hide in plain sight, often slipping through filters. PySpark sees them differently than null, so you’ve got to convert them first. Using F.when(F.col(column) ==,”, None) turns blanks into null, making them easier to manage.

How do you deal with missing data in a DataFrame?
Step one: find the gaps. Step two: decide if dropping, filling, or predicting works best. Each dataset tells a different story, so handling missing values depends on what matters most in your analysis—keeping bias low and accuracy high. That’s the goal.