Let’s discuss it one by one. Before that, we have to create a PySpark DataFrame for demonstration.
Example
We are going to create a dataframe with 5 rows and 6 columns and display it using the show() method.
import pyspark
#import SparkSession for creating a session
from pyspark.sql import SparkSession
#create an app named linuxhint
spark_app = SparkSession.builder.appName('linuxhint').getOrCreate()
# create student data with 5 rows and 6 attributes
students =[
{'rollno':'001','name':'sravan','age':23,'
height':5.79,'weight':67,'address':'guntur'},
{'rollno':'002','name':'ojaswi','age':16,
'height':3.79,'weight':34,'address':'hyd'},
{'rollno':'003','name':'gnanesh chowdary','age':7,
'height':2.79,'weight':17,'address':'patna'},
{'rollno':'004','name':'rohith','age':9,
'height':3.69,'weight':28,'address':'hyd'},
{'rollno':'005','name':'sridevi','age':37,
'height':5.59,'weight':54,'address':'hyd'}]
# create the dataframe
df = spark_app.createDataFrame( students)
#display dataframe
df.show()
Output:
PySpark translate()
translate() is used to replace strings in PySpark DataFrame column character by character. We have to specify the characters in a string to be replaced with some other characters. It takes three parameters.
Syntax:
Where,
- column is the name of the column in which the characters are replaced in this column.
- actual_characters are the characters present in the strings of the given column.
- replacing_characters are the characters that replace the actual_characters one by one.
Note – The number of characters in the actual_characters must be equal to the number of replacing_characters.
translate() can be used with the withColumn() method.
Overall Syntax:
Example 1
In this example, we are translating the characters – gunhy to @$%^& in the address column.
import pyspark
#import SparkSession for creating a session
from pyspark.sql import SparkSession
#import translate from pyspark.sql.functions
from pyspark.sql.functions import translate
#create an app named linuxhint
spark_app = SparkSession.builder.appName('linuxhint').getOrCreate()
# create student data with 5 rows and 6 attributes
students =[
{'rollno':'001','name':'sravan','age':23,'
height':5.79,'weight':67,'address':'guntur'},
{'rollno':'002','name':'ojaswi','age':16,
'height':3.79,'weight':34,'address':'hyd'},
{'rollno':'003','name':'gnanesh chowdary','age':7,
'height':2.79,'weight':17,'address':'patna'},
{'rollno':'004','name':'rohith','age':9,
'height':3.69,'weight':28,'address':'hyd'},
{'rollno':'005','name':'sridevi','age':37,
'height':5.59,'weight':54,'address':'hyd'}]
# create the dataframe
df = spark_app.createDataFrame( students)
#translate the characters - gunhy to @$%^&
df.withColumn('address', translate('address', 'gunhy', '@$%^&')).show()
Output:
We can see that in the address column – the strings that contain
- g is translated to @
- u is translated to $
- n is translated to %
- h is translated to ^
- y is translated to &
Example 2
In this example, we are translating the characters – jaswi to 56434 in the name column.
import pyspark
#import SparkSession for creating a session
from pyspark.sql import SparkSession
#import translate from pyspark.sql.functions
from pyspark.sql.functions import translate
#create an app named linuxhint
spark_app = SparkSession.builder.appName('linuxhint').getOrCreate()
# create student data with 5 rows and 6 attributes
students =[
{'rollno':'001','name':'sravan','age':23,'
height':5.79,'weight':67,'address':'guntur'},
{'rollno':'002','name':'ojaswi','age':16,
'height':3.79,'weight':34,'address':'hyd'},
{'rollno':'003','name':'gnanesh chowdary','age':7,
'height':2.79,'weight':17,'address':'patna'},
{'rollno':'004','name':'rohith','age':9,
'height':3.69,'weight':28,'address':'hyd'},
{'rollno':'005','name':'sridevi','age':37,
'height':5.59,'weight':54,'address':'hyd'}]
# create the dataframe
df = spark_app.createDataFrame( students)
#translate the characters - jaswi to 56434 in name column
df.withColumn('name', translate('name', 'jaswi', '56434')).show()
Output:
We can see that in the name column – the strings that contain
- j is translated to 5
- a is translated to 6
- s is translated to 4
- w is translated to 3
- i is translated to 4
PySpark overlay()
overlay() is used to replace values in a given column with another column values. It takes three parameters. It can be used with a select clause.
Syntax:
Where,
- replaced_column is the column in which values are replaced.
- replacing_column is the column in which it replaced the values in a replaced_ column.
- position is used to specify the position or location in replaced_column such that the values in replacing_column occupy replaced_column.
Note – If all the characters in values are replaced in replaced_column, from the next position, replaced_column characters will appear.
Overall Syntax:
Example
In this example, we will replace values in
- name column with age from 4 th character in name column
- rollno column with name from 2nd character
import pyspark
#import SparkSession for creating a session
from pyspark.sql import SparkSession
#import overlay from pyspark.sql.functions
from pyspark.sql.functions import overlay
#create an app named linuxhint
spark_app = SparkSession.builder.appName('linuxhint').getOrCreate()
# create student data with 5 rows and 6 attributes
students =[
{'rollno':'001','name':'sravan','age':23,'
height':5.79,'weight':67,'address':'guntur'},
{'rollno':'002','name':'ojaswi','age':16,
'height':3.79,'weight':34,'address':'hyd'},
{'rollno':'003','name':'gnanesh chowdary','age':7,
'height':2.79,'weight':17,'address':'patna'},
{'rollno':'004','name':'rohith','age':9,
'height':3.69,'weight':28,'address':'hyd'},
{'rollno':'005','name':'sridevi','age':37,
'height':5.59,'weight':54,'address':'hyd'}]
# create the dataframe
df = spark_app.createDataFrame( students)
#replace values in name column with age from 4 th character
df.select(overlay("name", "age", 4)).show()
#replace values in rollno column with name from 2nd character
df.select(overlay("rollno", "name", 2)).show()
Output:
From this output,
- characters in the age column are replaced in the name column from the 4th position of every value, and the rest of the characters have remained the same in the name column.
- characters in the name column are replaced in the rollno column from the 4th position of every value, and the rest of the characters have not resulted in the rollno column since the total number of characters in rollno column values is lesser than name column values. That’s why name column values are occupied.
Conclusion
From this tutorial, we saw how to replace strings in the dataframe columns translate() and overlay() functions with simple examples.translate() is used to replace strings in PySpark DataFrame column character by character. We have to specify the characters in a string to be replaced with some other characters. overlay() is used to replace values in a given column with another column values.
