pandas dataframe, add one column as moving average of another column for each group
<p>I have a dataframe <code>df</code> like below. </p>

<pre><code>dates = pd.date_range('2000-01-01', '2001-01-01')
df1 = pd.DataFrame({'date':dates, 'value':np.random.normal(size = len(dates)), 'market':'GOLD'})
df2 = pd.DataFrame({'date':dates, 'value':np.random.normal(size = len(dates)), 'market':'SILVER'})
df = pd.concat([df1, df2])
df = df.sort('date')

          date  market     value
0   2000-01-01    GOLD -1.361360
0   2000-01-01  SILVER  0.255830
1   2000-01-02  SILVER  0.196953
1   2000-01-02    GOLD  1.422454
2   2000-01-03    GOLD -0.827672
...
</code></pre>

<p>I want to add another column as the 10d moving average of value, for each market. </p>

<p>Is there a simple <code>df.groupby('market').???</code> that can achieve this? Or do I have to pivot the table to wide form, smooth each column, then melt back?</p>

<p>This builds on @unutbu's answer, and adds the results back to the original dataframe as a new column.</p>

<pre><code>result = df.set_index('date').groupby('market')['value'].rolling(10).mean()
</code></pre>

<p>Now if <code>df</code> is sorted by <code>market</code> <strong>first</strong> and then <code>date</code>, the results should be in sync and we can just assign back the values</p>

<pre><code>df.sort_values(['market','date'], inplace = True)
df['value10d_1'] = result.values
</code></pre>

<p>However, if you are paranoid as I am, <code>merge</code> should give peace of mind,</p>

<pre><code>df = pd.merge(df, result.reset_index().rename(columns = {'value':'value10d_2'}), on = ['market','date'])

df['value10d_1'] - df['value10d_2'] # all 0
</code></pre>

python pandas