计算python中由另一列分组的同一列、连续行之间的差异

Question

我有一个包含两列的数据框架：UserProductCombo、OrderDates。我对每个用户/产品组都有多个订单日期（每组1到5个日期）

我已经按降序对数据进行了排序，以获得每组的最高订单日期

我想计算每个组的订单日期之间的差异，并将它们放在我的数据框中的一个新列中（以天为单位）

（即OrderDate1-OrderDate2， OrderDate1-OrderDate3， OrderDate1-OrderDate4， OrderDate1-OrderDate5） 如果存在的订单不超过2个，我希望it转移到下一组

样本数据：

>>> bf_recency
        UserProduct               OrderDates
0   12111211/123232  2020-03-12 17:19:16.103
1   12111211/123232  2020-03-12 18:10:45.974
2   12111211/123232  2020-03-11 17:19:16.103
3   12111211/123232  2020-03-10 18:10:45.974
4   12111211/123232  2020-03-10 18:10:45.974
5   165870101/73066  2020-03-12 19:49:15.752

预期输出：

        UserProduct               diff(in days)
0   12111211/123232               N/A
1   12111211/123232               0
2   12111211/123232               1
3   12111211/123232               2
4   12111211/123232               2
5   165870101/73066               N/A

---

到目前为止，我有：

df_frequency =  df.groupby(["UserProduct"])['ORDER_DATE'].nlargest(5).reset_index(name ='OrderDates') 

df_frequency.sort_values(by=['OrderDates'],inplace=True, ascending=False)

df_freq = df_frequency.groupby(['UserProduct'])['OrderDates'].transform(lambda x: x.diff())  #STUCK HERE

Answer 1

您可以这样做：

In [500]: df                                                                                                                                                                                                
Out[500]: 
       UserProduct              OrderDates
0  12111211/123232 2020-03-12 17:19:16.103
1  12111211/123232 2020-03-12 18:10:45.974
2  12111211/123232 2020-03-11 17:19:16.103
3  12111211/123232 2020-03-10 18:10:45.974
4  12111211/123232 2020-03-10 18:10:45.974
5  165870101/73066 2020-03-12 19:49:15.752

In [575]: df['diff(in days)'] = 0
In [583]: grp = df.groupby('UserProduct')['OrderDates']
In [576]: for i, group in grp:  
     ...:     df["diff(in days)"][df.index.isin(group.index)] = group.sub(group.iloc[0])
     ...: 
In [581]: df['diff(in days)'] = df['diff(in days)'].dt.days.abs()                                                                                                                                           

In [582]: df                                                                                                                                                                                                
Out[582]: 
       UserProduct              OrderDates  diff(in days)
0  12111211/123232 2020-03-12 17:19:16.103              0
1  12111211/123232 2020-03-12 18:10:45.974              0
2  12111211/123232 2020-03-11 17:19:16.103              1
3  12111211/123232 2020-03-10 18:10:45.974              2
4  12111211/123232 2020-03-10 18:10:45.974              2
5  165870101/73066 2020-03-12 19:49:15.752              0