• The correct way to bin a pandas.DataFrame is to use pandas.cut
• Verify the date column is in a datetime format with pandas.to_datetime.
• Use .dt.hour to extract the hour, for use in the .cut method.
• Tested in python 3.8.11 and pandas 1.3.1

How to bin the data

import pandas as pd
import numpy as np  # for test data
import random  # for test data

# setup a sample dataframe; creates 1.5 months of hourly observations
np.random.seed(365)
random.seed(365)
data = {'date': pd.bdate_range('2020-09-21', freq='h', periods=1100).tolist(),
        'x': np.random.randint(10, size=(1100))}
df = pd.DataFrame(data)

# the date column of the sample data is already in a datetime format
# if the date column is not a datetime, then uncomment the following line
# df.date= pd.to_datetime(df.date)

# define the bins
bins = [0, 6, 12, 18, 24]

# add custom labels if desired
labels = ['00:00-05:59', '06:00-11:59', '12:00-17:59', '18:00-23:59']

# add the bins to the dataframe
df['Time Bin'] = pd.cut(df.date.dt.hour, bins, labels=labels, right=False)

df.head()

                  date  x     Time Bin
0  2020-09-21 00:00:00  2  00:00-05:59
1  2020-09-21 01:00:00  4  00:00-05:59
2  2020-09-21 02:00:00  1  00:00-05:59
3  2020-09-21 03:00:00  5  00:00-05:59
4  2020-09-21 04:00:00  2  00:00-05:59

df.tail()

                    date  x     Time Bin
1095 2020-11-05 15:00:00  2  12:00-17:59
1096 2020-11-05 16:00:00  3  12:00-17:59
1097 2020-11-05 17:00:00  1  12:00-17:59
1098 2020-11-05 18:00:00  2  18:00-23:59
1099 2020-11-05 19:00:00  2  18:00-23:59

Groupby 'Time Bin'

• Use pandas.DataFrame.groupby on 'Time Bin', and then aggregate 'x' into a list and mean.

# groupby Time Bin and aggregate a list for the observations, and mean
dfg = df.groupby('Time Bin', as_index=False)['x'].agg([list, 'mean'])

# change the column names, if desired
dfg.columns = ['X Observations', 'X mean']

dfg

                      X Observations    X mean
Time Bin                                 
00:00-05:59  [2, 4, 1, 5, 2, 2, ...]  4.416667
06:00-11:59  [9, 8, 4, 0, 3, 3, ...]  4.760870
12:00-17:59  [7, 7, 7, 0, 8, 4, ...]  4.384058
18:00-23:59  [3, 2, 6, 2, 6, 8, ...]  4.459559

Convert Time column to hours by Series.dt.hour and use cut for binning:

rng = pd.date_range('2017-04-03', periods=30, freq='H').strftime('%H:%M:%S')
df = pd.DataFrame({'Time': rng}) 

hours = pd.to_datetime(df['Time'], format='%H:%M:%S').dt.hour

df['cats'] = pd.cut(hours, 
                    bins=[0,6,12,18,24], 
                    include_lowest=True, 
                    labels=['cat1','cat2','cat3','cat4'])

print (df)
        Time  cats
0   00:00:00  cat1
1   01:00:00  cat1
2   02:00:00  cat1
3   03:00:00  cat1
4   04:00:00  cat1
5   05:00:00  cat1
6   06:00:00  cat1
7   07:00:00  cat2
8   08:00:00  cat2
9   09:00:00  cat2
10  10:00:00  cat2
11  11:00:00  cat2
12  12:00:00  cat2
13  13:00:00  cat3
14  14:00:00  cat3
15  15:00:00  cat3
16  16:00:00  cat3
17  17:00:00  cat3
18  18:00:00  cat3
19  19:00:00  cat4
20  20:00:00  cat4
21  21:00:00  cat4
22  22:00:00  cat4
23  23:00:00  cat4
24  00:00:00  cat1
25  01:00:00  cat1
26  02:00:00  cat1
27  03:00:00  cat1
28  04:00:00  cat1
29  05:00:00  cat1

Converting to datetime and using pandas.Grouper + Offset Aliases:

