title: Advanced groupby column creation in pandas Dataframe
body: <p>I have a catalogue of groups of galaxies in a DataFrame, <code>'compact'</code>, which consists mainly in </p>
  
  <ul>
  <li>a group id (<code>'CG'</code>, int),</li>
  <li>a magnitude (<code>'R'</code>, negative float)</li>
  <li>and a morphology (<code>'Morph'</code>, string, for example 'S' or 'E').</li>
  </ul>
  
  <p>I'm trying to construct a second pandas DataFrame with the following properties of the groups:</p>
  
  <ul>
  <li><code>'Morph'</code> of the object having the lowest <code>'R'</code> in the group</li>
  <li>Difference between the second lowest and the lowest <code>'R'</code> in the group</li>
  <li>Difference between the lowest <code>'R'</code> in the group and R of the group, defined as <code>-2.5*log10(sum(10**(-0.4*R)))</code></li>
  <li>Proportions of objects having a given <code>'Morph'</code> (on column for 'S', one for other morphologies, for example) in the group, NOT COUNTING THE ONE HAVING THE LOWEST <code>'R'</code>.</li>
  </ul>
  
  <p>I'm having troubles for the last one, could you help me to write it? The other ones work, but, as a secondary question, I would like if I'm doing it clean or if there's better to do.</p>
  
  <p>Here is my code (with a line for my last column which works but doesn't give exactly what I want, and a try in comments which doesn't work):</p>
  
  <pre><code>GroupBy = compact.sort_values('R').groupby('CG', as_index=False)
  R2 = GroupBy.head(2).groupby('CG', as_index=False).last().R
  R1 = GroupBy.first().sort_values('CG').R
  DeltaR12 =  R2 - R1 
  MorphCen = GroupBy.first().sort_values('CG').Morph
  Group = GroupBy.first().sort_values('CG').CG
  RGroup = GroupBy.apply(lambda x: -2.5*np.log10((10**(-0.4*x.R)).sum()))
  DeltaR1gr = R1 - RGroup
  
  
  # Works, but counts the object with lowest R:
  PropS = GroupBy.apply(lambda x: 1.0*x.loc[x['Morph'] == 'S'].shape[0]/x.shape[0])   
  # Tries to let aside lowest R, but doesn't work:
  # PropS = GroupBy.apply(lambda x: 1.0*x.loc[x['Morph'] == 'S' &amp; 
  #                        x['R']&gt;x['R'].min()].shape[0]/x.shape[0])
  
  # PropRed = same than PropS, but for 'Morph' != 'S'
  
  CompactML = pd.DataFrame([Group,MorphCen,DeltaR12,DeltaR1gr]).transpose()
  CompactML.columns = ['CG', 'MorphCen', 'DeltaR12','DeltaR1gr']
  </code></pre>