Tilde negation filtering out nulls

user_5185736 0
Viewed 125

I wonder if there's a neat way around this problem

Here's my unfiltered dataframe, deltas, which is the result of a full join between source and target datasets: enter image description here

I want to remove the 'Unknown' and 'Not Specified' values (i.e. rows 5 and 6), so I've used a tilde to remove those rows:

deltas = deltas.filter(~deltas['tgt_property_owner_type'].isin(['Unknown', 'Not Specified']))

However, running a display(deltas) with the tilde returns no results. I imagine this is because as those rows are null for the column, it excludes those too as it cannot say for certain they are not 'Unknown' or 'Not Specified'. However, a positive version of the above (i.e. no tilde) returns just rows 5 and 6 and not the nulls.

This is my interim solution, which works:

deltas = deltas.withColumn("IsMissingKValue",\
                            when(deltas.tgt_property_owner_type.isin(['Unknown', 'Not Specified']),True) \
                           .otherwise(False))
deltas = deltas.filter(deltas['IsMissingKValue'] == False)

Is there a clever way for the tilde negation to ignore the nulls during evaluation? I'm aware of work arounds such as replacing the nulls with blank strings or running this into a temp view and sorting it out with SQL, but want to know if there's a purer python syntax that can be used.

pyspark databricks python
1 Answers

You can create a predicate m which will ignore the null values when you want to check if column includes certain strings, and when you negate this predicate this will return the rows where column does not include certain strings plus the null rows

c = 'tgt_property_owner_type'
m = deltas[c].isin(['Unknown', 'Not Specified']) & deltas[c].isNotNull()

deltas = deltas.filter(~m)

Given the dataframe df

df.show()
+---+---+----+
|  A|  B|   C|
+---+---+----+
|  1| r2|   x|
|  3| r1|null|
|  3| r2|   y|
|  4| r1|   z|
|  5| r2|null|
|  5| r1|   p|
+---+---+----+

Here is the example of how this would work

m = df['C'].isin(['x', 'y']) & df['C'].isNotNull()

df.filter(m).show()
+---+---+---+
|  A|  B|  C|
+---+---+---+
|  1| r2|  x|
|  3| r2|  y|
+---+---+---+

df.filter(~m).show()
+---+---+----+
|  A|  B|   C|
+---+---+----+
|  3| r1|null|
|  4| r1|   z|
|  5| r2|null|
|  5| r1|   p|
+---+---+----+
user_128331660
Related