title: Python time series dataframe query inside for-loop
body: <p>I have a pandas dataframe <code>df</code> which I queried using a for loop that captures the events by considering the dummy variable of the <code>Status</code> column. The logic is that when <code>Status</code> changes from <code>0</code> to <code>1</code>, it marks the timestamp at <code>1</code> as the start of the event, when <code>Status</code> changes from <code>1</code> to <code>0</code>, it marks the timestamp at <code>0</code> as the end of the event.</p>
  <p><code>df</code> looks like this:</p>
  <pre><code>        ID  Timestamp               Value   Status
  103177  64  2010-09-21 23:13:21.090 21.5    1.0
  252019  64  2010-09-22 00:44:14.890 21.5    1.0
  271381  64  2010-09-22 00:44:15.890 21.5    0.0
  268939  64  2010-09-22 00:44:17.890 23.0    0.0
  259875  64  2010-09-22 00:44:18.440 23.0    1.0
  18870   64  2010-09-22 00:44:19.890 24.5    1.0
  205910  64  2010-09-22 00:44:23.440 24.5    0.0
  103865  64  2010-09-22 01:04:33.440 23.5    0.0
  152281  64  2010-09-22 01:27:01.790 22.5    0.0
  138988  64  2010-09-22 02:18:52.850 21.5    0.0
               ...
  </code></pre>
  <p>reproducible example:</p>
  <pre><code>from pandas import *
  from numpy import nan
  
  df = pd.DataFrame({'ID': {103177: 64,
    252019: 64,
    271381: 64,
    268939: 64,
    259875: 64,
    18870: 64,
    205910: 64,
    103865: 64,
    152281: 64,
    138988: 64},
   'Timestamp': {103177: Timestamp('2010-09-21 23:13:21.090000'),
    252019: Timestamp('2010-09-22 00:44:14.890000'),
    271381: Timestamp('2010-09-22 00:44:15.890000'),
    268939: Timestamp('2010-09-22 00:44:17.890000'),
    259875: Timestamp('2010-09-22 00:44:18.440000'),
    18870: Timestamp('2010-09-22 00:44:19.890000'),
    205910: Timestamp('2010-09-22 00:44:23.440000'),
    103865: Timestamp('2010-09-22 01:04:33.440000'),
    152281: Timestamp('2010-09-22 01:27:01.790000'),
    138988: Timestamp('2010-09-22 02:18:52.850000')},
   'Value': {103177: 21.5,
    252019: 21.5,
    271381: 21.5,
    268939: 23.0,
    259875: 23.0,
    18870: 24.5,
    205910: 24.5,
    103865: 23.5,
    152281: 22.5,
    138988: 21.5},
   'Status': {103177: 1.0,
    252019: 1.0,
    271381: 0.0,
    268939: 0.0,
    259875: 1.0,
    18870: 1.0,
    205910: 0.0,
    103865: 0.0,
    152281: 0.0,
    138988: 0.0}})
  
  df
  </code></pre>
  <p>query for-loop:</p>
  <pre><code>def my_func1(df):
      
      dataframe = []
      Start_time = []
      StartValue   = []
      End_time = []
      End_Value = []
      StartStatus = []
      End_Status = []
      ID = []
  
      state = 0
  
      for i in range(1, len(df.index)):
  
          if state == 0:
  
              if (df.loc[i, 'Status'] &gt; 0):
  
                  Start_time = df.loc[i, 'Timestamp']
                  StartValue = df.loc[i, 'Value']  
                  StartStatus = df.loc[i, 'Status']
                  ID = df.loc[i, 'ID']
  
                  state = 1
  
              else:
                  continue
  
          elif state == 1:
  
              if (df.loc[i, 'Status'] == 0):
              
                  End_time = df.loc[i, 'Timestamp']
                  End_Value = df.loc[i, 'Value']
                  End_Status = df.loc[i, 'Status'] 
                  
                  state = 0
  
              else:
                  continue
  
  
              dataframe.append([ ID, Start_time, StartValue, StartStatus, End_time, End_Value, End_Status])       
  
          else:
              continue
  
      # Dataframe 
      output_table = pd.DataFrame(dataframe, columns= [&quot;ID&quot;, &quot;StartTime&quot;, &quot;StartValue&quot;, &quot;StartStatus&quot;,  &quot;EndTime&quot;, &quot;EndValue&quot;, &quot;EndStatus&quot;])
  
      return output_table
  </code></pre>
  <p>and output table format looks like this(just an example, not same data as above):</p>
  <pre><code>    ID  StartTime                StartValue StartStatus EndTime                 EndValue EndStatus
  0   64  2010-09-22 00:44:14.890  21.5       1.0         2010-09-22 00:44:15.890 21.5     0.0
  1   64  2010-09-22 00:44:18.440  23.0       1.0         2010-09-22 00:44:23.440 24.5     0.0
  2   64  2010-09-23 00:06:48.970  100.0      1.0         2010-09-23 00:06:52.970 100.0    0.0
  3   64  2010-09-23 00:07:14.970  100.0      1.0         2010-09-23 00:07:23.970 100.0    0.0
  4   64  2010-09-23 02:07:52.990  100.0      1.0         2010-09-23 02:08:13.990 100.0    0.0
  </code></pre>
  <p>Now I want to look into each of the events <strong>inside the original dataframe <code>df</code></strong> using each row in <code>output_table</code>, where each event can be found by filtering <code>Timestamp</code> in between <code>StartTime</code> and <code>EndTime</code>. How can I apply another of my own defined function <code>my_func2</code> to the data of each these individual event in <code>df</code>? (<code>my_func2</code> is supposed to create a label in <code>output_table</code> that represents if each event satisfies some condition by considering the <code>Value</code> column.)</p>
  <hr />
  <p>Edit:</p>
  <p>To be more specific, <code>my_func2</code> measures continuous time interval where <code>Value</code> stays above <code>30</code> for at least 10 seconds. Then I want to find the overlap of <code>my_func1</code> and <code>my_func2</code>, and eventually calculate the <code>median</code> value in this time interval for each event.</p>
  <p>I guess I could work out the overlap by comparing the timestamps of the start and end points of the 2 events(calculated by the two functions). What I need help with is how to find the original data in <code>df</code> for calculating the <strong>median</strong> value for each of the individual event? Any idea is appreciated.</p>