topic How to convert 3 columns in one table in Discussions

How to convert 3 columns in one table

AvgFinch1077879 — Thu, 16 Nov 2023 13:25:52 GMT

Hi all,

I am reaching out to get some help on a data process problem. Some time ago, I could convert 3 columns (load pH, load NaCl, Protein % in Load Wash) in one table : since it was very helpful, I would like to do the same thing with a new experiment. Unfortunately, I don’t remember how I did it before. You can have a look at the screenshots enclosed to have a better understanding of what I want to obtain.

I use JMP 17.0.0.

Thank you very much in advance!

Original data:

Table obtained :

Re: How to convert 3 columns in one table

txnelson — Thu, 16 Nov 2023 14:17:39 GMT

Your sample data is not in a readable form, please attach a copy of your original data to aid in responding to your issue.

Re: How to convert 3 columns in one table

drdrf — Thu, 16 Nov 2023 14:32:50 GMT

From "Tables" select "Summary"

Then set as as shown in the screen shot

Statistics - Mean (Protein%)

Group - load NaCl

Subgroup - load pH

I think that should result in the format you are looking for.

Re: How to convert 3 columns in one table

AvgFinch1077879 — Thu, 16 Nov 2023 17:12:33 GMT

Thank you for your reply!

It would work like this, but I cannot transfer the color gradient (set up in column info) to the table.

I am pretty sure that I used a different way to obtain this table last time but I don't remember how.

Re: How to convert 3 columns in one table

AvgFinch1077879 — Thu, 16 Nov 2023 17:15:53 GMT

For example here, I would like to have the different pH values on the top, the NaCl values on the right and the "binding proportion" values in the table.

Re: How to convert 3 columns in one table

AvgFinch1077879 — Thu, 16 Nov 2023 17:18:07 GMT

Also, last time, the color gradient was directly transferred in the table. Which is not the case when I use the "Tables > Summary" feature.

Re: How to convert 3 columns in one table

txnelson — Thu, 16 Nov 2023 18:24:08 GMT

Here is a simple script that transforms the data

Names Default To Here( 1 );
dt = Current Data Table();

// Use Tabulate to create the transformed table
(tab = dt << Tabulate(
	Change Item Label( Statistics( Mean, " " ) ),
	Remove Column Label( Analysis Columns( Binding proportion ) ),
	Add Table(
		Column Table( Grouping Columns( :pH ), Statistics( Mean ), Analysis Columns( :Binding proportion ) ),
		Row Table( Grouping Columns( :"[NaCl]"n ) )
	)
)) << Make Into Data Table;

Current Data Table() << set name( "Final" );
dtFinal = Current Data Table();
Report( tab ) << close window;

// change column names and set the Value Colors
valColors = dt:Binding proportion << get property("Value Colors");
For( i = 2, i <= N Cols( dtFinal ), i++,
	Column( dtFinal, i ) << set name( Word( -1, Trim( Column( dtFinal, i ) << get name ), "()," ) );
	Column( dtFinal, i ) << set property("Value Colors", eval(valColors));
	Column(dtFinal,i)<<color cell by value; 
	Column(dtFinal,i)<<set display width(40);
);

Please do not just cut and paste this code, without taking the time to study it and learn what it is doing.

Re: How to convert 3 columns in one table

AvgFinch1077879 — Fri, 17 Nov 2023 08:59:28 GMT

Thank you very much!

I understand every line but I don't know where I should write in the color theme. I want to use "Yellow Green Red" gradient so I tried to replace "valColors" on line 22 by "Yellow Greed Red" but this is not it.

And when I run the script without changing anything this error message shows up:

Re: How to convert 3 columns in one table

txnelson — Fri, 17 Nov 2023 12:48:08 GMT

The script I provided was based upon having the Column Property "Value Colors" set. Apparently your starting data table does not have them set.

I have changed the JSL to use the "Gradient Colors" Column Property. I also changed the code to set the "Color Gradient" on the initial data table, so it now ensures that when the new data table is being created, that it has what it needs to set the colors for the cells.

Names Default To Here( 1 );
dt = Current Data Table();

// Set the color gradient column property
dt:binding proportion << set property("color gradient", {"Green Yellow Red"(1), Range( {6.2, 87.38, 25.6941666666667} )});
dt:binding proportion<<color cell by value;

// Use Tabulate to create the transformed table
(tab = dt << Tabulate(
	Change Item Label( Statistics( Mean, " " ) ),
	Remove Column Label( Analysis Columns( Binding proportion ) ),
	Add Table(
		Column Table( Grouping Columns( :pH ), Statistics( Mean ), Analysis Columns( :Binding proportion ) ),
		Row Table( Grouping Columns( :"[NaCl]"n ) )
	)
)) << Make Into Data Table;

Current Data Table() << set name( "Final" );
dtFinal = Current Data Table();
Report( tab ) << close window;

// change column names and set the Color Gradient
valColors = dt:Binding proportion << get property("Color gradient");
For( i = 2, i <= N Cols( dtFinal ), i++,
	Column( dtFinal, i ) << set name( Word( -1, Trim( Column( dtFinal, i ) << get name ), "()," ) );
	Column( dtFinal, i ) << set property("Color Gradient", eval(valColors));
	Column(dtFinal,i)<<color cell by value; 
	Column(dtFinal,i)<<set display width(40);
);

Re: How to convert 3 columns in one table

AvgFinch1077879 — Mon, 20 Nov 2023 08:17:07 GMT

Perfect, thanks a lot !