pd.to_datetime returning string result (even with errors='raise')
Hello,
I've got a dataset and I can convert to naive time just fine, but I would like to include timezone information.



INSTALLED VERSIONS
------------------
commit           : 5f648bf1706dd75a9ca0d29f26eadfbb595fe52b
python           : 3.8.5.final.0
python-bits      : 64
OS               : Linux
OS-release       : 4.19.128-microsoft-standard
Version          : #1 SMP Tue Jun 23 12:58:10 UTC 2020
machine          : x86_64
processor        : x86_64
byteorder        : little
LC_ALL           : None
LANG             : C.UTF-8
LOCALE           : en_US.UTF-8

pandas           : 1.3.2
numpy            : 1.19.4
pytz             : 2020.4
dateutil         : 2.8.1
pip              : 20.0.2
setuptools       : 44.0.0
Cython           : None
pytest           : 5.4.3
hypothesis       : 6.8.1
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : 1.3.7
lxml.etree       : 4.6.2
html5lib         : 1.1
pymysql          : None
psycopg2         : None
jinja2           : 2.11.2
IPython          : 7.19.0
pandas_datareader: None
bs4              : None
bottleneck       : None
fsspec           : 2021.07.0
fastparquet      : None
gcsfs            : None
matplotlib       : 3.3.3
numexpr          : None
odfpy            : None
openpyxl         : 3.0.5
pandas_gbq       : None
pyarrow          : 4.0.0
pyxlsb           : None
s3fs             : None
scipy            : 1.5.4
sqlalchemy       : 1.1.4
tables           : None
tabulate         : None
xarray           : 0.18.2
xlrd             : 2.0.1
xlwt             : None
numba            : 0.52.0

### Data
```
import pandas as pd
# https://nwis.waterdata.usgs.gov/usa/nwis/uv/?cb_00060=on&cb_00065=on&format=rdb&site_no=09333500&period=&begin_date=2000-01-01&end_date=2020-09-28
url = 'https://github.com/mattharrison/datasets/raw/master/data/dirtydevil.txt'
df = pd.read_csv(url, skiprows=lambda num: num <34 or num == 35, sep='\t')
```

The data has a *datetime* column and and a *tz_cd* column. I would like to combine them to get tz specific dates.

|    | agency_cd   |   site_no | datetime         | tz_cd   |   144166_00060 | 144166_00060_cd   |   144167_00065 |   144167_00065_cd |
|---:|:------------|----------:|:-----------------|:--------|---------------:|:------------------|---------------:|------------------:|
|  0 | USGS        |   9333500 | 2001-05-07 01:00 | MDT     |             71 | A:[91]            |            nan |               nan |
|  1 | USGS        |   9333500 | 2001-05-07 01:15 | MDT     |             71 | A:[91]            |            nan |               nan |
|  2 | USGS        |   9333500 | 2001-05-07 01:30 | MDT     |             71 | A:[91]            |            nan |               nan |
|  3 | USGS        |   9333500 | 2001-05-07 01:45 | MDT     |             70 | A:[91]            |            nan |               nan |
|  4 | USGS        |   9333500 | 2001-05-07 02:00 | MDT     |             70 | A:[91]            |            nan |               nan |

### Attempt 1

```pd.to_datetime((df.datetime+df.tz_cd).iloc[:5000])```

Appears to work, but takes a LONG time (hence the ``.iloc`` and this issue).

*Edit* My machine takes 2min 3s to convert all of the column (539,305 entries) to TZ aware. However, just converting to naive datetime takes 54ms! My attempts below are trying to lower that gap.

### Attempt 2 - Use ``format`` string

``pd.to_datetime((df.datetime+df.tz_cd).iloc[8730:8736],format='%Y-%m-%d %H:%M%Z', errors='raise')``

Fails with the exception:

