Conditional grouped iteration over a Pandas DataFrame

user_17693361 0
Viewed 27

I have the following DataFrame:

user event_id event_creation event_b USD
a 1 2021/07/01 NaN 10
a 2 2021/07/02 2021/07/17 10
a 3 2021/07/22 2021/08/02 10
a 4 2021/07/24 2021/08/02 10
a 5 2021/07/24 2021/08/06 10
a 6 2021/08/16 2021/01/29 10
b 7 2021/08/18 2021/10/14 10

I want to create a new column based on a conditional aggregated sum_cum.
Grouped by user, iterate over the event_ids,
For each event_id look at all previous events and sum the USD amount of all the events in which the event_b date is smaller than the current event's creation date.
So i will get the following DataFrame:

user event_id event_creation event_b USD sum_cum
a 1 2021/07/01 NaN 10 0
a 2 2021/07/02 2021/07/17 10 0
a 3 2021/07/22 2021/08/02 10 10
a 4 2021/07/24 2021/08/02 10 10
a 5 2021/07/24 2021/08/06 10 10
a 6 2021/08/16 2021/01/29 10 40
b 7 2021/08/18 2021/10/14 10 0

I tried to create loops but didn't bare any fruits, i hope the explanation is clear, thank you for helping :)

pandas dataframe python
1 Answers

Try this:

df.apply(
    lambda x: df[
        (df.event_b < x.event_creation) &
        (df.event_id < x.event_id) &
        (df.user == x.user)
    ].USD.sum(),
    axis=1)

You can also use groupby instead of df.user == x.user

If you have a very large dataset it might become slow, since you are slicing the df for each row.

user_71944740
Related