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

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]

[Siddharth Bhutiya]

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.

[Eric Escoto]

Heres a sample file.

Answer 1

[Arun]

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.