How to merge two or more columns into one?

Question

I have a streaming Dataframe that I want to calculate min and avg over some columns.

Instead of getting separate resulting columns of min and avg after applying the operations, I want to merge the min and average output into a single column.

The dataframe look like this:

+-----+-----+ | 1 | 2 | +-----+-----+- |24 | 55 | +-----+-----+ |20 | 51 | +-----+-----+

I thought I'd use a Scala tuple for it, but that does not seem to work:

val res = List("1","2").map(name => (min(col(name)), avg(col(name))).as(s"result($name)"))

All code used:

val res = List("1","2").map(name => (min(col(name)),avg(col(name))).as(s"result($name)")) val groupedByTimeWindowDF1 = processedDf.groupBy($"xyz", window($"timestamp", "60 seconds")) .agg(res.head, res.tail: _*)

I'm expecting the output after applying the min and avg mathematical opearations to be:

+-----------+-----------+ | result(1)| result(2)| +-----------+-----------+ |20 ,22 | 51,53 | +-----------+-----------+

How I should write the expression?

Jacek Laskowski · Accepted Answer · 2019-01-29 12:26:00Z

Use struct standard function:

struct(colName: String, colNames: String*): Column

struct(cols: Column*): Column

Creates a new struct column that composes multiple input columns.

That gives you the values as well as the names (of the columns).

val res = List("1","2").map(name => struct(min(col(name)), avg(col(name))) as s"result($name)") ^^^^^^ HERE

The power of struct can be seen when you want to reference one field in the struct and you can use the name (not index).

q.select("structCol.name")

Shaido · Accepted Answer · 2019-01-28 06:25:05Z

1

What you want to do is to merge the values of multiple columns together in a single column. For this you can use the array function. In this case it would be:

val res = List("1","2").map(name => array(min(col(name)),avg(col(name))).as(s"result($name)"))

Which will give you :

+------------+------------+ | result(1)| result(2)| +------------+------------+ |[20.0, 22.0]|[51.0, 53.0]| +------------+------------+

answered Jan 28, 2019 at 6:25

Shaido

28.6k26 gold badges76 silver badges82 bronze badges

4 Comments

SLU Over a year ago

Thanks , In addition how to merge column name with DF row. I want result [20.0,22.0,result(1)] in this format. how should be expr ? val res = List("1","2").map(name => array(min(col(name)),avg(col(name))).as(s"result($name)"))

Shaido Over a year ago

@shrikrishna: If I understand correctly you want to add the column name to the array? That seems very inconvenient to be honest, but you can add it with lit(s"result$name").

SLU Over a year ago

@ Shaido its work for me. I need column name for ForeachWriter for further operation and DB sink class. is any better design?

Shaido Over a year ago

@shrikrishna: I suspect there is but it depends on your actual situation, I would recommend asking a new question about it with more details.

Collectives™ on Stack Overflow

How to merge two or more columns into one?

2 Answers 2

Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Related