computing an EWMA of a DataFrame by time

I have this dataframe:

    avg                date    high  low      qty
0 16.92 2013-05-27 00:00:00   19.00 1.22 71151.00
1 14.84 2013-05-30 00:00:00   19.00 1.22 42939.00
2  9.19 2013-06-02 00:00:00   17.20 1.23  5607.00
3 23.63 2013-06-05 00:00:00 5000.00 1.22  5850.00
4 13.82 2013-06-10 00:00:00   19.36 1.22  5644.00
5 17.76 2013-06-15 00:00:00   24.00 2.02 16969.00

Each row is an observation of avg, high, low, and qty that was created on the specified date.

I'm trying to compute an exponential moving weighted average with a span of 60 days:

df["emwa"] = pandas.ewma(df["avg"],span=60,freq="D")

But I get

TypeError: Only valid with DatetimeIndex or PeriodIndex

Okay, so maybe I need to add a DateTimeIndex to my DataFrame when it's constructed. Let me change my constructor call from

df = pandas.DataFrame(records) #records is just a list of dictionaries

to

rng = pandas.date_range(firstDate,lastDate, freq='D')
df = pandas.DataFrame(records,index=rng)

But now I get

ValueError: Shape of passed values is (5,), indices imply (5, 1641601)

Any suggestions for how to compute my EMWA?

Solution 1:

You need two things, ensure the date column is of dates (rather of strings) and to set the index to these dates.
You can do this in one go using to_datetime:

In [11]: df.index = pd.to_datetime(df.pop('date'))

In [12]: df
Out[12]:
              avg     high   low    qty
date
2013-05-27  16.92    19.00  1.22  71151
2013-05-30  14.84    19.00  1.22  42939
2013-06-02   9.19    17.20  1.23   5607
2013-06-05  23.63  5000.00  1.22   5850
2013-06-10  13.82    19.36  1.22   5644
2013-06-15  17.76    24.00  2.02  16969

Then you can call emwa as expected:

In [13]: pd.ewma(df["avg"], span=60, freq="D")
Out[13]:
date
2013-05-27    16.920000
2013-05-28    16.920000
2013-05-29    16.920000
2013-05-30    15.862667
2013-05-31    15.862667
2013-06-01    15.862667
2013-06-02    13.563899
2013-06-03    13.563899
2013-06-04    13.563899
2013-06-05    16.207625
2013-06-06    16.207625
2013-06-07    16.207625
2013-06-08    16.207625
2013-06-09    16.207625
2013-06-10    15.697743
2013-06-11    15.697743
2013-06-12    15.697743
2013-06-13    15.697743
2013-06-14    15.697743
2013-06-15    16.070721
Freq: D, dtype: float64

and if you set this as a column:

In [14]: df['ewma'] = pd.ewma(df["avg"], span=60, freq="D")

In [15]: df
Out[15]:
              avg     high   low    qty       ewma
date
2013-05-27  16.92    19.00  1.22  71151  16.920000
2013-05-30  14.84    19.00  1.22  42939  15.862667
2013-06-02   9.19    17.20  1.23   5607  13.563899
2013-06-05  23.63  5000.00  1.22   5850  16.207625
2013-06-10  13.82    19.36  1.22   5644  15.697743
2013-06-15  17.76    24.00  2.02  16969  16.070721

Solution 2:

• pandas.ewma has been deprecated since pandas 0.17.0.
• The same functionality can be obtained by combining pandas.DataFrame.ewm() and pandas.DataFrame.mean()
• The following example will calculate a few means with exponential components (com = center of mass) on the closing price of the Microsoft stock.
• Also see:
  • pandas: Exponentially weighted windows

import pandas as pd
import matplotlib.pyplot as plt

# plotting style parameters - optional
plt.style.use('seaborn')
plt.rcParams['figure.figsize'] = (16.0, 10.0)

