Iterate through rows in pandas dateframe in a cleaner way using .iterrows() and keep track of rows inbetween specific values

Viewed 991

I've got a pandas dataframe in python 2.7 and I want to iterate through the rows and get the time between two types of events as well as the count of other types of events in between (given certain conditions).

My data is a pandas.DateFrame that looks like this:

     Time  Var1  EvntType  Var2
0    15    1     2         17
1    19    1     1         45
2    21    6     2         43
3    23    3     2         65
4    25    0     2         76 #this one should be skipped
5    26    2     2         35
6    28    3     2         25
7    31    5     1         16
8    33    1     2         25
9    36    5     1         36
10   39    1     2         21

Where I want to disregard rows where Var1 equals 0 and then count the time between events of type 1, and the events of type 2 (except where Var1 == 0) inbetween the events of type 1. So in the above case:

Start_time: 19, Time_inbetween: 12, Event_count: 4
Start_time: 31, Time_inbetween: 5, Event_count: 1

I'm doing this in the following way:

i=0
eventCounter = 0
lastStartTime = 0
length = data[data['EvntType']==1].shape[0]
results = np.zeros((length,3),dtype=int)
for row in data[data['Var1'] > 0].iterrows():
    myRow = row[1]
    if myRow['EvntType'] == 1:
        results[i,0] = lastStartTime
        results[i,1] = myRow['Time'] - lastStartTime
        results[i,2] = eventCounter
        lastStartTime = myRow['Time']
        eventCounter = 0
        i += 1
    else:
        eventCounter += 1

which gives me the desired result:

>>> results[1:]
array([[19, 12,  4],
       [31,  5,  1]])

But this seems really circumvent and takes a long time on large dataframes. How can I improve this?

1 Answers
Related