# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     name: python3
# ---

# + [markdown] id="P4f8LKqeA57v"
# # Data Upload

# + id="JXe9qYNAVTPK" executionInfo={"status": "ok", "timestamp": 1645715200702, "user_tz": -180, "elapsed": 3094, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}}
# Main Packages
import numpy as np
import pandas as pd
import matplotlib as mpl
import matplotlib.pyplot as plt
import seaborn as sns
from matplotlib.ticker import PercentFormatter
import calendar
import plotly.graph_objects as go
from plotly.subplots import make_subplots
# %matplotlib inline
import plotly.express as px

# + id="8N1C1wPFEDa6" executionInfo={"status": "ok", "timestamp": 1645715202702, "user_tz": -180, "elapsed": 269, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}}
import sqlite3
conn = sqlite3.connect(":memory:")

# + id="mLVXhn6f6Kx2" executionInfo={"status": "ok", "timestamp": 1645715215574, "user_tz": -180, "elapsed": 11580, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}}
# Customers, Sellers 
customers= pd.read_csv('/content/drive/MyDrive/Colab Notebooks/olist_customers_dataset.csv')
sellers= pd.read_csv('/content/drive/MyDrive/Colab Notebooks/olist_sellers_dataset.csv')

# Product 
products= pd.read_csv('/content/drive/MyDrive/Colab Notebooks/olist_products_dataset.csv')
products_name = pd.read_csv('/content/drive/MyDrive/Colab Notebooks/product_category_name_translation.csv')

# Order 
orders= pd.read_csv('/content/drive/MyDrive/Colab Notebooks/olist_orders_dataset.csv')
orders_item= pd.read_csv('/content/drive/MyDrive/Colab Notebooks/olist_order_items_dataset.csv')
orders_payment= pd.read_csv('/content/drive/MyDrive/Colab Notebooks/olist_order_payments_dataset.csv')
orders_review= pd.read_csv('/content/drive/MyDrive/Colab Notebooks/olist_order_reviews_dataset.csv')

# + [markdown] id="De8C6h7S7kxG"
# # Data Preprocessing

# + [markdown] id="hbFu4Gd1AFxZ"
# ## Data Integration

# + id="TU1hjxDBAFVm" executionInfo={"status": "ok", "timestamp": 1645717100199, "user_tz": -180, "elapsed": 2849, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}}
full_data = pd.merge(orders,orders_item,left_on='order_id',right_on='order_id')
full_data = full_data.merge(orders_payment,on ='order_id')
full_data = full_data.merge(orders_review,on ='order_id')
full_data = full_data.merge(customers,on ='customer_id')
full_data = full_data.merge(sellers,on ='seller_id')
full_data = full_data.merge(products,on ='product_id')
full_data = full_data.merge(products_name,on ='product_category_name')

# + [markdown] id="ickSW7vm_TGC"
# ## Data Reduction

# + id="sTLJGPMb_ULe" executionInfo={"status": "ok", "timestamp": 1645717105704, "user_tz": -180, "elapsed": 11, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}}
full_data.drop(['product_name_lenght',	'product_description_lenght'	,'product_photos_qty'	,'product_weight_g'	,
                'product_length_cm',	'product_height_cm'	,'product_width_cm','product_category_name','review_comment_title',
                'review_comment_message','review_creation_date','review_answer_timestamp','order_delivered_carrier_date'],axis=1,inplace=True)

# + [markdown] id="gf-fmDQR_gsC"
# ## Data Cleaning - Missing Data

# + colab={"base_uri": "https://localhost:8080/"} id="tHpJ-9UZ6RAo" executionInfo={"status": "ok", "timestamp": 1645362895186, "user_tz": -180, "elapsed": 997, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="9411a25e-9900-43cd-b88f-ba4f33e14ffc"
for feature in full_data.columns:
  if full_data[feature].isnull().sum() >=1:
    print(feature,  '---> total nulls are',(full_data[feature].isnull().sum()))

# + id="KCzoJXVq6aI7" executionInfo={"status": "ok", "timestamp": 1645717110082, "user_tz": -180, "elapsed": 2, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}}
#Fill the nulls --> order_approved_at
full_data["order_approved_at"].fillna(full_data["order_purchase_timestamp"], inplace=True)
#Fill the nulls --> order_delivered_customer_date
full_data["order_delivered_customer_date"].fillna(full_data["order_estimated_delivery_date"], inplace=True)

# + colab={"base_uri": "https://localhost:8080/"} id="69ZggpVB6cbV" executionInfo={"status": "ok", "timestamp": 1645362904266, "user_tz": -180, "elapsed": 1674, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="9c30a56e-c0e0-4bc9-c466-b798a5117330"
full_data.info()

# + [markdown] id="elHins77ALxS"
# ## Data Transformation and Feature Extraction

# + id="7nKexHGPYCR3" executionInfo={"status": "ok", "timestamp": 1645717124951, "user_tz": -180, "elapsed": 11125, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}}
#As the type of the purchase date is object, the type datetime64[ns] is converted first.
#And time, day, month, month-year and year information is received
full_data['order_purchase_timestamp'] = full_data['order_purchase_timestamp'].apply(pd.to_datetime)
full_data['purchase_time'] = full_data['order_purchase_timestamp'].dt.hour
full_data['purchase_MonthY'] = full_data['order_purchase_timestamp'].dt.to_period('M')
full_data['purchase_year'] = full_data['order_purchase_timestamp'].dt.year
full_data['purchase_month'] =  pd.Series(pd.Categorical(full_data['order_purchase_timestamp'].dt.month_name(), categories=list(calendar.month_name)))
full_data['purchase_day'] = pd.Series(pd.Categorical(full_data['order_purchase_timestamp'].dt.day_name(), categories=list(calendar.day_name)))

# + id="TMN-sTARjWvs" executionInfo={"status": "ok", "timestamp": 1645717127697, "user_tz": -180, "elapsed": 618, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}}
# Date information formatted as %Y-%m-%d

Dates = ['order_purchase_timestamp',
       'order_approved_at', 'order_delivered_customer_date',
      'order_estimated_delivery_date','shipping_limit_date'
       ,'order_delivered_customer_date']
for columns in Dates:
  full_data[columns] = pd.to_datetime(full_data[columns], format='%Y-%m-%d').dt.date

# + id="dSNR1KXbislR" executionInfo={"status": "ok", "timestamp": 1645717130246, "user_tz": -180, "elapsed": 663, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}}
#Estimated Delivery Date - Actual Delivery Date = Delta (Difference) on Delivery Date
full_data['shipping_time_delta'] = full_data['order_estimated_delivery_date'] - full_data['order_delivered_customer_date']

#Actual Delivery Date - Purchase Date = Actual Delivery Time
full_data['shipping_duration'] = full_data['order_delivered_customer_date'] - full_data['order_purchase_timestamp']

#Estimated Delivery Date - Purchased Date = Estimated Delivery Time
full_data['estimated_duration'] = full_data['order_estimated_delivery_date'] - full_data['order_purchase_timestamp']

