Subscribe Bookmark RSS Feed

Select rows with maximal values in one column

new-jmper

Community Trekker

Joined:

Jun 23, 2011

I can use summary to find the maximums (multiple due to groups) in one column. However, I also want to look up in which rows in the original table the maximums occur.

Is there an easy solution? Thanks for your help!
1 ACCEPTED SOLUTION

Accepted Solutions
mosfettesteng

Community Member

Joined:

Aug 1, 2013

Solution

I know this is two years later, but this is for all those who found this post with the same question.

Try the function "Get Selected Rows" in your script.

 

yourarray = dt << Get Selected Rows;

 

Now the array "yourarray" will contain a list of the row numbers that are selected.

 

To find the rows with the maximum value, try

 

// finds the maximum value in the columnt
peakvalue = Col Max( :columname); 

// selects all rows with the maximum value. (dt is the name of the data table.)
dt << Select Where( :columnname == peakvalue);

 // stores a list of the row numbers with the maximum value in this array
yourarray = dt << Get Selected Rows;    

 

11 REPLIES
ms

Super User

Joined:

Jun 23, 2011

You can match the original table with the summary table using Update (Tables menu) with group column(s) = group column(s) and data = max(data) as matching columns. The maximum rows will be denoted by non-missing cells in the added NRows column.

My first answer was based on sloppy thinking...


Message was edited by: MS
new-jmper

Community Trekker

Joined:

Jun 23, 2011

MS,

Thanks. I gave it a try and I am still selecting all rows in the original table when selecting the 2nd summary table. Whenever I select one row (Max for the group) in the summary table, every row in the first summary table in the group also gets selected.

I hope I am following your instruction correctly. Is the 2nd summary table named as "DataTable By (group, data) By (group)" ?

FYI, I am using JMP7.

Dong
ms

Super User

Joined:

Jun 23, 2011

Sorry, you´re right. I was too quick to answer. I will edit my erroneous post above

What I wanted to suggest is that you can try to combine Summary with Update (or Join, I am not sure Update is found in the JMP7 Tables Menu).

Here's a script example that should work but may be ineffective for very large data tables.

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << clear select;
Summarize( g = by( :sex ), m = Max( :age ) );
For( j = 1, j <= N Items( g ), j++,
	For Each Row( If( :sex == g[j] & :age == m[j], Row State() = Selected State( 1 ) ) )
);
new-jmper

Community Trekker

Joined:

Jun 23, 2011

The script looks good. I will give it a try and report back.

What surprised me is that JMP itself does not keep the index (row number) of the data maximum somewhere.

Dong
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

For Each Row() can be inefficient for large tables.

Select Where() will probably be faster:

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << clear select;
Summarize( g = by( :age ), m = Max( :height ) );

dt<<Select Where(:height==m[loc(g,char(:age) )]);
-Jeff
ankurtangirala

Community Member

Joined:

Jul 18, 2016

Fantastic solution. This is exactly what I was looking for!

new-jmper

Community Trekker

Joined:

Jun 23, 2011

I got JMP10 and revisited this problem.  "Join" using column matching  works well.  I guess table "update" seems would be even more straightforward.

Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Rows->Row Selection->Select Dominant will select the rows with the maximum (or minimum) values in a column or group of columns.

I believe that'll get you what you're looking for.

Jeff
-Jeff
new-jmper

Community Trekker

Joined:

Jun 23, 2011

Jeff,

Thanks. This appears to be new feature from JMP 9. I am using JMP 7. I guess I may request an upgrade.

Also, I am trying to select multiple maximums in one column. The maximums belong to different groups specified in other columns.

Dong