How do I use pandas' rolling_std to consider two columns in its observation?

Question

Data:

{'Open': {0: 159.18000000000001, 1: 157.99000000000001, 2: 157.66, 3: 157.53999999999999, 4: 155.03999999999999, 5: 155.47999999999999, 6: 155.44999999999999, 7: 155.93000000000001, 8: 155.0, 9: 157.72999999999999},  
 'Close': {0: 157.97999999999999, 1: 157.66, 2: 157.53999999999999, 3: 155.03999999999999, 4: 155.47999999999999, 5: 155.44999999999999, 6: 155.87, 7: 155.0, 8: 157.72999999999999, 9: 157.31}}

Code:

import pandas as pd

d = #... data above.
df = pd.DataFrame.from_dict(d)
df['Close_Stdev'] = pd.rolling_std(df[['Close']],window=5)

print df

#     Close    Open  Close_Stdev
# 0  157.98  159.18          NaN
# 1  157.66  157.99          NaN
# 2  157.54  157.66          NaN
# 3  155.04  157.54          NaN
# 4  155.48  155.04     1.369452
# 5  155.45  155.48     1.259754
# 6  155.87  155.45     0.975464
# 7  155.00  155.93     0.358567
# 8  157.73  155.00     1.065190
# 9  157.31  157.73     1.189378

Problem:

The above code has no problems. However, is it possible for rolling_std to be able to factor in its window of observation the first four values in Close and the fifth value in Open? Basically, I want rolling_std to calculate the following for its first Stdev:

157.98 # From Close
157.66 # From Close
157.54 # From Close
155.04 # From Close
155.04 # Bzzt, from Open.

Technically, this means that the last value of the observed list is always the last Close value.

Logic/Reason:

Obviously, this is stock data. I'm trying to check if it's better to factor in the Open price of a stock for the current trading day in the calculation of standard deviation rather than stop at just checking the previous Closes.

Desired Result:

#     Close    Open  Close_Stdev  Desired_Stdev
# 0  157.98  159.18          NaN            NaN
# 1  157.66  157.99          NaN            NaN
# 2  157.54  157.66          NaN            NaN
# 3  155.04  157.54          NaN            NaN
# 4  155.48  155.04     1.369452       1.480311
# 5  155.45  155.48     1.259754       1.255149
# 6  155.87  155.45     0.975464       0.994017
# 7  155.00  155.93     0.358567       0.361151
# 8  157.73  155.00     1.065190       0.368035
# 9  157.31  157.73     1.189378       1.291464

Extra Details:

