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
lwx228
Level VIII

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
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
lwx228
Level VIII

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;
gzmorgan0
Super User (Alumni)

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;

 

 

lwx228
Level VIII

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

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
lwx228
Level VIII

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