Converting UTC Column into datetime in python pandas

Question

I would like to ask for little support. I have here a python frame containing data giving in UTC format. I would like to transform the column into date-format.

Order Date
15-Feb-2024 UTC
17-Feb-2024 UTC
18-Feb-2024 UTC
02-Apr-2024 UTC
05-Mar-2024 UTC
04-Mar-2024 UTC
11-Apr-2024 UTC
12-Apr-2024 UTC
16-Mar-2024 UTC
04-Apr-2024 UTC
05-Feb-2024 UTC
05-Mar-2024 UTC
14-Apr-2024 UTC


df["Order Date"]=pd.to_datetime(df["Order Date"],utc=True,format='%d-%b-%Y')

Applying the line above gives me the following error

time data "15-Feb-2024 UTC" doesn't match format "%d-%b-%Y", at position 0. You might want to try:
    - passing `format` if your strings have a consistent format;
    - passing `format='ISO8601'` if your strings are all ISO8601 but not necessarily in exactly the same format;
    - passing `format='mixed'`, and the format will be inferred for each element individually. You might want to use `dayfirst` alongside this.

I tried all the options, without success. Can someone tell me where the issue is? All I need is to transform the column to a date column. I'd be grateful for any support

Did you try format='%d-%b-%Y UTC', or format='%d-%b-%Y %Z'? — OldBoy
– OldBoy, Commented May 4, 2024 at 10:02
Yes, it simply gives the the same answer to test the options given in my first comment — SMS
– SMS, Commented May 4, 2024 at 10:05
I just tried both of those format strings and they worked correctly on "04-May-2024 UTC". — OldBoy
– OldBoy, Commented May 4, 2024 at 10:10

Panda Kim · Accepted Answer · 2024-05-04 10:11:05Z

1

Example Code

import pandas as pd
data = {'Order Date': ['15-Feb-2024 UTC', '17-Feb-2024 UTC', '18-Feb-2024 UTC', '02-Apr-2024 UTC', '05-Mar-2024 UTC']}
df = pd.DataFrame(data)

df

    Order Date
0   15-Feb-2024 UTC
1   17-Feb-2024 UTC
2   18-Feb-2024 UTC
3   02-Apr-2024 UTC
4   05-Mar-2024 UTC

Code

if you want convert to datetime64[ns, UTC]

out = pd.to_datetime(df["Order Date"], utc=True, format='%d-%b-%Y UTC')

out:

0   2024-02-15 00:00:00+00:00
1   2024-02-17 00:00:00+00:00
2   2024-02-18 00:00:00+00:00
3   2024-04-02 00:00:00+00:00
4   2024-03-05 00:00:00+00:00
Name: Order Date, dtype: datetime64[ns, UTC]

or if you want convert to datetime[ns]

out = pd.to_datetime(df['Order Date'].str.replace(' UTC', ''))

out:

0   2024-02-15
1   2024-02-17
2   2024-02-18
3   2024-04-02
4   2024-03-05
Name: Order Date, dtype: datetime64[ns]

answered May 4, 2024 at 10:11

Panda Kim

13.6k2 gold badges8 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting UTC Column into datetime in python pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related