In this post, we'll see how to convert datetime to the same timezone in Pandas DataFrame. You can also find how to solve errors like:

  • ValueError: Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True
  • AttributeError: Can only use .dt accessor with datetimelike values
  • TypeError: Cannot localize tz-aware Timestamp, use tz_convert for conversions

So at the end you will get:

2015-05-11 03:00:00-04:00 -> 2016-10-07 08:30:19.428748+00:00
2016-10-07 08:30:19.428748+0000 -> 2015-05-11 07:00:00+00:00

or any other time zone.

Below you can find the short answer:

(1) Convert the dates with utc=True

df['date'] = pd.to_datetime(df['date'], utc=True)

(2) Remove time zones

df['date'].dt.tz_localize(None)

**(3) apply + tz_localize(None) **

df['date'].apply(lambda x: pd.to_datetime(x).tz_localize(None))

Let's cover all examples in the next section.

Setup

Suppose we have DataFrame like:

import pandas as pd

dates = ['2016-10-07 08:30:19.428748+0000', '2016-10-07 08:30:19.428748+0000', '2015-05-11 03:00:00-04:00', '2015-05-10 22:00:00-05:00', '2015-05-10']
df = pd.DataFrame({'end_date': dates})

data:

end_date
0 2016-10-07 08:30:19.428748+0000
1 2016-10-07 08:30:19.428748+0000
2 2015-05-11 03:00:00-04:00
3 2015-05-10 22:00:00-05:00
4 2015-05-10

1: Convert datetime to the same time zone

To convert datetime to the same time zone we can use method: to_datetime():


df['date'] = pd.to_datetime(df['date'], utc=True)

After the conversion we will get:

0   2016-10-07 08:30:19.428748+00:00
1   2016-10-07 08:30:19.428748+00:00
2          2015-05-11 07:00:00+00:00
3          2015-05-11 03:00:00+00:00
4          2015-05-10 00:00:00+00:00
Name: date, dtype: datetime64[ns, UTC]

As you can see the the time is changed for the records 2 and 3:

2015-05-10 22:00:00-05:00 - > 2015-05-11 03:00:00+00:00

because of the change of the timezone.

2: Remove the time zone completely

If you like to complete the timezone from your column or DataFrame we can do:

d = pd.Series(['2019-09-24 08:30:00-07:00', '2019-10-07 16:00:00-04:00', '2019-10-04 16:30:00+02:00'])
d.apply(lambda x: pd.to_datetime(x).tz_localize(None))

In the case above we need to use it because:

  • pd.to_datetime(df['date']) - may raise error or give unexpected results
  • df['time_tz'].dt.tz_localize(None) - the column is not datetime

3: Convert datetime with lambda + tz_localize

In some cases pd.to_datetime() will try to convert the dates but the column type will remain the object.

For such cases we can use lambda for a custom conversion:

d = pd.Series(['2019-09-24 08:30:00-07:00', '2019-10-07 16:00:00-04:00', '2019-10-04 16:30:00+02:00'])
d = pd.to_datetime(d)

The converted Series is from time object - which is not useful for us (check the errors described at the end):

0    2019-09-24 08:30:00-07:00
1    2019-10-07 16:00:00-04:00
2    2019-10-04 16:30:00+02:00
dtype: object

To convert the column we can use combination of .tz_localize(None) and apply:

d.apply(lambda x: pd.to_datetime(x).tz_localize(None))

The result is datetime64:

0   2019-09-24 08:30:00
1   2019-10-07 16:00:00
2   2019-10-04 16:30:00
dtype: datetime64[ns]

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

Sometimes Pandas will not convert the column to datetime. In that case error:

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

is raised. The code below will raise the error:

d = pd.Series(['2019-09-24 08:30:00-07:00', '2019-10-07 16:00:00-04:00', '2019-10-04 16:30:00+02:00'])
d = pd.to_datetime(d)
d = pd.to_datetime(d)

To solve the error and convert the column to date and not object we can use:

d.apply(lambda x: pd.to_datetime(x).tz_localize(None))

AttributeError: Can only use .dt accessor with datetimelike values

This error is shown when you try to use the '.dt' accessor on a non date column. In that case be sure that the column is from type date.

We can simulate the error by:

pd.Series(['2000-10-01']).dt.date

and solve it with method: pd.to_datetime()

pd.to_datetime(pd.Series(['2000-10-01'])).dt.date

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

Finally let's cover the error:

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

The error is returned from:

from datetime import datetime, timezone, timedelta
d = datetime(2016, 5, 6, 12, tzinfo=timezone(-timedelta(hours=1)))
pd.to_datetime(["2016-05-14 19:22 -0100", d])

Conclusion

We saw how to convert datetime and string to the same time zone in Pandas. Several errors typical for Pandas and date time were explained and solved.

Finally we discussed alternative conversion when pd.to_datetime() is not working as expected.