How to merge different data collected at different times?

Hi,

I have three text files (attached). Text files containing data with different time scales (measurements done at different time intervals and with different instruments). First columne of each data set is Date/Time with fromat (YYYY-MM-DD HH:MM:SS). I need to make one excel table putting all the data with one merged Date/Time scale.

I tried to use the following code:

names = {'Sea_Level.txt','GW_Level.txt','Air_P.txt'};% Trying to read the text files
fid = fopen(names{1}); 
c = textscan(fid,'%s %s %.2f','endofline','\r\n');
fclose(fid);

selection = true(length(c{1}),1);
selection(2:2:length(selection)) = false;
for i = 1:length(c)
    c{i} = c{i}(selection);
end

dates = NaT(length(c{1}),1); %allocating memory for the resulting table
for i = 1:length(dates)
%    the first two rows:
    dates(i) = datetime(datenum(strcat(c{1}(i)," ",c{2}(i))),'ConvertFrom','datenum');
end

My wishing merged table is as the folowing screen shot (It is prepared for first few lines in each text file)

Do you have any recommended tricks for this?

Have a good day!

/Hedi

Answer 1

[KSSV]

1. Read all the files and save time of each file.
2. Get the minimum and maximum of time out of the three files.
3. Create date time array from minimum time to maximum time with desired time step.
4. Then you can use ismember or knnsearch to see where the times of each file lie from the main array. Get the indices.
5. Using the above indices arrange your data.
6. If required, you can do interpolation as well. Read about interp1.

[Harr]

Dear KSSV,

Thank you for your reply.

I failed from first step and i couldn't read and save time of each file using above code. Do you have any suggestion/change the code? Shall i use the above code?

Regarding the time step I dont want to do any interpolation but rather want to have all the measured data listed. The main aim here is to put the data together and later analyse it statistically if they are correlated.

Answer 2

[炜嘉 王]

I think storing data in <timetable> would help a lot.

1) Store data from .txt documents in <timetable>, using method <readtimetable> respectively.

2) Merge 3 <timetable> using method <synchronize>.

Although <timetable> is not very efficient as a data format, it is convenient for storing data with timestamps. <timetable> support time-dependent data sampling and interpolation, so data processing will also be much easier.

Hope this helps.

[Harr]

Thanks WW for your reply, I will work on it and let you know if i managed to do so :)

[Harr]

Dear WW, I didn't manage to solve it. I hope for more details. BTW/ I am using R2018b, I am not sure if i can use <readtimetable>.