Counting rolling occurences depending on specific conditions faster in Pandas dataframe

user_7164176 0
Viewed 102

I have a Pandas dataframe like this:

name number
A   0.8466
A   0.8463
A   0.8482
A   0.8455
A   0.8423
A   0.8405
A   0.842
A   0.8453
A   0.8419
A   0.8394
A   0.8376
A   0.8368
A   0.8388
A   0.8392
A   0.8409
A   0.8415
A   0.8424
A   0.8425
A   0.8433
A   0.8412
A   0.8397

I would like to count how many times an upper and how many times a lower condition is met in a sliding window of 3 number values. I have a working code:

df['Upper'] = 0
df['Lower'] = 0
for i in range(len(df)):
    if i < 1:
        df['Upper'].iloc[i] = 0
        df['Lower'].iloc[i] = 0
    else:
        chip_sum_r = 0
        chip_sum_s = 0
        for j in range(3):
            if df['name'].iloc[i-j] == df['name'].iloc[i]:
                if df['number'].iloc[i-j] <= df['number'].iloc[i]*1.003 and df['number'].iloc[i-j] >   df['number'].iloc[i]:
                    chip_sum_r += 1
                if df['number'].iloc[i-j] >= df['number'].iloc[i]*0.997 and df['number'].iloc[i-j] < df['number'].iloc[i]:
                   chip_sum_s += 1
        df['Upper'].iloc[i] = chip_sum_r
        df['Lower'].iloc[i] = chip_sum_s

In the end I get:

    name  number   Upper  Lower
0     A  0.8466      0      0
1     A  0.8463      1      0
2     A  0.8482      0      2
3     A  0.8455      1      0
4     A  0.8423      0      0
5     A  0.8405      1      0
6     A  0.8420      1      1
7     A  0.8453      0      0
8     A  0.8419      1      0
9     A  0.8394      1      0
10    A  0.8376      1      0
11    A  0.8368      1      0
12    A  0.8388      0      2
13    A  0.8392      0      2
14    A  0.8409      0      2
15    A  0.8415      0      2
16    A  0.8424      0      2
17    A  0.8425      0      2
18    A  0.8433      0      2
19    A  0.8412      2      0
20    A  0.8397      1      0

However this is very slow for large number of data points. Is there a way to speed this up, I am not sure whether a vectorized approach would work here?

pandas dataframe python performance
3 Answers

Sure we can do it via numpy broadcast

n=3
s=df.number.values
s=s[:,None]/s
df['Lower']=np.sum(np.tril(np.triu(np.tril((s<=1.003) & (s>1)), -n+1)), 1)
df['Upper']=np.sum(np.tril(np.triu(np.tril((s<1) & (s>=0.997)), -n+1)), 1)
df
Out[52]: 
   name  number  Lower  Upper
0     A  0.8466      0      0
1     A  0.8463      0      1
2     A  0.8482      2      0
3     A  0.8455      0      1
4     A  0.8423      0      0
5     A  0.8405      0      1
6     A  0.8420      1      1
7     A  0.8453      0      0
8     A  0.8419      0      1
9     A  0.8394      0      1
10    A  0.8376      0      1
11    A  0.8368      0      1
12    A  0.8388      2      0
13    A  0.8392      2      0
14    A  0.8409      2      0
15    A  0.8415      2      0
16    A  0.8424      2      0
17    A  0.8425      2      0
18    A  0.8433      2      0
19    A  0.8412      0      2
20    A  0.8397      0      1
user_79645270

Although, I am not sure if this will speed up your performance that much after testing (10 milliseconds), You can use .shift() as an alternative to looping through rows and np.where() for your conditions. You would only loop 3 total times in the solution below, but I testeted Yorben's and it was 30x faster at 0.3 milliseconds per loop.

df['Upper'] = 0
df['Lower'] = 0
for j in range(3):
    df['Upper'] = np.where((df['name'] == df.shift(j)['name'])
                                & (df.shift(j)['number'] <= df['number']*1.003)
                                & (df.shift(j)['number'] >  df['number']),
                                df['Upper'] + 1, df['Upper'])
    df['Lower'] = np.where((df['name'] == df.shift(j)['name'])
                                & (df.shift(j)['number'] >= df['number']*0.997)
                                & (df.shift(j)['number'] <  df['number']),
                                df['Lower'] + 1, df['Lower'])

output:

  name  number  Upper   Lower
0   A   0.8466  0   0
1   A   0.8463  1   0
2   A   0.8482  0   2
3   A   0.8455  1   0
4   A   0.8423  0   0
5   A   0.8405  1   0
6   A   0.8420  1   1
7   A   0.8453  0   0
8   A   0.8419  1   0
9   A   0.8394  1   0
10  A   0.8376  1   0
11  A   0.8368  1   0
12  A   0.8388  0   2
13  A   0.8392  0   2
14  A   0.8409  0   2
15  A   0.8415  0   2
16  A   0.8424  0   2
17  A   0.8425  0   2
18  A   0.8433  0   2
19  A   0.8412  2   0
20  A   0.8397  1   0
user_63667700

You might be able to vectorise by applying a custom function to a rolling window of size 3

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.core.window.rolling.Rolling.apply.html

Even if that doesn't vectorise for your function, if you use the Numba engine (requiring a package install) you will probably see significant performance gains over raw python.

EDIT: if that apply is anything like normal pd or np apply chances are it is not vectorised. It might be possible instead to generate a bunch of intermediate columns using .shift() and build up the calculation that way - each intermediate calculation would then be vectorised.

user_27748230
Related