BUG: tz_convert doesn't work for multiple timezones

[user529481]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

I tried a simple example seen below, notice the timezone difference:

# This works
a = pd.to_datetime(['2022-11-04 16:59:00-04:00', '2022-11-06 18:00:00-04:00'], infer_datetime_format=True).tz_convert('UTC')

# This throws error: 'Index' object has no attribute 'tz_convert'
b = pd.to_datetime(['2022-11-04 16:59:00-04:00', '2022-11-06 18:00:00-05:00'], infer_datetime_format=True).tz_convert('UTC')

Issue Description

I'm trying to load a CSV file containing a time column and spans enough time that daylight savings come into effect.

Namely,

df['date'] = pd.to_datetime(df['date'], format='%Y-%m-%d %H:%M:%S%Z', errors='coerce')

When the time-zone argument is unchanged the above query takes less than a second to run for 200K records. But when I add one more record with a different timezone (from day light savings) the query takes around 6s and raises an error:

AttributeError: 'Index' object has no attribute 'tz_convert'

Expected Behavior

tz_convert should work for multiple timezones

Installed Versions

INSTALLED VERSIONS

commit : 87cfe4e
python : 3.10.9.final.0
python-bits : 64
OS : Darwin
OS-release : 22.5.0
Version : Darwin Kernel Version 22.5.0: Thu Jun 8 22:22:20 PDT 2023; root:xnu-8796.121.3~7/RELEASE_ARM64_T6000
machine : arm64
processor : arm
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.UTF-8

pandas : 1.5.0
numpy : 1.23.0
pytz : 2023.3
dateutil : 2.8.2
setuptools : 64.0.2
pip : 22.3.1
Cython : 0.29.32
pytest : 7.2.1
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.9.1
html5lib : 1.1
pymysql : None
psycopg2 : 2.9.6
jinja2 : 3.1.2
IPython : 8.5.0
pandas_datareader: 0.10.0
bs4 : 4.11.1
bottleneck : None
brotli : None
fastparquet : None
fsspec : 2022.11.0
gcsfs : None
matplotlib : 3.5.3
numba : 0.56.3
numexpr : 2.8.4
odfpy : None
openpyxl : 3.1.2
pandas_gbq : None
pyarrow : 9.0.0
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : 1.9.1
snappy : None
sqlalchemy : 1.4.42
tables : None
tabulate : 0.9.0
xarray : None
xlrd : None
xlwt : None
zstandard : None
tzdata : 2022.6

[MarcoGorelli]

thanks for your report - you're on an old version of pandas, could you upgrade to the latest release and try again please?

[MarcoGorelli]

ah I see the issue - a Series can only have a single time zone in its dtype

just set utc=True within to_datetime:

In [53]: pd.to_datetime(['2022-11-04 16:59:00-04:00', '2022-11-06 18:00:00-05:00'], utc=True)
Out[53]: DatetimeIndex(['2022-11-04 20:59:00+00:00', '2022-11-06 23:00:00+00:00'], dtype='datetime64[ns, UTC]', freq=None)

But when I add one more record with a different timezone (from day light savings) the query takes around 6s and raises an error:

yeah because this was never parsed as DatetimeIndex to begin with, it would have been parsed as an object Index where each element is a Timestamp, which has horrendous performance implications (such behaviour is now deprecated)

closing then, but please do let me know if there's an issue here which I didn't address and I'll reopen