
        
        
            
                
                
                    df = spark.createDataFrame([["Alex", 20], ["Bob", 30], ["Cathy", 40]], ["name", "age"])
df.show()
                
            
            +-----+---+
| name|age|
+-----+---+
| Alex| 20|
|  Bob| 30|
|Cathy| 40|
+-----+---+

Basic usage

To get rows where age is greater than 25:


        
        
            
                
                
                    df.where("age > 25").show()
                
            
            +-----+---+
| name|age|
+-----+---+
|  Bob| 30|
|Cathy| 40|
+-----+---+

Equivalently, we can pass a Column object that represents a boolean mask:


        
        
            
                
                
                    df.where(df.age > 25).show()
                
            
            +-----+---+
| name|age|
+-----+---+
|  Bob| 30|
|Cathy| 40|
+-----+---+

Equivalently, we can use the col(~) function of sql.functions to refer to the column:


        
        
            
                
                
                    from pyspark.sql import functions as F
df.where(F.col("age") > 25).show()
                
            
            +-----+---+
| name|age|
+-----+---+
|  Bob| 30|
|Cathy| 40|
+-----+---+

Compound queries

The where(~) method supports the AND and OR statement like so:


        
        
            
                
                
                    df.where("age > 25 AND name = 'Bob'").show()
                
            
            +----+---+
|name|age|
+----+---+
| Bob| 30|
+----+---+

Dealing with null values

Consider the following PySpark DataFrame:


        
        
            
                
                
                    df = spark.createDataFrame([["Alex", 20], [None, None], ["Cathy", None]], ["name", "age"])
df.show()
                
            
            +-----+----+
| name| age|
+-----+----+
| Alex|  20|
| null|null|
|Cathy|null|
+-----+----+

Let's query for rows where age!=10 like so:


        
        
            
                
                
                    df.where("age != 10").show()
                
            
            +----+---+
|name|age|
+----+---+
|Alex| 20|
+----+---+

Notice how only Alex's row is returned even though the other two rows technically have age!=10. This happens because PySpark's where(-) method filters our null values by default.

To prevent rows with null values getting filtered out, we can perform the query like so:


        
        
            
                
                
                    from pyspark.sql import functions as F
df.where((F.col("age") != 10) | (F.col("age").isNull())).show()
                
            
            +-----+----+
| name| age|
+-----+----+
| Alex|  20|
| null|null|
|Cathy|null|
+-----+----+

Note that PySpark's treatment of null values is different compared to Pandas because Pandas will retain rows with missing values, as demonstrated below:


        
        
            
                
                
                    import pandas as pd

df = pd.DataFrame({
    "col": ["a", "b", None]
})

df[df["col"] != "a"]
                
            
               col
1  b
2  None

Notice how the row with col=None is not left out!

Published by Isshin Inada

Edited by 0 others

Did you find this page useful?

thumb_up

thumb_down

Comment

Citation

Ask a question or leave a feedback...

Official PySpark Documentation

https://spark.apache.org/docs/latest/api/python/reference/api/pyspark.sql.DataFrame.where.html

thumb_up

thumb_down

chat_bubble_outline

settings

Enjoy our search

Hit / to insta-search docs and recipes!