Untitled
Nguyen_LSCM
9/7/2020
setwd('C:/Users/DellPC/Desktop/Corner/Py_source_code/Project/Olist')
htmltools::includeHTML('Olist_Visualization_Py.html')Olist dataset¶
Schema¶
In [297]:
import os
import pandas as pd
import numpy as np
In [298]:
geo = pd.read_csv('olist_geolocation_dataset.csv', dtype = {'geolocation_zip_code_prefix' : str})
In [299]:
geo
Out[299]:
In [300]:
# Get the first three and four first digits of zip codes,
# we will explore his further to understand how zup codes works
geo['geolocation_zip_code_prefix_1_digits'] = geo['geolocation_zip_code_prefix'].str[0:1]
geo['geolocation_zip_code_prefix_2_digits'] = geo['geolocation_zip_code_prefix'].str[0:2]
geo['geolocation_zip_code_prefix_3_digits'] = geo['geolocation_zip_code_prefix'].str[0:3]
geo['geolocation_zip_code_prefix_4_digits'] = geo['geolocation_zip_code_prefix'].str[0:4]
In [301]:
geo.head(10)
Out[301]:
In [302]:
geo['geolocation_zip_code_prefix'].nunique()
(r, c) = geo.shape
r/ geo['geolocation_zip_code_prefix'].nunique()
Out[302]:
In [303]:
geo['geolocation_zip_code_prefix'].value_counts(ascending = True).to_frame().describe()
Out[303]:
In [304]:
# Removing some outliers
geo = geo[geo.geolocation_lat <= 5.27438888]
geo = geo[geo.geolocation_lng >= -73.98283055]
geo = geo[geo.geolocation_lat >= -33.75116944]
geo = geo[geo.geolocation_lng <= -34.79314722]
In [305]:
from datashader.utils import lnglat_to_meters as webm
x, y = webm(geo.geolocation_lng, geo.geolocation_lat)
geo['x'] = pd.Series(x)
geo['y'] = pd.Series(y)
In [306]:
geo.head(3)
Out[306]:
Zip Codes in Brazil¶
In [307]:
# transforming the prefixes to int for plotting purposes
geo['geolocation_zip_code_prefix'] = geo['geolocation_zip_code_prefix'].astype(int)
geo['geolocation_zip_code_prefix_1_digits'] = geo['geolocation_zip_code_prefix_1_digits'].astype(int)
geo['geolocation_zip_code_prefix_2_digits'] = geo['geolocation_zip_code_prefix_2_digits'].astype(int)
geo['geolocation_zip_code_prefix_3_digits'] = geo['geolocation_zip_code_prefix_3_digits'].astype(int)
geo['geolocation_zip_code_prefix_4_digits'] = geo['geolocation_zip_code_prefix_4_digits'].astype(int)
In [308]:
brazil = geo
agg_name = 'geolocation_zip_code_prefix'
brazil[agg_name].describe().to_frame()
Out[308]:
In [309]:
import holoviews as hv
import geoviews as gv
import datashader as ds
from colorcet import fire, rainbow, bgy, bjy, bkr, kb, kr
from datashader.colors import colormap_select, Greys9
from holoviews.streams import RangeXY
from holoviews.operation.datashader import datashade, dynspread, rasterize
from bokeh.io import push_notebook, show, output_notebook
output_notebook()
hv.extension('bokeh')
%opts Overlay [width=800 height=600 toolbar='above' xaxis=None yaxis=None]
%opts QuadMesh [tools=['hover'] colorbar=True] (alpha=0 hover_alpha=0.2)
In [314]:
import holoviews as hv
import geoviews as gv
import datashader as ds
from colorcet import fire, rainbow, bgy, bjy, bkr, kb, kr, gwv
from datashader.colors import colormap_select, Greys9
from holoviews.streams import RangeXY
from holoviews.operation.datashader import datashade, dynspread, rasterize
from bokeh.io import push_notebook, show, output_notebook
output_notebook()
hv.extension('bokeh')
%opts Overlay [width=800 height=600 toolbar='above' xaxis=None yaxis=None]
%opts QuadMesh [tools=['hover'] colorbar=True] (alpha=0 hover_alpha=0.2)
T = 0.05
PX = 1
def plot_map(data, label, agg_data, agg_name, cmap):
url="http://server.arcgisonline.com/ArcGIS/rest/services/Canvas/World_Dark_Gray_Base/MapServer/tile/{Z}/{Y}/{X}.png"
geomap = gv.WMTS(url)
points = hv.Points(gv.Dataset(data, kdims=['x', 'y'], vdims=[agg_name]))
agg = datashade(points, element_type=gv.Image, aggregator=agg_data, cmap=cmap)
zip_codes = dynspread(agg, threshold=T, max_px=PX)
hover = hv.util.Dynamic(rasterize(points, aggregator=agg_data, width=50, height=25, streams=[RangeXY]), operation=hv.QuadMesh)
hover = hover.options(cmap=cmap)
img = geomap * zip_codes * hover
img = img.relabel(label)
return img
In [311]:
brazil.head(2)
agg_name
Out[311]:
In [315]:
plot_map(brazil, 'Zip Codes in Brazil', ds.min(agg_name), agg_name, cmap = gwv)
Out[315]:
In [295]:
import datashader as ds
from datashader import transfer_functions as tf
from functools import partial
from datashader.utils import export_image
from IPython.core.display import HTML, display
from colorcet import fire, rainbow, bgy, bjy, bkr, kb, kr
background = "black"
cm = partial(colormap_select, reverse=(background!="black"))
export = partial(export_image, background = background, export_path="export")
display(HTML("<style>.container { width:100% !important; }</style>"))
W = 700
def create_map(data, cmap, data_agg, export_name='img'):
pad = (data.x.max() - data.x.min())/50
x_range, y_range = ((data.x.min() - pad, data.x.max() + pad),
(data.y.min() - pad, data.y.max() + pad))
ratio = (y_range[1] - y_range[0]) / (x_range[1] - x_range[0])
plot_width = int(W)
plot_height = int(plot_width * ratio)
if ratio > 1.5:
plot_height = 550
plot_width = int(plot_height / ratio)
cvs = ds.Canvas(plot_width=plot_width, plot_height=plot_height, x_range=x_range, y_range=y_range)
agg = cvs.points(data, 'x', 'y', data_agg)
img = tf.shade(agg, cmap=cmap, how='eq_hist')
return export(img, export_name)
In [316]:
create_map(brazil, gwv, ds.mean(agg_name),'brazil_zip_codes')
Out[316]:
Zip Codes in States¶
In [130]:
geo.head(2)
Out[130]:
In [128]:
def filter_data(level, name):
df = geo[geo[level] == name]
#remove outliers
df = df[(df.x <= df.x.quantile(0.999)) & (df.x >= df.x.quantile(0.001))]
df = df[(df.y <= df.y.quantile(0.999)) & (df.y >= df.y.quantile(0.001))]
return df
In [135]:
sp = filter_data('geolocation_state', 'SP')
agg_name = 'geolocation_zip_code_prefix'
sp[agg_name].describe().to_frame()
Out[135]:
In [136]:
plot_map(sp, 'Zip Codes in Sao Paulo State', ds.min(agg_name), agg_name, cmap = rainbow)
Out[136]:
In [137]:
create_map(sp, rainbow, ds.mean(agg_name), 'sp_zip_codes')
Out[137]:
Zip Codes in Large Cities¶
In [158]:
geo.head(3)
Out[158]:
In [159]:
saopaulo = filter_data('geolocation_city', 'sao paulo')
agg_name = 'geolocation_zip_code_prefix'
saopaulo[agg_name].describe().to_frame()
Out[159]:
In [163]:
plot_map(saopaulo, 'Zip Codes in Sao Paulo City', ds.min(agg_name), agg_name, cmap = rainbow)
Out[163]:
In [161]:
create_map(saopaulo, rainbow, ds.mean(agg_name), 'sao_paulo_zip_codes')
Out[161]:
Zip Codes in Small Cities¶
In [164]:
atibaia = geo[geo['geolocation_city']=='atibaia']
agg_name = 'geolocation_zip_code_prefix'
atibaia[agg_name].describe().to_frame()
Out[164]:
In [165]:
plot_map(atibaia, 'Zip Codes in Atibaia', ds.min(agg_name), agg_name, cmap = rainbow)
Out[165]:
In [166]:
create_map(atibaia, rainbow, ds.mean(agg_name), 'atibaia_zip_codes')
Out[166]:
Abrangence of zip code digits¶
In [167]:
# Zip code: 2
df = filter_data('geolocation_zip_code_prefix_1_digits', 2)
create_map(df, cm(Greys9), ds.count(), 'zip_code_2')
Out[167]:
In [168]:
# Zip code: 22
df = filter_data('geolocation_zip_code_prefix_2_digits', 22)
create_map(df, cm(Greys9), ds.count(), 'zip_code_22')
Out[168]:
In [169]:
# Zip code: 220
df = filter_data('geolocation_zip_code_prefix_3_digits', 220)
create_map(df, cm(Greys9), ds.count(), 'zip_code_220')
Out[169]:
In [170]:
# Zip code: 2201
df = filter_data('geolocation_zip_code_prefix_4_digits', 2201)
create_map(df, cm(Greys9), ds.count(), 'zip_code_2201')
Out[170]:
In [171]:
# Zip code: 22010
df = filter_data('geolocation_zip_code_prefix', 22010)
create_map(df, cm(Greys9), ds.count(), 'zip_code_22010')
Out[171]:
Where does most revenue comes from?¶
In [198]:
orders_df = pd.read_csv('olist_orders_dataset.csv')
order_items = pd.read_csv('olist_order_items_dataset.csv')
order_reviews = pd.read_csv('olist_order_reviews_dataset.csv')
customer = pd.read_csv('olist_customers_dataset.csv', dtype={'customer_zip_code_prefix': str})
# getting the first 3 digits of customer zipcode
customer['customer_zip_code_prefix_3_digits'] = customer['customer_zip_code_prefix'].str[0:3]
customer['customer_zip_code_prefix_3_digits'] = customer['customer_zip_code_prefix_3_digits'].astype(int)
brazil_geo = geo.set_index('geolocation_zip_code_prefix_3_digits').copy()
In [211]:
# merging the data
orders = orders_df.merge(order_items, on='order_id')
orders = orders.merge(customer, on='customer_id')
orders = orders.merge(order_reviews, on='order_id')
In [210]:
gp = orders.groupby('customer_zip_code_prefix_3_digits')['price'].sum().to_frame()
revenue = brazil_geo.join(gp)
revenue
Out[210]:
In [213]:
gp = orders.groupby('customer_zip_code_prefix_3_digits')['price'].sum().to_frame()
revenue = brazil_geo.join(gp)
agg_name = 'revenue'
revenue[agg_name] = revenue.price / 1000
In [214]:
plot_map(revenue, 'Orders Revenue (thousands R$)', ds.mean(agg_name), agg_name, cmap=fire)
Out[214]:
What is the Average Ticket?¶
In [218]:
gp = orders.groupby('order_id').agg({'price': 'sum', 'customer_zip_code_prefix_3_digits': 'max'})
gp = gp.groupby('customer_zip_code_prefix_3_digits')['price'].mean().to_frame()
avg_ticket = brazil_geo.join(gp)
agg_name = 'avg_ticket'
avg_ticket[agg_name] = avg_ticket.price
In [216]:
plot_map(avg_ticket, 'Orders Average Ticket (R$)', ds.mean(agg_name), agg_name, cmap=bgy)
Out[216]:
In [217]:
create_map(avg_ticket, bgy, ds.mean('avg_ticket'), 'avg_ticket_brazil')
Out[217]:
Who pays more for transportation?¶
In [220]:
gp = orders.groupby('order_id').agg({'price': 'sum', 'freight_value': 'sum', 'customer_zip_code_prefix_3_digits': 'max'})
agg_name = 'freight_ratio'
gp[agg_name] = gp.freight_value / gp.price
gp = gp.groupby('customer_zip_code_prefix_3_digits')[agg_name].mean().to_frame()
freight_ratio = brazil_geo.join(gp)
In [221]:
plot_map(freight_ratio, 'Orders Average Freight Ratio', ds.mean(agg_name), agg_name, cmap=bgy)
Out[221]:
In [222]:
create_map(freight_ratio, bgy, ds.mean('freight_ratio'), 'freight_ratio_brazil')
Out[222]:
Average Delivery Time¶
In [223]:
orders['order_delivered_customer_date'] = pd.to_datetime(orders.order_delivered_customer_date)
orders['order_estimated_delivery_date'] = pd.to_datetime(orders.order_estimated_delivery_date)
orders['order_delivered_carrier_date'] = pd.to_datetime(orders.order_delivered_carrier_date)
orders['actual_delivery_time'] = orders.order_delivered_customer_date - orders.order_delivered_carrier_date
orders['actual_delivery_time'] = orders['actual_delivery_time'].dt.days
In [224]:
gp = orders.groupby('customer_zip_code_prefix_3_digits')['actual_delivery_time'].mean().to_frame()
delivery_time = brazil_geo.join(gp)
agg_name = 'avg_delivery_time'
delivery_time[agg_name] = delivery_time['actual_delivery_time']
In [225]:
plot_map(delivery_time, 'Orders Average Delivery Time (days)', ds.mean(agg_name), agg_name, cmap=bjy)
Out[225]:
In [226]:
create_map(delivery_time, bjy, ds.mean(agg_name), 'avg_delivery_time_brazil')
Out[226]:
In [229]:
pr = filter_data('geolocation_state', 'PR').set_index('geolocation_zip_code_prefix_3_digits')
gp = orders.groupby('customer_zip_code_prefix_3_digits')['actual_delivery_time'].mean().to_frame()
pr_delivery_time = pr.join(gp)
pr_delivery_time[agg_name] = pr_delivery_time['actual_delivery_time']
In [237]:
plot_map(pr_delivery_time, 'Orders Average Delivery Time in Parana State (days)', ds.mean(agg_name), agg_name, cmap=bjy)
Out[237]:
In [238]:
create_map(pr_delivery_time, bjy, ds.mean(agg_name), 'avg_delivery_time_pr')
Out[238]:
Interesting Point About Brazilian Suburbs¶
In [281]:
riodejaneiro = filter_data('geolocation_city', 'rio de janeiro').set_index('geolocation_zip_code_prefix_3_digits')
gp = orders.groupby('customer_zip_code_prefix_3_digits')['actual_delivery_time'].mean().to_frame()
rj_delivery_time = riodejaneiro.join(gp)
rj_delivery_time[agg_name] = rj_delivery_time['actual_delivery_time']
In [282]:
plot_map(rj_delivery_time, 'Orders Average Delivery Time in Rio de Janeiro (days)', ds.mean(agg_name), agg_name, cmap=bjy)
Out[282]:
In [244]:
saopaulo = filter_data('geolocation_city', 'sao paulo').set_index('geolocation_zip_code_prefix_3_digits')
gp = orders.groupby('customer_zip_code_prefix_3_digits')['actual_delivery_time'].mean().to_frame()
sp_delivery_time = saopaulo.join(gp)
sp_delivery_time[agg_name] = sp_delivery_time['actual_delivery_time']
In [245]:
plot_map(sp_delivery_time, 'Orders Average Delivery Time in Sao Paulo (days)', ds.mean(agg_name), agg_name, cmap=bjy)
Out[245]:
In [246]:
create_map(sp_delivery_time, bjy, ds.mean(agg_name), 'sao_paulo_avg_delivery_time')
Out[246]:
In [247]:
poa = filter_data('geolocation_city', 'porto alegre').set_index('geolocation_zip_code_prefix_3_digits')
gp = orders.groupby('customer_zip_code_prefix_3_digits')['actual_delivery_time'].mean().to_frame()
poa_delivery_time = poa.join(gp)
poa_delivery_time[agg_name] = poa_delivery_time['actual_delivery_time']
In [248]:
plot_map(poa_delivery_time, 'Orders Average Delivery Time in Porto Alegre (days)', ds.mean(agg_name), agg_name, cmap=bjy)
Out[248]:
In [249]:
create_map(poa_delivery_time, bjy, ds.mean(agg_name), 'poa_avg_delivery_time')
Out[249]:
Orders Average Review Score¶
In [254]:
gp = orders.groupby('customer_zip_code_prefix_3_digits')['review_score'].mean().to_frame()
score = brazil_geo.join(gp)
agg_name = 'avg_score'
score[agg_name] = score['review_score']
In [255]:
plot_map(score, 'Orders Average Review Score', ds.mean(agg_name), agg_name, cmap=bgy)
Out[255]:
In [256]:
create_map(score, bgy, ds.mean(agg_name), 'avg_review_score_brazil')
Out[256]:
In [258]:
rj_score = riodejaneiro.join(gp)
rj_score[agg_name] = rj_score['review_score']
In [260]:
plot_map(rj_score, 'Orders Average Review Score in Rio de Janeiro', ds.mean(agg_name), agg_name, cmap=bgy)
Out[260]:
In [262]:
create_map(rj_score, bgy, ds.mean(agg_name), 'rio_de_janeiro_score')
Out[262]:
Delayed Orders¶
In [263]:
orders['is_delayed'] = orders['order_delivered_customer_date'] > orders['order_estimated_delivery_date']
gp = orders.groupby('customer_zip_code_prefix_3_digits').agg({'is_delayed': ['sum', 'count']})
agg_name = 'delayed'
gp[agg_name] = gp['is_delayed']['sum'] / gp['is_delayed']['count']
gp = gp[agg_name]
order_delay = brazil_geo.join(gp)
In [264]:
plot_map(order_delay, 'Orders Delay Percentage in Brazil', ds.mean(agg_name), agg_name, cmap=bgy)
Out[264]:
In [265]:
create_map(order_delay, bgy, ds.mean(agg_name), 'brazil_order_delay_percentage')
Out[265]:
In [266]:
rj_delay = riodejaneiro.join(gp)
In [267]:
plot_map(rj_delay, 'Orders Delay Percentage in Rio de Janeiro', ds.mean(agg_name), agg_name, cmap=bgy)
Out[267]:
In [268]:
create_map(rj_delay, bgy, ds.mean(agg_name), 'rio_de_janeiro_order_delay_percentage')
Out[268]:
Does customers from small towns buy more items on a single order¶
In [270]:
orders['count'] = 1
gp = orders.groupby(['customer_zip_code_prefix_3_digits','order_id'], as_index=False)['count'].count()
gp = gp.groupby('customer_zip_code_prefix_3_digits')['count'].mean().to_frame()
avg_qty = brazil_geo.join(gp)
agg_name = 'avg_qty'
avg_qty[agg_name] = avg_qty['count']
In [272]:
plot_map(avg_qty, 'Average Item Quantity', ds.mean(agg_name), agg_name, cmap=bgy)
Out[272]:
In [274]:
create_map(avg_qty, bgy, ds.mean(agg_name), 'avg_qty_items')
Out[274]:
