Handling Null Values

Spark tutorial · PySpark.in

Handling Null Values in PySpark

Handling null (missing) values is a crucial part of data cleaning in PySpark. Apache Spark’s DataFrame API provides several functions to identify, replace, or remove null values to ensure data quality. This guide covers five key topics for null handling, with both beginner-friendly explanations and intermediate insights, along with code examples and practical tips.

1. Using isNull() and isNotNull()

Explanation (Basics): PySpark uses None (or NULL in Spark SQL) to represent missing data. The DataFrame Column class provides isNull() and isNotNull() methods to check for null values in a column. These return a Column of boolean values indicating which entries are null or not. Specifically, col.isNull() yields True for rows where the value is null and False otherwise, while col.isNotNull() does the opposite (True for non-null, False for null). Using these in DataFrame operations allows you to filter or flag missing data easily.

Intermediate Insights: isNull/isNotNull create boolean expressions that can be used in DataFrame transformations. For example, you might use df.filter(df["column"].isNotNull()) to keep only rows with non-missing values. These methods are preferred over direct Python None checks because Spark SQL handles null semantics differently (e.g., using == None may not behave as expected in filters). Under the hood, isNull() corresponds to SQL IS NULL (returning true for null inputs). You can also use these to create indicator columns marking nulls for analysis or debugging.

Syntax & Examples:

```

from pyspark.sql import SparkSession

from pyspark.sql.functions import col

spark = SparkSession.builder.getOrCreate()

# Sample DataFrame with a null value

data = [("James", None), ("Anna", 30), ("Julia", 25)]

df = spark.createDataFrame(data, ["Name", "Age"])

# Filter rows where Age is not null

non_null_df = df.filter(col("Age").isNotNull())

non_null_df.show()

```

In this example, the row with Name="James" (which had Age=None) is filtered out, as isNotNull() keeps only rows with valid age. You could similarly use df.filter(col("Age").isNull()) to do the opposite (select only rows with Age missing). Another use case is adding a boolean column: for instance, df.withColumn("Age_is_null", col("Age").isNull()) would append a column marking which rows have Age as null.

Practical Scenarios & Tips:

Filtering invalid data: Use isNotNull() to exclude incomplete records from analyses. For example, before computing statistics on a column, filter out nulls to avoid errors or skewed results.
Flagging missing values: Use isNull() in a withColumn to flag missing entries (e.g., create an "is_missing" indicator). This can help in data quality reports or conditional transformations.
Avoiding pitfalls: Always prefer col("X").isNull() over col("X") == None for correctness. Spark’s null handling follows SQL semantics where null comparisons are not straightforward – using the provided methods ensures the intended behavior.

2. Using fillna(), dropna(), and fill

Explanation (Basics): When dealing with missing values, you typically have two strategies: fill them with a default/value or drop the rows containing them. PySpark provides DataFrame.fillna() (and its alias DataFrame.na.fill(), sometimes called just fill) to replace nulls with specified values, and DataFrame.dropna() (alias DataFrame.na.drop()) to remove rows with nulls. These methods help ensure your DataFrame has no nulls before further processing.

fillna() / fill(): Replace null (and NaN) values with a given constant or with different values per column. You can pass a single value (e.g. df.fillna(0) to fill all numeric nulls with 0) or a dictionary mapping column names to replacement values (to fill different columns with different defaults).
dropna(): Remove rows containing nulls. By default it drops any row with at least one null (how='any'), but you can drop only rows where all values are null (how='all') or specify a threshold of non-null values required, as well as limit to a subset of columns.

Intermediate Insights: The fillna and dropna functions are flexible. For filling, PySpark infers the type – e.g., replacing a string column’s nulls with "Unknown" or a numeric column’s nulls with 0. If you provide a value that doesn’t match the column type, that column is left unchanged. The dropna method can be fine-tuned: using df.dropna(how="all") will only drop rows that are completely empty (all columns null), and df.dropna(thresh=k) will drop rows with fewer than k non-null values, which is useful if you want to keep rows that have at least a certain amount of valid data. You can also limit dropping to specific columns via the subset parameter.

Syntax & Examples:

```

from pyspark.sql import Row

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

# Sample DataFrame with multiple nulls and NaNs

df2 = spark.createDataFrame([

Row(age=10, score=80.0, name="Alice"),

Row(age=5, score=float("nan"), name="Bob"),

Row(age=None, score=None, name="Tom"),

Row(age=None, score=float("nan"), name=None)

])

df2.show()

# Fill null age with 0 and null name with "Unknown"

filled_df = df2.fillna({"age": 0, "name": "Unknown"})

filled_df.show()

# Drop any row that has a null or NaN in any column

dropped_df = df2.dropna()

dropped_df.show()

```

