How to explode in spark with delimiter

Question

I have a table :

id itemNames coupons 1 item (foo bar) is available, soaps true 2 item (bar) is available false 3 soaps, shampoo false 4 item (foo bar, bar) is available true 5 item (foo bar, bar) is available, (soap, shampoo) true 6 null false

I want to explode this to

id itemNames coupons 1 item (foo bar) is available true
1 soaps true 2 item (bar) is available false 3 soaps false 3 shame false 4 item (foo bar, bar) is available true 5 item (foo bar, bar) is available true 6 (soap, shampoo) true
6 null true

when I do :

 df.withColumn("itemNames", explode(split($"itemNames", "[,]")))

I am getting :

itemNames                                          coupons
item (foo bar) is available                        true       
soaps                                              true 
item (bar) is available                            false
soaps                                              false
shampoo                                            false
item (foo bar,                                     true
bar) is available                                  true 
(soap,                                             true    
shampoo)                                           true

Can someone tell me what I am doing wrong and how can I correct this ? The one pattern common here is the comma appears inside ().

stack0114106 · Answer 1 · 2018-10-05T06:18:05.573

1

your problem doesn't have a pattern to split the string from backwards. The below is a workaround and works for this particular case. I'm dividing by "available," using the lookbehind operation. Try this out in your dataframe explode

scala> "item (foo bar) is available, soaps".split("(?<=available),")
res41: Array[String] = Array(item (foo bar) is available, " soaps")

scala> "item (foo bar) is available, soaps".split("(?<=available),").length
res42: Int = 2

scala> "item (foo bar, bar) is available".split("(?<=available),")
res44: Array[String] = Array(item (foo bar, bar) is available)

scala> "item (foo bar, bar) is available".split("(?<=available),").length
res45: Int = 1

EDIT1

scala> "item (foo bar, bar) is empty, (soap, shampoo)".split("(?<=available|empty),").length
res1: Int = 2

scala>

edited Oct 05 '18 at 06:18

answered Oct 05 '18 at 05:42

stack0114106

8,534
3
13
38

Yeah.. This is will only when the values have available at the end. But I am looking for a more generic approach because my data also have other values – user3407267 Oct 05 '18 at 06:01
if you know the distinct other values you can alternate them.. see my EDIT1, otherwise you need to write a udf(), do the regex and return an array, then explode. – stack0114106 Oct 05 '18 at 06:17

score 1 · Accepted Answer · answered Oct 05 '18 at 07:39

1

With UDF and inspired by Regex to match only commas not in parentheses? :

val df = List(
  ("item (foo bar) is available, soaps", true),
  ("item (bar) is available", false),
  ("soaps, shampoo", false),
  ("item (foo bar, bar) is available", true),
  ("item (foo bar, bar) is available, (soap, shampoo)", true)
).
  toDF("itemNames", "coupons")
df.show(false)

val regex = Pattern.compile(
  ",         # Match a comma\n" +
    "(?!       # only if it's not followed by...\n" +
    " [^(]*    #   any number of characters except opening parens\n" +
    " \\)      #   followed by a closing parens\n" +
    ")         # End of lookahead",
  Pattern.COMMENTS)

val customSplit = (value: String) => regex.split(value)
val customSplitUDF = udf(customSplit)
val result = df.withColumn("itemNames", explode(customSplitUDF($"itemNames")))
result.show(false)

Output is:

+--------------------------------+-------+
|itemNames                       |coupons|
+--------------------------------+-------+
|item (foo bar) is available     |true   |
| soaps                          |true   |
|item (bar) is available         |false  |
|soaps                           |false  |
| shampoo                        |false  |
|item (foo bar, bar) is available|true   |
|item (foo bar, bar) is available|true   |
| (soap, shampoo)                |true   |
+--------------------------------+-------+

If "trim" is required, can be added to "customSplit" easly.

answered Oct 05 '18 at 07:39

pasha701

6,831
1
15
22

Im getting - Failed to execute user defined function($anonfun$1: (string) => array). – user3407267 Oct 05 '18 at 07:54
Can you please provide more trace, what is the reason for failure? – pasha701 Oct 05 '18 at 07:57
Updated the query with stack trace. Can you please let me know if I am doing anything wrong ? – user3407267 Oct 05 '18 at 08:03
Nvm, I think its NPE. – user3407267 Oct 05 '18 at 08:08
more trace is required, in provided specified only that function failed. Reason (NPE, etc) can be deeper in trace. If its NPE, guess, UDF can be improved. – pasha701 Oct 05 '18 at 08:09
I think it is because some rows have empty values(null). Do I need to update the udf to check for empty rows ? – user3407267 Oct 05 '18 at 08:12
yes, empty rows and null values can be checked in UDF. – pasha701 Oct 05 '18 at 08:18

How to explode in spark with delimiter

2 Answers2