CODEWITHSUNDEEP
scalaapache-sparkapache-spark-sql
Iman
Iman

Reputation: 25

How add a new column to in dataframe and populate the column?

Add a new column called Download_Type to a Dataframe with conditions:

If Size < 100,000, Download_Type = “Small”

If Size > 100,000 and Size < 1,000,000, Download_Type = “Medium”

Else Download_Type = “Large”

Input Data: log_file.txt

Sample Data "date","time","size","r_version","r_arch","r_os","package","version","country","ip_id" "2012-10-01","00:30:13",35165,"2.15.1","i686","linux-gnu","quadprog","1.5-4","AU",1

I have created a dataframe using these steps:

val file1 =  sc.textFile(“log_file.txt”)

val header = file1.first

val logdata = file1.filter(x=>x!=header)

case class Log(date:String, time:String, size: Double, r_version:String, r_arch:String, r_os:String, packagee:String, version:String, country:String, ipr:Int)

val logfiledata = logdata.map(_.split(“,”)),map(p=>Log(p(0),p(1),p(2).toDouble,p(3),p(4),p(5),p(6),p(7),p(8),p(9).toInt))

val logfiledf = logfiledata.toDF()

I isolated the size column and converted it into an array:

val size = logfiledf.select($"size")

val sizearr = size.collect.map(row=>row.getDouble(0))

I made a function so i can populate the newly added column:

def exp1(size:Array[Double])={

var result = ""

for(i <- 0 to (size.length-1)){

if(size(i)<100000) result += "small"

else(if(size(i) >=100000 && size(i) <1000000) "medium"

else "large"

}

return result

}

I tried this to populate the column Download_Type:

val logfiledf2 = logfiledf.withColumn("Download_Type", expr(exp1(sizearr))

How can I populate the new column called Download_type with the conditions:

If Size < 100,000, Download_Type = “Small”

If Size > 100,000 and Size < 1,000,000, Download_Type = “Medium”

Else Download_Type = “Large” ?

Upvotes: 1

Views: 108

Answers (1)

Leo C
Leo C

Reputation: 22439

You can simply apply withColumn to the loaded DataFrame logfiledf using when/otherwise, as shown below:

import org.apache.spark.sql.functions._
import spark.implicits._

val logfiledf = Seq(
  ("2012-10-01","00:30:13",35165.0,"2.15.1","i686","linux-gnu","quadprog","1.5-4","AU",1),
  ("2012-10-02","00:40:14",150000.0,"2.15.1","i686","linux-gnu","quadprog","1.5-4","US",2)
).toDF("date","time","size","r_version","r_arch","r_os","package","version","country","ip_id")

logfiledf.withColumn("download_type", when($"size" < 100000, "Small").otherwise(
    when($"size" < 1000000, "Medium").otherwise("Large")
  )
).show
// +----------+--------+--------+---------+------+---------+--------+-------+-------+-----+-------------+
// |      date|    time|    size|r_version|r_arch|     r_os| package|version|country|ip_id|download_type|
// +----------+--------+--------+---------+------+---------+--------+-------+-------+-----+-------------+
// |2012-10-01|00:30:13| 35165.0|   2.15.1|  i686|linux-gnu|quadprog|  1.5-4|     AU|    1|        Small|
// |2012-10-02|00:40:14|150000.0|   2.15.1|  i686|linux-gnu|quadprog|  1.5-4|     US|    2|       Medium|
// +----------+--------+--------+---------+------+---------+--------+-------+-------+-----+-------------+

Upvotes: 3

Related Questions