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

filenames = filedialog.askopenfiles(title="Select .csv log file", filetypes=(("CSV files", "*.csv"), ("all files", "*.*")))

start = time.clock()

flist = []
for name in filenames: 


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[0].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):

    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'])

        reader = csv.reader(csvInput)

        if (data[3][1] == 'HEX'): dataType = 16
        elif (data[3][1] == 'DEC'): dataType = 10
        else: print('Invalid Data Type')

        if (data[4][1] == 'HEX'): idType = 16
        elif (data[4][1] == 'DEC'): idType = 10
        else: print('Invalid ID Type') 

        start_date = datetime.datetime.strptime(data[6][1],'%Y-%m-%d %H:%M:%S')      

        for row in itertools.islice(data,8,None):
            try: ID = int(row[2],idType)
            except: ID = 0


            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[5],dataType)
            elif (ID == 0xcf11605):
                var8 = str(bin(int(row[5],dataType)))
                var9 = str(bin(int(row[6],dataType)));
            elif (ID == 0xcf11a05):
                var10 = row[5]
            elif (ID == 0xcf11e05):
                var11 = float((int(row[6],dataType)<<8)|(int(row[5],dataType)))
                var12 = float((int(row[8],dataType)<<8)|(int(row[7],dataType)))
                var13 = float((int(row[10],dataType)<<8)|(int(row[9],dataType)))
            elif (ID == 0xcf11f05):
                var14 = int(row[5],dataType)
                var15 = int(row[6],dataType)-40
                var16 = int(row[7],dataType)-40

            time_s = float(row[0])
            date = start_date+datetime.timedelta(seconds=time_s)
            writer.writerow([date,time_s, var[0], var[1], var[2], var[3], var4, var5, var6, var7, var8, var9, var10, var11, var12])


end = time.clock()

print(end - start)

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?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.