df['date'] = pd.to_datetime(df.date)
df.groupby(pd.Grouper(key='date', freq='30min')).mean().dropna()

    speed
date    
2018-09-20 01:30:00     47.040000
2018-09-20 07:30:00     26.311429
2018-09-20 10:30:00     39.947500
2018-09-20 11:00:00     32.298000

I think @DrV's is the correct answer, but I've prepared an example trying to show how something similar could be achieved using Pandas:

import numpy
import pandas
import datetime
import time

# Binning delta

delta = datetime.timedelta(hours=1)

# Sample data

sample = [
    ['2014-08-09 16:30:00', 'label1'],
    ['2014-08-09 15:30:00', 'label2'],
    ['2014-08-09 14:30:00', 'label3'],
    ['2014-08-09 14:00:00', 'label4']
]

# Create dataframe and append UNIX timestamp column

df = pandas.DataFrame(sample)
df.columns = ['Datetime', 'Label']
df['Datetime'] = pandas.to_datetime(df['Datetime'])
df['UnixStamp'] = df['Datetime'].apply(lambda d: time.mktime(d.timetuple()))
df = df.set_index('Datetime')

# Calculate bins

bins = numpy.arange(min(df['UnixStamp']), max(df['UnixStamp']) + delta.seconds, delta.seconds)

# Group columns by datetime bin

def bin_from_tstamp(tstamp):

    diffs = [abs(tstamp - bin) for bin in bins]
    return bins[diffs.index(min(diffs))]

grouped = df.groupby(df['UnixStamp'].map(
    lambda t: datetime.datetime.fromtimestamp(bin_from_tstamp(t))
))

At this point grouped contains the dataset grouped by datetime bins.

The following is the result of printing grouped.groups (where the keys are the datetime bins and the values are the grouped datetimes):

{
    numpy.datetime64('2014-08-09T18:00:00.000000000+0200'): [
        Timestamp('2014-08-09 16:30:00')
    ], 
    numpy.datetime64('2014-08-09T17:00:00.000000000+0200'): [
        Timestamp('2014-08-09 15:30:00')
    ], 
    numpy.datetime64('2014-08-09T16:00:00.000000000+0200'): [
        Timestamp('2014-08-09 14:30:00'), 
        Timestamp('2014-08-09 14:00:00'
    ]
}

You can groupby the time column directly:

df.groupby(pd.Grouper(key='date_build', freq='M')).size()

Only a little faster in terms of runtime - all those conversion operations are pretty fast.

In [198]: df = pd.DataFrame({'date_build': pd.date_range('1900-1-1', periods=100000)})

In [199]: %timeit pd.DatetimeIndex(df.date_build).to_series().resample('M', how='count')
10 loops, best of 3: 149 ms per loop

In [200]: %timeit df.groupby(pd.Grouper(key='date_build', freq='M')).size()
10 loops, best of 3: 136 ms per loop

Since you want to bin the data depending on the hour, the easiest way would be to first convert to 24-hour format and then use pd.cut on that.

df = pd.DataFrame({'ID': ['A', 'B', 'C', 'D'], 'Time': ['2:44PM', '3:23AM', '5:00PM', '12:00AM']})

df['Time'] = pd.to_datetime(df['Time'], format='%I:%M%p').dt.hour
df['T_flag'] = pd.cut(df['Time'], bins=np.array([-1,0,6,12,18,24]), labels=[3,0,1,2,3])

This will give:

ID  Time  T_flag
A    14        2
B     3        0
C    17        2
D     0        3

The first (-1,0] bin is necessary to convert 12:00AM to label 3. Converting this value to datetime will give 0 which by default would give it the label 0 (with the first bin (-1,6] or include_lowest=True).

I know it's late. But better late than never. I also came across a similar requirement and done by using pandas library.

• First, Load data in pandas data-frame

• Second, check TIME column must be datetime object and not object type (like string or whatever). You can check it by

  df.info()

for example, in my case TIME column was initially of object type i.e. string type

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 17640 entries, 0 to 17639
Data columns (total 3 columns):
TIME           17640 non-null object
value          17640 non-null int64
dtypes: int64(1), object(2)
memory usage: 413.5+ KB

• if that is the case, then convert it to pandas datetime object by using this command

  df['TIME'] = pd.to_datetime(df['TIME']) ignore this if already in datetime format

df.info() now gives updated format

 <class 'pandas.core.frame.DataFrame'>
 RangeIndex: 17640 entries, 0 to 17639
 Data columns (total 3 columns):
 TIME           17640 non-null datetime64[ns]
 value          17640 non-null int64
 dtypes: datetime64ns, int64(1)
 memory usage: 413.5 KB

• Now our dataframe is ready for magic :)

     counts = pd.Series(index=df.TIME, data=np.array(df.count)).resample('15T').count()
     print(counts[:3])
  

   TIME
   2017-07-01 00:00:00    3
   2017-07-01 00:15:00    3
   2017-07-01 00:30:00    3
   Freq: 15T, dtype: int64
  

  in above command 15T means 15minutes bucket, you can replace it with D for day bucket, 2D for 2 days bucket, M for month bucket, 2M for 2 months bucket and so on. You can read the detail of these notations on this link

