Spark-Scala Question on Using Case Class Demiliter

user_16687493 0
Viewed 93

I have a text file named stockmarket.txt:

2012-01-03,59.970001,61.060001,59.869999,60.330002,12668800,52.619234999999996,2012-01-04,60.209998999999996,60.349998,59.470001,59.709998999999996,9593300,52.078475,2012-01-05,59.349998,59.619999,58.369999,59.419998,12768200,51.825539,2012-01-06,59.419998,59.450001,58.869999,59.0,8069400,51.45922,2012-01-09,59.029999,59.549999,58.919998,59.18,6679300,51.616215000000004,2012-01-10,59.43,59.709998999999996,58.98,59.040001000000004,6907300,51.494109,2012-01-11,59.060001,59.529999,59.040001000000004,59.400002,6365600,51.808098,2012-01-12,59.790001000000004,60.0,59.400002,59.5,7236400,51.895315999999994

I want to seperate the line into a new row after the occurrence of every 7th comma as a delimiter and display the result. There will be 7 columns named Date,Open,High,Low,Close,Volume,AdjClose should be tagged with the data. Make use of Case Class wherever necessary.

apache-spark-sql apache-spark scala
1 Answers

case class Stock(date: String, open: String, high: Double, low: Double, close: Double, volume: Long, adjClose: Double)

ds.withColumn("value", 
    regexp_replace('value, "([^,]*),([^,]*),([^,]*),([^,]*),([^,]*),([^,]*),([^,]*),", "$1,$2,$3,$4,$5,$6,$7\n"))
  .withColumn("value", explode(split('value, "\n")))
  .withColumn("value", split('value, ",")).as[Seq[String]]
  .map(a => Stock(a.head, a(1), a(2).toDouble, a(3).toDouble, a(4).toDouble, a(5).toLong, a(6).toDouble))

That produces

+----------+------------------+------------------+------------------+------------------+--------+------------------+
|date      |open              |high              |low               |close             |volume  |adjClose          |
+----------+------------------+------------------+------------------+------------------+--------+------------------+
|2012-01-03|59.970001         |61.060001         |59.869999         |60.330002         |12668800|52.619234999999996|
|2012-01-04|60.209998999999996|60.349998         |59.470001         |59.709998999999996|9593300 |52.078475         |
|2012-01-05|59.349998         |59.619999         |58.369999         |59.419998         |12768200|51.825539         |
|2012-01-06|59.419998         |59.450001         |58.869999         |59.0              |8069400 |51.45922          |
|2012-01-09|59.029999         |59.549999         |58.919998         |59.18             |6679300 |51.616215000000004|
|2012-01-10|59.43             |59.709998999999996|58.98             |59.040001000000004|6907300 |51.494109         |
|2012-01-11|59.060001         |59.529999         |59.040001000000004|59.400002         |6365600 |51.808098         |
|2012-01-12|59.790001000000004|60.0              |59.400002         |59.5              |7236400 |51.895315999999994|
+----------+------------------+------------------+------------------+------------------+--------+------------------+

To test with your string I did:

val str = """<your sample string in here>"""
val ds = Seq(str).toDS  // you'd use `spark.read.text("stockmarket.txt")` or similar
user_20159040
Related