How to group by using Spark Scala in order to build nested DataFrame?

Question

I have this DataFrame:

val df: DataFrame = Seq(
("1", "1234 58", "SYSTEM_A", "5", "2022"),
("2", "1458 65", "SYSTEM_B", "2", "2021")
).toDF("id", "pseudo_id", "system", "number", "date")

I need to build a nested DataFrame using the df DataFrame with the following schema:

root
 |-- id: string (nullable = true)
 |-- pseudo_id: string (nullable = true)
 |-- system: string (nullable = true)
 |-- version: struct (nullable = false)
 |    |-- number: string (nullable = true)
 |    |-- date: string (nullable = true)

I tried to build it with:

val nestedDf: DataFrame = df
.groupBy("id", "pseudo_id", "system")
.agg(
  struct(
  "number",
  "date"
  ).as("version")
)

But I have obtained the following error :

Exception in thread "main" org.apache.spark.sql.AnalysisException: expression 'number' is neither present in the group by, nor is it an aggregate function. Add to group by or wrap in first() (or first_value) if you don't care which value you get.;

Any ideas ?

The error message explains it pretty well, you have to use an aggregation expression for all columns that are not part of the group by. The question is what you really intend to do? Is it just about re-structuring the data / changing the schema or do you want to aggregate (deduplicate?) the data? — Moritz
– Moritz, Commented Sep 5, 2022 at 16:19

werner · Accepted Answer · 2022-09-05 17:57:03Z

1

You can use a struct expression:

val df=...
val df2=df.selectExpr("id", "pseudo_id", "system", "struct(number, date) as version")
df2.printSchema()

Output:

root
 |-- id: string (nullable = true)
 |-- pseudo_id: string (nullable = true)
 |-- system: string (nullable = true)
 |-- version: struct (nullable = false)
 |    |-- number: string (nullable = true)
 |    |-- date: string (nullable = true)

answered Sep 5, 2022 at 17:57

werner

15k6 gold badges36 silver badges56 bronze badges

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

Collectives™ on Stack Overflow

How to group by using Spark Scala in order to build nested DataFrame?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related