Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.soft-sys.math.mathematica > #5272 > unrolled thread

Database "outer join" in Mathematica

Started byMark Coleman <markspcoleman@gmail.com>
First post2011-09-29 06:07 +0000
Last post2011-09-30 08:05 +0000
Articles 2 — 2 participants

Back to article view | Back to comp.soft-sys.math.mathematica


Contents

  Database "outer join" in Mathematica Mark Coleman <markspcoleman@gmail.com> - 2011-09-29 06:07 +0000
    Re: Database "outer join" in Mathematica Armand Tamzarian <mike.honeychurch@gmail.com> - 2011-09-30 08:05 +0000

#5272 — Database "outer join" in Mathematica

FromMark Coleman <markspcoleman@gmail.com>
Date2011-09-29 06:07 +0000
SubjectDatabase "outer join" in Mathematica
Message-ID<j611vs$m1r$1@smc.vnet.net>
Greetings,

I've struggled with building a general function in Mathematica that performs
an "outer join" similar to that performed in any standard database
system. As an example, consider the following two lists:

x1 = {{"dog", "cat", 1}, {"cow", "sheep", 2}, {"calf", "lamb",
   2}, {"pig", "horse", "x"}, {"lion", "tiger", "y"}}

x2 = {{2, "lemur", "goat"}, {3, "llama", "donkey"}, {"x", "cat",
   "mouse"}}


A standard database-style "outer join", that uses the last column of
x1 and the first column of x2 as the key field (note, these locations
are arbitrary) would return something like

x1x2OuterJoin = {{1, "dog", "cat", "", ""}, {2, "cow", "sheep",
   "lemur", "goat"}, {2, "calf", "lamb", "lemur", "goat"}, {3, "", "",
    "llama", "donkey"}, {"x", "pig", "horse", "cat", "mouse"}, {"y",
   "lion", "tiger", "", ""}}

That is, all rows of both lists occur, including those rows where
there are no match of the key field.

Ideally a very general outer join function, again as implemented in
most common database systems, would allow for keys in any location in
either of the lists, key values that are string or numeric, key values
that are not unique, joining on multiple keys, joining multiple
tables, etc. I searched through the Group archives and from time-to-
time, a question has touched on some aspect of this problem, but none
of the proposed solutions seem general enough. So I thought I post the
general case directly.

Any help or insights would be much appreciated.

Cheers,

Mark

[toc] | [next] | [standalone]


#5299

FromArmand Tamzarian <mike.honeychurch@gmail.com>
Date2011-09-30 08:05 +0000
Message-ID<j63t7u$6fm$1@smc.vnet.net>
In reply to#5272
On Sep 29, 4:07 pm, Mark Coleman <markspcole...@gmail.com> wrote:
> Greetings,
>
> I've struggled with building a general function in Mathematica that performs
> an "outer join" similar to that performed in any standard database
> system. As an example, consider the following two lists:
>
> x1 = {{"dog", "cat", 1}, {"cow", "sheep", 2}, {"calf", "lamb",
>    2}, {"pig", "horse", "x"}, {"lion", "tiger", "y"}}
>
> x2 = {{2, "lemur", "goat"}, {3, "llama", "donkey"}, {"x", "cat",
>    "mouse"}}
>
> A standard database-style "outer join", that uses the last column of
> x1 and the first column of x2 as the key field (note, these locations
> are arbitrary) would return something like
>
> x1x2OuterJoin = {{1, "dog", "cat", "", ""}, {2, "cow", "sheep",
>    "lemur", "goat"}, {2, "calf", "lamb", "lemur", "goat"}, {3, "", "",
>     "llama", "donkey"}, {"x", "pig", "horse", "cat", "mouse"}, {"y",
>    "lion", "tiger", "", ""}}
>
> That is, all rows of both lists occur, including those rows where
> there are no match of the key field.
>
> Ideally a very general outer join function, again as implemented in
> most common database systems, would allow for keys in any location in
> either of the lists, key values that are string or numeric, key values
> that are not unique, joining on multiple keys, joining multiple
> tables, etc. I searched through the Group archives and from time-to-
> time, a question has touched on some aspect of this problem, but none
> of the proposed solutions seem general enough. So I thought I post the
> general case directly.
>
> Any help or insights would be much appreciated.
>
> Cheers,
>
> Mark


Mark I assume that the data you want to work with isn't already in a
DB other wise you could do it there prior to "pulling" it into Mathematica.

Mike

[toc] | [prev] | [standalone]


Back to top | Article view | comp.soft-sys.math.mathematica


csiph-web