topic Re: How Do I create a Column formula for N Categories? in Discussions

How Do I create a Column formula for N Categories?

trish_roth — Wed, 18 Nov 2020 00:36:45 GMT

I would like to create a column formula that provides a count of the number of distinct values in a column. I can use the Column viewer, Show summary function in JMP 15 but I want to have that value in a column for use in other calculations.

For example in the Big Class Table - N Items in column "Sex" should show 2 as 2 distinct values.

Re: How Do I create a Column formula for N Categories?

txnelson — Wed, 18 Nov 2020 14:28:37 GMT

Here is the formula that I would use:

If( Row() == 1,
	Summarize( Current Data Table(), bygp = by( :sex ) );
	n = N Items( bygp );
);
n;

Re: How Do I create a Column formula for N Categories?

PatrickGiuliano — Thu, 19 Nov 2020 02:32:36 GMT

Thanks @txnelson you're the man!

@trish_roth and I also came up with the following ode to the gracious support from some of the other JMP team members.

Summarize( x = by( :sex ) );
N Items( x );

very much the same but a few less lines.

Re: How Do I create a Column formula for N Categories?

CTrahon0526 — Thu, 30 Nov 2023 17:47:29 GMT

@PatrickGiuliano ,

Is there a way to get the N Items by Variable?

I am trying to find the unique N values per Year per Work Week. Thanks.

Re: How Do I create a Column formula for N Categories?

txnelson — Thu, 30 Nov 2023 18:07:53 GMT

The function is

Col Number(<:column Name>)

See the Scripting Index for details

Re: How Do I create a Column formula for N Categories?

PatrickGiuliano — Thu, 30 Nov 2023 20:22:40 GMT

Thank you Jim!

@CTrahon0526 If you're new to JMP, here's a bit more elaboration on what Jim shared.

You can put the Col Number() function in a column formula and compute it directly in the data table.

I'm attaching a mock example jmp data table.

First, I created a "Year Week" column by using an Instant Column Formula in JMP. Right Click on the time column, New Formula Column > Date Time > Year Week.

Then I created a "Count by Year Week" column with the following column formula in it:

Col Number(:time, :Year Week)

Re: How Do I create a Column formula for N Categories?

CTrahon0526 — Fri, 01 Dec 2023 02:33:46 GMT

Thank you, @txnelson & @PatrickGiuliano .

Fairly new to JMP, still learning. Good to know about the Col Number() operator.

I have attached a sample of my data set. I am looking for the unique number of tools per week per year. Each tool could have multiple rows per week. I put an ideal outcome column in the set.

Thanks again for your help!

Re: How Do I create a Column formula for N Categories?

txnelson — Fri, 01 Dec 2023 03:17:27 GMT

I would not do what you are asking for in a formula. Rather, I would:

Click on Tables=>Summary and create a summary data table, grouping by Year, WW and ToolID
Using the newly created summary data table, once again, click on Tables=>Summary and create a new summary data table by grouping on just Year and WW. The new table's column called # Rows will give you the number of unique toolIDs per year/WW.
Next, click on the original data table, and then using the Tables pull down menu once again, select Update. This will copy the data from the second summary table into the original table. In the dialog box for the Update,
1. Select the table to update from(the second summary table)
2. Select to Match Columns
3. Select Year from each table and click on Match
4. Select WW from each table and click on Match
5. Click on OK

The data will now be in your original data table

Re: How Do I create a Column formula for N Categories?

PatrickGiuliano — Fri, 01 Dec 2023 03:51:01 GMT

@CTrahon0526, Jim's solution is beautiful! And it teaches you a lot about Table operations in JMP. I agree that a Column formula in this case is very tricky. Tables Summary 2X is a very elegant solution. Then "updating back" the results to the original data table using Tables > Update. That last step can kind be done interactively (by literally dragging and dropping the column from the second summary table back to the first, and finally to the main table) instead of using Tables > Update, but the solution using Update is more robust (reproducible) if you are going to be doing this calculation on many tables. But this kind of gives you a sense of how interactive JMP is as GUI-based tool.

