topic Re: Get distinct entries from a list? in Discussions

Get distinct entries from a list?

katharina_l — Wed, 04 Dec 2024 02:10:12 GMT

is there a simple way to obtain the distinct entries in a list? (similar to summary of a table by grouping column in order to get distinct entries in that column)
The result could be either a reduced list containing only the distinct entries (repeated ones removed) or just a number showing the amount of different entries.
Before writing a script I would like to check if there is a simple way / function / formula. Couldn't find anything in the JMP help ...

Re: Get distinct entries from a list?

txnelson — Wed, 04 Dec 2024 02:59:56 GMT

One way is to use an Associative Array

names default to here(1);

// Create a list
dt=
// Open Data Table: Big Class.jmp
// → Data Table( "Big Class" )
Open( "$SAMPLE_DATA/Big Class.jmp" );
genderList = :sex << get values;

// Get the distinct list of values
distinctList = (associative array(genderList))<<get keys;

{"F", "M"}

Re: Get distinct entries from a list?

katharina_l — Wed, 04 Dec 2024 04:24:40 GMT

awesome, this works! Thank you very much Jim!

Re: Get distinct entries from a list?

hogi — Wed, 04 Dec 2024 06:43:53 GMT

The approach via associative array is easy to script / remember / apply.
Take care if you want to get the distinct values for a list of mio of values.

A workaround:
save the values to a table and use either summarize or the tables/summary which you mentioned above.

Maybe, in the future, there will be a direct / fast way in JMP to calculate unique values?
I added this hope as a subtopic to
Please support the idea and vote : )

Re: Get distinct entries from a list?

katharina_l — Wed, 04 Dec 2024 07:17:08 GMT

Actually, my problem is that I want to detect unique entries in a sequence of strings in a table. Here I attached an example: Originally I have the column "sequence" (arbitrary delimiter, here the delimiter is "|). My actual workaround now is to transform this to a list and then apply Jim's solution with the associative array. But an easy way to calculate unique values would be highly appreciated ...

Re: Get distinct entries from a list?

hogi — Wed, 04 Dec 2024 08:01:04 GMT

Oh yeah, this is where the associative arrays hurt!
At the end you count the entries. Is this what you need - or do you also need the intermediate step (with the list of unique entries)?

A quick and easy way to handle unique values - let's hope that the JMP developers recognize this topic as something really useful. Such that it gets implemented in the next release ...

Re: Get distinct entries from a list?

katharina_l — Wed, 04 Dec 2024 08:09:59 GMT

For my current application, I only need the number of distinct entries, not the values themselves. But I think there might be many more situations where it would be helpful to reduce a list to its unique entries.

And yeah, let's hope for implementation in the next release :)

Re: Get distinct entries from a list?

hogi — Wed, 04 Dec 2024 12:05:19 GMT

Regarding speed, I just checked if the intermediate steps gets faster if I replace the associative array part with a simple

If(not(contains(), insert into(mylist ... - no benefit.

New Column( "unique entries",
	Expression,
	Formula(
		Local( {nr = N Rows( Current Data Table() )},
			If( Row() == nr,
				Caption( "done" )
			);
			Match( :variant,
				1, Associative Array( :list from sequence[Empty()] ) << get keys,
				2,
					myList = {};
					For Each( {entry}, :list from sequence,
						If( !Contains( myList, entry ),
							Insert Into( myList, entry )
						)
					);
					myList;
			);
		)
	)
)

for 1mio rows, I get:

So 1:0 for associative arrays. Any better idea?

[Another 2+2 seconds are needed for the columns :list from sequence and :N unique entries.
So, no real benefit to get into the ms regime for the intermediate step]

Re: Get distinct entries from a list?

hogi — Wed, 04 Dec 2024 10:30:20 GMT

- easy

- fast

and:
- not greedy

I just tried to check the timing with 10 mio rows - and got stuck.
JMP crashes and the last 60 seconds look like this:

Re: Get distinct entries from a list?

hogi — Thu, 12 Dec 2024 19:46:31 GMT

... so, I just tried it with 5 mio rows - and got an interesting insight:

When I save the table, close JMP and load the table again, the memory usage is significantly less than what is needed to calculate the values!

At first sight, I thought it's due to the Associative Array, but the same thing happens when I use mylist.

How can I prevent the column formula from eating my memory?

TS-00177710

Re: Get distinct entries from a list?

pmroz — Wed, 04 Dec 2024 15:13:33 GMT

FWIW here's a unique values function I wrote a while ago:

/*
Function Name: get_unique_values
Description: Find the unique values in a list.  Uses an associative array
Arguments:
in_list		List to get unique values for
*/
Get_unique_values = Function( {in_list},
      {Default Local}, 
      tmp = [=> 0]; 
      Insert Into( tmp, in_list ); 
      tmp << get keys; 
);

Sample calls:

a = {1, 1, 1, 2, 3, 3, 3};
b = get_unique_values(a);
print(b);

c = {"Hello", "World", "John", "Ed", "John", "World"};
d = get_unique_values(c);
print(d);

Output:

{1, 2, 3}
{"Ed", "Hello", "John", "World"}

Re: Get distinct entries from a list?

jthi — Wed, 04 Dec 2024 16:35:18 GMT

How complicate sequences do you have (are they just single letter?)? Do you know beforehand what they could potentially be? And how long can the sequence be?

For just calculating the amount of entries, you can use combination of N Items + Associative Array and Words

N Items(Associative Array(Words(:sequence, "|")))

Partially related

Re: Get distinct entries from a list?

hogi — Wed, 04 Dec 2024 17:32:00 GMT

@jthi wrote:

N Items(Associative Array(Words(:sequence, "|")))

Maybe I am wrong, but this suggestion looks very similar to what @katharina_l posted in
https://community.jmp.com/t5/Discussions/Get-distinct-entries-from-a-list/m-p/820530/highlight/true#M99918 ? ?

Re: Get distinct entries from a list?

katharina_l — Thu, 05 Dec 2024 00:33:59 GMT

thank you Holger and yes, definitely looks like the function I was looking for. Would be great if it could be implemented as a standard function in JMP.

Re: Get distinct entries from a list?

hogi — Sat, 03 May 2025 15:15:51 GMT

Col N Categories will come with JMP19.

For there is a separate wish - waiting for Kudos.