Mathew K Analytics

Lesson 39 · Pandas Full Course

Handle Time Zones in Pandas: tz_localize, tz_convert and Daylight Saving | Pandas Course #39

In this lesson you'll be able to: Tell naive timestamps from time-zone-aware ones Attach a zone with tzlocalize and switch with tzconvert Survive daylight…

What you'll learn

Datasets used in this lesson

Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.

📓 Full notebook

Download .ipynb

Pandas 39: Handling Time Zones and Offsets#

In this lesson you'll be able to:

  • Tell naive timestamps from time-zone-aware ones
  • Attach a zone with tz_localize and switch with tz_convert
  • Survive daylight saving: missing and repeated hours
  • Shift dates with Timedelta, DateOffset and business days
  • Line up data from several zones on one clock Data: a month of New York taxi trips, March 2019

Warm-up: trips by hour#

Lesson 38 grouped data by hours and days. Same taxis, same idea.

import pandas as pd
taxis = pd.read_csv("data/pandas/taxis.csv",
                    parse_dates=["pickup", "dropoff"])
by_hour = taxis.groupby(taxis["pickup"].dt.hour).size()
print(by_hour.idxmax(), by_hour.max())
18 417
daily = taxis.groupby(taxis["pickup"].dt.date).size()
print(daily.idxmax(), daily.max())
2019-03-14 260

Naive and aware timestamps#

Problem: a time with no zone could be anywhere on Earth.

first = taxis["pickup"].iloc[0]
print(first)
print(first.tz)
2019-03-23 20:21:09
None

Quick check: does the whole column have a time zone?

print(taxis["pickup"].dt.tz)
None
ny = taxis["pickup"].dt.tz_localize("America/New_York")
ny.head(3)
0   2019-03-23 20:21:09-04:00
1   2019-03-04 16:11:55-05:00
2   2019-03-27 17:53:01-04:00
Name: pickup, dtype: datetime64[us, America/New_York]
print(ny.dt.tz)
print(ny.iloc[0].strftime("%Z %z"))
print(ny.iloc[1].strftime("%Z %z"))
America/New_York
EDT -0400
EST -0500

Common mistake: comparing aware times with a naive one

ny >= pd.Timestamp("2019-03-25")
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\datetimelike.py:555, in DatetimeLikeArrayMixin._validate_comparison_value(self, other)
    554 try:
--> 555     self._check_compatible_with(other)
    556 except TypeError as err:
    557     # e.g. tzawareness mismatch

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\datetimes.py:557, in DatetimeArray._check_compatible_with(self, other)
    556     return
--> 557 self._assert_tzawareness_compat(other)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\datetimes.py:800, in DatetimeArray._assert_tzawareness_compat(self, other)
    799 elif other_tz is None:
--> 800     raise TypeError(
    801         "Cannot compare tz-naive and tz-aware datetime-like objects"
    802     )

TypeError: Cannot compare tz-naive and tz-aware datetime-like objects

The above exception was the direct cause of the following exception:

InvalidComparison                         Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\datetimelike.py:991, in DatetimeLikeArrayMixin._cmp_method(self, other, op)
    990 try:
--> 991     other = self._validate_comparison_value(other)
    992 except InvalidComparison:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\datetimelike.py:558, in DatetimeLikeArrayMixin._validate_comparison_value(self, other)
    556     except TypeError as err:
    557         # e.g. tzawareness mismatch
--> 558         raise InvalidComparison(other) from err
    560 elif not is_list_like(other):

InvalidComparison: 2019-03-25 00:00:00

During handling of the above exception, another exception occurred:

TypeError                                 Traceback (most recent call last)
Cell In[8], line 1
----> 1 ny >= pd.Timestamp("2019-03-25")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\ops\common.py:85, in _unpack_zerodim_and_defer.<locals>.new_method(self, other)
     82     other = sanitize_array(other, None)
     83     other = ensure_wrapped_if_datetimelike(other)
---> 85 return method(self, other)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arraylike.py:62, in OpsMixin.__ge__(self, other)
     60 @unpack_zerodim_and_defer("__ge__")
     61 def __ge__(self, other):
---> 62     return self._cmp_method(other, operator.ge)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\series.py:6735, in Series._cmp_method(self, other, op)
   6732 lvalues = self._values
   6733 rvalues = extract_array(other, extract_numpy=True, extract_range=True)
