topic Re: Populate Summary table with Main & StdDev confidence intervals in Discussions

Populate Summary table with Main & StdDev confidence intervals

Voizingu — Tue, 28 May 2024 03:41:38 GMT

Hello,

I am trying to generate a Summary table from a main datatable.

So far I was able to put all metrics in Column 1 with grouping (column 2).

I could then populate with Count, Mean, StdDev, +4sigma, -4sigma.

Next step is to add the confidence interval (95%) for Mean and STD: the same way it is done in the following analysis :

Fit Y by X > Means and Std Dev.

I could run ALL the analysis one-by-one with a FOR() loop and use DisplayBox to pick each values and populate my Summary data table, but I really would rather not, it would slow down the analysis by A LOT since I usually deal with 1000+ metrics and 10000+ rows.

Thanks in advance for your help

-Voiz

Attached my script that calls BIG CLASS.JMP as an example

Names Default To Here( 1 );
clear symbols();
Deletesymbols();

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

SplitBy = "sex";

//Get a list from all unique configs
summarize(ColumnSplitBy=by(Column (dt, SplitBy)));
show(ColumnSplitBy);
For( i = N Items( ColumnSplitBy ), i > 0, i--,
  If( ColumnSplitBy[i] == "",
  Remove From( ColumnSplitBy, i, 1 );
  )
);

//Get only numeric Columns
MetricCols = dt << Get Column Names( numeric, continuous );

//Create a new table to collect the stats from the "Test" table
CpkTable = New Table( "Summary Table",
	New Column( "Metric", "Character", "Nominal" ),
	New Column( SplitBy, "Character", "Nominal" ),
	New Column( "N", "Numeric", "Continuous" ),
	New Column( "LSL", "Numeric", "Continuous" ),
	New Column( "USL", "Numeric", "Continuous" ),
	New Column( "Mean", "Numeric", "Continuous" ),
	New Column( "StdDev", "Numeric", "Continuous" ),
	New Column( "+4σ", "Numeric", "Continuous", formula(:Mean + :StdDev * 4) ),
	New Column( "-4σ", "Numeric", "Continuous", formula(:Mean - :StdDev * 4) ),	
);

show(MetricCols);

for(i=1,i <= N Items(MetricCols) ,i++,
	show(i);
	
	// Calculate Stats
	Summarize(dt, exg = by(Column (dt, SplitBy)), 
	exc = Count((Column (dt, Eval(Eval Expr(Expr(Char(MetricCols[i]))))))),
	exm = Mean((Column (dt, Eval(Eval Expr(Expr(Char(MetricCols[i]))))))),
	exstd = StdDev((Column (dt, Eval(Eval Expr(Expr(Char(MetricCols[i]))))))),	
	);

	Limits = Column(dt,MetricCols[i]) << GetProperty ("Speclimits"); 

	//Loop to run through all configs and fill the table
	For( j= 1 ,j <= NItems(ColumnSplitBy) , j++,
		Show(j );

		y = Eval Expr(
			CpkTable << AddRows(
				{:Metric = Expr(Char( MetricCols[i] ) ) ;
				As Column(Splitby) = Expr(Char( ColumnSplitBy[j] ) ) ;
				:LSL= Expr(Try(Limits["LSL"]));
				:USL= Expr(Try(Limits["USL"]));
				:N= exc[j];
				:Mean= exm[j];
				:StdDev= exstd[j];
			};)
		);
		
		Print(y);
		Try(Eval(y));

	); //For j */
); //For i

Re: Populate Summary table with Main & StdDev confidence intervals

jthi — Tue, 28 May 2024 05:07:55 GMT

I'm not sure what is "a lot" in your case, but I would still first try using fit y by x platform.

Open it with as little as possible AND open it as invisible report (or private depending on your jmp version). After you have extracted the values close it.

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Probe.jmp", invisible);

cont_cols = dt << Get Column Names("Continuous", "String");

start = Tick Seconds();
For Each({cont_col}, cont_cols,
	fit = dt << Oneway(
		Y(Eval(cont_col)),
		X(:Site),
		Invisible
	);

	tb_vals = Report(fit)[Outline Box("Means and Std Deviations"), Table Box(1)] << get;
	fit << close window;
	// save results from tb_vals
);

end = Tick Seconds();
show(end - start);

Re: Populate Summary table with Main & StdDev confidence intervals

txnelson — Tue, 28 May 2024 12:43:34 GMT

My response did not answer the question

Re: Populate Summary table with Main & StdDev confidence intervals

Voizingu — Tue, 28 May 2024 17:57:53 GMT

Hello Jarmo,

Thanks a lot, it takes between 8 and 10s for the script to run, which is great since PROBE.JMP is quite a heavy file.

Do you have a trick to populate a Summary table with "tb_vals" for each metrics and grouping? I tried to do it each value at a time and my script is now super slow. Is there a way to populate a whole matrix into a table in a fast way?

Re: Populate Summary table with Main & StdDev confidence intervals

jthi — Tue, 28 May 2024 18:33:27 GMT

suI assume you wish to extract all of these for each of the Levels

I think this might be even faster (at least it is easier to script)

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Probe.jmp", invisible);

cont_cols = dt << Get Column Names("Continuous", "String");

ow = dt << Oneway(
	Y(Eval(cont_cols)),
	X(:Site),
	Means and Std Dev(1),
	SendToReport(
		Dispatch(
			{"Means and Std Deviations"},
			"Std Dev Lower 95%",
			NumberColBox,
			{Visibility("Visible")}
		),
		Dispatch(
			{"Means and Std Deviations"},
			"Std Dev Upper 95%",
			NumberColBox,
			{Visibility("Visible")}
		)
	),
	Invisible
);

tb = Report(ow[1])[Outline Box("Means and Std Deviations"), Table Box(1)];
dt_stats = tb << Make Combined Data Table;
tb << close window;

You can run Oneway for all of your columns at the same time WITH Means and Std Dev enabled (and "hidden" columns visible), get reference to first table box and then use << Make Combined Data Table. You will get result like this

You should then be able to use Update to add those columns you need to your final table.

If this doesn't work out for some reason, looping will and there are few options how you can do that. Use << Make Into Data Table and Update OR get the results from the table box into lists (<< Get might be enough) and update your data using lists + data table subscripting.

Re: Populate Summary table with Main & StdDev confidence intervals

Voizingu — Wed, 29 May 2024 22:27:41 GMT

Hi Jarmo,

Yes I tried the above script and it runs smoothly and fast on a big datafile.

Thanks so much!!

-Voiz