cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
ILoveJMP
Level III

Max value of each row across multiple columns and the corresponding name of the source column

Hi, is there an easy way to find the max value of each row and the column name (or parameter) corresponding to the max value? Below is a simple example to illustrate the request:

 

T1T2T3T4T5T6T7T8T9T10Max_ValueCol_Name (Parameter)
87.283.681.48383838382.383.384.687.2T1
85.984828483.383.3848386.587.287.2T10
84.982.7868382.382.3838283.685.286T3
85.684.682.784.684.384.384.383.684.986.286.2T10
85.283.68283.3838383.382.384.385.685.6T10
86.884.382.784.383.683.684.383.385.986.286.8T1
86.28482.3848783.383.68384.985.987T5
88.184.682.384.383.68484.383.385.986.288.1T1
85.683.381.783.3838383.682.78485.985.9T10
37.436.136.436.436.136.136.435.436.737.437.4T1
36.734.534.834.533.834.134.533.536.136.136.7T1
34.832.933.232.932.232.53631.933.835.136T7
3836.436.436.436.436.136.7393838.339T8
3835.135.14034.134.534.833.836.43740T4

 

Thanks!

21 REPLIES 21
ILoveJMP
Level III

Re: Max value of each row across multiple columns and the corresponding name of the source column

Thanks Jim! This is what I got and the output isn't correct, for instance, row #1 and #2.

 

 

ILoveJMP
Level III

Re: Max value of each row across multiple columns and the corresponding name of the source column

Thanks Jim! This is what I got and the output isn't correct, for instance, row #1 and #2.

txnelson
Super User

Re: Max value of each row across multiple columns and the corresponding name of the source column

OK....my error, I was thinking you were not able to get the code to run, not that you were getting invalid results.  The issue is that your list of columns, "colnames" isn't matching the list of columns you are doing the Loc Max on.  The first and second column names need to be stripped off of the "colnames" list.  The issue also then caused a problem in getting the MaxVal using the statement:

     MaxVal = dt[r, location];

since it would be looking at the entire data table, and the location it was returning is based upon a matrix that has 2 less columns than the data table has.

So I have done a simple rewrite showing one way to over come the issue

dt = Open( "C:/data.jmp" );
colnames = dt << getcolumnnames;
For( i = 2, i >= 0, i--,
	colnames = Remove( colnames, i, 1 )
);
For Each Row(
	r = Row();
	location = Loc Max( dt[r, 3 :: 25] ); // location of maximum value in array from Col #3 to #25
	//MaxVal = dt[r, location];
	MaxVal_Col = colnames[location] << getname;
	MaxVal = as column(eval(MaxVal_Col));
);
Jim
ILoveJMP
Level III

Re: Max value of each row across multiple columns and the corresponding name of the source column

Thanks Jim again! However, if you exam the output, I think you will notice it isn't correct.

txnelson
Super User

Re: Max value of each row across multiple columns and the corresponding name of the source column

OK, I am missing something.....attached is my output.  When I check the results, the column MaxVal contains the maximum value found in that row columns 3-25, and the MaxVal_Col is the name of the column where the max value was found.

 

What am I missing?

Jim
DataMojo
Level II

Re: Max value of each row across multiple columns and the corresponding name of the source column

I have seen some errors which is suspect due to caching. The only way i have gotten around these is by restarting my OS and not JMP itself.

Could you run the updated script but after a restart of the OS and check ?

ravi_sasjmp
Level II

Re: Max value of each row across multiple columns and the corresponding name of the source column

Hi Craige,

When I run your sample script in JMP 10, I get an error message that says - argument should be matrix{1} in access or evaluation of 'Loc Max' , Bad Argument ...

Is data table indexing not allowed in JMP10?

 

Thanks!

Craige_Hales
Super User

Re: Max value of each row across multiple columns and the corresponding name of the source column

The data table subscripting is new in JMP 13.

You can try something like this idea to build an expression that will create a row vector matrix from the current row when evaluated. 

dt = New Table( "Untitled",
    Add Rows( 3 ),
    New Column( "first", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 5, 9] ) ),
    New Column( "second", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 6, 10] ) ),
    New Column( "skip", character, setvalues( {"a", "b", "c"} ) ),
    New Column( "third", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [3, 7, 11] ) ),
    New Column( "fourth", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [4, 8, 12] ) )
);

names = dt << getcolumnnames( "numeric" ); // returns a list of all numeric column names
Show( names ); // {first, second, third, fourth}
Substitute Into( names, Expr( {} ), Expr( Concat() ) );
Show( Name Expr( names ) ); // first || second || third || fourth

Row() = 2; // names will pull data from the current row
rowvec = (Eval( names )); // [5 6 7 8]

Given a list of row names, the Substitute Into() function replaces the curly bracket list operator with the concat function. Concat function is the same as the || concatenation operator and can take a lot of arguments. The concatenation evaluates each column reference in the list for the current row. The individual scalar values are concatenated into the row matrix. 

Craige
dale_lehman
Level VII

Re: Max value of each row across multiple columns and the corresponding name of the source column

Is there anything wrong with using the IFMax function in a new column?  I realize that if the number of columns is large, this is unweildy, but if there are only 20 columns or less, it is pretty simple to just enter the IFMax formula. 

Craige_Hales
Super User

Re: Max value of each row across multiple columns and the corresponding name of the source column

(looking up IFMax...) yes, that would be way easier to explain if you can easily list out the columns. Possibly faster too. Thanks for pointing it out!

Craige