-> 6735 res_values = ops.comparison_op(lvalues, rvalues, op)
   6737 return self._construct_result(res_values, name=res_name, other=other)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\ops\array_ops.py:341, in comparison_op(left, right, op)
    332         raise ValueError(
    333             "Lengths must match to compare", lvalues.shape, rvalues.shape
    334         )
    336 if should_extension_dispatch(lvalues, rvalues) or (
    337     (isinstance(rvalues, (Timedelta, BaseOffset, Timestamp)) or right is NaT)
    338     and lvalues.dtype != object
    339 ):
    340     # Call the method on lvalues
--> 341     res_values = op(lvalues, rvalues)
    343 # TODO: but not pd.NA?
    344 elif (is_scalar(rvalues) or rvalues_is_zerodim) and isna(rvalues):
    345     # numpy does not like comparisons vs None

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\ops\common.py:85, in _unpack_zerodim_and_defer.<locals>.new_method(self, other)
     82     other = sanitize_array(other, None)
     83     other = ensure_wrapped_if_datetimelike(other)
---> 85 return method(self, other)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arraylike.py:62, in OpsMixin.__ge__(self, other)
     60 @unpack_zerodim_and_defer("__ge__")
     61 def __ge__(self, other):
---> 62     return self._cmp_method(other, operator.ge)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\datetimelike.py:995, in DatetimeLikeArrayMixin._cmp_method(self, other, op)
    993     if hasattr(other, "dtype") and isinstance(other.dtype, ArrowDtype):
    994         return NotImplemented
--> 995     return invalid_comparison(self, other, op)
    997 dtype = getattr(other, "dtype", None)
    998 if is_object_dtype(dtype):
    999     # We have to use comp_method_OBJECT_ARRAY instead of numpy
   1000     #  comparison otherwise it would raise when comparing to None

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\ops\invalid.py:55, in invalid_comparison(left, right, op)
     53 else:
     54     typ = type(right).__name__
---> 55     raise TypeError(f"Invalid comparison between dtype={left.dtype} and {typ}")
     56 return res_values

TypeError: Invalid comparison between dtype=datetime64[us, America/New_York] and Timestamp
cut = pd.Timestamp("2019-03-25", tz="America/New_York")
late = ny >= cut
print(late.sum())
1381

Convert between zones with tz_convert#

Problem: show the same moment on another city's clock.

taxis["pickup_ny"] = ny
taxis["pickup_utc"] = ny.dt.tz_convert("UTC")
taxis[["pickup_ny", "pickup_utc"]].head(3)
pickup_ny pickup_utc
0 2019-03-23 20:21:09-04:00 2019-03-24 00:21:09+00:00
1 2019-03-04 16:11:55-05:00 2019-03-04 21:11:55+00:00
2 2019-03-27 17:53:01-04:00 2019-03-27 21:53:01+00:00
london = ny.dt.tz_convert("Europe/London")
tokyo = ny.dt.tz_convert("Asia/Tokyo")
print(london.iloc[0])
print(tokyo.iloc[0])
2019-03-24 00:21:09+00:00
2019-03-24 09:21:09+09:00
rush = taxis["pickup"].dt.hour == 18
tokyo[rush].dt.hour.value_counts()
pickup
7    272
8    145
Name: count, dtype: int64

Quick check: are the New York and UTC columns equal?

same = taxis["pickup_ny"] == taxis["pickup_utc"]
print(same.all())
True

Common mistake: localising a column that already has a zone

taxis["pickup_utc"].dt.tz_localize("Europe/London")
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[14], line 1
----> 1 taxis["pickup_utc"].dt.tz_localize("Europe/London")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\accessor.py:127, in PandasDelegate._add_delegate_accessors.<locals>._create_delegator_method.<locals>.f(self, *args, **kwargs)
    125 @functools.wraps(method)
    126 def f(self, *args, **kwargs):
