BookmarkSubscribe
Choose Language Hide Translation Bar

JSL: get unique values of a data table column

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 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User ms
Super User

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

Try summarize... 

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

 

A couple of downsides.

  1. Even though the column summarized may be numeric, the list returned contains character values. 
  2. 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) 

 

0 Kudos
8 REPLIES 8

Re: JSL: get unique values

Thanks for your help! Summarize works fine!

Dahla
0 Kudos

Re: JSL: get unique values

Also:

unique values = Associative Array( col ) << Get Keys
Learn it once, use it forever!
uday_guntupalli
Community Trekker

Re: JSL: get unique values

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

Associative Array as suggested by @markbailey works well if you want to work in lists .
Best
Uday
0 Kudos
Robbb
Senior Member

Re: JSL: get unique values

Thanks for this wonderful and short solution @markbailey .

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?

 

0 Kudos
txnelson
Super User

Re: JSL: get unique values

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
Jim
0 Kudos

Re: JSL: get unique values

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;
Learn it once, use it forever!
ms
Super User ms
Super User

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

Try summarize... 

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

 

A couple of downsides.

  1. Even though the column summarized may be numeric, the list returned contains character values. 
  2. 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) 

 

0 Kudos
BSwid
Community Trekker

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

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

JMP Community UniqueList1.png

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.

JMP Community UniqueList2.png

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!

 

 

0 Kudos