R Code running differently in Alteryx

I am attempting to turn an R script that I have into an Alteryx flow. Unfortunately, I can't share the data behind this, but I changed it a bit so I can share the following R code. My issue is that this code doesn't do the correct things in Alteryx. It works perfectly fine in R Studio, however.

Note: I think stringr and Hmisc are the only packages I use in this section of code, although I have about seven packages read in altogether for this script.

Going into this step of the code, a sample of the column headers look like this:

[1] "ABCD.ns1:awardID.ns1:awardContractID.ns1:companyID"

[2] "ABCD.ns1:awardID.ns1:awardContractID.ns1:modNumber"

[3] "ABCD.ns1:typeID.ns1:referenceID.ns1:companyID"
[4] "ABCD.ns1:typeID.ns1:referenceID.ns1:companyCity"
[5] "ABCD.ns1:purchaserInformation.ns1:shippingCompanyID"
[6] "ABCD.ns1:purchaserInformation.ns1:shippingCompanyID.departmentID"

After running this code in R Studio, the columns look like:

[1] "AwardContractID_companyID(ABCD)"

[2] "AwardContractID_modNumber(ABCD)"

[3] "ReferenceID_companyID(ABCD)"

[4] "ReferenceID_companyCity(ABCD)"

[5] "ShippingCompanyID(ABCD)"

[6] "ShippingCompanyID.departmentID(ABCD)"

However, after running the same code in the R tool in Alteryx, the columns look like:

[1] "ABCD.Ns1.Awardid.Ns1:Awardcontractid.Ns1.Companyid(ABCD)"

[2] "ABCD.Ns1.Awardid.Ns1:Awardcontractid.Ns1.Modnumber(ABCD)"

[3] "ABCD.Ns1.Typeid.Ns1.Referenceid.Ns1.Companyid(ABCD)"
[4] "ABCD.Ns1.Typeid.Ns1.Referenceid.Ns1.Companycity(ABCD)"
[5] "ABCD.Ns1.Purchaserinformation.Ns1.Shippingcompanyid(ABCD)"
[6] "ABCD.Ns1.Purchaserinformation.Ns1.Shippingcompanyid.Departmentid(ABCD)"

There must be some reason why Alteryx isn't running this correctly, but I can't figure it out. The rest of my R script seems to work fine. It is only this section that is causing me problems.

i = 0
ind = c()
name_list <- c()
for (name in colnames(Main_Data)) {
  i = i + 1
  x <- str_detect(name, "^ABCD")
  if (x == TRUE) {
    a <- str_detect(name, "awardContractID")
    b <- str_detect(name, "referenceID")
    if (a == TRUE) {
      ind <- c(ind,i)
      y <- unlist(strsplit(name, split = ":", fixed = TRUE))
      z <- y[length(y)]
      name2 <- paste0("awardContractID_",z,"(ABCD)")
      name_list <- c(name_list, name2)
    } else if (b == TRUE) {
      ind <- c(ind,i)
      y <- unlist(strsplit(name, split = ":", fixed = TRUE))
      z <- y[length(y)]
      name2 <- paste0("referenceID_",z,"(ABCD)")
      name_list <- c(name_list, name2)
    } else {
      ind <- c(ind,i)
      y <- unlist(strsplit(name, split = ":", fixed = TRUE))
      z <- y[length(y)]
      name2 <- paste0(z,"(ABCD)")
      name_list <- c(name_list, name2)
    }
  }
}
i <- 1
for (j in ind) {
  colnames(Main_Data)[j] <- name_list[i]
  i = i + 1
}

colnames(Main_Data) <- capitalize(colnames(Main_Data))

Any help would be greatly appreciated. Thanks!

R Tool

Accepted answers

DavidxL

I've also run into this issue before and my solution was to read in the column names as a second data source. The original data will go to the R tool as input #1, and I also pass it through a Field Info tool which will give the column names as input #2.

(The Select tool is there to deselect all the other Field Info which aren't needed, but you don't necessarily have to do this). Then in the R tool you can read in the names from the Field Info side and then assign them to the column names of your original data, like so:

x <- read.Alteryx("#1", mode="data.frame")
xnames <- read.Alteryx("#2", mode="data.frame")
colnames(x) <- xnames$Name

All comments

JohnJPS

A wild guess is to assess the data types coming in: I like v_string or v_wstring of sufficient max length, trimmed of excess whitespace. Perhaps sone sanity checks within R too, to ensure it's not the data

rnewby

It looks like everything going in is v_string and everything coming out of the R code is v_wstring. I'm not sure if that is normal behavior, as I'm not too familiar with Alteryx data types, but it seems OK. The data check in R seems fine. I just can't figure out why the code doesn't do the exact same thing in both places.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235