topic Re: Count the Most Frequently Occurring Item in a Column in Discussions

Count the Most Frequently Occurring Item in a Column

SpannerHead — Thu, 24 Apr 2025 14:03:42 GMT

I want to readily identify the item that most frequently occurs by row count in a JMP column. I can easily determine the unique entries, how best can I get a count from there?

dt = Current Data Table();
summarize( lotz = by(:LOT));

Re: Count the Most Frequently Occurring Item in a Column

mmarchandFSLR — Thu, 24 Apr 2025 14:24:09 GMT

This takes into account the possibility of a tie for most frequent:

Names Default To Here( 1 );
dt = Data Table( "Big Class" );
dt << Add Rows( {:name = "BARBARA", :age = 13, :sex = "F", :height = 100, :weight = 79} );
Summarize( namez = By( :name ) );
numberz = Transform Each( {v, i}, namez, Length( Where( dt, :name == v ) ) );
max_num = Max( numberz );	//2
big_name = namez[Where( numberz == max_num )];	//{"BARBARA", "ROBERT"}

Re: Count the Most Frequently Occurring Item in a Column

txnelson — Thu, 24 Apr 2025 15:30:09 GMT

The Distribution Platform will give you a Mode.

It is under the red triangle for the Summary Statistics Paragraph. Just select

Customize Summary Statistics

and then select the additional statistics you want displayed

Re: Count the Most Frequently Occurring Item in a Column

hogi — Thu, 24 Apr 2025 16:05:51 GMT

If you prefer a column formula:

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class Families.jmp" );
New Column( "N by age",
	Formula( Col Number( 1, :age ) )
)

and to identify the winner:

New Column( "max N by age",
	Formula( Col Number( 1, :age ) == Col Max( Col Number( 1, :age ) ) )
)

Alternatively, you can use Summarize:

Summarize( namez = By( :age ), cnt = count(:age) );

or tables/summary:

Data Table( "Big Class Families" ) << Summary(
	Group( :age )
);

Re: Count the Most Frequently Occurring Item in a Column

SpannerHead — Thu, 24 Apr 2025 18:36:13 GMT

This is a good looking script but for some reason, it gives numberz as 5 for everything.

Thanks

Re: Count the Most Frequently Occurring Item in a Column

SpannerHead — Thu, 24 Apr 2025 18:43:55 GMT

The summarize suggestion is a good one. It gives me 2 dissociated lists. If I could somehow get that to be an associative array, I could use the max "cnt" value to identify the associated "namez" value.

Re: Count the Most Frequently Occurring Item in a Column

SpannerHead — Thu, 24 Apr 2025 19:07:18 GMT

I did this and it seems to work.

Summarize( namez = By( :LOT ), cnt = count(:LOT) );

keys = namez;
values = Eval List(cnt);

AA = Associative Array(keys, values);

For( g = 1, g <= N Items( AA ), g++,
    If(values[g] == Max(cnt), mainLOT = keys[g]));

Re: Count the Most Frequently Occurring Item in a Column

jthi — Thu, 24 Apr 2025 19:33:31 GMT

Summary Platform is what I generally use for this (how I use it depends on the use case: ties, do I need the count, how should missing values be handled and so on)

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");

dt_summary = dt << Summary(
	Group(:sex),
	Freq("None"),
	Weight("None"),
	output table name("res"),
	Private
);

// Using Loc Max
max_row = Loc Max(dt_summary[0, "N Rows"]);
max_val = dt_summary[max_row, 1];
show(max_val);

// Sorting
dt_summary << Sort(By(:N Rows), Replace Table, Order(Descending));
my_val = dt_summary[1, 1];
show(my_val);

Close(dt_summary, no save);

Also just using Mode function might be enough

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
val = Mode(dt[0, "sex"]);

And there are plenty of other options (like you can see in this thread already).

Re: Count the Most Frequently Occurring Item in a Column

SpannerHead — Thu, 24 Apr 2025 20:38:16 GMT

Jarmo

Mighty! This does the trick and it includes non numeric values. The only refinement I need is to have it ignore missing data somehow?

dt = Current Data Table();
val = Mode(dt[0, "process"]);

Re: Count the Most Frequently Occurring Item in a Column

SpannerHead — Thu, 24 Apr 2025 21:46:20 GMT

I made this tweak and I appear to be successful.

dt = Current Data Table();

full_proc_rows = dt << get rows where( !Is Missing( :process ) );

proc_val = Mode(dt[full_proc_rows, "process"]);