Showing results for 
Search instead for 
Did you mean: 

How to merge a list of tables with different columns in kdb/q into

New Contributor

I know I can do

raze table_list

But that require every single table in the table list with the same columns.

For my use case, I can addept adding columns with null values, if that column doesn't exist in all tables.In that case, I can do

(uj) over table_list

But this line of code is very slow. nowhere as fast as raze.

Is there an efficient way of merging all tables, even if they don't all have the same columns?


New Contributor II
You'll never get as far as raze with conforming tables but using global append (if you can accept the creation of a global) can be an improvement on uj over:

/conforming tabs
tabs:{flip`col1`col2`col3`col4`col5`col6!(n?100i;n?100j;n?.z.D;n?10f;n?.z.P;n?5h)}each til 10

q)\ts raze tabs
82 570426736

/non-conforming tabs
nctabs:{flip(6?`4)!(n?100i;n?100j;n?.z.D;n?10f;n?.z.P;n?5h)}each til 10

q)\ts a:(uj/)nctabs
5560 11729374832

/global append
q)\ts {t::(uj/)0#'x;{t,:x}each x}nctabs
3146 356520224



for some high cost of memory you can also do

q)\ts {t::(uj/)0#'x;{t,:x}each x}nctabs
1470 356520192

q)\ts {raze((uj/)0#'x)uj/:x}nctabs
577 8912912032


Another solution: construct list of all table columns with their null values, update every table's missing columns with appropriate nulls, reorder each table by union of column list, and flatten:

q)tabs:{flip`col1`col2`col3`col4`col5`col6!(n?100i;n?100j;n?.z.D;n?10f;n?.z.P;n?5h)}each til 10
q)\ts raze tabs
222 570428928
q)nctabs:{flip(6?`4)!(n?100i;n?100j;n?.z.D;n?10f;n?.z.P;n?5h)}each til 10
q)\ts a:(uj/)nctabs
8477 11729374832
// global append
q)\ts {t::(uj/)0#'x;{t,:x}each x}nctabs
3354 6060774704
// table update and raze
q)\ts b:raze nctabs {i:where not y[1] in cols x;y[1] xcols ![x;();0b;y[1][i]!y[0][i]]}\: (raze {first each (0#x) cols x} each nctabs;raze cols each nctabs)
2817 8912913616
// uj empty tables, join orig and raze
q)\ts c:{raze((uj/)0#'x)uj/:x}nctabs
2180 8912912176
q)(a~b) and (b~c) and (c~t)