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 resultsdf['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.