Spark dataframe - replace all values from key/value list in Scala

Question

I've found some similar solutions, but none that accomplish exactly what I want to do. I have a set of key/value pairs that I want to use for string substitution. e.g.

val replacements = Map( "STREET" -> "ST", "STR" -> "ST")

I am reading a table into a dataframe, and I would like modify a column to replace all instances of the key in my map with their values. So in the above map, look at the "street" column and replace all values of "STREET" with "ST" and all values of "STR" with "ST" etc.

I've been looking at some foldLeft implementations, but haven't been able to finagle it into working.

A basic solution would be great, but an optimal solution would be something I could plug into a Column function that someone wrote that I was hoping to update. Specifically a line like this:

val CleanIt: Column = trim(regexp_replace(regexp_replace(regexp_replace(colName," OF "," ")," AT "," ")," AND "," "))

@thebluephantom It doesn't/didn't have to be foldLeft, that was just suggested in literally every answer I found that was trying to do something similar. — Fincher
– Fincher, Commented Sep 17, 2018 at 22:20

Tzach Zohar · Accepted Answer · 2018-09-17 18:48:14Z

2

You can create this helper method that transforms a given column and a map of replacements into a new Column expression:

def withReplacements(column: Column, replacements: Map[String, String]): Column = 
  replacements.foldLeft[Column](column) {
    case (col, (from, to)) => regexp_replace(col, from, to)
  }

Then use it on your street column with your replacements map:

val result = df.withColumn("street", withReplacements($"street", replacements))

For example:

df.show()
// +------------+------+
// |      street|number|
// +------------+------+
// | Main STREET|     1|
// |Broadway STR|     2|
// |     1st Ave|     3|
// +------------+------+

result.show()
// +-----------+------+
// |     street|number|
// +-----------+------+
// |    Main ST|     1|
// |Broadway ST|     2|
// |    1st Ave|     3|
// +-----------+------+

NOTE: the keys in the map must be valid regular expressions. That means, for example, that if you want to replace the string "St." with "ST", you should use Map("St\\." -> "ST) (escaping the dot, which otherwise would be interpreted as regex's "any")

answered Sep 17, 2018 at 18:48

Tzach Zohar

37.9k3 gold badges83 silver badges86 bronze badges

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

1 Comment

Fincher Over a year ago

Perfect! Works exactly as I needed. Thanks!

Collectives™ on Stack Overflow

Spark dataframe - replace all values from key/value list in Scala

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related