# + colab={"base_uri": "https://localhost:8080/"} id="CaJU5ph66-ft" executionInfo={"status": "ok", "timestamp": 1645717137567, "user_tz": -180, "elapsed": 5, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="68dd3461-172a-41c9-8ed7-b410c4000149"
full_data.info()

# + [markdown] id="hl4xIpu-AR9t"
# ## Data Overview
#

# + colab={"base_uri": "https://localhost:8080/"} id="sIpE5g6M7MkD" executionInfo={"status": "ok", "timestamp": 1645715363034, "user_tz": -180, "elapsed": 4, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="3b74a468-347e-44c4-be97-223d6ef23a9a"
totalOrders = full_data.order_id.nunique()

print('Unique customer cities:',full_data.customer_city.nunique())
print('Unique customer states:',full_data.customer_state.nunique())
print('Unique seller cities:',full_data.seller_city.nunique())
print('Unique seller states:',full_data.seller_state.nunique())
print('Average price:',full_data.price.sum() / totalOrders)
print('Average qnt of products by order:',full_data.order_item_id.sum() / totalOrders)
print('Average freight price:',full_data.freight_value.sum() / totalOrders)
print('Total revenue for the period was:',full_data.price.sum())
print('Number of unique customers:',full_data.customer_unique_id.nunique())
print('Total order quantity:', totalOrders)
print('Average number of product by order:',full_data.freight_value.sum() / totalOrders)

# + colab={"base_uri": "https://localhost:8080/", "height": 522} id="GeaGN36g7QkL" executionInfo={"status": "ok", "timestamp": 1645717142257, "user_tz": -180, "elapsed": 7, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="a1e75330-f88a-4c55-b1cf-c070933e993d"
full_data

# + [markdown] id="OrkFQPym67pU"
# # Product Analysis

# + colab={"base_uri": "https://localhost:8080/"} id="4b7v7qEakmy_" executionInfo={"status": "ok", "timestamp": 1645451968606, "user_tz": -180, "elapsed": 264, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="49d75dfb-fed8-40b2-f659-0a157005be29"
#Product_id info was too long and complex, so last eight digits were checked for uniqueness
len(full_data['product_id'].unique()) == len(full_data['product_id'].str[-8:].unique())

# + id="Eu6RPYPwkoxP"
#Grafiklerde kullanmak amacıyla id kısaltıldı
full_data['product_id_shorten']=full_data['product_id'].str[-8:]

# + colab={"base_uri": "https://localhost:8080/"} id="8nAUVkKYksEd" executionInfo={"status": "ok", "timestamp": 1645452015246, "user_tz": -180, "elapsed": 305, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="43ce9549-f4a9-43d1-e295-256f90436f13"
#Id information has been shortened for use in graphics
len(full_data['customer_unique_id'].unique()) == len(full_data['customer_unique_id'].str[-8:].unique())

# + id="xnQ5Xl9jkuUL"
#The same operation was done for the customer_id information.
full_data['customer_unique_id_shorten']=full_data['customer_unique_id'].str[-8:]

# + colab={"base_uri": "https://localhost:8080/", "height": 415} id="nvnqx5kLEeVY" executionInfo={"status": "ok", "timestamp": 1645452351027, "user_tz": -180, "elapsed": 556, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="7ad9b036-ea05-4390-f20d-0a93f10539da"
#Bar graph of the general review score of the products

plt.figure(figsize=(10,6))
sns.countplot(x='review_score', data=full_data, palette='YlGnBu')
plt.title("Review Scores", fontsize=15, weight='bold',pad = 10.0)
plt.xlabel('Score', fontsize = 15)
plt.ylabel('Count', fontsize = 15)
plt.show()

# + id="noigqyTREi0c" executionInfo={"status": "ok", "timestamp": 1645716781045, "user_tz": -180, "elapsed": 5781, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}}
#The first 30 columns of the data are taken to be able to write #SQL queries.
data = full_data.iloc[:,:27]
data.to_sql("data",conn, if_exists="replace")

# + colab={"base_uri": "https://localhost:8080/", "height": 268} id="L_mpO5fThbIp" executionInfo={"status": "ok", "timestamp": 1645715609869, "user_tz": -180, "elapsed": 502, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="71e4c05f-4436-4296-86a8-19989d6893e2"
#Average scores analyzed according to order status information

order_status = pd.read_sql(""" SELECT order_status, COUNT(*) AS count, AVG(review_score) AS AVG_Score  from data 
                    GROUP BY order_status
                    ORDER BY count DESC
                 """ , conn )
order_status

#It is seen that the number of submissions is quite high and has a good average score.

# + [markdown] id="T6-c6Czu7ADE"
# ## Top 10 Products

# + colab={"base_uri": "https://localhost:8080/", "height": 363} id="wToswI21nJZn" executionInfo={"status": "ok", "timestamp": 1645453741560, "user_tz": -180, "elapsed": 14, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="9162f571-88df-4802-8b80-9dc10d31b01b"
#It is assumed that each row in the dataset is separate items of the order.
#For this reason, it is grouped according to product_id and the rows are counted.

top_ten_products = pd.read_sql(""" select product_id, Count(*) AS num , AVG(review_score) AS AVG_review,product_category_name_english  AS Category_nam from data
                                GROUP BY product_id
                                ORDER BY num DESC LIMIT 10
                                """,conn)
top_ten_products

#The ten best-selling products are in 6 different categories.

# + colab={"base_uri": "https://localhost:8080/"} id="vhFjqai7r_zV" executionInfo={"status": "ok", "timestamp": 1645453784439, "user_tz": -180, "elapsed": 4, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="9957025b-5c66-4b20-a543-0e84b7eedf8f"
top_ten_products['AVG_review'].mean()
#The scores of the products with high sales are also at a good level.

# + colab={"base_uri": "https://localhost:8080/", "height": 410} id="Ogma_13l7VCF" executionInfo={"status": "ok", "timestamp": 1645366489213, "user_tz": -180, "elapsed": 1040, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="a4436400-0d62-40e8-c18b-df150e0e3555"
#Plotting Top 10 Products
plt.figure(figsize=(10,6))
sns.countplot(x='product_id_shorten', data=full_data, palette='cool',
             order=full_data['product_id_shorten'].value_counts()[:10]\
             .sort_values().index).set_title("Top 10 Products", fontsize=15,
                                             weight='bold')
plt.xlabel("Product ID", fontsize = 14)
plt.ylabel("Sales Amount", fontsize = 14)
plt.show()

# + colab={"base_uri": "https://localhost:8080/", "height": 415} id="nrcchj2p7WKt" executionInfo={"status": "ok", "timestamp": 1645454168926, "user_tz": -180, "elapsed": 862, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="5164e32e-9ee3-4123-b1d8-1ca19322bb6e"
#Top 10 Product And Their Categories

