Brazilian E-Commerce Public Dataset by Olist

Avaliação e Limpeza Preliminar dos Dados

Sessão para carregamento de bibliotecas, dados brutos e variaveis de suporte. e aplicamos as primeiras normalizações (Cidades, Coordenadas e Reviews).

Configuração e Carga

Carregamento de bibliotecas, leitura dos arquivos CSV e definição das Expressões Regulares para estados.

# Carregamento de bibliotecas
library(readr)
library(dplyr)
library(lubridate)
library(stringr)
library(stringi)
library(skimr)
library(tidyr)

# Carregamento dos Datasets Brutos
customers <- read_csv("olist_customers_dataset.csv")
geolocation <- read_csv("olist_geolocation_dataset.csv")
order_items <- read_csv("olist_order_items_dataset.csv")
order_payments <- read_csv("olist_order_payments_dataset.csv")
order_reviews <- read_csv("olist_order_reviews_dataset.csv")
orders <- read_csv("olist_orders_dataset.csv")
products <- read_csv("olist_products_dataset.csv")
sellers <- read_csv("olist_sellers_dataset.csv")
category_translation <- read_csv("product_category_name_translation.csv")

# Definição de Regex para Estados do Brasil
brazil_states_regex <- "\\b(ac|ap|am|pa|ro|rr|to|al|ba|ce|ma|pb|pe|pi|rn|se|go|mt|ms|df|es|mg|rj|sp|pr|rs|sc)\\b"
regex_sudeste <- "\\b(sp|rj|mg|es)\\b"
regex_sul <- "\\b(pr|rs|sc)\\b"
regex_nordeste <- "\\b(ba|pe|ce|rn|pb|pi|al|se|ma)\\b"
regex_centro_oeste <- "\\b(go|mt|ms|df)\\b"
regex_norte <- "\\b(pa|am|ac|ro|rr|ap|to)\\b"

Análise Exploratória

Visualização superficial da estrutura dos dados, tipos de variáveis e valores ausentes iniciais.

# Visualização superficial da estrutura
skim(customers)

Data summary
Name	customers
Number of rows	99441
Number of columns	5
_______________________
Column type frequency:
character	5
________________________
Group variables	None

Variable type: character

skim_variable	complete_rate	min	max	n_unique
customer_id	1	32	32	99441
customer_unique_id	1	32	32	96096
customer_zip_code_prefix	1	5	5	14994
customer_city	1	3	32	4119
customer_state	1	2	2	27

skim(geolocation)

Data summary
Name	geolocation
Number of rows	1000163
Number of columns	5
_______________________
Column type frequency:
character	3
numeric	2
________________________
Group variables	None

Variable type: character

skim_variable	complete_rate	min	max	n_unique
geolocation_zip_code_prefix	1	5	5	19015
geolocation_city	1	2	38	8010
geolocation_state	1	2	2	27

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
geolocation_lat	0	1	-21.18	5.72	-36.61	-23.60	-22.92	-19.98	45.07	▇▂▁▁▁
geolocation_lng	0	1	-46.39	4.27	-101.47	-48.57	-46.64	-43.77	121.11	▁▇▁▁▁

skim(orders)

Data summary
Name	orders
Number of rows	99441
Number of columns	8
_______________________
Column type frequency:
character	3
POSIXct	5
________________________
Group variables	None

Variable type: character

skim_variable	complete_rate	min	max	n_unique
order_id	1	32	32	99441
customer_id	1	32	32	99441
order_status	1	7	11	8

Variable type: POSIXct

skim_variable	n_missing	complete_rate	min	max	median	n_unique
order_purchase_timestamp	0	1.00	2016-09-04 21:15:19	2018-10-17 17:30:18	2018-01-18 23:04:36	98875
order_approved_at	160	1.00	2016-09-15 12:16:38	2018-09-03 17:40:06	2018-01-19 11:36:13	90733
order_delivered_carrier_date	1783	0.98	2016-10-08 10:34:01	2018-09-11 19:48:28	2018-01-24 16:10:58	81018
order_delivered_customer_date	2965	0.97	2016-10-11 13:46:32	2018-10-17 13:22:46	2018-02-02 19:28:10	95664
order_estimated_delivery_date	0	1.00	2016-09-30 00:00:00	2018-11-12 00:00:00	2018-02-15 00:00:00	459

skim(products)

