CODEWITHSUNDEEP
pythonpandas
DeadJoker
DeadJoker

Reputation: 41

How to count frequency in a time period by group using pandas

Suppose I have sorted sample dataframe looks like this

CustomerID CallID       Date

123          1          01/30/2017

123          2          01/31/2017

123          3          02/03/2017

123          4          02/07/2017

123          5          02/08/2017

I want to count how many calls did I received from the same customer in the past 7 days as of each date. The desired output dataframe would be

CustomerID CallID       Date         NumOfCallsOneWeek

123          1          01/30/2017    1

123          2          01/31/2017    2

123          3          02/03/2017    3

123          4          02/07/2017    2

123          5          02/08/2017    3

Note as of 02/07/2017, the 2 calls on 01/30/2017 are received over a week ago, so they are not counted.

How do I do this in pandas? Thank you for your help.

Upvotes: 1

Views: 185

Answers (1)

BENY
BENY

Reputation: 323226

Using rolling with groupby 

df['NumOfCallsOneWeek']=df.groupby('CustomerID').apply(lambda x : x.set_index('Date').rolling('7D').count())['CallID'].values
df
Out[951]: 
   CustomerID  CallID       Date  NumOfCallsOneWeek
0         123       1 2017-01-30                1.0
1         123       2 2017-01-31                2.0
2         123       3 2017-02-03                3.0
3         123       4 2017-02-07                2.0
4         123       5 2017-02-08                3.0

Upvotes: 2

Related Questions