How to convert string date to desired date format in pyspark

user_16632714 0
Viewed 956

I have a column with dates, where a few records have mm-dd-yy, dd-mm-yy, yy-mm-dd.

df = sc.parallelize([['12-21-2021'],
                     ['04-23-2021'],
                     ['22-03-24'],
                     ['12/03/20']]).toDF(["Date"])

df.show()
+----------+
|      Date|
+----------+
|12-21-2021|
|04-23-2021|
|  22-03-24|
|  12/03/20|
+----------+

Now I want to convert string to date format. But as you can see the results for last two records though got a correct format but the result column is taking the wrong format. How do I make it to take the correct format?

 from pyspark.sql import functions as F
    from pyspark.sql.functions import col, unix_timestamp, to_date
    from pyspark.sql.functions import date_format
    spark.sql("set spark.sql.legacy.timeParserPolicy=LEGACY")
    sdf = df.withColumn("yyyy/MM/dd", F.to_date(F.unix_timestamp(df.Date,'yyyy/MM/dd').cast('timestamp'))) \
      .withColumn("yyyy-MM-dd", F.to_date(F.unix_timestamp(df.Date,'yyyy-MM-dd').cast('timestamp'))) \
      .withColumn("MM/dd/yyyy", F.to_date(F.unix_timestamp(df.Date,'MM/dd/yyyy').cast('timestamp'))) \
      .withColumn("MM-dd-yyyy", F.to_date(F.unix_timestamp(df.Date,'MM-dd-yyyy').cast('timestamp'))) \
      .withColumn("dd/MM/yy", F.to_date(F.unix_timestamp(df.Date,'dd/MM/yy').cast('timestamp'))) \
      .withColumn("dd-MM-yy", F.to_date(F.unix_timestamp(df.Date,'dd-MM-yy').cast('timestamp'))) \
      .withColumn("result", F.coalesce("yyyy/MM/dd", "yyyy-MM-dd", "MM/dd/yyyy", "MM-dd-yyyy",'dd/MM/yy','dd-MM-yy')) 
    display(sdf)

Date      yyyy/MM/dd    yyyy-MM-dd  MM/dd/yyyy  MM-dd-yyyy  dd/MM/yy    dd-MM-yy     result
12-21-2021  null          null        null      2021-12-21   null        null        2021-12-21
04-23-2021  null          null        null      2021-04-23   null        null        2021-04-23
22-03-24    null        0022-03-24    null       null        null     2024-03-22     0022-03-24
12/03/20    0012-03-20    null     0020-12-03    null      2020-03-12    null        0012-03-20
pyspark apache-spark-sql date-format date
1 Answers

I tested a solution that works for me, I hope it'll work for you.
Some points : replace '/' with '-' will allow to have only 2 different cases; but there will still be ambiguous values so you will need to choose an order for your coalesce (I see no way to identify whether a date is in format dd/MM or MM/dd with some numbers). I wonder if your problems would not come from your use of cast()
I let the intermediate formats for you to see specific result, but the coalesce works in my case.

from pyspark.sql import functions as F, types as T

def TEST_convert_dates(ds_with_dates):
    spark.sql("set spark.sql.legacy.timeParserPolicy=LEGACY")
    df = ds_with_dates\
        .withColumn('Date', F.regexp_replace('Date', '/', '-')) \
        .withColumn('Date_format1', F.to_date(F.col('Date'), 'MM-dd-yy').cast(T.DateType())) \
        .withColumn('Date_format2', F.to_date(F.col('Date'), 'dd-MM-yy').cast(T.DateType())) \
        .withColumn('Date_formated', F.coalesce(
            F.to_date(F.col('Date'), 'MM-dd-yy').cast(T.DateType()),
            F.to_date(F.col('Date'), 'dd-MM-yy').cast(T.DateType())
        ))
    return df

Here is the result:

Date           format 1       format 2         Result  
12-21-2021     2021-12-21        null        2021-12-21  
04-23-2021     2021-04-23        null        2021-04-23  
22-03-24         null        2024-03-22      2024-03-22  
12/03/20       2020-12-03    2020-03-12      2020-12-03
user_108916750
Related