Using regex function on date in Pyspark

user_13841348 0
Viewed 1003

I need to validate date(string format) in Pyspark Dataframe and I need to remove additonal characters,notations in date if they are present. How to validate like that ?

I came across this code

regex_string='\/](19|[2-9][0-9])\d\d$)|(^29[\/]02[\/](19|[2-9][0-9])(00|04|08|12|16|20|24|28|32|36|40|44|48|52|56|60|64|68|72|76|80|84|88|92|96)$)'
df.select(regexp_extract(col("date"),regex_string,0).alias("cleaned_map"),col('date')).show()

Below is my output

+-----------+-----------+
|cleaned_map|       date|
+-----------+-----------+
|           |01/06/w2020|
|           |02/06/2!020|
| 02/06/2020| 02/06/2020|
| 03/06/2020| 03/06/2020|
| 04/06/2020| 04/06/2020|
| 05/06/2020| 05/06/2020|
| 02/06/2020| 02/06/2020|
+-----------+-----------+

My expected output

+-----------+-----------+
|cleaned_map|       date|
+-----------+-----------+
| 01/06/2020|01/06/w2020|
| 02/06/2020|02/06/20!20|
| 03/06/2020| 03/06/2020|
| 04/06/2020| 04/06/2020|
| 05/06/2020| 05/06/2020|
| 06/06/2020| 06/06/2020|
| 07/06/2020| 07/06/2020|
+-----------+-----------+
pyspark-dataframes pyspark apache-spark-sql apache-spark regex
2 Answers

Try this-

    val df = Seq("01/06/w2020",
    "02/06/2!020",
    "02/06/2020",
    "03/06/2020",
    "04/06/2020",
    "05/06/2020",
    "02/06/2020",
    "//01/0/4/202/0").toDF("date")
    df.withColumn("cleaned_map", regexp_replace($"date", "[^0-9T]", ""))
      .withColumn("date_type", to_date($"cleaned_map", "ddMMyyyy"))
      .show(false)

    /**
      * +--------------+-----------+----------+
      * |date          |cleaned_map|date_type |
      * +--------------+-----------+----------+
      * |01/06/w2020   |01062020   |2020-06-01|
      * |02/06/2!020   |02062020   |2020-06-02|
      * |02/06/2020    |02062020   |2020-06-02|
      * |03/06/2020    |03062020   |2020-06-03|
      * |04/06/2020    |04062020   |2020-06-04|
      * |05/06/2020    |05062020   |2020-06-05|
      * |02/06/2020    |02062020   |2020-06-02|
      * |//01/0/4/202/0|01042020   |2020-04-01|
      * +--------------+-----------+----------+
      */

enrich this pattern "[^0-9/T]" if you want exclude any chars to be removed

user_47588230

Try regexp_replace to remove additional character notations.

    df.show()

    # +-----------+
    # |       date|
    # +-----------+
    # |01/06/w2020|
    # |02/06/2!020|
    # | 02/06/2020|
    # +-----------+

 df.withColumn("cleaned_map", F.regexp_replace("date", r'[^\d\/]','')).show()


    # +-----------+-----------+
    # |       date|cleaned_map|
    # +-----------+-----------+
    # |01/06/w2020| 01/06/2020|
    # |02/06/2!020| 02/06/2020|
    # | 02/06/2020| 02/06/2020|
    # +-----------+-----------+
user_86658430
Related