How can I write conditional regex replace in PySpark?

Question

I'm trying to get an if else statement working in the form of when() and otherwise() function. I tried thinking of many ways but my problem is the when() statement needs a column. Is there a way to get the following code working in when() and otherwise() format?

get_data = spark.sql("SELECT STRING({}) AS {} FROM {} WHERE Mobile='{}'".format(dynamic_tag_mapping_column_name, match[0], dynamic_tag_mapping_table_name, mobile_numbers[mob])).collect()[0][0]
if get_data!='0':
     textList.append(campaign_segment_text.withColumn('CampaignSMSText', func.expr("regexp_replace(CampaignSMSText, '{}', {})".format(match[0], get_data)))
else:
     textList.append(campaign_segment_text.withColumn('CampaignSMSText', func.expr("regexp_replace(CampaignSMSText, '{}', {})".format(match[0], dynamic_default_value)))

What I'm trying to achieve is if I get the get_data>0 value from database it will take the get_data value but if get_data=0, it will assign a default value that is set. I tried setting the String value to Int that I'm getting from the DB but that won't compare because get_data isn't a column. Is there anyway to get this working?

mck · Accepted Answer · 2020-12-12 15:44:34Z

1

Try putting case when inside F.expr?

get_data = spark.sql("SELECT {} AS {} FROM {} WHERE Mobile='{}'".format(dynamic_tag_mapping_column_name, match[0], dynamic_tag_mapping_table_name, mobile_numbers[mob])).collect()[0][0]

textList.append(
    campaign_segment_text.withColumn(
        'CampaignSMSText',
        func.expr(
            "regexp_replace(CampaignSMSText, '{}', case when {} != 0 then {} else {} end)".format(
                match[0], get_data, get_data, dynamic_default_value
            )
        )
    )
)

edited Dec 12, 2020 at 15:44

answered Dec 12, 2020 at 15:25

mck

42.7k13 gold badges44 silver badges62 bronze badges

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

7 Comments

mck Over a year ago

@VarunNagrare I found a missing close bracket in your code (and also my code), could you please try again?

Varun Nagrare Over a year ago

Okay I'll check it

Varun Nagrare Over a year ago

Yeah it worked! Thanks man. Your answer helped me a second time. :D

Varun Nagrare Over a year ago

I know UDF's are slow. I just wanted to practice since I don't know how UDF's work.

mck Over a year ago

yes you can use udf. the udf will take a string, pattern, and pattern to replace as input arguments, and return a replaced string

|

Collectives™ on Stack Overflow

How can I write conditional regex replace in PySpark?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related