在 Pandas 中如何基于日期在分组内高效地进行位移操作？

Question

我有一个数据帧 df:

df = pd.DataFrame({'id1':[1,1,1,1,1,4,4,4,6,6],
                     'id2':[45,45,33,33,33,1,1,1,34,34],
                     'vals':[0.1,0.2,0.6,0.1,0.15,0.34,0.12,0.5,0.4,0.45],
                     'date':pd.to_datetime(['2017-01-01','2017-01-02','2017-01-01',
                                            '2017-04-01','2017-04-02','2017-01-01',
                                            '2017-01-02','2017-01-03','2017-01-04',
                                            '2017-01-05'])})

我想根据时间为每个id1和id2组创建滞后项。例如，t_1是前一天的值。t_2将是两天前的值。如果没有两天前的值，我希望它是nan。这将是上述数据框的输出：

    date        id1 id2 vals    t_1   t_2
0   2017-01-01  1   33  0.60    NaN   NaN
1   2017-04-01  1   33  0.10    NaN   NaN
2   2017-04-02  1   33  0.15    0.10  NaN
0   2017-01-01  1   45  0.10    NaN   NaN
1   2017-01-02  1   45  0.20    0.10  NaN
0   2017-01-01  4   1   0.34    NaN   NaN
1   2017-01-02  4   1   0.12    0.34  NaN
2   2017-01-03  4   1   0.50    0.12  0.34
0   2017-01-04  6   34  0.40    NaN   NaN
1   2017-01-05  6   34  0.45    0.40  NaN

我可以使用下面的代码来实现这个功能，但对于大量组合非常低效 - 即如果我有10000 x 500个唯一的 id1 和 id2 组合，每个组合都有数天的数据，并且我想要两个滞后项，那么它需要很长时间。

num_of_lags = 2
for i in range(1, num_of_lags+1):
    final = pd.DataFrame()
    for name, group in df.groupby(['id1', 'id2']):
        temp = group.set_index('date', verify_integrity=False)
        temp = temp.shift(i, 'D').rename(columns={'vals':'t_' + str(i)}).reset_index()
        group = pd.merge(group, temp[['id1', 'id2', 'date', 't_' + str(i)]], 
                         on=['id1', 'id2', 'date'], how='left')
        final = pd.concat([final, group], axis=0)
    df = final.copy()

有更有效的方法来做这件事吗？

- user1566200

---

除了一个问题之外，你的问题是什么与id1和id2有关系呢？看起来你只是在使用vals。 - Brad Solomon

@BradSolomon 这里唯一的问题是与id1和id2有关 - 我正在通过这些分组，所以我只想在每个唯一的id1和id2组合内使用滞后变量。因此，我不能对整个数据框执行.shift(1, freq = 'D')。 - user1566200

Answer 1

通过将分组与取消堆叠和移位相结合，可以避免使用apply，从而实现大幅加速。

def compute_shift(df):
  df['group_no'] = df.groupby(['id1','id2']).ngroup()
  tmp = df[['date','vals','group_no']].set_index(['group_no','date'])\
                                      .unstack('group_no')\
                                      .resample('D').asfreq()
  tmp1 = tmp.shift(1).stack('group_no')['vals'].rename('t_1')
  tmp2 = tmp.shift(2).stack('group_no')['vals'].rename('t_2')

  df = df.join(tmp1, on=['date','group_no'])
  df = df.join(tmp2, on=['date','group_no'])
  return df

compute_shift(df)
date  id1  id2  vals  group_no   t_1   t_2
0 2017-01-01    1   45  0.10         1   NaN   NaN
1 2017-01-02    1   45  0.20         1  0.10   NaN
2 2017-01-01    1   33  0.60         0   NaN   NaN
3 2017-04-01    1   33  0.10         0   NaN   NaN
4 2017-04-02    1   33  0.15         0  0.10   NaN
5 2017-01-01    4    1  0.34         2   NaN   NaN
6 2017-01-02    4    1  0.12         2  0.34   NaN
7 2017-01-03    4    1  0.50         2  0.12  0.34
8 2017-01-04    6   34  0.40         3   NaN   NaN
9 2017-01-05    6   34  0.45         3  0.40   NaN

为了比较性能，我创建了一个合理大小的虚假数据集：

