cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
KdeWitt
Level I

Iterate through rows and select matching rows script then average

Hello, 

 

I am trying to write a script that uses one column to select matching cells there and then use those selected rows to find the average of the numbers in the column next to it but am not quite sure how to do that. Here is an example table of what I am working on (the last average cloumn is what I need a script to do):

Matching Column:Numbers:Average of grouped numbers
a23
a43
b24
b44
b64
c36
c66
c96
d22
d22
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Iterate through rows and select matching rows script then average

There are several ways to approach this, but the one I find the easiest is to use

     Tables==>Summary

to do the summarization, and then to merge the summary data back into the original table.  Here is a simple script that uses your example data to do that

Names Default To Here( 1 );

// Create an example data table
dt = New Table( "Example",
	Add Rows( 10 ),
	New Column( "Matching Column",
		Character,
		"Nominal",
		Set Values( {"a", "a", "b", "b", "b", "c", "c", "c", "d", "d"} )
	),
	New Column( "Numbers",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [2, 4, 2, 4, 6, 3, 6, 9, 2, 2] )
	)
);

// Pause so one can see the original table
wait(5);

// Summarize the data
dtSum = dt << Summary(
	invisible,
	Group( :Matching Column ),
	Mean( :Numbers ),
	Freq( "None" ),
	Weight( "None" ),
	statistics column name format( "column" ),
	Link to original data table( 0 ),
	output table name( "Means" )
);

// Adjust items in summary data to allow for proper merging
dtSum << delete columns( "N Rows" );
dtSum:Numbers << Set Name( "Average of grouped numbers" );

// Add the summary data to the original data table
dt << Update( With( dtSum ), Match Columns( :Matching Column = :Matching Column ) );

// Clean up the environment
Close( dtSum, nosave );
Jim

View solution in original post

3 REPLIES 3
txnelson
Super User

Re: Iterate through rows and select matching rows script then average

There are several ways to approach this, but the one I find the easiest is to use

     Tables==>Summary

to do the summarization, and then to merge the summary data back into the original table.  Here is a simple script that uses your example data to do that

Names Default To Here( 1 );

// Create an example data table
dt = New Table( "Example",
	Add Rows( 10 ),
	New Column( "Matching Column",
		Character,
		"Nominal",
		Set Values( {"a", "a", "b", "b", "b", "c", "c", "c", "d", "d"} )
	),
	New Column( "Numbers",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [2, 4, 2, 4, 6, 3, 6, 9, 2, 2] )
	)
);

// Pause so one can see the original table
wait(5);

// Summarize the data
dtSum = dt << Summary(
	invisible,
	Group( :Matching Column ),
	Mean( :Numbers ),
	Freq( "None" ),
	Weight( "None" ),
	statistics column name format( "column" ),
	Link to original data table( 0 ),
	output table name( "Means" )
);

// Adjust items in summary data to allow for proper merging
dtSum << delete columns( "N Rows" );
dtSum:Numbers << Set Name( "Average of grouped numbers" );

// Add the summary data to the original data table
dt << Update( With( dtSum ), Match Columns( :Matching Column = :Matching Column ) );

// Clean up the environment
Close( dtSum, nosave );
Jim
KdeWitt
Level I

Re: Iterate through rows and select matching rows script then average

Yes thank you very much that worked

Jeff_Perkinson
Community Manager Community Manager

Re: Iterate through rows and select matching rows script then average

You don't need a script for this. The Col Mean() function in the Formula Editor will compute means by a grouping column.

 

I've attached a data table made from the example you posted so you can see what it looks like in JMP itself.

 

JMPScreenSnapz046.png

-Jeff