R Association Rule coding using Apriori algorithm

[MAAbdullah47]

Dear All ,

I need help in the following two Questions:

1- I want to dynamically send any record data set e.g.: Based on Date period or any other columns, then send this dynamic parameter to be generated using an apriori algorithm and give me a result of Basket items with (LHS, RHS, Support, Confidence and lift) how I can do this task? . 

2- I have the following R code:

library(arules)
library(Matrix)
setwd("C:/Data Worked")
Order_Product_Sub<-read.csv("Order_Product.csv")
Orders_Basket <- read.transactions("Order_Product.csv", format ="single",rm.duplicates=TRUE, sep = ",", cols = c("orders.guid","products.name_en"))
Order_Rules <- apriori(data=Orders_Basket, parameter = list(supp=0.001, conf=0.001,minlen = 2,maxlen=4))
Orders_Basket_Table <- as.data.frame(inspect(sort(Order_Rules, by = "confidence"), ruleSep = "", setStart = "", setEnd = ""))
Orders_Basket_Table$No_Of_LHS<- rowSums(Orders_Basket_Table == ",")
x <- Orders_Basket_Table$lhs
Orders_Basket_Table$No_Of_LHS<-sapply(regmatches(x, gregexpr(",", x)), length)+1
Orders_Basket_Table[,2]<-NULL

After I run I get the following error:

Details: "ADO.NET: R script error.
Loading required package: Matrix

Attaching package: 'arules'

The following objects are masked from 'package:base':

abbreviate, write

Warning message:
In apriori(data = Orders_Basket, parameter = list(supp = 0.001, :
Mining stopped (maxlen reached). Only patterns up to a length of 4 returned!
Error in matrix(unlist(value, recursive = FALSE, use.names = FALSE), nrow = nr, :
'data' must be of a vector type, was 'NULL'
Calls: rowSums -> Ops.data.frame -> matrix
Execution halted
"

What is the cause of the error and how we can solve it?

Thank You In Advanced

[v-shex-msft]

Hi @MAAbdullah47,

Q1:
Yes, you can send your parameter to Data Query (R integration), but it not support dynamic send parameter and return the result.(current only support  manual update: modify your parameters -> apply change -> result will be refreshed)

For detail information, you can take a look at below article:

How to pass query parameter to Data Query (R integration)?

Q2:

Currently, there are many libraries only works in original r script, they are not available in power query r integration.

Regards,

Xiaoxin Sheng

[MAAbdullah47]

Thanks for your answers , for q1 I think the examples related to
C# not sure if I'm correct or not ?

For q2 what is your suggestions in the absence of a apriori algorithm ?

[v-shex-msft]

Hi @MAAbdullah47,

>>Thanks for your answers , for q1 I think the examples related to C# not sure if I'm correct or not ?

Yes, Invoke power query function not similar than methods in programming language methods.

>>For q2 what is your suggestions in the absence of a apriori algorithm ? 

Perhaps you can try to setting your R script root path and try to loading these libraries.

Regards,

Xiaoxin Sheng

[MAAbdullah47]

Can You explain more, If we put c:\ (the root) where they can get R resources?

Please give me some justifications

[v-shex-msft]

Hi @MAAbdullah47,

>>Can You explain more, If we put c:\ (the root) where they can get R resources?
I want to suggest you to manually setting the R script home path to help power bi  load/analysis the additional r script libraries resources.

If manual setting the home path also not works, I also haven't found other methods to load additional libraries packages to power.

Regards,

Xiaoxin Sheng

[MAAbdullah47]

Just to explain the issue not solved I'm now contacting Microsoft to solve the issue on this thread.