plt.figure(figsize=(10,6))
sns.set_style("whitegrid")
group_product= full_data.groupby(['product_id_shorten','product_category_name_english',])['product_id_shorten']\
                                        .count().sort_values(ascending=False).head(10)
group_product.plot(kind='barh',color='dodgerblue')
plt.title("Top 10 Products And Their Categories", fontsize=15, weight='bold',pad = 10.0)
plt.xlabel("Sales Amount", fontsize = 15)
plt.ylabel("Product ID - Product Category", fontsize = 15)
plt.show()

# + [markdown] id="Wwc10Kk77JDT"
# ## Top 10 Categories

# + colab={"base_uri": "https://localhost:8080/", "height": 363} id="CA28sNCFsKIF" executionInfo={"status": "ok", "timestamp": 1645455039962, "user_tz": -180, "elapsed": 284, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="73626383-0cc0-4885-b4f1-d6203cc4e7f8"
top_ten_categories = pd.read_sql(""" SELECT product_category_name_english AS Category_Name, COUNT(*) AS num, SUM(price) AS Total_Price,
                                    AVG(review_score) AS AVG_Score , COUNT(DISTINCT(seller_id)) AS Num_of_Seller FROM data
                                    GROUP BY product_category_name_english
                                    ORDER BY num DESC limit 10
                                """ , conn )

top_ten_categories
#Although the most sold product is in the furniture_decor category, the most sold category is the bed_bath_table category.
#The two categories with the highest total price are health_beauty and watches_gifts.
#Bed_bath_table has more sales and almost one of the highest turnovers, although the number of sellers is lower.
#Products in this category can be kept in their own warehouses by the e-commerce company.

# + colab={"base_uri": "https://localhost:8080/"} id="RpFtFv_Qsv4F" executionInfo={"status": "ok", "timestamp": 1645453983782, "user_tz": -180, "elapsed": 2438, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="6708a7f4-c911-4cc6-e090-6c3c8e319d18"
#Average review score of the top ten categories
top_ten_categories['AVG_Score'].mean()

# + colab={"base_uri": "https://localhost:8080/", "height": 414} id="TS-OVmQfgCC5" executionInfo={"status": "ok", "timestamp": 1645534566207, "user_tz": -180, "elapsed": 852, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="43f962e8-9c2b-429f-8102-92abc39e6672"
#Plotting Top 10 Categories

group_category= full_data.groupby(by='product_category_name_english')[['order_id']].agg(['count']).sort_values(by=('order_id','count'),ascending=False).head(10)
group_category.plot(kind='barh',color='mediumpurple',figsize=(10,6))
plt.title("Top 10 Categories", fontsize=15, weight='bold',pad = 10.0)
plt.xlabel('Number of customers', fontsize = 15)
plt.ylabel('Product Categories', fontsize = 15)
plt.show()

# + colab={"base_uri": "https://localhost:8080/", "height": 363} id="Ijn1vW_PcNaP" executionInfo={"status": "ok", "timestamp": 1645716075746, "user_tz": -180, "elapsed": 432, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="d1e8290a-e808-4251-e082-a2efb6beaffd"
#Let's look at category 2 with the highest turnover because the number of sellers is low. We can store products with low number of vendors ourselves.

watches_gifts =  pd.read_sql(""" SELECT customer_city AS Customer_City, COUNT(*) AS Num, SUM(price) AS Total_Price,
                                       AVG(review_score) AS AVG_Score , COUNT(DISTINCT(seller_id)) AS Num_of_Seller,
                                       MIN(DATE(order_purchase_timestamp)) AS First_order,
                                       MAX(DATE(order_purchase_timestamp)) AS Last_order ,
                                       julianday('2018-09-01 00:00:00.000')-julianday(MAX(DATE(order_purchase_timestamp))) AS num_of_days_from_last_order FROM data
                                       WHERE product_category_name_english = 'watches_gifts'
                                       GROUP BY customer_city
                                       ORDER BY num DESC  limit 10
                                            """ , conn )
watches_gifts
#Here the satisfaction rate of the city of Belo Horizonte is at a good level with 4.3.
#Let's examine this city and have an idea about its sellers.

# + colab={"base_uri": "https://localhost:8080/", "height": 363} id="0WkeWLfjdV3v" executionInfo={"status": "ok", "timestamp": 1645716124626, "user_tz": -180, "elapsed": 372, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="bcb1ad81-988b-4035-b1aa-d7f45e649129"
#Watches-gifs sellers in Belo Horizonte

df = pd.read_sql("""          SELECT seller_id, COUNT(order_id)AS Num, SUM(price) AS Total_Price,
                              AVG(review_score) AS AVG_Score,
                              MIN(DATE(order_purchase_timestamp)) AS First_order,
                              MAX(DATE(order_purchase_timestamp)) AS Last_order ,
                              julianday('2018-09-01 00:00:00.000')-julianday(MAX(DATE(order_purchase_timestamp))) AS num_of_days_from_last_order FROM data
                              WHERE product_category_name_english = 'watches_gifts' AND customer_city = 'belo horizonte'
                              GROUP BY seller_id
                              ORDER BY num DESC LIMIT 10
                                            """ , conn )
df
#Although the sellers in the 3rd and 4th index have high scores, they have not made a sale for two months. Sales of these vendors can be encouraged.

# + colab={"base_uri": "https://localhost:8080/", "height": 363} id="Kn0UzeSGf6QH" executionInfo={"status": "ok", "timestamp": 1645716309109, "user_tz": -180, "elapsed": 733, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="b1438140-832d-424f-ac24-290e20391fbe"
#The ten categories with the least sales

last_ten_categories =  pd.read_sql(""" SELECT product_category_name_english AS Category_Name, COUNT(*) AS Num, SUM(price) AS Total_Price,
                                       AVG(review_score) AS AVG_Score , COUNT(DISTINCT(seller_id)) AS Num_of_Seller,
                                       MIN(DATE(order_purchase_timestamp)) AS First_order,
                                       MAX(DATE(order_purchase_timestamp)) AS Last_order ,
                                       julianday('2018-09-01 00:00:00.000')-julianday(MAX(DATE(order_purchase_timestamp))) AS num_of_days_from_last_order FROM data
                                       GROUP BY product_category_name_english
                                       ORDER BY num  limit 10
                                            """ , conn )

last_ten_categories
#There has been no shopping in the Security category for a year and the score is low.
#Although the music category has 19 sellers and a high score, its sales are low.
#High-score categories can be examined and studies can be carried out on those who may have a demand.

# + colab={"base_uri": "https://localhost:8080/", "height": 363} id="WHR-1RIsjlyE" executionInfo={"status": "ok", "timestamp": 1645716392979, "user_tz": -180, "elapsed": 707, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="00f60587-d2b9-4dc0-f94a-d8f1a3229b28"
#Top 10 categories with lowest scores
last_ten_score_categ= pd.read_sql(""" SELECT product_category_name_english AS Category_name ,COUNT(*) AS num, AVG(review_score) AS AVG_Score,
                                      SUM(price) AS Total_Price, COUNT(DISTINCT(seller_id)) AS Num_of_Seller ,
                                      MIN(DATE(order_purchase_timestamp)) AS First_order,
                                      MAX(DATE(order_purchase_timestamp)) AS Last_order ,
                                      julianday('2018-09-01 00:00:00.000')-julianday(MAX(DATE(order_purchase_timestamp))) AS num_of_days_from_last_order FROM data
                                      GROUP BY product_category_name_english
                                      ORDER BY AVG(review_score)  limit 10
                                          """ , conn )

