Conditional rolling computation in pandas

Question

I would like to compute a quantity called "downside beta". Let's suppose I have a dataframe df:

df = pd.DataFrame({'A': [-0.1,0.3,-0.4, 0.8,-0.5],'B': [-0.2,0.5,0.3,-0.5,0.1]},index=[0, 1, 2, 3,4])

I would like to add a column, 'C' that computes this downside beta defined as the covariance between the columns A and B considering only the negative values of column A with the corresponding values of B. This covariance should be then divided by the variance of column A considering only the negative values.

In the above example, it should be equivalent of computing the covariance between the two series: [-0.1,-0.4,-0.5] and [-0.2,0.3,0.1]. Divided by the variance of the series [-0.1,-0.4,-0.5].

Next step would be to roll this metric over the index of an initial large dataframe df.

Is there an efficient way to do that? In a vectorized manner. I guess combining pd.rolling_cov and np.where?

Thank you!

cs95 · Accepted Answer · 2018-03-06 18:28:50Z

1

Is this what you're looking for? You can filter out positive values and then call pandas cov and var functions accordingly:

v = df[df.A.lt(0)]
v.cov() / v.A.var()

          A         B
A  1.000000 -0.961538
B -0.961538  1.461538

If you just want the value at the diagonal,

np.diag(v.cov() / v.A.var(), k=-1)
array([-0.96153846])

For a rolling window, you may need to jump through a few hoops, but this should be doable;

v = df[df.A.lt(0)]  
i = v.rolling(3).cov().A.groupby(level=0).last()
j = v.rolling(3).A.var()

i / j

0         NaN
2         NaN
4   -0.961538
Name: A, dtype: float64

edited Mar 6, 2018 at 18:28

answered Mar 6, 2018 at 17:48

cs95

406k106 gold badges744 silver badges797 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CTXR Over a year ago

Thanks for your answer. It is helpful. In my case it would be v.cov()/v.A.var() and I am only interested in the number in the diagonal (-0.961538)

CTXR Over a year ago

How about the same computation but on a rolling basis (with a window of 3 let's say? If we assume that the dataframe is much bigger. I tried result=pd.rolling_cov(v,3) but it concatenates the dataframes and I just need the diagonal as you just did. In addition, when I do result=pd.rolling_cov(v,3) / pd.rolling_var(v.A,3) it is not working. I think the indexes do not match. Thanks

cs95 Over a year ago

@CTXR Found something that should hopefully work for you. Let me know how it goes.

Collectives™ on Stack Overflow

Conditional rolling computation in pandas

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related