How to read in a non-standard formated CSV file containing times and measurments and change the output format?
조회 수: 3 (최근 30일)
이전 댓글 표시
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
댓글 수: 6
meghannmarie
2019년 8월 6일
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.
채택된 답변
meghannmarie
2019년 8월 6일
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');
추가 답변 (1개)
Jeremy Hughes
2019년 8월 5일
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"])
댓글 수: 0
참고 항목
카테고리
Help Center 및 File Exchange에서 Text Files에 대해 자세히 알아보기
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!