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
lwx228
Level VIII

How can calculate the geometric mean of some rows?

The geometric mean can be easily obtained in Summary.
But how to calculate the mean by the formula column, right?
I searched the help documentation and the community and still couldn't find a way.

2019-11-16_18-43.png2019-11-16_18-45.png


I want to calculate it this way:

dt=Current Data Table();
dt<<New Column("text",formula(Geometric Mean(:height[Index(Row()-10,Row())])));
dt<<run formulas;

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: How can calculate the geometric mean of some rows?

I am not aware of a Geometric Mean function in JMP.  One should be added, and I suggest that you add that to the JMP WishList.  However, that does not mean that one can not do what you want.  I came up with 2 different solutions.  The first is just the raw coding of the Geometric Mean in JSL.  In the attached data table, the column called GeoMean uses that formula.  It uses your request, to calculate the geometric mean for the current row - 10 rows, through the current row.

If( Row() == 1,
	Product = 1;
	Range = 11;
);
If( Row() <= 11,
	Product = Product * :height;
	If( Row() == Range,
		Returned = Product ^ (1 / Range),
		Returned = .
	);
,
	Product = Product / :height[Row() - Range];
	Product = Product * :height[Row()];
	Returned = Product ^ (1 / Range);
);
Returned;

The second form, the column called Geomean from Dist Platform, extracts the Geometric Mean from the output of the Distribution Platform, run on a subset of the data, subsetting 11 rows at a time

If( Row() < 11,
	returned = .,
	theValues = :height[Index( Row(), Row() - 10 )];
	_d = New Table( "sub", invisible, New Column( "height", values( theValues ) ) );
	dis = _d << Distribution(
		invisible,
		Continuous Distribution(
			Column( :height ),
			Quantiles( 0 ),
			Histogram( 0 ),
			Vertical( 0 ),
			Outlier Box Plot( 0 ),
			Customize Summary Statistics(
				Mean( 0 ),
				Std Dev( 0 ),
				Std Err Mean( 0 ),
				Upper Mean Confidence Interval( 0 ),
				Lower Mean Confidence Interval( 0 ),
				N( 0 ),
				Geometric Mean( 1 )
			)
		)
	);
	Returned = (Report( dis )[Number Col Box( 1 )] << get)[1];
	Close( _d, nosave );
);
Returned;

The first formula should be the fastest, since it does not calculate the Geomean from scratch for each row.

 

Jim

View solution in original post

7 REPLIES 7
txnelson
Super User

Re: How can calculate the geometric mean of some rows?

I am not aware of a Geometric Mean function in JMP.  One should be added, and I suggest that you add that to the JMP WishList.  However, that does not mean that one can not do what you want.  I came up with 2 different solutions.  The first is just the raw coding of the Geometric Mean in JSL.  In the attached data table, the column called GeoMean uses that formula.  It uses your request, to calculate the geometric mean for the current row - 10 rows, through the current row.

If( Row() == 1,
	Product = 1;
	Range = 11;
);
If( Row() <= 11,
	Product = Product * :height;
	If( Row() == Range,
		Returned = Product ^ (1 / Range),
		Returned = .
	);
,
	Product = Product / :height[Row() - Range];
	Product = Product * :height[Row()];
	Returned = Product ^ (1 / Range);
);
Returned;

The second form, the column called Geomean from Dist Platform, extracts the Geometric Mean from the output of the Distribution Platform, run on a subset of the data, subsetting 11 rows at a time

If( Row() < 11,
	returned = .,
	theValues = :height[Index( Row(), Row() - 10 )];
	_d = New Table( "sub", invisible, New Column( "height", values( theValues ) ) );
	dis = _d << Distribution(
		invisible,
		Continuous Distribution(
			Column( :height ),
			Quantiles( 0 ),
			Histogram( 0 ),
			Vertical( 0 ),
			Outlier Box Plot( 0 ),
			Customize Summary Statistics(
				Mean( 0 ),
				Std Dev( 0 ),
				Std Err Mean( 0 ),
				Upper Mean Confidence Interval( 0 ),
				Lower Mean Confidence Interval( 0 ),
				N( 0 ),
				Geometric Mean( 1 )
			)
		)
	);
	Returned = (Report( dis )[Number Col Box( 1 )] << get)[1];
	Close( _d, nosave );
);
Returned;

The first formula should be the fastest, since it does not calculate the Geomean from scratch for each row.

 

Jim
lwx228
Level VIII

Re: How can calculate the geometric mean of some rows?

I used the original method and the results were consistent.

2019-11-16_21-18.png

Thank Jim!

ron_horne
Super User (Alumni)

Re: How can calculate the geometric mean of some rows?

Hi @lwx228 ,

your question is a good one and must have a few ways of getting the answer. i will try to stick to using column formula.

create two new columns.

1 just transform height to its natural log.

2 get the exponent of the moving average of the first column.

This second column should give you a moving geometric mean you are looking for.

you can take the formulas from this piece of script:

	New Column( "log height",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( Ln( :height ) )
	);
	
	New Column( "Geometric mean height",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			If( Row() < 10,
				Empty(),
				Exp( Mean( :log height[Index( Row() - 10, Row() )] ) )
			)
		)
	);
	

please let us know if it works for you.

I would also like to ask other members of the forum if there is a way to embed the first ln transformation in the second formula and thus save the 2 column procedure.

 

julian
Community Manager Community Manager

Re: How can calculate the geometric mean of some rows?

Hi @ron_horne,

To your question: "is there a way to embed the first ln transformation in the second formula and thus save the 2 column procedure."

 

At first, I thought we could do something with Transform Column(), the way we would in scripting for a platform launch, e.g.: 

Transform Column( "Log[height]", Formula( Log( :height ) ) )

 

But, embedding that in a column formula got me nowhere. However, :height[Index( Row() - 10, Row() )] will return a matrix, and wrapping that in Ln() will evaluate each item, so the following should give a single-column version of your solution:

New Column("Geometric mean height (single column)", Numeric, "Continuous", Format("Best", 12), Formula(If(Row() < 10, Empty(), Exp(Mean(Ln(:height[Index(Row() - 10, Row())]))))) );

ln.png

 

@julian 

ron_horne
Super User (Alumni)

Re: How can calculate the geometric mean of some rows?

thank you @julian 

i was attempting to do it all in one go but didn't get it to work as in this picture:

Untitled.png

Re: How can calculate the geometric mean of some rows?

A useful strategy here I think is to use the "NRows()" function in JMP to call out the indexing on the row number if you're interested in the general Geometric mean calculation that is typically reported in summary statistics (and is the one that JMP reports under Customize Summary Statistics). 

PatrickGiuliano_0-1621463270388.png

PatrickGiuliano_1-1621463411841.png

I was able to summarize (reproduce) two of the approaches used in this thread, and I am including the data table on a generic dataset for anyone's  future reference. cheers, @PatrickGiuliano 

 

Re: How can calculate the geometric mean of some rows?

Also, I'm re-linking a blog post here on the Geometric mean from the SAS documentation which is very informative:  

https://blogs.sas.com/content/iml/2019/10/02/geometric-mean-deviation-cv-sas.html