How to adjust Moving Average for weekly analysis?

Question

I need to plot moving average on the basis of weekly intervals like 3 week interval or 21 days, but while adjusting for the missed dates it now counts 0 and thus it gives incorrect result.

from nsepy import get_history as gh
from datetime import date
import pandas as pd

nifty = gh(symbol="NIFTY IT", 
                    start=date(2015,1,1), 
                    end=date(2016,1,3),
                    index=True)
idx = pd.date_range('01-01-2015', '01-01-2016')
nifty.index = pd.DatetimeIndex(nifty.index)
nifty = nifty.reindex(idx, fill_value=0)
nifty["3weekMA"]=nifty["Close"].rolling(21).mean()
nifty[nifty.Open != 0]

What can be done to tackle that.

This is the actual result :

And the desired result must be something like :

This is because the moving average for close must be in the range of 11000 and not 8000.

it is good practice to include your results and the desired results so people can help you faster and better — Yuca, Dec 04 '18 at 18:41

Amit Amola · Accepted Answer · 2018-12-04T19:09:13.853

The simplest thing that comes to mind is that just remove the weekend values from your data:

nifty=nifty[nifty['Close']!=0]

And then perform the moving average:

nifty["3weekMA"]=nifty["Close"].rolling(15).mean()

Just instead of 21, use 15 and well, it will work as well as it should. There are few pointers to this though. Rolling mean will give mean of last 15 values but the issue is that it results this as the 15th value or 21st in your case, so the resultant plot would look something like this:

So to tackle this, all we need to do is maybe shift the new found moving average up or maybe just plot the Close values after first 7 and before last 7 alongwith moving average values and that would look something like:

plt.figure(figsize=(10,8))
plt.plot(nifty['Close'].values.tolist()[7:-7])
plt.plot(nifty['3weekMA'].values.tolist()[14:])

Well but visualization is just for representation purpose; I hope you get the gist about what to do with such data. I hope this solves your problem and yes the Moving Average value is indeed coming in 11Ks and not in 8Ks.

Sample Output:

        Date         Open       High        Low         Close       Volume      Turnover        3weekMA
        -------------------------------------------------------------------------------------------------
        2015-01-15  11672.30    11774.50    11575.10    11669.85    13882213    1.764560e+10    NaN
        2015-01-16  11708.85    11708.85    11582.85    11659.60    12368107    1.714690e+10    NaN
        2015-01-19  11732.50    11797.60    11629.05    11642.75    13696381    1.183750e+10    NaN
        2015-01-20  11681.80    11721.90    11635.70    11695.00    11021415    1.234730e+10    NaN
        2015-01-21  11732.45    11838.30    11659.70    11813.70    18679282    1.973070e+10    11418.113333
        2015-01-22  11832.55    11884.50    11782.95    11850.85    15715515    1.655670e+10    11460.456667
        2015-01-23  11877.90    11921.00    11767.40    11885.15    30034833    2.001210e+10    11494.660000
        2015-01-27  11915.60    11917.25    11679.55    11693.45    17005337    1.866840e+10    11524.320000
        2015-01-28  11712.55    11821.80    11693.80    11809.55    16876897    1.937590e+10    11580.963333
        2015-01-29  11812.35    11861.50    11728.75    11824.15    15520902    2.160790e+10    11641.506667
        2015-01-30  11998.35    12003.35    11799.35    11824.75    18559078    2.905950e+10    11695.280000
        2015-02-02  11871.35    11972.60    11847.80    11943.95    17272113    2.304050e+10    11731.566667
        2015-02-03  11963.75    12000.65    11849.00    11963.90    21053605    1.770590e+10    11759.583333

Absolutely understandable and valid point. I didn't think of that. So you mean if between 3 weeks there's a national holiday, then taking a rolling mean of 14 would be making more sense, rather than 15. Right? — Amit Amola, Dec 04 '18 at 19:15
Right, but for a one year data how am I going to know of all the holidays which came in between of these 3 week sets. Is there a way that we can calculate on the basis of weeks and not on days as that would give the perfect result. I'm very new to such analysis. — VishalQuery, Dec 04 '18 at 19:19
Okay so I did found something. Inside rolling, you can provide time related windows like= '3d' will be 3 seconds, so it will calculate rolling mean of 3 days, given your index is in Timestamp and so on. There is '3w' as well which means 3 weeks. It is currently giving error, but while figuring out the issue, I realized, it we are calculating Moving Averages, then what's the big issue in considering the holiday as similar to what we are doing with weekends(just let it be removed from data). Afterall we need MA of the days in which Nifty was open right? So what's the issue in that? — Amit Amola, Dec 04 '18 at 19:38
That's right... if that doesn't work i will deliver the day analogy. Meanwhile its just a task where segregation is made on the basis of weeks. — VishalQuery, Dec 04 '18 at 19:51
Well in that case, 1st Jan 2015 starts from Thursday and I guess that can't be starting of a week. We could however add 21 to the date each time of each line, get the index range and calculate mean of the Close column values for this range and mark the 11th index of these 21 dates as the resultant MA. I mean it's easy to do so as adding an integer to a datetime stamp works perfectly and will give you the 21st date from a particular date. Get it? And I believe you can do that by your own too. Still in case, you didn't understand what I am referring to, let me know. — Amit Amola, Dec 04 '18 at 19:56
Also, any idea of what will we do if we need to find 52 week moving average? — VishalQuery, Dec 04 '18 at 20:01
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/184736/discussion-between-amit-amola-and-vishalquery). — Amit Amola, Dec 05 '18 at 05:53

How to adjust Moving Average for weekly analysis?

1 Answers1