Sort matrix by date

Hello,

I have a matrix where the first collumn is date and the rest is different parameters like voltage and power etc..

The first collumn has for some reason been sorted like this: 01-jan-2019, 01-feb-2019, 01-mar2019 etc.

Is there some way that i can sort the whole matrix so that it goes like 01-jan-2019, 02-jan-2019 etc?

i have used the sort function on the datetime values and that works, but the rest of the matrix wont follow.

thanks in advance

[Andrei Bobrov]

Please attach small example your data as mat-file.

Answer 1

[Walter Roberson]

[~, sortidx] = sort(TheDatetimeValues);
YourMatrix = YourMatrix(sortidx,:);

[simen ommundsen]

hi,

i get this error when im trying to do it:

Error using tabular/sort

Too many output arguments.

Here is my code:

time=Data(:,1);
[~, sortidx] = sort(time);
Data = Data(sortidx,:);

Is it a problem that Data is in table format?

[Walter Roberson]

In the special case of sorting a table on the first variable, all you need is

Data = sort(Data);

[simen ommundsen]

I cant get it to work.. I get this error now:

Undefined function 'sort' for input arguments of type 'table'. Use the SORTROWS function instead.

The first collumn of my table is the date but it is not in datetime form. Do i need to change this first? because now i extract that column out and the transform it to the right datetime format that i want.

[Walter Roberson]

time = datetime(Data{:, 1});

Then do the sortidx approach

[simen ommundsen]

This is how the table looks like.

so now the date is 01-jan-2019 and time so its in "ddMMyyHHmmss" format now

Date                  Irradiance               voltage
10119092137.0000	31.6149100000000	1.00391000000000
10119093744.0000	114.917850000000	3.51951300000000
10119095238.0000	198.553969000000	7.00544400000000
10119095344.0000	202.762832000000	7.12712300000000
10119095946.0000	228.961811000000	8.49680100000000
10119100102.0000	234.781545000000	9.19912800000000
10119100249.0000	242.870823000000	10.7862560000000
10119100355.0000	247.440674000000	11.8221940000000
10119100500.0000	255.243218000000	12.9416470000000
10119100611.0000	260.973431000000	13.5564850000000
10119100847.0000	274.669557000000	14.1654710000000
10119101032.0000	278.580684000000	13.8827170000000
10119101139.0000	283.998185000000	13.8607250000000
10119101409.0000	293.313452000000	13.7188200000000
10119101539.0000	299.988929000000	13.8922410000000
10119101647.0000	303.132022000000	14.1880290000000
10119101756.0000	308.187098000000	15.1524130000000
10119101913.0000	311.902933000000	15.9656160000000

[Andrei Bobrov]

This is how the date will look: 15-Jan-2019 18:45:37

150119184537.0000?

[simen ommundsen]

Yes

[Peter Perkins]

"now the date is 01-jan-2019 and time so its in "ddMMyyHHmmss" format now"

No, it absolutely isn't. It format were dMMyyHHmmss.SSSS. If it were a datetime, and had the format you say it does, it would look like this:

>> datetime('01-jan-2019','Format',"ddMMyyHHmmss")
ans = 
  datetime
   010119000000

In any case, if it's really a datetime, make a timetable, not a table, and call sortrows with just the timetable. sortrows, not sort, as the error msg says: you can't call sort on a time/table, because you always want to sort rows.

If it's numeric, or who knows what else, convert it to a datetime and make a timetable.

>> Date = datetime(2019,1,1,9,0,0,'Format',"ddMMyyHHmmss.SSSS") + hours(2*rand(10,1));
>> Irradiance = rand(10,1);
>> Voltage = rand(10,1);
>> tt = timetable(Date,Irradiance,Voltage)
tt =
  10×2 timetable
          Date           Irradiance    Voltage
    _________________    __________    _______
    010119102443.5318     0.43874      0.27603
    010119090349.1964     0.38156       0.6797
    010119093313.8454     0.76552       0.6551
    010119090532.4340      0.7952      0.16261
    010119091139.3488     0.18687        0.119
    010119103848.8963     0.48976      0.49836
    010119102322.7660     0.44559      0.95974
    010119093803.1162     0.64631      0.34039
    010119105401.5987     0.70936      0.58527
    010119090408.0117     0.75469      0.22381
>> tt = sortrows(tt)
tt =
  10×2 timetable
          Date           Irradiance    Voltage
    _________________    __________    _______
    010119090349.1964     0.38156       0.6797
    010119090408.0117     0.75469      0.22381
    010119090532.4340      0.7952      0.16261
    010119091139.3488     0.18687        0.119
    010119093313.8454     0.76552       0.6551
    010119093803.1162     0.64631      0.34039
    010119102322.7660     0.44559      0.95974
    010119102443.5318     0.43874      0.27603
    010119103848.8963     0.48976      0.49836
    010119105401.5987     0.70936      0.58527

Then change the format to something more readblae:

>> tt.Date.Format = 'dd-MMM-yyyy HH:mm:ss.SSSS'
tt =
  10×2 timetable
              Date               Irradiance    Voltage
    _________________________    __________    _______
    01-Jan-2019 09:03:49.1964     0.38156       0.6797
    01-Jan-2019 09:04:08.0117     0.75469      0.22381
    01-Jan-2019 09:05:32.4340      0.7952      0.16261
    01-Jan-2019 09:11:39.3488     0.18687        0.119
    01-Jan-2019 09:33:13.8454     0.76552       0.6551
    01-Jan-2019 09:38:03.1162     0.64631      0.34039
    01-Jan-2019 10:23:22.7660     0.44559      0.95974
    01-Jan-2019 10:24:43.5318     0.43874      0.27603
    01-Jan-2019 10:38:48.8963     0.48976      0.49836
    01-Jan-2019 10:54:01.5987     0.70936      0.58527

[Peter Perkins]

Walter, a small FYI: sortrows on a table with no other inputs sorts on all the variables. sortrows on a timetable with no other inputs sorts on the time. The table you were referring to only had one var, so maybe that's what you meant.

[Walter Roberson]

https://www.mathworks.com/help/matlab/ref/sortrows.html#description

sortrows(tblA) sorts the rows of the table in ascending order based upon the values in the first variable

[Peter Perkins]

Yes, I guess we're sort of both right. I'm just noting that "If elements in the first variable are repeated, then sortrows sorts by the elements in the second variable, and so on."

[Walter Roberson]

timetables happen to have the property that rowtimes are unique, so it is never necessary to refer to the second or further variables to break ties.

[Peter Perkins]

Some timetables have unique row times. But it's not a requirement. In fact, in descending order of "niceness", the row times need not be

• regularly-spaced
• ascending
• sorted
• unique
• even defined

>> tt = timetable([1;2;3;4;5],'RowTimes',datetime(2019,5,[5;5;2;1;NaN]))
tt =
  5×1 timetable
       Time        Var1
    ___________    ____
    05-May-2019     1  
    05-May-2019     2  
    02-May-2019     3  
    01-May-2019     4  
    NaT             5  

As a result, time subscripting works a little differently than indexing by row number:

>> tt('5-May-2019',:)
ans =
  2×1 timetable
       Time        Var1
    ___________    ____
    05-May-2019     1  
    05-May-2019     2  
>> tt('6-May-2019',:)
ans =
  0×1 empty timetable

The idea here is that timetables give you a way to read in your very messy data (using readtimetable, since 18b) and clean up the data all inside of MATLAB.

Row names in a table do have to be unique, though.