How to merge different data collected at different times?

3 views (last 30 days)
Harr on 8 Apr 2021
Edited: Harr on 9 Apr 2021
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');
selection = true(length(c{1}),1);
selection(2:2:length(selection)) = false;
for i = 1:length(c)
c{i} = c{i}(selection);
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');
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!

Answers (2)

KSSV on 8 Apr 2021
  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.
  1 Comment
Harr on 8 Apr 2021
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.

Sign in to comment.

炜嘉 王
炜嘉 王 on 8 Apr 2021
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 on 8 Apr 2021
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>.

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!