(view in My Videos)

Re: How Do I create a Column formula for N Categories?

hogi — Sat, 03 Aug 2024 13:58:44 GMT

The drag & drop is very cool.

Nevertheless, there is a HUGE disadvantage o this approach:
it's not dynamic. If data is added to the table, the entries will be wrong.

This is THE benefit of a column formula: The values will be always correct.

Astonishing that there is no New Column Formula / JSL command Col N Categories ( ) available in JMP.

Re: How Do I create a Column formula for N Categories?

hogi — Sat, 03 Aug 2024 14:01:41 GMT

here is a wish to add such a functionality
... to JSL
... to New Column Formula
... to Graph Builder

I guess there are only a few JMP users in the world who don't need it ; )
Most JMP users will be excited to see such a functionality in a future version of JMP (... along with )

Re: How Do I create a Column formula for N Categories?

hogi — Sat, 24 Aug 2024 13:46:00 GMT

[removed] didn't show up at the right position.

Re: How Do I create a Column formula for N Categories?

wgardnerQS — Wed, 11 Dec 2024 19:11:40 GMT

A little late to the thread, but I was looking for a solution to the same problem. The formula below in @CTrahon0526 's datatable reproduces the values in "Unique Tool IDs Per Work Week Per Year" column

Col Sum( Col Cumulative Sum( 1, :Year, :WW, :Tool ID ) == 1, :Year, :WW )

Re: How Do I create a Column formula for N Categories?

hogi — Wed, 11 Dec 2024 21:19:02 GMT

Nice, I can use this a lot - so, now I have:

Names Default To Here( 1 );
Try(dt = CurrentDataTable(), stop());


updateLBox = Function( {t},
	(t << sib) << setitems( (t << topparent)[listboxbox( 1 )] << getselected )
);

nw = New Window("count unique entries",
<< Type("Modal Dialog"),
	Border Box( Left( 10 ), Right( 10 ), bottom( 5 ),
		V List Box(
			Lineup Box( N Col( 1 ), spacing( 10 ),
				// Text Box( "XG Boost Specification" ), 
				// colistbox only necessary if the script is standalone
				H List Box(
					Panel Box( "Select Columns", Col List Box( all, nlines( 15 ) ) ),
					Panel Box( "Cast Selected Columns into Roles",
						Lineup Box( N Col( 2 ),
							Button Box("Unique",
								<<setfunction( Function( {t}, updateLBox( t ) ) )
							),
							Cunique = Col List Box( nlines( 2 ), minitems( 1 ) ),
							Button Box("By Group",
								<<setfunction( Function( {t}, updateLBox( t ) ) )
							),
							CBy = Col List Box( nlines( 2 ), minitems( 0 ) )
						)
					)
				)
			),
			spacerbox(size(1,10)),
			Button Box( "OK",
				<<setfunction(
					Function({t}, ColsUnique = Cunique << get items;ColsBy= Cby << get items)
				)
			),
			spacerbox(size(1,10)),
		)
	)
);

ccs= Expr(Col Cumulative Sum( 1));
for each ({col}, ColsUnique, Insert Into(ccs, Name Expr(As column(col))));
for each ({col}, ColsBy, Insert Into(ccs, Name Expr(As column(col))));
cs = Eval Expr(Col Sum( Expr(Eval Expr(Expr(Name Expr(ccs)) == 1 ))));
for each ({col}, ColsBy, Insert Into(cs, Name Expr(As column(col))));

Eval (Eval Expr(new column ("unique_"|| concat items (ColsUnique,"_"),
	Formula(Expr(Name Expr(cs))))))

... available at a single mouse click : )

Re: How Do I create a Column formula for N Categories?

hogi — Wed, 25 Dec 2024 19:26:07 GMT

to get the next level of interactivity - please vote here:

e.g. to show the number of car makers - correct values for individual years - and split by door size:

(view in My Videos)

... there may be more useful applications : )