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

조회 수: 10 (최근 30일)
Eric Escoto
Eric Escoto 2021년 9월 22일
답변: Arun 2024년 2월 23일 11:19
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
Siddharth Bhutiya
Siddharth Bhutiya 2021년 9월 24일
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.

댓글을 달려면 로그인하십시오.

답변 (1개)

Arun
Arun 2024년 2월 23일 11:19
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.

카테고리

Help CenterFile Exchange에서 Tables에 대해 자세히 알아보기

제품

Community Treasure Hunt

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

Start Hunting!

Translated by