Clear Filters
Clear Filters

If date time matches to the minute then horizontally concatenate rows that match

3 views (last 30 days)
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
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.

Sign in to comment.

Answers (1)

Arun
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:
  1. Convert the data to table (if it is in timetable datatype)
  2. Create a table to store the concatenated data.
  3. Loop through the table to concatenate the rows.
  4. Store the rows in the new table.
  5. 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)
ans = 12x1
16 17 18 19 37 38 39 41 42 43
disp(outputTT)
Time mean speed mean size median speed median size std speed std size mean speed' mean size' median speed' median size' std speed' std size' ____________________ __________ _________ ____________ ___________ _________ ________ ___________ __________ _____________ ____________ __________ _________ 06-Feb-2019 01:16:30 3.3139 0.96386 3.376 0.953 0.81839 0.28156 0 0 0 0 0 0 06-Feb-2019 01:17:00 2.7173 0.76784 2.414 0.678 1.0341 0.34045 0 0 0 0 0 0 06-Feb-2019 01:18:00 3.7035 1.1432 4.001 1.144 1.5225 0.55248 3.5142 1.0676 3.7485 0.961 1.3547 0.46847 06-Feb-2019 01:19:00 2.7142 0.77038 2.532 0.697 0.94552 0.28732 2.3973 0.64241 2.3405 0.6145 0.81192 0.16323 06-Feb-2019 01:37:30 2.8152 0.7881 2.799 0.8185 0.62494 0.14126 0 0 0 0 0 0 06-Feb-2019 01:38:00 2.4655 0.66624 2.451 0.634 0.71777 0.14366 2.1175 0.61993 2.15 0.574 0.66038 0.13551 06-Feb-2019 01:39:00 2.0857 0.58962 2.019 0.5825 0.80645 0.12156 1.803 0.50891 1.644 0.4955 0.68124 0.10837 06-Feb-2019 01:41:30 2.1418 0.61262 2.101 0.59 0.70167 0.15117 0 0 0 0 0 0 06-Feb-2019 01:42:00 2.309 0.64531 2.2535 0.623 0.58572 0.14374 2.5256 0.72661 2.3105 0.6085 0.95383 0.29122 06-Feb-2019 01:43:00 2.6622 0.70635 2.422 0.67 1.0006 0.26358 2.8456 0.8176 2.817 0.7465 1.1803 0.34251 06-Feb-2019 01:44:00 3.0137 0.86669 2.943 0.7905 1.0501 0.32522 2.8453 0.74371 2.874 0.735 0.94322 0.22611 06-Feb-2019 01:45:00 2.5548 0.68349 2.507 0.636 0.89092 0.19551 2.7669 0.78805 2.827 0.716 0.83307 0.93586
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.

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!