01_EDA

Olist Brazilian E-Commerce¶

Notebook 01 — Exploratory Data Analysis (EDA)¶

Data source: Brazilian E-Commerce Public Dataset by Olist
License: CC BY-NC-SA 4.0

Contents

Step 1 — Import Libraries
Step 2 — Load Data (all tables)
Step 3 — First Overview (shape, dtypes, head)
Step 4 — Missing Values
Step 5 — Duplicates
Step 6 — Convert Data Types
Step 7 — Analyze Distributions (Numeric Columns)
Step 8 — Analyze Categorical Columns
Step 9 — Visualizations (Matplotlib / Seaborn)
Step 10 — Summary & Key Findings

↑ ContentsStep 1 — Import Libraries¶

In [2]:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from sqlalchemy import create_engine, inspect
from pathlib import Path

# Auto-detect environment: Kaggle vs local
kaggle_input = Path('/kaggle/input')
if kaggle_input.exists():
    db_path = next(kaggle_input.rglob('olist.sqlite'))   # find it on Kaggle
else:
    db_path = Path('..') / 'data' / 'raw' / 'olist.sqlite'  # local

# Safety check first
print(db_path.exists())   # must be True
print(db_path.resolve())  # shows the full path

engine = create_engine(f'sqlite:///{db_path}')

inspector = inspect(engine)
tabellen = inspector.get_table_names()
print(tabellen)

True
C:\Users\todor\Desktop\DAProjects\Olist-Brazilian-E-Commerce\data\raw\olist.sqlite
['customers', 'geolocation', 'leads_closed', 'leads_qualified', 'order_items', 'order_payments', 'order_reviews', 'orders', 'product_category_name_translation', 'products', 'sellers']

↑ ContentsStep 2 — Load Data (all tables)¶

In [3]:

df_customers = pd.read_sql("SELECT * FROM customers", engine)
df_geolocation = pd.read_sql("SELECT * FROM geolocation", engine)
df_leads_closed = pd.read_sql("SELECT * FROM leads_closed", engine)
df_leads_qualified = pd.read_sql("SELECT * FROM leads_qualified", engine).copy()
df_order_items = pd.read_sql("SELECT * FROM order_items", engine)
df_order_payments = pd.read_sql("SELECT * FROM order_payments", engine)
df_order_reviews = pd.read_sql("SELECT * FROM order_reviews", engine)
df_orders = pd.read_sql("SELECT * FROM orders", engine)
df_product_category_name_translation = pd.read_sql("SELECT * FROM product_category_name_translation", engine)
df_products = pd.read_sql("SELECT * FROM products", engine)
df_sellers = pd.read_sql("SELECT * FROM sellers", engine)

In [4]:

# Convert all at once
string_cols = ['customer_id', 'customer_unique_id', 'customer_city', 'customer_state']

df_customers[string_cols] = df_customers[string_cols].astype('string')

# Verify
print(df_customers.dtypes)

customer_id                 string[python]
customer_unique_id          string[python]
customer_zip_code_prefix             int64
customer_city               string[python]
customer_state              string[python]
dtype: object

Table	Description	Key Columns
`df_orders`	Central table. One row per order — status, timestamps, links to customer	`order_id`, `customer_id`, `order_status`, 5× date columns, `is_delivered`
`df_customers`	One row per order-customer. `customer_unique_id` identifies the real person across orders	`customer_id`, `customer_unique_id`, zip, city, state
`df_order_items`	One row per item in an order. An order can have multiple items from different sellers	`order_id`, `product_id`, `seller_id`, `price`, `freight_value`
`df_order_payments`	One row per payment. An order can be paid with multiple methods (card + voucher)	`order_id`, `payment_type`, `payment_installments`, `payment_value`
`df_order_reviews`	Customer review per order. Comment fields optional — most customers only left a score	`order_id`, `review_score`, `review_comment_message`, `has_comment`
`df_products`	One row per product. Category and physical dimensions. 610 rows have no category	`product_id`, `product_category_name`, weight, dimensions
`df_sellers`	One row per seller. Location data — used to calculate seller-to-customer distance	`seller_id`, zip, city, state
`df_geolocation`	GPS coordinates per zip code. Aggregated to one centroid per zip after deduplication	`zip_code_prefix`, `lat`, `lng`, city, state
`df_product_category_name_translation`	Lookup table — translates Portuguese category names to English	`product_category_name`, `product_category_name_english`
`df_leads_qualified`	Marketing leads that visited Olist’s landing page. First step in the seller funnel	`mql_id`, `first_contact_date`, `landing_page_id`, `origin`
`df_leads_closed`	Leads that converted to sellers. Links back to leads_qualified via mql_id	`mql_id`, `seller_id`, `won_date`, `business_type`, `has_profile_data`

↑ ContentsStep 3 — First Overview (shape, dtypes, head)¶

In [5]:

print('Table customers:')
print(df_customers.head(2))
print(df_customers.shape)
print(df_customers.dtypes)
print()

Table customers: 
                        customer_id                customer_unique_id  \
0  06b8999e2fba1a1fbc88172c00ba8bc7  861eff4711a542e4b93843c6dd7febb0   
1  18955e83d337fd6b2def6b18a428ac77  290c77bc529b7ac935b93aa66c333dc3   

   customer_zip_code_prefix          customer_city customer_state  
0                     14409                 franca             SP  
1                      9790  sao bernardo do campo             SP   

(99441, 5) 

sellers : 
 customer_id                 string[python]
customer_unique_id          string[python]
customer_zip_code_prefix             int64
customer_city               string[python]
customer_state              string[python]
dtype: object

In [6]:

print('Table geolocation:')
print(df_geolocation.head(2))
print(df_geolocation.shape)
print(df_geolocation.dtypes)
print()

Table geolocation: 
   geolocation_zip_code_prefix  geolocation_lat  geolocation_lng  \
0                         1037       -23.545621       -46.639292   
1                         1046       -23.546081       -46.644820   

  geolocation_city geolocation_state  
0        sao paulo                SP  
1        sao paulo                SP   

(1000163, 5) 

geolocation : 
 geolocation_zip_code_prefix      int64
geolocation_lat                float64
geolocation_lng                float64
geolocation_city                object
geolocation_state               object
dtype: object

In [7]:

print('Table leads_closed:')
print(df_leads_closed.head(2))
print(df_leads_closed.shape)
print(df_leads_closed.dtypes)
print()

Table leads_closed: 
                             mql_id                         seller_id  \
0  5420aad7fec3549a85876ba1c529bd84  2c43fb513632d29b3b58df74816f1b06   

                             sdr_id                             sr_id  \
0  a8387c01a09e99ce014107505b92388c  4ef15afb4b2723d8f3d81e51ec7afefe   

              won_date business_segment      lead_type lead_behaviour_profile  \
0  2018-02-26 19:58:54              pet  online_medium                    cat   

   has_company  has_gtin average_stock business_type  \
0          NaN       NaN          None      reseller   

   declared_product_catalog_size  declared_monthly_revenue  
0                            NaN                       0.0   

(99441, 5) 

leads_closed : 
 mql_id                            object
seller_id                         object
sdr_id                            object
sr_id                             object
won_date                          object
business_segment                  object
lead_type                         object
lead_behaviour_profile            object
has_company                      float64
has_gtin                         float64
average_stock                     object
business_type                     object
declared_product_catalog_size    float64
declared_monthly_revenue         float64
dtype: object

In [8]:

print('Table leads_qualified:')
print(df_leads_qualified.head(2))
print(df_leads_qualified.shape)
print(df_leads_qualified.dtypes)
print()

Table leads_qualified: 
                             mql_id first_contact_date  \
0  dac32acd4db4c29c230538b72f8dd87d         2018-02-01   
1  8c18d1de7f67e60dbd64e3c07d7e9d5d         2017-10-20   

                    landing_page_id       origin  
0  88740e65d5d6b056e0cda098e1ea6313       social  
1  007f9098284a86ee80ddeb25d53e0af8  paid_search   

(99441, 5) 

leads_qualified : 
 mql_id                object
first_contact_date    object
landing_page_id       object
origin                object
dtype: object

In [9]:

print('Table order_items:')
print(df_order_items.head(2))
print(df_order_items.shape)
print(df_order_items.dtypes)
print()

Table order_items: 
                           order_id  order_item_id  \
0  00010242fe8c5a6d1ba2dd792cb16214              1   
1  00018f77f2f0320c557190d7a144bdd3              1   

                         product_id                         seller_id  \
0  4244733e06e7ecb4970a6e2683c13e61  48436dade18ac8b2bce089ec2a041202   
1  e5f2d52b802189ee658865ca93d83a8f  dd7ddc04e1b6c2c614352b383efe2d36   

   shipping_limit_date  price  freight_value  
0  2017-09-19 09:45:35   58.9          13.29  
1  2017-05-03 11:05:13  239.9          19.93   

(99441, 5) 

order_items : 
 order_id                object
order_item_id            int64
product_id              object
seller_id               object
shipping_limit_date     object
price                  float64
freight_value          float64
dtype: object

In [10]:

print('Table order_payments:')
print(df_order_payments.head(2))
print(df_order_payments.shape)
print(df_order_payments.dtypes)
print()

Table order_payments: 
                           order_id  payment_sequential payment_type  \
