topic Re: Add Counter for Unique Cases in Groups in Discussions

Add Counter for Unique Cases in Groups

StumptownSlash — Wed, 10 May 2023 04:51:51 GMT

Hi all,

How can I write an integer value counter to increment unique values in column B within group A, to do this?

Here is what I have tried so far:

dt_Table1 = Data Table("Table1");
dt_Table1 << New Column("Dataset", Numeric, "Nominal", Set Selected);

Sum1 = dt_Table1 << Summary(Group(:Group, :Date));
Sum2 = Sum1 << Summary(Group(:Group));
:N Rows << Set Name("N Datasets in Group");

dt_Table1 << Update(
	with(Data Table(Sum2)),
	Merge Same Name Columns
Match Flag(0),
	Suppress foruma evaluation(0),
	By Matching Columns(:Group = :Group),
	Drop multiples(1, 0),
	Include Nonmatches(0, 0),
	Preserve main table order(1)
);

dt = dt_Table1;
For(i = 1, i <= :N Rows(dt), i++,
	While(Group_Number = GetValue(:Group[i]),
		N == Get Value(dt, :N Datasets in Group[i]),
		For(j = 1, j <= N, j++,
			:Dataset << Set Value == (j),

		),
		Group_Number = Group_Number + 1,

	),

);

Outputs:

Re: Add Counter for Unique Cases in Groups

jthi — Wed, 10 May 2023 05:02:38 GMT

You should be able to do this with some combination of Col Rank(), Col Cumulative Sum, Col Min() and so on functions. Based on your example, Col Rank() or Col Cumulative Sum(1, <byvars>) should be enough (there are no duplicate dates in any of the groups).

Column 3 is using Col Rank and Column 4 Col Cumulative Sum and Column 5 one option of a bit more complicated formula

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(8),
	Compress File When Saved(1),
	New Column("g", Numeric, "Continuous", Format("Best", 12), Set Values([1, 1, 1, 1, 2, 2, 2, 2])),
	New Column("d", Numeric, "Continuous", Format("Best", 12), Set Values([1, 2, 3, 4, 1, 2, 2, 3])),
	New Column("r", Numeric, "Continuous", Format("Best", 12), Set Values([1, 2, 3, 4, 1, 2, 2, 3])),
	New Column("Column 3", Numeric, "Continuous", Format("Best", 12), Formula(Col Rank(:d, :g))),
	New Column("Column 4", Numeric, "Continuous", Format("Best", 12), Formula(Col Cumulative Sum(1, :g)))
);

dt << New Column("Column 5", Numeric, Continuous, Formula(
	Col Cumulative Sum(
		If(Row() == Col Min(Row(), :g, :d),
			1
		,
			.
		),
		:g
	)
));

Re: Add Counter for Unique Cases in Groups

StumptownSlash — Wed, 10 May 2023 16:30:46 GMT

Thanks Jarmo! Your way is definitely cleaner. I don't totally follow the logic, but I see it works!

Note: I found another way, that also used Col Cumulative Sum(). Slower than yours. Multiple sub-tables and a join...

Summary1 = Table1 << Summary(Group(:Group, :Date));
Summary2 = Summary1 << Summary(Group(:Group, :Date));
Summary2 << New Column( "Dataset",
Numeric,
"Nominal",
Set Selected
);
:Dataset << Formula(Col Cumulative Sum( :N Rows, :Group ) );

//Join to Table 1

Table1 << Join(
with ( Data Table( Summary2 ) ),
Select(all),
SelectWith(
:Dataset
),
By Matching Columns( :Run = :Run ),
Drop multiples( 1, 0 ),
Include Nonmatches( 0, 0 ),
Preserve main table order( 1 ),
Output Table Name( "Table2" )
);

Re: Add Counter for Unique Cases in Groups

Byron_JMP — Mon, 15 May 2023 18:49:21 GMT

I make a new column with this formula.

