API: add DatetimeBlockTZ #8260
State: closed

closes #8260 
closes #10763 

ToDos:
- [x] doc updates
- [x] test with `Series.dt.*`
- [x] test with csv/HDF5
- [x] nat setting borked ATM
- [x] HDF5 example from 0.16.2

~~\- [ ] get_values/values - make consistent~~
~~\- [ ] maybe move `DatetimeTZBlock.shift` mostly to `DatetimeIndex.shift`~~

Also
- This cleans up the internal blocks calling conventions a bit
- Fixes a bug in `DatetimeIndex` and localizing when NaT's are present

```
-------------------------------------------------------------------------------
Test name                                    | head[ms] | base[ms] |  ratio   |
-------------------------------------------------------------------------------
timestamp_tz_ops_diff1                       |   0.1804 | 159.4527 |   0.0011 | # note these are 10k element
timestamp_tz_ops_diff2                       |   2.5350 | 156.4047 |   0.0162 | # note these are 10k elements
timeseries_timestamp_downsample_mean         |   3.1467 |   3.3040 |   0.9524 |
timestamp_series_compare                     |   9.0797 |   9.1290 |   0.9946 |
timestamp_ops_diff2                          |  19.7570 |  19.6819 |   1.0038 | # this is 1M elements
series_timestamp_compare                     |   9.3430 |   9.0226 |   1.0355 |
timestamp_ops_diff1                          |   9.7457 |   9.0450 |   1.0775 | # this is 1M elements
-------------------------------------------------------------------------------
Test name                                    | head[ms] | base[ms] |  ratio   |
-------------------------------------------------------------------------------

Ratio < 1.0 means the target commit is faster then the baseline.
Seed used: 1234

Target [8502474] : API: add Block.make_block
API: add DatetimeBlockWithTZ #8260
Base   [16a44ad] : Merge pull request #10199 from jreback/gil
PERF: releasing the GIL, #8882
```

Demo

```
In [1]:   df = DataFrame({'A' : date_range('20130101',periods=3),
   ...:                    'B' : date_range('20130101',periods=3,tz='US/Eastern'),
   ...:                    'C' : date_range('20130101',periods=3,tz='CET')})

In [2]:    df
Out[2]: 
           A                   B                   C
0 2013-01-01 2013-01-01 05:00:00 2012-12-31 23:00:00
1 2013-01-02 2013-01-02 05:00:00 2013-01-01 23:00:00
2 2013-01-03 2013-01-03 05:00:00 2013-01-02 23:00:00

In [3]:    df.dtypes
Out[3]: 
A                datetime64[ns]
B    datetime64[ns, US/Eastern]
C           datetime64[ns, CET]
dtype: object

In [4]: df.B
Out[4]: 
0   2013-01-01 00:00:00-05:00
1   2013-01-02 00:00:00-05:00
2   2013-01-03 00:00:00-05:00
Name: B, dtype: datetime64[ns, US/Eastern]

In [5]: df.B.dt.tz_localize(None)
Out[5]: 
0   2013-01-01
1   2013-01-02
2   2013-01-03
dtype: datetime64[ns]
```


Labels:
name: Performance
description: Memory or execution speed performance
name: Internals
description: Related to non-user accessible pandas implementation
name: Timezones
description: Timezone data dtype

Comments:
cc @ssanderson 

Ooh, shiny!  I'll try this out tonight.

@jreback Nice! I will try to go through it one of the coming days

@jreback one quirk I'm noticing is that if you construct a DataFrame directly from a DatetimeIndex, you lose the tz information:

```
In [1]: dates = date_range('2014-01-01', periods=10, tz='UTC')

In [2]: from_dict = DataFrame({'a': dates})

In [3]: from_dict.dtypes
Out[3]:
a    datetime64[ns, UTC]
dtype: object

In [4]: from_index = DataFrame(dates)

In [5]: from_index.dtypes
Out[5]:
0    datetime64[ns]
dtype: object
```

Is this expected behavior?