last_ten_score_categ
#Although the sales are high in the office_furniture category, its score is 3.5.

# + colab={"base_uri": "https://localhost:8080/", "height": 1000} id="gnGeSFC2x1hv" executionInfo={"status": "ok", "timestamp": 1645716462323, "user_tz": -180, "elapsed": 287, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="d28f6aa3-e4b8-40f3-9250-3e462986f07e"
#Let's take a closer look at the office furniture category.

furniture_office = pd.read_sql(""" Select seller_id, COUNT(*) AS Num, AVG(review_score) AS AVG_Score, 
                                  MIN(DATE(order_purchase_timestamp)) AS First_order,
                                  MAX(DATE(order_purchase_timestamp)) AS Last_order , 
                                  julianday('2018-09-01 00:00:00.000')-julianday(MAX(DATE(order_purchase_timestamp))) AS num_of_days_from_last_order from data
                                  WHERE product_category_name_english = 'office_furniture'
                                  GROUP BY seller_id
                                  ORDER BY Num DESC
                                """,conn)
furniture_office

#It is seen that the seller's scores in the 3rd and 6th indexes are low.
#On the other hand, it is striking that the seller in the 2nd index has not made sales for more than 1 year and has a score of 3.5, which is not bad.

# + colab={"base_uri": "https://localhost:8080/", "height": 425} id="S6Z5swjI0mUb" executionInfo={"status": "ok", "timestamp": 1645716522893, "user_tz": -180, "elapsed": 463, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="5233faf7-a1b8-4e2d-bc14-e3efed494c18"
#Top 10 selling categories and their percentage in total orders
group_category= full_data.groupby(by='product_category_name_english')[['order_id']].agg(['count']).sort_values(by=('order_id','count'),ascending=False)
group_category['Percent share of customers'] = group_category.apply(lambda x: (x/(x.sum()))*100)
group_category.head(10)

# + colab={"base_uri": "https://localhost:8080/", "height": 542} id="cI_9vhbNP86w" executionInfo={"status": "ok", "timestamp": 1645456351415, "user_tz": -180, "elapsed": 448, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="8b0c3580-d18f-4695-fc54-91d2e6f438ff"
fig = px.pie(values=group_category['Percent share of customers'], names=group_category.index,title= 'Percentages of Product Categories')
fig.update_traces(textposition='inside')
fig.update_layout(uniformtext_minsize=15, uniformtext_mode='hide')
fig.show()
#The top 10 categories account for almost 65% of all orders.

# + [markdown] id="LQY6doiE59BI"
# # Time Series Analysis

# + [markdown] id="6SJC-D1K6E3I"
# ## Monthly Sales Analysis

# + colab={"base_uri": "https://localhost:8080/", "height": 436} id="rxaZOFVHpiko" executionInfo={"status": "ok", "timestamp": 1645716801208, "user_tz": -180, "elapsed": 2625, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="43c5e933-3fb2-4ddd-cc8e-74acf065626d"
#Average sales by month

month = full_data.groupby('purchase_month').size().sort_values()
fig=plt.figure(figsize=(10,6))
sns.set_style("darkgrid")
sns.barplot(y=month.index, x=month.values)
plt.title('Average Monthly Purchase Amount',fontsize=20,pad = 10.0)
plt.xlabel('Amount of Purchase', fontsize = 15)
plt.ylabel('Month', fontsize = 15)

#This image may mislead us, as we have the data for the whole of 2017 and about half of 2018.

# + [markdown] id="C283N-iY6KbM"
# ## Year-Monthly Sales Analysis

# + colab={"base_uri": "https://localhost:8080/", "height": 436} id="OEU9JliZ72Pz" executionInfo={"status": "ok", "timestamp": 1645716837647, "user_tz": -180, "elapsed": 1137, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="67978c33-9cc6-4df3-edd4-0d7e25a72610"
#Let's take a look at all the months we have as Month-Year:

Month_Year = full_data.groupby('purchase_MonthY').size().sort_index()
fig=plt.figure(figsize=(10,6))
sns.barplot(y=Month_Year.index, x=Month_Year.values)
plt.title('Year-Monthly Purchase',fontsize=20,pad = 10.0)
plt.xlabel('Amount of Purchase', fontsize = 15)
plt.ylabel('Year-Month', fontsize = 15)

#It is seen that there is a big sale in November due to Black Friday.

# + [markdown] id="pzYTXJrz6P76"
# ## Daily Sales Analysis

# + colab={"base_uri": "https://localhost:8080/", "height": 436} id="vyqaJTJt74p2" executionInfo={"status": "ok", "timestamp": 1645716873681, "user_tz": -180, "elapsed": 15, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="11fadb83-4d24-4b71-eeb0-14dfc39c4a28"
#Sales by day

day = full_data.groupby('purchase_day').size().sort_values()
fig=plt.figure(figsize=(10,6))
sns.barplot(y=day.index, x=day.values)
plt.title('Average Daily Purchase Amount',fontsize=20,pad = 10.0)
plt.xlabel('Amount of Purchase', fontsize = 15)
plt.ylabel('Day', fontsize = 15)

#It can be said that more shopping is done on weekdays.

# + [markdown] id="LpPvEJ786R34"
# ## Hourly Sales Analysis

# + colab={"base_uri": "https://localhost:8080/", "height": 440} id="ej8GtXcz78zV" executionInfo={"status": "ok", "timestamp": 1645717153307, "user_tz": -180, "elapsed": 912, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="955da792-1795-4090-b7f9-a38e7b20dcea"
#Sales by the hour

full_data.groupby('purchase_time').size().sort_index().plot(kind='bar',figsize=(15,6),color='lightseagreen')
plt.title('Average Hourly Purchase Amount',fontsize=20,pad = 10.0)
plt.xlabel('Amount of Purchase', fontsize = 15)
plt.ylabel('Purchase Amount', fontsize = 15)

#It is seen that sales are made between 10 a.m. and 10 p.m.

# + [markdown] id="mCuxUpuV5FT8"
#
# # Customer Analysis

# + colab={"base_uri": "https://localhost:8080/", "height": 404} id="sjuQ0OI3qVzY" executionInfo={"status": "ok", "timestamp": 1645436562797, "user_tz": -180, "elapsed": 1132, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="08f73d6f-5acc-4390-bb6c-0fae91d5b38c"
#If we look at the cumulative number of customers:

