How to read in a non-standard formated CSV file containing times and measurments and change the output format?

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

[meghannmarie]

If all times are in sequential order and you have and ABC, DEF, GHI data for every time step then something like this will work:

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

time_idx = contains(raw(:,1),'TIM');
abc_idx = contains(raw(:,1),'ABC');
def_idx = contains(raw(:,1),'DEF');
ghi_idx = contains(raw(:,1),'GHI');

time = unique(cell2mat(raw(time_idx,2:3)),'rows');
abc = raw(abc_idx,2);
def = raw(def_idx,2);
ghi = raw(ghi_idx,2);

T = table(time(:,1),time(:,2),abc,def,ghi,'VariableNames',{'DATE','TIME','ABC','DEF','GHI'});
writetable(T,data_reformat.csv);

[Mark Jackson]

The times are in sequential order but there are two complications I failed to mention. Some of the repeated times are slightly different, not exact copies. Also, while almost every time has the three measurements (ABC,DEF,GHI) a few odd cases have one (or more missing). For example:

TIM, 20190805, 110000.00
ABC, 123
TIM, 20190805, 110000.56
DEF, 456
GHI, 789
TIM, 20190805, 110001.00
ABC, 123
GHI, 789
TIM, 20190805, 110002.00
ABC, 123
DEF, 456
TIM, 20190805, 110002.35
GHI, 789

Note the fractional seconds in the times and the DEF measurement missing in the middle.

I'm quite happy to ignore the fractions on the seconds and just keep the integer seconds. Any thoughts on how to amend your example to cope with this? Rounding the time values? Also for missing measurements, something like NaN would work

[meghannmarie]

I think I have figured it out. Is the second field in the time hhmmss.ss?

[Mark Jackson]

In my example the second field is hhmmss.SS. In truth I believe the data is hhmmss.SSSS hope this doesn't change things

The full time (TIM) format is yyyyMMdd hhmmss.SSSS

[meghannmarie]

Do you want to round to nearest second or drop the fraction? In your example I was using, there would be 2 values for GHI in 110001 second value if you round.

[Mark Jackson]

I'm quite happy to drop the fraction if that makes things easier. I don't need the extra precision in the times

Answer 1

[meghannmarie]

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

[Mark Jackson]

Worked a treat!! Thanks for your help

Answer 2

[Jeremy Hughes]

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