#Project
# Importing Dataset
library(readr)
supermarket <- read_csv("C:/Users/hp/Downloads/4th semester/cap482/Supermarket sales/supermarket_sales - Sheet1.csv")
## Rows: 1000 Columns: 17
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (8): Invoice ID, Branch, City, Customer type, Gender, Product line, Dat...
## dbl (8): Unit price, Quantity, Tax 5%, Total, cogs, gross margin percentage...
## time (1): Time
##
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
# <---- Data Pre-Processing ---->
# Mutating columns A. Branch and City --> Branch_and_City
library(dplyr)
##
## Attaching package: 'dplyr'
##
## The following objects are masked from 'package:stats':
##
## filter, lag
##
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
df<-supermarket
# Q1 Mutating columns for the simplicity
# finding the number of occurrence of each branch in each city
t1<-table(supermarket[,c(2,3)])
print(t1)
## City
## Branch Mandalay Naypyitaw Yangon
## A 0 0 340
## B 332 0 0
## C 0 328 0
# Key observation: we see that in each city there is one branch, so we can say that the branches are equally distributed among the cities.Therefore we can mutate the column for the shake of simplicity
# Mutate to create the 'Branch_and_City' column
supermarket <- supermarket %>%
mutate(Branch_and_City = paste(Branch, "||", City))
colnames(supermarket)
## [1] "Invoice ID" "Branch"
## [3] "City" "Customer type"
## [5] "Gender" "Product line"
## [7] "Unit price" "Quantity"
## [9] "Tax 5%" "Total"
## [11] "Date" "Time"
## [13] "Payment" "cogs"
## [15] "gross margin percentage" "gross income"
## [17] "Rating" "Branch_and_City"
# Mutating columns B. Time --> Day_time ( Different time zone )
library(dplyr)
library(lubridate)
##
## Attaching package: 'lubridate'
## The following objects are masked from 'package:base':
##
## date, intersect, setdiff, union
library(ggplot2)
#Q2 What is the overall time most people buy?
# Convert the 'Time' column to a datetime object and extract the hour component
supermarket <- supermarket %>%
mutate(hour = hour(hms(Time)))
# Mutate to create the 'Day time' column based on hour values
supermarket <- supermarket %>%
mutate(Day_time = ifelse(hour >= 0 & hour < 6, "Night",
ifelse(hour >= 6 & hour < 12, "Morning",
ifelse(hour >= 12 & hour < 18, "Afternoon",
"Evening"))))
all_levels2 <- c("Night", "Morning", "Afternoon", "Evening")
supermarket$Day_time <- factor(supermarket$Day_time,order=TRUE, levels = all_levels2)
Day_time_colors <- c("Night" = "black", "Morning" = "#f1eadb", "Afternoon"="#EE4540", "Evening" = "#4C4B63")
t1<-table(supermarket[,20])
print(t1)
## Day_time
## Night Morning Afternoon Evening
## 0 191 528 281
ggplot(supermarket, aes(x = Day_time, fill = Day_time)) +
geom_bar(color = "black") +
scale_fill_manual(values = Day_time_colors) + # Assign colors manually
labs(title = "At which time mostly people buy ", x = "Day time", y = "Frequency")
colnames(supermarket)
## [1] "Invoice ID" "Branch"
## [3] "City" "Customer type"
## [5] "Gender" "Product line"
## [7] "Unit price" "Quantity"
## [9] "Tax 5%" "Total"
## [11] "Date" "Time"
## [13] "Payment" "cogs"
## [15] "gross margin percentage" "gross income"
## [17] "Rating" "Branch_and_City"
## [19] "hour" "Day_time"
# Mutating columns c. Date --> Weekdays
library(dplyr)
library(lubridate)
library(ggplot2)
#Q3 Which week days people prefer to buy for all 3 month?
# Convert 'Date' column to actual date objects
supermarket$Date <- as.Date(supermarket$Date, format = "%m/%d/%y")
# Extract weekdays and create a new column
supermarket <- supermarket %>%
mutate(Weekday = weekdays(Date))
all_levels3 <- c("Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
supermarket$Weekday <- factor(supermarket$Weekday,order=TRUE, levels = all_levels3)
weekday_colors <- c(
"Sunday" = "#FF5733",
"Monday" = "#FFC300",
"Tuesday" = "#7D3C98",
"Wednesday" = "#3498DB",
"Thursday" = "#1ABC9C",
"Friday" = "#F39C12",
"Saturday" = "#A569BD"
)
t2<-table(supermarket[,21])
print(t2)
## Weekday
## Sunday Monday Tuesday Wednesday Thursday Friday Saturday
## 150 155 129 139 155 141 131
ggplot(supermarket, aes(x = Weekday, fill = Weekday)) +
geom_bar(color = "black") +
scale_fill_manual(values = weekday_colors) +
labs(title = "At which week days mostly people buy ", x = "Week days", y = "Frequency")
# Mutating columns D. Rating --> Rating_level
library(dplyr)
library(ggplot2)
#Q4 What is the overall satisfaction level of customers ?
# Mutate to create the 'Rating_Level' column based on Rating values
supermarket <- supermarket %>%
mutate(Rating_Level = ifelse(Rating >= 0 & Rating < 4, "Very Sad",
ifelse(Rating >= 4 & Rating < 6, "Sad",
ifelse(Rating >= 6 & Rating < 7, "Moderate",
ifelse(Rating >= 7 & Rating < 9, "Happy",
"Very Happy")))))
all_levels <- c("Very Sad", "Sad", "Moderate", "Happy", "Very Happy")
supermarket$Rating_Level <- factor(supermarket$Rating_Level,order=TRUE, levels = all_levels)
rating_colors <- c("Very Sad" = "#393B57", "Sad" = "#9C495D", "Moderate" = "#E2894D", "Happy" = "#EEBB79", "Very Happy" = "#F7EBAB")
t3<-table(supermarket[,22])
print(t3)
## Rating_Level
## Very Sad Sad Moderate Happy Very Happy
## 0 315 184 335 166
ggplot(supermarket, aes(x = Rating_Level, fill = Rating_Level)) +
geom_bar(color = "black") +
scale_fill_manual(values = rating_colors) + # Assign colors manually
labs(title = "Histogram: Rating", x = "Rating Level", y = "Frequency")
# <---- Exploratory Data Analysis ---->
#5. <---- understanding customer ---->
library(dplyr)
library(ggplot2)
library(gridExtra)
## Warning: package 'gridExtra' was built under R version 4.3.3
##
## Attaching package: 'gridExtra'
## The following object is masked from 'package:dplyr':
##
## combine
# 5.1 What is the average total amount spent by each customer type and gender ?
# 5.1.1 By customer type (Member,Normal)
df<-supermarket
# Group the data by Customer_Type and calculate the average Total amount spent by each type
df_customer_type <- df %>%
group_by(`Customer type`) %>%
summarise(Average_Total_Amount_Spent = mean(Total))
customer_colors <- c("Normal" = "skyblue", "Member" = "orange")
# Visualize the customer_type vs avg amount spend
bar_plot_customer<- ggplot(df_customer_type, aes(x = `Customer type`, y = Average_Total_Amount_Spent , fill = `Customer type`)) +
geom_bar(stat = "identity",color = "black") +
scale_fill_manual(values = customer_colors) +
labs(title = "Average Total Amount Spent By Customer Type", x = "Customer Type", y = "Average Total Amount Spent")
# 5.1.2 By Gender (Male,Female)
# Group the data by Gender and calculate the average Total amount spent by each gender
df_gender <- df %>%
group_by(Gender) %>%
summarise(Average_Total_Amount_Spent = mean(Total))
gender_colors <- c("Male" = "skyblue", "Female" = "salmon")
# Visualize the gender vs avg amount spend
bar_plot_Gender<-ggplot(df_gender, aes(x = Gender, y = Average_Total_Amount_Spent, fill = Gender)) +
geom_bar(stat = "identity",color = "black") +
scale_fill_manual(values = gender_colors) +
labs(title = "Average Total Amount Spent By Gender", x = "Gender", y = "Average Total Amount Spent")
# comparing data
grid.arrange(bar_plot_Gender,bar_plot_customer,ncol=2)
# 5.2 Which product lines are the most popular among Gender and customer?
# 5.2.1 among Gender?
# Product line vs Gender , Amount spend
ggplot(df, aes(x = `Product line`, y = Total, fill = Gender)) +
geom_bar(stat = "identity", position = "dodge") +
scale_fill_manual(values = c("Male" = "#00246B", "Female" = "#ff3375")) +
labs(title ="Total Amount by Product Line and Gender",x = "Product Line",y = "Total Money")+
theme(axis.text.x = element_text(angle = 45, hjust = 1))
# 5.2.2 among customers?
# Product line vs Customer type , Amount spend
ggplot(df, aes(x = `Product line`, y = Total , fill = `Customer type`)) +
geom_bar(stat = "identity", position = "dodge") +
scale_fill_manual(values = c("Member" = "#507B6A", "Normal" = "#4B1816")) +
labs(title ="Total Amount by Product Line and customer",x = "Product Line",y = "Total Money")+
theme(axis.text.x = element_text(angle = 45, hjust = 1))
# 5.3 What is the satisfaction level of customers type and gender ?
# 5.3.1 By Gender
g3<-ggplot(supermarket, aes(x = Rating_Level, fill = Gender)) +
geom_bar(position = "dodge",color = "black") +
scale_fill_manual(values = c("Male" = "#2986cc", "Female" = "#c90076")) +
labs(title = "Rating by Gender", x = "Rating Level", y = "Frequency")
# 5.3.2 By custioner
g4<-ggplot(supermarket, aes(x = Rating_Level, fill = `Customer type`)) +
geom_bar(position = "dodge",color = "black") +
scale_fill_manual(values = c("Member" = "#507B6A", "Normal" = "#4B1816")) +
labs(title = "Rating by Customer", x = "Rating Level", y = "Frequency")
# comparing data
grid.arrange(g3,g4,nrow=2)
# 6 <------- understanding product line ------>
library(dplyr)
library(ggplot2)
library(gridExtra)
df<-supermarket
# 6.1 What are the most popular product lines in the supermarket?
# 6.1.1 by rating
ggplot(df, aes(x = `Product line`, y = Rating , fill = `Product line`)) +
geom_bar(stat = "identity")+
labs(title ="Product Line and Rating",x = "Product Line",y = "Total Rating")+
theme(axis.text.x = element_text(angle = 45, hjust = 1))
# 6.1.2 by total money
ggplot(df, aes(x = `Product line`, y = Total , fill = `Product line`)) +
geom_bar(stat = "identity")+
labs(title ="Product Line and Total Money",x = "Product Line",y = "Total Money")+
theme(axis.text.x = element_text(angle = 45, hjust = 1))
# 6.2 What are the total unit prices for each product line?
ggplot(df, aes(x = `Product line`, y = `Unit price` , fill = `Product line`)) +
geom_bar(stat = "identity")+
labs(title ="Product Line and Unit price",x = "Product Line",y = "Unit price")+
theme(axis.text.x = element_text(angle = 45, hjust = 1))
# 6.3 What are the most profitable product lines in the supermarket?
ggplot(df, aes(x = `Product line`, y = `gross income` , fill = `Product line`)) +
geom_bar(stat = "identity")+
labs(title ="Product Line and gross income",x = "Product Line",y = "gross income")+
theme(axis.text.x = element_text(angle = 45, hjust = 1))
# key observation : same as Product Line and Total Money graph
# 7 <------ understanding branch ------>
library(dplyr)
library(ggplot2)
library(gridExtra)
df<-supermarket
# 7.1 Which cities are the biggest contributors to the overall sales?
b1<-ggplot(df, aes(x = Branch_and_City, y = Total , fill = Branch_and_City)) +
geom_bar(stat = "identity")+
labs(title ="Branch_and_City vs Total money",x = "Branch_and_City",y = "Total money")+
theme(axis.text.x = element_text(angle = 45, hjust = 1))
# 7.2 What are the most popular branches of the supermarket in terms of customer ratings?
b2<-ggplot(df, aes(x = Branch_and_City, y = Rating , fill = Branch_and_City)) +
geom_bar(stat = "identity")+
labs(title ="Branch_and_City vs Rating",x = "Branch_and_City",y = "Rating")+
theme(axis.text.x = element_text(angle = 45, hjust = 1))
# comparing data
grid.arrange(b1,b2,ncol=2)
# 8 <----- Payment ------>
library(dplyr)
library(ggplot2)
library(gridExtra)
df<-supermarket
# 8.1 What are the most popular payment methods for different branch?
branch_and_City_colors <- c("A || Yangon" = "#F16645", "B || Mandalay" = "#FFC65D", "C || Naypyitaw" = "#4CC3D9")
# Visualize the counts of payment methods for different branches
ggplot(df, aes(x = Payment, fill = Branch_and_City)) +
geom_bar(position = "dodge") +
scale_fill_manual(values = branch_and_City_colors)+
labs(title = "Popular Payment Methods by Branch", x = "Payment Method", fill = "Branch")
# 9. <----- Relation with date and time ------>
library(dplyr)
library(ggplot2)
library(gridExtra)
df<-supermarket
# 9.1 What is the most time that different type of people buy?
# 9.1.1 By Bender
gender_colors <- c("Male" = "skyblue", "Female" = "salmon")
dt1<-ggplot(supermarket, aes(x = Day_time, fill = Gender)) +
geom_bar(position = "dodge", color = "black") +
scale_fill_manual(values = gender_colors ) + # Assign colors manually
labs(title = "Most Common Purchase Times by Gender", x = "Day time", y = "Frequency")
# 9.1.2 By Customer type
customer_colors <- c("Normal" = "skyblue", "Member" = "orange")
dt2<-ggplot(supermarket, aes(x = Day_time, fill = `Customer type`)) +
geom_bar(position = "dodge", color = "black") +
scale_fill_manual(values = customer_colors ) + # Assign colors manually
labs(title = "Most Common Purchase Times by Gender", x = "Day time", y = "Frequency")
# comparing data
grid.arrange(dt1,dt2,nrow=2)
# 9.2 What is the most time that people buy in each city and branch?
branch_and_City_colors <- c("A || Yangon" = "#F16645", "B || Mandalay" = "#FFC65D", "C || Naypyitaw" = "#4CC3D9")
# Visualize the counts of payment methods for different branches
ggplot(df, aes(x = Day_time, fill = Branch_and_City)) +
geom_bar(position = "dodge") +
scale_fill_manual(values = branch_and_City_colors)+
labs(title = "Most Common Purchase Times by Branch", x = "Payment Method", fill = "Branch")
# <------ correlation of gross income and branch ------>
#10 What is the relationship between the unit price and the quantity of each product?
correlation <- cor(df$`Unit price`, df$Quantity)
print(correlation)
## [1] 0.01077756
# output = 0.01077756
#We can see a correlation value of 0.010778 indicates a very weak positive correlation between the two variables.
#------------------------------------------------------------------------------------------#
# <----- Conclusion ----->
# Observation
# 1. Overall people usually buy in Afternoon
# 2. Overall people usually buy on Monday and Friday
# 3. Overall people' Rating either Sad or happy
# 4. Average spending of money:
# Female and member spend more money then male and normal people
# 5. Fashion accessories and Home and lifestyle are most successful product line for Gender
# 6. Fashion accessories is the most successful product line for diff customer
# 7. Female and Member give more good rating than male and normal people
# 8. product line:
# Fashion accessories is the most rated product
# Most money is spend on Food and beverages
# Fashion accessories has the highest unit price
# 9. Branch: C || Naypyitaw is most profitable
#10. Branch: A || Yangon is the most rated city
#11. Popular payment method for branch :
# cash = C
# credit card = B
# E wallet = A