Also surprising: if I take a series of dtype `datetime64[ns, UTC]` and call.values on it, I get a DatetimeIndex rather than an ndarray:

```
In [7]: from_dict['a'].values
Out[7]:
DatetimeIndex(['2014-01-01', '2014-01-02', '2014-01-03', '2014-01-04',
               '2014-01-05', '2014-01-06', '2014-01-07', '2014-01-08',
               '2014-01-09', '2014-01-10'],
              dtype='datetime64[ns]', freq='D', tz='UTC')
```

The result I'd expect here is what you actually get from doing

```
In [21]: from_dict['a'].values.values
Out[21]:
array(['2013-12-31T19:00:00.000000000-0500',
       '2014-01-01T19:00:00.000000000-0500',
       '2014-01-02T19:00:00.000000000-0500',
       '2014-01-03T19:00:00.000000000-0500',
       '2014-01-04T19:00:00.000000000-0500',
       '2014-01-05T19:00:00.000000000-0500',
       '2014-01-06T19:00:00.000000000-0500',
       '2014-01-07T19:00:00.000000000-0500',
       '2014-01-08T19:00:00.000000000-0500',
       '2014-01-09T19:00:00.000000000-0500'], dtype='datetime64[ns]')
```

do you have the current one?

this all works (it might not have in a prior one)

This is the latest https://github.com/jreback/pandas/commit/19ec61d3bcbe84dada3f38bad3daa061a78707af I think

```
In [3]: from_dict
Out[3]: 
           a
0 2014-01-01
1 2014-01-02
2 2014-01-03
3 2014-01-04
4 2014-01-05
5 2014-01-06
6 2014-01-07
7 2014-01-08
8 2014-01-09
9 2014-01-10

In [4]: from_dict.dtypes
Out[4]: 
a    datetime64[ns, UTC]
dtype: object
```

`Series.values` IS a `DatetimeIndex` that is how its implemented. Its very similar to how `Sparse/Categorical` are done. This preserves the tz info inside the object (actually have freq too). Rather than relying upon a ndarray impl and passing it around. Much cleaner this way.

I'm testing with a development install of this branch:

```
(pandas)[~/clones/pandas]@(tz:2422fe50fc)$ git log HEAD^..HEAD
commit 2422fe50fcc5aac541a0be8d67a4e86309b3e2ec
Author: Jeff Reback <jeff@reback.net>
Date:   Sat Jun 27 17:55:29 2015 -0400

    API: add DatetimeBlockTZ #8260
```

@ssanderson pretty old branch. pull my latest.

```
commit 757bbf92c926d4584d01bce419a576c7cb831fce
Author: Jeff Reback <jeff@reback.net>
Date:   Wed Jul 1 19:38:16 2015 -0400

    start on csv

commit 2422fe50fcc5aac541a0be8d67a4e86309b3e2ec
Author: Jeff Reback <jeff@reback.net>
Date:   Sat Jun 27 17:55:29 2015 -0400

    API: add DatetimeBlockTZ #8260
```

