numpy - merging rows with same time in array from csv data file

Question

I have some data log files, where sadly the logger choose to log data with a timecode with a presicion in floating of the seconds, even though logging every 2 second. For that reason in some of the files there can be several lines for the same sampling time.

Example start of log data:

12/1/2014 12:02:02 PM
1
Time,Notes,CO2 Pump Set Point,CO2 Pump Control Mode,CO2 Pump Alarm Set Point,CO2 Pump Flow,CO2 Pump Pressure,CO2 Pump Control Profile,CO2 Pump RPM,CO2 Pump Run State,CO2 Pump Alarm State,Cosolvent Pump Set Point,Cosolvent Pump Control Mode,Cosolvent Pump Alarm Set Point,Cosolvent Pump Flow,Cosolvent Pump Pressure,Cosolvent Pump Control Profile,Cosolvent Pump RPM,Cosolvent Pump Run State,Cosolvent Pump Alarm State,Heater Controller Zone 1 Set Point,Heater Controller Zone 2 Set Point,Heater Controller Zone 3 Set Point,Heater Controller Zone 4 Set Point,Heater Controller Zone 5 Set Point,Heater Controller Zone 6 Set Point,Heater Controller Zone 1 Alarm Set Point,Heater Controller Zone 2 Alarm Set Point,Heater Controller Zone 3 Alarm Set Point,Heater Controller Zone 4 Alarm Set Point,Heater Controller Zone 5 Alarm Set Point,Heater Controller Zone 6 Alarm Set Point,Heater Controller Zone 1 Temperature,Heater Controller Zone 2 Temperature,Heater Controller Zone 3 Temperature,Heater Controller Zone 4 Temperature,Heater Controller Zone 5 Temperature,Heater Controller Zone 6 Temperature,Heater Controller Zone 1 on/off Status,Heater Controller Zone 2 on/off Status,Heater Controller Zone 3 on/off Status,Heater Controller Zone 4 on/off Status,Heater Controller Zone 5 on/off Status,Heater Controller Zone 6 on/off Status,Heater Controller Zone 1 alarm Status,Heater Controller Zone 2 alarm Status,Heater Controller Zone 3 alarm Status,Heater Controller Zone 4 alarm Status,Heater Controller Zone 5 alarm Status,Heater Controller Zone 6 alarm Status,Pressure Regulator Set Point,Pressure Regulator Alarm Set Point,Pressure Regulator External Heater Set Point,Pressure Regulator External Heater Alarm Set Point,Pressure Regulator Valve Heater Set Point,Pressure Regulator Pressure,Pressure Regulator External Heater Temp.,Pressure Regulator Valve Heater Temp.,Pressure Regulator Run State,Pressure Regulator Needle Position,Pressure Regulator Alarm State
12:02:03:562,,50.000000,0,400,0.000000,0,2,0,0,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
12:02:04:125,,,,,,,,,,,,,,,,,,,,0,0,0,0,,,0,0,0,0,,,0,0,0,0,,,1,1,1,1,,,0,0,0,0,,,,,,,,,,,,,
12:02:04:218,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0,400,0,100,35,0,0,26,0,3649,0
12:02:05:625,,50.000000,0,400,0.000000,0,2,0,0,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
12:02:06:187,,,,,,,,,,,,,,,,,,,,0,0,0,0,,,0,0,0,0,,,0,0,0,0,,,1,1,1,1,,,0,0,0,0,,,,,,,,,,,,,
12:02:06:281,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0,400,0,100,35,0,0,26,0,3649,0
12:02:07:687,,50.000000,0,400,0.000000,0,2,0,0,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
12:02:08:250,,,,,,,,,,,,,,,,,,,,0,0,0,0,,,0,0,0,0,,,0,0,0,0,,,1,1,1,1,,,0,0,0,0,,,,,,,,,,,,,
12:02:08:343,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0,400,0,100,35,0,0,27,0,3649,0

getting the data out with header names by:

def timeConv(x):
    return DT.datetime.strptime(x, '%H:%M:%S:%f')
dat=genfromtxt(name,skip_header=2,delimiter=',',usecols=(2,6,32,33,34,35,50,55,59),names=True)
time=genfromtxt(name,skip_header=3,delimiter=',',usecols=(0), converters={0:timeConv},dtype=('S'))

giving me the first three lines in my data array that are of void type:

(50.0, 0.0, nan, nan, nan, nan, nan, nan, nan)
(nan, nan, 0.0, 0.0, 0.0, 0.0, nan, nan, nan)
(nan, nan, nan, nan, nan, nan, 0.0, 0.0, 3649.0)

I was thinking of going through each line in my data and compare dt to the preivous line, and then join the lines, but how do I join those data rows most effecient?

for i in range(len(time)-1):
    dt=time[i+1]-time[i]
    if dt.seconds==0:
        WHAT TO PUT HERE??

sorry within 1 second, as samples are taken every 2 second. Updated the if statement in original post — Termo, Mar 20 '15 at 12:14

score 0 · Answer 1 · answered Mar 12 '23 at 16:40

Solution without taking header names through genfromtxt:

dat=genfromtxt(name,skip_header=4,delimiter=',',usecols=(2,6,32,33,34,35,50,55,59))
dat=nan_to_num(dat)
c=0
idx=[]
for i in range(len(time)-1):
    dt=time[i+1]-time[i]
    if dt.seconds==0:
        if c==0:
            c=i
        dat[c]+= dat[i+1]
        idx.append(i+1)     
    else:
        c=0
dat=delete(dat,idx,axis=0)
time=delete(time,idx)

_{This answer was posted as an edit to the question numpy - merging rows with same time in array from csv data file by the OP Termo under CC BY-SA 3.0.}

numpy - merging rows with same time in array from csv data file

1 Answers1