Here:

We created df2 with some nulls (age or name missing) and NaN (score). Calling fillna({"age": 0, "name": "Unknown"}) replaces all null ages with 0 and null names with "Unknown". The numeric score column is unchanged by this fill since we didn't specify a fill value for it (you could add e.g. "score": 0.0 in the dict to fill numeric NaNs/nulls as well).
We then call df2.dropna() with default how='any', which drops any row that contains any null or NaN. In the output of dropped_df.show(), only the first row (Alice) remains, because it was the only one with no missing values in any column.

You can adjust dropna() behavior: for example, df2.na.drop(how="all") would drop only rows where all columns are null/NaN (in our sample, none of the rows have all values null, so all except possibly the one with name None would remain), and df2.na.drop(subset=["name"]) would drop rows where the name is null, regardless of other columns.

Practical Scenarios & Tips:

Imputing missing data: Use fillna when you want to fill missing values with substitutes. For example, fill missing numeric fields with 0 or an average, and missing strings with placeholders like "Unknown". This is useful to avoid losing data that is partially available. You can fill multiple columns in one go by passing a dictionary of {column: value} replacements.
Dropping incomplete records: Use dropna when records with nulls are not useful or would bias analysis. For instance, if a row is missing critical fields (like an ID or a required measurement), dropping it might be appropriate. Tweak the how or thresh to be as lenient or strict as needed. E.g., df.dropna(thresh=2) keeps rows with at least 2 non-null values.
Combination approach: It’s common to drop rows that are too empty (many nulls) while filling in single nulls in otherwise good rows. PySpark allows mixing these – you might drop rows where a crucial column (like ID or name) is null, then apply fillna to other columns to handle remaining nulls.
Remember NaN: By default, Spark’s null-handling treats NaN (Not a Number) as equivalent to null for dropping and filling. The examples above showed score=float("nan") being dropped by dropna(). If you specifically want to target NaNs, consider using df.fillna() for numeric columns or the DataFrame summary functions to replace or remove NaNs.

3. Replacing Values in DataFrames

Explanation (Basics): Beyond null-specific handling, PySpark DataFrames have a more general method DataFrame.replace() (also accessible via df.na.replace()) to substitute one set of values with another. This is useful for standardizing or cleaning data – for example, replacing placeholder strings like "N/A" with actual nulls, or converting codes to meaningful values. The replace function can operate on any values (not just nulls) of type string, number, or boolean. You can replace a single value, multiple values, or even use a dictionary for mappings.

Insights: df.replace(old_value, new_value, subset=None) returns a new DataFrame where all occurrences of old_value are replaced with new_value in the specified columns (or all columns if no subset provided). If you pass a list of values and a list of replacements, it will perform the mapping pairwise. You can also supply a dict to map multiple old values to new values in one call. Notably, replace can handle None as either the value to replace or the replacement value. For instance, you might replace a special marker (like "unknown") with actual null (None), or vice versa. Keep in mind that type matters: the replacement value should be the same type as the values being replaced (Spark will ignore columns where types don’t match the replacement).

Syntax & Examples:

```

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

# Using replace to substitute values

df3 = spark.createDataFrame([

(10, 80, "Alice"),

(5, None, "Bob"),

(None, 10, "Tom"),

(None, None, None)

], schema=["age", "height", "name"])

# 1) Replace all occurrences of 10 with 20 across the DataFrame

df3.replace(10, 20).show()

# 2) Replace 'Alice' with None (null) in all columns

df3.replace("Alice", None).show()

# 3) Replace multiple names in the 'name' column

df3.replace(["Alice", "Bob"], ["A", "B"], subset="name").show()

```

Explanation of the above code:

Global numeric replace: df3.replace(10, 20) will find every occurrence of the value 10 in any column and replace it with 20. In our df3, this affects the age column (and the height column where it equals 10) – for example, age 10 becomes 20.
Replace with null: df3.replace("Alice", None) will replace the string "Alice" with actual null (None) wherever it appears. In the output, Alice’s name would become null.
Column-specific multiple replace: df3.replace(["Alice", "Bob"], ["A", "B"], subset="name") targets only the name column, replacing "Alice" with "A" and "Bob" with "B". Other columns are unaffected in this call. The result would show name values transformed (Alice → A, Bob → B, others unchanged).

