This tutorial will explain how filters can be used on dataframes in Pyspark. where() function is an alias for filter() function.

PySpark: Dataframe Filters

This tutorial will explain how filters can be used on dataframes in Pyspark. where() function is an alias for filter() function. Following topics will be covered on this page:

• Basic filters
• Filter using IN clause
• Filter using not IN clause
• Filter using List
• Filter Null Values
• Filter not Null Values
• Filter using LIKE operator
• Filter using not LIKE operator
• Filter using Contains
• Filter using Between
• Multicolumn filters

➠ Filter Syntax: Filter function takes only 1 parameter. One or multiple conditions can be used to filter data, each condition will evaluate to either True or False. where() function is an alias for filter() function.

Dataframe.filter(condition) 


Sample Data: Dataset used to explain dataframe filters can be downloaded from here(employee) and here(department).

from pyspark.sql.functions import col #importing col function

empdf=spark.read.parquet("file:///path_to_file/employee.parquet")

deptdf=spark.read.parquet("file:///path_to_file/department.parquet")

➠ Basic Filters:

• Example 1:

  
  from pyspark.sql.functions import col #importing col function
  
  empdf.filter(col("emp_name")=="SCOTT").show()
  
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000288|   SCOTT|3000.00|   1000276|    200|
  +-------+--------+-------+----------+-------+
  

  
  
• Example 2: This approach may not work with multiple filter conditions.

  
  empdf.filter("emp_name=='SCOTT'").show()
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000288|   SCOTT|3000.00|   1000276|    200|
  +-------+--------+-------+----------+-------+
  

  
  
• Example 3: Filter records after applying modulo(remainder) operation.

  
  empdf.filter(col("emp_no")%10==9).show()
  
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000299|   ALLEN|1600.00|   1000258|    300|
  +-------+--------+-------+----------+-------+
  

  
  
• Example 4: Filter records using not equal (!=) condition.

  
  empdf.filter(col("manager_id")!=1000258).show()
  
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000258|   BLAKE|2850.00|   1000245|    300|
  |1000262|   CLARK|2450.00|   1000245|    100|
  |1000276|   JONES|2975.00|   1000245|    200|
  |1000288|   SCOTT|3000.00|   1000276|    200|
  |1000292|    FORD|3000.00|   1000276|    200|
  |1000294|   SMITH| 800.00|   1000292|    200|
  |1000326|   ADAMS|1100.00|   1000288|    200|
  |1000346|  MILLER|1300.00|   1000262|    100|
  |1000347|   DAVID|1400.00|   1000245|    500|
  +-------+--------+-------+----------+-------+
  

  
  
• Example 5: Filter records using less than (<) or greater than(>) operator.

  
  empdf.filter(col("salary") <  1400).show()
  
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000294|   SMITH| 800.00|   1000292|    200|
  |1000310|    WARD|1250.00|   1000258|    300|
  |1000312|  MARTIN|1250.00|   1000258|    300|
  |1000326|   ADAMS|1100.00|   1000288|    200|
  |1000336|   JAMES| 950.00|   1000258|    300|
  |1000346|  MILLER|1300.00|   1000262|    100|
  +-------+--------+-------+----------+-------+
  

➠ Filter using IN clause: isin attribute of col function can be used to pass comma separated values or a list as parameter to filter data.

• Example 1:

  
  from pyspark.sql.functions import col
  
  empdf.filter((col("emp_no").isin(1000262,1000276,1000310))).show()
  
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000262|   CLARK|2450.00|   1000245|    100|
  |1000276|   JONES|2975.00|   1000245|    200|
  |1000310|    WARD|1250.00|   1000258|    300|
  +-------+--------+-------+----------+-------+
  

  
  
• Example 2: Filter data using Python list.

  
  li=[1000346,1000288,1000294]
  empdf.filter((col("emp_no").isin(li))).show()
  
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000288|   SCOTT|3000.00|   1000276|    200|
  |1000294|   SMITH| 800.00|   1000292|    200|
  |1000346|  MILLER|1300.00|   1000262|    100|
  +-------+--------+-------+----------+-------+
  

➠ Filter using not IN clause: Tilde sign(~) can be used along with isin attribute of col function to negate passed comma separated values or a list as parameter to filter data.

