Built with Tutify.io

    AIStudyLabs

    PySpark Replace Empty Value With None/null on DataFrame

    PySpark Replace Empty Value With None/null on DataFrame

    In PySpark DataFrame use when().otherwise() SQL functions to find out if a column has an empty value and use withColumn() transformation to replace a value of an existing column. In this article, I will explain how to replace an empty value with None/null on a single column, all columns selected a list of columns of DataFrame with Python examples.

    Note: In PySpark DataFrame None value are shown as null value.

    Related: How to get Count of NULL, Empty String Values in PySpark DataFrame

    Let's create a PySpark DataFrame with empty values on some rows.

    
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName('AIStudyLabs.com').getOrCreate()
data = [("","CA"), ("Julia",""),("Robert",""),("","NJ")]
df =spark.createDataFrame(data,["name","state"])
df.show()

#+------+-----+
#|  name|state|
#+------+-----+
#|      |   CA|
#| Julia|     |
#|Robert|     |
#|      |   NJ|
#+------+-----+

    <a id="h-pyspark-replace-empty-value-with-none"></a>

    PySpark Replace Empty Value with None

    In order to replace empty value with None/null on single DataFrame column, you can use withColumn() and when().otherwise() function.

    
#Replace empty string with None value
from pyspark.sql.functions import col,when
df.withColumn("name", \
       when(col("name")=="" ,None) \
          .otherwise(col("name"))) \
  .show()

#+------+-----+
#|  name|state|
#+------+-----+
#|  null|   CA|
#| Julia|     |
#|Robert|     |
#|  null|   NJ|
#+------+-----+

    <a id="h-replace-empty-value-with-none-on-all-dataframe-columns"></a>

    Replace Empty Value with None on All DataFrame Columns

    To replace an empty value with None/null on all DataFrame columns, use df.columns to get all DataFrame columns, loop through this by applying conditions.

    
#Replace empty string with None for all columns
from pyspark.sql.functions import col,when
df2=df.select([when(col(c)=="",None).otherwise(col(c)).alias(c) for c in df.columns])
df2.show()

#+------+-----+
#|  name|state|
#+------+-----+
#|  null|   CA|
#| Julia| null|
#|Robert| null|
#|  null|   NJ|
#+------+-----+

    <a id="h-replace-empty-value-with-none-on-selected-columns"></a>

    Replace Empty Value with None on Selected Columns

    Similarly, you can also replace a selected list of columns, specify all columns you wanted to replace in a list and use this on same expression above.

    
#Replace empty string with None on selected columns
from pyspark.sql.functions import col,when
replaceCols=["name","state"]
df2=df.select([when(col(c)=="",None).otherwise(col(c)).alias(c) for c in replaceCols])
df2.show()

    <a id="h-complete-example"></a>

    Complete Example

    Following is a complete example of replace empty value with None.

    
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName('AIStudyLabs.com').getOrCreate()
data = [("","CA"), ("Julia",""),("Robert",""),("","NJ")]
df =spark.createDataFrame(data,["name","state"])
df.show()

#Replace empty string with None
from pyspark.sql.functions import col,when
df.withColumn("name", \
       when(col("name")=="" ,None) \
          .otherwise(col("name"))) \
  .show()
  
#Replace empty string with None for all columns
df2=df.select([when(col(c)=="",None).otherwise(col(c)).alias(c) for c in df.columns])
df2.show()

#Replace empty string with None on selected columns
replaceCols=["name","state"]
df2=df.select([when(col(c)=="",None).otherwise(col(c)).alias(c) for c in replaceCols])
df2.show()

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

    Conclusion

    In summary, you have learned how to replace empty string values with None/null on single, all, and selected PySpark DataFrame columns using Python example.

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

    Related Articles

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

    Reference