Dataframe column substring based on the value during join

Question

I have a dataframe with column having values like "COR//xxxxxx-xx-xxxx" or "xxxxxx-xx-xxxx"

I need to compare this column with another column in a different dataframe based on the column value.

If column value have "COR//xxxxx-xx-xxxx", I need to use substring("column", 4, length($"column")
If the column value have "xxxxx-xx-xxxx", I can compare directly without using substring.

For example:

val DF1 = DF2.join(DF3, upper(trim($"column1".substr(4, length($"column1")))) === upper(trim(DF3("column1"))))

I am not sure how to add the condition while joining. Could anyone please let me know how can we achieve this in Spark dataframe?

Apurba Pandey · Accepted Answer · 2018-11-14 08:22:06Z

1

You can try adding a new column based on the conditions and join on the new column. Something like this.

val data = List("COR//xxxxx-xx-xxxx", "xxxxx-xx-xxxx")
val DF2 = ps.sparkSession.sparkContext.parallelize(data).toDF("column1")
val DF4 = DF2.withColumn("joinCol", when(col("column1").like("%COR%"),
  expr("substring(column1, 6, length(column1)-1)")).otherwise(col("column1")) )

DF4.show(false)

The new column will have values like this.

+------------------+-------------+
|column1           |joinCol      |
+------------------+-------------+
|COR//xxxxx-xx-xxxx|xxxxx-xx-xxxx|
|xxxxx-xx-xxxx     |xxxxx-xx-xxxx|
+------------------+-------------+

You can now join based on the new column added.

val DF1 = DF4.join(DF3, upper(trim(DF4("joinCol"))) === upper(trim(DF3("column1"))))

Hope this helps.

edited Nov 14, 2018 at 8:22

answered Nov 14, 2018 at 5:58

Apurba Pandey

1,07611 silver badges23 bronze badges

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

Comments

Shaido · Accepted Answer · 2018-11-14 05:56:45Z

0

Simply create a new column to use in the join:

DF2.withColumn("column2", 
  when($"column1" rlike "COR//.*", 
    $"column1".substr(lit(4), length($"column1")).
  otherwise($"column1"))

Then use column2 in the join. It is also possible to add the whole when clause directly in the join but it would look very messy.

Note that to use a constant value in substr you need to use lit. And if you want to remove the whole "COR//" part, use 6 instead of 4.

answered Nov 14, 2018 at 5:56

Shaido

28.6k26 gold badges76 silver badges82 bronze badges

1 Comment

Bab Over a year ago

Its perfect. Thank you for your response.

Collectives™ on Stack Overflow

Dataframe column substring based on the value during join

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related