Remove rows in pandas dataframe after certain value (while for looping?)

user_12045573 0
Viewed 395

I have a dataframe with the users' history in online shop. Example:

In [1]:   a = pd.DataFrame([[1, 'view', 'a'], [1, 'cart', 'b'], [2, 'cart','b'], [2, 'cart','c'], [2, 'view','d'], 
                 [2, 'purchase','d'], [2, 'view','e'], [2, 'cart','e']],
                columns=['user_session', 'event_type', 'product_id'])

In [2]: df

Out[2]: 
   user_session  event_type    product_id
0  1             view            a
1  1             cart            b
2  2             cart            b
3  2             cart            c
4  2             view            d
5  2             purchase        d
6  2             view            e
7  2             cart            e

There can be more purchases pro one user_session. I need to delete ALL further rows in a session as soon as first purchase occurs. The partial solution I found here: Removing rows after a certain string in pandas and it is:

df.loc[:(df['event_type'] == 'purchase').idxmax()]

But I need to iterate thru a huge dataset with millions of rows. Is it a good idea to use for loop here?It should be probably better opportunity.

Another way would be probably to build up a list of indexes of rows that I want to delete as mentioned here: dropping a row while iterating through pandas dataframe


for i in df.index:
    ....
    if {make your decision here}:
        indexes_to_drop.append(i)
    ....

df.drop(df.index[indexes_to_drop], inplace=True )

But again, is there any other way?

Many thanks in advance!

pandas dataframe session python
2 Answers

You can check the condition and then use cummax to set the condition to True after the first time it occurs within group. Then we slice the DataFrame:

mask = ~(a['event_type'].eq('purchase').groupby(a['user_session']).cummax())

a[mask]
#   user_session event_type product_id
#0             1       view          a
#1             1       cart          b
#2             2       cart          b
#3             2       cart          c
#4             2       view          d

Or if you need to also keep the purchase row use two groupbys, with a shift for the second:

mask = ~(a['event_type'].eq('purchase')
          .groupby(a['user_session']).cummax()
          .groupby(a['user_session']).shift()
          .fillna(False))

a[mask]
#   user_session event_type product_id
#0             1       view          a
#1             1       cart          b
#2             2       cart          b
#3             2       cart          c
#4             2       view          d
#5             2   purchase          d
user_43333590

Try:

to_remove = (a['event_type'].eq('purchase')
                .groupby(a['user_session'])
                .apply(lambda x: x.shift(fill_value=0).cumsum())
            )
a[to_remove == 0]

Output:

   user_session event_type product_id
0             1       view          a
1             1       cart          b
2             2       cart          b
3             2       cart          c
4             2       view          d
5             2   purchase          d

If you don't want the first purchase event, replace apply(lambda ...) with just .cumsum()

user_42384080
Related