CODEWITHSUNDEEP
pythonpandas
Into Numbers
Into Numbers

Reputation: 973

Pandas - Group by Period of Time / deleting consecutive rows based on multiple conditions

What's the best way to solve following problem:

I have a Pandas dataframe which looks like this:

Index  Date                  Name       Product
01     2017-09-6 18:01:15    Mike       xxx
02     2017-09-6 18:02:35    Mike       yyy
03     2017-09-6 18:07:25    Mike       xxx
04     2017-09-6 18:09:35    Mike       yyy
05     2017-09-6 18:09:39    John       yyy
06     2017-09-6 18:23:15    Mike       xxx
07     2017-09-6 18:25:35    Mike       xxx
08     2017-09-6 18:50:39    John       yyy
09     2017-09-6 18:58:15    Mary       xxx
10     2017-09-6 19:07:20    Mary       xxx
11     2017-09-6 19:07:25    Mike       zzz
12     2017-09-6 19:09:09    Mary       xxx
13     2017-09-6 21:03:45    Mary       zzz
14     2017-09-6 21:12:15    Mike       yyy
15     2017-09-6 21:20:15    Mike       yyy

This dataframe represents product searches of multiple users in a database. What I want to do now is creating a dataframe, which filters out multiple searches of the same user on the same product within a certain period of time (let's say 15 minutes) - in other words: every additional search within 15 minutes after the first search meeting the name and product criterias should be deleted.

The resulting dataframe should look like this:

Index  Date                  Name       Product
01     2017-09-6 18:01:15    Mike       xxx
02     2017-09-6 18:02:35    Mike       yyy
05     2017-09-6 18:09:39    John       yyy
06     2017-09-6 18:23:15    Mike       xxx
08     2017-09-6 18:50:39    John       yyy
09     2017-09-6 18:58:15    Mary       xxx
11     2017-09-6 19:07:25    Mike       zzz
13     2017-09-6 21:03:45    Mary       zzz
14     2017-09-6 21:12:15    Mike       yyy

What's the best way to solve this in Python?

THX & BR bdriven

Upvotes: 0

Views: 178

Answers (1)

jf328
jf328

Reputation: 7351

I think you'll need to loop over the Date series to create a filter.

(Let's assume your Date column for each [Name, Product] group is sorted ascending already.)

def date_filter(s):
    s = s.values
    anchor = s[0]
    res = [False] * len(s)
    res[0] = True

    for idx, x in enumerate(s):
        if (x - anchor) / np.timedelta64(1, 'm') > 15:
            res[idx] = True
            anchor = x

    return res

df[ df.groupby(['Name','Product'])['Date'].transform(date_filter) ]

Upvotes: 1

Related Questions