0  b81ef226f3fe1789b1e8b2acac839d17                   1  credit_card   
1  a9810da82917af2d9aefd1278f1dcfa0                   1  credit_card   

   payment_installments  payment_value  
0                     8          99.33  
1                     1          24.39   

(99441, 5) 

order_payments : 
 order_id                 object
payment_sequential        int64
payment_type             object
payment_installments      int64
payment_value           float64
dtype: object

In [11]:

print('Table order_reviews:')
print(df_order_reviews.head(2))
print(df_order_reviews.shape)
print(df_order_reviews.dtypes)
print()

Table order_reviews: 
                          review_id                          order_id  \
0  7bc2406110b926393aa56f80a40eba40  73fc7af87114b39712e6da79b0a377eb   
1  80e641a11e56f04c1ad469d5645fdfde  a548910a1c6147796b98fdf73dbeba33   

   review_score review_comment_title review_comment_message  \
0             4                 None                   None   
1             5                 None                   None   

  review_creation_date review_answer_timestamp  
0  2018-01-18 00:00:00     2018-01-18 21:46:59  
1  2018-03-10 00:00:00     2018-03-11 03:05:13   

(99441, 5) 

order_reviews : 
 review_id                  object
order_id                   object
review_score                int64
review_comment_title       object
review_comment_message     object
review_creation_date       object
review_answer_timestamp    object
dtype: object

In [12]:

print('Table orders:')
print(df_orders.head(2))
print(df_orders.shape)
print(df_orders.dtypes)
print()

Table orders: 
                           order_id                       customer_id  \
0  e481f51cbdc54678b7cc49136f2d6af7  9ef432eb6251297304e76186b10a928d   
1  53cdb2fc8bc7dce0b6741e2150273451  b0830fb4747a6c6d20dea0b8c802d7ef   

  order_status order_purchase_timestamp    order_approved_at  \
0    delivered      2017-10-02 10:56:33  2017-10-02 11:07:15   
1    delivered      2018-07-24 20:41:37  2018-07-26 03:24:27   

  order_delivered_carrier_date order_delivered_customer_date  \
0          2017-10-04 19:55:00           2017-10-10 21:25:13   
1          2018-07-26 14:31:00           2018-08-07 15:27:45   

  order_estimated_delivery_date  
0           2017-10-18 00:00:00  
1           2018-08-13 00:00:00   

(99441, 5) 

orders : 
 order_id                         object
customer_id                      object
order_status                     object
order_purchase_timestamp         object
order_approved_at                object
order_delivered_carrier_date     object
order_delivered_customer_date    object
order_estimated_delivery_date    object
dtype: object

In [13]:

print('Table product_category_name_translation:')
print(df_product_category_name_translation.head(2))
print(df_product_category_name_translation.shape)
print(df_product_category_name_translation.dtypes)
print()

Table product_category_name_translation: 
    product_category_name product_category_name_english
0            beleza_saude                 health_beauty
1  informatica_acessorios         computers_accessories 

(99441, 5) 

product_category_name_translation : 
 product_category_name            object
product_category_name_english    object
dtype: object

In [14]:

print('Table products:')
print(df_products.head(2))
print(df_products.shape)
print(df_products.dtypes)
print()

Table products: 
                         product_id product_category_name  \
0  1e9e8ef04dbcff4541ed26657ea517e5            perfumaria   
1  3aa071139cb16b67ca9e5dea641aaa2f                 artes   

   product_name_lenght  product_description_lenght  product_photos_qty  \
0                 40.0                       287.0                 1.0   
1                 44.0                       276.0                 1.0   

   product_weight_g  product_length_cm  product_height_cm  product_width_cm  
0             225.0               16.0               10.0              14.0  
1            1000.0               30.0               18.0              20.0   

(99441, 5) 

products : 
 product_id                     object
product_category_name          object
product_name_lenght           float64
product_description_lenght    float64
product_photos_qty            float64
product_weight_g              float64
product_length_cm             float64
product_height_cm             float64
product_width_cm              float64
dtype: object

In [15]:

print('Table sellers:')
print(df_sellers.head(2))
print(df_sellers.shape)
print(df_sellers.dtypes)
print()

Table sellers: 
                          seller_id  seller_zip_code_prefix seller_city  \
0  3442f8959a84dea7ee197c632cb2df15                   13023    campinas   
1  d1b65fc7debc3361ea86b5f14c68d2e2                   13844  mogi guacu   

  seller_state  
0           SP  
1           SP   

(99441, 5) 

sellers : 
 seller_id                 object
seller_zip_code_prefix     int64
seller_city               object
seller_state              object
dtype: object

↑ ContentsStep 4 — Missing Values¶

4.1 customers — Missing Values¶

Finding: No missing values found in any column. Dataset is complete.

In [16]:

print(df_customers.isnull().sum())

customer_id                 0
customer_unique_id          0
customer_zip_code_prefix    0
customer_city               0
customer_state              0
dtype: int64

4.2 geolocation — Missing Values¶

Finding: No missing values found in any column. Dataset is complete.

In [17]:

print(df_geolocation.isnull().sum())

geolocation_zip_code_prefix    0
geolocation_lat                0
geolocation_lng                0
geolocation_city               0
geolocation_state              0
dtype: int64

4.3 leads_closed — Missing Values¶

Finding: 4 columns have >90% missing values (has_company, has_gtin, average_stock, declared_product_catalog_size).
These were optional onboarding fields — most leads did not fill them in.

Strategy:

Columns with >90% missing values are not dropped → no information loss
Instead, a binary column has_profile_data is created (1 = data provided, 0 = not provided)
Columns with <5% missing values are filled with "Unknown"

ML relevance: has_profile_data can be used as a feature — leads who provide complete information may show higher conversion potential.

In [18]:

print( df_leads_closed.isnull().sum())

cols_sparse = ['has_company', 'has_gtin', 'average_stock', 'declared_product_catalog_size']

# Create binary column
df_leads_closed['has_profile_data'] = df_leads_closed[cols_sparse].notnull().any(axis=1).astype(int)

# Drop original columns
df_leads_closed.drop(columns=cols_sparse, inplace=True)

# Fill small missing values
df_leads_closed['business_segment'] = df_leads_closed['business_segment'].fillna('Unknown')
df_leads_closed['business_type'] = df_leads_closed['business_type'].fillna('Unknown')
df_leads_closed['lead_type'] = df_leads_closed['lead_type'].fillna('Unknown')
df_leads_closed['lead_behaviour_profile'] = df_leads_closed['lead_behaviour_profile'].fillna('Unknown')

mql_id                             0
seller_id                          0
sdr_id                             0
sr_id                              0
won_date                           0
business_segment                   1
lead_type                          6
lead_behaviour_profile           177
has_company                      779
has_gtin                         778
average_stock                    776
business_type                     10
declared_product_catalog_size    773
declared_monthly_revenue           0
dtype: int64

4.4 leads_qualified — Missing Values¶

Finding: Only 60 missing values found (<1% of total rows).
Strategy: Rows dropped — negligible data loss.

In [19]:

# print( df_leads_qualified.isnull().sum())
print(f'Row count: {df_leads_qualified["origin"].count()}, Null values: {df_leads_qualified["origin"].isnull().sum()}')
df_leads_qualified= df_leads_qualified.dropna()
print( df_leads_qualified.isnull().sum() )

Row count: 7940, Null values: 60
mql_id                0
first_contact_date    0
landing_page_id       0
origin                0
dtype: int64

4.5 order_items — Missing Values¶

Finding: No missing values found in any column. Dataset is complete.

In [20]:

print( df_order_items.isnull().sum() )

order_id               0
order_item_id          0
product_id             0
seller_id              0
shipping_limit_date    0
price                  0
freight_value          0
dtype: int64

4.6 order_payments — Missing Values¶

Finding: No missing values found in any column. Dataset is complete.

In [21]:

print( df_order_payments.isnull().sum() )

order_id                0
payment_sequential      0
payment_type            0
payment_installments    0
payment_value           0
dtype: int64

4.7 order_reviews — Missing Values¶

Finding: review_comment_title (88%) and review_comment_message (59%) have missing values.
These are not data errors — customers simply chose not to leave a written comment.

Strategy:

Binary column has_comment created (1 = comment written, 0 = no comment)
Missing text fields filled with "No comment"

ML relevance: has_comment may correlate with review score — dissatisfied customers tend to write more comments.

In [22]:

print( df_order_reviews.isnull().sum() )
# print( df_order_reviews.count() )

df_order_reviews['has_comment'] = df_order_reviews['review_comment_message'].notnull().astype(int)
df_order_reviews['review_comment_title'] = df_order_reviews['review_comment_title'].fillna('No comment')
df_order_reviews['review_comment_message'] = df_order_reviews['review_comment_message'].fillna('No comment')

review_id                      0
order_id                       0
review_score                   0
review_comment_title       87656
review_comment_message     58247
review_creation_date           0
review_answer_timestamp        0
dtype: int64

4.8 orders — Missing Values¶

Finding: 3 date columns have missing values — this is expected behavior, not data errors.

Column	Missing	Reason
`order_approved_at`	160	Orders that were cancelled before approval
`order_delivered_carrier_date`	1783	Orders not yet picked up by carrier
`order_delivered_customer_date`	2965	Orders not yet delivered to customer

