Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

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