title: Python: Function for grouping and keying instances where criteria met
body: <p>I have a dataset which is mostly time + timedelta values relating to the shift length worked. If the start of the next shift is within one hour of the previous shift end time, this is counted as a 'continuing shift' and the combined shift length should be used (see the column ['SUMCOND']). This can be applied to multiple lines to chain 'continuing' shifts together.</p>
  <p><strong>The issue I have is when doing the grouping where multiple rows meet the criteria, there is no differentiation of what should be separate groups.</strong> For example in the table below, rows 3 + 4 should be grouped and rows 5, 6 and 7 should be grouped for two separate keys. I have added the ['Desired_Key'] column in as what the correct output should look like.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th></th>
  <th>staff_id</th>
  <th>Start Time</th>
  <th>End Time</th>
  <th>shift length (m)</th>
  <th>Time betw prevshift (s)</th>
  <th>Time betw nextshift (s)</th>
  <th>continuing</th>
  <th>SUMCOND</th>
  <th>COUNTCOND</th>
  <th>Key</th>
  <th>Desired_Key</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>A</td>
  <td>22/11/2021 20:30</td>
  <td>23/11/2021 2:00</td>
  <td>330</td>
  <td>2980800</td>
  <td>8580</td>
  <td></td>
  <td>330</td>
  <td>1</td>
  <td>4705</td>
  <td>4705</td>
  </tr>
  <tr>
  <td>2</td>
  <td>A</td>
  <td>23/11/2021 4:23</td>
  <td>23/11/2021 9:00</td>
  <td>277.2</td>
  <td>8580</td>
  <td>6048168</td>
  <td></td>
  <td>277.2</td>
  <td>1</td>
  <td>4706</td>
  <td>4706</td>
  </tr>
  <tr>
  <td>3</td>
  <td>A</td>
  <td>1/02/2022 9:03</td>
  <td>1/02/2022 12:30</td>
  <td>207</td>
  <td>6048168</td>
  <td>0</td>
  <td>Y</td>
  <td>207</td>
  <td>1</td>
  <td>4707</td>
  <td>4707</td>
  </tr>
  <tr>
  <td>4</td>
  <td>A</td>
  <td>1/02/2022 12:30</td>
  <td>1/02/2022 14:30</td>
  <td>120</td>
  <td>0</td>
  <td>542220</td>
  <td>Y</td>
  <td>327</td>
  <td>2</td>
  <td>4707</td>
  <td>4707</td>
  </tr>
  <tr>
  <td>5</td>
  <td>A</td>
  <td>7/02/2022 21:07</td>
  <td>7/02/2022 23:55</td>
  <td>168</td>
  <td>542220</td>
  <td>1380</td>
  <td>Y</td>
  <td>495</td>
  <td>3</td>
  <td>4707</td>
  <td>4708</td>
  </tr>
  <tr>
  <td>6</td>
  <td>A</td>
  <td>8/02/2022 0:18</td>
  <td>8/02/2022 5:30</td>
  <td>312</td>
  <td>1380</td>
  <td>1800</td>
  <td>Y</td>
  <td>807</td>
  <td>4</td>
  <td>4707</td>
  <td>4708</td>
  </tr>
  <tr>
  <td>7</td>
  <td>A</td>
  <td>8/02/2022 6:00</td>
  <td>8/02/2022 8:00</td>
  <td>120</td>
  <td>1800</td>
  <td></td>
  <td>Y</td>
  <td>927</td>
  <td>5</td>
  <td>4707</td>
  <td>4708</td>
  </tr>
  <tr>
  <td>8</td>
  <td>B</td>
  <td>7/10/2021 0:55</td>
  <td>7/10/2021 2:55</td>
  <td>120</td>
  <td></td>
  <td>629460</td>
  <td></td>
  <td>120</td>
  <td>1</td>
  <td>4708</td>
  <td>4709</td>
  </tr>
  <tr>
  <td>9</td>
  <td>B</td>
  <td>14/10/2021 9:46</td>
  <td>14/10/2021 13:59</td>
  <td>253.8</td>
  <td>629460</td>
  <td>65412</td>
  <td></td>
  <td>253.8</td>
  <td>1</td>
  <td>4709</td>
  <td>4710</td>
  </tr>
  <tr>
  <td>10</td>
  <td>B</td>
  <td>15/10/2021 8:10</td>
  <td>15/10/2021 13:59</td>
  <td>349.8</td>
  <td>65412</td>
  <td>2592</td>
  <td>Y</td>
  <td>349.8</td>
  <td>1</td>
  <td>4710</td>
  <td>4711</td>
  </tr>
  <tr>
  <td>11</td>
  <td>B</td>
  <td>15/10/2021 14:43</td>
  <td>15/10/2021 16:43</td>
  <td>120</td>
  <td>2592</td>
  <td>258120</td>
  <td>Y</td>
  <td>469.8</td>
  <td>2</td>
  <td>4710</td>
  <td>4711</td>
  </tr>
  <tr>
  <td>12</td>
  <td>B</td>
  <td>18/10/2021 16:25</td>
  <td>18/10/2021 18:25</td>
  <td>120</td>
  <td>258120</td>
  <td>88500</td>
  <td></td>
  <td>120</td>
  <td>1</td>
  <td>4711</td>
  <td>4712</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Time between next shift was calculated as below (staff match is just checking id is the same):</p>
  <pre><code>df.loc[df['staff_match'] == True, 'time_betw_next shift'] = aco['Start Time'].shift(-1) - df['End Time']
  </code></pre>
  <p>Time between previous shift was also calculated, as before it was just identifying the first row of grouped shifts as continuing, so this was a way of linking them.</p>
  <pre><code>df.loc[df['staff_match'] == True, 'time betw prevshift'] = df['Start Time'] - df['End Time'].shift() 
  </code></pre>
  <p>The function used to determine 'continuing' shift is:</p>
  <pre><code>def continuing(x):
      
      if ((x['time betw nextshift (s)'] &lt;= 3600) &amp; (x['time betw nextshift (s)'] &gt;= -2000)):
          return 'Y'
      elif ((x['time betw prevshift (s)'] &lt;= 3600) &amp; (x['time betw prevshift (s)'] &gt;= -2000)):
          return 'Y' 
      else:
          return np.nan
  </code></pre>
  <p>The negative number was used as sometimes the start of the next shift is incorrectly transcribed as slightly earlier then the end time of the prior shift. I feel there is something in this function which could be used to break up the incorrect grouping.</p>
  <p>The code used to generate the combined shift length ['SUMCOND'] and the identifier key ['Key'] are as follows:</p>
  <pre><code>g=df['shift length (m)'].groupby([df['staff_id'],df.continuing.ne(aco.continuing.shift()).cumsum()])
  df['SUMCOND']=g.cumsum()
  df['COUNTCOND']=g.cumcount()+1
  df[&quot;Key&quot;] = aco['COUNTCOND'].eq(1).cumsum()
  </code></pre>