熊猫进阶:如何为在5天内购买至少两次的顾客获得结果？

Question

几个小时以来，我一直试图解决一个问题，但一直坚持下去。以下是问题概要：

import numpy as np
import pandas as pd


df = pd.DataFrame({'orderid': [10315, 10318, 10321, 10473, 10621, 10253, 10541, 10645],
          'customerid': ['ISLAT', 'ISLAT', 'ISLAT', 'ISLAT', 'ISLAT', 'HANAR', 'HANAR', 'HANAR'],
          'orderdate': ['1996-09-26', '1996-10-01', '1996-10-03', '1997-03-13', '1997-08-05', '1996-07-10', '1997-05-19', '1997-08-26']})
df

   orderid customerid   orderdate
0    10315      ISLAT  1996-09-26
1    10318      ISLAT  1996-10-01
2    10321      ISLAT  1996-10-03
3    10473      ISLAT  1997-03-13
4    10621      ISLAT  1997-08-05
5    10253      HANAR  1996-07-10
6    10541      HANAR  1997-05-19
7    10645      HANAR  1997-08-26

我想选择所有客户谁已订购超过一次在5天内。

例如，在这里，只有客户在5天内订购，他已经做了两次。

我想获得以下格式的输出：

所需输出

customerid  initial_order_id    initial_order_date  nextorderid nextorderdate   daysbetween
ISLAT       10315               1996-09-26          10318       1996-10-01      5
ISLAT       10318               1996-10-01          10321       1996-10-03      2

Answer 1

您可以使用sort_values和diff创建列‘days间隔’。在获得以下命令之后，您可以为每个customerid提供一次join df和df，并对所有数据进行shift。最后，在query中满足“daysbetween_next”中的天数：

df['daysbetween'] = df.sort_values(['customerid', 'orderdate'])['orderdate'].diff().dt.days
df_final = df.join(df.groupby('customerid').shift(-1), 
                   lsuffix='_initial', rsuffix='_next')\
             .drop('daysbetween_initial', axis=1)\
             .query('daysbetween_next <= 5 and daysbetween_next >=0')

Answer 2

首先，为了能够以天数计算差异，请将orderdate列转换为日期时间。

df.orderdate = pd.to_datetime(df.orderdate)

然后定义以下函数：

def fn(grp):
    return grp[(grp.orderdate.shift(-1) - grp.orderdate) / np.timedelta64(1, 'D') <= 5]

并最终适用于：

df.sort_values(['customerid', 'orderdate']).groupby('customerid').apply(fn)

Answer 3

这是有点棘手，因为可以有任意数量的购买对在5天的窗口。这是利用merge_asof的一个很好的用例，它允许对数据进行近似但不精确的匹配。

输入数据

import pandas as pd
df = pd.DataFrame({'orderid': [10315, 10318, 10321, 10473, 10621, 10253, 10541, 10645],
          'customerid': ['ISLAT', 'ISLAT', 'ISLAT', 'ISLAT', 'ISLAT', 'HANAR', 'HANAR', 'HANAR'],
          'orderdate': ['1996-09-26', '1996-10-01', '1996-10-03', '1997-03-13', '1997-08-05', '1996-07-10', '1997-05-19', '1997-08-26']})

定义一个函数，用于计算给定客户数据的对购买量。

def compute_purchase_pairs(df):
    # Approximate self join on the date, but not exact.
    df_combined = pd.merge_asof(df,df, left_index=True, right_index=True,
                                suffixes=('_first', '_second') , allow_exact_matches=False)
    # Compute difference
    df_combined['timedelta'] = df_combined['orderdate_first'] - df_combined['orderdate_second']
    return df_combined

进行预处理并计算成对

# Convert to datetime
df['orderdate'] = pd.to_datetime(df['orderdate'])
# Sort dataframe from last buy to newest (groupby will not change this order)
df2 = df.sort_values(by='orderdate', ascending=False)
# Create an index for joining
df2 = df.set_index('orderdate', drop=False)

# Compute puchases pairs for each customer
df_differences = df2.groupby('customerid').apply(compute_purchase_pairs)
# Show only the ones we care about
result = df_differences[df_differences['timedelta'].dt.days<=5]
result.reset_index(drop=True)

结果

   orderid_first customerid_first orderdate_first  orderid_second  \
0          10318            ISLAT      1996-10-01         10315.0   
1          10321            ISLAT      1996-10-03         10318.0   

  customerid_second orderdate_second timedelta  
0             ISLAT       1996-09-26    5 days  
1             ISLAT       1996-10-01    2 days