Delete rows in PySpark dataframe based on multiple conditions

user_13695889 0
Viewed 4873

I have a dataframe with a structure similar to the following:

col1, col2, col3, col4
A,A,A,A
A,B,C,D
B,C,A,D
A,C,A,D
A,F,A,A
A,V,B,A

What I want is to 'drop' the rows where conditions are met for all columns at the same time. For example, drop rows where col1 == A and col2 == C at the same time. Note that, in this case, the only row that should be dropped would be "A,C,A,D" as it's the only one where both conditions are met at the same time. Hence, the dataframe should look like this:

col1, col2, col3, col4
A,A,A,A
A,B,C,D
B,C,A,D
A,F,A,A
A,V,B,A

What I've tried so far is:

# spark library import
import pyspark.sql.functions as F

df = df.filter(
               ((F.col("col1") != "A") & (F.col("col2") != "C"))
               )

This one doesn't filter as I want, because it removes all rows where only one condition is met, likecol1 == "A" or col2 == "C", returning:

col1, col2, col3, col4
B,C,A,D

Can anybody please help me out with this?

Thanks

pyspark dataframe python
3 Answers
from pyspark.sql.functions import when
df.withColumn('Result',when(df.col1!='A',"True").when(df.col2!='C',"True")).filter("Result==True").drop("Result").show()
user_54424680

Combine both conditions and do a NOT:

cond = (F.col('col1') == 'A') & (F.col('col2') == 'C')

df.filter(~cond)
user_141657300

This can be a working solution for you - use when() condition and create a column based on the condition you wish for

Create the DF Here

df = spark.createDataFrame([("A", "A", "A", "A"),("A", "B", "C", "D"),("B", "C", "A", "D"),("A", "C", "A", "D"), ("A", "F", "A", "A"), ("A", "V", "B", "A")],[ "col1","col2", "col3", "col4"])
+----+----+----+----+
|col1|col2|col3|col4|
+----+----+----+----+
|A   |A   |A   |A   |
|A   |B   |C   |D   |
|B   |C   |A   |D   |
|A   |C   |A   |D   |
|A   |F   |A   |A   |
|A   |V   |B   |A   |
+----+----+----+----+

When Condition as

df = df.withColumn("filter_col", F.when(((F.col("col1") == F.lit("A")) & (F.col("col2") == F.lit("C"))), F.lit("1")))
df.show()
+----+----+----+----+----------+
|col1|col2|col3|col4|filter_col|
+----+----+----+----+----------+
|   A|   A|   A|   A|      null|
|   A|   B|   C|   D|      null|
|   B|   C|   A|   D|      null|
|   A|   C|   A|   D|         1|
|   A|   F|   A|   A|      null|
|   A|   V|   B|   A|      null|
+----+----+----+----+----------+

Filter out the row now

df = df.filter(F.col("filter_col").isNull()).select("col1", "col2", "col3", "col4")
df.show()
   +----+----+----+----+
|col1|col2|col3|col4|
+----+----+----+----+
|   A|   A|   A|   A|
|   A|   B|   C|   D|
|   B|   C|   A|   D|
|   A|   F|   A|   A|
|   A|   V|   B|   A|
+----+----+----+----+
user_51082200
Related