title: Grouping data with pandas into separate batches
body: <p>I am trying to create a function which adds a column onto a <code>DataFrame</code> that creates a batch number for a set of time data. I am assuming that once the level goes below/above 1000 we have emptied (and then refilled) the batch and thus it's a new batch. This means I can then group the data by batch number to do further analysis.</p>
  
  <p>My data looks like</p>
  
  <pre><code>df_test = pd.DataFrame(
  { 'Time'  : [1300, 1400, 1500, 1600, 1700, 1800],
    'Level' : [ 200, 1300, 1500,  200, 1500, 1400],
    'Data'  : range(6) })
  
     Data  Level  Time
  0     0    200  1300
  1     1   1300  1400
  2     2   1500  1500
  3     3    200  1600
  4     4   1500  1700
  5     5   1400  1800
  </code></pre>
  
  <p>And what I want it to look like is:</p>
  
  <pre><code>   Batch  Data  Level  Time
  0    NaN     0    200  1300
  1      1     1   1300  1400
  2      1     2   1500  1500
  3    NaN     3    200  1600
  4      2     4   1500  1700
  5      2     5   1400  1800
  </code></pre>
  
  <p>I'm not bothered about what happens to the batch number between batches. NaN seems like a sensible choice but anything separate and distinct.</p>
  
  <p>So far I've tried doing:</p>
  
  <pre><code>df['inUse'] = df['Level'] &gt; 1000
  </code></pre>
  
  <p>And then trying to work out how to number a column and start a new number every true but can't work out how. Any help would be appreciated! Thanks.</p>
  
  <h2>EDIT</h2>
  
  <p>Tried:</p>
  
  <pre><code>df['Batch'] = (df['Level'] &lt; 1000).cumsum()
  
  Traceback (most recent call last):
    File "&lt;stdin&gt;", line 1, in &lt;module&gt;
    File "C:\Users\alarr\Python\WinPython-64bit-2.7.5.3\python-2.7.5.amd64\lib\site-packages\pandas\core\series.py", line 1662, in cumsum
      np.putmask(result, mask, pa.NA)
  ValueError: cannot convert float NaN to integer
  </code></pre>
  
  <p>Also using python 2.7.5 and pandas 0.12.0. Also worth mentioning I would like to apply this to datasets of up to 1 million rows. Forgot to mention this!</p>
  
  <h2>Solved:</h2>
  
  <p>I tried both answers on my main dataset and both worked! Thank you. Out of curiosity, I timed both and cumsum method ran in 0.016s whereas counter ran in 0.156s. Interesting to see the difference between both! </p>