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
>