Color code Top 5

[#1]

I would like to color code my table (so that all the charts and graphs I make follow the same). But I want to do it so that top 5 are red, middle 15 are yellow, and bottom 27 are green. There has to be an easy way to do this. 

For context, I have all the CDC rate data for various diseases that I need to track for 2014-2021. I need to be able to compare the US to any of the states and have it show if that state is in the top 5, middle, or bottom, compared to other states. In a perfect world, the one United States row would always show up as black as the benchmark. I have attached the data files below. One is the data in columns, the other has it stacked. 

I am using JMP 17. 

[#2 · Accepted solution]

Here is a little script that gives the top,middle and bottom, and US groupings

It does this separately for each year.  If that is not what you want, then the script can easily be changed by taking out the year references.

Names Default To Here( 1 );
dt = Data Table( "All Items 2014-2021 Stacked" );

dtSum = 
// → Data Table( "SummaryData" )
Data Table( "All Items 2014-2021 Stacked" ) << Summary(
	Group( :State, :Year ),
	Mean( :Data ),
	Freq( "None" ),
	Weight( "None" ),
	Link to original data table( 0 ),
	output table name( "SummaryData" )
);
dtSum << delete rows( dtSum << get rows where( :state == "United States" ) );

// Sort data table
dtSum = Data Table( "SummaryData" ) << Sort(
	By( :Year, :"Mean(Data)"n ),
	Replace Table,
	Order( Ascending, Ascending )
);

DtSum << New Column( "Color Group",
	character,
	set each value(
		If(
			Col Cumulative Sum( 1, :Year ) <= 5, "Top",
			Col Cumulative Sum( 1, :Year ) <= 20, "Middle",
			"Bottom"
		)
	)
);

Data Table( "All Items 2014-2021 Stacked" ) << Update(
	With( Data Table( "SummaryData" ) ),
	Match Columns( :State = :State, :Year = :Year )
);

dt:Color Group[dt << get rows where( :Color Group == "" )] = "U.S.";
dt << delete column( :"Mean(Data)"n );

dt:color group << set property( "value colors", {"Bottom" = 20, "Middle" = 41, "Top" = 19, "U.S." = 0} );

dt << Graph Builder(
	Size( 534, 464 ),
	Show Control Panel( 0 ),
	Variables( X( :State ), Y( :Data ), Color( :Color Group ) ),
	Elements( Points( X, Y, Legend( 7 ) ) )
);

[#3]

This helps a lot. But the first part of your script, where it generates the summary data. What does N rows mean? I need to know which virus each one is. 

[#4]

The N Rows column shows you how many columns exist for one group in your main table. In the screenshot below, the summary table tells you that there are 3 rows for Alabama in 2014, and so on. This does mix the different viruses in column Mean(Data).

[#5]

Unfortunately, I need all the viruses separate. Would I be better off having each virus as its own table?

[#6]

Separating each virus into separate data tables would be one way to handle the issue.  Then run the script for each virus data table.

It would be also possible to just add in Virus into the script I provided, along with the State and Year aggregation, and it would perform the calculations separately for each virus.

[#7]

So now that I have it perfect-ish. I have one more question. Is there a way to combine my data column and my color group column so that I see the data/numbers in the data column, but the color in that cell is that of the color group? 

[#8 · Accepted solution]

The answer might depend a bit on what you are using the table (and colors) for. There are pure scripting options or you can do this interactively: When you have Value Colors in one of your columns select that column and go to Rows/Color or Mark by Column

then press OK from the menu.

This will set this these colors as row states

Right click on those rows and select color by row state

now you have all rows with those colors.

Then select all columns you don't want to have that color, right click on one of the selected cells and make the color white

You will end up with specific colors as row states on rows though with this method and color on your cells (you use white to overwrite the row state color)

It also seems like that JMP cannot record these actions to provide you with a script.

Edit:

Some ideas for scripting can be seen from How to use JSL to set the color gradient of a column in text format by the value of a numeric column...  but your situation is a bit different

Edit2:

Here is a bit more advanced method of coloring the cells using scripting

Names Default To Here(1);

dt = Open("$DOWNLOADS/All Items 2014-2021 Stacked.jmp");

color_property = Column(dt, "Color Group") << Get Property("Value Colors");
values = dt[0, "Color Group"];

For Each({color_prop}, color_property,
	value = Arg(color_prop, 1);
	color = Arg(color_prop, 2);
	color_rows = dt << Get Rows Where(:Color Group == value);
	:Data << color cells({color, color_rows});
);

[#9]

The Scripting Index provides definitions and examples for all most of the functions and messages that can be used in JSL.  Here is a simple bit of JSL to do what you need, however, you need to familiarize yourself with the Scripting Index so you will be able to discover your own answers.  That is an on going process for all of us.

dt:color group << color cells("green", dt << get rows where(:color group =="Bottom"));
dt:color group << color cells("yellow", dt << get rows where(:color group =="Middle"));
dt:color group << color cells("red", dt << get rows where(:color group =="Top"));
dt:color group << color cells("black", dt << get rows where(:color group =="U.S."));

[#10]

Thank you!!! I have it perfect now!!!