CODEWITHSUNDEEP
pythonaveragerolling-computationpercentiledayofmonth
DJP
DJP

Reputation: 11

python dataframe rolling on month-end dates: rolling average of the lowest 1% values

For each month-end date and each "PERMNO" (company identifier), I'd like to compute the rolling average of the lowest 1% values in the past 252 days from a dataframe column named "RET"

To compute it, I did:

def get_es(x):
    return (x<=x.iloc[0:249].quantile(0.01)).mean()
df['es_1pct'] = df.groupby(['PERMNO'])['RET'].rolling(window = 250, min_periods=200).apply(get_es).reset_index()['RET']

However, the results are completely wrong because it returns the whole data sample's average of the observations that are below the 1st percentile, and it's not rolling at all.

(p.s., I learned the code from Calculate the average of the lowest n percentile, but they don't do rolling.)

Could someone please help out on how to roll just on the month-end dates to speed up the computation; and more importantly, how to properly compute the rolling average of the lowest 1st percentile?

Much appreciated!

Best, Darcy

Upvotes: 1

Views: 20

Answers (0)

Related Questions