If date time matches to the minute then horizontally concatenate rows that match
2 views (last 30 days)
Show older comments
Example data:
I didn't write it correctly. But, assume that the first column is a datenum/datetime, etc. in order. In fact the data I have is in a timetable format. It would be preferable to work with just the timetable. datenum_1 = datenum_2 =/= datenum_3.
Task: concatinate rows one and two because datenums are equivalent. Leave row three alone.
Result: New timetable with two rows and 0-values filled in where needed.
Notes: The data is in 30 second and 1 minute intervals. When I say two datenums match that means to the minute value (i. e. 13:30:00 and 13:30:30).
A = [datenum_1 1 2;datenum_2 3 4; datenum_3 5 6]
Result:
C = [datenum_1 1 2 3 4; datenum_3 5 6 0 0]
2 Comments
Siddharth Bhutiya
on 24 Sep 2021
You mentioned that your original problem is in timetable form. Solving this in timetable should be easier. Could you post the example (input and expected output) in timetable form? It looks like you start with a timetable with 2 variables and the result has 4 variables.
Answers (1)
Arun
on 23 Feb 2024
Hi Eric,
I understand that you want to concatenate the rows with same minute values. There are maximum 2 rows for a particular minute and the minute values having no match should have the row filled with zero for the remaining columns.
This can be achieved by following these steps:
- Convert the data to table (if it is in timetable datatype)
- Create a table to store the concatenated data.
- Loop through the table to concatenate the rows.
- Store the rows in the new table.
- Exit the loop when no more data to concatenate.
Here is a code that performs the above steps and generate the required table:
load("output.mat")
%convert the data in table format
TTtable = timetable2table(TTtest);
%create a table to store the concatenated data with twice the columbs
VarNames = TTtable.Properties.VariableNames;
VarNames = [VarNames,VarNames(2:end)+"'"];
dataTypes = varfun(@class, TTtable, 'OutputFormat', 'cell');
dataTypes = [dataTypes,dataTypes(2:end)];
outputTT = table('Size', [0 numel(VarNames)], 'VariableNames', VarNames, 'VariableTypes', dataTypes);
%loop through the table to find the rows for same minute value
i = 1;
while i<= height(TTtable)
%for last row if it the first value for that minute
if i == height(TTtable)
outputTT(end+1,:) = [table2cell(TTtable(i,:)),0,0,0, 0, 0, 0]
else
%check if there is another value for that minute.
%if yes, concatenate the rows
if minute(TTtable(i,:).Time) == minute(TTtable(i+1,:).Time)
outputTT(end+1,:) = [table2cell(TTtable(i,:)),table2cell(TTtable(i+1,2:end))];
i = i+1;
else
%add zeros for the remaning columns
outputTT(end+1,:) = [table2cell(TTtable(i,:)),0,0,0, 0, 0, 0];
end
end
i = i+1;
end
% output to verify
minute(outputTT.Time)
disp(outputTT)
For more information related to table please refer the documentation link: https://www.mathworks.com/help/matlab/tables.html
Hope this help to get the data in required format.
HTH.
0 Comments
See Also
Categories
Find more on Dates and Time in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!