I have a pd.dataframe that looks like this:
cookie date channel goal_reached
cookie_1 2020-01-12 paid 0
cookie_1 2020-02-17 organic 0
cookie_1 2020-04-02 referral 1
cookie_1 2020-05-13 direct 0
cookie_1 2020-05-16 direct 0
cookie_2 2020-01-18 referral 0
cookie_2 2020-03-13 paid 1
cookie_2 2020-04-01 organic 0
cookie_2 2020-05-16 organic 0
cookie_2 2020-05-22 paid 0
cookie_3 2020-01-13 direct 0
cookie_3 2020-04-14 organic 0
cookie_3 2020-06-10 organic 0
I want to to group by for each cookie value and drop all the rows after the date with goal_reached value 1. If for cookie there is no goal_reached value 1, i need take all rows.
I want to have an end output like this:
cookie channel goal_reached
cookie_1 paid > organic > referral 1
cookie_2 referral > paid 1
cookie_3 direct > organic > organic 0
I have the following code, but it can group by with all the rows:
df = df.sort_values(['cookie', 'date'],
ascending=[False, True])
df = df.groupby('cookie', as_index=False).agg({'channel': lambda x: "%s" % ' > '.join(x), 'reg_goal': 'max'})
You can try this:
df = df[df.groupby('cookie')['goal_reached'].transform(lambda x: x.cumsum().cumsum()).lt(2)]
df = df.groupby('cookie').agg({'channel': lambda x: ' > '.join(x), 'goal_reached': 'max'})
print(df)
channel goal_reached
cookie
cookie_1 paid > organic > referral 1
cookie_2 referral > paid 1
cookie_3 direct > organic > organic 0