Pandas pd.cut() – binning datetime column / series

by

UPDATE: starting from Pandas v0.20.1 (May 5, 2017) pd.cut and pd.qcut support datetime64 and timedelta64 dtypes (GH14714, GH14798).

Thanks @lighthouse65 for checking this!

Updated answer:

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'], bins=bins_dt, labels=labels)

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 datetime values into UNIX epoch (timestamp – # of seconds since 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 applyied to bins:

In [66]: bins_dt.astype(np.int64)//10**9
Out[66]: Int64Index([946684800, 946944000, 947203200, 947462400, 947721600, 947980800], dtype="int64")

labels:

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]']

More Related Contents:

  1. How to combine multiple columns in a DataFrame to Pandas datetime
  2. converting a pandas date to week number
  3. How to change column names from numbers to names [closed]
  4. How to change the location of the column name in pandas?
  5. How to provide a reproducible copy of your DataFrame with to_clipboard()
  6. Binning a column with Python Pandas
  7. How to replace NaNs by preceding or next values in pandas DataFrame?
  8. Filtering Pandas DataFrames on dates
  9. How to print pandas DataFrame without index
  10. Find column whose name contains a specific string
  11. How to deal with multi-level column names downloaded with yfinance
  12. Rolling Mean on pandas on a specific column
  13. Pandas deleting row with df.drop doesn’t work
  14. finding values in pandas series – Python3
  15. Count number of words per row
  16. Pandas filter dataframe rows with a specific year
  17. Importing pandas shows ImportError: cannot import name hashtable
  18. Python/Pandas convert string to time only
  19. Bin pandas dataframe by every X rows
  20. Convert Pandas Series to DateTime in a DataFrame
  21. How to extract first 8 characters from a string in pandas
  22. Python: create a pandas data frame from a list
  23. SKLearn warning “valid feature names” in version 1.0
  24. Updating a value in a pandas dataframe in an iterrows loop
  25. Python pandas: how to remove nan and -inf values
  26. Group DataFrame in 5-minute intervals
  27. Pandas custom function to find whether it is the 1st, 2nd etc Monday, Tuesday, etc – all suggestions welcome
  28. What values are valid in Pandas ‘Freq’ tags?
  29. pandas extract year from datetime: df[‘year’] = df[‘date’].year is not working
  30. Python Pandas : pandas.to_datetime() is switching day & month when day is less than 13

Leave a Comment