Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
lwx228
Level VII

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

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;

2020-06-18_09-33.png

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

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

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
	)
);
Jim

View solution in original post

5 REPLIES 5
Highlighted
lwx228
Level VII

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

  • 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;
Highlighted
gzmorgan0
Super User

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

@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;

 

 

Highlighted
lwx228
Level VII

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

Thank you for your help!

2020-06-18_17-11.png

Highlighted
txnelson
Super User

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

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
	)
);
Jim

View solution in original post

Highlighted
lwx228
Level VII

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

Thank Jim!

 

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