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!
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.

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:

-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