customer_cumulative = full_data.groupby('customer_id',as_index=False).agg({'order_purchase_timestamp': 'min'})
customer_cumulative.groupby('order_purchase_timestamp').count().cumsum().plot(figsize=(15,6),color='blue')
plt.title('Customers Cumulative',fontsize=20,pad = 10.0)
plt.xlabel('Timeline', fontsize = 14)
plt.ylabel('Customer count cumulative', fontsize = 14);

#As expected, an increasing graph emerges.

# + id="Sl8ULoh-qb1B" executionInfo={"status": "ok", "timestamp": 1645717327487, "user_tz": -180, "elapsed": 963, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} colab={"base_uri": "https://localhost:8080/", "height": 423} outputId="43fb3a2b-d2da-4c0b-c6f8-66c33fa2ce66"
#Let's take a look at the last time customers shopped.

customer_orders = pd.read_sql(""" SELECT customer_unique_id AS Customer_ID, COUNT(order_id) AS num_of_order, SUM(price) AS price,
                                  AVG(review_score) AS AVG_Score, DATE(MAX(order_purchase_timestamp)) AS last_order ,
                                  julianday('2018-09-01 12:08:15.310')-julianday(DATE(MAX(order_purchase_timestamp))) AS not_order_for 
                                  from data 
                                  GROUP BY customer_unique_id
                                  ORDER BY last_order
                              """ , conn )
#Customers who have not shopped in the last 100 days
inactive_customer = customer_orders[customer_orders['not_order_for'] > 100]

#Customers who shopped in the last 100 days
active_customer = customer_orders[customer_orders['not_order_for'] < 100]
inactive_customer

# + colab={"base_uri": "https://localhost:8080/", "height": 468} id="9wezwn1hqgMm" executionInfo={"status": "ok", "timestamp": 1645717335137, "user_tz": -180, "elapsed": 1848, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="a009f7b9-66be-420d-a0e5-4b802f2046b1"
#Inactive Customer Graph
inactive_graph = pd.concat([inactive_customer['num_of_order'] , inactive_customer['not_order_for']],axis=1)
inactive_graph.plot(figsize=(12,7))
plt.title("Inactive Customers", fontsize=15, weight='bold',pad = 10.0)
plt.xlabel('Number of Customer', fontsize = 15)
plt.ylabel('Number of Days Without Purchase', fontsize = 15)
plt.show()
#The orange line represents how many days each customer has not shopped. The blue line shows the total number of purchases for each customer.

# + colab={"base_uri": "https://localhost:8080/"} id="7Ihcn-4jt_qI" executionInfo={"status": "ok", "timestamp": 1645717470183, "user_tz": -180, "elapsed": 2192, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="05874a56-2a2e-4a34-ec3f-818003c087f8"
total_customers = inactive_customer['Customer_ID'].count() + active_customer['Customer_ID'].count()
total_customers

# + colab={"base_uri": "https://localhost:8080/"} id="UqNveJamqpr6" executionInfo={"status": "ok", "timestamp": 1645717482192, "user_tz": -180, "elapsed": 307, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="d9ec672c-4bc6-4023-8859-e99376dd8fa4"
inactive_customer['Customer_ID'].count() / total_customers
#79 percent of our customers are inactive

# + colab={"base_uri": "https://localhost:8080/"} id="NET7_B68vEBZ" executionInfo={"status": "ok", "timestamp": 1645717523904, "user_tz": -180, "elapsed": 373, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="144e518c-a03f-4357-ba34-671bb9832493"
active_customer['Customer_ID'].count() / total_customers
#About 20 percent of our customers are active

# + colab={"base_uri": "https://localhost:8080/"} id="_pvl4C4guM2K" executionInfo={"status": "ok", "timestamp": 1645717554880, "user_tz": -180, "elapsed": 311, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="d4f36d44-930e-4792-c66a-ac325e9e5db5"
total_price = inactive_customer['price'].sum() + active_customer['price'].sum()
total_price

# + colab={"base_uri": "https://localhost:8080/"} id="cpb5ASNUt6in" executionInfo={"status": "ok", "timestamp": 1645717556613, "user_tz": -180, "elapsed": 305, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="e2bbc9d0-0bea-4161-d220-814792c6d378"
inactive_customer['price'].sum() / total_price
#Inactive customers account for 79 percent of our earnings.

# + colab={"base_uri": "https://localhost:8080/"} id="MMYmPy8KXPFq" executionInfo={"status": "ok", "timestamp": 1645717557648, "user_tz": -180, "elapsed": 4, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="fffd8aaf-ca05-4058-ad71-01f2fc910429"
active_customer['price'].sum() / total_price

# + colab={"base_uri": "https://localhost:8080/"} id="lwDL9gLua8N7" executionInfo={"status": "ok", "timestamp": 1645717736275, "user_tz": -180, "elapsed": 3, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="6508bd4b-a0c5-406f-808c-4d79f81efb9c"
active_customer[active_customer['AVG_Score']>4]['AVG_Score'].count() / active_customer['AVG_Score'].count()
#approximately 63 percent of our active customers are satisfied with their purchases

# + colab={"base_uri": "https://localhost:8080/"} id="R5a1Bplnq-RH" executionInfo={"status": "ok", "timestamp": 1645717686925, "user_tz": -180, "elapsed": 287, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="89cc27a3-d3fe-4bc1-c333-57f66fa660b2"
inactive_customer[inactive_customer['AVG_Score']>4]['AVG_Score'].count() / inactive_customer['AVG_Score'].count()
#our inactive customers are slightly less satisfied

# + colab={"base_uri": "https://localhost:8080/"} id="Q_htWFSCXraw" executionInfo={"status": "ok", "timestamp": 1645717707587, "user_tz": -180, "elapsed": 282, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="12a2f0c7-2eff-47e4-8fcf-631890a3390b"
inactive_customer[inactive_customer['AVG_Score']<3]['AVG_Score'].count() / inactive_customer['AVG_Score'].count()
#15 percent of inactive customers have a review score of less than 3

# + colab={"base_uri": "https://localhost:8080/"} id="RQgnfqYRXv2X" executionInfo={"status": "ok", "timestamp": 1645717731782, "user_tz": -180, "elapsed": 3, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="df0d3e9a-45cb-4b29-be7f-ac1a6ad24b1e"
active_customer[active_customer['AVG_Score']<3]['AVG_Score'].count() / active_customer['AVG_Score'].count()

# + [markdown] id="F_9XgLpI5QCM"
#
# ## City ​​Based Customer Analysis

# + colab={"base_uri": "https://localhost:8080/"} id="6zelBJEYnPhb" executionInfo={"status": "ok", "timestamp": 1645717827689, "user_tz": -180, "elapsed": 308, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="6cb7094c-eebc-4a37-a7ed-b0622d446e40"
#Total number of customers

Number_of_customers = full_data.customer_unique_id.nunique()
Number_of_customers

# + colab={"base_uri": "https://localhost:8080/", "height": 582} id="jHJmobUwmff-" executionInfo={"status": "ok", "timestamp": 1645717831156, "user_tz": -180, "elapsed": 806, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="b122caf0-31b7-4f04-9ae3-bdc64153d1d9"
#Cities and percentages of customers

