# Lookup construction using data matrix and Reap/Sow versus AppendTos

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.

• Don't have time to pull the code apart but some general comments: Always try to work with entire lists rather than looping through them as you are doing here. AppendTo is very slow. For one offs it might be acceptable but you are doing this operation 434 times. Faster options are functions like Join and Flatten. For the latter it is often faster to simply create flattened lists and partition at the end. Jan 5 '15 at 1:16
• @MikeHoneychurch Thanks for the comment! I replaced the ApppendTos with (ffr~Join~ABg[[m, 5]])// Sort. No speed difference was noticed. You must know there are in the data at the most three different parts for an ffr! Jan 7 '15 at 20:58

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}
];

• First of all my compliments for your excellent playing chess blindfold. At the moment I do not have a site with FTP possibilities so I cannot provide the xlsx-file. Your proposal is not completely working as it should. 1) You overlooked a minor detail in my procedural approach. I had to remove the Transpose@ just before the Partition-command. 2) The line with results is not working. The three parts of fff should be looked up in column 1 and from the three found unique rows the items in colum 5 should be Joined together. I am still trying to find a repair. Perhaps you see it at once. Jan 7 '15 at 12:14
• Your suggestion for Reap/Sow works fine! I only had to add a Flatten to the second level around Sow. Thanks. Jan 7 '15 at 21:25
• @Rombert You're very welcome, the mistake with result was simple: I had to replace First with Last and data with table. I've also changed two other lines to something that may be more efficient. Jan 7 '15 at 23:42
• I edited two things in order to make var results working: 1) table=MapAt[ToString,data,{All,{2,3}}]; (n.b. 2nd arg of MapAt put back to data) 2) table=MapAt[ToExpression,table,{All,{1,4,5}}]; First column should become ToExpression. Hope you will/can approve (;) Jan 8 '15 at 14:40
• @Rombert Sorry, I understand now that my update yesterday was not correct. I've now updated again with a version of what I had to begin with. The original version also had a problem, but I hope I fixed that with my update. Because the MapAt thing doesn't correspond to exactly what the original code does. Jan 8 '15 at 14:48