Strategy: Dates are kept as NaT — filling with a placeholder would corrupt delivery time calculations.
Binary column is_delivered created (1 = delivered, 0 = not delivered).

ML relevance: is_delivered and delivery delay (estimated vs actual) are strong features for predicting customer satisfaction.

In [23]:

print( df_orders.isnull().sum() )
# print( df_orders.count() )

# Binary column — delivered or not
df_orders['is_delivered'] = df_orders['order_delivered_customer_date'].notnull().astype(int)

order_id                            0
customer_id                         0
order_status                        0
order_purchase_timestamp            0
order_approved_at                 160
order_delivered_carrier_date     1783
order_delivered_customer_date    2965
order_estimated_delivery_date       0
dtype: int64

4.9 product_category_name_translation — Missing Values¶

Finding: No missing values found in any column. Dataset is complete.

In [24]:

print( df_product_category_name_translation.isnull().sum() )

product_category_name            0
product_category_name_english    0
dtype: int64

4.10 products — Missing Values¶

Column	Missing	Strategy
`product_category_name` + description fields	610 (1.8%)	Filled with `"unknown"` — products exist but have no category assigned
`product_weight_g`, dimensions	2	Rows dropped — physical dimensions are required for analysis

In [25]:

print( df_products.isnull().sum() ) 
# print( df_products.count() ) 

df_products['product_category_name'] = df_products['product_category_name'].fillna('unknown')
df_products['product_name_lenght'] = df_products['product_name_lenght'].fillna(0)
df_products['product_description_lenght'] = df_products['product_description_lenght'].fillna(0)
df_products['product_photos_qty'] = df_products['product_photos_qty'].fillna(0)

# Drop the 2 rows with missing dimensions — truly unusable
df_products = df_products.dropna(subset=['product_weight_g', 'product_length_cm'])

product_id                      0
product_category_name         610
product_name_lenght           610
product_description_lenght    610
product_photos_qty            610
product_weight_g                2
product_length_cm               2
product_height_cm               2
product_width_cm                2
dtype: int64

4.11 sellers — Missing Values¶

Finding: No missing values found in any column. Dataset is complete.

In [26]:

print( df_sellers.isnull().sum() )

seller_id                 0
seller_zip_code_prefix    0
seller_city               0
seller_state              0
dtype: int64

↑ ContentsStep 5 — Duplicates¶

Finding: No duplicates found in any table except geolocation.

geolocation: 261,831 duplicate rows — expected behavior.
Each zip code prefix covers a geographic area with multiple GPS coordinates.
Strategy: Aggregated by zip_code_prefix — mean lat/lng used as centroid per area.

In [27]:

print( df_customers.duplicated().sum() )
print( df_geolocation.duplicated().sum() )
print( df_leads_closed.duplicated().sum() )
print( df_leads_qualified.duplicated().sum() )
print( df_order_items.duplicated().sum() )
print( df_order_payments.duplicated().sum() )
print( df_order_reviews.duplicated().sum() )
print( df_orders.duplicated().sum() )
print( df_product_category_name_translation.duplicated().sum() )
print( df_products.duplicated().sum() )
print( df_sellers.duplicated().sum() )

# Keep only the centroid per ZIP for analysis
df_geolocation = df_geolocation.groupby('geolocation_zip_code_prefix').agg(
    geolocation_lat=('geolocation_lat', 'mean'),
    geolocation_lng=('geolocation_lng', 'mean'),
    geolocation_city=('geolocation_city', 'first'),
    geolocation_state=('geolocation_state', 'first')
).reset_index()

↑ ContentsStep 6 — Convert Data Types¶

6.1 Convert customers¶

ID and location columns are converted from object to string — enables consistent text operations and reduces memory usage.

In [28]:

# Convert all string columns at once
string_cols = ['customer_id', 'customer_unique_id', 'customer_city', 'customer_state', 'customer_zip_code_prefix']
df_customers[string_cols] = df_customers[string_cols].astype('string')

# Verify
print(df_customers.dtypes)

customer_id                 string[python]
customer_unique_id          string[python]
customer_zip_code_prefix    string[python]
customer_city               string[python]
customer_state              string[python]
dtype: object

6.2 Convert geolocation¶

geolocation_city and geolocation_state columns are converted from object to string — enables consistent text operations and reduces memory usage.

In [29]:

print( df_geolocation.dtypes )

string_cols = ['geolocation_zip_code_prefix', 'geolocation_city', 'geolocation_state']
df_geolocation[string_cols] = df_geolocation[string_cols].astype('string')

geolocation_zip_code_prefix      int64
geolocation_lat                float64
geolocation_lng                float64
geolocation_city                object
geolocation_state               object
dtype: object

6.3 Convert df_leads_closed¶

ID columns converted to string, won_date converted to datetime.
IDs are identifiers, not numbers — string type ensures safe joins across tables.

In [30]:

string_cols = ['mql_id', 'seller_id', 'sdr_id', 'sr_id', 
               'business_segment', 'lead_type', 'lead_behaviour_profile', 'business_type']
df_leads_closed[string_cols] = df_leads_closed[string_cols].astype('string')

# won_date as datetime
df_leads_closed['won_date'] = pd.to_datetime(df_leads_closed['won_date'])

# Verify
print(df_leads_closed.dtypes)

mql_id                      string[python]
seller_id                   string[python]
sdr_id                      string[python]
sr_id                       string[python]
won_date                    datetime64[ns]
business_segment            string[python]
lead_type                   string[python]
lead_behaviour_profile      string[python]
business_type               string[python]
declared_monthly_revenue           float64
has_profile_data                     int64
dtype: object

6.4 Convert leads_qualified¶

ID columns converted to string, first_contact_date converted to datetime.

In [31]:

string_cols = ['mql_id', 'landing_page_id', 'origin']
df_leads_qualified[string_cols] = df_leads_qualified[string_cols].astype('string')

# first_contact_date as datetime
df_leads_qualified['first_contact_date'] = pd.to_datetime(df_leads_qualified['first_contact_date'])

# Verify
print(df_leads_qualified.dtypes)

mql_id                string[python]
first_contact_date    datetime64[ns]
landing_page_id       string[python]
origin                string[python]
dtype: object

6.5 Convert order_items¶

ID columns converted to string, shipping_limit_date converted to datetime.
order_item_id, price and freight_value remain numeric — used for calculations.

In [32]:

string_cols = ['order_id', 'product_id', 'seller_id']
df_order_items[string_cols] = df_order_items[string_cols].astype('string')

# shipping_limit_date as datetime
df_order_items['shipping_limit_date'] = pd.to_datetime(df_order_items['shipping_limit_date'])

# Verify
print(df_order_items.dtypes)

order_id               string[python]
order_item_id                   int64
product_id             string[python]
seller_id              string[python]
shipping_limit_date    datetime64[ns]
price                         float64
freight_value                 float64
dtype: object

6.6 Convert order_payments¶

order_id and payment_type converted to string.
payment_sequential, payment_installments and payment_value remain numeric — used for calculations.

In [33]:

df_order_payments['order_id'] = df_order_payments['order_id'].astype('string')
df_order_payments['payment_type'] = df_order_payments['payment_type'].astype('string')

# Verify
print(df_order_payments.dtypes)

order_id                string[python]
payment_sequential               int64
payment_type            string[python]
payment_installments             int64
payment_value                  float64
dtype: object

6.7 Convert order_reviews¶

ID and text columns converted to string, date columns converted to datetime.
review_score and has_comment remain numeric — used for analysis and ML.

In [34]:

string_cols = ['review_id', 'order_id', 'review_comment_title', 'review_comment_message']
df_order_reviews[string_cols] = df_order_reviews[string_cols].astype('string')

# Date columns as datetime
df_order_reviews['review_creation_date'] = pd.to_datetime(df_order_reviews['review_creation_date'])
df_order_reviews['review_answer_timestamp'] = pd.to_datetime(df_order_reviews['review_answer_timestamp'])

# Verify
print(df_order_reviews.dtypes)

review_id                  string[python]
order_id                   string[python]
review_score                        int64
review_comment_title       string[python]
review_comment_message     string[python]
review_creation_date       datetime64[ns]
review_answer_timestamp    datetime64[ns]
has_comment                         int64
dtype: object

6.8 Convert orders¶

ID and status columns converted to string, all 5 date columns converted to datetime.
is_delivered remains numeric — used for ML.

In [35]:

string_cols = ['order_id', 'customer_id', 'order_status']
df_orders[string_cols] = df_orders[string_cols].astype('string')

# Date columns as datetime
date_cols = ['order_purchase_timestamp', 'order_approved_at', 
             'order_delivered_carrier_date', 'order_delivered_customer_date', 
             'order_estimated_delivery_date']
df_orders[date_cols] = df_orders[date_cols].apply(pd.to_datetime)

# is_delivered stays int64

# Verify
print(df_orders.dtypes)

order_id                         string[python]
customer_id                      string[python]
order_status                     string[python]
order_purchase_timestamp         datetime64[ns]
order_approved_at                datetime64[ns]
order_delivered_carrier_date     datetime64[ns]
order_delivered_customer_date    datetime64[ns]
order_estimated_delivery_date    datetime64[ns]
is_delivered                              int64
dtype: object

