title: How to optimize grouping of a DataFrame and performing operations on the groups
body: <p>Here's an example of my dataframe:</p>
  <pre><code>d = {'group': ['a', 'a', 'a', 'b', 'b', 'b', 'b', 'b', 'c', 'd', 'd'], \
       'round': [3, 3, 2, 1, 3, 1, 3, 3, 3, 2, 1], \
       'score': [0.3, 0.1, 0.6, 0.8, 0.2, 0.5, 0.5, 0.6, 0.4, 0.9, 0.1]}
  df = pd.DataFrame(d)
  df
  </code></pre>
  <pre><code>    group   round   score
  0   a          3    0.3
  1   a          3    0.1
  2   a          2    0.6
  3   b          1    0.8
  4   b          3    0.2
  5   b          1    0.5
  6   b          3    0.5
  7   b          3    0.6
  8   c          3    0.4
  9   d          2    0.9
  10  d          1    0.1
  </code></pre>
  <p>My actual dataframe has 6 columns and &gt; 1,000,000 rows. I'm trying to figure out the fastest way to do the following:</p>
  <p>For each group find the average of scores and perform some calculation with it for each of 3 rounds. If there are no scores, write 'NA'.</p>
  <p>I'm not sure if it would be faster to make a list of lists and then convert it into a dataframe or make a new dataframe and populate that, so i went with the list first:</p>
  <pre><code>def test_df(data):
      value_counts = data['group'].value_counts().to_dict()
      avgs = []
  
      for key, val in value_counts.items():
          row = data[data['group'] == key]
          
          x = [key]
          
          if val &lt; 2:
              x.extend([10 * row['score'].values[0] + 1 if i == row['round'].values[0] else 'NA' for i in range (1,4)])
          
          else:
              x.extend([(10 * row[row['round'] == i]['score'].mean() + 1) if len(row[row['round'] == i]) &gt; 0 else 'NA' for i in range(1, 4)])
              
          avgs.append(x)     
              
      return avgs
  </code></pre>
  <p>Here I created a separate case because about 80% of groups in my data only have one row, so I figured it might speed things up maybe?</p>
  <p>this returns the correct results in format <code>[group, round 1, round 2, round 3]</code></p>
  <pre><code>[['b', 7.5, 'NA', 5.333333333333333],
   ['a', 'NA', 7.0, 3.0],
   ['d', 2.0, 10.0, 'NA'],
   ['c', 'NA', 'NA', 5.0]]
  </code></pre>
  <p>but it's looking like it's going to take a really really long time on the actual dataframe...
  Does anyone have any better ideas?</p>