将佛罗里达EDT时区DataFrame转加州时区遇AmbiguousTimeError问题

问题背景 #

数据库中存储的加州数据采用佛罗里达夏令时（EDT）格式，需转换为加州时区（America/Los_Angeles）。运行转换代码时，在时间2023-11-05 01:00:00触发AmbiguousTimeError错误。

原始代码 #

import pandas as pd
from pytz import timezone
from datetime import datetime

# Sample DataFrame (ensure it is defined correctly in your script)
data = {
    'Datetime': pd.date_range(start='2023-11-04', periods=10, freq='H'),
    'Data': range(10)
}
invdf = pd.DataFrame(data)
invdf.set_index('Datetime', inplace=True)

# Time zone information
eastern = timezone('US/Eastern')

# Localize the datetime to Eastern time zone considering daylight saving time
invdf['Datetime'] = invdf.index
invdf['Datetime'] = invdf['Datetime'].dt.tz_localize('US/Eastern', ambiguous='infer')

# Convert to Los Angeles time (Pacific time)
invdf['Datetime'] = invdf['Datetime'].dt.tz_convert('America/Los_Angeles')

# Reset the index to updated datetime
invdf.set_index('Datetime', inplace=True, drop=True)

报错信息 #

---------------------------------------------------------------------------
AmbiguousTimeError                        Traceback (most recent call last)
Cell In[208], line 2
      1 invdf['Datetime'] = invdf.index
----> 2 invdf['Datetime'] = invdf['Datetime'].dt.tz_localize('US/Eastern', ambiguous='infer')
      3 # Convert to Los Angeles time (Pacific time)
      4 invdf['Datetime'] = invdf['Datetime'].dt.tz_convert('America/Los_Angeles')

File /opt/conda/lib/python3.10/site-packages/pandas/core/accessor.py:112, in PandasDelegate._add_delegate_accessors.<locals>._create_delegator_method.<locals>.f(self, *args, **kwargs)
    111 def f(self, *args, **kwargs):
--> 112     return self._delegate_method(name, *args, **kwargs)

File /opt/conda/lib/python3.10/site-packages/pandas/core/indexes/accessors.py:132, in Properties._delegate_method(self, name, *args, **kwargs)
    129 values = self._get_values()
    131 method = getattr(values, name)
--> 132 result = method(*args, **kwargs)
    134 if not is_list_like(result):
    135     return result

File /opt/conda/lib/python3.10/site-packages/pandas/core/indexes/datetimes.py:293, in DatetimeIndex.tz_localize(self, tz, ambiguous, nonexistent)
    286 @doc(DatetimeArray.tz_localize)
    287 def tz_localize(
    288     self,
   (...)
    291     nonexistent: TimeNonexistent = "raise",
    292 ) -> Self:
--> 293     arr = self._data.tz_localize(tz, ambiguous, nonexistent)
    294     return type(self)._simple_new(arr, name=self.name)

File /opt/conda/lib/python3.10/site-packages/pandas/core/arrays/_mixins.py:81, in ravel_compat.<locals>.method(self, *args, **kwargs)
     78 @wraps(meth)
     79 def method(self, *args, **kwargs):
     80     if self.ndim == 1:
--> 81         return meth(self, *args, **kwargs)
     83     flags = self._ndarray.flags
     84     flat = self.ravel("K")

File /opt/conda/lib/python3.10/site-packages/pandas/core/arrays/datetimes.py:1088, in DatetimeArray.tz_localize(self, tz, ambiguous, nonexistent)
   1085     tz = timezones.maybe_get_tz(tz)
   1086     # Convert to UTC
--> 1088     new_dates = tzconversion.tz_localize_to_utc(
   1089         self.asi8,
   1090         tz,
   1091         ambiguous=ambiguous,
   1092         nonexistent=nonexistent,
   1093         creso=self._creso,
   1094     )
   1095 new_dates_dt64 = new_dates.view(f"M8[{self.unit}]")
   1096 dtype = tz_to_dtype(tz, unit=self.unit)

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

File tzconversion.pyx:656, in pandas._libs.tslibs.tzconversion._get_dst_hours()

AmbiguousTimeError: 2023-11-05 01:00:00

错误原因 #

2023年11月5日是美国夏令时切换至冬令时的节点，美国东部时区（US/Eastern）会在当天凌晨2点将时钟回调至1点，导致**2023-11-05 01:00:00这个时间点实际出现两次**：一次属于夏令时（EDT，UTC-4），一次属于冬令时（EST，UTC-5）。

使用ambiguous='infer'参数时，pandas需要通过时间序列的连续性推断该时间的归属，但当前小时递增的序列无法提供足够的判断依据，因此抛出AmbiguousTimeError。

解决方案 #

方案1：明确指定模糊时间的处理规则 #

直接修改ambiguous参数，指定模糊时间的处理方式：

# 方式1：将模糊时间标记为缺失值（NaT）
invdf['Datetime'] = invdf['Datetime'].dt.tz_localize('US/Eastern', ambiguous='NaT')

# 方式2：手动指定模糊时间的时区类型
# 假设11月5日1点属于冬令时（EST），对应False；夏令时则设为True
ambiguous_flag = invdf['Datetime'] == datetime(2023, 11, 5, 1)
invdf['Datetime'] = invdf['Datetime'].dt.tz_localize('US/Eastern', ambiguous=~ambiguous_flag)

方案2：直接基于EDT时区本地化 #

由于原始数据以佛罗里达夏令时（EDT）存储，可直接绑定EDT时区（无需考虑夏令时切换），再转换至加州时区：

from pytz import EDT

# 直接将时间绑定为EDT时区
invdf['Datetime'] = invdf['Datetime'].dt.tz_localize(EDT)
# 转换为加州时区
invdf['Datetime'] = invdf['Datetime'].dt.tz_convert('America/Los_Angeles')

方案3：通过UTC中转转换 #

先将时间转换为UTC时区（无夏令时问题），再转至加州时区，绕开模糊时间的判断：

# 本地化时指定模糊时间的推断规则，同时处理不存在的时间
invdf['Datetime'] = invdf['Datetime'].dt.tz_localize('US/Eastern', ambiguous='infer', nonexistent='shift_forward')
# 转至UTC
invdf['Datetime'] = invdf['Datetime'].dt.tz_convert('UTC')
# 最终转换为加州时区
invdf['Datetime'] = invdf['Datetime'].dt.tz_convert('America/Los_Angeles')

内容的提问来源于stack exchange，提问作者Mainland