我有個問題。我想計算客戶第二天有多少物品到達。這意味著例如我有客戶，customerId == 1我想查看當天2022-05-04有多少包裹到達第二天。第二天將是2022-05-05。如果我們將客戶的兩天加在一起，我們得到 2。最后一個日期不應該有值，例如2022-05-08 == None。
我試圖計算下一個日期。但是我如何計算和計算第二天到達的物品數量？
資料框：
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
代碼：
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
我想要的是：
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
uj5u.com熱心網友回復：
注釋代碼
# 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'))
結果
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/483918.html
上一篇：適用于時間序列資料的時間增量