topic Re: How do I find rows with the same values for any two columns in a given column? in Discussions

How do I find rows with the same values for any two columns in a given column?

lwx228 — Fri, 09 Jun 2023 23:29:30 GMT

For example, columns 1 —— 3 here:

dt = Open( "$SAMPLE_DATA\Missing Data Pattern.jmp" );
dt<<New Column("T1",formula(If(	As Column( 1 ) == As Column( 2 ) | As Column( 2 ) ==	As Column( 3 ) | As Column( 1 ) == As Column( 3 ),	1)));dt<<run formulas;Column("T1")<<deleteFormula;

Thanks!

Re: How do I find rows with the same values for any two columns in a given column?

lwx228 — Thu, 18 Jun 2020 03:10:43 GMT

I tried to simplify it:

dt = Current Data Table();
x = dt << Get As Matrix();
For( i = 1, i <= N Row( dt ), i++,
	For( j = 1, j <= 2, j++,
		If( x[i, j] == x[i, j + 1],
			x[i, 5] = 1;
			Break();
		)
	)
);

dt[0, 0] = x;

Re: How do I find rows with the same values for any two columns in a given column?

txnelson — Thu, 18 Jun 2020 04:01:56 GMT

Here is a different take on the issue. I am using a Mode() function to see what the mode is. If an actual mode exists, that means that at least 2 values match, and therefore column t1 will get set to 1. See the JSL comments for a more complete explanation.

I have attached a data table that I used for testing.

Names Default To Here( 10 );

dt = Current Data Table();
New Column( "t1" );
For( i = 1, i <= N Rows( dt ), i++,
	// add a value to the matrix that is less
	// than any value in the data table.  If
	// the mode() function does not find an
	// actual mode, it returns the smallest
	// value from the matrix, so if that is
	// the value returned, you know there
	// are not at least 2 values that equal
	// each other in the columns
	If( Mode( dt[i, 0] || [-999] ) == -999,
		dt:t1[i] = .,
		dt:t1[i] = 1
	)
);

Re: How do I find rows with the same values for any two columns in a given column?

lwx228 — Thu, 18 Jun 2020 04:17:41 GMT

Thank Jim!

Mode( dt[i, 1::3] || [-999] ) == -999

Re: How do I find rows with the same values for any two columns in a given column?

gzmorgan0 — Thu, 18 Jun 2020 08:47:26 GMT

@lwx228, @txnelson ,

Just for the fun of it...

Added @lwx228 example data to @txnelson , see attached JMP data table JN_AdjacentPlus.jmp
The script below is embedded in the table. The script is named "Find any rows with matching adjacent columns", actually matching values not empties.
Note #1: This script will take any number of columns. The columns do not need to be adjacent in the data table, just change the column specification in the definition of xmat.
Note #2: Abs(x1-x2) ensures adjacent matches return zero and all other values are > 0.
Note #3: Vmin() finds the minimum of each column of a matrix. So, transpose( Vmin(transpose(Abs(x1-x2))) ) returns the row minimum of Abs(x1-x2)
Note #4: Loc( vec ==0 ) returns the row numbers whose value is zero.
Note #5: Jim's solution finds any match, this script finds adjacent matches.

Names Default to Here(1);

dt = current data table();

xmat = dt[0,1::4];

nc = ncol(xmat);
x1 = xmat[0,1::nc-1];
x2 = xmat[0,2::nc];
idx = Loc( Transpose(Vmin(Transpose(abs(x1-x2)))) == 0);

dt << New Column("t1", numeric, continuous);
:t1[idx]=1;

Re: How do I find rows with the same values for any two columns in a given column?

lwx228 — Thu, 18 Jun 2020 09:14:44 GMT

Thank you for your help!