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
amy
amy
Level II

How to select the highest three cell values in every column?

For every column that contains a word, say "Prediction", I want to extract top 3 cell values either greater than 5 or greater than 4.5 (if I get 3 numbers above 5 then I dont want any numbers above 4.5) in a list. Further, picking the one that is farthest away from 5 and hide the corresponding cell value in its raw table. For example, in this example table given below:

In Prediction B column, I want to store 6.6, 5.3 and 5. And hide the cell adjacent to Prediction B nut in Column B i.e. cell value 65. 

 

Capture.PNG 

My code selects columns which have the string "Prediction" in it. 

dt = Current Data Table();
a = "Prediction";
col = dt << get column names( string );
nc = N Items( col );
For( i = 1, i <= nc, i++,
	If( Contains( col[i], a ), )
);

How can I choose the top 3 cells greater than 5 or 4.5? Thank you so much JMP community for being so prompt. 

1 ACCEPTED SOLUTION

Accepted Solutions
dale_lehman
Level VII

Re: Top 3 cell values in every column

I've attached your example with an attempt to use the column B predictions as you specified.  I'm not sure this does exactly what you want, but I think it is close.

View solution in original post

17 REPLIES 17
dale_lehman
Level VII

Re: Top 3 cell values in every column

The column rank function (and some IF statements) might do the trick.

amy
amy
Level II

Re: Top 3 cell values in every column

@dale_lehman well, I couldn't find much on how I can use the column rank function. I can extract out one maximum value but I still can't figure how I can go ahead with top 3. 

dale_lehman
Level VII

Re: Top 3 cell values in every column

I've attached your example with an attempt to use the column B predictions as you specified.  I'm not sure this does exactly what you want, but I think it is close.

amy
amy
Level II

Re: Top 3 cell values in every column

@dale_lehman Thanks for the help. I have updated my script to select the maximum value of every column and have saved it in the "val" variable. Do you know how I can exclude the cell with the maximum number in the first column, perform some operations then exclude the cell with maximum number in second column and so forth? 

 

dt = Current Data Table();
col = dt << get column names( string );
nc = N Items( col );
For( i = 1, i <= nc, i++,
	Col1 = Column( dt, i );
	If( Contains( col[i], "Prediction" ),
		Show( col[i] );
		val = Abs( Col Max( Col1 ) );
		Show( val );
		MaxRow = dt << get rows where( As Column( Col1 ) == Abs( Col Max( Col1 ) ) );
	);
);

 

dale_lehman
Level VII

Re: Top 3 cell values in every column

I don't do much scripting so I'll leave that for someone else to help you with.  I'll only say that I don't really understand the reason to want to exclude rows after doing these operations - it would seem better to create new columns with those rows either excluded or set to missing.  I would think that script would be relatively straightforward and I don't like the idea of overwriting the original data (if that is what you are suggesting).

amy
amy
Level II

Re: Top 3 cell values in every column

This is a way of checking for data that needs to be removed from my analysis so I have to do it the same way. Thanks a lot for all the help I really appreciate it @dale_lehman

uday_guntupalli
Level VIII

Re: Top 3 cell values in every column

@amy
           Are you looking to exclude the entire row or the row for that column only ? The reason I ask, I am pretty sure you can exlude the whole row by using: 

 

dt << Exclude ;

          However, if you just want to exclude the row in a column, I dont think it is possible. So why don't you try to store the row numbers into a variable and then exclude those rows ? 

Best
Uday
amy
amy
Level II

Re: Top 3 cell values in every column

I don't even want to exclude an entire row. I want to exclude only cells. For Example, in the table I have posted, I want to be able to exclude the cell in column "B" that is adjacent to the cell that has value 6.6 in column "Prediction B". I am still struggling to figure how I can use regex to match such strings. I have made 2 lists, one which contains col names that have the word "Prediction" in them and another that dont. I would appreciate if you could also point me out to any function you think would work out best with such type of tables. I have just started working with JSL and I am still struggling with it. Thanks in advance for the help. @uday_guntupalli

P.S: Does that mean there is no way I can exclude a single cell from a column in JSL?

txnelson
Super User

Re: Top 3 cell values in every column

In JMP you do not "Exclude" an individual cell, rather, you set the cell's value to a "Missing Value".  The Missing Value's symbol is "."

 

Of if the column is of a character Data Type, you set the value to ""

Jim