Ignore preceding values for a given column when calculating rolling.mean using Pandas
<p>Here is a <em>very small</em> subset of time series data that I have:</p>

<pre><code>Date              Client        Value
01-Sep-2016T      ABC           160000
02-Sep-2016T      ABC           150000
03-Sep-2016T      ABC           190000
04-Sep-2016T      ABC           200000
05-Sep-2016T      ABC           140000
06-Sep-2016T      ABC           120000
07-Sep-2016T      ABC           185000
08-Sep-2016T      ABC           119000
01-Sep-2016T      DEF           200
02-Sep-2016T      DEF           100
03-Sep-2016T      DEF           150
04-Sep-2016T      DEF           10
05-Sep-2016T      DEF           5
06-Sep-2016T      DEF           160
07-Sep-2016T      DEF           150
08-Sep-2016T      DEF           3
</code></pre>

<p>I create a data frame, as follows:</p>

<pre><code>dataFrame = pd.read_csv('test_data_02.csv')
</code></pre>

<p>Then, I attempt to add a moving average of the <code>Value</code> column, as follows:</p>

<pre><code>dataFrame['Value_MovingAverage'] = dataFrame['Value'].rolling(window=3, min_periods=1, center=False).mean()
</code></pre>

<p>Then, when I call <code>dataFrame.head(20)</code> to see the resulting <code>ValueMovingAverage</code> column, I see:</p>

<pre><code>    Date      Client    Value     Value_MovingAverage
0   01-Sep    ABC       160000    160000.000000
1   02-Sep    ABC       150000    155000.000000
2   03-Sep    ABC       190000    166666.666667
3   04-Sep    ABC       200000    180000.000000
4   05-Sep    ABC       140000    176666.666667
5   06-Sep    ABC       120000    153333.333333
6   07-Sep    ABC       185000    148333.333333
7   08-Sep    ABC       119000    141333.333333
8   01-Sep    DEF       200       **101400.000000**
9   02-Sep    DEF       100       39766.666667
10  03-Sep    DEF       150       150.000000
11  04-Sep    DEF       10        86.666667
12  05-Sep    DEF       5         55.000000
13  06-Sep    DEF       160       58.333333
14  07-Sep    DEF       150       105.000000
15  08-Sep    DEF       3         104.333333
</code></pre>

<p>As we can see, the <code>Value_MovingAverage</code> for the 'DEF' clients is affected by the very high values for the preceding two 'ABC' clients.  For example, index # 8 is showing a 3-day moving average for 'DEF' of 101400.000000, because it's using the following values:</p>

<p>185,000
119,000
200</p>

<p>average --> 101400</p>

<p>I'm trying to get the Value_MovingAverage for index # 8 to show nothing (because there are no preceding values for client 'ABC') and index # 14 to show a Value_MovingAverage of 58.33333, because it's referencing the following:</p>

<p>160
10
5
average --> 58.33333</p>

<p>My questions are:</p>

<p>1) how do I tell Pandas to ignore the values for 'ABC' when computing the moving average for the 'DEF' clients (and so on for all other 'Client' values in the entire data frame)?  Note that I have hundreds of 'Client' values, so creating different frames (one for each 'Client') and then applying the rolling average is not really an option.</p>

<p>2) how do I offset the moving average by one row so that the average for a given number of rows doesn't take <strong>itself</strong> into account?</p>

<p>Thanks in advance!</p>

<p><strong>UPDATE:</strong></p>

<pre><code>In [41]: df['new'] = (df.groupby('Client', as_index=False)
   ....:                .rolling(3, min_periods=1, center=False)
   ....:                .Value.mean()
   ....:                .reset_index(drop=True))

In [42]: df
Out[42]:
            Date Client   Value            new
0   01-Sep-2016T    ABC  160000  160000.000000
1   02-Sep-2016T    ABC  150000  155000.000000
2   03-Sep-2016T    ABC  190000  166666.666667
3   04-Sep-2016T    ABC  200000  180000.000000
4   05-Sep-2016T    ABC  140000  176666.666667
5   06-Sep-2016T    ABC  120000  153333.333333
6   07-Sep-2016T    ABC  185000  148333.333333
7   08-Sep-2016T    ABC  119000  141333.333333
8   01-Sep-2016T    DEF     200     200.000000
9   02-Sep-2016T    DEF     100     150.000000
10  03-Sep-2016T    DEF     150     150.000000
11  04-Sep-2016T    DEF      10      86.666667
12  05-Sep-2016T    DEF       5      55.000000
13  06-Sep-2016T    DEF     160      58.333333
14  07-Sep-2016T    DEF     150     105.000000
15  08-Sep-2016T    DEF       3     104.333333
</code></pre>

<p><strong>Old answer:</strong></p>

<pre><code>In [28]: df.groupby('Client').rolling(3, min_periods=1, center=False).mean()
Out[28]:
                   Date Client          Value
Client
ABC    0   01-Sep-2016T    ABC  160000.000000
       1   02-Sep-2016T    ABC  155000.000000
       2   03-Sep-2016T    ABC  166666.666667
       3   04-Sep-2016T    ABC  180000.000000
       4   05-Sep-2016T    ABC  176666.666667
       5   06-Sep-2016T    ABC  153333.333333
       6   07-Sep-2016T    ABC  148333.333333
       7   08-Sep-2016T    ABC  141333.333333
DEF    8   01-Sep-2016T    DEF     200.000000
       9   02-Sep-2016T    DEF     150.000000
       10  03-Sep-2016T    DEF     150.000000
       11  04-Sep-2016T    DEF      86.666667
       12  05-Sep-2016T    DEF      55.000000
       13  06-Sep-2016T    DEF      58.333333
       14  07-Sep-2016T    DEF     105.000000
       15  08-Sep-2016T    DEF     104.333333
</code></pre>

<p>or:</p>

<pre><code>In [31]: df.groupby('Client', as_index=False).rolling(3, min_periods=1, center=False).mean().reset_index(drop=True)
Out[31]:
            Date Client          Value
0   01-Sep-2016T    ABC  160000.000000
1   02-Sep-2016T    ABC  155000.000000
2   03-Sep-2016T    ABC  166666.666667
3   04-Sep-2016T    ABC  180000.000000
4   05-Sep-2016T    ABC  176666.666667
5   06-Sep-2016T    ABC  153333.333333
6   07-Sep-2016T    ABC  148333.333333
7   08-Sep-2016T    ABC  141333.333333
8   01-Sep-2016T    DEF     200.000000
9   02-Sep-2016T    DEF     150.000000
10  03-Sep-2016T    DEF     150.000000
11  04-Sep-2016T    DEF      86.666667
12  05-Sep-2016T    DEF      55.000000
13  06-Sep-2016T    DEF      58.333333
14  07-Sep-2016T    DEF     105.000000
15  08-Sep-2016T    DEF     104.333333
</code></pre>

python pandas moving-average