topic Re: How to find intersection of two tables column names? in Discussions

How to find intersection of two tables column names?

AT — Tue, 13 Mar 2018 17:56:08 GMT

Hi,

I have two data tables with similar column names and like to write a script to find the overlap (intersection) of two column names and write the interesection into a new data table. I tried using Tables and join and did not work for me.

I appreciate your help. Thanks

Re: How to find intersection of two tables column names?

txnelson — Tue, 13 Mar 2018 19:32:15 GMT

Here is a simple example of how to do this. I am not sure of what the final combined data table is, so I guessed it had to be a concatenation.

names default to here(1);
dtA = Open("$SAMPLE_DATA\Big Class.jmp");
dtB = Open("$SAMPLE_DATA\Big Class Families.jmp");

dtACol = New Table("A Columns", private,
	New Column("A", character, values(dtA<<get column names))
);
dtBCol = New Table("B Columns", private,
	New Column("B", character, values(dtB<<get column names))
);

dtJoin = dtACol << Join( private,
	With( dtBCol ),
	By Matching Columns( :A = :B ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 )
);

CommonColList = dtJoin:A << getvalues;

dtAsubset = dtA << subset( private, selected rows(0), columns(CommonColList));
dtBsubset = dtB << subset( private, selected rows(0), columns(CommonColList));

dtFinal = dtAsubset << Concatenate( dtBsubset );

Re: How to find intersection of two tables column names?

AT — Tue, 13 Mar 2018 21:35:53 GMT

Thanks Jim. My data tables are not equal in number of rows and the final table should be the intersection of the the two similar columns. I have for instance 25 rows in table A and 25 rows in table B and the output should be the two rows that is not comon between similar columns.

Re: How to find intersection of two tables column names?

uday_guntupalli — Tue, 13 Mar 2018 21:50:46 GMT

@AT,

dtA = Open("$SAMPLE_DATA\Big Class.jmp");
dtB = Open("$SAMPLE_DATA\Big Class Families.jmp");

ACols = Associative Array(dtA << Get Column Names("String")); 
BCols = Associative Array(dtB << Get Column Names("String")); 
Intersection = ACols ;
Intersection << Intersect(BCols); 
Res = Intersection << Get Keys;

Can you work with the common columns being outside the data table and then defining a new table ?

If so, look at the above example.

Re: How to find intersection of two tables column names?

txnelson — Tue, 13 Mar 2018 21:58:39 GMT

OK, I am now totally confused......can you please provide a couple of input data tables and the desired outcome data table?

Re: How to find intersection of two tables column names?

AT — Tue, 13 Mar 2018 22:08:23 GMT

Thanks. I tried it and did not see output table. I have attached my two input data tables.

Re: How to find intersection of two tables column names?

txnelson — Tue, 13 Mar 2018 22:20:54 GMT

Is this what you want? Note the Match Flag value for the rows not found in both data tables.

Names Default To Here( 1 );
input = Data Table( "input" );
output = Data Table( "output" );

dtFinal = input << Join(
	With( Output ),
	Match Flag( 1 ),
	By Matching Columns( :boardno = :boardno ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 1 ),
	Preserve main table order( 1 ),
	Merge same name columns( 1 )
);

Re: How to find intersection of two tables column names?

AT — Tue, 13 Mar 2018 22:48:33 GMT

Thanks. I have attached the output. It is 25 rows with Match Flag 3 and only two with Match Flag 1. I was expecting only two rows with no match. What does Match Flag 1 and 3 mean? (freuqnecy?).

Re: How to find intersection of two tables column names?

txnelson — Tue, 13 Mar 2018 23:33:40 GMT

1=data came only from data table 1

2=data came only from data table 2

3=data came from both data tables(a match)

See page 288 in the Using JMP documentation

Help==>Books==>Using JMP

Re: How to find intersection of two tables column names?

AT — Wed, 14 Mar 2018 22:21:42 GMT

Thanks Jim.