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. 

17 REPLIES 17
amy
amy
Level II

Re: Top 3 cell values in every column

@txnelson Well, after setting the value of a cell to missing value/ hide/ exclude a cell's value, I would want to run some models onthe data table and then decide to either keep it that way or include the value to the cell again. Is this possible? 

uday_guntupalli
Level VIII

Re: Top 3 cell values in every column

@amy,

 You can always subset tables, rather than trying to include individual cells. 

Best
Uday
txnelson
Super User

Re: Top 3 cell values in every column

There is not a direct way to retireve a cell value once it has been set to Missing.  That is the reason that @dale_lehman suggested using new columns and @uday_guntupalli suggests to create a new subset of the data, and then to modify the subset.

Jim
amy
amy
Level II

Re: Top 3 cell values in every column

Okay, thank you for helping out. @txnelson @uday_guntupalli

uday_guntupalli
Level VIII

Re: Top 3 cell values in every column

@amy,

 

dt = Current Data Table(); 
SearchTerm = "Prediction"; 
ColNames = dt << Get Column Names("String"); 
DesRowsList = {}; 
for(i = 1, i <= N Cols(dt), i++,
		If(Contains(ColNames[i],SearchTerm),
			SR = dt << Select Where(As Column(dt,ColNames[i]) > 4.5) << Get Selected Rows; 
			Insert Into(DesRowsList,SR); 
			dt << Clear Select; 
		  );
   );

This should help you get the rows that meet your condition into one list. From there you should be able to extract top 3 in each case. To answer your question, I am not aware of excluding only cells 

Best
Uday
amy
amy
Level II

Re: Top 3 cell values in every column

Also, is there a way I can match columns like B and Prediction B? Thanks

txnelson
Super User

Re: Top 3 cell values in every column

Use the Loc() function instead of the Contains() function to specify specific strings.

Jim
amy
amy
Level II

Re: Top 3 cell values in every column

@txnelson I could do that using regex: match = regex(colstrings, "[{,]\s*([^{,}]*Prediction[^{,}]*)[,}]", "\1");