CODEWITHSUNDEEP
pythonpandas
sci-guy
sci-guy

Reputation: 2584

Pandas Find Date Frequency

I have a dataset: 

    login                 id
0   2015-06-22 04:55:00   1
1   2015-06-23 05:55:00   1
2   2015-06-25 04:55:00   2
3   2015-06-26 02:55:00   2
4   2015-07-02 04:55:00   2
5   2015-07-12 04:55:00   3
6   2015-07-13 04:55:00   3
7   2015-07-15 04:55:00   5
8   2015-07-21 04:55:00   5
9   2015-07-22 04:55:00   5
10  2015-07-30 04:55:00   5
11  2015-08-30 04:55:00   5
12  2015-06-02 04:55:00   7
13  2015-07-02 04:55:00   7
14  2015-08-02 04:55:00   7

I am using Pandas in Python for the analysis. I would like to check if a particular id has logged in at least 2 times in a 24 hour period, and store those ids in a list called good_id.

For example: id = 1 would not be stored because they logged in twice but in a 25 hour period. id = 2 would be stored because they have.

etc.

Upvotes: 3

Views: 1237

Answers (3)

Vaishali
Vaishali

Reputation: 38415

Groupby and diff,

df['login'] = pd.to_datetime(df['login'])

df.loc[df.groupby('id')['login'].diff().astype('timedelta64[h]') <= 24, 'id'].unique().tolist()

You get

[2, 3, 5]

Upvotes: 0

Quang Hoang
Quang Hoang

Reputation: 150745

Here's a multiple-step approach:

df['last_log'] = df.groupby('id').login.shift().fillna(pd.to_datetime(0))
df['duration'] = df.login - df.last_log

# good ids
df.id[(df['duration'] <= pd.Timedelta(1, 'd'))].unique()

# output: array([2, 3, 5], dtype=int64)

Upvotes: 2

Ajay Shah
Ajay Shah

Reputation: 414

Make sure you are sorting the dataframe by index and then by login.

import numpy as np

df.sort_values(by=['id','login'],inplace=True)
df['diff'] = df['login'].diff() / np.timedelta64(1,'h')

Upvotes: 0

Related Questions