Hi
I have a large CSV data file which I want to read in to matlab and correlate data times (shown as TIM in the example below) with various data measurements (ABC, DEF, GHI). The structure of the data file (which I cannot change) is such that the date / time is stated on a single line and the following lines (until the next TIM line) contain measurements taken at the previous time step. Broadly speaking the data format is as follows:
TIM, 20190805, 110000ABC, 123DEF, 456GHI, 789TIM, 20190805, 110001ABC, 123DEF, 456GHI, 789TIM, 20190805, 110002ABC, 123DEF, 456GHI, 789
The output I want is
DATE , TIME , ABC, DEF, GHI20190805, 110000, 123, 456, 78920190805, 110001, 123, 456, 78920190805, 110002, 123, 456, 789
so i can easily correlate times with measurements. The brute force approach would be to loop over every line, save the date / time when it appears and keep reading over the measurements until you reach then next time stamp. Once you hit this, save everything into an array of some flavour (cell etc) with the time step. However, my CSV file is very large so reading through and parsing the whole file line-by-line is VERY slow. Hence I am looking for a quicker way to read in the data and get to the output I want.
I first approached this by using the readtable command in MATLAB to get a MATLAB table, then find all the time/date (TIM) rows and use row offsets e.g. ABC = 1, DEF = 2, GHI = 3 etc to filter out the measurement rows. I then end up with four arrays TIM, ABC, DEF, GHI. However, as always, this is proving to be a nightmare due to inconsistencies in the data. Examples of this are
TIM, 20190805, 110000ABC, 123GHI, 789DEF, 456TIM, 20190805, 110001ABC, 123GHI, 789DEF, 456TIM, 20190805, 110002ABC, 123DEF, 456GHI, 789
(Note that the order of DEF and GHI have been switched in the first two blocks)
TIM, 20190805, 110000ABC, 123TIM, 20190805, 110000DEF, 456GHI, 789TIM, 20190805, 110001ABC, 123DEF, 456GHI, 789TIM, 20190805, 110002ABC, 123DEF, 456TIM, 20190805, 110002GHI, 789
(Note the repeated date / time (TIM) lines with the same times)
Any thoughts on a robust reader to transform my data?
Thanks in advance
Best Answer