Counting how many items arrive the next day over time

user_17136258 0
Viewed 53

I have a problem. I want to calculate how many items arrived the next day for the customer. This means for example I have the customer with the customerId == 1 and I want to look at the day 2022-05-04 to see how many parcels arrived the next day. The next day would be 2022-05-05. If we add the two days together for the customer, we get 2. The last date should not have a value, for example 2022-05-08 == None.

I have tried to calculate the next date. But how can I count and calculate how many items arrived on the next day?

Dataframe:

   customerId    fromDate
0           1  2022-05-04
1           1  2022-05-05
2           1  2022-05-05
3           1  2022-05-06
4           1  2022-05-08
5           2  2022-05-10
6           2  2022-05-12

Code:

import pandas as pd
import datetime

d = {'customerId': [1, 1, 1, 1, 1, 2, 2],
     'fromDate': ['2022-05-04', '2022-05-05', '2022-05-05', '2022-05-06', '2022-05-08', '2022-05-10', '2022-05-12']
    }
df = pd.DataFrame(data=d)

def nearest(items, pivot):
  try:
    return min(items, key=lambda x: abs(x - pivot))
  except:
    return None

df['fromDate'] = pd.to_datetime(df['fromDate'], errors='coerce').dt.date
df["count_next_date"] = df['fromDate'].apply(lambda x: nearest(df['fromDate'], x)) 

[OUT]
   customerId    fromDate  count_next
0           1  2022-05-04  2022-05-04
1           1  2022-05-05  2022-05-05
2           1  2022-05-05  2022-05-05
3           1  2022-05-07  2022-05-07
4           2  2022-05-10  2022-05-10
5           2  2022-05-12  2022-05-12

What I want:

   customerId    fromDate  count_next
0           1  2022-05-04  2
1           1  2022-05-05  1
2           1  2022-05-05  1
3           1  2022-05-06  0
4           1  2022-05-08  None
5           2  2022-05-10  0
6           2  2022-05-12  None
pandas dataframe datetime python
1 Answers

Annotated code

# Convert the column to datetime
df['fromDate'] = pd.to_datetime(df['fromDate'])

# Group by custid and prev date to calculate 
# number of items arriving next day
date = df['fromDate'] - pd.DateOffset(days=1)
items = df.groupby(['customerId', date], as_index=False).size()

# Merge the item count with original df 
out = df.merge(items, how='left')

# Fill the nan values with 0
out['size'] = out['size'].fillna(0)

# mask the item count corresponding to last date for each customerid
out['size'] = out['size'].mask(~out['customerId'].duplicated(keep='last'))

Result

print(out)

   customerId   fromDate  size
0           1 2022-05-04   2.0
1           1 2022-05-05   1.0
2           1 2022-05-05   1.0
3           1 2022-05-06   0.0
4           1 2022-05-08   NaN
5           2 2022-05-10   0.0
6           2 2022-05-12   NaN
user_128331660
Related