Creating Time slots for a list of timestamps using Pandas

Question

I have a column of Pandas Datetime64 type elements

df['time']

0         2019-10-04 12:03:53+00:00
1         2019-10-04 11:21:23+00:00
2         2019-10-04 12:23:11+00:00
3         2019-10-04 18:04:52+00:00
4         2019-10-04 12:22:21+00:00
                     ...           
2889974   2019-10-11 10:53:19+00:00
2889975   2019-10-11 10:58:38+00:00
2889976   2019-10-10 10:36:47+00:00
2889977   2019-10-10 10:36:47+00:00
2889978   2019-07-08 04:36:45+00:00
Name: time, Length: 2889979, dtype: datetime64[ns, UTC]

AND a column of the corresponding timestamps called df['time_full'] like so;

df['time_full']

0          12:03:53
1          11:21:23
2          12:23:11
3          18:04:52
4          12:22:21
             ...   
2889974    10:53:19
2889975    10:58:38
2889976    10:36:47
2889977    10:36:47
2889978    04:36:45
Name: time_full, Length: 2889979, dtype: object

I want to create slots of 30 minutes throughout the day (basically 48 slots) and assign a slot to all of the values in the df['time'] column. Basically, create a bunch of categorical variables a time stamp. Something like this (just an example):

df['time']                             df['slot']

0         2019-10-04 12:03:53+00:00    4
1         2019-10-04 11:21:23+00:00    2
2         2019-10-04 12:23:11+00:00    32
3         2019-10-04 18:04:52+00:00    40
4         2019-10-04 12:22:21+00:00    5

I tried binning the slots using Pandas' pd.cut() method like here, and ended up doing this :

pd.cut(df['time'].astype(np.int64)//10**9,
       bins=pd.date_range("00:00", "23:59", freq="30min"))

BUT got an output that looked like :

0          NaN
1          NaN
2          NaN
3          NaN
4          NaN
          ... 
2889974    NaN
2889975    NaN
2889976    NaN
2889977    NaN
2889978    NaN
Name: time, Length: 2889979, dtype: category
Categories (47, interval[int64]): [(1575331200000000000, 1575333000000000000] < (1575333000000000000, 1575334800000000000] < (1575334800000000000, 1575336600000000000] < (1575336600000000000, 1575338400000000000] ... (1575408600000000000, 1575410400000000000] < (1575410400000000000, 1575412200000000000] < (1575412200000000000, 1575414000000000000] < (1575414000000000000, 1575415800000000000]]

I also tried using df['time_full'] as bins, but it threw an error since it is a list of strings. I think the issue is that df['time'] is not meant for binning when it has both date and time, but I'm not really sure. Any help would be appreciated.

@luigigi I've edited for clarity, and given an example. Does this help? — Lakshay Sharma, Dec 03 '19 at 10:47
yes, now i got it. but are the slots in your example output the real slots or just random values? — luigigi, Dec 03 '19 at 10:54
@luigigi Random values, I just put them there to illustrate that I want a categorical variable — Lakshay Sharma, Dec 03 '19 at 10:55

luigigi · Accepted Answer · 2019-12-03T11:08:27.620

If you want the slots to range from 0 to 47 you can use this:

df['slots'] = df['time'].apply(lambda x: x.hour*2 if x.minute <= 29 else x.hour*2+1)
df

                             time  slots
0       2019-10-04 12:03:53+00:00     24
1       2019-10-04 11:21:23+00:00     22
2       2019-10-04 12:23:11+00:00     24
3       2019-10-04 18:04:52+00:00     36
4       2019-10-04 12:22:21+00:00     24
2889974 2019-10-11 10:53:19+00:00     21
2889975 2019-10-11 10:58:38+00:00     21
2889976 2019-10-10 10:36:47+00:00     21
2889977 2019-10-10 10:36:47+00:00     21
2889978 2019-07-08 04:36:45+00:00      9

Further testing:

                  date  slots
0  2019-10-04 00:00:00      0
1  2019-10-04 00:30:00      1
2  2019-10-04 01:00:00      2
3  2019-10-04 01:30:00      3
4  2019-10-04 02:00:00      4
5  2019-10-04 02:30:00      5
6  2019-10-04 03:00:00      6
7  2019-10-04 03:30:00      7
8  2019-10-04 04:00:00      8
9  2019-10-04 04:30:00      9
10 2019-10-04 05:00:00     10
11 2019-10-04 05:30:00     11
12 2019-10-04 06:00:00     12
13 2019-10-04 06:30:00     13
14 2019-10-04 07:00:00     14
15 2019-10-04 07:30:00     15
16 2019-10-04 08:00:00     16
17 2019-10-04 08:30:00     17
18 2019-10-04 09:00:00     18
19 2019-10-04 09:30:00     19
20 2019-10-04 10:00:00     20
21 2019-10-04 10:30:00     21
22 2019-10-04 11:00:00     22
23 2019-10-04 11:30:00     23
24 2019-10-04 12:00:00     24
25 2019-10-04 12:30:00     25
26 2019-10-04 13:00:00     26
27 2019-10-04 13:30:00     27
28 2019-10-04 14:00:00     28
29 2019-10-04 14:30:00     29
30 2019-10-04 15:00:00     30
31 2019-10-04 15:30:00     31
32 2019-10-04 16:00:00     32
33 2019-10-04 16:30:00     33
34 2019-10-04 17:00:00     34
35 2019-10-04 17:30:00     35
36 2019-10-04 18:00:00     36
37 2019-10-04 18:30:00     37
38 2019-10-04 19:00:00     38
39 2019-10-04 19:30:00     39
40 2019-10-04 20:00:00     40
41 2019-10-04 20:30:00     41
42 2019-10-04 21:00:00     42
43 2019-10-04 21:30:00     43
44 2019-10-04 22:00:00     44
45 2019-10-04 22:30:00     45
46 2019-10-04 23:00:00     46
47 2019-10-04 23:30:00     47

If you want to the slot range from 1 to 48:

df['slots'] = df['time'].apply(lambda x: x.hour*2+1 if x.minute <= 29 else x.hour*2+2)

score 0 · Answer 2 · answered Dec 03 '19 at 10:51

0

Depends how you want to have the values. Check How to convert DatetimeIndexResampler to DataFrame?

df = pd.DataFrame(pd.date_range('2000-01-02', freq='15min', periods=15), columns=['time'])
df.set_index(df['time'], inplace=True)
df=df.resample('30min').interpolate()
df

answered Dec 03 '19 at 10:51

uxke

446
3
9

I think this has more to do with resampling. My problem is towards binning and categorical variable conversion of DateTime Values. I edited my question for better clarity. Does that make more sense? – Lakshay Sharma Dec 03 '19 at 10:54

Creating Time slots for a list of timestamps using Pandas

2 Answers2