• Example 1:

  
  from pyspark.sql.functions import col
  
  empdf.filter(~(col("emp_no").isin(1000262,1000276,1000310))).show()
  
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000245| PRADEEP|5000.00|      null|    100|
  |1000258|   BLAKE|2850.00|   1000245|    300|
  |1000288|   SCOTT|3000.00|   1000276|    200|
  |1000292|    FORD|3000.00|   1000276|    200|
  |1000294|   SMITH| 800.00|   1000292|    200|
  |1000299|   ALLEN|1600.00|   1000258|    300|
  |1000312|  MARTIN|1250.00|   1000258|    300|
  |1000315|  TURNER|1500.00|   1000258|    300|
  |1000326|   ADAMS|1100.00|   1000288|    200|
  |1000336|   JAMES| 950.00|   1000258|    300|
  |1000346|  MILLER|1300.00|   1000262|    100|
  |1000347|   DAVID|1400.00|   1000245|    500|
  +-------+--------+-------+----------+-------+
  

  
  
• Example 2:

  
  li=[1000346,1000288,1000294]
  empdf.filter(~(col("emp_no").isin(li))).show()
  
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000245| PRADEEP|5000.00|      null|    100|
  |1000258|   BLAKE|2850.00|   1000245|    300|
  |1000262|   CLARK|2450.00|   1000245|    100|
  |1000276|   JONES|2975.00|   1000245|    200|
  |1000292|    FORD|3000.00|   1000276|    200|
  |1000299|   ALLEN|1600.00|   1000258|    300|
  |1000310|    WARD|1250.00|   1000258|    300|
  |1000312|  MARTIN|1250.00|   1000258|    300|
  |1000315|  TURNER|1500.00|   1000258|    300|
  |1000326|   ADAMS|1100.00|   1000288|    200|
  |1000336|   JAMES| 950.00|   1000258|    300|
  |1000347|   DAVID|1400.00|   1000245|    500|
  +-------+--------+-------+----------+-------+
  

➠ Filter using List: List can be passed as parameter to isin attribute of col function.

from pyspark.sql.functions import col

li=[1000346,1000288,1000294]

empdf.filter((col("emp_no").isin(li))).show()

+-------+--------+-------+----------+-------+
| emp_no|emp_name| salary|manager_id|dept_no|
+-------+--------+-------+----------+-------+
|1000288|   SCOTT|3000.00|   1000276|    200|
|1000294|   SMITH| 800.00|   1000292|    200|
|1000346|  MILLER|1300.00|   1000262|    100|
+-------+--------+-------+----------+-------+

➠ Filter Null Values: Null values can only be queried using isNull attribute of col function. Rows were fetched where manager_id was null in the below example.

from pyspark.sql.functions import col

empdf.filter(col("manager_id").isNull()).show()

+-------+--------+-------+----------+-------+
| emp_no|emp_name| salary|manager_id|dept_no|
+-------+--------+-------+----------+-------+
|1000245| PRADEEP|5000.00|      null|    100|
+-------+--------+-------+----------+-------+

➠ Filter not Null Values: isNotNull attribute of col function can be used to filter out null values.

from pyspark.sql.functions import col

empdf.filter(col("manager_id").isNotNull()).show()

+-------+--------+-------+----------+-------+
| emp_no|emp_name| salary|manager_id|dept_no|
+-------+--------+-------+----------+-------+
|1000258|   BLAKE|2850.00|   1000245|    300|
|1000262|   CLARK|2450.00|   1000245|    100|
|1000276|   JONES|2975.00|   1000245|    200|
|1000288|   SCOTT|3000.00|   1000276|    200|
|1000292|    FORD|3000.00|   1000276|    200|
|1000294|   SMITH| 800.00|   1000292|    200|
|1000299|   ALLEN|1600.00|   1000258|    300|
|1000310|    WARD|1250.00|   1000258|    300|
|1000312|  MARTIN|1250.00|   1000258|    300|
|1000315|  TURNER|1500.00|   1000258|    300|
|1000326|   ADAMS|1100.00|   1000288|    200|
|1000336|   JAMES| 950.00|   1000258|    300|
|1000346|  MILLER|1300.00|   1000262|    100|
|1000347|   DAVID|1400.00|   1000245|    500|
+-------+--------+-------+----------+-------+

➠ Filter using LIKE operator: LIKE attribute of col function can be used to filter data based on partial string / pattern in the column. This can also be used to check if column value start with particular string/character or end with particular string/character.

