topic Re: loop through column names and do bivariate fitting with certain ones and output slope and R squared value in Discussions

loop through column names and do bivariate fitting with certain ones and output slope and R squared value

joshua — Tue, 10 Mar 2020 18:06:35 GMT

Relevant post is here,
https://community.jmp.com/t5/Discussions/How-to-group-by-and-get-slopes-in-a-JSL-fit-model-script/m-p/251632#M49404
I have many columns and do not want to include some of them to the analysis. So I need to loop through the column names and pick the ones I need and do bivariate fitting.

So by following the solution on above post

I did

NamesDefaultToHere(1);

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


colList = dt << get column names( string );

For( i = 1, i <= N Items( colList ), i++,
	If( ! Contains( colList[i], "Validation") & ! Contains( colList[i], "People") ,
	
	biv = Bivariate(invisible,
	Y( :Fish Caught ),
	X( :i),
	Fit Line( {Line Color( "Medium Dark Red" )} ),
	By( :Validation )
))
	
	dtR2 = report(biv[1])["Summary of Fit"][tablebox(1)]<< make combined data table;

dtR2 = report(biv[1])["Summary of Fit"][tablebox(1)]<< make combined data table;
dtSlope = report(biv[1])["Parameter Estimates"][tablebox(1)]<< make combined data table (invisible);
dtR2 << select where(:Column 1 != "RSquare");
dtR2 << delete rows;
dtR2:Column 2 << set name("RSquare");
dtR2 << delete Columns({"Validation 2","Column 1"});	

dtSlope << select where(:Term == "Intercept");
dtSlope << delete rows;
dtSlope:Estimate << set name( "Slope");
dtSlope << delete columns({"Validation 2","Term","~Bias","Std Error","t Ratio","Prob>|t|"});

// Put the data together
dtR2 << Update(
	With( dtSlope ),
	Match Columns( :Validation = :Validation, :X = :X, :Y = :Y )
);

// Clean up the items not needed
close(dtSlope, nosave);
window("Fishing - Bivariate of Fish Caught") << close window;
);

but it seems that I'm not sure how to implement this correctly.

Re: loop through column names and do bivariate fitting with certain ones and output slope and R squared value

joshua — Tue, 10 Mar 2020 19:54:20 GMT

Can somebody from Jmp staff help me with this problem ?

Re: loop through column names and do bivariate fitting with certain ones and output slope and R squared value

Mark_Bailey — Tue, 10 Mar 2020 21:29:13 GMT

Names Default To Here( 1 );

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

col = dt << Get Column Names( Numeric, String );

For( c = N Items( col ), c > 0 , c--,
	If( col[c] == "Fish Caught", Remove From( col, c ) );
	If( col[c] == "Validation", Remove From( col, c ) );
	If( col[c] == "People", Remove From( col, c ) );
);

biv = dt << Bivariate(
	Y( :Fish Caught ),
	X( Eval( col ) ),
	By( :Validation ),
	Fit Line
);

biv rep = biv << Report;

summary dt = biv rep[1]["Summary of Fit"][TableBox(1)] << Make Combined Data Table;
parameter dt = biv rep[1]["Parameter Estimates"][TableBox(1)] << Make Combined Data Table;

Wait( 0 );

biv << Close Window;

Column( summary dt, 6 ) << Set Name( "RSquare" );
summary dt
	<< Select Where( Contains( :Column 1[], "RSquare" ) & Not( Contains( :Column 1[], "Adj" ) ) )
	<< Invert Row Selection
	<< Delete Rows
	<< Delete Columns( { "Y", "Validation 2", "Column 1" } );

Column( parameter dt, 7 ) << Set Name( "Slope" );
parameter dt
	<< Select Where( Contains( :Term[], "Intercept" ) )
	<< Delete Rows
	<< Delete Columns( { "Y", "Validation 2", "Term", "~Bias", "Std Error", "t Ratio", "Prob>|t|" } );

summary dt << Join(
	With(parameter dt ),
	Select( :Validation, :X, :RSquare ),
	SelectWith( :Slope ),
	By Row Number
);

Close( summary dt, No Save );
Close( parameter dt, No Save );

Re: loop through column names and do bivariate fitting with certain ones and output slope and R squared value

Mark_Bailey — Wed, 11 Mar 2020 21:14:15 GMT

Did this solution help you?

Re: loop through column names and do bivariate fitting with certain ones and output slope and R squared value

joshua — Thu, 12 Mar 2020 19:02:55 GMT

yes mark, Sorry for delay:)