Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear All, I'm Trying to put the following code Inside R script visual:
#############################
library(arules)
library(Rsenal)
library(tidyverse)
#### Assignment of column names ####
setwd("C:/Users/XXXXXX/Data Worked")
## assigning column names to variables to help with filtering with NSE
day_of_the_week <- "day_of_the_week"
order_month <- "order_month"
order_year <- "order_year"
day_of_the_month <- "day_of_the_month"
order_hour <- "order_hour"
order_day_part <- "order_day_part"
order_month_n <- "order_month_n"
order_date <- "Order Date"#"order_business_date" #Order Date
#order_open <- "order_opened_at" #orders.opened_at
order_open <- "orders.opened_at" #orders.opened_at
#transaction_id <- "order_guid" #orders.guid
transaction_id <- "orders.guid" #orders.guid
#product_names <- "product_name_en" #products.name_en
product_names <- "Product" #products.name_en , branches.name_en
#branch_name <- "Branch"
branch_name <- "Branch"
oerder_tyep <-"Order_Type"
#product_sale_quantities <- "product_quantity" ,orders.products.quantity
product_sale_quantities <- "orders.products.quantity"
# End of Column Name assignments #
#### Feature generation ####
dd <- weekdays(as.Date(dataset[,order_date]))
dataset$day_of_the_week <- dd
## year of the order is added
YY <- format(as.Date(dataset[,order_date], format="%Y-%m-%d"),"%Y")
dataset$order_year<- YY
## order month is added to the data.
mm<-format(as.Date(dataset[,order_date], format="%Y-%m-%d"),"%m")
dataset$order_month <- mm
bb<-format(as.Date(dataset[,order_date], format="%Y-%m-%d"),"%b")
dataset$order_month_n <- bb
## order day of the month is added
dom <- format(as.Date(dataset[,order_date], format="%Y-%m-%d"),"%d")
dataset$day_of_the_month <- dom
## order hour is added orders.opened_at
hour<- format(strptime(dataset[, order_open], format="%Y-%m-%d %H:%M:%S"),"%H")
#hour<- format(strptime(dataset[, orders.opened_at], format="%Y-%m-%d %H:%M:%S"),"%H")
dataset$order_hour <- as.numeric(hour)
dataset$order_day_part <- ifelse(dataset$order_hour >= 6 & dataset$order_hour <= 10, "Morning(06:00 to 10:00)",
ifelse(dataset$order_hour >= 11 & dataset$order_hour <= 15, "Afternoon(11:00 to 15:00)",
ifelse(dataset$order_hour >= 16 & dataset$order_hour <= 20, "Evening(16:00 to 20:00)",
ifelse(dataset$order_hour >= 21, "Night(21:00 to 24:00)",
"Midnight(00:00 to 06:00)"))))
dataset$order_day_part <- ordered(dataset$order_day_part, levels = c("Midnight(00:00 to 06:00)", "Morning(06:00 to 10:00)",
"Afternoon(11:00 to 15:00)", "Evening(16:00 to 20:00)",
"Night(21:00 to 24:00)"))
order_string <- as.character(transaction_id)
product_string <- as.character(product_names)
dataset1 <- select_(dataset, transaction_id, product_names, branch_name, product_sale_quantities,
day_of_the_week, order_month, order_year, day_of_the_month, order_day_part,
order_month_n)
# Feature generation end #
#### Possible filter values ####
day <- as.list(c("All", sort(unique(as.character(dataset1[,day_of_the_week])))))
branch <- as.list(c("All", sort(unique(as.character(dataset1[,branch_name])))))
month <- as.list(c("All", sort(unique(dataset1[,order_month_n]))))
year <- as.list(c("All", sort(unique(dataset1[,order_year]))))
# Possible filter values end #
#### Filter area #####
day_filter <- "All"
branch_filter <- "All"
month_filter <- "All"
year_filter <- "All"
# Filter area end #
#### Filter operation start ####
if(day_filter %in% c("All") & branch_filter %in% c("All") & month_filter %in% c("All") & year_filter %in% c("All")){
dataset <- as(split(dataset1[,product_string], dataset1[,order_string]), "transactions")
} else if(day_filter %in% c("All") & branch_filter != "All" & month_filter %in% c("All") & year_filter %in% c("All")){
trans <- select_(dataset1, transaction_id, product_names, branch_name, as.character(day_of_the_week),
as.character(order_month), as.character(order_year), as.character(order_day_part))
trans <- filter_(trans, lazyeval::interp(~var == branch_filter, var = as.name(branch_name)))
dataset <- as(split(trans[,product_string], trans[,order_string]), "transactions")
} else if(day_filter %in% c("All") & branch_filter == "All" & month_filter != "All" & year_filter %in% c("All")){
trans <- select_(dataset1, transaction_id, product_names, branch_name, as.character(day_of_the_week),
order_month, as.character(order_year), as.character(order_day_part),
order_month_n)
trans <- filter_(trans, lazyeval::interp(~var == month_filter, var = as.name(order_month_n)))
dataset <- as(split(trans[,product_string], trans[,order_string]), "transactions")
} else if(day_filter %in% c("All") & branch_filter == "All" & month_filter == "All" & year_filter != "All"){
trans <- select_(dataset1, transaction_id, product_names, branch_name, as.character(day_of_the_week),
as.character(order_month), as.character(order_year), as.character(order_day_part),
order_month_n)
trans <- filter_(trans, lazyeval::interp(~var == year_filter, var = as.name(order_year)))
dataset <- as(split(trans[,product_string], trans[,order_string]), "transactions")
} else if(day_filter %in% c("All") & branch_filter != "All" & month_filter == "All" & year_filter != "All"){
trans <- select_(dataset1, transaction_id, product_names, branch_name, as.character(day_of_the_week),
as.character(order_month), as.character(order_year), as.character(order_day_part),
order_month_n)
trans <- filter_(trans, lazyeval::interp(~var == year_filter, var = as.name(order_year)),
lazyeval::interp(~var == branch_filter, var = as.name(branch_name)))
dataset <- as(split(trans[,product_string], trans[,order_string]), "transactions")
} else if(day_filter %in% c("All") & branch_filter != "All" & month_filter != "All" & year_filter %in% c("All")){
trans <- select_(dataset1, transaction_id, product_names, branch_name, as.character(day_of_the_week),
as.character(order_month), as.character(order_year), as.character(order_day_part),
order_month_n)
trans <- filter_(trans, lazyeval::interp(~ var == branch_filter, var = as.name(branch_name)),
lazyeval::interp(~ var == month_filter, var = as.name(as.character(order_month_n))))
dataset <- as(split(trans[,product_string], trans[,order_string]), "transactions")
} else if(day_filter %in% c("All") & branch_filter == "All" & month_filter != "All" & year_filter != "All"){
trans <- select_(dataset1, transaction_id, product_names, branch_name, as.character(day_of_the_week),
as.character(order_month), as.character(order_year), as.character(order_day_part),
order_month_n)
trans <- filter_(trans, lazyeval::interp(~ var == year_filter, var = as.name(order_year)),
lazyeval::interp(~ var == month_filter, var = as.name(as.character(order_month_n))))
dataset <- as(split(trans[,product_string], trans[,order_string]), "transactions")
} else if(day_filter %in% c("All") & branch_filter != "All" & month_filter != "All" & year_filter != "All"){
trans <- select_(dataset1, transaction_id, product_names, branch_name, as.character(day_of_the_week),
as.character(order_month), as.character(order_year), as.character(order_day_part),
order_month_n)
trans <- filter_(trans, lazyeval::interp(~ var == year_filter, var = as.name(order_year)),
lazyeval::interp(~ var == month_filter, var = as.name(as.character(order_month_n))),
lazyeval::interp(~ var == branch_filter, var = as.name(branch_name)))
dataset <- as(split(trans[,product_string], trans[,order_string]), "transactions")
} else if(day_filter != "All" & branch_filter == "All" & month_filter != "All" & year_filter != "All"){
trans <- select_(dataset1, transaction_id, product_names, branch_name, as.character(day_of_the_week),
as.character(order_month), as.character(order_year), as.character(order_day_part),
order_month_n)
trans <- filter_(trans, lazyeval::interp(~ var == day_filter, var = as.name(as.character(day_of_the_week))),
lazyeval::interp(~ var == month_filter, var = as.name(as.character(order_month_n))),
lazyeval::interp(~ var == year_filter, var = as.name(order_year)))
dataset <- as(split(trans[,product_string], trans[,order_string]), "transactions")
} else if(day_filter != "All" & branch_filter == "All" & month_filter != "All" & year_filter == "All"){
trans <- select_(dataset1, transaction_id, product_names, branch_name, as.character(day_of_the_week),
as.character(order_month), as.character(order_year), as.character(order_day_part),
order_month_n)
trans <- filter_(trans, lazyeval::interp(~ var == day_filter, var = as.name(as.character(day_of_the_week))),
lazyeval::interp(~ var == month_filter, var = as.name(as.character(order_month_n))))
dataset <- as(split(trans[,product_string], trans[,order_string]), "transactions")
} else if(day_filter != "All" & branch_filter == "All" & month_filter == "All" & year_filter == "All"){
trans <- select_(dataset1, transaction_id, product_names, branch_name, as.character(day_of_the_week),
as.character(order_month), as.character(order_year), as.character(order_day_part),
order_month_n)
trans <- filter_(trans, lazyeval::interp(~ var == day_filter, var = as.name(as.character(day_of_the_week))))
dataset <- as(split(trans[,product_string], trans[,order_string]), "transactions")
} else if(day_filter != "All" & branch_filter == "All" & month_filter == "All" & year_filter != "All"){
trans <- select_(dataset1, transaction_id, product_names, branch_name, as.character(day_of_the_week),
as.character(order_month), as.character(order_year), as.character(order_day_part))
trans <- filter_(trans, lazyeval::interp(~ var == day_filter, var = as.name(as.character(day_of_the_week))),
lazyeval::interp(~ var == year_filter, var = as.name(order_year)))
dataset <- as(split(trans[,product_string], trans[,order_string]), "transactions")
} else if(day_filter != "All" & branch_filter != "All" & month_filter == "All" & year_filter == "All"){
trans <- select_(dataset1, transaction_id, product_names, branch_name, as.character(day_of_the_week),
as.character(order_month), as.character(order_year), as.character(order_day_part))
trans <- filter_(trans, lazyeval::interp(~ var == day_filter, var = as.name(as.character(day_of_the_week))),
lazyeval::interp(~ var == branch_filter, var = as.name(branch_name)))
dataset <- as(split(trans[,product_string], trans[,order_string]), "transactions")
} else if(day_filter != "All" & branch_filter != "All" & month_filter == "All" & year_filter != "All"){
trans <- select_(dataset1, transaction_id, product_names, branch_name, as.character(day_of_the_week),
as.character(order_month), as.character(order_year), as.character(order_day_part),
order_month_n)
trans <- filter_(trans, lazyeval::interp(~ var == day_filter, var = as.name(as.character(day_of_the_week))),
lazyeval::interp(~ var == branch_filter, var = as.name(branch_name)),
lazyeval::interp(~ var == year_filter, var = as.name(order_year)))
dataset <- as(split(trans[,product_string], trans[,order_string]), "transactions")
} else if(day_filter != "All" & branch_filter != "All" & month_filter != "All" & year_filter == "All") {
trans <- select_(dataset1, transaction_id, product_names, branch_name, as.character(day_of_the_week),
as.character(order_month), as.character(order_year), as.character(order_day_part),
order_month_n)
trans <- filter_(trans, lazyeval::interp(~ var == day_filter, var = as.name(as.character(day_of_the_week))),
lazyeval::interp(~ var == branch_filter, var = as.name(branch_name)),
lazyeval::interp(~ var == month_filter, var = as.name(as.character(order_month_n))))
dataset <- as(split(trans[,product_string], trans[,order_string]), "transactions")
} else {
trans <- select_(dataset1, transaction_id, product_names, branch_name, as.character(day_of_the_week),
as.character(order_month), as.character(order_year), as.character(order_day_part),
order_month_n)
trans <- filter_(trans, lazyeval::interp(~ var == day_filter, var = as.name(as.character(day_of_the_week))),
lazyeval::interp(~ var == branch_filter, var = as.name(branch_name)),
lazyeval::interp(~ var == month_filter, var = as.name(as.character(order_month_n))),
lazyeval::interp(~ var == year_filter, var = as.name(order_year)))
dataset <- as(split(trans[,product_string], trans[,order_string]), "transactions")
}
############################# From Alih
# arAll <- apriori(dataset, parameter=list(support = 0.001, confidence = 0.001,
# minlen = 2))
#
# Orders_Basket_Table <- inspect(sort(arAll, by = "confidence"),
# ruleSep = "", setStart = "",
# setEnd = "")
#############################
#rules_1 <- apriori(reports,parameter = list(supp = 0.001, conf = 0.001, target = "rules"))
rules_1 <- apriori(dataset,parameter = list(supp = 0.001, conf = 0.001, target = "rules"))
t =data.frame( lhs = labels( lhs(rules_1) ),
rhs = labels( rhs(rules_1) ),
quality(rules_1) )
#t = inspect(head(sort(rules, by ="support"),15))
#t = inspect(sort(rules_1, by ="support"))
#############
t$rhs<-as.character(t$rhs)
t$lhs<-as.character(t$lhs)
t$rhs<-gsub("{", "", t$rhs,fixed="TRUE")
t$rhs<-gsub("}", "", t$rhs,fixed="TRUE")
t$lhs<-gsub("{", "", t$lhs,fixed="TRUE")
t$lhs<-gsub("}", "", t$lhs,fixed="TRUE")
t$No_Of_LHS<- rowSums(t== ",")
x <- t$lhs
t$No_Of_LHS<-sapply(regmatches(x, gregexpr(",", x)), length)+1
t$LHS_Start_With <- gsub("^(.*?),.*", "\\1", t$lhs)
write.csv(t,"Orders_Basket_Table_1.csv")
##############data table
# rules_dt <- data.table( lhs = labels( lhs(rules_1) ),
# rhs = labels( rhs(rules_1) ),
# quality(rules_1) )[ order(-lift), ]
#
# # XX<-data.frame( lhs = labels( lhs(rules) ),
# # rhs = labels( rhs(rules) ),
# # quality(rules) )
#
# DT::datatable(rules_dt)
# #write.csv(dataset,"setting.csv")
############
par(mar = c(0,0,0,0))
plot(c(0, 0), c(0, 0))
if (is.null(t)) {
t = data.frame("no rules found")
text(x = 0.5, y = 0.5, paste("No Rules found"),
cex = 1.6, col = "black")
} else {
addtable2plot(-1, -1, t, bty = "n", display.rownames = F, hlines = F,
vlines = F)
}
###########################
When I Run this script in power bi I got errors (please refer to the image errors attached with this message).
Solved! Go to Solution.
Hello @Anonymous,
Please send an email to pbicvsupport@microsoft.com with error details.
We'll look into this issue.
Ignat Vilesov,
Software Engineer
Microsoft Power BI Custom Visuals
Hello @MAAbdullah47,
Can you send description of this issue to pbicvsupport@microsoft.com?
We'll include our R developer to address the issue.
Ignat Vilesov,
Software Engineer
Microsoft Power BI Custom Visuals
Ok I'll send you the email tomorrow.
So save my email (m.mubarah@quantda.net)
I got a similar error too - any solutions for this?
Hello @Anonymous,
Please send an email to pbicvsupport@microsoft.com with error details.
We'll look into this issue.
Ignat Vilesov,
Software Engineer
Microsoft Power BI Custom Visuals
Ok, The email I'll send from is: m.mubarah@quantda.net.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
2 | |
2 | |
1 | |
1 |