Pandas counting/adding values by date and id

user_9530957 0
Viewed 45

I want to count all orders which got paid until the date of each order.

Input:
enter image description here

Expected output:
enter image description here

The following code works but is extremely slowly. Taking upwards of 10 hours for 100k+ rows. There is certainly a better way.

orders_paid,orders_inkasso = []

for y,row in df_dated_filt.iterrows():
    x = x + 1
    orders_paid.append(df_dated_filt[(df_dated_filt["order_id"] != row["order_id"]) & (df_dated_filt["m_order_paid"] == 1) & 
                      (df_dated_filt["customer_id"] == row["customer_id"]) & 
                      (pd.to_datetime(df_dated_filt['order_date'])<pd.to_datetime(row['order_date']))]["order_id"].count())
df_dated_filt["m_orders_paid"] = orders_paid
pandas numpy python
1 Answers

Use sort_values to get dates in ascending order, then groupby transform with cumsum + shift to get total paid based up before current date:

df['order_date'] = pd.to_datetime(df['order_date'])

df['total_paid'] = (
    df.sort_values('order_date')
        .groupby('customer_id')['order_paid']
        .transform(lambda g: g.cumsum().shift(fill_value=0))
)

df:

   customer_id  order_id order_date  order_paid  total_paid
0            1        12 2019-01-06           0           1
1            1        22 2019-01-01           1           0
2            1        31 2019-01-03           0           1
3            2        34 2018-05-08           0           0
4            2        44 2018-05-12           1           0
5            2        48 2018-05-29           1           1
6            2        55 2018-05-30           1           2

(Note I think that the first row should be 1 since that customer has one paid order on 2019-01-01, and row one is on 2019-01-06 which is after 2019-01-01. Additionally the provided code also puts a 1 in the first row.)

Complete Working Example:

import pandas as pd

df = pd.DataFrame({
    'customer_id': [1, 1, 1, 2, 2, 2, 2],
    'order_id': [12, 22, 31, 34, 44, 48, 55],
    'order_date': ['2019-01-06', '2019-01-01', '2019-01-03',
                   '2018-05-08', '2018-05-12', '2018-05-29', '2018-05-30'],
    'order_paid': [0, 1, 0, 0, 1, 1, 1]
})
df['order_date'] = pd.to_datetime(df['order_date'])

df['total_paid'] = (
    df.sort_values('order_date')
        .groupby('customer_id')['order_paid']
        .transform(lambda g: g.cumsum().shift(fill_value=0))
)
print(df)

Assuming more operations depend on dates being in ascending order, it may be beneficial to sort the DataFrame by customer_id and order_date:

df = df.sort_values(['customer_id', 'order_date'])

Then future operations do not need to sort:

df['total_paid'] = (
    df.groupby('customer_id')['order_paid']
        .transform(lambda g: g.cumsum().shift(fill_value=0))
)

Then after all ordered date dependent operations are complete use:

df = df.sort_values(['customer_id', 'order_id'])

to restore the original order of the frame.

user_154978880
Related