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
