# Loop through group and difference lags

I have time-series data (t) that I want to loop through each t and take the difference of the previous five lags t-1 ... t-5.

This code works, but is inefficient. I'm not sure how to increase the performance and efficiency of the code.

Sample data

import pandas as pd
import numpy.random as nr

dat = pd.DataFrame({'t': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10], 'rnum': nr.rand(10)})


Function

def diff_n(idat):
# Get t from group
i = idat['t'].iat[0]

# If one, nothing to difference
if i == 1:
return None

# Get value to loop through and diff
h1 = idat.groupby('t')['rnum'].mean().values

# Subset dat (outside fn)
h2 = dat[(dat.t < i ) & (dat.t >= i - 5)]

# Combine data for return
retdat = pd.DataFrame()

# Loop through t-1 to last obs
for j in range(max(h2.t), min(h2.t), -1):

# Get value to difference from h1
h3 = h2[h2.t == j].groupby('t')['rnum'].mean().values

# Difference
ndiff = (h3 - h1)

# Build df and concat
indat = pd.DataFrame({'t': [i], 'lag': i - j, 'diff': ndiff})
retdat = pd.concat([retdat, indat])
return retdat


Groupby and apply the function to each t

dat.groupby('t').apply(lambda x: diff_n(x))


Output

>>> dat.groupby('t').apply(lambda x: diff_n(x))
t  lag      diff
t
2  0   2    1  0.440644
3  0   3    1 -0.284075
0   3    2  0.156569
4  0   4    1  0.439154
0   4    2  0.155079
0   4    3  0.595723
5  0   5    1 -0.095552
0   5    2  0.343602
0   5    3  0.059527
0   5    4  0.500171
6  0   6    1  0.296337
0   6    2  0.200784
0   6    3  0.639939
0   6    4  0.355864
0   6    5  0.796507
7  0   7    1 -0.749913
0   7    2 -0.453576
0   7    3 -0.549128
0   7    4 -0.109974
0   7    5 -0.394049
8  0   8    1  0.506379
0   8    2 -0.243534
0   8    3  0.052803
0   8    4 -0.042749
0   8    5  0.396405
9  0   9    1 -0.021761
0   9    2  0.484618
0   9    3 -0.265295
0   9    4  0.031042
0   9    5 -0.064510
10 0  10    1  0.409118
0  10    2  0.387357
0  10    3  0.893736
0  10    4  0.143823
0  10    5  0.440160

• Does your t column always contain numbers in consecutive increasing order? Could you have this sequence 't': [1, 2, 3, 4, 5, 6, 3, 7, 8, 9, 2, 10, 5] (repetitive numbers) ? Feb 1, 2020 at 13:07
• @RomanPerekhrest I'm not sure what you mean. But yes, the nature of the problem means t is in consecutive increasing order. Feb 1, 2020 at 15:32
• Grouping by t values with dat.groupby('t') implicitly indicates that there could repetitive values, but in that case - the above code will fail (ValueError: arrays must all be same length .+. KeyError: 't'). Sample input 't': [1, 2, 3, 3, 4, 5, 5, 6, 7, 8, 9, 10]. But if your t column is not supposed to contain duplicate values - then why grouping on t ? Feb 1, 2020 at 18:38
• @RomanPerekhrest Yes, t will contain duplicate values because the lag variable is different across t. I use groupby() to get each t, then apply the function on n lags. Feb 1, 2020 at 21:04
• As I mentioned, your code will not work on this sample input pd.DataFrame({'t': [1, 2, 3, 3, 4, 5, 5, 6, 7, 8, 9, 10], 'rnum': nr.rand(12)}) Feb 2, 2020 at 9:33

### Toward better performance and functionality (reducing code run time twice)

Always give your identifier/variable/function a meaningful name.
Most of your identifiers/variables should be renamed to reflect the actual intention and purpose.
A short sample of better naming:

• dat --> df
• idat --> group
• i variable, which is actually t value --> t
• h1 variable, which is actually rnum mean value --> rnum_mean

and so on ... (see the final implementation below)

diff_n function

The number 5 serves as "subtraction factor" thus instead of hard-coding it it's better to make that factor adjustable and define it as default argument:

def diff_n(group, n=5):


Calling .groupby('t') in idat.groupby('t') and h2[h2.t == j].groupby('t') is redundant as the input/initial dataframe is already grouped by same t values (dat.groupby('t').apply(lambda x: diff_n(x)) and diff_n function will accept sub-dataframe having the same t column values but various rnum column values.

No need to mean().values as mean() is a reducing function and returns a single value.

Instead of generating a new dataframe and accumulate data with pd.concat() on each loop iteration - a single dataframe can be generated at once accepting a list of composed dictionaries.

The final optimized approach:

import pandas as pd
import numpy.random as nr

df = pd.DataFrame({'t': [1, 2, 3, 3, 4, 5, 5, 6, 7, 8, 9, 10], 'rnum': nr.rand(12)})

def diff_n(group, n=5):
t, rnum_mean = group.t.iloc[0], group.rnum.mean()
if t == 1:
return None

# Subset df (outside fn)
diff_rows = df[(df.t < t) & (df.t >= t - n)]
return pd.DataFrame([{'t': t,
'lag': t - i,
'diff': diff_rows[diff_rows.t == i].rnum.mean() - rnum_mean}
for i in range(max(diff_rows.t), min(diff_rows.t), -1)])

res = df.groupby('t').apply(lambda x: diff_n(x))


Time performance comparison (tested on randomly generated sequence of numbers for rnum column):

rnum_data = [0.32371336559866004, 0.10698919887971459, 0.7953413399540619,
0.9868916409057458, 0.9441608945915095, 0.47072752314030053,
0.4508488822488548, 0.028372702128714233, 0.87623218782289,
0.16471466305535765, 0.1, 0.2]
df = pd.DataFrame({'t': [1, 2, 3, 3, 4, 5, 5, 6, 7, 8, 9, 10], 'rnum': rnum_data})
...


The old (initial) approach:

import timeit
print(timeit.timeit("dat.groupby('t').apply(lambda x: diff_n(x))", globals=globals(), number=100))

5.156789435990504


The new approach:

import timeit
print(timeit.timeit("df.groupby('t').apply(lambda x: diff_n(x))", globals=globals(), number=100))

2.5971586129890056

• Great! Thank you for your effort. Much better performance. Feb 3, 2020 at 18:30

1. Reindex your dataframe with t. You are writing a lot of times .t, if you will use index some of pandas index optimization will kick in and your code will be faster.
2. Do not do concat on only 2 items. Concatenate function will work much faster if you will just do all of the concatenation of all 5 elements at once.
3. Why do you need h2? Try to avoid unnecessary coping of the data frame. You can work on the original h1 or even idat since your function does not change anything implicitly.