Data summary
Name	products
Number of rows	32951
Number of columns	9
_______________________
Column type frequency:
character	2
numeric	7
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
product_id	0	1.00	32	32	0	32951	0
product_category_name	610	0.98	3	46	0	73	0

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
product_name_lenght	610	0.98	48.48	10.25	5	42	51	57	76	▁▁▃▇▁
product_description_lenght	610	0.98	771.50	635.12	4	339	595	972	3992	▇▃▁▁▁
product_photos_qty	610	0.98	2.19	1.74	1	1	1	3	20	▇▁▁▁▁
product_weight_g	2	1.00	2276.47	4282.04	0	300	700	1900	40425	▇▁▁▁▁
product_length_cm	2	1.00	30.82	16.91	7	18	25	38	105	▇▅▂▁▁
product_height_cm	2	1.00	16.94	13.64	2	8	13	21	105	▇▂▁▁▁
product_width_cm	2	1.00	23.20	12.08	6	15	20	30	118	▇▃▁▁▁

skim(sellers)

Data summary
Name	sellers
Number of rows	3095
Number of columns	4
_______________________
Column type frequency:
character	4
________________________
Group variables	None

Variable type: character

skim_variable	complete_rate	min	max	n_unique
seller_id	1	32	32	3095
seller_zip_code_prefix	1	5	5	2246
seller_city	1	2	40	611
seller_state	1	2	2	23

skim(order_items)

Data summary
Name	order_items
Number of rows	112650
Number of columns	7
_______________________
Column type frequency:
character	3
numeric	3
POSIXct	1
________________________
Group variables	None

Variable type: character

skim_variable	complete_rate	min	max	n_unique
order_id	1	32	32	98666
product_id	1	32	32	32951
seller_id	1	32	32	3095

Variable type: numeric

skim_variable	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
order_item_id	1	1.20	0.71	1.00	1.00	1.00	1.00	21.00	▇▁▁▁▁
price	1	120.65	183.63	0.85	39.90	74.99	134.90	6735.00	▇▁▁▁▁
freight_value	1	19.99	15.81	0.00	13.08	16.26	21.15	409.68	▇▁▁▁▁

Variable type: POSIXct

skim_variable	n_missing	complete_rate	min	max	median	n_unique
shipping_limit_date	0	1	2016-09-19 00:15:34	2020-04-09 22:35:08	2018-01-26 13:59:35	93318

skim(order_payments)

Data summary
Name	order_payments
Number of rows	103886
Number of columns	5
_______________________
Column type frequency:
character	2
numeric	3
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
order_id	0	1	32	32	0	99440	0
payment_type	0	1	6	11	0	5	0

Variable type: numeric

skim_variable	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
payment_sequential	1	1.09	0.71	1	1.00	1	1.00	29.00	▇▁▁▁▁
payment_installments	1	2.85	2.69	0	1.00	1	4.00	24.00	▇▂▁▁▁
payment_value	1	154.10	217.49	0	56.79	100	171.84	13664.08	▇▁▁▁▁

skim(order_reviews)

Data summary
Name	order_reviews
Number of rows	99224
Number of columns	7
_______________________
Column type frequency:
character	4
numeric	1
POSIXct	2
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	n_unique	whitespace
review_id	0	1.00	32	32	98410	0
order_id	0	1.00	32	32	98673	0
review_comment_title	87658	0.12	1	26	4178	0
review_comment_message	58256	0.41	1	208	35743	18

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
review_score	0	1	4.09	1.35	1	4	5	5	5	▂▁▁▃▇

Variable type: POSIXct

skim_variable	n_missing	complete_rate	min	max	median	n_unique
review_creation_date	0	1	2016-10-02 00:00:00	2018-08-31 00:00:00	2018-02-02 00:00:00	636
review_answer_timestamp	0	1	2016-10-07 18:32:28	2018-10-29 12:27:35	2018-02-04 22:41:47	98248

skim(category_translation)

Data summary
Name	category_translation
Number of rows	71
Number of columns	2
_______________________
Column type frequency:
character	2
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
product_category_name	0	1	3	46	0	71	0
product_category_name_english	0	1	3	39	0	71	0

Normalização de Cidades

Padronização de nomes de cidades removendo acentos, caracteres especiais e inconsistências. (Ainda em andamento, tem muitas variações para normalizar).

Foi criaodum método para limpar caso a caso. >. obs: Muitos desses valores foram vistos manualmente, utilizando view com valores agrupados.

