Sorting Table Data by Groups of Rows

6 次查看（过去 30 天）
Connor
Connor 2019-8-14
编辑： dpb 2019-8-23
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}

请先登录，再回答此问题。

回答（2 个）

dpb
dpb 2019-8-15
编辑：dpb 2019-8-23
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...
  2 个评论
Connor
Connor 2019-8-21
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
dpb 2019-8-23
Ooops...a typo. The RHS in the assignment is t(1,i), the first element of each column, not t(1,:).
Fixed up Answer...

请先登录，再进行评论。

Andrei Bobrov
Andrei Bobrov 2019-8-21
Let T - your table with size (176120x5) and T(:,1) - the timestamp column.
T{:,1} = fillmissing(T{:,1},'previous');
T_out = sortrows(T,1);
  2 个评论
Connor
Connor 2019-8-21
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
Andrei Bobrov 2019-8-21
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);

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Preprocessing Data 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by