python - 在 groupby 数据框中查找给定日期的最接近日期(Python)

Question

我正在尝试在我的 pandas 数据框中生成 Last_Payment_Date 字段，并且需要在给定的 Order_Date 之前找到最接近的 Payment_Date对于每个客户(即 groupby)。

Payment_Date 总是在 Order_Date 之后，但可能需要不同的时间段，这很难使用排序和移位找到最近的日期。

Masking似乎是一种可能的方式，但我一直无法想出如何使用它的方法。

感谢我能得到的所有帮助!

Cust_No  Order_Date  Payment_Date  Last_Payment_Date
      A    5/8/2014      6/8/2014                Nat
      B    6/8/2014      1/5/2015                Nat
      B    7/8/2014      7/8/2014                Nat
      A    8/8/2014      1/5/2015           6/8/2014
      A    9/8/2014     10/8/2014           6/8/2014
      A  10/11/2014    12/11/2014          10/8/2014
      B  11/12/2014      1/1/2015           7/8/2014
      B    1/2/2015      2/2/2015           1/1/2015
      A    2/5/2015      5/5/2015           1/5/2015
      B    3/5/2015      4/5/2015           2/2/2015

Answer 1

Series.searchsorted很大程度上做你想做的——它可用于查找 Order_Date 在 Payment_Date 中的位置。在特别是，它返回对应于每个位置的序号索引需要插入 Order_Date 以保持 Payment_Dates排序。例如，假设

In [266]: df['Payment_Date']
Out[266]: 
0   2014-06-08
2   2014-07-08
4   2014-10-08
5   2014-12-11
6   2015-01-01
1   2015-01-05
3   2015-01-05
7   2015-02-02
9   2015-04-05
8   2015-05-05
Name: Payment_Date, dtype: datetime64[ns]

In [267]: df['Order_Date']
Out[267]: 
0   2014-05-08
2   2014-07-08
4   2014-09-08
5   2014-10-11
6   2014-11-12
1   2014-06-08
3   2014-08-08
7   2015-01-02
9   2015-03-05
8   2015-02-05
Name: Order_Date, dtype: datetime64[ns]

然后 searchsorted 返回

In [268]: df['Payment_Date'].searchsorted(df['Order_Date'])
Out[268]: array([0, 1, 2, 3, 3, 0, 2, 5, 8, 8])

第一个值，例如0，表示Order_Date，2014-05-08，必须插入序号索引 0(在 Payment_Date 之前2014-06-08) 以保持 Payment_Date 的排序顺序。第二个值，1，表示 Order_Date，2014-07-08，必须插入序号索引 1(在 Payment_Date 2014-06-08 之后和 2014-07-08 之前)使 Payment_Date 保持有序。其他指标依此类推。

当然，现在有一些复杂的问题:

1. Payment_Dates 需要按照 searchsorted 的排序顺序返回有意义的结果:

   df = df.sort_values(by=['Payment_Date'])    
   

2. 我们需要按Cust_No

   分组

   grouped = df.groupby('Cust_No')
   

3. 我们想要Payment_Date 的索引在订单日期。因此，我们确实需要将索引减一:

   idx = grp['Payment_Date'].searchsorted(grp['Order_Date']) 
   result = grp['Payment_Date'].iloc[idx-1]
   

这样 grp['Payment_Date'].iloc[idx-1] 就会获取 prior Payment_Date。

1. 当searchsorted返回0时，Order_Date小于所有Payment_Date。在这种情况下，我们需要一个 NaT。

   result[idx == 0] = pd.NaT
   

---

所以把它们放在一起，

import pandas as pd
NaT = pd.NaT
T = pd.Timestamp
df = pd.DataFrame({
    'Cust_No': ['A', 'B', 'B', 'A', 'A', 'A', 'B', 'B', 'A', 'B'],
    'expected': [
        NaT,  NaT,  NaT, T('2014-06-08'), T('2014-06-08'), T('2014-10-08'), 
        T('2014-07-08'), T('2015-01-01'), T('2015-01-05'), T('2015-02-02')], 
    'Order_Date': [
        T('2014-05-08'), T('2014-06-08'), T('2014-07-08'), T('2014-08-08'), 
        T('2014-09-08'), T('2014-10-11'), T('2014-11-12'), T('2015-01-02'), 
        T('2015-02-05'), T('2015-03-05')], 
    'Payment_Date': [
        T('2014-06-08'), T('2015-01-05'), T('2014-07-08'), T('2015-01-05'), 
        T('2014-10-08'), T('2014-12-11'), T('2015-01-01'), T('2015-02-02'), 
        T('2015-05-05'), T('2015-04-05')]})

def last_payment_date(s, df):
    grp = df.loc[s.index]
    idx = grp['Payment_Date'].searchsorted(grp['Order_Date']) 
    result = grp['Payment_Date'].iloc[idx-1]
    result[idx == 0] = pd.NaT
    return result

df = df.sort_values(by=['Payment_Date'])    
grouped = df.groupby('Cust_No')
df['Last_Payment_Date'] = grouped['Payment_Date'].transform(last_payment_date, df)

print(df)

产量

  Cust_No Order_Date Payment_Date   expected Last_Payment_Date
0       A 2014-05-08   2014-06-08        NaT               NaT
2       B 2014-07-08   2014-07-08        NaT               NaT
4       A 2014-09-08   2014-10-08 2014-06-08        2014-06-08
5       A 2014-10-11   2014-12-11 2014-10-08        2014-10-08
6       B 2014-11-12   2015-01-01 2014-07-08        2014-07-08
1       B 2014-06-08   2015-01-05        NaT               NaT
3       A 2014-08-08   2015-01-05 2014-06-08        2014-06-08
7       B 2015-01-02   2015-02-02 2015-01-01        2015-01-01
9       B 2015-03-05   2015-04-05 2015-02-02        2015-02-02
8       A 2015-02-05   2015-05-05 2015-01-05        2015-01-05