Calculate time difference between entries in a file using python
Question: I have csv file with the data formatted like this `date,time,event,user,net` .
I need to go through each line of this file, and if event == start, continue
till it reach the line with event == end for the same user and net, then
calculate the time difference between the two events. I have this code:

    
    
    import csv
    import datetime
    import time
    
    with open('dates.csv', 'rb') as csv_file:
        csv_read = csv.reader(csv_file)
        for row in csv_read:
            if row[2]=="start":
                n1=datetime.datetime.strptime(row[1], '%H:%M:%S')
    
                for row2 in csv_read:
                    if (row2[2]=="End" and row[3]==row2[3] and row[4]==row2[4]):
                        n2=datetime.datetime.strptime(row2[1], '%H:%M:%S')
    
                        print row[2],row[1], row2[2], row2[1]
    
                        diff = n2 - n1
                        print "time difference = ", diff.seconds
                        break
    

But the problem with this code is that when it find a match "End" and
calculate the time, it will start searching from the line after the with the
match "End", ignoring the lines before it. as an example

    
    
    May,20,9:02:22,2010,start,user1,net-3
    May,20,9:02:23,2010,start,user1,net-3
    May,20,9:02:55,2010,start,user1,net-2
    May,20,9:02:55,2010,End,user1,net-3
    May,20,9:03:43,2010,End,user1,net-2
    May,20,9:02:55,2010,End,user1,net-3
    May,20,9:03:43,2010,End,user1,net-2
    May,20,9:03:44,2010,start,user1,net-2
    May,20,9:03:49,2010,End,user1,net-2
    

will only produce the following output:

    
    
    Connect 9:02:22 Disconnect 9:02:55
    time difference =  33
    Connect 9:03:44 Disconnect 9:03:49
    time difference =  5
    

So does anyone have an idea how to solve this problem, please? Also is it
possible to add the time difference as an extra column to the existing data?

thanks

I've updated the code, but now I'm facing a new problem, my file contains
35734 rows, but the output file contain only 350 rows, I'm confused as to why
this happens, and I would appreciate any help, thanks Updated Code:

    
    
    l1=[]  ## empty list
    l2=[]  ## empty list
    
    csv_file=open('dates_read.csv', 'r+')
    csv_wfile=open('dates_write.csv', 'w+')
    
    csv_read = csv.reader(csv_file)
    csv_read1 = csv_read
    csv_write = csv.writer(csv_wfile)
    for row in csv_read:
        s=csv_read.line_num
        if (row[4]=="start" and (s not in l1)):
            n1=datetime.datetime.strptime(row[2], '%H:%M:%S')
    
            l1.append(s)
            month = str(row[0])
            day = int(row[1])
            time = str(row[2])
            year = int(row[3])
            user = str(row[5])
            net = str(row[6])
            dwell_time = str(row[7])
            for row2 in csv_read1:
                e=csv_read1.line_num
                if (row2[4]=="End" and row[5]==row2[5] and row[6]==row2[6] and (csv_read1.line_num not in l2)and s<e):
                    n2=datetime.datetime.strptime(row2[2], '%H:%M:%S')
                    diff = n2 - n1
                    dwell_time= diff
                    print("time difference = ", diff.seconds,"\n")
                    csv_write.writerow([month, day, time, year, user, net, dwell_time])
                    l2.append(e)
                    break
    print (s) #prints 818
    print (e) #prints 35734 
    


Answer: The only problem with your code is that you are traversing the lines for
**END** keyword right after it encounters the first **START** . Instead it
should traverse the line of the file from the beginning. With that we also had
to take into consideration that same line doesn't get traversed again. For
that we can use a list, that can hold the line number of the line been
traversed.

Instead of writing a new code i made changes to your code only.

    
    
    >>> l=[]  ## empty list
    >>> csv_file=open('dates.csv')
    >>> csv_read = csv.reader(csv_file)
    >>> for row in csv_read:
                if row[0].split()[4]=="start":
                    n1=datetime.datetime.strptime(row[0].split()[2], '%H:%M:%S')
                    s=csv_read.line_num
                    csv_file1=open('/Python34/Workspace/Stoverflow/dates.csv')
                    csv_read1 = csv.reader(csv_file1)
                    for row2 in csv_read1:
                        e=csv_read1.line_num
                        ## Inside if iam adding to more checks that verify that the same line is not traversed again and the END flag is always encountered after START flag
                        if (row2[0].split()[4]=="End" and row[0].split()[6]==row2[0].split()[6] and row[0].split()[5]==row2[0].split()[5] and (csv_read1.line_num not in l) and s<csv_read1.line_num):
                            n2=datetime.datetime.strptime(row2[0].split()[2], '%H:%M:%S')
                            print("Connect : ",row[0].split()[2]," Disconnect :",row2[0].split()[2])
                            diff = n2 - n1
                            print("time difference = ", diff.seconds,"\n")
                            l.append(csv_read1.line_num)
                            del csv_read1
                        break
                del csv_file1
    

