Export R^2 from "Linear Regression" tool to Excel

[Trey1]

Hi,

I am running a simple linear regression and wanting to export the R^2 generated by the Linear Regression tool to excel. I found a macro for exporting the coefficients, but I can't figure out how to get the R^2. 

Thank you,

Trey

[grossal · Accepted solution]

Hi @Trey1,

I am not sure what Macro you use to attempt the extract, but I don't think it is needed. You could just Use the R-Anker and filter out the data you want like this:

The Regex parses our Measure like this:

Result:

Workflow for reference attached. Let me know if this helped.

Best

Alex

[Trey1]

Thank you! This is a much cleaner solution.

Could you explain it briefly? I would like to also export the standard error of the coefficient to calculate a CI. 

Thank you,

Trey

[grossal]

It's pretty simple, I'll try my best to explain it @Trey1:

I'll describe the process of building the workflow and let you try it yourself first instead of posting just the solution.

Step 1: Add a Browse-Tool to the R-Anker of the Linear Regression

Step 2: Figure out the row of your information

Step 3: Find out the 'Desc' for your Row (In the results windows)

Step 4: Add a Filter Tool and filter by Desc 'FitStats' (or the Desc of the row you want)

Step 5: Figure out what's needed for the Regex (Click Browse Tool after the Filter and double click on Report column) - Scroll to the right till you find your information and copy it

Step 6: Copy paste it into a Regex Tool and change Column to Report and Output Method to Parse.

Step 7: Modify the Regular Expression. This is a bit tricky but not too difficult:

7.1: Add () around all you just pasted

7.2: Add () around the number you want

7.3: Replace numbers with \d+ to dynamically look for numbers instead of looking for a fixed value

Step 8: Rename the columns:

Let me know if this small manual helped you to do it yourself or if you need some additional specification / and / or my support for the solution.

Best

Alex

[Trey1]

This is incredible! Thank you so much! I just need help on the last step. I am trying to pull out the standard error of my coefficient (bolded below). I pulled out the "coefficients" desc.

</style><datatable style="border: 1px solid #000000;" width="100%" cellspacing="0px" cellpadding="0px" rowGroupSize="1" rowsContained="3"><views><dataview></dataview></views><columns><col/><col/><col/><col/><col/><col/></columns><thead><tr class="headerRow"><td class="column0"> </td><td class="column1">Estimate</td><td class="column2">Std. Error</td><td class="column3">t value</td><td class="column4">Pr(&gt;|t|)</td><td class="column5"> </td></tr></thead><dbody><dr><cell class=" column0 Rule0" style="" >(Intercept)</cell><cell class=" column1 Rule0" style="" >-0.003565</cell><cell class=" column2 Rule0" style="" >0.01489</cell><cell class=" column3 Rule0" style="" >-0.2395</cell><cell class=" column4 Rule0" style="" >0.8116</cell><cell class=" column5 Rule0" style="" > </cell></dr><dr><cell class=" column0 Rule0" style="" >Avg_Charleston__SC</cell><cell class=" column1 Rule0" style="" > 0.678864</cell><cell class=" column2 Rule0" style="" >0.06576</cell><cell class=" column3 Rule0" style="" >10.3236</cell><cell class=" column4 Rule0" style="" >1.15e-14</cell><cell class=" column5 Rule0" style="" >***</cell></dr></dbody></datatable></div></td></tr><tr><td style="vertical-align:middle;"><div class="DefaultText" style="width:100%;"><span style="font-family:Verdana;font-size:12.0pt;">Significance codes:<nbsp/> 0 &apos;***&apos; 0.001 &apos;**&apos; 0.01 &apos;*&apos; 0.05 &apos;.&apos; 0.1 &apos; &apos; 1</span></div></td></tr></tbody></table>

I will be changing the regressor. I can't seem to get a unique set of characters to parse.

The table is: 

Thank you,

Trey

[grossal]

We need to do a little more trickery (Regex) here to complete the challenge: 

Avg_Charleston__SC.*column2 Rule0" style="" >(0.\d+)

.* allows all characters and as much as possible from them, that way we start after Avg_Charleston__SC to look for column2 Rule0" style="" > and the number behind it. I am not quite sure how much the data changes if you change the Regressor. If it actually changes the word Avg_Charleston__SC you would need some more tricks. Probably the easiest way would be creating a list of of all Regressors and replacing them with a new Word and than mapping the Regex to the new word. The workflow would look like this:

And this are the settings: 

Text Input (lower one):

Find Replace:

 

Regex:

Let me know if it worked for you!

Best

Alex

[Trey1]

That worked!! Thank you so much 🙂