# read `test.csv` data at the bottom of answer
df = pd.read_csv('test.csv', index_col='Date', parse_dates=['Date'])

# display(df.head())
                  High         Low        Open       Close      Volume   Adj Close
Date                                                                              
2020-01-02  160.729996  158.330002  158.779999  160.619995  22622100.0  159.737595
2020-01-03  159.949997  158.059998  158.320007  158.619995  21116200.0  157.748581
2020-01-06  159.100006  156.509995  157.080002  159.029999  20813700.0  158.156342
2020-01-07  159.669998  157.320007  159.320007  157.580002  21634100.0  156.714310
2020-01-08  160.800003  157.949997  158.929993  160.089996  27746500.0  159.210495

# New DataFrame to keep it clean
ewma = pd.DataFrame(index=df.index)

# calculate ewma
ewma['com10'] = df['Close'].ewm(com=10).mean()
ewma['com50'] = df['Close'].ewm(com=50).mean()
ewma['com100'] = df['Close'].ewm(com=100).mean()

# display(ewma.head())
                 com10       com50      com100
Date                                          
2020-01-02  160.619995  160.619995  160.619995
2020-01-03  159.572376  159.610094  159.615020
2020-01-06  159.374105  159.412887  159.418069
2020-01-07  158.859571  158.940967  158.951671
2020-01-08  159.154647  159.179963  159.183889

# plot
ewma.plot()

Jupyter Notebook can be found here: pandas_exponential_average.ipynb

test.csv

