Search a Table With Numerical Entries

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.

[Kamal Premaratne]

Just a follow-up: My objective is to create a digraph (directed graph) from the data in the original table.

Answer 1

[Steven Lord]

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

[Kamal Premaratne]

Thank you very much Steven. But the problems with the table I have are the following (and yes, I probably should have mentioned these before, sorry about that):

-- The table is not square. So I cannot generate an adjacency matrix directly.

-- The row names and the column names are not necessary in the same order.

[Kamal Premaratne]

Just a bit of background: This data corresponds to the neuronal connections of C Elegans (from WorkWiring.org).

[Steven Lord]

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]

Yep, this should work. Let me try on the big table. Thank you so much Streven.

[Kamal Premaratne]

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.