Spark DataFrame aggregate multiple column into one column as a string

Question

I want to convert a Spark DataFrame into another DataFrame with a specific manner as follows:

I have Spark DataFrame:

+---------+------------+
|protocol |   count    |
+---------+------------+
|      TCP|    8231    |
|     ICMP|    7314    |
|      UDP|    5523    |
|     IGMP|    4423    |
|      EGP|    2331    |
+---------+------------+

And I want to turn it into:

+----------------------------------------------------------+
|Aggregated                                                |
+----------------------------------------------------------+
|{tcp: 8231, icmp: 7314, udp: 5523, igmp: 4423, egp: 2331} |
+----------------------------------------------------------+

The aggregated column can be both list or map, or string. Is this possible with DataFrame functions or do I need to create my own udf to aggregate this ?

Do you want to use all the rows in the dataframe when doing this? Since there are not that many it would probably be easier to collect the data and use pure Scala to convert it. — Shaido
– Shaido, Commented May 17, 2018 at 8:40
@Shaido Yes, all of the rows, and yet the example that given is not the only rows that exist. The rows will keep increasing by the time — Dimas Rizky
– Dimas Rizky, Commented May 17, 2018 at 8:43

Alper t. Turker · Accepted Answer · 2018-05-17 09:14:26Z

2

pivot and toJSON will give you what you need

import org.apache.spark.sql.functions.first

df.groupBy().pivot("protocol").agg(first("count")).toJSON.show(false)
// +----------------------------------------------------------+                    
// |value                                                     |
// +----------------------------------------------------------+
// |{"EGP":2331,"ICMP":7314,"IGMP":4423,"TCP":8321,"UDP":5523}|
// +----------------------------------------------------------+

answered May 17, 2018 at 9:14

Alper t. Turker

35.3k9 gold badges89 silver badges118 bronze badges

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

Comments

Shaido · Accepted Answer · 2018-05-17 09:11:41Z

0

Since you want to convert all columns to a single one and it does not seem to be many columns to begin with, you can collect the dataframe to the driver and use pure Scala code to convert it into the format you want.

The following will give you a Array[String]:

val res = df.as[(String, Int)].collect.map{case(protocol, count) => protocol + ": " + count}

To convert it into a single string, simply do:

val str = res.mkString("{", ", ", "}")

answered May 17, 2018 at 9:11

Shaido

28.6k26 gold badges76 silver badges82 bronze badges

Comments

Mann · Accepted Answer · 2018-05-17 09:22:13Z

0

Concat columns in dataframe and create a new column:

var new_df = df.withColumn("concat", concat($"protocol", lit(" : "), $"count"))

To aggregate it into a single row as a list you can do this.

var new_df = new_df.groupBy().agg(collect_list("concat").as("aggregated"))
new_df.show

If you want to get the data into a string instead of dataframe, you can collect it as following.

new_df.select("concat").collect.map(x=> x.get(0)).mkString("{", ",", "}")

edited May 17, 2018 at 9:22

answered May 17, 2018 at 8:39

Mann

3072 silver badges14 bronze badges

Collectives™ on Stack Overflow

Spark DataFrame aggregate multiple column into one column as a string

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related