Ishan - 9 months ago 41

Scala Question

I have imported data in spark dataframe in spark-shell. Data is filled in it like :

`Col1 | Col2 | Col3 | Col4`

A1 | 11 | B2 | a|b;1;0xFFFFFF

A1 | 12 | B1 | 2

A2 | 12 | B2 | 0xFFF45B

Here in Col4, the values are of different kinds and I want to separate them like (suppose "a|b" is type of alphabets, "1 or 2" is a type of digit and "0xFFFFFF or 0xFFF45B" is a type of hexadecimal no.):

So, the output should be :

`Col1 | Col2 | Col3 | alphabets | digits | hexadecimal`

A1 | 11 | B2 | a | 1 | 0xFFFFFF

A1 | 11 | B2 | b | 1 | 0xFFFFFF

A1 | 12 | B1 | | 2 |

A2 | 12 | B2 | | | 0xFFF45B

Hope I've made my query clear to you and I am using spark-shell. Thanks in advance.

Answer Source

Edit after getting this answer about how to make backreference in `regexp_replace`

.

You can use `regexp_replace`

with a backreference, then `split`

twice and `explode`

. It is, imo, cleaner than my original solution

```
val df = List(
("A1" , "11" , "B2" , "a|b;1;0xFFFFFF"),
("A1" , "12" , "B1" , "2"),
("A2" , "12" , "B2" , "0xFFF45B")
).toDF("Col1" , "Col2" , "Col3" , "Col4")
val regExStr = "^([A-z|]+)?;?(\\d+)?;?(0x.*)?$"
val res = df
.withColumn("backrefReplace",
split(regexp_replace('Col4,regExStr,"$1;$2;$3"),";"))
.select('Col1,'Col2,'Col3,
explode(split('backrefReplace(0),"\\|")).as("letter"),
'backrefReplace(1) .as("digits"),
'backrefReplace(2) .as("hexadecimal")
)
+----+----+----+------+------+-----------+
|Col1|Col2|Col3|letter|digits|hexadecimal|
+----+----+----+------+------+-----------+
| A1| 11| B2| a| 1| 0xFFFFFF|
| A1| 11| B2| b| 1| 0xFFFFFF|
| A1| 12| B1| | 2| |
| A2| 12| B2| | | 0xFFF45B|
+----+----+----+------+------+-----------+
```

you still need to replace empty strings by `null`

though...

Here is a solution that sticks to DataFrames but is also quite messy. You can first use `regexp_extract`

three times (possible to do less with backreference?), and finally `split`

on "|" and `explode`

. Note that you need a coalesce for `explode`

to return everything (you still might want to change the empty strings in `letter`

to `null`

in this solution).

```
val res = df
.withColumn("alphabets", regexp_extract('Col4,"(^[A-z|]+)?",1))
.withColumn("digits", regexp_extract('Col4,"^([A-z|]+)?;?(\\d+)?;?(0x.*)?$",2))
.withColumn("hexadecimal",regexp_extract('Col4,"^([A-z|]+)?;?(\\d+)?;?(0x.*)?$",3))
.withColumn("letter",
explode(
split(
coalesce('alphabets,lit("")),
"\\|"
)
)
)
res.show
+----+----+----+--------------+---------+------+-----------+------+
|Col1|Col2|Col3| Col4|alphabets|digits|hexadecimal|letter|
+----+----+----+--------------+---------+------+-----------+------+
| A1| 11| B2|a|b;1;0xFFFFFF| a|b| 1| 0xFFFFFF| a|
| A1| 11| B2|a|b;1;0xFFFFFF| a|b| 1| 0xFFFFFF| b|
| A1| 12| B1| 2| null| 2| null| |
| A2| 12| B2| 0xFFF45B| null| null| 0xFFF45B| |
+----+----+----+--------------+---------+------+-----------+------+
```

Note: The regexp part could be so much better with backreference, so if somebody knows how to do it, please comment!