- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Top 3 cell values in every column
The column rank function (and some IF statements) might do the trick.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 ) ) );
);
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 ?
Uday
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 ""