6.9 Convert product_category_name_translation¶

Both columns converted to string — pure text reference table, no numeric values.

In [36]:

df_product_category_name_translation = df_product_category_name_translation.astype('string')

# Verify
print(df_product_category_name_translation.dtypes)

product_category_name            string[python]
product_category_name_english    string[python]
dtype: object

6.10 Convert products¶

product_id and product_category_name converted to string.
All physical dimensions and counts remain float64 — used for analysis and ML.

In [37]:

string_cols = ['product_id', 'product_category_name']
df_products[string_cols] = df_products[string_cols].astype('string')

# Verify
print(df_products.dtypes)

product_id                    string[python]
product_category_name         string[python]
product_name_lenght                  float64
product_description_lenght           float64
product_photos_qty                   float64
product_weight_g                     float64
product_length_cm                    float64
product_height_cm                    float64
product_width_cm                     float64
dtype: object

6.11 Convert sellers¶

All columns converted to string — ID, zip code and location fields are identifiers, not numbers.

In [38]:

string_cols = ['seller_id', 'seller_zip_code_prefix', 'seller_city', 'seller_state']
df_sellers[string_cols] = df_sellers[string_cols].astype('string')

# Verify
print(df_sellers.dtypes)

seller_id                 string[python]
seller_zip_code_prefix    string[python]
seller_city               string[python]
seller_state              string[python]
dtype: object

↑ ContentsStep 7 — Analyze Distributions (Numeric Columns)¶

7.1 customers — describe()¶

In [44]:

print(df_customers.describe())

                             customer_id                customer_unique_id  \
count                              99441                             99441   
unique                             99441                             96096   
top     18955e83d337fd6b2def6b18a428ac77  8d50f5eadf50201ccdcedfb9e2ac8455   
freq                                   1                                17   

       customer_zip_code_prefix customer_city customer_state  
count                     99441         99441          99441  
unique                    14994          4119             27  
top                       22790     sao paulo             SP  
freq                        142         15540          41746

Insight	Value
Total customers	99,441 — all unique, no duplicates
ZIP code areas	14,994 distinct areas across Brazil
Top city	São Paulo — 15,540 customers (15.6%)
Top state	SP — 41,746 customers (42%)

Finding: Customer base is heavily concentrated in São Paulo state — relevant for regional sales analysis.

7.2 geolocation — describe()¶

In [46]:

df_geolocation = df_geolocation[
    (df_geolocation['geolocation_lat'].between(-33.75, 5.27)) &
    (df_geolocation['geolocation_lng'].between(-73.98, -28.85))
]

print(df_geolocation.describe())

       geolocation_lat  geolocation_lng
count     19006.000000     19006.000000
mean        -19.073481       -46.076416
std           7.243093         5.201544
min         -33.689948       -72.916069
25%         -23.564197       -49.006838
50%         -22.419894       -46.632322
75%         -15.616915       -43.260094
max           5.013519       -29.384846

Finding: Invalid coordinates detected — lat max 42.18 (Europe) and lng max 121.10 (China).
Brazil bounds: lat (-33.75, 5.27), lng (-73.98, -28.85).
Strategy: Rows outside Brazil’s geographic bounds removed.

7.3 leads_closed — describe()¶

In [47]:

print(df_leads_closed.describe())

                            won_date  declared_monthly_revenue  \
count                            842              8.420000e+02   
mean   2018-04-23 06:04:00.891923968              7.337768e+04   
min              2017-12-05 02:00:00              0.000000e+00   
25%    2018-03-07 12:48:36.249999872              0.000000e+00   
50%              2018-04-13 21:22:15              0.000000e+00   
75%       2018-05-21 17:38:27.500000              0.000000e+00   
max              2018-11-14 18:04:19              5.000000e+07   
std                              NaN              1.744799e+06   

       has_profile_data  
count        842.000000  
mean           0.102138  
min            0.000000  
25%            0.000000  
50%            0.000000  
75%            0.000000  
max            1.000000  
std            0.303009

Insight	Value
Date range	Dec 2017 — Nov 2018 (~1 year)
Median monthly revenue	0 — 75% of leads declared no revenue
Max monthly revenue	50,000,000 — extreme outlier
has_profile_data mean	0.10 — only 10% of leads provided profile data

Finding: declared_monthly_revenue is heavily skewed — most leads declared 0, a few declared very high values. Log transformation may be needed before ML.

7.4 leads_qualified — describe()¶

In [48]:

print(df_leads_qualified.describe())

                  first_contact_date
count                           7940
mean   2018-02-05 11:49:39.748110848
min              2017-06-14 00:00:00
25%              2017-12-31 00:00:00
50%              2018-02-25 00:00:00
75%              2018-04-15 00:00:00
max              2018-05-31 00:00:00

Insight	Value
Total leads	7,940
Date range	Jun 2017 — May 2018 (~1 year)
Median contact date	Feb 25, 2018

Finding: Dataset covers exactly 1 year of marketing activity. Useful for analyzing lead volume trends over time (monthly/quarterly).

7.5 order_items — describe()¶

In [ ]:

print(df_order_items.describe() )

       order_item_id            shipping_limit_date          price  \
count  112650.000000                         112650  112650.000000   
mean        1.197834  2018-01-07 15:36:52.192685312     120.653739   
min         1.000000            2016-09-19 00:15:34       0.850000   
25%         1.000000     2017-09-20 20:57:27.500000      39.900000   
50%         1.000000            2018-01-26 13:59:35      74.990000   
75%         1.000000  2018-05-10 14:34:00.750000128     134.900000   
max        21.000000            2020-04-09 22:35:08    6735.000000   
std         0.705124                            NaN     183.633928   

       freight_value  
count  112650.000000  
mean       19.990320  
min         0.000000  
25%        13.080000  
50%        16.260000  
75%        21.150000  
max       409.680000  
std        15.806405

Insight	Value
Total items	112,650
Median price	R$ 74.99 — mean R$ 120.65 (skewed by outliers)
Max price	R$ 6,735 — extreme outlier
Median freight	R$ 16.26
Max freight	R$ 409.68
Items per order	75% of orders have only 1 item (max 21)

Finding: price is heavily right-skewed — log transformation recommended before ML.
shipping_limit_date max is 2020 while order data ends 2018 — possible data quality issue.

7.6 order_payments — describe()¶

In [50]:

print(df_order_payments.describe())

       payment_sequential  payment_installments  payment_value
count       103886.000000         103886.000000  103886.000000
mean             1.092679              2.853349     154.100380
std              0.706584              2.687051     217.494064
min              1.000000              0.000000       0.000000
25%              1.000000              1.000000      56.790000
50%              1.000000              1.000000     100.000000
75%              1.000000              4.000000     171.837500
max             29.000000             24.000000   13664.080000

Insight	Value
Total payments	103,886
Median payment value	R$ 100.00 — mean R$ 154.10 (skewed by outliers)
Max payment value	R$ 13,664 — extreme outlier
Median installments	1 — 75% pay in max 4 installments
Max installments	24 — installment payments are common in Brazil
Min installments	0 — possible data anomaly

Finding: Installment payments (payment_installments) reflect Brazilian consumer behavior — paying in multiple installments is culturally common.
payment_installments = 0 should be investigated — likely voucher or gift card payments.

7.7 order_reviews — describe()¶

In [51]:

print(df_order_reviews.describe())

       review_score           review_creation_date  \
count  99224.000000                          99224   
mean       4.086421  2018-01-12 20:49:23.948238336   
min        1.000000            2016-10-02 00:00:00   
25%        4.000000            2017-09-23 00:00:00   
50%        5.000000            2018-02-02 00:00:00   
75%        5.000000            2018-05-16 00:00:00   
max        5.000000            2018-08-31 00:00:00   
std        1.347579                            NaN   

             review_answer_timestamp   has_comment  
count                          99224  99224.000000  
mean   2018-01-16 00:23:56.977938688      0.412975  
min              2016-10-07 18:32:28      0.000000  
25%    2017-09-27 01:53:27.249999872      0.000000  
50%       2018-02-04 22:41:47.500000      0.000000  
75%       2018-05-20 12:11:21.500000      1.000000  
max              2018-10-29 12:27:35      1.000000  
std                              NaN      0.492371

Insight	Value
Total reviews	99,224
Median score	5.0 — mean 4.09 (left-skewed, mostly positive)
Has comment	41% of customers left a written comment
Date range	Oct 2016 — Aug 2018

Finding: Review scores are heavily skewed toward 5 stars — most customers are satisfied.
Negative reviews (1–2 stars) are the minority but likely contain the most valuable text comments for NLP analysis.
has_comment correlates with dissatisfaction — worth testing as ML feature.

7.8 orders — describe()¶

In [52]:

print(df_orders.describe() )

            order_purchase_timestamp              order_approved_at  \
count                          99441                          99281   
mean   2017-12-31 08:43:12.776581120  2017-12-31 18:35:24.098800128   
min              2016-09-04 21:15:19            2016-09-15 12:16:38   
25%              2017-09-12 14:46:19            2017-09-12 23:24:16   
50%              2018-01-18 23:04:36            2018-01-19 11:36:13   
75%              2018-05-04 15:42:16            2018-05-04 20:35:10   
max              2018-10-17 17:30:18            2018-09-03 17:40:06   
std                              NaN                            NaN   

        order_delivered_carrier_date  order_delivered_customer_date  \
