Concatenate data tables by matching column names.

Faisal_MEM — Fri, 24 May 2019 20:40:22 GMT

Have two data tables.

DT1 has Colm: A, B, C, E, H, I.
DT2 has colm: A, B ,C , D ,E ,F, G, H, I

I need to concatenate Dt2 rows to DT1's rows but only the columns that match to DT1. So dont want colms: D, F, G, from DT2.

Thanks in advance for the help.

Re: Concatenate data tables by matching column names.

Craige_Hales — Fri, 24 May 2019 22:50:50 GMT

(If there is a better way, someone post it!)

If you are doing this through JMP's menus, make subsets with the common columns and concatenate the subsets. To automate that with JSL, something like this.

dt1 = New Table( "Untitled 6", invisible,
	New Column( "a", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2] ) ),
	New Column( "b", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [3, 4] ) ),
	New Column( "c", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [5, 6] ) ),
	New Column( "d", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [7, 8] ) )
);
dt2 = New Table( "Untitled 7", invisible,
	New Column( "b", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [9, 10] ) ),
	New Column( "c", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [11, 12] ) ),
	New Column( "d", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [13, 14] ) ),
	New Column( "e", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [15, 16] ) )
);
// find column names common to both tables
names1 = dt1 << getcolumnnames( "string" ); // {"a", "b", "c", "d"}
names2 = dt2 << getcolumnnames( "string" ); // {"b", "c", "d", "e"}
// associative arrays offer a nice intersection method
set1 = Associative Array( names1 ); // ["a" => 1, "b" => 1, "c" => 1, "d" => 1, => 0]
set2 = Associative Array( names2 ); // ["b" => 1, "c" => 1, "d" => 1, "e" => 1, => 0]
common = set1; // make a copy; <<intersect updates the left-hand-side
common << intersect( set2 ); // common==["b" => 1, "c" => 1, "d" => 1, => 0]
commonnames = common << getkeys; // {"b", "c", "d"}
// make subsets by selecting columns
dt1s = dt1 << Subset( All rows, columns( commonnames ), linked );
dt2s = dt2 << Subset( All rows, columns( commonnames ), linked );
// do the concatenation; tweak the extra parameters as needed
dt = dt1s << Concatenate( dt2s, Output Table( "ConcatTable" ), Create source column );
// clean up the intermediate tables
close(dt1s,nosave);
close(dt2s,nosave);
close(dt1,nosave);
close(dt2,nosave);

Concatenated common columns

Update: add the linked keyword to the <<subset for efficiency.

topic Re: Concatenate data tables by matching column names. in Discussions

Concatenate data tables by matching column names.

Re: Concatenate data tables by matching column names.