# don't show warnings while knitting
knitr::opts_chunk$set(warning = FALSE, message = FALSE)
library(ggplot2)
library(sqldf)
# constants
my_working_dir_pc <- "C:\\Users\\Alex\\OneDrive - Lehigh University\\GBUS461\\Data\\Sephora\\Sephora"
myFile_products <- "products.csv"
myFile_reviews <- "reviews.csv"
myFile_authors <- "authors.csv"
# import data
setwd(my_working_dir_pc)
products <- read.csv(myFile_products)
reviews <- read.csv(myFile_reviews)
authors <- read.csv(myFile_authors)
sqldf::sqldf("
SELECT *
FROM products p INNER JOIN reviews r ON p.product_id = r.product_id
INNER JOIN authors a ON r.author_id = r.author_id
LIMIT 100
")
# table row count and review count
# check to ensure they are the same
sqldf("
SELECT COUNT(review_id), COUNT(*), COUNT(DISTINCT review_id)
FROM reviews
")
# without "DISTINCT" it does not matter which
# column you use (previously review_id, now author_id)
# it shows the table row count.
# why is counting up DISTINCT authors < table row count?
sqldf("
SELECT COUNT(author_id), COUNT(DISTINCT author_id)
FROM reviews
")
# review count by rating, order = ascending
sqldf("
SELECT rating, COUNT(review_id) AS NumReviews
FROM reviews
GROUP BY rating
ORDER BY rating
")
# review count by rating, order = descending
sqldf("
SELECT rating, COUNT(review_id) AS NumReviews
FROM reviews
GROUP BY rating
ORDER BY rating DESC
")
# convert to date data type, and from the date, calculate month and year
reviews$submission_date <- as.Date(reviews$submission_date, format = "%m/%d/%y")
reviews$ReviewDate <- format(reviews$submission_date, "%d")
reviews$ReviewMonthInt <- as.integer(format(reviews$submission_date, "%m"))
reviews$ReviewMonth <- format(reviews$submission_date, "%b")
reviews$ReviewYear <- format(reviews$submission_date, "%y")
# count and group by
sqldf("
SELECT ReviewMonth, COUNT(review_id) AS NumReviews
FROM reviews
GROUP BY ReviewMonth
ORDER BY ReviewMonthInt
")
# where clause
sqldf("
SELECT ReviewMonth, COUNT(review_id) AS NumReviews
FROM reviews
WHERE ReviewYear = 21 OR ReviewMonthInt BETWEEN 2 AND 6
GROUP BY ReviewMonth
ORDER BY ReviewMonthInt
")
# r bar chart by identity
reviewsByMonth <- sqldf("
SELECT ReviewMonth, ReviewMonthInt, COUNT(review_id) AS Reviews
FROM reviews
WHERE ReviewYear BETWEEN 15 AND 22
GROUP BY ReviewMonth, ReviewMonthInt
ORDER BY ReviewMonthInt
")
barEx <- ggplot(data = reviewsByMonth, aes(x = reorder(ReviewMonth, ReviewMonthInt), y = Reviews)) +
geom_bar(stat = "identity") +
labs(title = "Luxury Brands Engagement by Month", x = "Review Month", y = "# of Reviews")
barEx
# same bar chart by count
barExC <- ggplot(data = reviews[reviews$ReviewYear > 14 & reviews$ReviewYear < 23,],
aes(x = reorder(ReviewMonth, ReviewMonthInt))) + geom_bar(stat = "count") +
labs(title = "Luxury Brands Engagement by Month", x = "Review Month", y = "# of Reviews")
barExC
# average bar chart
avgRatingsByMonth <- sqldf::sqldf("
SELECT ReviewMonth, ReviewMonthInt, AVG(rating) AS AvgRatings
FROM reviews
WHERE ReviewYear BETWEEN 15 AND 22
GROUP BY ReviewMonth, ReviewMonthInt
ORDER BY ReviewMonthInt
")
library(ggplot2)
ggplot(data = avgRatingsByMonth, aes(x = reorder(ReviewMonth, ReviewMonthInt), y = AvgRatings)) +
geom_bar(stat = "identity") +
labs(title = "Luxury Brands Average Rating by Month", x = "Review Month", y = "Average Rating")
# average bar chart with "summary"
ggplot(data = reviews[reviews$ReviewYear > 14 & reviews$ReviewYear < 23,],
aes(x = reorder(ReviewMonth, ReviewMonthInt), y = rating)) + geom_bar(stat = "summary", fun = mean) +
labs(title = "Luxury Brands Average Rating by Month", x = "Review Month", y = "Average Rating")
# generate data for bar chart by identity
d <- sqldf::sqldf("
SELECT primary_category, COUNT(DISTINCT brand_id) AS Brands
FROM products WHERE primary_category < 'H'
GROUP BY primary_category ORDER BY primary_category
")
barExA <- ggplot(data = d, aes(x = primary_category, y = Brands)) +
geom_bar(stat = "identity") +
labs(title = "Brands by Category", x = "Primary Category", y = "# of Brands")
barExAC <- ggplot(data = products[products$primary_category < 'H',], aes(x = primary_category)) +
geom_bar(stat = "count") +
labs(title = "Products by Category", x = "Primary Category", y = "# of Products")
gridExtra::grid.arrange(barExA, barExAC, nrow=1, ncol=2)
# histogram versus bar chart
reviews$submission_date <- as.Date(reviews$submission_date, format = "%m/%d/%y")
reviews$ReviewDate <- format(reviews$submission_date, "%d")
reviews$ReviewMonthInt <- as.integer(format(reviews$submission_date, "%m"))
reviews$ReviewMonth <- format(reviews$submission_date, "%b")
reviews$ReviewYear <- format(reviews$submission_date, "%y")
reviewsByMonth <- sqldf::sqldf("
SELECT ReviewMonth, ReviewMonthInt, COUNT(review_id) AS Reviews
FROM reviews
WHERE ReviewYear BETWEEN 15 AND 22
GROUP BY ReviewMonth, ReviewMonthInt
ORDER BY ReviewMonthInt
")
library(ggplot2)
barEx <- ggplot(data = reviewsByMonth, aes(x = reorder(ReviewMonth, ReviewMonthInt), y = Reviews)) +
geom_bar(stat = "identity") +
labs(title = "Luxury Brands Engagement by Month", x = "Review Month", y = "# of Reviews")
hisEx <- ggplot(data = reviews, aes(rating)) +
geom_histogram(binwidth = .5) +
labs(title = "Luxury Brands Ratings Distribution", x = "Review Ratings (out of 5 Stars)", y = "# of Reviews")
gridExtra::grid.arrange(hisEx, barEx, nrow=1, ncol=2)
# histogram: all products' average lifetime ratings
ggplot(data = products[is.na(products$rating) == FALSE,], aes(rating)) +
geom_histogram(binwidth = .1) +
labs(title = "Ratings Distribution of All Products", x = "Average Ratings (out of 5 Stars)", y = "# of Products")
#Discussion: possible next steps of analysis?
# histogram: all products' prices
ggplot(data = products[is.na(products$price_usd) == FALSE,], aes(price_usd)) +
geom_histogram(binwidth = 5) +
labs(title = "Price Distribution of All Products", x = "Prices (USD)", y = "# of Products")
#Discussion: possible next steps of analysis?
# bar chart that shows product count by category
# same bar chart by count
bar_PCategories <- ggplot(data = products,
aes(x = primary_category)) + geom_bar(stat = "count") +
labs(title = "Number of Products in Each Category on Sephora.com", x = "Primary Category", y = "# of Products")
bar_PCategories
ggplot(data = reviewsByMonth, aes(x = reorder(ReviewMonth, ReviewMonthInt), y = Reviews)) +
geom_bar(stat = "identity") + coord_flip() +
labs(title = "Luxury Brands Engagement by Month", x = "Review Month", y = "# of Reviews")
# average prices of skincare products by highlights
# before we plot the bar chart, we need to generate its input
# for each product, we need to do text processing to split the highlights column
# and correspond the results to the product id
# once we figure out how to do it for 1 product, we loop through the entire
# table and do this processing for all products
# create an empty table called "highlight_table"
# it has two columns, product id and a single highlight
# if a product has multiple highlights, say, 2, it will show up as 2 rows
# in this table
highlight_table <- data.frame(product_id = character(), highlight = character())
# product_id is column 1 in the products table
# highlights is column 21 in the products table
colProductId <- 1
colHighlights <- 21
for (i in 1:nrow(products)){
#for (i in 1:1) {
current_highlights <- products[i,colHighlights] #get the highlights from products
if(nchar(current_highlights) > 0){ #keep going only if highlights is not blank
current_product_id <- products[i,colProductId] #get the product id
current_highlights <- gsub("\\[|\\]|'", "", current_highlights)
#print(current_product_id)
#Split the input using the comma as a separator
current_highlights <- unlist(strsplit(current_highlights, ", "))
highlight_table <- rbind(highlight_table, data.frame(product_id = current_product_id, highlight = current_highlights))
}
}
# after we have created and populated the highlights table
# take a look and do some simple counts
# E.g, how many products have highlights? 6287
# and how many highlights are there?
sqldf("
SELECT COUNT(DISTINCT product_id) products, COUNT(DISTINCT highlight) highlights
FROM highlight_table
")
# now, let's merge the highlight_table with the products table
# and calculate the average by each highlight
skincareHighlightsPrices <- sqldf::sqldf("
SELECT h.highlight AS Highlight, AVG(price_usd) AS AvgPrice
FROM highlight_table h INNER JOIN products p
ON h.product_id = p.product_id
WHERE primary_category = 'Skincare'
GROUP BY h.highlight
HAVING COUNT(DISTINCT h.product_id) > 10
ORDER BY AVG(price_usd) DESC
LIMIT 50
")
# average prices of skincare products by highlights, bar chart
# vertical bar chart
bar_HighlightPrices <- ggplot(data = skincareHighlightsPrices, aes(x = reorder(Highlight, AvgPrice), y = AvgPrice)) +
geom_bar(stat = "identity") + coord_flip() +
labs(title = "Top 50 Highlights of Skincare Products by Avg. Price", x = "Highlight", y = "Avg. Product Price")
#ggsave("bar_HighlightPrices.pdf") # saves the graph to a PDF file
bar_HighlightPrices
# average ratings of skincare products by highlights, bar chart
# now, let's merge the highlight_table with the products table
# and calculate the average by each highlight
skincareHighlightsRatings <- sqldf::sqldf("
SELECT h.highlight AS Highlight, AVG(rating) AS AvgRating
FROM highlight_table h INNER JOIN products p
ON h.product_id = p.product_id
WHERE primary_category = 'Skincare'
GROUP BY h.highlight
HAVING COUNT(DISTINCT h.product_id) > 10
ORDER BY AVG(rating) DESC
LIMIT 50
")
# vertical bar chart
bar_HighlightRatings <- ggplot(data = skincareHighlightsRatings, aes(x = reorder(Highlight, AvgRating), y = AvgRating)) +
geom_bar(stat = "identity") + coord_flip() +
labs(title = "Top 50 Highlights of Skincare Products by Avg. Rating", x = "Highlight", y = "Avg. Product Rating")
bar_HighlightRatings
# operators
sqldf::sqldf("
SELECT COUNT(a.author_id), COUNT(DISTINCT a.author_id)
FROM reviews r INNER JOIN authors a
ON r.author_id = a.author_id
WHERE (r.rating >= 4 AND r.helpfulness > 0.5)
OR a.skin_tone = 'light'
")
# operator NOT IN
sqldf::sqldf("
SELECT COUNT(author_id)
FROM authors
WHERE skin_tone NOT IN ('light', 'medium')
")
# grouped bar chart
d <- sqldf::sqldf("
SELECT skin_tone, rating,COUNT(r.review_id) AS Reviews
FROM reviews r
INNER JOIN authors a ON r.author_id = a.author_id
GROUP BY skin_tone, rating
")
ggplot(data = d, aes(x = rating, y = Reviews, fill = skin_tone)) + geom_bar(stat = "identity", position = position_dodge()) +
labs(title = "Reviews by Rating and Skin Tone", x = "Review Rating", y = "# of Reviews", fill = "Skin Tone")
# stacked bar chart
d <- sqldf::sqldf("
SELECT skin_tone, rating,COUNT(r.review_id) AS Reviews
FROM reviews r
INNER JOIN authors a ON r.author_id = a.author_id
GROUP BY skin_tone, rating
")
ggplot(data = d, aes(x = rating, y = Reviews, fill = skin_tone)) + geom_bar(stat = "identity") +
labs(title = "Reviews by Rating and Skin Tone", x = "Review Rating", y = "# of Reviews", fill = "Skin Tone")
# stacked bar chart that shows percentages
d1 <- sqldf::sqldf("SELECT skin_tone, rating,COUNT(r.review_id) AS Reviews
FROM reviews r INNER JOIN authors a ON r.author_id = a.author_id
GROUP BY skin_tone, rating")
d2 <- sqldf::sqldf("
SELECT rating,COUNT(r.review_id) AS Reviews
FROM reviews r
INNER JOIN authors a ON r.author_id = a.author_id
GROUP BY rating")
d <- sqldf::sqldf("SELECT d1.skin_tone, d1.rating, d1.Reviews*1.0/d2.Reviews AS PercReviews
FROM d1 INNER JOIN d2 ON d1.rating = d2.rating")
ggplot(data = d, aes(x = rating, y = PercReviews, fill = skin_tone)) + geom_bar(stat = "identity") +
labs(title = "% Reviews by Rating and Skin Tone", x = "Review Rating", y = "% of Reviews", fill = "Skin Tone")
# pie charts, 2D and 3D
totalReviews <- nrow(reviews)
sqlStr <- paste("SELECT rating, COUNT(review_id)*1.0/", as.character(totalReviews), sep="")
sqlStr <- paste(sqlStr, "AS Reviews FROM reviews GROUP BY rating")
d <- sqldf::sqldf(sqlStr)
library(plotrix) #for plotting 3D pie charts
#native R function
pie(d$Reviews, labels = d$rating)
# pie chart with ggplot
ggplot(d, aes(x = "", y = Reviews, fill = rating)) +
geom_col() + geom_text(aes(label = round(Reviews,3)),
position = position_stack(vjust = 0.5)) +
coord_polar(theta = "y") +
labs(title = "% Reviews by Rating", fill = "Rating")
# pie chart with ggplot
ggplot(d, aes(x = rating, y = Reviews, fill = rating)) +
geom_col() + geom_text(aes(label = round(Reviews,3)),
position = position_stack(vjust = 0.5)) + coord_flip() +
labs(title = "% Reviews by Rating", y = "% Reviews", fill = "Rating")
ggplot(d, aes(rating, Reviews)) +
geom_bar(stat = "identity") +
coord_flip() +
labs(title = "Reviews by Rating", x = "# of Reviews", y = "Rating")
# generate data
d <- sqldf::sqldf("
SELECT ReviewYear, rating, COUNT(review_id) aS Reviews
FROM reviews WHERE ReviewYear BETWEEN 15 AND 22 GROUP BY ReviewYear, rating
")
# area chart
ggplot(data=d, aes(x=as.integer(ReviewYear), y=Reviews, fill=as.character(rating))) + geom_area() +
labs(title = "Reviews by Rating", x = "Year", y = "# of Reviews", fill = "Rating")
# area chart with year breaks
ggplot(data=d, aes(x=as.integer(ReviewYear), y=Reviews, fill=as.character(rating))) + geom_area() +
labs(title = "Reviews by Rating", x = "Year", y = "# of Reviews", fill = "Rating") +
scale_x_continuous(breaks=c(15,16,17,18,19,20,21,22))
# line chart
ggplot(data=d[d$rating==5,], aes(x=as.integer(ReviewYear), y=Reviews)) + geom_line() + geom_point() +
scale_x_continuous(breaks=c(15,16,17,18,19,20,21,22)) +
labs(title = "5-Star Reviews by Year", x = "Year", y = "# of Reviews", fill = "Rating")
# multiple-series line chart
ggplot(data=d, aes(x=as.integer(ReviewYear), y=Reviews, group=as.character(rating))) +
geom_line(aes(linetype=as.character(rating))) + geom_point() +
scale_x_continuous(breaks=c(15,16,17,18,19,20,21,22)) +
labs(title = "Yearly Reviews by Rating", x = "Year", y = "# of Reviews") +
theme(legend.position="bottom") + theme(legend.title = element_blank())
# boxplot
ggplot(data=products[products$price_usd < 1000,], aes(x="",y=price_usd)) + geom_boxplot() +
labs(title = "Product Price Distribution", x = "", y = "Price")
# horizontal boxplot
ggplot(data=products[products$price_usd < 1000,], aes(x="",y=price_usd)) + geom_boxplot() +
labs(title = "Product Price Distribution", x = "", y = "Price") + coord_flip()
ggplot(data=products[products$price_usd < 1000,], aes(x=primary_category,y=price_usd)) + geom_boxplot() +
labs(title = "Product Price Distribution", x = "Primary Category", y = "Price") + coord_flip()
#AF original code
# generate data
d1 <- sqldf::sqldf("
SELECT ReviewYear, rating, COUNT(review_id) aS Reviews
FROM reviews WHERE ReviewYear BETWEEN 15 AND 23 GROUP BY ReviewYear, rating
")
d2 <- sqldf::sqldf("
SELECT ReviewYear, COUNT(review_id) as Reviews
FROM reviews
WHERE ReviewYear BETWEEN 15 AND 23
GROUP BY ReviewYear
")
d <- sqldf::sqldf("
SELECT d1.ReviewYear, d1.rating, d1.Reviews*1.0/d2.Reviews as PercReviews
FROM d1 INNER JOIN d2 on d1.ReviewYear = d2.ReviewYear
")
# area chart with year breaks
ggplot(data=d, aes(x=as.integer(ReviewYear), y=PercReviews, fill=as.character(rating))) + geom_area() +
labs(title = "Reviews by Rating", x = "Year", y = "# of Reviews", fill = "Rating") +
scale_x_continuous(breaks=c(15,16,17,18,19,20,21,22))
head(reviews)
reviews$ReviewDayOfWeek <- weekdays(reviews$submission_date)
d <- sqldf::sqldf("
SELECT ReviewDayOfWeek as day, AVG(rating) as avgRating
FROM reviews
GROUP BY day
")
ggplot(data = d, aes(x = day, y = avgRating, fill = day)) + geom_bar(stat = "identity", position = position_dodge()) +
labs(title = "Average Rating by Day", x = "Day of Week", y = "Average Rating", fill = "Day of Week")
d <- sqldf::sqldf("
SELECT ReviewDayOfWeek as day, COUNT(review_id) as numberOfReviews
FROM reviews
GROUP BY day
")
ggplot(data = d, aes(x = day, y = numberOfReviews, fill = day)) + geom_bar(stat = "identity", position = position_dodge()) +
labs(title = "Number of Reviews on Each Day", x = "Day of Week", y = "# of Reviews", fill = "Day of Week")
ggplot(data=reviews, aes(x=ReviewDayOfWeek,y=rating, fill = ReviewDayOfWeek)) + geom_boxplot() +
labs(title = "Rating Distribution by Day", x = "Day of Week", y = "Average Rating")
ggplot(products, aes(x = price_usd, y = rating)) +
geom_point() +
geom_smooth(method = "lm", se = FALSE) +
xlim(0,500)
t.test(products$rating, products$price_usd)
##
## Welch Two Sample t-test
##
## data: products$rating and products$price_usd
## t = -81.498, df = 8494.6, p-value < 2.2e-16
## alternative hypothesis: true difference in means is not equal to 0
## 95 percent confidence interval:
## -48.60265 -46.31952
## sample estimates:
## mean of x mean of y
## 4.194513 51.655595