--> 127     return self._delegate_method(name, *args, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\accessors.py:126, in Properties._delegate_method(self, name, *args, **kwargs)
    123 values = self._get_values()
    125 method = getattr(values, name)
--> 126 result = method(*args, **kwargs)
    128 if not is_list_like(result):
    129     return result

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\datetimes.py:543, in DatetimeIndex.tz_localize(self, tz, ambiguous, nonexistent)
    398 def tz_localize(
    399     self,
    400     tz,
    401     ambiguous: TimeAmbiguous = "raise",
    402     nonexistent: TimeNonexistent = "raise",
    403 ) -> Self:
    404     """
    405     Localize tz-naive Datetime Array/Index to tz-aware Datetime Array/Index.
    406 
   (...)
    541     dtype: datetime64[ns, Europe/Warsaw]
    542     """  # noqa: E501
--> 543     arr = self._data.tz_localize(tz, ambiguous, nonexistent)
    544     return type(self)._simple_new(arr, name=self.name)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\_mixins.py:83, in ravel_compat.<locals>.method(self, *args, **kwargs)
     80 @wraps(meth)
     81 def method(self, *args, **kwargs):
     82     if self.ndim == 1:
---> 83         return meth(self, *args, **kwargs)
     85     flags = self._ndarray.flags
     86     flat = self.ravel("K")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\datetimes.py:1102, in DatetimeArray.tz_localize(self, tz, ambiguous, nonexistent)
   1100         new_dates = tz_convert_from_utc(self.asi8, self.tz, reso=self._creso)
   1101     else:
-> 1102         raise TypeError("Already tz-aware, use tz_convert to convert.")
   1103 else:
   1104     tz = timezones.maybe_get_tz(tz)

TypeError: Already tz-aware, use tz_convert to convert.
taxis["pickup_utc"].dt.tz_convert("Europe/London").head(3)
0   2019-03-24 00:21:09+00:00
1   2019-03-04 21:11:55+00:00
2   2019-03-27 21:53:01+00:00
Name: pickup_utc, dtype: datetime64[us, Europe/London]
wall = taxis["pickup_utc"].dt.tz_localize(None)
print(wall.dt.tz)
print(wall.iloc[0])
None
2019-03-24 00:21:09

Your turn#

Make the drop-off times aware in New York, then show them in Tokyo.

drop = taxis["dropoff"].dt.tz_localize("America/New_York")
drop.dt.tz_convert("Asia/Tokyo").head(3)
0   2019-03-24 09:27:24+09:00
1   2019-03-05 06:19:00+09:00
2   2019-03-28 07:00:25+09:00
Name: dropoff, dtype: datetime64[us, Asia/Tokyo]

Daylight saving: missing and repeated hours#

Problem: clocks jump, so some local times never happen.

Quick check: any pickups between 2 and 3 a.m. on 10 March?

night = taxis["pickup"].between("2019-03-10 01:00",
                                "2019-03-10 04:00")
taxis.loc[night, "pickup"].sort_values()
2717   2019-03-10 01:01:50
5831   2019-03-10 01:10:49
3      2019-03-10 01:23:59
4797   2019-03-10 01:43:09
1089   2019-03-10 01:43:32
1255   2019-03-10 01:43:34
249    2019-03-10 01:44:58
6116   2019-03-10 03:07:25
1599   2019-03-10 03:17:59
2410   2019-03-10 03:33:21
5305   2019-03-10 03:38:25
353    2019-03-10 03:41:47
3319   2019-03-10 03:58:44
Name: pickup, dtype: datetime64[us]
offset = taxis["pickup_ny"].dt.strftime("%z")
offset.value_counts()
pickup_ny
-0400    4493
-0500    1940
Name: count, dtype: int64

Common mistake: localising a time that never existed

pd.Timestamp("2019-03-10 02:30").tz_localize("America/New_York")
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[20], line 1
----> 1 pd.Timestamp("2019-03-10 02:30").tz_localize("America/New_York")

File pandas/_libs/tslibs/timestamps.pyx:3212, in pandas._libs.tslibs.timestamps.Timestamp.tz_localize()

File pandas/_libs/tslibs/tzconversion.pyx:200, in pandas._libs.tslibs.tzconversion.tz_localize_to_utc_single()

File pandas/_libs/tslibs/tzconversion.pyx:442, in pandas._libs.tslibs.tzconversion.tz_localize_to_utc()

ValueError: 2019-03-10 02:30:00 is a nonexistent time due to daylight savings time. Try using the 'nonexistent' argument.
gap = pd.Timestamp("2019-03-10 02:30")
print(gap.tz_localize("America/New_York",
                      nonexistent="shift_forward"))
2019-03-10 03:00:00-04:00

Quick check: in November the clocks go back. Which 01:30 is it?

autumn = pd.Timestamp("2019-11-03 01:30")
print(autumn.tz_localize("America/New_York", ambiguous=True))
print(autumn.tz_localize("America/New_York", ambiguous=False))
2019-11-03 01:30:00-04:00
2019-11-03 01:30:00-05:00
days = pd.date_range("2019-03-09", periods=3, freq="D",
                     tz="America/New_York")
print(days.to_series().diff())
2019-03-09 00:00:00-05:00               NaT
2019-03-10 00:00:00-05:00   1 days 00:00:00
2019-03-11 00:00:00-04:00   0 days 23:00:00
Freq: D, dtype: timedelta64[us]

Your turn#

How many hours ahead of New York was London for each trip?

ldn = ny.dt.tz_convert("Europe/London").dt.tz_localize(None)
ahead = ldn - ny.dt.tz_localize(None)
ahead.value_counts()
pickup
0 days 04:00:00    4267
0 days 05:00:00    2166
Name: count, dtype: int64

Shift dates with offsets#

Problem: 'one day later' can mean 24 hours or the same time tomorrow.

Quick check: noon on 9 March, plus one day. Noon on the 10th?

sat = pd.Timestamp("2019-03-09 12:00", tz="America/New_York")
print(sat + pd.Timedelta(days=1))
print(sat + pd.DateOffset(days=1))
2019-03-10 13:00:00-04:00
2019-03-10 12:00:00-04:00
fri = taxis.loc[taxis["pickup"].dt.dayofweek == 4, "pickup"]
ride = fri.iloc[0]
print(ride.day_name(), ride)
print(ride + pd.offsets.BDay(1))
Friday 2019-03-22 12:47:13
2019-03-25 12:47:13
end = taxis["pickup_ny"] + pd.offsets.MonthEnd(0)
nxt = taxis["pickup_ny"] + pd.offsets.MonthBegin(1)
print(end.iloc[0], nxt.iloc[0], sep="\n")
2019-03-31 20:21:09-04:00
2019-04-01 20:21:09-04:00
monday = taxis["pickup"] + pd.offsets.Week(weekday=0)
print(monday.head(3))
print(monday.dt.day_name().value_counts())
0   2019-03-25 20:21:09
1   2019-03-11 16:11:55
2   2019-04-01 17:53:01
Name: pickup, dtype: datetime64[us]
pickup
Monday    6433
Name: count, dtype: int64
start = taxis["pickup_ny"].dt.floor("D")
(taxis["pickup_ny"] - start).head(3)
0   0 days 20:21:09
1   0 days 16:11:55
2   0 days 17:53:01
Name: pickup_ny, dtype: timedelta64[us]

Your turn#

Invoices are due 5 business days after a trip. When is the first one due?

due = taxis["pickup"] + pd.offsets.BDay(5)
pd.DataFrame({"trip": taxis["pickup"], "due": due}).head(3)
trip due
0 2019-03-23 20:21:09 2019-03-29 20:21:09
1 2019-03-04 16:11:55 2019-03-11 16:11:55
2 2019-03-27 17:53:01 2019-04-03 17:53:01

Line up data from several zones#

Problem: records from different cities must share one clock.

dep = pd.Timestamp("2019-03-25 20:00", tz="America/New_York")
arr = pd.Timestamp("2019-03-26 07:05", tz="Europe/London")
print(arr - dep)
0 days 07:05:00
both = pd.Series([dep, arr])
print(both.dtype)
print(pd.to_datetime(both, utc=True))
object
0   2019-03-26 00:00:00+00:00
1   2019-03-26 07:05:00+00:00
dtype: datetime64[us, UTC]

Common mistake: parsing text with mixed UTC offsets

stamps = ["2019-03-25 20:00-04:00", "2019-03-26 07:05+00:00"]
pd.to_datetime(stamps)
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[33], line 2
      1 stamps = ["2019-03-25 20:00-04:00", "2019-03-26 07:05+00:00"]
----> 2 pd.to_datetime(stamps)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\tools\datetimes.py:1072, in to_datetime(arg, errors, dayfirst, yearfirst, utc, format, exact, unit, origin, cache)
   1070         result = _convert_and_box_cache(argc, cache_array)
   1071     else:
-> 1072         result = convert_listlike(argc, format)
   1073 else:
   1074     result = convert_listlike(np.array([arg]), format)[0]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\tools\datetimes.py:435, in _convert_listlike_datetimes(arg, format, name, utc, unit, errors, dayfirst, yearfirst, exact)
    433 # `format` could be inferred, or user didn't ask for mixed-format parsing.
    434 if format is not None and format != "mixed":
--> 435     return _array_strptime_with_fallback(arg, name, utc, format, exact, errors)
    437 result, tz_parsed = objects_to_datetime64(
    438     arg,
    439     dayfirst=dayfirst,
   (...)
    443     allow_object=True,
    444 )
    446 if tz_parsed is not None:
    447     # We can take a shortcut since the datetime64 numpy array
    448     # is in UTC

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\tools\datetimes.py:470, in _array_strptime_with_fallback(arg, name, utc, fmt, exact, errors)
    459 def _array_strptime_with_fallback(
    460     arg,
    461     name,
   (...)
    465     errors: str,
    466 ) -> Index:
    467     """
    468     Call array_strptime, with fallback behavior depending on 'errors'.
    469     """
--> 470     result, tz_out = array_strptime(arg, fmt, exact=exact, errors=errors, utc=utc)
    471     if tz_out is not None:
    472         unit = np.datetime_data(result.dtype)[0]

File pandas/_libs/tslibs/strptime.pyx:567, in pandas._libs.tslibs.strptime.array_strptime()

File pandas/_libs/tslibs/strptime.pyx:318, in pandas._libs.tslibs.strptime.DatetimeParseState.check_for_mixed_inputs()

ValueError: Mixed timezones detected. Pass utc=True in to_datetime or tz='UTC' in DatetimeIndex to convert to a common timezone.
utc = pd.to_datetime(stamps, utc=True)
print(utc)
print(utc.tz_convert("Europe/London"))
DatetimeIndex(['2019-03-26 00:00:00+00:00', '2019-03-26 07:05:00+00:00'], dtype='datetime64[us, UTC]', freq=None)
DatetimeIndex(['2019-03-26 00:00:00+00:00', '2019-03-26 07:05:00+00:00'], dtype='datetime64[us, Europe/London]', freq=None)
by_utc = taxis.groupby(taxis["pickup_utc"].dt.hour).size()
print(by_utc.idxmax(), by_utc.max())
23 426

Your turn#

The flight lands at 07:05 in London. What time is it in New York?

print(arr.tz_convert("America/New_York"))
2019-03-26 03:05:00-04:00

Mini-project: would a UTC report change our story?#

Plan: count trips per day on both clocks, measure the shift, chart it.

local = taxis.groupby(taxis["pickup_ny"].dt.date).size()
world = taxis.groupby(taxis["pickup_utc"].dt.date).size()
print(local.idxmax(), local.max())
print(world.idxmax(), world.max())
2019-03-14 260
2019-03-14 269
ny_day = taxis["pickup_ny"].dt.date
moved = ny_day != taxis["pickup_utc"].dt.date
print(moved.sum(), round(moved.mean(), 3))
1465 0.228
hours = taxis.loc[moved, "pickup_ny"].dt.hour
print(hours.min(), hours.max())
19 23
hourly = pd.DataFrame({"New York": by_hour, "UTC": by_utc})
hourly.loc[17:23]
New York UTC
17 388 315
18 417 361
19 406 334
20 368 333
21 355 357
22 321 399
23 296 426
both_days = pd.DataFrame({"New York": local, "UTC": world})
both_days.tail(3)
New York UTC
2019-03-30 215.0 217.0
2019-03-31 187.0 210.0
2019-04-01 NaN 26.0
change = (world - local).dropna()
print(change.idxmin(), change.min())
print(change.idxmax(), change.max())
2019-03-01 -71.0
2019-03-10 34.0
import matplotlib.pyplot as plt
ax = both_days.plot(figsize=(9, 4), marker="o", markersize=3)
ax.set_title("Taxi trips per day: New York vs UTC calendar")
ax.set_xlabel("Date")
ax.set_ylabel("Trips")
plt.show()
No description has been provided for this image
share = moved.mean()
print(f"{share:.0%} of trips change date in UTC; "
      f"rush hour moves from {by_hour.idxmax()}:00 "
      f"to {by_utc.idxmax()}:00.")
23% of trips change date in UTC; rush hour moves from 18:00 to 23:00.

Recap#

  • Naive times have no zone; aware times know theirs
  • tz_localize attaches a zone once; tz_convert switches clocks
  • Daylight saving skips and repeats hours: use nonexistent and ambiguous
  • Timedelta adds exact time; DateOffset and BDay follow the calendar
  • Mixed zones? Parse with utc=True and convert at the end

Challenges#

  1. Convert the drop-offs to UTC and find the busiest UTC day.
  2. Count taxi trips per hour on a Tokyo clock.
  3. Add 3 business days to every Friday pickup. Which weekday is it?

What's next#

Lesson 40: Quick Visualizations using Pandas plot() Turn any Series or DataFrame into a chart in one line.

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.