- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Select rows with maximal values in one column
Is there an easy solution? Thanks for your help!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select rows with maximal values in one column
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select rows with maximal values in one column
My first answer was based on sloppy thinking...
Message was edited by: MS
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select rows with maximal values in one column
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select rows with maximal values in one column
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 ) ) )
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select rows with maximal values in one column
What surprised me is that JMP itself does not keep the index (row number) of the data maximum somewhere.
Dong
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select rows with maximal values in one column
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) )]);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select rows with maximal values in one column
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select rows with maximal values in one column
Fantastic solution. This is exactly what I was looking for!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Select rows with maximal values in one column
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
- Report Inappropriate Content
Re: Select rows with maximal values in one column
I believe that'll get you what you're looking for.
Jeff