Extract Year from (Month Day, Year, HH:MM:SS) datetime format using Regex in Pyspark

user_16931717 0
Viewed 165

Using Pyspark, I'm querying a data base with some datetime info:

df.select(df.Activity_date).show(5, truncate = False)

+-------------------------+
|Activity_date            |
+-------------------------+
|Jul 20, 2015, 1:06:57 PM |
|Oct 11, 2015, 10:03:14 PM|
|Dec 8, 2016, 4:28:12 PM  |
|Dec 12, 2016, 10:22:44 PM|
|Dec 25, 2016, 10:37:32 PM|
+-------------------------+

I would like to do a query that extracts the Month, then the Year.

Here's where things stand:

df.select( "Activity_Type", 
regexp_extract("Activity_Date",r"^(\\w{3})",1).alias("Month"), 
regexp_extract("Activity_Date", r"^\\d{4}$",1).alias("Year"), 
).show(5,truncate=False)

+-------------+-----+----+
|Activity_Type|Month|Year|
+-------------+-----+----+
|Ride         |Jul  |    |
|Ride         |Oct  |    |
|Ride         |Dec  |    |
|Ride         |Dec  |    |
|Ride         |Dec  |    |
+-------------+-----+----+

As you can see, I'm having a hard time writing the regex to extract the Year part.

pyspark apache-spark-sql apache-spark python regex
3 Answers
user_116932460

You don't need to use regex here. Simply convert the string dates to timestamp then use Spark datetime functions to extract month and year:

from pyspark.sql import functions as F

df = spark.createDataFrame([
    ("Jul 20, 2015, 1:06:57 PM",), ("Oct 11, 2015, 10:03:14 PM",),
    ("Dec 8, 2016, 4:28:12 PM",), ("Dec 12, 2016, 10:22:44 PM",),
    ("Dec 25, 2016, 10:37:32 PM",)], ["Activity_date"])

df1 = (df.withColumn("Activity_date", F.to_timestamp("Activity_date", "MMM d, yyyy, h:mm:ss a"))
       .withColumn("Month", F.date_format("Activity_date", "MMM"))
       .withColumn("Year", F.year("Activity_date"))
       )

df1.show()
#+-------------------+-----+----+
#|      Activity_date|Month|Year|
#+-------------------+-----+----+
#|2015-07-20 13:06:57|  Jul|2015|
#|2015-10-11 22:03:14|  Oct|2015|
#|2016-12-08 16:28:12|  Dec|2016|
#|2016-12-12 22:22:44|  Dec|2016|
#|2016-12-25 22:37:32|  Dec|2016|
#+-------------------+-----+----+
user_13865510

And, in a classic case of further attempts, here's one possible answer:

df.select(
    "Activity_Type",
    regexp_extract("Activity_Date", r"^(\w{3})", 1).alias("Month"),
    regexp_extract("Activity_Date", r"^\w{3}\s\d{1,2}(,)\s(.{4})",2).alias("Year"),
).show(5,truncate=False)
user_169317170
Related