topic JSL: get unique values of a data table column in Discussions

JSL: get unique values of a data table column

Wed, 26 Jun 2019 17:11:44 GMT

Hi,

I currently use the summary command to get unique values of a data table column, which in my opinion is too complicated for that simple task. I think there has to be a simpler command that works on a data table column or a vector, but I cannot find one. Am I right?

My current code looks like this:

// sample data set:
dt = New Table( "dt" );
dt:"Column 1" << setvalues( [1, 1, 2, 7, 7, 4, 5, 1, 5] );

// use summary to get unique values:
dt_sum = dt << summary( group( :Column 1 ), N, outputtablename( "dt_sum" ) );
unique_values = dt_sum:"Column 1" << getvalues;

Dahla

Re: JSL: get unique values

Fri, 04 Dec 2009 11:58:12 GMT

Thanks for your help! Summarize works fine!

Dahla

Re: JSL: get unique values

Mark_Bailey — Fri, 15 Sep 2017 18:05:59 GMT

Also:

unique values = Associative Array( col ) << Get Keys

Re: JSL: get unique values

uday_guntupalli — Fri, 15 Sep 2017 19:50:59 GMT

Summarize works well if you want to keep the data in a data table format .

Associative Array as suggested by @Mark_Bailey works well if you want to work in lists .

Re: JSL: get unique values of a data table column

ms — Fri, 06 Oct 2017 14:09:13 GMT

Try summarize...

Summarize can do more...see the scripting docs.

A couple of downsides.

Even though the column summarized may be numeric, the list returned contains character values.
Related to 1, the precision of numeric values gets truncated based on the column width.

// sample data set:
dt = newtable("dt");
dt:"Column 1" << setvalues([1, 1, 2, 7, 7, 4, 5, 1, 5]);  summarize(unique_values=by(Column 1));
show(unique_values)

But ... here's a workaround to the downsides:

summarize(unique_values=by(Column 1), unique_numeric_values=(max(Column 1)));
show(unique_values, unique_numeric_values)

Re: JSL: get unique values of a data table column

BSwid — Mon, 01 Apr 2019 19:48:09 GMT

Proposing a Summary solution for JMP users on the beginner (my) side of the spectrum.

In this example we want a list of unique finished goods from a list of customer orders. I won't link the table and I'll give the Output Table a name.

On the original data table go to Tables -> Summary

Drag Finished Good from the select columns over to Group on the right.
Give the Output table a name, in this case UniqueFG.

Uncheck Link to original data table.

Click Action OK.

The resulting data table is a list of all unique FG and the count of rows.
Click edit Source script.

It will look something like this.

Data Table( "Test" ) <<
Summary(
	Group( :Finished Good ),
	Freq( "None" ),
	Weight( "None" ),
	Link to original data table( 0 ),
	output table name( "UniqueFG" )
)

Copy that code.

Create a new script in the original data table and paste in the code. I've modified the code slightly to make it more flexible/scalable and commented in some notes below.

//Creates a list of unique FG #s to feed to SAP to get unit weights and carton quantities
Names Default to Here(1);
dt = current data table();

dt <<
Summary(
	Group( :Finished Good ),  //this is the column from which you want unique values
	Freq( "None" ),
	Weight( "None" ),
	Link to original data table( 0 ), //this turns off table linking
	output table name( "UniqueFG" )  // this is the name of the new datatable
)

Save.

Feel free to let me know if this is/isn't a good approach or code structure. Thanks!

Re: JSL: get unique values

Robbb — Wed, 26 Jun 2019 06:19:48 GMT

Thanks for this wonderful and short solution @Mark_Bailey .

It most often works but not in the following case:

values = {0.111, 0.222, 0.333}; // This is actually something like: values = Column(SomeList[1]) << Get Keys
N Items(Associative Array(values)) // gives 1

JMP ignores the decimal places when creating the associative array. (Using JMP 12)

How can I fix that using accociative arrays so that N Items returns 3 instead of 1?

Re: JSL: get unique values

txnelson — Wed, 26 Jun 2019 14:49:04 GMT

You could try Summarize(), and then simply loop through the list that is returned and change the string values back to numeric by using the num() function.
You could create a new column where you use a formula
:variable * 100
and then convert the data as it comes out of the Associative Array
You could create a new column, converting the values to character

Re: JSL: get unique values

Mark_Bailey — Wed, 26 Jun 2019 16:59:05 GMT

Study this example:

Names Default to Here( 1 );

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

values = dt:age << Get Values;

array = Associative Array( values );

array << Get Keys;

Re: JSL: get unique values

Ressel — Tue, 16 Nov 2021 15:07:37 GMT

Hi, what would be the approach for using the keys in the array for selecting or filtering rows with the same keys, but in a different JMP table. I have two tables. One of them maps sample IDs for chemical testing to different factor settings (temperature, time, study nr., etc.). I am successfully getting the sample IDs in an array. (Sorry, I'm a total newbie to this.)

smplID = Associative array(:sampleid) << Get Keys;

Now I want to use these keys to select rows in the second table (where my responses are), invert the row selection and delete the rows. Perhaps the solution below, as described here, is better suited to start with?

Summarize(dt, smpleID = by(:sampleid));

Or maybe this approach is preferable?

dt << Summary (Group ( :sampleid ), Frequ( "None" ), Weight( "None" );

Anyway, what I really want to know is, how I may use any of these approaches to effectively cleanse a data table for rows I don't need, before I proceed any further with data analysis.

Edit: discussed & solved here.