df = pd.DataFrame({'date':np.random.randint(1, 1000, 10**6), 
                   'id1':np.random.randint(1, 100, 10**6),
                   'id2':np.random.randint(1, 100, 10**6),
                   'vals':np.random.random(10**6)})
df = df.drop_duplicates(subset=['date','id1','id2'], keep='last')
df = df.sort_values('date')
dates = pd.date_range('20150101','20180101').to_series().reset_index(drop=True)
df['date'] = df['date'].map(dates)

如果我们将性能与Wen和Scott的解决方案进行比较：

%timeit df.groupby(['id1','id2'],sort=False).apply(lambda x : x['vals'].shift()*((x['date'] -  pd.to_timedelta(1, unit='d')).isin(x['date'].tolist())).replace(False,np.nan))
824 ms ± 19.3 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%timeit df.groupby(['id1','id2'], as_index=False)\
   .apply(lambda x: x.assign(t_1=x.vals.resample('D').asfreq().shift(1),\
                             t_2=x.vals.resample('D').asfreq().shift(2)))
1.38 s ± 25.3 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%timeit compute_shift(df)
96.4 ms ± 2.14 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

如果您的DataFrame不是很大，我可能更喜欢Scott Boston的解决方案，因为它看起来更加简洁，但如果运行时间是一个问题，那么unstack+shift+join会更快。

编辑：添加重新采样以填充缺失的日期。

- P.Tillmann

---

这比迄今为止的其他方法快多了。120万行和60万个唯一组，大约需要~3秒钟。 - user1566200

@user1566200 我添加了一个重新采样步骤来填补缺失的日期。如果没有它，你的数据中没有连续的日期可能会导致错误的结果。 - P.Tillmann

Answer 2

您可以尝试使用 set_index, resample 和 shift 来实现此目的：

df1 = df.set_index('date')
df1.groupby(['id1','id2'], as_index=False)\
   .apply(lambda x: x.assign(t_1=x.vals.resample('D').asfreq().shift(1),
                             t_2=x.vals.resample('D').asfreq().shift(2)))

输出：

              id1  id2  vals   t_1   t_2
  date                                  
0 2017-01-01    1   33  0.60   NaN   NaN
  2017-04-01    1   33  0.10   NaN   NaN
  2017-04-02    1   33  0.15  0.10   NaN
1 2017-01-01    1   45  0.10   NaN   NaN
  2017-01-02    1   45  0.20  0.10   NaN
2 2017-01-01    4    1  0.34   NaN   NaN
  2017-01-02    4    1  0.12  0.34   NaN
  2017-01-03    4    1  0.50  0.12  0.34
3 2017-01-04    6   34  0.40   NaN   NaN
  2017-01-05    6   34  0.45  0.40   NaN

- Scott Boston

Answer 3

通过使用apply...

移位1

df.groupby(['id1','id2'],sort=False).apply(lambda x : x['vals'].shift()*((x['date'] -  pd.to_timedelta(1, unit='d')).isin(x['date'].tolist())).replace(False,np.nan))
Out[775]: 
id1  id2   
1    45   0     NaN
          1    0.10
     33   2     NaN
          3     NaN
          4    0.10
4    1    5     NaN
          6    0.34
          7    0.12
6    34   8     NaN
          9    0.40
dtype: float64

shift2

df.groupby(['id1','id2'],sort=False).apply(lambda x : x['vals'].shift(2)*((x['date'] -  pd.to_timedelta(2, unit='d')).isin(x['date'].tolist())).replace(False,np.nan))
Out[776]: 
id1  id2   
1    45   0     NaN
          1     NaN
     33   2     NaN
          3     NaN
          4     NaN
4    1    5     NaN
          6     NaN
          7    0.34
6    34   8     NaN
          9     NaN
dtype: float64

- BENY

---

这是目前为止最快的方法。在大数据集上仍然相当慢，但我不确定是否能在不并行化的情况下加速。 - user1566200

@user1566200 可能通过并行计算加速。 :-) - BENY

Answer 4

你可以尝试使用多索引和合并

df.index = [df['date'],df['id1'],df['id2']]

df1 = df.copy()

df1.index = [df.index.get_level_values(0) - timedelta(2),df['id1'],df['id2']]

df.merge(df1,left_index=True,right_index=True,how='left')

- Abhishek Sharma