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}
  댓글 수: 1
Andrei Bobrov
Andrei Bobrov 2019년 8월 21일
Please attach small part of your data here as mat-file.

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

답변 (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 CenterFile Exchange에서 Preprocessing Data에 대해 자세히 알아보기

Community Treasure Hunt

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

Start Hunting!

Translated by