count                          97658                          96476   
mean   2018-01-04 21:49:48.138278656  2018-01-14 12:09:19.035542272   
min              2016-10-08 10:34:01            2016-10-11 13:46:32   
25%    2017-09-15 22:28:50.249999872  2017-09-25 22:07:22.249999872   
50%              2018-01-24 16:10:58     2018-02-02 19:28:10.500000   
75%              2018-05-08 13:37:45  2018-05-15 22:48:52.249999872   
max              2018-09-11 19:48:28            2018-10-17 13:22:46   
std                              NaN                            NaN   

       order_estimated_delivery_date  is_delivered  
count                          99441  99441.000000  
mean   2018-01-24 03:08:37.730111232      0.970183  
min              2016-09-30 00:00:00      0.000000  
25%              2017-10-03 00:00:00      1.000000  
50%              2018-02-15 00:00:00      1.000000  
75%              2018-05-25 00:00:00      1.000000  
max              2018-11-12 00:00:00      1.000000  
std                              NaN      0.170082

Insight	Value
Total orders	99,441
Delivered orders	96,476 (97%)
Never approved	160 — cancelled before approval
Never reached carrier	1,783
Never reached customer	2,965
Date range	Sep 2016 — Oct 2018

Finding: 97% delivery rate is strong. The 3% undelivered orders are worth investigating —
late deliveries and cancellations are a key driver of negative reviews.
Delivery delay (estimated vs actual date) will be a strong ML feature for predicting review score.

7.9 product_category_name_translation — describe()¶

In [53]:

print(df_product_category_name_translation.describe())

       product_category_name product_category_name_english
count                     71                            71
unique                    71                            71
top             beleza_saude                 health_beauty
freq                       1                             1

Insight	Value
Total categories	71 — all unique, no duplicates
Top category	`beleza_saude` → `health_beauty`

Note: Pure lookup table — used to translate Portuguese category names to English before analysis.

7.10 products — describe()¶

In [54]:

print(df_products.describe())

       product_name_lenght  product_description_lenght  product_photos_qty  \
count         32949.000000                32949.000000        32949.000000   
mean             47.580594                  757.232814            2.148502   
std              12.069277                  637.750493            1.745738   
min               0.000000                    0.000000            0.000000   
25%              41.000000                  326.000000            1.000000   
50%              51.000000                  584.000000            1.000000   
75%              57.000000                  961.000000            3.000000   
max              76.000000                 3992.000000           20.000000   

       product_weight_g  product_length_cm  product_height_cm  \
count      32949.000000       32949.000000       32949.000000   
mean        2276.472488          30.815078          16.937661   
std         4282.038731          16.914458          13.637554   
min            0.000000           7.000000           2.000000   
25%          300.000000          18.000000           8.000000   
50%          700.000000          25.000000          13.000000   
75%         1900.000000          38.000000          21.000000   
max        40425.000000         105.000000         105.000000   

       product_width_cm  
count      32949.000000  
mean          23.196728  
std           12.079047  
min            6.000000  
25%           15.000000  
50%           20.000000  
75%           30.000000  
max          118.000000

In [56]:

zero_weight = df_products[df_products['product_weight_g'] == 0]
print(f"Produkte mit 0g: {len(zero_weight)}")
print(zero_weight[['product_id', 'product_category_name', 'product_weight_g']])

Produkte mit 0g: 4
                             product_id product_category_name  \
9769   81781c0fed9fe1ad6e8c81fca1e1cb08       cama_mesa_banho   
13683  8038040ee2a71048d4bdbbdc985b69ab       cama_mesa_banho   
14997  36ba42dd187055e1fbe943b2d11430ca       cama_mesa_banho   
32079  e673e90efa65a5409ff4196c038bb5af       cama_mesa_banho   

       product_weight_g  
9769                0.0  
13683               0.0  
14997               0.0  
32079               0.0

In [ ]:

# 
df_products = df_products[df_products['product_weight_g'] > 0]

Insight	Value
Total products	32,949
Median weight	700g — mean 2,276g (skewed by heavy outliers)
Max weight	40,425g (40kg) — extreme outlier
Min weight	0g — data anomaly, needs filtering
Median photos	1 — most products have only 1 photo
Max photos	20
Min name length	0 — products with no name, data anomaly

product_weight_g = 0: 4 products in category cama_mesa_banho (bed & bath) —

physical products with missing weight data, not digital products. Rows dropped.

7.11 sellers — describe()¶

In [57]:

print(df_sellers.describe())

                               seller_id seller_zip_code_prefix seller_city  \
count                               3095                   3095        3095   
unique                              3095                   2246         611   
top     1b938a7ec6ac5061a66a3766e0e75f90                  14940   sao paulo   
freq                                   1                     49         694   

       seller_state  
count          3095  
unique           23  
top              SP  
freq           1849

Insight	Value
Total sellers	3,095 — all unique
Top city	São Paulo — 694 sellers (22%)
Top state	SP — 1,849 sellers (60%)
Distinct cities	611
Distinct states	23

Finding: Sellers are even more concentrated in São Paulo than customers (60% vs 42%).
This geographic overlap may explain shorter delivery times for SP customers.

Tabelle	Beschreibung	Wichtige Spalten
`df_orders`	Zentrale Tabelle. Eine Zeile pro Bestellung — Status, Zeitstempel, Verbindung zum Kunden	`order_id`, `customer_id`, `order_status`, 5× Datumsspalten, `is_delivered`
`df_customers`	Eine Zeile pro Bestell-Kunde. `customer_unique_id` identifiziert die echte Person über mehrere Bestellungen	`customer_id`, `customer_unique_id`, PLZ, Stadt, Bundesstaat
`df_order_items`	Eine Zeile pro Artikel in einer Bestellung. Eine Bestellung kann mehrere Artikel von verschiedenen Verkäufern enthalten	`order_id`, `product_id`, `seller_id`, `price`, `freight_value`
`df_order_payments`	Eine Zeile pro Zahlung. Eine Bestellung kann mit mehreren Methoden bezahlt werden (Karte + Gutschein)	`order_id`, `payment_type`, `payment_installments`, `payment_value`
`df_order_reviews`	Kundenbewertung pro Bestellung. Kommentarfelder optional — die meisten Kunden hinterließen nur eine Punktzahl	`order_id`, `review_score`, `review_comment_message`, `has_comment`
`df_products`	Eine Zeile pro Produkt. Kategorie und physische Abmessungen. 610 Zeilen ohne Kategorie	`product_id`, `product_category_name`, Gewicht, Abmessungen
`df_sellers`	Eine Zeile pro Verkäufer. Standortdaten — für Entfernungsberechnung zwischen Verkäufer und Kunde	`seller_id`, PLZ, Stadt, Bundesstaat
`df_geolocation`	GPS-Koordinaten pro PLZ. Nach Deduplizierung auf einen Mittelpunkt pro PLZ aggregiert	`zip_code_prefix`, `lat`, `lng`, Stadt, Bundesstaat
`df_product_category_name_translation`	Nachschlagetabelle — übersetzt portugiesische Kategorienamen ins Englische	`product_category_name`, `product_category_name_english`
`df_leads_qualified`	Marketing-Leads die Olist’s Landingpage besucht haben. Erster Schritt im Verkäufer-Funnel	`mql_id`, `first_contact_date`, `landing_page_id`, `origin`
`df_leads_closed`	Leads die zu Verkäufern konvertiert sind. Verbindung zu leads_qualified über mql_id	`mql_id`, `seller_id`, `won_date`, `business_type`, `has_profile_data`

In [ ]:

df_customers
df_geolocation
df_leads_closed 
df_leads_qualified 
df_order_items 
df_order_payments 
df_order_reviews  
df_orders 
df_product_category_name_translation 
df_products 
df_sellers

↑ ContentsStep 8 — Analyze Categorical Columns¶

8.1 order_status — value_counts()¶

In [ ]:

print("=== order_status ===")
print(df_orders['order_status'].value_counts())

=== order_status ===
order_status
delivered      96478
shipped         1107
canceled         625
unavailable      609
invoiced         314
processing       301
created            5
approved           2
Name: count, dtype: Int64

Status	Count	%
delivered	96,478	97.0%
shipped	1,107	1.1%
canceled	625	0.6%
unavailable	609	0.6%
invoiced	314	0.3%
processing	301	0.3%
created	5	<0.1%
approved	2	<0.1%

Finding: 97% of orders are delivered successfully.
canceled and unavailable together account for ~1.2% — worth analyzing if they correlate with negative reviews.

8.2 payment_type — value_counts()¶

In [59]:

print("\n=== payment_type ===")
print(df_order_payments['payment_type'].value_counts())

=== payment_type ===
payment_type
credit_card    76795
boleto         19784
voucher         5775
debit_card      1529
not_defined        3
Name: count, dtype: Int64

Payment Type	Count	%
credit_card	76,795	74%
boleto	19,784	19%
voucher	5,775	6%
debit_card	1,529	1%
not_defined	3	<0.1%

Finding: Credit card dominates with 74%. boleto (19%) is a Brazilian-specific payment method — a bank slip paid in cash or online.
not_defined (3 rows) is a data anomaly — negligible, can be ignored.

