Combine two matrices according to datetime in first column
1 Ansicht (letzte 30 Tage)
Ältere Kommentare anzeigen
Tessa van Kol
am 17 Mai 2022
Kommentiert: Cris LaPierre
am 18 Mai 2022
Dear all,
I have two matrices 'rawT' and 'rawV' (see data.mat). I want to combine columns 2 until 4 of 'rawV' with the columns 1 until 4 of 'rawT' according to the date time in column 1.
For example, row 2 columns 2 until 4 of 'rawV'
must be in row 12 columns 5 until 7 of 'rawT'. This is because the first columns of both 'rawV' and 'rawT' contain nearly the same date and time.
My plan was to index the first column and match both first columns. I know that the date time values of the first columns don't match exactly with each other and the matrices are not the same length. Those pose problems.
tV = datetime(rawV(2:length(rawV),1),'Format','dd-MM-yyyy HH:mm');
tT = datetime(rawT(2:length(rawT),1),'Format','dd-MM-yyyy HH:mm');
idx = ismember(tV,tT);
0 Kommentare
Akzeptierte Antwort
Cris LaPierre
am 17 Mai 2022
You are going to have a challenge here because the tolerance indicated in your example (~1 minute) is much larger than the smallest step size in you data. This means that using +/- 1 min everywhere might result in combining duplicate rows.
In addition, you have a couple places where your time goes backwards, and then once sorted, several times where the adjacent rows have the same time, but different values. There is also one instance where you just have a date and no time. You will need to work out a solution for these situations.
There are two possible approaches I would start with: Convert your cells to timetables (cell2table and table2timetable), then use outerjoin or use synchronize to combine the two tables, horizontally concatenating the two data sets.
load TVKdata.mat
% Conver cells to timetables. I choose to sort
rawTt = cell2table(rawT(2:end,:),'VariableNames', rawT(1,:));
rawTt.tijd = datetime(rawTt.tijd,'InputFormat','dd-MM-yyyy HH:mm:ss');
rawTT = table2timetable(rawTt);
rawTT = sortrows(rawTT,"tijd");
rawVt = cell2table(rawV(2:end,:),'VariableNames', rawV(1,:));
rawVt.tijd = datetime(rawVt.tijd,'InputFormat','dd-MM-yyyy HH:mm:ss');
rawVV = table2timetable(rawVt);
rawVV = sortrows(rawVV,"tijd");
% Option 1: Join tables
joinedData = outerjoin(rawTT,rawVV)
% Option 2: synchronize
rawTT(ismissing(rawTT.tijd),:) = [];
finalTT = synchronize(rawTT,rawVV)
Note that the number of rows in both options is less than the sum of rows from rawT and rawV (210583), meaning it has combined rows that have the same exact time.
Weitere Antworten (0)
Siehe auch
Kategorien
Mehr zu Data Type Conversion finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!