A common thing to want to do with data is to combine it (at least I thought this was a common thing). In SQL there is the idea of table joins "select id,v1,v2 from A,B where A.id=B.id" kind of thing. I'm trying to do a join like this using Mathematica, i.e. retrieve table A from the Internet, retrieve table B from the Internet, combine, do stuff with the resulting joined columns.
My First effort was this:
(* Simulate tables *)
a = Table[{x, RandomReal[]}, {x, 1, 1000}];
b = Table[{x, RandomReal[]}, {x, 1, 1000}];
(* Use this simple match function for now *)
RMatch[x_] := x[[1]] == x[[3]];
Length[Part[
Select[Flatten[Outer[Function[{x, y}, Join[x, y]], a, b, 1], 1],
RMatch], All, {1, 2, 4}]] // Timing
{1.906250, 1000}
This works, but is way too slow. Imagine if it was 100,000 rows each; the program may never finish. Also this will take up a lot of memory; creating a temporary list of rows that is NxM in size.
So I tried an iterative approach instead:
(* Simulate tables *)
a = Table[{x, RandomReal[]}, {x, 1, 1000}];
b = Table[{x, RandomReal[]}, {x, 1, 1000}];
(* Use this simple match function for now *)
RMatch[x_, y_] := x[[1]] == y[[1]];
RJoin[a_, b_, match_] := Block[
{lena = Length[a], lenb = Length[b], arow, brow, i, j},
Flatten[Reap[
For[i = 1, i <= lena, ++i,
arow = a[[i]];
For[j = 1, j <= lenb, ++j,
brow = b[[j]];
If[match[arow, brow], Sow[Join[arow, brow]]
]
]
]
][[2]], 1
]
]
Length[Select[RJoin[a, b, RMatch],
Function[{x}, x[[1]] == x[[3]]]]] // Timing
{2.562500, 1000}
This will use much less memory, but is even slower.
Is this something that just isn't appropriate for Mathematica i.e. do this using a different tool, output the table and work with that instead? That would be a shame, but it seems that it may be necessary.
Update:
Thanks to the great answers below I decided to give up on the general case and focus on unique ID matching, which is the situation that arises the most. Following Verbeia and Michael E2's examples I came up with a version that is fast and readable.
(* Simulate tables *)
a = Table[{x, RandomReal[]}, {x, 1, 1000000, 2}];
b = Table[{x, RandomReal[]}, {x, 1, 1000000, 3}];
RJoin[a_, b_] := Block[{f},
Do[
f[x[[1]]] = x[[2]],
{x, a}
];
Cases[b /. {x_, y_} -> {x, y, f[x]}, {_, _, _Real}]
]
(* Use and time *)
Length[RJoin[a, b]] // Timing
{1.750000, 166667}
That's 1.75 seconds to join two tables with almost a million rows total. This is mildly faster than Michael E's version which took 4.2 seconds on my computer.
When I need a more general case I plan to use Java to process the data, but this should handle most of my cases.
Update 2
I managed to simplify and speed up the arbitrary matching case a little bit
(*Simulate tables*)
a = Table[{x, RandomReal[]}, {x, 1, 1000, 1}];
b = Table[{x, RandomReal[]}, {x, 1, 1000, 1}];
(*Use this simple match function for now*)
RMatch[x_, y_] := x[[1]] == y[[1]];
RJoin[a_, b_, match_] :=
Flatten[
Reap[Do[
If[match[x, y],
Sow[{x[[1]], x[[2]], y[[2]]}];
],
{x, a}, {y, b}
];
][[2]]
, 1]
Length[RJoin[a, b, RMatch]] // Timing
{1.359375, 1000}
Outerone. $\endgroup$