python - 从另一个数据帧计算日期之间的出现次数(给定 ID 值)

Question

Pandas: select DF rows based on another DF是我能找到的与我的问题最接近的答案，但我认为它并不能完全解决问题。

无论如何，我正在使用两个非常大的 pandas 数据帧(所以速度是一个考虑因素)，df_emails 和 df_trips，它们都已经按 CustID 排序，然后按日期排序。

df_emails 包含我们向客户发送电子邮件的日期，它看起来像这样:

   CustID   DateSent
0       2 2018-01-20
1       2 2018-02-19
2       2 2018-03-31
3       4 2018-01-10
4       4 2018-02-26
5       5 2018-02-01
6       5 2018-02-07

df_trips 包括顾客来店的日期以及他们花了多少钱，它看起来像这样:

   CustID   TripDate  TotalSpend
0       2 2018-02-04          25
1       2 2018-02-16         100
2       2 2018-02-22         250
3       4 2018-01-03          50
4       4 2018-02-28         100
5       4 2018-03-21         100
6       8 2018-01-07         200

基本上，我需要做的是在发送的每封电子邮件之间找出每位客户的旅行次数和总支出。如果这是最后一次为给定客户发送电子邮件，我需要在电子邮件之后但在数据结束之前 (2018-04-01) 找到旅行总数和总支出。所以最终的数据框看起来像这样:

   CustID   DateSent NextDateSentOrEndOfData  TripsBetween  TotalSpendBetween
0       2 2018-01-20              2018-02-19           2.0              125.0
1       2 2018-02-19              2018-03-31           1.0              250.0
2       2 2018-03-31              2018-04-01           0.0                0.0
3       4 2018-01-10              2018-02-26           0.0                0.0
4       4 2018-02-26              2018-04-01           2.0              200.0
5       5 2018-02-01              2018-02-07           0.0                0.0
6       5 2018-02-07              2018-04-01           0.0                0.0

尽管我已尽力以 Python/Pandas 友好的方式做到这一点，但我能够实现的唯一准确解决方案是通过 np.where、移位和循环。解决方案如下所示:

df_emails["CustNthVisit"] = df_emails.groupby("CustID").cumcount()+1

df_emails["CustTotalVisit"] = df_emails.groupby("CustID")["CustID"].transform('count')

df_emails["NextDateSentOrEndOfData"] = pd.to_datetime(df_emails["DateSent"].shift(-1)).where(df_emails["CustNthVisit"] != df_emails["CustTotalVisit"], pd.to_datetime('04-01-2018'))

for i in df_emails.index:
    df_emails.at[i, "TripsBetween"] = len(df_trips[(df_trips["CustID"] == df_emails.at[i, "CustID"]) & (df_trips["TripDate"] > df_emails.at[i,"DateSent"]) & (df_trips["TripDate"] < df_emails.at[i,"NextDateSentOrEndOfData"])])

for i in df_emails.index:
    df_emails.at[i, "TotalSpendBetween"] = df_trips[(df_trips["CustID"] == df_emails.at[i, "CustID"]) & (df_trips["TripDate"] > df_emails.at[i,"DateSent"]) & (df_trips["TripDate"] < df_emails.at[i,"NextDateSentOrEndOfData"])].TotalSpend.sum()

df_emails.drop(['CustNthVisit',"CustTotalVisit"], axis=1, inplace=True)

但是，%%timeit 显示这仅在上面显示的七行上花费了 10.6 毫秒，这使得该解决方案在我大约 1,000,000 行的实际数据集上几乎不可行。有谁知道这里有更快且可行的解决方案吗？

Answer 1

将下一个日期列添加到电子邮件

df_emails["NextDateSent"] = df_emails.groupby("CustID").shift(-1)

对 merge_asof 进行排序，然后合并到最近的位置以创建行程查找表

df_emails = df_emails.sort_values("DateSent")
df_trips = df_trips.sort_values("TripDate")
df_lookup = pd.merge_asof(df_trips, df_emails, by="CustID", left_on="TripDate",right_on="DateSent", direction="backward")

聚合所需数据的查找表。

df_lookup = df_lookup.loc[:, ["CustID", "DateSent", "TotalSpend"]].groupby(["CustID", "DateSent"]).agg(["count","sum"])

将其加入到电子邮件表中。

df_merge = df_emails.join(df_lookup, on=["CustID", "DateSent"]).sort_values("CustID")

我选择将 NaN 保留为 NaN，因为我不喜欢填充默认值(如果您愿意，您可以稍后再做，但是如果您输入，您将无法轻易区分存在的事物和不存在的事物早期的默认值)

   CustID   DateSent NextDateSent  (TotalSpend, count)  (TotalSpend, sum)
0       2 2018-01-20   2018-02-19                  2.0              125.0
1       2 2018-02-19   2018-03-31                  1.0              250.0
2       2 2018-03-31          NaT                  NaN                NaN
3       4 2018-01-10   2018-02-26                  NaN                NaN
4       4 2018-02-26          NaT                  2.0              200.0
5       5 2018-02-01   2018-02-07                  NaN                NaN
6       5 2018-02-07          NaT                  NaN                NaN