topic Looping through rows of a column to find the overall count in Discussions

Looping through rows of a column to find the overall count

PredictorRoot52 — Mon, 04 Sep 2023 08:19:21 GMT

Trying to find the number of counts of current entry in a column. The below function works but seems to be slow. Any suggestions to improve the script run time?

JMP14, JMP16

My proposal: I think initially constructing Associative Array by adding the entries from the :ColName as "Key" and increasing the value (Value++) when duplicate entry i.e. duplicate Key or if it's a new entry, adding a separate Key with value set to 1 would be a better way. Then you can just loop through rows to find the number of "Value" for it.

Re: Looping through rows of a column to find the overall count

jthi — Mon, 04 Sep 2023 09:11:30 GMT

Is there a specific reason why you wish to loop over the rows? You could most likely use Tabulate or Summary table to perform similar task

Summary + Stack table

Re: Looping through rows of a column to find the overall count

txnelson — Mon, 04 Sep 2023 20:40:05 GMT

Here are a couple of ways to do what I believe you want.

Names Default To Here( 1 );

dt = Current Data Table();

Summarize( dt, items = by( :colName ) );
countOfUniqueValues = N Items( items );

// or

countOfUniqueValues = N Items( Associative Array( :colName ) << get keys );

Re: Looping through rows of a column to find the overall count

PredictorRoot52 — Mon, 11 Sep 2023 21:00:51 GMT

I am summarizing (dt_summary) the bigger data table (dt) which gives me the number of rows for each particular entry in the column: ColName.

dt_summary = dt << Summary( Group( :ColName ), Freq( "None" ), Weight( "None" ) );

Now I want to iterate through the original table (dt) rows and get the number of rows from summary table for current row to check if above 10 (occurrences).

Summary Table (group by height):

So let's say I am on first row, the height is 59 and occurrences are 2, I want to populate that information in the current data table (dt) in a new column (height_qty).

Re: Looping through rows of a column to find the overall count

txnelson — Mon, 11 Sep 2023 21:38:16 GMT

The simplest solution is to simply merge the summary table into the original table using the

Tables => Update

platform

names default to here(1);
dt =

// Open Data Table: Big Class.jmp
// → Data Table( "Big Class" )
Open( "$SAMPLE_DATA/Big Class.jmp" );

dt_summary = dt << Summary( invisible, Group( :height ), Freq( "None" ), Weight( "None" ) );
dt_summary:N Rows << set name("height_qty");

dt << Update(
	With( dt_summary ),
	Match Columns( :height = :height )
);

close( dt_summary, nosave );

Re: Looping through rows of a column to find the overall count

PredictorRoot52 — Mon, 11 Sep 2023 23:37:13 GMT

Really appreciate your quick help, Jim. It worked as expected and the run time reduced to ~2 seconds from almost a minute.