I'm fairly new to python and pandas but trying to get better with it for parsing and processing large data files. I'm currently working on a project that requires me to parse a a few hundred CSV CAN files at the time. The files have 9 columns of interest (1 ID and 7 data fields), have about 1-2 million rows, and are encoded in hex.
id Flags DLC Data0 Data1 Data2 Data3 Data4 Data5 Data6 Data7 cf11505 4 1 ff cf11505 4 1 ff cf11505 4 1 ff cf11a05 4 1 0 cf11505 4 1 ff cf11505 4 1 ff cf11505 4 1 ff cf11005 4 8 ff ff ff ff ff ff ff ff
I need to decode the hex, and then extract a bunch of different variables from it depending on the CAN ID.
I wrote a script to parse these files that looks like this:
import os import csv # imports the csv module import itertools import datetime import time from tkinter import filedialog from tkinter import Tk Tk().withdraw() filenames = filedialog.askopenfiles(title="Select .csv log file", filetypes=(("CSV files", "*.csv"), ("all files", "*.*"))) start = time.clock() flist =  for name in filenames: flist.append(name.name) for filename in flist: cur_file = ''.join(filename.split('/')[-1:]) print('working on ' + cur_file +'...') time_s = 0 var = [0,0,0,0] var4 = 0 var5 = 0 var6 = 0 var7 = 0 var8 = 0 var9 = 0 var10 = 0 var11 = 0 var12 = 0 ###########create new filename and filepath cur_file = ''.join(filename.split('/')[-1:]) #pulls filename of current file folders_to_append = '/Log Files--Processed/' + '/'.join(flist.split('/')[-3:-1]) #folders to append to new filepath trunc_filepath = '/'.join(filename.split('/')[0:-4]) new_filepath = trunc_filepath + folders_to_append + '/' new_filename= trunc_filepath + folders_to_append + '/processed_' + cur_file if not os.path.exists(new_filepath): os.makedirs(new_filepath) ########################################## csvInput = open(filename, 'r') # opens the csv file csvOutput = open(new_filename, 'w', newline='') writer = csv.writer(csvOutput) #creates the writer object writer.writerow(['Date','Time Since Start (s)', 'var1', 'var2', 'var3', 'var4', 'var5', 'var6', 'var7', 'var8', 'var9', 'var10', 'var11', 'var12', 'var13', 'var14', 'var15', 'var16']) try: reader = csv.reader(csvInput) data=list(reader) if (data == 'HEX'): dataType = 16 elif (data == 'DEC'): dataType = 10 else: print('Invalid Data Type') if (data == 'HEX'): idType = 16 elif (data == 'DEC'): idType = 10 else: print('Invalid ID Type') start_date = datetime.datetime.strptime(data,'%Y-%m-%d %H:%M:%S') for row in itertools.islice(data,8,None): #print(row) try: ID = int(row,idType) except: ID = 0 #print(ID) if (ID == 0xcf11005): for i in range(0,4): var[i] = float((int(row[2*i+6],dataType)<<8)|(int(row[2*i+5],dataType)))/10 elif (ID == 0xcf11505): var4 = int(row,dataType) elif (ID == 0xcf11605): var8 = str(bin(int(row,dataType))) var9 = str(bin(int(row,dataType))); elif (ID == 0xcf11a05): var10 = row elif (ID == 0xcf11e05): var11 = float((int(row,dataType)<<8)|(int(row,dataType))) var12 = float((int(row,dataType)<<8)|(int(row,dataType))) var13 = float((int(row,dataType)<<8)|(int(row,dataType))) elif (ID == 0xcf11f05): var14 = int(row,dataType) var15 = int(row,dataType)-40 var16 = int(row,dataType)-40 else: continue time_s = float(row) date = start_date+datetime.timedelta(seconds=time_s) writer.writerow([date,time_s, var, var, var, var, var4, var5, var6, var7, var8, var9, var10, var11, var12]) finally: csvInput.close() csvOutput.close() end = time.clock() print(end - start) print('done')
It basically uses the CSV reader and writer to generate a processed CSV file line by line for each CSV. For a 2 million row CSV CAN file, it takes about 40 secs to fully run on my work desktop. Knowing that line by line iteration is much slower than performing vectorized operations on a pandas dataframe, I thought I could do better, so I wrote a separate script (which I'll call script #2) where all the math was performed in a vectorized fashion, and then I used pandas .to_csv() function. Unfortunately, with script #2, the .to_csv() function took as long to run as the entire script #1, so it didn't end up being faster. I played around with the chunk size of .to_csv(), but I never managed to improve the runtime more than a second or so.
Is using the CSV reader/writer really the fastest way to do this? And if so, is there any way I can make my code run faster?