Spark dataframe create explode with order

Question

I have a data like below

Input Df

+----------+-----------------------------------+--------------|
|SALES_NO   |SALE_LINE_NUM   | CODE_1   | CODE_3   | CODE_2   |
+----------+----------------------------|------+---|----------|
|123       |1                | ABC      | E456     |  GHF989  |
|123       |2                | EDF      | EFHJ     |  WAEWA   |
|234       |1                | 2345     | 985E     |  AWW     |
|234       |2                | WERWE    |          |          |
|234       |3                | ERC      | AERER    |          |
|456       |1                | WER      | AWER     |          |
+----------+-----------------------------------+--------------|

Output will be created like : for each unique sales_no, sales_line_num create a each new row for different code column if code is not null and order for the same.

For code_1, order will be 1.

For code_2, order will be 2.

Output df

SALES_NO  SALES_LINE_NUM   CODE    ORDER
123          1              ABC      1
123          1              E456     2
123          1              GHF989   3
123          2              EDF      1
123          2              EFHJ     2
123          2              WAEWA    3
234          1              2345     1
234          1              985E     2
234          1              AWW      3
234          2              WERWE    1
234          3              ERC      1
234          3              AERER    2
456          1              WER      1
456          1              AWER     2

Can anyone please help? Thanks in advance

vilalabinot · Answer

For this dataset:

var ds = spark.sparkContext.parallelize(Seq(
  (123, 1, "ABC", "E456", "GHF989"),
  (123, 2, "EDF", "EFHJ", "WAEWA"),
  (234, 1, "2345", "985E", "AWW"),
  (234, 2, "WERWE", "", ""),
  (234, 3, "ERC", "AERER", ""),
  (456, 1, "WER", "AWER", ""),
)).toDF("SALES_NO", "SALE_LINE_NUM", "CODE_1", "CODE_3", "CODE_2")

We need to unpivot through stack as below:

ds = ds.selectExpr(
  "SALES_NO",
  "SALE_LINE_NUM",
  "stack(3, CODE_1, '1', CODE_2, '2', CODE_3, '3') as (CODE, ORDER)"
)

Which should give what you want:

+--------+-------------+------+-----+
|SALES_NO|SALE_LINE_NUM|CODE  |ORDER|
+--------+-------------+------+-----+
|123     |1            |ABC   |1    |
|123     |1            |GHF989|2    |
|123     |1            |E456  |3    |
|123     |2            |EDF   |1    |
|123     |2            |WAEWA |2    |
|123     |2            |EFHJ  |3    |
|234     |1            |2345  |1    |
|234     |1            |AWW   |2    |
|234     |1            |985E  |3    |
|234     |2            |WERWE |1    |
+--------+-------------+------+-----+

More about unpivoting can be found here.

Good luck!

Spark dataframe create explode with order

1 Answers