If( Row() == 1,

If( :ID == Lag( :ID, 1 ),

Lag( :Name( "Count of ID" ) ) + 1,1))

This assumes your "ID" column is sorted sequentially.

the Count of ID, column is the name of the new column with the above formula

Re: Add Counter for Unique Cases in Groups

hogi — Sat, 28 Oct 2023 20:31:58 GMT

Hm, seems that Formula editor is missing a Col N Categories function with a GroupBy option.

Wish is waiting for Kudos:

Re: Add Counter for Unique Cases in Groups

txnelson — Sun, 29 Oct 2023 15:59:43 GMT

Here is a little script that produces the number of unique values found in column B, within column A

Names Default To Here( 1 );
dt = new table("Example",
New Column("A", character),
	New Column("B", character)
);
charList = {"A","B","C","D","E","F","G","H","I","J"};
dt << add rows(25);
for each row(
	:A = char(random integer(1,5));
	:B = charList[random integer(1,10)]
);

dt << New Column( "count",
	set each value(
		As Constant( Summarize( dt, bygroup = by( :a, :p ) ) );
		N Items( Loc( bygroup[1], :a ) );
	)
);

Re: Add Counter for Unique Cases in Groups

hogi — Sun, 29 Oct 2023 16:57:27 GMT

nice workaround :)

(p -> b)

but the missing Col N Categories still hurts. Can one add a custom function Col N Categories (: col, <{...}>)

the optional list could be used to specify columns/subgroups used as by - like the :a in the previous example.

Who knows how in the summarize function the columns can be referenced?

Re: Add Counter for Unique Cases in Groups

jthi — Sun, 29 Oct 2023 18:31:38 GMT

Usually some weird combination of different Col something() functions can also be used (might not be the fastest due to the comparison done and if I remember correctly you shouldn't use Col functions this way even though it is one of the best ways of using them).

dt << New Column("C", Formula(
	Col Sum(Row() == Col Min(Row(), :A, :B), :A);	
));

Re: Add Counter for Unique Cases in Groups

jthi — Sun, 29 Oct 2023 18:37:10 GMT

Can one add a custom function Col N Categories (: col, <{...}>)

Not in clean and nice way ,

Re: Add Counter for Unique Cases in Groups

hogi — Sun, 29 Oct 2023 21:59:18 GMT

@jthi wrote:
Not in clean and nice way

Ouch!

I wanted to add the custom Col N Categories function to have it available in the right click New Formula Column menu.

So, if it requires additional Expr() Exrepssions around the columns when calling the function, there is NO chance to get this right?

Argh ....

Really astonishing that it takes so long to add the missing Col N Categories :(

Re: Add Counter for Unique Cases in Groups

hogi — Sun, 29 Oct 2023 22:30:25 GMT

amazing!
Depending on the structure of the table, many orders of magnitude faster :)

but still far away from a solution for the right click New Formula Column menu :(

Names Default To Here( 1 );
dt = new table("Example",
New Column("A", character),
	New Column("B", character)
);
dt << add rows(20000);
for each row(
	:A = char(random integer(1,5));
	:B = char(random integer(1,1000));
);

t0 = hptime();
dt << New Column( "count",
	set each value(
		As Constant( Summarize( dt, bygroup = by( :a, :b) ) );
		N Items( Loc( bygroup[1], :a ) );
	)
);
(hptime() -t0)/1000000; //~ 1min

t0 = hptime();
dt << New Column("C", set each value(
	Col Sum(Row() == Col Min(Row(), :A, :B), :A);	
));
(hptime() -t0)/1000000; // < 1sec

Re: Add Counter for Unique Cases in Groups

hogi — Sat, 03 May 2025 15:01:19 GMT

happy news: JMP19 will provide Col N Unique():
https://community.jmp.com/t5/JMP-Wish-List/Col-N-Categories/idc-p/870972/highlight/true#M7292
and it will be available via the new formula column menu: