# Extracting “records” from a matrix

From a rank 3 matrix like

M =
{
{{a, v, x01}, {a, w, x02}, {a, x, x03}, {a, y, x04}, {a, z, x05}},
{{b, v, x06}, {b, w, x07}, {b, x, x08}, {b, y, x09}, {b, z, x10}},
{{c, v, x11}, {c, w, x12}, {c, x, x13}, {c, y, x14}, {c, z, x15}},
{{d, v, x16}, {d, w, x17}, {d, x, x18}, {d, y, x19}, {d, z, x20}}
};


I want to extract information based , for instance, on the following keys :

Keys1 = {y, w};
Keys2 = {d, a, b};


Using

Map[Cases[Flatten[Map[Cases[Flatten[P, 1], {_, #, _}] &, Keys1], 1], {#, _, _}]&,Keys2]


gives the desired unsorted result:

{{{d, y, x19}, {d, w, x17}}, {{a, y, x04}, {a, w, x02}}, {{b, y,  x09}, {b, w, x07}}}


In reality, my matrices have up to 30000 rows, but always 3 columns: Key1 (String), Key2 (String), x01 ... x20 (Lists of real values)

Because of many repetitive lookups, timing here is important, so my question is:

Are there faster ways?

-
You may be interested in Dispatch and friends. I think browsing this site for that function will result with many topics related to your question. There are also answers based on different constructions. – Kuba May 11 '14 at 15:37
Related: (9702), (22599), (29334) – Mr.Wizard May 11 '14 at 15:51
Your method takes me about 1 seconds with a 30000 matrix. Is that too long? – Öskå May 11 '14 at 15:59

Assuming the structure of your example maintains (that is, rows have same first element "key", columns have same second element "key"), this is hundreds of times faster than your example method on a 30000 row X 50 column dataset, selecting a 20 Keys2 X 10 Keys1 sample, and also considerably faster than the current answers.

With[{rr = Thread[#[[All, 1, 1]] -> Range@Length@#],
rc = Thread[#[[1, All, 2]] -> Range@Length@#[[1]]]},
Partition[Extract[#, Tuples[{#3 /. rr, #2 /. rc}]], Length@#2]] &[M, Keys1, Keys2]

-
Thank you !!! Thats's exactly what I needed & amazingly fast. Another question: If I start with a rank 2 matrix like {{a, v, x0}, {a, w, x1}, {b, v, x2}, {b, w, x3}} (no partition between a and b keys), would you modify your code or simply partition M before running it? – eldo May 12 '14 at 9:25
@eldo: You could pre-partition, but that's just added and unneeded work - better in that case to tweak the technique, or use a simpler one (e.g, nested cases would probably be quite sufficiently fast for the rank-2 case). Happy to ponder it though if you have a need for that for huge arrays, but answer will be later - getting to be near sun-up here ;-) – ciao May 12 '14 at 9:36

This is almost certainly a duplicate, but in an effort to be helpful you may consider a nested list of rules:

M2 = #[[1, 1]] -> (Rule @@@ #[[All, 2 ;;]]) & /@ M

{a -> {v -> x01, w -> x02, x -> x03, y -> x04, z -> x05},
b -> {v -> x06, w -> x07, x -> x08, y -> x09, z -> x10},
c -> {v -> x11, w -> x12, x -> x13, y -> x14, z -> x15},
d -> {v -> x16, w -> x17, x -> x18, y -> x19, z -> x20}}


Now e.g.:

Keys1 /. (Keys2 /. M2)

{{x19, x17}, {x04, x02}, {x09, x07}}


A faster methods would be to use hash tables which can be implemented as DownValues definitions, i.e. table[key] = value.

Or using Dispatch as Kuba's comment above reminds me:

M3 = Replace[M2, x : {__Rule} :> Dispatch[x], {0, -3}];

Map[Keys1 /. # &, Keys2 /. M3]

{{x19, x17}, {x04, x02}, {x09, x07}}


(Map is needed because /. will not thread across a list of Dispatch tables the way it will explicit lists of Rules.)

-