Sorting table row variables with number and letters

24 views (last 30 days)
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,

Accepted Answer

Adam Danz
Adam Danz on 24 Jun 2022
This option also appears in the OP's 2017b documentation.
  9 Comments
RD
RD on 28 Jun 2022
Edited: RD on 28 Jun 2022
% Assuming numeric part is the only section to change in abc###xyz
nstr = regexp(TableB.Properties.RowNames, '\d+', 'match','once');
n = str2double(nstr);
[~, sortIdx] = sort(n);
% Sort table
TableC = TableB(sortIdx,:)
@Adam Danz Thanks this worked. Read little bit more on regexp to understand how the function works.
Thanks again everyone else! I will mark this as solved.
Stephen23
Stephen23 on 28 Jun 2022
Simpler:
TableC = natsortrows(TableB,[],'RowNames')

Sign in to comment.

More Answers (2)

Voss
Voss on 24 Jun 2022
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 Comments
Voss
Voss on 25 Jun 2022
My answer works the same whether you have a table with 5 rows or 500 rows.
You do not need to construct the table A like I did, since the premise of the question was that you already have the table. I only constructed A explicitly in my answer to show how it works.
Stephen23
Stephen23 on 25 Jun 2022
"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?

Sign in to comment.


Stephen23
Stephen23 on 25 Jun 2022
Edited: Stephen23 on 25 Jun 2022
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

Categories

Find more on Data Type Conversion in Help Center and File Exchange

Products


Release

R2017b

Community Treasure Hunt

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

Start Hunting!