title: Frequency tables in pandas (like plyr in R)
body: <p>My problem is how to calculate frequencies on multiple variables in pandas . 
  I have from this dataframe : </p>
  
  <pre><code>d1 = pd.DataFrame( {'StudentID': ["x1", "x10", "x2","x3", "x4", "x5", "x6",   "x7",     "x8", "x9"],
                         'StudentGender' : ['F', 'M', 'F', 'M', 'F', 'M', 'F', 'M', 'M', 'M'],
                   'ExamenYear': ['2007','2007','2007','2008','2008','2008','2008','2009','2009','2009'],
                   'Exam': ['algebra', 'stats', 'bio', 'algebra', 'algebra', 'stats', 'stats', 'algebra', 'bio', 'bio'],
                   'Participated': ['no','yes','yes','yes','no','yes','yes','yes','yes','yes'],
                    'Passed': ['no','yes','yes','yes','no','yes','yes','yes','no','yes']},
                    columns = ['StudentID', 'StudentGender', 'ExamenYear', 'Exam', 'Participated', 'Passed'])
  </code></pre>
  
  <p>To the following result</p>
  
  <pre><code>             Participated  OfWhichpassed
   ExamenYear                             
  2007                   3              2
  2008                   4              3
  2009                   3              2
  </code></pre>
  
  <p>(1) One possibility I tried is to compute two dataframe and bind them </p>
  
  <pre><code>t1 = d1.pivot_table(values = 'StudentID', rows=['ExamenYear'], cols = ['Participated'], aggfunc = len)
  t2 = d1.pivot_table(values = 'StudentID', rows=['ExamenYear'], cols = ['Passed'], aggfunc = len)
  tx = pd.concat([t1, t2] , axis = 1)
  
  Res1 = tx['yes']
  </code></pre>
  
  <p>(2) The second possibility is to use an aggregation function . </p>
  
  <pre><code>import collections
  dg = d1.groupby('ExamenYear')
  Res2 = dg.agg({'Participated': len,'Passed': lambda x : collections.Counter(x == 'yes')[True]})
  
   Res2.columns = ['Participated', 'OfWhichpassed']
  </code></pre>
  
  <p>Both ways are awckward to say the least. 
  <strong>How is this done properly in pandas ?</strong> </p>
  
  <p>P.S: I also tried <em>value_counts</em> instead of <em>collections.Counter</em> but could not get it to work </p>
  
  <p>For reference: Few months ago, I asked similar question for R  <a href="https://stackoverflow.com/questions/11852408/">here</a> and <strong>plyr</strong> could help </p>
  
  <p><strong>---- UPDATE ------</strong></p>
  
  <p>user <strong>DSM</strong> is right. there was a mistake in the desired table result. </p>
  
  <p>(1) The code for option one is </p>
  
  <pre><code> t1 = d1.pivot_table(values = 'StudentID', rows=['ExamenYear'], aggfunc = len)
   t2 = d1.pivot_table(values = 'StudentID', rows=['ExamenYear'], cols = ['Participated'], aggfunc = len)
   t3 = d1.pivot_table(values = 'StudentID', rows=['ExamenYear'], cols = ['Passed'], aggfunc = len)
  
   Res1 = pd.DataFrame( {'All': t1,
                         'OfWhichParticipated': t2['yes'],
                       'OfWhichPassed': t3['yes']})
  </code></pre>
  
  <p>It will produce the result </p>
  
  <pre><code>             All  OfWhichParticipated  OfWhichPassed
  ExamenYear                                         
  2007          3                    2              2
  2008          4                    3              3
  2009          3                    3              2
  </code></pre>
  
  <p>(2) For Option 2, thanks to user <strong>herrfz</strong>, I figured out how to use value_count and the code will be </p>
  
  <pre><code>Res2 = d1.groupby('ExamenYear').agg({'StudentID': len,
                                   'Participated': lambda x: x.value_counts()['yes'],
                                   'Passed': lambda x: x.value_counts()['yes']})
  
  Res2.columns = ['All', 'OfWgichParticipated', 'OfWhichPassed']
  </code></pre>
  
  <p>which will produce the same result as Res1</p>
  
  <p>My question remains though: </p>
  
  <p>Using Option 2, will it be possible to use the same Variable twice (for another operation ?) can one pass a custom name for the resulting variable ? </p>
  
  <p><strong>---- A NEW UPDATE ----</strong> </p>
  
  <p>I have finally decided to use <strong>apply</strong> which I understand is more flexible. </p>