Thank you for sharing.
I realized a simple alternate implementation of what I was trying for
yesterday[1]
First get the indices of b in a
(0{"1 b) i. (0{"1 a)
3 0 1 3
My struggle yesterday is what to do if the value wasn't found ( #
b)... A simple approach is to just append a blank element to the end
of b
((0{"1 b) i. (0{"1 a)) { ((0{"1 b),<'')
┌┬─┬─┬┐
││1│2││
└┴─┴─┴┘
The timing ends up being about 2x faster on large arrays
a=:(<"0 i. 4),.(<'x')
b=:_2]\(1;'a';2;'b';10;'c')
c=:_3]\0;'x';'';1;'x';'a';2;'x';'b';3;'x';''
leftJoin =: 13 : 'x,.}."1 (((0{"1 y) i. (0{"1 x)) { (y,<''''))'
c-: (a leftJoin b)
1
NB. Ric Sherlock's implementation from yesterday [1]
joinLeft=: [ , ] #~ e.~&:({."1)
summary=: (~.@:({."1) ,. {."1 ]/. {:"1)
c-: (a summary@joinLeft b)
1
a=:(<"0 i. 1e6),.(<'x')
b=:_2]\(999997;1;999999;2)
timespacex 'c1=:a summary@joinLeft b'
1.28482 1.16477e8
timespacex 'c2=:a leftJoin b'
0.627465 6.65212e7
c1 -: c2
1
[1] - http://jsoftware.com/pipermail/chat/2014-August/006196.html
On Wed, Aug 13, 2014 at 6:55 AM, Ric Sherlock <[email protected]> wrote:
> A recent thread prompted me to finally put together a utility to do
> something that I've been doing on an ad hoc basis for far too long.
> I'm sure that others have tools that do something similar and would love to
> see other approaches.
>
> intersect=: [ -. -.
> union=: ,
> joinLeft=: [ , [ #~ e.
>
> joinRight=: joinLeft~
>
> joinInner=: union ([ #~ e.) intersect
>
> joinOuter=: union
>
>
> NB.*merge c Merge two tables on specified index fields
>
> NB. eg: TableA (<'Name') merge joinOuter TableB
>
> NB. form: x m merge v y
>
> NB. x y are: Tables with field labels in first row
>
> NB. m is: list of boxed labels of columns to use as index
>
> NB. v is: verb strategy for joining table indicies
>
> merge=: conjunction define
>
> :
>
> key=. boxopen m
>
> mhdr=. key , key -.~ ~. x ,&{. y NB. header in merged
>
> mkey=. ~. x v&:((key i.~ {.) {"1 }.) y NB. key cols in merged
>
> l_cidx=. x ([ i. key -.~ [ -. -.~)&{. y NB. cols not in right or key
>
> r_cidx=. y (] i. -.~)&{. x NB. cols only in right
>
> left_ridx=. mkey i.~ (}. {"1~ key i.~ {.) x
>
> r_ridx=. mkey i.~ (}. {"1~ key i.~ {.) y
>
> mdat=. ((<l_ridx;l_cidx) { a: ,~ }.x) ,. (<r_ridx;r_cidx) { a: ,~ }.y
>
> mhdr , mkey ,. mdat
>
> )
>
>
> require 'tables/csv'
>
>
> TableA=: fixcsv 0 :0
>
> Id,Name,Age,Sex
>
> 1,Alex,40,M
>
> 2,Jim,12,M
>
> 3,Jerry,19,F
>
> 4,Brian,42,M
>
> 5,Frieda,9,F
>
> )
>
>
> TableB=: fixcsv 0 :0
>
> Id,Name,Job,Status
>
> 1,Alex,Waiter,Separated
>
> 3,Jerry,Unemployed,Married
>
> 5,Frieda,student,Single
>
> 6,Jan,CEO,Married
>
> )
>
>
> Note 'example usage'
>
> TableA 'Id' merge joinLeft TableB
>
> TableA ('Id';'Name') merge joinLeft TableB
>
> TableA ('Id';'Name') merge joinRight TableB
>
> TableA 'Id' merge joinOuter TableB
>
> )
> ----------------------------------------------------------------------
> For information about J forums see http://www.jsoftware.com/forums.htm
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
