CODEWITHSUNDEEP
pysparkapache-spark-sql
user6200992
user6200992

Reputation: 311

Extract week day number from string column (datetime stamp) in spark api

I am new to Spark API. I am trying to extract weekday number from a column say col_date (having datetime stamp e.g '13AUG15:09:40:15') which is string and add another column as weekday(integer). I am not able to do successfully.

Upvotes: 1

Views: 8439

Answers (2)

Grant Shannon
Grant Shannon

Reputation: 5055

the approach below worked for me, using a 'one line' udf - similar but different to above:

from  pyspark.sql  import SparkSession, functions
spark = SparkSession.builder.appName('dayofweek').getOrCreate()

set up the dataframe:

df = spark.createDataFrame(
    [(1, "2018-05-12")
     ,(2, "2018-05-13")
     ,(3, "2018-05-14")
      ,(4, "2018-05-15")
      ,(5, "2018-05-16")
      ,(6, "2018-05-17")
      ,(7, "2018-05-18")
      ,(8, "2018-05-19")
      ,(9, "2018-05-20")
    ], ("id", "date"))

set up the udf:

from pyspark.sql.functions import udf,desc
from datetime import datetime

weekDay =  udf(lambda x: datetime.strptime(x, '%Y-%m-%d').strftime('%w'))

df = df.withColumn('weekDay', weekDay(df['date'])).sort(desc("date"))

results:

    df.show()

+---+----------+-------+
| id|      date|weekDay|
+---+----------+-------+
|  9|2018-05-20|      0|
|  8|2018-05-19|      6|
|  7|2018-05-18|      5|
|  6|2018-05-17|      4|
|  5|2018-05-16|      3|
|  4|2018-05-15|      2|
|  3|2018-05-14|      1|
|  2|2018-05-13|      0|
|  1|2018-05-12|      6|
+---+----------+-------+

Upvotes: 1

Matus Cimerman
Matus Cimerman

Reputation: 447

Well, this is quite simple.

This simple function make all the job and returns weekdays as number (monday = 1):

from time import time
from datetime import datetime

# get weekdays and daily hours from timestamp
def toWeekDay(x):
#     v = datetime.strptime(datetime.fromtimestamp(int(x)).strftime("%Y %m %d %H"), "%Y %m %d %H").strftime('%w') - from unix timestamp
    v = datetime.strptime(x, '%d%b%y:%H:%M:%S').strftime('%w')
    return v

days = ['13AUG15:09:40:15','27APR16:20:04:35'] # create example dates
days = sc.parallelize(days) # for example purposes - transform python list to RDD so we can do it in a 'Spark [parallel] way'
days.take(2) # to see whats in RDD
> ['13AUG15:09:40:15', '27APR16:20:04:35']

result = v.map(lambda x: (toWeekDay(x))) # apply functon toWeekDay on each element of RDD
result.take(2) # lets see results
> ['4', '3']

Please see Python documentation for further details on datetime processing.

Upvotes: 0

Related Questions