It isn't hard to write an elementary code (after a groupby by name) by for, while and ... for
the following problem. Is there a more systematic way of solving it by more advanced commands of python, pandas or SQL ?
In a website, users do actions 'enter' , 'exit' and 'purchase'
df1 = pd.DataFrame({'name' : ['Bob' , 'Alice', 'Ali', 'Bob', '...'] ,
'action' : ['enter', 'enter', 'enter', 'purchase' , '...'],
'time' :[4, 10, 12, 18, '...']})
We want to add next to purchases, the time that purchase takes:
df1['T'] = ['','','',14,'...']
To be more specific this times for each user are independent from other users. consider a specific user and his\her actions:
action = ['enter' , 'exit' , 'enter' , 'purchase' , 'purchase', 'exit', 'enter', 'purchase', 'purchase', 'purchase', 'exit', 'enter']
time = [2 , 10, 32, 50, 60, 75, 100, 113,114,115, 140, 145 ]
df2 = pd.DataFrame({'action' : action, 'time' : time})
We want the following:
df2['T'] = ['' , '', '', 18,18, '' , '' , 10, 10 , 10, '','']
We consider the time of last purchase in each collection of successive purchases (may be only one) as a cut for presence of user in site (so we cut time to [2,60] and [60,115] and ignore actions of each user which are after last of his\her purchases). We subtract times from exit to enter that forms interval and we get times 36 and 30. Then we divide this time equally between successive purchases, if there is.