Extracting colors from a #rrggbb column

[#1]

Problem

You have colors in the HTML format "#RRGGBB" and you want to use them in JMP graphs. For instance, here is the data set I used for the world metro stations scatter plot. The color column is a character column with six hexadecimal digits, two for each of the color components.

Solution 1: Row Colors

In a JMP table, each row can have a few attributes associated with it, including color and marker shape, collectively called "row states". To automatically convert from the color text column to row states color, I used this script:

For Each Row(
	Color Of() = RGB Color(
		Hex To Number(Substr(:color, 2, 2)),
		Hex To Number(Substr(:color, 4, 2)),
		Hex To Number(Substr(:color, 6, 2))
	);
);

Each call to substr() pulls out one 2-character hex sequence, and then Hex To Number() converts it into a number in the 0-255 range. RGB Color() combines the three components into a single color value and Color Of() assigns it the the row state of the current row.

I put this script into a table script, so I can rerun it when I get updated data.

With the colors stored in row states, they will automatically get used for scatter plot marker colors.

Solution 2: Value Colors

For graph elements that don't usually correspond to individual rows, such as bar charts and treemaps, the row colors won't help. However, we can a Value Colors property to the color column and use it in the Color role in Graph Builder, those colors will be used in the graph. To create a Value Colors column property programmatically, first create a test Value Colors property and do a Copy Table Script on the data table to see how JMP records the Values Colors as JSL. You'll see something like this:

...
Set Property( "Value Colors",
  {"#000000" = -5425372, "#003399" = -13577875, "#004899" = -4354269,
  "#0063aa" = -13400361, "#006fba" = -2668175, "#0070bc" = -10628061,
  ...})

In each assignment, the lefthand side is the data value and the righthand side is the JSL number representation of a color value, same as the result of the RGB Color() function.

So our task is to create such an expression programmatically using JSL expressions and then send it to the data table to set the property with the Add Column Properties() message.

Here is my code:

eadd = Expr(
	Column( "color" ) << Add Column Properties( Set Property( "Value Colors", LLL ) )
);

evc = {};
ec = Expr( SSS = CCC );

For Each Row(
	ec2 = Substitute( Name Expr( ec ),
		Expr( SSS ), :color,
		Expr( CCC ),
			RGB Color(
				Hex To Number( Substr( :color, 2, 2 ) ),
				Hex To Number( Substr( :color, 4, 2 ) ),
				Hex To Number( Substr( :color, 6, 2 ) )
			)
	);
	Insert Into( evc, Name Expr( ec2 ) );
);

Substitute Into( eadd, Expr( LLL ), Name Expr( evc ) );

eadd; // evaluates the expression

By convention, I'm prefixing the expression variables with "e" and using uppercase for the substitution patterns. The basic idea is to make a expression with the basic form using placeholders, and then use Substitute to replace the placeholders with computed values. But here, there are two levels of substitution going on. One for the command, eadd, which has the LLL placeholder for the list of value colors, and one for each assignment, ec, which has placeholders SSS and CCC for the string and the color.

Discussion

 With either solution, we can make a scatter plot such as this one, which shows the colors of the biggest metros.

Homework

Make formula columns that pull out the red, green and blue components into separate columns.

Make formula columns that create hue, lightness, and saturation columns.

See Also

Raw data files for metro colors on github