CODEWITHSUNDEEP
pythonpandasdataframeconditional-statementsrolling-computation
CTXR
CTXR

Reputation: 199

Conditional rolling computation in pandas

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!

Upvotes: 1

Views: 903

Answers (1)

cs95
cs95

Reputation: 402263

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

Upvotes: 1

Related Questions