cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Faisal_MEM
Level I

Concatenate data tables by matching column names.

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.

1 REPLY 1
Craige_Hales
Super User

Re: Concatenate data tables by matching column names.

(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 columnsConcatenated common columns

 

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

 

Craige