title: Optimal way to Subtract rows based on column values in Python
body: <p>I need to subtract the values of the rows based on the values of a different column. My dataframe looks like this:</p>
  
  <pre><code>Id |    col1    |  col2    | col3 |
  1  | 2016-01-02 |  7:00:00 |  Yes |
  1  | 2016-01-02 |  7:05:00 |  No  |
  1  | 2016-01-02 |  7:10:00 |  Yes |
  1  | 2016-01-02 |  8:00:00 |  No  |
  2  | 2016-01-02 |  7:10:00 |  Yes | 
  2  | 2016-01-02 |  7:50:00 |  No  | 
  2  | 2016-01-02 |  9:00:00 |  No  | 
  2  | 2016-01-02 |  9:10:00 |  No  | 
  2  | 2016-01-02 |  9:15:00 |  No  | 
  3  | 2016-01-02 |  6:05:00 |  Yes | 
  3  | 2016-01-02 |  6:10:00 |  Yes | 
  3  | 2016-01-02 |  6:20:00 |  Yes | 
  3  | 2016-01-02 |  6:45:00 |  No  | 
  </code></pre>
  
  <p>I need to calculate the average time difference in the combination of <code>col1</code> and <code>col2</code> based on the value of <code>col3</code>. The rule is the following:</p>
  
  <blockquote>
    <p>Whenever there is a <code>Yes</code> in <code>col3</code> do <code>row-next row</code></p>
  </blockquote>
  
  <p>A simplified version of what I have done so far is to loop through all the values in the dataframe and do this:</p>
  
  <pre><code>for i in range(len(df)):
      if df['col3'][i] == 'Yes':
          date1 = datetime.combine(df['col1'][i], df['col2'][i])
          date2 = datetime.combine(df['col1'][i+1], df['col2'][i+1])
          dict[df['Id'][i]] = date1-date2
  </code></pre>
  
  <p>The variable <code>dict</code> is just a dictionary that holds the results for each different <code>Id</code>.</p>
  
  <p>Since I have more than 6MM rows, the loop takes a lot of time to complete, so I was wondering if someone could come up with a more efficient and elegant solution.</p>
  
  <p>Thanks!</p>