Spark : How do I exploded data and add column name also in pyspark or scala spark?

Question

Spark: I want explode multiple columns and consolidate as single column with column name as separate row.

Input data: 
    +-----------+-----------+-----------+
    |   ASMT_ID |   WORKER  |   LABOR   |
    +-----------+-----------+-----------+
    |   1       |   A1,A2,A3|   B1,B2   |
    +-----------+-----------+-----------+
    |   2       |   A1,A4   |   B1      |
    +-----------+-----------+-----------+

Expected Output:


+-----------+-----------+-----------+
|   ASMT_ID |WRK_CODE   |WRK_DETL   |
+-----------+-----------+-----------+
|   1       |   A1      |   WORKER  |
+-----------+-----------+-----------+
|   1       |   A2      |   WORKER  |
+-----------+-----------+-----------+
|   1       |   A3      |   WORKER  |
+-----------+-----------+-----------+
|   1       |   B1      |   LABOR   |
+-----------+-----------+-----------+
|   1       |   B2      |   LABOR   |
+-----------+-----------+-----------+
|   2       |   A1      |   WORKER  |
+-----------+-----------+-----------+
|   2       |   A4      |   WORKER  |
+-----------+-----------+-----------+
|   2       |   B1      |   LABOR   |
+-----------+-----------+-----------+

PFA: Input image

Post the code you tried, At least like loading the data into spark. — loneStar, Feb 12 '18 at 14:38

score 2 · Accepted Answer · answered Feb 12 '18 at 14:55

Not the best case probably but a couple of explodes and unionAll is all you need.

import org.apache.spark.sql.functions._

df1.show
+-------+--------+-----+
|ASMT_ID|  WORKER|LABOR|
+-------+--------+-----+
|      1|A1,A2,A3|B1,B2|
|      2|   A1,A4|   B1|
+-------+--------+-----+

df1.cache

val workers = df1.drop("LABOR")
                 .withColumn("WRK_CODE" , explode(split($"WORKER" , ",") ) )
                 .withColumn("WRK_DETL", lit("WORKER"))
                 .drop("WORKER")

val labors = df1.drop("WORKER")
                .withColumn("WRK_CODE" , explode(split($"LABOR", ",") ) )
                .withColumn("WRK_DETL", lit("LABOR") )
                .drop("LABOR")

workers.unionAll(labors).orderBy($"ASMT_ID".asc , $"WRK_CODE".asc).show

+-------+--------+--------+
|ASMT_ID|WRK_CODE|WRK_DETL|
+-------+--------+--------+
|      1|      A1|  WORKER|
|      1|      A2|  WORKER|
|      1|      A3|  WORKER|
|      1|      B1|   LABOR|
|      1|      B2|   LABOR|
|      2|      A1|  WORKER|
|      2|      A4|  WORKER|
|      2|      B1|   LABOR|
+-------+--------+--------+

Spark : How do I exploded data and add column name also in pyspark or scala spark?

1 Answers1