630320_en-US

liliconcom

Add Counter for Unique Cases in Groups

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:

 

 

 

Automation and ScriptingBasic Data Analysis and ModelingRe: Add Counter for Unique Cases in Groups

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, ) 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

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

I make a new column with this formula. 

 

If( Row() == 1,

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

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

Wish is waiting for Kudos:

new JSL function: Col N Categories 

Re: Add Counter for Unique Cases in Groups

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</img> ) ) );
  N Items( Loc( bygroup[1], :a ) );
 )
);

Re: Add Counter for Unique Cases in Groups

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?
Custom Function - how to reference the column 

 

 

 

Re: Add Counter for Unique Cases in Groups

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

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

Not in clean and nice way Creating statistical custom (formula) function which has byVar ,Option or function to evaluate a custom column formula at once, or cache values between rows 

Re: Add Counter for Unique Cases in Groups

---

@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

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;&nbsp;// <&nbsp;1sec

Re: Add Counter for Unique Cases in Groups

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: