Repository: pandas-dev/pandas
Pull request: ENH: support cut/qcut for datetime/timedelta (GH14714)
 - [x] closes #14714
 - [x] tests added / passed
 - [x] passes ``git diff upstream/master | flake8 --diff``
 - [x] whatsnew entry



Problem statement:
ENH: enable pd.cut to handle i8 convertibles
so this should work for timedeltas AND datetimes.

Should be straight forward. detect an i8 convertible. turn into i8. do the cut. turn back to the original dtype.

```
In [15]: s = Series(pd.to_timedelta(np.random.randint(0,100,size=10),unit='ms')).sort_values()

In [16]: s
Out[16]:
3   00:00:00.005000
5   00:00:00.007000
7   00:00:00.010000
4   00:00:00.017000
9   00:00:00.023000
1   00:00:00.043000
0   00:00:00.045000
6   00:00:00.047000
8   00:00:00.065000
2   00:00:00.090000
dtype: timedelta64[ns]

In [18]: pd.cut(s, 5)
TypeError: unsupported operand type(s) for +: 'Timedelta' and 'float'

# works when converted
In [17]: pd.cut(s.astype('timedelta64[ms]'), 5)
Out[17]:
3    (4.915, 22]
5    (4.915, 22]
7    (4.915, 22]
4    (4.915, 22]
9       (22, 39]
1       (39, 56]
0       (39, 56]
6       (39, 56]
8       (56, 73]
2       (73, 90]
dtype: category
Categories (5, object): [(4.915, 22] < (22, 39] < (39, 56] < (56, 73] < (73, 90]]

```

Hints from issue discussion:
@jreback in the above example should the return type of the final object be 'timedelta64'? (the datatype of the original input)
yes (it should be original dtype)
@jreback Does it currently return strings? I got the below output by printing typeof over the members of category object returned
Categories (5, object): [(2.915, 20] < (20, 37] < (37, 54] < (54, 71] < (71, 88]]
(2.915, 20] <type 'str'>
(2.915, 20] <type 'str'>
(37, 54] <type 'str'>
(37, 54] <type 'str'>
(37, 54] <type 'str'>
(37, 54] <type 'str'>
(54, 71] <type 'str'>
(54, 71] <type 'str'>
(71, 88] <type 'str'>
(71, 88] <type 'str'>
yes it returns strings
we don't have an interval type currently

@jreback i am bit confused now, as part of this enhancement we first need convert to a dtype which cut can handle (timedelta64[ms]) and then return the type (timedelta64[ns]) from which we originally started. Though the objects returned will still be strings but they will be strings composed of object types that we initially passed to cut (timedelta64[ns])?
yes this is a bit tricky. I think you:

