Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Select rows with maximal values in one column

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 11, 2011 7:11 PM
(13238 views)

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!

Is there an easy solution? Thanks for your help!

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Aug 1, 2013 1:00 PM
| Last Modified: Oct 4, 2017 1:08 PM
(13512 views)
| Posted in reply to message from new-jmper 05-11-2011

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

12 REPLIES 12

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Select rows with maximal values in one column

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

My first answer was based on sloppy thinking...

Message was edited by: MS

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Select rows with maximal values in one column

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

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Select rows with maximal values in one column

Created:
May 12, 2011 1:38 PM
| Last Modified: Oct 4, 2017 1:09 PM
(11569 views)
| Posted in reply to message from new-jmper 05-12-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 ) ) )
);
```

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Select rows with maximal values in one column

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

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

Dong

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Select rows with maximal values in one column

Created:
May 13, 2011 11:28 AM
| Last Modified: Oct 4, 2017 1:09 PM
(11569 views)
| Posted in reply to message from new-jmper 05-13-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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Select rows with maximal values in one column

Created:
Aug 3, 2020 9:52 AM
| Last Modified: Aug 3, 2020 10:14 AM
(40 views)
| Posted in reply to message from Jeff_Perkinson 05-13-2011

Hello, sorry to reply to an old thread, but I cannot find relevant examples elsewhere.

I would like to summarize by group, then select those rows that have max weight by group.

I was able to Summarize, but I don't know how to select those rows.

```
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << clear select;
Summarize( g = by( :age, :sex ), m = Max( :weight ) );
/* How to select those rows summarized by group?
```

Thank you

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Select rows with maximal values in one column

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

Highlighted
##

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Select rows with maximal values in one column

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Select rows with maximal values in one column

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

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

Jeff

-Jeff

Article Labels

There are no labels assigned to this post.