8.3 product_category — Top 10¶

In [60]:

print("\n=== product_category (Top 10) ===")
print(df_products['product_category_name'].value_counts().head(10))

=== product_category (Top 10) ===
product_category_name
cama_mesa_banho           3029
esporte_lazer             2867
moveis_decoracao          2657
beleza_saude              2444
utilidades_domesticas     2335
automotivo                1900
informatica_acessorios    1639
brinquedos                1411
relogios_presentes        1329
telefonia                 1134
Name: count, dtype: Int64

Category (PT)	Category (EN)	Count
cama_mesa_banho	bed & bath	3,029
esporte_lazer	sports & leisure	2,867
moveis_decoracao	furniture & decor	2,657
beleza_saude	health & beauty	2,444
utilidades_domesticas	home appliances	2,335
automotivo	automotive	1,900
informatica_acessorios	computer accessories	1,639
brinquedos	toys	1,411
relogios_presentes	watches & gifts	1,329
telefonia	telephony	1,134

Finding: Home & lifestyle categories dominate — bed/bath, furniture, and sports are the top 3.
Tech categories (computer accessories, telephony) are also well represented.

8.4 seller_state — value_counts()¶

In [ ]:

print("\n=== seller_state ===")
print(df_sellers['seller_state'].value_counts())

=== seller_state ===
seller_state
SP    1849
PR     349
MG     244
SC     190
RJ     171
RS     129
GO      40
DF      30
ES      23
BA      19
CE      13
PE       9
PB       6
MS       5
RN       5
MT       4
RO       2
SE       2
AC       1
PI       1
MA       1
AM       1
PA       1
Name: count, dtype: Int64

State	Count	%
SP (São Paulo)	1,849	60%
PR (Paraná)	349	11%
MG (Minas Gerais)	244	8%
SC (Santa Catarina)	190	6%
RJ (Rio de Janeiro)	171	6%
Others (18 states)	292	9%

Finding: 60% of all sellers are based in São Paulo — Brazil’s economic hub.
The top 5 states account for 91% of all sellers, while 18 states share only 9%.
This concentration likely impacts delivery times for customers in remote states.

8.5 customer_state — Top 10¶

In [62]:

print("\n=== customer_state ===")
print(df_customers['customer_state'].value_counts().head(10))

=== customer_state ===
customer_state
SP    41746
RJ    12852
MG    11635
RS     5466
PR     5045
SC     3637
BA     3380
DF     2140
ES     2033
GO     2020
Name: count, dtype: Int64

State	Count	%
SP (São Paulo)	41,746	42%
RJ (Rio de Janeiro)	12,852	13%
MG (Minas Gerais)	11,635	12%
RS (Rio Grande do Sul)	5,466	5%
PR (Paraná)	5,045	5%
SC (Santa Catarina)	3,637	4%
BA (Bahia)	3,380	3%
DF (Brasília)	2,140	2%
ES (Espírito Santo)	2,033	2%
GO (Goiás)	2,020	2%

Finding: SP, RJ and MG together account for 67% of all customers.
Customers are more spread across Brazil than sellers — interesting for delivery time analysis.
States like BA, DF, GO have many customers but very few sellers → longer delivery times expected.

8.6 lead origin — value_counts()¶

In [ ]:

print("\n=== lead origin ===")
print(df_leads_qualified['origin'].value_counts())

=== lead origin ===
origin
organic_search       2296
paid_search          1586
social               1350
unknown              1099
direct_traffic        499
email                 493
referral              284
other                 150
display               118
other_publicities      65
Name: count, dtype: Int64

Origin	Count	%
organic_search	2,296	29%
paid_search	1,586	20%
social	1,350	17%
unknown	1,099	14%
direct_traffic	499	6%
email	493	6%
referral	284	4%
other / display / publicities	333	4%

Finding: Organic search is the top lead source (29%) — Olist’s SEO is effective.
Paid search + social together account for 37% — significant marketing spend.
14% unknown origin is a tracking gap worth noting.

8.7 business_type — value_counts()¶

In [64]:

print("\n=== business_type ===")
print(df_leads_closed['business_type'].value_counts())

=== business_type ===
business_type
reseller        587
manufacturer    242
Unknown          10
other             3
Name: count, dtype: Int64

Business Type	Count	%
reseller	587	69%
manufacturer	242	29%
Unknown	10	1%
other	3	<1%

Finding: 69% of closed leads are resellers — they buy products and resell on Olist.
Manufacturers (29%) sell their own products directly.
Unknown and other are negligible — previously filled from missing values.

8.8 review_score — value_counts()¶

In [65]:

print("\n=== review_score ===")
print(df_order_reviews['review_score'].value_counts().sort_index())

=== review_score ===
review_score
1    11424
2     3151
3     8179
4    19142
5    57328
Name: count, dtype: int64

Score	Count	%
5	57,328	58%
4	19,142	19%
1	11,424	12%
3	8,179	8%
2	3,151	3%

Finding: Distribution is strongly bimodal — customers either love (5★ 58%) or hate (1★ 12%) the experience.
Score 2 and 3 are the least common — customers rarely feel “neutral”.
1★ reviews are the second most common score — key target for ML prediction.

↑ ContentsStep 9 — Visualizations (Matplotlib / Seaborn)¶

9.1 Orders per Month¶

In [103]:

# Orders per month (order_purchase_timestamp already converted to datetime in Step 6.8)
orders_per_month = df_orders.groupby(df_orders['order_purchase_timestamp'].dt.to_period('M')).size()

plt.figure(figsize=(14, 5), facecolor='#f8f8f8')
ax = plt.gca()
ax.set_facecolor('#f8f8f8')

plt.plot(orders_per_month.index.astype(str), orders_per_month.values, 
         marker='o', linewidth=2.5, color='#2196F3', markerfacecolor='white', 
         markeredgecolor='#2196F3', markeredgewidth=2, markersize=7)

# Fill area under the line
plt.fill_between(orders_per_month.index.astype(str), orders_per_month.values, 
                 alpha=0.1, color='#2196F3')

# Annotate Black Friday
plt.annotate(f'Black Friday\n{orders_per_month["2017-11"]:,} orders', 
             xy=('2017-11', orders_per_month['2017-11'] ),
             xytext=('2017-08', orders_per_month['2017-11'] - 300),
             arrowprops=dict(arrowstyle='->', color='red'),
             fontsize=10, color='red', alpha=0.7)

ax.yaxis.grid(True, linestyle='--', alpha=0.5)
ax.set_axisbelow(True)
plt.xticks(rotation=45, fontsize=10)
plt.title('Orders per Month', fontsize=14, fontweight='bold', pad=15)
plt.xlabel('Month', fontsize=12)
plt.ylabel('Number of Orders', fontsize=12)
sns.despine()
plt.tight_layout()
plt.show()

No description has been provided for this image

We group all orders by purchase month and count them.
This reveals seasonality, growth trends, and anomalies in order volume over time.

Expected findings:

Overall growth trend from 2016 to 2018
Seasonal peaks (e.g. Black Friday, Christmas)
Possible drop at the end — dataset may be incomplete for the last month

Finding: Strong growth from late 2017 through mid-2018.
November 2017 spike likely driven by Black Friday — a major shopping event in Brazil.
Sharp drop at the end of the dataset indicates incomplete data for the final months, not a real decline.

9.2 Review Score Distribution¶

In [104]:

palette=['#d73027', '#f46d43', '#fdae61', '#66bd63', '#1a9850']

plt.figure(figsize=(9, 5), facecolor='#f8f8f8')
ax = sns.countplot(data=df_order_reviews, x='review_score', 
              order=[1, 2, 3, 4, 5], palette=palette,
              hue='review_score', legend=False, alpha=0.5)

ax.set_facecolor('#f8f8f8')
ax.yaxis.grid(True, linestyle='--', alpha=0.6)
ax.set_axisbelow(True)

for p in ax.patches:
    pct = p.get_height() / len(df_order_reviews) * 100
    ax.annotate(f'{int(p.get_height()):,}\n({pct:.1f}%)', 
                (p.get_x() + p.get_width() / 2., p.get_height()),
                ha='center', va='bottom', fontsize=10)

plt.title('Review Score Distribution', fontsize=14, fontweight='bold', pad=15)
plt.xlabel('Review Score', fontsize=12)
plt.ylabel('Number of Reviews', fontsize=12)
plt.xticks([0, 1, 2, 3, 4], ['★', '★★', '★★★', '★★★★', '★★★★★'], fontsize=14)
sns.despine()
plt.tight_layout()
plt.show()

Bar chart showing how customers rated their orders (1–5 stars).
Reveals whether the platform has mostly satisfied or dissatisfied customers.

Finding: Strongly bimodal distribution — 58% gave 5 stars, 12% gave 1 star.
Customers rarely feel neutral (scores 2 and 3 are the least common).
1★ reviews are a key target for ML — predicting dissatisfaction before it happens.

9.3 Top 10 Product Categories¶

In [121]:

# Data preparation
top10_count = df_products['product_category_name'].value_counts().head(10).reset_index()
top10_count.columns = ['product_category_name', 'count']
top10_count = top10_count.merge(df_product_category_name_translation, on='product_category_name')