everthing works except for csv rounding tripping (not sure I can get it as can't repro the tz upon readback) but we'll see, and to/from hdf5 (but soon).

@ssanderson I have been amending that commit, so for sure update.

I pulled 2422fe50fcc5aac541a0be8d67a4e86309b3e2ec ~20 minutes ago.  The commit hash would change if the content had changed.

did you make? (it has a tad bit of cython code changes)

I did a `pip install -e .` in a fresh virtualenv.  What in the above that I posted is different from what you're seeing?

sorry, I didn't see what you meant above. `DataFrame(new_index)` hang on a sec. Didnt have a test for that.

The from_dict case is working as expected modulo the unexpected type of `.values`, which sounds like it's actually as-designed.  The one that I think is incorrect is direct construction from a DatetimeIndex.

very subtle path difference here....fixing

```
In [6]: DataFrame(date_range('20130101',periods=3,tz='US/Eastern')).dtypes
Out[6]: 
0    datetime64[ns]
dtype: object

In [7]: DataFrame(date_range('20130101',periods=3,tz='US/Eastern',name='foo')).dtypes
Out[7]: 
foo    datetime64[ns, US/Eastern]
dtype: object
```

@ssanderson fixed

if you have a chance I'd like to see how you are actually using it, if you could post some small sample code would be great. I can time things, but having a usecase is even better.

@jreback I think DataFrame indexing is broken with columns of tz-aware dtype:

```
In [2]: df = DataFrame({'a': date_range('2014-01-01', periods=10, tz='UTC')})

In [3]: df
Out[3]:
           a
0 2014-01-01
1 2014-01-02
2 2014-01-03
3 2014-01-04
4 2014-01-05
5 2014-01-06
6 2014-01-07
7 2014-01-08
8 2014-01-09
9 2014-01-10

In [4]: df.iloc[5]
---------------------------------------------------------------------------
IndexError                                Traceback (most recent call last)
<ipython-input-4-1d4dcfe8a425> in <module>()
----> 1 df.iloc[5]

/home/ssanderson/clones/pandas/pandas/core/indexing.pyc in __getitem__(self, key)
   1187             return self._getitem_tuple(key)
   1188         else:
-> 1189             return self._getitem_axis(key, axis=0)
   1190
   1191     def _getitem_axis(self, key, axis=0):

/home/ssanderson/clones/pandas/pandas/core/indexing.pyc in _getitem_axis(self, key, axis)
   1478                 self._is_valid_integer(key, axis)
   1479
-> 1480             return self._get_loc(key, axis=axis)
   1481
   1482     def _convert_to_indexer(self, obj, axis=0, is_setter=False):

/home/ssanderson/clones/pandas/pandas/core/indexing.pyc in _get_loc(self, key, axis)
     87
     88     def _get_loc(self, key, axis=0):
---> 89         return self.obj._ixs(key, axis=axis)
     90
     91     def _slice(self, obj, axis=0, kind=None):

/home/ssanderson/clones/pandas/pandas/core/frame.pyc in _ixs(self, i, axis)
   1727                     copy=True
   1728                 else:
-> 1729                     new_values = self._data.fast_xs(i)
   1730
   1731                     # if we are a copy, mark as such

/home/ssanderson/clones/pandas/pandas/core/internals.pyc in fast_xs(self, loc)
   2899         """
   2900         if len(self.blocks) == 1:
-> 2901             return self.blocks[0].values[:, loc]
   2902
   2903         items = self.items

/home/ssanderson/clones/pandas/pandas/tseries/base.pyc in __getitem__(self, key)
     93             attribs['freq'] = freq
     94
---> 95             result = getitem(key)
     96             if result.ndim > 1:
     97                 return result

IndexError: too many indices for array
```

hmm, let me look.

ok, indexing is fixed. wasn't testing things fully :mask: 

> if you have a chance I'd like to see how you are actually using it, if you could post some small sample code would be great. I can time things, but having a usecase is even better.

@jreback here's a slightly modified version of some code that's part of a recent [Zipline](https://github.com/quantopian/zipline) change where I'm computing a matrix of dates/security_ids where I want to know, for each (date, security) pair, whether the security existed on that date.  I'm currently using numpy recarrays of int64 for this because doing it with series would probably be too slow.

```
def lifetimes_matrix(dates):
    """
    Compute a DataFrame representing asset lifetimes for the specified date
    range.

    Parameters
    ----------
    dates : pd.DatetimeIndex
        The dates for which to compute lifetimes.

    Returns
    -------
    lifetimes : pd.DataFrame
        A frame of dtype bool with `dates` as index and an Int64Index of
        assets as columns.  The value at `lifetimes.loc[date, asset]` will
        be True iff `asset` existed on `data`.
    """
    lifetimes = np.recarray(
        shape=(len(),),
        dtype=[('sid', 'i8'), ('start', 'i8'), ('end', 'i8')],
    )
    # Fill in lifetimes with values from database...

    raw_dates = dates.asi8[:, None]
    mask = (lifetimes.start <= raw_dates) & (raw_dates <= lifetimes.end)
    return pd.DataFrame(mask, index=dates, columns=lifetimes.sid)
```

This is by no means unreasonable for me to do, but I suspect that lots of people who are comfortable with pandas wouldn't know that they should drop into numpy for something like this because of the overhead of doing all these comparisons when the inputs have dtype `object`.

I think @llllllllll has also run into performance issues using blaze with pandas and PostgreSQL because we store postgres data in columns of type TimestampWithTimezone, which causes blaze to do computations on DataFrames of dtype object after pulling data out of the DB.

@ssanderson ok, both of these should be 'blazing' fast now. (the postgresql might take a tiny bit more work as this is a change there).

Here's another 'issue'. csv's don't have enough information to reconstruct the tz's really at all. Well they can be done as fixed zones, but you rarely want that. So you have to know what you are going to. Have you thought about this?

```
In [7]: df = DataFrame({'A' : date_range('20130101',periods=3),
                                  'B' : date_range('20130101',periods=3,tz='US/Eastern'),
                                  'C' : date_range('20130101',periods=3,tz='CET')})

In [8]: df.iloc[1,1] = pd.NaT

In [9]: df.iloc[1,2] = pd.NaT

In [10]: df
Out[10]: 
           A                         B                         C
0 2013-01-01 2013-01-01 00:00:00-05:00 2013-01-01 00:00:00+01:00
1 2013-01-02                       NaT                       NaT
2 2013-01-03 2013-01-03 00:00:00-05:00 2013-01-03 00:00:00+01:00

In [11]: df.dtypes
Out[11]: 
A                datetime64[ns]
B    datetime64[ns, US/Eastern]
C           datetime64[ns, CET]
dtype: object

In [12]: df.to_csv('foo.csv')

In [13]: !cat foo.csv
,A,B,C
0,2013-01-01,2013-01-01 00:00:00-05:00,2013-01-01 00:00:00+01:00
1,2013-01-02,,
2,2013-01-03,2013-01-03 00:00:00-05:00,2013-01-03 00:00:00+01:00

In [14]: rdf = pd.read_csv('foo.csv',index_col=0)

In [15]: rdf
Out[15]: 
            A                          B                          C
0  2013-01-01  2013-01-01 00:00:00-05:00  2013-01-01 00:00:00+01:00
1  2013-01-02                        NaN                        NaN
2  2013-01-03  2013-01-03 00:00:00-05:00  2013-01-03 00:00:00+01:00

In [16]: rdf.dtypes
Out[16]: 
A    object
B    object
C    object
dtype: object

In [17]: pd.to_datetime(rdf['B']).dt.tz_localize('UTC').dt.tz_convert('US/Eastern')
Out[17]: 
0   2013-01-01 00:00:00-05:00
1                         NaT
2   2013-01-03 00:00:00-05:00
dtype: datetime64[ns, US/Eastern]
```

@ssanderson I didn't run into any issue with performance; however, I just chose to use tz-naiive datetimes throughout to avoid the penalties. The main issue was actually pushing things into postgress as type object because it would create a column with type string instead of datetime; however, this should fix all of that.

We have so many issues roundtripping csvs that had DatetimeIndex with timezones.  You have to write ugly things like stripping off the tz offset and then localize afterwards.  I think passing in the timezone name to `read_csv` and have that do the stripping/localizing would be an okay thing.

@rockg yes I agree. I think best to (before the PR and after) to simply read as object that do your own conversions. I suppose we could allow something like

`pd.read_csv(...., parse_dates= [{column : 'A', tz : 'US/Eastern'}]` (or accept this in the `dtype` spec). but that's another issue

@jorisvandenbossche https://travis-ci.org/jreback/pandas/jobs/69726572

can you repro this?

This works locally and therefore is puzzling.
I actually don't want it to coerce the object dtypes to my new format for SQL now (its tricky because of the funny postgres tz objects).