How to replace a string in Pyspark dataframe column from another column in Dataframe

Question

Experts, this is minor, but I am not able to just get it right.

+--------------+----------------------------------------------------------+-------------------+
|table         |query                                                     |date               |
+--------------+----------------------------------------------------------+-------------------+
|AGENT         |select * from table where DW_EFFECTIVE_DATE_PARTITION ='X'|2019-12-24 00:00:00|
+--------------+----------------------------------------------------------+-------------------+

All I want in this dataframe is to change column query to:

select * from table where DW_EFFECTIVE_DATE_PARTITION ='2019-12-24 00:00:00'

I tried:

>>> dfX.withColumn('query',regexp_replace('query',"'X'","'" + dfX['d'] + "'")).show()
Traceback (most recent call last):
TypeError: 'Column' object is not callable

Desired Output:

+--------------+----------------------------------------------------------------------------+-------------------+
|table         |query                                                                       |date             |
+--------------+----------------------------------------------------------------------------+-------------------+
|AGENT         |select * from table where DW_EFFECTIVE_DATE_PARTITION ='2019-12-24 00:00:00'|2019-12-24 00:00:00|
+--------------+----------------------------------------------------------------------------+-------------------+

JMLizano · Accepted Answer · 2020-01-09 21:59:01Z

2

You can use selectExpr instead of withColumn:

>>> df.selectExpr("table","regexp_replace(query, 'X', date) as query", "date").show(truncate=False)
+-----+----------------------------------------------------------------------------+-------------------+
|table|query                                                                       |date               |
+-----+----------------------------------------------------------------------------+-------------------+
|AGENT|select * from table where DW_EFFECTIVE_DATE_PARTITION ='2019-12-24 00:00:00'|2019-12-24 00:00:00|
+-----+----------------------------------------------------------------------------+-------------------+

answered Jan 9, 2020 at 21:59

JMLizano

813 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

blackbishop · Accepted Answer · 2020-01-09 21:33:57Z

Use regexp_replace with expr so shat you could replace a string by another column value:

replace_expr = """regexp_replace(query,"'X'",concat("'", date, "'"))"""
df.withColumn("query", expr(replace_expr)).show(truncate=False)

Gives:

+-----+----------------------------------------------------------------------------+-------------------+
|table|query                                                                       |date               |
+-----+----------------------------------------------------------------------------+-------------------+
|AGENT|select * from table where DW_EFFECTIVE_DATE_PARTITION ='2019-12-24 00:00:00'|2019-12-24 00:00:00|
+-----+----------------------------------------------------------------------------+-------------------+

gxlzlihao · Accepted Answer · 2020-11-11 07:07:12Z

0

def replace_string(s):
  if s == "A":
    return "a"
  else:
    return "b"
replace_string_udf = spark.udf.register("replace_string", replace_string, StringType())
df = df.withColumn("new_column", replace_string_udf("old_column_name"))

answered Nov 11, 2020 at 7:07

gxlzlihao

314 bronze badges

Collectives™ on Stack Overflow

How to replace a string in Pyspark dataframe column from another column in Dataframe

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related