Sorting table row variables with number and letters

25 visualizzazioni (ultimi 30 giorni)
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,

Risposta accettata

Adam Danz
Adam Danz il 24 Giu 2022
This option also appears in the OP's 2017b documentation.
  9 Commenti
RD
RD il 28 Giu 2022
Modificato: RD il 28 Giu 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 il 28 Giu 2022
Simpler:
TableC = natsortrows(TableB,[],'RowNames')

Accedi per commentare.

Più risposte (2)

Voss
Voss il 24 Giu 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 Commenti
Voss
Voss il 25 Giu 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 il 25 Giu 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?

Accedi per commentare.


Stephen23
Stephen23 il 25 Giu 2022
Modificato: Stephen23 il 25 Giu 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

Categorie

Scopri di più su Data Type Conversion in Help Center e File Exchange

Prodotti


Release

R2017b

Community Treasure Hunt

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

Start Hunting!

Translated by