How do I conditionally remove text from a string in a column in a Scala dataframe?

user_7401258 0
Viewed 397

I'm currently exploring Azure Databricks for a POC (Scala and Databricks are both completely new to me. I'm using this (Cars - Corgis) sample dataset to show off the manipulation characteristics of Databricks.

My problem is that I have a dataframe column called 'model' that contains data like '2009 Audi A3' and '2005 Mercedes E550'. What I would like to be able to do is alter that column so instead of the aforementioned, it reads as 'Audi A3' or 'Mercedes E550'. I have a separate model year column so trying to reduce the size of the columns where possible.

From what I have seen, replaceAllIn doesn't seem to work with strings with Scala.

This is my code so far:

//Use the dataframe from the previous cell and trim the model year from the model column so for example it reads as 'Audi A3' instead of '2009 Audi A3'
import scala.util.matching.Regex

val modelPrefixPatternMatch = "[0-9 ]".r
val newModel = modelPrefixPatternMatch.replaceAllIn((specificColumnsDf.select("model")),"")

However, when I run this code, I get the following error message:

command-1778339999318469:5: error: overloaded method value replaceAllIn with alternatives:
  (target: CharSequence,replacer: scala.util.matching.Regex.Match => String)String <and>
  (target: CharSequence,replacement: String)String
 cannot be applied to (org.apache.spark.sql.DataFrame, String)
val newModel = modelPrefixPatternMatch.replaceAllIn((specificColumnsDf.select("model")),"")

I have also tried completing the SparkSQL but didn't have any luck there either.

Thanks!

azure-databricks databricks scala regex
1 Answers

In Spark you would normally add additional columns using withColumn and then select only the columns you want. In this simple example, I use regexp_replace function to trim out the years, something like this:

%scala
import org.apache.spark.sql.functions._
import org.apache.spark.sql.Column

df
  .withColumn("cleanColumn", regexp_replace($"`Identification.Model Year`", "20[0-2][0-9] ","") )
  .select($"`Identification.Model Year`", $"cleanColumn").distinct
  .show(false)

My results:

My results

We could probably make the regular expression tighter, eg tie it to the start of the column or open it up for years 1980, 1990 etc - this is just an example.

If the year is always at the start then you could just use substring and start at position 5. The regex approach at least protects from the year not being there for some records.

HTH

user_15275040
Related