title: Efficient way to use pandas group by on a million records
body: <p>I have a dataframe which can be generated using the code below</p>
  
  <pre><code>df2 = pd.DataFrame({'subject_ID':[1,1,1,1,1,1,2,2,2,2],'colum' : ['L1CreaDate','L1Crea','L2CreaDate','L2Crea','L3CreaDate','L3Crea','L1CreaDate','L1Crea','L2CreaDate','L2Crea'], 
                  'dates':['2016-10-30 00:00:00',2.3,'2016-10-30 00:00:00',2.5,np.nan,np.nan,'2016-10-30 00:00:00',12.3,'2016-10-30 00:00:00',12.3]})
  </code></pre>
  
  <p>I am trying to do the below operations on the above dataframe. Though the code works absolutely fine , the issue is when I use the <code>group by statement</code>. It's quick in sample dataframe but in real data with over 1 million records, it takes a while and just running for a long time</p>
  
  <pre><code>    df2['col2'] = df2['colum'].str.split("Date").str[0]
      df2['col3'] = df2['col2'].str.extract('(\d+)', expand=True).astype(int)
      df2 = df2.sort_values(by=['subject_ID','col3'])
      df2['count'] = df2.groupby(['subject_ID','col2'])['dates'].transform(pd.Series.count)
  </code></pre>
  
  <p>I do <code>groupby</code> to get the below output <code>count</code> column so that I can reject records with count as <code>0</code>. There is a logic involved behind dropping NA's. It's not just about dropping all NA's. If you would like to know about that then refer this post <a href="https://stackoverflow.com/questions/57285680/retain-few-nas-and-drop-rest-of-nas-during-stack-operation-in-python">retain few NA's and drop rest of the NA's logic</a></p>
  
  <p>In real data one person might have more than 10000 rows. So a single dataframe has more than 1 million rows. </p>
  
  <p>Is there any other better and efficient way to do a <code>groupby</code> or get the <code>count</code> column?</p>
  
  <p><a href="https://i.stack.imgur.com/ya8kH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ya8kH.png" alt="enter image description here"></a></p>