customer_city = full_data.groupby(by='customer_city')[['customer_unique_id']].agg(['count']).sort_values(by=('customer_unique_id','count'),ascending=False)[0:15]
customer_city['Percent share of customers'] = customer_city.apply(lambda x: (x/(Number_of_customers))*100)
customer_city

# + colab={"base_uri": "https://localhost:8080/", "height": 582} id="si_HfxrDvxzU" executionInfo={"status": "ok", "timestamp": 1645717857476, "user_tz": -180, "elapsed": 7, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="a66c4ceb-ea5a-4145-e8d0-97c691fea639"
#Total earnings and average earnings by cities
full_data.groupby(by='customer_city')[['payment_value']].agg(['sum','mean']).sort_values(by=('payment_value','sum'),ascending=False)[0:15]

# + colab={"base_uri": "https://localhost:8080/", "height": 526} id="1BeYUA-RVPJj" executionInfo={"status": "ok", "timestamp": 1645301473555, "user_tz": -180, "elapsed": 1342, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="0a282375-cd48-4849-9f36-0375f5e40cf0"
#Graph of percentages of cities where customers are located

sns.set_style("whitegrid")
plt.figure(figsize=(15,6))
df = full_data.groupby(by='customer_city')[['customer_unique_id']].agg(['count']).sort_values(by=('customer_unique_id','count'),ascending=False)[:20]
df['Percent share of customers'] = df.apply(lambda x: (x/(Number_of_customers))*100)
df['Percent share of customers'].plot(kind='bar',color='dodgerblue')
plt.title('Customer Percentages of Cities',fontsize=20,pad = 10.0)
plt.xlabel('Customer Cities', fontsize = 15)
plt.ylabel('Percent share of customers', fontsize = 15)
plt.legend()
plt.show()

# + colab={"base_uri": "https://localhost:8080/", "height": 616} id="a0sdICdRsE0A" executionInfo={"status": "ok", "timestamp": 1645304287244, "user_tz": -180, "elapsed": 988, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="253f30b6-5c95-4e8e-d679-a376fc50f9a0"
# Adding cummulative percentage for Pareto Chart
df = full_data.groupby(by='customer_city')[['customer_unique_id']].agg(['count']).sort_values(by=('customer_unique_id','count'),ascending=False)[:20]
df['Percent share of customers'] = df.apply(lambda x: (x/(Number_of_customers))*100)
df["cumpercentage"] = df["Percent share of customers"].cumsum()/df["Percent share of customers"].sum()*100

#Plotting Pareto chart
fig, ax = plt.subplots(figsize=(15,8))
ax.bar(df.index, df[('customer_unique_id','count')], color="dodgerblue",width=0.8)
ax2 = ax.twinx()
ax2.plot(df.index, df["cumpercentage"], color="orangered", marker="D", ms=7)
ax2.yaxis.set_major_formatter(PercentFormatter())
ax.tick_params(axis="x",rotation=90)
ax.tick_params(axis="y", colors="C0")
ax2.tick_params(axis="y", colors="orangered")
plt.title('Percentages and Sum of Customer Cities',fontsize=20,pad = 10.0)
plt.show()

# + [markdown] id="ytovqbG05WQv"
# ## State ​​Based Customer Analysis

# + colab={"base_uri": "https://localhost:8080/", "height": 582} id="nRpZGkz7nw20" executionInfo={"status": "ok", "timestamp": 1645481571719, "user_tz": -180, "elapsed": 8, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="6d6df154-dd69-4dac-ba60-56fafcc9f0d7"
#Countries and percentages of customers
customer_state = full_data.groupby(by='customer_state')[['customer_unique_id']].agg(['count']).sort_values(by=('customer_unique_id','count'),ascending=False)[0:15]
customer_state['Percent share of customers'] = customer_state.apply(lambda x: (x/(Number_of_customers))*100)
customer_state

# + colab={"base_uri": "https://localhost:8080/", "height": 582} id="7UBk5Duhv-WP" executionInfo={"status": "ok", "timestamp": 1645481602613, "user_tz": -180, "elapsed": 485, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="46adf846-54de-48d6-925f-44fe0b826ea4"
#Total and average earnings from countries
full_data.groupby(by='customer_state')[['payment_value']].agg(['sum','mean']).sort_values(by=('payment_value','sum'),ascending=False)[0:15]

# + colab={"base_uri": "https://localhost:8080/", "height": 424} id="jljdTfTPW_YT" executionInfo={"status": "ok", "timestamp": 1645301608196, "user_tz": -180, "elapsed": 915, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="406f1bab-5e30-4152-f352-83e17aa3b562"
#Graph of percentage of customers in countries
plt.figure(figsize=(15,6))
df = full_data.groupby(by='customer_state')[['customer_unique_id']].agg(['count']).sort_values(by=('customer_unique_id','count'),ascending=False)[:20]
df['Percent share of customers'] = df.apply(lambda x: (x/(Number_of_customers))*100)
df['Percent share of customers'].plot(kind='bar',color='teal')
plt.title('Customer Percentages of States',fontsize=20,pad = 10.0)
plt.xlabel('Customer States', fontsize = 15)
plt.ylabel('Percent share of customers', fontsize = 15)
plt.legend()
plt.show()

# + colab={"base_uri": "https://localhost:8080/", "height": 616} id="DmeN48ftotcw" executionInfo={"status": "ok", "timestamp": 1645304262324, "user_tz": -180, "elapsed": 1363, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="bd674779-7f9c-40fa-f123-5bb6cf6f0ddc"
# Adding cummulative percentage for Pareto Chart
df = full_data.groupby(by='customer_state')[['customer_unique_id']].agg(['count']).sort_values(by=('customer_unique_id','count'),ascending=False)
df['Percent share of customers'] = df.apply(lambda x: (x/(Number_of_customers))*100)
df["cumpercentage"] = df["Percent share of customers"].cumsum()/df["Percent share of customers"].sum()*100

#Plotting Pareto chart
fig, ax = plt.subplots(figsize=(15,10))
ax.bar(df.index, df[('customer_unique_id','count')], color="forestgreen",width=0.8)
ax2 = ax.twinx()
ax2.plot(df.index, df["cumpercentage"], color="orangered", marker="D", ms=7)
ax2.yaxis.set_major_formatter(PercentFormatter())
ax.tick_params(axis="y", colors="forestgreen")
ax2.tick_params(axis="y", colors="orangered")
plt.title('Percentages and Sum of Customer States',fontsize=20,pad = 10.0)
plt.show()

# + [markdown] id="Z0Ip-WEz5ZvG"
# ## Payment Types

# + colab={"base_uri": "https://localhost:8080/", "height": 238} id="3wnuqCAL-5hD" executionInfo={"status": "ok", "timestamp": 1645305594064, "user_tz": -180, "elapsed": 1622, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="d2c5f313-cb47-4243-804e-8d6b69d31369"
#Let's create dataframe for payment types at look at some numbers

