Sorting table row variables with number and letters

Question

RD 2022-6-24

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1747750-sorting-table-row-variables-with-number-and-letters

评论： BL 2024-5-13

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,

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Adam Danz 2022-6-24

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1747750-sorting-table-row-variables-with-number-and-letters#answer_993010

Use TableC = sortrows(TableB,'RowNames')

This option also appears in the OP's 2017b documentation.

9 个评论
显示 7更早的评论隐藏 7更早的评论

Stephen23 2022-6-25

编辑：Stephen23 2022-6-25

在 MATLAB Online 中打开

Note that:

your example creates a table with two variables and no row names,
your explanation shows a table with one variable and rows names.

So it is unclear what your actual data table is like. Descriptions of data by OPs are always much less accurate that being provided with actual sample data, which you can do by clicking the paperclip button.

"Also used natsortrows(TableB, [ ] , 'RowNames'); No change in output it still remains the same."

Lets check that right now (using a table that matches your description, but not your example):

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 

So, the function NATSORTROWS() correctly sorted the RowNames alphanumerically, just as it is documented and tested to do, using an example table that matches your description. In contrast you tried some code (which you did not show) using a table (which you did not show) and got a result (which you did not show), so we can only conclude that the problem lies with the user, not with the function. Not showing what you did is the perfect way to make it really difficult for people to help you.

Adam Danz 2022-6-25

在 MATLAB Online 中打开

@RD, this is how I imagine your data to look

data.Name = ["A";"B";"C"];
data.abc0xyz = randi(9,3,1);
data.abc10xyz = randi(9,3,1);
data.abc20xyz = randi(9,3,1);
data.abc1xyz = randi(9,3,1);
data.abc2xyz = randi(9,3,1);
data.abc3xyz = randi(9,3,1);
data.abc22xyz = randi(9,3,1);
data.abc12xyz = randi(9,3,1);
disp(data)
        Name: [3×1 string]
     abc0xyz: [3×1 double]
    abc10xyz: [3×1 double]
    abc20xyz: [3×1 double]
     abc1xyz: [3×1 double]
     abc2xyz: [3×1 double]
     abc3xyz: [3×1 double]
    abc22xyz: [3×1 double]
    abc12xyz: [3×1 double]
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; 
disp(TableB)
                Var1
                ____

    abc0xyz      4  
    abc10xyz     2  
    abc20xyz     9  
    abc1xyz      2  
    abc2xyz      2  
    abc3xyz      1  
    abc22xyz     9  
    abc12xyz     1  

Extract numeric values and sort

To follow through on the idea of using regular expressions to extract the numeric part of the row name:

% 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,:)
TableC = 8×1 table
                Var1
                ____

    abc0xyz      4  
    abc1xyz      2  
    abc2xyz      2  
    abc3xyz      1  
    abc10xyz     2  
    abc12xyz     1  
    abc20xyz     9  
    abc22xyz     9  

RD 2022-6-28

编辑：RD 2022-6-28

% 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 2022-6-28

在 MATLAB Online 中打开

Simpler:

TableC = natsortrows(TableB,[],'RowNames')

请先登录，再进行评论。

Answer 2

Stephen23 2022-6-25

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1747750-sorting-table-row-variables-with-number-and-letters#answer_993230

编辑：Stephen23 2022-6-25

在 MATLAB Online 中打开

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 

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

BL 2024-5-13

Thanks Stephen, this was exactly what I was looking for! :)

请先登录，再进行评论。

Answer 3

Voss 2022-6-24

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1747750-sorting-table-row-variables-with-number-and-letters#answer_993005

在 MATLAB Online 中打开

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 个评论
显示 1更早的评论隐藏 1更早的评论

Voss 2022-6-25

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 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?

请先登录，再进行评论。

Sorting table row variables with number and letters

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

9 个评论
显示 7更早的评论隐藏 7更早的评论

更多回答（2 个）

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

3 个评论
显示 1更早的评论隐藏 1更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

Sorting table row variables with number and letters

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

9 个评论 显示 7更早的评论隐藏 7更早的评论

更多回答（2 个）

1 个评论 显示 -1更早的评论隐藏 -1更早的评论

3 个评论 显示 1更早的评论隐藏 1更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

9 个评论
显示 7更早的评论隐藏 7更早的评论

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

3 个评论
显示 1更早的评论隐藏 1更早的评论