Association Rules

Moderator
6037 Views
Highlighted
Moderator
Posts: 17
Registered: ‎09-29-2015

Association Rules

[ Edited ]

report_assoc.JPGDescription

Association rules are ideal to quicly derive insights from large datasets.  The rules are autmatically detected and visualized. 
 

Prerequisites (The sample .pbix files will not work without these prerequites completed)

1. Install R Engine

Power BI Desktop does not include, deploy or install the R engine. To run R scripts in Power BI Desktop, you must separately installR on your local computer. You can download and install R for free from many locations, including the Revolution Open download page, and the CRAN Repository.

 

2. Install the required R packages.

Download the R script attached to this message and run it to install all required packages on your local machine. 

 

Required R packages:

arules, arulesViz, grDevices, gridExtra, grid, methods


Tested on:

CRAN 3.3.1, MRO 3.3.0, , powerbi.com

 

Legal Disclaimers:

Terms of Service and Third Party Programs.

 

Attachment
Attachment
Member
Posts: 111
Registered: ‎04-23-2016

Re: Association Rules

[ Edited ]

Hi

I wonder if you can advice that what Im trying to acheive is possible.

 

I wanted to transform my dataset to transactions using a simple R script below
library(arules)

dd = as(dataset, "transactions")
df = as.data.frame(as(dd, "matrix"))

 

 

However I got an error message beolow. Can you please help what this error actually mean and how to fix it?

 

DataSource.Error: ADO.NET: R script error.
Loading required package: Matrix

Attaching package: 'arules'

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

    abbreviate, write

Error: could not find function "as"
Execution halted

Details:
    DataSourceKind=R
    DataSourcePath=R
    Message=R script error.
Loading required package: Matrix

Attaching package: 'arules'

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

    abbreviate, write

Error: could not find function "as"
Execution halted

    ErrorCode=-2147467259
    ExceptionType=Microsoft.PowerBI.Radio.RScriptRuntimeException

Member
Posts: 111
Registered: ‎04-23-2016

Re: Association Rules

Answered by own question!

library(arules)
library(methods)
data(dataset)
dd1=as(dataset,"transactions")
trans=dd1
AdultData=as.data.frame(as(dd1, "matrix"))
AdultData=cbind(id=1:nrow(AdultData),AdultData)

 

 

replace data(Adult) with data(dataset) and R does the rest!.. didnt have eyes for details there.

 

 

 

Frequent Visitor
Posts: 6
Registered: ‎12-23-2015

Re: Association Rules

Hi,

 

I have a table with 2 Columns ID and Product.

I want to Edit the Script in R, but I can't get it to work

How should it be the read.transactions statement?  

> txn <- read.transactions(dataset, rm.duplicates= TRUE,format='basket',sep='','',cols =1)

 

But I get this error...

Error in readLines(file, encoding = encoding) : 'con' is not a connection

 

 

Moderator
Posts: 64
Registered: ‎08-10-2016

Re: Association Rules

Hi,
 
The function "read.transactions"  reads a transaction data file from disk and creates a transactions object.  The error you get indicates that R could not open the file for reading.
 
 
1) Save your excel as CSV for example to :  "c:\\Users\\someone\\Downloads\\try.csv
2) Run
txn <- read.transactions("c:\\Users\\boefraty\\Downloads\\try.csv", rm.duplicates= TRUE,format='single', sep='','',cols =c(2,1))

Beside this, you should make sure that your data is a transaction object: to store a binary incidence matrix, item labels, and optionally transaction IDs and user IDs.  Check  your format is "single" or "basket"
 
You are welcome to continue and discuss this example with me via email: boefraty@microsoft.com   or here

Moderator
Posts: 64
Registered: ‎08-10-2016

Re: Association Rules

The data with 2 columns: "TransactionID" and "Product" is indeed very natural. Attached is the PBIX with R code suited to such input.

The relevant data transformation is in lines

nnn = names(dataset)

transData <- as(split(dataset[,nnn[2]], dataset[,nnn[1]]), "transactions")

 

The data in PBIX is synthetic.

Capture.JPG

Attachment
New Member
Posts: 1
Registered: ‎01-26-2017

Re: Association Rules

Hello, I downloaded the association rules custom viz and succesfully have it working with the data. I was wondering though if there was any way we could sort the rules based on the RHS? I tried adding a filter, but what that did was exclude the data from ever getting to the association rules algorithm. Is there a way to sort the association rules without excluding data?

Moderator
Posts: 64
Registered: ‎08-10-2016

Re: Association Rules

Capture.JPG

Do you want to sort by second column? 

Regular Visitor
Posts: 40
Registered: ‎09-07-2015

Re: Association Rules

Hi, I have installed r (default version and one that I used is 3.3.2 though) as per provided links and ran the script. When refreshing the PBIX I get the following error:

Moderator
Posts: 64
Registered: ‎08-10-2016

Re: Association Rules

The problem is with "ISLR" package it is required by R connector (not R visual), so you have two workarounds:

1) Don't do refresh

2) call command
install.packages("ISLR")

from any R console