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.
Re: Top 3 cell values in every column@txnelson I could do that using regex: match = regex(colstrings, "[{,]\s*([^{,}]*Prediction[^{,}]*)[,}]", "\1");
Re: Top 3 cell values in every columnUse the Loc() function instead of the Contains() function to specify specific strings.
Re: Top 3 cell values in every columnAlso, is there a way I can match columns like B and Prediction B? Thanks
Re: Top 3 cell values in every columnOkay, thank you for helping out. @txnelson @uday_guntupalli
Re: Top 3 cell values in every columnThere 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,
You can always subset tables, rather than trying to include individual cells.
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
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?
Re: Top 3 cell values in every columnIn 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 columnI 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@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 columnThis 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 columnI 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@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 columnI'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@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 columnThe column rank function (and some IF statements) might do the trick.
