convert random string to datetime in python

Question

I have extracted data with date formatted 28-12-2019 19:13 +07:00 and it was not recognize as datetime by Pandas. how can I create new column with column name creation_date and add the correct datetime format.

i tried this but error


data = 'data.xlsx'
df = pd.read_excel(data)

df['creation_date'] = pd.to_datetime(df['date'].str[:2] + '-' + df['date'].str[4:2] + '-' + df['date'].str[7:4])

print(df)

i want the format to be recognized in excel which is 12/28/2019 08:13:00 PM. +1 hour since my GMT is +8

What do you mean by `089:13:00 PM`? – Vishal A. Feb 12 '22 at 05:58 — Vishal A., Feb 12 '22 at 05:58
ah my bad, i mean ```08:13:00 PM``` – AK555666 Feb 12 '22 at 06:17 — AK555666, Feb 12 '22 at 06:17

score 1 · Accepted Answer · answered Feb 12 '22 at 06:57

I've written the below solution for a single string. In your case, you'll need to adapt it for your whole data frame column.

First of all, you need to convert the string containing date and time to a datetime object.

You can do this using following code:

import datetime
Date = "28-12-2019 19:13 +07:00"
datetimeObj = datetime.datetime.strptime(Date, "%d-%m-%Y %H:%M %z")

Which will give you a datetime object like this:

datetime.datetime(2019, 12, 28, 19, 13, tzinfo=datetime.timezone(datetime.timedelta(seconds=25200)))

The next thing you'll need to do is, convert this datetime object to your preferred timezone.

For that you can use the following code:

datetimeObj.astimezone(ZoneInfo('Europe/London'))

Note:

I've considered London for the sake of demonstration. You'll need to choose it accordingly.
The above solution will work only if you have Python 3.9+ installed. If you have any other version installed, check this answer.

The next thing you'll need to do is formatting the datetime object so that it represents date and time in a way you want.

datetimeObj = datetime.datetime.strftime(datetimeObj, "%m/%d/%Y %H:%M")

This will produce the following output:

'12/28/2019 12:13'

Thanks Man, that helps me alot. I follow your instruction and apply lambda to my series ```df['cerated_at'] = df['created_at'].apply(lambda x: datetime.datetime.strptime(x, '%d-%m-%Y %H:%M %z')).apply(lambda x: x.strftime('%M-%d-%Y %H:%M'))``` thank you — AK555666, Feb 12 '22 at 12:07

convert random string to datetime in python

1 Answers1