topic Re: Searching Maximum value of each row across several column and name of source column in Discussions

Searching Maximum value of each row across several column and name of source column

Jackie_ — Fri, 09 Jun 2023 16:00:36 GMT

Hi,

I'm trying to come up with a jsl script which can find max value of each row and the column name corresponding to the max value. I was able to find the max value across all the columns but need some advice to find the column name corresponding to the max value

Here's the code:

Names Default To Here( 1 );

dt = Current Data Table();
dt << Select Columns( 2 :: 14 );
col = dt << Get Selected Columns( );
val = dt:MaxVal << get values( Format( "Best", 12 ) );

dt:Maximum Value << Set Formula( Max(col) );
nc = (N Items( col ));



For( j = 1, j <= N Items( val ), j++,
	xxx = {};

	For( i = 1, i <= nc, i++,
		If( Contains( char(col[i]), val[j] ),
			Insert Into( xxx, char(col[i]) )
		)
	);
	Column( dt, 16 )[j] =  xxx;
);

Re: Searching Maximum value of each row across several column and name of source column

jthi — Wed, 19 Oct 2022 16:14:11 GMT

There are many ways of doing this. Below is one example which should get you what you need:

Names Default To Here(1);

dt = Open("$DOWNLOADS/Sample data.jmp");

col_names = (dt << Get Column Names("String"))[2::(N Cols() - 2)];

m = dt[0, col_names];

Column(dt, "Maximum Value") << Set Values(V Max(m`));

Column(dt, "Maximum Col Name") << Set Data Type("Character");
Column(dt, "Maximum Col Name") << Set Each Value(
	max_idx = Loc(m[Row(), 0], :Maximum value)[1];
	col_names[max_idx];
);

Edit: Copy pasted correct script

Edit2: Second quite similar option:

Names Default To Here(1);

dt = Open("$DOWNLOADS/Sample data.jmp");
Column(dt, "Maximum Col Name") << Set Data Type("Character");

col_names = (dt << Get Column Names("String"))[2::(N Cols() - 2)];

Column(dt, "Maximum Value") << Set Each Value(Max(dt[Row(), col_names]));
Column(dt, "Maximum Col Name") << Set Each Value(
	// could use loc max, but as we need maximum value in column, we can just use that with Loc
	max_idx = Loc(dt[Row(), col_names], :Maximum value)[1];
	col_names[max_idx];
);

Re: Searching Maximum value of each row across several column and name of source column

Jackie_ — Wed, 19 Oct 2022 16:04:36 GMT

@jthi Thanks. Your code finds max value across column it seems and I want to evaluate max value of each row across multiple columns. I am expecting the following results

Re: Searching Maximum value of each row across several column and name of source column

jthi — Wed, 19 Oct 2022 16:11:15 GMT

I had copy-pasted some very weird script, I have now edited it to correct one

Re: Searching Maximum value of each row across several column and name of source column

txnelson — Wed, 19 Oct 2022 16:24:23 GMT

Here is another way of solving the problem

Names Default To Here( 1 );

dt = Current Data Table();
dt << Select Columns( 2 :: 14 );
col = dt << Get Selected Columns( );

dt << new column("MaxVal", set each value(Max(col)));
dt << new column("MaxVal Column", character, set each value(char(col[loc(matrix(col),:MaxVal)[1]])));

Re: Searching Maximum value of each row across several column and name of source column

OrdinaryParrot2 — Thu, 23 Mar 2023 04:44:26 GMT

dt << new column("MaxVal Column", character, set each value(char( col[loc(As List(matrix(col)),:MaxVal)[1] ])));

Propose above correction to last line for JMP14 atleast: the matrix(col) returns a matrix and the loc function needs a list as first argument so the matrix needs to be converted into a list with "As list".

Re: Searching Maximum value of each row across several column and name of source column

markschahl — Fri, 28 Jun 2024 07:21:11 GMT

Thanks for this Jim! Just used this on 250 columns. Hope you are doing well.
I am now a long way from Texas. Map.