Built with Tutify.io

    AIStudyLabs

    PySpark NOT isin() or IS NOT IN Operator

    PySpark NOT isin() or IS NOT IN Operator

    The NOT isin() operation in PySpark is used to filter rows in a DataFrame where the column's value is not present in a specified list of values. This is accomplished by using the isin() function combined with the negation operator ~.

    The resulting boolean column indicates True for rows where the value is absent from the list, effectively excluding those values from the DataFrame. isin() is a function of Column class which returns a boolean value True if the value of the expression is satisfied

    The NOT IN condition (sometimes called the NOT Operator) is used to negate a condition of isin() result.

    <a id="h-1-not-in-examples"></a>

    1. NOT IN Examples

    Quick examples of using NOT IN operator to filter rows from DataFrame.

    
# PySpark not isin()
listValues = ["Java","Scala"]
df.filter(~df.languages.isin(listValues)).show()

from pyspark.sql.functions import col
df.filter(~col("languages").isin(listValues)).show()

# Using NOT IN operator
df.filter("languages in ('Java','Scala')" ).show()

# PySpark SQL NOT IN
df.createOrReplaceTempView("TAB")
spark.sql("SELECT * FROM TAB WHERE " +
    "language NOT IN ('Java','Scala')").show()

    <a id="h-2-syntax-of-not-in"></a>

    2. Syntax of NOT IN

    Usage of NOT IN

    
# Syntax of NOT isin()
~ Column.isin(*cols)

    Here, *cols is python syntax for expanding an array to dump its elements into the function parameters one at a time in order. Let's create a DataFrame and run the above examples.

    
#Import
from pyspark.sql import SparkSession

# Create SparkSession
spark = SparkSession.builder.master("local[1]") 
    .appName("AIStudyLabs.com").getOrCreate()

# Create DataFrame    
data = [("James","Java"),("Michael","Spark"),
        ("Robert","Python")]
columns = ["name","languages"]
df = spark.createDataFrame(data,columns)
df.show()

    <a id="h-3-pyspark-not-in-example"></a>

    3. PySpark NOT IN Example

    PySpark DataFrame API doesn’t have a function notin() to check value does not exist in a list of values however, you can use NOT operator(~) in conjunction with isin() function to negate the result. Let's see with an example, below example filter the rows languages column value not present in 'Java' & 'Scala'.

    
# PySpark NOT isin()
listValues = ["Java","Scala"]
df.filter(~df.languages.isin(listValues)).show()

from pyspark.sql.functions import col
df.filter(~col("languages").isin(listValues)).show()

# Using == operator
df.filter(dataframe.column.isin(listValues) == False)

    Yields below output.

    <a id="h-4-using-not-in-operator-with-expression"></a>

    4. Using NOT IN Operator with Expression

    Let's see how to use NOT IN operator in the PySpark expression condition.

    
# Using NOT IN operator
df.filter("languages NOT IN ('Java','Scala')" ).show()

    <a id="h-5-pyspark-sql-not-in-operator"></a>

    5. PySpark SQL NOT IN Operator

    In PySpark SQL, you can use NOT IN operator to check values not exists in a list of values, it is usually used with the WHERE clause. In order to use SQL, make sure you define a temporary view or table using createOrReplaceTempView().

    
# PySpark SQL NOT IN
df.createOrReplaceTempView("TAB")
spark.sql("SELECT * FROM TAB WHERE " +
    "languages NOT IN ('Java','Scala')").show()

    These return the same output as above.

    <a id="h-6-conclusion"></a>

    6. Conclusion

    In PySpark, to filter rows where a column's value is not in a specified list of values, you can use the negation of the isin() function. This can be achieved by combining isin() with the ~ operator. This operation returns a boolean column that is True for rows where the column's value does not match any value in the list.

    Happy Learning !!

    <a id="h-related-articles"></a>

    Related Articles

    <a id="h-reference"></a>

    Reference