CODEWITHSUNDEEP
pythonpython-3.xpandas
Abhay
Abhay

Reputation: 845

Python pandas: calculate rolling mean based on multiple criteria

I have a dataframe that shows the closing value for a bunch of stocks for the last 10 days. It has a datetime index & stocks can be identified by their name/code. Can you help me figure out how to calculate the 4 day moving average for each separate stock in the dataframe?

I've tried to use the pd.DataFrame.rolling().mean() method but it just gives the rolling mean for the whole dataset. Not sure where to go next...

sampleData = hundredDayData['2019-03-11':'2019-03-20'] sampleData['Close: 4 day mean'] = sampleData['Close'].rolling(window=4).mean() sampleData.head(24) enter image description here

Upvotes: 0

Views: 1587

Answers (1)

mujjiga
mujjiga

Reputation: 16856

df = pd.DataFrame({'code': ['a']*10+['b']*10, 'Close': [1]*20})
df.groupby('code')['Close'].rolling(window=4).mean().reset_index()
  • Group by code and calculate rolling mean with in the group

Note: If your code's are all jumbled (as shown below), then you should be using

df = pd.DataFrame({'code': ['a']*10+['b']*10+['a']*10, 'Close': [1]*30})
rolling = df.groupby('code')['Close'].rolling(window=4).mean().reset_index().set_index('level_1').rename(
    columns={'Close':'rolling'})[['rolling']]
df.merge(rolling, left_index=True, right_index=True)

Calculate rolling mean at group level but use the index to merge it back into the main dataframe as a new column.

Upvotes: 1

Related Questions