```---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
~/envs/menv/lib/python3.8/site-packages/pandas/core/tools/datetimes.py in _to_datetime_with_format(arg, orig_arg, name, tz, fmt, exact, errors, infer_datetime_format)
    508         try:
--> 509             values, tz = conversion.datetime_to_datetime64(arg)
    510             dta = DatetimeArray(values, dtype=tz_to_dtype(tz))

~/envs/menv/lib/python3.8/site-packages/pandas/_libs/tslibs/conversion.pyx in pandas._libs.tslibs.conversion.datetime_to_datetime64()

TypeError: Unrecognized value type: <class 'str'>

During handling of the above exception, another exception occurred:

ValueError                                Traceback (most recent call last)
<ipython-input-5-853da2332591> in <module>
      1 # Can't use Z to speed up
----> 2 pd.to_datetime((df.datetime+df.tz_cd).iloc[8730:8736],format='%Y-%m-%d %H:%M%Z', errors='raise')

~/envs/menv/lib/python3.8/site-packages/pandas/core/tools/datetimes.py in to_datetime(arg, errors, dayfirst, yearfirst, utc, format, exact, unit, infer_datetime_format, origin, cache)
    885             result = arg.map(cache_array)
    886         else:
--> 887             values = convert_listlike(arg._values, format)
    888             result = arg._constructor(values, index=arg.index, name=arg.name)
    889     elif isinstance(arg, (ABCDataFrame, abc.MutableMapping)):

~/envs/menv/lib/python3.8/site-packages/pandas/core/tools/datetimes.py in _convert_listlike_datetimes(arg, format, name, tz, unit, errors, infer_datetime_format, dayfirst, yearfirst, exact)
    391 
    392     if format is not None:
--> 393         res = _to_datetime_with_format(
    394             arg, orig_arg, name, tz, format, exact, errors, infer_datetime_format
    395         )

~/envs/menv/lib/python3.8/site-packages/pandas/core/tools/datetimes.py in _to_datetime_with_format(arg, orig_arg, name, tz, fmt, exact, errors, infer_datetime_format)
    511             return DatetimeIndex._simple_new(dta, name=name)
    512         except (ValueError, TypeError):
--> 513             raise err
    514 
    515 

~/envs/menv/lib/python3.8/site-packages/pandas/core/tools/datetimes.py in _to_datetime_with_format(arg, orig_arg, name, tz, fmt, exact, errors, infer_datetime_format)
    498 
    499         # fallback
--> 500         res = _array_strptime_with_fallback(
    501             arg, name, tz, fmt, exact, errors, infer_datetime_format
    502         )

~/envs/menv/lib/python3.8/site-packages/pandas/core/tools/datetimes.py in _array_strptime_with_fallback(arg, name, tz, fmt, exact, errors, infer_datetime_format)
    434 
    435     try:
--> 436         result, timezones = array_strptime(arg, fmt, exact=exact, errors=errors)
    437         if "%Z" in fmt or "%z" in fmt:
    438             return _return_parsed_timezone_results(result, timezones, tz, name)

~/envs/menv/lib/python3.8/site-packages/pandas/_libs/tslibs/strptime.pyx in pandas._libs.tslibs.strptime.array_strptime()

ValueError: time data '2001-10-27 23:30MDT' does not match format '%Y-%m-%d %H:%M%Z' (match)
```

I've scoured the web and cannot find any examples of parsing dates using the the TZ names that work. Ideas?
*EDIT* - I'll anwser my own question here. For TZ names, you need to use what `pytz` supports. The following code get close, but fails because I have "ambiguous" dates and need to pass an `ambiguous` parameter at a lower level, but the parameter isn't exposed. See Attempt 4 for different option that exposes `ambiguous`
```
pd.to_datetime((df.datetime+df.tz_cd.str.replace('MDT', 'MST7MDT')),format='%Y-%m-%d %H:%M%Z', errors='raise')
```

### Attempt 3 - Convert TZ names to offsets

This appears to work

```
pd.to_datetime((df.datetime +  df.tz_cd.str.replace('MST', '-07:00').str.replace('MDT', '-06:00')).iloc[8730:8736],
              format='%Y-%m-%d %H:%M%z', errors='raise')
```

However, when I change the offsets, it fails (fails in that it returns strings and doesn't raise an error!):

```
# Fails with -07:00
pd.to_datetime((df.datetime + df.tz_cd.str.replace('MST', '-07:00').str.replace('MDT', '-06:00')).iloc[8730:8737],
              format='%Y-%m-%d %H:%M%z', errors='raise')
```

Here is the output:
```
8730    2001-10-27 23:30:00-06:00
8731    2001-10-27 23:45:00-06:00
8732    2001-10-28 00:00:00-06:00
8733    2001-10-28 00:15:00-06:00
8734    2001-10-28 00:30:00-06:00
8735    2001-10-28 00:45:00-06:00
8736    2001-10-28 01:00:00-07:00
dtype: object
```

### (EDIT) Attempt 4 - @jreback's advice to use `.transform`
I was pointed to https://stackoverflow.com/questions/47822265/efficiently-convert-timezones-in-pandas-dataframe which suggested this code
```
(df
    .assign(datetime=pd.to_datetime(df.datetime))
    .iloc[8730:8737]  # works with end slice 8736
    .groupby('tz_cd')
    .datetime
    .transform(lambda g: g.dt.tz_localize(g.name.replace('MDT', 'MST7MDT')))#, ambiguous=False))
)
```

This runs much quicker (about 3-5 sec on the whole dataset), but also fails and gives the same result as attempt 3, a series with strings instead of objects.


### Summary

I would like to quickly and consistently parse tz information. Would appreciate any insight or advice. (Also, shouldn't ``errors='raise'`` cause an Exception in this case?)

Thanks much! 🙏

