Mathematica 9 is now available
Services & Resources / Wolfram Forums / MathGroup Archive
-----

MathGroup Archive 2007

[Date Index] [Thread Index] [Author Index]

Search the Archive

Re: Database like "left join" or SAS like Merge function

  • To: mathgroup at smc.vnet.net
  • Subject: [mg83599] Re: Database like "left join" or SAS like Merge function
  • From: "Steve Luttrell" <steve at _removemefirst_luttrell.org.uk>
  • Date: Sat, 24 Nov 2007 04:11:58 -0500 (EST)
  • References: <fi6abc$oh3$1@smc.vnet.net>

Here is an example that does what you want:

Define a matrix where the first two columns contain keys and the third 
column contains data.
In[1]:= mat1=Table[{RandomInteger[3],RandomInteger[2],RandomReal[]},{10}]
Out[1]= 
{{2,0,0.719448},{2,0,0.155021},{3,0,0.138765},{1,2,0.714609},{0,0,0.244554},{2,0,0.582359},{1,2,0.354337},{0,2,0.498536},{0,2,0.159724},{1,2,0.624066}}

Similarly, define a second matrix.
In[2]:= mat2=Table[{RandomInteger[3],RandomInteger[2],RandomReal[]},{20}]
Out[2]= 
{{1,0,0.756742},{3,2,0.112279},{2,1,0.091024},{2,0,0.177684},{2,1,0.77082},{3,1,0.25392},{0,0,0.508483},{2,2,0.201832},{1,2,0.738323},{2,2,0.139091},{1,2,0.918997},{3,1,0.801789},{1,1,0.0767979},{1,2,0.600228},{3,0,0.129787},{1,1,0.699115},{2,1,0.897158},{3,2,0.364663},{2,0,0.487728},{2,1,0.901181}}

Join the matrices, then sort the result by the key columns, then split the 
sorted result into subsets according to their key.
In[3]:= Split[Sort[Join[mat1,mat2]],#1[[2]]==#2[[2]]&]
Out[3]= 
{{{0,0,0.244554},{0,0,0.508483}},{{0,2,0.159724},{0,2,0.498536}},{{1,0,0.756742}},{{1,1,0.0767979},{1,1,0.699115}},{{1,2,0.354337},{1,2,0.600228},{1,2,0.624066},{1,2,0.714609},{1,2,0.738323},{1,2,0.918997}},{{2,0,0.155021},{2,0,0.177684},{2,0,0.487728},{2,0,0.582359},{2,0,0.719448}},{{2,1,0.091024},{2,1,0.77082},{2,1,0.897158},{2,1,0.901181}},{{2,2,0.139091},{2,2,0.201832}},{{3,0,0.129787},{3,0,0.138765}},{{3,1,0.25392},{3,1,0.801789}},{{3,2,0.112279},{3,2,0.364663}}}

This is a very basic example, but it can readily be generalised.

Steve Luttrell
West Malvern, UK

"Peter" <pjcrosbie at gmail.com> wrote in message 
news:fi6abc$oh3$1 at smc.vnet.net...
> Is there a builtin function that can "join" (merge) two two-
> dimensional data lists based on a single or multiple key column?
>
> This seems like such a basic requirement that I feel like I must be
> missing something obvious.
>
> Thanks, Peter
> 



  • Prev by Date: Button[label, action] - InputString[]?
  • Next by Date: Re: Re: Fast way of checking for perfect squares?
  • Previous by thread: Re: Database like "left join" or SAS like Merge function
  • Next by thread: Re: Database like "left join" or SAS like Merge function