An alternative to your leftJoin
leftJoin=: [ ,. i.~&:({."1) { a: ,~ }."1@]On Thu, Aug 14, 2014 at 1:57 AM, Joe Bogner <[email protected]> wrote: > 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 ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm
