35640_en-US

liliconcom

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:

 

T1	T2	T3	T4	T5	T6	T7	T8	T9	T10	Max_Value	Col_Name (Parameter)
87.2	83.6	81.4	83	83	83	83	82.3	83.3	84.6	87.2	T1
85.9	84	82	84	83.3	83.3	84	83	86.5	87.2	87.2	T10
84.9	82.7	86	83	82.3	82.3	83	82	83.6	85.2	86	T3
85.6	84.6	82.7	84.6	84.3	84.3	84.3	83.6	84.9	86.2	86.2	T10
85.2	83.6	82	83.3	83	83	83.3	82.3	84.3	85.6	85.6	T10
86.8	84.3	82.7	84.3	83.6	83.6	84.3	83.3	85.9	86.2	86.8	T1
86.2	84	82.3	84	87	83.3	83.6	83	84.9	85.9	87	T5
88.1	84.6	82.3	84.3	83.6	84	84.3	83.3	85.9	86.2	88.1	T1
85.6	83.3	81.7	83.3	83	83	83.6	82.7	84	85.9	85.9	T10
37.4	36.1	36.4	36.4	36.1	36.1	36.4	35.4	36.7	37.4	37.4	T1
36.7	34.5	34.8	34.5	33.8	34.1	34.5	33.5	36.1	36.1	36.7	T1
34.8	32.9	33.2	32.9	32.2	32.5	36	31.9	33.8	35.1	36	T7
38	36.4	36.4	36.4	36.4	36.1	36.7	39	38	38.3	39	T8
38	35.1	35.1	40	34.1	34.5	34.8	33.8	36.4	37	40	T4

 

Thanks!

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

I mis-read your question at first...so this answer contains a simplification, not using substituteInto, and a variation that looks for a particular value.

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

findval=7;
row = 2;
Row() = row; // names will pull data from the current row
rowpos = Loc( findval==matrix(evallist(names)) ); 

show(names[rowpos],names[rowpos[1]][row]);
names[rowpos] = {:third};
names[rowpos[1]][2] = 7;

Loc returns the positions of the non-zero items in a matrix. The == operator with a scalar and a matrix returns a matrix of 1s and 0s. The Loc function returns a matrix in rowpos (with the indexes of the 1s); the [1] takes the first location that matched findval. names[rowpos[1]] is then a column name, and the [row] gets the matching answer from the column's row.

 

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

Thanks Craige!

Instead of Concat(), is there any other function that can be used with Substitute Into(), to find position of particular value in a row across multiple columns?

 

 

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
rowpos = Loc( names, 6 ); // works only with a list

 

 

Thanks,

Ravi

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!

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. 

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. 

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!

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?

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.

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));
);

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.

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.

 

 

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 ?

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

Here is the data table after I ran the script

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

Thanks much Jim for the reply! However, it still doesn't work for me with dt=current data table(). Not sure what is going on. BTW, could you send me the output file you generated?

Thanks!

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

The script and data table worked great for me.  Now, I did change the

 

     dt=Open("........");

to

 

     dt=current data table();

Since the table was already open.

 

So, I would suggest that you try that, or that you place an additional statement after your dt=Open statement:

     wait(0);

There is a chance that your data table has not finished before the For Each Row starts.  The Wait(0); will force the open to complete before running the For Each Row

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

It still doesn't work properly for me. Attached please find both the script and the data files. Could you please let me know what I did wrong here? 

Thanks!

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

Good point. The example could be generalized like this (intentionally ugly column arrangment...) Pick one of the either/or that works best for you. The 2nd one has the advantage of isolating you from accidental rearrangements of the columns. The first one might be good for 100s of columns.

dt = New Table( "Untitled",
    New Column( "maxval", numeric ),
    New Column( "a", Numeric, Set Values( [1, 33, 1] ) ),
    New Column( "maxcol", Numeric ),
    New Column( "b", Numeric, Set Values( [2, 2, 2] ) ),
    New Column( "c", Numeric, Set Values( [333, 1, 0] ) ),
);


// either
vars = [2 4 5];
colnames = (dt << getcolumnnames)[vars];
// or
colnames={a,b,c}; // this might be best choice


For Each Row(
    row = Row();
    location = Loc Max( dt[ row, colnames] ); // location of maximum value in array
    col = colnames[location]<<GETNAME></GETNAME>

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

Resolved i was missing the offset of columns that had to be added to the location variable.

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

I have run into one issue here, if the columns I need to choose are between the data set it doesnt seem to work.

 

For Each Row(
    r = Row();
    // 1::3 means columns 1 through 3.  {a,b,c} would also work.
    location = Loc Max( dt[ r, 57 :: 113] ); // location of maximum value in array
    maxval = dt[r, location];
    maxcol = colnames[location] << getname;
);

 

I need to choose columns 57 through 113, but for some reason when I do this it through the script off and i get incorrect values. 

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

// this example uses JMP 13 data table indexing

dt = New Table( "Untitled",
    New Column( "a", Numeric, Set Values( [1, 33, 1] ) ),
    New Column( "b", Numeric, Set Values( [2, 2, 2] ) ),
    New Column( "c", Numeric, Set Values( [333, 1, 0] ) ),
    New Column( "maxval", numeric ),
    New Column( "maxcol", Numeric )
);

colnames = dt << getcolumnnames;

For Each Row(
    r = Row();
    // 1::3 means columns 1 through 3.  {a,b,c} would also work.
    location = Loc Max( dt[ r, 1 :: 3] ); // location of maximum value in array
    maxval = dt[r, location];
    maxcol = colnames[location] << getname;
);

Data table with maximum value from row and name of maximum column for rowData table with maximum value from row and name of maximum column for rowData table with maximum value from row and name of maximum column for row

Earlier post on data table subscripting

Re: Max value of each row across multiple columns and the corresponding name of the source columnLooking for the exact same solution.
For each row find max value and return column name so i can run statistics on the resultant data.
Eg to find how many times a column name shows up as maximum, the number of columns that contribute to max value, the STD DEV etc.