[Python] Unexpected PySpark filter behaviour

I want to filter out the rows where CID (string type) is '-' and trait_diff is null. The codes I have provided are filtering out the rows where both of them are null even if I do not put isNull for CID (Like in 3rd line of code).

df = df.filter(
~((col('CID') == "-") & (col('CID').isNull()) & col('trait_diff').isNull())
)

df = df.filter(
~((col('CID').isNull()) & col('trait_diff').isNull())
)

df = df.filter(
~((col('CID') == "-") & col('trait_diff').isNull())
)


Above 3 code filter out the same rows where CID is null and trait_diff is null regardless if CID of being null is not present. I have checked it filters out 3 rows in all cases and it is where both of these are null.

In the first, I can't understand how CID could be '-' and null at the same time. When removing the ~ to get the 3 filtered out rows, it does not return anything. (to check what rows are getting filtered, I exported the excel file, databricks did not return anything)

I have also tried out this one exactly the same behaviour as above ones.

df = df.filter(
(col('CID') != "-") | col('trait_diff').isNotNull()
)


Can anybody help me out here understanding why it does not give expected results.

This one seems to work giving out expected number of rows, no idea why this works (because if CID is '-' and it is not null and we do not need to put this condition in) and not others

df = df.filter(
~((col('CID') == "-") & col('CID').isNotNull() & col('trait_diff').isNull())
)

Continue reading...