How do I calculate the Average salary per location in Spark Scala with below two data sets ?
File1.csv(Column 4 is salary)
Ram, 30, Engineer, 40000
Bala, 27, Doctor, 30000
Hari, 33, Engineer, 50000
Siva, 35, Doctor, 60000
File2.csv(Column 2 is location)
Hari, Bangalore
Ram, Chennai
Bala, Bangalore
Siva, Chennai
The above files are not sorted. Need to join these 2 files and find average salary per location. I tried with below code but unable to make it.
val salary = sc.textFile("File1.csv").map(e => e.split(","))
val location = sc.textFile("File2.csv").map(e.split(","))
val joined = salary.map(e=>(e(0),e(3))).join(location.map(e=>(e(0),e(1)))
val joinedData = joined.sortByKey()
val finalData = joinedData.map(v => (v._1,v._2._1._1,v._2._2))
val aggregatedDF = finalData.map(e=> e.groupby(e(2)).agg(avg(e(1))))
aggregatedDF.repartition(1).saveAsTextFile("output.txt")
Please help with code and sample output how it will look.
Many Thanks