Is there a function to replace matched string with another column's value?

Question

Im trying to replace a matched string only - and nothing else within the column, with another value.

For example:

My name is GaryBrooks. 
The Partnertime series was good.

Match:

GaryBrooks
Partner time

Expected output:

My name is [TM="GaryBrooks"].
The [TM="Partner time"] series was good.

So far, ive done the following;

| trademarkname | tm_value | DESCRIPTION_TEXT |Compare|
------------------------------------------------------------
| GaryBrooks  | [TM="GaryBrooks"]| My name is GaryBrooks. |yes
| Partner time| [TM="Partner time"] |The Partnertime series was good.|yes

file['Compare'] = file.apply(lambda x: 'Yes' if x['trademarkname'] in x['DESCRIPTION_TEXT'] else 'No',axis=1)

I was successful until the match was found but not yet in replacing it. Im not sure if this is a regexp replace function or a for loop

Something like this is what I wanna do or think: WHEN "Compare" IS 'Yes' THEN regexp_replace("DESCRIPTION_TEXT", "trademarkname" (This is what has to be matched, "tm_value" (*this is what the string should be replaced with)

Partnertime and Partner time, is that intended? So the space could be ignored when you match? — Daeho Ro
– Daeho Ro, Commented Jul 8, 2023 at 5:35

notNull · Accepted Answer · 2023-07-07 16:06:58Z

1

Try with expr in withColumn and we are going to replace the matched value with tm_value data.

Example:

from pyspark.sql.functions import *
df = spark.createDataFrame([('GaryBrooks','[TM="GaryBrooks"]','My name is GaryBrooks.','yes'),('Partner time','[TM="Partner time"]','The Partnertime series was good.','yes')],['trademarkname','tm_value','DESCRIPTION_TEXT','Compare'])
df.withColumn("output", expr('regexp_replace(DESCRIPTION_TEXT,"(GaryBrooks|Partnertime)",tm_value)')).\
show(10,False)
#+-------------+-------------------+--------------------------------+-------+----------------------------------------+
#|trademarkname|tm_value           |DESCRIPTION_TEXT                |Compare|output                                  |
#+-------------+-------------------+--------------------------------+-------+----------------------------------------+
#|GaryBrooks   |[TM="GaryBrooks"]  |My name is GaryBrooks.          |yes    |My name is [TM="GaryBrooks"].           |
#|Partner time |[TM="Partner time"]|The Partnertime series was good.|yes    |The [TM="Partner time"] series was good.|
#+-------------+-------------------+--------------------------------+-------+----------------------------------------+

answered Jul 7, 2023 at 16:06

notNull

31.8k4 gold badges41 silver badges58 bronze badges

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

2 Comments

Sid Over a year ago

What if there are more than a few tm values and trademark names, how would I do this? a dictionary?

notNull Over a year ago

you need to create a regex matching string and use it in the regexp_replace function.

Collectives™ on Stack Overflow

Is there a function to replace matched string with another column's value?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related