P5 eda.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c1a78b82",
   "metadata": {},
   "source": [
    "# Import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc96a7f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# IMPORT\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "# balance par mois sur les credits en statut\n",
    "cust = pd.read_csv('input/archive/olist_customers_dataset.csv',\n",
    "                   delimiter=',', error_bad_lines=False, low_memory=False)\n",
    "geo = pd.read_csv('input/archive/olist_geolocation_dataset.csv',\n",
    "                  delimiter=',',\n",
    "                  error_bad_lines=False,\n",
    "                  low_memory=False)\n",
    "order_item = pd.read_csv('input/archive/olist_order_items_dataset.csv',\n",
    "                         delimiter=',',\n",
    "                         error_bad_lines=False,\n",
    "                         low_memory=False)\n",
    "order_payment = pd.read_csv('input/archive/olist_order_payments_dataset.csv',\n",
    "                            delimiter=',',\n",
    "                            error_bad_lines=False,\n",
    "                            low_memory=False)\n",
    "order_review = pd.read_csv('input/archive/olist_order_reviews_dataset.csv',\n",
    "                           delimiter=',',\n",
    "                           error_bad_lines=False,\n",
    "                           low_memory=False)\n",
    "order = pd.read_csv('input/archive/olist_orders_dataset.csv',\n",
    "                    delimiter=',',\n",
    "                    error_bad_lines=False,\n",
    "                    low_memory=False)\n",
    "product = pd.read_csv('input/archive/olist_products_dataset.csv',\n",
    "                      delimiter=',',\n",
    "                      error_bad_lines=False,\n",
    "                      low_memory=False)\n",
    "seller = pd.read_csv('input/archive/olist_sellers_dataset.csv',\n",
    "                     delimiter=',',\n",
    "                     error_bad_lines=False,\n",
    "                     low_memory=False)\n",
    "product_cat = pd.read_csv('input/archive/' +\n",
    "                          'product_category_name_translation.csv',\n",
    "                          delimiter=',',\n",
    "                          error_bad_lines=False,\n",
    "                          low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "87d4fc96",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "print('cust lignes/colonnes', cust.shape)\n",
    "print('geo lignes/colonnes', geo.shape)\n",
    "print('order_item lignes/colonnes', order_item.shape)\n",
    "print('order_payment lignes/colonnes', order_payment.shape)\n",
    "print('order_review lignes/colonnes', order_review.shape)\n",
    "print('order lignes/colonnes', order.shape)\n",
    "print('product lignes/colonnes', product.shape)\n",
    "print('seller lignes/colonnes', seller.shape)\n",
    "print('product_cat lignes/colonnes', product_cat.shape)\n",
    "\n",
    "display(order)\n",
    "display(order_item)\n",
    "display(order_payment)\n",
    "display(order_review)\n",
    "display(product)\n",
    "display(product_cat)\n",
    "display(seller)\n",
    "display(geo)\n",
    "display(cust)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dcd1e2b3",
   "metadata": {},
   "source": [
    "# Analyse produits commandés\n",
    "* jointure product/product cat / order_item / seller"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c818a27c",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# jointure produit et cat pour avoir la cat anglaise plus facile pour nous\n",
    "import datetime as dt\n",
    "product2 = pd.merge(product, product_cat, how='outer',\n",
    "                    on='product_category_name')\n",
    "# display(product2)\n",
    "\n",
    "# champs inutiles\n",
    "lis2dr = ['product_category_name',\n",
    "          'product_name_lenght', 'product_description_lenght']\n",
    "product2 = product2.drop(columns=lis2dr)\n",
    "\n",
    "\n",
    "# fusion seller/order_item/product\n",
    "product3 = order_item.set_index('seller_id').join(\n",
    "    seller.set_index('seller_id'))\n",
    "product3 = product3.reset_index().set_index(\n",
    "    'product_id').join(product2.set_index('product_id'))\n",
    "\n",
    "pd.set_option('max_columns', None)\n",
    "# display(product3)\n",
    "# display(product3.loc[product3.order_id=='e8fa22c3673b1dd17ea315021b1f0f61',:])\n",
    "pd.set_option('max_columns', 10)\n",
    "# order_item_id represente le numero de l'article dans la commande\n",
    "# ex: 1er article 2eme article...\n",
    "\n",
    "vc1 = pd.DataFrame(product3.value_counts('product_category_name_english'))\n",
    "vc1.columns = ['val']\n",
    "vc1['pct'] = vc1['val']/vc1['val'].sum()\n",
    "pd.set_option('max_rows', None)\n",
    "display(vc1)\n",
    "pd.set_option('max_rows', 10)\n",
    "\n",
    "vc2 = pd.DataFrame(product3.value_counts('seller_state'))\n",
    "vc2.columns = ['val']\n",
    "vc2['pct'] = vc2['val']/vc2['val'].sum()\n",
    "# display(vc2)\n",
    "\n",
    "vc3 = pd.DataFrame(product3.value_counts('seller_city'))\n",
    "vc3.columns = ['val']\n",
    "vc3['pct'] = vc3['val']/vc3['val'].sum()\n",
    "# display(vc3)\n",
    "\n",
    "plt.rcParams[\"figure.figsize\"] = [16, 6]\n",
    "plt.rcParams[\"figure.autolayout\"] = True\n",
    "f, axes = plt.subplots(1, 2)\n",
    "\n",
    "axes[0].pie(vc2.loc[(vc2.pct >= 0.01)]['pct'],\n",
    "            labels=vc2.loc[(vc2.pct >= 0.01)]['pct'].index,\n",
    "            normalize=False,\n",
    "            autopct='%1.1f%%',\n",
    "            shadow=True,\n",
    "            startangle=90)  # explode=explode,\n",
    "# Equal aspect ratio ensures that pie is drawn as a circle.\n",
    "axes[0].axis('equal')\n",
    "axes[0].set_title('commandes: distribution des etats des vendeurs')\n",
    "\n",
    "axes[1].pie(vc3.loc[(vc3.pct >= 0.01)]['pct'],\n",
    "            labels=vc3.loc[(vc3.pct >= 0.01)]['pct'].index,\n",
    "            normalize=False, autopct='%1.1f%%',\n",
    "            shadow=True, startangle=90)  # explode=explode,\n",
    "# Equal aspect ratio ensures that pie is drawn as a circle.\n",
    "axes[1].axis('equal')\n",
    "axes[1].set_title('commandes: distribution des villes des vendeurs')\n",
    "plt.show()\n",
    "\n",
    "\n",
    "#################################################\n",
    "# calcul des montant par type de prduit\n",
    "vc4 = product3.groupby(\n",
    "    by=\"product_category_name_english\").agg({'price': ['sum']})\n",
    "vc4.columns = vc4.columns.droplevel(0)\n",
    "vc4.columns = ['ca']\n",
    "vc4 = vc4.sort_values('ca', ascending=False)\n",
    "vc4['pct'] = vc4.ca/vc4.ca.sum()\n",
    "\n",
    "# display(vc4)\n",
    "f, axes = plt.subplots(1, 2)\n",
    "\n",
    "axes[0].pie(vc1.loc[(vc1.pct >= 0.01)]['pct'],\n",
    "            labels=vc1.loc[(vc1.pct >= 0.01)]['pct'].index,\n",
    "            normalize=False,\n",
    "            autopct='%1.1f%%',\n",
    "            shadow=True,\n",
    "            startangle=90)\n",
    "# Equal aspect ratio ensures that pie is drawn as a circle.\n",
    "axes[0].axis('equal')\n",
    "axes[0].set_title('commandes: distribution des catgeories de produits')\n",
    "\n",
    "axes[1].pie(vc4.loc[(vc4.pct >= 0.01)]['pct'],\n",
    "            labels=vc4.loc[(vc4.pct >= 0.01)]['pct'].index,\n",
    "            normalize=False,\n",
    "            autopct='%1.1f%%',\n",
    "            shadow=True,\n",
    "            startangle=90)\n",
    "# Equal aspect ratio ensures that pie is drawn as a circle.\n",
    "axes[1].axis('equal')\n",
    "axes[1].set_title('ca: distribution des catgeories de produits')\n",
    "\n",
    "#################################################\n",
    "f, axes = plt.subplots(1, 1)\n",
    "ax = sns.boxplot(x='product_category_name_english', y='price',\n",
    "                 data=product3, showmeans=True, showfliers=False)\n",
    "ax.title.set_text(\"prix moyen par categorie\")\n",
    "ax.set_xticklabels(ax.get_xticklabels(), rotation=90)\n",
    "\n",
    "plt.show\n",
    "\n",
    "#################################################\n",
    "vc4['ca_rank'] = vc4.pct.rank(method='max', ascending=False)\n",
    "# display(vc4)\n",
    "\n",
    "product4 = product3.reset_index()\n",
    "product4 = product4.set_index('product_category_name_english').join(vc4)\n",
    "# display(product4.loc[product4.pct>=0.01,:].sort_values('ca_rank',ascending=True).reset_index())\n",
    "\n",
    "f, axes = plt.subplots(1, 1)\n",
    "ax = sns.boxplot(x='product_category_name_english', y='price',\n",
    "                 data=product4.loc[product4.pct >= 0.01, :].sort_values(\n",
    "                     'ca_rank', ascending=True).reset_index(),\n",
    "                 showmeans=True,\n",
    "                 showfliers=False)\n",
    "ax.title.set_text(\n",
    "    \"prix moyen par categorie (celles qui font plus de 1% du CA total)\")\n",
    "ax.set_xticklabels(ax.get_xticklabels(), rotation=90)\n",
    "plt.show\n",
    "\n",
    "f, axes = plt.subplots(1, 1)\n",
    "ax = sns.boxplot(x='product_category_name_english',\n",
    "                 y='pct',\n",
    "                 data=product4.loc[product4.pct >= 0.01, :].sort_values(\n",
    "                     'ca_rank', ascending=True).reset_index(),\n",
    "                 showmeans=True,\n",
    "                 showfliers=False)\n",
    "ax.title.set_text(\n",
    "    \"CA moyen par categorie (celles qui font plus de 1% du CA total)\")\n",
    "ax.set_xticklabels(ax.get_xticklabels(), rotation=90)\n",
    "plt.show\n",
    "\n",
    "#################################################\n",
    "# \"datetime\"\n",
    "# attention les plot ont du mal avec les types timedelta\n",
    "# -> les changer en float ou avec total_seconds\n",
    "product4.shipping_limit_date = pd.to_datetime(\n",
    "    product4.shipping_limit_date, errors='coerce')\n",
    "product4['init_date'] = dt.datetime(2016, 1, 1, 0, 0, 0)\n",
    "\n",
    "product4['difdate'] = product4.shipping_limit_date-product4['init_date']\n",
    "# operator // pour division entiere\n",
    "product4['difdate'] = (product4['difdate'].dt.total_seconds()/3600/24)//7\n",
    "\n",
    "\n",
    "product4['year'] = product4.shipping_limit_date.dt.isocalendar().year\n",
    "product4['mois'] = product4.shipping_limit_date.dt.month\n",
    "product4['semaine'] = product4.shipping_limit_date.dt.isocalendar().week\n",
    "product4 = product4.loc[(product4.year < 2020), :]\n",
    "# display(product4.sort_values('shipping_limit_date'))\n",
    "\n",
    "histprod4 = pd.DataFrame(product4.value_counts(\n",
    "    'difdate')).sort_values('difdate')\n",
    "histprod4.columns = ['nbc']\n",
    "\n",
    "ax0 = plt.subplot(111)\n",
    "sns.barplot(x=histprod4.index, y='nbc', data=histprod4, color='blue', ax=ax0)\n",
    "ax0.set_title(\n",
    "    \"nb de commandes par semaine\" +\n",
    "    \" sur l'échantillon : semaine 1 commence le 01/01/2016\")\n",
    "plt.xticks(np.arange(0, 105, 5).tolist())\n",
    "plt.show()\n",
    "#######################################################\n",
    "histprod4_ca = product4.groupby(by=\"difdate\").agg({'price': ['sum']})\n",
    "histprod4_ca.columns = histprod4_ca.columns.droplevel(0)\n",
    "# display(histprod4_ca)\n",
    "\n",
    "ax0 = plt.subplot(111)\n",
    "sns.barplot(x=histprod4_ca.index, y='sum',\n",
    "            data=histprod4_ca, color='blue', ax=ax0)\n",
    "ax0.set_title(\n",
    "    \"CA par semaine sur l'échantillon : semaine 1 commence le 01/01/2016\")\n",
    "plt.xticks(np.arange(0, 105, 5).tolist())\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45b4acaa",
   "metadata": {},
   "outputs": [],
   "source": [
    "product5 = product4.reset_index()\n",
    "pd.set_option('max_columns', None)\n",
    "display(product5)\n",
    "pd.set_option('max_columns', 10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fdc39d8f",
   "metadata": {},
   "source": [
    "# analyse order_payment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3e01c11",
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "display(order_payment.payment_type.value_counts(normalize=True))\n",
    "display(order_payment.payment_installments.value_counts().head(10))\n",
    "order_payment.loc[order_payment.payment_sequential > 1, :]\n",
    "order_payment.loc[order_payment.order_id ==\n",
    "                  'fedcd9f7ccdc8cba3a18defedd1a5547', :].sort_values(\n",
    "    'payment_sequential')\n",
    "display(order_payment.loc[order_payment.payment_installments == 3, :])\n",
    "order_payment.loc[order_payment.order_id ==\n",
    "                  '1dcf0c8cd36ffaf57784fbdc90079310', :].sort_values(\n",
    "    'payment_sequential')\n",
    "\n",
    "\n",
    "plt.figure(figsize=(4, 4))\n",
    "ax = order_payment.payment_type.value_counts(normalize=True).plot.barh(\n",
    "    color=\"#8d19a9\",\n",
    "    title='Pourcentage de type de paiement',\n",
    "    xlabel='pourcentage de type de paiement')\n",
    "ax.grid(zorder=0)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d2a21d4",
   "metadata": {},
   "source": [
    "## aggregation des order_payments par order_id\n",
    "* payment_sequential count/max\n",
    "* payment_installments min/mean/max\n",
    "* payment_value sum/min/mean/max\n",
    "* 'payment_type' nb et pct du type de paiement (cash/credit card/debt card/ not defined / voucher\n",
    "* payment value par type de paiement\n",
    "\n",
    "* si cash: commande payée entierement en cash\n",
    "* si debit commande payée entierement en debit\n",
    "* si credit 3% des commandes payées en plus avec des voucher\n",
    "* seuls les credits et mix credit voucher peuvent avoir plusieurs paiements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b318207c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# payment_sequential: count et max doivent donner la meme chose\n",
    "pay1 = order_payment.groupby(by=\"order_id\").agg({\n",
    "    'payment_sequential': ['count', 'max'],\n",
    "    'payment_installments': ['min', 'mean', 'max'],\n",
    "    'payment_value': ['sum', 'min', 'mean', 'max']})\n",
    "pay1.columns = pay1.columns.droplevel(0)\n",
    "pay1.columns = ['pay_seq_count', 'pay_seq_max',\n",
    "                'pay_ins_min', 'pay_ins_mean',\n",
    "                'pay_ins_max', 'pay_val_sum',\n",
    "                'pay_val_min', 'pay_val_mean',\n",
    "                'pay_val_max']\n",
    "# seulement 80 commandes avec sequence count different de sequence max\n",
    "pay1.loc[pay1.pay_seq_count != pay1.pay_seq_max, :]\n",
    "\n",
    "pay2 = order_payment.groupby(\n",
    "    'order_id')['payment_type'].value_counts().unstack()\n",
    "pay2.columns = ['cash', 'creditc', 'debitc', 'nd', 'voucher']\n",
    "pay3 = order_payment.groupby(\n",
    "    'order_id')['payment_type'].value_counts(normalize=True).unstack()\n",
    "pay3.columns = ['cash_pct', 'creditc_pct',\n",
    "                'debitc_pct', 'nd_pct', 'voucher_pct']\n",
    "pay2 = pay2.join(pay3)\n",
    "pay2 = pay2.fillna(0)\n",
    "pay1 = pay1.join(pay2)\n",
    "\n",
    "\n",
    "# calcul des montant par type de paiement\n",
    "pay4 = order_payment.loc[\n",
    "    order_payment.payment_type == 'credit_card', :].groupby(\n",
    "    by=\"order_id\").agg({'payment_value': ['sum']})\n",
    "pay4.columns = pay4.columns.droplevel(0)\n",
    "pay4.columns = ['pay_val_creditc']\n",
    "pay5 = order_payment.loc[\n",
    "    order_payment.payment_type == 'debit_card', :].groupby(\n",
    "    by=\"order_id\").agg({'payment_value': ['sum']})\n",
    "pay5.columns = pay5.columns.droplevel(0)\n",
    "pay5.columns = ['pay_val_debitc']\n",
    "pay6 = order_payment.loc[\n",
    "    order_payment.payment_type == 'boleto', :].groupby(\n",
    "    by=\"order_id\").agg({'payment_value': ['sum']})\n",
    "pay6.columns = pay6.columns.droplevel(0)\n",
    "pay6.columns = ['pay_val_cash']\n",
    "pay7 = order_payment.loc[\n",
    "    order_payment.payment_type == 'voucher', :].groupby(\n",
    "    by=\"order_id\").agg({'payment_value': ['sum']})\n",
    "pay7.columns = pay7.columns.droplevel(0)\n",
    "pay7.columns = ['pay_val_voucher']\n",
    "pay8 = order_payment.loc[\n",
    "    order_payment.payment_type == 'not_defined', :].groupby(\n",
    "    by=\"order_id\").agg({'payment_value': ['sum']})\n",
    "pay8.columns = pay8.columns.droplevel(0)\n",
    "pay8.columns = ['pay_val_nd']\n",
    "\n",
    "print('pourcentage des paiements faits en credit card:',\n",
    "      pay1.creditc_pct.mean())\n",
    "print('pourcentage des paiements faits en debit card:',\n",
    "      pay1.debitc_pct.mean())\n",
    "print('pourcentage des paiements faits en cash:',\n",
    "      pay1.cash_pct.mean())\n",
    "print('pourcentage des paiements faits en voucher:',\n",
    "      pay1.voucher_pct.mean())\n",
    "\n",
    "pay1 = pay1.join(pay4)\n",
    "pay1 = pay1.join(pay5)\n",
    "pay1 = pay1.join(pay6)\n",
    "pay1 = pay1.join(pay7)\n",
    "pay1 = pay1.join(pay8)\n",
    "\n",
    "pay1[['pay_val_creditc', 'pay_val_debitc',\n",
    "      'pay_val_cash', 'pay_val_voucher', 'pay_val_nd']] = pay1[[\n",
    "          'pay_val_creditc', 'pay_val_debitc',\n",
    "          'pay_val_cash', 'pay_val_voucher', 'pay_val_nd']].fillna(0)\n",
    "pay1['pay_val_pct_creditc'] = pay1['pay_val_creditc']/pay1['pay_val_sum']\n",
    "pay1['pay_val_pct_debitc'] = pay1['pay_val_debitc']/pay1['pay_val_sum']\n",
    "pay1['pay_val_pct_cash'] = pay1['pay_val_cash']/pay1['pay_val_sum']\n",
    "pay1['pay_val_pct_voucher'] = pay1['pay_val_voucher']/pay1['pay_val_sum']\n",
    "pay1['pay_val_pct_nd'] = pay1['pay_val_nd']/pay1['pay_val_sum']\n",
    "\n",
    "# petit nettoyage\n",
    "pay1 = pay1.loc[pay1.pay_seq_count ==\n",
    "                pay1.pay_seq_max, :]  # on retire 80 lignes\n",
    "pay1 = pay1.loc[pay1.pay_val_pct_nd != 1, :]\n",
    "pay1 = pay1.drop(columns=['pay_seq_max', 'pay_val_nd', 'pay_val_pct_nd'])\n",
    "\n",
    "display(pay1.pay_val_pct_cash.value_counts())\n",
    "display(pay1.pay_val_pct_voucher.value_counts())\n",
    "display(pay1.pay_val_pct_debitc.value_counts())\n",
    "display(pay1.pay_val_pct_creditc.value_counts())\n",
    "\n",
    "pay1.loc[pay1.pay_val_pct_cash == 1, ['pay_type']] = 'cash'\n",
    "pay1.loc[pay1.pay_val_pct_debitc == 1, ['pay_type']] = 'debit'\n",
    "pay1.loc[((pay1.pay_val_pct_creditc == 1) & (\n",
    "    pay1.pay_val_pct_voucher == 0)), ['pay_type']] = 'credit'\n",
    "pay1.loc[((pay1.pay_val_pct_voucher == 1) & (\n",
    "    pay1.pay_val_pct_creditc == 0)), ['pay_type']] = 'voucher'\n",
    "pay1.loc[((pay1.pay_val_pct_voucher > 0) & (pay1.pay_val_pct_creditc > 0)), [\n",
    "    'pay_type']] = 'mix credit voucher'\n",
    "\n",
    "# autre nettoyage pour données inutiles\n",
    "pay1 = pay1.drop(columns=['pay_val_pct_creditc',\n",
    "                 'pay_val_pct_debitc', 'pay_val_pct_cash'])\n",
    "pay1 = pay1.drop(columns=['pay_val_creditc', 'pay_val_debitc', 'pay_val_cash'])\n",
    "pay1 = pay1.drop(columns=['cash_pct', 'creditc_pct', 'debitc_pct', 'nd_pct'])\n",
    "pay1 = pay1.drop(columns=['cash', 'creditc', 'debitc', 'nd'])\n",
    "\n",
    "pd.set_option('max_columns', None)\n",
    "display(pay1.loc[pay1.pay_ins_min != pay1.pay_ins_max, :])\n",
    "pd.set_option('max_columns', 10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c192909a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "display(pay1.pay_type.value_counts(normalize=True))\n",
    "\n",
    "\n",
    "plt.rcParams[\"figure.figsize\"] = [16, 5]\n",
    "plt.rcParams[\"figure.autolayout\"] = True\n",
    "\n",
    "f, axes = plt.subplots(1, 3)\n",
    "sns.countplot(x='pay_type', data=pay1, ax=axes[0])\n",
    "sns.boxplot(x='pay_type', y='pay_val_sum',\n",
    "            data=pay1, showmeans=True, ax=axes[1])\n",
    "sns.boxplot(x='pay_type', y='pay_val_sum', data=pay1,\n",
    "            showfliers=False, showmeans=True, ax=axes[2])\n",
    "axes[0].set_title('type de payment')\n",
    "axes[1].set_title('somme par commande')\n",
    "axes[2].set_title('zoom somme par commande')\n",
    "plt.show()\n",
    "\n",
    "f, axes = plt.subplots(1, 3)\n",
    "sns.boxplot(x='pay_type', y='pay_val_pct_voucher',\n",
    "            data=pay1.loc[pay1.pay_type == 'mix credit voucher', :],\n",
    "            showmeans=True,\n",
    "            ax=axes[0])\n",
    "sns.boxplot(x='pay_type', y='pay_ins_max',\n",
    "            data=pay1, showmeans=True, ax=axes[1])\n",
    "sns.boxplot(x='pay_type', y='pay_ins_max', data=pay1,\n",
    "            showfliers=False, showmeans=True, ax=axes[2])\n",
    "axes[0].set_title('pct de voucher pour paiement mixte')\n",
    "axes[1].set_title('nb pay par commande')\n",
    "axes[2].set_title('zoom nb pay par commande')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f02d6b72",
   "metadata": {},
   "source": [
    "# jointure order et order_payment et analyse d'order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "258bb3df",
   "metadata": {},
   "outputs": [],
   "source": [
    "# on fusionne avec order\n",
    "order1 = order.set_index('order_id')\n",
    "order1 = order1.join(pay1)\n",
    "# \"datetime\"\n",
    "order1.order_purchase_timestamp = pd.to_datetime(\n",
    "    order1.order_purchase_timestamp, errors='coerce')\n",
    "order1.order_approved_at = pd.to_datetime(\n",
    "    order1.order_approved_at, errors='coerce')\n",
    "order1.order_delivered_carrier_date = pd.to_datetime(\n",
    "    order1.order_delivered_carrier_date, errors='coerce')\n",
    "order1.order_delivered_customer_date = pd.to_datetime(\n",
    "    order1.order_delivered_customer_date, errors='coerce')\n",
    "order1.order_estimated_delivery_date = pd.to_datetime(\n",
    "    order1.order_estimated_delivery_date, errors='coerce')\n",
    "\n",
    "# difference de temps (c est vraimeent histoire 2)\n",
    "order1['tim2approv'] = order1['order_approved_at'] - \\\n",
    "    order1['order_purchase_timestamp']\n",
    "order1['tim2carrier'] = order1['order_delivered_carrier_date'] - \\\n",
    "    order1['order_approved_at']\n",
    "order1['tim2deliv'] = order1['order_delivered_customer_date'] - \\\n",
    "    order1['order_delivered_carrier_date']\n",
    "order1['tim2estim'] = order1['order_estimated_delivery_date'] - \\\n",
    "    order1['order_delivered_customer_date']\n",
    "\n",
    "display(order1.order_status.value_counts(normalize=True))\n",
    "\n",
    "# test doublons sur clés\n",
    "\n",
    "\n",
    "def doublonrem(datain, datanam, keynam):\n",
    "    \"\"\"enleve les doublons.\"\"\"\n",
    "    temp = datain.copy()\n",
    "    temp2 = temp.drop_duplicates(subset=keynam, keep='last', inplace=False)\n",
    "    # inplace -> renvoie une table filtrée\n",
    "    # ou modifie directement la table de base sans rien renvoyer\n",
    "    print('extraction de ', temp2.shape[0]-datain.shape[0],\n",
    "          'doublons de la table ', datanam, ' basée sur la variable ', keynam)\n",
    "    return temp2\n",
    "# order2=doublonrem(order1.reset_index(),'order','order_id')\n",
    "\n",
    "\n",
    "order1 = order1.sort_values('order_purchase_timestamp')\n",
    "pd.set_option('max_columns', None)\n",
    "display(order1)\n",
    "pd.set_option('max_columns', 10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1cc2eb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime as dt\n",
    "print(order1.order_purchase_timestamp.dtypes)\n",
    "print(order1.tim2approv.dtypes)\n",
    "\n",
    "# attention les plot ont du mal avec les types timedelta\n",
    "# -> les changer en float ou avec total_seconds\n",
    "order1.tim2approv = order1.tim2approv.dt.total_seconds()/3600/24\n",
    "order1.tim2carrier = order1.tim2carrier.dt.total_seconds()/3600/24\n",
    "order1.tim2deliv = order1.tim2deliv.dt.total_seconds()/3600/24\n",
    "order1.tim2estim = order1.tim2estim.dt.total_seconds()/3600/24\n",
    "\n",
    "print(order1.tim2approv.dtypes)\n",
    "\n",
    "plt.rcParams[\"figure.figsize\"] = [16, 4]\n",
    "plt.rcParams[\"figure.autolayout\"] = True\n",
    "\n",
    "f, axes = plt.subplots(1, 2)\n",
    "sns.scatterplot(data=order1, x=\"order_purchase_timestamp\",\n",
    "                y=\"tim2approv\", ax=axes[0])\n",
    "sns.scatterplot(data=order1, x=\"order_purchase_timestamp\",\n",
    "                y=\"tim2carrier\", ax=axes[1])\n",
    "axes[0].set_title('nombre de jours avant validation de la commande')\n",
    "axes[1].set_title('nombre de jours entre validation et transporteur')\n",
    "plt.show()\n",
    "f, axes = plt.subplots(1, 2)\n",
    "sns.scatterplot(data=order1, x=\"order_purchase_timestamp\",\n",
    "                y=\"tim2deliv\", ax=axes[0])\n",
    "sns.scatterplot(data=order1, x=\"order_purchase_timestamp\",\n",
    "                y=\"tim2estim\", ax=axes[1])\n",
    "axes[0].set_title('nombre de jours entre transporteur et livraison')\n",
    "axes[1].set_title(\"nombre de jours d'avance sur la date de livraison estimée\")\n",
    "plt.show()\n",
    "f, axes = plt.subplots(1, 2)\n",
    "sns.boxplot(x='order_status', y='tim2approv',\n",
    "            data=order1, showmeans=True, ax=axes[0])\n",
    "sns.boxplot(x='order_status', y='tim2approv', data=order1,\n",
    "            showfliers=False, showmeans=True, ax=axes[1])\n",
    "axes[0].set_title('nombre de jours avant validation de la commande par statut')\n",
    "axes[1].set_title(\n",
    "    \"nombre de jours avant validation de la commande par statut\" +\n",
    "    \" - zoom sans outliers\")\n",
    "plt.show()\n",
    "f, axes = plt.subplots(1, 2)\n",
    "sns.boxplot(x='order_status', y='tim2carrier',\n",
    "            data=order1, showmeans=True, ax=axes[0])\n",
    "sns.boxplot(x='order_status', y='tim2carrier', data=order1,\n",
    "            showfliers=False, showmeans=True, ax=axes[1])\n",
    "axes[0].set_title(\n",
    "    'nombre de jours entre validation et transporteur par statut')\n",
    "axes[1].set_title(\n",
    "    \"nombre de jours entre validation et transporteur par statut\" +\n",
    "    \"- zoom sans outliers\")\n",
    "plt.show()\n",
    "f, axes = plt.subplots(1, 2)\n",
    "sns.boxplot(x='order_status', y='tim2deliv',\n",
    "            data=order1, showmeans=True, ax=axes[0])\n",
    "sns.boxplot(x='order_status', y='tim2deliv', data=order1,\n",
    "            showfliers=False, showmeans=True, ax=axes[1])\n",
    "axes[0].set_title(\"nombre de jours entre transporteur et livraison\" +\n",
    "                  \" par statut\")\n",
    "axes[1].set_title(\n",
    "    \"nombre de jours entre transporteur et livraison par statut\" +\n",
    "    \" - zoom sans outliers\")\n",
    "plt.show()\n",
    "f, axes = plt.subplots(1, 2)\n",
    "sns.boxplot(x='order_status', y='tim2estim',\n",
    "            data=order1, showmeans=True, ax=axes[0])\n",
    "sns.boxplot(x='order_status', y='tim2estim', data=order1,\n",
    "            showfliers=False, showmeans=True, ax=axes[1])\n",
    "axes[0].set_title(\n",
    "    \"nombre de jours d'avance sur la date de livraison estimée par statut\")\n",
    "axes[1].set_title(\n",
    "    \"nombre de jours d'avance sur la date de livraison estimée \" +\n",
    "    \"par statut - zoom sans outliers\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "68cee4c1",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "orderhist = order1[['order_purchase_timestamp', 'pay_val_sum']].copy()\n",
    "orderhist['pay_val_cumsum'] = orderhist.pay_val_sum.cumsum()\n",
    "orderhist['mois'] = orderhist.order_purchase_timestamp.dt.month\n",
    "orderhist['semaine'] = orderhist.order_purchase_timestamp.dt.isocalendar().week\n",
    "# display(orderhist)\n",
    "# sns.relplot(data=orderhist, x=\"order_purchase_timestamp\",\n",
    "# y=\"pay_val_cumsum\", kind=\"line\")\n",
    "\n",
    "orderseason1 = orderhist.groupby(by=\"mois\").agg(\n",
    "    {'pay_val_sum': ['count', 'mean', 'median']})\n",
    "orderseason1.columns = orderseason1.columns.droplevel(0)\n",
    "orderseason2 = orderhist.groupby(by=\"semaine\").agg(\n",
    "    {'pay_val_sum': ['count', 'mean', 'median']})\n",
    "orderseason2.columns = orderseason2.columns.droplevel(0)\n",
    "# display(orderseason1)\n",
    "\n",
    "\n",
    "# chose tres importante\n",
    "# pour aligner les x avec un double y utiliser la fonction\n",
    "# ax.get_xticks() pour le x du second axe\n",
    "#\n",
    "\n",
    "ax0 = plt.subplot(121)\n",
    "ax1 = ax0.twinx()  # permet le double axe\n",
    "ax2 = plt.subplot(122)\n",
    "ax3 = ax2.twinx()\n",
    "\n",
    "sns.barplot(x=orderseason1.index, y='count',\n",
    "            data=orderseason1, color='blue', ax=ax0)\n",
    "sns.lineplot(data=orderseason1, x=ax0.get_xticks(),\n",
    "             y=orderseason1['mean'], marker='s', legend=\"auto\", ax=ax1)\n",
    "sns.lineplot(data=orderseason1, x=ax0.get_xticks(),\n",
    "             y=orderseason1['median'], marker='s', legend=\"auto\", ax=ax1)\n",
    "\n",
    "sns.barplot(x=orderseason2.index, y='count',\n",
    "            data=orderseason2, color='blue', ax=ax2)\n",
    "sns.lineplot(data=orderseason2, x=ax2.get_xticks(),\n",
    "             y=orderseason2['mean'], marker='s', legend='brief', ax=ax3)\n",
    "sns.lineplot(data=orderseason2, x=ax2.get_xticks(),\n",
    "             y=orderseason2['median'], marker='s', legend='brief', ax=ax3)\n",
    "\n",
    "ax1.set(ylabel='median(o)/mean(b)')\n",
    "ax3.set(ylabel='median(o)/mean(b)')\n",
    "ax0.set_title('panier moyen / median vs nb de commandes par mois')\n",
    "ax2.set_title('panier moyen / median vs nb de commandes par semaine')\n",
    "plt.xticks(np.arange(0, 52, step=2))\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8f6111b",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "import matplotlib.image as img\n",
    "ordercust = order1.copy()\n",
    "ordercust = ordercust.reset_index()\n",
    "# fusion order et customer\n",
    "ordercust1 = pd.merge(ordercust, cust, how='outer', on='customer_id')\n",
    "# passage en dummies pour le type de paiement\n",
    "ordercust2 = pd.get_dummies(data=ordercust1, columns=['pay_type'])\n",
    "pd.set_option('max_columns', None)\n",
    "# display(ordercust)#99441 commandes/lignes\n",
    "# display(cust) #99441 customer id/ lignes (1 par commande en fait)\n",
    "# -> c 'est le customer unique id qui compte'\n",
    "# display(ordercust2)\n",
    "pd.set_option('max_columns', 10)\n",
    "\n",
    "# aggregation par customer_unique _id\n",
    "ordercust3 = ordercust2.groupby(by=\"customer_unique_id\").agg({\n",
    "    'pay_val_sum': ['count', 'sum', 'mean', 'median'],\n",
    "    'pay_type_cash': ['mean'],\n",
    "    'pay_type_debit': ['mean'],\n",
    "    'pay_type_credit': ['mean'],\n",
    "    'pay_type_mix credit voucher': ['mean'],\n",
    "    'pay_type_voucher': ['mean']})\n",
    "\n",
    "ordercust3.columns = ordercust3.columns.droplevel(0)\n",
    "ordercust3.columns = ['pay_count', 'pay_sum', 'pay_mean',\n",
    "                      'pay_median', 'cash', 'debit', 'credit', 'mix', 'voucher']\n",
    "# display(ordercust3.sort_values('pay_count'))\n",
    "\n",
    "######################################################\n",
    "ordercust4 = ordercust3.value_counts('pay_count', normalize=True)\n",
    "display(ordercust4)\n",
    "print(\"97% des clients n'ont passé qu'une seule commande\")\n",
    "\n",
    "f, axes = plt.subplots(1, 2)\n",
    "\n",
    "axes[0].pie(ordercust3.value_counts('pay_count',\n",
    "                                    normalize=True).head(2),\n",
    "            labels=ordercust3.value_counts('pay_count').head(2).index,\n",
    "            normalize=False, autopct='%1.1f%%',\n",
    "            shadow=True, startangle=90)\n",
    "# Equal aspect ratio ensures that pie is drawn as a circle.\n",
    "axes[0].axis('equal')\n",
    "axes[0].set_title('nombre de commandes par client')\n",
    "\n",
    "ordercust3['pay_type'] = 'mix'\n",
    "ordercust3.loc[(ordercust3.cash+ordercust3.debit == 1),\n",
    "               ['pay_type']] = 'cash_debit'\n",
    "ordercust3.loc[(ordercust3.credit+ordercust3.voucher +\n",
    "                ordercust3.mix == 1), ['pay_type']] = 'credit_voucher'\n",
    "\n",
    "ordercust5 = ordercust3.value_counts('pay_type', normalize=True)\n",
    "display(ordercust5)\n",
    "print('tres peu de mix credit/cash')\n",
    "\n",
    "f, axes = plt.subplots(1, 2)\n",
    "sns.scatterplot(data=ordercust3, x='pay_count', y='pay_sum',\n",
    "                hue='pay_type', alpha=0.5, ax=axes[0])\n",
    "sns.scatterplot(data=ordercust3, x='pay_count', y='pay_mean',\n",
    "                hue='pay_type', alpha=0.5, ax=axes[1])\n",
    "axes[0].set_title(\n",
    "    \"CA par nombre d'achat par client et par type de paiement du client\")\n",
    "axes[1].set_title(\n",
    "    \"CA moyen par nombre d'achat par client et par type de paiement du client\")\n",
    "plt.show\n",
    "\n",
    "f, axes = plt.subplots(1, 2)\n",
    "sns.boxplot(x='pay_type', y='pay_mean',\n",
    "            data=ordercust3.loc[ordercust3.pay_type != 'mix', :],\n",
    "            showmeans=True, showfliers=False, ax=axes[0])\n",
    "sns.boxplot(x='pay_type', y='pay_sum',\n",
    "            data=ordercust3.loc[ordercust3.pay_type != 'mix', :],\n",
    "            showmeans=True, showfliers=False, ax=axes[1])\n",
    "axes[0].set_title(\"CA par type de paiement du client\")\n",
    "axes[1].set_title(\"CA moyen par type de paiement du client\")\n",
    "plt.show\n",
    "\n",
    "######################################################\n",
    "idcp = ordercust2[['customer_unique_id', 'customer_zip_code_prefix',\n",
    "                   'customer_city', 'customer_state']].copy()\n",
    "idcp2 = doublonrem(idcp, 'unique id', 'customer_unique_id')\n",
    "\n",
    "idcp2['customer_city'] = idcp2['customer_city'].str.replace('ã', 'a')\n",
    "idcp2['customer_city'] = idcp2['customer_city'].str.replace('í', 'i')\n",
    "idcp2['customer_city'] = idcp2['customer_city'].str.replace('é', 'e')\n",
    "idcp2['customer_city'] = idcp2['customer_city'].str.replace('ê', 'e')\n",
    "idcp2['customer_city'] = idcp2['customer_city'].str.replace('ô', 'o')\n",
    "idcp2['customer_city'] = idcp2['customer_city'].str.replace('á', 'a')\n",
    "idcp2['customer_city'] = idcp2['customer_city'].str.replace('ú', 'u')\n",
    "idcp2['customer_city'] = idcp2['customer_city'].str.replace('â', 'a')\n",
    "idcp2['customer_city'] = idcp2['customer_city'].str.replace('ó', 'o')\n",
    "idcp2['customer_city'] = idcp2['customer_city'].str.replace(\"t'\", 't')\n",
    "idcp2['customer_city'] = idcp2['customer_city'].str.replace('õ', 'o')\n",
    "idcp2['customer_city'] = idcp2['customer_city'].str.replace('ç', 'c')\n",
    "idcp2['customer_city'] = idcp2['customer_city'].str.replace(\"d'\", \"d \")\n",
    "idcp2['customer_city'] = idcp2['customer_city'].str.replace(\n",
    "    \"embu\", 'embu das artes')\n",
    "idcp2['customer_city'] = idcp2['customer_city'].str.replace(\n",
    "    \"das artes das artes\", \"das artes\")\n",
    "idcp2['customer_city'] = idcp2['customer_city'].str.replace(\"-\", ' ')\n",
    "\n",
    "# fusion avec la base geo\n",
    "geo1 = geo.copy()\n",
    "\n",
    "# nettoyage\n",
    "# on enleve les positions hors du bresil aberrantes et on fixe le sproblemes d'orthographe\n",
    "geo1.loc[((geo1.geolocation_lng > -28) | (geo1.geolocation_lng < -75) |\n",
    "          (geo1.geolocation_lat > 5) | (geo1.geolocation_lat < -34)),\n",
    "         ['geolocation_lat', 'geolocation_lng']] = np.nan\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace('ã', 'a')\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace('í', 'i')\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace('é', 'e')\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace('ê', 'e')\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace('ô', 'o')\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace('á', 'a')\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace('ú', 'u')\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace('â', 'a')\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace('ó', 'o')\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace(\"t'\", 't')\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace('õ', 'o')\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace('ç', 'c')\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace(\"d'\", \"d \")\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace(\n",
    "    \"embu\", 'embu das artes')\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace(\n",
    "    \"das artes das artes\", \"das artes\")\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace(\"-\", ' ')\n",
    "\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace(\n",
    "    \"piumhi\", 'piumhii')\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace(\n",
    "    \"santa terezinha\", 'santa teresinha')\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.replace(\n",
    "    \"belem de sao francisco\", 'belem do sao francisco')\n",
    "\n",
    "\n",
    "geo1['geolocation_city'] = geo1['geolocation_city'].str.lower()\n",
    "\n",
    "# aggregation des données geo par zip\n",
    "geo1_a = geo1.groupby(by=\"geolocation_zip_code_prefix\").agg(\n",
    "    {'geolocation_lat': ['mean'], 'geolocation_lng': ['mean']})\n",
    "geo1_a.columns = geo1_a.columns.droplevel(0)\n",
    "geo1_a.columns = ['lat_zip', 'lng_zip']\n",
    "\n",
    "# aggregation des données geo par city\n",
    "geo1_b = geo1.groupby(by=\"geolocation_city\").agg(\n",
    "    {'geolocation_lat': ['mean'], 'geolocation_lng': ['mean']})\n",
    "geo1_b.columns = geo1_b.columns.droplevel(0)\n",
    "geo1_b.columns = ['lat_city', 'lng_city']\n",
    "\n",
    "# aggregation des données geo par state\n",
    "geo1_c = geo1.groupby(by=\"geolocation_state\").agg(\n",
    "    {'geolocation_lat': ['mean'], 'geolocation_lng': ['mean']})\n",
    "geo1_c.columns = geo1_c.columns.droplevel(0)\n",
    "geo1_c.columns = ['lat_state', 'lng_state']\n",
    "\n",
    "\n",
    "# fusion city state et donnes geo moyennees\n",
    "# on vire les doublons\n",
    "geo2 = doublonrem(geo1, 'geo', 'geolocation_zip_code_prefix')\n",
    "geo2 = geo2.loc[:, ['geolocation_zip_code_prefix', 'geolocation_city',\n",
    "                    'geolocation_state']].set_index('geolocation_zip_code_prefix')\n",
    "geo2 = geo2.join(geo1_a)\n",
    "\n",
    "fus1 = idcp2.set_index('customer_zip_code_prefix').join(geo2)\n",
    "fus1 = fus1.reset_index()\n",
    "fus1.rename(columns={'index': 'customer_zip_code_prefix'}, inplace=True)\n",
    "\n",
    "fus2 = fus1.set_index('customer_city').join(geo1_b)\n",
    "fus2 = fus2.reset_index()\n",
    "fus2.rename(columns={'index': 'customer_city'}, inplace=True)\n",
    "\n",
    "fus3 = fus2.set_index('customer_state').join(geo1_c)\n",
    "fus3 = fus3.reset_index()\n",
    "fus3.rename(columns={'index': 'customer_state'}, inplace=True)\n",
    "\n",
    "fus3[['lat_city_fill', 'lng_city_fill']] = fus3[['lat_city', 'lng_city']]\n",
    "fus3.loc[(fus3.lat_city.isna()), ['lat_city_fill']] = fus3['lat_state']\n",
    "fus3.loc[(fus3.lng_city.isna()), ['lng_city_fill']] = fus3['lng_state']\n",
    "\n",
    "fus3 = fus3.set_index('customer_unique_id')\n",
    "ordercustgeo = ordercust3.join(fus3)\n",
    "\n",
    "#################################################\n",
    "#################################################\n",
    "display(ordercustgeo.value_counts('geolocation_state', normalize=True))\n",
    "display(ordercustgeo.value_counts('geolocation_city', normalize=True))\n",
    "\n",
    "f, axes = plt.subplots(1, 2)\n",
    "\n",
    "axes[0].pie(ordercustgeo.value_counts('geolocation_state',\n",
    "                                      normalize=True).head(10),\n",
    "            labels=ordercustgeo.value_counts(\n",
    "                'geolocation_state').head(10).index,\n",
    "            normalize=False, autopct='%1.1f%%',\n",
    "            shadow=True, startangle=90)\n",
    "# Equal aspect ratio ensures that pie is drawn as a circle.\n",
    "axes[0].axis('equal')\n",
    "axes[0].set_title(\n",
    "    'distribution des clients par etat (les 10 plus représentés)')\n",
    "\n",
    "axes[1].pie(ordercustgeo.value_counts('geolocation_city',\n",
    "                                      normalize=True).head(10),\n",
    "            labels=ordercustgeo.value_counts(\n",
    "                'geolocation_city').head(10).index,\n",
    "            normalize=False, autopct='%1.1f%%', shadow=True, startangle=90)\n",
    "# explode=explode,\n",
    "# Equal aspect ratio ensures that pie is drawn as a circle.\n",
    "axes[1].axis('equal')\n",
    "axes[1].set_title(\n",
    "    'distribution des clients par ville (les 10 plus representées)')\n",
    "plt.show()\n",
    "\n",
    "#################################################\n",
    "test = pd.DataFrame(ordercustgeo.value_counts(\n",
    "    'customer_state', normalize=True))\n",
    "test.columns = ['cust_pct']\n",
    "test['cust_state_rank'] = test['cust_pct'].rank(method='max', ascending=False)\n",
    "\n",
    "ordercustgeo2 = ordercustgeo.reset_index()\n",
    "ordercustgeo2.rename(columns={'index': 'customer_unique_id'}, inplace=True)\n",
    "ordercustgeo2 = ordercustgeo2.set_index('customer_state')\n",
    "ordercustgeo2 = ordercustgeo2.join(test['cust_state_rank'])\n",
    "ordercustgeo2 = ordercustgeo2.reset_index()\n",
    "ordercustgeo2.rename(columns={'index': 'customer_state'}, inplace=True)\n",
    "ordercustgeo2 = ordercustgeo2.set_index('customer_unique_id')\n",
    "\n",
    "# display(test)\n",
    "# display(ordercustgeo2.sort_values('cust_state_rank'))\n",
    "\n",
    "f, axes = plt.subplots(1, 1)\n",
    "ax = sns.boxplot(x='customer_state', y='pay_mean',\n",
    "                 data=ordercustgeo2.loc[(ordercustgeo2.cust_state_rank <= 10) & (\n",
    "                     ordercustgeo2.pay_type != 'mix')].sort_values([\n",
    "                         'cust_state_rank', 'pay_type']),\n",
    "                 hue='pay_type',\n",
    "                 showmeans=True,\n",
    "                 showfliers=False)\n",
    "ax.title.set_text(\n",
    "    \"panier moyen par etat (les 10 plus representés) et par type de paiement\")\n",
    "plt.show\n",
    "\n",
    "#################################################\n",
    "test = pd.DataFrame(ordercustgeo.value_counts('customer_city', normalize=True))\n",
    "test.columns = ['cust_pct']\n",
    "test['cust_city_rank'] = test['cust_pct'].rank(method='max', ascending=False)\n",
    "\n",
    "ordercustgeo3 = ordercustgeo.reset_index()\n",
    "ordercustgeo3.rename(columns={'index': 'customer_unique_id'}, inplace=True)\n",
    "ordercustgeo3 = ordercustgeo3.set_index('customer_city')\n",
    "ordercustgeo3 = ordercustgeo3.join(test['cust_city_rank'])\n",
    "ordercustgeo3 = ordercustgeo3.reset_index()\n",
    "ordercustgeo3.rename(columns={'index': 'customer_city'}, inplace=True)\n",
    "ordercustgeo3 = ordercustgeo3.set_index('customer_unique_id')\n",
    "\n",
    "# display(test)\n",
    "# display(ordercustgeo3.sort_values('cust_city_rank'))\n",
    "\n",
    "f, axes = plt.subplots(1, 1)\n",
    "ax = sns.boxplot(x='customer_city', y='pay_mean',\n",
    "                 data=ordercustgeo3.loc[(ordercustgeo3.cust_city_rank <= 10) & (\n",
    "                     ordercustgeo3.pay_type != 'mix')].sort_values([\n",
    "                         'cust_city_rank', 'pay_type']),\n",
    "                 hue='pay_type',\n",
    "                 showmeans=True,\n",
    "                 showfliers=False)\n",
    "ax.title.set_text(\"panier moyen par ville (les 10 plus représentées)\")\n",
    "plt.show\n",
    "\n",
    "#################################################\n",
    "\n",
    "plt.rcParams[\"figure.figsize\"] = [16, 6]\n",
    "plt.rcParams[\"figure.autolayout\"] = True\n",
    "\n",
    "ordercustgeo_aggstate = ordercustgeo.groupby(\n",
    "    by=\"customer_state\").agg({'pay_sum': ['sum']})\n",
    "ordercustgeo_aggstate.columns = ordercustgeo_aggstate.columns.droplevel(0)\n",
    "ordercustgeo_aggstate.columns = ['ca']\n",
    "ordercustgeo_aggstate['ca_pct'] = ordercustgeo_aggstate.ca / \\\n",
    "    ordercustgeo_aggstate.ca.sum()\n",
    "\n",
    "ordercustgeo_aggcity = ordercustgeo.groupby(\n",
    "    by=\"customer_city\").agg({'pay_sum': ['sum']})\n",
    "ordercustgeo_aggcity.columns = ordercustgeo_aggcity.columns.droplevel(0)\n",
    "ordercustgeo_aggcity.columns = ['ca']\n",
    "ordercustgeo_aggcity['ca_pct'] = ordercustgeo_aggcity.ca / \\\n",
    "    ordercustgeo_aggcity.ca.sum()\n",
    "\n",
    "\n",
    "# display(ordercustgeo_aggstate.sort_values('ca',ascending=False))\n",
    "# display(ordercustgeo.value_counts('geolocation_state',normalize=True))\n",
    "# display(ordercustgeo.value_counts('geolocation_city',normalize=True))\n",
    "\n",
    "f, axes = plt.subplots(1, 2)\n",
    "\n",
    "axes[0].pie(ordercustgeo_aggstate.sort_values('ca',\n",
    "                                              ascending=False).ca_pct.head(10),\n",
    "            labels=ordercustgeo_aggstate.sort_values(\n",
    "                'ca', ascending=False).head(10).index,\n",
    "            normalize=False, autopct='%1.1f%%',\n",
    "            shadow=True,\n",
    "            startangle=90)\n",
    "# Equal aspect ratio ensures that pie is drawn as a circle.\n",
    "axes[0].axis('equal')\n",
    "axes[0].set_title('distribution des CA par etat (les 10 plus représentés)')\n",
    "\n",
    "axes[1].pie(ordercustgeo_aggcity.sort_values('ca',\n",
    "                                             ascending=False).ca_pct.head(10),\n",
    "            labels=ordercustgeo_aggcity.sort_values(\n",
    "                'ca', ascending=False).head(10).index,\n",
    "            normalize=False, autopct='%1.1f%%',\n",
    "            shadow=True,\n",
    "            startangle=90)  # explode=explode,\n",
    "# Equal aspect ratio ensures that pie is drawn as a circle.\n",
    "axes[1].axis('equal')\n",
    "axes[1].set_title('distribution des CA par ville (les 10 plus representées)')\n",
    "plt.show()\n",
    "####################################################################\n",
    "brazil_img = img.imread('input/brazil.png')\n",
    "\n",
    "###################################################\n",
    "wesh1 = ordercustgeo_aggstate.join(doublonrem(ordercustgeo[[\n",
    "    'customer_state',\n",
    "    'lat_state',\n",
    "    'lng_state']].set_index('customer_state'), 'geo', 'lat_state'))\n",
    "wesh2 = ordercustgeo_aggcity.join(doublonrem(ordercustgeo[[\n",
    "                                  'customer_city', 'lat_city_fill',\n",
    "                                  'lng_city_fill']].set_index('customer_city'),\n",
    "    'geo', 'lat_city_fill'))\n",
    "\n",
    "\n",
    "f, axes = plt.subplots(1, 2)\n",
    "sns.scatterplot(data=wesh1, x='lng_state', y='lat_state',\n",
    "                size=\"ca_pct\", hue=\"ca_pct\", sizes=(40, 400),\n",
    "                alpha=1, ax=axes[0])\n",
    "sns.scatterplot(data=wesh2, x=\"lng_city_fill\", y=\"lat_city_fill\",\n",
    "                size=\"ca_pct\", hue=\"ca_pct\", sizes=(40, 400),\n",
    "                alpha=1, ax=axes[1])\n",
    "\n",
    "axes[0].set_title('CA par etat')\n",
    "axes[1].set_title('CA par villes')\n",
    "\n",
    "im0 = axes[0].imshow(brazil_img, extent=[-75, -28, -34, 5],\n",
    "                     alpha=0.8, aspect='auto')\n",
    "im1 = axes[1].imshow(brazil_img, extent=[-75, -28, -34, 5],\n",
    "                     alpha=0.8, aspect='auto')\n",
    "\n",
    "plt.show()\n",
    "\n",
    "\n",
    "####################################################################\n",
    "ordercustgeo_aggstate = ordercustgeo.loc[ordercustgeo.pay_type == 'cash_debit',\n",
    "                                         :].groupby(\n",
    "    by=\"customer_state\").agg({'pay_sum': ['sum']})\n",
    "ordercustgeo_aggstate.columns = ordercustgeo_aggstate.columns.droplevel(0)\n",
    "ordercustgeo_aggstate.columns = ['ca']\n",
    "ordercustgeo_aggstate['ca_pct'] = ordercustgeo_aggstate.ca / \\\n",
    "    ordercustgeo_aggstate.ca.sum()\n",
    "\n",
    "ordercustgeo_aggcity = ordercustgeo.loc[ordercustgeo.pay_type == 'cash_debit',\n",
    "                                        :].groupby(\n",
    "    by=\"customer_city\").agg({'pay_sum': ['sum']})\n",
    "ordercustgeo_aggcity.columns = ordercustgeo_aggcity.columns.droplevel(0)\n",
    "ordercustgeo_aggcity.columns = ['ca']\n",
    "ordercustgeo_aggcity['ca_pct'] = ordercustgeo_aggcity.ca / \\\n",
    "    ordercustgeo_aggcity.ca.sum()\n",
    "\n",
    "\n",
    "# display(ordercustgeo_aggstate.sort_values('ca',ascending=False))\n",
    "# display(ordercustgeo.value_counts('geolocation_state',normalize=True))\n",
    "# display(ordercustgeo.value_counts('geolocation_city',normalize=True))\n",
    "\n",
    "f, axes = plt.subplots(1, 2)\n",
    "\n",
    "axes[0].pie(ordercustgeo_aggstate.sort_values('ca', ascending=False).ca_pct.head(10),\n",
    "            labels=ordercustgeo_aggstate.sort_values(\n",
    "    'ca', ascending=False).head(10).index, normalize=False, autopct='%1.1f%%',\n",
    "    shadow=True, startangle=90)\n",
    "# Equal aspect ratio ensures that pie is drawn as a circle.\n",
    "axes[0].axis('equal')\n",
    "axes[0].set_title(\n",
    "    'distribution des CA (cash/debit) par etat (les 10 plus représentés)')\n",
    "\n",
    "axes[1].pie(ordercustgeo_aggcity.sort_values('ca', ascending=False).ca_pct.head(10),\n",
    "            labels=ordercustgeo_aggcity.sort_values(\n",
    "    'ca', ascending=False).head(10).index, normalize=False, autopct='%1.1f%%',\n",
    "    shadow=True, startangle=90)  # explode=explode,\n",
    "# Equal aspect ratio ensures that pie is drawn as a circle.\n",
    "axes[1].axis('equal')\n",
    "axes[1].set_title(\n",
    "    'distribution des CA (cash/debit) par ville (les 10 plus representées)')\n",
    "plt.show()\n",
    "\n",
    "####################################################################\n",
    "ordercustgeo_aggstate = ordercustgeo.loc[\n",
    "    ordercustgeo.pay_type == 'credit_voucher', :].groupby(\n",
    "    by=\"customer_state\").agg({'pay_sum': ['sum']})\n",
    "ordercustgeo_aggstate.columns = ordercustgeo_aggstate.columns.droplevel(0)\n",
    "ordercustgeo_aggstate.columns = ['ca']\n",
    "ordercustgeo_aggstate['ca_pct'] = ordercustgeo_aggstate.ca / \\\n",
    "    ordercustgeo_aggstate.ca.sum()\n",
    "\n",
    "ordercustgeo_aggcity = ordercustgeo.loc[\n",
    "    ordercustgeo.pay_type == 'credit_voucher', :].groupby(\n",
    "    by=\"customer_city\").agg({'pay_sum': ['sum']})\n",
    "ordercustgeo_aggcity.columns = ordercustgeo_aggcity.columns.droplevel(0)\n",
    "ordercustgeo_aggcity.columns = ['ca']\n",
    "ordercustgeo_aggcity['ca_pct'] = ordercustgeo_aggcity.ca / \\\n",
    "    ordercustgeo_aggcity.ca.sum()\n",
    "\n",
    "\n",
    "# display(ordercustgeo_aggstate.sort_values('ca',ascending=False))\n",
    "# display(ordercustgeo.value_counts('geolocation_state',normalize=True))\n",
    "# display(ordercustgeo.value_counts('geolocation_city',normalize=True))\n",
    "\n",
    "f, axes = plt.subplots(1, 2)\n",
    "\n",
    "axes[0].pie(ordercustgeo_aggstate.sort_values('ca',\n",
    "                                              ascending=False).ca_pct.head(10),\n",
    "            labels=ordercustgeo_aggstate.sort_values(\n",
    "    'ca', ascending=False).head(10).index, normalize=False, autopct='%1.1f%%',\n",
    "    shadow=True, startangle=90)\n",
    "# Equal aspect ratio ensures that pie is drawn as a circle.\n",
    "axes[0].axis('equal')\n",
    "axes[0].set_title(\n",
    "    'distribution des CA (credit/voucher) par etat (les 10 plus représentés)')\n",
    "\n",
    "axes[1].pie(ordercustgeo_aggcity.sort_values(\n",
    "    'ca', ascending=False).ca_pct.head(10),\n",
    "    labels=ordercustgeo_aggcity.sort_values(\n",
    "    'ca', ascending=False).head(10).index, normalize=False, autopct='%1.1f%%',\n",
    "    shadow=True, startangle=90)  # explode=explode,\n",
    "# Equal aspect ratio ensures that pie is drawn as a circle.\n",
    "axes[1].axis('equal')\n",
    "axes[1].set_title(\n",
    "    'distribution des CA (credit/voucher) par ville (les 10 plus representées)')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00f10d7e",
   "metadata": {},
   "source": [
    "# geo\n",
    "* beaucoup de coordonnées pour la meme ville et le meme zip\n",
    "* on peut imaginer que cette base a ete remplie avec la base ip clients\n",
    "* dans ce cas : SP (sao paulo), MG (Minas Gerais - capitale belo horzonte), RJ (Rio de Janeiro), RS (Rio Grande do Sul capitale:Porto Alegre ) , PR (Paraná capitale: Curitiba) sont les etats les plus representés ce qui semble normal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2948a29",
   "metadata": {},
   "outputs": [],
   "source": [
    "# print(geo.geolocation_lng.min())\n",
    "# print(geo.geolocation_lng.max())\n",
    "# print(geo.geolocation_lat.min())\n",
    "# print(geo.geolocation_lat.max())\n",
    "\n",
    "import matplotlib.image as img\n",
    "geo.sort_values('geolocation_lng')\n",
    "geo.loc[geo.geolocation_city == 'santa lucia do piai', :]\n",
    "\n",
    "geo1 = geo.copy()\n",
    "# nettoyage\n",
    "# on enleve les positions hors du bresil aberrantes\n",
    "geo1.loc[((geo1.geolocation_lng > -28) | (geo1.geolocation_lng < -75)\n",
    "          | (geo1.geolocation_lat > 5) | (geo1.geolocation_lat < -34)),\n",
    "         ['geolocation_lat', 'geolocation_lng']] = np.nan\n",
    "geo1.loc[(geo.geolocation_city == 'são paulo'),\n",
    "         ['geolocation_city']] = 'sao paulo'\n",
    "\n",
    "datanull = 1-(geo1.isnull().sum()/len(geo1.index))\n",
    "plt.figure(figsize=(10, 2))\n",
    "ax = datanull.plot.barh(\n",
    "    color=\"#8d19a9\", title='Pourcentage de données par variable filtrée',\n",
    "    xlabel='pourcentage de données')\n",
    "ax.grid(zorder=0)\n",
    "plt.show()\n",
    "\n",
    "display(datanull)\n",
    "\n",
    "brazil_img = img.imread('input/brazil.png')\n",
    "\n",
    "###################################################\n",
    "geo1a = geo1.geolocation_city.value_counts()\n",
    "geo1aa = geo1.groupby(by=\"geolocation_city\").agg(\n",
    "    {'geolocation_lat': ['mean'], 'geolocation_lng': ['mean']})\n",
    "geo1aa.columns = geo1aa.columns.droplevel(0)\n",
    "geo1aa.columns = ['lat', 'lng']\n",
    "geo1aaa = geo1aa.join(pd.DataFrame(geo1a))\n",
    "\n",
    "geo1aaa.columns = ['lat', 'lng', 'nb']\n",
    "geo1aaa['pct'] = geo1aaa.nb/geo1aaa.nb.sum()\n",
    "geo1aaa = geo1aaa.sort_values('nb', ascending=False).head(10)\n",
    "\n",
    "\n",
    "geo1b = geo1.geolocation_state.value_counts()\n",
    "geo1bb = geo1.groupby(by=\"geolocation_state\").agg(\n",
    "    {'geolocation_lat': ['mean'], 'geolocation_lng': ['mean']})\n",
    "geo1bb.columns = geo1bb.columns.droplevel(0)\n",
    "geo1bb.columns = ['lat', 'lng']\n",
    "geo1bbb = geo1bb.join(pd.DataFrame(geo1b))\n",
    "\n",
    "geo1bbb.columns = ['lat', 'lng', 'nb']\n",
    "geo1bbb['pct'] = geo1bbb.nb/geo1bbb.nb.sum()\n",
    "geo1bbb = geo1bbb.sort_values('nb', ascending=False).head(10)\n",
    "\n",
    "plt.rcParams[\"figure.figsize\"] = [16, 6]\n",
    "plt.rcParams[\"figure.autolayout\"] = True\n",
    "\n",
    "f, axes = plt.subplots(1, 3)\n",
    "sns.scatterplot(data=geo1, x='geolocation_lng',\n",
    "                y='geolocation_lat', size=0.5, alpha=0.5, ax=axes[0])\n",
    "sns.scatterplot(data=geo1aaa, x=\"lng\", y=\"lat\", size=\"pct\",\n",
    "                hue=\"pct\", sizes=(40, 400), alpha=1, ax=axes[1])\n",
    "sns.scatterplot(data=geo1bbb, x=\"lng\", y=\"lat\", size=\"pct\",\n",
    "                hue=\"pct\", sizes=(40, 400), alpha=1, ax=axes[2])\n",
    "axes[0].set_title('base geo par zip')\n",
    "axes[1].set_title('base geo pour les 10 villes les plus rep')\n",
    "axes[2].set_title('base geo pour les 10 etats les plus rep')\n",
    "im0 = axes[0].imshow(brazil_img, extent=[-75, -28, -34, 5],\n",
    "                     alpha=0.8, aspect='auto')\n",
    "im1 = axes[1].imshow(brazil_img, extent=[-75, -28, -34, 5],\n",
    "                     alpha=0.8, aspect='auto')\n",
    "im2 = axes[2].imshow(brazil_img, extent=[-75, -28, -34, 5],\n",
    "                     alpha=0.8, aspect='auto')\n",
    "plt.show()\n",
    "\n",
    "#################################################\n",
    "f, axes = plt.subplots(1, 2)\n",
    "\n",
    "label1 = geo1aaa.sort_values('nb', ascending=False).head(10).index\n",
    "size1 = geo1aaa.sort_values('nb', ascending=False).head(10)['pct']\n",
    "\n",
    "axes[0].pie(size1, labels=label1, autopct='%1.1f%%',\n",
    "            shadow=True, startangle=90, normalize=False)\n",
    "# explode=explode,\n",
    "# Equal aspect ratio ensures that pie is drawn as a circle.\n",
    "axes[0].axis('equal')\n",
    "axes[0].set_title('distribution des villes')\n",
    "\n",
    "label2 = geo1bbb.sort_values('nb', ascending=False).head(10).index\n",
    "size2 = geo1bbb.sort_values('nb', ascending=False).head(10)['pct']\n",
    "\n",
    "axes[1].pie(size2, labels=label2, autopct='%1.1f%%',\n",
    "            shadow=True, startangle=90, normalize=False)\n",
    "# explode=explode,\n",
    "# Equal aspect ratio ensures that pie is drawn as a circle.\n",
    "axes[1].axis('equal')\n",
    "axes[1].set_title('distribution des etats')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9bc5bc9",
   "metadata": {},
   "source": [
    "# fusion totale\n",
    "* note pour plus tard , on ne peut garder que les delivred qui represente 97%\n",
    "* en general quand il y a plus de 1 sequence de paiement : on a affaire à du voucher mais ca reste rare et les autres cas sont anecdotiques "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6e561f6",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# il peut y avoir plusieurs echanges pour les review : on garde la finale\n",
    "# display(order_review.loc[order_review.order_id=='8e17072ec97ce29f0e1f111e598b0c85'])\n",
    "\n",
    "order_review = pd.read_csv('input/archive/olist_order_reviews_dataset.csv',\n",
    "                           delimiter=',',\n",
    "                           error_bad_lines=False,\n",
    "                           low_memory=False)\n",
    "order_review1 = doublonrem(order_review, 'order_review', 'order_id')\n",
    "full1 = ordercust2.set_index('order_id').join(\n",
    "    order_review1.set_index('order_id'))\n",
    "\n",
    "# nettoyage\n",
    "# *le *1 permet de transformer le boolean en 0/1\n",
    "full1['rev_title_flag'] = full1.review_comment_title.notna()*1\n",
    "full1['rev_comment_flag'] = full1.review_comment_message.notna()*1\n",
    "\n",
    "#################################################\n",
    "\n",
    "h1 = full1.review_score.value_counts(normalize=True, dropna=False)\n",
    "h2 = full1.rev_title_flag.value_counts(normalize=True, dropna=False)\n",
    "h3 = full1.rev_comment_flag.value_counts(normalize=True, dropna=False)\n",
    "display(full1.order_status.value_counts(normalize=True, dropna=False))\n",
    "\n",
    "f, axes = plt.subplots(1, 3)\n",
    "axes[0].pie(h1, labels=h1.index, autopct='%.0f%%', normalize=False)\n",
    "axes[1].pie(h2, labels=h2.index, autopct='%.0f%%', normalize=False)\n",
    "axes[2].pie(h3, labels=h3.index, autopct='%.0f%%', normalize=False)\n",
    "\n",
    "axes[0].set_title('distribution des scores review')\n",
    "axes[1].set_title(\"Presence d'un titre de review\")\n",
    "axes[2].set_title(\"Presence d'un commentaire de review\")\n",
    "\n",
    "plt.show()\n",
    "\n",
    "f, axes = plt.subplots(1, 3)\n",
    "sns.boxplot(x='review_score', y='rev_title_flag', data=full1,\n",
    "            showmeans=True, showfliers=False, ax=axes[0])\n",
    "sns.boxplot(x='review_score', y='rev_comment_flag',\n",
    "            data=full1,\n",
    "            showmeans=True,\n",
    "            showfliers=False,\n",
    "            meanprops={\"marker\": \"o\",\n",
    "                       \"markerfacecolor\": \"white\",\n",
    "                       \"markeredgecolor\": \"black\",\n",
    "                       \"markersize\": \"10\"}, ax=axes[1])\n",
    "sns.boxplot(x='order_status', y='rev_comment_flag',\n",
    "            data=full1,\n",
    "            showmeans=True,\n",
    "            showfliers=False,\n",
    "            meanprops={\"marker\": \"o\",\n",
    "                       \"markerfacecolor\": \"white\",\n",
    "                       \"markeredgecolor\": \"black\",\n",
    "                       \"markersize\": \"10\"}, ax=axes[2])\n",
    "axes[0].set_title(\"titre de review moyen par score review\")\n",
    "axes[1].set_title(\"commentaire de review moyen par score review\")\n",
    "axes[2].set_title(\"commentaire de review moyen par order_status\")\n",
    "axes[2].set_xticklabels(axes[2].get_xticklabels(), rotation=45)\n",
    "plt.show\n",
    "\n",
    "#################################################\n",
    "\n",
    "listodr = ['review_id', 'review_creation_date', 'review_answer_timestamp',\n",
    "           'review_comment_title', 'review_comment_message', 'rev_title_flag',\n",
    "           'order_approved_at', 'order_delivered_carrier_date',\n",
    "           'order_delivered_customer_date', 'order_estimated_delivery_date',\n",
    "           'pay_ins_min', 'pay_ins_mean', 'pay_val_min', 'pay_val_max',\n",
    "           'voucher']\n",
    "full1 = full1.drop(columns=listodr)\n",
    "\n",
    "pd.set_option('max_columns', None)\n",
    "display(full1)\n",
    "pd.set_option('max_columns', 10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0e6d18b",
   "metadata": {},
   "source": [
    "* 90% des commandes ne portent que sur 1 seul article\n",
    "* 7.5% des commandes portant sur 2 ou plus articles portent sur le meme article\n",
    "* 9.5% des commandes portant sur 2 ou plus articles portent sur la meme categorie\n",
    "* on peut se debarasser des commandes multiples à categorie differente"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9bc1048b",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# volume pour synthetiser les dimensions\n",
    "product5['product_vol_m3'] = product5['product_length_cm'] * \\\n",
    "    product5['product_height_cm']*product5['product_width_cm']*1e-06\n",
    "\n",
    "\n",
    "pd.set_option('max_columns', None)\n",
    "display(product5.loc[product5.order_id == 'e332eda82fe7ca4e86daf4f71ca785df'])\n",
    "display(product5.loc[product5.order_id == '0097f0545a302aafa32782f1734ff71c'])\n",
    "display(product5.loc[product5.order_id == '00bcee890eba57a9767c7b5ca12d3a1b'])\n",
    "pd.set_option('max_columns', 10)\n",
    "\n",
    "# aggregation par order_id\n",
    "product5['lcat'] = product5.product_category_name_english.str.len()\n",
    "prodagg = product5.groupby(by=\"order_id\").agg({'order_item_id': ['count', 'max'],\n",
    "                                               'price': ['sum', 'mean', 'std'],\n",
    "                                              'freight_value': ['sum', 'mean'],\n",
    "                                               'product_weight_g': ['sum', 'mean'],\n",
    "                                               'lcat': ['std']})\n",
    "\n",
    "prodagg.columns = prodagg.columns.droplevel(0)\n",
    "prodagg.columns = ['it_count', 'it_max', 'pr_sum', 'pr_mean',\n",
    "                   'pr_std', 'fdp_sum', 'fdp_mean', 'poi_sum', 'poi_mean', 'lcat_std']\n",
    "\n",
    "# vision du pourcentage de commandes par nombre d'articles\n",
    "p1 = pd.DataFrame(prodagg.it_count.value_counts(normalize=True, dropna=False))\n",
    "p1.columns = ['pct']\n",
    "p2 = p1.loc[p1.index <= 3, :]\n",
    "# display(p2)\n",
    "\n",
    "f, axes = plt.subplots(1, 2)\n",
    "axes[0].pie(p2.pct, labels=p2.index, autopct='%.0f%%', normalize=False)\n",
    "axes[0].set_title(\"distribution des commandes multiples (nb d'articles)\")\n",
    "\n",
    "sns.boxplot(x='it_count', y='pr_sum', data=prodagg.loc[prodagg.it_count <= 3],\n",
    "            showmeans=True,\n",
    "            showfliers=False,\n",
    "            meanprops={\"marker\": \"o\",\n",
    "                       \"markerfacecolor\": \"white\",\n",
    "                       \"markeredgecolor\": \"black\",\n",
    "                       \"markersize\": \"10\"}, ax=axes[1])\n",
    "axes[1].set_title(\"panier moyen en fonciton du nombre d'article par commande\")\n",
    "\n",
    "#####################################################\n",
    "# vision des commmandes à articles differents ('variation de prix des articles')\n",
    "display(prodagg.pr_std.value_counts(normalize=True, dropna=False))\n",
    "# vision des commmandes à catégories differentes\n",
    "#('variation du nombre de lettre des catégories')\n",
    "display(prodagg.lcat_std.value_counts(normalize=True, dropna=False))\n",
    "\n",
    "#####################################################\n",
    "# on retire les dimensions car resumées en volume\n",
    "product6 = product5.copy()\n",
    "list2dr = ['product_length_cm', 'product_height_cm', 'product_width_cm']\n",
    "product6 = product6.drop(columns=list2dr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3f66533",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "from scipy.cluster.hierarchy import dendrogram, linkage\n",
    "from matplotlib import pyplot as plt\n",
    "import numpy as np\n",
    "from scipy.stats import normaltest\n",
    "from scipy.stats import shapiro\n",
    "from scipy.stats import spearmanr\n",
    "from scipy.stats import pearsonr\n",
    "from sklearn.preprocessing import MinMaxScaler\n",
    "product6 = product5.copy()\n",
    "prodagg2 = product6.groupby(by=\"product_category_name_english\").agg({\n",
    "    'price': ['count', 'median', 'std'],\n",
    "    'freight_value': ['median', 'std'],\n",
    "    'product_weight_g': ['median', 'std'],\n",
    "    'product_length_cm': ['median', 'std'],\n",
    "    'product_height_cm': ['median', 'std'],\n",
    "    'product_width_cm': ['median', 'std'],\n",
    "    'product_vol_m3': ['median', 'std']})\n",
    "prodagg2.columns = prodagg2.columns.droplevel(0)\n",
    "prodagg2.columns = ['count', 'pr', 'pr_std', 'fdp', 'fdp_std', 'poi',\n",
    "                    'poi_std', 'l', 'l_std', 'h', 'h_std', 'w', 'w_std',\n",
    "                    'vol', 'vol_std']\n",
    "\n",
    "# normalisation de l'erreur\n",
    "# display(prodagg2)\n",
    "prodagg2.pr_std = prodagg2.pr_std/prodagg2.pr\n",
    "prodagg2.vol_std = prodagg2.vol_std/prodagg2.vol\n",
    "# display(prodagg2)\n",
    "\n",
    "prodagg3 = prodagg2[['count', 'pr', 'pr_std',\n",
    "                     'fdp', 'poi', 'vol', 'vol_std']].copy()\n",
    "pd.set_option('max_rows', None)\n",
    "# display(prodagg3)\n",
    "pd.set_option('max_rows', 10)\n",
    "\n",
    "# Tansformation MINMAX scaler du jeu pour standardiser les données\n",
    "# on utlise MINMAX car bcp de 0 et de 1\n",
    "scaler = MinMaxScaler()\n",
    "scaler.fit(prodagg3)\n",
    "\n",
    "prodagg3_MM = pd.DataFrame(scaler.transform(\n",
    "    prodagg3), columns=prodagg3.columns, index=prodagg3.index)\n",
    "# display(prodagg3_MM)\n",
    "\n",
    "#################################\n",
    "# normalité\n",
    "\n",
    "# https://towardsdatascience.com/pearson-coefficient-of-correlation-explained-369991d93404\n",
    "\n",
    "\n",
    "def calculate_corr(df):\n",
    "    \"\"\"calcul des correlation.\"\"\"\n",
    "\n",
    "    df = df.dropna()._get_numeric_data()\n",
    "    dfcols = pd.DataFrame(columns=df.columns)\n",
    "    pearscoeff = dfcols.transpose().join(dfcols, how='outer')\n",
    "    pearspvalues = pearscoeff.copy()\n",
    "    shappvalues = pearscoeff.copy()\n",
    "    normpvalues = pearscoeff.copy()\n",
    "    spearcoeff = pearscoeff.copy()\n",
    "    spearpvalues = pearscoeff.copy()\n",
    "    for r in df.columns:\n",
    "        for c in df.columns:\n",
    "            pearscoeff[r][c] = round(pearsonr(df[r], df[c])[0], 4)\n",
    "            pearspvalues[r][c] = round(pearsonr(df[r], df[c])[1], 4)\n",
    "            spearcoeff[r][c] = round(spearmanr(df[r], df[c])[0], 4)\n",
    "            spearpvalues[r][c] = round(spearmanr(df[r], df[c])[1], 4)\n",
    "            if r == c:\n",
    "                # attention pvalue shapiro wilk seulement pour N<5000\n",
    "                shappvalues[r][c] = round(shapiro(df[r])[1], 4)\n",
    "                # d'agostino basé sur omnibus plus adapté pour\n",
    "                # les grands echantillons\n",
    "                normpvalues[r][c] = round(normaltest(df[r])[1], 4)\n",
    "    pearscoeff[df.columns] = pearscoeff[df.columns].astype(float)\n",
    "    pearspvalues[df.columns] = pearspvalues[df.columns].astype(float)\n",
    "    shappvalues[df.columns] = shappvalues[df.columns].astype(float)\n",
    "    normpvalues[df.columns] = normpvalues[df.columns].astype(float)\n",
    "    spearcoeff[df.columns] = spearcoeff[df.columns].astype(float)\n",
    "    spearpvalues[df.columns] = spearpvalues[df.columns].astype(float)\n",
    "    return pearscoeff, pearspvalues, shappvalues,\\\n",
    "        normpvalues, spearcoeff, spearpvalues\n",
    "\n",
    "\n",
    "cocorr, pvcorr, shap, dagos, cospcorr, pvspcorr = calculate_corr(prodagg3_MM)\n",
    "\n",
    "plt.rcParams[\"figure.figsize\"] = [16, 6]\n",
    "f, axes = plt.subplots(1, 2)\n",
    "\n",
    "sns.heatmap(\n",
    "    cocorr,\n",
    "    vmin=-1, vmax=1, center=0,\n",
    "    cmap=sns.diverging_palette(20, 220, n=200),\n",
    "    square=True,\n",
    "    annot=True,\n",
    "    fmt='.2f',\n",
    "    linewidths=.5, ax=axes[0])\n",
    "axes[0].title.set_text('Coefficient correlation de Pearson')\n",
    "\n",
    "sns.heatmap(\n",
    "    pvcorr,\n",
    "    vmin=0, vmax=1, center=0.5,\n",
    "    cmap=sns.diverging_palette(200, 20, n=200),\n",
    "    square=True,\n",
    "    annot=True,\n",
    "    fmt='.2f',\n",
    "    linewidths=.5, ax=axes[1])\n",
    "axes[1].title.set_text('Pvalue correlation de Pearson')\n",
    "\n",
    "plt.show()\n",
    "\n",
    "\n",
    "# scipy.cluster.hierarchy.dendrogram(\n",
    "# Z, (linkage matrix)\n",
    "# p=30, (p parameter for truncate_mode)\n",
    "# truncate_mode=None, (Truncation is used to condense the dendrogram)\n",
    "#defaut: none\n",
    "# lastp\n",
    "# level!No more than p levels of the dendrogram tree are displayed\n",
    "# color_threshold=None,\n",
    "# get_leaves=True,\n",
    "#orientation='top', bottom, left, right\n",
    "# labels=None,\n",
    "# default, labels is None so the index of the original observation\n",
    "# is used to label the leaf nodes\n",
    "# labels[i] value is the text to put under the th leaf node only if\n",
    "# it corresponds to an original observation\n",
    "# count_sort=False,\n",
    "# distance_sort=False,\n",
    "# show_leaf_counts=True,\n",
    "# no_plot=False,\n",
    "# no_labels=False,\n",
    "# leaf_font_size=None,\n",
    "# leaf_rotation=None,\n",
    "# leaf_label_func=None,\n",
    "# show_contracted=False,\n",
    "# link_color_func=None,\n",
    "# ax=None,\n",
    "# above_threshold_color='C0')\n",
    "\n",
    "Z1 = linkage(prodagg3_MM.loc[:, ['pr', 'vol']], 'ward')\n",
    "Z2 = linkage(prodagg3_MM.loc[:, ['pr', 'vol', 'pr_std', 'vol_std']], 'ward')\n",
    "Z3 = linkage(prodagg3_MM.loc[:, ['count', 'pr', 'vol']], 'ward')\n",
    "Z4 = linkage(\n",
    "    prodagg3_MM.loc[:, ['count', 'pr', 'vol', 'pr_std', 'vol_std']], 'ward')\n",
    "\n",
    "plt.rcParams[\"figure.figsize\"] = [16, 10]\n",
    "f, axes = plt.subplots(1, 2)\n",
    "dendrogram(Z1, orientation='right', labels=prodagg3_MM.index,\n",
    "           leaf_font_size=10, ax=axes[0])\n",
    "dendrogram(Z2, orientation='right', labels=prodagg3_MM.index,\n",
    "           leaf_font_size=10, ax=axes[1])\n",
    "axes[0].set_title(\"HClustering Dendrogram: features 'pr','vol'\")\n",
    "axes[1].set_title(\n",
    "    \"HClustering Dendrogram: features 'pr','vol','pr_std','vol_std'\")\n",
    "plt.show()\n",
    "f, axes = plt.subplots(1, 2)\n",
    "dendrogram(Z3, orientation='right', labels=prodagg3_MM.index,\n",
    "           leaf_font_size=10, ax=axes[0])\n",
    "dendrogram(Z4, orientation='right', labels=prodagg3_MM.index,\n",
    "           leaf_font_size=10, ax=axes[1])\n",
    "axes[0].set_title(\"HClustering Dendrogram: features 'count','pr','vol'\")\n",
    "axes[1].set_title(\n",
    "    \"HClustering Dendrogram: features 'count','pr','vol','pr_std','vol_std'\")\n",
    "plt.show()\n",
    "\n",
    "#######################################################"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "835fc887",
   "metadata": {},
   "outputs": [],
   "source": [
    "# cluster2\n",
    "indA2 = ['computers', 'furniture_bedroom',\n",
    "         'furniture_mattress_and_upholstery', 'home_appliances_2',\n",
    "         'industry_commerce_and_business',\n",
    "         'office_furniture', 'small_appliances_home_oven_and_coffee']\n",
    "\n",
    "indB2 = ['agro_industry_and_commerce', 'air_conditioning',\n",
    "         'art', 'arts_and_craftmanship', 'audio',\n",
    "         'bed_bath_table', 'books_general_interest', 'books_imported',\n",
    "         'books_technical', 'cds_dvds_musicals',\n",
    "         'christmas_supplies', 'computers_accessories',\n",
    "         'construction_tools_safety',\n",
    "         'cool_stuff', 'costruction_tools_garden',\n",
    "         'diapers_and_hygiene', 'drinks', 'fashion_childrens_clothes',\n",
    "         'fashion_male_clothing', 'fashion_shoes',\n",
    "         'fashion_underwear_beach', 'flowers', 'food', 'food_drink',\n",
    "         'furniture_decor', 'furniture_living_room', 'garden_tools',\n",
    "         'home_comfort_2', 'home_confort', 'housewares',\n",
    "         'kitchen_dining_laundry_garden_furniture', 'la_cuisine',\n",
    "         'luggage_accessories', 'perfumery', 'pet_shop',\n",
    "         'security_and_services', 'stationery', 'tablets_printing_image',\n",
    "         'toys', 'watches_gifts']\n",
    "\n",
    "indC2 = ['auto', 'baby', 'cine_photo', 'consoles_games',\n",
    "         'construction_tools_construction',\n",
    "         'construction_tools_lights',\n",
    "         'costruction_tools_tools', 'dvds_blu_ray', 'electronics',\n",
    "         'fashio_female_clothing',\n",
    "         'fashion_bags_accessories',\n",
    "         'fashion_sport', 'fixed_telephony', 'health_beauty',\n",
    "         'home_appliances',\n",
    "         'home_construction', 'market_place',\n",
    "         'music', 'musical_instruments', 'party_supplies',\n",
    "         'signaling_and_security',\n",
    "         'small_appliances', 'sports_leisure', 'telephony']\n",
    "\n",
    "prodagg3.loc[indA2, ['GR2']] = 'A'\n",
    "prodagg3.loc[indB2, ['GR2']] = 'B'\n",
    "prodagg3.loc[indC2, ['GR2']] = 'C'\n",
    "\n",
    "prodagg3_GR2 = prodagg3.groupby(by=\"GR2\").agg({'count': ['mean'],\n",
    "                                              'pr': ['median'],\n",
    "                                               'pr_std': ['median'],\n",
    "                                               'vol': ['median'],\n",
    "                                               'vol_std': ['median']})\n",
    "prodagg3_GR2.columns = prodagg3_GR2.columns.droplevel(0)\n",
    "prodagg3_GR2.columns = ['count', 'pr', 'pr_std', 'vol', 'vol_std']\n",
    "display(prodagg3_GR2)\n",
    "\n",
    "# cluster4\n",
    "indA4 = ['arts_and_craftmanship', 'audio', 'auto', 'baby',\n",
    "         'books_general_interest',\n",
    "         'books_imported', 'books_technical',\n",
    "         'cds_dvds_musicals', 'christmas_supplies', 'consoles_games',\n",
    "         'construction_tools_construction',\n",
    "         'construction_tools_safety', 'costruction_tools_garden',\n",
    "         'cool_stuff',\n",
    "         'costruction_tools_tools',\n",
    "         'diapers_and_hygiene', 'drinks', 'electronics',\n",
    "         'fashion_childrens_clothes',\n",
    "         'fashion_male_clothing',\n",
    "         'fashion_shoes', 'fashion_underwear_beach', 'fixed_telephony',\n",
    "         'flowers',\n",
    "         'food', 'food_drink', 'garden_tools',\n",
    "         'home_appliances', 'home_construction', 'market_place', 'music',\n",
    "         'musical_instruments',\n",
    "         'party_supplies', 'perfumery',\n",
    "         'pet_shop', 'security_and_services', 'small_appliances',\n",
    "         'stationery',\n",
    "         'tablets_printing_image', 'telephony', 'toys']\n",
    "\n",
    "indB4 = ['cine_photo', 'construction_tools_lights', 'dvds_blu_ray',\n",
    "         'fashio_female_clothing',\n",
    "         'fashion_bags_accessories',\n",
    "         'fashion_sport', 'signaling_and_security']\n",
    "\n",
    "indC4 = ['agro_industry_and_commerce', 'air_conditioning', 'art',\n",
    "         'computers', 'furniture_bedroom',\n",
    "         'furniture_living_room',\n",
    "         'furniture_mattress_and_upholstery', 'home_appliances_2',\n",
    "         'home_comfort_2', 'home_confort',\n",
    "         'industry_commerce_and_business',\n",
    "         'kitchen_dining_laundry_garden_furniture', 'la_cuisine',\n",
    "         'luggage_accessories', 'office_furniture',\n",
    "         'small_appliances_home_oven_and_coffee']\n",
    "\n",
    "indD4 = ['bed_bath_table', 'computers_accessories',\n",
    "         'furniture_decor', 'health_beauty',\n",
    "         'housewares', 'sports_leisure',\n",
    "         'watches_gifts']\n",
    "\n",
    "prodagg3.loc[indA4, ['GR4']] = 'A'\n",
    "prodagg3.loc[indB4, ['GR4']] = 'B'\n",
    "prodagg3.loc[indC4, ['GR4']] = 'C'\n",
    "prodagg3.loc[indD4, ['GR4']] = 'D'\n",
    "\n",
    "prodagg3_GR4 = prodagg3.groupby(by=\"GR4\").agg({'count': ['mean'],\n",
    "                                              'pr': ['median'],\n",
    "                                               'pr_std': ['median'],\n",
    "                                               'vol': ['median'],\n",
    "                                               'vol_std': ['median']})\n",
    "prodagg3_GR4.columns = prodagg3_GR4.columns.droplevel(0)\n",
    "prodagg3_GR4.columns = ['count', 'pr', 'pr_std', 'vol', 'vol_std']\n",
    "display(prodagg3_GR4)\n",
    "\n",
    "plt.rcParams[\"figure.figsize\"] = [16, 6]\n",
    "plt.rcParams[\"figure.autolayout\"] = True\n",
    "\n",
    "f, axes = plt.subplots(1, 2)\n",
    "sns.scatterplot(data=prodagg3, x=\"vol\", y=\"pr\", hue='GR2', ax=axes[0])\n",
    "sns.scatterplot(data=prodagg3, x=\"vol_std\", y=\"pr_std\", hue='GR2', ax=axes[1])\n",
    "axes[0].set_title('product_category par prix/volume et par cluster GR2')\n",
    "axes[1].set_title(\n",
    "    'product_category par prix std/volume std et par cluster GR2')\n",
    "plt.show()\n",
    "\n",
    "f, axes = plt.subplots(1, 2)\n",
    "sns.scatterplot(data=prodagg3, x=\"vol\", y=\"pr\", hue='GR4', ax=axes[0])\n",
    "sns.scatterplot(data=prodagg3, x=\"count\", y=\"pr\", hue='GR4', ax=axes[1])\n",
    "axes[0].set_title('product_category par prix/volume et par cluster GR4')\n",
    "axes[1].set_title(\n",
    "    'product_category par prix std/volume std et par cluster GR4')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c033945",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('max_columns', None)\n",
    "display(product5)\n",
    "display(product5.loc[product5.order_item_id == 6])\n",
    "display(product5.loc[product5.order_id == 'ab14fdcfbe524636d65ee38360e22ce8'])\n",
    "pd.set_option('max_columns', 10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7420fde6",
   "metadata": {},
   "source": [
    "# cas d'une commande à plusieurs articles et un paiement credit en 4 fois"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d80e3838",
   "metadata": {},
   "outputs": [],
   "source": [
    "display(order_payment)\n",
    "display(order.loc[order.order_id == 'ab14fdcfbe524636d65ee38360e22ce8'])\n",
    "display(order_item.loc[order_item.order_id ==\n",
    "        'ab14fdcfbe524636d65ee38360e22ce8'])\n",
    "display(product.loc[product.product_id == '9571759451b1d780ee7c15012ea109d4'])\n",
    "display(order_payment.loc[order_payment.order_id ==\n",
    "        'ab14fdcfbe524636d65ee38360e22ce8'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5218d042",
   "metadata": {},
   "source": [
    "# cas d'une commande àvec sequence >1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1bb21cee",
   "metadata": {},
   "outputs": [],
   "source": [
    "pay1.loc[pay1.pay_seq_count > 1]\n",
    "# 009ac365164f8e06f59d18a08045f6c4\n",
    "# 0016dfedd97fc2950e388d2971d718c7\n",
    "\n",
    "display(order.loc[order.order_id == '009ac365164f8e06f59d18a08045f6c4'])\n",
    "display(order_item.loc[order_item.order_id ==\n",
    "        '009ac365164f8e06f59d18a08045f6c4'])\n",
    "display(product.loc[product.product_id == '35557c68a22ecebcf066e25ca2ddc144'])\n",
    "display(order_payment.loc[order_payment.order_id ==\n",
    "        '009ac365164f8e06f59d18a08045f6c4'])\n",
    "display(order_payment.loc[order_payment.order_id ==\n",
    "        '009ac365164f8e06f59d18a08045f6c4', ['payment_value']].sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7738b75d",
   "metadata": {},
   "outputs": [],
   "source": [
    "display(order_payment.loc[order_payment.payment_sequential > 1].value_counts(\n",
    "    'payment_type'))\n",
    "display(order_payment.loc[(order_payment.payment_sequential > 1) & (\n",
    "    order_payment.payment_type == 'debit_card')])\n",
    "display(order_payment.loc[order_payment.order_id ==\n",
    "        'a4431cbd79dbddaae7988ce6091cbc3c'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "796a195a",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}