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

[KdbNoob]

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?

[TerryLynch]

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

n:1000000;

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

q)a~t

1b

Terry

[Attila]

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

Attila

[alvi_kabir919]

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)n:1000000;

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)

1b