This can easily be done in Excel by using the formula STDEV.S and selecting the numbers as seen in the screenshot below. However, I want this done in Python and pandas for personal reasons (I'm highlighting F6, it's not just visible due to Snagit's effect).

enter image description here

score 5 · Accepted Answer · edited May 23 '17 at 12:16

You could use Welford's method to compute the standard deviation. The advantage of doing it this way is that it can be expressed as vectorized arithmetic on a whole column with only 5 iterations. This should be faster than doing the calculation row-by-row and having to compose the window for each row.

First, here is a sanity check which shows Welford's method can reproduce the same result as

df['Close_Stdev'] = pd.rolling_std(df[['Close']],window=5)

import numpy as np
import pandas as pd

class OnlineVariance(object):
    """
    Welford's algorithm computes the sample variance incrementally.
    """
    def __init__(self, iterable=None, ddof=1):
        self.ddof, self.n, self.mean, self.M2 = ddof, 0, 0.0, 0.0
        if iterable is not None:
            for datum in iterable:
                self.include(datum)

    def include(self, datum):
        self.n += 1
        self.delta = datum - self.mean
        self.mean += self.delta / self.n
        self.M2 += self.delta * (datum - self.mean)
        self.variance = self.M2 / (self.n-self.ddof)

    @property
    def std(self):
        return np.sqrt(self.variance)


d = {'Open': {0: 159.18000000000001, 1: 157.99000000000001, 2: 157.66, 3:
 157.53999999999999, 4: 155.03999999999999, 5: 155.47999999999999, 6:
 155.44999999999999, 7: 155.93000000000001, 8: 155.0, 9: 157.72999999999999},
 'Close': {0: 157.97999999999999, 1: 157.66, 2: 157.53999999999999, 3:
 155.03999999999999, 4: 155.47999999999999, 5: 155.44999999999999, 6: 155.87, 7:
 155.0, 8: 157.72999999999999, 9: 157.31}}

df = pd.DataFrame.from_dict(d)

df['Close_Stdev'] = pd.rolling_std(df[['Close']],window=5)

ov = OnlineVariance()
for n in range(5):
    ov.include(df['Close'].shift(n))

df['std'] = ov.std
print(df)
assert np.isclose(df['Close_Stdev'], df['std'], equal_nan=True).all()

yields

    Close    Open  Close_Stdev       std
0  157.98  159.18          NaN       NaN
1  157.66  157.99          NaN       NaN
2  157.54  157.66          NaN       NaN
3  155.04  157.54          NaN       NaN
4  155.48  155.04     1.369452  1.369452
5  155.45  155.48     1.259754  1.259754
6  155.87  155.45     0.975464  0.975464
7  155.00  155.93     0.358567  0.358567
8  157.73  155.00     1.065190  1.065190
9  157.31  157.73     1.189378  1.189378

So, to incorporate the opening value in the calculation,

ov = OnlineVariance()
ov.include(df['Open'])
for n in range(1, 5):
    ov.include(df['Close'].shift(n))
df['std'] = ov.std
print(df)

yields

    Close    Open       std
0  157.98  159.18       NaN
1  157.66  157.99       NaN
2  157.54  157.66       NaN
3  155.04  157.54       NaN
4  155.48  155.04  1.480311
5  155.45  155.48  1.255149
6  155.87  155.45  0.994017
7  155.00  155.93  0.361151
8  157.73  155.00  0.368035
9  157.31  157.73  1.291464

Wow. Give me a half hour to test this one out. *Definitely* more elegant. — WGS, Dec 05 '14 at 04:52
Awesome. It works very well. And I learned something today. A well-deserved +1 and accept! — WGS, Dec 05 '14 at 05:52

score 0 · Answer 2 · answered Dec 05 '14 at 03:27

I played around with numpy until I got what I want. It's pretty fast, but it's not pandaic and potentially unsafe on so many levels. I am open to a prettier answer than this one. For the meantime, this works well enough for my cause.

import numpy
...

new_std = []
for i in range(df2.shape[0]+1):
    print df2['Close'].iloc[i-5:i]
    try:
        close_ = np.array(df2['Close'].iloc[i-5:i])
        open_ = np.array(df2['Open'].iloc[i-5:i])
        # Change the close from last date in list to the open
        # of that same date to simulate before-end-of-day trading.
        close_[-1] = open_[-1]
        new_std.append(np.std(close_, ddof=1))
    except:
        new_std.append(np.NAN)

df2['Desired_Stdev'] = new_std[1:] # Truncate to fit index.
print df2

#     Close    Open  Close_Stdev  Desired_Stdev
# 0  157.98  159.18          NaN            NaN
# 1  157.66  157.99          NaN            NaN
# 2  157.54  157.66          NaN            NaN
# 3  155.04  157.54          NaN            NaN
# 4  155.48  155.04     1.369452       1.480311
# 5  155.45  155.48     1.259754       1.255149
# 6  155.87  155.45     0.975464       0.994017
# 7  155.00  155.93     0.358567       0.361151
# 8  157.73  155.00     1.065190       0.368035
# 9  157.31  157.73     1.189378       1.291464

How do I use pandas' rolling_std to consider two columns in its observation?

2 Answers2