E-Commerce Analysis
The goal of this notebook is to provide an analytical perspective on e-commerce relationships in Brazil. To achieve this, we will begin by conducting an exploratory data analysis using graphical tools. This will enable us to create self-explanatory plots that facilitate a better understanding of the Brazilian online purchasing landscape. Finally, we will analyze customer reviews and implement Sentiment Analysis by utilizing Natural Language Processing tools to classify the text.
Throughout this notebook, we will embark on an extensive journey to fully comprehend the data and produce useful charts that elucidate key concepts and offer insights derived from the data. In conclusion, we will provide a step-by-step guide on text preparation and sentiment classification, utilizing customer reviews from various online platforms.
Libraries
# Standard libs
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib inline
from matplotlib.gridspec import GridSpec
pd.set_option('display.max_columns', 100)
import plotly.offline as py
import plotly.express as px
import plotly.graph_objs as go
import json
import requests
import folium
from folium.plugins import FastMarkerCluster, Fullscreen, MiniMap, HeatMap, HeatMapWithTime, LocateControl
from wordcloud import WordCloud
from collections import Counter
from PIL import Image
# Utilities
#from viz_utils import *
#from custom_transformers import *
#from ml_utils import *
# DataPrep
import re
from nltk.corpus import stopwords
from nltk.stem import RSLPStemmer
from sklearn.feature_extraction.text import CountVectorizer, TfidfVectorizer
from sklearn.base import BaseEstimator, TransformerMixin
from sklearn.pipeline import Pipeline
import joblib
# Modeling
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LogisticRegression
from sklearn.naive_bayes import GaussianNB
import lightgbm as lgb
Reading the Data
For this task we have differente data sources, each one describing a specific topic related to e-commerce sales. The files are:
olist_customers_dataset.csv olist_geolocation_dataset.csv olist_orders_dataset.csv olist_order_items_dataset.csv olist_order_payments_dataset.csv olist_order_reviews_dataset.csv olist_products_dataset.csv olist_sellers_dataset.csv product_category_name_translation.csv
The relationship between these files are described on the documentation. So let's read the datasets and make an initial analysis with all of them. This step will help us a lot to take right decisions in a future exploratory data analysis
# Reading all the files
raw_path = '/work/dataset/'
olist_customer = pd.read_csv(raw_path + 'olist_customers_dataset.csv')
olist_geolocation = pd.read_csv(raw_path + 'olist_geolocation_dataset.csv')
olist_orders = pd.read_csv(raw_path + 'olist_orders_dataset.csv')
olist_order_items = pd.read_csv(raw_path + 'olist_order_items_dataset.csv')
olist_order_payments = pd.read_csv(raw_path + 'olist_order_payments_dataset.csv')
olist_order_reviews = pd.read_csv(raw_path + 'olist_order_reviews_dataset.csv')
olist_products = pd.read_csv(raw_path + 'olist_products_dataset.csv')
olist_sellers = pd.read_csv(raw_path + 'olist_sellers_dataset.csv')
An Overview of the Dataset
Before creating a unique dataset with all useful information, let's look at the shape of each dataset, so we can be more assertive on how to use joining statements.
# Collections for each dataset
datasets = [olist_customer, olist_geolocation, olist_orders, olist_order_items, olist_order_payments,
olist_order_reviews, olist_products, olist_sellers]
names = ['olist_customer', 'olist_geolocation', 'olist_orders', 'olist_order_items', 'olist_order_payments',
'olist_order_reviews', 'olist_products', 'olist_sellers']
data_info=pd.DataFrame()
data_info['dataset']=names
data_info['n_rows']=[df.shape[0] for df in datasets]
data_info['n_cols']=[df.shape[1] for df in datasets]
data_info['null_amount']=[df.isnull().sum().sum() for df in datasets]
data_info['qty_null_columns']=[len([col for col, null in df.isnull().sum().items() if null > 0]) for df in datasets]
data_info['null_columns']=[', '.join([col for col, null in df.isnull().sum().items() if null > 0]) for df in datasets]
data_info.style.background_gradient()
Now let's look at each dataset and bring some detailed parameters about the data content.
df_overview = pd.DataFrame()
name_col = []
Exploratory Data Analysis
So now we will go trough an exploratory data analysis to get insights from E-Commerce in Brazil. The aim here is to divide this session into topics so we can explore graphics for each subject (orders, customers, products, items, and others).
Total Orders on E-Commerce
We know that e-commerce is really a growing trend in a global perspective. Let's dive into the orders dataset to see how this trend can be presented in Brazil, at least on the dataset range.
Looking at the dataset columns, we can see orders with different status and with different timestamp columns like purchase, approved, delivered and estimated delivery. First, let's look at the status of the orders we have in this dataset.
How many orders we have for each status?
df_orders=olist_orders.merge(olist_customer,how='left',on='customer_id')
x=df_orders['order_status'].value_counts().index
y=df_orders['order_status'].value_counts().values
pe=[np.round(a*100/y.sum(),1) for a in y]
def plot_count(x,y,pe,title,pal):
fig, ax = plt.subplots(figsize=(14, 6))
ax=sns.countplot(data=df_orders, x='order_status',palette=pal,order = df_orders['order_status'].value_counts().index)
#ax = sns.barplot(x=x, y=y,palette=pal)
patches = ax.patches
ax.bar_label(ax.containers[0])
#adding labels to the bars
for i in range(len(patches)):
x = patches[i].get_x() + patches[i].get_width()
y = patches[i].get_height()
p=ax.annotate('{:.1f}%'.format(pe[i]), (x, y), ha='center')
p.set(color = "black", size = 12)
#customizing the plot
for s in ["top", "right"]:
ax.spines[s].set_visible(False)
# Adding axes and tick labels
ax.tick_params(axis = "x", labelsize = 14, labelrotation = 45)
ax.set_ylabel("count", size = 14)
#ax.set_xlabel(title, size = 14)
ax.set_title(title, size = 14)
plt.show()
plot_count(x,y,pe,'Orders counts','GnBu_d')
By the time this dataset was created, the highest amount of orders went from delivered ones. Only 3% of all orders came from the other status.
Is that a growing trend on e-commerce in Brazil? How can we describe a complete scenario?
For the next plots, let's dive into the real evolution of e-commerce in terms of purchase orders. For this, we have to extract some info on the order_purchase_timestamp following the topics:
1. Transform timestamp columns. 2. Extract time attributes from these datetime columns (year, month, day, day of week and hour). 3. Evaluate the e-commerce scenario using this attributes.
# Changing the data type for date columns
timestamp_cols = ['order_purchase_timestamp', 'order_approved_at', 'order_delivered_carrier_date',
'order_estimated_delivery_date']
for col in timestamp_cols:
df_orders[col] = pd.to_datetime(df_orders[col])
# Extracting attributes for purchase date - Year and Month
df_orders['order_purchase_year'] = df_orders['order_purchase_timestamp'].apply(lambda x: x.year)
df_orders['order_purchase_month'] = df_orders['order_purchase_timestamp'].apply(lambda x: x.month)
df_orders['order_purchase_month_name'] = df_orders['order_purchase_timestamp'].apply(lambda x: x.strftime('%b'))
df_orders['order_purchase_year_month'] = df_orders['order_purchase_timestamp'].apply(lambda x: x.strftime('%Y%m'))
df_orders['order_purchase_date'] = df_orders['order_purchase_timestamp'].apply(lambda x: x.strftime('%Y%m%d'))
# Extracting attributes for purchase date - Day and Day of Week
df_orders['order_purchase_day'] = df_orders['order_purchase_timestamp'].apply(lambda x: x.day)
df_orders['order_purchase_dayofweek'] = df_orders['order_purchase_timestamp'].apply(lambda x: x.dayofweek)
df_orders['order_purchase_dayofweek_name'] = df_orders['order_purchase_timestamp'].apply(lambda x: x.strftime('%a'))
# Extracting attributes for purchase date - Hour and Time of the Day
df_orders['order_purchase_hour'] = df_orders['order_purchase_timestamp'].apply(lambda x: x.hour)
hours_bins = [-0.1, 6, 12, 18, 23]
hours_labels = ['Dawn', 'Morning', 'Afternoon', 'Night']
df_orders['order_purchase_time_day'] = pd.cut(df_orders['order_purchase_hour'], bins=hours_bins, labels=hours_labels)
# New DataFrame after transformations
df_orders.head()
So now we can purpose a complete analysis on orders amount of brazilian e-commerce during the period of the dataset. For that let's plot three graphs using a GridSpec with the aim answer the following questions:
1. Is there any growing trend on brazilian e-commerce? 2. On what day of week brazilian customers tend to do online purchasing? 3. What time brazilian customers tend do buy (Dawn, Morning, Afternoon or Night)?
fig = plt.figure(constrained_layout=True, figsize=(13, 10))
# Axis definition
gs = GridSpec(2, 2, figure=fig)
ax1 = fig.add_subplot(gs[0, :])
ax2 = fig.add_subplot(gs[1, 0])
ax3 = fig.add_subplot(gs[1, 1])
# Lineplot - Evolution of e-commerce orders along time
sns.lineplot(data=df_orders['order_purchase_year_month'].value_counts().sort_index(), ax=ax1,
color='darkslateblue', linewidth=2)
ax1.annotate(f'Highest orders \nreceived', (13, 7500), xytext=(-75, -25),
textcoords='offset points', bbox=dict(boxstyle="round4", fc="w", pad=.8),
arrowprops=dict(arrowstyle='-|>', fc='w'), color='dimgrey', ha='center')
ax1.annotate(f'Noise on data \n(huge decrease)', (23, 0), xytext=(48, 25),
textcoords='offset points', bbox=dict(boxstyle="round4", fc="w", pad=.5),
arrowprops=dict(arrowstyle='-|>', fc='w'), color='dimgrey', ha='center')
for s in ["top", "right"]:
ax1.spines[s].set_visible(False)
for tick in ax1.get_xticklabels():
tick.set_rotation(45)
ax1.set_title('Evolution of Total Orders in Brazilian E-Commerce', size=14, color='dimgrey')
total = float(len(df_orders['order_purchase_dayofweek']))
ax2=sns.countplot(data=df_orders, x='order_purchase_dayofweek',ax=ax2,palette='YlGnBu')
# Barchart - Total of orders by day of week
weekday_label = ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']
ax2.set_xticklabels(weekday_label)
ax2.bar_label(ax2.containers[0])
for p in ax2.patches:
percentage = '{:.1f}%'.format(100 * p.get_height()/total)
x = p.get_x() + p.get_width()/2
y = p.get_height()//2
d=ax2.annotate(percentage, (x, y),ha='center')
d.set(color = "black", size = 12)
ax2.set_title('Total Orders by Day of Week', size=14, color='dimgrey', pad=20)
for s in ["top", "right"]:
ax2.spines[s].set_visible(False)
# Barchart - Total of orders by time of the day
total = float(len(df_orders['order_purchase_time_day']))
day_color_list = ['darkslateblue', 'deepskyblue', 'darkorange', 'purple']
ax3=sns.countplot(data=df_orders, x='order_purchase_time_day',ax=ax3, palette=day_color_list)
ax3.set_title('Total Orders by Time of the Day', size=14, color='dimgrey', pad=20)
ax3.bar_label(ax3.containers[0])
for p in ax3.patches:
percentage = '{:.1f}%'.format(100 * p.get_height()/total)
x = p.get_x() + p.get_width()/2
y = p.get_height()//2
d=ax3.annotate(percentage, (x, y),ha='center')
d.set(color = "black", size = 12)
for s in ["top", "right"]:
ax3.spines[s].set_visible(False)
plt.tight_layout()
plt.show()
By the chart above we can conclude:
E-commerce on Brazil really has a growing trend along the time. We can see some seasonality with peaks at specific months, but in general we can see clear that customers are more prone to buy things online than before.
Monday are the preferred day for brazilians customers and they tend to buy more at afternoons.
Obs: we have a sharp decrease between August 2018 and September 2018 and maybe the origin of that is related to noise on data. For further comparison between 2017 and 2018, let's just consider orders between January and August in both years
E-commerce: a comparison between 2017 and 2018
# Creating figure
fig = plt.figure(constrained_layout=True, figsize=(13, 5))
# Axis definition
gs = GridSpec(1, 3, figure=fig)
ax1 = fig.add_subplot(gs[0, 0])
ax2 = fig.add_subplot(gs[0, 1:])
# Annotation - Grown on e-commerce orders between 2017 and 2018
df_orders_compare = df_orders.query('order_purchase_year in (2017, 2018) & order_purchase_month <= 8')
year_orders = df_orders_compare['order_purchase_year'].value_counts()
growth = int(round(100 * (1 + year_orders[2017] / year_orders[2018]), 0))
ax1.text(0.00, 0.73, f'{year_orders[2017]}', fontsize=40, color='mediumseagreen', ha='center')
ax1.text(0.00, 0.64, 'orders registered in 2017\nbetween January and August', fontsize=10, ha='center')
ax1.text(0.00, 0.40, f'{year_orders[2018]}', fontsize=60, color='darkslateblue', ha='center')
ax1.text(0.00, 0.31, 'orders registered in 2018\nbetween January and August', fontsize=10, ha='center')
signal = '+' if growth > 0 else '-'
ax1.text(0.00, 0.20, f'{signal}{growth}%', fontsize=14, ha='center', color='white', style='italic', weight='bold',
bbox=dict(facecolor='navy', alpha=0.5, pad=10, boxstyle='round, pad=.7'))
ax1.axis('off')
# Bar chart - Comparison between monthly sales between 2017 and 2018
ax2=sns.countplot(df_orders_compare, x='order_purchase_month', hue='order_purchase_year', ax=ax2,
palette='YlGnBu')
month_label = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug']
ax2.set_xticklabels(month_label)
ax2.set_title('Total Orders Comparison Between 2017 and 2018 (January to August)', size=12, color='dimgrey', pad=20)
plt.legend(loc='lower right')
ax2.bar_label(ax2.containers[0])
ax2.bar_label(ax2.containers[1])
for s in ["top", "right"]:
ax2.spines[s].set_visible(False)
plt.show()
E-Commerce Around Brazil
For preparing the data to a workaround analysis on brazilian's states e-commerce, we will take the following steps:
1. Merge the orders data to order_items data; 2. Use an API (brazilian government) to return the region of each customer_state; 3. Purpose useful charts to answer business questions.
Brazilian APIs ans links for geolocation info:
# Merging orders and order_items
df_orders_items=df_orders.merge(olist_order_items,how='left',on='order_id')
# Using the API to bring the region to the data
r=requests.get('https://servicodados.ibge.gov.br/api/v1/localidades/distritos')
content = [c['municipio']['microrregiao']['mesorregiao']['UF'] for c in json.loads(r.text)]
br_info = pd.DataFrame(content)
br_info['nome_regiao'] = br_info['regiao'].apply(lambda x: x['nome'])
br_info.drop('regiao', axis=1, inplace=True)
br_info.drop_duplicates(inplace=True)
# Threting geolocations outside brazilian map
#Brazils most Northern spot is at 5 deg 16′ 27.8″ N latitude.;
geo_prep = olist_geolocation[olist_geolocation.geolocation_lat <= 5.27438888]
#it’s most Western spot is at 73 deg, 58′ 58.19″W Long.
geo_prep = geo_prep[geo_prep.geolocation_lng >= -73.98283055]
#It’s most southern spot is at 33 deg, 45′ 04.21″ S Latitude.
geo_prep = geo_prep[geo_prep.geolocation_lat >= -33.75116944]
#It’s most Eastern spot is 34 deg, 47′ 35.33″ W Long.
geo_prep = geo_prep[geo_prep.geolocation_lng <= -34.79314722]
geo_group = geo_prep.groupby(by='geolocation_zip_code_prefix', as_index=False).min()
# Merging all the informations
df_orders_items = df_orders_items.merge(br_info, how='left', left_on='customer_state', right_on='sigla')
df_orders_items = df_orders_items.merge(geo_group, how='left', left_on='customer_zip_code_prefix',
right_on='geolocation_zip_code_prefix')
df_orders_items.head()
An overview of customer's order by region, state and city
# Filtering data between 201701 and 201808
df_orders_filt = df_orders_items[(df_orders_items['order_purchase_year_month'].astype(int) >= 201701)]
df_orders_filt = df_orders_filt[(df_orders_filt['order_purchase_year_month'].astype(int) <= 201808)]
# Grouping data by region
df_regions_group = df_orders_filt.groupby(by=['order_purchase_year_month', 'nome_regiao'], as_index=False)
df_regions_group = df_regions_group.agg({'customer_id': 'count', 'price': 'sum'}).sort_values(by='order_purchase_year_month')
df_regions_group.columns = ['month', 'region', 'order_count', 'order_amount']
df_regions_group.reset_index(drop=True, inplace=True)
# Grouping data by city (top 10)
df_cities_group = df_orders_filt.groupby(by='geolocation_city',
as_index=False).count().loc[:, ['geolocation_city', 'order_id']]
df_cities_group = df_cities_group.sort_values(by='order_id', ascending=False).reset_index(drop=True)
df_cities_group = df_cities_group.iloc[:10, :]
# Creating and preparing figure and axis
fig = plt.figure(constrained_layout=True, figsize=(15, 10))
gs = GridSpec(2, 2, figure=fig)
ax1 = fig.add_subplot(gs[0, 0])
ax2 = fig.add_subplot(gs[1, 0])
ax3 = fig.add_subplot(gs[:, 1])
# Count of orders by region
sns.lineplot(x='month', y='order_count', ax=ax1, data=df_regions_group, hue='region',
size='region', style='region', palette='magma', markers=['o'] * 5)
for s in ["top", "right"]:
ax1.spines[s].set_visible(False)
ax1.set_title('Evolution of E-Commerce Orders on Brazilian Regions', size=12, color='dimgrey')
ax1.set_ylabel('')
for tick in ax1.get_xticklabels():
tick.set_rotation(45)
# Top cities with more customers orders in Brazil
ax2=sns.barplot(y='geolocation_city', x='order_id', data=df_cities_group, ax=ax2, palette='magma')
for s in ["top", "right"]:
ax2.spines[s].set_visible(False)
ax2.set_title('Top 10 Brazilian Cities with More Orders', size=12, color='dimgrey')
ax2.set_ylabel('')
ax2.bar_label(ax2.containers[0])
#calculating the number of customer in each state
ax3=sns.barplot(y=df_orders_filt['customer_state'].value_counts().index,x=df_orders_filt['customer_state'].value_counts(), ax=ax3, palette='viridis')
ax3.set_title('Total of Customers Orders by State', size=12, color='dimgrey')
ax3.set_ylabel('')
for s in ["top", "right"]:
ax3.spines[s].set_visible(False)
ax3.bar_label(ax3.containers[0])
plt.show()
How customers are distributed in Brazil? (a 30k orders sample from 2018 in a map)
# Zipping locations
lats = list(df_orders_items.query('order_purchase_year == 2018')['geolocation_lat'].dropna().values)[:30000]
longs = list(df_orders_items.query('order_purchase_year == 2018')['geolocation_lng'].dropna().values)[:30000]
locations = list(zip(lats, longs))
# Creating a mapa using folium
map1 = folium.Map(location=[-15, -50], zoom_start=4.0)
# Plugin: FastMarkerCluster
FastMarkerCluster(data=locations).add_to(map1)
map1
By the map we showed above, we have already the insight that the southeast of Brazil has the highest number of orders given through e-commerce.
E-Commerce Impact on Economy
Until now, we just answered questions on E-commerce scenario considering the number of orders received. We could see the volumetry amonth months, day of week, time of the day and even the geolocation states.
Now, we will analyze the money moved by e-commerce by looking at order prices, freights and others
How about the e-commerce sales? Did they grow up over time?
For answering this question, let's first group our data in a way to look at the evolution overall.
# Grouping data
df_month_aggreg = df_orders_filt.groupby(by=['order_purchase_year', 'order_purchase_year_month'], as_index=False)
df_month_aggreg = df_month_aggreg.agg({
'order_id': 'count',
'price': 'sum',
'freight_value': 'sum'
})
# Adding new columns for analysis
df_month_aggreg['price_per_order'] = df_month_aggreg['price'] / df_month_aggreg['order_id']
df_month_aggreg['freight_per_order'] = df_month_aggreg['freight_value'] / df_month_aggreg['order_id']
df_month_aggreg.head()
# Creating figure
fig = plt.figure(constrained_layout=True, figsize=(15, 12))
# Axis definition
gs = GridSpec(2, 3, figure=fig)
ax1 = fig.add_subplot(gs[0, :])
ax2 = fig.add_subplot(gs[1, 0])
ax3 = fig.add_subplot(gs[1, 1:])
# Plot 1 - Evolution of total orders and total sales on e-commerce
df_month_aggreg=df_month_aggreg.sort_values(by='order_purchase_year_month')
sns.lineplot(x='order_purchase_year_month', y='price', ax=ax1, data=df_month_aggreg,linewidth=2, color='darkslateblue', marker='o', label='Total Amount')
ax1_twx = ax1.twinx()
sns.countplot(df_orders_filt, x='order_purchase_year_month', ax=ax1_twx, palette='YlGnBu_r')
ax1_twx.set_yticks(np.arange(0, 20000, 2500))
# Customizing the first plot
ax1.spines["top"].set_visible(False)
ax1_twx.spines["top"].set_visible(False)
for tick in ax1.get_xticklabels():
tick.set_rotation(45)
for x, y in df_month_aggreg.price.items():
ax1.annotate(str(round(y/1000, 1))+'K', xy=(x, y), textcoords='offset points', xytext=(0, 10),
ha='center', color='dimgrey')
ax1.annotate(f'Highest Value Sold on History\n(Black Friday?)', (10, 1000000), xytext=(-120, -20),
textcoords='offset points', bbox=dict(boxstyle="round4", fc="w", pad=.8),
arrowprops=dict(arrowstyle='-|>', fc='w'), color='dimgrey', ha='center')
ax1.set_title('Evolution of E-commerce: Total Orders and Total Amount Sold (R$)', size=14, color='dimgrey', pad=20)
# Plot 2 - Big Numbers of Sales Evolution
month_comparison = ['201701', '201702', '201703', '201704', '201705', '201706', '201707', '201708',
'201801', '201802', '201803', '201804', '201805', '201806', '201807', '201808']
df_sales_compare = df_month_aggreg.query('order_purchase_year_month in (@month_comparison)')
sold_2017 = df_sales_compare.query('order_purchase_year == 2017')['price'].sum()
sold_2018 = df_sales_compare.query('order_purchase_year == 2018')['price'].sum()
growth = 1 + (sold_2017 / sold_2018)
ax2.text(0.50, 0.73, 'R$' + str(round(sold_2017/1000000, 2)) + 'M', fontsize=40, color='mediumseagreen', ha='center')
ax2.text(0.50, 0.60, 'total amount sold in 2017\nbetween January and August', fontsize=10, ha='center')
ax2.text(0.50, 0.36, 'R$' + str(round(sold_2018/1000000, 2)) + 'M', fontsize=60, color='darkslateblue', ha='center')
ax2.text(0.50, 0.25, 'total amount sold in 2018\nbetween January and August', fontsize=10, ha='center')
signal = '+' if growth > 0 else '-'
ax2.text(0.50, 0.13, f'{signal}{str(round(100 * growth, 2))}%', fontsize=14, ha='center', color='white', style='italic', weight='bold',
bbox=dict(facecolor='navy', alpha=0.5, pad=10, boxstyle='round, pad=.7'))
ax2.axis('off')
# Plot 3 - Evolution of mean freight value paid by the customers
sns.lineplot(x='order_purchase_year_month', y='freight_per_order', data=df_month_aggreg, linewidth=2,
color='silver', marker='o', ax=ax3)
ax3.spines["top"].set_visible(False)
ax3.spines["right"].set_visible(False)
for tick in ax3.get_xticklabels():
tick.set_rotation(45)
for x, y in df_month_aggreg.freight_per_order.items():
ax3.annotate(round(y, 2), xy=(x, y), textcoords='offset points', xytext=(0, 10),
ha='center', color='dimgrey')
ax3.set_title('Evolution of Average Freight Value (RS) Paid by Customers', size=14, color='dimgrey', pad=20)
plt.tight_layout()
plt.show()
How the total sales (sum of price) are concentraded in brazilian states?
# Grouping data
df_geo = df_orders_filt.groupby(by='geolocation_state', as_index=False)
t_df1=df_geo.agg({
'price': 'sum',
}).rename(columns={'price':'sum_price'})
t_df2=df_geo.agg({
'price': 'mean',
}).rename(columns={'price':'mean_price'})
t_df1=t_df1.merge(t_df2,how='inner',on='geolocation_state')
t_df1[['sum_price','mean_price']] = t_df1[['sum_price','mean_price']].apply(lambda x: round(x,0))
t_df1.sort_values('mean_price',ascending=False,inplace=True)
t_df1
# Creating figure
fig = plt.figure(constrained_layout=True, figsize=(15, 12))
# Axis definition
gs = GridSpec(1, 2, figure=fig)
ax2 = fig.add_subplot(gs[0, 0])
ax1 = fig.add_subplot(gs[0, 1])
#calculating the sum of price in each state
ax1=sns.barplot(y=t_df1['geolocation_state'],x=t_df1['sum_price'], ax=ax1, palette='viridis')
ax1.set_title('Sum of price by Customer State', size=12, color='dimgrey')
ax1.set_ylabel('')
for s in ["top", "right"]:
ax1.spines[s].set_visible(False)
ax1.bar_label(ax1.containers[0])
#calculating the mean of price in each state
ax2=sns.barplot(y=t_df1['geolocation_state'],x=t_df1['mean_price'], ax=ax2, palette='viridis')
ax2.set_title('Mean of price by Customer State', size=12, color='dimgrey')
ax2.set_ylabel('')
for s in ["top", "right"]:
ax2.spines[s].set_visible(False)
ax2.bar_label(ax2.containers[0])
plt.show()
It's very interesting to see how some states have a high total amount sold and a low price per order. If we look at SP (São Paulo) for example, it's possible to see that it is the state with most valuable state for e-commerce (5,188,099 sold) but it is also where customers pay less per order (110.00 per order).
For the the freight :
# Grouping data
df_geo = df_orders_filt.groupby(by='geolocation_state', as_index=False)
t_df1=df_geo.agg({
'freight_value': 'sum',
}).rename(columns={'freight_value':'sum_freight'})
t_df2=df_geo.agg({
'freight_value': 'mean',
}).rename(columns={'freight_value':'mean_freight'})
t_df1=t_df1.merge(t_df2,how='inner',on='geolocation_state')
t_df1[['sum_freight','mean_freight']] = t_df1[['sum_freight','mean_freight']].apply(lambda x: round(x,0))
t_df1.sort_values('mean_freight',ascending=False,inplace=True)
t_df1
# Creating figure
fig = plt.figure(constrained_layout=True, figsize=(15, 12))
# Axis definition
gs = GridSpec(1, 2, figure=fig)
ax2 = fig.add_subplot(gs[0, 0])
ax1 = fig.add_subplot(gs[0, 1])
#calculating the sum of freight in each state
ax1=sns.barplot(y=t_df1['geolocation_state'],x=t_df1['sum_freight'], ax=ax1, palette='viridis')
ax1.set_title('Sum of freight value by Customer State', size=12, color='dimgrey')
ax1.set_ylabel('')
for s in ["top", "right"]:
ax1.spines[s].set_visible(False)
ax1.bar_label(ax1.containers[0])
#calculating the mean of freight in each state
ax2=sns.barplot(y=t_df1['geolocation_state'],x=t_df1['mean_freight'], ax=ax2, palette='viridis')
ax2.set_title('Mean of freight value by Customer State', size=12, color='dimgrey')
ax2.set_ylabel('')
for s in ["top", "right"]:
ax2.spines[s].set_visible(False)
ax2.bar_label(ax2.containers[0])
plt.show()
Here we can get insights about the customers states with highest mean freight value. For example, customers in Roraima (RR), Paraíba (PB), Rondônia (RO) and Acre (AC) normaly pays more than anyone on freights.
What are the best states to buy in Brazil? An analysis on sales, freight and delivery time
# Calculating working days between purchasing, delivering and estimated delivery
purchasing = df_orders_filt['order_purchase_timestamp']
delivered = df_orders_filt['order_delivered_customer_date']
estimated = df_orders_filt['order_estimated_delivery_date']
df_orders_filt['time_to_delivery'] = calc_working_days(purchasing, delivered, convert=True)
df_orders_filt['diff_estimated_delivery'] = calc_working_days(estimated, delivered, convert=True)
# Grouping data by state
states_avg_grouped = df_orders_filt.groupby(by='customer_state', as_index=False).mean()
states_freight_paid = states_avg_grouped.loc[:, ['customer_state', 'freight_value']]
states_time_to_delivery = states_avg_grouped.loc[:, ['customer_state', 'time_to_delivery']]
states_estimated_delivery = states_avg_grouped.loc[:, ['customer_state', 'diff_estimated_delivery']]
# Sorting data
states_freight_paid = states_freight_paid.sort_values(by='freight_value', ascending=False)
states_time_to_delivery = states_time_to_delivery.sort_values(by='time_to_delivery', ascending=False)
states_estimated_delivery = states_estimated_delivery.sort_values(by='diff_estimated_delivery')
fig, axs = plt.subplots(3, 3, figsize=(15, 10))
# Plot Pack 01 - Freight value paid on states
sns.barplot(x='freight_value', y='customer_state', data=states_freight_paid.head(), ax=axs[1, 0], palette='viridis')
axs[1, 0].set_title('Top 5 States with Highest \nAverage Freight Value', size=12, color='black')
sns.barplot(x='freight_value', y='customer_state', data=states_freight_paid.tail(), ax=axs[2, 0], palette='viridis_r')
axs[2, 0].set_title('Top 5 States with Lowest \nAverage Freight Value', size=12, color='black')
for ax in axs[1, 0], axs[2, 0]:
ax.set_xlabel('Mean Freight Value')
ax.set_xlim(0, states_freight_paid['freight_value'].max())
format_spines(ax, right_border=False)
ax.set_ylabel('')
# Annotations
axs[0, 0].text(0.50, 0.30, f'R${round(df_orders_filt.freight_value.mean(), 2)}', fontsize=45, ha='center')
axs[0, 0].text(0.50, 0.12, 'is the mean value of freight paid', fontsize=12, ha='center')
axs[0, 0].text(0.50, 0.00, 'for online shopping', fontsize=12, ha='center')
axs[0, 0].axis('off')
# Plot Pack 02 - Time to delivery on states
sns.barplot(x='time_to_delivery', y='customer_state', data=states_time_to_delivery.head(), ax=axs[1, 1], palette='viridis')
axs[1, 1].set_title('Top 5 States with Highest \nAverage Time to Delivery', size=12, color='black')
sns.barplot(x='time_to_delivery', y='customer_state', data=states_time_to_delivery.tail(), ax=axs[2, 1], palette='viridis_r')
axs[2, 1].set_title('Top 5 States with Lowest \nAverage Time do Delivery', size=12, color='black')
for ax in axs[1, 1], axs[2, 1]:
ax.set_xlabel('Time to Delivery')
ax.set_xlim(0, states_time_to_delivery['time_to_delivery'].max())
format_spines(ax, right_border=False)
ax.set_ylabel('')
# Annotations
axs[0, 1].text(0.40, 0.30, f'{int(df_orders_filt.time_to_delivery.mean())}', fontsize=45, ha='center')
axs[0, 1].text(0.60, 0.30, 'working days', fontsize=12, ha='center')
axs[0, 1].text(0.50, 0.12, 'is the average delay for delivery', fontsize=12, ha='center')
axs[0, 1].text(0.50, 0.00, 'for online shopping', fontsize=12, ha='center')
axs[0, 1].axis('off')
# Plot Pack 03 - Differnece between delivered and estimated on states
sns.barplot(x='diff_estimated_delivery', y='customer_state', data=states_estimated_delivery.head(), ax=axs[1, 2], palette='viridis')
axs[1, 2].set_title('Top 5 States where Delivery is \nReally Fast Comparing to Estimated', size=12, color='black')
sns.barplot(x='diff_estimated_delivery', y='customer_state', data=states_estimated_delivery.tail(), ax=axs[2, 2], palette='viridis_r')
axs[2, 2].set_title('Top 5 States where Delivery is \nNot So Fast Comparing to Estimated', size=12, color='black')
for ax in axs[1, 2], axs[2, 2]:
ax.set_xlabel('Days Between Delivery and Estimated')
ax.set_xlim(states_estimated_delivery['diff_estimated_delivery'].min()-1,
states_estimated_delivery['diff_estimated_delivery'].max()+1)
format_spines(ax, right_border=False)
ax.set_ylabel('')
# Annotations
axs[0, 2].text(0.40, 0.30, f'{int(df_orders_filt.diff_estimated_delivery.mean())}', fontsize=45, ha='center')
axs[0, 2].text(0.60, 0.30, 'working days', fontsize=12, ha='center')
axs[0, 2].text(0.50, 0.12, 'is the average difference between', fontsize=12, ha='center')
axs[0, 2].text(0.50, 0.00, 'delivery and estimated date', fontsize=12, ha='center')
axs[0, 2].axis('off')
plt.suptitle('Comparative Study: E-Commerce on Brazilian States', size=16)
plt.tight_layout()
plt.show()
Payment Type Analysis
One of the datasets provided have informations about order's payment. To see how payments can take influence on e-commerce, we can build a mini-dashboard with main concepts: payments type and payments installments. The idea is to present enough information to clarify how ecommerce buyers usually prefer to pay orders.
# Grouping data
df_orders_pay = df_orders_filt.merge(olist_order_payments, how='left', on='order_id')
# Creating figure
fig = plt.figure(constrained_layout=True, figsize=(15, 12))
# Axis definition
gs = GridSpec(2, 2, figure=fig)
ax1 = fig.add_subplot(gs[0, 0])
ax2 = fig.add_subplot(gs[0, 1])
ax3 = fig.add_subplot(gs[1, :])
# Plot 1 - Payment types in a donut chart
colors = ['darkslateblue', 'cornflowerblue', 'silver', 'darkviolet', 'crimson']
label_names = df_orders_pay['payment_type'].value_counts().index
plt.pie(df_orders_pay, col='payment_type', ax=ax1, label_names=label_names, colors=colors,
title='Count of Transactions by Payment Type', text=f'{len(df_orders_pay)}\npayments \nregistered')
# Plot 2 - Payment installments
single_countplot(df_orders_pay, ax=ax2, y='payment_installments')
ax2.set_title('A Distribution of Payment Installments', color='dimgrey', size=12)
# Plot 3 - Evolution of payment types
payment_evl = df_orders_pay.groupby(by=['order_purchase_year_month', 'payment_type'], as_index=False).count()
payment_evl = payment_evl.loc[:, ['order_purchase_year_month', 'payment_type', 'order_id']]
payment_evl = payment_evl.sort_values(by=['order_purchase_year_month', 'order_id'], ascending=[True, False])
sns.lineplot(x='order_purchase_year_month', y='order_id', data=payment_evl, ax=ax3, hue='payment_type',
style='payment_type', size='payment_type', palette=colors, marker='o')
format_spines(ax3, right_border=False)
ax3.set_title('Evolution of Payment Types in Brazilian E-Commerce', size=12, color='dimgrey')
plt.show()
In fact, we can see by the line chart that payments made by credit card really took marjority place on brazilian e-commerce. Besides that, since 201803 it's possible to see a little decrease on this type of payment. By the other side, payments made by debit card is showing a growing trend since 201805, wich is a good opportunity for investor to improve services for payments like this.
On the bar chart above, we can see how brazilian customers prefer to pay the orders: mostly of them pay once into 1 installment and it's worth to point out the quantity of payments done by 10 installments.
we close our session for EDA and now we can start the Natural Language Process step on reviews
Natural Language Processing
As long as we could improve our relationship with the data, the path is open to start the Natural Language Processing step to analyze the comments left on e-commerce orders. The goal is to use this as input to a sentimental analysis model for understanding the customer's sentiment on purchasing things online. Let's take a look on the reviews data.