Sorting table row variables with number and letters

조회 수: 4(최근 30일)
RD
RD 2022년 6월 24일
댓글: Stephen23 2022년 6월 28일
Hello,
I am trying to sort a table with values which looks something like this as an example below, my actual data has hundreds of rows of data.
And I want to make it look like this:
A = struct2table(data); %Data imported as struct and converted to table (50 x 6)
B = A(2,2:end); %Deletion of extra rows and keeping columns with variable names (1 x 5)
arrayB = table2array(B);
TableB = array2table(arrayB.'); % Transposing so variablenames are in rows (5 x 1)
TableB.Properties.RowNames = B.Properties.VariableNames; %Assigning Variable names to rows
TableC = TableB(:,sort(B.Properties.VariableNames));
After running the code I get the error:
Unrecognized variable name 'abc_0_xyz'.
I'm stuck at this error since the variable is clearly in the table already so something is probably not right.
I'm thinking I need to remove either "_xyz" or "abc_" for the sort to work or use delimiter both of which I don't know how or what function to use in my case. It's my first post so apologies in advance if I missed anything. Any help is greatly appreciated
Thanks,

채택된 답변

Adam Danz
Adam Danz 2022년 6월 24일
This option also appears in the OP's 2017b documentation.
  댓글 수: 9
Stephen23
Stephen23 2022년 6월 28일
Simpler:
TableC = natsortrows(TableB,[],'RowNames')

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

추가 답변(2개)

Voss
Voss 2022년 6월 24일
It seems like you can rearrange the rows by using the second output from sort.
If the abc_0_xyz, etc., are the RowNames of the original table:
A = table([10;50;30;90;150],'RowNames',{'abc_0_xyz';'abc_4_xyz';'abc_3_xyz';'abc_1_xyz';'abc_2_xyz'})
A = 5×1 table
Var1 ____ abc_0_xyz 10 abc_4_xyz 50 abc_3_xyz 30 abc_1_xyz 90 abc_2_xyz 150
[~,idx] = sort(A.Properties.RowNames);
newA = A(idx,:)
newA = 5×1 table
Var1 ____ abc_0_xyz 10 abc_1_xyz 90 abc_2_xyz 150 abc_3_xyz 30 abc_4_xyz 50
Or if the abc_0_xyz, etc., are a variable in the original table:
A = table({'abc_0_xyz';'abc_4_xyz';'abc_3_xyz';'abc_1_xyz';'abc_2_xyz'},[10;50;30;90;150])
A = 5×2 table
Var1 Var2 _____________ ____ {'abc_0_xyz'} 10 {'abc_4_xyz'} 50 {'abc_3_xyz'} 30 {'abc_1_xyz'} 90 {'abc_2_xyz'} 150
[~,idx] = sort(A.Var1);
newA = A(idx,:)
newA = 5×2 table
Var1 Var2 _____________ ____ {'abc_0_xyz'} 10 {'abc_1_xyz'} 90 {'abc_2_xyz'} 150 {'abc_3_xyz'} 30 {'abc_4_xyz'} 50
  댓글 수: 3
Stephen23
Stephen23 2022년 6월 25일
"I missed a big detail.. the number of RowNames/Variables are in several hundereds.... is there a way to automatically define them with respective [10;50;30......n] values?"
In your question you wrote that you created that table from some imported data:
"%Data imported as struct and converted to table (50 x 6)"
What is stopping you from using that? Why do you suddenly want to write everything out by hand?

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


Stephen23
Stephen23 2022년 6월 25일
편집: Stephen23 2022년 6월 25일
Your question and examples are confusing: sometimes you show that you want to sort RowNames, and sometimes that you want to sort by first variable/column. However both of them are very easy to achieve using my FEX submission NATSORTROWS():
Sort by RowNames:
A = table([10;50;30;90;150], 'RowNames',{'abc_0_xyz';'abc_20_xyz';'abc_10_xyz';'abc_2_xyz';'abc_1_xyz'})
A = 5×1 table
Var1 ____ abc_0_xyz 10 abc_20_xyz 50 abc_10_xyz 30 abc_2_xyz 90 abc_1_xyz 150
B = natsortrows(A,[],'RowNames')
B = 5×1 table
Var1 ____ abc_0_xyz 10 abc_1_xyz 150 abc_2_xyz 90 abc_10_xyz 30 abc_20_xyz 50
Sort by any variable:
A = table({'abc_0_xyz';'abc_20_xyz';'abc_10_xyz';'abc_2_xyz';'abc_1_xyz'},[10;50;30;90;150])
A = 5×2 table
Var1 Var2 ______________ ____ {'abc_0_xyz' } 10 {'abc_20_xyz'} 50 {'abc_10_xyz'} 30 {'abc_2_xyz' } 90 {'abc_1_xyz' } 150
B = natsortrows(A,[],'Var1') % using variable name
B = 5×2 table
Var1 Var2 ______________ ____ {'abc_0_xyz' } 10 {'abc_1_xyz' } 150 {'abc_2_xyz' } 90 {'abc_10_xyz'} 30 {'abc_20_xyz'} 50
B = natsortrows(A,[],1) % using index
B = 5×2 table
Var1 Var2 ______________ ____ {'abc_0_xyz' } 10 {'abc_1_xyz' } 150 {'abc_2_xyz' } 90 {'abc_10_xyz'} 30 {'abc_20_xyz'} 50

제품


릴리스

R2017b

Community Treasure Hunt

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

Start Hunting!

Translated by