Python Pandas Counts using rolling time window

Question

I have a dataframe which looks like this

customerId Date         Amount_Spent
123        01/01/2018   500
456        01/01/2018   250
123        02/01/2018   300
456        02/01/2018   100

I want to count customers (distinct/non-distinct) who have spent more than 200 on two consecutive days.

So I expect to get

customerId Date1        Date2         Total_Amount_Spent
123        01/01/2018   02/01/2018    800

Can someone help me with this?

Seanny123 · Accepted Answer · 2018-12-10 15:36:43Z

2

There is two check , one check the days diff, and another is check the amount always more than 100 which using all , then both situation satisfied we select the ID.

s=df.groupby('customerId').agg({'Date':lambda x : (x.iloc[0]-x.iloc[-1]).days==-1,'Amount_Spent':lambda x : (x>100).all()}).all(1)
newdf=df.loc[df.customerId.isin(s.index),]
newdf
Out[1242]:
   customerId       Date  Amount_Spent
0         123 2018-01-01           500
2         123 2018-01-02           300

Using groupby + agg again to get the format you need

newdf.groupby('customerId').agg({'Date':['first','last'],'Amount_Spent':'sum'})
Out[1244]: 
                 Date            Amount_Spent
                first       last          sum
customerId                                   
123        2018-01-01 2018-01-02          800

edited Dec 10, 2018 at 15:36

Seanny123

9,44617 gold badges74 silver badges136 bronze badges

answered Dec 10, 2018 at 14:53

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Khurram Majeed Over a year ago

@w-b Can you please provide some explanation of what your first code block is doing?

BENY Over a year ago

@checking two date different whether it is continue or not , and check all the value in group should be greater than 100

LOrD_ARaGOrN Over a year ago

@w-b can u plz check your code. Its not working. I tried to fix it but no luck. trying to understand how to use lambda in agg().

Collectives™ on Stack Overflow

Python Pandas Counts using rolling time window

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related