• now, our buckets data is done as you can see above. for time range use this command. Use the same time range as of data. In my case, my data was 3 months so I am creating time-range of 3 months.

 r = pd.date_range('2017-07', '2017-09', freq='15T')
 x = np.repeat(np.array(r), 2, axis=0)[1:-1]
 # now reshape data to fit in Dataframe
 x = np.array(x)[:].reshape(-1, 2)
 # now fit in dataframe and print it
 final_df = pd.DataFrame(x, columns=['start', 'end'])
 print(final_df[:3])

                  start                 end
0   2017-07-01 00:00:00 2017-07-01 00:15:00
1   2017-07-01 00:15:00 2017-07-01 00:30:00
2   2017-07-01 00:30:00 2017-07-01 00:45:00

date ranges also done

• Now append count and dateranges to get final outcome

   final_df['count'] = np.array(means)
   print(final_df[:3])
  

                  start                 end count
0   2017-07-01 00:00:00 2017-07-01 00:15:00     3
1   2017-07-01 00:15:00 2017-07-01 00:30:00     3
2   2017-07-01 00:30:00 2017-07-01 00:45:00     3

Hope anyone find it useful.

I would create a column with my binned hours using dt.hour

So

df["binned_hours"] = pd.cut(df.timestamp.dt.hour, bins=24)

You may want to take a look at pd.cut.

# toy data
df = pd.DataFrame(pd.date_range('2020-01-01', '2022-01-01'), columns = ['date'])

          date
0   2020-01-01
1   2020-01-02
2   2020-01-03
3   2020-01-04
4   2020-01-05
..         ...

You can generate the labels and boundaries for the bins.

from numpy import datetime64
bin_labels = [1, 2, 3, 4]
cut_bins = [datetime64('2019-12-31'), datetime64('2020-04-01'), datetime64('2020-12-31'), datetime64('2021-09-01'), datetime64('2022-01-01')]

And save the bins into a new column.

df['cut'] = pd.cut(df['date'], bins = cut_bins, labels = bin_labels]

          date cut
0   2020-01-01   1
1   2020-01-02   1
2   2020-01-03   1
3   2020-01-04   1
4   2020-01-05   1
..         ...  ..
727 2021-12-28   4
728 2021-12-29   4
729 2021-12-30   4
730 2021-12-31   4
731 2022-01-01   4

Hope it helps.

It works for me with pandas 0.23.4

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'userID': ['DSm7ysk', 'no51CdJ', 'foo', 'bar'],
    'duration': [pd.Timedelta('3 hours 8 minutes 49 seconds'), pd.Timedelta('35 minutes 50 seconds'), pd.Timedelta('1 minutes 13 seconds'), pd.Timedelta('6 minutes 43 seconds')]
})

bins = [
    pd.Timedelta(minutes = 0),
    pd.Timedelta(minutes = 5),
    pd.Timedelta(minutes = 10),
    pd.Timedelta(minutes = 20),
    pd.Timedelta(minutes = 30),
    pd.Timedelta(hours = 4)
]

labels = ['0-5min', '5-10min', '10-20min', '20-30min', '30min+']

df['bins'] = pd.cut(df['duration'], bins, labels = labels)

Result: