Subscribe Bookmark RSS Feed

Geometric Mean within a Summary Script

melanie_william

Community Trekker

Joined:

May 5, 2014

I am trying to find the geometric mean of fecal bacteria for about 15 years of data over roughly 30 to 40 monitoring stations.  The goal of my script is to conduct two filters (one on the Project ID column and another on the Characteristic Name column), then group the geo means by station then by year, and in the final table show the Station ID, Year, Characteristic Name, N, geo mean, and the Median. 

I know now that I can't include the script for finding the geo mean in the Summary part of the script but I haven't been able to figure how where in this script I should place it.  I'm not sure if I need to completely restructure the script or need to go about this in a totally different way. 

Any advice or suggestions would be greatly appreciated!

myPath = "O:/Planning/BPB/IBP_Pilot_Files/JMPScrips/Geomean/";

dt = Data Table( "Data_dwq_20140502_133555_RegRes_For_Testing.jmp" );

dt << Select Where (:Project ID == "NCAMBNT" |:Project ID == "NCISCO" |:Project ID == "TPBA");

dt << Subset((Selected Rows), Output table name ("subset"));

dt = data table ("subset");

dt << Select Where (:Characteristic Name == "Fecal Coliform" | :Characteristic Name == "Total Coliform");

dt << Subset((Selected Rows), Output table name ("subset2"));

dt = data table ("subset2");

dtsum=dt << Summary(

  Group( :Station ID, :Year, :Characteristic Name),

  N( :Result Value as Number ),

  Exp(Col Mean(Log(:Result Value as Number))),

  Median( :Result Value as Number )

);

1 REPLY
brady_brady

Staff

Joined:

Jun 9, 2012

Hi Melanie.

Here is one way: once you have your data ready to summarize, create a log column for the desired variable. Then use Summary to find the group means of this log data. Then exponentiate the results and place them in a new column.

Here is an example:

dt = open("$Sample_Data/Car Physical Data.jmp");

dt << new column("log_weight", formula(log(:weight)));

dtSum = (dt << summary(mean(:log_weight), group(:Country, :type)));

dtsum << new column("Geom_mean_weight", formula(exp(:name("Mean(log_weight)"))));

Cheers,

Brady