Asked by Mark Jackson
on 5 Aug 2019

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

ABC, 123

DEF, 456

GHI, 789

TIM, 20190805, 110001

ABC, 123

DEF, 456

GHI, 789

TIM, 20190805, 110002

ABC, 123

DEF, 456

GHI, 789

The output I want is

DATE , TIME , ABC, DEF, GHI

20190805, 110000, 123, 456, 789

20190805, 110001, 123, 456, 789

20190805, 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, 110000

ABC, 123

GHI, 789

DEF, 456

TIM, 20190805, 110001

ABC, 123

GHI, 789

DEF, 456

TIM, 20190805, 110002

ABC, 123

DEF, 456

GHI, 789

(Note that the order of DEF and GHI have been switched in the first two blocks)

TIM, 20190805, 110000

ABC, 123

TIM, 20190805, 110000

DEF, 456

GHI, 789

TIM, 20190805, 110001

ABC, 123

DEF, 456

GHI, 789

TIM, 20190805, 110002

ABC, 123

DEF, 456

TIM, 20190805, 110002

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

Answer by meghannmarie
on 6 Aug 2019

Accepted Answer

This gets the anwer if you drop the fraction and the time format is HHmmss, if you have a fraction you can edit the line with time format.

[~,~,raw] = xlsread('data.csv');

num_rows = size(raw,1);

time_idx = contains(raw(:,1),'TIM');

abc_idx = contains(raw(:,1),'ABC');

def_idx = contains(raw(:,1),'DEF');

ghi_idx = contains(raw(:,1),'GHI');

val_times = NaT(num_rows,1);

date = datetime([raw{time_idx,2}]','ConvertFrom','yyyymmdd');

time = datetime(cellstr(num2str([raw{time_idx,3}]','%06d')),'InputFormat','HHmmss');% edit '%06d' and 'HHmmss' if you have decimal seconds

val_times(time_idx) = date + timeofday(time);

val_times = fillmissing(val_times,'previous');

times = unique(val_times);

vals = raw(:,2);

output_data = cell(numel(times),4);

output_data(:) = {NaN};

output_data(:,1) = cellstr(datestr(times));

abc = vals(abc_idx);

abc_times = val_times(abc_idx);

[~,I] = intersect(times,abc_times,'stable');

output_data(I,2) = abc;

def = vals(def_idx);

def_times = val_times(def_idx);

[~,I]=intersect(times,def_times,'stable');

output_data(I,3) = def;

ghi = vals(ghi_idx);

ghi_times = val_times(ghi_idx);

[~,I]=intersect(times,ghi_times,'stable');

output_data(I,4) = ghi;

T = cell2table(output_data,'VariableNames',{'DATE','ABC','DEF','GHI'});

writetable(T,'data_reformat.csv');

Answer by Jeremy Hughes
on 5 Aug 2019

You could do this with textscan

fid = fopen(file);

vars = textscan(fid,'TIM%f%fABC%fDEF%fGHI%f','Delimiter',{','},'Whitespace',' \n')

T = table(vars{:},'VariableNames',["DATE","TIME","ABC","DEF","GHI"])

Sign in to comment.

Opportunities for recent engineering grads.

Apply Today
## 6 Comments

## meghannmarie (view profile)

## Direct link to this comment

https://au.mathworks.com/matlabcentral/answers/474928-how-to-read-in-a-non-standard-formated-csv-file-containing-times-and-measurments-and-change-the-outp#comment_732096

## Mark Jackson (view profile)

## Direct link to this comment

https://au.mathworks.com/matlabcentral/answers/474928-how-to-read-in-a-non-standard-formated-csv-file-containing-times-and-measurments-and-change-the-outp#comment_732139

## meghannmarie (view profile)

## Direct link to this comment

https://au.mathworks.com/matlabcentral/answers/474928-how-to-read-in-a-non-standard-formated-csv-file-containing-times-and-measurments-and-change-the-outp#comment_732162

## Mark Jackson (view profile)

## Direct link to this comment

https://au.mathworks.com/matlabcentral/answers/474928-how-to-read-in-a-non-standard-formated-csv-file-containing-times-and-measurments-and-change-the-outp#comment_732164

## meghannmarie (view profile)

## Direct link to this comment

https://au.mathworks.com/matlabcentral/answers/474928-how-to-read-in-a-non-standard-formated-csv-file-containing-times-and-measurments-and-change-the-outp#comment_732207

## Mark Jackson (view profile)

## Direct link to this comment

https://au.mathworks.com/matlabcentral/answers/474928-how-to-read-in-a-non-standard-formated-csv-file-containing-times-and-measurments-and-change-the-outp#comment_732265

Sign in to comment.