Compute rolling z-score in pandas dataframe

rolling.apply with a custom function is significantly slower than using builtin rolling functions (such as mean and std). Therefore, compute the rolling z-score from the rolling mean and rolling std:

def zscore(x, window):
    r = x.rolling(window=window)
    m = r.mean().shift(1)
    s = r.std(ddof=0).shift(1)
    z = (x-m)/s
    return z

According to the definition given on this page the rolling z-score depends on the rolling mean and std just prior to the current point. The shift(1) is used above to achieve this effect.

Below, even for a small Series (of length 100), zscore is over 5x faster than using rolling.apply. Since rolling.apply(zscore_func) calls zscore_func once for each rolling window in essentially a Python loop, the advantage of using the Cythonized r.mean() and r.std() functions becomes even more apparent as the size of the loop increases. Thus, as the length of the Series increases, the speed advantage of zscore increases.

In [58]: %timeit zscore(x, N)
1000 loops, best of 3: 903 µs per loop

In [59]: %timeit zscore_using_apply(x, N)
100 loops, best of 3: 4.84 ms per loop

This is the setup used for the benchmark:

import numpy as np
import pandas as pd
np.random.seed(2017)

def zscore(x, window):
    r = x.rolling(window=window)
    m = r.mean().shift(1)
    s = r.std(ddof=0).shift(1)
    z = (x-m)/s
    return z


def zscore_using_apply(x, window):
    def zscore_func(x):
        return (x[-1] - x[:-1].mean())/x[:-1].std(ddof=0)
    return x.rolling(window=window+1).apply(zscore_func)

N = 5
x = pd.Series((np.random.random(100) - 0.5).cumsum())

result = zscore(x, N)
alt = zscore_using_apply(x, N)

assert not ((result - alt).abs() > 1e-8).any()

You should use native functions of pandas:

 # Compute rolling zscore for column ="COL" and window=window
 col_mean = df["COL"].rolling(window=window).mean()
 col_std = df["COL"].rolling(window=window).std()

 df["COL_ZSCORE"] = (df["COL"] - col_mean)/col_std

def zscore(arr, window):
    x = arr.rolling(window = 1).mean()
    u = arr.rolling(window = window).mean()
    o = arr.rolling(window = window).std()

    return (x-u)/o

df['zscore'] = zscore(df['value'],window)

Let us say you have a data frame called data, which looks like this:

enter image description here

then you run the following code,

data_zscore=data.apply(lambda x: (x-x.expanding().mean())/x.expanding().std())

enter image description here Please note that the first row will always have NaN values as it doesn't have a standard deviation.

This can be solved in a single line of code. Given that s is the input series and wlen is the window length:

zscore = s.sub(s.rolling(wlen).mean()).div(s.rolling(wlen).std())

If you need to shift the mean and std it can still be done:

zscore = s.sub(s.rolling(wlen).mean().shift()).div(s.rolling(wlen).std().shift())