Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

Trey1
7 - Meteor

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

6 REPLIES 6
grossal
15 - Aurora
15 - Aurora

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:

 

grossal_0-1596607070658.png

The Regex parses our Measure like this:

grossal_2-1596607173619.png

 

 

Result:

grossal_1-1596607090634.png

 

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

 

 

Best

Alex

Trey1
7 - Meteor

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
15 - Aurora
15 - Aurora

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

grossal_0-1596639413785.png

Step 2: Figure out the row of your information

grossal_1-1596639517982.png

 

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

grossal_3-1596639604645.png

 

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

grossal_4-1596639710227.png

 

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

grossal_5-1596639866004.png

 

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

grossal_6-1596639972200.png

 

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:

grossal_7-1596640138302.png

 

 

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
7 - Meteor

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: 

Trey1_0-1596647423475.png

 

Thank you,

Trey

grossal
15 - Aurora
15 - Aurora

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:

 

2020-08-05 19-28-43.png

 

 

And this are the settings: 

 

Text Input (lower one):

2020-08-05 19-25-13.png

 

 

Find Replace:

2020-08-05 19-26-16.png

 

Regex:

2020-08-05 19-28-12.png

 

Let me know if it worked for you!

 

Best

Alex

Trey1
7 - Meteor

That worked!! Thank you so much 🙂

Labels