Need to get fields from lookup file with calculated values using spark dataframe

user_13506867 0
Viewed 52

I have a products file as below and the formula for cost price and discount in the lookup file. we need get the discount and cost price fields from lookup file.

product_id,product_name,marked_price,selling_price,profit

101,AAAA,5500,5400,500

102,ABCS,7000,6500,1000

103,GHMA,6500,5600,700

104,PSNLA,8450,8000,800

105,GNBC,1250,1200,600

lookup file:

key,value
cost_price,(selling_price+profit)
discount,(marked_price-selling_price)

Final output:

product_id,product_name,marked_price,selling_price,profit,cost_price,discount
101,AAAA,5500,5400,500,5900,100
102,ABCS,7000,6500,1000,7500,500
103,GHMA,6500,5600,700,6300,900
104,PSNLA,8450,8000,800,8800,450
105,GNBC,1250,1200,600,1800,50

apache-spark-sql apache-spark
2 Answers

First, you should make a Map out of your lookup-file, then you can add the columns using expr:

val lookup = Map(
  "cost_price" -> "selling_price+profit",
  "discount" -> "(marked_price-selling_price)"
)

val df = Seq(
  (101, "AAAA", 5500, 5400, 500),
  (102, "ABCS", 7000, 6500, 1000)
)
  .toDF("product_id", "product_name", "marked_price", "selling_price", "profit")


df
  .withColumn("cost_price",expr(lookup("cost_price")))
  .withColumn("discount",expr(lookup("discount")))
  .show()

gives :

+----------+------------+------------+-------------+------+----------+--------+
|product_id|product_name|marked_price|selling_price|profit|cost_price|discount|
+----------+------------+------------+-------------+------+----------+--------+
|       101|        AAAA|        5500|         5400|   500|      5900|     100|
|       102|        ABCS|        7000|         6500|  1000|      7500|     500|
+----------+------------+------------+-------------+------+----------+--------+

you can also iterate your lookup :

val finalDF = lookup.foldLeft(df){case (df,(k,v)) => df.withColumn(k,expr(v))}
user_11385230 
import spark.implicits._
val dataheader = Seq("product_id", "product_name", "marked_price", "selling_price", "profit")
val data = Seq(
  (101, "AAAA", 5500, 5400, 500),
  (102, "ABCS", 7000, 6500, 1000),
  (103, "GHMA", 6500, 5600, 700),
  (104, "PSNLA", 8450, 8000, 800),
  (105, "GNBC", 1250, 1200, 600))
  
val dataDF = data.toDF(dataheader: _*)
val lookupMap = Seq(
  ("cost_price", "(selling_price+profit)"),
  ("discount", "(marked_price-selling_price)")) //You can read your data from look up file and construct a dataframe
.toDF("key", "value").select("key", "value").as[(String, String)].collect.toMap

lookupMap.foldLeft(dataDF)((df, look) => {
  df.withColumn(look._1, expr(look._2))
}).show()
user_68698190
Related