- convert to i8
- do the binning
- construct the labels based on the bins / dtype
- stringify them
@jreback thanks, this is what i was thinking
IF we had an ``Interval`` type then this would be very easy (https://github.com/pandas-dev/pandas/issues/8625), e.g. ``Period`` is an interval type for datetimes (but not actually implemented that way, and has *slightly* different semantics).
@jreback is there an error with the way i am making this round trip

I start with s which is timedelta64[ns]. This is what "s". I then convert using the astype conversion used earlier here. Then i convert back to timedelta64[ns] using another as type conversion. But this conversion does not retain data and r is just a list having no time information 

s = pd.Series(pd.to_timedelta(np.random.randint(0,100,size=10),unit='ms')).sort_values()
print s
p = s.astype('timedelta64[ms]')
r = p.astype(s.dtype)
print r


python data_conversion.py 
3   00:00:00.008000
5   00:00:00.015000
8   00:00:00.031000
1   00:00:00.040000
9   00:00:00.045000
6   00:00:00.046000
2   00:00:00.072000
0   00:00:00.082000
4   00:00:00.091000
7   00:00:00.091000
dtype: timedelta64[ns]
3   00:00:00.000000
5   00:00:00.000000
8   00:00:00.000000
1   00:00:00.000000
9   00:00:00.000000
6   00:00:00.000000
2   00:00:00.000000
0   00:00:00.000000
4   00:00:00.000000
7   00:00:00.000000
dtype: timedelta64[ns]

no, you will *always* convert to ``ns`` and *always* back from ``ns``. (internally you will do

``.values.view('i8')``

then ``pd.to_timedelta(result, unit='ns')`` to convert back.

Commit message: removed trailing whitespace from test_tile file

Patch to review:
diff --git a/doc/source/whatsnew/v0.20.0.txt b/doc/source/whatsnew/v0.20.0.txt
index 6fe0ad8092a03..5e94a95e38cbb 100644
--- a/doc/source/whatsnew/v0.20.0.txt
+++ b/doc/source/whatsnew/v0.20.0.txt
@@ -50,6 +50,7 @@ Other enhancements
 
 - ``pd.read_excel`` now preserves sheet order when using ``sheetname=None`` (:issue:`9930`)
 
+- ``pd.cut`` and ``pd.qcut`` now support datetime64 and timedelta64 dtypes (issue:`14714`)
 
 .. _whatsnew_0200.api_breaking:
 
diff --git a/pandas/tools/tests/test_tile.py b/pandas/tools/tests/test_tile.py
index e5b9c65b515d6..ce634ddfc275b 100644
--- a/pandas/tools/tests/test_tile.py
+++ b/pandas/tools/tests/test_tile.py
@@ -12,6 +12,7 @@
 from pandas.core.algorithms import quantile
 from pandas.tools.tile import cut, qcut
 import pandas.tools.tile as tmod
+from pandas import to_datetime, DatetimeIndex
 
 
 class TestCut(tm.TestCase):
@@ -283,6 +284,36 @@ def test_single_bin(self):
         result = cut(s, 1, labels=False)
         tm.assert_series_equal(result, expected)
 
+    def test_datetime_cut(self):
+        # GH 14714
+        # testing for time data to be present as series
+        data = to_datetime(Series(['2013-01-01', '2013-01-02', '2013-01-03']))
+        result, bins = cut(data, 3, retbins=True)
+        expected = Series(['(2012-12-31 23:57:07.200000, 2013-01-01 16:00:00]',
+                          '(2013-01-01 16:00:00, 2013-01-02 08:00:00]',
+                           '(2013-01-02 08:00:00, 2013-01-03 00:00:00]'],
+                          ).astype("category", ordered=True)
+        tm.assert_series_equal(result, expected)
+
+        # testing for time data to be present as list
+        data = [np.datetime64('2013-01-01'), np.datetime64('2013-01-02'),
+                np.datetime64('2013-01-03')]
+        result, bins = cut(data, 3, retbins=True)
+        tm.assert_series_equal(Series(result), expected)
+
+        # testing for time data to be present as ndarray
+
+        data = np.array([np.datetime64('2013-01-01'),
+                        np.datetime64('2013-01-02'),
+                        np.datetime64('2013-01-03')])
+        result, bins = cut(data, 3, retbins=True)
+        tm.assert_series_equal(Series(result), expected)
+
+        # testing for time data to be present as datetime index
+        data = DatetimeIndex(['2013-01-01', '2013-01-02', '2013-01-03'])
+        result, bins = cut(data, 3, retbins=True)
+        tm.assert_series_equal(Series(result), expected)
+
 
 def curpath():
     pth, _ = os.path.split(os.path.abspath(__file__))
diff --git a/pandas/tools/tile.py b/pandas/tools/tile.py
index ef75f2f84779b..2b2f145f1857d 100644
--- a/pandas/tools/tile.py
+++ b/pandas/tools/tile.py
@@ -11,8 +11,9 @@
 import pandas.core.algorithms as algos
 import pandas.core.nanops as nanops
 from pandas.compat import zip
-
+from pandas import to_timedelta, to_datetime
 import numpy as np
+from pandas.types.common import is_datetime64_dtype, is_timedelta64_dtype
 
 
 def cut(x, bins, right=True, labels=None, retbins=False, precision=3,
@@ -81,6 +82,11 @@ def cut(x, bins, right=True, labels=None, retbins=False, precision=3,
     array([1, 1, 1, 1, 1], dtype=int64)
     """
     # NOTE: this binning code is changed a bit from histogram for var(x) == 0
+    # for handling the cut for datetime and timedelta objects
+    x_is_series, series_index, name, x = _preprocess_for_cut(x)
+
+    original, x, dtype = _coerce_to_type(x)
+
     if not np.iterable(bins):
         if is_scalar(bins) and bins < 1:
             raise ValueError("`bins` should be a positive integer.")
@@ -114,9 +120,12 @@ def cut(x, bins, right=True, labels=None, retbins=False, precision=3,
         if (np.diff(bins) < 0).any():
             raise ValueError('bins must increase monotonically.')
 
-    return _bins_to_cuts(x, bins, right=right, labels=labels,
-                         retbins=retbins, precision=precision,
-                         include_lowest=include_lowest)
+    fac, bins = _bins_to_cuts(x, bins, right=right, labels=labels,
+                              retbins=retbins, precision=precision,
+                              include_lowest=include_lowest, dtype=dtype)
+
+    return _postprocess_for_cut(fac, bins, retbins, x_is_series,
+                                series_index, name)
 
 
 def qcut(x, q, labels=None, retbins=False, precision=3):
@@ -166,26 +175,26 @@ def qcut(x, q, labels=None, retbins=False, precision=3):
     >>> pd.qcut(range(5), 4, labels=False)
     array([0, 0, 1, 2, 3], dtype=int64)
     """
+    x_is_series, series_index, name, x = _preprocess_for_cut(x)
+
+    original, x, dtype = _coerce_to_type(x)
+
     if is_integer(q):
         quantiles = np.linspace(0, 1, q + 1)
     else:
         quantiles = q
     bins = algos.quantile(x, quantiles)
-    return _bins_to_cuts(x, bins, labels=labels, retbins=retbins,
-                         precision=precision, include_lowest=True)
-
+    fac, bins = _bins_to_cuts(x, bins, labels=labels, retbins=retbins,
+                              precision=precision, include_lowest=True,
+                              dtype=dtype)
 
-def _bins_to_cuts(x, bins, right=True, labels=None, retbins=False,
-                  precision=3, name=None, include_lowest=False):
-    x_is_series = isinstance(x, Series)
-    series_index = None
+    return _postprocess_for_cut(fac, bins, retbins, x_is_series,
+                                series_index, name)
 
-    if x_is_series:
-        series_index = x.index
-        if name is None:
-            name = x.name
 
-    x = np.asarray(x)
+def _bins_to_cuts(x, bins, right=True, labels=None, retbins=False,
+                  precision=3, name=None, include_lowest=False,
+                  dtype=None):
 
     side = 'left' if right else 'right'
     ids = bins.searchsorted(x, side=side)
@@ -205,7 +214,8 @@ def _bins_to_cuts(x, bins, right=True, labels=None, retbins=False,
             while True:
                 try:
                     levels = _format_levels(bins, precision, right=right,
-                                            include_lowest=include_lowest)
+                                            include_lowest=include_lowest,
+                                            dtype=dtype)
                 except ValueError:
                     increases += 1
                     precision += 1
@@ -229,18 +239,12 @@ def _bins_to_cuts(x, bins, right=True, labels=None, retbins=False,
             fac = fac.astype(np.float64)
             np.putmask(fac, na_mask, np.nan)
 
-    if x_is_series:
-        fac = Series(fac, index=series_index, name=name)
-
-    if not retbins:
-        return fac
-
     return fac, bins
 
 
 def _format_levels(bins, prec, right=True,
-                   include_lowest=False):
-    fmt = lambda v: _format_label(v, precision=prec)
+                   include_lowest=False, dtype=None):
+    fmt = lambda v: _format_label(v, precision=prec, dtype=dtype)
     if right:
         levels = []
         for a, b in zip(bins, bins[1:]):
@@ -258,12 +262,16 @@ def _format_levels(bins, prec, right=True,
     else:
         levels = ['[%s, %s)' % (fmt(a), fmt(b))
                   for a, b in zip(bins, bins[1:])]
-
     return levels
 
 
-def _format_label(x, precision=3):
+def _format_label(x, precision=3, dtype=None):
     fmt_str = '%%.%dg' % precision
+
+    if is_datetime64_dtype(dtype):
+        return to_datetime(x, unit='ns')
+    if is_timedelta64_dtype(dtype):
+        return to_timedelta(x, unit='ns')
     if np.isinf(x):
         return str(x)
     elif is_float(x):
@@ -300,3 +308,56 @@ def _trim_zeros(x):
     if len(x) > 1 and x[-1] == '.':
         x = x[:-1]
     return x
+
+
+def _coerce_to_type(x):
+    """
+    if the passed data is of datetime/timedelta type,
+    this method converts it to integer so that cut method can
+    handle it
+    """
+    dtype = None
+    original = x
+    if is_timedelta64_dtype(x):
+        x = to_timedelta(x).view(np.int64)
+        dtype = np.timedelta64
+
+    elif is_datetime64_dtype(x):
+        x = to_datetime(x).view(np.int64)
+        dtype = np.datetime64
+    return original, x, dtype
+
+
+def _preprocess_for_cut(x):
+    """
+    handles preprocessing for cut where we convert passed
+    input to array, strip the index information and store it
+    seperately
+    """
+    x_is_series = isinstance(x, Series)
+    series_index = None
+
+    name = None
+    if x_is_series:
+        series_index = x.index
+        if name is None:
+            name = x.name
+
+    x = np.asarray(x)
+
+    return x_is_series, series_index, name, x
+
+
+def _postprocess_for_cut(fac, bins, retbins, x_is_series, series_index, name):
+    """
+    handles post processing for the cut method where
+    we combine the index information if the originally passed
+    datatype was a series
+    """
+    if x_is_series:
+        fac = Series(fac, index=series_index, name=name)
+
+    if not retbins:
+        return fac
+
+    return fac, bins


Code review comments:
diff_hunk: @@ -81,6 +82,11 @@ def cut(x, bins, right=True, labels=None, retbins=False, precision=3,
     array([1, 1, 1, 1, 1], dtype=int64)
     """
     # NOTE: this binning code is changed a bit from histogram for var(x) == 0
+    # for handling the cut for datetime and timedelta objects
+    x_is_series, series_index, name, x = _preprocess_for_cut(x)
+
+    original, x, dtype = _coerce_to_type(x)
path: pandas/tools/tile.py
text: @user1:
I don't think `original` is used? In that case, it's not needed to return it

@author:
I have addressed this review comment

diff_hunk: @@ -166,26 +175,26 @@ def qcut(x, q, labels=None, retbins=False, precision=3):
     >>> pd.qcut(range(5), 4, labels=False)
     array([0, 0, 1, 2, 3], dtype=int64)
     """
+    x_is_series, series_index, name, x = _preprocess_for_cut(x)
+
+    original, x, dtype = _coerce_to_type(x)
+
     if is_integer(q):
         quantiles = np.linspace(0, 1, q + 1)
     else:
         quantiles = q
     bins = algos.quantile(x, quantiles)
-    return _bins_to_cuts(x, bins, labels=labels, retbins=retbins,
-                         precision=precision, include_lowest=True)
-
+    fac, bins = _bins_to_cuts(x, bins, labels=labels, retbins=retbins,
+                              precision=precision, include_lowest=True,
+                              dtype=dtype)
 
-def _bins_to_cuts(x, bins, right=True, labels=None, retbins=False,
-                  precision=3, name=None, include_lowest=False):
-    x_is_series = isinstance(x, Series)
-    series_index = None
+    return _postprocess_for_cut(fac, bins, retbins, x_is_series,
+                                series_index, name)
 
-    if x_is_series:
-        series_index = x.index
-        if name is None:
-            name = x.name
 
-    x = np.asarray(x)
+def _bins_to_cuts(x, bins, right=True, labels=None, retbins=False,
path: pandas/tools/tile.py
text: @user1:
I think `retbins` arg is not used anymore in this function?

@user1:
And the same for `name`

@author:
I have addressed this review comment

diff_hunk: @@ -300,3 +308,56 @@ def _trim_zeros(x):
     if len(x) > 1 and x[-1] == '.':
         x = x[:-1]
     return x
+
+
+def _coerce_to_type(x):
+    """
+    if the passed data is of datetime/timedelta type,
+    this method converts it to integer so that cut method can
+    handle it
+    """
+    dtype = None
+    original = x
+    if is_timedelta64_dtype(x):
+        x = to_timedelta(x).view(np.int64)
+        dtype = np.timedelta64
+
+    elif is_datetime64_dtype(x):
+        x = to_datetime(x).view(np.int64)
+        dtype = np.datetime64
+    return original, x, dtype
+
+
+def _preprocess_for_cut(x):
+    """
+    handles preprocessing for cut where we convert passed
+    input to array, strip the index information and store it
+    seperately
+    """
+    x_is_series = isinstance(x, Series)
+    series_index = None
+
+    name = None
+    if x_is_series:
+        series_index = x.index
+        if name is None:
path: pandas/tools/tile.py
text: @user1:
this check is not needed, as you defined `name = None` above

@author:
I have addressed this review comment

diff_hunk: @@ -81,6 +82,11 @@ def cut(x, bins, right=True, labels=None, retbins=False, precision=3,
     array([1, 1, 1, 1, 1], dtype=int64)
     """
     # NOTE: this binning code is changed a bit from histogram for var(x) == 0
+    # for handling the cut for datetime and timedelta objects
+    x_is_series, series_index, name, x = _preprocess_for_cut(x)
+
+    original, x, dtype = _coerce_to_type(x)
+
     if not np.iterable(bins):
         if is_scalar(bins) and bins < 1:
             raise ValueError("`bins` should be a positive integer.")
path: pandas/tools/tile.py
text: @user1:
The `try ... except ...` below should not be necessary anymore, since `x` is already an array (asarray is used in `_preprocess_for_cut`)

@author:
@user1 you are referring to the try except in the pd.cut method, specifically these lines

        try:  # for array-like
            sz = x.size
        except AttributeError:
            x = np.asarray(x)
            sz = x.size

Is that correct?

@user1:
yes (I couldn't comment on those lines) 
Normally it should already be assured before that you have an array here

@author:
@user1 thanks, that is what i thought!

@author:
I have addressed this review comment