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

CodePudding user response：

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:

1. I've considered London for the sake of demonstration. You'll need to choose it accordingly.
2. 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'