Using Action to dynamically select columns with R Code

I'm creating a macro in which I have an R script node that is running a simple linear regression model. Yes, I know linear regression is available as a tool - I'm trying to stick with something simple here to scale up for other processes. Most of my R processes I need to select a single target and an unknown amount of predictor variables. Essentially I need to partially mimic the configuration window in the Linear Regression tool. I have dissected the macro of the linear regression tool but the predictor selection is throwing me off.

My R Script is simple:

data <- read.Alteryx("#1", mode="data.frame")

RGR <- lm(Target ~ IV1+IV2+IV3, data = data)

I have been successful in replacing "Target" with an Action where I have the action replace the string "Target" but I can't figure out how to replace IV1:3 dynamically. If in the future I have 8 variables instead of 3 that I'm using I need to replace "IV1+IV2+IV3" with "IV1+IV2+IV3+IV4+IV5+IV6+IV7+IV8" and so forth.

It seems like from the linear regression macro that I need to do something with XML (which I'm unfamiliar with) If anyone has any input on how to accomplish this with an action, I'd be eternally grateful!

Thanks!

AOO.yxmc

AOO Sample Workflow.yxzp

Macros

Interface Tools

R Tool

Accepted answers

AkimasaKajitani

Hi @Anthony_A

You can use "Generate Custom List" option at List Box tool.

Setting is below.

Start Text and End Text is Null and Separator is "+".

AOO Sample Workflow_AK2.yxzp

All comments

joshbennett

Hi @Anthony_A - admittedly I have very little personal experience with R, but if I understood your question correctly, you want to be able to replace IV1, IV2, IV3, etc. variables within your R script with field references from an incoming data set (but please correct me if I am misunderstanding).

If so, one idea that may be worth trying is to place your R tool within a macro and leverage the Action tool's "Update Raw XML with Formula" setting along with a List Box that will allow you to select the fields from an incoming data set you want to assign as predictor variables.

If you have not worked with directly manipulating a tool's XML before, make sure to first go to Options > User Settings > Edit User Settings > Advanced > check the box for 'Display XML in Properties Window' and click Save. After doing this, you will notice a new option available within the tool configuration pane, which shows the following when displayed for the R tool loaded with your script:

This shows us the XML behind the tool configuration, which looks more intimidating than it actually is. If you want to replace the "IV1+IV2+IV3" string in your script with the same syntax for fields from your incoming dataset, after adding the R tool with your script to a macro workflow and connecting an Action (configured for 'Update Raw XML with Formula') and List Box (configured for 'Generate Custom List'), you should be able to pass the full string of your script into the R tool (updated with references to your dynamically selected fields as predictor variables) by adding all of your script text up to the "IV1+IV2+IV3" part in the 'Start Text' field, adding + to the 'Separator' field, and all of your script text following "IV1+IV2+IV3" to your 'End Text' field.

By doing this, your macro should essentially re-write the script each time you run it, substituting "IV1+IV2+IV3" for the fields you select in the macro tool's configuration interface from your main workflow, delimited by +. For example, if you select Field1, Field2, Field3, and Field4 in your macro config, your macro should pass the following string into your R tool:

data <- read.Alteryx("#1", mode="data.frame")
RGR <- lm(Target ~ Field1+Field2+Field3+Field4, data = data)

To force the new line / carriage return in your script between lines 1 and 2, you could place a placeholder character (e.g., "|") at the end of the string you added to the List Box's 'Start Text' field and then update your Action formula expression to something like:

regex_replace([#1], "\|", "\n")

Hope that helps! Let me know if that doesn't work for you or if you have any clarifying questions.

AkimasaKajitani

Hi @Anthony_A

If you only need to replace IV1+IV2+IV3, you can use a simple "Update Value".

Please refer attached file.

AOO Sample Workflow_AK.yxzp

Anthony_A

Hi @AkimasaKajitani

Thanks for your solution! That is a good work around but I think would be better for a user to select the variables with a List Box or another easy selection function. Do you know of a method of using a list box to dynamically replace "IV1+IV2+IV3" with selected fields from the List Box? So then "IV1+IV2+IV3" would be replaced with "Selectedfield1+Selectedfield2+Selectedfield3+Selected4... and so forth" from the selected columns in the List Box?

It looks like the linear regression macro updates the XML and I have been trying to take a similar approach with a list box connected to an action replacing the outer xml code with:

'  <SelectFields>
    <SelectField field="' + replace(EscapeXMLMetacharacters([IV1+IV2+IV3]), ",", '" selected="True" />
    <SelectField field="') + '" selected="True" />
    <SelectField field="*Unknown" selected="False" />
  </SelectFields>'

but I haven't had any luck. Let me know if you have any thoughts!

Really appreciate the help!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335