How to transform rows with Spark to a row with a map Column

Question

I have a Dataframe like this:

KeyA	KeyB	KeyC	KeyD	Field	Value
1	2	3	4	A	W
1	2	3	4	A	X
1	2	3	4	B	Y
1	2	3	4	B	Z
1	2	3	5	A	B

How can i transform this Dataframe in a Dataframe like this so that the data is grouped by thefour key colmumns with a map of column Field as key and a List of the values.:

KeyA	KeyB	KeyC	KeyD	Map (Key:String, Value: List)
1	2	3	4	A:W,X
1	2	3	4	B:Y,Z
1	2	3	5	A:B

I would be very grateful for your help.

mck · Accepted Answer · 2020-12-10 07:03:08Z

2

Use collect_list and create_map:

df.show()
+----+----+----+----+-----+-----+
|KeyA|KeyB|KeyC|KeyD|Field|Value|
+----+----+----+----+-----+-----+
|   1|   2|   3|   4|    A|    W|
|   1|   2|   3|   4|    A|    X|
|   1|   2|   3|   4|    B|    Y|
|   1|   2|   3|   4|    B|    Z|
|   1|   2|   3|   5|    A|    B|
+----+----+----+----+-----+-----+

df2 = df.groupBy(
    'KEYA','KEYB','KEYC','KEYD','FIELD'
).agg(
    F.collect_list('VALUE').alias('VALUES')
).withColumn(
    'mapped',
    F.create_map('FIELD','VALUES')
).drop('VALUES')

df2.show()
+----+----+----+----+-----+-------------+
|KEYA|KEYB|KEYC|KEYD|FIELD|       mapped|
+----+----+----+----+-----+-------------+
|   1|   2|   3|   4|    A|[A -> [W, X]]|
|   1|   2|   3|   4|    B|[B -> [Y, Z]]|
|   1|   2|   3|   5|    A|   [A -> [B]]|
+----+----+----+----+-----+-------------+

answered Dec 10, 2020 at 7:03

mck

42.7k13 gold badges44 silver badges62 bronze badges

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

Comments

s.polam · Accepted Answer · 2020-12-10 07:06:33Z

1

Check below code.

scala> df
.select(
    struct(
        $"keya",
        $"keyb",
        $"keyc",
        $"keyd"
    ).as("keys"), // Grouping all keys into struct
    $"field",
    $"value"
)
.groupBy($"field",$"keys") // groupby keys & field
.agg(collect_list($"value").as("value")) // collecting list of values
.select(
    $"keys.*",
    map($"field",$"value").as("map_data") // constructing map.
)
.show(false)

+----+----+----+----+-------------+
|keya|keyb|keyc|keyd|map_data     |
+----+----+----+----+-------------+
|1   |2   |3   |4   |[B -> [Y, Z]]|
|1   |2   |3   |4   |[A -> [W, X]]|
|1   |2   |3   |5   |[A -> [B]]   |
+----+----+----+----+-------------+

answered Dec 10, 2020 at 7:06

s.polam

10.4k2 gold badges17 silver badges29 bronze badges

Collectives™ on Stack Overflow

How to transform rows with Spark to a row with a map Column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related