topic Re: Calculating linear regression slope in formula in Discussions
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/219817#M43986
Dear <LI-USER uid="2687" login="txnelson"></LI-USER>, can you show how to tweak the above code to run fit models on rows selections based on each 'wafer' id/value, instead of the random sampling? Thanks!Fri, 02 Aug 2019 06:55:23 GMTTRR212019-08-02T06:55:23ZCalculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/36411#M21394
<P>Hello,</P><P> </P><P>I was wondering if there was a way to calculate the linear regression slope of column data within a data table formula.<BR /><BR />A simple example of the table is below. The data is ordered and I would like to calculate the slope of a fit line of volume over time using the current row's data and previous points to perform the regression.<BR /><BR />The idea is that I would like to see the how the calculated slope changes with increasing the number of data points. From doing this manually, the slope values end up approaching a given value and stay within +/-5% after 20 or so points.<BR /><BR /></P><TABLE><TBODY><TR><TD><STRONG>Instance</STRONG></TD><TD><STRONG>Time (s)</STRONG></TD><TD><STRONG>Volume (mL)</STRONG></TD><TD><STRONG>Slope (mL/s)</STRONG></TD></TR><TR><TD>1</TD><TD>0</TD><TD>0</TD><TD>n/a</TD></TR><TR><TD>2</TD><TD>5</TD><TD>0.5</TD><TD>m1</TD></TR><TR><TD>3</TD><TD>10</TD><TD>0.8</TD><TD>m2</TD></TR><TR><TD>...</TD><TD>...</TD><TD>...</TD><TD>...</TD></TR><TR><TD>N</TD><TD>T</TD><TD>V</TD><TD>mN-1</TD></TR></TBODY></TABLE><P><BR /><BR />If that is possible, my next challenge would be to set this up so that I can calculate the slope formula so that it uses N data points on either side of the current row for a symmetrical moving calculation instead.</P><P> </P><P> </P><P>Thank you in advance.</P>Mon, 27 Feb 2017 18:52:16 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/36411#M21394bio_grad2017-02-27T18:52:16ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/36415#M21396
<P>Here is a script that gives you the output you want, it just does it a different way. It uses the Fit Model Platform and loops through it several time, increasing the N.</P>
<PRE><CODE class=" language-jsl">Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA\semiconductor capability.jmp" );
// Create an output table to hold the results
dtOut = New Table( "summary",
New Column( "Instance" ),
New Column( "N" ),
New Column( "RSquare" ),
New Column( "RSM" ),
New Column( "Intercept" ),
New Column( "b1" ),
New Column( "b2" )
);
Loop Counter = 0;
// Loop across the different sizes
For( i = 5, i <= 111, i = i + 5,
Loop Counter++;
// Create a random sample data table from the original data table
dt << New Column( "random", formula( Random Uniform() ) );
dt << sort( by( dt:random ), , Order( Ascending ), replace table( 1 ) );
dt << delete columns( "Random" );
dt << select where( Row() <= i );
dtSample = dt << subset( invisible, selected rows( 1 ), selected columns( 0 ) );
// Run the regression
fm = dtSample << Fit Model(
Y( :NPN1 ),
Effects( :PNP1, :PNP2 ),
Personality( "Standard Least Squares" ),
Emphasis( "Minimal Report" ),
Run(
:NPN1 << {Summary of Fit( 1 ), Analysis of Variance( 1 ), Parameter Estimates( 1 ),
Lack of Fit( 0 ), Plot Actual by Predicted( 0 ), Plot Regression( 0 ),
Plot Residual by Predicted( 0 ), Plot Effect Leverage( 0 )}
)
);
// Capture the output data
dtOut << Add Rows( 1 );
dtOut:Instance[N Rows( dtOut )] = Loop Counter;
dtOut:N[N Rows( dtOut )] = i;
dtOut:RSquare[N Rows( dtOut )] = (Report( fm )["Summary of Fit"][1][2] << get)[1];
dtOut:RSM[N Rows( dtOut )] = (Report( fm )["Summary of Fit"][1][2] << get)[3];
dtOut:Intercept[N Rows( dtOut )] = (Report( fm )["Parameter Estimates"][1][3] << get)[1];
dtOut:b1[N Rows( dtOut )] = (Report( fm )["Parameter Estimates"][1][3] << get)[2];
dtOut:b2[N Rows( dtOut )] = (Report( fm )["Parameter Estimates"][1][3] << get)[3];
// Clean up after our selfs
fm << close window;
Close( dtSample, nosave );
);</CODE></PRE>Mon, 27 Feb 2017 20:15:36 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/36415#M21396txnelson2017-02-27T20:15:36ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/36417#M21397
<P>Try plugging this formula into a new formula column (this will calculate the slope up to the current row):<BR /><BR /></P>
<PRE><CODE class=" language-jsl">(Row() * Summation( i = 1, Row(), :Time[i] * :Volume[i] )
-Summation( i = 1, Row(), :Time[i] ) * Summation( i = 1, Row(), :Volume[i] )) / (
Row() * Summation( i = 1, Row(), :Time[i] ^ 2 )
-Summation( i = 1, Row(), :Time[i] ) ^ 2)</CODE></PRE>
<P>To do the second part of your question would only require changing the index on the summation, i.e. from i=Row()-N to Row()+N.<BR /><BR /><BR />Hope this helps.<BR /><BR />-Jerry<BR /><BR /><BR /></P>Fri, 02 Aug 2019 15:06:32 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/36417#M21397jerry_cooper2019-08-02T15:06:32ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/36418#M21398
<P>You could also use a column formula. I opened Big Class.jmp from the Sample Data folder. (Select <STRONG>Help</STRONG> > <STRONG>Sample Data Library</STRONG> > <STRONG>Big Class.jmp</STRONG> > <STRONG>Open</STRONG>) I crated a new numeric column called <STRONG>moving slope</STRONG>. This example computes the moving slope with a span of 3 for Y = weight and X = height with the following column formula:</P>
<PRE><CODE class=" language-jsl">If( Row() > 1,
x = J( Row(), 1, 1 ) || :height[Index( 1, Row() )]`;
y = :weight[Index( 1, Row() )]`;
(Inv( x` * x ) * x` * y)[2];
,
Empty()
)</CODE></PRE>
<P> Then plot moving slope versus row in Graph Builder.</P>Mon, 27 Feb 2017 21:00:29 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/36418#M21398markbailey2017-02-27T21:00:29ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/36442#M21412
<P>This is exactly what I was looking for. I had no idea these operations were available!</P><P> </P><P>Thank you!</P>Tue, 28 Feb 2017 05:17:49 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/36442#M21412bio_grad2017-02-28T05:17:49ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/42556#M24716
<P>wow~~ Awesome!!!!!!</P><P>Thank you so much!!!!! : )</P>Thu, 27 Jul 2017 04:52:16 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/42556#M24716James_Kim2017-07-27T04:52:16ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/50035#M28471
<P>Hi</P><P> </P><P>I am trying to apply your formula to calculate the moving slope with a window of 8, by which I mean (for Row() > 7), I want to calculate the slope of the linear regression for the first set of x and y data points [1 to 8], and then the next set of 8 data points incrementing by 1 [2 to 9], and so on. I am trying to calculate the maximum slope to get the initial rate of an enzymatic reaction.</P><P> </P><P>Please can you tell me how you would modify your script to achieve this? I have tested your formula and I obtain the slope for all data points from Row 1 to Row n, rather than a moving window of defined size.</P><P> </P><P>Thank you in advance</P><P> </P><P> </P><P> </P>Tue, 23 Jan 2018 16:07:25 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/50035#M28471Frank_Bowler2018-01-23T16:07:25ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/50045#M28473
<P>This modified formula produces a moving slope of span 8:</P>
<PRE><CODE class=" language-jsl">If( Row() > 7,
x = J( 8, 1, 1 ) || :height[Index( Row() - 7, Row() )]`;
y = :weight[Index( Row() - 7, Row() )]`;
(Inv( x` * x ) * x` * y)[2];
)</CODE></PRE>Tue, 23 Jan 2018 16:27:41 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/50045#M28473markbailey2018-01-23T16:27:41ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/50046#M28474
<P>Excellent, that works, thank you</P>Tue, 23 Jan 2018 16:43:23 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/50046#M28474Frank_Bowler2018-01-23T16:43:23ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/72084#M35538
<P>Hi,</P><P>Is there any plan to put slope and intercept into formula builder of a future JMP version w/ a more straight forward direct function ? </P><P>thx.</P>Mon, 10 Sep 2018 18:36:46 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/72084#M35538altug_bayram2018-09-10T18:36:46ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/72106#M35544
<P>Take a look at the Linear Regression() function, which could be used in a formula. It might be something you can use.</P>Mon, 10 Sep 2018 21:25:40 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/72106#M35544txnelson2018-09-10T21:25:40ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/108661#M39517
<P>Hi, </P><P> </P><P>You mention a "span of 3", if we want to modify it to be moving slope of "N" samples, which value will need to change?</P><P> </P><P>Thanks!</P>Fri, 01 Feb 2019 19:16:02 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/108661#M39517rdzdec2019-02-01T19:16:02ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/219817#M43986
Dear <LI-USER uid="2687" login="txnelson"></LI-USER>, can you show how to tweak the above code to run fit models on rows selections based on each 'wafer' id/value, instead of the random sampling? Thanks!Fri, 02 Aug 2019 06:55:23 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/219817#M43986TRR212019-08-02T06:55:23ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/255376#M50142
<LI-USER uid="5358" login="markbailey"></LI-USER> Hi mark, I am new to JMP and am trying to do something similar to bio_grad where I perform linear regression on 3 points, then 4 points, then so on in a column in my data table. I am trying to understand what your answer is actually doing and had a couple of questions. What does || mean? also what is J?Tue, 31 Mar 2020 21:47:14 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/255376#M50142mksaad2020-03-31T21:47:14ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/255377#M50143
<P>J() is a function that creates a matrix of identical values. The default is 1.</P>
<P>|| is an operator that indicate concatenation. So in Mark's example he is concatenating 2 matrices.</P>
<P> </P>
<P>These functions and operators are fully documented in the Scripting Guide</P>
<P> Help==>JMP Document Library ….. Scripting Guide</P>Tue, 31 Mar 2020 21:58:06 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/255377#M50143txnelson2020-03-31T21:58:06ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/260157#M51053
<P>hi ... </P><P> </P><P>You mentioned "<SPAN>Linear Regression() function, which could be used in a formula" ...and <STRONG>getting a slope from a formula fitting to provided data set being the final objective</STRONG>, where would one go find Linear Regression() function or any help ?</SPAN></P><P><SPAN>Do you have any example ? </SPAN></P><P> </P><P><SPAN>thanks</SPAN></P>Thu, 23 Apr 2020 19:17:51 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/260157#M51053altug_bayram2020-04-23T19:17:51ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/260166#M51059
<P>Select Help > Scripting Index. Enter "regression" in the filter box. You should find the Linear Regression() function in the second list. Select it to see information about this function on the right.</P>Thu, 23 Apr 2020 19:28:20 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/260166#M51059markbailey2020-04-23T19:28:20ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/260200#M51062
I am not looking for a script.<BR />I am looking for a formula application (i.e. using formula builder)Thu, 23 Apr 2020 20:21:04 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/260200#M51062altug_bayram2020-04-23T20:21:04ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/260249#M51063
<P>A JMP formula can pretty much be anything you want it to be. My pointing you to the Linear Regression() function in the Scripting Index, happens to show it's example in the form of a script, but the function can also be used in a formula. Below is a silly example, of calculating the regression for first rows 1-3, then 1-4, 1-5, etc. for all of the rows in the Big Class data table. Here is the formula...….attached in the data table with the new formula column added</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Regression.PNG" style="width: 999px;"><img src="https://kvoqx44227.i.lithium.com/t5/image/serverpage/image-id/23401i85F5B73332513B99/image-size/large?v=1.0&px=999" title="Regression.PNG" alt="Regression.PNG" /></span></P>Thu, 23 Apr 2020 20:46:42 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/260249#M51063txnelson2020-04-23T20:46:42ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/260265#M51064
<P>Can you give us some more background on what you're looking for?</P>
<P> </P>
<P>Formulas, generally speaking, work across rows of a data table, evaluating one per row with column values from that row as inputs.</P>
<P> </P>
<P>So, by asking for a regression function in the formula editor, one would expect that you had a matrix of input values in each row of your data table. Perhaps that's what you've got but that's an unusual structure.</P>
<P> </P>
<P>So, with some more information we may be able to help you understand how you could use a formula for this, or perhaps, point you in a direction outside of the formula editor. </P>Thu, 23 Apr 2020 21:14:59 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/260265#M51064Jeff_Perkinson2020-04-23T21:14:59ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/260966#M51091
<P>I know.</P>
<P> </P>
<P>My point is that the best way to start learning about functions that you might use in a formula is with the Scripting Index.</P>Fri, 24 Apr 2020 12:26:59 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/260966#M51091markbailey2020-04-24T12:26:59ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/261181#M51112
<P>Hello JMP team ... </P><P>Nelson's approach is very likely what I was looking for. I am conversing w/ him on a similar need with mann kendall tau test. </P><P> </P><P>My input is not in a single row. </P><P> </P><P>My data is typically contains several parameters each measured at a different time step. Tidy data format; parameters are in columns, each row is a different time state - i.e. chronology is in rows. </P><P> </P><P>I am looking into many statistical functions measuring trend in one of these or a fit versus some other X in time but <STRONG>as a function of rolling windows. </STRONG>So instead of regressing the whole history of X vs Y for instance, I like a logic that looks into regression of the two within constant past N time steps. Each row then re-evaluates and gets me a new result. These could a slope from linear regression, or a tau test from a non-parametric MK test or some other stat output. </P><P> </P><P>Many times, we do not know when the correlation between the X's have changed, so this is one attempt to look into the impact of time for any analysis at hand. N mentioned above can be changed to various values to examine short term vs mid/long terms trends.</P><P> </P><P>I am ok w/ scripts, they are not my preference unless the computations can great benefit a method offered only by scripts.</P><P>My preference is generally to put the calculations into formulaes and have them maintained within the data frames. </P><P><STRONG>Until today, </STRONG>I <STRONG>did not </STRONG>know that script structures could be deployed within formulaes. </P><P>That probably was the main cause of my confusion. </P><P> </P><P>Appreciate all your help. </P>Fri, 24 Apr 2020 22:51:46 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/261181#M51112altug_bayram2020-04-24T22:51:46ZRe: Calculating linear regression slope in formula
https://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/261224#M51123
<P>You said, "<SPAN>Many times, we do not know when the correlation between the X's have changed, so this is one attempt to look into the impact of time for any analysis at hand.</SPAN>" Have you tried the Functional Data Explorer in JMP Pro? It might be useful for detection a change in the shape through the functional principal components.</P>Sat, 25 Apr 2020 12:46:38 GMThttps://community.jmp.com/t5/Discussions/Calculating-linear-regression-slope-in-formula/m-p/261224#M51123markbailey2020-04-25T12:46:38Z