Choose Language Hide Translation Bar
Highlighted

## 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 a 2 3 a 4 3 b 2 4 b 4 4 b 6 4 c 3 6 c 6 6 c 9 6 d 2 2 d 2 2
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

## 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",
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
3 REPLIES 3
Highlighted

## 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",
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
Highlighted

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

Yes thank you very much that worked

Highlighted  Jeff_Perkinson 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. -Jeff
Article Labels

There are no labels assigned to this post.