MATLAB Answers

Search a Table With Numerical Entries

2 views (last 30 days)
I have a table which has only numerical (actually integer) entries. Each row and column are indexed by a string. As an example,
I1L I1R I2L I2R I3 I4
----- ----- ----- ----- ----- -----
I1L 0 0 2 0 1 0
I1R 0 0 0 1 1 0
I2L 10 0 0 3 0 0
I want to be able to identify the non-zero entries and then create a 3-column table which has the row name, column name, and the integer value. For example,
I1L I2L 2
I1L I3 1
I1R I2R 1
I1R I3 1
I2L I1L 10
I2L I2R 3
Of course, the table that I am working with is quite large. I have been trying various combinations of commands, all in vain I'm afraid. I would very much appreciate any assistance. Thank you.

  1 Comment

Kamal Premaratne
Kamal Premaratne on 29 May 2020
Just a follow-up: My objective is to create a digraph (directed graph) from the data in the original table.

Sign in to comment.

Accepted Answer

Steven Lord
Steven Lord on 29 May 2020
If you want to make a digraph from your table, you don't need to go to the three-column table first. Let's make a sample table in the same form as what you have. Let's start off with 20 non-zero values at random locations in a 10-by-10 array.
ind = randi(100, 1, 20);
weight = randi(10, 1, 20);
A = zeros(10, 10);
A(ind) = weight;
Turn this into a table array with variable names A1, A2, ... [Yes, I know defining stand-alone variables with numbered names is discouraged. Letting MATLAB automatically make variables inside a table array with numbered names like this doesn't clutter the namespace, so I'm okay with that.]
T = array2table(A)
Now build your digraph. I assumed you want the node names in the digraph to be the variable names in the table.
D = digraph(T.Variables, T.Properties.VariableNames)
If you only wanted some of the rows and variables to be present:
selection = 1:2:width(T);
D2 = digraph(T{selection, selection}, T.Properties.VariableNames(selection))


Show 2 older comments
Steven Lord
Steven Lord on 29 May 2020
If your table isn't that big you could use the setdiff function to determine which names are in the VariableNames of your table but not the RowNames. Then you could add rows to the table corresponding to the missing RowNames and use indexing (like I did with selection) to extract the data from the rows and variables in a consistent order.
If your table is large, call find on T.Variables to obtain the row and column indices and the values. Use the row and column indices to index into T.Properties.RowNames and T.Properties.VariableNames. Now you have source, target, and weight lists that you can pass into digraph.
T.Properties.RowNames = T.Properties.VariableNames;
T2 = T;
T2 = T2([1 5 8 2 6 10], :)
[row, col, value] = find(T2.Variables);
D2 = digraph(T2.Properties.RowNames(row), T2.Properties.VariableNames(col), value);
Kamal Premaratne
Kamal Premaratne on 29 May 2020
Yep, this should work. Let me try on the big table. Thank you so much Streven.
Kamal Premaratne
Kamal Premaratne on 30 May 2020
It works fine on the dataset. I was a bit concerned because the total number of nodes of the digraph being generated was less than the number of variables (columns) of the table. But then I realized that this might be due to the fact that there are isolated nodes (nodes that are not connected any other node) which we do not capture because we only pick edges with non-zero weights.
Again, Steve, thank you so much.

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!