Efficient shifting based on date within groups in Pandas?

Question

I have a dataframe 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'])})

I want to create lag terms based on time for each group of id1 and id2. For example, t_1 would be the value from the day before. t_2 would be the value from two days before. If there is no value from two-days before, I would like it to be nan. This would be the output for the above dataframe:

    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

I can do this by using the code below, but it is extremely inefficient for a large number of groups - i.e. if I have 10000 x 500 unique combinations of id1 and id2, several days of data for each, and I want 2 lag terms, it takes a long time.

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()

Is there a more efficient way of doing this?

Chelsiechelsy

Following your question except for one thing--what does this have to do with id1 and id2? Looks like you're just using vals. – Buyers

@BradSolomon The only thing this has to do is id1 and id2 is that I am grouping by those - so I only want lag variables within each unique combo of id1 and id2. Therefore I can't just do .shift(1, freq='D') on the entire dataframe – Chelsiechelsy

Answer 1

By using a combination of assigning a group with unstack and shift its possible to avoid the usage of apply, resulting in a great speedup.

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

To compare performance I created a fake dataset of reasonable size:

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)

If we compare performance with the solution of Wen and 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)

If your DataFrame is not that big i would probably prefer Scott Bostons solution because it feels cleaner but if runtime is a concern unstack+shift+join is faster.

EDIT: Added resample to fill missing dates.

Sequestered

This is way faster than the other methods so far. 1.2 million rows and 600,000 unique groups, this takes about ~3 seconds. – Chelsiechelsy

@Chelsiechelsy I added a resample step to fill in missing dates. Without it can lead to wrong results of your data has no continuous dates. – Sequestered

Answer 2

You can try this using set_index, resample and 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)))

Output:

              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

Aliform

Answer 3

By using apply...

shift1

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

Parfait

This is the fastest method so far. Still quite slow on a large dataset, but I'm not sure that it could be sped up without parallelizing. – Chelsiechelsy

@Chelsiechelsy it may speed up with parallel computing . :-) – Parfait

Answer 4

You can try multi index and merge

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')

Coattail