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?