topic Re: Selecting Cells based on color in Discussions

Selecting Cells based on color

MikeDereviankin — Fri, 09 Jun 2023 22:00:16 GMT

Hello,

After running the outlier analysis tool in JMP, I color coded the cells that contained outliers. How can I select all these cells and replace them with the name, "outlier"? The next step, would be to name all the other cells "normal".

Let me know if this is feasible. A snapshot of the datasheet in appended in this post.

Re: Selecting Cells based on color

txnelson — Fri, 11 Dec 2020 03:12:48 GMT

I am not aware of an easy way to get the cell color from a cell, let alone, select all of the rows for cells in a column, that has a specific color in it. However, the way that I do that, is to extract the script for the column of interest, and then to search for the Color Cells area of the script, and parse out the rows.

I have attached a sample data table. The data table was produced using the Outlier Analysis Tool. When the below script is run on that data table, it will find the cells in the column SS-E that have been colored light red(color 35 to JMP), and change the value for those cells to "outlier" and then change all of the other cells to "normal".

Please note, to change the cells to either "outlier" or "normal" the column had to be changed to a character column, and all of the original data are replaced.

Here is the script

names default to here(1);
dt=current data table();


x=char(:name("ss-e")<<get script);
x=substr(x,contains(x,"Color Cells"));

x=substr(x,contains(x,"{35, ")+5);
x=substr(x,1,contains(x,"}"));


myMatrix =matrix( parse(x));

dt:Name("ss-e") << data type(character);

dt << select rows(myMatrix);

dt:Name("ss-e")[dt<<get selected rows] = "outlier";
dt << invert row selection;
dt:Name("ss-e")[dt<<get selected rows] = "normal";

Re: Selecting Cells based on color

ian_jmp — Fri, 11 Dec 2020 12:59:30 GMT

As an alternative to the approach of @txnelson, it's maybe worth going back a step to think about how the table was made. The attached version of the 'Penta' sample data was made exploiting the 'Add To Missing Value Codes' option:

You can then use code like this, which has the added benefit that you are not actually changing the data values in the table:

NamesDefaultToHere(1);

dt = DataTable("myPenta");

// Pick a column
myCol = Column(dt, "L4");

// Get the values deemed to be outliers
mvc = myCol << getProperty("Missing Value Codes");

// Delete this column property, else the subsequent 'getValues()' will return '.'
// rather than the cell value in the case of outliers
myCol << deleteProperty("Missing Value Codes");

// Get all the values
vals = myCol << getValues;

// Build the list to go into the 'Value Label' property
valList = {};
for(v=1, v<=NItems(vals), v++,
	if(Contains(mvc, vals[v]),
		// We have an outlier . . .
		InsertInto(valList, EvalExpr(Expr(vals[v]) = "Outlier")),
		// Else the point is 'Normal'
		InsertInto(valList, EvalExpr(Expr(vals[v]) = "Normal"))
		)
	);

// Add the 'Value Label' property
myCol << valueLabels(valList);

Re: Selecting Cells based on color

MikeDereviankin — Fri, 11 Dec 2020 14:30:46 GMT

This works great!

What is the syntax to iteratively loop through each column name instead of appending just one column name? How do you restrict this loop to only look through columns 1-10 for example?

Appreciate the scripting help, this is great stuff.

Re: Selecting Cells based on color

hogi — Mon, 22 Jul 2024 05:05:55 GMT

One can go via the script behind the columns to get all colored cells: