使用python计算文件中条目之间的时间差

Question

我有一个csv文件，数据格式是这样的date,time,event,user,net。我需要检查这个文件的每一行，如果event ==开始，继续，直到它到达同一用户和网络的event == end行，然后计算两个事件之间的时间差。我有这样的代码：

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

但这段代码的问题是，当它找到匹配的"End“并计算时间时，它将从匹配"End”之后的行开始搜索，忽略它前面的行。举个例子

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

将仅生成以下输出：

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

有谁知道如何解决这个问题吗？还可以将时间差作为额外的列添加到现有数据中吗？

谢谢

我已经更新了代码，但现在我面临一个新的问题，我的文件包含35734行，但输出文件只包含350行，我不明白为什么会发生这种情况，如果有任何帮助，我将不胜感激，谢谢更新的代码：

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 1

代码的唯一问题是，在遇到第一个START之后，您正在遍历END关键字的各行。相反，它应该从头开始遍历文件的行。因此，我们还必须考虑到不会再次遍历相同的行。为此，我们可以使用一个列表，它可以保存被遍历的行的行号。

我只对你的代码做了修改，而不是写新的代码。

>>> 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

Answer 2

我认为使用地图来解决这个问题会更好。

将(user_id，net_id)定义为键，将(start_status，start_time)定义为值，如下所示：

class UserNet:
   user_id = -1
   net_id = -1
   // Other Operation

class StartStatus:
   start_flag = False
   start_time = -1
   // Other Operation

当您读取一行时，首先判断该行中的状态字符串是START还是END。

如果结束了，则使用从该行读取在新映射结构中查找，查找start_time和减号以获得答案。

如果是START，则将该值插入到新map结构中。

如果你不想要错误判断，那么start_flag是不必要的，它是表示重复开始的标志，也许你不需要它。