Date,High,Low,Open,Close,Volume,Adj Close
2020-01-02,160.72999572753906,158.3300018310547,158.77999877929688,160.6199951171875,22622100.0,159.7375946044922
2020-01-03,159.9499969482422,158.05999755859375,158.32000732421875,158.6199951171875,21116200.0,157.7485809326172
2020-01-06,159.10000610351562,156.50999450683594,157.0800018310547,159.02999877929688,20813700.0,158.15634155273438
2020-01-07,159.6699981689453,157.32000732421875,159.32000732421875,157.5800018310547,21634100.0,156.7143096923828
2020-01-08,160.8000030517578,157.9499969482422,158.92999267578125,160.08999633789062,27746500.0,159.2104949951172
2020-01-09,162.22000122070312,161.02999877929688,161.83999633789062,162.08999633789062,21385000.0,161.1995086669922
2020-01-10,163.22000122070312,161.17999267578125,162.82000732421875,161.33999633789062,20725900.0,160.45364379882812
2020-01-13,163.30999755859375,161.25999450683594,161.75999450683594,163.27999877929688,21626500.0,162.3829803466797
2020-01-14,163.60000610351562,161.72000122070312,163.38999938964844,162.1300048828125,23477400.0,161.2393035888672
2020-01-15,163.94000244140625,162.57000732421875,162.6199951171875,163.17999267578125,21417900.0,162.28353881835938
2020-01-16,166.24000549316406,164.02999877929688,164.35000610351562,166.1699981689453,23865400.0,165.25711059570312
2020-01-17,167.47000122070312,165.42999267578125,167.4199981689453,167.10000610351562,34371700.0,166.1820068359375
2020-01-21,168.19000244140625,166.42999267578125,166.67999267578125,166.5,29517200.0,165.58529663085938
2020-01-22,167.49000549316406,165.67999267578125,167.39999389648438,165.6999969482422,24138800.0,164.78968811035156
2020-01-23,166.8000030517578,165.27000427246094,166.19000244140625,166.72000122070312,19680800.0,165.80409240722656
2020-01-24,167.52999877929688,164.4499969482422,167.50999450683594,165.0399932861328,24918100.0,164.13331604003906
2020-01-27,163.3800048828125,160.1999969482422,161.14999389648438,162.27999877929688,32078100.0,161.3884735107422
2020-01-28,165.75999450683594,163.07000732421875,163.77999877929688,165.4600067138672,24899900.0,164.551025390625
2020-01-29,168.75,165.69000244140625,167.83999633789062,168.0399932861328,34754500.0,167.1168212890625
2020-01-30,174.0500030517578,170.7899932861328,174.0500030517578,172.77999877929688,51597500.0,171.83079528808594
2020-01-31,172.39999389648438,169.5800018310547,172.2100067138672,170.22999572753906,36142700.0,169.2947998046875
2020-02-03,174.5,170.39999389648438,170.42999267578125,174.3800048828125,30149100.0,173.42201232910156
2020-02-04,180.63999938964844,176.30999755859375,177.13999938964844,180.1199951171875,36433300.0,179.13047790527344
2020-02-05,184.1999969482422,178.41000366210938,184.02999877929688,179.89999389648438,39186300.0,178.91168212890625
2020-02-06,183.82000732421875,180.05999755859375,180.97000122070312,183.6300048828125,27751400.0,182.62120056152344
2020-02-07,185.6300048828125,182.47999572753906,182.85000610351562,183.88999938964844,33515600.0,182.8797607421875
2020-02-10,188.83999633789062,183.25,183.5800018310547,188.6999969482422,35844300.0,187.663330078125
2020-02-11,190.6999969482422,183.5,190.64999389648438,184.44000244140625,53159900.0,183.42674255371094
2020-02-12,185.85000610351562,181.85000610351562,185.5800018310547,184.7100067138672,47062900.0,183.69525146484375
2020-02-13,186.22999572753906,182.8699951171875,183.0800018310547,183.7100067138672,35295800.0,182.7007598876953
2020-02-14,185.41000366210938,182.64999389648438,183.25,185.35000610351562,23149500.0,184.3317413330078
2020-02-18,187.6999969482422,185.5,185.61000061035156,187.22999572753906,27792200.0,186.201416015625
2020-02-19,188.17999267578125,186.47000122070312,188.05999755859375,187.27999877929688,29997500.0,186.75985717773438
2020-02-20,187.25,181.10000610351562,186.9499969482422,184.4199981689453,36862400.0,183.90780639648438
2020-02-21,183.5,177.25,183.1699981689453,178.58999633789062,48572600.0,178.093994140625
2020-02-24,174.5500030517578,163.22999572753906,167.77000427246094,170.88999938964844,68311100.0,170.41537475585938
2020-02-25,174.83999633789062,167.64999389648438,174.1999969482422,168.07000732421875,68073300.0,167.6032257080078
2020-02-26,173.25999450683594,168.2100067138672,169.7100067138672,170.1699981689453,56206100.0,169.69737243652344
2020-02-27,167.02999877929688,157.97999572753906,163.32000732421875,158.17999267578125,93033600.0,157.7406768798828
2020-02-28,163.7100067138672,152.0,152.41000366210938,162.00999450683594,97073600.0,161.56004333496094
2020-03-02,172.9199981689453,162.30999755859375,165.30999755859375,172.7899932861328,71030800.0,172.3101043701172
2020-03-03,175.0,162.25999450683594,173.8000030517578,164.50999450683594,71677000.0,164.0531005859375
2020-03-04,170.6999969482422,165.6199951171875,168.49000549316406,170.5500030517578,49814400.0,170.07632446289062
2020-03-05,170.8699951171875,165.69000244140625,166.0500030517578,166.27000427246094,47817300.0,165.80821228027344
2020-03-06,163.11000061035156,156.0,162.61000061035156,161.57000732421875,72821100.0,161.12127685546875
2020-03-09,157.75,150.0,151.0,150.6199951171875,70419300.0,150.20167541503906
2020-03-10,161.02999877929688,152.5800018310547,158.16000366210938,160.9199981689453,65354400.0,160.4730682373047
2020-03-11,157.6999969482422,151.14999389648438,157.1300048828125,153.6300048828125,56371600.0,153.2033233642578
2020-03-12,153.47000122070312,138.5800018310547,145.3000030517578,139.05999755859375,93226400.0,138.6737823486328
2020-03-13,161.91000366210938,140.72999572753906,147.5,158.8300018310547,92727400.0,158.3888702392578
2020-03-16,149.35000610351562,135.0,140.0,135.4199981689453,87905900.0,135.04388427734375
2020-03-17,147.5,135.0,140.0,146.57000732421875,81059800.0,146.16293334960938
2020-03-18,146.0,135.02000427246094,138.0,140.39999389648438,81593200.0,140.0100555419922
2020-03-19,150.14999389648438,139.0,142.77000427246094,142.7100067138672,85922700.0,142.31365966796875
2020-03-20,147.10000610351562,135.86000061035156,146.0,137.35000610351562,84866200.0,136.96853637695312
2020-03-23,140.57000732421875,132.52000427246094,137.00999450683594,135.97999572753906,78975200.0,135.6023406982422
2020-03-24,149.60000610351562,141.27000427246094,143.75,148.33999633789062,82516700.0,147.92800903320312
2020-03-25,154.3300018310547,144.44000244140625,148.91000366210938,146.9199981689453,75638200.0,146.51194763183594
2020-03-26,156.66000366210938,148.3699951171875,148.39999389648438,156.11000061035156,64568100.0,155.6764373779297
2020-03-27,154.88999938964844,149.1999969482422,151.75,149.6999969482422,57042300.0,149.2842254638672
2020-03-30,160.60000610351562,150.00999450683594,152.44000244140625,160.22999572753906,63420300.0,159.7849884033203
2020-03-31,164.77999877929688,156.55999755859375,159.39999389648438,157.7100067138672,77927200.0,157.27198791503906
2020-04-01,157.75,150.82000732421875,153.0,152.11000061035156,57969900.0,151.6875457763672
2020-04-02,155.47999572753906,150.36000061035156,151.86000061035156,155.25999450683594,49630700.0,154.8287811279297
2020-04-03,157.3800048828125,152.19000244140625,155.10000610351562,153.8300018310547,41243300.0,153.40277099609375
2020-04-06,166.5,157.5800018310547,160.32000732421875,165.27000427246094,67111700.0,164.8109893798828
2020-04-07,170.0,163.25999450683594,169.58999633789062,163.49000549316406,62769000.0,163.0359344482422
2020-04-08,166.6699981689453,163.5,165.6699981689453,165.1300048828125,48318200.0,164.67138671875
2020-04-09,167.3699951171875,163.3300018310547,166.36000061035156,165.13999938964844,51431800.0,164.6813507080078
2020-04-13,165.57000732421875,162.3000030517578,164.35000610351562,165.50999450683594,41905300.0,165.05032348632812
2020-04-14,173.75,168.0,169.0,173.6999969482422,52874300.0,173.2175750732422
2020-04-15,173.57000732421875,169.24000549316406,171.1999969482422,171.8800048828125,40940800.0,171.4026336669922
2020-04-16,177.27999877929688,172.89999389648438,174.3000030517578,177.0399932861328,50479600.0,176.5482940673828
2020-04-17,180.0,175.8699951171875,179.5,178.60000610351562,52765600.0,178.10397338867188
2020-04-20,178.75,174.99000549316406,176.6300048828125,175.05999755859375,36669600.0,174.57379150390625
2020-04-21,173.6699981689453,166.11000061035156,173.5,167.82000732421875,56203700.0,167.35391235351562
2020-04-22,174.0,170.82000732421875,171.38999938964844,173.52000427246094,34651600.0,173.0380859375
2020-04-23,175.05999755859375,170.91000366210938,174.11000061035156,171.4199981689453,32790800.0,170.94390869140625
2020-04-24,174.55999755859375,170.7100067138672,172.05999755859375,174.5500030517578,34305300.0,174.06521606445312
2020-04-27,176.89999389648438,173.3000030517578,176.58999633789062,174.0500030517578,33194400.0,173.5666046142578
2020-04-28,175.6699981689453,169.38999938964844,175.58999633789062,169.80999755859375,34392700.0,169.33837890625
2020-04-29,177.67999267578125,171.8800048828125,173.22000122070312,177.42999267578125,51286600.0,176.9372100830078
2020-04-30,180.39999389648438,176.22999572753906,180.0,179.2100067138672,53661300.0,178.7122802734375
2020-05-01,178.63999938964844,174.00999450683594,175.8000030517578,174.57000732421875,39370500.0,174.08517456054688
2020-05-04,179.0,173.8000030517578,174.49000549316406,178.83999633789062,30372900.0,178.34329223632812
2020-05-05,183.64999389648438,179.89999389648438,180.6199951171875,180.75999450683594,36839200.0,180.25796508789062
2020-05-06,184.1999969482422,181.6300048828125,182.0800018310547,182.5399932861328,32139300.0,182.03302001953125
2020-05-07,184.5500030517578,182.5800018310547,184.1699981689453,183.60000610351562,28316000.0,183.090087890625
2020-05-08,185.0,183.36000061035156,184.97999572753906,184.67999267578125,30912600.0,184.1670684814453
2020-05-11,187.50999450683594,182.85000610351562,183.14999389648438,186.74000549316406,30892700.0,186.2213592529297
2020-05-12,187.0399932861328,182.3000030517578,186.8000030517578,182.50999450683594,32038200.0,182.0030975341797
2020-05-13,184.0500030517578,176.5399932861328,182.5500030517578,179.75,44711500.0,179.2507781982422
2020-05-14,180.69000244140625,175.67999267578125,177.5399932861328,180.52999877929688,41873900.0,180.0286102294922
2020-05-15,187.05999755859375,177.0,179.05999755859375,183.16000366210938,46610400.0,182.65130615234375
2020-05-18,186.1999969482422,183.9600067138672,185.75,184.91000366210938,35306600.0,184.3964385986328
2020-05-19,186.60000610351562,183.49000549316406,185.02999877929688,183.6300048828125,26799100.0,183.1199951171875
2020-05-20,185.85000610351562,183.94000244140625,184.80999755859375,185.66000366210938,31261300.0,185.66000366210938
2020-05-21,186.6699981689453,183.2899932861328,185.39999389648438,183.42999267578125,29119500.0,183.42999267578125
2020-05-22,184.4600067138672,182.5399932861328,183.19000244140625,183.50999450683594,20826900.0,183.50999450683594
2020-05-26,186.5,181.10000610351562,186.33999633789062,181.57000732421875,36073600.0,181.57000732421875
2020-05-27,181.99000549316406,176.60000610351562,180.1999969482422,181.80999755859375,39517100.0,181.80999755859375
2020-05-28,184.14999389648438,180.3800048828125,180.74000549316406,181.39999389648438,33810200.0,181.39999389648438
2020-05-29,184.27000427246094,180.41000366210938,182.72999572753906,183.25,42146700.0,183.25
2020-06-01,183.0,181.4600067138672,182.5399932861328,182.8300018310547,22622400.0,182.8300018310547
2020-06-02,185.0,181.35000610351562,184.25,184.91000366210938,30794600.0,184.91000366210938
2020-06-03,185.94000244140625,183.5800018310547,184.82000732421875,185.36000061035156,27311000.0,185.36000061035156
2020-06-04,185.83999633789062,182.3000030517578,184.3000030517578,182.9199981689453,28761800.0,182.9199981689453
2020-06-05,187.72999572753906,182.00999450683594,182.6199951171875,187.1999969482422,39893600.0,187.1999969482422
2020-06-08,188.5500030517578,184.44000244140625,185.94000244140625,188.36000061035156,33211600.0,188.36000061035156
2020-06-09,190.6999969482422,187.25999450683594,188.0,189.8000030517578,29783900.0,189.8000030517578
2020-06-10,198.52000427246094,191.00999450683594,191.1300048828125,196.83999633789062,43872300.0,196.83999633789062
2020-06-11,195.75999450683594,186.07000732421875,193.1300048828125,186.27000427246094,52854700.0,186.27000427246094
2020-06-12,191.72000122070312,185.17999267578125,190.5399932861328,187.74000549316406,43345700.0,187.74000549316406
2020-06-15,190.82000732421875,184.00999450683594,184.5800018310547,188.94000244140625,32770200.0,188.94000244140625
2020-06-16,195.5800018310547,191.4600067138672,192.88999938964844,193.57000732421875,42556700.0,193.57000732421875
2020-06-17,196.32000732421875,193.69000244140625,195.02999877929688,194.24000549316406,25655900.0,194.24000549316406
2020-06-18,196.49000549316406,194.0,194.0,196.32000732421875,23061600.0,196.32000732421875
2020-06-19,199.2899932861328,194.3699951171875,198.58999633789062,195.14999389648438,44441100.0,195.14999389648438
2020-06-22,200.75999450683594,195.22999572753906,195.7899932861328,200.57000732421875,32818900.0,200.57000732421875
2020-06-23,203.9499969482422,201.42999267578125,202.08999633789062,201.91000366210938,30917400.0,201.91000366210938
2020-06-24,203.25,196.55999755859375,201.60000610351562,197.83999633789062,36740600.0,197.83999633789062
2020-06-25,200.61000061035156,195.47000122070312,197.8000030517578,200.33999633789062,27803900.0,200.33999633789062
2020-06-26,199.88999938964844,194.8800048828125,199.72999572753906,196.3300018310547,54675800.0,196.3300018310547
2020-06-29,198.52999877929688,193.5500030517578,195.77999877929688,198.44000244140625,26701600.0,198.44000244140625
2020-06-30,204.39999389648438,197.74000549316406,197.8800048828125,203.50999450683594,34310300.0,203.50999450683594
2020-07-01,206.35000610351562,201.77000427246094,203.13999938964844,204.6999969482422,32061200.0,204.6999969482422
2020-07-02,208.02000427246094,205.0,205.67999267578125,206.25999450683594,29315800.0,206.25999450683594
2020-07-06,211.1300048828125,208.08999633789062,208.8300018310547,210.6999969482422,31897600.0,210.6999969482422
2020-07-07,214.6699981689453,207.99000549316406,210.4499969482422,208.25,33600700.0,208.25
2020-07-08,213.25999450683594,208.69000244140625,210.07000732421875,212.8300018310547,33600000.0,212.8300018310547
2020-07-09,216.3800048828125,211.47000122070312,216.3300018310547,214.32000732421875,33121700.0,214.32000732421875
2020-07-10,214.0800018310547,211.0800018310547,213.6199951171875,213.6699981689453,26177600.0,213.6699981689453
2020-07-13,215.8000030517578,206.5,214.47999572753906,207.07000732421875,38135600.0,207.07000732421875
2020-07-14,208.85000610351562,202.02999877929688,206.1300048828125,208.35000610351562,37591800.0,208.35000610351562
2020-07-15,211.3300018310547,205.02999877929688,209.55999755859375,208.0399932861328,32179400.0,208.0399932861328
2020-07-16,205.6999969482422,202.30999755859375,205.39999389648438,203.9199981689453,29940700.0,203.9199981689453
2020-07-17,205.0399932861328,201.38999938964844,204.47000122070312,202.8800048828125,31635300.0,202.8800048828125
2020-07-20,212.3000030517578,203.00999450683594,205.0,211.60000610351562,36884800.0,211.60000610351562
2020-07-21,213.94000244140625,208.02999877929688,213.66000366210938,208.75,38105800.0,208.75