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

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

JMP2021 — Thu, 13 Jul 2023 20:57:36 GMT

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 A	new column
kkk	1
dd	2
kkk	1
cc	3

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

WebDesignesCrow — Fri, 14 Jul 2023 01:48:15 GMT

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

Then you create new column using below formula;

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

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

JMP2021 — Fri, 14 Jul 2023 03:49:33 GMT

thank you. It worked.

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

JMP2021 — Fri, 14 Jul 2023 03:58:07 GMT

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.

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

WebDesignesCrow — Mon, 17 Jul 2023 03:03:07 GMT

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".

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.