Pandas pd.cut () - bitning datetime column / series
Trying to make bin using pd.cut (), but it's rather complicated -
The collegium sends me several files with report dates, such as:
'03-16-2017 to 03-22-2017'
'03-23-2017 to 03-29-2017'
'03-30-2017 to 04-05-2017'
They are all merged into a single data frame and the column name is df ['Filedate'] so that every record in the file has the correct form.
The last day is the cutoff point, so I created a new column df ['Filedate_bin'] that converts the last day to 3/22/2017, 3/29/2017, 4/05/2017 as a string.
Then I created a list: Filedate_bin_list = df.Filedate_bin.unique (). As a result, I have a unique list of string cut-off dates that I would like to use as bins.
By importing different data into the dataframe, there is a transaction column: 3/28/2017, 3/29/2017, 3/30/2017, 4/1/2017, 4/2/2017, etc. Assigning them to the basket is tricky, he tried:
df['bin'] = pd.cut(df.Processed_date, Filedate_bin_list)
Received TypeError: unsupported operand type for -: 'str' and 'str'
Came back and tried to convert Filedate_bin to datetime format = '% m /% d /% Y' and get
TypeError: Cannot cast ufunc less input from dtype('<m8[ns]') to dtype ('<m8') with casting rule 'same_kind'.
Is there a better way to bin my processed_data (s) for textboxes?
I am trying to link to my processed dates from 3 to 27/2017 to '03 -23-2017 to 03-29-2017 '
source to share
UPDATE: Since Pandas v0.20.1 (May 5, 2017) pd.cut
and pd.qcut
support pd.qcut
datetime64 and timedelta64 ( GH14714 , GH14798 ).
Thanks @ lighthouse65 for checking!
Old answer:
Consider this approach:
df = pd.DataFrame(pd.date_range('2000-01-02', freq='1D', periods=15), columns=['Date'])
bins_dt = pd.date_range('2000-01-01', freq='3D', periods=6)
bins_str = bins_dt.astype(str).values
labels = ['({}, {}]'.format(bins_str[i-1], bins_str[i]) for i in range(1, len(bins_str))]
df['cat'] = pd.cut(df.Date.astype(np.int64)//10**9,
bins=bins_dt.astype(np.int64)//10**9,
labels=labels)
Result:
In [59]: df
Out[59]:
Date cat
0 2000-01-02 (2000-01-01, 2000-01-04]
1 2000-01-03 (2000-01-01, 2000-01-04]
2 2000-01-04 (2000-01-01, 2000-01-04]
3 2000-01-05 (2000-01-04, 2000-01-07]
4 2000-01-06 (2000-01-04, 2000-01-07]
5 2000-01-07 (2000-01-04, 2000-01-07]
6 2000-01-08 (2000-01-07, 2000-01-10]
7 2000-01-09 (2000-01-07, 2000-01-10]
8 2000-01-10 (2000-01-07, 2000-01-10]
9 2000-01-11 (2000-01-10, 2000-01-13]
10 2000-01-12 (2000-01-10, 2000-01-13]
11 2000-01-13 (2000-01-10, 2000-01-13]
12 2000-01-14 (2000-01-13, 2000-01-16]
13 2000-01-15 (2000-01-13, 2000-01-16]
14 2000-01-16 (2000-01-13, 2000-01-16]
In [60]: df.dtypes
Out[60]:
Date datetime64[ns]
cat category
dtype: object
Explanation:
df.Date.astype(np.int64)//10**9
- converts values datetime
to UNIX era (timestamp - number of seconds from 1970-01-01 00:00:00
):
In [65]: df.Date.astype(np.int64)//10**9
Out[65]:
0 946771200
1 946857600
2 946944000
3 947030400
4 947116800
5 947203200
6 947289600
7 947376000
8 947462400
9 947548800
10 947635200
11 947721600
12 947808000
13 947894400
14 947980800
Name: Date, dtype: int64
the same will apply to bins
:
In [66]: bins_dt.astype(np.int64)//10**9
Out[66]: Int64Index([946684800, 946944000, 947203200, 947462400, 947721600, 947980800], dtype='int64')
tags:
In [67]: labels
Out[67]:
['(2000-01-01, 2000-01-04]',
'(2000-01-04, 2000-01-07]',
'(2000-01-07, 2000-01-10]',
'(2000-01-10, 2000-01-13]',
'(2000-01-13, 2000-01-16]']
source to share