How to add multiple columns in a spark dataframe using SCALA

Question

I have a condition where I have to add 5 columns (to an existing DF) for 5 months of a year.

The existing DF is like:

EId EName Esal
1   abhi  1100
2   raj   300
3   nanu  400
4   ram   500

The Output should be as follows:

EId EName Esal Jan  Feb  March April May  
1   abhi  1100 1100 1100 1100  1100  1100 
2   raj   300  300  300  300   300   300  
3   nanu  400  400  400  400   400   400
4   ram   500  500  500  500   500   500

I can do this one by one with withColumn but that takes a lot of time.

Is there a way I can run some loop and keep on adding columns till my conditions are exhausted.

Many thanks in advance.

How is _"I can do this one by one with withColumn but that takes a lot of time."_ different from _"Is there a way I can run some loop and keep on adding columns till my conditions are exhausted."_? I don't see any difference. — Jacek Laskowski, Jan 06 '18 at 17:54

score 7 · Answer 1 · answered Jan 05 '18 at 13:57

7

You can use foldLeft. You'll need to create a List of the columns that you want.

df.show
+---+----+----+
| id|name| sal|
+---+----+----+
|  1|   A|1100|
+---+----+----+

val list = List("Jan", "Feb" , "Mar", "Apr") // ... you get the idea

list.foldLeft(df)((df, month) => df.withColumn(month , $"sal" ) ).show
+---+----+----+----+----+----+----+
| id|name| sal| Jan| Feb| Mar| Apr|
+---+----+----+----+----+----+----+
|  1|   A|1100|1100|1100|1100|1100|
+---+----+----+----+----+----+----+

So, basically what happens is you fold the sequence you created while starting with the original dataframe and applying transformation as you keep on traversing through the list.

answered Jan 05 '18 at 13:57

philantrovert

9,904
3
37
61

3

How is that solution different from _"I can do this one by one with withColumn but that takes a lot of time."_? – Jacek Laskowski Jan 06 '18 at 17:53
It's not. It's just a little cleaner, I guess than back-to-back `withColumn`s – philantrovert Jan 06 '18 at 19:01
@JacekLaskowski The difference is that I want to loop over and keep on adding columns....say for example I have to add 20 or 30 columns bases on a condition, then is it possible to do that in a loop or somehow that I dont have to manually add it everytime, it should be iterated? – Rahul Mukherjee Jan 07 '18 at 13:02

USB · Answer 2 · 2018-10-26T12:58:19.233

Yes , You can do the same using foldLeft.FoldLeft traverse the elements in the collection from left to right with the desired value.

So you can store the desired columns in a List(). For Example:

val BazarDF = Seq(
        ("Veg", "tomato", 1.99),
        ("Veg", "potato", 0.45),
        ("Fruit", "apple", 0.99),
        ("Fruit", "pineapple", 2.59)
         ).toDF("Type", "Item", "Price")

Create a List with column name and values(as an example used null value)

var ColNameWithDatatype = List(("Jan", lit("null").as("StringType")),
      ("Feb", lit("null").as("StringType")
     ))
var BazarWithColumnDF1 = ColNameWithDatatype.foldLeft(BazarDF) 
  { (tempDF, colName) =>
                     tempDF.withColumn(colName._1, colName._2)
                }

You can see the example Here

score 1 · Answer 3 · answered Dec 15 '21 at 11:15

Have in mind that withColumn method of DataFrame could have performance issues when called in loop:

Spark DAG differs with 'withColumn' vs 'select'
There is even mentioned about that in https://spark.apache.org/docs/latest/api/scala/org/apache/spark/sql/Dataset.html#withColumn(colName:String,col:org.apache.spark.sql.Column):org.apache.spark.sql.DataFrame

this method introduces a projection internally. Therefore, calling it multiple times, for instance, via loops in order to add multiple columns can generate big plans which can cause performance issues and even StackOverflowException. To avoid this, use select with the multiple columns at once.

The safer way is to do it with select:

val monthsColumns = months.map { month:String =>
  col("sal").as(month)
}
val updatedDf = df.select(df.columns.map(col) ++ monthsColumns: _*)

How to add multiple columns in a spark dataframe using SCALA

3 Answers3