Sorting Table Data by Groups of Rows

Hello,

I have a very large data table consisting of timestamps and acceleration data. The data is grouped into packets where the timestamps are given once for every 10 acceleration values (timestamp followed by 9 NaN values and then another timestamp), and are not listed in any sort of order. I'm looking for a way to sort the data by timestamp, but also include the 9 rows with NaN following the timestamp, to get these groups of data sorted by timestamp in ascending order. Any help with how I could do this would be greatly appreciated.

{64,3889983,-304,-68,8172;64,NaN,-308,-64,8172;64,NaN,-284,-80,8168;64,NaN,-320,-24,8164;64,NaN,-292,-80,8176;64,NaN,-280,-84,8164;64,NaN,-276,-44,8184;64,NaN,-296,-80,8144;64,NaN,-296,-40,8160;64,NaN,-296,-76,8192;220,1840916,3052,2068,-7332;220,NaN,2984,1984,-7356;220,NaN,3044,2080,-7612;220,NaN,3116,1992,-7540;220,NaN,3060,1984,-7640;220,NaN,3120,1940,-7556;220,NaN,3004,1944,-7476;220,NaN,2892,1844,-7484;220,NaN,2892,1916,-7344;220,NaN,2844,1964,-7380}

[Andrei Bobrov]

Please attach small part of your data here as mat-file.

Answer 1

[dpb]

If the number of missing values is always 9, then extract the time column to a vector and

t=datatable(:,1);      % retrieve the time data via whatever syntax need for how is stored
t=reshape(t,10,[]);    % rearrange by set of 10 by column
for i=1:size(t,2)      % over all columns
  t(:,i)=t(1,i);       % set to first value in column
end
datatable(:,1)=t(:);   % replace values in original table via needed syntax 

Now you can sort on the time column...

[Connor]

The table I'm working with is 176120x5, and I need the data from the other 4 columns to be sorted accordingly with the timestamp column. When I use this code to sort by timestamp, it creates at 10x17612 double and produces the following error message:

Unable to perform assignment because the size of the left side is 10-by-1 and the size of the right side is 1-by-17612.

[dpb]

Ooops...a typo. The RHS in the assignment is t(1,i), the first element of each column, not t(1,:).

Fixed up Answer...

Answer 2

[Andrei Bobrov]

Let T - your table with size (176120x5) and T(:,1) - the timestamp column.

T{:,1} = fillmissing(T{:,1},'previous');
T_out = sortrows(T,1);

[Connor]

Since I am going to be making plots with the data, I need to do a linear interpolation of the timestamps. When I do that with the fillmissing function, it interpolates linearly by the 2 nearest timestamps. This is why I first have to sort the timestamps into ascending order, with the 9 NaN values following, and the other columns of data included, and then interpolate the NaN values using fillmissing 'linear'

[Andrei Bobrov]

i don't understand what you want, but maybe it:

A = [64,3889983,-304,-68,8172;64,NaN,-308,-64,8172;64,NaN,-284,-80,8168;64,NaN,-320,-24,8164;64,NaN,-292,-80,8176;64,NaN,-280,-84,8164;64,NaN,-276,-44,8184;64,NaN,-296,-80,8144;64,NaN,-296,-40,8160;64,NaN,-296,-76,8192;220,1840916,3052,2068,-7332;220,NaN,2984,1984,-7356;220,NaN,3044,2080,-7612;220,NaN,3116,1992,-7540;220,NaN,3060,1984,-7640;220,NaN,3120,1940,-7556;220,NaN,3004,1944,-7476;220,NaN,2892,1844,-7484;220,NaN,2892,1916,-7344;220,NaN,2844,1964,-7380];
T = array2table(A);
T.A6 = fillmissing(T.A2,'previous');
T = sortrows(T,'A6');
T_out = T(:,1:end-1);