How can calculate the geometric mean of some rows?

[#1]

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.

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!

[#2 · Accepted solution]

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.

[#3]

I used the original method and the results were consistent.

Thank Jim!

[#4]

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.

[#5]

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())]))))) );

@julian 

[#6]

thank you @julian 

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

[#7]

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). 

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 

[#8]

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