Practical Scenarios & Tips:

Standardizing data entries: Use replace to normalize variations. For example, if you have categorical data with variants like "Male"/"M" or "Female"/"F", you can replace them to a single standard value. Or replace "N/A"/"NA" strings with actual nulls (None) so that Spark recognizes them as missing.
Fixing outliers or errors: If you notice a specific erroneous value (e.g., a sensor recorded -999 for missing readings, or an age of 150 which is invalid), you can replace those with a more appropriate value or null. E.g., df.replace(-999, None) to turn an error code into a null.
Multiple replacements at once: Leverage dictionary or list syntax to perform multiple replacements in one go. This is more efficient than chaining multiple .replace() calls. For instance, df.na.replace({"OLD1": "NEW1", "OLD2": "NEW2"}, subset=["colA"]) would replace OLD1→NEW1 and OLD2→NEW2 in column colA in one pass.
Remember fillna vs replace: To handle nulls specifically, prefer fillna (as it’s concise for that purpose). But if you need to replace other values or a mix of values (including possibly turning null to a value or vice versa), use replace. For example, to convert nulls to a sentinel like -1, you could do df.replace(None, -1) (though fillna(-1) is equivalent in this case). To convert sentinel values to nulls, replace is the way (e.g. replace 0 with None).
Type considerations: If you attempt to replace a string in an integer column, Spark will ignore that column. Ensure the value types align with the target column types when using replace, or use the subset parameter to target only the relevant columns.

4. Removing Null Rows

Explanation (Basics): Removing rows that contain null values is a common operation when those rows are incomplete or not useful for analysis. We have already introduced DataFrame.dropna() in section 2, which is the primary method to drop null-containing rows. Additionally, you can remove nulls by filtering using isNotNull() on required columns. The goal is to exclude any records that don't meet your completeness criteria.

Insights: Deciding to remove rows with nulls depends on context. You may choose to drop only if a certain column (or set of columns) is null, or if too many columns are null. PySpark’s dropna() supports these choices via its parameters:

how='any' (default) drops a row if any column is null.
how='all' drops a row only if all columns are null.
thresh=k keeps a row if it has at least k non-null values (drops if fewer than k).
subset=[cols] to consider only certain columns for null checks.

Using isNotNull() filters gives you manual control for complex conditions (e.g., drop if colA is null and colB is null, etc.). Under the hood, filtering nulls with isNotNull or using dropna(subset=...) are similar; dropna is just a convenient wrapper.

Syntax & Examples:

Dropping nulls using dropna:

```

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

# Using replace to substitute values

df3 = spark.createDataFrame([

(10, 80, "Alice"),

(5, None, "Bob"),

(None, 10, "Tom"),

(None, None, None)

], schema=["age", "height", "name"])

# Drop rows with any nulls in any column

clean_df = df3.na.drop() # same as df3.dropna()

clean_df.show()

# Drop rows where both age and name are null (subset with how='any')

subset_drop_df = df3.dropna(subset=["age", "name"])

subset_drop_df.show()

```

In the first call, df3.na.drop() will remove any row that has a null in any column. In our sample df3 (from section 3), that would drop every row except those where all columns were filled (likely only the row with Alice, if any). The second call dropna(subset=["age","name"]) drops rows where either age or name is null. This focuses the null-check on those two columns only – for example, a row with age=None or name=None will be dropped, but a row with score null would not be dropped if age and name are present.

Dropping nulls using filter:

```

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

# Using replace to substitute values

df3 = spark.createDataFrame([

(10, 80, "Alice"),

(5, None, "Bob"),

(None, 10, "Tom"),

(None, None, None)

], schema=["age", "height", "name"])

# Using filter to drop rows with null in specific column(s)

no_null_name_df = df3.filter(col("name").isNotNull())

no_null_name_df.show()

no_null_multi_df = df3.filter( (col("age").isNotNull()) & (col("height").isNotNull()) )

no_null_multi_df.show()

```

Here, no_null_name_df retains only rows where the name is not null (dropping any row with name = null) – this is equivalent to dropna(subset=["name"]). The no_null_multi_df example shows how to drop rows that have null in either of two columns by combining conditions: we keep rows where both age and height are non-null. Using filters with multiple conditions gives fine-grained control (you could also drop where one column is null but another is not, etc., by adjusting the boolean logic).

