Creating new pandas columns containing count of distinct entries
<p>I am learning pandas, and have downloaded a dataset of all the Olympic medal results to 2008. It is of the form:</p>

<pre><code>In[138]: medals.head()
Out[138]: 
      City  Edition     Sport Discipline                      Athlete  NOC  \
9792  Rome     1960  Aquatics     Diving           PHELPS, Brian Eric  GBR   
9793  Rome     1960  Aquatics     Diving        WEBSTER, Robert David  USA   
9794  Rome     1960  Aquatics     Diving         TOBIAN, Gary Milburn  USA   
9795  Rome     1960  Aquatics     Diving               KRUTOVA, Ninel  URS   
9796  Rome     1960  Aquatics     Diving  KRÄMER-ENGEL-GULBIN, Ingrid  EUA   

     Gender         Event Event_gender   Medal  
9792    Men  10m platform            M  Bronze  
9793    Men  10m platform            M    Gold  
9794    Men  10m platform            M  Silver  
9795  Women  10m platform            W  Bronze  
9796  Women  10m platform            W    Gold  
</code></pre>

<p>What I would initially like to do is to convert this to a dataframe which has columns <code>Edition, NOC, Bronze, Silver, Gold</code> where Bronze, Silver and Gold are the total numbers of each grade of medal won by the NOC at that Olympics.</p>

<p>So far I've got</p>

<pre><code>"""
Analyze historical Olympic performance
"""

import matplotlib.pyplot as plt
import pandas as pd
import matplotlib
matplotlib.style.use('ggplot')

isocodes = pd.read_csv('countrycodes.csv')
for k in ['official_name_en', 'official_name_fr', 'name',
          'ITU', 'MARC', 'WMO', 'DS', 'Dial', 'FIFA', 
          'FIPS', 'GAUL', 'IOC', 'ISO4217-currency_alphabetic_code',
          'ISO4217-currency_country_name', 'ISO4217-currency_minor_unit',
          'ISO4217-currency_name', 'ISO4217-currency_numeric_code',
          'is_independent', 'Capital', 'TLD', 'Languages',
          'geonameid', 'EDGAR' ]:         
          del isocodes[k]     

allmedals = pd.read_excel('medals.xlsx', sheetname='Medals')
ioccodes = pd.read_excel('medals.xlsx', sheetname='Codes')
del ioccodes['Country.1']
codes=pd.merge(ioccodes, isocodes, left_on='ISO code', 
               right_on='ISO3166-1-Alpha-2')

# Convert the year of the games to int from str and
# then filter out all records before 1960

pd.to_numeric(allmedals['Edition'])
medals = allmedals[(allmedals['Edition'] &gt;= 1960)]

# Filter out any duplicates - i.e. for events like the relay
# where each team member is awarded a medal

medals = medals.drop_duplicates(['City', 'Edition', 'Sport', 
                        'Discipline', 'NOC', 'Gender',
                        'Event', 'Event_gender', 'Medal'])

# Now get the medal counts for each Olympics

grouped = medals.groupby(["Edition", "NOC", "Medal"])["Medal"].\
                        count().reset_index(name="count")
</code></pre>

<p>I <em>know</em> that this must be a fairly standard pandas operation, and I'm almost there:</p>

<pre><code>In[139]: grouped.head()
Out[139]: 
   Edition  NOC   Medal  count
0     1960  ARG  Bronze      1
1     1960  ARG  Silver      1
2     1960  AUS  Bronze      6
3     1960  AUS    Gold      8
4     1960  AUS  Silver      8
</code></pre>

<p>but I can't work out how to group/aggregate the <code>grouped</code> dataframe. I'd be grateful for any tips (and also any other advice - e.g. is the use of <code>del</code>, <code>drop_duplicates()</code> etc. considered good practice?)</p>