Wrapping ADF (Augmented Dickey-Fuller) R Test in Alteryx using R Tools or R Packages

[sstrange31]

I just want to see if anyone has had any luck wrapping an ADF test using R in Alteryx. Im fairly new to the R side of Alteryx.Honestly, any time series based R testing would help a lot!

[JohnJPS]

Hi @sstrange31

From the other direction, I know nothing of the ADF test.  However, I've attached a workflow that generates some dummy data and passes it into R. I then run the ADF test on one of the variables; (again: I don't know what the test is, at all, so please take with a grain of salt).

I then pass the original dataset to Output#1 and the results of the ADF test to Output#2.

Hopefully that at least helps get you rolling.

 - John

[sstrange31]

@JohnJPS 

Thanks soooooooo much. That was above and beyond anything I thought i'd get. Thanks so much for the help.

I have an additional question if you don't mind: Is there a way for me to wrap it in a loop? Like lets say I have a set of 1000 records for 3 Widgets. Is there a way to loop through and have 3 records for output by my WidgetID? I would think it would have less overhead than wrapping this in a macro and calling R multiple times.

I have attached an adapted version of your wf as an example.

Example "looped" output

Widget     Statistic

1              -10.12

2              -9.14

3              -10.6

Again thanks so much for the help. Sorry if I am not explaining it precisely I am not super versed in R jargon.

[JohnJPS]

Sure, that's pretty striaght-forward in R; I've attached a new workflow that generates slightly different data and does the ADF within with WidgetID:

library(tseries)

df <- as.data.frame(read.Alteryx("#1", mode="data.frame"),stringsAsFactors=FALSE)

# ADF test on column "r" grouped by WidgetID...
outDF <- aggregate(df$r, list(df$WidgetID), adf.test)

# alteryx often prefers everything as character data...
outDF[, ] <- lapply(outDF[,], as.character)

write.Alteryx(df, 1)
write.Alteryx(outDF, 2)

I also force the output dataframe to as.character, which gets rid of an odd error you sometimes see passing things from R back to Alteryx.

Hope that helps!

John

[sstrange31]

Hey @JohnJPS ,

again thanks so much for the help. That was definitely what i was looking for but i think i messed up on the example. I was just trying to convey the three input sets. How can i get the rest of the results  that i saw with the first workflow you provided?

statistic                                     parameter              alternative                     p.value               method                                              data.name
-10.1034752108035                9                             stationary                     0.01                    Augmented Dickey-Fuller Test           df$r

I tinkered around and dropped the "[, ]" off the outDF which got me some results that i can transpose but i just wanted to get your opinion on whether that was SOP or if im just hacking it together. Here is the code i updated:

library(tseries) 
df <- as.data.frame(read.Alteryx("#1", mode="data.frame"),stringsAsFactors=FALSE) 

# ADF test on column "r" grouped by WidgetID... 
outDF <- aggregate(df$r, list(df$WidgetID), adf.test) 
# alteryx often prefers everything as character data... 
outDF <- lapply(outDF[,], as.character) 
write.Alteryx(df, 1) 
write.Alteryx(outDF, 2)

[JohnJPS · Accepted solution]

Hi @sstrange31,

I had another look at it and, frankly, I'm not exactly polished on the R apply/grouping functions.  Based on this post at StackOverflow, and in particular the answer that looks at performance, I tried it using a data.table rather than a data.frame, and the code is actually almost identical, and should be faster if you're running a lot of data through it.

library(tseries)
library(data.table)
dt <- setDT(read.Alteryx("#1", mode="data.frame"))
outDT <- dt[, adf.test(r), WidgetID]
write.Alteryx(dt, 1)
write.Alteryx(outDT, 2)

It also didn't have any trouble with the format of the output (so I could exclude the lappy/as.character stuff entirely, and it provided the output pretty much exactly as you desire, column headers and all:

... although, you may have to sort it; for some reason it comes in haphazard order for WidgetID.

Hope that helps!

John

[sstrange31]

Thats exactly what i was looking for! Again, thanks so much for taking the time to help me out! Have a great weekend!