cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
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 

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

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) 

 

View solution in original post

9 REPLIES 9

Re: JSL: get unique values

Thanks for your help! Summarize works fine!

Dahla

Re: JSL: get unique values

Also:

unique values = Associative Array( col ) << Get Keys
uday_guntupalli
Level VIII

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 @Mark_Bailey works well if you want to work in lists .
Best
Uday
Robbb
Level III

Re: JSL: get unique values

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?

 

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

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;
Ressel
Level VI

Re: JSL: get unique values

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.

ms
Super User (Alumni) ms
Super User (Alumni)

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) 

 

BSwid
Level IV

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!