topic Re: Top 3 cell values in every column in Discussions

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

amy — Thu, 16 Aug 2018 18:37:28 GMT

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.

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.

Re: Top 3 cell values in every column

dale_lehman — Mon, 30 Apr 2018 16:07:04 GMT

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

Re: Top 3 cell values in every column

amy — Tue, 01 May 2018 13:37:52 GMT

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

Re: Top 3 cell values in every column

dale_lehman — Tue, 01 May 2018 14:29:04 GMT

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.

Re: Top 3 cell values in every column

amy — Wed, 02 May 2018 14:22:10 GMT

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

Re: Top 3 cell values in every column

dale_lehman — Wed, 02 May 2018 13:27:57 GMT

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).

Re: Top 3 cell values in every column

amy — Wed, 02 May 2018 14:03:45 GMT

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

Re: Top 3 cell values in every column

uday_guntupalli — Wed, 02 May 2018 14:46:43 GMT

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

Re: Top 3 cell values in every column

amy — Wed, 02 May 2018 15:06:16 GMT

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?

Re: Top 3 cell values in every column

txnelson — Wed, 02 May 2018 15:18:28 GMT

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 ""

Re: Top 3 cell values in every column

amy — Wed, 02 May 2018 15:20:13 GMT

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

Re: Top 3 cell values in every column

uday_guntupalli — Wed, 02 May 2018 15:23:18 GMT

@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

Re: Top 3 cell values in every column

uday_guntupalli — Wed, 02 May 2018 15:24:45 GMT

@amy,

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

Re: Top 3 cell values in every column

txnelson — Wed, 02 May 2018 15:28:35 GMT

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.

Re: Top 3 cell values in every column

amy — Wed, 02 May 2018 15:33:43 GMT

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

Re: Top 3 cell values in every column

amy — Wed, 02 May 2018 16:24:02 GMT

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

Re: Top 3 cell values in every column

txnelson — Wed, 02 May 2018 16:36:01 GMT

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

Re: Top 3 cell values in every column

amy — Wed, 02 May 2018 17:49:07 GMT

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