#' Função para Limpeza de Nomes de Cidades (ainda incompleta)
normalize_city <- function(cities) {
  
  normalized_cities <- cities %>%
    str_to_lower() %>%    # 1. Converter para minúsculas
    str_remove(., paste0("\\s+", brazil_states_regex, "$")) %>%    # 2. Remover abreviações de estado no FINAL da string
    str_replace(., ".+@.+\\..+", NA_character_) %>%    # 3. Invalidar e-mails
    str_extract(., "^[^/\\\\,]+") %>%    # 4. Extrair apenas a parte ANTES de separadores como / , \
    str_remove_all(., "\\(.*?\\)") %>%    # 5. Remover texto entre parênteses
    str_replace_all(., "d'|d´|d`", "d'") %>%    # 6. Padronizar apóstrofos
    str_remove_all(., "[\\.'ºª´\\^~]") %>%    # 7. Remover pontuação e caracteres indesejados
    str_remove_all(., "^[\\.]+") %>% 
    str_replace_all(., "-", " ") %>%
    str_squish()    # 8. Remover espaços extras
  
    normalized_cities <- str_replace(
      normalized_cities, 
      paste0("^", brazil_states_regex, "$"), NA_character_)    # 9. Invalidar (NA) cidades que são APENAS abreviações de estado
    
    normalized_cities[normalized_cities == ""] <- NA_character_    # 10. Substituir strings vazias por NA
  
  return(normalized_cities)
}

# Aplicação da normalização nas tabelas relevantes
customers <- customers %>% mutate(customer_city = normalize_city(customer_city))
sellers <- sellers %>% mutate(seller_city = normalize_city(seller_city))
geolocation <- geolocation %>% mutate(geolocation_city = normalize_city(geolocation_city))

Tratamento de Coordenadas

Pretendo usar mapas na análise e pra isso fui dar uma olhada nas coordenadas. Vi que para cada prefixo de cep, tem inúmeras coordenadas(o que realmente é padrão e faz sentido), porém para o mapa isso não fica legal, então pode ser interessante criar um “ponto médio” para cada prefixo de cep.

Primeiro vamos vizualizar como está…

# Podemos ver que cada zip_code tem inumeras coordenadas, 
# precisamos de um ponto médio para cada zip_code ou o mapa vai ficar uma loucura...
geolocation_per_zip_code <- geolocation %>%
  group_by(geolocation_zip_code_prefix) %>%
  arrange(geolocation_zip_code_prefix)

print(head(geolocation_per_zip_code, 50))

## # A tibble: 50 × 5
## # Groups:   geolocation_zip_code_prefix [3]
##    geolocation_zip_code_prefix geolocation_lat geolocation_lng geolocation_city
##    <chr>                                 <dbl>           <dbl> <chr>           
##  1 01001                             -23.54929       -46.63356 sao paulo       
##  2 01001                             -23.55050       -46.63434 sao paulo       
##  3 01001                             -23.55064       -46.63441 sao paulo       
##  4 01001                             -23.54970       -46.63391 sao paulo       
##  5 01001                             -23.55143       -46.63407 sao paulo       
##  6 01001                             -23.55050       -46.63434 sao paulo       
##  7 01001                             -23.55134       -46.63403 sao paulo       
##  8 01001                             -23.54978       -46.63396 são paulo       
##  9 01001                             -23.55134       -46.63403 sao paulo       
## 10 01001                             -23.55050       -46.63434 sao paulo       
## # ℹ 40 more rows
## # ℹ 1 more variable: geolocation_state <chr>

Agora unificando as coordenadas por CEP e utilizando a média das latitudes e longitudes.

# Agrupar por CEP e calcular o ponto médio (média de lat/lon)
geolocation_per_zip_code <- geolocation %>%
  group_by(geolocation_zip_code_prefix) %>%
  arrange(geolocation_zip_code_prefix) %>%
  summarise(
    geolocation_lat = mean(geolocation_lat, na.rm = TRUE),
    geolocation_lng = mean(geolocation_lng, na.rm = TRUE)
  ) %>%
  ungroup()

print(head(geolocation_per_zip_code, 50))

## # A tibble: 50 × 3
##    geolocation_zip_code_prefix geolocation_lat geolocation_lng
##    <chr>                                 <dbl>           <dbl>
##  1 01001                             -23.55019       -46.63402
##  2 01002                             -23.54815       -46.63498
##  3 01003                             -23.54899       -46.63573
##  4 01004                             -23.54980       -46.63476
##  5 01005                             -23.54946       -46.63673
##  6 01006                             -23.55010       -46.63614
##  7 01007                             -23.55005       -46.63725
##  8 01008                             -23.54600       -46.63589
##  9 01009                             -23.54684       -46.63649
## 10 01010                             -23.54639       -46.63523
## # ℹ 40 more rows

Pré-Agregações e Tratamentos de NAs

Preparação de tabelas transacionais secundárias e limpeza de valores ausentes

Agregação de Pagamentos

