list manipulation - Lookup construction using data matrix and Reap/Sow versus AppendTo`s

I am a user of MMA9 and made my own kind of Lookup, which is now a standard command in Version 10.

In my program there is a section in which by means of a StringReplace[ ]-command containing a lot of rules the necessary work is done really very fast.

I tried to do the same in the following way but this appears to be very slow (a factor of 10).

tabel[n_] := Table[{k, k, k, k, k, k}, {k, 1, n}];
tblhoogteABg = 434; ABg = tabel@tblhoogteABg;
dABg = Import["ABbis2175B(434)klaar.xlsx"];

eABg = Partition[Flatten[dABg], 5] // Transpose;
Do[ABg[[i, j]] = ToString[ eABg[[j]][[i]]], {i, 1, tblhoogteABg}, {j, 1, 3}];
Do[ABg[[i, j]] = ToExpression[ eABg[[j]][[i]]], {i, 1, tblhoogteABg}, {j, 4, 5}]

The items of colums 1,2 and 3 are String, those of colums 4 and 5 are Expressions (lists).

fff = ABg[[433, 2]];
rl = fff // ToExpression; 
ffr = {};
ll = Length[rl];   
Do[ 

   Do[rlp = rl[[el]]; (* rlp is a part of rl *)
     If[rlp == (ABg[[m, 1]] // ToExpression), 
        ffr = (ffr~AppendTo~ABg[[m, 5]]) // ToExpression // Flatten], 
 {el,1,ll}], {m, 1, tblhoogteABg}]; 
Print[fff, "->", ffr]

fff is a string in column 2 of ABg[[ , ]] and this is changed into a list rl = fff//ToExpression and transformed into ffr.

ffr is build up via a series of ffr~AppendTo~ABg[[m, 5]] commands, as many as there are parts rlp in rl, a list with length ll.

The row number m is found via the If-statement (the actual lookup part).

The result is:

{25B,75A,725B}->{151,12301,1451,5315701,17431901,
 199005929846082820906192074956026987594151} 

I learned in some Q&A discussion concerning Reap and Sow that the combination of them is more efficient than the AppendTo-commands.

However, up to now I did not succeed in using Reap and Sow in the above fragment. I hope that someone can give me some help and that this method appears to be more rapid.

Answer

Adding elements to a growing list is slow in general. We get much better performance out of Mathematica if we treat data in chunks, and use high level functions as much as we can. This usually translates to a functional style of programming, as opposed to procedural programming. Do, While and For as therefore best to try to avoid altogether, in favor of Nest, Map and Apply.

As I understand it your code can be written like this (cannot tell whether it has bugs or not without sample data):

createTable[n_] := Table[{k, k, k, k, k, k}, {k, 1, n}];


numberOfRows = 434;
table = createTable[numberOfRows];

data = Import["ABbis2175B(434)klaar.xlsx"];
data = Partition[Flatten[data], 5];

table = MapAt[ToString, data, {All, {2, 3}}];
table = MapAt[ToExpression, data, {All, {1, 4, 5}}];

fff = table[[433, 2]];


fffElements = ToExpression@fff;
fffLength = Length@fffElements;
results = Flatten[Last /@ Select[table[[All, {1, 5}]], MemberQ[fffElements, First@#] &],2];

Print[fff, "->", results]

Basically, as you can see, Part ([[ ]]) and Map are the workhorses now rather than Do. There are probably things I would have written differently if I had the file, but the main idea is evident in this piece of code.

I don't know if this helps you. I'm kind of asking you to adopt a new programming paradigm... but it's the way to get more performance out of Mathematica.

But I've also been thinking about how I can address your actual question, that is, how can Sow be incorporated? I think this should work:

ffr = Last@Reap@Do[
     rlp = rl[[el]];(*rlp is a part of rl*)
     If[
      rlp == (ABg[[m, 1]] // ToExpression),
      Sow[ABg[[m, 5]] // ToExpression // Flatten]
      ],
     {el, 1, ll}, {m, 1, tblhoogteABg}
     ];