Create a dataframe from a hashmap with keys as column names and values as rows in Spark

Question

I have a dataframe and I have a column which is a map in dataframe like this -

scala> df.printSchema

root
 |-- A1: map (nullable = true)
 |    |-- key: string
 |    |-- value: string (valueContainsNull = true)

I need to select all the keys from dataframe as column name and values as rows.

For eg: Let say I have 2 records like this-

1. key1 -> value1, key2 -> value2, key3 -> value3 ....
2. key1 -> value11, key3 -> value13, key4 -> value14 ...

I want the output dataframe as

key1             key2                 key3             key4
value1           value2               value3            null
value11          null                 value13           value14

How can I do this?

mtoto · Accepted Answer · 2017-03-30 12:03:41Z

7

First we need to create an id column by which we can group your data, then explode the map column A1, and finally reshape your df using pivot():

import org.apache.spark.sql.functions.{monotonically_increasing_id, explode, first}

df.withColumn("id", (monotonically_increasing_id()))
  .select($"id", explode($"A1"))
  .groupBy("id")
  .pivot("key")
  .agg(first("value")).show()
+---+-------+------+-------+-------+
| id|   key1|  key2|   key3|   key4|
+---+-------+------+-------+-------+
|  0| value1|value2| value3|   null|
|  1|value11|  null|value13|value14|
+---+-------+------+-------+-------+

answered Mar 30, 2017 at 12:03

mtoto

24.3k4 gold badges62 silver badges74 bronze badges

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

3 Comments

Neha Over a year ago

Hey, When i try to run this, i m getting below error- error: value pivot is not a member of org.apache.spark.sql.GroupedData Do you have any idea which import statement would work?

mtoto Over a year ago

what spark version do you use?

Neha Over a year ago

i was able to get this with spark version 2.1. thanks :)

Artur Rashitov · Accepted Answer · 2017-03-30 12:09:02Z

0

Assuming that column with the Map is named "my_map"

Get set of unique keys (skip this step if you already has the keys beforehand):

val keys = df
  .select(explode(expr("map_keys(my_map)")).as("keys_to_rows"))
  .agg(collect_set("keys_to_rows"))
  .collect()
  .head.getSeq[String](0)

Select map values by keys as a columns:

df.select(
  keys.map(key => col(s"B.$key").as(key)): _*
)

answered Mar 30, 2017 at 12:09

Artur Rashitov

4944 silver badges12 bronze badges

Collectives™ on Stack Overflow

Create a dataframe from a hashmap with keys as column names and values as rows in Spark

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related