Um pedido pode ter mais de ujm item e meio de pagamento (como descrito no proprio link da base de dados). Então é necessário agrupar.

# 1. Calcular o valor total financeiro do pedido
payments_agg_valor <- order_payments %>%
  group_by(order_id) %>%
  summarise(valor_total_pedido = sum(payment_value, na.rm = TRUE))

# 2. Identificar o método de pagamento principal (primeira sequencia)
payments_agg_tipo <- order_payments %>%
  filter(payment_sequential == 1) %>%
  select(order_id, tipo_pagamento_principal = payment_type) %>%
  distinct(order_id, .keep_all = TRUE) 

# 3. Junção final de pagamentos
payments_final <- payments_agg_valor %>%
  left_join(payments_agg_tipo, by = "order_id")

Tratamento de Reviews

Nem todo mundo escreve comentários em suas reviews de compra, então há muitos valores nulos nas colunas de comentários e títulos de avaliações. Além disso, alguns pedidos tem mais de um registro de comentários (clientes que mudaram de opinião).

# Tratamento de NAs e Seleção da Review mais recente
reviews_final <- order_reviews %>%
  mutate(
    review_comment_message = replace_na(review_comment_message, "sem_comentario"),
    review_comment_title = replace_na(review_comment_title, "sem_comentario"),
    review_creation_date = as_datetime(review_creation_date)
  ) %>%
  group_by(order_id) %>%
  arrange(desc(review_creation_date)) %>%
  slice(1) %>% # Mantém apenas a última review
  ungroup() %>%
  select(order_id, review_score, review_comment_message)

Estratégia de Junção e Construção do Dataset Analítico

Construção das três tabelas analíticas principais, integrando as tabelas limpas anteriormente.

Análise de Vendas, Produtos e Receita

tb_comercial <- order_items %>%
  # Selecionar apenas colunas relevantes da base
  select(order_id, order_item_id, product_id, seller_id, price, freight_value) %>%
  
  # 1. Juntar com Pedidos (apenas datas essenciais e status)
  left_join(orders %>% select(order_id, order_status, order_purchase_timestamp), by = "order_id") %>%
  
  # 2. Juntar com Produtos (já traduzidos e com NAs tratados)
  left_join(products, by = "product_id") %>%
  
  # 3. Juntar com Vendedores (apenas localização básica)
  left_join(sellers %>% select(seller_id, seller_city, seller_state), by = "seller_id") %>%
  
  # 4. Juntar com Pagamentos Agregados (Valor total do pedido e forma de pagamento)
  # Lembra da pré-agregação feita na Etapa 3a para evitar duplicatas
  left_join(payments_final, by = "order_id")

Performance Logística

geo_cust <- geolocation_per_zip_code %>% 
  select(geolocation_zip_code_prefix, lat_cli = geolocation_lat, lng_cli = geolocation_lng)

geo_sell <- geolocation_per_zip_code %>% 
  select(geolocation_zip_code_prefix, lat_venda = geolocation_lat, lng_venda = geolocation_lng)

# Criando a tb_logistica
tb_logistica <- order_items %>%
  select(order_id, order_item_id, seller_id, freight_value) %>%
  
  # 1. Juntar dados de Pedidos
  left_join(orders, by = "order_id") %>%
  
  # 2. Juntar CEP do Vendedor
  left_join(sellers %>% select(seller_id, seller_zip_code_prefix, seller_state), by = "seller_id") %>%
  
  # 3. Juntar CEP do Cliente
  left_join(customers %>% select(customer_id, customer_zip_code_prefix, customer_state), by = "customer_id") %>%
  
  # 4. Juntar Coordenadas (Lat/Lon)
  left_join(geo_sell, by = c("seller_zip_code_prefix" = "geolocation_zip_code_prefix")) %>%
  left_join(geo_cust, by = c("customer_zip_code_prefix" = "geolocation_zip_code_prefix"))

Perfil de Consumo (RFV - Recência, Frequência, Valor)

tb_clientes <- orders %>%
  # 1. Trazer quem é o cliente único
  left_join(customers, by = "customer_id") %>%
  
  # 2. Trazer o valor gasto (da tabela de pagamentos pré-agregada)
  left_join(payments_final, by = "order_id") %>%
  
  # 3. Trazer nota do review
  left_join(reviews_final, by = "order_id") %>%
  
  group_by(customer_unique_id) %>%
  summarise(
    # Dados Cadastrais
    cidade = first(customer_city),
    estado = first(customer_state),
    
    # Métricas Brutas
    total_pedidos = n(),
    total_gasto = sum(valor_total_pedido, na.rm = TRUE),
    
    # Datas
    data_primeira_compra = min(order_purchase_timestamp),
    data_ultima_compra = max(order_purchase_timestamp),
    
    # Satisfação Média bruta
    nota_media = mean(review_score, na.rm = TRUE)
  ) %>%
  ungroup()

