cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Try the Materials Informatics Toolkit, which is designed to easily handle SMILES data. This and other helpful add-ins are available in the JMP® Marketplace
Choose Language Hide Translation Bar
JMP2021
Level III

How to create a new column with numerical number based on the unique grouping of column A

I have a table with Column A (char). I want to add a new column in number value which represents the grouping of column A. How to do this automatically? Column A has too many characters for me to manually group them.

 

column Anew column
kkk1
dd2
kkk1
cc3
3 ACCEPTED SOLUTIONS

Accepted Solutions
WebDesignesCrow
Super User

Re: How to create a new column with numerical number based on the unique grouping of column A

May be can use Cols --> Utilities --> Labels to Code (it automatically assign a code based alphabetical order)?

WebDesignesCrow_0-1689299256039.png

 

Then you create new column using below formula;

:new column << Set Formula( Col Stored Value( :Column A ) );

 

 

 

 

View solution in original post

JMP2021
Level III

Re: How to create a new column with numerical number based on the unique grouping of column A

thank you. It worked.

 

View solution in original post

JMP2021
Level III

Re: How to create a new column with numerical number based on the unique grouping of column A

one more question is that the code is sequential given. I wonder if there is a way to assign the number based on the highest occurrence in column A.

 

View solution in original post

4 REPLIES 4
WebDesignesCrow
Super User

Re: How to create a new column with numerical number based on the unique grouping of column A

May be can use Cols --> Utilities --> Labels to Code (it automatically assign a code based alphabetical order)?

WebDesignesCrow_0-1689299256039.png

 

Then you create new column using below formula;

:new column << Set Formula( Col Stored Value( :Column A ) );

 

 

 

 

JMP2021
Level III

Re: How to create a new column with numerical number based on the unique grouping of column A

thank you. It worked.

 

JMP2021
Level III

Re: How to create a new column with numerical number based on the unique grouping of column A

one more question is that the code is sequential given. I wonder if there is a way to assign the number based on the highest occurrence in column A.

 

WebDesignesCrow
Super User

Re: How to create a new column with numerical number based on the unique grouping of column A

to assign the number based on highest occurrence in column A need some work around.

1. create a summary table by group, "Column A" --> sort N column "Descending"

2. add formula column: new result = Row ();

3. join both tables or update main table to match "Column A".

WebDesignesCrow_0-1689562591289.png

 

If you're using scripting, it needs few lines as below;

// → Data Table( "Untitled" )
New Table( "Untitled" );

// Data table summary
// → Data Table( "Untitled By (Column A)" )
Data Table( "Untitled" ) << Summary(
Group( :Column A ),
Freq( "None" ),
Weight( "None" )
);

// Sort data table
Data Table( "Untitled By (Column A)" ) << Sort(
	By( :N Rows ),
	Replace Table,
	Order( Descending )
);

// New column: new result
Data Table( "Untitled By (Column A)" ) << New Column( "new result",
	Numeric,
	"Continuous",
	Format( "Best", 12 ), 
        Formula( Row() )
);

// Update data tables
Data Table( "Untitled" ) << Update(
	With( Data Table( "Untitled By (Column A)" ) ),
	Match Columns( :Column A = :Column A )
);

hope it helps.