df = full_data.groupby(by='payment_type')[['customer_unique_id']].agg(['count']).sort_values(by=('customer_unique_id','count'),ascending=False)
df['Percent customers'] = df.apply(lambda x: (x/(Number_of_customers))*100)
df['Payments'] = np.round(full_data.groupby(by='payment_type')[['payment_value']].agg(['sum']),4)
total_payment_value = df['Payments'].sum()

#Calculating percent shares
def percent_payment(x):
  z = x/total_payment_value
  return z * 100

df['Percent payment value'] = df['Payments'].apply(lambda x: percent_payment(x))
df

# + colab={"base_uri": "https://localhost:8080/", "height": 542} id="hqh2vo8c2oDP" executionInfo={"status": "ok", "timestamp": 1645305864300, "user_tz": -180, "elapsed": 282, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="8ee831e2-89a1-4a8d-c3c3-4c488e7090ba"
fig = make_subplots(rows=1, cols=2, specs=[[{'type':'domain'}, {'type':'domain'}]])
fig.add_trace(go.Pie(labels=df.index, values=df['Percent customers'], name="Percent customers"),
              1, 1)
fig.add_trace(go.Pie(labels=df.index, values=df['Percent payment value'], name="'Percent payment value'"),
              1, 2)

# Use `hole` to create a donut-like pie chart
fig.update_traces(hole=.4, hoverinfo="label+percent+name")

fig.update_layout(
    title_text="Payment Types",
    # Add annotations in the center of the donut pies.
    annotations=[dict(text='Customer', x=0.18, y=0.5, font_size=20, showarrow=False),
                 dict(text='Price', x=0.82, y=0.5, font_size=20, showarrow=False)])
fig.show()

# + colab={"base_uri": "https://localhost:8080/", "height": 363} id="2UGLdoMgw6n4" executionInfo={"status": "ok", "timestamp": 1645522195707, "user_tz": -180, "elapsed": 6, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="2fc52a9c-6f77-4beb-f46c-c6d47a5af8dc"
#Most used installment amounts
payment_seq = pd.read_sql ( """ select payment_sequential, COUNT(*) as num ,AVG(review_score) AS score from data
              GROUP BY payment_sequential
              ORDER BY num DESC LIMIT 10
""",conn)
payment_seq
#In general, there is a cash purchase, which may be due to the low price per order.
#Installments can be made by increasing the debt

# + [markdown] id="hiJOihvvX38m"
#
# # Seller Analysis

# + [markdown] id="9DvpPxAWnuti"
# ## Total Number of Sellers

# + colab={"base_uri": "https://localhost:8080/", "height": 404} id="CoWEV9HZqhfK" executionInfo={"status": "ok", "timestamp": 1645520556139, "user_tz": -180, "elapsed": 2145, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="68701d7f-ce07-4669-c7c9-8097cbe26538"
#Graph of number of sellers over time

seller_cumulative = full_data.groupby('seller_id',as_index=False).agg({'order_purchase_timestamp': 'min'})
seller_cumulative.groupby('order_purchase_timestamp').count().cumsum().plot(figsize=(15,6),color='blue')
plt.title('Sellers Cumulative',fontsize=20,pad = 10.0)
plt.xlabel('Timeline', fontsize = 14)
plt.ylabel('Seller count cumulative', fontsize = 14);

# + colab={"base_uri": "https://localhost:8080/"} id="KuEhJNsTrHh4" executionInfo={"status": "ok", "timestamp": 1645521556150, "user_tz": -180, "elapsed": 309, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="0b01abc0-15b0-415e-ecee-4f66204500af"
#Total number of sellers
Number_of_sellers = full_data.seller_id.nunique()
Number_of_sellers

# + [markdown] id="r5G0bd8nnpN0"
# ## Actice and Inactive Sellers

# + colab={"base_uri": "https://localhost:8080/", "height": 423} id="3bc4H4qgq6z1" executionInfo={"status": "ok", "timestamp": 1645520763323, "user_tz": -180, "elapsed": 285, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="c36257f9-582d-487f-fa40-b1dbee889fc0"
#Let's look at the last time sellers made a sale.

seller_orders = pd.read_sql(""" SELECT seller_id AS Seller_ID, COUNT(order_id) AS num_of_order, SUM(price) AS price,
                                  AVG(review_score) AS AVG_Score, DATE(MAX(order_purchase_timestamp)) AS last_order ,
                                  julianday('2018-07-01 12:08:15.310')-julianday(DATE(MAX(order_purchase_timestamp))) AS not_order_for 
                                  from data 
                                  GROUP BY seller_id
                                  ORDER BY last_order
                              """ , conn )
#Sellers who have not made a sale in the last 100 days
inactive_seller = seller_orders[seller_orders['not_order_for'] > 100]

#Sellers who have made a sale in the last 100 days
active_seller = seller_orders[seller_orders['not_order_for'] < 100]
inactive_seller

# + colab={"base_uri": "https://localhost:8080/", "height": 468} id="g7irpPngreBF" executionInfo={"status": "ok", "timestamp": 1645521033617, "user_tz": -180, "elapsed": 732, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="1d0a3ca5-fa0a-4cf6-cf9f-40c80cdfc9b6"
#Graph of inactive sellers and total sales

inactive_graph = pd.concat([inactive_seller['num_of_order'] , inactive_seller['not_order_for']],axis=1)
inactive_graph.plot(figsize=(12,7))
plt.title("Inactive Sellers", fontsize=15, weight='bold',pad = 10.0)
plt.xlabel('Number of Sellers', fontsize = 15)
plt.ylabel('Number of Days Without Purchase', fontsize = 15)
plt.show()

# + colab={"base_uri": "https://localhost:8080/"} id="XM0LnNA9tqSr" executionInfo={"status": "ok", "timestamp": 1645522526193, "user_tz": -180, "elapsed": 274, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="877ffc38-0aa2-45fe-abf8-bb929e5be75d"
#Inactive sellers, what percentage of total sellers
inactive_seller['Seller_ID'].count() / Number_of_sellers

# + colab={"base_uri": "https://localhost:8080/"} id="hbWFeRA9tsWr" executionInfo={"status": "ok", "timestamp": 1645522548493, "user_tz": -180, "elapsed": 275, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="26ce0ab4-dbbd-4002-8a5f-5292ff6571ff"
#Active sellers, what percentage of total sellers
active_seller['Seller_ID'].count() / Number_of_sellers

# + colab={"base_uri": "https://localhost:8080/"} id="8Gkqi_YtvRwv" executionInfo={"status": "ok", "timestamp": 1645521775136, "user_tz": -180, "elapsed": 2940, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="3909571e-c59a-4a18-b5dc-3ebe1eea7d12"
#Total Price
total_price = inactive_seller['price'].sum() + active_seller['price'].sum()
total_price

