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
- :
- Discussions
- :
- Select the 10 largest values of my table

Topic Options

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

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

Jun 11, 2013 5:39 AM
(4031 views)

Hi,

I would like to extract the ten largest values of my table.

How can I do ?

Is there a script associated ?

thanks for your help !

1 ACCEPTED SOLUTION

Accepted Solutions

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

Jun 11, 2013 7:34 AM
(6238 views)
| Posted in reply to message from antoinesofradir 06/11/2013 10:20 AM

Two ways to do it:

Easy way:

dt << select where**(**:humidity >= humidity_mtx**[****10])**;

Another way:

dt << select where**(**contains**(**humidity_mtx**[****1**::**10]**, :humidity**))**;

5 REPLIES

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

Re: Select the 10 largest values of my table

Jun 11, 2013 6:25 AM
(3702 views)
| Posted in reply to message from antoinesofradir 06/11/2013 08:39 AM

Here's one way to do it using the summarize command. Summarize yields the list of unique values but they're a list of character strings.

dt = open**(**"$sample_data\bands data.jmp"**)**;

summarize**(**humidity_list = by**(**:Humidity**))**;

n = nitems**(**humidity_list**)**;

humidity_mtx = index**(1**, n**)**;

for **(**i = **1**, i <= n, i++,

humidity_mtx**[**i**]** = num**(**humidity_list**[**i**])**;

**)**;

humidity_mtx = sort descending**(**humidity_mtx**)**;

print**(**humidity_mtx**[****1**::**10])**;

**[105**, **103**, **100**, **98**, **96**, **95**, **94**, **93**, **92**, **91]**

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

Re: Select the 10 largest values of my table

Thank you !

I have my ten values in this list but i would like now to select in a table, the lines which have a value in this list :

I tried : mytable<< Select Where(value in values_mtx);

but it doesnt work

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

Jun 11, 2013 7:34 AM
(6239 views)
| Posted in reply to message from antoinesofradir 06/11/2013 10:20 AM

Two ways to do it:

Easy way:

dt << select where**(**:humidity >= humidity_mtx**[****10])**;

Another way:

dt << select where**(**contains**(**humidity_mtx**[****1**::**10]**, :humidity**))**;

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

Re: Select the 10 largest values of my table

Jun 11, 2013 9:20 AM
(3702 views)
| Posted in reply to message from antoinesofradir 06/11/2013 10:20 AM

The original question may be answered in different ways depending of the objectives.

PMroz code successfully selects all rows of the 10 top levels (which means more than ten rows selected if several rows have the same value).

If you rather want to select exactly ten rows with the highest values (and possibly leaving tied values of the lowest top-ten levels behind) this approach could be used:

dt = open**(**"$sample_data\Bands Data.jmp"**)**;

n=nrow**(**dt**)**;

dt << select rows**(**loc**(**ranking**(**:humidity**[****1**::n**])**>=n-**10****))**;

Here's an alternative that selects the percentage of rows (roughly) corresponding to the to the top ten rows, but here tied rows with the lowest value of the top 10 rows are included:

dt << select where**(**:humidity >= col quantile**(**:humidity, **1**-**10**/n**))**;

All three approaches here yields different results, but each may be right depending on the circumstances.

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

Re: Select the 10 largest values of my table

Thank you very much PMroz and MS !