Binning time column in Python

user_10698499 0
Viewed 2842

I had a datetime column (as object type) in my Pandas dataframe. I broke the datetime column to two columns date and time and I transformed both using to_datetime to the following format with the following code:

 df['Time'] =  pd.to_datetime(df['Time'], format='%H:%M:%S').dt.time
 df['Date'] =  pd.to_datetime(df['Date'], format='%Y.%m.%d')

After the above code execution, I have the Date column of type Datetime and the Time column (e.g., 00:14:53) of type object - I don't know why not Datetime since I used to_datetime-

I want to break the time column to 4 categories/bins, 00:00:00 - 06:00:00 as category 1, then 06:00:00 to 12:00:00 and so on.

I tried different methods with the cut but all wrong.

What I am doing wrong, any help how to do this?

Thanks

pandas time python
2 Answers

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
user_29010020
  1. Convert date to unix timestamp
def convert_to_unix(s):
    return time.mktime(datetime.strptime(s, "%Y-%m-%d %H:%M:%S").timetuple())
  1. Then convert the timestamp to hours from seconds (60*60) and divide it by the time interval(6 hours, in this case)
df['bins'] = np.array(  [ int ( convert_to_unix(i) / 60 * 60 * 6) for i in df['Time']]  )

You can change the category after that.

user_126675340
Related