How can I convert my datetime column in pandas all to the same timezone

Question

I have a dataframe with a DataTime column (with Timezone in different formats). It appears like timezone is UTC but I want to convert the column to pd.to_datetime and that is failing. That is problem #1. Since that fails I cannot do any datetime operations on the time period such as group the column by date / figure out the days / group by hour of the day and so on. Here's my dataframe df_res

    DateTime
    2017-11-02 19:49:28-07:00
    2017-11-27 07:32:22-08:00
    2017-12-27 17:01:15-08:00

OUTPUT for the command

      df_res["DateTime"] = df_res["DateTime"].dt.tz_convert('America/New_York')

AttributeError: Can only use .dt accessor with datetimelike values

WHen I convert to datetime

   df_res['DateTime'] = pd.to_datetime(df_res['DateTime'])

ValueError: Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True

I feel I am going around in circles. I need to convert the column to datetime in order to perform operations & in order to do that I need to have them all the same timezone but I cannot have the same timezone unless it is a datetime object so how can I best approach this. I did refer to previous postings but they seem to convert to datetime as easily as possible:

Convert datetime columns to a different timezone pandas Convert pandas timezone-aware DateTimeIndex to naive timestamp, but in certain timezone

How are you creating the "DateTime" column values in the first place? — benvc, Mar 27 '19 at 22:12

score 26 · Accepted Answer · answered May 06 '20 at 18:18

26

I think that it is not necessary to apply lambdas:

df_res['DateTime'] = pd.to_datetime(df_res['DateTime'], utc=True)

documentation: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.to_datetime.html

answered May 06 '20 at 18:18

JLM

548
1
5
8

7

This would only work if your datetimes are actually in UTC, right? – Dr_Zaszuś Dec 15 '20 at 15:48
This was seriously helpful line of code for me. Much appreciated – Ishwor Bhusal Jan 08 '21 at 08:12

score 7 · Answer 2 · answered Mar 27 '19 at 20:44

7

You can check this:

df = pd.DataFrame({
    'time': [
        '2017-11-02 19:49:28-08:00', 
        '2017-11-27 07:32:22-07:00', 
        '2017-12-27 17:01:15-07:00'
    ]
})

df['time'] = pd.to_datetime(df['time'])

df['time'].apply(lambda x: pd.to_datetime(x).tz_localize('US/Eastern'))

0   2017-11-03 03:49:28-04:00
1   2017-11-27 14:32:22-05:00
2   2017-12-28 00:01:15-05:00
Name: time, dtype: datetime64[ns, US/Eastern]

answered Mar 27 '19 at 20:44

CezarySzulc

1,664
1
13
26

1

Thanks .. And what if my dataframe has over a 10k+ datetime entries. The DateTime is of type `object` too and I need to convert them all? – py_noob Mar 28 '19 at 01:36
1

@py_noob: Maybe too late for you, I've met the same problem that after converting to datetime format, the column is of type `object`. However, when I check each line, they are all in datetime format. Its strange but I think it's not a problem, isn't it? – Anh-Thi DINH Feb 13 '20 at 09:02
2

In my case I needed to pass a `utf=True` arg to `pd.to_datetime()`, as in `df['time'] = pd.to_datetime(df['time'], utf=True)` – dustintheglass Aug 25 '20 at 19:21

How can I convert my datetime column in pandas all to the same timezone

2 Answers2

Linked