• Example 1:

  
  
  from pyspark.sql.functions import col
  
  empdf.filter(col("emp_name").like("%ES%")).show()
  
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000276|   JONES|2975.00|   1000245|    200|
  |1000336|   JAMES| 950.00|   1000258|    300|
  +-------+--------+-------+----------+-------+
  

  
  
• Example 2: Employee name ends with S.

  
  empdf.filter(col("emp_name").like("%S")).show()
  
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000276|   JONES|2975.00|   1000245|    200|
  |1000326|   ADAMS|1100.00|   1000288|    200|
  |1000336|   JAMES| 950.00|   1000258|    300|
  +-------+--------+-------+----------+-------+
  

  
  
• Example 3: Employee name starts with S.

  
  empdf.filter(col("emp_name").like("S%")).show()
  
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000288|   SCOTT|3000.00|   1000276|    200|
  |1000294|   SMITH| 800.00|   1000292|    200|
  +-------+--------+-------+----------+-------+
  

  
  
• Example 4: Employee name that ends D and having A somewhere.

  
  empdf.filter(col("emp_name").like("%A%D")).show()
  
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000310|    WARD|1250.00|   1000258|    300|
  |1000347|   DAVID|1400.00|   1000245|    500|
  +-------+--------+-------+----------+-------+
  

➠ Filter using not LIKE operator: Tilde sign(~) can be used along with LIKE attribute of col function to negate string passed in LIKE clause. This can also be used to check if column value does not start with particular string/character or does not end with particular string/character.

• Example 1:

  
  from pyspark.sql.functions import col
  
  empdf.filter(~col("emp_name").like("%ES%")).show()
  
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000245| PRADEEP|5000.00|      null|    100|
  |1000258|   BLAKE|2850.00|   1000245|    300|
  |1000262|   CLARK|2450.00|   1000245|    100|
  |1000288|   SCOTT|3000.00|   1000276|    200|
  |1000292|    FORD|3000.00|   1000276|    200|
  |1000294|   SMITH| 800.00|   1000292|    200|
  |1000299|   ALLEN|1600.00|   1000258|    300|
  |1000310|    WARD|1250.00|   1000258|    300|
  |1000312|  MARTIN|1250.00|   1000258|    300|
  |1000315|  TURNER|1500.00|   1000258|    300|
  |1000326|   ADAMS|1100.00|   1000288|    200|
  |1000346|  MILLER|1300.00|   1000262|    100|
  |1000347|   DAVID|1400.00|   1000245|    500|
  +-------+--------+-------+----------+-------+
  

  
  
• Example 2: Employee name does not ends with S.

  
  empdf.filter(~col("emp_name").like("%S")).show()
  
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000245| PRADEEP|5000.00|      null|    100|
  |1000258|   BLAKE|2850.00|   1000245|    300|
  |1000262|   CLARK|2450.00|   1000245|    100|
  |1000288|   SCOTT|3000.00|   1000276|    200|
  |1000292|    FORD|3000.00|   1000276|    200|
  |1000294|   SMITH| 800.00|   1000292|    200|
  |1000299|   ALLEN|1600.00|   1000258|    300|
  |1000310|    WARD|1250.00|   1000258|    300|
  |1000312|  MARTIN|1250.00|   1000258|    300|
  |1000315|  TURNER|1500.00|   1000258|    300|
  |1000346|  MILLER|1300.00|   1000262|    100|
  |1000347|   DAVID|1400.00|   1000245|    500|
  +-------+--------+-------+----------+-------+
  

  
  
• Example 3: Employee name does not starts with S.

  
  empdf.filter(~col("emp_name").like("S%")).show()
  
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000245| PRADEEP|5000.00|      null|    100|
  |1000258|   BLAKE|2850.00|   1000245|    300|
  |1000262|   CLARK|2450.00|   1000245|    100|
  |1000276|   JONES|2975.00|   1000245|    200|
  |1000292|    FORD|3000.00|   1000276|    200|
  |1000299|   ALLEN|1600.00|   1000258|    300|
  |1000310|    WARD|1250.00|   1000258|    300|
  |1000312|  MARTIN|1250.00|   1000258|    300|
  |1000315|  TURNER|1500.00|   1000258|    300|
  |1000326|   ADAMS|1100.00|   1000288|    200|
  |1000336|   JAMES| 950.00|   1000258|    300|
  |1000346|  MILLER|1300.00|   1000262|    100|
  |1000347|   DAVID|1400.00|   1000245|    500|
  +-------+--------+-------+----------+-------+
  

