EDA_notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Author**: [Alex FitzGerald](https://www.linkedin.com/in/alex-fitzgerald-0734076a/)\n",
    "\n",
    "![windjammer header](visuals/windjammer_logo.jpg)\n",
    "\n",
    "## Overview\n",
    "This project creates a user-friendly customer lifetime value (CLV) prediction engine able to take in transaction data and return important CLV predictions with a high degree of accuracy for a merchant's entire customer base and individual customers over a selected period of time in the future. The data processing and modeling techniques were validated on a publicly available e-commerce data set from Olist but the app is flexible enough to re-process and model unstructured transaction data from any non-contractual business with continuous purchasing behavior (ex. grocery purchases, e-commerce, movie rentals).\n",
    "\n",
    "## Business Problem\n",
    "How much are your customers worth? CLV is a north star metric for marketers and key to many business analyses; sales/marketing activity ROI, sales forecasts, company valuation, churn mitigation tactics, and customer persona analysis. \n",
    "\n",
    "Olist, the largest e-commerce department store in Brazil, wants to help the small business merchants who sell goods through their marketplace understand their CLV. Olist makes their money taking a commission on orders placed on their marketplace. They want to help their merchants sell more products and retain those merchants on their marketplace by providing top-notch business analytics.\n",
    "\n",
    "Olist has decided to create a user friendly app to help their merchants better understand the customers who buy their products through the Olist marketplace. Empowering their merchants with CLV analysis will help merchants improve their strategy and increase sales and commissions through Olist. The addition of a customer insight tool will also improve the retention of merchants on the Olist network. \n",
    "\n",
    "To accomplish this project, Olist has hired Windjammer Consulting, a data-science consulting firm.\n",
    "\n",
    "To test out the app for accuracy, Olist has provided a full transaction data set of purchases from all vendors and asked Windjammer Consulting to return CLV predictions with minimal error (RMSE).\n",
    "If the model passes muster, they want Windjammer Consulting to create an application that allows merchants to upload their own data and retrieve predictions.\n",
    "\n",
    "## EDA Notebook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exploratory Data Analysis\n",
    "1. Data base exploration\n",
    "2. Inital observations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Data base exploration\n",
    "To create a CLV, model we need a customer identifier, purchase timestamp, and purchase value. The data base contains more information than we need so I'm only going to open these three files for further exploration. \n",
    "- olist_customers_dataset\n",
    "    - This dataset has information about the customer and its location. Use it to identify unique customers in the orders dataset.\n",
    "- olist_order_items_dataset\n",
    "    - d\n",
    "- olist_order_payments_dataset\n",
    "    - This dataset includes data about the orders payment options."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data base files\n",
      " \n",
      "olist_customers_dataset.csv\n",
      "olist_geolocation_dataset.csv\n",
      "olist_order_items_dataset.csv\n",
      "olist_order_payments_dataset.csv\n",
      "olist_order_reviews_dataset.csv\n",
      "olist_orders_dataset.csv\n",
      "olist_products_dataset.csv\n",
      "olist_sellers_dataset.csv\n",
      "product_category_name_translation.csv\n"
     ]
    }
   ],
   "source": [
    "#import zipfile to deal with zipped data\n",
    "from zipfile import ZipFile\n",
    "# Create iterate over our files in the zip file and print their names\n",
    "print(\"Data base files\")\n",
    "print(\" \")\n",
    "with ZipFile('data/customer_data_base.zip', 'r') as zipObj:\n",
    "   # Get list of files names in zip\n",
    "   listOfiles = zipObj.namelist()\n",
    "   # Iterate over the list of file names in given list & print them\n",
    "   for elem in listOfiles:\n",
    "       print(elem)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Inital imports\n",
    "%matplotlib inline\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import lifetimes\n",
    "from zipfile import ZipFile\n",
    "import random"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Load in our data\n",
    "# open zipped dataset and save to df's\n",
    "with ZipFile(\"data/customer_data_base.zip\") as z:\n",
    "   # open the orders csv file in the dataset\n",
    "   with z.open(\"olist_orders_dataset.csv\") as a:    \n",
    "      # read the dataset as dataframe \"df\"\n",
    "      orders_df = pd.read_csv(a)\n",
    "        \n",
    "   # open the customers csv file in the dataset\n",
    "   with z.open(\"olist_customers_dataset.csv\") as b:    \n",
    "      # read the dataset as dataframe \"df\"\n",
    "      customers_df = pd.read_csv(b)     \n",
    "        \n",
    "   # open the customers csv file in the dataset\n",
    "   with z.open(\"olist_order_payments_dataset.csv\") as c:    \n",
    "      # read the dataset as dataframe \"df\"\n",
    "      payments_df = pd.read_csv(c)     \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Data_base_schema](visuals/data_base_schema.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Initial Observations\n",
    "\n",
    "#### customers_dataset\n",
    "- All we need from this dataset is `customer_unique_id` and `customer_id`\t\n",
    "- No null or missing values\n",
    "- We have 96,096 unique customers\n",
    "    - Total unique `customer_id`: 99,441\n",
    "    - Total unique `customer_unique_id`: 96,096\n",
    "- All 27 Brazilian states + federal district (FD) represented\n",
    "- customer_unique_id represents the unique customer identifier\n",
    "- customer_id is a key to the orders dataset and represents a unique order id\n",
    "    - From the data providers, \"At our system each order is assigned to a unique customerid.\"\n",
    "\n",
    "#### olist_order_items_dataset\n",
    "- From this dataset we need; `order_purchase_timestamp`, `order_id`, `customer_id`\n",
    "- `unique order_ids`: 99,441\n",
    "- `unique customer_ids`: 99,441\n",
    "- `order_purchase_timestamp` units of time is in days\n",
    "- Dates range 773 days, from 2016-09-04 to 2018-10-17\n",
    "- We don't have many observations early in our data set, 2017-04 we get more data\n",
    "- we see the order status's 'canceled' and 'unavailable' causing issues & we may decide to drop these after joining depending on their order's monetary value\n",
    "\n",
    "#### olist_order_payments_dataset\n",
    "- From this dataset we need `payment_value`\n",
    "- Unique `order_ids`: 99,440\n",
    "- Duplicate `order_ids`: 4,446\n",
    "- We have entries with zero `payment_value`\n",
    "- We have right outliers in `payment_value`\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### customers_dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(99441, 5)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#99441 observations\n",
    "customers_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "customer_id                 0\n",
       "customer_unique_id          0\n",
       "customer_zip_code_prefix    0\n",
       "customer_city               0\n",
       "customer_state              0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#We have no null or na values\n",
    "customers_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 99441 entries, 0 to 99440\n",
      "Data columns (total 5 columns):\n",
      " #   Column                    Non-Null Count  Dtype \n",
      "---  ------                    --------------  ----- \n",
      " 0   customer_id               99441 non-null  object\n",
      " 1   customer_unique_id        99441 non-null  object\n",
      " 2   customer_zip_code_prefix  99441 non-null  int64 \n",
      " 3   customer_city             99441 non-null  object\n",
      " 4   customer_state            99441 non-null  object\n",
      "dtypes: int64(1), object(4)\n",
      "memory usage: 3.8+ MB\n"
     ]
    }
   ],
   "source": [
    "#We have mostly strings and an integer for zip code\n",
    "customers_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>customer_zip_code_prefix</th>\n",
       "      <th>customer_city</th>\n",
       "      <th>customer_state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>06b8999e2fba1a1fbc88172c00ba8bc7</td>\n",
       "      <td>861eff4711a542e4b93843c6dd7febb0</td>\n",
       "      <td>14409</td>\n",
       "      <td>franca</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18955e83d337fd6b2def6b18a428ac77</td>\n",
       "      <td>290c77bc529b7ac935b93aa66c333dc3</td>\n",
       "      <td>9790</td>\n",
       "      <td>sao bernardo do campo</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4e7b3e00288586ebd08712fdd0374a03</td>\n",
       "      <td>060e732b5b29e8181a18229c7b0b2b5e</td>\n",
       "      <td>1151</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b2b6027bc5c5109e529d4dc6358b12c3</td>\n",
       "      <td>259dac757896d24d7702b9acbbff3f3c</td>\n",
       "      <td>8775</td>\n",
       "      <td>mogi das cruzes</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4f2d8ab171c80ec8364f7c12e35b23ad</td>\n",
       "      <td>345ecd01c38d18a9036ed96c73b8d066</td>\n",
       "      <td>13056</td>\n",
       "      <td>campinas</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        customer_id                customer_unique_id  \\\n",
       "0  06b8999e2fba1a1fbc88172c00ba8bc7  861eff4711a542e4b93843c6dd7febb0   \n",
       "1  18955e83d337fd6b2def6b18a428ac77  290c77bc529b7ac935b93aa66c333dc3   \n",
       "2  4e7b3e00288586ebd08712fdd0374a03  060e732b5b29e8181a18229c7b0b2b5e   \n",
       "3  b2b6027bc5c5109e529d4dc6358b12c3  259dac757896d24d7702b9acbbff3f3c   \n",
       "4  4f2d8ab171c80ec8364f7c12e35b23ad  345ecd01c38d18a9036ed96c73b8d066   \n",
       "\n",
       "   customer_zip_code_prefix          customer_city customer_state  \n",
       "0                     14409                 franca             SP  \n",
       "1                      9790  sao bernardo do campo             SP  \n",
       "2                      1151              sao paulo             SP  \n",
       "3                      8775        mogi das cruzes             SP  \n",
       "4                     13056               campinas             SP  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Explore head of dataframe\n",
    "#There is a difference between customer_id and customer_unique_id, let's explore further\n",
    "customers_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total unique customer_id: 99441\n",
      "Total unique customer_unique_id: 96096\n"
     ]
    }
   ],
   "source": [
    "#As the name suggests, unique_customer_id is a more unique customer identifier\n",
    "print(f'Total unique customer_id: {len(customers_df[\"customer_id\"].unique())}')\n",
    "print(f'Total unique customer_unique_id: {len(customers_df[\"customer_unique_id\"].unique())}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0348089410589412"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#We have 1.03 unique customer_id per every customer_unique_id\n",
    "len(customers_df[\"customer_id\"].unique())/len(customers_df[\"customer_unique_id\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "27"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#All 27 Brazilian states + federal district (FD) represented\n",
    "len(customers_df[\"customer_state\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SP    41746\n",
       "RJ    12852\n",
       "MG    11635\n",
       "RS     5466\n",
       "PR     5045\n",
       "SC     3637\n",
       "BA     3380\n",
       "DF     2140\n",
       "ES     2033\n",
       "GO     2020\n",
       "PE     1652\n",
       "CE     1336\n",
       "PA      975\n",
       "MT      907\n",
       "MA      747\n",
       "MS      715\n",
       "PB      536\n",
       "PI      495\n",
       "RN      485\n",
       "AL      413\n",
       "SE      350\n",
       "TO      280\n",
       "RO      253\n",
       "AM      148\n",
       "AC       81\n",
       "AP       68\n",
       "RR       46\n",
       "Name: customer_state, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers_df[\"customer_state\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### olist_order_items_dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(99441, 8)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#99441 observations, same as our customers dataset\n",
    "orders_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 99441 entries, 0 to 99440\n",
      "Data columns (total 8 columns):\n",
      " #   Column                         Non-Null Count  Dtype \n",
      "---  ------                         --------------  ----- \n",
      " 0   order_id                       99441 non-null  object\n",
      " 1   customer_id                    99441 non-null  object\n",
      " 2   order_status                   99441 non-null  object\n",
      " 3   order_purchase_timestamp       99441 non-null  object\n",
      " 4   order_approved_at              99281 non-null  object\n",
      " 5   order_delivered_carrier_date   97658 non-null  object\n",
      " 6   order_delivered_customer_date  96476 non-null  object\n",
      " 7   order_estimated_delivery_date  99441 non-null  object\n",
      "dtypes: object(8)\n",
      "memory usage: 6.1+ MB\n"
     ]
    }
   ],
   "source": [
    "#We have only objects. We'll need to translate order_purchase_timestamp to a more useful d type\n",
    "orders_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n",
      "RangeIndex: 99441 entries, 0 to 99440\n",
      "Series name: order_purchase_timestamp\n",
      "Non-Null Count  Dtype \n",
      "--------------  ----- \n",
      "99441 non-null  object\n",
      "dtypes: object(1)\n",
      "memory usage: 777.0+ KB\n"
     ]
    }
   ],
   "source": [
    "#translate order_purchase_timestamp to date time\n",
    "orders_df[\"order_purchase_timestamp\"] = pd.to_datetime(orders_df[\"order_purchase_timestamp\"]).dt.date #normalize()\n",
    "orders_df[\"order_purchase_timestamp\"].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2017-10-02\n",
       "1    2018-07-24\n",
       "2    2018-08-08\n",
       "3    2017-11-18\n",
       "4    2018-02-13\n",
       "Name: order_purchase_timestamp, dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#our units of time is in days\n",
    "orders_df[\"order_purchase_timestamp\"].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " min date: 2016-09-04\n",
      " max date: 2018-10-17\n",
      " range: 773 days, 0:00:00\n"
     ]
    }
   ],
   "source": [
    "print(f' min date: {orders_df[\"order_purchase_timestamp\"].min()}')\n",
    "print(f' max date: {orders_df[\"order_purchase_timestamp\"].max()}')\n",
    "print(f' range: {orders_df[\"order_purchase_timestamp\"].max()-orders_df[\"order_purchase_timestamp\"].min()}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAmMAAAEvCAYAAAAJusb3AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8qNh9FAAAACXBIWXMAAAsTAAALEwEAmpwYAAAZbElEQVR4nO3df7DldX3f8eerbLBqNCxyQ5Af2VUXM+g0q26RtNGxIcKCrWAmtcu0YTXU1SrT2CaTrs0fWA0zmGjs0LE4qFuxY0AUlZ2AwZUx0cwEZFGKgCIXhLCbhV1BRaNFwXf/OJ9bj5d7d5d7zt3Pufc+HzPfud/z/v76vNl7ltd+f5yTqkKSJEl9/KPeA5AkSVrJDGOSJEkdGcYkSZI6MoxJkiR1ZBiTJEnqyDAmSZLU0areA1ioo446qtasWdN7GJIkSQd08803f6uqpuZatmTD2Jo1a9i5c2fvYUiSJB1QkvvmW+ZlSkmSpI4MY5IkSR0ZxiRJkjoyjEmSJHVkGJMkSerIMCZJktSRYUySJKkjw5gkSVJHhjFJkqSODGOSJEkdGcYkSZI6WrLfTSlJWhnWbL2m9xDG5t6LXtV7CJpAnhmTJEnqyDAmSZLUkWFMkiSpI8OYJElSR4YxSZKkjgxjkiRJHRnGJEmSOjKMSZIkdWQYkyRJ6sgwJkmS1JFhTJIkqSPDmCRJUkcHDGNJtiXZm+S2odrHktzSpnuT3NLqa5L8cGjZ+4e2eUmSryaZTnJxkrT6kUl2JLmr/Vy9CH1KkiRNpIM5M/ZhYONwoar+TVWtr6r1wFXAJ4cW3z2zrKreNFS/BHgDsK5NM/vcClxfVeuA69trSZKkFeGAYayqvgA8PNeydnbrtcDl+9tHkmOAZ1bVDVVVwEeAs9vis4DL2vxlQ3VJkqRlb9R7xl4GPFhVdw3V1ib5SpK/TvKyVjsW2DW0zq5WAzi6qva0+QeAo0cckyRJ0pKxasTtz+Fnz4rtAU6oqoeSvAT4dJIXHOzOqqqS1HzLk2wBtgCccMIJCxyyJEnS5FjwmbEkq4DfAj42U6uqR6vqoTZ/M3A3cCKwGzhuaPPjWg3gwXYZc+Zy5t75jllVl1bVhqraMDU1tdChS5IkTYxRLlP+JvD1qvr/lx+TTCU5rM0/h8GN+ve0y5CPJDml3Wd2LnB122w7sLnNbx6qS5IkLXsH89EWlwN/Czw/ya4k57VFm3jijfsvB25tH3XxCeBNVTVz8/+bgQ8C0wzOmH2m1S8CXpnkLgYB76KFtyNJkrS0HPCesao6Z5766+aoXcXgoy7mWn8n8MI56g8Bpx5oHJIkScuRn8AvSZLUkWFMkiSpI8OYJElSR4YxSZKkjgxjkiRJHRnGJEmSOhr165AkSRNqzdZreg9B0kHwzJgkSVJHhjFJkqSODGOSJEkdGcYkSZI6MoxJkiR1ZBiTJEnqyDAmSZLUkWFMkiSpI8OYJElSR4YxSZKkjgxjkiRJHRnGJEmSOjKMSZIkdWQYkyRJ6mhV7wFI0iRZs/Wa3kOQtMJ4ZkySJKkjw5gkSVJHhjFJkqSODhjGkmxLsjfJbUO1tyfZneSWNp05tOxtSaaT3Jnk9KH6xlabTrJ1qL42yY2t/rEkh4+zQUmSpEl2MGfGPgxsnKP+3qpa36ZrAZKcBGwCXtC2+Z9JDktyGPA+4AzgJOCcti7Au9q+ngd8GzhvlIYkSZKWkgOGsar6AvDwQe7vLOCKqnq0qr4JTAMnt2m6qu6pqh8BVwBnJQnwG8An2vaXAWc/uRYkSZKWrlE+2uL8JOcCO4Hfr6pvA8cCNwyts6vVAO6fVX8p8CzgO1X12BzrS5K0rCynj06596JX9R7CsrHQG/gvAZ4LrAf2AO8Z14D2J8mWJDuT7Ny3b9+hOKQkSdKiWlAYq6oHq+rxqvoJ8AEGlyEBdgPHD616XKvNV38IOCLJqln1+Y57aVVtqKoNU1NTCxm6JEnSRFlQGEtyzNDL1wAzT1puBzYleUqStcA64EvATcC69uTk4Qxu8t9eVQV8Hvjttv1m4OqFjEmSJGkpOuA9Y0kuB14BHJVkF3AB8Iok64EC7gXeCFBVtye5ErgDeAx4S1U93vZzPnAdcBiwrapub4f4L8AVSf4Y+ArwoXE1J0mSNOkOGMaq6pw5yvMGpqq6ELhwjvq1wLVz1O/hp5c5JUmSVhQ/gV+SJKkjw5gkSVJHhjFJkqSODGOSJEkdGcYkSZI6MoxJkiR1ZBiTJEnqyDAmSZLUkWFMkiSpI8OYJElSR4YxSZKkjgxjkiRJHRnGJEmSOjKMSZIkdWQYkyRJ6sgwJkmS1JFhTJIkqSPDmCRJUkeGMUmSpI4MY5IkSR0ZxiRJkjoyjEmSJHVkGJMkSerIMCZJktSRYUySJKkjw5gkSVJHBwxjSbYl2ZvktqHanyb5epJbk3wqyRGtvibJD5Pc0qb3D23zkiRfTTKd5OIkafUjk+xIclf7uXoR+pQkSZpIB3Nm7MPAxlm1HcALq+qfAN8A3ja07O6qWt+mNw3VLwHeAKxr08w+twLXV9U64Pr2WpIkaUU4YBirqi8AD8+qfbaqHmsvbwCO298+khwDPLOqbqiqAj4CnN0WnwVc1uYvG6pLkiQte+O4Z+x3gc8MvV6b5CtJ/jrJy1rtWGDX0Dq7Wg3g6Kra0+YfAI4ew5gkSZKWhFWjbJzkj4DHgI+20h7ghKp6KMlLgE8necHB7q+qKknt53hbgC0AJ5xwwsIHLkmSNCEWfGYsyeuAfwn823bpkap6tKoeavM3A3cDJwK7+dlLmce1GsCD7TLmzOXMvfMds6ouraoNVbVhampqoUOXJEmaGAsKY0k2An8IvLqqfjBUn0pyWJt/DoMb9e9plyEfSXJKe4ryXODqttl2YHOb3zxUlyRJWvYOeJkyyeXAK4CjkuwCLmDw9ORTgB3tEypuaE9Ovhx4R5IfAz8B3lRVMzf/v5nBk5lPZXCP2cx9ZhcBVyY5D7gPeO1YOpMkSVoCDhjGquqcOcofmmfdq4Cr5lm2E3jhHPWHgFMPNA5JkqTlyE/glyRJ6sgwJkmS1JFhTJIkqSPDmCRJUkeGMUmSpI4MY5IkSR0ZxiRJkjoyjEmSJHVkGJMkSerIMCZJktSRYUySJKkjw5gkSVJHhjFJkqSODGOSJEkdGcYkSZI6MoxJkiR1ZBiTJEnqyDAmSZLUkWFMkiSpo1W9ByBp6Vuz9ZreQ5CkJcszY5IkSR0ZxiRJkjoyjEmSJHVkGJMkSerIMCZJktSRYUySJKmjgwpjSbYl2ZvktqHakUl2JLmr/Vzd6klycZLpJLcmefHQNpvb+ncl2TxUf0mSr7ZtLk6ScTYpSZI0qQ72zNiHgY2zaluB66tqHXB9ew1wBrCuTVuAS2AQ3oALgJcCJwMXzAS4ts4bhrabfSxJkqRl6aDCWFV9AXh4Vvks4LI2fxlw9lD9IzVwA3BEkmOA04EdVfVwVX0b2AFsbMueWVU3VFUBHxnalyRJ0rI2yj1jR1fVnjb/AHB0mz8WuH9ovV2ttr/6rjnqT5BkS5KdSXbu27dvhKFLkiRNhrHcwN/OaNU49nWA41xaVRuqasPU1NRiH06SJGnRjRLGHmyXGGk/97b6buD4ofWOa7X91Y+boy5JkrTsjRLGtgMzT0RuBq4eqp/bnqo8Bfhuu5x5HXBaktXtxv3TgOvaskeSnNKeojx3aF+SJEnL2qqDWSnJ5cArgKOS7GLwVORFwJVJzgPuA17bVr8WOBOYBn4AvB6gqh5O8k7gprbeO6pq5qGANzN4YvOpwGfaJEmStOwdVBirqnPmWXTqHOsW8JZ59rMN2DZHfSfwwoMZiyRJ0nLiJ/BLkiR1ZBiTJEnqyDAmSZLUkWFMkiSpI8OYJElSR4YxSZKkjgxjkiRJHRnGJEmSOjKMSZIkdWQYkyRJ6sgwJkmS1NFBfTelpPFbs/Wa3kOQJE0Az4xJkiR1ZBiTJEnqyDAmSZLUkWFMkiSpI8OYJElSR4YxSZKkjgxjkiRJHRnGJEmSOjKMSZIkdWQYkyRJ6sgwJkmS1JFhTJIkqSPDmCRJUkcLDmNJnp/klqHpkSRvTfL2JLuH6mcObfO2JNNJ7kxy+lB9Y6tNJ9k6alOSJElLxaqFblhVdwLrAZIcBuwGPgW8HnhvVb17eP0kJwGbgBcAzwY+l+TEtvh9wCuBXcBNSbZX1R0LHZskSdJSseAwNsupwN1VdV+S+dY5C7iiqh4FvplkGji5LZuuqnsAklzR1jWMSZKkZW9c94xtAi4fen1+kluTbEuyutWOBe4fWmdXq81XlyRJWvZGDmNJDgdeDXy8lS4BnsvgEuYe4D2jHmPoWFuS7Eyyc9++feParSRJUjfjODN2BvDlqnoQoKoerKrHq+onwAf46aXI3cDxQ9sd12rz1Z+gqi6tqg1VtWFqamoMQ5ckSeprHGHsHIYuUSY5ZmjZa4Db2vx2YFOSpyRZC6wDvgTcBKxLsradZdvU1pUkSVr2RrqBP8nTGTwF+cah8p8kWQ8UcO/Msqq6PcmVDG7Mfwx4S1U93vZzPnAdcBiwrapuH2VckiRJS8VIYayq/gF41qza7+xn/QuBC+eoXwtcO8pYJEmSliI/gV+SJKkjw5gkSVJHhjFJkqSODGOSJEkdGcYkSZI6MoxJkiR1ZBiTJEnqyDAmSZLU0Ugf+ipJklamNVuv6T2Esbj3olf1HoJnxiRJknoyjEmSJHVkGJMkSerIMCZJktSRYUySJKkjw5gkSVJHhjFJkqSODGOSJEkdGcYkSZI68hP4teQsl099liQJPDMmSZLUlWFMkiSpI8OYJElSR4YxSZKkjgxjkiRJHRnGJEmSOjKMSZIkdTRyGEtyb5KvJrklyc5WOzLJjiR3tZ+rWz1JLk4yneTWJC8e2s/mtv5dSTaPOi5JkqSlYFxnxv5FVa2vqg3t9Vbg+qpaB1zfXgOcAaxr0xbgEhiEN+AC4KXAycAFMwFOkiRpOVusy5RnAZe1+cuAs4fqH6mBG4AjkhwDnA7sqKqHq+rbwA5g4yKNTZIkaWKMI4wV8NkkNyfZ0mpHV9WeNv8AcHSbPxa4f2jbXa02X12SJGlZG8d3U/56Ve1O8ovAjiRfH15YVZWkxnAcWtjbAnDCCSeMY5eSJEldjXxmrKp2t597gU8xuOfrwXb5kfZzb1t9N3D80ObHtdp89dnHurSqNlTVhqmpqVGHLkmS1N1IYSzJ05M8Y2YeOA24DdgOzDwRuRm4us1vB85tT1WeAny3Xc68Djgtyep24/5prSZJkrSsjXqZ8mjgU0lm9vXnVfWXSW4CrkxyHnAf8Nq2/rXAmcA08APg9QBV9XCSdwI3tfXeUVUPjzg2SZKkiTdSGKuqe4BfnaP+EHDqHPUC3jLPvrYB20YZjyRJ0lLjJ/BLkiR1ZBiTJEnqyDAmSZLUkWFMkiSpI8OYJElSR4YxSZKkjgxjkiRJHRnGJEmSOjKMSZIkdWQYkyRJ6sgwJkmS1JFhTJIkqSPDmCRJUkeGMUmSpI4MY5IkSR0ZxiRJkjoyjEmSJHVkGJMkSerIMCZJktSRYUySJKkjw5gkSVJHhjFJkqSODGOSJEkdGcYkSZI6MoxJkiR1tOAwluT4JJ9PckeS25P8Xqu/PcnuJLe06cyhbd6WZDrJnUlOH6pvbLXpJFtHa0mSJGnpWDXCto8Bv19VX07yDODmJDvasvdW1buHV05yErAJeAHwbOBzSU5si98HvBLYBdyUZHtV3THC2CRJkpaEBYexqtoD7Gnz30vyNeDY/WxyFnBFVT0KfDPJNHByWzZdVfcAJLmirWsYkyRJy95Y7hlLsgZ4EXBjK52f5NYk25KsbrVjgfuHNtvVavPVJUmSlr2Rw1iSnweuAt5aVY8AlwDPBdYzOHP2nlGPMXSsLUl2Jtm5b9++ce1WkiSpm5HCWJKfYxDEPlpVnwSoqger6vGq+gnwAX56KXI3cPzQ5se12nz1J6iqS6tqQ1VtmJqaGmXokiRJE2GUpykDfAj4WlX92VD9mKHVXgPc1ua3A5uSPCXJWmAd8CXgJmBdkrVJDmdwk//2hY5LkiRpKRnlacp/DvwO8NUkt7TafwXOSbIeKOBe4I0AVXV7kisZ3Jj/GPCWqnocIMn5wHXAYcC2qrp9hHFJkiQtGaM8Tfk3QOZYdO1+trkQuHCO+rX7206SJGm58hP4JUmSOjKMSZIkdWQYkyRJ6sgwJkmS1JFhTJIkqSPDmCRJUkeGMUmSpI4MY5IkSR0ZxiRJkjoyjEmSJHVkGJMkSerIMCZJktSRYUySJKkjw5gkSVJHq3oPQIfGmq3X9B6CJEmag2fGJEmSOjKMSZIkdWQYkyRJ6sgwJkmS1JFhTJIkqSPDmCRJUkeGMUmSpI4MY5IkSR0ZxiRJkjoyjEmSJHXk1yHth18hJEmSFtvEnBlLsjHJnUmmk2ztPR5JkqRDYSLCWJLDgPcBZwAnAeckOanvqCRJkhbfRIQx4GRguqruqaofAVcAZ3UekyRJ0qKblDB2LHD/0OtdrSZJkrSsLakb+JNsAba0l99PcmfP8TwJRwHf6j2IQ2gl9buSegX7XQlWUs8rqVew3znlXYdgJAO/PN+CSQlju4Hjh14f12o/o6ouBS49VIMalyQ7q2pD73EcKiup35XUK9jvSrCSel5JvYL9TrJJuUx5E7AuydokhwObgO2dxyRJkrToJuLMWFU9luR84DrgMGBbVd3eeViSJEmLbiLCGEBVXQtc23sci2TJXVod0UrqdyX1Cva7EqyknldSr2C/EytV1XsMkiRJK9ak3DMmSZK0IhnG5pDk+CSfT3JHktuT/F6rH5lkR5K72s/Vrf4rSf42yaNJ/mDWvo5I8okkX0/ytSS/Ns8xtyXZm+S2WfU5jzmJ/SZ5fpJbhqZHkrx1nmPO+fVXSc5vtUpy1HLudWj5xUm+P+5eJ63fJF8c2v7vk3x6mfTb7b07zp7bsv/U9nFbksuT/ON5jrm57feuJJuH6hcmuX/Sf5/H0evQ8u2z/+yXW79JnjHr/fCtJP99mfT7l0m+k+QvZtXXJrkxg7/HPpbBw4WLp6qcZk3AMcCL2/wzgG8w+JqmPwG2tvpW4F1t/heBfwpcCPzBrH1dBvz7Nn84cMQ8x3w58GLgtln1OY85qf0O7fMw4AHgl+dZdjfwnPbf5P8AJ7VlLwLWAPcCRy3nXtvyDcD/Br4/6b/L4+h3aL2rgHOXer9tebf37jh7ZvBB298EntpeXwm8bo7jHQnc036ubvOr27JT2ngm+vd5HL225b8F/PnsP/vl2u/QejcDL1/q/bZlpwL/CviLWfUrgU1t/v3Af1iMP+OZyTNjc6iqPVX15Tb/PeBrDP5wz2IQrmg/z27r7K2qm4AfD+8nyS8w+Iv6Q229H1XVd+Y55heAh+dYNOcxx2lc/c5yKnB3Vd03x7J5v/6qqr5SVfeO3NQ8JqnXDL6T9U+BPxy1r/lMUr8zkjwT+A3g0wtsa14d+u363m3HH2fPq4CnJlkFPA34+znWOR3YUVUPV9W3gR3AxrbvG6pqz7h6m22Sek3y88B/Bv54PN090ST1OyPJiQxC0BdH6+6JOvRLVV0PfG+4liQM/o76xOxjLhbD2AEkWcPgbM2NwNFDf9E8ABx9gM3XAvuA/5XkK0k+mOTpT3IIT/aYIxmx32GbgMvnWTYRX381Ab2eD2xfzP95DZuAfmecDVxfVY88iWM+aYeo3/05pO9dGK3nqtoNvBv4O2AP8N2q+uwcqy759++Yen0n8B7gBwts4UmZgH5nbAI+Vu2U0WI5RP3O51nAd6rqsfZ60X/HDWP70f7lcxXw1tn/42i/iAf6ZVzF4PLFJVX1IuAfGJxiXZCDPOaCjaHfmf0cDrwa+PjYBzkmvXtN8mzgXwP/48lst1C9+53lHBYWbg7ahPW76O9dGL3ndh/OWQz+Efls4OlJ/t0iDXckvXtNsh54blV96kkOfUF69zvLQv9xctAmrN9DwjA2jyQ/x+CX4aNV9clWfjDJMW35McDeA+xmF7Crqm5srz8BvLjdpDhzI+SbDrCPJ3vMBRlTvzPOAL5cVQ+2bWf3e1Bff7VYJqTXFwHPA6aT3As8Lcn0iK3NaUL6nRnLUQwuZV4zSk/7c4j73Z9D8t5t+x9Hz78JfLOq9lXVj4FPAv8syUuHen41y+P9O2qvvwZsaO/dvwFOTPJX4+nwZ01IvzNj+VVgVVXdPJbm5nCI+53PQ8AR7RInHILfccPYHNr14g8BX6uqPxtatB2YeZpmM3D1/vZTVQ8A9yd5fiudCtxRVfdX1fo2vf8Aw3lSx1yIcfU75GfOfMzRb7evv5qUXqvqmqr6papaU1VrgB9U1fNG6+6JJqXfoe1/m8GNsv93YR3tX4d+92fR37sw1p7/DjglydPaPk9t+7xxqOftDL4p5bQkq9sZiNNabdFNSq9VdUlVPbu9d38d+EZVvWJcfc6YlH6H9rOoZ7U79Dundvbt8wz+vjrYY46mFvHpgKU6MXhzFXArcEubzmRwHfl64C7gc8CRbf1fYnAW7BHgO23+mW3ZemBn29enmePJlLbe5Qyubf+4bX9eq895zAnu9+kM/lXxCwc45pkMnpS5G/ijofp/bPt7jMENlx9crr3OWmexnj6bqH6BvwI2LkavHfvt9t5dhJ7/G/B14DYGT/k+ZZ5j/i4w3abXD9X/pO3vJ+3n25drr0PL17B4T1NOVL8Mnq78lSXy/j3Yfr/I4N7uH7btT2/15wBfav8dPj7f9uOa/AR+SZKkjrxMKUmS1JFhTJIkqSPDmCRJUkeGMUmSpI4MY5IkSR0ZxiRJkjoyjEmSJHVkGJMkSero/wHM4/pXOXdUvgAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 720x360 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Dates range 773 days, from 2016-09-04 to 2018-10-17\n",
    "#We have very few observations early in our data set\n",
    "fig, ax = plt.subplots(figsize=(10,5))\n",
    "ax.hist(orders_df[\"order_purchase_timestamp\"]);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>order_status</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>order_approved_at</th>\n",
       "      <th>order_delivered_carrier_date</th>\n",
       "      <th>order_delivered_customer_date</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>e481f51cbdc54678b7cc49136f2d6af7</td>\n",
       "      <td>9ef432eb6251297304e76186b10a928d</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-10-02</td>\n",
       "      <td>2017-10-02 11:07:15</td>\n",
       "      <td>2017-10-04 19:55:00</td>\n",
       "      <td>2017-10-10 21:25:13</td>\n",
       "      <td>2017-10-18 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>53cdb2fc8bc7dce0b6741e2150273451</td>\n",
       "      <td>b0830fb4747a6c6d20dea0b8c802d7ef</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-07-24</td>\n",
       "      <td>2018-07-26 03:24:27</td>\n",
       "      <td>2018-07-26 14:31:00</td>\n",
       "      <td>2018-08-07 15:27:45</td>\n",
       "      <td>2018-08-13 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>47770eb9100c2d0c44946d9cf07ec65d</td>\n",
       "      <td>41ce2a54c0b03bf3443c3d931a367089</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>2018-08-08 08:55:23</td>\n",
       "      <td>2018-08-08 13:50:00</td>\n",
       "      <td>2018-08-17 18:06:29</td>\n",
       "      <td>2018-09-04 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>949d5b44dbf5de918fe9c16f97b45f8a</td>\n",
       "      <td>f88197465ea7920adcdbec7375364d82</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-18</td>\n",
       "      <td>2017-11-18 19:45:59</td>\n",
       "      <td>2017-11-22 13:39:59</td>\n",
       "      <td>2017-12-02 00:28:42</td>\n",
       "      <td>2017-12-15 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ad21c59c0840e6cb83a9ceb5573f8159</td>\n",
       "      <td>8ab97904e6daea8866dbdbc4fb7aad2c</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-13</td>\n",
       "      <td>2018-02-13 22:20:29</td>\n",
       "      <td>2018-02-14 19:46:34</td>\n",
       "      <td>2018-02-16 18:17:02</td>\n",
       "      <td>2018-02-26 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           order_id                       customer_id  \\\n",
       "0  e481f51cbdc54678b7cc49136f2d6af7  9ef432eb6251297304e76186b10a928d   \n",
       "1  53cdb2fc8bc7dce0b6741e2150273451  b0830fb4747a6c6d20dea0b8c802d7ef   \n",
       "2  47770eb9100c2d0c44946d9cf07ec65d  41ce2a54c0b03bf3443c3d931a367089   \n",
       "3  949d5b44dbf5de918fe9c16f97b45f8a  f88197465ea7920adcdbec7375364d82   \n",
       "4  ad21c59c0840e6cb83a9ceb5573f8159  8ab97904e6daea8866dbdbc4fb7aad2c   \n",
       "\n",
       "  order_status order_purchase_timestamp    order_approved_at  \\\n",
       "0    delivered               2017-10-02  2017-10-02 11:07:15   \n",
       "1    delivered               2018-07-24  2018-07-26 03:24:27   \n",
       "2    delivered               2018-08-08  2018-08-08 08:55:23   \n",
       "3    delivered               2017-11-18  2017-11-18 19:45:59   \n",
       "4    delivered               2018-02-13  2018-02-13 22:20:29   \n",
       "\n",
       "  order_delivered_carrier_date order_delivered_customer_date  \\\n",
       "0          2017-10-04 19:55:00           2017-10-10 21:25:13   \n",
       "1          2018-07-26 14:31:00           2018-08-07 15:27:45   \n",
       "2          2018-08-08 13:50:00           2018-08-17 18:06:29   \n",
       "3          2017-11-22 13:39:59           2017-12-02 00:28:42   \n",
       "4          2018-02-14 19:46:34           2018-02-16 18:17:02   \n",
       "\n",
       "  order_estimated_delivery_date  \n",
       "0           2017-10-18 00:00:00  \n",
       "1           2018-08-13 00:00:00  \n",
       "2           2018-09-04 00:00:00  \n",
       "3           2017-12-15 00:00:00  \n",
       "4           2018-02-26 00:00:00  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Explore head of dataframe\n",
    "#Indexed by order_id\n",
    "#From this dataset we have the required elements for a CLV model; customer identifier, purchase timestamp.\n",
    "orders_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duplicate order_ids: 0\n",
      "unique order_ids: 99441\n"
     ]
    }
   ],
   "source": [
    "#order_id's are unique\n",
    "print(f'duplicate order_ids: {len(orders_df[\"order_id\"].unique()) - len(orders_df[\"order_id\"])}')\n",
    "print(f'unique order_ids: {len(orders_df[\"order_id\"].unique())}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duplicate customer_id: 0\n",
      "unique customer_ids: 99441\n"
     ]
    }
   ],
   "source": [
    "#order_id's are unique\n",
    "print(f'duplicate customer_id: {len(orders_df[\"customer_id\"].unique()) - len(orders_df[\"customer_id\"])}')\n",
    "print(f'unique customer_ids: {len(orders_df[\"customer_id\"].unique())}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "delivered      96478\n",
       "shipped         1107\n",
       "canceled         625\n",
       "unavailable      609\n",
       "invoiced         314\n",
       "processing       301\n",
       "created            5\n",
       "approved           2\n",
       "Name: order_status, dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#We have some order statuses that are concerning for a CLV model; canceled and unavailable\n",
    "#We have to check if these order types are associated with zero or negative monetary values when we join datasets \n",
    "orders_df[\"order_status\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "order_id                            0\n",
       "customer_id                         0\n",
       "order_status                        0\n",
       "order_purchase_timestamp            0\n",
       "order_approved_at                 160\n",
       "order_delivered_carrier_date     1783\n",
       "order_delivered_customer_date    2965\n",
       "order_estimated_delivery_date       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#We have have some null values to deal with in order_approved_at, order_delivered_carrier_date, order_delivered_customer_date\n",
    "orders_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAABQ4AAANkCAYAAAD2mLcyAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8qNh9FAAAACXBIWXMAAAsTAAALEwEAmpwYAACOFUlEQVR4nOzdd7hlZXU/8O+aoagoigVBNKKxl4glGruxYkGwo1GxBWNJUZOoP6OisaCJEhOJihXFWGPBioJiBUUjdlEEo6JYaCIiZWb9/tj7msM9d8qBmbkzZz6f5znPvWfvd+/97n3O7LlnnbXet7o7AAAAAACTVix3BwAAAACAzY/AIQAAAAAwReAQAAAAAJgicAgAAAAATBE4BAAAAACmCBwCAAAAAFMEDgEAAABgM1BVb6qqX1bVt9awvqrq36vqxKr6RlXdYmLdflX1g/Gx34boj8AhAAAAAGwe3pJkz7Wsv3eS646P/ZO8Jkmq6opJnp/kNkluneT5VbXTJe2MwCEAAAAAbAa6+7NJTl9Lk72TvLUHxya5QlXtmuReST7Z3ad39xlJPpm1ByDXyzaXdAcAAAAAsLnb7uaP6+XuwwXHv/mJGTIFFxzS3YfMsIvdkvxk4vlPx2VrWn6JCBwCAAAAwCYwBglnCRQuK6XKAAAAALBlOCXJNSaeX31ctqbll4iMQwAAAADmXq1Yudxd2BAOT/LUqnpnholQzurun1fVEUleMjEhyj2TPPuSHkzgEAAAAAA2A1X1jiR3SXLlqvpphpmSt02S7n5tko8muU+SE5P8Lsljx3WnV9U/Jzlu3NULu3ttk6ysX3+6l31cSAAAAADYqC71p3+17EGw3x/32lruPszCGIcAAAAAwBSBQwAAAABgijEOAQAAAJh7czI5yiYl4xAAAAAAmCLjEAAAAIC5J+NwdjIOAQAAAIApAocAAAAAwBSlygAAAADMvVqpVHlWMg4BAAAAgCkChwAAAADAFKXKAAAAAMy9FWZVnpmMQwAAAABgioxDAAAAAOZeyTicmYxDAAAAAGCKwCEAAAAAMEWpMgAAAABzT6ny7GQcAgAAAABTZBwCAAAAMPdqhfy5WbliAAAAAMAUgUMAAAAAYIpSZQAAAADmnslRZifjEAAAAACYIuMQAAAAgLkn43B2Mg4BAAAAgCkChwAAAADAFKXKAAAAAMw9pcqzk3EIAAAAAEyRcQgAAADA3KuVMg5nJeMQAAAAAJgicAgAAAAATFGqDAAAAMDcMznK7GQcAgAAAABTZBwCAAAAMPdkHM5OxiEAAAAAMEXgEAAAAACYolQZAAAAgLm3QqnyzGQcAgAAAABTBA4BAAAAgClKlQEAAACYe2ZVnp2MQwAAAABgioxDAAAAAOaejMPZyTgEAAAAAKYIHAIAAAAAU5QqAwAAADD3lCrPTsYhAAAAADBFxiEAAAAAc0/G4exkHAIAAAAAUwQOAQAAAIApSpUBAAAAmHtKlWcn4xAAAAAAmCLjEAAAAIC5VytlHM5KxiEAAAAAMEXgEAAAAACYolQZAAAAgLlncpTZyTgEAAAAAKbIOAQAAABg7sk4nJ2MQwAAAABgisAhAAAAADBFqTIAAAAAc0+p8uxkHAIAAAAAU2QcAgAAADD3Vqyo5e7CFkfGIQAAAAAwReAQAAAAAJiiVBkAAACAuVdKlWcm4xAAAAAAmCJwCAAAAABMUaoMAAAAwNyrUqo8KxmHAAAAAMAUGYcAAAAAzL0VJkeZmYxDAAAAAGCKwCEAAAAAMEWpMgAAAABzr5Qqz0zGIQAAAAAwRcYhAAAAAHNvS8g4rKo9k7wqycokb+juAxetPyjJn49PL5Nk5+6+wrhuVZJvjut+3N33v6T9ETgEAAAAgGVWVSuTHJzkHkl+muS4qjq8u7+z0Ka7nzbR/q+T3HxiF+d29x4bsk9KlQEAAABg+d06yYndfVJ3n5/knUn2Xkv7hyd5x8bskMAhAAAAAHNvRdWyP6pq/6r6ysRj/4ku7pbkJxPPfzoum1JV10xyrSSfmlh8qXGfx1bVPhvimilVBgAAAIBNoLsPSXLIBtjVvkne292rJpZds7tPqaprJ/lUVX2zu394SQ4icAgAAADA3NsCJkc5Jck1Jp5ffVy2lH2TPGVyQXefMv48qaqOzjD+4SUKHCpVBgAAAIDld1yS61bVtapquwzBwcMXN6qqGyTZKckxE8t2qqrtx9+vnOT2Sb6zeNtZyTgEAAAAgGXW3RdW1VOTHJFkZZI3dfe3q+qFSb7S3QtBxH2TvLO7e2LzGyZ5XVWtzpAoeODkbMwXV130GAAAAAAwf276jx9Z9iDYN19+382+XnqSUmUAAAAAYIpSZQAAAADm3orNf3KUzY6MQwAAAABgisAhAAAAADBFqTIAAAAAc6+kz83MJQMAAAAApggcAgAAAABTlCoDAAAAMPeqzKo8KxmHAAAAAMAUGYcAAAAAzL0VK2QczkrGIQAAAAAwReAQAAAAAJiiVBkAAACAuVdKlWcm4xAAAAAAmCJwyJSqOqCqern7wf+pqrdU1Y82wn53H1/va2/ofQMAAMDmpFbUsj+2NAKHsHXbPcnzkwgcAgAAABchcLiVqqrtN9Fxtq2qLSqkXoPtlrsfAAAAAMtJ4HAOVNWeVXVMVZ1bVWdV1Qeq6voT64+uqs9X1V5V9bWqOi/Jk8d1N6+qz1XV76vqlKp6bpKpQF9VbVNVz66q71XVeVX1s6p6RVVdaqLN7lXVVfXkqnp5Vf0syXlJrrAe53Cpqjqoqr5VVb+tqlOr6kNVdYNF7R4zHuNO43n+tqpOq6qDq+rSa+jLK6vql1X1u6r6cFXtvmifP6qqw6rqcVX1vSTnJ7nvel7bg6vqF1W1zaJ9bl9VZ1TVqyaWXaWqXjte5/PGa7n/EtfiblX1P+Nr8sOqeuK6rt8arulTx76fXlVnVtWxVXXfifV3SfLp8eknx+vV43IAAACYKyuqlv2xpTGr8hauqvZM8pEkn0rysCSXTfLCJJ+vqj26+5Sx6fWS/HuSf05yUpLTq+rK43anJtkvQ5DvH5L80RKHOizJXkleluSLSW447mv3JA9a1PY5SY5Lsn+SlUl+vx6nsn2SyyV5UZKfJ7lihuDmMVV1w+4+dYn+vDvJfya5dZLnJdkhyWMWtXt2kuOTPDbJzklekuQTVXXj7r5got2fJ9kjyQuS/DLJj9bz2r5t7Oc9k3x0Yn/3yxAwfWuSVNWOST6f5NJJDkhycpJ7JXlNVW3f3f8xtrvhuJ+vJNl3vC4HjMdetfZLOGX3JG9I8qMM/9b3SvLhqrp3d388yf8keUqSg5P8TYbXLEm+M+NxAAAAgDkkcLjle1GGQOC9u/vCJKmqY5J8P8kzkjx9bHflJPfs7uMXNqyqF2cItt2zu38yLvtkkv+dPEBV3TFD4Gy/7n7ruPjIqjo9yWFjEO34iU1+keQB3b3eE6x091lJnjBxzJVJjhj39fAkBy3a5KPd/ffj758YJ3N5YVW9pLu/P9Hu7CR7d/fqcb/fzxDAe3SSN0602ynJLScDlFX1rqzj2nb3sVX1gySPykUDh49K8t3u/ur4/G+TXDPJTbv7B+OyI6vqCkmeX1WvGY/xT2Of79nd54zH/GKSHyb52Vov4iIT1ydVtSLJURkCyE9K8vHu/k1VLQQJv9vdx86yfwAAANiSbImTkyw3pcpbsKraIcktkrxrIbCVJN19cpIvJLnzRPMfLQruJcltkxy7EDQctz0nyYcWtdszQ/nue8eS5W3G0txPjOvvtKj9B2YJGk6cz0Or6ktVdWaSC5OckyHT7vpLNH/3oufvzPB+vvWi5e9dCBomSXd/IclPM5z7pGMXBQ1nubZvS7J3VV1u3PZKSe4zLl+wZ5IvJTl50TU8IsmVktxobHfbDEHRcyaO+ZPxmDOpqluOpdm/yHA9L0hyjyx9PQEAAAAuQuBwy7ZThvEIf77EulMzlPsuWKrNrhky+hZbvGznJNtlCORdMPH45bj+SovaL3WstaqqvZK8K8l3kzwiyW2S/GmSXyW51BKbLO7jwvPd1tFuYdnidov7PMu1PWzs44PH5w/LkM172ESbnTMEWC9Y9HjPuH7hGq7va7JWVXWNDBmGV0zy10lul+F6fjxLX08AAACAi1CqvGU7I0kn2WWJdbskOX3i+VIZgD9PctUlli9edlqGcQrvuIZ+LC6hnTnbMMN4fid292MWFlTVtrlogG5xH7+96HmSnLJEu6W2PX7RssV9Xu9r290nV9UXkjwyyZvHn0dPZnJmuIa/zFCyvJQTxp/r+5qsy55JLp/kod3904WFVXWZGfcDAAAAc0Gp8uxkHG7BxnLWryZ5yDgmYJKkqq6ZIcPs6HXs4pgkfzZmpy1su0OGSTQmLWSpXb67v7LEY6ax99bgMhnKaSc9KsPkKkt56KLn+yZZnaEceNKDx/H9kiRVdfskV89w7mt0Ma7tW5PcZZyR+La5aJlyMlzDGyT58Rqu4dlju2OS3Gd8HRaOeY0kt19bf5ewECD8wwQwVXW9JfZz3vjz0gEAAACYIONwy/fcDDP/friq/jPDmIAvSHJWklesY9uDMswI/ImqOiD/N6vyuZONuvvoqnpHhjEOX5nkyxmCdLtnGMvvmYsmJLk4Pp5kn6o6KMmHk9wqQ4ntmWtof5+q+pcM4yzeOsnzk7x1YuKRBZdL8oGqel2SqyR5aZIfZJzteB1mubbvSfIfGcqTz03y3kXrD8pQwvy58RxPyDAxzQ2S3LG79x7bvSjJQzK8Jv+SoUT8gMxYqpzkyAyB2LdW1SsylEC/IMmPc9EvDL4/tnvcONnNeUlOmAhkAgAAwFxYIeNwZjIOt3Dd/fEk901yhQwThrw2wziBd1hXJmB3/zrJ3ZL8OsmhSQ7OEMB70xLNH5khgPXgJB/MEBh7aoYg3KxBraW8PsmLMwTXPpQhILlXhiDdUh6ZYYbg92eY4fj1GYKgi700yYlJ3pLkP5P8T5J7dfcFS7S9iFmubXefOfZ7twyTw5y9aP1ZGTIVP5rkmRkmRXlTkr2TfHqi3XfHc79MhjEfD0zyqgzjFa637v52kr/IMJPz4Un+Mcmzknx2UbvTMryON0vymSTHJbnlLMcCAAAA5lNdjMlvYdlU1WMyjCN43e4+cS3tdk9ycpK/7O43bJreAQAAAJur2x/4qWUPgn3hWXfdotIelSoDAAAAMPeqtqiY3WZB4JCNrqrW9T5b1VJf19s4Wcva7naru3v1puoPAAAAMJ+MccimcME6Hvut7466+y3dXWsrUx7b/WhsN49lykdl7ddzqTEqAQAAYKtWK5b/saWRccim8KfrWH/yJunF/Hhihtmi1+TXm6ojAAAAwPwSOGSj6+6vLHcf5kl3n7DcfQAAAADmn8AhAAAAAHNvxQqTo8xqC6yuBgAAAAA2NoFDAAAAAGCKUmUAAAAA5l4pVZ6ZjEMAAAAAYIqMQwAAAADmXpWMw1nJOAQAAAAApggcAgAAAABTlCoDAAAAMPdWmBxlZjIOAQAAAIApMg4BAAAAmHsl43BmMg4BAAAAgCkChwAAAADAFKXKAAAAAMy9lUqVZybjEAAAAACYIuMQAAAAgLkn43B2Mg4BAAAAgCkChwAAAADAFKXKAAAAAMw9pcqzk3EIAAAAAEyRcQgAAADA3JNxODsZhwAAAADAFIFDAAAAAGCKUmUAAAAA5p5S5dnJOAQAAAAApsg4BAAAAGDubSPjcGYyDgEAAACAKQKHAAAAAMAUpcoAAAAAzD2To8xOxiEAAAAAMEXgEAAAAACYolQZAAAAgLmnVHl2Mg4BAAAAgCkyDgEAAACYeytXyJ+blSsGAAAAAEwROAQAAAAApihVBgAAAGDumRxldjIOAQAAAIApMg4BAAAAmHsyDmcn4xAAAAAAmCJwCAAAAACbgaras6pOqKoTq+pZS6x/TFX9qqqOHx9PmFi3X1X9YHzstyH6o1QZAAAAgLm3uZcqV9XKJAcnuUeSnyY5rqoO7+7vLGr6ru5+6qJtr5jk+UlulaSTfHXc9oxL0icZhwAAAACw/G6d5MTuPqm7z0/yziR7r+e290ryye4+fQwWfjLJnpe0QzIOAQAAAJh7K2v5Mw6rav8k+08sOqS7Dxl/3y3JTybW/TTJbZbYzYOq6k5Jvp/kad39kzVsu9sl7a/AIQAAAABsAmOQ8JB1NlyzDyV5R3efV1VPTHJokrtukM4tQakyAAAAACy/U5JcY+L51cdlf9Ddp3X3eePTNyS55fpue3HIOAQAAABg7m3uk6MkOS7JdavqWhmCfvsmecRkg6ratbt/Pj69f5Lvjr8fkeQlVbXT+PyeSZ59STskcAgAAAAAy6y7L6yqp2YIAq5M8qbu/nZVvTDJV7r78CR/U1X3T3JhktOTPGbc9vSq+ucMwcckeWF3n35J+1TdfUn3AQAAAACbtad/8FvLHgR75d432ezTHicZ4xAAAAAAmCJwCAAAAABMMcYhAAAAAHNvm81/cpTNjoxDAAAAAGCKwCEAAAAAMEWpMgAAAABzb6VS5ZnJOAQAAAAApsg4BAAAAGDuyTicnYxDAAAAAGCKwCEAAAAAMEWpMgAAAABzT6ny7GQcAgAAAABTZBwCAAAAMPdkHM5OxiEAAAAAMEXgEAAAAACYolQZAAAAgLmnVHl2Mg4BAAAAgCkyDgEAAACYezIOZyfjEAAAAACYInAIAAAAAExRqgwAAADA3FOqPDsZhwAAAADAFBmHAAAAAMw9GYezk3EIAAAAAEwROAQAAAAApihVBgAAAGDuKVWenYxDAAAAAGCKjEMAAAAA5p6Mw9nJOAQAAAAApggcAgAAAABTlCoDAAAAMPdWllLlWck4BAAAAACmCBwCAAAAAFOUKgMAAAAw91YoVZ6ZjEMAAAAAYIqMQwAAAADm3koJhzOTcQgAAAAATBE4BAAAAACmKFUGAAAAYO6tWKFWeVYyDgEAAACAKTIOAQAAAJh7K0vG4axkHAIAAAAAUwQOAQAAAIApSpUBAAAAmHsrlCrPTMYhAAAAADBFxiEAAAAAc2+lhMOZyTgEAAAAAKYIHAIAAAAAU5QqAwAAADD3VqxQqzwrgUNgvR35g1/1cvcBYG3uft2r+GsQAAA2EIFDAAAAAObeivId86yMcQgAAAAATBE4BAAAAACmKFUGAAAAYO6tVKk8MxmHAAAAAMAUGYcAAAAAzD2To8xOxiEAAAAAMEXgEAAAAACYolQZAAAAgLm3coVS5VnJOAQAAAAApggcAgAAAABTlCoDAAAAMPfMqjw7GYcAAAAAwBQZhwAAAADMvZUSDmcm4xAAAAAAmCJwCAAAAABMUaoMAAAAwNwzOcrsZBwCAAAAAFNkHAIAAAAw91aukHE4KxmHAAAAAMAUgUMAAAAAYIpSZQAAAADmnkrl2ck4BAAAAACmCBwCAAAAMPdWVi37Y12qas+qOqGqTqyqZy2x/ulV9Z2q+kZVHVVV15xYt6qqjh8fh2+Ia6ZUGQAAAACWWVWtTHJwknsk+WmS46rq8O7+zkSzryW5VXf/rqqelOTlSR42rju3u/fYkH2ScQgAAAAAy+/WSU7s7pO6+/wk70yy92SD7v50d/9ufHpskqtvzA4JHAIAAAAw91ZULfujqvavqq9MPPaf6OJuSX4y8fyn47I1eXySj008v9S4z2Orap8Ncc2UKgMAAADAJtDdhyQ55JLup6oemeRWSe48sfia3X1KVV07yaeq6pvd/cNLchyBQwAAAADm3srNv+72lCTXmHh+9XHZRVTV3ZM8J8mdu/u8heXdfcr486SqOjrJzZNcosDh5n/JAAAAAGD+HZfkulV1raraLsm+SS4yO3JV3TzJ65Lcv7t/ObF8p6rafvz9yklun2RyUpWLRcYhAAAAACyz7r6wqp6a5IgkK5O8qbu/XVUvTPKV7j48yb8kuWyS91RVkvy4u++f5IZJXldVqzMkCh64aDbmi0XgEAAAAIC5t2IItG3WuvujST66aNnzJn6/+xq2+2KSm27o/ihVBgAAAACmCBwCAAAAAFOUKgMAAAAw91ZuAaXKmxsZhwAAAADAFBmHAAAAAMy9LWFylM2NjEMAAAAAYIrAIQAAAAAwRakyAAAAAHNvpfS5mblkAAAAAMAUGYcAAAAAzD2To8xOxiEAAAAAMEXgEAAAAACYolQZAAAAgLmnUnl2Mg4BAAAAgCkyDgEAAACYeysi5XBWMg4BAAAAgCkChwAAAADAFKXKAAAAAMw9k6PMTsYhAAAAADBFxiEAAAAAc2+FjMOZyTgEAAAAAKYIHAIAAAAAU5QqAwAAADD3TI4yOxmHAAAAAMAUGYcAAAAAzL0VkXI4KxmHAAAAAMAUgUMAAAAAYIpSZQAAAADmnslRZifjEAAAAACYInAIAAAAAExRqgwAAADA3FuhVHlmMg4BAAAAgCkyDgEAAACYexIOZyfjEAAAAACYInAIAAAAAExRqgwAAADA3FtRipVnJeMQAAAAAJgi4xAAAACAuSfhcHYyDgEAAACAKQKHAAAAAMAUpcoAAAAAzD3Zc7NzzQAAAACAKTIOAQAAAJh7ZXaUmck4BAAAAACmCBwCAAAAAFOUKgMAAAAw91aoVJ6ZjEMAAAAAYIqMQwAAAADmnrlRZifjEAAAAACYInAIAAAAAExRqgwAAADA3JM9NzvXDAAAAACYIuMQAAAAgLlXZkeZmYxDAAAAAGCKwCEAAAAAMEWpMgAAAABzb4VK5ZnJOAQAAAAApggcAgAAAABTlCoDAAAAMPdUKs9OxiEAAAAAMEXGIQAAAABzz+Qos5NxCAAAAABMETgEAAAAAKYoVQYAAABg7lWpVZ6VjEMAAAAAYIqMQwAAAADmnslRZifjEAAAAACYInAIAAAAAEyZy8BhVR1QVb3c/Ugufl+qaveq6qp6zMSyt1TVjzZk/zaFqjq6qo5e7n6sr6q6y3jt77Il92V87911w/cKAAAAtjy1GTy2NHMZOJxj/5zkAcvdia3A/yS57fhzS/b8JAKHAAAAwMWyxU6OUlXbd/d5m+A42ya5sLuXPYOxu3+4qY+5qa7zprSmc6qqlUmqu3+T5NgNdKxKsm13n78h9gdbms9+5H058n3vyFmnn5Zd/2j3PPgv/zbXucnNlmz7g29+LR889HX55Sk/zvnn/T5X3HmX3O6e98vdH/iITdxrAABgHq2oLTHnb3ltFhmHVbVnVR1TVedW1VlV9YGquv7E+qOr6vNVtVdVfa2qzkvy5HHdzavqc1X1+6o6paqemyWyP6tqm6p6dlV9r6rOq6qfVdUrqupSE20WyoOfXFUvr6qfJTkvyRXW8zw2WF/WsP8/lCpX1fZVdXpVvXKJdg8dz+PmE8vuXFVHVdXZVXVOVR1RVTdZtN3arvO1qurtVfWrsc/HV9VU9mNV7TtxXt9eqs36qKodqurAqvrhuK9Tq+q/q+qq4/qrVNXrqur7VfW7qvpJVf1XVe22aD8HjNfiJuM5/zbJu8d1XVUvrqpnVdXJSc5PctM1lQdX1QOr6tjxeGdW1Xuq6o8WtflRVR1WVY+rqu+N+7zvep7zVcZz+M24/7dmifdeVd2zqj5aVT8f+/KtqnrGGPhcaLMQ6H7OeC5dVQdMrF/n+wEuqa9+9qi855BX5V4PeVSe/e9vyrVveNMcfMDf5/Rfnrpk++0vfencZa8H5+8OfHX+6T8Py54P2y8fefub8tmPvG8T9xwAAIBkMwgcVtWeST6S5LdJHpbkSUlukuTzi4JA10vy70n+I8m9khxVVVdO8qkkV06yX5KnJNkzyeOWONRhSf4pyX9lCOS8NMnjk7x9ibbPGY+3f4bS4N+vx3lsrL4sacyYe3eSh08GjEaPSvKt7v7a2Lf7JjkqwzV+ZJJHJLlcks9V1TUWbbvUdb5Gki8luVmSpyW5f4Yy3v+uqvtPXIO7j+f0gyQPTPIvSV6V5PqZQVVtl+STSf46yVuS3C/JU5OcnmSnsdkVM7wuz85wnf8hyXWTfGENAdgPJvnM2PeDJpY/JsNr8Pfjz5+toU9/leS/k3wnyYOTPDHD+/QzVXW5Rc3/PMnTk7xg7Ns31ue8k7xvPNf/l+HfwoUZXofFrp3h9Xzc2OdDkxyQ5MUTbW47/nzL+Pttk7xhPJdZ3g9wsR31gXfmz+52n9x+z/tnl2vsnof+1dNy+Z2ulM999ANLtv+j69wgt7rz3XO1a147V97larn1n98rN7zFrXPit7++aTsOAACwTGpIrjuhqk6sqmctsX77qnrXuP5LVbX7xLpnj8tPqKp7bYj+bA6lyi9KclKSe3f3hUlSVcck+X6SZ2QIwCRDQO6e3X38woZV9eIkO4zLfzIu+2SS/508QFXdMUMgZr/ufuu4+MiqOj3JYVW1x+R+k/wiyQNmLE9+2kbqy9q8LUMA6+5Jjhj3f5UMwarnTLR7VZLPdPfeE/34dIbr/owkfzfRdqnr/MYMmZN37u7TxsVHjEGmFyY5fFz2giTfS7J3d68et/1ekmOSnLCe55QMwazbjvs5fGL5exd+6e4TkvztRB9XJvlCkh8nuXeS9y/a579396uWOFaN53vuxL5ueJEGVZdN8rIkb+7ux00s//J4Xo9P8m8Tm+yU5JbdvXRa1VKdqLpHkjskeXh3v3NcfERVfSzJ1SfbdvdrJ7arJJ9Lsl2Sv6+q/9fdq7v72GFVTunuxWXXs7wf4GK58IIL8pMTv5+7P/DhF1l+w1v8aU763rfWax8/+eH3c9J3v5X7PmKp718AAABms7lXKo+xjYOT3CPJT5McV1WHd/d3Jpo9PskZ3X2dqto3Q7ziYVV1oyT7JrlxkqtliDVdr7tXXZI+LWvGYVXtkOQWSd61EDRMku4+OUMQ6M4TzX+0REDttkmOXQjUjduek+RDi9rtmaFk9L01lAlvU1XbJPnEuP5Oi9p/4GKMabix+rJG3f2FJD/MkGG4YN8Mr+vbk6Sqrpvkj5O8fdHxfpchoLf4eEtd5z2TfDTJWYv2cUSSm1XVjuOb+0+TvHchaDj28dgkP1rfcxrdM8mpi4KGU6rqSVX19bH8+MIMQcNk6QzHxYHEBR+fDBquwW2T7Jjpa/iTDIHSxdfw2FmChhPHWJUhq3HSOxc3rKpdayjT/t8M76ULMgTgr5Bk57Ud5GK8H+Bi+e1vzsrq1atyuStc8SLLL3eFK+Y3Z5y2hq0Gz9nvAfnbff48L3vaE3Kn+z4gd7zPPhuxpwAAAJuNWyc5sbtPGudKeGeSvRe12TtD5WEyJFjdbUwq2jvJO7v7vDGuduK4v0tkuTMOd8qQ8fXzJdadmuSaE8+XarNrkqVSV36x6PnOGTKyzllDP6606PlSx1qXjdWXdTksQ6bZDmOg8lFJPtXdp0wcL0neOD4W+/Gi50ud+85JHj0+lnKlJJdOsm2mzzdrWLY2V0pyytoaVNVfZyipfmWGMuUzMgRMj02yVKnyml7T9XmtF67hkWtYf8bF2Odiu2b4xuCCRcsvcu2qakWGDM+rZShP/l6Sc5PskyHLdK3jZGb29wNsck972cE57/fn5kff+3Y+8JbX5EpX3TW3ueuey90tAABgC1fLP+9tqmr/DEPjLTikuw8Zf98tQ5LSgp8muc2iXfyhTXdfWFVnZYij7JaLTvT603HZJbLcgcMzknSSXZZYt0uGMe0WLPXq/jzJVZdYvnjZaRnGw7vjGvqxeFy7i/NO2lh9WZe3JXl+kgdW1ZcyZP3tt+h4yTAW4FKBr8Wz/S517qdlKId92Rr68LMMGX8XZM3X4H+XWL4mv84wfuDa7JvkqO5+xsKCqrrWWtqv6TVdn9d64Ro+Jsm3l1h/9sXY52I/T7JTVW27KHi4+Hr+cZJbJXlUdx+2sLCq9lrP48z6foCL5bI7Xj4rVqzM2WeefpHlZ595enbcae3fj1x5l6slSXbb/Y/zmzNPz0f/600ChwAAwFwYg4SHrLPhZmJZA4fdfU5VfTXJQ6rqgIW666q6ZpLbZemJISYdk+QfquoaE+MK7pBkcRDl40memeTy3X3UBj2JZe5Ld/+wqr6YIdPwehkyGSenID0hQ6nwjbv7wIt5mI9nKKX99trKeqvquCQPHl/LhTEOb5Nk98wWOPxEkn2raq/uXlzqveAySX6zaNljZzjGLL6YITh4ne4+dF2NL6ZjkqxM8qBctDx530XtLjP+/ENwsaq2TfIXS+zz/AyZoJM2xPsB1mmbbbfNNa5zvXzva8flFne46x+Wf+9rx2WP291lvffT3bnwgsWJuAAAAHPplCSTk5ZePdMVmQttfjoOPXb5DElC67PtzJY74zBJnpthVuUPV9V/Jrlshkk2zkryinVse1CSJyf5RFUdkOS8DGWrFwludffRVfWODOMKvjLJl5OszhDQuk+SZ3b39y/heSxnX96WYfDMmyZ5f3f/duJ4XVVPSfLBcbbid2fI6LtqhuDsj7v7levY//PGfn62ql6dIfC0U4aswGtPTBjy/AxBvw9U1euSXCXDaznreH+HJfnLJO+oqpdmmNH5chlmef637v5exgBsVf2/sW93zTDb8QbX3b+pqn9IcvA4+czHMrw/d8swDufR3f1fl/AYn6yqzyd5XQ0zdP8gwyQ6izMvv5shCPviqlqVIYD4tDXs9jtJ7ltVH8+Q3fuz7v7ZBng/wHq52z775tBX/nOueb0b5Y9vdNN87qMfyJmnn5Y7jGMWHvqKf06S7PeM5yZJjv7Qe3Olq+6aq+72R0mSE7/99Rz1vnfkjvd9wLL0HwAAmDP/NyXD5uq4JNcdKypPyZBM9IhFbQ7PUGl6TIY4yKfG2M/hSf5rjDVdLcl1M8RLLpFlDxx298er6r4Zgk7vzpAldXSSf+zutZbtdvevq+puGWaJPTRDhPW1Gc7reYuaPzLJXyd5XIax4M7LEAA7IrOPwbe59eVd43F3yRBEXNy3j1bVncZjvSFDFtqpGWrf37WunXf3j6vqVhnG1HtJhoDgaRnGdDx0ot2RVfUXY7v3ZRiI8+8yMfvx+ujuC6rqnhneE/uPP0/LMGHOQt3jCzNMBvK0DOP6fSZDYPGkWY41Q59eV1U/yRAMfkSG1/WUDCXcx2+gwzwww7iNL80wUcrhSZ6a5AMT/Ti/qvZJ8uokb81wPd6UYWzC1y/a31PH/X0oyfYZgrgHXNL3A6yvW97pbjnn7LPy8Xcdmt+cflp2vea18uQD/iVX2nkYneKMX130drd69ep84C2vyem/ODUrVq7MVXbdLXs/5q9yh3vvswy9BwAA2LTGMQufmiE+tDLJm7r721X1wiRfGSeRfWOSt1XViRliAvuO2367qt6dIYnowiRPuaQzKidJzT55MLC1OvIHv3LDADZrd7/uVWq5+wAAwObpvHPOXvbPtNvvcLkt6u/VFcvdAQAAAABg87PspcpbgnGwybVZ1VI310tVrUyytuj66oWJVebF1njOAAAAwJZPxuH6uWAdj/2Wr2tbnB9m7ddy8XiQ8+CorP2c37R8XQMAAICtRK9e/scWRsbh+vnTdaw/eZP0Yj7slWGijjVZ64Q4W6gnZpgVek1+vak6AgAAALC+BA7XQ3d/Zbn7MC+6+5vL3YdNrbtPWO4+AAAAAMxK4BAAAACA+Wd6ipkZ4xAAAAAAmCLjEAAAAID5twVOTrLcZBwCAAAAAFMEDgEAAACAKUqVAQAAAJh7pVR5ZjIOAQAAAIApMg4BAAAAmH8yDmcm4xAAAAAAmCJwCAAAAABMUaoMAAAAwPxTqjwzGYcAAAAAwBQZhwAAAADMPxmHM5NxCAAAAABMETgEAAAAAKYoVQYAAABg/q1WqjwrGYcAAAAAwBQZhwAAAADMvTI5ysxkHAIAAAAAUwQOAQAAAIApSpUBAAAAmH9KlWcm4xAAAAAAmCJwCAAAAABMUaoMAAAAwPzrXu4ebHFkHAIAAAAAU2QcAgAAADD/TI4yMxmHAAAAAMAUgUMAAAAAYIpSZQAAAADmXilVnpmMQwAAAABgioxDAAAAAOafjMOZyTgEAAAAAKYIHAIAAAAAU5QqAwAAADD/lCrPTMYhAAAAADBFxiEAAAAA80/G4cxkHAIAAAAAUwQOAQAAAIApSpUBAAAAmHulVHlmMg4BAAAAgCkyDgEAAACYf6tlHM5KxiEAAAAAMEXgEAAAAACYolQZAAAAgPnXvdw92OLIOAQAAAAApsg4BAAAAGD+tclRZiXjEAAAAACYInAIAAAAAExRqgwAAADA3CulyjOTcQgAAAAATBE4BAAAAACmKFUGAAAAYP4pVZ6ZjEMAAAAAYIqMQwAAAADmn4zDmck4BAAAAACmCBwCAAAAAFOUKgMAAAAw/1avWu4ebHFkHAIAAAAAU2QcAgAAADD3erXJUWYl4xAAAAAAmCJwCAAAAABMUaoMAAAAwPwzOcrMZBwCAAAAAFNkHAIAAAAw/2QczkzGIQAAAAAwReAQAAAAAJiiVBkAAACAuderlCrPSsYhAAAAADBF4BAAAACA+bd69fI/LoGqumJVfbKqfjD+3GmJNntU1TFV9e2q+kZVPWxi3Vuq6uSqOn587LGuYwocAgAAAMDm71lJjuru6yY5any+2O+SPLq7b5xkzyT/VlVXmFj/D929x/g4fl0HFDgEAAAAgM3f3kkOHX8/NMk+ixt09/e7+wfj7z9L8sskV7m4BxQ4BAAAAGD+rV617I+q2r+qvjLx2H+GM7hqd/98/P3UJFddW+OqunWS7ZL8cGLxi8cS5oOqavt1HdCsygAAAACwCXT3IUkOWdP6qjoyyS5LrHrOov10VfVa9rNrkrcl2a+7FwZXfHaGgON2Yx+emeSFa+uvwCEAAAAAc69Xr1ruLqxTd999Teuq6hdVtWt3/3wMDP5yDe12TPKRJM/p7mMn9r2QrXheVb05yd+vqz9KlQEAAABg83d4kv3G3/dL8sHFDapquyTvT/LW7n7vonW7jj8rw/iI31rXAQUOAQAAAGDzd2CSe1TVD5LcfXyeqrpVVb1hbPPQJHdK8piqOn587DGue3tVfTPJN5NcOcmL1nVApcoAAAAAzL/Vq9fdZjPW3acludsSy7+S5Anj74clOWwN29911mPKOAQAAAAApggcAgAAAABTlCoDAAAAMPe2hFmVNzcyDgEAAACAKTIOAQAAAJh/Mg5nJuMQAAAAAJgicAgAAAAATFGqDAAAAMD8W716uXuwxZFxCAAAAABMkXEIAAAAwNzrVSZHmZWMQwAAAABgisAhAAAAADBFqTIAAAAA82+1UuVZyTgEAAAAAKbIOAQAAABg/sk4nJmMQwAAAABgisAhAAAAADBFqTIAAAAAc69Xr17uLmxxZBwCAAAAAFNkHAIAAAAw/0yOMjMZhwAAAADAFBmHwHq78mW2Xe4uAKzV9f/6A73cfQBYmxP+Y59a7j4AwPoSOAQAAABg/ilVnplSZQAAAABgisAhAAAAADBFqTIAAAAAc69Xr17uLmxxZBwCAAAAAFNkHAIAAAAw/0yOMjMZhwAAAADAFIFDAAAAAGCKUmUAAAAA5p9S5ZnJOAQAAAAApsg4BAAAAGDu9SoZh7OScQgAAAAATBE4BAAAAACmKFUGAAAAYP6tXr3cPdjiyDgEAAAAAKbIOAQAAABg/q02OcqsZBwCAAAAAFMEDgEAAACAKUqVAQAAAJh7rVR5ZjIOAQAAAIApMg4BAAAAmHu9evVyd2GLI+MQAAAAAJgicAgAAAAATFGqDAAAAMDc61VKlWcl4xAAAAAAmCLjEAAAAIC5J+NwdjIOAQAAAIApAocAAAAAwBSlygAAAADMvV6tVHlWMg4BAAAAgCkChwAAAADAFKXKAAAAAMw9syrPTsYhAAAAADBFxiEAAAAAc0/G4exkHAIAAAAAUwQOAQAAAIApSpUBAAAAmHurV61a7i5scWQcAgAAAABTZBwCAAAAMPd6tclRZiXjEAAAAACYInAIAAAAAExRqgwAAADA3OtVSpVnJeMQAAAAAJgi4xAAAACAuSfjcHYyDgEAAACAKQKHAAAAAMAUpcoAAAAAzL1erVR5VjIOAQAAAIApMg4BAAAAmHurTY4yMxmHAAAAAMAUgUMAAAAA2MxV1RWr6pNV9YPx505raLeqqo4fH4dPLL9WVX2pqk6sqndV1XbrOqbAIQAAAABzr1etXvbHJfSsJEd193WTHDU+X8q53b3H+Lj/xPKXJTmou6+T5Iwkj1/XAQUOAQAAAGDzt3eSQ8ffD02yz/puWFWV5K5J3jvL9iZHAQAAAGDubYCMv0usqvZPsv/EokO6+5D13Pyq3f3z8fdTk1x1De0uVVVfSXJhkgO7+wNJrpTkzO6+cGzz0yS7reuAAocAAAAAsAmMQcI1Bgqr6sgkuyyx6jmL9tNV1WvYzTW7+5SqunaST1XVN5OcdXH6K3AIAAAAAJuB7r77mtZV1S+qatfu/nlV7Zrkl2vYxynjz5Oq6ugkN0/y30muUFXbjFmHV09yyrr6Y4xDAAAAAOZer1697I9L6PAk+42/75fkg4sbVNVOVbX9+PuVk9w+yXe6u5N8OsmD17b9YgKHAAAAALD5OzDJParqB0nuPj5PVd2qqt4wtrlhkq9U1dczBAoP7O7vjOuemeTpVXVihjEP37iuAypVBgAAAIDNXHefluRuSyz/SpInjL9/MclN17D9SUluPcsxBQ4BAAAAmHubw6zKWxqlygAAAADAFBmHAAAAAMw9GYezk3EIAAAAAEwROAQAAAAApihVBgAAAGDurV6tVHlWMg4BAAAAgCkyDgEAAACYeyZHmZ2MQwAAAABgisAhAAAAADBFqTIAAAAAc69XrVruLmxxZBwCAAAAAFNkHAIAAAAw93q1yVFmJeMQAAAAAJgicAgAAAAATFGqDAAAAMDc61VKlWcl4xAAAAAAmCLjEAAAAIC5J+NwdjIOAQAAAIApAocAAAAAwBSlygAAAADMvdVKlWcm4xAAAAAAmCJwCAAAAABMUaoMAAAAwNzr1UqVZyXjEAAAAACYIuMQAAAAgLnXJkeZmYxDAAAAAGCKwCEAAAAAMEWpMgAAAABzr1f1cndhiyPjEAAAAACYIuMQAAAAgLm32uQoM5NxCAAAAABMETgEAAAAAKYoVQYAAABg7vVqk6PMSsYhAAAAADBFxiEAAAAAc2/1KhmHs5JxCAAAAABMETgEAAAAAKYoVQYAAABg7vWq1cvdhS2OjEMAAAAAYIqMQwAAAADmXpscZWYyDgEAAACAKcsWOKyqA6pqswj1Xty+VNXuVdVV9ZiJZW+pqh9tyP5tClV1dFUdvdz9WJPxNbrrcvdjU7u4r0tV3WW8Zr4cAAAAAC4WQYUN75+TPGC5OzGHnp9kqwscXgJ3yXDN/BsHAACAJKtX9bI/tjQbdYzDqtq+u8/bmMcYj7Ntkgu7e9lfge7+4aY+5qa6zszOa8PW7ogPvjcfetdhOfO003L13a+V/Z7ytNzwT26+ZNsvffbTOfJD78vJJ34/F5x/fq5+zWvlAX/xmNzq9nfaxL0GtiaPuOO18vi7XSdX2fFS+cHPz85L3vfNfPWHp62x/f1uefU84e7Xze4775Df/v7CHHPCr/Ky938rvz7bf/cAwPxZ72ykqtqzqo6pqnOr6qyq+kBVXX9i/dFV9fmq2quqvlZV5yV58rju5lX1uar6fVWdUlXPTVJLHGObqnp2VX2vqs6rqp9V1Suq6lITbRbKg59cVS+vqp8lOS/JFdbzPDZYX9aw/z+UKlfV9lV1elW9col2Dx3P4+YTy+5cVUdV1dlVdU5VHVFVN1m03dqu87Wq6u1V9auxz8dX1VT2Y1XtO3Fe316qzfqoqh2q6sCq+uG4r1Or6r+r6qrj+iVLwBeXc4/X+p/H/fy+qn49nuMdxvUL+3jOeM26qg6Y2P6RVfX1iW3fVlW7Ljrmj6rqsKp6VFWdML6PP1dV1x3P43VVdVpV/WJ8nbdZtP1Vquq143vmvPH67b+ozWPGvt2pqt5TVWcm+dIM13Odr0tVXaqqDqqqb1XVb8dr/qGqusFEmwMyZBsmyQUL12xi/WWq6mVVdXJVnT/+fE4pa2YD++KnP5lDX/3KPOARj8mBh7w117vxn+Slz3pafv2LU5ds/91v/E9ufPNb5VkveWVedshbs8dtbpd/ff4z891vfG0T9xzYWtz7Frvl/z3opnntJ76ffV726Xzt5NPz+ifdNrvudOkl29/iWlfMyx99y7z/yz/O/V7yqTzl9V/KH+9yufzrfrfaxD0HAC6OXrV62R9bmvXKOKyqPZN8JMmnkjwsyWWTvDDJ56tqj+4+ZWx6vST/nqFc96Qkp1fVlcftTk2yX4Yg3z8k+aMlDnVYkr2SvCzJF5PccNzX7kketKjtc5Icl2T/JCuT/H49zmNj9WVJ3X1eVb07ycOr6h+6e9XE6kcl+VZ3f23s232TfDDDdX7k2OaZST5XVX/S3T+Z2Hap63yNDEGqXyZ5WpJfZXit/ruq9unuw8fj3D3Jf43HeUaSqyR5VZJtk5ywPuc17me7JJ9McrMkByY5Nsnlk9wryU5JfrG++xrP82kZXtPjk+yY5FZJrjiuv22SY5K8JcnrxmU/Hfux/7jsXUmeneRqSV6S5DZVdYvu/u3Ece6U5I/H422X5N+S/HeGa3hikn3HNv+U5IdJ/nM8xo5JPp/k0kkOSHLyeJ6vqSGj8D8Wnc/bk7wjyYOz/v/G1vd12T7J5ZK8KMnPx2v05CTHVNUNu/vUJG9IcvUkj09yhyR/eN+NAdEjktwow/vnm0n+LMlzx309Y336C+vjI+95R+58r/vlbvfbJ0nyuL/5+3z9uGPyicP/O4/4y6dMtX/MUy/69nvIfk/I1479Qo77wmfXmKUIcEk89s//OO//0o/zni/+b5LkRe/9Ru54w53z8DtcK6/80Hem2u9xrSvm1DPPzaGfHgpMfnra73LYZ07KPz3kTzZpvwEANpX1LVV+UYbgyr27+8Ikqapjknw/Q6Dh6WO7Kye5Z3cfv7BhVb04yQ7j8p+Myz6Z5H8nD1BVd8wQ6Nqvu986Lj6yqk5PctgYoDx+YpNfJHnAjOXJT9tIfVmbtyV5YpK7ZwjYpKqukmTPDIGyBa9K8pnu3nuiH5/OcN2fkeTvJtoudZ3fmCFz8s7dvVBfc8QYUHxhksPHZS9I8r0ke3f36nHb72UIzK134DBDcPO2434On1j+3hn2seC2ST7R3a+aWPahhV+6+9iqSpJTuvvYheVVtTJD8Ovo7t53Yvn3knwuyeMyBFgXXDbJnt191thulwzX/cvd/fdjm0+OQdyHZAwcJvnbJNdMctPu/sG47MiqukKS51fVaxb+XSxcg+7+xxmvwXq9LmPfn7DoGhyR4d/Dw5Mc1N0/raqfjk2+tKhvD88QTLxzd392XHbUeH2fX1Uv6+5fzth3mHLhBRfkpO9/L/d76F9cZPmf3Oo2+f63v7ne+zn33N9lh8tebkN3DyDbrqzc+BpXyJuOOvEiy7/wvV/m5te64pLb/M9Jp+Vpe90of36TXfLpb52anXbYLve55W757Ldn+b4UAGDLsc7SxKraIcktkrxrMgDR3Scn+UKSO080/9ESAbXbJjl2MmOuu8/JRGBotGeS85O8dyxd3WbMjvrEuH7xIFcfuBhjGm6svqxRd38hQ/baoyYW75vh2r89Sarquhky4d6+6Hi/yxA4Wny8pa7znkk+muSsRfs4IsnNqmrHMcj0pxkCW3/Ijx2DcT9a33Ma3TPJqYuChhfXcUnuU1Uvrqo7jNmM6+P6SXbOeB0XdPfnMwSD77yo/TELQcPR98afRyxq970k15h4vmeGbM6Tl7i2V8qQvTfp/evZ/yR/CP6t9+tSQ5n7l8ZS6AuTnJMhKHr9xW2XsGeGa/PFJd7b22bIPoRL7DdnnZnVq1fl8jtd9MP35Xe6Ys48fc1jh0064gPvyem/+mXudI97b4wuAlu5nXbYPtusXDE1NuFpZ5+Xq+y4/ZLbHP+jM/L0txyXf330LfOtf7t/jj3wPqlUnnnYVzdFlwGAS2j16l72x5ZmfcY02ylDJtvPl1h3av6vnDRraLNrli5bXbxs5wzlo+ckuWDisZD9dKVF7Zc61rpsrL6sy2FJ9hmDsMkQRPzURIn3zuPPNy463gVJ7rfE8ZY6952TPHqJ7f9los9XzhAcWp9rsC5XSnLKOlutn5dkGJPv/hkyBU+rqjePpeVrs/DeW5/3ZpKcsej5+WtZPjmW5c4ZgreLr+17xvWX9L253q9LVe2VoSz7u0kekeQ2GYKOv1rU5zXZOUP25OJz+fK4ftb3NmwUX/rsp3LY6/4jf/OcF+Yqu+y67g0ANoE/3uVyee6D/yT/ecQJedDLj87jD/5irrLj9nnhvnssd9cAADaK9SlVPiNJJ9lliXW7JDl94vlSodOfJ7nqEssXLzstwziFd1xDP3626PnFCdNurL6sy9syBMYeWFVfyhDo2W/R8ZJhjL4jl9j+/EXPlzr30zIE3V62hj78LEN22gVZ8zX43yWWr8mvk9xkHW1+nwzjIXb35DlcJDjV3Rdk6PfLxvLh+yV5ZZLLZCgZX5OF996a3psb6uv/0zIEjf92DesXl3jP+t78ddb/ddk3yYnd/ZiFBTXMKr50TdW00zKM0fjQNaz/0XruB9Zqx8tfIStWrMxZZ5x+keVnnXF6rnDFtcenj/3MUTn4wBfkKc96fm55uzXdhgEumTPOOS8XrlqdK1/uotmFV7rc9vnVb5aeIfmJ97hevvG/Z+SNY3nzCT/7Tc5994X5r6fdKa/80HfyizPXOeQ2AMAWZZ0Zh2Mp71eTPGQsqUySVNU1k9wuydHr2MUxSf5sHGtvYdsdMkw8MunjGTKmLt/dX1niMWuwbrPpS3f/MMMEK48aH+cked9EkxMyBGxuvIbjfWM9DvPxJH+S5Ntr2Md54+QsxyV58OQMulV1mwyTvsziE0l2GTPg1mQh4PWHAOM4LuDt1rRBd5/a3W/IEECdDEyen2FykkknZMjI23dyYVXdLkNW3dFrPYP19/EkN0jy4zVc27Mvyc5nfF0ukyEAPOlRGSYImrTwiWfxNft4hjLs367hXH59CU4F/mCbbbfNta93g3zzqxedWPybX/1yrnfjm65xu2OOPjKvfukL8uRnPi9/due7bexuAluxC1Z1vv2TM3O7G+x8keW3u8HO+drJpy+5zaW2W5lVi0qMFp6vGMYLBgA2Y72ql/2xpVnfyVGem2G21w9X1X9mGE/tBUnOSvKKdWx7UIZZXz9RVQfk/2YyPneyUXcfXVXvyDCu4CszlE6uzhA4uU+SZ3b399ezv5tjX96W5OAkN03y/snZfru7q+opST44ju/37gxZaFfNEGT7cXe/ch37f97Yz89W1aszBCJ3yhB8u3Z3P25s9/wMQb8PVNXrMsze+4IMpb2zOCzJXyZ5R1W9NMMYgJfLMNvwv3X395J8LMN75PVV9fwMMwL/Y5LJmY5TVR9M8vUk/5Mhw/XmGcbie91Es+8kuW9VfXxs87Pu/llVPS/J66rqsLFPuyV5cZIfJHnTjOe0JgdlyHz8XFUdlCFguUOGYOIdJye0uQTW93X5eIay94OSfDjD7NN/neTMRe0WpoJ8RlV9LMmq7v5KhvEgH5thQpRXZLju22UYY/P+Sfbp7t9tgPOB3PchD8+rX3pA/vgGN871b/InOfJD78vpv/517rHXA5Mkr37pAUmSpz57+PmFT30iB7/0gDzyr/4mN/yTm/9hLMRtttkml93x8stxCsCce/Onf5iXP+qW+cb/npH/Oem0PPwO18rOl79U3vn5k5MkL3vULZIkz3zb/yRJPv2tU/PPD98jD7/D7vncd3+ZnXe8VP7fg26ab/34zPz8jHPXeBwAgC3VegUOu/vj40yzz88Q1Do/QzbXP64r+667f11Vd8swe+2hGUolXzse+3mLmj8yQxDkcRlmHD4vQwBsYdbYS2SZ+/Ku8bi7ZAgiLu7bR6vqTuOx3pAhU+zUJMeO265Vd/+4qm6V5IAMYwZeJcP5fSvDuS60O7Kq/mJs974kJ2aYsXlNZbhrOt4FVXXPDO+J/cefp2WYMOf0sc2ZVXW/DIG3dyf5aYYZnu+e5C4Tu/tshlmMn5Iho+7HSV6eIQC44KkZZkj+UIYA5AuSHNDdh1TV7zIEgD+YISj50QzvzXNmOae1nOtZYxbj85I8M0Nw8swMAcT/3kDHWN/X5fUZMgYfl2G27uMyZMwunpDlwxlmhX7y2O9KUuPrdq8kz8rwul0rQwbsDzN8ObC4LB4uttv9+T1y9m/OyvsPe3POOP3Xucbu186zXnrQH8YsPO2XF72VHvmh92fVqlU59OCDcujBB/1h+Y1udos8/6DXbNK+A1uHj/3PKdlph+3ypHtdPzvvuH2+//Ozs/9rjsnPxiDgrjtd5iLt3/+lH2eH7bfJX9zp2nnmA26Ss8+9MF/6/q/yL4d/ezm6DwDMaPWq1etuxEXU7BMTA1ur40850w0D2Kw97MCjl7sLAGt1wn/so64dYJkcc9c7L/tn2tt+6jNb1P8D6zOrMgAAAACwlVnfMQ43e1W1rnNZ1dIr18s4Cc7aIuCru1t+73qoqsr0xCUX0d2LJzsBAAAANrAtcXKS5TZPGYcXrOOx3/J1bYvzw6z9Wi4eD5I1u3PW8d6sqt2XrXcAAAAAazA3GYdJ/nQd60/eJL2YD3tlmIBkTdY6IQ4X8dWs+73pegIAAMBGJuNwdnMTOOzuryx3H+ZFd39zufswL7r77CTemwAAAMAWZ55KlQEAAACADWRuMg4BAAAAYE1WrzLP66xkHAIAAAAAU2QcAgAAADD3erXJUWYl4xAAAAAAmCJwCAAAAABMUaoMAAAAwNxbvUqp8qxkHAIAAAAAU2QcAgAAADD3etXq5e7CFkfGIQAAAAAwReAQAAAAAJiiVBkAAACAudcmR5mZjEMAAAAAYIqMQwAAAADm3uotPOOwqq6Y5F1Jdk/yoyQP7e4zFrX58yQHTSy6QZJ9u/sDVfWWJHdOcta47jHdffzajinjEAAAAAA2f89KclR3XzfJUePzi+juT3f3Ht29R5K7Jvldkk9MNPmHhfXrChomAocAAAAAsCXYO8mh4++HJtlnHe0fnORj3f27i3tAgUMAAAAA5l6vXr3sj6rav6q+MvHYf4ZTuGp3/3z8/dQkV11H+32TvGPRshdX1Teq6qCq2n5dBzTGIQAAAABsAt19SJJD1rS+qo5MsssSq56zaD9dVWsctLGqdk1y0yRHTCx+doaA43ZjH56Z5IVr66/AIQAAAABsBrr77mtaV1W/qKpdu/vnY2Dwl2vZ1UOTvL+7L5jY90K24nlV9eYkf7+u/ggcAgAAADD3tvRZlZMcnmS/JAeOPz+4lrYPz5Bh+AcTQcfKMD7it9Z1QGMcAgAAAMDm78Ak96iqHyS5+/g8VXWrqnrDQqOq2j3JNZJ8ZtH2b6+qbyb5ZpIrJ3nRug4o4xAAAACAuddbeMZhd5+W5G5LLP9KkidMPP9Rkt2WaHfXWY8p4xAAAAAAmCJwCAAAAABMUaoMAAAAwNzrVauXuwtbHBmHAAAAAMAUGYcAAAAAzL3VW/jkKMtBxiEAAAAAMEXgEAAAAACYolQZAAAAgLnXSpVnJuMQAAAAAJgi4xAAAACAube6ZRzOSsYhAAAAADBF4BAAAAAAmKJUGQAAAIC5t0qp8sxkHAIAAAAAU2QcAgAAADD3Vkk4nJmMQwAAAABgisAhAAAAADBFqTIAAAAAc8/kKLOTcQgAAAAATBE4BAAAAACmKFUGAAAAYO6ZVXl2Mg4BAAAAgCkyDgEAAACYeyZHmZ2MQwAAAABgisAhAAAAADBFqTIAAAAAc8/kKLOTcQgAAAAATJFxCAAAAMDcMznK7GQcAgAAAABTBA4BAAAAgClKlQEAAACYeyZHmZ2MQwAAAABgioxDAAAAAOaejMPZyTgEAAAAAKYIHAIAAAAAU5QqAwAAADD3VrVa5VnJOAQAAAAApsg4BAAAAGDumRxldjIOAQAAAIApAocAAAAAwBSlygAAAADMPZOjzE7GIQAAAAAwRcYhAAAAAHPP5Cizk3EIAAAAAEwROAQAAAAApihVBgAAAGDumRxldjIOAQAAAIApAocAAAAAwBSlygAAAADMPbMqz07GIQAAAAAwRcYhAAAAAHPP5Cizk3EIAAAAAEwROAQAAAAApihVBgAAAGDurV7uDmyBZBwCAAAAAFNkHAIAAAAw90yOMjsZhwAAAADAFIFDAAAAAGCKUmUAAAAA5t4qlcozk3EIAAAAAEyRcQgAAADA3DM5yuxkHAIAAAAAUwQOAQAAAIApSpUBAAAAmHsmR5mdjEMAAAAAYIqMQwAAAADmnslRZifjEAAAAACYInAIAAAAAExRqgwAAADA3DM5yuxkHAIAAAAAU2QcAgAAADD3TI4yOxmHAAAAAMAUgUMAAAAAYIrAIQAAAABzb1Uv/+OSqKqHVNW3q2p1Vd1qLe32rKoTqurEqnrWxPJrVdWXxuXvqqrt1nVMgUMAAAAA2Px9K8kDk3x2TQ2qamWSg5PcO8mNkjy8qm40rn5ZkoO6+zpJzkjy+HUdUOAQAAAAADZz3f3d7j5hHc1uneTE7j6pu89P8s4ke1dVJblrkveO7Q5Nss+6jmlWZWC97bHbFWq5+wCwNif8xz7L3QUAADZTr+0fLftn2qraP8n+E4sO6e5DNuAhdkvyk4nnP01ymyRXSnJmd184sXy3de1M4BAAAAAANoExSLjGQGFVHZlklyVWPae7P7jROrYGAocAAAAAsBno7rtfwl2ckuQaE8+vPi47LckVqmqbMetwYflaGeMQAAAAAObDcUmuO86gvF2SfZMc3t2d5NNJHjy22y/JOjMYBQ4BAAAAYDNXVQ+oqp8muW2Sj1TVEePyq1XVR5NkzCZ8apIjknw3ybu7+9vjLp6Z5OlVdWKGMQ/fuM5jDgFHAAAAAID/I+MQAAAAAJgicAgAAAAATBE4BAAAAACmCBwCsMFUVS13HwAAANgwBA4BuMSqatuqqh5n3KqqnQQRAQAAtmwChwBcIlV16yRPTHK18fmTk7wjyRWXs18AAFuiqlox8bsvYoFltc1ydwCALd5lkxyY5JZV9a0k/5LkH5OcuZydAliwKCN6RZLtu/vcifUrunv1snUQ2OpN3qeS/CFYOLEMYFmU+xAAl0RVbZ/kPknemeEP3RckeakP4cDmYFHQ8AFJHp7kpkm+nuTT3f26xe0ANqVF96n7JdknydWT/DLDl7M/6O4Llq+HwNZMqTIAl0h3n5chu3DbDP+v3DjJVZezTwALJj6MPzr/9wXHp5JcK8k/VdU7J9sBbGoT96n9krwnw3AvnWSPJJ9P8pSquvyydRDYqsk4BOBiW/iGvKp2T3KDJH+U5N+TvDfJM7v7lOXsH0CSVNUNknwsyRuSvLq7z6qqHZKcnORnSfbq7p+MbWUeAptcVd0iyQeS/EeSQ8b71OWSnJHh/vWY7j5tGbsIbKWMcQjATCY/VE/8/FGSH1XVpZOsSnLw2PQfuvtn43Z3SnJGd39zeXoObG0m7ldXT7Jdko9291nj6rcn+V2S/br7J1V17e4+SdAQWCbXT7I6yRET96l3JPlJkud192lVtV13n+8LDmBTEjgEYL0tGoPnzzJkGe6S5GvdfUR3n7tQ9pcheLiqqt6Q4UP7YUkenUTgENioJiY7WZnkwiRXyTCEwinj+o9kGOdwr+7++pjp8/iq+vfuPmG5+g1sHapqm+6+cNHiGyfZtru/Mbb5aJKb5P/uU3dN8riq+svJyZ0ANjaBQwDW20TQ8FFJ/jXJTzOMw7O6qj7Y3U/v7nPG4OHqJIck2SvD/zfP7+7DlqnrwJyrqj9Jsl13f6W7V1fVQ5PsXFWvTfLzDPekfcYJUm6S5H7jh/HtkuyZIdtn1XL1H5h/VXWzJCd392/G549N8u3u/nKSzyV5VlU9LMlDMny5cf/xPnWZJH+aZNcMX4L8aDn6D2ydTI4CwEyq6kEZxt95RXffMsljM2QUPqmqXp8k3X1Od785yS2SPDfJg7r7n8ft/d8DbFDj7O5PTXJoVd2hqp6QYSKU33T3hd19dJL3J3ltklsmecj4YfxySR6W5BlJ3t3dJy7PGQDzrqqulGT/JF8anz80yRuT3Gps8r0kn07ypiR3SXLb7v5aVV0qyUMz3KfeMQ4PA7DJmBwFgPVWVX+U5DVJvtzdLxi/Of9ckg8lOT/JI5O8prv/Zg3bL5QPAmxQVXW9JF9OcnaGrJyndfd/LJQEjvevf01y/wxffpyR5JoZAof/0t0vHvdj7DBgg6uqlRkCgK/IMGPyrhm+fH1Xd/9+bHPvJC/JMAzMvyQ5NcnNkzwxyYHd/ZKxnfsUsMkIHAKw3qrqKkkOyDB+4W+SfD7JUd39+PFD+eczZB++pbsft2wdBbYqVbWyu1dV1QuT/FOGD9tP6e73j+sXZoC/XJJ/TnLrJNdK8skM97BDx3a+3AA2qqr6jyRPSfKrJH/S3b+oqm27+4Jx/Z9nCDA+MMPwCV9L8r7ufuO43n0K2KQEDgGYSVVdaZzZ79kZ/rB9SJKTxjHF3pLkNkmuneS+3X3kMnYV2MpU1d9nGP/rsRk+lD+zuw8f1/3hw/ZY2rxDkjMnlvkwDmw0Y8bhtklenORySe6b5JwMJcmnVdX23X3e2LaS7DRuen53/3Zc7j4FbHIChwBcLFX1riTXGcc5TFXtmORtGcbn+XJ3f3E5+wdsvarqOkmOTfLrDMHDD47LV2YoT/5Vd5+9jF0EtlJVddkkFyR5UJKXJzk3Q/Dw1xNDK1ylu381tl8xfjmrPBlYFgaoB+Di+nySm1fV3uOH9H0yzPj3xYWgoYlQgI1hzMZZ+P2PqupGVXXrhWXjJCe3S3LlJAdW1V5jluHeST6Q5AabuMvAVmbRferKVbVbVV0+yXljZuF7kjwryaWTfLGqdhqDhg9N8sOqutoYLFydJIKGwHLZZrk7AMDmY8Zvsz+WYda/92eYZGCHJC/s7i8vNFBOA2xok/epqnpEkr9L8sdJflNV30ryiO4+u7u/X1W3yzCB05uTfDPD2IYHdfdxy9N7YGuw6D71kCT/kCHbOUleV1WHjfeod2WYKOUlSX5QVR/L8EXswd39s2XoOsAUpcoApKqu193fvxjb7Zohq+eqSU7s7k+My43BA2xUY9DwDRlmHv1gkvskeWGSo5M8vLt/Mba7doYxxVYkObK7Xz8uV/YHbFRV9cgkr0nyuiSHJvnHJPdL8qEkL+nu71XVtknukeRxGWZaPqy7XzNu7+8pYNkJHAJs5arqKUlemWTv7v74uGytH6jXtt4fucDGVlW3SPLWDB+wDxyHS/hqki8l2SPJCUkePBE83DbD373nj8/dp4ANbtEkTLdN8qYkb+zuf62q6yX5cpL/zTBcwnszVGqcMDHz+xW7+/TF+wJYTsaeAuDnSY7PUDqzZzKMozM5Ns9iawsq+iMX2JCq6klVdaOJ5yuS7JxhIqbXVtV1MwQM35XkLzJMNnD7JG+sql2SpLsvmAgalvsUsCFV1WOq6jrjJCYLn7GvneTEJP8+Bg2PSfKu7r5ZkjcmeXiS/1dVN174u2oiaOg+BWw2ZBwCkDFg+OwMf+Q+fqLkeJ2lfMr9gI1lzCQ8IcMH7sctDKkwBgSv2d1fqqr/zjBG2JO6+1dVdYUk30pytXHbW3T3uctyAsDcq6q7Jflwkk8leUp3/2hcvm2SW3b3sVV1RJJzMtynflFV18ow83sy3N8e091nbvLOA6wHGYcAW6karByffjXJR5NckOQtVXWXZN2Zh4sG/75/Vd1+4/Ya2MqcnGTPJLsneXNV3WC875w6Bg0vneRGSX7Y3b8at9klyS+SPD3JiwUNgY2pu49KcmCGe9Grx3FVFzKdj62q3ZJcP8knFoZPyJA1/dMkRyT5uKAhsDkTOATYSvVgVVU9PsmRSR6c5MwMH7rfXlX3XGi3VPBwUdDw7zKUCe6wiboPzLlxfK9V3f3JDCV918wwHusNJ5pdfnzsXFU7VNUOGWZOPjvJB7v7sHFfa/wCBODiGrMK090vyDBZ0/UzlCZfc1HTFUluNW6zXZKbJflZksd292vH5e5TwGZJ4BBgKzaW1xycYfDufbv7FkmekORXSd5UVXdPpoOHi4KGf51hVtO/XShxBrgkJsf3Gr/EuFOG8Vj3TPKKyczDJM9Psl+GMQ8/kGEG049298kL+zOcArChjfegC8bfb53kx0m2yTBD8mTw8MwkhyfZu6o+l+TNSV6V5DPdvWphf+5TwOZK4BBg63arDB/G35ehJDDd/aYkL8gwZtjbJsuWkyWDhv+WYcyeQzZx34E5NXGPeXSG+9M1MwQF353kdkkOyZDZk+5+fZJHJ1mV5LdJntbdLx+3l8EDbBSL7lNHJ7ljkk+Ov983Y9lyd5+T5KVJXp9k2yRXT/L07v7XcXv3KWCzZnIUgK1YVf1Lkid2947j8+0mZh59foZMntMyTJhy+KJtF4KGT+zuN2zSjgNzb5w84FNJ3pnkRd19TlVdLsldMpQEfjfJXyU5YcyKvlSSCxYyeMZSZ7OSAhvNOFvyZzNkEf5zd/9uXP6SDBUcx2aoyDh5vEddmGTHidmT3aeAzZ6MQ4CtyMK32lW1cP//fJIVVfW3Yybh+Qvj9WTIQPxRhjF4rrxoP0/NUGazv6AhsJFsk2ECgZPHjJ1099kZJhN4Toby5ZdkmJAg3f37iaBh+TAObAJXzTDO6ie7+3cTYx7+vyTvTXK/JK+squuM96gLk5yRuE8BWw6BQ4A5t6gEZptFP7+U5JtJnpTkIckwC+D4h+81krwnyQPH8uVU1Yqq2jXJo5I8obvfuAlOAdg6bZPhb9XtkmESgoUvODKMF/bjJPsk+a+qusLkhsYKAzaR08ef10v+8DfUduOyZ2a4T/1ZkjdW1Y5jm578CbC522bdTQDYUi0aj3DvDANzXzfJKVX1uu7+dFU9IsOkAi+tqrtmmGH5lkn+NslTuvuHE/taneTnVXX/7v7FspwUsFXo7u9W1fsy3Js+3d3frqqVGcYy3DbJD5K8LMk53X3mMnYV2Hr9OslJSR5RVV/o7m8uDPmS5FpJzkvyhSSf6O7fLFcnAS4JGYcAc2wiaLhfkncluUyGEpndkhxRVc8eZx69U4bsw73Gdo9I8rzufvPifY2/CxoCm8IhSf43yUer6tbdvWrM2rlHhjLmT3f3WxMTDACb3vj30NOS3D7Jc6vq9klSVZdNskeGIV/+ursPHZe7TwFbHJOjAMy5qtojw2ykr07yhu4+s6qukmEMw+OTPKi7f1FVl86QxXONJGd394/H7Q3cDSybqnpYkqdnyIT+coYvvm+e5PndfeBy9g3Yui1UdlTVwzNMkHJ6khOSnJ9hIqcDuvuly9hFgEtM4BBgzlXV/ZO8Jsn9u/ur47KPJrl+kgd399eq6ooLM/wt2rbafxTAMpj80mIcYmGvJPdMcmqSo7r7beM69ylg2VXVbZI8LMmfZrhPHbEwgZz7FLAlEzgEmCNL/WFaVc9O8uzu3nF8/rEkN06yV3d/vapum+TZSf5SCTKwsc3yAXpx2yWey4gGNrhZ7zWLxpRemWSb7j5vfbYF2NwZ4xBgjkz80XrnqrrZuPgz47K/rqoPZgga7jMGDS+T5I5JLpXkssvRZ2DrMnGf2rOqbr22Mb8m2i60qUXrfRgHNriJe89eVfVH67rXLBoHetVC0HB87j4FbNEEDgHmSFWtqKprZZgl+U7j4p8mOS7Jy5PcNsndu/t/xjENH5Lk75O8e2H2ZICNraoenOSjSa6b4YuLtVr4UO4DOLCpVNVDk/x3kvtUlc/NwFZLqTLAHKqq9ya5aZLbdvfpVXXLJG/LMKvyu5N8I8ltkjw6ycu7+8XjdsbgATaqcXKmpyU5L8P959z13G6yFHCn7j5jI3YT2IpV1fZJ/ivJ15Mc1N1nr+d2/o4C5o5vTgDmyEQ53zuSXCHJQ8axdb6aZN8kH0uyT4bswz9K8ncTQcMV/tgFNqaq2jvDzKMPSvK9ixk0/Kskr66qy2+8ngJbqzHT8OAkOyb5/MUJGlbVM6rqORuxmwCbjMAhwByZCPx9IMmPkjxqobSvu7+R5G+S3DDJjZI8tLvfnBi4G9hkbpZkzyTXTHJucpEvPJa06MP4Xyf5zySf6O6zNnJfga1MVW2XoWLjsUnukmS79dxu8j711CT/kuT0jdRNgE1K4BBgC1dVD6iqF1bVzlW1TTIMzJ3k+UluUVWPn2i+ahy0+8wk54/bl6AhsDEtjA/W3S9M8owMH8afX1V7rC3TeYmg4UEZZoA/dBN0G9hKLHyB0d3nJ3ltkmcnuSDJo8dg4lq3XXSfelWSJ3T3azZurwE2DYFDgC1YVV0hwwQn/y/Jp5IcVFVXHVd/Kcl3ktyvqrZdHCCcmGxAeTKwQS2RRTg54+irMnwov06S51bVTde0j0Ufxv8tyV919xs3SqeBrcrkfWrRrMinJHl7kpcmeViSl68pM3oN96kndvebNmLXATYpk6MAbOGqasckV0zykgyzJl85yRuTHJLhg/kHktyhu7+4XH0Eth6LPkjvmeSeSW6c5LNJvtDdR4/rnpNhVvdPJ3led39rDfv7mySvzBA0fMPGPwNg3i26T901ye2T7JzkxxkmQ7mwqq6U5KlJnpfkP5I8bU1fto7lyf8W9ylgDgkcAmwhFv2Re8Ukl05yTpILuvucqto2ydWT/FWSB4y/H5Fk7yQfSbJvd5+zLJ0HtjpV9Zgkr07y1QxDI9wiySlJ3tHdLx3bPDND8PCYDMHD4xft41EZJlPxYRzY4CbuU8dnCBxeKcnPkjypuz8/zgL/pAyVHW9I8jeLh3epqsdm+ML2CTINgXmkVBlgC7AoaLhvko9nKEM+Ocnrq+oO3X1Bd5/c3c9M8uAkf5fkluMuPixoCGwqVXXrJC9L8oIkD+7ueyS5Q4bMwzsuDKnQ3S9L8u9J7pdktyV2dU6GLz0EDYENqqrumOTlGTIKH9Dd18twL7pxkidU1WW6+1dJXje2e3KG+9jkPlZkmATlEYKGwLyScQiwBamqRyR5S4Yy5O8muW6SByW5MMOEAUcuan+1JNfq7i+Mz2tNZTYAF0dVbdPdFy5a9rgkT0+yd3f/cFz23iS3Gpd9vap26e5Tx3W37O6vbuq+A1uvqvqHJH+RIWh48rjsI0mun+Sh3f0/E22vluTq3f3lJfazwiRzwDwTOATYQlTVLhkyDT+X5B+7+9xx+YOTPCdJJXl4d393XH6RP2T9YQtsaFV1SJKvJHn95JcSVfXCJPt39y7j848muUmS+3X3N6rq9knuOm536sR27lPARrXwJWpVvSvJH3f3rcbli+9T90ry5939rEXbu08BWxWlygCbqSVm8Nshw2Qnx3b3uVW1TZJ093uTHJzkhkluvdB48R+1/sgFNqSqumyS3yc5cfwQPnnPOjXJjlV1y6r6cJKbJtlr/DB+6QzlgHtk+MLjD9yngI1l4R418SXHl5Jcu6puVlXvz3Cfuv94n9oxQ1nytapq58n9uE8BWxuBQ4DN1OSspFV1q3HxBRnG3sk449+24+9vyDDpwD2Xo6/A1qe7f5vk6d39qap6UJK/rarLjKvfkOSkJMcmuVmS+47lyZdO8pAkj01yeHf/fDn6DmwdlvgSdtLXM4xP+Mkkt0lyh+4+fvzb6oEZ7lMf6u5fbvyeAmy+BA4BNmNjGfJHM4xl+Nsk30/ywLHML919QVWtGMuYf5/khGXrLLDVGCcEWPgCY7sk90nyyiSPGScUOD/DbMlfT9JJbjiO0fq8DBnS/97dh477WtsHe4CLbfJL2CQHV9WLqur+47qjMowZfekkP07yx1V15yT/mOE+dXB3HzZu7z4FbLW2We4OALC0qrpKkltkmJX0Q93926raP8nnk7ykqv61uz+UZMckd0uya4YP6QAb1UKp3php+JMM46yem+RVSbapqlcnOSrJGUkOHB9XzjBG6z9092vH7Y0VBmxUVfXwJG/K/00qd3ZV3aa7n9PdL6+q3ydZ+KL2nAxfwv5jdx88bu8+BWzVTI4CsBmqqr2TPCHJ9ZI8t7vfXVUru3tVVf15kv9Osm2G8uQzM5Qvv6y7X7RcfQa2LlV13yQfSvKkDLO9XzbJi5M8Pskzkrymuy8Y2147w+zvZ3b3b8ZlPowDG01VrcwwPvQ7knwmyX8muXqSA5LsmeSN3f2Mse1VM3y58fskv1sYRsF9CkDGIcDmao8k987wQfvccdnq8Q/YT1fVLZM8OsNA3icl+ddxkhR/5AIb3fgh+84ZMgn/q7vPS3JeVT1nbPKKJF1Vb+ruc7r7pEXbl/sUsKEtzJicJOOXrecm+XmSI8dxWb9XVf+UIUD4l2Pzv+/uX1TVLycnenKfAhgIHAJsRhaCft39gqo6M8lBSZ5fVT/u7q9n+CC+TXefnKGEecntN3G3ga1IVT00yZOTXC7JQd199sS967SJ4OHLkqysqtd197mT+5iY1RRgg5kY0/D+Gb6AvWKGqoyF4RGqu0+sqheOmzxuXPaMiYDjRX4CbO1MjgKwjBYPtj0Z9OvuVyV5dobxeJ5bVTcZl1+4pkG6BQ2BDW3yflNVOyb5RZI7Jbl5htK+dPfqiQlTTssw5uF/ZZgw5bqbus/A1quq/iLJezJkRd8syY2SPKyqdpwICp6U5IUZxjV82sKkcwBMM8YhwGZgHCvsPkl2T/KJJF/s7uPGdf+UYbywTyV5Xnd/e7n6CWy9quoxGcZevWuS22eY/OQHSZ7Q3Z8b2/wh63mc4Okm3f3p5ekxsLWpqp0yZDt/O8mhSXZJ8tQkf5XhC42Dx5LlhfbXTbJ7d39yGboLsEUQOARYZlX16CRvSPKdDGMa/kmS4zMM2v26sc1zkvxdkmMyBA+PX46+AluPybHCxoznozOMafiW7v51Vd07yUcyfKnx3O4+Zmw7NWSCYRSAja2q9krygAxZhn/T3V8Yl18pyfOSPCVLBA8ntnefAliCUmWAZVRV10jyuAwlyXfv7lsluVuSlUmeXlUPSZLufnGSVyW5X5KrLVN3ga3IRNDwNkm2zzCD8pvGoGF198eS3D9DBuKLqurPxu2mPnj7MA5sAvtlmDhulyT/m/whGHhahpmUD84wPvTfVNXlFm/sPgWwNIFDgGVSVffJ8M34dkk+OfFh/HNJ/jLJpZLsV1U7JEl3vyjJrbv7o8vWaWCrUlXXz5DpfFySq3b36f+3qqq7P5wheHjHJC+vqjsuU1eBrdTE+KoPTvKmJLsmeVlVXWUhGNjdZ2QIHr4xyYuSXG95eguw5VGqDLAMqmrbJF9Icqskv0yyR3efWlUrk6S7V1XVI5O8dVz3jUXbK6cBNrpxvLAnZ8iMPj/Jnbv7l+O9auEDeY8zmH4gyYO6+/3L1V9g/k0Oo7CG9W/LMG70W5O8aMw4XFh3pSQ37u7PbvyeAswHGYcAm9j4B+8FGf6ofX+SnZM8cZztb1WShT+Gz8jwwfwyi/chaAhsLJOzKI9ZOq9J8tok10jylnH5qiQrxqBhdffhSa4taAhsTIvGXr1DVT21qg6uqr2r6oZJ0t2PSnJEhrLlfxqDhRnXnbYQNFzIVARg7bZZ7g4AzLvF34wv/D6WJu+f5ApJ/jbJT6rq/d19RlVtn+S6GYKHv1uGbgNbkUX3qW3GrObzkqS7T6+qN2b4UuNFVfXh7r7fmBn9h8zDXHRMMV9uABvcRNDwsUn+NclvM4zB+ldJPlNVr+zuD3f3I6rq7UkekeGe9oLu/vWifblPAawHpcoAG9Gib8Zvm+RGGcYu/Hx3f31cvlOGEr9bZ5id9Kgk107yqCQHdvdLl6HrwFZi0X3q/hnKkm+U5KdJXp7hfvXbqrriuO5FSY7o7r0Xbw+wsVXVXZMcnuT5Gf5+OinJ/kmemeSsJM/o7k+Nbd+Z5KFJ7qI8GeDiETgE2ASq6jFJ/j3JBRmyvS+d5MAkh3X398fg4WFJ7p3kW0k+kuTY7v7guL0MHmCjqqr9Msze/oEkRyb5pwz3rNckeetE8PAxGTJ9Ptfdd16e3gJbm4UvKarqhUn2SrJnd/9iYv1Dk7wuyfuSPLW7zx2X37O7P7EsnQaYA0qVATaCRRk8eyT/v707j/d1rvf//3jZgyFTKDk06aiUlAYS1TfpJKGjSRopHfUVSXWEwiGkkKFCZDwNSvHLUL/TqTQXQhLH0WCo1PeYFXtje37/eL8/+rTszf6y1vrsvdbjfru57bWu6fZe/1yu63m9368XBwAHAmf3Q95Eeylfvar26o1R3kYr5P0c4ELa13SqamaSeyb5T5A0jVTVS4H9aY0EDqmqJwKr0xqiHNCPObUvWz4ZeARw0wIvKEnjYFBzNV3f/HhghUFoOHhOSvLlqloP2AnYvarm9NP+ox/nR1hJeggsCCtJE2AoNHwusDLwY+CEJJf1//YA3gdsD2zWz7kBeDPw37QZPttW1bKGhpImUu/yvhHwvR4aPhW4BDgZeBJwNbAX8JbexOlG4ONJPt3Pr/leWJIeoqp6UlWtMQgMq+rlVfWyvvs64B+q6mU9DLynqgYTYn4NzAIeMbaEgqGhJD00BoeSNEH6jJ2fAN8CHpXk//TtM/qMxCOAs4D3V9Xy0JoQAK8CLqctXX7GSAYvacoaG/T1Lu9fAz5bVcsAn6fNjt43yS3AYcCqwIeAHfrsnrlD51v3RtK4qarHAAcBB1bVir3cyzeAZfohRwE3AnsAa/dnqnv6R5DHAVcBc/2oIUnjw+BQkibODcB7aF+//7GqngmQZN7Qi/ZVwCOB+x5ue3j4WuDDSX4yuUOWNJWNKaOwTlU9oe/6VZIf0D5WPAo4cagD6Rzgov7vHGdBS5pISf5E+/D6ZlrN5+Noy4/PqqoZfYnyDsC6wKnA+6pqc1pzlA8Cn0vyJz9qSNL4MDiUpAmS5HbgNOBg2lfyg6rqUYP9/cv4bFrAuNTwl/EkNyQ5sB/nvVrSwzYmNHwTbZbhvlW12tAL9jLAo4F/6MfNBp4MfCfJk5N8ZgRDlzRNDNU0/CQtNHwecDGtGdO9Seb1Q79Ja5AyC9iPdj97M/CRvqLDMgqSNE5sjiJJD9OYl/HZwOwkfwFIcktVfRUI8Enga1V1PHA97Uv5O4E9hrsCjmVNHknjYeg+tS1wArA3cF6S64cOuxr4JbBrVa0L/JVWj/VDgwOG73mSNF6GuiYPmpgsA3wPeCmt2ckBSf5rcHySn/Ra0k+nf4hN8ut+LRuhSNI4KZ/7JOmhGxMabg28g7bU7yrgJOCrSe6sqhWBV9O6k65K65p8FfDTJEeNvZYkTYS+NPlM2myd/ZLc0bcP38s2AfYB1qF1Tj46yWEjGbCkaWHMPejJwJ+T3Np/35VWa/ULwAFJrujbZwLL9xIv872WJOnhc/mbJD0MQw+5bwVOBP4P8H7gMbQX7w9U1dK9wcAZtM6k1wK3ATsNhYYzfMiVNAlWoHVKvnAQGsLfNzhJ8h3gdbSPIJsNQkPLJkiaCGNCwzcAp9NmPT8WIMnhwAeANwJ79mARWjO5z1XVmsPX83lKksaXS5Ul6WGqqpfQ6usckOQTVfUPwONp4eCuwLyq+mSSm6vqzH7akcCpVfWmJLcN1eyRpIn0OOARwDXQaq32rsr0358P3JXkouGT+ou9y/4kjbuh0PBtwKeBI4D/P8l1gyXHSQ6rqhm0utFPqqrLgO1pz16/HdngJWkacKmyJD0MfZnMnsDaSbatqqcA5wP/Tqsf9l3gicDHgE8muaOqlgdeAxwO/ArYJMmcUYxf0vRSVasCPwMuT7J53zYjybyqWppWi7WAXZPcOcKhSppGeq3CM2mh4WeS/LVvXx2YmWTwsWMnWn3oecDJSY7s212eLEkTxCUnkvQwJLmHVsvwhKpaCjgVOBvYN8mNwAf7odsDH6mqmUluA74CfBi4zNBQ0iS6GfgisH5VHTcUGq4AbEOrxfpTQ0NJk+wpwN3ASUn+WlXLVtVxwH8AP+iN5UjyaWBT4J+GQsMlDA0laeK4VFmSHqYk1wLXVtUzabUN903yP333csCfgduB3/SgkSR/qarPJpkLfimXNPH6feauqvo4rUnT64GNq+oiYGXgBcDBSU4c5TglTW0LeOa5izbb+R1VdSuwM62swnHA2sBWVfXKJOckuWHMtSyjIEkTyOBQksbPUsDqwGoAVbUkrZ7YmcDugzqGgwfmQWgIFvKW9PANZg8uaH+S9Jk5N/cupecB/wysC1wE7Jjki/1aS/gyLmkiDNU0fBnw6yS/Ay6glXrZlfbB9QLg3b3Ey/OAzYH7zYT2+UmSJp7BoSSNn+uA/wT2rqpn0b6e7wjsMTY0HN0QJU01VbUV8I0kdy9EeHhvDwVvA04BTuklFO4Zup6hoaQJVVVPAk4Ebq+qzZP8rqp2oXV+vzfJVf24pYFnAX+ilVqQJE0yaxxK0jhJ8kdaE5QLgdcBmwEfSXLU0DGGhpLGTVVtA5wOfLIHgPN659EFGoSCVTV4DpxXVTV2vyRNlCS/AT7efz29qtZM8qckVw6Fhk8EtgMOAz6X5OLRjFaSpje7KkvSAoydHfhAs3CGj62qlYBZwNJJrn6wcyXpoer3myOAFwFfB3ZbmJmHkjRZBs9IQ/8uOVTj+d3A+4FbgVcPdU9+JfAu4Om0LsuHDF9rNH+JJE1PBoeS9CCq6rW0LqO/f4jn+5AradxV1aweEi4LHAX8L1pX9/cluWdhwsMxHz0MGyVNmKpabzBrsKpmJ7mr//wu4IPATcDWSX5fVU+jNXA6P8m5/Tg/wkrSCLhUWZIeQK8d9mVg6wdb/rcghoaSxlsP/O7uvz4a+A5wL/A24GMLs2x5TGj4JmCHh3qfk6QHUlXPAH5eVccB9A7vs/vPxwDHA88BTuvLli8HDhwKDe2eLEkjYnAoSQtQVSsDWwD7Aycv7Eyc4VphVbXixIxO0nQ2FPhtR6urugVwGW3Gzk7AoQ8UHo4JDXcBTgVucMahpPEw/CzU3UQrq7B9VX0K7gsPl+w/HwRcQuvy/q2qWh64r2mTH2ElaXTsqixJ81FVr6M1OFkdOLt3IF2Y84ZfxncG1qqqDy/s+ZK0sKpqPeBIWoOBI5PcVlWrAMcC2wL3VtUHxy5bns996lDgX5J8dTR/iaSpZMw95hnAX3rX5IOBucC/VhVJ3jNU6/CJtKDweOACn5skadFhcChJ87cC8Nr+86MX5oQxD8rvoX1Z/xcffiVNkDWBAF/voeGMJDdU1Q7AGcB7aR2T9xgsa55PaHg4sGOS40fzJ0iaSuZTAmEf4Cf9PvTHqjqyH/qvfVb0u6rqkcD6wC3Ax5NcP/ZakqTRcamyJHXVAfSX6G2AecBbq2qdBzt3zMv4EcAOvoxLmkB3AMsBKwL0ZcmzktwM7Ex7CX8jcOzg/jbmPvVJDA0ljaOhe8y2wAnA52hdkf/Y9/+R9oy0H+356hrgm/3Y7w5Cw+FrSZJGy+BQ0rQ2XIMn3dDvXwF2AF4A7FVVT13QNRYwg+eEiRy7pGnveuBGWs2w1QGGGqasAvwVuBz4wfD9rarei6GhpAlSVU8AdqfdZ45K8rO+fQmAHg4eAWwO/By4GHh3r3M4v/qIkqQRcqmypGlrTOC3GfBKYA3g18AxwLVJTu4Pusf14/ZNcuXwdYausRMu+5M0jsbcp1YAlgLmJLk1ySVVdRSwL/D7qjopyW+qahngScC3gd2S3NTPX6J3JX0y7SX9c6P4myRNeSvQ7kEXJrljsHG4K3KfGX1e/+8+Q/cpSdIiwuBQ0rQ1pivpp4Ef0e6LbwFeAxxWVccnObGq7qU1HJhVVfsk+dXwtarqXcBRwDuSnDiJf4akKWpMaPgG4F3A04Brq+qSJDsk2a93b98L2KKqzqctX94a+MhQaFiDl/EkO43gz5E0fTwOeARwDUAvoTCYDU1VbQDcleTisScaGkrSoqcsHSFpOquq9YEzaUtmjktyU1UtB9wKfBHYJcmN/dh30GYebp7km2Ousy6wXpKTJ3P8kqa+3mBgUCvsd8BjgTfRXso37fetdwBbAOv17V9J8ql+vg0GJE2aqloV+BlweZLN+7YZvQ7r0rQlzAXsmuTOEQ5VkrQQDA4lTTvDy2D6TMFdgFcluapv+xrw3L7t4qpaevBgW1VrJ7lizPVmJJk3uX+FpOmgqh4LnEPrknxYklv79l/SZha+bOjetRSwJMDQcS77kzSpqmo28G/AO2n3rnf10HAF2mzojwO7u0JDkhYPLlWWNC1U1SHA3CR7Jbl3aNnMWsCsoRfvc4BnAFskubSqNgVeXFUHJJkzCA2HX8YNDSWNl/nMDlyWNsPwZ0Nh4Fm00HCrJFdV1bOTXJRkDjBnzLUMDSVNmn7fuauqPg6sCrwe2LiqLgJWpjWcO9jQUJIWH3ZVljTlVdXKtBfvPapqd/i7zqO/BNasqnWr6ivAusCWPTRcGtgIeDbtYfc+voxLmghDNQ036J1Jl6I1Gvhz334u8EzajOhLq+rJwD5VtcmCriVJ46WqZjzQ/iTpH1dvBnYFdgauoD1f/ZnWQO6Afi3fRSVpMeCMQ0lTXpIbq2ov4DbgoKqaOXhopXXzuwi4ALiZVqfw+r7kbxvg3cCHkvxhBEOXNA31hk2HAtsBlwNXAbtU1eq0jshbJflFXw64BfBo2v1LkiZEVW0FfCPJ3Q9WoqWv7FgiyW3AKcAp/dnrnqHrWUZBkhYTBoeSprTBw22SX1fVp4B5wP5VdXuSI5NcXVWfptU5fAKwfp9p+HRgN+CjSU7q17LBgKQJVVWPAF5BCw7/I8ncPht6T1rTpjcmuaSqVqKFhnsDe82vO6kkjYeq2gY4FfhsVe2a5J6FCQ/7uYOAcN7wc5ShoSQtPmyOImlaqKrX017G16V1HYX2sn1Q3//PtC6lm9DCxYuBM5Ic0/f7ZVzShKqqVwKvBp5Haybw46F9RwA70GZIX0orv/B84IihZX9+3JA07vqHiiOAFwFfB3ZbmJmHkqSpweBQ0pTXQ8Mv0Dr8XUyr7/o+4MXAPkn2Hzp2LeB24K4kN/VthoaSJlSf6XwGLTS8E1gnyS1jurrvCDynH/Md4MdJvtr3eZ+SNO4GzeSqalngKOB/AWcD71uYmYf9Gvd91DBslKTFj8GhpCmtv4yfy9+W+N3Rt68N7AG8GfhAksP69iV6bZ7qBb6dwSNpUvRmKIfQZh2ekGSHvn3JJHOHjht0hR/8bmgoadyNCfzWpDWM2xd4FPBZWg3oBwwPx1zjTbRO8ccbHkrS4sNOVpKmlKqqoZ+XBO4B/hG4LskdVTUTIMkVtGU3twOHVNWH+vZ7+78Z/leSxsvwfWpYkqtptVW/Dryqqvbv2+cO7l3997vHnGdoKGncDQV+2wEX0uqqXgbcBOwEHNqbnsybX7flMaHhLrQ6iTcYGkrS4sUZh5KmpKp6HbBSkmOr6izg8cBGSW4fnq1TVScCmwKr0+qFXWBYKGmijHmR3pjWJXlN2tLjK3pX9ycChwPPBU5M8uF+vEv8JE2qqloP+B7wceDIJLdV1SrAscALgc8DHxw783DMvW5n4DDg3UmOH8kfIkl6yJxxKGlKGDPT8OXAacCMqlqetlR5ZWDfqlp2KDRcAVgFOAZ4QZLzDQ0lTaShF+ntaXXC/pU2c+dLwClV9fQkvwN2Bi4A3lJVB/dzDQ0lTbY1gQBf76HhjCQ30Jo1XQ68F/hY/yi7oNDwcAwNJWmxZXAoaUoYekBdgzZ78FDgpCS3AccD3we2BY6uqlV7E5StaR2Wv5fkp/1874uSJlRVbQp8EtgHeEWSRwIfBV4KfLg3RLmWFh5eBuxcVRuObMCSprM7gOWAFaF9wOgh4c20e9QtwBuBY6sbExp+EtjR0FCSFl8zH/wQSVo8VNXLaEtnZgNH95qGg26A2wGfAF4DXE9rlrIkcGCSHw6uYa0wSZNgY+AS4PQkf+jb/gm4Ejg4yZ1VtUyS66pqJ1qH5Z+MaKySprfrgRuB7avqN0n+MFRndRXgr7R71w+GV21U1XtpH3F3TPK5yR60JGn8GBxKmkpmAXNoMw6XhdZEoKpm9+YCu9G6AG4M3A1cm+RbYFdSSRNjbGf2XlZhfWDWIDSsqnOBZwBbJPlFVW0EPKuqTuwNU67ux3mfkjTuxswSXAFYCpiT5NYkl1TVUbRuyr+vqpOS/KaqlgGeBHwb2C3JTf38wX3qybTlyYaGkrSYMziUtFga+zIOkOTcqrqNVrPwvVV1WZLPJ7lrqCHKZf2/4Wv5Mi5pQgy9jK/eg8IZtCBwo6paDfgcLTTcsoeGK9KW/d3Vjx2+lvcpSeNqTGj4BuBdwNOAa6vqkiQ7JNmv35v2AraoqvNpy5e3Bj4yFBrW4D6VZKcR/DmSpAlgV2VJi50xD7kbAevSHnK/DZwHrEHr8rcUsE+SL/VjDQglTbqqeiHwDeDpSa6pqs1oTZuup81+3iLJZVU1i1aL9aPAB5J8eWSDljStVNWbgBNoHzN+BzwWeBNwDbBpkpuq6h3AFrT60NcAX0nyqX7+/T7oSpKmBoNDSYut3pX0UOB2/la4+0LgbbQZ1acCywAf9gVc0qhU1frA14HjgP16CYUPAgcDpwMfo3UtfTnwEWD/JB8b1XglTS9V9VjgHOAM4LAkt/btv6Q9X70syVV921K0GtEMHeeHWUmawuweKmmxVFWbAEcB+wGbJFkJeC+ttuHZwF+Ad9KaoBxcVW8d1VglTXsXAefTlvUNnr2Oo92zNqfNPvwO8Hpgr0FoaJd3SROh11odtixthuHPhsLAs2ih4VZJrqqqZwMkGdQ+HBxXhoaSNLX5QCppcbUx8FvgNNqSGpIMgsRBt+QLgL2BeaMapKTpobr5bJ+V5B5gT2AtYFeAJLf0e9YzaYHiZsDWSQ7v5zmDR9KEGCr3skFVPYFW2mUF4M99+7m0e9OrklxaVU8G9ukfbed7LUnS1GVzFEmLq7WA5ZNcD/e9nN+d5EtVtQHw9qp6ZJJvVNVLklw32uFKmsrGdE5+HjAjyU97UyZo9Qy/CWxZVf/ef0+S3wC/Gb6WM3gkTbSq2o5W7mU74HLgKmCXqlqd1hF5q96waTatruGjgZtHM1pJ0ig541DS4upyYNWq2hKg1wyb1fddSPt6vkrfdx3Md2mOJD0sVXV4VZ3cf16iqp4C/Bg4u6qOrKpnV9XMJDcCJwMvADZ4oGDQGTySJlJVPQJ4BS04/I/+AeMrwFuA5wA7JrmkqlYC3kBbvfHvSS4e1ZglSaNjcChpcXU6MBfYtTceGISHs2kdlv8buG34BF/GJY2nqlqGdp85HiDJvUmupM3OOZT2wv1V4PSqGnR+/3dgr6p6jPckSZOtql4JHAmsDZyXZC5Akg/TakfPBvasqk8DJwGHAJ9I8ul+vh9hJWmasauypMVWVb2c1gHwSuALtAYEG9Jqie2R5IgRDk/SFFZVs5PcNSiT0Gc/vzPJVkPHPJpW03BL4Em0zsrL0ZYB7pzkmyMYuqRpqqqWpj03PQ+4E1gnyS1VtXSSO/sxO9JmHT6P1rTpx0m+2vdZe1WSpiGDQ0mLtarakNaddE1gBnANcGySQ/v+claPpPFUVXvT7jVfTnJnn+n8MWBH4OtJth06dgatpvRuwCbAS/uu1yQ5Y3JHLmm6681QDgFeDZyQZIe+fcnB7MP++6yhGq2GhpI0jRkcSlrs9Ro8qwHLADcm+W3f7kOupHHVZ+ycT7vnvBs4J8kd/T70AWAH4LtJtunHz+xdlamqNYBnAKsnOX4kf4CkaeGBPpxW1eNoy5U3Ao5J8pG+/b77lSRJAwaHkqYkZxpKmig9JDwdWAfYmRYe/qWqHgnsDrydvw8PZye5az7X8eOGpHE3/AxUVRvTyiOsSVt6fEWS66vqicDhwHOBE3uNQ6pqRpJ5oxm5JGlRZHAoSZL0/6iHh2fQGgwsTHjoTB5Jk6qqtgc+CfwJWBW4G/gFsGuSXw3NPFwP+FKS3Uc2WEnSIsuuypIkSf8P+kzBm4CtgStonUhfWVXLJrkZOBg4AXhhVX0NwNBQ0mSqqk1poeE+wCuSPBL4KK3O6od7Q5RraR8+LgN27nWjJUn6OwaHkiRJD6Cqavj3wfLiofDwSuYfHn4B2LKqXjHJQ5akjYFLgNOT/K5v+yfa/erg3thpmSTXATsBr0/yk9EMVZK0KHOpsiRJ0gKMqRW2AfAU4DHAd4FfJLmrqlYEzur7dgbOTvLXqloZeGqSH41m9JKmg7F1nfvHjnOAFZJs1LedS2vOtEWSX1TVRsCzaPUN7xg619qrkqS/44xDSZKkBRgKDbejhYP7AbsBPwUOrarnJrkFeBVt2fInga36zMMbB6FhVfnMJWlCDN2nVu+bZgBXA8tW1WpDoeGWPTRcEXgj8I/92OFrGRpKkv6OD7GSJEkPoKpeTmsg8AlgM+DxwPtoy/s277XCBsuWrwFOAVYbvoYv45ImUlW9ELiyqh7fa6p+nRYWXgg8jVbn8JKqmgVsBWwJ/CTJ7SMbtCRpsWBwKEmSNKSqZvZ/B89JWwHn0Zb0/VeSubT6YdcCZ/ZaYYOGKVsAb0hy1QiGLmn6mgv8BdihqmYl+Satw/tqwPnA7KpaD3g/cDTwmSRfHtloJUmLDWscSpIkAVW1O3BCkv+pqhlJ5vXtPwD+J8mr++/nAuvQaoVd2mckzkvyn2OuZ60wSZOif/D4GrAm8Jwkc/uS5LcAB9FCxSWB3wGnJDm8n+d9SpL0gAwOJUnStFdV6wI/A/5Ee+m+qapmJrmnqs6iNRl4Uf/5WfytwcAKtGXMtwAfSnLniP4ESVPcoMN7xrzA9RmGd1fVOsDPgb2THDy0/0nAo4F7gT8luaZvNzSUJD0olypLkiTB5cD2tGejC6tq5V4nDOB4YMOquo5WK+xlPTScBbwGeBHwfUNDSRMpHUBVPa+qnt+3390PuR74JrBlVa1eVUv0jsu/SfKTJD8bCg3L0FCStDAMDiVJ0rQ2mFmY5Eu0+l93Az+pqkf2Q34OnAg8ArgI+E1VPYfWHOVTwNFJvjqCoUuaBqrq8Ko6uf+8RFU9BfgxcHZVHVlVz+73sRuBk4EXABs8UDA4dtaiJEkL4lJlSZI0bfVZN4MZPC8CNqF1Tl4fuAJ4cZIbqmptYDvg3cDtwCzgj8CpSQ7t57vsT9K4qqplgA8B30ryg6HtLweeTevw/lfgF8CewB+Ao4CnA69M8qdJH7QkaUoxOJQkSdNeVb0F+CxwCnA18BxgS9pL+Aa9YcrywEq0pcnXAn9OckU/39BQ0riqqtlJ7hqqYbgl8M4kWw0d82hgV9r96knA14HlgCcDO/fuypIkPWQGh5IkaVqrqscBPwROozUVuLOqluBv3UjvANZPctMCzi+X/UkaT1W1N3AN8OV+T5oNfAzYEfh6km2Hjp0BzAR2o82afmnf9ZokZ0zuyCVJU83MUQ9AkiRpxJahdRz9eX9BH3RT/gKwCvAJ4DtV9dIkN1bVjCTzBicbGkoaT1W1NPA6YDXgjqo6J8kdVfVRYA6wQ1WdlmSbwSlJ5gIHVdWpwDOA1Q0NJUnjwRmHkiRpWquqVYHfAx9J8rG+bdagU2lVXQE8BbiV9iI/17BQ0kSqqpWA04F1gJ2Bc5L8pTdt2h14O/DdQXg4WNY8n+tYRkGS9LDYVVmSJE1bfUnyrcCZwNt7wwF6PbElquoJtBk++wH/O8kcQ0NJE62XRngtrUnTUcArq2rZJDcDBwMnAC+pqtP68XdV1f1WkxkaSpIeLoNDSZI0bSW5N8kc4FhaCZe9q+p1ffeKwEbAXOCLSb4I94WNkjRh+kzBm4CteeDw8IVV9TWAJPeMbMCSpCnLpcqSJElAVb0G2AN4Gu1FPbRlgvslOXCUY5M0tT1Qk6W+bPn/o5VMGLtseS/gvcBWSb4xaQOWJE0bBoeSJGlaG35hr6pnAxvQZvlcDfwwySljj5Ok8TLmHrQBLSB8DPBd4Bd9GfKKwFn8LTw8O8lfq2pl4KlJfjSa0UuSpjqDQ0mSNCWNDfoeqEnAgx1rgwFJE62qtgM+DtwBLAU8CvgMcHKSC/vMwzOAtYD3A2cl+cvQ+d6nJEnjzho9kiRpShqawfPaqlrjgV6ox84kHHusL+OSJlJvzHQk8AlgM+DxwPuAnYDNq2rpoZqH1wCn0Lq838f7lCRpIhgcSpKkKauqtgK+DGxdVTNGPR5JAhh0QB5qtrQVcB5wYpL/SjIX2Bi4FjgzyZ1DDVO2AN6Q5KoRDF2SNM0YHEqSpCmp1/7aAtifttRv3kKeV0M/rzgxo5M0HVXV7lX1qCT3VNWMoVmC6wL3JLmhH3cu8Hxa05NL+4zETQCS3Jjkq/043+ckSRPK/9FIkqQpp6peBxwNPB34eZLbFvK84SYFOwP7VdXyEzdSSdNFVa0L7AucX1UrJZk3mHkI3AKs0o87C3gGsGUPDVcA3ghsWVVLD1/T5cmSpIlmcChJkqaiFYDXAhsCj16YE8aEhu8BjgAuXdjQUZIexOXA9rR3sAurauUk9/R9xwMbVtV1wNOAlyX5RVXNAl4DvAj4fpI7RzFwSdL0ZXAoSZKmhOoAkhwPbAPMA95aVes82LljZhoeAezQryNJD0tVzUxyT5Iv0Toi3w38pKoe2Q/5OXAi8AjgIuA3VfUcWnOUTwFHD5YnS5I0mWpME0FJkqTFxnDgt4D9bwM+B3wF+Lck//VA1+ih4eHAjoaGksbDmHvMi2i1CjcD1geuAF6c5IaqWhvYDng3cDswC/gjcGqSQ/v5S7g8WZI0mQwOJUnSYmnMy/hmwCuBNYBfA8cA1ya5u6q2B46jhYf7JrlyAdfbCTgSQ0NJE6Cq3gJ8FjgFuBp4DrAl8AdggyT/02uqrkRbmnwt8OckV/TzDQ0lSZPO4FCSJC3Wqmo74NPAj4CZtPpgdwCHAccnmdNnHh4LnA3sk+RXY67xLuAzwDuSnDiJw5c0DVTV44AfAqcBeye5s3dEfgtwEO2etX6SmxZw/gPOrpYkaaJY41CSJC22qmp94EBgP+ANSTYB1gKeQGuM8giAJCfTaoW9GnjsfC71Y2B7Q0NJE2QZWqOmn/fQcGafPfgF4FBgTeA7VbUyQFXNGD7Z0FCSNCoGh5IkabHSZ+kMPBu4Bfja0Eydk4HfA4ckubGqlgZI8jng6Um+OeZ6M5Jc2sNFSZoINwMzaB81SHJPVc1KcnevX3glsC7w66paCnBJsiRpkWBwKEmSFnlVdUhVHQCQ5N6qmtV3rQXMSnJVP+4c4LnAFkkurqpNgT37izjDtcIG104ybxL/FEnTTL/f3AqcCby9ql4O0GuwLlFVTwDm0GZO/+8kc5xhKElaVBgcSpKkRVpfuvdYYI+q2h3aC3ff/Utgzapat6q+Qpuxs2WSS/tMw41osxJXHr6mDQYkTZYk9yaZQ6uzOhPYu6pe13evSLtPzQW+mOSLcL+Z1ZIkjczMUQ9AkiTpgfTlxnsBtwEH9dpgB/Td5wEXARfQlgKul+T6PsNwG+DdwIeS/GEEQ5ek+yT5z6r6ILAHcHJVfQgIsA6w33DHdz9uSJIWFXZVliRJi6xef3Be//mZtCDwX4BdkxzZt28H7EKrHbY9sDTwdGA34KNJDurH2ZVU0kgM33+q6tnABsDWwNXAD5OcMvY4SZIWBQaHkiRpkVdVrwdeQVuKvF7fvNdQKPjPwJuATYB5wMXAGUmO6fuXcAaPpPE2Nuh7oHvNgx3rfUqStCgyOJQkSYu0Hhp+Afg3WiC4BPA+4MXAPkn2Hzp2LeB24K5Bl2VfxiVNtKp6LfDTJL8f9VgkSRpPBoeSJGmR1RucnEvrSPrGJHf07WvT6oS9GfhAksP69iV61+VKEpf9SZpoVbUVrWPye4HP2KldkjSV2K1LkiQtMqqqhn5eErgH+EfguiR3VNVMgCRXAEfQZhce0psM3NdQYBAWGhpKmki96/sWwP7AyQsbGo651604MaOTJOnhMziUJEmLjKHmAa8DtktyN3AJ8OKqWi7JPVU1qx/7c+BrwB+AA6tq/eGXcUmaSP0+dTStGdPPk9y2kOcNN0rZGdivqpafuJFKkvTQGRxKkqSRGzP75uXAacCM/jJ9LrAysG9VLdvDRKpqBWAV4BjgBUnOd4ahpEm0AvBaYEPg0QtzwpjQ8D20mdOXLmzoKEnSZDM4lCRJIzf0Ir0GsDpwKHBSf5k+Hvg+sC1wdFWt2pugbE3rsPy9JD/t5/tsI2nCVAeQ5HhgG1on97dW1ToPdu6YmYZHADv060iStEjy4VqSJC0SqupltIBwP+CWXtNwVp9huB1wOrAJcD1wPvAZ4JgkPxxcw+7Jksbb8IzodEO/fwXYAXgBsFdVPXVB1xgTGh4O7JjkhIkcuyRJD9fMUQ9AkiSpmwXMoc04XBYgyd1VNTvJ3KraDfgssDFwN3Btkm/B37opj2jckqaoMYHfZsArgTWAX9PKJFyb5OQ+2/m4fty+Sa4cvs7QNXbib6GhMw0lSYu8shSQJEmabMMv42O2b0x7GV8TeGeSz/ftg5mH87uWoaGkCVVV2wGfBn5Em3zxNOAO4DDg+CRzquptwLHA2cA+SX415hrvos2UfkeSEydx+JIkPWQGh5IkaVKNmcGzEbAu7SX828B5tNk8nweWor18f6kfa0AoadJV1frAmbSahMcluamqlgNuBb4I7JLkxn7sO2gzDzdP8s0x11kXWC/JyZM5fkmSHg6DQ0mSNBJVtT2tCcrtwHLAisCFwNtoM3pOBZYBPpzkyyMapqRpaPhDRZ8puAvwqiRX9W1fA57bt11cVUsnubPvWzvJFWOuNyPJvMn9KyRJevhsjiJJkiZdVW0CHEVrhLJJkpWA99JqG54N/AV4J21Gz8FV9dZRjVXS9FBVh1TVAdAaLVXVrL5rLWDWUGh4Di003KKHhpsCe1bVUv3cK/px971rGRpKkhZXBoeSJGkUNgZ+C5wG/A4gySBIXBI4MMkFwN6AL9ySJlRVrQw8FtijqnaH1pyp7/4lsGZVrVtVX6GVV9gyyaVVtTSwEfBsYOXha1paQZI0FdhVWZIkjcJawPJJroe/NT9J8qWq2gB4e1U9Msk3quolSa4b7XAlTWVJbqyqvYDbgIOqamaSA/ru84CLgAuAm2l1Cq/vMwy3Ad4NfCjJH0YwdEmSJpQzDiVJ0ihcDqxaVVtCm9kztCzwQlpjlFX6vuugNVUZxUAlTW1VNQMgya+BTwGfBfavql369qtpHZV/BcwG1q+qNwB79e1HJDmpX8v7lCRpSnHGoSRJGoXTgd2BXavqz0nO7+HhbFqH5f+mzfy5z6ATsySNp0H9wap6PfAK2lJkgMOr6hFJDkpyUlXdArwJOIFWQuFi4P1Jjunn2/ldkjTl2FVZkiSNRFW9HDgDuBL4Am0p4IbAnsAeSY4Y4fAkTSM9NPwC8G+0QHAJ4H3Ai4F9kuw/dOxatG7wdyW5qW8zNJQkTUkGh5IkaWSqakPgOGBNYAZwDXBskkP7/nKmoaSJ1BucnEvr4v7GJHf07WsDewBvBj6Q5LC+fYnedbmSxPuUJGkqMziUJEkjVVUrAasBywA3Jvlt3+4MHknjbjjoq6olgXtpXd7PTLJzb4xyT9//HOA7wHLAnkk+NqpxS5I0CgaHkiRpkeMMHkkTrapeB6yU5NiqOgt4PLBRktsHnd77cScCmwKrA88HLvD+JEmaLuyqLEmSFjm+lEsab8Mdj3uN1dOAGVW1PG2p8srAvlW17FBouAKtw/sxwAt6IyfvT5KkacPgUJIkSdKUN7Q8eQ3a7MFDgZOS3AYcD3wf2BY4uqpW7U1QtgbWA76X5Kf9fN+hJEnTxsxRD0CSJEmSJkNVvQw4FpgNHJ3kjsGy5KraDvgE8BrgelqzlCWBA5P8cHANa69KkqYTg0NJkiRJ08UsYA5txuGyAD00nJ1kblXtBnwW2Bi4G7g2ybfAhk2SpOnJ5iiSJEmSppwFNVmqqo1pNQvXBN6Z5PN9+30NUeZzjqGhJGlaMjiUJEmSNKUMh4ZVtRGwLvA04NvAecAawOeBpYB9knypH2tAKEnSEINDSZIkSVNSVW1Pa4JyO7AcsCJwIfA2WtmmU4FlgA8n+fKIhilJ0iLLjmCSJEmSppyq2gQ4CtgP2CTJSsB7abUNzwb+AryT1gTl4Kp666jGKknSosrgUJIkSdJUtDHwW+A04HcASQZB4qBb8gXA3sC8UQ1SkqRFmV2VJUmSJE1FawHLJ7ke/tb8JMmXqmoD4O1V9cgk36iqlyS5brTDlSRp0eOMQ0mSJElT0eXAqlW1JUCSu6tqVt93Ia0xyip933XQmqqMYqCSJC2qDA4lSZIkTUWnA3OBXatqfbgvPJxN67D838BtwyfEzpGSJP0duypLkiRJmpKq6uXAGcCVwBeAi4ANgT2BPZIcMcLhSZK0yDM4lCRJkjRlVdWGwHHAmsAM4Brg2CSH9v3lTENJkubP4FCSJEnSlFZVKwGrAcsANyb5bd++RJJ7Rzo4SZIWYQaHkiRJkqYdZxpKkvTgDA4lSZIkSZIk3Y9dlSVJkiRJkiTdj8GhJEmSJEmSpPsxOJQkSZIkSZJ0PwaHkiRJkiRJku7H4FCSJEmSJEnS/RgcSpIkSZIkSbofg0NJkiRJkiRJ92NwKEmSJEmSJOl+/i/hujxuQdvMIgAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 1440x864 with 2 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Let's visualize our errors to understand what might be causing them\n",
    "#Install missingno\n",
    "import missingno as msno\n",
    "msno.heatmap(orders_df);\n",
    "#It looks like order_delivered_carrier_date and order_delivered_customer_date are connected. When one is in error..\n",
    "#so is the other (most of the time)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "canceled     0.88125\n",
       "delivered    0.08750\n",
       "created      0.03125\n",
       "Name: order_status, dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Dealing with order_approved_at null values first\n",
    "#We see that there is a strong correlation between order_approved_at being null and \"order_status\" being 'canceled'\n",
    "#This makes sense, if the order was canceled, it never got the chance to be approved.\n",
    "order_approved_at_null_df = orders_df[orders_df[\"order_approved_at\"].isnull() == True]\n",
    "order_approved_at_null_df[\"order_status\"].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "delivered      0.970203\n",
       "shipped        0.011132\n",
       "canceled       0.006285\n",
       "unavailable    0.006124\n",
       "invoiced       0.003158\n",
       "processing     0.003027\n",
       "created        0.000050\n",
       "approved       0.000020\n",
       "Name: order_status, dtype: float64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Compared to the order_approved_at_null_df, our full df has a much lower % of 'canceled' status\n",
    "orders_df[\"order_status\"].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1130     canceled\n",
       "1801     canceled\n",
       "1868     canceled\n",
       "2029     canceled\n",
       "2161     canceled\n",
       "           ...   \n",
       "97696    canceled\n",
       "98415    canceled\n",
       "98909    canceled\n",
       "99283    canceled\n",
       "99347    canceled\n",
       "Name: order_status, Length: 160, dtype: object"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_approved_at_null_df[\"order_status\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "null order_approved_at & delivered: 14\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>order_status</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>order_approved_at</th>\n",
       "      <th>order_delivered_carrier_date</th>\n",
       "      <th>order_delivered_customer_date</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5323</th>\n",
       "      <td>e04abd8149ef81b95221e88f6ed9ab6a</td>\n",
       "      <td>2127dc6603ac33544953ef05ec155771</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-02-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-02-23 12:04:47</td>\n",
       "      <td>2017-03-01 13:25:33</td>\n",
       "      <td>2017-03-17 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16567</th>\n",
       "      <td>8a9adc69528e1001fc68dd0aaebbb54a</td>\n",
       "      <td>4c1ccc74e00993733742a3c786dc3c1f</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-02-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-02-23 09:01:52</td>\n",
       "      <td>2017-03-02 10:05:06</td>\n",
       "      <td>2017-03-21 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19031</th>\n",
       "      <td>7013bcfc1c97fe719a7b5e05e61c12db</td>\n",
       "      <td>2941af76d38100e0f8740a374f1a5dc3</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-02-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-02-22 16:25:25</td>\n",
       "      <td>2017-03-01 08:07:38</td>\n",
       "      <td>2017-03-17 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22663</th>\n",
       "      <td>5cf925b116421afa85ee25e99b4c34fb</td>\n",
       "      <td>29c35fc91fc13fb5073c8f30505d860d</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-02-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-02-22 11:23:10</td>\n",
       "      <td>2017-03-09 07:28:47</td>\n",
       "      <td>2017-03-31 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23156</th>\n",
       "      <td>12a95a3c06dbaec84bcfb0e2da5d228a</td>\n",
       "      <td>1e101e0daffaddce8159d25a8e53f2b2</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-02-17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-02-22 11:23:11</td>\n",
       "      <td>2017-03-02 11:09:19</td>\n",
       "      <td>2017-03-20 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               order_id                       customer_id  \\\n",
       "5323   e04abd8149ef81b95221e88f6ed9ab6a  2127dc6603ac33544953ef05ec155771   \n",
       "16567  8a9adc69528e1001fc68dd0aaebbb54a  4c1ccc74e00993733742a3c786dc3c1f   \n",
       "19031  7013bcfc1c97fe719a7b5e05e61c12db  2941af76d38100e0f8740a374f1a5dc3   \n",
       "22663  5cf925b116421afa85ee25e99b4c34fb  29c35fc91fc13fb5073c8f30505d860d   \n",
       "23156  12a95a3c06dbaec84bcfb0e2da5d228a  1e101e0daffaddce8159d25a8e53f2b2   \n",
       "\n",
       "      order_status order_purchase_timestamp order_approved_at  \\\n",
       "5323     delivered               2017-02-18               NaN   \n",
       "16567    delivered               2017-02-18               NaN   \n",
       "19031    delivered               2017-02-18               NaN   \n",
       "22663    delivered               2017-02-18               NaN   \n",
       "23156    delivered               2017-02-17               NaN   \n",
       "\n",
       "      order_delivered_carrier_date order_delivered_customer_date  \\\n",
       "5323           2017-02-23 12:04:47           2017-03-01 13:25:33   \n",
       "16567          2017-02-23 09:01:52           2017-03-02 10:05:06   \n",
       "19031          2017-02-22 16:25:25           2017-03-01 08:07:38   \n",
       "22663          2017-02-22 11:23:10           2017-03-09 07:28:47   \n",
       "23156          2017-02-22 11:23:11           2017-03-02 11:09:19   \n",
       "\n",
       "      order_estimated_delivery_date  \n",
       "5323            2017-03-17 00:00:00  \n",
       "16567           2017-03-21 00:00:00  \n",
       "19031           2017-03-17 00:00:00  \n",
       "22663           2017-03-31 00:00:00  \n",
       "23156           2017-03-20 00:00:00  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#When order status is 'delivered', we'd expect a an \"order_approved_at\" date but it seems like there..\n",
    "#were rare instances of data loss. This isn't a big concern bc we don't need 'order_approved_at'\n",
    "print(f'null order_approved_at & delivered: {len(order_approved_at_null_df[order_approved_at_null_df[\"order_status\"]==\"delivered\"])}')\n",
    "order_approved_at_null_df[order_approved_at_null_df[\"order_status\"]==\"delivered\"].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "null order_approved_at & created: 5\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>order_status</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>order_approved_at</th>\n",
       "      <th>order_delivered_carrier_date</th>\n",
       "      <th>order_delivered_customer_date</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7434</th>\n",
       "      <td>b5359909123fa03c50bdb0cfed07f098</td>\n",
       "      <td>438449d4af8980d107bf04571413a8e7</td>\n",
       "      <td>created</td>\n",
       "      <td>2017-12-05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-01-11 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9238</th>\n",
       "      <td>dba5062fbda3af4fb6c33b1e040ca38f</td>\n",
       "      <td>964a6df3d9bdf60fe3e7b8bb69ed893a</td>\n",
       "      <td>created</td>\n",
       "      <td>2018-02-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-03-07 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21441</th>\n",
       "      <td>7a4df5d8cff4090e541401a20a22bb80</td>\n",
       "      <td>725e9c75605414b21fd8c8d5a1c2f1d6</td>\n",
       "      <td>created</td>\n",
       "      <td>2017-11-25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-12-12 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55086</th>\n",
       "      <td>35de4050331c6c644cddc86f4f2d0d64</td>\n",
       "      <td>4ee64f4bfc542546f422da0aeb462853</td>\n",
       "      <td>created</td>\n",
       "      <td>2017-12-05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-01-08 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58958</th>\n",
       "      <td>90ab3e7d52544ec7bc3363c82689965f</td>\n",
       "      <td>7d61b9f4f216052ba664f22e9c504ef1</td>\n",
       "      <td>created</td>\n",
       "      <td>2017-11-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-12-01 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               order_id                       customer_id  \\\n",
       "7434   b5359909123fa03c50bdb0cfed07f098  438449d4af8980d107bf04571413a8e7   \n",
       "9238   dba5062fbda3af4fb6c33b1e040ca38f  964a6df3d9bdf60fe3e7b8bb69ed893a   \n",
       "21441  7a4df5d8cff4090e541401a20a22bb80  725e9c75605414b21fd8c8d5a1c2f1d6   \n",
       "55086  35de4050331c6c644cddc86f4f2d0d64  4ee64f4bfc542546f422da0aeb462853   \n",
       "58958  90ab3e7d52544ec7bc3363c82689965f  7d61b9f4f216052ba664f22e9c504ef1   \n",
       "\n",
       "      order_status order_purchase_timestamp order_approved_at  \\\n",
       "7434       created               2017-12-05               NaN   \n",
       "9238       created               2018-02-09               NaN   \n",
       "21441      created               2017-11-25               NaN   \n",
       "55086      created               2017-12-05               NaN   \n",
       "58958      created               2017-11-06               NaN   \n",
       "\n",
       "      order_delivered_carrier_date order_delivered_customer_date  \\\n",
       "7434                           NaN                           NaN   \n",
       "9238                           NaN                           NaN   \n",
       "21441                          NaN                           NaN   \n",
       "55086                          NaN                           NaN   \n",
       "58958                          NaN                           NaN   \n",
       "\n",
       "      order_estimated_delivery_date  \n",
       "7434            2018-01-11 00:00:00  \n",
       "9238            2018-03-07 00:00:00  \n",
       "21441           2017-12-12 00:00:00  \n",
       "55086           2018-01-08 00:00:00  \n",
       "58958           2017-12-01 00:00:00  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#were rare instances of data loss for the 'created status' as well\n",
    "print(f'null order_approved_at & created: {len(order_approved_at_null_df[order_approved_at_null_df[\"order_status\"]==\"created\"])}')\n",
    "order_approved_at_null_df[order_approved_at_null_df[\"order_status\"]==\"created\"].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "shipped        0.373356\n",
       "canceled       0.208769\n",
       "unavailable    0.205396\n",
       "invoiced       0.105902\n",
       "processing     0.101518\n",
       "delivered      0.002698\n",
       "created        0.001686\n",
       "approved       0.000675\n",
       "Name: order_status, dtype: float64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Now looking at our null values with order delivered dates\n",
    "null_order_delivered_customer_date_df = orders_df[orders_df[\"order_delivered_customer_date\"].isnull() == True]\n",
    "null_order_delivered_customer_date_df[\"order_status\"].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "unavailable    0.341559\n",
       "canceled       0.308469\n",
       "invoiced       0.176108\n",
       "processing     0.168817\n",
       "created        0.002804\n",
       "approved       0.001122\n",
       "delivered      0.001122\n",
       "Name: order_status, dtype: float64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_order_delivered_carrier_date_df = orders_df[orders_df[\"order_delivered_carrier_date\"].isnull() == True]\n",
    "null_order_delivered_carrier_date_df[\"order_status\"].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Again we see the order status's 'canceled' and 'unavailable' causing issues, we'll drop these from our joined dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### olist_order_payments_dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(103886, 5)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "payments_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 103886 entries, 0 to 103885\n",
      "Data columns (total 5 columns):\n",
      " #   Column                Non-Null Count   Dtype  \n",
      "---  ------                --------------   -----  \n",
      " 0   order_id              103886 non-null  object \n",
      " 1   payment_sequential    103886 non-null  int64  \n",
      " 2   payment_type          103886 non-null  object \n",
      " 3   payment_installments  103886 non-null  int64  \n",
      " 4   payment_value         103886 non-null  float64\n",
      "dtypes: float64(1), int64(2), object(2)\n",
      "memory usage: 4.0+ MB\n"
     ]
    }
   ],
   "source": [
    "#We see the data we need, 'payment_value' and 'order_id' in appropriate d types\n",
    "payments_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "order_id                0\n",
       "payment_sequential      0\n",
       "payment_type            0\n",
       "payment_installments    0\n",
       "payment_value           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#No null values\n",
    "payments_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>payment_sequential</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>payment_installments</th>\n",
       "      <th>payment_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b81ef226f3fe1789b1e8b2acac839d17</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>8</td>\n",
       "      <td>99.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a9810da82917af2d9aefd1278f1dcfa0</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>24.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25e8ea4e93396b6fa0d3dd708e76c1bd</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>65.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ba78997921bbcdc1373bb41e913ab953</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>8</td>\n",
       "      <td>107.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>42fdf880ba16b47b59251dd489d4441a</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>2</td>\n",
       "      <td>128.45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           order_id  payment_sequential payment_type  \\\n",
       "0  b81ef226f3fe1789b1e8b2acac839d17                   1  credit_card   \n",
       "1  a9810da82917af2d9aefd1278f1dcfa0                   1  credit_card   \n",
       "2  25e8ea4e93396b6fa0d3dd708e76c1bd                   1  credit_card   \n",
       "3  ba78997921bbcdc1373bb41e913ab953                   1  credit_card   \n",
       "4  42fdf880ba16b47b59251dd489d4441a                   1  credit_card   \n",
       "\n",
       "   payment_installments  payment_value  \n",
       "0                     8          99.33  \n",
       "1                     1          24.39  \n",
       "2                     1          65.71  \n",
       "3                     8         107.78  \n",
       "4                     2         128.45  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#data set indexed by order_id\n",
    "payments_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duplicate order_ids: 4446\n",
      "unique order_ids: 99440\n"
     ]
    }
   ],
   "source": [
    "print(f'duplicate order_ids: {len(payments_df[\"order_id\"])-len(payments_df[\"order_id\"].unique())}')\n",
    "print(f'unique order_ids: {len(payments_df[\"order_id\"].unique())}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "fa65dad1b0e818e3ccc5cb0e39231352    29\n",
       "ccf804e764ed5650cd8759557269dc13    26\n",
       "285c2e15bebd4ac83635ccc563dc71f4    22\n",
       "895ab968e7bb0d5659d16cd74cd1650c    21\n",
       "fedcd9f7ccdc8cba3a18defedd1a5547    19\n",
       "                                    ..\n",
       "6d2a30c9b7dcee3ed507dc9a601f99e7     1\n",
       "a7737f6d9208dd56ea498a322ed3c37f     1\n",
       "646e62df54f3e236eb6d5ff3b31429b8     1\n",
       "e115da7a49ec2acf622e1f31da65cfb9     1\n",
       "28bbae6599b09d39ca406b747b6632b1     1\n",
       "Name: order_id, Length: 99440, dtype: int64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#order_id's repeat in our data set\n",
    "payments_df['order_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>payment_sequential</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>payment_installments</th>\n",
       "      <th>payment_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14321</th>\n",
       "      <td>fa65dad1b0e818e3ccc5cb0e39231352</td>\n",
       "      <td>1</td>\n",
       "      <td>voucher</td>\n",
       "      <td>1</td>\n",
       "      <td>3.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23074</th>\n",
       "      <td>fa65dad1b0e818e3ccc5cb0e39231352</td>\n",
       "      <td>2</td>\n",
       "      <td>voucher</td>\n",
       "      <td>1</td>\n",
       "      <td>8.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65641</th>\n",
       "      <td>fa65dad1b0e818e3ccc5cb0e39231352</td>\n",
       "      <td>3</td>\n",
       "      <td>voucher</td>\n",
       "      <td>1</td>\n",
       "      <td>2.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9985</th>\n",
       "      <td>fa65dad1b0e818e3ccc5cb0e39231352</td>\n",
       "      <td>4</td>\n",
       "      <td>voucher</td>\n",
       "      <td>1</td>\n",
       "      <td>29.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28330</th>\n",
       "      <td>fa65dad1b0e818e3ccc5cb0e39231352</td>\n",
       "      <td>5</td>\n",
       "      <td>voucher</td>\n",
       "      <td>1</td>\n",
       "      <td>0.66</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               order_id  payment_sequential payment_type  \\\n",
       "14321  fa65dad1b0e818e3ccc5cb0e39231352                   1      voucher   \n",
       "23074  fa65dad1b0e818e3ccc5cb0e39231352                   2      voucher   \n",
       "65641  fa65dad1b0e818e3ccc5cb0e39231352                   3      voucher   \n",
       "9985   fa65dad1b0e818e3ccc5cb0e39231352                   4      voucher   \n",
       "28330  fa65dad1b0e818e3ccc5cb0e39231352                   5      voucher   \n",
       "\n",
       "       payment_installments  payment_value  \n",
       "14321                     1           3.71  \n",
       "23074                     1           8.51  \n",
       "65641                     1           2.95  \n",
       "9985                      1          29.16  \n",
       "28330                     1           0.66  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Exploring an order_id with many instances\n",
    "#Order_ids represent unique orders and the duplicates in this data-set represent intalment type payments\n",
    "payments_df[payments_df['order_id'] == \"fa65dad1b0e818e3ccc5cb0e39231352\"].sort_values(by=[\"payment_sequential\"],ascending=True).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>payment_sequential</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>payment_installments</th>\n",
       "      <th>payment_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>63398</th>\n",
       "      <td>285c2e15bebd4ac83635ccc563dc71f4</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>1.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51943</th>\n",
       "      <td>285c2e15bebd4ac83635ccc563dc71f4</td>\n",
       "      <td>2</td>\n",
       "      <td>voucher</td>\n",
       "      <td>1</td>\n",
       "      <td>1.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75172</th>\n",
       "      <td>285c2e15bebd4ac83635ccc563dc71f4</td>\n",
       "      <td>3</td>\n",
       "      <td>voucher</td>\n",
       "      <td>1</td>\n",
       "      <td>1.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81583</th>\n",
       "      <td>285c2e15bebd4ac83635ccc563dc71f4</td>\n",
       "      <td>4</td>\n",
       "      <td>voucher</td>\n",
       "      <td>1</td>\n",
       "      <td>2.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76134</th>\n",
       "      <td>285c2e15bebd4ac83635ccc563dc71f4</td>\n",
       "      <td>5</td>\n",
       "      <td>voucher</td>\n",
       "      <td>1</td>\n",
       "      <td>1.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               order_id  payment_sequential payment_type  \\\n",
       "63398  285c2e15bebd4ac83635ccc563dc71f4                   1  credit_card   \n",
       "51943  285c2e15bebd4ac83635ccc563dc71f4                   2      voucher   \n",
       "75172  285c2e15bebd4ac83635ccc563dc71f4                   3      voucher   \n",
       "81583  285c2e15bebd4ac83635ccc563dc71f4                   4      voucher   \n",
       "76134  285c2e15bebd4ac83635ccc563dc71f4                   5      voucher   \n",
       "\n",
       "       payment_installments  payment_value  \n",
       "63398                     1           1.62  \n",
       "51943                     1           1.24  \n",
       "75172                     1           1.40  \n",
       "81583                     1           2.89  \n",
       "76134                     1           1.75  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "payments_df[payments_df['order_id'] == \"285c2e15bebd4ac83635ccc563dc71f4\"].sort_values(by=[\"payment_sequential\"],ascending=True).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>payment_sequential</th>\n",
       "      <th>payment_installments</th>\n",
       "      <th>payment_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>103886.000000</td>\n",
       "      <td>103886.000000</td>\n",
       "      <td>103886.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.092679</td>\n",
       "      <td>2.853349</td>\n",
       "      <td>154.100380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.706584</td>\n",
       "      <td>2.687051</td>\n",
       "      <td>217.494064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>56.790000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>171.837500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>29.000000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>13664.080000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       payment_sequential  payment_installments  payment_value\n",
       "count       103886.000000         103886.000000  103886.000000\n",
       "mean             1.092679              2.853349     154.100380\n",
       "std              0.706584              2.687051     217.494064\n",
       "min              1.000000              0.000000       0.000000\n",
       "25%              1.000000              1.000000      56.790000\n",
       "50%              1.000000              1.000000     100.000000\n",
       "75%              1.000000              4.000000     171.837500\n",
       "max             29.000000             24.000000   13664.080000"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#We have some major right outliers in `payments_value`\n",
    "payments_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAZEAAAD4CAYAAAAtrdtxAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8qNh9FAAAACXBIWXMAAAsTAAALEwEAmpwYAAATWklEQVR4nO3df6zd9X3f8edrdiEkXbAdLEZtNDur1cmJ1IVYxFGmKgodGBLVTGojUDXclMXSIF3aVepMI40taaTQVkuClpKi4MZENITRrFiE1PJIqm1/4HAJCWCI6xsDwRaEG0xga9Qmbt/743wMh8u9NnzOub7nludDOrrf7/v7+Z7v+3zuj5fP9/u916kqJEnq8Y8WuwFJ0tJliEiSuhkikqRuhogkqZshIknqtnyxGxi3s846q9atW7fYbUjSknLffff9oKpWv9r9/sGFyLp165iamlrsNiRpSUnyeM9+ns6SJHUzRCRJ3QwRSVI3Q0SS1M0QkSR1M0QkSd0MEUlSN0NEktTNEJEkdfsH9xvro1i34yuLctzHPvHeRTmuJI3KdyKSpG6GiCSp20lDJMnOJE8neWiotirJ3iQH28eVrZ4k1yeZTvJAkvOG9tnWxh9Msm2o/vYkD7Z9rk+SEx1DkjQ5Xsk7kc8DW2bVdgB3V9UG4O62DnAxsKE9tgM3wCAQgGuBdwDnA9cOhcINwAeH9ttykmNIkibESUOkqv4XcHRWeSuwqy3vAi4dqt9cA/cAK5KcA1wE7K2qo1X1LLAX2NK2vbGq7qmqAm6e9VxzHUOSNCF6r4mcXVVPtuWngLPb8hrgiaFxh1vtRPXDc9RPdIyXSbI9yVSSqZmZmY6XI0nqMfKF9fYOosbQS/cxqurGqtpUVZtWr37V/zGXJKlTb4h8v52Kon18utWPAOcOjVvbaieqr52jfqJjSJImRG+I7AaO32G1DbhjqH5Fu0trM/BcOyW1B7gwycp2Qf1CYE/b9nySze2urCtmPddcx5AkTYiT/sZ6ki8C7wbOSnKYwV1WnwBuS3Il8Djw/jb8LuASYBr4EfABgKo6muRjwL1t3Eer6vjF+qsY3AF2BvDV9uAEx5AkTYiThkhVXT7PpgvmGFvA1fM8z05g5xz1KeCtc9SfmesYkqTJ4W+sS5K6GSKSpG6GiCSpmyEiSepmiEiSuhkikqRuhogkqZshIknqZohIkroZIpKkboaIJKmbISJJ6maISJK6GSKSpG6GiCSpmyEiSepmiEiSuhkikqRuhogkqZshIknqZohIkroZIpKkboaIJKmbISJJ6maISJK6GSKSpG6GiCSpmyEiSepmiEiSuhkikqRuhogkqdtIIZLkt5LsT/JQki8meV2S9Un2JZlO8qUkp7Wxp7f16bZ93dDzXNPqB5JcNFTf0mrTSXaM0qskafy6QyTJGuDfA5uq6q3AMuAy4Drgk1X1s8CzwJVtlyuBZ1v9k20cSTa2/d4CbAH+KMmyJMuAzwAXAxuBy9tYSdKEGPV01nLgjCTLgdcDTwLvAW5v23cBl7blrW2dtv2CJGn1W6vqb6vqUWAaOL89pqvqUFX9GLi1jZUkTYjuEKmqI8AfAt9jEB7PAfcBP6yqY23YYWBNW14DPNH2PdbGv2m4Pmuf+eovk2R7kqkkUzMzM70vSZL0Ko1yOmslg3cG64GfAd7A4HTUKVdVN1bVpqratHr16sVoQZJek0Y5nfWLwKNVNVNVPwG+DLwLWNFObwGsBY605SPAuQBt+5nAM8P1WfvMV5ckTYhRQuR7wOYkr2/XNi4AHga+DvxyG7MNuKMt727rtO1fq6pq9cva3VvrgQ3AN4B7gQ3tbq/TGFx83z1Cv5KkMVt+8iFzq6p9SW4HvgkcA+4HbgS+Atya5Pda7aa2y03AF5JMA0cZhAJVtT/JbQwC6BhwdVX9HUCSDwF7GNz5tbOq9vf2K0kav+4QAaiqa4FrZ5UPMbizavbYvwF+ZZ7n+Tjw8TnqdwF3jdKjJGnh+BvrkqRuhogkqZshIknqZohIkroZIpKkboaIJKmbISJJ6maISJK6GSKSpG6GiCSpmyEiSepmiEiSuhkikqRuhogkqZshIknqZohIkroZIpKkboaIJKmbISJJ6maISJK6GSKSpG6GiCSpmyEiSepmiEiSuhkikqRuhogkqZshIknqZohIkroZIpKkboaIJKmbISJJ6jZSiCRZkeT2JN9J8kiSdyZZlWRvkoPt48o2NkmuTzKd5IEk5w09z7Y2/mCSbUP1tyd5sO1zfZKM0q8kabxGfSfyaeAvquqfAz8PPALsAO6uqg3A3W0d4GJgQ3tsB24ASLIKuBZ4B3A+cO3x4GljPji035YR+5UkjVF3iCQ5E/gF4CaAqvpxVf0Q2ArsasN2AZe25a3AzTVwD7AiyTnARcDeqjpaVc8Ce4Etbdsbq+qeqirg5qHnkiRNgFHeiawHZoA/SXJ/ks8leQNwdlU92cY8BZzdltcATwztf7jVTlQ/PEddkjQhRgmR5cB5wA1V9Tbgr3nx1BUA7R1EjXCMVyTJ9iRTSaZmZmYW+nCSpGaUEDkMHK6qfW39dgah8v12Kor28em2/Qhw7tD+a1vtRPW1c9RfpqpurKpNVbVp9erVI7wkSdKr0R0iVfUU8ESSn2ulC4CHgd3A8TustgF3tOXdwBXtLq3NwHPttNce4MIkK9sF9QuBPW3b80k2t7uyrhh6LknSBFg+4v6/AdyS5DTgEPABBsF0W5IrgceB97exdwGXANPAj9pYqupoko8B97ZxH62qo235KuDzwBnAV9tDkjQhRgqRqvoWsGmOTRfMMbaAq+d5np3AzjnqU8BbR+lRkrRw/I11SVI3Q0SS1M0QkSR1M0QkSd0MEUlSN0NEktTNEJEkdTNEJEndDBFJUjdDRJLUzRCRJHUzRCRJ3QwRSVI3Q0SS1M0QkSR1M0QkSd0MEUlSN0NEktTNEJEkdTNEJEndDBFJUjdDRJLUzRCRJHUzRCRJ3QwRSVI3Q0SS1M0QkSR1M0QkSd0MEUlSN0NEktTNEJEkdRs5RJIsS3J/kjvb+vok+5JMJ/lSktNa/fS2Pt22rxt6jmta/UCSi4bqW1ptOsmOUXuVJI3XON6JfBh4ZGj9OuCTVfWzwLPAla1+JfBsq3+yjSPJRuAy4C3AFuCPWjAtAz4DXAxsBC5vYyVJE2KkEEmyFngv8Lm2HuA9wO1tyC7g0ra8ta3Ttl/Qxm8Fbq2qv62qR4Fp4Pz2mK6qQ1X1Y+DWNlaSNCFGfSfyKeB3gL9v628CflhVx9r6YWBNW14DPAHQtj/Xxr9Qn7XPfHVJ0oToDpEk7wOerqr7xthPby/bk0wlmZqZmVnsdiTpNWOUdyLvAn4pyWMMTjW9B/g0sCLJ8jZmLXCkLR8BzgVo288Enhmuz9pnvvrLVNWNVbWpqjatXr16hJckSXo1ukOkqq6pqrVVtY7BhfGvVdWvAl8HfrkN2wbc0ZZ3t3Xa9q9VVbX6Ze3urfXABuAbwL3Ahna312ntGLt7+5Ukjd/ykw951f4jcGuS3wPuB25q9ZuALySZBo4yCAWqan+S24CHgWPA1VX1dwBJPgTsAZYBO6tq/wL0K0nqNJYQqaq/BP6yLR9icGfV7DF/A/zKPPt/HPj4HPW7gLvG0aMkafz8jXVJUjdDRJLUzRCRJHUzRCRJ3QwRSVI3Q0SS1M0QkSR1M0QkSd0MEUlSN0NEktTNEJEkdTNEJEndDBFJUjdDRJLUzRCRJHUzRCRJ3QwRSVI3Q0SS1M0QkSR1M0QkSd0MEUlSN0NEktTNEJEkdTNEJEndDBFJUjdDRJLUzRCRJHUzRCRJ3QwRSVI3Q0SS1M0QkSR1M0QkSd26QyTJuUm+nuThJPuTfLjVVyXZm+Rg+7iy1ZPk+iTTSR5Ict7Qc21r4w8m2TZUf3uSB9s+1yfJKC9WkjReo7wTOQb8dlVtBDYDVyfZCOwA7q6qDcDdbR3gYmBDe2wHboBB6ADXAu8AzgeuPR48bcwHh/bbMkK/kqQx6w6Rqnqyqr7Zlv8v8AiwBtgK7GrDdgGXtuWtwM01cA+wIsk5wEXA3qo6WlXPAnuBLW3bG6vqnqoq4Oah55IkTYCxXBNJsg54G7APOLuqnmybngLObstrgCeGdjvcaieqH56jPtfxtyeZSjI1MzMz2ouRJL1iI4dIkp8G/gz4zap6fnhbewdRox7jZKrqxqraVFWbVq9evdCHkyQ1I4VIkp9iECC3VNWXW/n77VQU7ePTrX4EOHdo97WtdqL62jnqkqQJMcrdWQFuAh6pqv86tGk3cPwOq23AHUP1K9pdWpuB59pprz3AhUlWtgvqFwJ72rbnk2xux7pi6LkkSRNg+Qj7vgv4N8CDSb7Var8LfAK4LcmVwOPA+9u2u4BLgGngR8AHAKrqaJKPAfe2cR+tqqNt+Srg88AZwFfbQ5I0IbpDpKr+DzDf721cMMf4Aq6e57l2AjvnqE8Bb+3tUZK0sPyNdUlSN0NEktTNEJEkdTNEJEndDBFJUjdDRJLUzRCRJHUzRCRJ3QwRSVI3Q0SS1M0QkSR1M0QkSd0MEUlSN0NEktTNEJEkdTNEJEndDBFJUjdDRJLUzRCRJHUzRCRJ3QwRSVI3Q0SS1M0QkSR1M0QkSd0MEUlSN0NEktTNEJEkdTNEJEndDBFJUjdDRJLUzRCRJHWb+BBJsiXJgSTTSXYsdj+SpBdNdIgkWQZ8BrgY2AhcnmTj4nYlSTpu+WI3cBLnA9NVdQggya3AVuDhRe1qzNbt+MqiHfuxT7x30Y4taemb9BBZAzwxtH4YeMfsQUm2A9vb6v9LcqDzeGcBP+jcd7GM1HOuG2Mnr8xrbo4XwVLrF5Zez0utXzh5z/+050knPURekaq6Ebhx1OdJMlVVm8bQ0imz1Hpeav3C0ut5qfULS6/npdYvLFzPE31NBDgCnDu0vrbVJEkTYNJD5F5gQ5L1SU4DLgN2L3JPkqRmok9nVdWxJB8C9gDLgJ1VtX8BDznyKbFFsNR6Xmr9wtLrean1C0uv56XWLyxQz6mqhXheSdJrwKSfzpIkTTBDRJLUzRBpJuXPqyQ5N8nXkzycZH+SD7f6qiR7kxxsH1e2epJc3/p+IMl5Q8+1rY0/mGTbAve9LMn9Se5s6+uT7Gt9fandGEGS09v6dNu+bug5rmn1A0kuWuB+VyS5Pcl3kjyS5J2TPMdJfqt9PTyU5ItJXjdpc5xkZ5Knkzw0VBvbnCZ5e5IH2z7XJ8kC9fwH7evigST/I8mKoW1zzt98Pz/m+xyNs9+hbb+dpJKc1dZPzRxX1Wv+weCi/XeBNwOnAd8GNi5SL+cA57Xlfwz8FYM/+fL7wI5W3wFc15YvAb4KBNgM7Gv1VcCh9nFlW165gH3/B+BPgTvb+m3AZW35s8C/a8tXAZ9ty5cBX2rLG9u8nw6sb5+PZQvY7y7g37bl04AVkzrHDH7p9lHgjKG5/bVJm2PgF4DzgIeGamObU+AbbWzavhcvUM8XAsvb8nVDPc85f5zg58d8n6Nx9tvq5zK4Aelx4KxTOccL8g261B7AO4E9Q+vXANcsdl+tlzuAfwUcAM5ptXOAA235j4HLh8YfaNsvB/54qP6ScWPucS1wN/Ae4M72BfiDoW/EF+a3faG/sy0vb+Mye86Hxy1Av2cy+KGcWfWJnGNe/MsNq9qc3QlcNIlzDKzjpT+QxzKnbdt3huovGTfOnmdt+9fALW15zvljnp8fJ/o+GHe/wO3AzwOP8WKInJI59nTWwFx/XmXNIvXygnYa4m3APuDsqnqybXoKOLstz9f7qXxNnwJ+B/j7tv4m4IdVdWyOY7/QV9v+XBt/KvtdD8wAf5LBKbjPJXkDEzrHVXUE+EPge8CTDObsPiZ7jo8b15yuacuz6wvt1xn8i5yT9DZX/UTfB2OTZCtwpKq+PWvTKZljQ2RCJflp4M+A36yq54e31eCfCRNxb3aS9wFPV9V9i93Lq7CcwSmBG6rqbcBfMzjV8oIJm+OVDP7w6HrgZ4A3AFsWtakOkzSnr0SSjwDHgFsWu5f5JHk98LvAf1qsHgyRgYn68ypJfopBgNxSVV9u5e8nOadtPwd4utXn6/1UvaZ3Ab+U5DHgVgantD4NrEhy/JdZh4/9Ql9t+5nAM6ewXxj8C+twVe1r67czCJVJneNfBB6tqpmq+gnwZQbzPslzfNy45vRIW55dXxBJfg14H/CrLfw4SW9z1Z9h/s/RuPwzBv+4+Hb7HlwLfDPJP+not2+Ox3k+dKk+GPzL9FD7ZBy/MPaWReolwM3Ap2bV/4CXXqD8/bb8Xl568ewbrb6KwXn/le3xKLBqgXt/Ny9eWP/vvPSC4lVt+WpeetH3trb8Fl560fIQC3th/X8DP9eW/3Ob34mcYwZ/uXo/8PrWwy7gNyZxjnn5NZGxzSkvv+h7yQL1vIXBfzexeta4OeePE/z8mO9zNM5+Z217jBeviZySOV6wHyhL7cHgToa/YnCXxUcWsY9/yeAt/wPAt9rjEgbnV+8GDgL/c+iTHgb/cdd3gQeBTUPP9evAdHt84BT0/m5eDJE3ty/I6faNdHqrv66tT7ftbx7a/yPtdRxgDHfenKTXfwFMtXn+8/bNNLFzDPwX4DvAQ8AX2g+yiZpj4IsMrtn8hMG7vSvHOafApvb6vwv8N2bdGDHGnqcZXDM4/v332ZPNH/P8/JjvczTOfmdtf4wXQ+SUzLF/9kSS1M1rIpKkboaIJKmbISJJ6maISJK6GSKSpG6GiCSpmyEiSer2/wEa+C6kjNlCbgAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "plt.hist(payments_df[\"payment_value\"]);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>payment_sequential</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>payment_installments</th>\n",
       "      <th>payment_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>57411</th>\n",
       "      <td>00b1cb0320190ca0daa2c88b35206009</td>\n",
       "      <td>1</td>\n",
       "      <td>not_defined</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100766</th>\n",
       "      <td>b23878b3e8eb4d25a158f57d96331b18</td>\n",
       "      <td>4</td>\n",
       "      <td>voucher</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51280</th>\n",
       "      <td>4637ca194b6387e2d538dc89b124b0ee</td>\n",
       "      <td>1</td>\n",
       "      <td>not_defined</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36822</th>\n",
       "      <td>fa65dad1b0e818e3ccc5cb0e39231352</td>\n",
       "      <td>14</td>\n",
       "      <td>voucher</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77885</th>\n",
       "      <td>fa65dad1b0e818e3ccc5cb0e39231352</td>\n",
       "      <td>13</td>\n",
       "      <td>voucher</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85539</th>\n",
       "      <td>f5136e38d1a14a4dbd87dff67da82701</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>6726.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49581</th>\n",
       "      <td>fefacc66af859508bf1a7934eab1e97f</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>6922.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41419</th>\n",
       "      <td>0812eb902a67711a1cb742b3cdaa65ae</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>8</td>\n",
       "      <td>6929.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34370</th>\n",
       "      <td>736e1922ae60d0d6a89247b851902527</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>7274.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52107</th>\n",
       "      <td>03caa2c082116e1d31e67e9ae3700499</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>13664.08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>103886 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                order_id  payment_sequential payment_type  \\\n",
       "57411   00b1cb0320190ca0daa2c88b35206009                   1  not_defined   \n",
       "100766  b23878b3e8eb4d25a158f57d96331b18                   4      voucher   \n",
       "51280   4637ca194b6387e2d538dc89b124b0ee                   1  not_defined   \n",
       "36822   fa65dad1b0e818e3ccc5cb0e39231352                  14      voucher   \n",
       "77885   fa65dad1b0e818e3ccc5cb0e39231352                  13      voucher   \n",
       "...                                  ...                 ...          ...   \n",
       "85539   f5136e38d1a14a4dbd87dff67da82701                   1       boleto   \n",
       "49581   fefacc66af859508bf1a7934eab1e97f                   1       boleto   \n",
       "41419   0812eb902a67711a1cb742b3cdaa65ae                   1  credit_card   \n",
       "34370   736e1922ae60d0d6a89247b851902527                   1       boleto   \n",
       "52107   03caa2c082116e1d31e67e9ae3700499                   1  credit_card   \n",
       "\n",
       "        payment_installments  payment_value  \n",
       "57411                      1           0.00  \n",
       "100766                     1           0.00  \n",
       "51280                      1           0.00  \n",
       "36822                      1           0.00  \n",
       "77885                      1           0.00  \n",
       "...                      ...            ...  \n",
       "85539                      1        6726.66  \n",
       "49581                      1        6922.21  \n",
       "41419                      8        6929.31  \n",
       "34370                      1        7274.88  \n",
       "52107                      1       13664.08  \n",
       "\n",
       "[103886 rows x 5 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#We also have some payment_value equal to zero\n",
    "payments_df.sort_values([\"payment_value\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pre-Join Data Cleaning\n",
    "- **customers_dataset** Drop all but `customer_unique_id` and `customer_id`\n",
    "- **payments_df**: group by `order_id` and sum to get total `payment_value` per unique order\n",
    "- **payments_df**: drop all but the `order_id` and  `payment_value` column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_unique_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>06b8999e2fba1a1fbc88172c00ba8bc7</th>\n",
       "      <td>861eff4711a542e4b93843c6dd7febb0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18955e83d337fd6b2def6b18a428ac77</th>\n",
       "      <td>290c77bc529b7ac935b93aa66c333dc3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4e7b3e00288586ebd08712fdd0374a03</th>\n",
       "      <td>060e732b5b29e8181a18229c7b0b2b5e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b2b6027bc5c5109e529d4dc6358b12c3</th>\n",
       "      <td>259dac757896d24d7702b9acbbff3f3c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4f2d8ab171c80ec8364f7c12e35b23ad</th>\n",
       "      <td>345ecd01c38d18a9036ed96c73b8d066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17ddf5dd5d51696bb3d7c6291687be6f</th>\n",
       "      <td>1a29b476fee25c95fbafc67c5ac95cf8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e7b71a9017aa05c9a7fd292d714858e8</th>\n",
       "      <td>d52a67c98be1cf6a5c84435bd38d095d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5e28dfe12db7fb50a4b2f691faecea5e</th>\n",
       "      <td>e9f50caf99f032f0bf3c55141f019d99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56b18e2166679b8a959d72dd06da27f9</th>\n",
       "      <td>73c2643a0a458b49f58cea58833b192e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>274fa6071e5e17fe303b9748641082c8</th>\n",
       "      <td>84732c5050c01db9b23e19ba39899398</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99441 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                customer_unique_id\n",
       "customer_id                                                       \n",
       "06b8999e2fba1a1fbc88172c00ba8bc7  861eff4711a542e4b93843c6dd7febb0\n",
       "18955e83d337fd6b2def6b18a428ac77  290c77bc529b7ac935b93aa66c333dc3\n",
       "4e7b3e00288586ebd08712fdd0374a03  060e732b5b29e8181a18229c7b0b2b5e\n",
       "b2b6027bc5c5109e529d4dc6358b12c3  259dac757896d24d7702b9acbbff3f3c\n",
       "4f2d8ab171c80ec8364f7c12e35b23ad  345ecd01c38d18a9036ed96c73b8d066\n",
       "...                                                            ...\n",
       "17ddf5dd5d51696bb3d7c6291687be6f  1a29b476fee25c95fbafc67c5ac95cf8\n",
       "e7b71a9017aa05c9a7fd292d714858e8  d52a67c98be1cf6a5c84435bd38d095d\n",
       "5e28dfe12db7fb50a4b2f691faecea5e  e9f50caf99f032f0bf3c55141f019d99\n",
       "56b18e2166679b8a959d72dd06da27f9  73c2643a0a458b49f58cea58833b192e\n",
       "274fa6071e5e17fe303b9748641082c8  84732c5050c01db9b23e19ba39899398\n",
       "\n",
       "[99441 rows x 1 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Drop all but `customer_unique_id` and `customer_id`\n",
    "customers_df_cleaned = customers_df.drop(columns=[\"customer_zip_code_prefix\",\"customer_city\",\"customer_state\"])\n",
    "#Reset our index\n",
    "customers_df_cleaned.set_index('customer_id',inplace=True)\n",
    "customers_df_cleaned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "#group by `order_id` and sum to get total `payment_value` per unique order\n",
    "payments_df_cleaned = payments_df.groupby(\"order_id\",).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>payment_value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>order_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>00010242fe8c5a6d1ba2dd792cb16214</th>\n",
       "      <td>72.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00018f77f2f0320c557190d7a144bdd3</th>\n",
       "      <td>259.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000229ec398224ef6ca0657da4fc703e</th>\n",
       "      <td>216.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00024acbcdf0a6daa1e931b038114c75</th>\n",
       "      <td>25.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00042b26cf59d7ce69dfabb4e55b4fd9</th>\n",
       "      <td>218.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fffc94f6ce00a00581880bf54a75a037</th>\n",
       "      <td>343.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fffcd46ef2263f404302a634eb57f7eb</th>\n",
       "      <td>386.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fffce4705a9662cd70adb13d4a31832d</th>\n",
       "      <td>116.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fffe18544ffabc95dfada21779c9644f</th>\n",
       "      <td>64.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fffe41c64501cc87c801fd61db3f6244</th>\n",
       "      <td>55.79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99440 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  payment_value\n",
       "order_id                                       \n",
       "00010242fe8c5a6d1ba2dd792cb16214          72.19\n",
       "00018f77f2f0320c557190d7a144bdd3         259.83\n",
       "000229ec398224ef6ca0657da4fc703e         216.87\n",
       "00024acbcdf0a6daa1e931b038114c75          25.78\n",
       "00042b26cf59d7ce69dfabb4e55b4fd9         218.04\n",
       "...                                         ...\n",
       "fffc94f6ce00a00581880bf54a75a037         343.40\n",
       "fffcd46ef2263f404302a634eb57f7eb         386.53\n",
       "fffce4705a9662cd70adb13d4a31832d         116.85\n",
       "fffe18544ffabc95dfada21779c9644f          64.71\n",
       "fffe41c64501cc87c801fd61db3f6244          55.79\n",
       "\n",
       "[99440 rows x 1 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#drop all but the `order_id` and  `payment_value` column\n",
    "payments_df_cleaned = payments_df_cleaned[[\"payment_value\"]]\n",
    "payments_df_cleaned "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_status</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>order_approved_at</th>\n",
       "      <th>order_delivered_carrier_date</th>\n",
       "      <th>order_delivered_customer_date</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9ef432eb6251297304e76186b10a928d</th>\n",
       "      <td>e481f51cbdc54678b7cc49136f2d6af7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-10-02</td>\n",
       "      <td>2017-10-02 11:07:15</td>\n",
       "      <td>2017-10-04 19:55:00</td>\n",
       "      <td>2017-10-10 21:25:13</td>\n",
       "      <td>2017-10-18 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b0830fb4747a6c6d20dea0b8c802d7ef</th>\n",
       "      <td>53cdb2fc8bc7dce0b6741e2150273451</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-07-24</td>\n",
       "      <td>2018-07-26 03:24:27</td>\n",
       "      <td>2018-07-26 14:31:00</td>\n",
       "      <td>2018-08-07 15:27:45</td>\n",
       "      <td>2018-08-13 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41ce2a54c0b03bf3443c3d931a367089</th>\n",
       "      <td>47770eb9100c2d0c44946d9cf07ec65d</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>2018-08-08 08:55:23</td>\n",
       "      <td>2018-08-08 13:50:00</td>\n",
       "      <td>2018-08-17 18:06:29</td>\n",
       "      <td>2018-09-04 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f88197465ea7920adcdbec7375364d82</th>\n",
       "      <td>949d5b44dbf5de918fe9c16f97b45f8a</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-18</td>\n",
       "      <td>2017-11-18 19:45:59</td>\n",
       "      <td>2017-11-22 13:39:59</td>\n",
       "      <td>2017-12-02 00:28:42</td>\n",
       "      <td>2017-12-15 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8ab97904e6daea8866dbdbc4fb7aad2c</th>\n",
       "      <td>ad21c59c0840e6cb83a9ceb5573f8159</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-13</td>\n",
       "      <td>2018-02-13 22:20:29</td>\n",
       "      <td>2018-02-14 19:46:34</td>\n",
       "      <td>2018-02-16 18:17:02</td>\n",
       "      <td>2018-02-26 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39bd1228ee8140590ac3aca26f2dfe00</th>\n",
       "      <td>9c5dedf39a927c1b2549525ed64a053c</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-03-09</td>\n",
       "      <td>2017-03-09 09:54:05</td>\n",
       "      <td>2017-03-10 11:18:03</td>\n",
       "      <td>2017-03-17 15:08:01</td>\n",
       "      <td>2017-03-28 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1fca14ff2861355f6e5f14306ff977a7</th>\n",
       "      <td>63943bddc261676b46f01ca7ac2f7bd8</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-06</td>\n",
       "      <td>2018-02-06 13:10:37</td>\n",
       "      <td>2018-02-07 23:22:42</td>\n",
       "      <td>2018-02-28 17:37:56</td>\n",
       "      <td>2018-03-02 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1aa71eb042121263aafbe80c1b562c9c</th>\n",
       "      <td>83c1379a015df1e13d02aae0204711ab</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-08-27</td>\n",
       "      <td>2017-08-27 15:04:16</td>\n",
       "      <td>2017-08-28 20:52:26</td>\n",
       "      <td>2017-09-21 11:24:17</td>\n",
       "      <td>2017-09-27 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b331b74b18dc79bcdf6532d51e1637c1</th>\n",
       "      <td>11c177c8e97725db2631073c19f07b62</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>2018-01-08 21:36:21</td>\n",
       "      <td>2018-01-12 15:35:03</td>\n",
       "      <td>2018-01-25 23:32:54</td>\n",
       "      <td>2018-02-15 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>edb027a75a1449115f6b43211ae02a24</th>\n",
       "      <td>66dea50a8b16d9b4dee7af250b4be1a5</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-03-08</td>\n",
       "      <td>2018-03-09 11:20:28</td>\n",
       "      <td>2018-03-09 22:11:59</td>\n",
       "      <td>2018-03-16 13:08:30</td>\n",
       "      <td>2018-04-03 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99441 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                          order_id  \\\n",
       "customer_id                                                          \n",
       "9ef432eb6251297304e76186b10a928d  e481f51cbdc54678b7cc49136f2d6af7   \n",
       "b0830fb4747a6c6d20dea0b8c802d7ef  53cdb2fc8bc7dce0b6741e2150273451   \n",
       "41ce2a54c0b03bf3443c3d931a367089  47770eb9100c2d0c44946d9cf07ec65d   \n",
       "f88197465ea7920adcdbec7375364d82  949d5b44dbf5de918fe9c16f97b45f8a   \n",
       "8ab97904e6daea8866dbdbc4fb7aad2c  ad21c59c0840e6cb83a9ceb5573f8159   \n",
       "...                                                            ...   \n",
       "39bd1228ee8140590ac3aca26f2dfe00  9c5dedf39a927c1b2549525ed64a053c   \n",
       "1fca14ff2861355f6e5f14306ff977a7  63943bddc261676b46f01ca7ac2f7bd8   \n",
       "1aa71eb042121263aafbe80c1b562c9c  83c1379a015df1e13d02aae0204711ab   \n",
       "b331b74b18dc79bcdf6532d51e1637c1  11c177c8e97725db2631073c19f07b62   \n",
       "edb027a75a1449115f6b43211ae02a24  66dea50a8b16d9b4dee7af250b4be1a5   \n",
       "\n",
       "                                 order_status order_purchase_timestamp  \\\n",
       "customer_id                                                              \n",
       "9ef432eb6251297304e76186b10a928d    delivered               2017-10-02   \n",
       "b0830fb4747a6c6d20dea0b8c802d7ef    delivered               2018-07-24   \n",
       "41ce2a54c0b03bf3443c3d931a367089    delivered               2018-08-08   \n",
       "f88197465ea7920adcdbec7375364d82    delivered               2017-11-18   \n",
       "8ab97904e6daea8866dbdbc4fb7aad2c    delivered               2018-02-13   \n",
       "...                                       ...                      ...   \n",
       "39bd1228ee8140590ac3aca26f2dfe00    delivered               2017-03-09   \n",
       "1fca14ff2861355f6e5f14306ff977a7    delivered               2018-02-06   \n",
       "1aa71eb042121263aafbe80c1b562c9c    delivered               2017-08-27   \n",
       "b331b74b18dc79bcdf6532d51e1637c1    delivered               2018-01-08   \n",
       "edb027a75a1449115f6b43211ae02a24    delivered               2018-03-08   \n",
       "\n",
       "                                    order_approved_at  \\\n",
       "customer_id                                             \n",
       "9ef432eb6251297304e76186b10a928d  2017-10-02 11:07:15   \n",
       "b0830fb4747a6c6d20dea0b8c802d7ef  2018-07-26 03:24:27   \n",
       "41ce2a54c0b03bf3443c3d931a367089  2018-08-08 08:55:23   \n",
       "f88197465ea7920adcdbec7375364d82  2017-11-18 19:45:59   \n",
       "8ab97904e6daea8866dbdbc4fb7aad2c  2018-02-13 22:20:29   \n",
       "...                                               ...   \n",
       "39bd1228ee8140590ac3aca26f2dfe00  2017-03-09 09:54:05   \n",
       "1fca14ff2861355f6e5f14306ff977a7  2018-02-06 13:10:37   \n",
       "1aa71eb042121263aafbe80c1b562c9c  2017-08-27 15:04:16   \n",
       "b331b74b18dc79bcdf6532d51e1637c1  2018-01-08 21:36:21   \n",
       "edb027a75a1449115f6b43211ae02a24  2018-03-09 11:20:28   \n",
       "\n",
       "                                 order_delivered_carrier_date  \\\n",
       "customer_id                                                     \n",
       "9ef432eb6251297304e76186b10a928d          2017-10-04 19:55:00   \n",
       "b0830fb4747a6c6d20dea0b8c802d7ef          2018-07-26 14:31:00   \n",
       "41ce2a54c0b03bf3443c3d931a367089          2018-08-08 13:50:00   \n",
       "f88197465ea7920adcdbec7375364d82          2017-11-22 13:39:59   \n",
       "8ab97904e6daea8866dbdbc4fb7aad2c          2018-02-14 19:46:34   \n",
       "...                                                       ...   \n",
       "39bd1228ee8140590ac3aca26f2dfe00          2017-03-10 11:18:03   \n",
       "1fca14ff2861355f6e5f14306ff977a7          2018-02-07 23:22:42   \n",
       "1aa71eb042121263aafbe80c1b562c9c          2017-08-28 20:52:26   \n",
       "b331b74b18dc79bcdf6532d51e1637c1          2018-01-12 15:35:03   \n",
       "edb027a75a1449115f6b43211ae02a24          2018-03-09 22:11:59   \n",
       "\n",
       "                                 order_delivered_customer_date  \\\n",
       "customer_id                                                      \n",
       "9ef432eb6251297304e76186b10a928d           2017-10-10 21:25:13   \n",
       "b0830fb4747a6c6d20dea0b8c802d7ef           2018-08-07 15:27:45   \n",
       "41ce2a54c0b03bf3443c3d931a367089           2018-08-17 18:06:29   \n",
       "f88197465ea7920adcdbec7375364d82           2017-12-02 00:28:42   \n",
       "8ab97904e6daea8866dbdbc4fb7aad2c           2018-02-16 18:17:02   \n",
       "...                                                        ...   \n",
       "39bd1228ee8140590ac3aca26f2dfe00           2017-03-17 15:08:01   \n",
       "1fca14ff2861355f6e5f14306ff977a7           2018-02-28 17:37:56   \n",
       "1aa71eb042121263aafbe80c1b562c9c           2017-09-21 11:24:17   \n",
       "b331b74b18dc79bcdf6532d51e1637c1           2018-01-25 23:32:54   \n",
       "edb027a75a1449115f6b43211ae02a24           2018-03-16 13:08:30   \n",
       "\n",
       "                                 order_estimated_delivery_date  \n",
       "customer_id                                                     \n",
       "9ef432eb6251297304e76186b10a928d           2017-10-18 00:00:00  \n",
       "b0830fb4747a6c6d20dea0b8c802d7ef           2018-08-13 00:00:00  \n",
       "41ce2a54c0b03bf3443c3d931a367089           2018-09-04 00:00:00  \n",
       "f88197465ea7920adcdbec7375364d82           2017-12-15 00:00:00  \n",
       "8ab97904e6daea8866dbdbc4fb7aad2c           2018-02-26 00:00:00  \n",
       "...                                                        ...  \n",
       "39bd1228ee8140590ac3aca26f2dfe00           2017-03-28 00:00:00  \n",
       "1fca14ff2861355f6e5f14306ff977a7           2018-03-02 00:00:00  \n",
       "1aa71eb042121263aafbe80c1b562c9c           2017-09-27 00:00:00  \n",
       "b331b74b18dc79bcdf6532d51e1637c1           2018-02-15 00:00:00  \n",
       "edb027a75a1449115f6b43211ae02a24           2018-04-03 00:00:00  \n",
       "\n",
       "[99441 rows x 7 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_df_cleaned = orders_df.set_index(\"customer_id\")\n",
    "orders_df_cleaned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#join customers and orders together with customer_id\n",
    "lifetimes_object_df_uncleaned = pd.concat([customers_df_cleaned,orders_df_cleaned], axis=1, join='inner')\n",
    "#check that we retained all our unique customer_id's\n",
    "len(lifetimes_object_df_uncleaned) == len(customers_df_cleaned) == len(orders_df_cleaned)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_status</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>order_approved_at</th>\n",
       "      <th>order_delivered_carrier_date</th>\n",
       "      <th>order_delivered_customer_date</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>06b8999e2fba1a1fbc88172c00ba8bc7</th>\n",
       "      <td>861eff4711a542e4b93843c6dd7febb0</td>\n",
       "      <td>00e7ee1b050b8499577073aeb2a297a1</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-16</td>\n",
       "      <td>2017-05-16 15:22:12</td>\n",
       "      <td>2017-05-23 10:47:57</td>\n",
       "      <td>2017-05-25 10:35:35</td>\n",
       "      <td>2017-06-05 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18955e83d337fd6b2def6b18a428ac77</th>\n",
       "      <td>290c77bc529b7ac935b93aa66c333dc3</td>\n",
       "      <td>29150127e6685892b6eab3eec79f59c7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>2018-01-12 20:58:32</td>\n",
       "      <td>2018-01-15 17:14:59</td>\n",
       "      <td>2018-01-29 12:41:19</td>\n",
       "      <td>2018-02-06 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                customer_unique_id  \\\n",
       "customer_id                                                          \n",
       "06b8999e2fba1a1fbc88172c00ba8bc7  861eff4711a542e4b93843c6dd7febb0   \n",
       "18955e83d337fd6b2def6b18a428ac77  290c77bc529b7ac935b93aa66c333dc3   \n",
       "\n",
       "                                                          order_id  \\\n",
       "customer_id                                                          \n",
       "06b8999e2fba1a1fbc88172c00ba8bc7  00e7ee1b050b8499577073aeb2a297a1   \n",
       "18955e83d337fd6b2def6b18a428ac77  29150127e6685892b6eab3eec79f59c7   \n",
       "\n",
       "                                 order_status order_purchase_timestamp  \\\n",
       "customer_id                                                              \n",
       "06b8999e2fba1a1fbc88172c00ba8bc7    delivered               2017-05-16   \n",
       "18955e83d337fd6b2def6b18a428ac77    delivered               2018-01-12   \n",
       "\n",
       "                                    order_approved_at  \\\n",
       "customer_id                                             \n",
       "06b8999e2fba1a1fbc88172c00ba8bc7  2017-05-16 15:22:12   \n",
       "18955e83d337fd6b2def6b18a428ac77  2018-01-12 20:58:32   \n",
       "\n",
       "                                 order_delivered_carrier_date  \\\n",
       "customer_id                                                     \n",
       "06b8999e2fba1a1fbc88172c00ba8bc7          2017-05-23 10:47:57   \n",
       "18955e83d337fd6b2def6b18a428ac77          2018-01-15 17:14:59   \n",
       "\n",
       "                                 order_delivered_customer_date  \\\n",
       "customer_id                                                      \n",
       "06b8999e2fba1a1fbc88172c00ba8bc7           2017-05-25 10:35:35   \n",
       "18955e83d337fd6b2def6b18a428ac77           2018-01-29 12:41:19   \n",
       "\n",
       "                                 order_estimated_delivery_date  \n",
       "customer_id                                                     \n",
       "06b8999e2fba1a1fbc88172c00ba8bc7           2017-06-05 00:00:00  \n",
       "18955e83d337fd6b2def6b18a428ac77           2018-02-06 00:00:00  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lifetimes_object_df_uncleaned.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reset index to order_id so we can join with payment info\n",
    "lifetimes_object_df_uncleaned.set_index(\"order_id\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Join with payment info\n",
    "lifetimes_object_df_uncleaned = pd.concat([lifetimes_object_df_uncleaned,payments_df_cleaned], axis=1, join='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "lifetimes_object_df_uncleaned.to_csv(\"data/lifetimes_object_df_uncleaned.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Post-Join Data Cleaning\n",
    "- Drop `payment_value` less or equal to zero\n",
    "- Drop 'canceled' and 'unavailable' rows in the `order_status` column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Drop `payment_value` less or equal to zero\n",
    "lifetimes_object_df = lifetimes_object_df_uncleaned[lifetimes_object_df_uncleaned[\"payment_value\"]>0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Drop 'canceled' and 'unavailable' rows in the `order_status` column\n",
    "lifetimes_object_df = lifetimes_object_df[lifetimes_object_df[\"order_status\"]!=\"canceled\"]\n",
    "lifetimes_object_df = lifetimes_object_df[lifetimes_object_df[\"order_status\"]!=\"unavailable\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "We dropped 1234 rows\n"
     ]
    }
   ],
   "source": [
    "#counting dropped rows\n",
    "print(f'We dropped {len(lifetimes_object_df_uncleaned) - len(lifetimes_object_df)} rows')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "lifetimes_object_df.to_csv(\"data/lifetimes_object_df.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (lifetime-env)",
   "language": "python",
   "name": "lifetime-env"
  },
  "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.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}