revenue_by_category = df_order_items.merge(df_products[['product_id', 'product_category_name']], on='product_id') \
                                     .merge(df_product_category_name_translation, on='product_category_name') \
                                     .groupby('product_category_name_english')['price'].sum() \
                                     .sort_values(ascending=False).head(10).reset_index()
revenue_by_category.columns = ['product_category_name_english', 'revenue']

fig, axes = plt.subplots(1, 2, figsize=(20, 7), facecolor='#f8f8f8')

# Left — Top 10 by product count
axes[0].set_facecolor('#f8f8f8')
sns.barplot(data=top10_count, y='product_category_name_english', x='count',
            palette='Blues_r', hue='product_category_name_english', legend=False, ax=axes[0])
for p in axes[0].patches:
    axes[0].annotate(f'{int(p.get_width()):,}',
                (p.get_width() + 20, p.get_y() + p.get_height() / 2.),
                ha='left', va='center', fontsize=10)
axes[0].xaxis.grid(True, linestyle='--', alpha=0.5)
axes[0].set_axisbelow(True)
axes[0].set_title('Top 10 by Number of Products', fontsize=13, fontweight='bold', pad=15)
axes[0].set_xlabel('Number of Products', fontsize=11)
axes[0].set_ylabel('')
sns.despine(ax=axes[0])

# Right — Top 10 by revenue
axes[1].set_facecolor('#f8f8f8')
sns.barplot(data=revenue_by_category, y='product_category_name_english', x='revenue',
            palette='Greens_r', hue='product_category_name_english', legend=False, ax=axes[1])
for p in axes[1].patches:
    axes[1].annotate(f' R$ {int(p.get_width()):,}',
                (p.get_width() + 100, p.get_y() + p.get_height() / 2.),
                ha='left', va='center', fontsize=10)
axes[1].xaxis.grid(True, linestyle='--', alpha=0.5)
axes[1].set_axisbelow(True)
axes[1].set_title('Top 10 by Revenue', fontsize=13, fontweight='bold', pad=15)
axes[1].set_xlabel('Total Revenue (R$)', fontsize=11)
axes[1].set_ylabel('')
sns.despine(ax=axes[1])

plt.suptitle('Top 10 Product Categories', fontsize=15, fontweight='bold', y=1.02)
plt.tight_layout()
plt.show()

Two charts side by side — left shows the most listed categories, right shows the highest revenue generators.
This reveals whether the most common products are also the most profitable.

Finding: Health & beauty and watches & gifts generate high revenue despite fewer products listed.
Bed & bath has the most products but does not lead in revenue — high volume, lower price point.

9.4 Payment Methods¶

In [162]:

payment_counts = df_order_payments['payment_type'].value_counts()
payment_counts = payment_counts[payment_counts.index != 'not_defined']

colors = ['#2196F3', '#4CAF50', '#FF9800', '#9C27B0']

# Hide percentage for small segments
def autopct_format(pct):
    return f'{pct:.1f}%' if pct > 3 else ''

fig, ax = plt.subplots(figsize=(8, 8), facecolor='#f8f8f8')
ax.set_facecolor('#f8f8f8')

wedges, texts, autotexts = ax.pie(
    payment_counts.values,
    labels=None,
    autopct=autopct_format,
    colors=colors,
    startangle=90,
    wedgeprops=dict(width=0.5),
    pctdistance=0.65
)

for autotext in autotexts:
    autotext.set_fontsize(11)
    autotext.set_fontweight('bold')

# Manual annotation for debit_card
ax.annotate('Debit Card\n1.5%',
            xy=(0.05, 1),
            xytext=(-0.11, 1.1),
            arrowprops=dict(arrowstyle='->', color='#9C27B0', lw=1.5),
            fontsize=11, color='#9C27B0', fontweight='bold')

# Legend with clean labels
ax.legend(wedges, ['Credit Card', 'Boleto', 'Voucher', 'Debit Card'],
          loc='lower center', bbox_to_anchor=(0.5, 0),
          ncol=4, fontsize=11)

ax.set_title('Payment Methods', fontsize=14, fontweight='bold', pad=15)
plt.tight_layout()
plt.show()

Donut chart showing the distribution of payment methods across all orders.
debit_card is annotated separately due to its small share.

Payment Method	Share
Credit Card	73.9%
Boleto	19.0%
Voucher	5.6%
Debit Card	1.5%

Finding: Credit card dominates with 74%. boleto (19%) is a Brazilian-specific payment method — a bank slip paid in cash or online.
The high boleto share reflects Brazil’s large unbanked population who cannot use credit cards.

9.5 Delivery Time vs. Review Score¶

In [173]:

# Calculate delivery time in days
df_orders['delivery_time'] = (df_orders['order_delivered_customer_date'] - 
                               df_orders['order_purchase_timestamp']).dt.days

# Merge with reviews
df_delivery_review = df_orders[['order_id', 'delivery_time', 'is_delivered']].merge(
    df_order_reviews[['order_id', 'review_score']], on='order_id')

# Keep only delivered orders
df_delivery_review = df_delivery_review[df_delivery_review['is_delivered'] == 1]

plt.figure(figsize=(10, 6), facecolor='#f8f8f8')
ax = plt.gca()
ax.set_facecolor('#f8f8f8')

sns.boxplot(data=df_delivery_review, x='review_score', y='delivery_time',
            palette=['#d73027', '#f46d43', '#fdae61', '#66bd63', '#1a9850'],
            hue='review_score', legend=False)

# Mean values per score
means = df_delivery_review.groupby('review_score')['delivery_time'].mean()
for i, mean in enumerate(means):
    ax.annotate(f'avg: {mean:.1f}d', xy=(i, mean - 3), 
                ha='center', va='bottom', fontsize=10, 
                fontweight='bold', color='white',
                xytext=(0, 10), textcoords='offset points')

plt.ylim(0, 80)
ax.yaxis.grid(True, linestyle='--', alpha=0.5)
ax.set_axisbelow(True)
plt.title('Delivery Time vs. Review Score', fontsize=14, fontweight='bold', pad=15)
plt.xlabel('Review Score', fontsize=12)
plt.ylabel('Delivery Time (days)', fontsize=12)
plt.xticks([0, 1, 2, 3, 4], ['★', '★★', '★★★', '★★★★', '★★★★★'], fontsize=12)
sns.despine()
plt.tight_layout()
plt.show()

Boxplot showing the relationship between delivery time and customer satisfaction.
Average delivery time per score is annotated directly on each box.

Score	Avg. Delivery Time
★	20.8 days
★★	16.2 days
★★★	13.8 days
★★★★	11.8 days
★★★★★	10.2 days

Finding: Customers who gave 1★ waited on average twice as long as 5★ customers (20.8 vs 10.2 days).
Delivery time is a strong predictor of customer satisfaction — a key feature for the ML model.

9.6 Customers by State¶

In [174]:

# Top 10 customers by state
top10_states = df_customers['customer_state'].value_counts().head(10).reset_index()
top10_states.columns = ['state', 'count']

plt.figure(figsize=(10, 6), facecolor='#f8f8f8')
ax = plt.gca()
ax.set_facecolor('#f8f8f8')

sns.barplot(data=top10_states, y='state', x='count',
            palette='Blues_r', hue='state', legend=False)

# Values at end of bars
for p in ax.patches:
    ax.annotate(f'{int(p.get_width()):,}',
                (p.get_width() + 100, p.get_y() + p.get_height() / 2.),
                ha='left', va='center', fontsize=10)

ax.xaxis.grid(True, linestyle='--', alpha=0.5)
ax.set_axisbelow(True)
plt.title('Top 10 States by Number of Customers', fontsize=14, fontweight='bold', pad=15)
plt.xlabel('Number of Customers', fontsize=12)
plt.ylabel('')
sns.despine()
plt.tight_layout()
plt.show()

Top 10 Brazilian states ranked by number of customers.

Finding: São Paulo (SP) alone accounts for 42% of all customers.
SP, RJ and MG together represent 67% — the southeast region dominates Olist’s customer base.

In [226]:

import plotly.express as px

# Customers per state
customers_per_state = df_customers['customer_state'].value_counts().reset_index()
customers_per_state.columns = ['state', 'count']

fig = px.choropleth(
    customers_per_state,
    geojson='https://raw.githubusercontent.com/codeforamerica/click_that_hood/master/public/data/brazil-states.geojson',
    locations='state',
    featureidkey='properties.sigla',
    color='count',
    color_continuous_scale='Blues',
    title='Customers by State — Brazil',
    labels={'count': 'Number of Customers'},
    hover_data={'state': True, 'count': True}
)

fig.update_geos(
    visible=False,
    lataxis_range=[-43, 15],
    lonaxis_range=[-85, -25],
    projection_scale=1.5
)

fig.update_layout(
    title_font_size=16,
    title_x=0.5,
    geo=dict(bgcolor='rgba(0,0,0,0)'),
    paper_bgcolor='#f8f8f8',
    width=800,
    height=800
)

fig.show()

Interactive choropleth map showing customer distribution across Brazilian states.

Finding: São Paulo (SP) dominates with 42% of all customers — visible as the darkest region.
The southeast region (SP, RJ, MG) concentrates 67% of the customer base.
Northern and central states have very few customers despite their large geographic size.

9.7 Average Delivery Time by State¶

In [246]:

