Concatenate data tables by matching column names.

Faisal_MEM

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.

Craige_Hales

(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.