Passing columns and not variables with string format for converting yymmdd to dd-mm-YYYY in python

Question

I have a dataset like the below:

df = spark.sql("select '210927' as t_date")

Now, I want to convert it to '27-09-2021'. Below is my code along with the error:

   >>> df = df.withColumn("modifiedDate",datetime.strptime("t_date", "%y%m%d").strftime('%d-%m-%Y'))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python3.8/_strptime.py", line 568, in _strptime_datetime
    tt, fraction, gmtoff_fraction = _strptime(data_string, format)
  File "/usr/lib/python3.8/_strptime.py", line 349, in _strptime
    raise ValueError("time data %r does not match format %r" %
ValueError: time data 't_date' does not match format '%y%m%d'

I tried several examples from SO and this link but didn't work. I am using Python with Pyspark

Both the existing and the proposed date formats are very problematic. Consider using something sensible like ISO8601. — jarmod, Sep 27 '21 at 13:50
If you wanted a day-month-year format why does your format string have a year-month-day format? — wwii, Sep 27 '21 at 13:53
have you tried `datetime.strptime("210927", "%y%m%d").strftime('%d-%m-%Y')`? It looks like this gives the desired output — rv.kvetch, Sep 27 '21 at 13:53
@rv.kvetch post your solution as an answer and I will accept it because it is dynamic — whatsinthename, Sep 27 '21 at 14:18
and how do I pass the column for this? It is accepting string only — whatsinthename, Sep 27 '21 at 14:29
hmm, not familiar with the dataset, but you might need to loop through each column value. — rv.kvetch, Sep 27 '21 at 14:34

score 0 · Answer 1 · answered Sep 27 '21 at 13:55

0

How about

from datetime import datetime

x = '210927'
year = 2000 + int(x[:2])
month = int(x[2:4])
day = int(x[4:])
dt = datetime(year=year, month=month, day=day)
print(dt)

output

2021-09-27 00:00:00

answered Sep 27 '21 at 13:55

balderman

22,927
7
34
52

why the string slicing if you're using datetime class functionality anyway? rv.kvetch's comment provides a much simpler solution I think. – FObersteiner Sep 27 '21 at 14:08
Thank you for your answer @balderman but I will have to handle it dynamically which is satisfied by rv.kvetch's answer – whatsinthename Sep 27 '21 at 14:18

score 0 · Accepted Answer · answered Sep 30 '21 at 15:23

Below code worked for me:

df = spark.createDataFrame(
    [
        (1, "210927"),
        (2, "210928"),
        (3, "210929"),
        (4, "210930"),
        (5, "211001"),
    ],
    StructType(
        [
            StructField("id", IntegerType(), False),
            StructField("col", StringType(), False),
        ]
    ),
)
 
pDF= df.toPandas()
valuesList = pDF['col'].to_list()
modifiedList = list()
 
for i in valuesList:
...  modifiedList.append(datetime.strptime(i, "%y%m%d").strftime('%d-%m-%Y'))
 
pDF['t_date1']=modifiedList
 
df = spark.createDataFrame(pDF)

Might help someone someday!

Passing columns and not variables with string format for converting yymmdd to dd-mm-YYYY in python

2 Answers2