CODEWITHSUNDEEP
scalaapache-sparkdataframeapache-spark-sql
Dynamite
Dynamite

Reputation: 390

Grouping by values on a Spark Dataframe

I'm working on a Spark dataframe containing this kind of data:

A,1,2,3
B,1,2,3
C,1,2,3
D,4,2,3

I want to aggegate this data on the three last columns, so the output would be :

ABC,1,2,3
D,4,2,3

How can I do it in scala ? (this is not a big dataframe so performance is secondary here)

Upvotes: 0

Views: 517

Answers (2)

abiratsis
abiratsis

Reputation: 7336

Alternatively you can map by your key in this case c2, c3, c4 and then concatenate your values via reduce by key. In the end I format each row as needed through the last map. It should be something like the following:

    val data=sc.parallelize(List(
       ("A",  "1",  "2", "3"),
       ("B",  "1",  "2", "3"),
       ("C",  "1",  "2", "3"),
       ("D",  "4",  "2", "3")))

val res = data.map{ case (c1, c2, c3, c4) => ((c2, c3, c4), String.valueOf(c1)) }
.reduceByKey((x, y) => x + y)
.map(v => v._2.toString + "," + v._1.productIterator.toArray.mkString(","))
.collect

Upvotes: 1

vindev
vindev

Reputation: 2280

As mentioned in the comments you can first use groupBy to group your columns and then use concat_ws on your first column. Here is one way of doing it,

//create you original DF
val df = Seq(("A",1,2,3),("B",1,2,3),("C",1,2,3),("D",4,2,3)).toDF("col1","col2","col3","col4")
df.show

//output
+----+----+----+----+
|col1|col2|col3|col4|
+----+----+----+----+
|   A|   1|   2|   3|
|   B|   1|   2|   3|
|   C|   1|   2|   3|
|   D|   4|   2|   3|
+----+----+----+----+

//group by "col2","col3","col4" and store "col1" as list and then
//convert it to string

df.groupBy("col2","col3","col4")
.agg(collect_list("col1").as("col1"))
//you can change the string separator by concat_ws first arg
.select(concat_ws("", $"col1") as "col1",$"col2",$"col3",$"col4").show

//output
+----+----+----+----+
|col1|col2|col3|col4|
+----+----+----+----+
|   D|   4|   2|   3|
| ABC|   1|   2|   3|
+----+----+----+----+

Upvotes: 2

Related Questions