I have this DataFrame:
dic = {'users' : ['A','A','B','A','A','B','A','A','A','A','A','B','A'],
'product' : [1,1,2,2,1,2,1,2,1,1,2,1,1],
'action' : ['see', 'see', 'see', 'see', 'buy', 'buy', 'see', 'see', 'see', 'see', 'buy', 'buy', 'buy']
}
df = pd.DataFrame(dic, columns=dic.keys())
df
users product action
0 A 1 see
1 A 1 see
2 B 2 see
3 A 2 see
4 A 1 buy
5 B 2 buy
6 A 1 see
7 A 2 see
8 A 1 see
9 A 1 see
10 A 2 buy
11 B 1 buy
12 A 1 buy
What I need is a column that count How many times did each user see a product before buying it.
The result should be something like this:
dic = {'users' : ['A','A','B','A','A','B','A','A','A','A','A','B','A'],
'product' : [1,1,2,2,1,2,1,2,1,1,2,1,1],
'action' : ['see', 'see', 'see', 'see', 'buy', 'buy', 'see', 'see', 'see', 'see', 'buy', 'buy', 'buy'],
'see_before_buy' : [1,2,1,1,2,1,1,2,2,3,2,0,3]
}
users product action see_before_buy
0 A 1 see 1
1 A 1 see 2
2 B 2 see 1
3 A 2 see 1
4 A 1 buy 2
5 B 2 buy 1
6 A 1 see 1
7 A 2 see 2
8 A 1 see 2
9 A 1 see 3
10 A 2 buy 2
11 B 1 buy 0
12 A 1 buy 3
Could somebody help me?
You may need create a addtional key for groupby , by using cumsum after shfit
addkey=df.groupby(['user','#product']).action.apply(lambda x : x.eq('buy').shift().fillna(0).cumsum())
df['seebefore']=df.action.eq('see').groupby([df.user,df['#product'],addkey]).cumsum()
df
Out[131]:
index user #product action seebefore
0 0 A 1 see 1.0
1 1 A 1 see 2.0
2 2 B 2 see 1.0
3 3 A 2 see 1.0
4 4 A 1 buy 2.0
5 5 B 2 buy 1.0
6 6 A 1 see 1.0
7 7 A 2 see 2.0
8 8 A 1 see 2.0
9 9 A 1 see 3.0
10 10 A 2 buy 2.0
11 11 B 1 buy 0.0
12 12 A 1 buy 3.0