How do I group a dataframe by a column Id and then label 2 days intervals within the groups?

user_14388049 0
Viewed 94

I have a dataset that contains 5 columns where the first column is a visitorId, second column is a datetime, and the last column is a searchId. It looks something like that

|visitorId | datetime  |searchId |
|:---------|:---------:|--------:|
|  123     | 2020-06-06|  abd    |     
|  123     | 2020-06-07|  cde    |        
|  123     | 2020-06-08|  dgh    |
|  123     | 2020-06-18|  sdw    |
|  123     | 2020-06-21|  hkl    |
|  345     | 2020-06-21|   dsu   |
|  456     | 2020-06-06|  sdh    |
|  456     | 2020-06-20|  ckb    |
|  456     | 2020-05-24|  etw    |

What I want to do is to label the datetimes in another column based on a 2 day interval for each unique visitorId. So if I have 5 entries from visitor123 on 2020-06-06, 2020-06-07, 2020-06-08, 2020-06-18, 2020-06-21 : then 2020-06-06, 2020-06-07 and 2020-06-08 would be in one group because they are all within a 2 day interval from the first date (2020-06-06) and assigned one group label (0). 2020-06-18 would be assigned another group label (1) since there is no other date within the 2 day interval from 2020-06-18 and similarly, 2020-06-21 would be another unique group label (2). And this process is repeated for a new visitorid. So what I want to end up with is

|visitorId | datetime  | searchId | group label |
|:---------|:---------:|:--------:|------------:|
|  123     | 2020-06-06|   abd    |     0       |
|  123     | 2020-06-07|   cde    |     0       |  
|  123     | 2020-06-08|   dgh    |     0       |
|  123     | 2020-06-18|   sdw    |     1       |
|  123     | 2020-06-21|   hkl    |     2       |
|  345     | 2020-06-21|   dsu    |     0       |
|  456     | 2020-06-19|   sdh    |     0       |
|  456     | 2020-06-20|   ckb    |     0       |
|  456     | 2020-07-24|   etw    |     1       |
|  456     | 2020-08-09|   ekn    |     2       |

I was thinking I could use grouper, but I haven't been successful doing it. Would appreciate some help! (This is done in python)

pandas-groupby pandas dataframe python
2 Answers

Use Series.diff() + GroupBy.cumsum(), as follows:

Preparation:

#Convert your column 'datetime' to datetime format if not already in that format
df['datetime'] = pd.to_datetime(df['datetime'])

# sort columns
df = df.sort_values(['visitorId','datetime'])

Main logics:

df['group label'] = df['datetime'].diff().ge('2 days').groupby(df['visitorId']).cumsum()

Result:

print(df)

   visitorId   datetime searchId  group label
0        123 2020-06-06      abd            0
1        123 2020-06-07      cde            0
2        123 2020-06-08      dgh            0
3        123 2020-06-18      sdw            1
4        123 2020-06-21      hkl            2
5        345 2020-06-21      dsu            0
6        456 2020-06-19      sdh            0
7        456 2020-06-20      ckb            0
8        456 2020-07-24      etw            1
9        456 2020-08-09      ekn            2
user_150706970

First sort your dataframe, then we can use groupby.diff()

#df['datetime'] = pd.to_datetime(df['datetime'])
df = df.sort_values(['visitorId','datetime'])
df['group label'] = df.groupby('visitorId')['datetime'].diff().ge('2 days').astype(int)


print(df)


    visitorId   datetime   searchId  group label
1    123      2020-06-06    abd                0
2    123      2020-06-07    cde                0
3    123      2020-06-09    dgh                1
4    345      2020-06-05    dsu                0
5    345      2020-06-06    sdf                0
6    456      2020-06-06    sdh                0
7    456      2020-06-24    etw                1

edit.

s = df.groupby('visitorId')['datetime'].diff().ge('2 days').astype(int)

df['group_label'] = np.where(
    s.gt(0),
    df.groupby(['visitorId',s]).cumcount() + 1,
    0
)

    visitorId   datetime    searchId    group label  group_label
1         123 2020-06-06     abd           0                   0
2         123 2020-06-07     cde           0                   0
3         123 2020-06-08     dgh           0                   0
4         123 2020-06-18     sdw           1                   1
5         123 2020-06-21     hkl           2                   2
6         345 2020-06-21     dsu           0                   0
7         456 2020-06-19     sdh           0                   0
8         456 2020-06-20     ckb           0                   0
9         456 2020-07-24     etw           1                   1
10        456 2020-08-09     ekn           2                   2
user_93751020
Related