Spark Java - Merge same column multiple rows

Question

I'm using Java Spark and I have 1 Dataframe like this

+---+-----+------+ |id |color|datas | +----------------+ |1 |blue |data1| |1 |red |data2| |1 |orange|data3| |2 |black |data4| |2 | |data5| |2 |yellow| | |3 |white |data7| |3 | |data8| +----------------+

I need to modify this dataframe to look like this :

+---+--------------------+---------------------+ |id |color |datas | +----------------------------------------------+ |1 |[blue, red, orange] |[data1, data2, data3]| |2 |[black, yellow] |[data4, data5] | |3 |[white] |[data7, data8] | +----------------------------------------------+

I want to merge the data to create an 'array' of the same column but from differents rows based on the 'id' column.

I'm able to do it throught UserDefinedAggregateFunction but I can only do it one column at a time and it takes too much time to process.

Thank you

Edit : I'm using Spark 1.6

koiralo · Accepted Answer · 2017-05-23 10:25:20Z

2

you can group by "id" and then use collect_list function to get the aggregated values.

dataframe.groupBy("id").agg(collect_list(struct("color")).as("color"), collect_list(struct("dates")).as("dates") )

Hope this helps

answered May 23, 2017 at 10:25

koiralo

23.2k6 gold badges57 silver badges77 bronze badges

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

4 Comments

Lucien Over a year ago

Thanks for the fast answer but unfortunately, I got a "org.apache.spark.sql.AnalysisException: undefined function collect_list" when I try with collect_list I'm using Spark 1.6

koiralo Over a year ago

did you import the function?? import static org.apache.spark.sql.functions.*;

Lucien Over a year ago

yes I did, I found this answer for the undefined function stackoverflow.com/questions/35324049/… I'm trying to solve it

koiralo Over a year ago

if you import function from spark sql it should work.

Gabriela Vaca Guzman G. · Accepted Answer · 2020-01-21 18:09:07Z

The actual function that works for me is:

dataframe.groupBy("id").agg(collect_list("color").as("color"), collect_list("date").as("date") ) dataframe.createOrReplaceTempView("dataframe")

Then create a new query where you can use the struct()

dffinal = spark.sql(s"""SELECT struct(a.color) AS colors, struct(a.date) AS dates FROM dataframe a """)

Collectives™ on Stack Overflow

Spark Java - Merge same column multiple rows

2 Answers 2

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Linked

Related