# Average delivery time per state
delivery_per_state = df_orders[['order_id', 'customer_id', 'delivery_time']].merge(
    df_customers[['customer_id', 'customer_state']], on='customer_id'
)

avg_delivery = delivery_per_state.groupby('customer_state')['delivery_time'] \
                                  .mean().round(1).reset_index()
avg_delivery.columns = ['state', 'avg_days']
avg_delivery = avg_delivery.sort_values('avg_days', ascending=False)

national_avg = avg_delivery['avg_days'].mean()

plt.figure(figsize=(10, 8), facecolor='#f8f8f8')
ax = plt.gca()
ax.set_facecolor('#f8f8f8')

sns.barplot(data=avg_delivery, y='state', x='avg_days',
            palette='RdYlGn_r', hue='state', legend=False)

# Values at end of bars
for p in ax.patches:
    ax.annotate(f'{p.get_width():.1f}d',
                (p.get_width() + 0.2, p.get_y() + p.get_height() / 2.),
                ha='left', va='center', fontsize=9)

# National average line
plt.axvline(x=national_avg, color='blue', linestyle='-', alpha=0.4)
plt.text(national_avg + 0.2, len(avg_delivery) - 1, 
         f'National avg: {national_avg:.1f}d', fontsize=10, color='black')

ax.xaxis.grid(True, linestyle='--', alpha=0.5)
ax.set_axisbelow(True)
plt.title('Average Delivery Time by State', fontsize=14, fontweight='bold', pad=15)
plt.xlabel('Average Delivery Time (days)', fontsize=12)
plt.ylabel('')
sns.despine()
plt.tight_layout()
plt.show()

Horizontal bar chart showing average delivery time per customer state, sorted from longest to shortest.
Dashed line shows the national average of 18.3 days.

Fastest	Days	Slowest	Days
SP	8.3d	RR	29.0d
PR	11.5d	AP	26.7d
MG	11.5d	AM	26.0d

Finding: SP customers receive orders in 8.3 days — 3.5x faster than RR (29.0 days).
Remote northern states (RR, AP, AM) wait the longest — few sellers ship to these regions.
15 out of 27 states are above the national average of 18.3 days.
Most sellers are based in SP — distance is the primary driver of delivery time inequality.

9.8 Correlation Heatmap¶

In [ ]:

import numpy as np

# Select relevant numeric columns
df_corr = df_order_items[['order_id', 'price', 'freight_value']].merge(
    df_order_payments[['order_id', 'payment_value', 'payment_installments']], on='order_id'
).merge(
    df_order_reviews[['order_id', 'review_score', 'has_comment']], on='order_id'
).merge(
    df_orders[['order_id', 'delivery_time']], on='order_id'
).drop(columns='order_id').dropna()

corr = df_corr.corr()

# Mask upper triangle
mask = np.triu(np.ones_like(corr, dtype=bool), k=1)

plt.figure(figsize=(10, 8), facecolor='#f8f8f8')
ax = plt.gca()
ax.set_facecolor('#f8f8f8')

sns.heatmap(corr,
            mask=mask,
            annot=True,
            fmt='.2f',
            cmap='RdYlGn',
            linewidths=1,
            linecolor='white',
            square=True,
            annot_kws={'size': 11, 'weight': 'bold'},
            vmin=-1, vmax=1,
            cbar_kws={'shrink': 0.8})

plt.title('Correlation Heatmap', fontsize=14, fontweight='bold', pad=20)
plt.xticks(rotation=45, ha='right', fontsize=11)
plt.yticks(rotation=0, fontsize=11)
plt.tight_layout()
plt.show()

Correlation matrix of key numeric variables. Values range from -1 (strong negative) to +1 (strong positive).
Only the lower triangle is shown — the matrix is symmetric.

Correlation	Value	Interpretation
`price` ↔ `payment_value`	0.74	Higher priced items lead to higher payment values
`delivery_time` ↔ `review_score`	-0.30	Longer delivery = lower review score
`review_score` ↔ `has_comment`	-0.28	Dissatisfied customers write more comments
`price` ↔ `freight_value`	0.41	Heavier/larger items cost more to ship

Finding: delivery_time is the strongest negative predictor of review_score — confirms our earlier analysis.
price and payment_value are strongly correlated (0.74) — expected, as payment covers the item price.
Most variables show weak correlations — suggesting non-linear relationships worth exploring with ML.

↑ ContentsStep 10 — Summary & Key Findings¶

Dataset Overview¶

9 tables, 99,441 orders, 96,096 unique customers, 3,095 sellers
Time period: September 2016 — October 2018 (~2 years)
Geography: 27 Brazilian states, heavily concentrated in São Paulo

Data Quality¶

Issue	Table	Action
>90% missing values	`leads_closed`	Binary column `has_profile_data` created
Optional comment fields	`order_reviews`	Binary column `has_comment` created
Date columns as NaT	`orders`	Kept as NaT — not delivered orders have no date
Invalid coordinates	`geolocation`	Filtered to Brazil bounds
261,831 duplicates	`geolocation`	Aggregated to centroid per ZIP
4 products with 0g weight	`products`	Rows dropped

Key Business Findings¶

Delivery

97% of orders are successfully delivered
Average delivery time: 18.3 days nationally
SP customers receive orders in 8.3 days — RR customers wait 29.0 days (3.5x longer)

Customer Satisfaction

58% of customers give 5★ — platform is generally well received
1★ reviews correlate with longer delivery times (avg 20.8 days vs 10.2 days for 5★)
Dissatisfied customers are more likely to leave a written comment

Revenue & Payments

Credit card dominates (74%), boleto second (19%) — reflects Brazilian payment culture
Top revenue categories: health & beauty, watches & gifts, computers
price and payment_value strongly correlated (0.74)

Geography

60% of sellers are in São Paulo — explains delivery time inequality
42% of customers are in São Paulo, 67% in SP+RJ+MG combined

Marketing Funnel

7,940 qualified leads → 842 closed deals (~10.6% conversion rate)
Organic search is the top lead source (29%)
Only 10% of closed leads provided full profile data

Features for ML Model¶

Feature	Target	Reason
`delivery_time`	`review_score`	Strongest predictor (-0.30)
`has_comment`	`review_score`	Negative reviews correlate with comments
`price`	`review_score`	Higher value orders have higher expectations
`payment_installments`	`review_score`	Reflects financial stress of customer
`freight_value`	`review_score`	High shipping cost may cause dissatisfaction

Next: 02_SQL_Analysis.ipynb — deeper analysis using SQL queries on the SQLite database.

CategoryUncategorized

Olist Brazilian E-Commerce¶

Notebook 01 — Exploratory Data Analysis (EDA)¶

↑ ContentsStep 1 — Import Libraries¶

↑ ContentsStep 2 — Load Data (all tables)¶

↑ ContentsStep 3 — First Overview (shape, dtypes, head)¶

↑ ContentsStep 4 — Missing Values¶

4.1 customers — Missing Values¶

4.2 geolocation — Missing Values¶

4.3 leads_closed — Missing Values¶

4.4 leads_qualified — Missing Values¶

4.5 order_items — Missing Values¶

4.6 order_payments — Missing Values¶

4.7 order_reviews — Missing Values¶

4.8 orders — Missing Values¶

4.9 product_category_name_translation — Missing Values¶

4.10 products — Missing Values¶

4.11 sellers — Missing Values¶

↑ ContentsStep 5 — Duplicates¶

↑ ContentsStep 6 — Convert Data Types¶

6.1 Convert customers¶

6.2 Convert geolocation¶

6.3 Convert df_leads_closed¶

6.4 Convert leads_qualified¶

6.5 Convert order_items¶

6.6 Convert order_payments¶

6.7 Convert order_reviews¶

6.8 Convert orders¶

6.9 Convert product_category_name_translation¶

6.10 Convert products¶

6.11 Convert sellers¶

↑ ContentsStep 7 — Analyze Distributions (Numeric Columns)¶

7.1 customers — describe()¶

7.2 geolocation — describe()¶

7.3 leads_closed — describe()¶

7.4 leads_qualified — describe()¶

7.5 order_items — describe()¶

7.6 order_payments — describe()¶

7.7 order_reviews — describe()¶

7.8 orders — describe()¶

7.9 product_category_name_translation — describe()¶

7.10 products — describe()¶

7.11 sellers — describe()¶

↑ ContentsStep 8 — Analyze Categorical Columns¶

8.1 order_status — value_counts()¶

8.2 payment_type — value_counts()¶

8.3 product_category — Top 10¶

8.4 seller_state — value_counts()¶

8.5 customer_state — Top 10¶

8.6 lead origin — value_counts()¶

8.7 business_type — value_counts()¶

8.8 review_score — value_counts()¶

↑ ContentsStep 9 — Visualizations (Matplotlib / Seaborn)¶

9.1 Orders per Month¶

9.2 Review Score Distribution¶

9.3 Top 10 Product Categories¶

9.4 Payment Methods¶

9.5 Delivery Time vs. Review Score¶

9.6 Customers by State¶

9.7 Average Delivery Time by State¶

9.8 Correlation Heatmap¶

↑ ContentsStep 10 — Summary & Key Findings¶

Dataset Overview¶

Data Quality¶

Key Business Findings¶

Features for ML Model¶

Leave a Reply Cancel reply

Meta