Iterating over a Pandas grouped dataframe
<p>I am using <code>groupby</code> in <code>pandas</code> to create some <code>json</code> style data. I am having trouble iterating over the grouped <code>dataframe</code> as it doesn't recognize my keys</p>

<pre><code>import pandas as pd


df = pd.DataFrame(data=[['Group A', 10],
                       ['Group A', 12],
                       ['Group B', 22],
                       ['Group B', 25],
                       ['Group B', 26]],
                  columns = ['Group', 'Value'])

df = df.groupby('Group').agg(['mean', 'count']).reset_index()

json_data = [{'id': row['Group'],
              'name': row['Group'],
              'value': row['mean']} for index, row in df.iteritems()]
print json_data
</code></pre>

<p>Error: </p>

<pre><code>KeyError: 'Group'
</code></pre>

<p>Desired Output:</p>

<pre><code>[{
    'id': 'Group A',
    'name': 'Group A',
    'value': 11
}, {
    'id': 'Group B',
    'name': 'Group B',
    'value': 24.33333
    }]
</code></pre>

<p>Try this: Here's a link to <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_json.html" rel="nofollow">DataFrame.to_json()</a></p>

<pre><code>df = df.groupby('Group').mean().reset_index().rename(columns = {'Group':"id" })
df['name'] = df['id']
df.to_json(orient="records")

'[{"id":"Group A","Value":11.0,"name":"Group A"},{"id":"Group B","Value":24.3333333333,"name":"Group B"}]'
</code></pre>

<p>You can reorder the JSON output this way: </p>

<pre><code>df[['id','name', 'Value', ]].to_json(orient="records")

'[{"id":"Group A","name":"Group A","Value":11.0},{"id":"Group B","name":"Group B","Value":24.3333333333}]'
</code></pre>

python json python-2.7 pandas