# + colab={"base_uri": "https://localhost:8080/"} id="16adNiBjvY8X" executionInfo={"status": "ok", "timestamp": 1645521790056, "user_tz": -180, "elapsed": 274, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="1f5599f7-cb15-4bd2-db47-c2eaa184acd9"
#Percentage of inactive sellers in total price
inactive_seller['price'].sum() / total_price

# + colab={"base_uri": "https://localhost:8080/"} id="PZB8UAbHveJu" executionInfo={"status": "ok", "timestamp": 1645521807874, "user_tz": -180, "elapsed": 290, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="2d91f9a1-11a0-4687-daca-cd9c11b8cf8d"
#Percentage of active sellers in total price
active_seller['price'].sum() / total_price

# + colab={"base_uri": "https://localhost:8080/"} id="85NLTkiCvslZ" executionInfo={"status": "ok", "timestamp": 1645521870825, "user_tz": -180, "elapsed": 6, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="e503a68c-61f3-4127-9742-ba0263d7db90"
#total order
total_order = inactive_seller['num_of_order'].sum() + active_seller['num_of_order'].sum()
total_order

# + colab={"base_uri": "https://localhost:8080/"} id="_wULPcuvvyF0" executionInfo={"status": "ok", "timestamp": 1645521894423, "user_tz": -180, "elapsed": 12, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="9e336299-3a3b-4e94-c763-192006429f6f"
#Percentage of inactive sellers in total sales
inactive_seller['num_of_order'].sum() / total_order

# + colab={"base_uri": "https://localhost:8080/"} id="bo6mzOtdv4Qz" executionInfo={"status": "ok", "timestamp": 1645521903523, "user_tz": -180, "elapsed": 278, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="f28e59bd-0311-44e6-bae7-ae06fde1b1a4"
#Percentage of active sellers in total sales
active_seller['num_of_order'].sum() / total_order

# + colab={"base_uri": "https://localhost:8080/"} id="rrBtHXUDv7ZE" executionInfo={"status": "ok", "timestamp": 1645522710348, "user_tz": -180, "elapsed": 1764, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="e6b646cb-d45f-41ce-a4c6-4bb338cec1a6"
#Percentage of inactive sellers with a score greater than 4
inactive_seller[inactive_seller['AVG_Score']>4]['AVG_Score'].count() / inactive_seller['AVG_Score'].count()

# + colab={"base_uri": "https://localhost:8080/"} id="3YRpTkGpwH8u" executionInfo={"status": "ok", "timestamp": 1645522751272, "user_tz": -180, "elapsed": 272, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="5f80d8df-7f40-45ee-eeb9-0e5606dd0be7"
#Percentage of active sellers with a score greater than 4
active_seller[active_seller['AVG_Score']>4]['AVG_Score'].count() / active_seller['AVG_Score'].count()

# + colab={"base_uri": "https://localhost:8080/"} id="BvxZKPLLlRzk" executionInfo={"status": "ok", "timestamp": 1645522954799, "user_tz": -180, "elapsed": 270, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="475ff45d-a4b2-4433-84ce-00235fc727f0"
seller_inf = pd.read_sql ( """ select seller_id, COUNT(*) as num ,AVG(review_score) AS score from data
              GROUP BY seller_id
              ORDER BY score DESC
""",conn)
good_sellers = seller_inf[seller_inf['num']>500]
print("Number of sellers selling more than 500 units                 :",good_sellers['seller_id'].count())
print("Average number of sales of sellers selling more than 500 units:",good_sellers['num'].mean())
print("Average score of sales of sellers selling more than 500 units :",good_sellers['score'].mean())

# + [markdown] id="zbbiTudZnjE_"
# ## States of Sellers

# + colab={"base_uri": "https://localhost:8080/", "height": 615} id="NwmnbgT-wfHq" executionInfo={"status": "ok", "timestamp": 1645522063082, "user_tz": -180, "elapsed": 967, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="6a628f86-9341-491c-e3df-189108305bc8"
#Countries where sellers are located
num_sellers = full_data.seller_id.nunique()
# Adding cummulative percentage for Pareto Chart
df = full_data.groupby(by='seller_state')[['seller_id']].agg(['count']).sort_values(by=('seller_id','count'),ascending=False)
df['Percent share of seller'] = df.apply(lambda x: (x/(num_sellers))*100)
df["cumpercentage"] = df["Percent share of seller"].cumsum()/df["Percent share of seller"].sum()*100

#Plotting Pareto chart
fig, ax = plt.subplots(figsize=(15,10))
ax.bar(df.index, df[('seller_id','count')], color="forestgreen",width=0.8)
ax2 = ax.twinx()
ax2.plot(df.index, df["cumpercentage"], color="orangered", marker="D", ms=7)
ax2.yaxis.set_major_formatter(PercentFormatter())
ax.tick_params(axis="y", colors="forestgreen")
ax2.tick_params(axis="y", colors="orangered")
plt.title('Percentages and Sum of Sellers States',fontsize=20,pad = 10.0)
plt.show()

# + [markdown] id="cjlj7IbZsTyx"
# # Delivery Analysis

# + colab={"base_uri": "https://localhost:8080/"} id="-Y0Ep1Dprh3i" executionInfo={"status": "ok", "timestamp": 1645537541602, "user_tz": -180, "elapsed": 264, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="5ab192aa-1d1f-4000-f8d7-ed8615e1ea26"
print("Shipping Time",full_data['shipping_duration'].mean())
print("Estimated Time",full_data['estimated_duration'].mean())
#It can be said that the delivery times are generally successful.

# + colab={"base_uri": "https://localhost:8080/", "height": 394} id="AaWtknKFrnNC" executionInfo={"status": "ok", "timestamp": 1645537557069, "user_tz": -180, "elapsed": 279, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="f3609119-38a0-4de3-c885-cf5c6ff75a85"
#Top 10 countries with shortest delivery time
full_data.groupby('customer_state')[['shipping_duration']].mean().sort_values(by='shipping_duration').head(10)

# + colab={"base_uri": "https://localhost:8080/"} id="WAkYxUTQrq45" executionInfo={"status": "ok", "timestamp": 1645537696190, "user_tz": -180, "elapsed": 321, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="7062d57c-6e0b-45ec-9ae1-2fdae36508b0"
#How many of my orders were delivered late?
late_delivery = full_data[full_data['order_estimated_delivery_date'] < full_data['order_delivered_customer_date']] ['order_id'].count()  
late_delivery

# + colab={"base_uri": "https://localhost:8080/"} id="bdAI9Pl1sB3v" executionInfo={"status": "ok", "timestamp": 1645537710332, "user_tz": -180, "elapsed": 399, "user": {"displayName": "<NAME>", "photoUrl": "https://lh3.googleusercontent.com/a/default-user=s64", "userId": "14093034254050366238"}} outputId="f72a607a-49a4-415a-843b-64b370a728de"
totalOrders = full_data.order_id.nunique()
late_delivery / totalOrders
#Considering the percentage, a very small amount was delivered late. Overall good delivery.

StarcoderdataJupyterScriptsDedupFiltered