➠ Filter using Contains: Contain attribute of col function looks for a string or a character anywhere in the column and return matched data. Contain can perform subset of operations that LIKE operator can perform.

from pyspark.sql.functions import col

empdf.filter(col("emp_name").contains("S")).show()

+-------+--------+-------+----------+-------+
| emp_no|emp_name| salary|manager_id|dept_no|
+-------+--------+-------+----------+-------+
|1000276|   JONES|2975.00|   1000245|    200|
|1000288|   SCOTT|3000.00|   1000276|    200|
|1000294|   SMITH| 800.00|   1000292|    200|
|1000326|   ADAMS|1100.00|   1000288|    200|
|1000336|   JAMES| 950.00|   1000258|    300|
+-------+--------+-------+----------+-------+

➠ Filter using Between: Between attribute of col function can be used to filter data from a column based on lower and upper range. In the below example, all rows will be returned where salary is between 2500 and 4500.

from pyspark.sql.functions import col

empdf.filter(col("salary").between(2500,4500)).show()

+-------+--------+-------+----------+-------+
| emp_no|emp_name| salary|manager_id|dept_no|
+-------+--------+-------+----------+-------+
|1000258|   BLAKE|2850.00|   1000245|    300|
|1000276|   JONES|2975.00|   1000245|    200|
|1000288|   SCOTT|3000.00|   1000276|    200|
|1000292|    FORD|3000.00|   1000276|    200|
+-------+--------+-------+----------+-------+

➠ Multicolumn filters: Multiple columns can be used to filter data in dataframe. Pipe(|) can be used between conditions to perform OR operation as in SQL joins and ampersand(&) can be used between conditions to perform AND operation as in SQL joins.

• Example 1: This will return rows where emp_name is either FORD or ALLEN.

  
  from pyspark.sql.functions import col
  
  empdf.filter((col("emp_name")=="FORD")|(col("emp_name")=="ALLEN")).show()
  
  +-------+--------+-------+-------+
  | emp_no|emp_name| salary|dept_no|
  +-------+--------+-------+-------+
  |1000292|    FORD|3000.00|    200|
  |1000299|   ALLEN|1600.00|    300|
  +-------+--------+-------+-------+
  

  
  
• Example 2: This will return rows where either emp_name is FORD or people having salary greater than 2000.

  
  empdf.filter((col("emp_name")=="FORD")|(col("salary")>2000)).show()
  
  +-------+--------+-------+-------+
  | emp_no|emp_name| salary|dept_no|
  +-------+--------+-------+-------+
  |1000245| PRADEEP|5000.00|    100|
  |1000258|   BLAKE|2850.00|    300|
  |1000262|   CLARK|2450.00|    100|
  |1000276|   JONES|2975.00|    200|
  |1000288|   SCOTT|3000.00|    200|
  |1000292|    FORD|3000.00|    200|
  +-------+--------+-------+-------+
  

  
  
• Example 3: This will return rows where people are part of dept_no 200 and have salary greater than 2000.

  
  empdf.filter((col("dept_no")==200)&(col("salary")>2000)).show()
  
  +-------+--------+-------+-------+
  | emp_no|emp_name| salary|dept_no|
  +-------+--------+-------+-------+
  |1000276|   JONES|2975.00|    200|
  |1000288|   SCOTT|3000.00|    200|
  |1000292|    FORD|3000.00|    200|
  +-------+--------+-------+-------+
  

  
  
• Example 4: This will return rows where people are part of dept_no 200 and have salary greater than 2000 or emp_name is FORD.

  
  empdf.filter((((col("dept_no")==200)&(col("salary")>2000))|(col("emp_name")=="JAMES"))).show()
  
  +-------+--------+-------+----------+-------+
  | emp_no|emp_name| salary|manager_id|dept_no|
  +-------+--------+-------+----------+-------+
  |1000276|   JONES|2975.00|   1000245|    200|
  |1000288|   SCOTT|3000.00|   1000276|    200|
  |1000292|    FORD|3000.00|   1000276|    200|
  |1000336|   JAMES| 950.00|   1000258|    300|
  +-------+--------+-------+----------+-------+