Engenharia de Variáveis

Volume para possível análise de complexidade de frete.

tb_comercial <- tb_comercial %>%
  mutate(
    # Volume do produto
    volume_produto_cm3 = product_length_cm * product_height_cm * product_width_cm,
  )

Tempo (dias de entrega, atraso, aprovação) e SLA

tb_logistica <- tb_logistica %>%
  mutate(
    # Variáveis de Tempo
    tempo_processamento_horas = as.numeric(difftime(order_approved_at, order_purchase_timestamp, units = "hours")),
    tempo_entrega_total_dias = as.numeric(difftime(order_delivered_customer_date, order_purchase_timestamp, units = "days")),
    atraso_entrega_dias = as.numeric(difftime(order_delivered_customer_date, order_estimated_delivery_date, units = "days")),
    
    # Variáveis Categóricas (SLA)
    status_sla = case_when(
      atraso_entrega_dias > 0 ~ "Atrasado",
      atraso_entrega_dias <= 0 ~ "No Prazo/Adiantado"
    ),
  )

Perfil de Consumo

# Data de referência para limitar análises
data_ref <- max(orders$order_purchase_timestamp, na.rm = TRUE) + days(1)

tb_clientes <- tb_clientes %>%
  mutate(
    # 1. Cliente Recorrente
    # Se total_pedidos > 1, é recorrente
    tipo_cliente = ifelse(total_pedidos > 1, "Recorrente", "Novo"),
    
    # 2. Recência (Dias sem comprar) 
    recencia_dias = as.numeric(difftime(data_ref, data_ultima_compra, units = "days")),
    
    # 3. Ticket Médio (Valor Gasto / Qtd Pedidos)
    ticket_medio = total_gasto / total_pedidos
  )

Remoção de Colunas Redundantes (colocar em passos anterirres)

tb_comercial <- tb_comercial %>%
  select(
    # Remove metadados de qualidade do cadastro do produto
    -product_name_lenght, 
    -product_description_lenght, 
    -product_photos_qty,
    
    # Remove dimensões individuais (já temos volume e densidade)
    -product_length_cm, 
    -product_height_cm, 
    -product_width_cm
  )

# Verificando o resultado
colnames(tb_comercial)

##  [1] "order_id"                 "order_item_id"           
##  [3] "product_id"               "seller_id"               
##  [5] "price"                    "freight_value"           
##  [7] "order_status"             "order_purchase_timestamp"
##  [9] "product_category_name"    "product_weight_g"        
## [11] "seller_city"              "seller_state"            
## [13] "valor_total_pedido"       "tipo_pagamento_principal"
## [15] "volume_produto_cm3"

tb_logistica <- tb_logistica %>%
  select(
    # Remove datas intermediárias que já viraram métricas de tempo
    -order_approved_at,
    -order_delivered_carrier_date
  )

# Verificando o resultado
colnames(tb_logistica)

##  [1] "order_id"                      "order_item_id"                
##  [3] "seller_id"                     "freight_value"                
##  [5] "customer_id"                   "order_status"                 
##  [7] "order_purchase_timestamp"      "order_delivered_customer_date"
##  [9] "order_estimated_delivery_date" "seller_zip_code_prefix"       
## [11] "seller_state"                  "customer_zip_code_prefix"     
## [13] "customer_state"                "lat_venda"                    
## [15] "lng_venda"                     "lat_cli"                      
## [17] "lng_cli"                       "tempo_processamento_horas"    
## [19] "tempo_entrega_total_dias"      "atraso_entrega_dias"          
## [21] "status_sla"

CPAD_SEGUNDA_VA

Caio Lins

2025-11-16

Brazilian E-Commerce Public Dataset by Olist

Avaliação e Limpeza Preliminar dos Dados

Configuração e Carga

Análise Exploratória

Normalização de Cidades

Tratamento de Coordenadas

Pré-Agregações e Tratamentos de NAs

Agregação de Pagamentos

Tratamento de Reviews

Estratégia de Junção e Construção do Dataset Analítico

Análise de Vendas, Produtos e Receita

Performance Logística

Perfil de Consumo (RFV - Recência, Frequência, Valor)

Engenharia de Variáveis

Volume para possível análise de complexidade de frete.

Tempo (dias de entrega, atraso, aprovação) e SLA

Perfil de Consumo

Remoção de Colunas Redundantes (colocar em passos anterirres)