Combine two matrices according to datetime in first column
1 visualización (últimos 30 días)
Mostrar comentarios más antiguos
Tessa van Kol
el 17 de Mayo de 2022
Comentada: Cris LaPierre
el 18 de Mayo de 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 comentarios
Respuesta aceptada
Cris LaPierre
el 17 de Mayo de 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.
Más respuestas (0)
Ver también
Categorías
Más información sobre Data Type Conversion en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!