Data preprocessing
Brazilian E-Commerce Public Dataset
[Link]
Load and merge the required tables (orders, customers, reviews, products,
geolocation). Identify and remove duplicate or irrelevant rows.
Detect and handle missing values in delivery dates, customer reviews, or product
categories. How many rows were impacted?
Convert delivery timestamps into delivery durations. Which orders were delivered
late compared to the estimated delivery date?
Create derived variables: delivery_delay = actual - estimated, review_score_category
= high / medium / low.
Code:
library(dplyr)
library(tidyverse)
library(ggplot2)
library(ggcorrplot)
# Load data
orders <- read_csv("olist_orders_dataset.csv")
customers <- read_csv("olist_customers_dataset.csv")
reviews <- read_csv("olist_order_reviews_dataset.csv")
products <- read_csv("olist_products_dataset.csv")
geolocation <- read_csv("olist_geolocation_dataset.csv")
orders <- orders %>%
select(order_id, customer_id, order_status, order_purchase_timestamp,
order_approved_at, order_delivered_carrier_date,
order_delivered_customer_date, order_estimated_delivery_date)
customers <- customers %>%
select(customer_id, customer_unique_id, customer_city, customer_state)
reviews <- reviews %>%
select(order_id, review_score, review_comment_message)
products <- products %>%
select(product_id, product_category_name)
geolocation <- geolocation %>%
select(geolocation_zip_code_prefix, geolocation_city, geolocation_state)
order_data <- orders %>%
left_join(customers, by = "customer_id") %>%
left_join(reviews, by = "order_id")
# Entire row duplicates
duplicates_all <- order_data %>%
duplicated() %>%
sum()
order_data <- order_data %>%
distinct()
order_data <- order_data %>%
distinct(order_id, .keep_all = TRUE)
# View different order statuses
table(order_data$order_status)
# Filter only delivered or shipped orders
order_data <- order_data %>%
filter(order_status %in% c("delivered", "shipped"))
view(order_data)
order_data <- order_data %>%
mutate(
order_purchase_timestamp = [Link](order_purchase_timestamp, format="%Y-%m-
%d %H:%M:%S"),
order_delivered_customer_date = [Link](order_delivered_customer_date,
format="%Y-%m-%d %H:%M:%S"),
order_estimated_delivery_date = [Link](order_estimated_delivery_date,
format="%Y-%m-%d %H:%M:%S")
order_data <- order_data %>%
mutate(
delivery_delay = [Link](difftime(order_delivered_customer_date,
order_estimated_delivery_date, units = "days")),
delivered_late = delivery_delay > 0
late_orders <- order_data %>%
filter(delivered_late == TRUE)
nrow(late_orders) # Number of late deliveries
head(late_orders) # View sample late orders
late_orders %>%
select(order_id, order_estimated_delivery_date, order_delivered_customer_date,
delivery_delay)
order_data <- order_data %>%
mutate(
# Ensure timestamps are in proper date-time format
order_delivered_customer_date = [Link](order_delivered_customer_date, format =
"%Y-%m-%d %H:%M:%S"),
order_estimated_delivery_date = [Link](order_estimated_delivery_date, format =
"%Y-%m-%d %H:%M:%S"),
delivery_delay = [Link](difftime(order_delivered_customer_date,
order_estimated_delivery_date, units = "days")),
review_score_category = case_when(
review_score >= 4 ~ "high",
review_score == 3 ~ "medium",
review_score <= 2 ~ "low",
TRUE ~ NA_character_ # in case of missing review_score