title: Creating new pandas columns containing count of distinct entries
body: <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>