Create new column from datetime field in spark scala 2.11.11

Question

I imported a local csv file into my spark scala project. I have to calculate some statistics from the data. One of the calculations involve grouping the data by the date field and counting the occurrences by id. The second calculation involves showing the top 100 names in the data that appear frequently.

Any help would be appreciated on these items.

For the first one, I am able to create the "Month" column, but null values are there instead of just the numeric month so that I can group by the new "Month" column. Can someone help with this?

TestData_csv.withColumn("Month",date_format(to_date(col("date")), "MM")).show(false)

Answer 1

You were almost there!

The problem in your case was that the to_date function you were using did not understand what format the strings were written in. There is also a to_date function that accepts a fmt string to make Spark understand how to parse your strings.

That would looks something like this:

import spark.implicits._

// This is just the data you posted in a comment on your question
val df = spark.read.option("sep", ";").option("header", "true").csv("./dateTimeCSV.csv")

df.show
+---+----------+---------+---------+-----+                                                                                                                                                                                                                                      
| Id|      Date|    Fname|    Lname|Route|                                                                                                                                                                                                                                      
+---+----------+---------+---------+-----+                                                                                                                                                                                                                                      
|  1|  1/1/2017|     Greg|    Rollo|    S|                                                                                                                                                                                                                                      
|  2|  1/2/2017|  Frankie|    Allen|    N|                                                                                                                                                                                                                                      
|  2|  1/3/2017|   Denise|    Smith|    E|                                                                                                                                                                                                                                      
|  2| 2/11/2017|  Earlene|     Lane|    W|                                                                                                                                                                                                                                      
|  2| 3/22/2017|  Donovan|   Joseph|   SE|                                                                                                                                                                                                                                      
|  3| 3/11/2017|  Rebecca|    Bowls|   NE|                                                                                                                                                                                                                                      
|  3|  4/5/2017|Katherine|     Cook|   NW|                                                                                                                                                                                                                                      
|  4| 5/16/2017|   Alicia|    Mason|   SW|                                                                                                                                                                                                                                      
|  4|  6/9/2017|      Bob| Peterson|    S|                                                                                                                                                                                                                                      
|  4| 6/30/2017|    Janet|     Love|    N|                                                                                                                                                                                                                                      
|  5|  7/2/2017|  Richard|   Dingle|    E|                                                                                                                                                                                                                                      
|  5| 8/25/2017|   Thomas|Velasquez|    W|                                                                                                                                                                                                                                      
|  5| 8/10/2017|    Susan|     King|   SE|                                                                                                                                                                                                                                      
|  1| 9/25/2017|  Pratesh|   Venkat|   NE|                                                                                                                                                                                                                                      
|  1|10/14/2017|     Neha|    Kumar|   NW|                                                                                                                                                                                                                                      
|  1| 11/2/2017|    Louis| Williams|   SW|                                                                                                                                                                                                                                      
|  3|11/28/2017|  Winston|   McLean|    E|                                                                                                                                                                                                                                      
|  3| 12/2/2017|    Clark|     Kent|    N|                                                                                                                                                                                                                                      
|  3|12/14/2017|    Bruce|    Wayne|    S|                                                                                                                                                                                                                                      
+---+----------+---------+---------+-----+

val output = df.withColumn("Month",date_format(to_date(df("Date"), "MM/dd/yyyy"), "MM"))

output.show
+---+----------+---------+---------+-----+-----+                                                                                                                                                                                                                                
| Id|      Date|    Fname|    Lname|Route|Month|                                                                                                                                                                                                                                
+---+----------+---------+---------+-----+-----+                                                                                                                                                                                                                                
|  1|  1/1/2017|     Greg|    Rollo|    S|   01|                                                                                                                                                                                                                                
|  2|  1/2/2017|  Frankie|    Allen|    N|   01|                                                                                                                                                                                                                                
|  2|  1/3/2017|   Denise|    Smith|    E|   01|                                                                                                                                                                                                                                
|  2| 2/11/2017|  Earlene|     Lane|    W|   02|                                                                                                                                                                                                                                
|  2| 3/22/2017|  Donovan|   Joseph|   SE|   03|                                                                                                                                                                                                                                
|  3| 3/11/2017|  Rebecca|    Bowls|   NE|   03|                                                                                                                                                                                                                                
|  3|  4/5/2017|Katherine|     Cook|   NW|   04|                                                                                                                                                                                                                                
|  4| 5/16/2017|   Alicia|    Mason|   SW|   05|                                                                                                                                                                                                                                
|  4|  6/9/2017|      Bob| Peterson|    S|   06|                                                                                                                                                                                                                                
|  4| 6/30/2017|    Janet|     Love|    N|   06|                                                                                                                                                                                                                                
|  5|  7/2/2017|  Richard|   Dingle|    E|   07|                                                                                                                                                                                                                                
|  5| 8/25/2017|   Thomas|Velasquez|    W|   08|                                                                                                                                                                                                                                
|  5| 8/10/2017|    Susan|     King|   SE|   08|                                                                                                                                                                                                                                
|  1| 9/25/2017|  Pratesh|   Venkat|   NE|   09|                                                                                                                                                                                                                                
|  1|10/14/2017|     Neha|    Kumar|   NW|   10|                                                                                                                                                                                                                                
|  1| 11/2/2017|    Louis| Williams|   SW|   11|                                                                                                                                                                                                                                
|  3|11/28/2017|  Winston|   McLean|    E|   11|                                                                                                                                                                                                                                
|  3| 12/2/2017|    Clark|     Kent|    N|   12|                                                                                                                                                                                                                                
|  3|12/14/2017|    Bruce|    Wayne|    S|   12|                                                                                                                                                                                                                                
+---+----------+---------+---------+-----+-----+

As you can see, I added a fmt string with as value "MM/dd/yyyy" to make Spark understand how to parse the date.

Hope this helps!