Group Project
Muhammad Umair (Muhammad Umair)
Kong
Wardatul Fadhilah Binti Amir Nazri (S2039977)
5/22/2021
Amazon Product Reviews R
INTRODUCTION
As consumers, we want to spend on worth-buy products.
While in-store staff and the sellers will always say good things,
we want to know more about the product we are interested on, from both aspects - the good and the bad.
One of many others way to gain this info, is through experience from existing user which is tend to be would be more reliable.
PROBLEM STATEMENT
Most of the common rating systems only show the average rating scale from 1 to 5.
However, this type of rating showcase is too general and if prospect buyer want to know
the actual comment/review of the specific product, they will have to browse through the comment section and read one by one.
This process is time consuming and the buyer might missed out the critical feedback due to time-constraint.
OBJECTIVE
a. To give aggregated text visualization
Therefore, the purpose of this apps is to help the prospect buyers to be able to quickly browse through the reviews summary and gain more detailed understanding on the product via word cloud.
b. Provide pro and cons of the product
Two word clouds are being generated of this sentiment analysis of both positive reviews and negative reviews.
DATA SOURCE & CLEANING
Write Data source and cleaning details here
SHINY APP & CODE
Guidelines
- User can search the interested appliances in the search column.“, strong(”Eg: Refrigerator, Stove, Oven, Washer, Dryer, Humidfier"), User can choose to narrow the search by selecting the brand of ideal appliances from dropdown menu.
- A table with the selected type of appliances will be listed based on the review counts in desending order.
- User can further select any product from the table product to view the word cloud. Table of the review’s summary also will be shown once click on the product.
- Click here to open shiny app in new tab
Shiny App Screen shoot
Shiny App
Shiny App Code
shinyApp(
con <- dbConnect(RPostgres::Postgres(), dbname='pds-project',
host = 'pdsgrouproject.carynrwc1v04.ap-southeast-1.rds.amazonaws.com',
port = 5432,
user = 'pdsgroupproject',
password = 'pdsadmin123'),
ui = fluidPage(
titlePanel("Amazon Product Reviews"),
p("Amazon Product Reviews", id= "title"),
tags$head(
tags$script(HTML('
Shiny.addCustomMessageHandler("updatechildrow", function(data) {
//console.log(data);
reviews = data[1];
reviews_count = reviews["overall"].length;
dataset = [];
for (i=0; i<reviews_count; i++) {
d = [];
d.push(reviews["overall"][i]);
d.push(reviews["vote"][i]);
d.push(reviews["summary"][i]);
d.push(reviews["reviewtext"][i]);
dataset.push(d);
}
$("div[id*=\'child_details\']:not(#child_details+data[0])").parent().parent().remove();
$("tr.selected").removeClass("selected");
$("#child_details"+data[0]).DataTable({
data: dataset,
columns: [
{ title: "Rating" },
{ title: "Votes" },
{ title: "Summary" },
{ title: "Review" }
]
});
})
'))
),
sidebarLayout(
sidebarPanel(
textInput("txtSearch", "Search product:"),
selectInput("selectBrand", "Brand", c('All')),
actionButton("btnSearch", "Search"),
plotOutput("good_review"),
plotOutput("bad_review")
),
mainPanel(
dataTableOutput("tblProducts")
)
)),
server = function(input, output, session) {
brands <- observeEvent(input$txtSearch, {
if (input$txtSearch != "") {
sql <- paste("SELECT DISTINCT brand FROM amazon_prod_reviews dwd ",
"WHERE UPPER(title) LIKE '%", toupper(input$txtSearch), "%' ",
"ORDER BY brand",
sep="")
#cat(sql,"\n")
res <- dbGetQuery(con, sql)
# Update the selection for Brand. Include the 'All' option to select all brands.
updateSelectInput(session, "selectBrand", choices=c('All',res), selected='All')
}
})
# Update data table with selected brand.
df <- eventReactive(input$btnSearch, {
if (input$txtSearch != "") {
sql <- paste("SELECT AVG(overall) overall_avg, COUNT(overall) review_count, ",
"title, brand FROM amazon_prod_reviews ",
"WHERE UPPER(title) LIKE '%", toupper(input$txtSearch), "%' ",
sep="")
# If 'All' is not selected, construct the SQL to search for only the specific brand.
if (input$selectBrand != 'All') {
sql <- paste(sql,
"AND brand = ", dbQuoteString(con, input$selectBrand), " ",
sep="")
}
sql <- paste(sql,
"GROUP BY Title, Brand ORDER BY review_count DESC, overall_avg DESC ",
# "LIMIT 10 ",
sep="")
#cat(sql,"\n")
res <- dbGetQuery(con, sql)
}
})
# dataset for good review
datasource <- eventReactive(input$productselect,{
wordCloudDf <- data.frame()
sql <- paste("SELECT overall, summary FROM amazon_prod_reviews ",
"WHERE title = '", input$productselect[5], "' ",
"AND overall >= 3",
sep="")
wordCloudDf <- dbGetQuery(con, sql)
good_t_w <-
wordCloudDf %>%
select(summary)
tokens <- good_t_w %>%
unnest_tokens(word, summary) %>%
count(word, sort = TRUE) %>%
ungroup()
tokens_clean <- tokens %>%
anti_join(stop_words)
nums <- tokens_clean %>% filter(str_detect(word, "^[0-9]")) %>% select(word) %>% unique()
tokens_clean <- tokens_clean %>%
anti_join(nums, by = "word")
tokens_clean <- tokens_clean %>%
anti_join(data.frame(word=c("star","stars")), by = "word")
return(tokens_clean)
})
# dataset for bad review
datasource_v2 <- eventReactive(input$productselect,{
sql <- paste("SELECT overall, summary FROM amazon_prod_reviews ",
"WHERE title = '", input$productselect[5], "' ",
"AND overall < 3",
sep="")
wordCloudDf <- dbGetQuery(con, sql)
bad_t_w <-
wordCloudDf %>%
select(summary)
tokens2 <- bad_t_w %>%
unnest_tokens(word, summary) %>%
count(word, sort = TRUE) %>%
ungroup()
tokens_clean2 <- tokens2 %>%
anti_join(stop_words)
nums <- tokens_clean2 %>% filter(str_detect(word, "^[0-9]")) %>% select(word) %>% unique()
tokens_clean2 <- tokens_clean2 %>%
anti_join(nums, by = "word")
tokens_clean2 <- tokens_clean2 %>%
anti_join(data.frame(word=c("star","stars")), by = "word")
return(tokens_clean2)
})
# wordcloud for good review
output$good_review <- renderPlot({
x <- datasource()$word
y <- datasource()$n
minfreq_bigram <- 2
wordcloud(x,y,random.order=FALSE,scale = c(3,0.5),min.freq = minfreq_bigram,colors = brewer.pal(8,"Dark2"),
max.words=50)
})
# wordcloud for bad review
output$bad_review <- renderPlot({
x <- datasource_v2()$word
y <- datasource_v2()$n
minfreq_bigram <- 2
wordcloud(x,y,random.order=FALSE,scale = c(3,0.5),min.freq = minfreq_bigram,colors = brewer.pal(8,"Dark2"),
max.words=50)
})
output$tblProducts <- renderDataTable(
{ df() },
filter = list(position='top', clear=FALSE),
callback = JS("
var format = function(d) {
Shiny.setInputValue('productselect', [d.index(), d.data()]);
return d.data();
}
table.on('click', 'td', function() {
var tr = $(this).closest('tr');
var row = table.row(tr);
var rowData = row.data();
var index = row.index();
if (row.child.isShown()) {
row.child.hide();
}
else {
row.child('<table id=\"child_details'+index+'\"></table>').show();
format(row);
}
});
")
)
prod <- observeEvent(input$productselect, {
# print(input$productselect[1])
# print(input$productselect[5])
sql <- paste("SELECT overall, vote, summary, reviewtext FROM amazon_prod_reviews ",
"WHERE title=",dbQuoteString(con,input$productselect[5]), sep="")
res <- dbGetQuery(con, sql)
session$sendCustomMessage("updatechildrow", list(input$productselect[1], res))
})
},
options = list(height = 1000)
)