how to use Regexp_replace in spark

Question

I am pretty new to spark and would like to perform an operation on a column of a dataframe so as to replace all the , in the column with .

Assume there is a dataframe x and column x4

I want the output to be as

The code I am using is

import org.apache.spark.sql.Column
def replace = regexp_replace((x.x4,1,6566:String,1.6566:String)x.x4)

But I get the following error

import org.apache.spark.sql.Column
<console>:1: error: ')' expected but '.' found.
       def replace = regexp_replace((train_df.x37,0,160430299:String,0.160430299:String)train_df.x37)

Any help on the syntax, logic or any other suitable way would be much appreciated

mtoto · Accepted Answer · 2016-10-17 08:05:54Z

34

Here's a reproducible example, assuming x4 is a string column.

import org.apache.spark.sql.functions.regexp_replace

val df = spark.createDataFrame(Seq(
  (1, "1,3435"),
  (2, "1,6566"),
  (3, "-0,34435"))).toDF("Id", "x4")

The syntax is regexp_replace(str, pattern, replacement), which translates to:

df.withColumn("x4New", regexp_replace(df("x4"), "\\,", ".")).show
+---+--------+--------+
| Id|      x4|   x4New|
+---+--------+--------+
|  1|  1,3435|  1.3435|
|  2|  1,6566|  1.6566|
|  3|-0,34435|-0.34435|
+---+--------+--------+

edited Oct 17, 2016 at 8:05

answered Oct 17, 2016 at 7:46

mtoto

24.3k4 gold badges62 silver badges74 bronze badges

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

4 Comments

Abu Shoeb Over a year ago

can i use multiple characters in place of comma? for exmaple, i want to replace comma dot exclamation by any other char?

mtoto Over a year ago

you want to replace multiple special characters by one character? yes it is possible.

Abu Shoeb Over a year ago

I tried but didn't work. could you please tell me how to do that.

mtoto Over a year ago

you could try something like regexp_replace(df.col, "[\\?,\\.,\\$]", "."))

ForeverLearner · Accepted Answer · 2021-09-24 08:48:51Z

-2

We could use the map method to do this transformation:

scala> df.map(each => { 
(each.getInt(0),each.getString(1).replaceAll(",", "."))
})
.toDF("Id","x4")
.show

Output:

+---+--------+
| Id|      x4|
+---+--------+
|  1|  1.3435|
|  2|  1.6566|
|  3|-0.34435|
+---+--------+

edited Sep 24, 2021 at 8:48

answered Jun 6, 2020 at 15:16

ForeverLearner

2,1832 gold badges36 silver badges53 bronze badges

Collectives™ on Stack Overflow

how to use Regexp_replace in spark

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related