I came up with an alternative interface for merging tables that I like
better. It is more flexible (handles more than 2 tables) with default key,
but suffers on the performance front.
NB.*mergeTables a Merge tables on key columns using join strategy
NB. form: x u merge y
NB. x is: list of of key labels (optional, default is first field)
NB. y is: list of boxed tables to merge
NB. u is: verb strategy for joining table indicies
mergeTables=: 1 :0
key=. {.{. >@{. y NB. default key is first field
key u mergeTables y
:
key=. boxopen x
mhdr=. key , key -.~ ~. ; {.&.> y NB. header in merged
mkeys=. ~.; u&.>/ ((key i.~ {.) {"1 }.)&.> y NB. key cols in merged
cidx=. (] i.&.> [: }. [: -.~&.>/\ key ; ]) {.&.> y NB. add uniq cols
progressively from left
ridx=. (mkey i.~ }. {"1~ key i.~ {.)&.> y
mdat=. ;,.&.>/ (ridx <@;&.> cidx) ([ { a: ,~ }.@]) &.> y
mhdr , mkey ,. mdat
)
require 'tables/csv'
A=: 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
)
B=: fixcsv 0 :0
Id,Name,Job,Status
3,Jerry,Unemployed,Married
6,Jan,CEO,Married
5,Frieda,student,Single
1,Alex,Waiter,Separated
)
C=: fixcsv 0 :0
Id,Bank,Name,Kids
3,Rabobank,Jerry,2
6,HSBC,Jan,0
4,Westpac,Brian,1
1,HSBC,Alex,2
7,,Ben,0
)
Note 'example usage'
'Id' joinLeft mergeTables A;<B
('Id';'Name') joinLeft mergeTables A;<B
('Id';'Name') joinLeft mergeTables A;B;<C
('Id';'Name') joinRight mergeTables A;B;<C
joinOuter mergeTables A;B;<C
)
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