Practical Scenarios:

Required fields: If certain columns are essential (e.g., a primary key or identifier, timestamp, etc.), it often makes sense to drop rows where those are null. Use dropna(subset=[...]) for those required fields. For example, df.dropna(subset=["user_id"]) ensures no output row has a missing user ID.
Partial dropping: Sometimes you only want to remove rows that are mostly empty. In that case, using thresh is helpful – e.g., df.dropna(thresh=3) will drop rows that have fewer than 3 non-null values, meaning at least 3 columns must be present for a row to survive.
Combination filters: For complex conditions that dropna can’t express (like “drop if colA is null and colB is null, but not if only one is null”), use df.filter(...) with the appropriate logical combination. For instance, df.filter(~(col("A").isNull() & col("B").isNull())) would drop rows where both A and B are null (the ~ negates the condition, thus keeping everything except those).
Performance: Dropping or filtering nulls is generally efficient in Spark. However, remember that dropping rows reduces your dataset size – ensure that’s acceptable for your analysis. If the fraction of null rows is small, dropping is fine; if it’s large, consider whether imputing (filling) might preserve more data.
Know your data: Always inspect how many nulls you have (e.g., using df.select([count(when(col(c).isNull(), c)).alias(c) for c in df.columns]) or similar aggregation) before deciding to drop en masse. You might inadvertently drop a significant portion of data. A common pattern is to first use filters like isNull/isNotNull to count or explore missing data, then apply drop rules informed by that exploration.

5. Handling Nulls in Join Conditions

Explanation (Basics): Joining DataFrames in Spark while handling nulls can be tricky. In SQL-style semantics (which Spark follows), a join condition like df1.key == df2.key will not match rows where key is null on either side – because null is not considered equal to null by default. This means if both DataFrames have records with a null join key, those records won't join to each other in an inner join. They would be dropped from an inner join and appear with nulls only on one side in an outer join. To properly handle nulls in join conditions, you have a few approaches:

Exclude null keys before joining (if null means “no linkage”, you might filter(col("key").isNotNull()) on each side prior to join).
Use a null-safe equality comparison that considers nulls as equal.
Replace or fill null keys with a placeholder value as a workaround (not always recommended, but sometimes used).

Insights: Spark provides a null-safe equals operator, accessible in DataFrame API via Column.eqNullSafe() method. This corresponds to the SQL operator <=>. Using df1.col.eqNullSafe(df2.col) in the join condition will treat NULL == NULL as true. For example, df1.join(df2, df1["id"].eqNullSafe(df2["id"])) will match rows even when id is null in both DataFrames – something that a normal == join would not do. The difference can be seen in join results: an inner join on a null key with the normal == yields 0 matches, whereas using eqNullSafe yields a match for the pair of null keys.

Another approach is using coalesce to replace nulls with a default before comparison (e.g., coalesce(df1.key, lit("##")) == coalesce(df2.key, lit("##")) treating “##” as a placeholder). This can achieve a similar effect, but you must choose a placeholder that doesn’t occur in real data, and it adds a bit of complexity. Generally, eqNullSafe is cleaner and explicitly built for this scenario.

Syntax & Example:

```

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

# Prepare two DataFrames to join

data1 = [(1, "Alice"), (2, None)]

data2 = [(1, "Engineer"), (2, None)]

dfA = spark.createDataFrame(data1, ["id", "value"])

dfB = spark.createDataFrame(data2, ["id", "value"])

# Inner join on id using standard equality (null won't match null)

inner_join_std = dfA.join(dfB, dfA["id"] == dfB["id"], "inner")

print("Inner join rows (standard):", inner_join_std.count())

# Inner join on id using null-safe equality

inner_join_nullsafe = dfA.join(dfB, dfA["id"].eqNullSafe(dfB["id"]), "inner")

print("Inner join rows (null-safe):", inner_join_nullsafe.count())

inner_join_nullsafe.show()

```

In this example, dfA and dfB each have an id 1 and an id 2 (with the id 2 being null in both). Using the regular join condition (== on id), the row with id null does not join – so the inner join count will be 1 (only the id=1 matched). Using the null-safe equality with eqNullSafe, the null id in dfA is considered equal to the null id in dfB, so that pair joins, resulting in 2 matched rows. The output of inner_join_nullsafe.show() would include the null id row joined, something you wouldn’t get with a normal join condition.

Handling Null Values