0.23.4 changed read_csv parsing for a mixed-timezone datetimes

[TomAugspurger]

Previously, a column in a CSV with mixed timezones would (I think) convert each value to UTC and discard the tzinfo.

import pandas as pd
import io

content = """\
Sat, 22 Apr 2017 15:11:58 -0500
Fri, 21 Apr 2017 14:20:57 -0500
Thu, 9 Mar 2017 11:15:30 -0600"""

df = pd.read_csv(io.StringIO(content), parse_dates=True, header=None, names=['day', 'datetime'], index_col='datetime')

On 0.23.4 that's

In [7]: df.index
Out[7]:
DatetimeIndex(['2017-04-22 20:11:58', '2017-04-21 19:20:57',
               '2017-03-09 17:15:30'],
              dtype='datetime64[ns]', name='datetime', freq=None)

On 0.24 that's

In [7]: df.index
Out[7]:
Index([2017-04-22 15:11:58-05:00, 2017-04-21 14:20:57-05:00,
       2017-03-09 11:15:30-06:00],
      dtype='object', name='datetime')

I'm not sure what the expected behavior is here, but I think the old behavior is as good as any.

I haven't verified, but #22380 seems like a likely candidate for introducing the change.

[TomAugspurger]

cc @jbrockmendel, @mroeschke, @gfyoung, @swyoon if you have thoughts here.

[mroeschke]

Appears this was an intentional change as described by the original issue: #22256 (comment)

Also AFAICT, the coercion to UTC behavior before was not documented in read_csv(?). In general, I am keen on preserving the distinct UTC offsets instead of coercing to UTC as it leads to less surprises.

[TomAugspurger]

I don't see anything in the original issue #22256 (comment) about mixed timezones.

So to clarify, what's the expected output of

In [8]: pd.read_csv(io.StringIO('a\n2000-01-01T00:00:00+06:00\n2000-01-01T00:00:00+05:00'), parse_dates=['a']).a

In 0.23.4, that's

Out[4]:
0   1999-12-31 18:00:00
1   1999-12-31 19:00:00
Name: a, dtype: datetime64[ns]

and in 0.24.0 that's

Out[8]:
0    2000-01-01 00:00:00+06:00
1    2000-01-01 00:00:00+05:00
Name: a, dtype: object

If we want the 0.24.0 behavior, do we have an alternative recommendation for "parse this mess into a column of datetimes"? Something like

pd.read_csv(..., date_parser=lambda x: pd.to_datetime(x, utc=True))

If that's the recommendation going forward, I can submit a PR updating the release note for 0.24 to note the breaking change, and an example with mixed timezones.

[jbrockmendel]

FWIW I see two upsides to not-converting to UTC. First, conversion is lossy; mixed timezones is a pretty weird special case and if it is intentional, we preserve it. Second, I'm pretty sure this is consistent with to_datetime's treatment of strings, which is a plus.

[TomAugspurger]

Actually, does date_parser operate row-wise? We probably don't want to recommend that. The alternative would be something like

date_columns = ['a']
df = pd.read_csv(...)
df[date_columns] = df[date_columns].apply(pd.to_datetime, utc=True)

so read it in as strings, and then convert later?

[TomAugspurger]

mixed timezones is a pretty weird special case and if it is intentional, we preserve it.

I would distinguish mixed timezones in a storage format like CSV from our internal representation. IMO, it's important to support easily ingesting this kind of data.

It does appear to be consistent with to_datetime

In [6]: pd.to_datetime(['2000-01-01T00:00:00+06:00', '2000-01-01T00:00:00+05:00'])
Out[6]: Index([2000-01-01 00:00:00+06:00, 2000-01-01 00:00:00+05:00], dtype='object')

In [7]: pd.to_datetime(['2000-01-01T00:00:00+06:00', '2000-01-01T00:00:00+05:00'], utc=True)
Out[7]: DatetimeIndex(['1999-12-31 18:00:00+00:00', '1999-12-31 19:00:00+00:00'], dtype='datetime64[ns, UTC]', freq=None)

[mroeschke]

Yeah I'd recommend post processing via apply (or to_datetime directly if it's just one column).

The to_datetime example above was recently fixed in 0.24.0 as well. http://pandas.pydata.org/pandas-docs/stable/whatsnew/v0.24.0.html#parsing-datetime-strings-with-timezone-offsets

[TomAugspurger]

Thanks. I'll link to that.

…

On Mon, Jan 28, 2019 at 3:52 PM Matthew Roeschke ***@***.***> wrote: Yeah I'd recommend post processing via apply (or to_datetime directly if it's just one column). The to_datetime example above was recently fixed in 0.24.0 as well. http://pandas.pydata.org/pandas-docs/stable/whatsnew/v0.24.0.html#parsing-datetime-strings-with-timezone-offsets — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#24987 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABQHIk_BnZg4v26cFTOLl6naOGkUqimVks5vH3E0gaJpZM4aWoOk> .

[gfyoung]

I'm a little late to the discussion, but I do agree with the general sentiment here. I think a documentation change will suffice.

Actually, does date_parser operate row-wise

@TomAugspurger : The behavior of date_parser varies depending on how parse_dates is passed in. However, IIUC it does not operate "row-wise" as I believe you are implying.

[TomAugspurger]

Thanks @gfyoung, that's the recommendation I went with in #24989.