ENH: support cut/qcut for datetime/timedelta (GH14714)
State: closed

 - [x] closes #14714
 - [x] tests added / passed
 - [x] passes ``git diff upstream/master | flake8 --diff``
 - [x] whatsnew entry



Labels:
name: Enhancement
name: Datetime
description: Datetime data dtype
name: Timedelta
description: Timedelta data type
name: Algos
description: Non-arithmetic algos: value_counts, factorize, sorting, isin, clip, shift, diff

Comments:
https://github.com/pandas-dev/pandas/issues/14714

@jreback created this PR to verify if i am heading in the right direction with this change
@aileronajay Roughly this looks in the right direction, yes. But, we will also want this to work with datetime and not only timedelta values. So you will have to pass the dtype somehow to distuinguish between both when making the labels.
Another problem will be that you now assume this to be a series (you access the `.values`), while this also works for arrays. And that you convert a series to an array, and in this way loose the index information.

I would also encourage you to already add tests with some examples and the desired results, so you know what you are working towards.
pls add some tests
## [Current coverage](https://codecov.io/gh/pandas-dev/pandas/pull/14737?src=pr) is 85.27% (diff: 93.02%)


> No coverage report found for **master** at 725453d.

> Powered by [Codecov](https://codecov.io?src=pr). Last update [725453d...be9b2fd](https://codecov.io/gh/pandas-dev/pandas/compare/725453deb2efae1b84d87131e785a42a22753659...be9b2fdcb8b4ee1b0aca9bb6f8d13ef8971c7c30?src=pr)
@jorisvandenbossche ". And that you convert a series to an array, and in this way loose the index information.
"
After making call to the cut, will the output still be related to the initial index? I thought we split the  data into ranges by making a call to cut and the result does not have the same order/index as the original input that we pass to cut
@aileronajay As you can see, yes, the return value of `cut` retains the index:

```
In [18]: s = pd.Series(range(5))

In [19]: s
Out[19]: 
0    0
1    1
2    2
3    3
4    4
dtype: int64

In [20]: pd.cut(s, bins=2)
Out[20]: 
0    (-0.004, 2]
1    (-0.004, 2]
2    (-0.004, 2]
3         (2, 4]
4         (2, 4]
dtype: category
Categories (2, object): [(-0.004, 2] < (2, 4]]
```

Have a look for `x_is_series` how this is handled currently (eg here: https://github.com/aileronajay/pandas/blob/1b2dd2428d6ed2371cac89e56d474941c7225653/pandas/tools/tile.py#L243)
@jorisvandenbossche @jreback  i have changed how i convert the time object to integers, so i get a series now instead of array , x = x.astype(np.int64), it is similar to what is done in pandas.tools.util to_numeric method. This is the output that i  get now

 s = pd.Series(pd.to_timedelta(np.random.randint(0,10000,size=10),unit='ns')).sort_values()
 pd.cut(s,6)
1    (0 days 00:00:00.000000, 0 days 00:00:00.000001]
9    (0 days 00:00:00.000000, 0 days 00:00:00.000001]
7    (0 days 00:00:00.000001, 0 days 00:00:00.000003]
4    (0 days 00:00:00.000004, 0 days 00:00:00.000006]
2    (0 days 00:00:00.000004, 0 days 00:00:00.000006]
0    (0 days 00:00:00.000004, 0 days 00:00:00.000006]
3    (0 days 00:00:00.000004, 0 days 00:00:00.000006]
5    (0 days 00:00:00.000006, 0 days 00:00:00.000007]
8    (0 days 00:00:00.000006, 0 days 00:00:00.000007]
6    (0 days 00:00:00.000007, 0 days 00:00:00.000008]
@jreback i have moved the extra processing to the format method
@sinhrks @jreback @jorisvandenbossche i have incorporated all changes pertaining to all the review comments
@aileronajay looking good! 
Some additional general remarks:

- Can you add a notice in the whatsnew file (doc/source/whatsnew/v0.20.0.txt)
- For the tests, you nicely structured them. But, if you would combine them in one test (you can keep the structure by using comments), you can shorten the code a large part by not having to repeat the expected values.
- Can you add docstrings to the new functions you defined? I know we don't always do that consistently in the codebase but we have to start somewhere (it doesn't always have to be long, eg a one line description can already do a lot)
- Not sure if this should be dealt with in this PR, but we should also make it possible to specify your own bins for these data types (now, you only tested for the case where you specify the number of bins, eg `bins=True`, while eg `bins=[pd.Timestamp(..), ...]` should also work)
@jorisvandenbossche I have addressed the review comments in https://github.com/pandas-dev/pandas/pull/14737/commits/13c49b24d4c01474c89edea9b36c7a09a26945cc
@jreback i have made the documentation changes requested
@jreback are there additional changes required in the PR?
@aileronajay Thanks a lot! 
Do you want to do a follow-up PR to also add the ability to specify custom bins for datetime/timedelta values?
@jorisvandenbossche thanks! Yes i would like to do PR. I will need some additional information though as to how the custom bins for datetime/timedelta values would work, i mean what would be the expected functionality 
@aileronajay The idea is the following. Assume the example from your tests:

```
In [2]: data = pd.to_datetime(pd.Series(['2013-01-01', '2013-01-02', '2013-01-03']))

In [6]: pd.cut(data, bins=3)
Out[6]: 
0    (2012-12-31 23:57:07.200000, 2013-01-01 16:00:00]
1           (2013-01-01 16:00:00, 2013-01-02 08:00:00]
2           (2013-01-02 08:00:00, 2013-01-03 00:00:00]
dtype: category
Categories (3, object): [(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]]
```

Here you specify the number of bins, and the bin edges are generated automatically. But you can also specify the bin edges yourself (for numeric dtypes, this is not yet working for datetimes). So the idea is that you would be able to specify it like this:
```
In [7]: pd.cut(data, bins=[pd.Timestamp('2012-12-31'), pd.Timestamp('2012-01-02'), pd.Timestamp('2012-01-04')])
```
@jorisvandenbossche, i think if we specify the bin (edges) in terms of datetime, the "data" or the input should also be of the type datetime, is that correct? Also we would only be able to specify edges only in terms of datetime and not timedelta, as datetime represent specific time instances and so between the datetime instances we have windows that we can split the input data into. Timedelta objects are difference in time objects, would be able to use them as bins? 
>  if we specify the bin (edges) in terms of datetime, the "data" or the input should also be of the type datetime, is that correct?

Yes, indeed, the specified bin edges need to match the type of the data

> we would only be able to specify edges only in terms of datetime and not timedelta

No, you added support for 'cutting' timedelta data in this PR:

```
In [6]: data = pd.Series(pd.timedelta_range("0 days", freq='H', periods=5))

In [7]: data
Out[7]: 
0   00:00:00
1   01:00:00
2   02:00:00
3   03:00:00
4   04:00:00
dtype: timedelta64[ns]

In [8]: pd.cut(data, bins=3)
Out[8]: 
0    (-1 days +23:59:45.600000, 0 days 01:20:00]
1    (-1 days +23:59:45.600000, 0 days 01:20:00]
2             (0 days 01:20:00, 0 days 02:40:00]
3             (0 days 02:40:00, 0 days 04:00:00]
4             (0 days 02:40:00, 0 days 04:00:00]
dtype: category
Categories (3, object): [(-1 days +23:59:45.600000, 0 days 01:20:00] < (0 days 01:20:00, 0 days 02:40:00] <
                         (0 days 02:40:00, 0 days 04:00:00]]
```

so it should also be possible to specify the bin edges manually for this type of data.

Actually, I see you didn't add tests for the timedelta case, and I didn't notice this before merging. Would you also like to do a PR for adding some tests for this (can be separate PR)?




@jorisvandenbossche yes, i will do separate PR for tests for the timedelta test cases
@jorisvandenbossche i coerced the bins to a numeric type, using the coerce private method that I created earlier and i get the following output (below) , is this on lines of what we desire?

 pd.cut(pd.Series([np.datetime64('2013-01-01'),np.datetime64('2013-01-02')]),
...     [np.datetime64('2012-12-18'), np.datetime64('2013-01-03'),np.datetime64('2013-01-05')])
0    (2012-12-18 00:00:00, 2013-01-03 00:00:00]
1    (2012-12-18 00:00:00, 2013-01-03 00:00:00]
dtype: category
Categories (2, object): [(2012-12-18 00:00:00, 2013-01-03 00:00:00] < (2013-01-03 00:00:00, 2013-01-05 00:00:00]]
@aileronajay Yes, that looks like it. But you can just put up a PR, even if the code is only work in progress, then it will be easier to judge.
@jorisvandenbossche , yeah about to do that now