Creating a Unix Timestamp out of a string with format d-m-yyyy (Pyspark or Pandans)

user_16743780 0
Viewed 62

I have some strange (string) date format which I want to convert into a more sensible timestamp (e.g. Unix as YYYY-MM-DD) in Pyspark (Pandas is also fine).

Input (examples):

Screenshot from Timestamps

So instead of 17.3.2021 (which stands for 17th of March 2021) I want to have the format "2021-03-17"

"2.3.2021" stands for 2nd March 2021 and should be converted in "2021-03-02". So the problem is that as well Months as Days will be shown in one-digit for the 1st to 9th day respective January to september. With the "to_date" in Pyspark it didn't work as well as every transformation which I tried in Pandas lead to invalid values or errors.

Thank you very much in advance

pyspark pandas date datetime
2 Answers

Use from_unixtime,unix_timestamp functions.

df.show()
#+--------+
#|      dt|
#+--------+
#|2.3.2021|
#+--------+

from pyspark.sql.functions import *


#from spark3
spark.sql("set spark.sql.legacy.timeParserPolicy=LEGACY")
df.withColumn("dt",from_unixtime(unix_timestamp(col("dt"),"dd.mm.yyyy"),"yyyy-mm-dd")).\
show()
#+----------+
#|        dt|
#+----------+
#|2021-03-02|
#+----------+

#casting as date type
from pyspark.sql.types import *
df.withColumn("dt",from_unixtime(unix_timestamp(col("dt"),"dd.mm.yyyy"),"yyyy-mm-dd").cast("date")).\
show()
#+----------+
#|        dt|
#+----------+
#|2021-03-02|
#+----------+
user_76326950

Use pandas.to_datetime:

>>> pd.to_datetime(['17.3.2021']).strftime('%Y-%m-%d')
['2021-03-17']

Of course, instead of the list you can pass a Series.

df['new_date'] = pd.to_datetime(df['Date']).strftime('%Y-%m-%d')
user_163434640
Related