Creating Orders Dataset.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating Customers Dataset\n",
    "\n",
    "The objective is to join all tables of Olist dataset into one to carry out an exploration of the orders data relying on one single datasource."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [],
   "source": [
    "customer_data=pd.read_csv('olist_customers_dataset.csv')\n",
    "#orders_dataset contains dates, parse_dates arg should be called to convert them to datetime objects\n",
    "order_data=pd.read_csv('olist_orders_dataset.csv',parse_dates=['order_purchase_timestamp','order_approved_at','order_delivered_carrier_date','order_delivered_customer_date','order_estimated_delivery_date'])\n",
    "order_items=pd.read_csv('olist_order_items_dataset.csv')\n",
    "payments=pd.read_csv('olist_order_payments_dataset.csv')\n",
    "reviews=pd.read_csv('olist_order_reviews_dataset.csv')\n",
    "products=pd.read_csv('olist_products_dataset.csv')\n",
    "seller_geo=pd.read_csv('olist_sellers_dataset.csv')\n",
    "product_category=pd.read_csv('product_category_name_translation.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "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",
      "order_id                         99441 non-null object\n",
      "customer_id                      99441 non-null object\n",
      "order_status                     99441 non-null object\n",
      "order_purchase_timestamp         99441 non-null datetime64[ns]\n",
      "order_approved_at                99281 non-null datetime64[ns]\n",
      "order_delivered_carrier_date     97658 non-null datetime64[ns]\n",
      "order_delivered_customer_date    96476 non-null datetime64[ns]\n",
      "order_estimated_delivery_date    99441 non-null datetime64[ns]\n",
      "dtypes: datetime64[ns](5), object(3)\n",
      "memory usage: 6.1+ MB\n"
     ]
    }
   ],
   "source": [
    "# order_data: check if dates are datetime \n",
    "order_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "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 10:56:33</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</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 20:41:37</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</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 08:38:49</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</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 19:28:06</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</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 21:18:39</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</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 10:56:33 2017-10-02 11:07:15   \n",
       "1    delivered      2018-07-24 20:41:37 2018-07-26 03:24:27   \n",
       "2    delivered      2018-08-08 08:38:49 2018-08-08 08:55:23   \n",
       "3    delivered      2017-11-18 19:28:06 2017-11-18 19:45:59   \n",
       "4    delivered      2018-02-13 21:18:39 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  \n",
       "1                    2018-08-13  \n",
       "2                    2018-09-04  \n",
       "3                    2017-12-15  \n",
       "4                    2018-02-26  "
      ]
     },
     "execution_count": 179,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#order_data: check format of dates\n",
    "order_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"data_schema.png\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "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",
       "      <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",
       "  </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",
       "      <td>00e7ee1b050b8499577073aeb2a297a1</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-16 15:05:35</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</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",
       "      <td>29150127e6685892b6eab3eec79f59c7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-01-12 20:48:24</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</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",
       "      <td>b2059ed67ce144a36e2aa97d2c9e9ad2</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-05-19 16:07:45</td>\n",
       "      <td>2018-05-20 16:19:10</td>\n",
       "      <td>2018-06-11 14:31:00</td>\n",
       "      <td>2018-06-14 17:58:51</td>\n",
       "      <td>2018-06-13</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",
       "      <td>951670f92359f4fe4a63112aa7306eba</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-03-13 16:06:38</td>\n",
       "      <td>2018-03-13 17:29:19</td>\n",
       "      <td>2018-03-27 23:22:42</td>\n",
       "      <td>2018-03-28 16:04:25</td>\n",
       "      <td>2018-04-10</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",
       "      <td>6b7d50bd145f6fc7f33cebabd7e49d0f</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-07-29 09:51:30</td>\n",
       "      <td>2018-07-29 10:10:09</td>\n",
       "      <td>2018-07-30 15:16:00</td>\n",
       "      <td>2018-08-09 20:55:48</td>\n",
       "      <td>2018-08-15</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   \n",
       "\n",
       "                           order_id order_status order_purchase_timestamp  \\\n",
       "0  00e7ee1b050b8499577073aeb2a297a1    delivered      2017-05-16 15:05:35   \n",
       "1  29150127e6685892b6eab3eec79f59c7    delivered      2018-01-12 20:48:24   \n",
       "2  b2059ed67ce144a36e2aa97d2c9e9ad2    delivered      2018-05-19 16:07:45   \n",
       "3  951670f92359f4fe4a63112aa7306eba    delivered      2018-03-13 16:06:38   \n",
       "4  6b7d50bd145f6fc7f33cebabd7e49d0f    delivered      2018-07-29 09:51:30   \n",
       "\n",
       "    order_approved_at order_delivered_carrier_date  \\\n",
       "0 2017-05-16 15:22:12          2017-05-23 10:47:57   \n",
       "1 2018-01-12 20:58:32          2018-01-15 17:14:59   \n",
       "2 2018-05-20 16:19:10          2018-06-11 14:31:00   \n",
       "3 2018-03-13 17:29:19          2018-03-27 23:22:42   \n",
       "4 2018-07-29 10:10:09          2018-07-30 15:16:00   \n",
       "\n",
       "  order_delivered_customer_date order_estimated_delivery_date  \n",
       "0           2017-05-25 10:35:35                    2017-06-05  \n",
       "1           2018-01-29 12:41:19                    2018-02-06  \n",
       "2           2018-06-14 17:58:51                    2018-06-13  \n",
       "3           2018-03-28 16:04:25                    2018-04-10  \n",
       "4           2018-08-09 20:55:48                    2018-08-15  "
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1. merge customers and orders to merge customer data and order id, to later merge other tables\n",
    "df=customer_data.merge(order_data, on='customer_id')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2. merge df with order items data for additional data related to each order\n",
    "df=df.merge(order_items, on='order_id')\n",
    "df=df[['customer_id', 'customer_unique_id', 'customer_zip_code_prefix',\n",
    "       'customer_city', 'customer_state', 'order_id', 'order_status',\n",
    "       'order_purchase_timestamp', 'order_approved_at',\n",
    "       'order_delivered_carrier_date', 'order_delivered_customer_date',\n",
    "       'order_estimated_delivery_date','product_id', 'seller_id', 'price',\n",
    "       'freight_value']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "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",
       "      <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",
       "      <th>product_id</th>\n",
       "      <th>seller_id</th>\n",
       "      <th>price</th>\n",
       "      <th>freight_value</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>06b8999e2fba1a1fbc88172c00ba8bc7</td>\n",
       "      <td>861eff4711a542e4b93843c6dd7febb0</td>\n",
       "      <td>14409</td>\n",
       "      <td>franca</td>\n",
       "      <td>SP</td>\n",
       "      <td>00e7ee1b050b8499577073aeb2a297a1</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-16 15:05:35</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</td>\n",
       "      <td>a9516a079e37a9c9c36b9b78b10169e8</td>\n",
       "      <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "      <td>124.99</td>\n",
       "      <td>21.88</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>2</td>\n",
       "      <td>146.87</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",
       "      <td>29150127e6685892b6eab3eec79f59c7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-01-12 20:48:24</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</td>\n",
       "      <td>4aa6014eceb682077f9dc4bffebc05b0</td>\n",
       "      <td>b8bc237ba3788b23da09c0f1f3a3288c</td>\n",
       "      <td>289.00</td>\n",
       "      <td>46.48</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>8</td>\n",
       "      <td>335.48</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",
       "      <td>b2059ed67ce144a36e2aa97d2c9e9ad2</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-05-19 16:07:45</td>\n",
       "      <td>2018-05-20 16:19:10</td>\n",
       "      <td>2018-06-11 14:31:00</td>\n",
       "      <td>2018-06-14 17:58:51</td>\n",
       "      <td>2018-06-13</td>\n",
       "      <td>bd07b66896d6f1494f5b86251848ced7</td>\n",
       "      <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "      <td>139.94</td>\n",
       "      <td>17.79</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>7</td>\n",
       "      <td>157.73</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",
       "      <td>951670f92359f4fe4a63112aa7306eba</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-03-13 16:06:38</td>\n",
       "      <td>2018-03-13 17:29:19</td>\n",
       "      <td>2018-03-27 23:22:42</td>\n",
       "      <td>2018-03-28 16:04:25</td>\n",
       "      <td>2018-04-10</td>\n",
       "      <td>a5647c44af977b148e0a3a4751a09e2e</td>\n",
       "      <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "      <td>149.94</td>\n",
       "      <td>23.36</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>173.30</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",
       "      <td>6b7d50bd145f6fc7f33cebabd7e49d0f</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-07-29 09:51:30</td>\n",
       "      <td>2018-07-29 10:10:09</td>\n",
       "      <td>2018-07-30 15:16:00</td>\n",
       "      <td>2018-08-09 20:55:48</td>\n",
       "      <td>2018-08-15</td>\n",
       "      <td>9391a573abe00141c56e38d84d7d5b3b</td>\n",
       "      <td>4a3ca9315b744ce9f8e9374361493884</td>\n",
       "      <td>230.00</td>\n",
       "      <td>22.25</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>8</td>\n",
       "      <td>252.25</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   \n",
       "\n",
       "                           order_id order_status order_purchase_timestamp  \\\n",
       "0  00e7ee1b050b8499577073aeb2a297a1    delivered      2017-05-16 15:05:35   \n",
       "1  29150127e6685892b6eab3eec79f59c7    delivered      2018-01-12 20:48:24   \n",
       "2  b2059ed67ce144a36e2aa97d2c9e9ad2    delivered      2018-05-19 16:07:45   \n",
       "3  951670f92359f4fe4a63112aa7306eba    delivered      2018-03-13 16:06:38   \n",
       "4  6b7d50bd145f6fc7f33cebabd7e49d0f    delivered      2018-07-29 09:51:30   \n",
       "\n",
       "    order_approved_at order_delivered_carrier_date  \\\n",
       "0 2017-05-16 15:22:12          2017-05-23 10:47:57   \n",
       "1 2018-01-12 20:58:32          2018-01-15 17:14:59   \n",
       "2 2018-05-20 16:19:10          2018-06-11 14:31:00   \n",
       "3 2018-03-13 17:29:19          2018-03-27 23:22:42   \n",
       "4 2018-07-29 10:10:09          2018-07-30 15:16:00   \n",
       "\n",
       "  order_delivered_customer_date order_estimated_delivery_date  \\\n",
       "0           2017-05-25 10:35:35                    2017-06-05   \n",
       "1           2018-01-29 12:41:19                    2018-02-06   \n",
       "2           2018-06-14 17:58:51                    2018-06-13   \n",
       "3           2018-03-28 16:04:25                    2018-04-10   \n",
       "4           2018-08-09 20:55:48                    2018-08-15   \n",
       "\n",
       "                         product_id                         seller_id   price  \\\n",
       "0  a9516a079e37a9c9c36b9b78b10169e8  7c67e1448b00f6e969d365cea6b010ab  124.99   \n",
       "1  4aa6014eceb682077f9dc4bffebc05b0  b8bc237ba3788b23da09c0f1f3a3288c  289.00   \n",
       "2  bd07b66896d6f1494f5b86251848ced7  7c67e1448b00f6e969d365cea6b010ab  139.94   \n",
       "3  a5647c44af977b148e0a3a4751a09e2e  7c67e1448b00f6e969d365cea6b010ab  149.94   \n",
       "4  9391a573abe00141c56e38d84d7d5b3b  4a3ca9315b744ce9f8e9374361493884  230.00   \n",
       "\n",
       "   freight_value  payment_sequential payment_type  payment_installments  \\\n",
       "0          21.88                   1  credit_card                     2   \n",
       "1          46.48                   1  credit_card                     8   \n",
       "2          17.79                   1  credit_card                     7   \n",
       "3          23.36                   1  credit_card                     1   \n",
       "4          22.25                   1  credit_card                     8   \n",
       "\n",
       "   payment_value  \n",
       "0         146.87  \n",
       "1         335.48  \n",
       "2         157.73  \n",
       "3         173.30  \n",
       "4         252.25  "
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 3. merge df wtih payments\n",
    "df=df.merge(payments,on='order_id')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "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",
       "      <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>...</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "      <th>product_id</th>\n",
       "      <th>seller_id</th>\n",
       "      <th>price</th>\n",
       "      <th>freight_value</th>\n",
       "      <th>payment_sequential</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>payment_installments</th>\n",
       "      <th>payment_value</th>\n",
       "      <th>review_score</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",
       "      <td>00e7ee1b050b8499577073aeb2a297a1</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-16 15:05:35</td>\n",
       "      <td>2017-05-16 15:22:12</td>\n",
       "      <td>2017-05-23 10:47:57</td>\n",
       "      <td>...</td>\n",
       "      <td>2017-06-05</td>\n",
       "      <td>a9516a079e37a9c9c36b9b78b10169e8</td>\n",
       "      <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "      <td>124.99</td>\n",
       "      <td>21.88</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>2</td>\n",
       "      <td>146.87</td>\n",
       "      <td>4</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",
       "      <td>29150127e6685892b6eab3eec79f59c7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-01-12 20:48:24</td>\n",
       "      <td>2018-01-12 20:58:32</td>\n",
       "      <td>2018-01-15 17:14:59</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-02-06</td>\n",
       "      <td>4aa6014eceb682077f9dc4bffebc05b0</td>\n",
       "      <td>b8bc237ba3788b23da09c0f1f3a3288c</td>\n",
       "      <td>289.00</td>\n",
       "      <td>46.48</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>8</td>\n",
       "      <td>335.48</td>\n",
       "      <td>5</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",
       "      <td>b2059ed67ce144a36e2aa97d2c9e9ad2</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-05-19 16:07:45</td>\n",
       "      <td>2018-05-20 16:19:10</td>\n",
       "      <td>2018-06-11 14:31:00</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-06-13</td>\n",
       "      <td>bd07b66896d6f1494f5b86251848ced7</td>\n",
       "      <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "      <td>139.94</td>\n",
       "      <td>17.79</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>7</td>\n",
       "      <td>157.73</td>\n",
       "      <td>5</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",
       "      <td>951670f92359f4fe4a63112aa7306eba</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-03-13 16:06:38</td>\n",
       "      <td>2018-03-13 17:29:19</td>\n",
       "      <td>2018-03-27 23:22:42</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-04-10</td>\n",
       "      <td>a5647c44af977b148e0a3a4751a09e2e</td>\n",
       "      <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "      <td>149.94</td>\n",
       "      <td>23.36</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>173.30</td>\n",
       "      <td>5</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",
       "      <td>6b7d50bd145f6fc7f33cebabd7e49d0f</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-07-29 09:51:30</td>\n",
       "      <td>2018-07-29 10:10:09</td>\n",
       "      <td>2018-07-30 15:16:00</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-08-15</td>\n",
       "      <td>9391a573abe00141c56e38d84d7d5b3b</td>\n",
       "      <td>4a3ca9315b744ce9f8e9374361493884</td>\n",
       "      <td>230.00</td>\n",
       "      <td>22.25</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>8</td>\n",
       "      <td>252.25</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\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   \n",
       "\n",
       "                           order_id order_status order_purchase_timestamp  \\\n",
       "0  00e7ee1b050b8499577073aeb2a297a1    delivered      2017-05-16 15:05:35   \n",
       "1  29150127e6685892b6eab3eec79f59c7    delivered      2018-01-12 20:48:24   \n",
       "2  b2059ed67ce144a36e2aa97d2c9e9ad2    delivered      2018-05-19 16:07:45   \n",
       "3  951670f92359f4fe4a63112aa7306eba    delivered      2018-03-13 16:06:38   \n",
       "4  6b7d50bd145f6fc7f33cebabd7e49d0f    delivered      2018-07-29 09:51:30   \n",
       "\n",
       "    order_approved_at order_delivered_carrier_date      ...       \\\n",
       "0 2017-05-16 15:22:12          2017-05-23 10:47:57      ...        \n",
       "1 2018-01-12 20:58:32          2018-01-15 17:14:59      ...        \n",
       "2 2018-05-20 16:19:10          2018-06-11 14:31:00      ...        \n",
       "3 2018-03-13 17:29:19          2018-03-27 23:22:42      ...        \n",
       "4 2018-07-29 10:10:09          2018-07-30 15:16:00      ...        \n",
       "\n",
       "  order_estimated_delivery_date                        product_id  \\\n",
       "0                    2017-06-05  a9516a079e37a9c9c36b9b78b10169e8   \n",
       "1                    2018-02-06  4aa6014eceb682077f9dc4bffebc05b0   \n",
       "2                    2018-06-13  bd07b66896d6f1494f5b86251848ced7   \n",
       "3                    2018-04-10  a5647c44af977b148e0a3a4751a09e2e   \n",
       "4                    2018-08-15  9391a573abe00141c56e38d84d7d5b3b   \n",
       "\n",
       "                          seller_id   price  freight_value  \\\n",
       "0  7c67e1448b00f6e969d365cea6b010ab  124.99          21.88   \n",
       "1  b8bc237ba3788b23da09c0f1f3a3288c  289.00          46.48   \n",
       "2  7c67e1448b00f6e969d365cea6b010ab  139.94          17.79   \n",
       "3  7c67e1448b00f6e969d365cea6b010ab  149.94          23.36   \n",
       "4  4a3ca9315b744ce9f8e9374361493884  230.00          22.25   \n",
       "\n",
       "   payment_sequential  payment_type payment_installments  payment_value  \\\n",
       "0                   1   credit_card                    2         146.87   \n",
       "1                   1   credit_card                    8         335.48   \n",
       "2                   1   credit_card                    7         157.73   \n",
       "3                   1   credit_card                    1         173.30   \n",
       "4                   1   credit_card                    8         252.25   \n",
       "\n",
       "   review_score  \n",
       "0             4  \n",
       "1             5  \n",
       "2             5  \n",
       "3             5  \n",
       "4             5  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 4. merge df with reviews, left merge as not all reviews data have customer_id\n",
    "df=df.merge(reviews, on='order_id')\n",
    "df=df[['customer_id', 'customer_unique_id', 'customer_zip_code_prefix',\n",
    "       'customer_city', 'customer_state', 'order_id', 'order_status',\n",
    "       'order_purchase_timestamp', 'order_approved_at',\n",
    "       'order_delivered_carrier_date', 'order_delivered_customer_date',\n",
    "       'order_estimated_delivery_date', 'product_id',\n",
    "       'seller_id', 'price', 'freight_value', 'payment_sequential',\n",
    "       'payment_type', 'payment_installments', 'payment_value',\n",
    "       'review_score']]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "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",
       "      <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>...</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "      <th>seller_id</th>\n",
       "      <th>price</th>\n",
       "      <th>freight_value</th>\n",
       "      <th>payment_sequential</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>payment_installments</th>\n",
       "      <th>payment_value</th>\n",
       "      <th>review_score</th>\n",
       "      <th>product_category_name</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",
       "      <td>00e7ee1b050b8499577073aeb2a297a1</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-16 15:05:35</td>\n",
       "      <td>2017-05-16 15:22:12</td>\n",
       "      <td>2017-05-23 10:47:57</td>\n",
       "      <td>...</td>\n",
       "      <td>2017-06-05</td>\n",
       "      <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "      <td>124.99</td>\n",
       "      <td>21.88</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>2</td>\n",
       "      <td>146.87</td>\n",
       "      <td>4</td>\n",
       "      <td>moveis_escritorio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8912fc0c3bbf1e2fbf35819e21706718</td>\n",
       "      <td>9eae34bbd3a474ec5d07949ca7de67c0</td>\n",
       "      <td>68030</td>\n",
       "      <td>santarem</td>\n",
       "      <td>PA</td>\n",
       "      <td>c1d2b34febe9cd269e378117d6681172</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-09 00:50:13</td>\n",
       "      <td>2017-11-10 00:47:48</td>\n",
       "      <td>2017-11-22 01:43:37</td>\n",
       "      <td>...</td>\n",
       "      <td>2017-12-19</td>\n",
       "      <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "      <td>112.99</td>\n",
       "      <td>24.90</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>275.79</td>\n",
       "      <td>1</td>\n",
       "      <td>moveis_escritorio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8912fc0c3bbf1e2fbf35819e21706718</td>\n",
       "      <td>9eae34bbd3a474ec5d07949ca7de67c0</td>\n",
       "      <td>68030</td>\n",
       "      <td>santarem</td>\n",
       "      <td>PA</td>\n",
       "      <td>c1d2b34febe9cd269e378117d6681172</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-09 00:50:13</td>\n",
       "      <td>2017-11-10 00:47:48</td>\n",
       "      <td>2017-11-22 01:43:37</td>\n",
       "      <td>...</td>\n",
       "      <td>2017-12-19</td>\n",
       "      <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "      <td>112.99</td>\n",
       "      <td>24.90</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>275.79</td>\n",
       "      <td>1</td>\n",
       "      <td>moveis_escritorio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f0ac8e5a239118859b1734e1087cbb1f</td>\n",
       "      <td>3c799d181c34d51f6d44bbbc563024db</td>\n",
       "      <td>92480</td>\n",
       "      <td>nova santa rita</td>\n",
       "      <td>RS</td>\n",
       "      <td>b1a5d5365d330d10485e0203d54ab9e8</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-07 20:11:26</td>\n",
       "      <td>2017-05-08 22:22:56</td>\n",
       "      <td>2017-05-19 20:16:31</td>\n",
       "      <td>...</td>\n",
       "      <td>2017-06-12</td>\n",
       "      <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "      <td>124.99</td>\n",
       "      <td>15.62</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>7</td>\n",
       "      <td>140.61</td>\n",
       "      <td>3</td>\n",
       "      <td>moveis_escritorio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6bc8d08963a135220ed6c6d098831f84</td>\n",
       "      <td>23397e992b09769faf5e66f9e171a241</td>\n",
       "      <td>25931</td>\n",
       "      <td>mage</td>\n",
       "      <td>RJ</td>\n",
       "      <td>2e604b3614664aa66867856dba7e61b7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-03 19:45:40</td>\n",
       "      <td>2018-02-04 22:29:19</td>\n",
       "      <td>2018-02-19 18:21:47</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-03-22</td>\n",
       "      <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "      <td>106.99</td>\n",
       "      <td>30.59</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>10</td>\n",
       "      <td>137.58</td>\n",
       "      <td>4</td>\n",
       "      <td>moveis_escritorio</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        customer_id                customer_unique_id  \\\n",
       "0  06b8999e2fba1a1fbc88172c00ba8bc7  861eff4711a542e4b93843c6dd7febb0   \n",
       "1  8912fc0c3bbf1e2fbf35819e21706718  9eae34bbd3a474ec5d07949ca7de67c0   \n",
       "2  8912fc0c3bbf1e2fbf35819e21706718  9eae34bbd3a474ec5d07949ca7de67c0   \n",
       "3  f0ac8e5a239118859b1734e1087cbb1f  3c799d181c34d51f6d44bbbc563024db   \n",
       "4  6bc8d08963a135220ed6c6d098831f84  23397e992b09769faf5e66f9e171a241   \n",
       "\n",
       "   customer_zip_code_prefix    customer_city customer_state  \\\n",
       "0                     14409           franca             SP   \n",
       "1                     68030         santarem             PA   \n",
       "2                     68030         santarem             PA   \n",
       "3                     92480  nova santa rita             RS   \n",
       "4                     25931             mage             RJ   \n",
       "\n",
       "                           order_id order_status order_purchase_timestamp  \\\n",
       "0  00e7ee1b050b8499577073aeb2a297a1    delivered      2017-05-16 15:05:35   \n",
       "1  c1d2b34febe9cd269e378117d6681172    delivered      2017-11-09 00:50:13   \n",
       "2  c1d2b34febe9cd269e378117d6681172    delivered      2017-11-09 00:50:13   \n",
       "3  b1a5d5365d330d10485e0203d54ab9e8    delivered      2017-05-07 20:11:26   \n",
       "4  2e604b3614664aa66867856dba7e61b7    delivered      2018-02-03 19:45:40   \n",
       "\n",
       "    order_approved_at order_delivered_carrier_date          ...            \\\n",
       "0 2017-05-16 15:22:12          2017-05-23 10:47:57          ...             \n",
       "1 2017-11-10 00:47:48          2017-11-22 01:43:37          ...             \n",
       "2 2017-11-10 00:47:48          2017-11-22 01:43:37          ...             \n",
       "3 2017-05-08 22:22:56          2017-05-19 20:16:31          ...             \n",
       "4 2018-02-04 22:29:19          2018-02-19 18:21:47          ...             \n",
       "\n",
       "  order_estimated_delivery_date                         seller_id   price  \\\n",
       "0                    2017-06-05  7c67e1448b00f6e969d365cea6b010ab  124.99   \n",
       "1                    2017-12-19  7c67e1448b00f6e969d365cea6b010ab  112.99   \n",
       "2                    2017-12-19  7c67e1448b00f6e969d365cea6b010ab  112.99   \n",
       "3                    2017-06-12  7c67e1448b00f6e969d365cea6b010ab  124.99   \n",
       "4                    2018-03-22  7c67e1448b00f6e969d365cea6b010ab  106.99   \n",
       "\n",
       "   freight_value  payment_sequential  payment_type payment_installments  \\\n",
       "0          21.88                   1   credit_card                    2   \n",
       "1          24.90                   1   credit_card                    1   \n",
       "2          24.90                   1   credit_card                    1   \n",
       "3          15.62                   1   credit_card                    7   \n",
       "4          30.59                   1   credit_card                   10   \n",
       "\n",
       "   payment_value  review_score  product_category_name  \n",
       "0         146.87             4      moveis_escritorio  \n",
       "1         275.79             1      moveis_escritorio  \n",
       "2         275.79             1      moveis_escritorio  \n",
       "3         140.61             3      moveis_escritorio  \n",
       "4         137.58             4      moveis_escritorio  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 5. merge with products\n",
    "df=df.merge(products, on='product_id')\n",
    "df=df[['customer_id', 'customer_unique_id', 'customer_zip_code_prefix',\n",
    "       'customer_city', 'customer_state', 'order_id', 'order_status',\n",
    "       'order_purchase_timestamp', 'order_approved_at',\n",
    "       'order_delivered_carrier_date', 'order_delivered_customer_date',\n",
    "       'order_estimated_delivery_date', 'seller_id', 'price', 'freight_value', 'payment_sequential',\n",
    "       'payment_type', 'payment_installments', 'payment_value', 'review_score',\n",
    "       'product_category_name']]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "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",
       "      <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>...</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "      <th>seller_id</th>\n",
       "      <th>price</th>\n",
       "      <th>freight_value</th>\n",
       "      <th>payment_sequential</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>payment_installments</th>\n",
       "      <th>payment_value</th>\n",
       "      <th>review_score</th>\n",
       "      <th>product_category_name_english</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",
       "      <td>00e7ee1b050b8499577073aeb2a297a1</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-16 15:05:35</td>\n",
       "      <td>2017-05-16 15:22:12</td>\n",
       "      <td>2017-05-23 10:47:57</td>\n",
       "      <td>...</td>\n",
       "      <td>2017-06-05</td>\n",
       "      <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "      <td>124.99</td>\n",
       "      <td>21.88</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>2</td>\n",
       "      <td>146.87</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8912fc0c3bbf1e2fbf35819e21706718</td>\n",
       "      <td>9eae34bbd3a474ec5d07949ca7de67c0</td>\n",
       "      <td>68030</td>\n",
       "      <td>santarem</td>\n",
       "      <td>PA</td>\n",
       "      <td>c1d2b34febe9cd269e378117d6681172</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-09 00:50:13</td>\n",
       "      <td>2017-11-10 00:47:48</td>\n",
       "      <td>2017-11-22 01:43:37</td>\n",
       "      <td>...</td>\n",
       "      <td>2017-12-19</td>\n",
       "      <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "      <td>112.99</td>\n",
       "      <td>24.90</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>275.79</td>\n",
       "      <td>1</td>\n",
       "      <td>office_furniture</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8912fc0c3bbf1e2fbf35819e21706718</td>\n",
       "      <td>9eae34bbd3a474ec5d07949ca7de67c0</td>\n",
       "      <td>68030</td>\n",
       "      <td>santarem</td>\n",
       "      <td>PA</td>\n",
       "      <td>c1d2b34febe9cd269e378117d6681172</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-09 00:50:13</td>\n",
       "      <td>2017-11-10 00:47:48</td>\n",
       "      <td>2017-11-22 01:43:37</td>\n",
       "      <td>...</td>\n",
       "      <td>2017-12-19</td>\n",
       "      <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "      <td>112.99</td>\n",
       "      <td>24.90</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>275.79</td>\n",
       "      <td>1</td>\n",
       "      <td>office_furniture</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f0ac8e5a239118859b1734e1087cbb1f</td>\n",
       "      <td>3c799d181c34d51f6d44bbbc563024db</td>\n",
       "      <td>92480</td>\n",
       "      <td>nova santa rita</td>\n",
       "      <td>RS</td>\n",
       "      <td>b1a5d5365d330d10485e0203d54ab9e8</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-07 20:11:26</td>\n",
       "      <td>2017-05-08 22:22:56</td>\n",
       "      <td>2017-05-19 20:16:31</td>\n",
       "      <td>...</td>\n",
       "      <td>2017-06-12</td>\n",
       "      <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "      <td>124.99</td>\n",
       "      <td>15.62</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>7</td>\n",
       "      <td>140.61</td>\n",
       "      <td>3</td>\n",
       "      <td>office_furniture</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6bc8d08963a135220ed6c6d098831f84</td>\n",
       "      <td>23397e992b09769faf5e66f9e171a241</td>\n",
       "      <td>25931</td>\n",
       "      <td>mage</td>\n",
       "      <td>RJ</td>\n",
       "      <td>2e604b3614664aa66867856dba7e61b7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-03 19:45:40</td>\n",
       "      <td>2018-02-04 22:29:19</td>\n",
       "      <td>2018-02-19 18:21:47</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-03-22</td>\n",
       "      <td>7c67e1448b00f6e969d365cea6b010ab</td>\n",
       "      <td>106.99</td>\n",
       "      <td>30.59</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>10</td>\n",
       "      <td>137.58</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        customer_id                customer_unique_id  \\\n",
       "0  06b8999e2fba1a1fbc88172c00ba8bc7  861eff4711a542e4b93843c6dd7febb0   \n",
       "1  8912fc0c3bbf1e2fbf35819e21706718  9eae34bbd3a474ec5d07949ca7de67c0   \n",
       "2  8912fc0c3bbf1e2fbf35819e21706718  9eae34bbd3a474ec5d07949ca7de67c0   \n",
       "3  f0ac8e5a239118859b1734e1087cbb1f  3c799d181c34d51f6d44bbbc563024db   \n",
       "4  6bc8d08963a135220ed6c6d098831f84  23397e992b09769faf5e66f9e171a241   \n",
       "\n",
       "   customer_zip_code_prefix    customer_city customer_state  \\\n",
       "0                     14409           franca             SP   \n",
       "1                     68030         santarem             PA   \n",
       "2                     68030         santarem             PA   \n",
       "3                     92480  nova santa rita             RS   \n",
       "4                     25931             mage             RJ   \n",
       "\n",
       "                           order_id order_status order_purchase_timestamp  \\\n",
       "0  00e7ee1b050b8499577073aeb2a297a1    delivered      2017-05-16 15:05:35   \n",
       "1  c1d2b34febe9cd269e378117d6681172    delivered      2017-11-09 00:50:13   \n",
       "2  c1d2b34febe9cd269e378117d6681172    delivered      2017-11-09 00:50:13   \n",
       "3  b1a5d5365d330d10485e0203d54ab9e8    delivered      2017-05-07 20:11:26   \n",
       "4  2e604b3614664aa66867856dba7e61b7    delivered      2018-02-03 19:45:40   \n",
       "\n",
       "    order_approved_at order_delivered_carrier_date  \\\n",
       "0 2017-05-16 15:22:12          2017-05-23 10:47:57   \n",
       "1 2017-11-10 00:47:48          2017-11-22 01:43:37   \n",
       "2 2017-11-10 00:47:48          2017-11-22 01:43:37   \n",
       "3 2017-05-08 22:22:56          2017-05-19 20:16:31   \n",
       "4 2018-02-04 22:29:19          2018-02-19 18:21:47   \n",
       "\n",
       "               ...               order_estimated_delivery_date  \\\n",
       "0              ...                                  2017-06-05   \n",
       "1              ...                                  2017-12-19   \n",
       "2              ...                                  2017-12-19   \n",
       "3              ...                                  2017-06-12   \n",
       "4              ...                                  2018-03-22   \n",
       "\n",
       "                          seller_id   price  freight_value  \\\n",
       "0  7c67e1448b00f6e969d365cea6b010ab  124.99          21.88   \n",
       "1  7c67e1448b00f6e969d365cea6b010ab  112.99          24.90   \n",
       "2  7c67e1448b00f6e969d365cea6b010ab  112.99          24.90   \n",
       "3  7c67e1448b00f6e969d365cea6b010ab  124.99          15.62   \n",
       "4  7c67e1448b00f6e969d365cea6b010ab  106.99          30.59   \n",
       "\n",
       "   payment_sequential  payment_type payment_installments  payment_value  \\\n",
       "0                   1   credit_card                    2         146.87   \n",
       "1                   1   credit_card                    1         275.79   \n",
       "2                   1   credit_card                    1         275.79   \n",
       "3                   1   credit_card                    7         140.61   \n",
       "4                   1   credit_card                   10         137.58   \n",
       "\n",
       "   review_score  product_category_name_english  \n",
       "0             4               office_furniture  \n",
       "1             1               office_furniture  \n",
       "2             1               office_furniture  \n",
       "3             3               office_furniture  \n",
       "4             4               office_furniture  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 6. merge with product_category to have prod cat english\n",
    "df=df.merge(product_category, on='product_category_name')\n",
    "#keep only englsh prod cat\n",
    "df=df[['customer_id', 'customer_unique_id', 'customer_zip_code_prefix',\n",
    "       'customer_city', 'customer_state', 'order_id', 'order_status',\n",
    "       'order_purchase_timestamp', 'order_approved_at',\n",
    "       'order_delivered_carrier_date', 'order_delivered_customer_date',\n",
    "       'order_estimated_delivery_date', 'seller_id', 'price', 'freight_value', 'payment_sequential',\n",
    "       'payment_type', 'payment_installments', 'payment_value', 'review_score','product_category_name_english']]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "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",
       "      <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>...</th>\n",
       "      <th>freight_value</th>\n",
       "      <th>payment_sequential</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>payment_installments</th>\n",
       "      <th>payment_value</th>\n",
       "      <th>review_score</th>\n",
       "      <th>product_category_name_english</th>\n",
       "      <th>seller_zip_code_prefix</th>\n",
       "      <th>seller_city</th>\n",
       "      <th>seller_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",
       "      <td>00e7ee1b050b8499577073aeb2a297a1</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-16 15:05:35</td>\n",
       "      <td>2017-05-16 15:22:12</td>\n",
       "      <td>2017-05-23 10:47:57</td>\n",
       "      <td>...</td>\n",
       "      <td>21.88</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>2</td>\n",
       "      <td>146.87</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8912fc0c3bbf1e2fbf35819e21706718</td>\n",
       "      <td>9eae34bbd3a474ec5d07949ca7de67c0</td>\n",
       "      <td>68030</td>\n",
       "      <td>santarem</td>\n",
       "      <td>PA</td>\n",
       "      <td>c1d2b34febe9cd269e378117d6681172</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-09 00:50:13</td>\n",
       "      <td>2017-11-10 00:47:48</td>\n",
       "      <td>2017-11-22 01:43:37</td>\n",
       "      <td>...</td>\n",
       "      <td>24.90</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>275.79</td>\n",
       "      <td>1</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8912fc0c3bbf1e2fbf35819e21706718</td>\n",
       "      <td>9eae34bbd3a474ec5d07949ca7de67c0</td>\n",
       "      <td>68030</td>\n",
       "      <td>santarem</td>\n",
       "      <td>PA</td>\n",
       "      <td>c1d2b34febe9cd269e378117d6681172</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-09 00:50:13</td>\n",
       "      <td>2017-11-10 00:47:48</td>\n",
       "      <td>2017-11-22 01:43:37</td>\n",
       "      <td>...</td>\n",
       "      <td>24.90</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>275.79</td>\n",
       "      <td>1</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f0ac8e5a239118859b1734e1087cbb1f</td>\n",
       "      <td>3c799d181c34d51f6d44bbbc563024db</td>\n",
       "      <td>92480</td>\n",
       "      <td>nova santa rita</td>\n",
       "      <td>RS</td>\n",
       "      <td>b1a5d5365d330d10485e0203d54ab9e8</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-07 20:11:26</td>\n",
       "      <td>2017-05-08 22:22:56</td>\n",
       "      <td>2017-05-19 20:16:31</td>\n",
       "      <td>...</td>\n",
       "      <td>15.62</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>7</td>\n",
       "      <td>140.61</td>\n",
       "      <td>3</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6bc8d08963a135220ed6c6d098831f84</td>\n",
       "      <td>23397e992b09769faf5e66f9e171a241</td>\n",
       "      <td>25931</td>\n",
       "      <td>mage</td>\n",
       "      <td>RJ</td>\n",
       "      <td>2e604b3614664aa66867856dba7e61b7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-03 19:45:40</td>\n",
       "      <td>2018-02-04 22:29:19</td>\n",
       "      <td>2018-02-19 18:21:47</td>\n",
       "      <td>...</td>\n",
       "      <td>30.59</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>10</td>\n",
       "      <td>137.58</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        customer_id                customer_unique_id  \\\n",
       "0  06b8999e2fba1a1fbc88172c00ba8bc7  861eff4711a542e4b93843c6dd7febb0   \n",
       "1  8912fc0c3bbf1e2fbf35819e21706718  9eae34bbd3a474ec5d07949ca7de67c0   \n",
       "2  8912fc0c3bbf1e2fbf35819e21706718  9eae34bbd3a474ec5d07949ca7de67c0   \n",
       "3  f0ac8e5a239118859b1734e1087cbb1f  3c799d181c34d51f6d44bbbc563024db   \n",
       "4  6bc8d08963a135220ed6c6d098831f84  23397e992b09769faf5e66f9e171a241   \n",
       "\n",
       "   customer_zip_code_prefix    customer_city customer_state  \\\n",
       "0                     14409           franca             SP   \n",
       "1                     68030         santarem             PA   \n",
       "2                     68030         santarem             PA   \n",
       "3                     92480  nova santa rita             RS   \n",
       "4                     25931             mage             RJ   \n",
       "\n",
       "                           order_id order_status order_purchase_timestamp  \\\n",
       "0  00e7ee1b050b8499577073aeb2a297a1    delivered      2017-05-16 15:05:35   \n",
       "1  c1d2b34febe9cd269e378117d6681172    delivered      2017-11-09 00:50:13   \n",
       "2  c1d2b34febe9cd269e378117d6681172    delivered      2017-11-09 00:50:13   \n",
       "3  b1a5d5365d330d10485e0203d54ab9e8    delivered      2017-05-07 20:11:26   \n",
       "4  2e604b3614664aa66867856dba7e61b7    delivered      2018-02-03 19:45:40   \n",
       "\n",
       "    order_approved_at order_delivered_carrier_date      ...       \\\n",
       "0 2017-05-16 15:22:12          2017-05-23 10:47:57      ...        \n",
       "1 2017-11-10 00:47:48          2017-11-22 01:43:37      ...        \n",
       "2 2017-11-10 00:47:48          2017-11-22 01:43:37      ...        \n",
       "3 2017-05-08 22:22:56          2017-05-19 20:16:31      ...        \n",
       "4 2018-02-04 22:29:19          2018-02-19 18:21:47      ...        \n",
       "\n",
       "  freight_value payment_sequential payment_type  payment_installments  \\\n",
       "0         21.88                  1  credit_card                     2   \n",
       "1         24.90                  1  credit_card                     1   \n",
       "2         24.90                  1  credit_card                     1   \n",
       "3         15.62                  1  credit_card                     7   \n",
       "4         30.59                  1  credit_card                    10   \n",
       "\n",
       "   payment_value  review_score product_category_name_english  \\\n",
       "0         146.87             4              office_furniture   \n",
       "1         275.79             1              office_furniture   \n",
       "2         275.79             1              office_furniture   \n",
       "3         140.61             3              office_furniture   \n",
       "4         137.58             4              office_furniture   \n",
       "\n",
       "   seller_zip_code_prefix      seller_city  seller_state  \n",
       "0                    8577  itaquaquecetuba            SP  \n",
       "1                    8577  itaquaquecetuba            SP  \n",
       "2                    8577  itaquaquecetuba            SP  \n",
       "3                    8577  itaquaquecetuba            SP  \n",
       "4                    8577  itaquaquecetuba            SP  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 7. merge df with seller geo data\n",
    "df=df.merge(seller_geo, on='seller_id')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Assessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "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",
       "      <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>...</th>\n",
       "      <th>freight_value</th>\n",
       "      <th>payment_sequential</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>payment_installments</th>\n",
       "      <th>payment_value</th>\n",
       "      <th>review_score</th>\n",
       "      <th>product_category_name_english</th>\n",
       "      <th>seller_zip_code_prefix</th>\n",
       "      <th>seller_city</th>\n",
       "      <th>seller_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",
       "      <td>00e7ee1b050b8499577073aeb2a297a1</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-16 15:05:35</td>\n",
       "      <td>2017-05-16 15:22:12</td>\n",
       "      <td>2017-05-23 10:47:57</td>\n",
       "      <td>...</td>\n",
       "      <td>21.88</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>2</td>\n",
       "      <td>146.87</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8912fc0c3bbf1e2fbf35819e21706718</td>\n",
       "      <td>9eae34bbd3a474ec5d07949ca7de67c0</td>\n",
       "      <td>68030</td>\n",
       "      <td>santarem</td>\n",
       "      <td>PA</td>\n",
       "      <td>c1d2b34febe9cd269e378117d6681172</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-09 00:50:13</td>\n",
       "      <td>2017-11-10 00:47:48</td>\n",
       "      <td>2017-11-22 01:43:37</td>\n",
       "      <td>...</td>\n",
       "      <td>24.90</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>275.79</td>\n",
       "      <td>1</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8912fc0c3bbf1e2fbf35819e21706718</td>\n",
       "      <td>9eae34bbd3a474ec5d07949ca7de67c0</td>\n",
       "      <td>68030</td>\n",
       "      <td>santarem</td>\n",
       "      <td>PA</td>\n",
       "      <td>c1d2b34febe9cd269e378117d6681172</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-09 00:50:13</td>\n",
       "      <td>2017-11-10 00:47:48</td>\n",
       "      <td>2017-11-22 01:43:37</td>\n",
       "      <td>...</td>\n",
       "      <td>24.90</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>275.79</td>\n",
       "      <td>1</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f0ac8e5a239118859b1734e1087cbb1f</td>\n",
       "      <td>3c799d181c34d51f6d44bbbc563024db</td>\n",
       "      <td>92480</td>\n",
       "      <td>nova santa rita</td>\n",
       "      <td>RS</td>\n",
       "      <td>b1a5d5365d330d10485e0203d54ab9e8</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-07 20:11:26</td>\n",
       "      <td>2017-05-08 22:22:56</td>\n",
       "      <td>2017-05-19 20:16:31</td>\n",
       "      <td>...</td>\n",
       "      <td>15.62</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>7</td>\n",
       "      <td>140.61</td>\n",
       "      <td>3</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6bc8d08963a135220ed6c6d098831f84</td>\n",
       "      <td>23397e992b09769faf5e66f9e171a241</td>\n",
       "      <td>25931</td>\n",
       "      <td>mage</td>\n",
       "      <td>RJ</td>\n",
       "      <td>2e604b3614664aa66867856dba7e61b7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-03 19:45:40</td>\n",
       "      <td>2018-02-04 22:29:19</td>\n",
       "      <td>2018-02-19 18:21:47</td>\n",
       "      <td>...</td>\n",
       "      <td>30.59</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>10</td>\n",
       "      <td>137.58</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>fd3a0b1bd209f0e7d420c9c3d1127613</td>\n",
       "      <td>567ab47ca4deb92d46dbf54dce07d0a7</td>\n",
       "      <td>88460</td>\n",
       "      <td>angelina</td>\n",
       "      <td>SC</td>\n",
       "      <td>574fe1739f65af76badd0999db300b4f</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-03-23 15:10:17</td>\n",
       "      <td>2017-03-23 15:25:11</td>\n",
       "      <td>2017-03-28 18:23:51</td>\n",
       "      <td>...</td>\n",
       "      <td>15.06</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>5</td>\n",
       "      <td>142.05</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>fbd40c083aa8cddebb5265b2ba6aaf2e</td>\n",
       "      <td>f40ab89b622248b7ca125af4b486b887</td>\n",
       "      <td>32341</td>\n",
       "      <td>contagem</td>\n",
       "      <td>MG</td>\n",
       "      <td>e0b26f14d2bcc710bb02f77a4628763b</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-16 10:00:49</td>\n",
       "      <td>2017-05-17 03:45:27</td>\n",
       "      <td>2017-05-23 10:35:48</td>\n",
       "      <td>...</td>\n",
       "      <td>30.71</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>155.70</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10558ef4afea173bfb5e2cbe3d5b0bb5</td>\n",
       "      <td>749943913a9851a39c9baf51877fbab6</td>\n",
       "      <td>78134</td>\n",
       "      <td>varzea grande</td>\n",
       "      <td>MT</td>\n",
       "      <td>eaae5bd20fb15d85aa673d9b7c0e8ca5</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-03-18 23:04:36</td>\n",
       "      <td>2017-03-18 23:04:36</td>\n",
       "      <td>2017-03-28 06:29:47</td>\n",
       "      <td>...</td>\n",
       "      <td>21.34</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>2</td>\n",
       "      <td>148.33</td>\n",
       "      <td>2</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1c37c0f0cd1d88d46d9fc9494762abbd</td>\n",
       "      <td>432ecfa8b7b7ad2663c7abed0dc83c51</td>\n",
       "      <td>31270</td>\n",
       "      <td>belo horizonte</td>\n",
       "      <td>MG</td>\n",
       "      <td>c4e2bd2043fbd75b325a47adfabf0d77</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-03-15 07:45:10</td>\n",
       "      <td>2018-03-15 07:55:24</td>\n",
       "      <td>2018-03-27 15:58:41</td>\n",
       "      <td>...</td>\n",
       "      <td>33.08</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>3</td>\n",
       "      <td>150.07</td>\n",
       "      <td>3</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>20a452f528d487411fd7d3ebda1d0f20</td>\n",
       "      <td>31318a0597cd9d50ce4cfd03c80fe780</td>\n",
       "      <td>37540</td>\n",
       "      <td>santa rita do sapucai</td>\n",
       "      <td>MG</td>\n",
       "      <td>1c7fe02ac4c7be50c59afb295cf85b89</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-01-26 13:22:09</td>\n",
       "      <td>2018-01-30 03:47:31</td>\n",
       "      <td>2018-02-09 13:17:44</td>\n",
       "      <td>...</td>\n",
       "      <td>21.76</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>257.50</td>\n",
       "      <td>2</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>20a452f528d487411fd7d3ebda1d0f20</td>\n",
       "      <td>31318a0597cd9d50ce4cfd03c80fe780</td>\n",
       "      <td>37540</td>\n",
       "      <td>santa rita do sapucai</td>\n",
       "      <td>MG</td>\n",
       "      <td>1c7fe02ac4c7be50c59afb295cf85b89</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-01-26 13:22:09</td>\n",
       "      <td>2018-01-30 03:47:31</td>\n",
       "      <td>2018-02-09 13:17:44</td>\n",
       "      <td>...</td>\n",
       "      <td>21.76</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>257.50</td>\n",
       "      <td>2</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>62f766c82849282806bf2f62ca86432e</td>\n",
       "      <td>83aca63b2193ea126b287f3ae61528d9</td>\n",
       "      <td>89115</td>\n",
       "      <td>gaspar</td>\n",
       "      <td>SC</td>\n",
       "      <td>5ee63c8c9ae2e5c38791b35932cf4c69</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-09 11:30:18</td>\n",
       "      <td>2017-05-09 11:45:13</td>\n",
       "      <td>2017-05-19 19:32:55</td>\n",
       "      <td>...</td>\n",
       "      <td>15.62</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>6</td>\n",
       "      <td>843.66</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>62f766c82849282806bf2f62ca86432e</td>\n",
       "      <td>83aca63b2193ea126b287f3ae61528d9</td>\n",
       "      <td>89115</td>\n",
       "      <td>gaspar</td>\n",
       "      <td>SC</td>\n",
       "      <td>5ee63c8c9ae2e5c38791b35932cf4c69</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-09 11:30:18</td>\n",
       "      <td>2017-05-09 11:45:13</td>\n",
       "      <td>2017-05-19 19:32:55</td>\n",
       "      <td>...</td>\n",
       "      <td>15.62</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>6</td>\n",
       "      <td>843.66</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>62f766c82849282806bf2f62ca86432e</td>\n",
       "      <td>83aca63b2193ea126b287f3ae61528d9</td>\n",
       "      <td>89115</td>\n",
       "      <td>gaspar</td>\n",
       "      <td>SC</td>\n",
       "      <td>5ee63c8c9ae2e5c38791b35932cf4c69</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-09 11:30:18</td>\n",
       "      <td>2017-05-09 11:45:13</td>\n",
       "      <td>2017-05-19 19:32:55</td>\n",
       "      <td>...</td>\n",
       "      <td>15.62</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>6</td>\n",
       "      <td>843.66</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>62f766c82849282806bf2f62ca86432e</td>\n",
       "      <td>83aca63b2193ea126b287f3ae61528d9</td>\n",
       "      <td>89115</td>\n",
       "      <td>gaspar</td>\n",
       "      <td>SC</td>\n",
       "      <td>5ee63c8c9ae2e5c38791b35932cf4c69</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-09 11:30:18</td>\n",
       "      <td>2017-05-09 11:45:13</td>\n",
       "      <td>2017-05-19 19:32:55</td>\n",
       "      <td>...</td>\n",
       "      <td>15.62</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>6</td>\n",
       "      <td>843.66</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>62f766c82849282806bf2f62ca86432e</td>\n",
       "      <td>83aca63b2193ea126b287f3ae61528d9</td>\n",
       "      <td>89115</td>\n",
       "      <td>gaspar</td>\n",
       "      <td>SC</td>\n",
       "      <td>5ee63c8c9ae2e5c38791b35932cf4c69</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-09 11:30:18</td>\n",
       "      <td>2017-05-09 11:45:13</td>\n",
       "      <td>2017-05-19 19:32:55</td>\n",
       "      <td>...</td>\n",
       "      <td>15.62</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>6</td>\n",
       "      <td>843.66</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>62f766c82849282806bf2f62ca86432e</td>\n",
       "      <td>83aca63b2193ea126b287f3ae61528d9</td>\n",
       "      <td>89115</td>\n",
       "      <td>gaspar</td>\n",
       "      <td>SC</td>\n",
       "      <td>5ee63c8c9ae2e5c38791b35932cf4c69</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-09 11:30:18</td>\n",
       "      <td>2017-05-09 11:45:13</td>\n",
       "      <td>2017-05-19 19:32:55</td>\n",
       "      <td>...</td>\n",
       "      <td>15.62</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>6</td>\n",
       "      <td>843.66</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>d3286ca43aa21423c6045b2806ca8e56</td>\n",
       "      <td>acd02fc85cf6339f896c161af2b6abcd</td>\n",
       "      <td>96083</td>\n",
       "      <td>pelotas</td>\n",
       "      <td>RS</td>\n",
       "      <td>b1cc044d321053348a5e482ca7e6b2e3</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-01-30 19:29:01</td>\n",
       "      <td>2018-01-30 19:54:04</td>\n",
       "      <td>2018-02-09 13:08:10</td>\n",
       "      <td>...</td>\n",
       "      <td>38.59</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>145.58</td>\n",
       "      <td>2</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>c49bec5f74f0413130abdaed8e7621d0</td>\n",
       "      <td>67d6f91d256bdf128fc960a166f413d5</td>\n",
       "      <td>25640</td>\n",
       "      <td>petropolis</td>\n",
       "      <td>RJ</td>\n",
       "      <td>0d831c5aa932379c1cf5b0b1b40b214b</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-10-13 10:36:54</td>\n",
       "      <td>2017-10-13 10:49:47</td>\n",
       "      <td>2017-10-24 18:58:49</td>\n",
       "      <td>...</td>\n",
       "      <td>34.18</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>151.17</td>\n",
       "      <td>3</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0058ebe2dc136d918dd001968cfa5903</td>\n",
       "      <td>9e8993ba2665307b71bd1641c2e1cb3d</td>\n",
       "      <td>39495</td>\n",
       "      <td>montalvania</td>\n",
       "      <td>MG</td>\n",
       "      <td>27ae3ad0516a9b55f2236e24f690e01b</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-02-09 16:19:02</td>\n",
       "      <td>2017-02-09 16:30:12</td>\n",
       "      <td>2017-02-22 03:51:44</td>\n",
       "      <td>...</td>\n",
       "      <td>15.01</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>5</td>\n",
       "      <td>270.00</td>\n",
       "      <td>3</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0058ebe2dc136d918dd001968cfa5903</td>\n",
       "      <td>9e8993ba2665307b71bd1641c2e1cb3d</td>\n",
       "      <td>39495</td>\n",
       "      <td>montalvania</td>\n",
       "      <td>MG</td>\n",
       "      <td>27ae3ad0516a9b55f2236e24f690e01b</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-02-09 16:19:02</td>\n",
       "      <td>2017-02-09 16:30:12</td>\n",
       "      <td>2017-02-22 03:51:44</td>\n",
       "      <td>...</td>\n",
       "      <td>15.01</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>5</td>\n",
       "      <td>270.00</td>\n",
       "      <td>3</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>f91eb13e253823f25cf9a20fc2d08f56</td>\n",
       "      <td>1c91f8d50340522dd6601237ad13afff</td>\n",
       "      <td>78500</td>\n",
       "      <td>colider</td>\n",
       "      <td>MT</td>\n",
       "      <td>be2463c66944514bb175f7a519a72cd7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-07 19:01:48</td>\n",
       "      <td>2018-02-07 19:15:27</td>\n",
       "      <td>2018-02-20 13:59:00</td>\n",
       "      <td>...</td>\n",
       "      <td>43.19</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>160.18</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>8bf4f9552bc2db14d6195bbbb5131941</td>\n",
       "      <td>b428f59c73c1bbeb3584744b4570654b</td>\n",
       "      <td>3728</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "      <td>7ebc5c60f532934ce55b3025203ebe5b</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-06-24 08:59:47</td>\n",
       "      <td>2017-06-27 03:45:30</td>\n",
       "      <td>2017-06-29 17:37:56</td>\n",
       "      <td>...</td>\n",
       "      <td>23.46</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>148.45</td>\n",
       "      <td>2</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>70bc2fe12d0580eb8336285500defe0e</td>\n",
       "      <td>c71792e0cbb396e0a77258f474d971ab</td>\n",
       "      <td>30672</td>\n",
       "      <td>belo horizonte</td>\n",
       "      <td>MG</td>\n",
       "      <td>66573bb355bdba7b6df55fba794b800d</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-10-15 10:30:57</td>\n",
       "      <td>2017-10-15 10:49:15</td>\n",
       "      <td>2017-10-24 22:23:05</td>\n",
       "      <td>...</td>\n",
       "      <td>28.15</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>141.14</td>\n",
       "      <td>5</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>11eee0e62a0b905cf878ff13c8aec4a0</td>\n",
       "      <td>7dd466ce70871be5aba0ea1a43df6021</td>\n",
       "      <td>40230</td>\n",
       "      <td>salvador</td>\n",
       "      <td>BA</td>\n",
       "      <td>ff7400d904161b62b6e830b3988f5cbd</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-03-27 15:37:03</td>\n",
       "      <td>2018-03-27 15:51:53</td>\n",
       "      <td>2018-04-11 01:05:20</td>\n",
       "      <td>...</td>\n",
       "      <td>37.97</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>54.96</td>\n",
       "      <td>2</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>11eee0e62a0b905cf878ff13c8aec4a0</td>\n",
       "      <td>7dd466ce70871be5aba0ea1a43df6021</td>\n",
       "      <td>40230</td>\n",
       "      <td>salvador</td>\n",
       "      <td>BA</td>\n",
       "      <td>ff7400d904161b62b6e830b3988f5cbd</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-03-27 15:37:03</td>\n",
       "      <td>2018-03-27 15:51:53</td>\n",
       "      <td>2018-04-11 01:05:20</td>\n",
       "      <td>...</td>\n",
       "      <td>37.97</td>\n",
       "      <td>2</td>\n",
       "      <td>voucher</td>\n",
       "      <td>1</td>\n",
       "      <td>100.00</td>\n",
       "      <td>2</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>cf4dc8d4be7642c7c05a73188a3d8c1c</td>\n",
       "      <td>35c0fa6f2e6bbb3576e725d22e1066b8</td>\n",
       "      <td>23585</td>\n",
       "      <td>rio de janeiro</td>\n",
       "      <td>RJ</td>\n",
       "      <td>edc9338008f2e9017f842a8bcad3af94</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-04-28 16:55:22</td>\n",
       "      <td>2017-04-28 17:10:12</td>\n",
       "      <td>2017-05-12 10:54:53</td>\n",
       "      <td>...</td>\n",
       "      <td>15.04</td>\n",
       "      <td>1</td>\n",
       "      <td>voucher</td>\n",
       "      <td>1</td>\n",
       "      <td>140.03</td>\n",
       "      <td>5</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>d24a0f00aa18133811d0aa666da75ff6</td>\n",
       "      <td>14803be716853016918954f7ef189abd</td>\n",
       "      <td>29900</td>\n",
       "      <td>linhares</td>\n",
       "      <td>ES</td>\n",
       "      <td>6ee77fd090a49ec9c4e15bd1a94e100e</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-03-23 17:44:48</td>\n",
       "      <td>2017-03-25 02:15:36</td>\n",
       "      <td>2017-04-07 15:15:50</td>\n",
       "      <td>...</td>\n",
       "      <td>16.59</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>143.58</td>\n",
       "      <td>5</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>d1ec48f722227f712bd7c376e194dcc2</td>\n",
       "      <td>5fff3ec8fc85df5fe57190fd9b6543ed</td>\n",
       "      <td>86975</td>\n",
       "      <td>mandaguari</td>\n",
       "      <td>PR</td>\n",
       "      <td>166029aa4dc1bed21f0d50ebdb4ef286</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-04-02 09:09:45</td>\n",
       "      <td>2018-04-02 09:28:31</td>\n",
       "      <td>2018-04-16 14:02:55</td>\n",
       "      <td>...</td>\n",
       "      <td>57.08</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>10</td>\n",
       "      <td>870.35</td>\n",
       "      <td>5</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>d1ec48f722227f712bd7c376e194dcc2</td>\n",
       "      <td>5fff3ec8fc85df5fe57190fd9b6543ed</td>\n",
       "      <td>86975</td>\n",
       "      <td>mandaguari</td>\n",
       "      <td>PR</td>\n",
       "      <td>166029aa4dc1bed21f0d50ebdb4ef286</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-04-02 09:09:45</td>\n",
       "      <td>2018-04-02 09:28:31</td>\n",
       "      <td>2018-04-16 14:02:55</td>\n",
       "      <td>...</td>\n",
       "      <td>57.08</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>10</td>\n",
       "      <td>870.35</td>\n",
       "      <td>5</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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>116551</th>\n",
       "      <td>d8b65951c84554c80270720c9b5f1178</td>\n",
       "      <td>c6c805ba1449708b259b6da9375318ec</td>\n",
       "      <td>13201</td>\n",
       "      <td>jundiai</td>\n",
       "      <td>SP</td>\n",
       "      <td>6bcce39be127575854779e39a50f2059</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-04-28 02:23:24</td>\n",
       "      <td>2018-05-01 05:32:22</td>\n",
       "      <td>2018-05-04 11:35:00</td>\n",
       "      <td>...</td>\n",
       "      <td>11.15</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>56.15</td>\n",
       "      <td>5</td>\n",
       "      <td>diapers_and_hygiene</td>\n",
       "      <td>6380</td>\n",
       "      <td>carapicuiba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116552</th>\n",
       "      <td>62d3c457010afc957d955568318e6d78</td>\n",
       "      <td>7e3b6916cdc9629811177f17f7ab07ce</td>\n",
       "      <td>3118</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "      <td>796033ffde6ba06e0abe387e36ee8fe0</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-08-26 14:17:08</td>\n",
       "      <td>2018-08-26 14:25:15</td>\n",
       "      <td>2018-08-27 07:45:00</td>\n",
       "      <td>...</td>\n",
       "      <td>19.08</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>3</td>\n",
       "      <td>158.97</td>\n",
       "      <td>5</td>\n",
       "      <td>diapers_and_hygiene</td>\n",
       "      <td>89063</td>\n",
       "      <td>blumenau</td>\n",
       "      <td>SC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116553</th>\n",
       "      <td>e924482b6903522795771e672721f5d2</td>\n",
       "      <td>ab4a2b8d2a955029eb49579cbb027994</td>\n",
       "      <td>2403</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "      <td>30d7af26cb5f8577b5c6a0164dc814c5</td>\n",
       "      <td>canceled</td>\n",
       "      <td>2018-07-26 12:45:33</td>\n",
       "      <td>2018-07-26 12:55:25</td>\n",
       "      <td>NaT</td>\n",
       "      <td>...</td>\n",
       "      <td>11.22</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>3</td>\n",
       "      <td>40.02</td>\n",
       "      <td>1</td>\n",
       "      <td>diapers_and_hygiene</td>\n",
       "      <td>1403</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116554</th>\n",
       "      <td>c167ca7e3249df1c0fce76509c0da7bc</td>\n",
       "      <td>c04825f7f2dea64775bba021dd842fdd</td>\n",
       "      <td>44005</td>\n",
       "      <td>feira de santana</td>\n",
       "      <td>BA</td>\n",
       "      <td>d6f7121c5ede8e22d75e57b6e836871f</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-08-16 14:42:48</td>\n",
       "      <td>2018-08-16 14:55:21</td>\n",
       "      <td>2018-08-17 08:24:00</td>\n",
       "      <td>...</td>\n",
       "      <td>22.52</td>\n",
       "      <td>1</td>\n",
       "      <td>debit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>107.42</td>\n",
       "      <td>4</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116555</th>\n",
       "      <td>38278865371cd2796600255e9d1bb872</td>\n",
       "      <td>8092e2240cc0a4ac008c897518f59b01</td>\n",
       "      <td>6023</td>\n",
       "      <td>osasco</td>\n",
       "      <td>SP</td>\n",
       "      <td>618b0e262ba7b2c46a5b349fadbd4996</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-05-19 17:48:44</td>\n",
       "      <td>2018-05-22 09:36:46</td>\n",
       "      <td>2018-05-22 13:46:00</td>\n",
       "      <td>...</td>\n",
       "      <td>7.39</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>57.19</td>\n",
       "      <td>5</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116556</th>\n",
       "      <td>6e888e003d673cdce73296c4b656fefe</td>\n",
       "      <td>4c0ec556021e916e7d469399bf98d09c</td>\n",
       "      <td>13471</td>\n",
       "      <td>americana</td>\n",
       "      <td>SP</td>\n",
       "      <td>549e5c65d1f41cb1c4d307752c3b9eae</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-05-22 14:22:51</td>\n",
       "      <td>2018-05-22 14:34:57</td>\n",
       "      <td>2018-05-23 09:07:00</td>\n",
       "      <td>...</td>\n",
       "      <td>7.39</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>3</td>\n",
       "      <td>57.19</td>\n",
       "      <td>5</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116557</th>\n",
       "      <td>51ff0c33aa4696c23727652d9868dfc5</td>\n",
       "      <td>d10687a929e58de647bfda07ce41713e</td>\n",
       "      <td>66823</td>\n",
       "      <td>belem</td>\n",
       "      <td>PA</td>\n",
       "      <td>0ec7299d65db721d9a6b5c95c2ff494a</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-05-11 16:51:46</td>\n",
       "      <td>2018-05-11 17:08:39</td>\n",
       "      <td>2018-05-14 09:48:00</td>\n",
       "      <td>...</td>\n",
       "      <td>19.04</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>6</td>\n",
       "      <td>68.84</td>\n",
       "      <td>5</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116558</th>\n",
       "      <td>1f763783a93e0c559a0c7dc847b31eef</td>\n",
       "      <td>158da4c494bfc8f41b20366129483924</td>\n",
       "      <td>23030</td>\n",
       "      <td>rio de janeiro</td>\n",
       "      <td>RJ</td>\n",
       "      <td>bd2f31f2548344a9d1f4ea1c03625e93</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-05-17 16:37:22</td>\n",
       "      <td>2018-05-18 00:14:47</td>\n",
       "      <td>2018-05-18 07:13:00</td>\n",
       "      <td>...</td>\n",
       "      <td>15.23</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>2</td>\n",
       "      <td>65.03</td>\n",
       "      <td>3</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116559</th>\n",
       "      <td>4058d823a6156dd8f9a068750310bac5</td>\n",
       "      <td>3381bbc6c746aa5ff869359fb016c9f7</td>\n",
       "      <td>4517</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "      <td>85abe9ebac688730f1c3591c1ca2a167</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-05-31 20:13:28</td>\n",
       "      <td>2018-05-31 20:33:08</td>\n",
       "      <td>2018-06-01 10:01:00</td>\n",
       "      <td>...</td>\n",
       "      <td>7.44</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>3</td>\n",
       "      <td>90.24</td>\n",
       "      <td>5</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116560</th>\n",
       "      <td>ca5fc68d665a2ce5392fccbde3475f72</td>\n",
       "      <td>a914b2be4782207abc6758095ec9e8aa</td>\n",
       "      <td>4130</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "      <td>01787af385123b93ba50c5f05530ee12</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-05-15 17:33:50</td>\n",
       "      <td>2018-05-15 17:54:29</td>\n",
       "      <td>2018-05-16 09:25:00</td>\n",
       "      <td>...</td>\n",
       "      <td>7.39</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>114.38</td>\n",
       "      <td>5</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116561</th>\n",
       "      <td>ca5fc68d665a2ce5392fccbde3475f72</td>\n",
       "      <td>a914b2be4782207abc6758095ec9e8aa</td>\n",
       "      <td>4130</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "      <td>01787af385123b93ba50c5f05530ee12</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-05-15 17:33:50</td>\n",
       "      <td>2018-05-15 17:54:29</td>\n",
       "      <td>2018-05-16 09:25:00</td>\n",
       "      <td>...</td>\n",
       "      <td>7.39</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>114.38</td>\n",
       "      <td>5</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116562</th>\n",
       "      <td>29e227115fb68d196f0e1dab3c40a622</td>\n",
       "      <td>da8accaa0030a75e2e7848ec481a4828</td>\n",
       "      <td>50070</td>\n",
       "      <td>recife</td>\n",
       "      <td>PE</td>\n",
       "      <td>335cf1cb074c8dfcdf59bc86f8255409</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-05-15 09:08:59</td>\n",
       "      <td>2018-05-16 09:33:16</td>\n",
       "      <td>2018-05-16 08:57:00</td>\n",
       "      <td>...</td>\n",
       "      <td>19.04</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>6</td>\n",
       "      <td>68.84</td>\n",
       "      <td>5</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116563</th>\n",
       "      <td>7241aae1af5ffcdb5411a9d23be378ea</td>\n",
       "      <td>0cd7d20504975ef3cd878aaffa80e482</td>\n",
       "      <td>20241</td>\n",
       "      <td>rio de janeiro</td>\n",
       "      <td>RJ</td>\n",
       "      <td>b1e786caeddece12e5efd30580eaaabc</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-04-20 08:04:26</td>\n",
       "      <td>2018-04-24 17:46:25</td>\n",
       "      <td>2018-04-23 12:34:51</td>\n",
       "      <td>...</td>\n",
       "      <td>15.23</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>65.03</td>\n",
       "      <td>5</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116564</th>\n",
       "      <td>2eba59cbdb86a8a535076c9178c8dc3f</td>\n",
       "      <td>2726e5dc94666be0b1fdf08d02eade18</td>\n",
       "      <td>5417</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "      <td>e8dbfb34a57fe34db02546aa50b56f10</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-05-16 15:01:23</td>\n",
       "      <td>2018-05-16 15:19:35</td>\n",
       "      <td>2018-05-18 07:13:00</td>\n",
       "      <td>...</td>\n",
       "      <td>7.39</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>57.19</td>\n",
       "      <td>5</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116565</th>\n",
       "      <td>704524826c3b6ea14cea19ba0c11ec4e</td>\n",
       "      <td>2852637db607bf7d453e51b172b3e7d6</td>\n",
       "      <td>65075</td>\n",
       "      <td>sao luis</td>\n",
       "      <td>MA</td>\n",
       "      <td>215b38acfb4aef3bb0144656bb102421</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-05-18 01:52:29</td>\n",
       "      <td>2018-05-19 03:13:01</td>\n",
       "      <td>2018-05-21 10:39:00</td>\n",
       "      <td>...</td>\n",
       "      <td>22.85</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>72.65</td>\n",
       "      <td>5</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116566</th>\n",
       "      <td>4e54b60d9094da89bfd9e8006ec8bcba</td>\n",
       "      <td>0195c54646cda6452f5e1a0474f3b502</td>\n",
       "      <td>3377</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "      <td>f203b250a442eac108f23c45f8b1efc8</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-05-09 17:18:42</td>\n",
       "      <td>2018-05-11 02:57:42</td>\n",
       "      <td>2018-05-11 10:54:00</td>\n",
       "      <td>...</td>\n",
       "      <td>7.39</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>57.19</td>\n",
       "      <td>5</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116567</th>\n",
       "      <td>324ec4addc12b40f3f4223fc376185fb</td>\n",
       "      <td>0bf96ae17d786534def0b9f7ae788b14</td>\n",
       "      <td>29167</td>\n",
       "      <td>serra</td>\n",
       "      <td>ES</td>\n",
       "      <td>a71ebda57a67e6349ab52539913f78d8</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-07-26 16:24:10</td>\n",
       "      <td>2018-07-26 16:35:10</td>\n",
       "      <td>2018-07-27 10:32:00</td>\n",
       "      <td>...</td>\n",
       "      <td>15.76</td>\n",
       "      <td>1</td>\n",
       "      <td>debit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>110.66</td>\n",
       "      <td>5</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116568</th>\n",
       "      <td>637321037fb8b34323ce7fd8aab4a0f1</td>\n",
       "      <td>07547425c8f7a7086f529f2d3b80d21a</td>\n",
       "      <td>14806</td>\n",
       "      <td>araraquara</td>\n",
       "      <td>SP</td>\n",
       "      <td>91be51c856a90d7efe86cf9d082d6ae3</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-06-12 15:58:03</td>\n",
       "      <td>2018-06-12 16:32:26</td>\n",
       "      <td>2018-06-12 15:44:00</td>\n",
       "      <td>...</td>\n",
       "      <td>19.17</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>18</td>\n",
       "      <td>397.90</td>\n",
       "      <td>1</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116569</th>\n",
       "      <td>d39cfc95a6ba2cbd9cfa6bb862fd8d77</td>\n",
       "      <td>aad1c7f54ec632ddf6ac8b080b27cd27</td>\n",
       "      <td>88333</td>\n",
       "      <td>balneario camboriu</td>\n",
       "      <td>SC</td>\n",
       "      <td>aa4bd1aa4ff7249ea156343f704478a0</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-08-18 09:47:18</td>\n",
       "      <td>2018-08-18 09:55:14</td>\n",
       "      <td>2018-08-20 08:49:00</td>\n",
       "      <td>...</td>\n",
       "      <td>18.69</td>\n",
       "      <td>1</td>\n",
       "      <td>debit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>103.59</td>\n",
       "      <td>4</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116570</th>\n",
       "      <td>ac54eabad353ecdbd32b0d554d99f411</td>\n",
       "      <td>2f521a0cfb4cc68e6ff29a6371d9d850</td>\n",
       "      <td>4350</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "      <td>c9e7721790d06c21ff6fcd2dee7a2f73</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-07-04 20:23:40</td>\n",
       "      <td>2018-07-05 16:24:41</td>\n",
       "      <td>2018-07-05 08:39:00</td>\n",
       "      <td>...</td>\n",
       "      <td>8.05</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>120.95</td>\n",
       "      <td>5</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116571</th>\n",
       "      <td>c8371460f35fcbaec011347c9e84b020</td>\n",
       "      <td>956431f32e0be7439ae229ff4984bc60</td>\n",
       "      <td>22745</td>\n",
       "      <td>rio de janeiro</td>\n",
       "      <td>RJ</td>\n",
       "      <td>b36511d6ed0cbc11beb1ab142ab3021c</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-08-14 14:21:42</td>\n",
       "      <td>2018-08-14 14:35:08</td>\n",
       "      <td>2018-08-15 06:55:00</td>\n",
       "      <td>...</td>\n",
       "      <td>17.09</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>145.09</td>\n",
       "      <td>5</td>\n",
       "      <td>cine_photo</td>\n",
       "      <td>9041</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116572</th>\n",
       "      <td>520ac67588ffaaf2c60691f72b4a7dcc</td>\n",
       "      <td>d931428aff71cf37ebd00c5808ea9aba</td>\n",
       "      <td>92320</td>\n",
       "      <td>canoas</td>\n",
       "      <td>RS</td>\n",
       "      <td>a79bd7af35a4fffee2b23fded963b4ea</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-04-21 20:16:02</td>\n",
       "      <td>2017-04-21 20:25:22</td>\n",
       "      <td>2017-04-28 14:29:17</td>\n",
       "      <td>...</td>\n",
       "      <td>16.05</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>41.04</td>\n",
       "      <td>4</td>\n",
       "      <td>fashion_sport</td>\n",
       "      <td>6038</td>\n",
       "      <td>osasco</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116573</th>\n",
       "      <td>1a655f9c2a5fd2c3e77940ffdc848989</td>\n",
       "      <td>5408b524d4f3a4ac4e9b7b52d993fae7</td>\n",
       "      <td>95940</td>\n",
       "      <td>arroio do meio</td>\n",
       "      <td>RS</td>\n",
       "      <td>57edfe6486364c7777241f5ab5902715</td>\n",
       "      <td>processing</td>\n",
       "      <td>2017-06-13 10:51:54</td>\n",
       "      <td>2017-06-13 11:03:07</td>\n",
       "      <td>NaT</td>\n",
       "      <td>...</td>\n",
       "      <td>15.10</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>4</td>\n",
       "      <td>40.09</td>\n",
       "      <td>1</td>\n",
       "      <td>fashion_sport</td>\n",
       "      <td>6038</td>\n",
       "      <td>osasco</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116574</th>\n",
       "      <td>ff09fd7b29e7488a8d8a20badcd8befe</td>\n",
       "      <td>8c21dd8c37144807c601f99f2a209dfb</td>\n",
       "      <td>72587</td>\n",
       "      <td>brasilia</td>\n",
       "      <td>DF</td>\n",
       "      <td>157de1652c032429a625d034be79eeeb</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-10 09:00:07</td>\n",
       "      <td>2017-05-10 09:10:18</td>\n",
       "      <td>2017-05-15 10:06:54</td>\n",
       "      <td>...</td>\n",
       "      <td>14.10</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>3</td>\n",
       "      <td>39.09</td>\n",
       "      <td>5</td>\n",
       "      <td>fashion_sport</td>\n",
       "      <td>6038</td>\n",
       "      <td>osasco</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116575</th>\n",
       "      <td>ff09fd7b29e7488a8d8a20badcd8befe</td>\n",
       "      <td>8c21dd8c37144807c601f99f2a209dfb</td>\n",
       "      <td>72587</td>\n",
       "      <td>brasilia</td>\n",
       "      <td>DF</td>\n",
       "      <td>157de1652c032429a625d034be79eeeb</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-10 09:00:07</td>\n",
       "      <td>2017-05-10 09:10:18</td>\n",
       "      <td>2017-05-15 10:06:54</td>\n",
       "      <td>...</td>\n",
       "      <td>14.10</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>3</td>\n",
       "      <td>39.09</td>\n",
       "      <td>5</td>\n",
       "      <td>fashion_sport</td>\n",
       "      <td>6038</td>\n",
       "      <td>osasco</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116576</th>\n",
       "      <td>ce2172509c4149d65212484eb761bc37</td>\n",
       "      <td>c5da6424f1f5decedef8672a1a0103e1</td>\n",
       "      <td>88350</td>\n",
       "      <td>brusque</td>\n",
       "      <td>SC</td>\n",
       "      <td>edcf1e1eeb52381be9388c90152be52d</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-08-21 11:29:05</td>\n",
       "      <td>2018-08-21 11:50:47</td>\n",
       "      <td>2018-08-21 15:37:00</td>\n",
       "      <td>...</td>\n",
       "      <td>24.83</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>5</td>\n",
       "      <td>314.32</td>\n",
       "      <td>5</td>\n",
       "      <td>arts_and_craftmanship</td>\n",
       "      <td>95910</td>\n",
       "      <td>lajeado</td>\n",
       "      <td>RS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116577</th>\n",
       "      <td>ee1cfdc92e449920e25d3ca4ab4da4f6</td>\n",
       "      <td>8d80e477643ec569b24592ce3d9dfc4c</td>\n",
       "      <td>9951</td>\n",
       "      <td>diadema</td>\n",
       "      <td>SP</td>\n",
       "      <td>2ef4a11b6e24fdfbb43b92cb5f95edff</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-07-23 18:35:14</td>\n",
       "      <td>2018-07-24 10:31:25</td>\n",
       "      <td>2018-07-24 14:37:00</td>\n",
       "      <td>...</td>\n",
       "      <td>12.86</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>84.63</td>\n",
       "      <td>3</td>\n",
       "      <td>arts_and_craftmanship</td>\n",
       "      <td>17201</td>\n",
       "      <td>jau</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116578</th>\n",
       "      <td>ee1cfdc92e449920e25d3ca4ab4da4f6</td>\n",
       "      <td>8d80e477643ec569b24592ce3d9dfc4c</td>\n",
       "      <td>9951</td>\n",
       "      <td>diadema</td>\n",
       "      <td>SP</td>\n",
       "      <td>2ef4a11b6e24fdfbb43b92cb5f95edff</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-07-23 18:35:14</td>\n",
       "      <td>2018-07-24 10:31:25</td>\n",
       "      <td>2018-07-24 14:37:00</td>\n",
       "      <td>...</td>\n",
       "      <td>12.87</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>84.63</td>\n",
       "      <td>3</td>\n",
       "      <td>arts_and_craftmanship</td>\n",
       "      <td>17201</td>\n",
       "      <td>jau</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116579</th>\n",
       "      <td>ab0cf72dfe0538a63a57d6905ccb7b57</td>\n",
       "      <td>8c3c961a52bb5a0ffbce1d4e6d25cd67</td>\n",
       "      <td>35930</td>\n",
       "      <td>joao monlevade</td>\n",
       "      <td>MG</td>\n",
       "      <td>2c12150c742ae2fa48bc703964c16c5f</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-07-28 17:55:27</td>\n",
       "      <td>2018-07-29 18:30:31</td>\n",
       "      <td>2018-07-30 14:54:00</td>\n",
       "      <td>...</td>\n",
       "      <td>19.60</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>7</td>\n",
       "      <td>77.50</td>\n",
       "      <td>5</td>\n",
       "      <td>arts_and_craftmanship</td>\n",
       "      <td>17201</td>\n",
       "      <td>jau</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116580</th>\n",
       "      <td>c61336d6c835632048f973d2f2db598d</td>\n",
       "      <td>428f4721a509be8c52de6ec2900ba918</td>\n",
       "      <td>12305</td>\n",
       "      <td>jacarei</td>\n",
       "      <td>SP</td>\n",
       "      <td>9f7ba492c273ffac8f90a623b8eadd3b</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-08-23 12:31:37</td>\n",
       "      <td>2018-08-24 03:24:24</td>\n",
       "      <td>2018-08-24 15:14:00</td>\n",
       "      <td>...</td>\n",
       "      <td>12.79</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>24.69</td>\n",
       "      <td>5</td>\n",
       "      <td>arts_and_craftmanship</td>\n",
       "      <td>17201</td>\n",
       "      <td>jau</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>116581 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             customer_id                customer_unique_id  \\\n",
       "0       06b8999e2fba1a1fbc88172c00ba8bc7  861eff4711a542e4b93843c6dd7febb0   \n",
       "1       8912fc0c3bbf1e2fbf35819e21706718  9eae34bbd3a474ec5d07949ca7de67c0   \n",
       "2       8912fc0c3bbf1e2fbf35819e21706718  9eae34bbd3a474ec5d07949ca7de67c0   \n",
       "3       f0ac8e5a239118859b1734e1087cbb1f  3c799d181c34d51f6d44bbbc563024db   \n",
       "4       6bc8d08963a135220ed6c6d098831f84  23397e992b09769faf5e66f9e171a241   \n",
       "5       fd3a0b1bd209f0e7d420c9c3d1127613  567ab47ca4deb92d46dbf54dce07d0a7   \n",
       "6       fbd40c083aa8cddebb5265b2ba6aaf2e  f40ab89b622248b7ca125af4b486b887   \n",
       "7       10558ef4afea173bfb5e2cbe3d5b0bb5  749943913a9851a39c9baf51877fbab6   \n",
       "8       1c37c0f0cd1d88d46d9fc9494762abbd  432ecfa8b7b7ad2663c7abed0dc83c51   \n",
       "9       20a452f528d487411fd7d3ebda1d0f20  31318a0597cd9d50ce4cfd03c80fe780   \n",
       "10      20a452f528d487411fd7d3ebda1d0f20  31318a0597cd9d50ce4cfd03c80fe780   \n",
       "11      62f766c82849282806bf2f62ca86432e  83aca63b2193ea126b287f3ae61528d9   \n",
       "12      62f766c82849282806bf2f62ca86432e  83aca63b2193ea126b287f3ae61528d9   \n",
       "13      62f766c82849282806bf2f62ca86432e  83aca63b2193ea126b287f3ae61528d9   \n",
       "14      62f766c82849282806bf2f62ca86432e  83aca63b2193ea126b287f3ae61528d9   \n",
       "15      62f766c82849282806bf2f62ca86432e  83aca63b2193ea126b287f3ae61528d9   \n",
       "16      62f766c82849282806bf2f62ca86432e  83aca63b2193ea126b287f3ae61528d9   \n",
       "17      d3286ca43aa21423c6045b2806ca8e56  acd02fc85cf6339f896c161af2b6abcd   \n",
       "18      c49bec5f74f0413130abdaed8e7621d0  67d6f91d256bdf128fc960a166f413d5   \n",
       "19      0058ebe2dc136d918dd001968cfa5903  9e8993ba2665307b71bd1641c2e1cb3d   \n",
       "20      0058ebe2dc136d918dd001968cfa5903  9e8993ba2665307b71bd1641c2e1cb3d   \n",
       "21      f91eb13e253823f25cf9a20fc2d08f56  1c91f8d50340522dd6601237ad13afff   \n",
       "22      8bf4f9552bc2db14d6195bbbb5131941  b428f59c73c1bbeb3584744b4570654b   \n",
       "23      70bc2fe12d0580eb8336285500defe0e  c71792e0cbb396e0a77258f474d971ab   \n",
       "24      11eee0e62a0b905cf878ff13c8aec4a0  7dd466ce70871be5aba0ea1a43df6021   \n",
       "25      11eee0e62a0b905cf878ff13c8aec4a0  7dd466ce70871be5aba0ea1a43df6021   \n",
       "26      cf4dc8d4be7642c7c05a73188a3d8c1c  35c0fa6f2e6bbb3576e725d22e1066b8   \n",
       "27      d24a0f00aa18133811d0aa666da75ff6  14803be716853016918954f7ef189abd   \n",
       "28      d1ec48f722227f712bd7c376e194dcc2  5fff3ec8fc85df5fe57190fd9b6543ed   \n",
       "29      d1ec48f722227f712bd7c376e194dcc2  5fff3ec8fc85df5fe57190fd9b6543ed   \n",
       "...                                  ...                               ...   \n",
       "116551  d8b65951c84554c80270720c9b5f1178  c6c805ba1449708b259b6da9375318ec   \n",
       "116552  62d3c457010afc957d955568318e6d78  7e3b6916cdc9629811177f17f7ab07ce   \n",
       "116553  e924482b6903522795771e672721f5d2  ab4a2b8d2a955029eb49579cbb027994   \n",
       "116554  c167ca7e3249df1c0fce76509c0da7bc  c04825f7f2dea64775bba021dd842fdd   \n",
       "116555  38278865371cd2796600255e9d1bb872  8092e2240cc0a4ac008c897518f59b01   \n",
       "116556  6e888e003d673cdce73296c4b656fefe  4c0ec556021e916e7d469399bf98d09c   \n",
       "116557  51ff0c33aa4696c23727652d9868dfc5  d10687a929e58de647bfda07ce41713e   \n",
       "116558  1f763783a93e0c559a0c7dc847b31eef  158da4c494bfc8f41b20366129483924   \n",
       "116559  4058d823a6156dd8f9a068750310bac5  3381bbc6c746aa5ff869359fb016c9f7   \n",
       "116560  ca5fc68d665a2ce5392fccbde3475f72  a914b2be4782207abc6758095ec9e8aa   \n",
       "116561  ca5fc68d665a2ce5392fccbde3475f72  a914b2be4782207abc6758095ec9e8aa   \n",
       "116562  29e227115fb68d196f0e1dab3c40a622  da8accaa0030a75e2e7848ec481a4828   \n",
       "116563  7241aae1af5ffcdb5411a9d23be378ea  0cd7d20504975ef3cd878aaffa80e482   \n",
       "116564  2eba59cbdb86a8a535076c9178c8dc3f  2726e5dc94666be0b1fdf08d02eade18   \n",
       "116565  704524826c3b6ea14cea19ba0c11ec4e  2852637db607bf7d453e51b172b3e7d6   \n",
       "116566  4e54b60d9094da89bfd9e8006ec8bcba  0195c54646cda6452f5e1a0474f3b502   \n",
       "116567  324ec4addc12b40f3f4223fc376185fb  0bf96ae17d786534def0b9f7ae788b14   \n",
       "116568  637321037fb8b34323ce7fd8aab4a0f1  07547425c8f7a7086f529f2d3b80d21a   \n",
       "116569  d39cfc95a6ba2cbd9cfa6bb862fd8d77  aad1c7f54ec632ddf6ac8b080b27cd27   \n",
       "116570  ac54eabad353ecdbd32b0d554d99f411  2f521a0cfb4cc68e6ff29a6371d9d850   \n",
       "116571  c8371460f35fcbaec011347c9e84b020  956431f32e0be7439ae229ff4984bc60   \n",
       "116572  520ac67588ffaaf2c60691f72b4a7dcc  d931428aff71cf37ebd00c5808ea9aba   \n",
       "116573  1a655f9c2a5fd2c3e77940ffdc848989  5408b524d4f3a4ac4e9b7b52d993fae7   \n",
       "116574  ff09fd7b29e7488a8d8a20badcd8befe  8c21dd8c37144807c601f99f2a209dfb   \n",
       "116575  ff09fd7b29e7488a8d8a20badcd8befe  8c21dd8c37144807c601f99f2a209dfb   \n",
       "116576  ce2172509c4149d65212484eb761bc37  c5da6424f1f5decedef8672a1a0103e1   \n",
       "116577  ee1cfdc92e449920e25d3ca4ab4da4f6  8d80e477643ec569b24592ce3d9dfc4c   \n",
       "116578  ee1cfdc92e449920e25d3ca4ab4da4f6  8d80e477643ec569b24592ce3d9dfc4c   \n",
       "116579  ab0cf72dfe0538a63a57d6905ccb7b57  8c3c961a52bb5a0ffbce1d4e6d25cd67   \n",
       "116580  c61336d6c835632048f973d2f2db598d  428f4721a509be8c52de6ec2900ba918   \n",
       "\n",
       "        customer_zip_code_prefix          customer_city customer_state  \\\n",
       "0                          14409                 franca             SP   \n",
       "1                          68030               santarem             PA   \n",
       "2                          68030               santarem             PA   \n",
       "3                          92480        nova santa rita             RS   \n",
       "4                          25931                   mage             RJ   \n",
       "5                          88460               angelina             SC   \n",
       "6                          32341               contagem             MG   \n",
       "7                          78134          varzea grande             MT   \n",
       "8                          31270         belo horizonte             MG   \n",
       "9                          37540  santa rita do sapucai             MG   \n",
       "10                         37540  santa rita do sapucai             MG   \n",
       "11                         89115                 gaspar             SC   \n",
       "12                         89115                 gaspar             SC   \n",
       "13                         89115                 gaspar             SC   \n",
       "14                         89115                 gaspar             SC   \n",
       "15                         89115                 gaspar             SC   \n",
       "16                         89115                 gaspar             SC   \n",
       "17                         96083                pelotas             RS   \n",
       "18                         25640             petropolis             RJ   \n",
       "19                         39495            montalvania             MG   \n",
       "20                         39495            montalvania             MG   \n",
       "21                         78500                colider             MT   \n",
       "22                          3728              sao paulo             SP   \n",
       "23                         30672         belo horizonte             MG   \n",
       "24                         40230               salvador             BA   \n",
       "25                         40230               salvador             BA   \n",
       "26                         23585         rio de janeiro             RJ   \n",
       "27                         29900               linhares             ES   \n",
       "28                         86975             mandaguari             PR   \n",
       "29                         86975             mandaguari             PR   \n",
       "...                          ...                    ...            ...   \n",
       "116551                     13201                jundiai             SP   \n",
       "116552                      3118              sao paulo             SP   \n",
       "116553                      2403              sao paulo             SP   \n",
       "116554                     44005       feira de santana             BA   \n",
       "116555                      6023                 osasco             SP   \n",
       "116556                     13471              americana             SP   \n",
       "116557                     66823                  belem             PA   \n",
       "116558                     23030         rio de janeiro             RJ   \n",
       "116559                      4517              sao paulo             SP   \n",
       "116560                      4130              sao paulo             SP   \n",
       "116561                      4130              sao paulo             SP   \n",
       "116562                     50070                 recife             PE   \n",
       "116563                     20241         rio de janeiro             RJ   \n",
       "116564                      5417              sao paulo             SP   \n",
       "116565                     65075               sao luis             MA   \n",
       "116566                      3377              sao paulo             SP   \n",
       "116567                     29167                  serra             ES   \n",
       "116568                     14806             araraquara             SP   \n",
       "116569                     88333     balneario camboriu             SC   \n",
       "116570                      4350              sao paulo             SP   \n",
       "116571                     22745         rio de janeiro             RJ   \n",
       "116572                     92320                 canoas             RS   \n",
       "116573                     95940         arroio do meio             RS   \n",
       "116574                     72587               brasilia             DF   \n",
       "116575                     72587               brasilia             DF   \n",
       "116576                     88350                brusque             SC   \n",
       "116577                      9951                diadema             SP   \n",
       "116578                      9951                diadema             SP   \n",
       "116579                     35930         joao monlevade             MG   \n",
       "116580                     12305                jacarei             SP   \n",
       "\n",
       "                                order_id order_status  \\\n",
       "0       00e7ee1b050b8499577073aeb2a297a1    delivered   \n",
       "1       c1d2b34febe9cd269e378117d6681172    delivered   \n",
       "2       c1d2b34febe9cd269e378117d6681172    delivered   \n",
       "3       b1a5d5365d330d10485e0203d54ab9e8    delivered   \n",
       "4       2e604b3614664aa66867856dba7e61b7    delivered   \n",
       "5       574fe1739f65af76badd0999db300b4f    delivered   \n",
       "6       e0b26f14d2bcc710bb02f77a4628763b    delivered   \n",
       "7       eaae5bd20fb15d85aa673d9b7c0e8ca5    delivered   \n",
       "8       c4e2bd2043fbd75b325a47adfabf0d77    delivered   \n",
       "9       1c7fe02ac4c7be50c59afb295cf85b89    delivered   \n",
       "10      1c7fe02ac4c7be50c59afb295cf85b89    delivered   \n",
       "11      5ee63c8c9ae2e5c38791b35932cf4c69    delivered   \n",
       "12      5ee63c8c9ae2e5c38791b35932cf4c69    delivered   \n",
       "13      5ee63c8c9ae2e5c38791b35932cf4c69    delivered   \n",
       "14      5ee63c8c9ae2e5c38791b35932cf4c69    delivered   \n",
       "15      5ee63c8c9ae2e5c38791b35932cf4c69    delivered   \n",
       "16      5ee63c8c9ae2e5c38791b35932cf4c69    delivered   \n",
       "17      b1cc044d321053348a5e482ca7e6b2e3    delivered   \n",
       "18      0d831c5aa932379c1cf5b0b1b40b214b    delivered   \n",
       "19      27ae3ad0516a9b55f2236e24f690e01b    delivered   \n",
       "20      27ae3ad0516a9b55f2236e24f690e01b    delivered   \n",
       "21      be2463c66944514bb175f7a519a72cd7    delivered   \n",
       "22      7ebc5c60f532934ce55b3025203ebe5b    delivered   \n",
       "23      66573bb355bdba7b6df55fba794b800d    delivered   \n",
       "24      ff7400d904161b62b6e830b3988f5cbd    delivered   \n",
       "25      ff7400d904161b62b6e830b3988f5cbd    delivered   \n",
       "26      edc9338008f2e9017f842a8bcad3af94    delivered   \n",
       "27      6ee77fd090a49ec9c4e15bd1a94e100e    delivered   \n",
       "28      166029aa4dc1bed21f0d50ebdb4ef286    delivered   \n",
       "29      166029aa4dc1bed21f0d50ebdb4ef286    delivered   \n",
       "...                                  ...          ...   \n",
       "116551  6bcce39be127575854779e39a50f2059    delivered   \n",
       "116552  796033ffde6ba06e0abe387e36ee8fe0    delivered   \n",
       "116553  30d7af26cb5f8577b5c6a0164dc814c5     canceled   \n",
       "116554  d6f7121c5ede8e22d75e57b6e836871f    delivered   \n",
       "116555  618b0e262ba7b2c46a5b349fadbd4996    delivered   \n",
       "116556  549e5c65d1f41cb1c4d307752c3b9eae    delivered   \n",
       "116557  0ec7299d65db721d9a6b5c95c2ff494a    delivered   \n",
       "116558  bd2f31f2548344a9d1f4ea1c03625e93    delivered   \n",
       "116559  85abe9ebac688730f1c3591c1ca2a167    delivered   \n",
       "116560  01787af385123b93ba50c5f05530ee12    delivered   \n",
       "116561  01787af385123b93ba50c5f05530ee12    delivered   \n",
       "116562  335cf1cb074c8dfcdf59bc86f8255409    delivered   \n",
       "116563  b1e786caeddece12e5efd30580eaaabc    delivered   \n",
       "116564  e8dbfb34a57fe34db02546aa50b56f10    delivered   \n",
       "116565  215b38acfb4aef3bb0144656bb102421    delivered   \n",
       "116566  f203b250a442eac108f23c45f8b1efc8    delivered   \n",
       "116567  a71ebda57a67e6349ab52539913f78d8    delivered   \n",
       "116568  91be51c856a90d7efe86cf9d082d6ae3    delivered   \n",
       "116569  aa4bd1aa4ff7249ea156343f704478a0    delivered   \n",
       "116570  c9e7721790d06c21ff6fcd2dee7a2f73    delivered   \n",
       "116571  b36511d6ed0cbc11beb1ab142ab3021c    delivered   \n",
       "116572  a79bd7af35a4fffee2b23fded963b4ea    delivered   \n",
       "116573  57edfe6486364c7777241f5ab5902715   processing   \n",
       "116574  157de1652c032429a625d034be79eeeb    delivered   \n",
       "116575  157de1652c032429a625d034be79eeeb    delivered   \n",
       "116576  edcf1e1eeb52381be9388c90152be52d    delivered   \n",
       "116577  2ef4a11b6e24fdfbb43b92cb5f95edff    delivered   \n",
       "116578  2ef4a11b6e24fdfbb43b92cb5f95edff    delivered   \n",
       "116579  2c12150c742ae2fa48bc703964c16c5f    delivered   \n",
       "116580  9f7ba492c273ffac8f90a623b8eadd3b    delivered   \n",
       "\n",
       "       order_purchase_timestamp   order_approved_at  \\\n",
       "0           2017-05-16 15:05:35 2017-05-16 15:22:12   \n",
       "1           2017-11-09 00:50:13 2017-11-10 00:47:48   \n",
       "2           2017-11-09 00:50:13 2017-11-10 00:47:48   \n",
       "3           2017-05-07 20:11:26 2017-05-08 22:22:56   \n",
       "4           2018-02-03 19:45:40 2018-02-04 22:29:19   \n",
       "5           2017-03-23 15:10:17 2017-03-23 15:25:11   \n",
       "6           2017-05-16 10:00:49 2017-05-17 03:45:27   \n",
       "7           2017-03-18 23:04:36 2017-03-18 23:04:36   \n",
       "8           2018-03-15 07:45:10 2018-03-15 07:55:24   \n",
       "9           2018-01-26 13:22:09 2018-01-30 03:47:31   \n",
       "10          2018-01-26 13:22:09 2018-01-30 03:47:31   \n",
       "11          2017-05-09 11:30:18 2017-05-09 11:45:13   \n",
       "12          2017-05-09 11:30:18 2017-05-09 11:45:13   \n",
       "13          2017-05-09 11:30:18 2017-05-09 11:45:13   \n",
       "14          2017-05-09 11:30:18 2017-05-09 11:45:13   \n",
       "15          2017-05-09 11:30:18 2017-05-09 11:45:13   \n",
       "16          2017-05-09 11:30:18 2017-05-09 11:45:13   \n",
       "17          2018-01-30 19:29:01 2018-01-30 19:54:04   \n",
       "18          2017-10-13 10:36:54 2017-10-13 10:49:47   \n",
       "19          2017-02-09 16:19:02 2017-02-09 16:30:12   \n",
       "20          2017-02-09 16:19:02 2017-02-09 16:30:12   \n",
       "21          2018-02-07 19:01:48 2018-02-07 19:15:27   \n",
       "22          2017-06-24 08:59:47 2017-06-27 03:45:30   \n",
       "23          2017-10-15 10:30:57 2017-10-15 10:49:15   \n",
       "24          2018-03-27 15:37:03 2018-03-27 15:51:53   \n",
       "25          2018-03-27 15:37:03 2018-03-27 15:51:53   \n",
       "26          2017-04-28 16:55:22 2017-04-28 17:10:12   \n",
       "27          2017-03-23 17:44:48 2017-03-25 02:15:36   \n",
       "28          2018-04-02 09:09:45 2018-04-02 09:28:31   \n",
       "29          2018-04-02 09:09:45 2018-04-02 09:28:31   \n",
       "...                         ...                 ...   \n",
       "116551      2018-04-28 02:23:24 2018-05-01 05:32:22   \n",
       "116552      2018-08-26 14:17:08 2018-08-26 14:25:15   \n",
       "116553      2018-07-26 12:45:33 2018-07-26 12:55:25   \n",
       "116554      2018-08-16 14:42:48 2018-08-16 14:55:21   \n",
       "116555      2018-05-19 17:48:44 2018-05-22 09:36:46   \n",
       "116556      2018-05-22 14:22:51 2018-05-22 14:34:57   \n",
       "116557      2018-05-11 16:51:46 2018-05-11 17:08:39   \n",
       "116558      2018-05-17 16:37:22 2018-05-18 00:14:47   \n",
       "116559      2018-05-31 20:13:28 2018-05-31 20:33:08   \n",
       "116560      2018-05-15 17:33:50 2018-05-15 17:54:29   \n",
       "116561      2018-05-15 17:33:50 2018-05-15 17:54:29   \n",
       "116562      2018-05-15 09:08:59 2018-05-16 09:33:16   \n",
       "116563      2018-04-20 08:04:26 2018-04-24 17:46:25   \n",
       "116564      2018-05-16 15:01:23 2018-05-16 15:19:35   \n",
       "116565      2018-05-18 01:52:29 2018-05-19 03:13:01   \n",
       "116566      2018-05-09 17:18:42 2018-05-11 02:57:42   \n",
       "116567      2018-07-26 16:24:10 2018-07-26 16:35:10   \n",
       "116568      2018-06-12 15:58:03 2018-06-12 16:32:26   \n",
       "116569      2018-08-18 09:47:18 2018-08-18 09:55:14   \n",
       "116570      2018-07-04 20:23:40 2018-07-05 16:24:41   \n",
       "116571      2018-08-14 14:21:42 2018-08-14 14:35:08   \n",
       "116572      2017-04-21 20:16:02 2017-04-21 20:25:22   \n",
       "116573      2017-06-13 10:51:54 2017-06-13 11:03:07   \n",
       "116574      2017-05-10 09:00:07 2017-05-10 09:10:18   \n",
       "116575      2017-05-10 09:00:07 2017-05-10 09:10:18   \n",
       "116576      2018-08-21 11:29:05 2018-08-21 11:50:47   \n",
       "116577      2018-07-23 18:35:14 2018-07-24 10:31:25   \n",
       "116578      2018-07-23 18:35:14 2018-07-24 10:31:25   \n",
       "116579      2018-07-28 17:55:27 2018-07-29 18:30:31   \n",
       "116580      2018-08-23 12:31:37 2018-08-24 03:24:24   \n",
       "\n",
       "       order_delivered_carrier_date      ...      freight_value  \\\n",
       "0               2017-05-23 10:47:57      ...              21.88   \n",
       "1               2017-11-22 01:43:37      ...              24.90   \n",
       "2               2017-11-22 01:43:37      ...              24.90   \n",
       "3               2017-05-19 20:16:31      ...              15.62   \n",
       "4               2018-02-19 18:21:47      ...              30.59   \n",
       "5               2017-03-28 18:23:51      ...              15.06   \n",
       "6               2017-05-23 10:35:48      ...              30.71   \n",
       "7               2017-03-28 06:29:47      ...              21.34   \n",
       "8               2018-03-27 15:58:41      ...              33.08   \n",
       "9               2018-02-09 13:17:44      ...              21.76   \n",
       "10              2018-02-09 13:17:44      ...              21.76   \n",
       "11              2017-05-19 19:32:55      ...              15.62   \n",
       "12              2017-05-19 19:32:55      ...              15.62   \n",
       "13              2017-05-19 19:32:55      ...              15.62   \n",
       "14              2017-05-19 19:32:55      ...              15.62   \n",
       "15              2017-05-19 19:32:55      ...              15.62   \n",
       "16              2017-05-19 19:32:55      ...              15.62   \n",
       "17              2018-02-09 13:08:10      ...              38.59   \n",
       "18              2017-10-24 18:58:49      ...              34.18   \n",
       "19              2017-02-22 03:51:44      ...              15.01   \n",
       "20              2017-02-22 03:51:44      ...              15.01   \n",
       "21              2018-02-20 13:59:00      ...              43.19   \n",
       "22              2017-06-29 17:37:56      ...              23.46   \n",
       "23              2017-10-24 22:23:05      ...              28.15   \n",
       "24              2018-04-11 01:05:20      ...              37.97   \n",
       "25              2018-04-11 01:05:20      ...              37.97   \n",
       "26              2017-05-12 10:54:53      ...              15.04   \n",
       "27              2017-04-07 15:15:50      ...              16.59   \n",
       "28              2018-04-16 14:02:55      ...              57.08   \n",
       "29              2018-04-16 14:02:55      ...              57.08   \n",
       "...                             ...      ...                ...   \n",
       "116551          2018-05-04 11:35:00      ...              11.15   \n",
       "116552          2018-08-27 07:45:00      ...              19.08   \n",
       "116553                          NaT      ...              11.22   \n",
       "116554          2018-08-17 08:24:00      ...              22.52   \n",
       "116555          2018-05-22 13:46:00      ...               7.39   \n",
       "116556          2018-05-23 09:07:00      ...               7.39   \n",
       "116557          2018-05-14 09:48:00      ...              19.04   \n",
       "116558          2018-05-18 07:13:00      ...              15.23   \n",
       "116559          2018-06-01 10:01:00      ...               7.44   \n",
       "116560          2018-05-16 09:25:00      ...               7.39   \n",
       "116561          2018-05-16 09:25:00      ...               7.39   \n",
       "116562          2018-05-16 08:57:00      ...              19.04   \n",
       "116563          2018-04-23 12:34:51      ...              15.23   \n",
       "116564          2018-05-18 07:13:00      ...               7.39   \n",
       "116565          2018-05-21 10:39:00      ...              22.85   \n",
       "116566          2018-05-11 10:54:00      ...               7.39   \n",
       "116567          2018-07-27 10:32:00      ...              15.76   \n",
       "116568          2018-06-12 15:44:00      ...              19.17   \n",
       "116569          2018-08-20 08:49:00      ...              18.69   \n",
       "116570          2018-07-05 08:39:00      ...               8.05   \n",
       "116571          2018-08-15 06:55:00      ...              17.09   \n",
       "116572          2017-04-28 14:29:17      ...              16.05   \n",
       "116573                          NaT      ...              15.10   \n",
       "116574          2017-05-15 10:06:54      ...              14.10   \n",
       "116575          2017-05-15 10:06:54      ...              14.10   \n",
       "116576          2018-08-21 15:37:00      ...              24.83   \n",
       "116577          2018-07-24 14:37:00      ...              12.86   \n",
       "116578          2018-07-24 14:37:00      ...              12.87   \n",
       "116579          2018-07-30 14:54:00      ...              19.60   \n",
       "116580          2018-08-24 15:14:00      ...              12.79   \n",
       "\n",
       "       payment_sequential payment_type  payment_installments  payment_value  \\\n",
       "0                       1  credit_card                     2         146.87   \n",
       "1                       1  credit_card                     1         275.79   \n",
       "2                       1  credit_card                     1         275.79   \n",
       "3                       1  credit_card                     7         140.61   \n",
       "4                       1  credit_card                    10         137.58   \n",
       "5                       1  credit_card                     5         142.05   \n",
       "6                       1       boleto                     1         155.70   \n",
       "7                       1  credit_card                     2         148.33   \n",
       "8                       1  credit_card                     3         150.07   \n",
       "9                       1       boleto                     1         257.50   \n",
       "10                      1       boleto                     1         257.50   \n",
       "11                      1  credit_card                     6         843.66   \n",
       "12                      1  credit_card                     6         843.66   \n",
       "13                      1  credit_card                     6         843.66   \n",
       "14                      1  credit_card                     6         843.66   \n",
       "15                      1  credit_card                     6         843.66   \n",
       "16                      1  credit_card                     6         843.66   \n",
       "17                      1  credit_card                     1         145.58   \n",
       "18                      1  credit_card                     1         151.17   \n",
       "19                      1  credit_card                     5         270.00   \n",
       "20                      1  credit_card                     5         270.00   \n",
       "21                      1  credit_card                     1         160.18   \n",
       "22                      1       boleto                     1         148.45   \n",
       "23                      1  credit_card                     1         141.14   \n",
       "24                      1  credit_card                     1          54.96   \n",
       "25                      2      voucher                     1         100.00   \n",
       "26                      1      voucher                     1         140.03   \n",
       "27                      1       boleto                     1         143.58   \n",
       "28                      1  credit_card                    10         870.35   \n",
       "29                      1  credit_card                    10         870.35   \n",
       "...                   ...          ...                   ...            ...   \n",
       "116551                  1       boleto                     1          56.15   \n",
       "116552                  1  credit_card                     3         158.97   \n",
       "116553                  1  credit_card                     3          40.02   \n",
       "116554                  1   debit_card                     1         107.42   \n",
       "116555                  1       boleto                     1          57.19   \n",
       "116556                  1  credit_card                     3          57.19   \n",
       "116557                  1  credit_card                     6          68.84   \n",
       "116558                  1  credit_card                     2          65.03   \n",
       "116559                  1  credit_card                     3          90.24   \n",
       "116560                  1  credit_card                     1         114.38   \n",
       "116561                  1  credit_card                     1         114.38   \n",
       "116562                  1  credit_card                     6          68.84   \n",
       "116563                  1       boleto                     1          65.03   \n",
       "116564                  1  credit_card                     1          57.19   \n",
       "116565                  1       boleto                     1          72.65   \n",
       "116566                  1       boleto                     1          57.19   \n",
       "116567                  1   debit_card                     1         110.66   \n",
       "116568                  1  credit_card                    18         397.90   \n",
       "116569                  1   debit_card                     1         103.59   \n",
       "116570                  1  credit_card                     1         120.95   \n",
       "116571                  1  credit_card                     1         145.09   \n",
       "116572                  1  credit_card                     1          41.04   \n",
       "116573                  1  credit_card                     4          40.09   \n",
       "116574                  1  credit_card                     3          39.09   \n",
       "116575                  1  credit_card                     3          39.09   \n",
       "116576                  1  credit_card                     5         314.32   \n",
       "116577                  1  credit_card                     1          84.63   \n",
       "116578                  1  credit_card                     1          84.63   \n",
       "116579                  1  credit_card                     7          77.50   \n",
       "116580                  1       boleto                     1          24.69   \n",
       "\n",
       "        review_score product_category_name_english  seller_zip_code_prefix  \\\n",
       "0                  4              office_furniture                    8577   \n",
       "1                  1              office_furniture                    8577   \n",
       "2                  1              office_furniture                    8577   \n",
       "3                  3              office_furniture                    8577   \n",
       "4                  4              office_furniture                    8577   \n",
       "5                  4              office_furniture                    8577   \n",
       "6                  4              office_furniture                    8577   \n",
       "7                  2              office_furniture                    8577   \n",
       "8                  3              office_furniture                    8577   \n",
       "9                  2              office_furniture                    8577   \n",
       "10                 2              office_furniture                    8577   \n",
       "11                 4              office_furniture                    8577   \n",
       "12                 4              office_furniture                    8577   \n",
       "13                 4              office_furniture                    8577   \n",
       "14                 4              office_furniture                    8577   \n",
       "15                 4              office_furniture                    8577   \n",
       "16                 4              office_furniture                    8577   \n",
       "17                 2              office_furniture                    8577   \n",
       "18                 3              office_furniture                    8577   \n",
       "19                 3              office_furniture                    8577   \n",
       "20                 3              office_furniture                    8577   \n",
       "21                 4              office_furniture                    8577   \n",
       "22                 2              office_furniture                    8577   \n",
       "23                 5              office_furniture                    8577   \n",
       "24                 2              office_furniture                    8577   \n",
       "25                 2              office_furniture                    8577   \n",
       "26                 5              office_furniture                    8577   \n",
       "27                 5              office_furniture                    8577   \n",
       "28                 5              office_furniture                    8577   \n",
       "29                 5              office_furniture                    8577   \n",
       "...              ...                           ...                     ...   \n",
       "116551             5           diapers_and_hygiene                    6380   \n",
       "116552             5           diapers_and_hygiene                   89063   \n",
       "116553             1           diapers_and_hygiene                    1403   \n",
       "116554             4                    cine_photo                    9041   \n",
       "116555             5                    cine_photo                    9041   \n",
       "116556             5                    cine_photo                    9041   \n",
       "116557             5                    cine_photo                    9041   \n",
       "116558             3                    cine_photo                    9041   \n",
       "116559             5                    cine_photo                    9041   \n",
       "116560             5                    cine_photo                    9041   \n",
       "116561             5                    cine_photo                    9041   \n",
       "116562             5                    cine_photo                    9041   \n",
       "116563             5                    cine_photo                    9041   \n",
       "116564             5                    cine_photo                    9041   \n",
       "116565             5                    cine_photo                    9041   \n",
       "116566             5                    cine_photo                    9041   \n",
       "116567             5                    cine_photo                    9041   \n",
       "116568             1                    cine_photo                    9041   \n",
       "116569             4                    cine_photo                    9041   \n",
       "116570             5                    cine_photo                    9041   \n",
       "116571             5                    cine_photo                    9041   \n",
       "116572             4                 fashion_sport                    6038   \n",
       "116573             1                 fashion_sport                    6038   \n",
       "116574             5                 fashion_sport                    6038   \n",
       "116575             5                 fashion_sport                    6038   \n",
       "116576             5         arts_and_craftmanship                   95910   \n",
       "116577             3         arts_and_craftmanship                   17201   \n",
       "116578             3         arts_and_craftmanship                   17201   \n",
       "116579             5         arts_and_craftmanship                   17201   \n",
       "116580             5         arts_and_craftmanship                   17201   \n",
       "\n",
       "            seller_city  seller_state  \n",
       "0       itaquaquecetuba            SP  \n",
       "1       itaquaquecetuba            SP  \n",
       "2       itaquaquecetuba            SP  \n",
       "3       itaquaquecetuba            SP  \n",
       "4       itaquaquecetuba            SP  \n",
       "5       itaquaquecetuba            SP  \n",
       "6       itaquaquecetuba            SP  \n",
       "7       itaquaquecetuba            SP  \n",
       "8       itaquaquecetuba            SP  \n",
       "9       itaquaquecetuba            SP  \n",
       "10      itaquaquecetuba            SP  \n",
       "11      itaquaquecetuba            SP  \n",
       "12      itaquaquecetuba            SP  \n",
       "13      itaquaquecetuba            SP  \n",
       "14      itaquaquecetuba            SP  \n",
       "15      itaquaquecetuba            SP  \n",
       "16      itaquaquecetuba            SP  \n",
       "17      itaquaquecetuba            SP  \n",
       "18      itaquaquecetuba            SP  \n",
       "19      itaquaquecetuba            SP  \n",
       "20      itaquaquecetuba            SP  \n",
       "21      itaquaquecetuba            SP  \n",
       "22      itaquaquecetuba            SP  \n",
       "23      itaquaquecetuba            SP  \n",
       "24      itaquaquecetuba            SP  \n",
       "25      itaquaquecetuba            SP  \n",
       "26      itaquaquecetuba            SP  \n",
       "27      itaquaquecetuba            SP  \n",
       "28      itaquaquecetuba            SP  \n",
       "29      itaquaquecetuba            SP  \n",
       "...                 ...           ...  \n",
       "116551      carapicuiba            SP  \n",
       "116552         blumenau            SC  \n",
       "116553        sao paulo            SP  \n",
       "116554      santo andre            SP  \n",
       "116555      santo andre            SP  \n",
       "116556      santo andre            SP  \n",
       "116557      santo andre            SP  \n",
       "116558      santo andre            SP  \n",
       "116559      santo andre            SP  \n",
       "116560      santo andre            SP  \n",
       "116561      santo andre            SP  \n",
       "116562      santo andre            SP  \n",
       "116563      santo andre            SP  \n",
       "116564      santo andre            SP  \n",
       "116565      santo andre            SP  \n",
       "116566      santo andre            SP  \n",
       "116567      santo andre            SP  \n",
       "116568      santo andre            SP  \n",
       "116569      santo andre            SP  \n",
       "116570      santo andre            SP  \n",
       "116571      santo andre            SP  \n",
       "116572           osasco            SP  \n",
       "116573           osasco            SP  \n",
       "116574           osasco            SP  \n",
       "116575           osasco            SP  \n",
       "116576          lajeado            RS  \n",
       "116577              jau            SP  \n",
       "116578              jau            SP  \n",
       "116579              jau            SP  \n",
       "116580              jau            SP  \n",
       "\n",
       "[116581 rows x 24 columns]"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Visual assessment\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 116581 entries, 0 to 116580\n",
      "Data columns (total 24 columns):\n",
      "customer_id                      116581 non-null object\n",
      "customer_unique_id               116581 non-null object\n",
      "customer_zip_code_prefix         116581 non-null int64\n",
      "customer_city                    116581 non-null object\n",
      "customer_state                   116581 non-null object\n",
      "order_id                         116581 non-null object\n",
      "order_status                     116581 non-null object\n",
      "order_purchase_timestamp         116581 non-null datetime64[ns]\n",
      "order_approved_at                116567 non-null datetime64[ns]\n",
      "order_delivered_carrier_date     115368 non-null datetime64[ns]\n",
      "order_delivered_customer_date    114066 non-null datetime64[ns]\n",
      "order_estimated_delivery_date    116581 non-null datetime64[ns]\n",
      "seller_id                        116581 non-null object\n",
      "price                            116581 non-null float64\n",
      "freight_value                    116581 non-null float64\n",
      "payment_sequential               116581 non-null int64\n",
      "payment_type                     116581 non-null object\n",
      "payment_installments             116581 non-null int64\n",
      "payment_value                    116581 non-null float64\n",
      "review_score                     116581 non-null int64\n",
      "product_category_name_english    116581 non-null object\n",
      "seller_zip_code_prefix           116581 non-null int64\n",
      "seller_city                      116581 non-null object\n",
      "seller_state                     116581 non-null object\n",
      "dtypes: datetime64[ns](5), float64(3), int64(5), object(11)\n",
      "memory usage: 22.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleaing issues\n",
    "\n",
    "- No missing data\n",
    "- product_category_name_english to be changed to product_category"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleaning\n",
    "\n",
    "- 'product_category_name_english' to be changed to 'product_category'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "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",
       "      <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>...</th>\n",
       "      <th>freight_value</th>\n",
       "      <th>payment_sequential</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>payment_installments</th>\n",
       "      <th>payment_value</th>\n",
       "      <th>review_score</th>\n",
       "      <th>product_name</th>\n",
       "      <th>seller_zip_code_prefix</th>\n",
       "      <th>seller_city</th>\n",
       "      <th>seller_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",
       "      <td>00e7ee1b050b8499577073aeb2a297a1</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-16 15:05:35</td>\n",
       "      <td>2017-05-16 15:22:12</td>\n",
       "      <td>2017-05-23 10:47:57</td>\n",
       "      <td>...</td>\n",
       "      <td>21.88</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>2</td>\n",
       "      <td>146.87</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8912fc0c3bbf1e2fbf35819e21706718</td>\n",
       "      <td>9eae34bbd3a474ec5d07949ca7de67c0</td>\n",
       "      <td>68030</td>\n",
       "      <td>santarem</td>\n",
       "      <td>PA</td>\n",
       "      <td>c1d2b34febe9cd269e378117d6681172</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-09 00:50:13</td>\n",
       "      <td>2017-11-10 00:47:48</td>\n",
       "      <td>2017-11-22 01:43:37</td>\n",
       "      <td>...</td>\n",
       "      <td>24.90</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>275.79</td>\n",
       "      <td>1</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8912fc0c3bbf1e2fbf35819e21706718</td>\n",
       "      <td>9eae34bbd3a474ec5d07949ca7de67c0</td>\n",
       "      <td>68030</td>\n",
       "      <td>santarem</td>\n",
       "      <td>PA</td>\n",
       "      <td>c1d2b34febe9cd269e378117d6681172</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-09 00:50:13</td>\n",
       "      <td>2017-11-10 00:47:48</td>\n",
       "      <td>2017-11-22 01:43:37</td>\n",
       "      <td>...</td>\n",
       "      <td>24.90</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>275.79</td>\n",
       "      <td>1</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f0ac8e5a239118859b1734e1087cbb1f</td>\n",
       "      <td>3c799d181c34d51f6d44bbbc563024db</td>\n",
       "      <td>92480</td>\n",
       "      <td>nova santa rita</td>\n",
       "      <td>RS</td>\n",
       "      <td>b1a5d5365d330d10485e0203d54ab9e8</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-07 20:11:26</td>\n",
       "      <td>2017-05-08 22:22:56</td>\n",
       "      <td>2017-05-19 20:16:31</td>\n",
       "      <td>...</td>\n",
       "      <td>15.62</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>7</td>\n",
       "      <td>140.61</td>\n",
       "      <td>3</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6bc8d08963a135220ed6c6d098831f84</td>\n",
       "      <td>23397e992b09769faf5e66f9e171a241</td>\n",
       "      <td>25931</td>\n",
       "      <td>mage</td>\n",
       "      <td>RJ</td>\n",
       "      <td>2e604b3614664aa66867856dba7e61b7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-03 19:45:40</td>\n",
       "      <td>2018-02-04 22:29:19</td>\n",
       "      <td>2018-02-19 18:21:47</td>\n",
       "      <td>...</td>\n",
       "      <td>30.59</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>10</td>\n",
       "      <td>137.58</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        customer_id                customer_unique_id  \\\n",
       "0  06b8999e2fba1a1fbc88172c00ba8bc7  861eff4711a542e4b93843c6dd7febb0   \n",
       "1  8912fc0c3bbf1e2fbf35819e21706718  9eae34bbd3a474ec5d07949ca7de67c0   \n",
       "2  8912fc0c3bbf1e2fbf35819e21706718  9eae34bbd3a474ec5d07949ca7de67c0   \n",
       "3  f0ac8e5a239118859b1734e1087cbb1f  3c799d181c34d51f6d44bbbc563024db   \n",
       "4  6bc8d08963a135220ed6c6d098831f84  23397e992b09769faf5e66f9e171a241   \n",
       "\n",
       "   customer_zip_code_prefix    customer_city customer_state  \\\n",
       "0                     14409           franca             SP   \n",
       "1                     68030         santarem             PA   \n",
       "2                     68030         santarem             PA   \n",
       "3                     92480  nova santa rita             RS   \n",
       "4                     25931             mage             RJ   \n",
       "\n",
       "                           order_id order_status order_purchase_timestamp  \\\n",
       "0  00e7ee1b050b8499577073aeb2a297a1    delivered      2017-05-16 15:05:35   \n",
       "1  c1d2b34febe9cd269e378117d6681172    delivered      2017-11-09 00:50:13   \n",
       "2  c1d2b34febe9cd269e378117d6681172    delivered      2017-11-09 00:50:13   \n",
       "3  b1a5d5365d330d10485e0203d54ab9e8    delivered      2017-05-07 20:11:26   \n",
       "4  2e604b3614664aa66867856dba7e61b7    delivered      2018-02-03 19:45:40   \n",
       "\n",
       "    order_approved_at order_delivered_carrier_date      ...       \\\n",
       "0 2017-05-16 15:22:12          2017-05-23 10:47:57      ...        \n",
       "1 2017-11-10 00:47:48          2017-11-22 01:43:37      ...        \n",
       "2 2017-11-10 00:47:48          2017-11-22 01:43:37      ...        \n",
       "3 2017-05-08 22:22:56          2017-05-19 20:16:31      ...        \n",
       "4 2018-02-04 22:29:19          2018-02-19 18:21:47      ...        \n",
       "\n",
       "  freight_value payment_sequential payment_type  payment_installments  \\\n",
       "0         21.88                  1  credit_card                     2   \n",
       "1         24.90                  1  credit_card                     1   \n",
       "2         24.90                  1  credit_card                     1   \n",
       "3         15.62                  1  credit_card                     7   \n",
       "4         30.59                  1  credit_card                    10   \n",
       "\n",
       "   payment_value  review_score      product_name  seller_zip_code_prefix  \\\n",
       "0         146.87             4  office_furniture                    8577   \n",
       "1         275.79             1  office_furniture                    8577   \n",
       "2         275.79             1  office_furniture                    8577   \n",
       "3         140.61             3  office_furniture                    8577   \n",
       "4         137.58             4  office_furniture                    8577   \n",
       "\n",
       "       seller_city  seller_state  \n",
       "0  itaquaquecetuba            SP  \n",
       "1  itaquaquecetuba            SP  \n",
       "2  itaquaquecetuba            SP  \n",
       "3  itaquaquecetuba            SP  \n",
       "4  itaquaquecetuba            SP  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 189,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.rename(columns={'product_category_name_english':'product_name'},inplace=True)\n",
    "#check results\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['customer_id', 'customer_unique_id', 'customer_zip_code_prefix',\n",
       "       'customer_city', 'customer_state', 'order_id', 'order_status',\n",
       "       'order_purchase_timestamp', 'order_approved_at',\n",
       "       'order_delivered_carrier_date', 'order_delivered_customer_date',\n",
       "       'order_estimated_delivery_date', 'seller_id', 'price', 'freight_value',\n",
       "       'payment_sequential', 'payment_type', 'payment_installments',\n",
       "       'payment_value', 'review_score', 'product_name',\n",
       "       'seller_zip_code_prefix', 'seller_city', 'seller_state'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 190,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Feature Engineering\n",
    "\n",
    "- approval_timespan\n",
    "- delivery_timespan\n",
    "- delivered_on_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "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",
       "      <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>...</th>\n",
       "      <th>payment_sequential</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>payment_installments</th>\n",
       "      <th>payment_value</th>\n",
       "      <th>review_score</th>\n",
       "      <th>product_name</th>\n",
       "      <th>seller_zip_code_prefix</th>\n",
       "      <th>seller_city</th>\n",
       "      <th>seller_state</th>\n",
       "      <th>approval_timespan</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",
       "      <td>00e7ee1b050b8499577073aeb2a297a1</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-16 15:05:35</td>\n",
       "      <td>2017-05-16 15:22:12</td>\n",
       "      <td>2017-05-23 10:47:57</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>2</td>\n",
       "      <td>146.87</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "      <td>0 days 00:16:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8912fc0c3bbf1e2fbf35819e21706718</td>\n",
       "      <td>9eae34bbd3a474ec5d07949ca7de67c0</td>\n",
       "      <td>68030</td>\n",
       "      <td>santarem</td>\n",
       "      <td>PA</td>\n",
       "      <td>c1d2b34febe9cd269e378117d6681172</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-09 00:50:13</td>\n",
       "      <td>2017-11-10 00:47:48</td>\n",
       "      <td>2017-11-22 01:43:37</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>275.79</td>\n",
       "      <td>1</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "      <td>0 days 23:57:35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8912fc0c3bbf1e2fbf35819e21706718</td>\n",
       "      <td>9eae34bbd3a474ec5d07949ca7de67c0</td>\n",
       "      <td>68030</td>\n",
       "      <td>santarem</td>\n",
       "      <td>PA</td>\n",
       "      <td>c1d2b34febe9cd269e378117d6681172</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-09 00:50:13</td>\n",
       "      <td>2017-11-10 00:47:48</td>\n",
       "      <td>2017-11-22 01:43:37</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>275.79</td>\n",
       "      <td>1</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "      <td>0 days 23:57:35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f0ac8e5a239118859b1734e1087cbb1f</td>\n",
       "      <td>3c799d181c34d51f6d44bbbc563024db</td>\n",
       "      <td>92480</td>\n",
       "      <td>nova santa rita</td>\n",
       "      <td>RS</td>\n",
       "      <td>b1a5d5365d330d10485e0203d54ab9e8</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-07 20:11:26</td>\n",
       "      <td>2017-05-08 22:22:56</td>\n",
       "      <td>2017-05-19 20:16:31</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>7</td>\n",
       "      <td>140.61</td>\n",
       "      <td>3</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "      <td>1 days 02:11:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6bc8d08963a135220ed6c6d098831f84</td>\n",
       "      <td>23397e992b09769faf5e66f9e171a241</td>\n",
       "      <td>25931</td>\n",
       "      <td>mage</td>\n",
       "      <td>RJ</td>\n",
       "      <td>2e604b3614664aa66867856dba7e61b7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-03 19:45:40</td>\n",
       "      <td>2018-02-04 22:29:19</td>\n",
       "      <td>2018-02-19 18:21:47</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>10</td>\n",
       "      <td>137.58</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "      <td>1 days 02:43:39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        customer_id                customer_unique_id  \\\n",
       "0  06b8999e2fba1a1fbc88172c00ba8bc7  861eff4711a542e4b93843c6dd7febb0   \n",
       "1  8912fc0c3bbf1e2fbf35819e21706718  9eae34bbd3a474ec5d07949ca7de67c0   \n",
       "2  8912fc0c3bbf1e2fbf35819e21706718  9eae34bbd3a474ec5d07949ca7de67c0   \n",
       "3  f0ac8e5a239118859b1734e1087cbb1f  3c799d181c34d51f6d44bbbc563024db   \n",
       "4  6bc8d08963a135220ed6c6d098831f84  23397e992b09769faf5e66f9e171a241   \n",
       "\n",
       "   customer_zip_code_prefix    customer_city customer_state  \\\n",
       "0                     14409           franca             SP   \n",
       "1                     68030         santarem             PA   \n",
       "2                     68030         santarem             PA   \n",
       "3                     92480  nova santa rita             RS   \n",
       "4                     25931             mage             RJ   \n",
       "\n",
       "                           order_id order_status order_purchase_timestamp  \\\n",
       "0  00e7ee1b050b8499577073aeb2a297a1    delivered      2017-05-16 15:05:35   \n",
       "1  c1d2b34febe9cd269e378117d6681172    delivered      2017-11-09 00:50:13   \n",
       "2  c1d2b34febe9cd269e378117d6681172    delivered      2017-11-09 00:50:13   \n",
       "3  b1a5d5365d330d10485e0203d54ab9e8    delivered      2017-05-07 20:11:26   \n",
       "4  2e604b3614664aa66867856dba7e61b7    delivered      2018-02-03 19:45:40   \n",
       "\n",
       "    order_approved_at order_delivered_carrier_date        ...          \\\n",
       "0 2017-05-16 15:22:12          2017-05-23 10:47:57        ...           \n",
       "1 2017-11-10 00:47:48          2017-11-22 01:43:37        ...           \n",
       "2 2017-11-10 00:47:48          2017-11-22 01:43:37        ...           \n",
       "3 2017-05-08 22:22:56          2017-05-19 20:16:31        ...           \n",
       "4 2018-02-04 22:29:19          2018-02-19 18:21:47        ...           \n",
       "\n",
       "  payment_sequential payment_type payment_installments  payment_value  \\\n",
       "0                  1  credit_card                    2         146.87   \n",
       "1                  1  credit_card                    1         275.79   \n",
       "2                  1  credit_card                    1         275.79   \n",
       "3                  1  credit_card                    7         140.61   \n",
       "4                  1  credit_card                   10         137.58   \n",
       "\n",
       "   review_score      product_name seller_zip_code_prefix      seller_city  \\\n",
       "0             4  office_furniture                   8577  itaquaquecetuba   \n",
       "1             1  office_furniture                   8577  itaquaquecetuba   \n",
       "2             1  office_furniture                   8577  itaquaquecetuba   \n",
       "3             3  office_furniture                   8577  itaquaquecetuba   \n",
       "4             4  office_furniture                   8577  itaquaquecetuba   \n",
       "\n",
       "   seller_state  approval_timespan  \n",
       "0            SP    0 days 00:16:37  \n",
       "1            SP    0 days 23:57:35  \n",
       "2            SP    0 days 23:57:35  \n",
       "3            SP    1 days 02:11:30  \n",
       "4            SP    1 days 02:43:39  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 191,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['approval_timespan']=df['order_approved_at']-df['order_purchase_timestamp']\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "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",
       "      <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>...</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>payment_installments</th>\n",
       "      <th>payment_value</th>\n",
       "      <th>review_score</th>\n",
       "      <th>product_name</th>\n",
       "      <th>seller_zip_code_prefix</th>\n",
       "      <th>seller_city</th>\n",
       "      <th>seller_state</th>\n",
       "      <th>approval_timespan</th>\n",
       "      <th>delivery_timespan</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",
       "      <td>00e7ee1b050b8499577073aeb2a297a1</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-16 15:05:35</td>\n",
       "      <td>2017-05-16 15:22:12</td>\n",
       "      <td>2017-05-23 10:47:57</td>\n",
       "      <td>...</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>2</td>\n",
       "      <td>146.87</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "      <td>0 days 00:16:37</td>\n",
       "      <td>8 days 19:13:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8912fc0c3bbf1e2fbf35819e21706718</td>\n",
       "      <td>9eae34bbd3a474ec5d07949ca7de67c0</td>\n",
       "      <td>68030</td>\n",
       "      <td>santarem</td>\n",
       "      <td>PA</td>\n",
       "      <td>c1d2b34febe9cd269e378117d6681172</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-09 00:50:13</td>\n",
       "      <td>2017-11-10 00:47:48</td>\n",
       "      <td>2017-11-22 01:43:37</td>\n",
       "      <td>...</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>275.79</td>\n",
       "      <td>1</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "      <td>0 days 23:57:35</td>\n",
       "      <td>17 days 23:22:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8912fc0c3bbf1e2fbf35819e21706718</td>\n",
       "      <td>9eae34bbd3a474ec5d07949ca7de67c0</td>\n",
       "      <td>68030</td>\n",
       "      <td>santarem</td>\n",
       "      <td>PA</td>\n",
       "      <td>c1d2b34febe9cd269e378117d6681172</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-09 00:50:13</td>\n",
       "      <td>2017-11-10 00:47:48</td>\n",
       "      <td>2017-11-22 01:43:37</td>\n",
       "      <td>...</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>275.79</td>\n",
       "      <td>1</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "      <td>0 days 23:57:35</td>\n",
       "      <td>17 days 23:22:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f0ac8e5a239118859b1734e1087cbb1f</td>\n",
       "      <td>3c799d181c34d51f6d44bbbc563024db</td>\n",
       "      <td>92480</td>\n",
       "      <td>nova santa rita</td>\n",
       "      <td>RS</td>\n",
       "      <td>b1a5d5365d330d10485e0203d54ab9e8</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-07 20:11:26</td>\n",
       "      <td>2017-05-08 22:22:56</td>\n",
       "      <td>2017-05-19 20:16:31</td>\n",
       "      <td>...</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>7</td>\n",
       "      <td>140.61</td>\n",
       "      <td>3</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "      <td>1 days 02:11:30</td>\n",
       "      <td>17 days 11:31:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6bc8d08963a135220ed6c6d098831f84</td>\n",
       "      <td>23397e992b09769faf5e66f9e171a241</td>\n",
       "      <td>25931</td>\n",
       "      <td>mage</td>\n",
       "      <td>RJ</td>\n",
       "      <td>2e604b3614664aa66867856dba7e61b7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-03 19:45:40</td>\n",
       "      <td>2018-02-04 22:29:19</td>\n",
       "      <td>2018-02-19 18:21:47</td>\n",
       "      <td>...</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>10</td>\n",
       "      <td>137.58</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "      <td>1 days 02:43:39</td>\n",
       "      <td>23 days 22:39:41</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        customer_id                customer_unique_id  \\\n",
       "0  06b8999e2fba1a1fbc88172c00ba8bc7  861eff4711a542e4b93843c6dd7febb0   \n",
       "1  8912fc0c3bbf1e2fbf35819e21706718  9eae34bbd3a474ec5d07949ca7de67c0   \n",
       "2  8912fc0c3bbf1e2fbf35819e21706718  9eae34bbd3a474ec5d07949ca7de67c0   \n",
       "3  f0ac8e5a239118859b1734e1087cbb1f  3c799d181c34d51f6d44bbbc563024db   \n",
       "4  6bc8d08963a135220ed6c6d098831f84  23397e992b09769faf5e66f9e171a241   \n",
       "\n",
       "   customer_zip_code_prefix    customer_city customer_state  \\\n",
       "0                     14409           franca             SP   \n",
       "1                     68030         santarem             PA   \n",
       "2                     68030         santarem             PA   \n",
       "3                     92480  nova santa rita             RS   \n",
       "4                     25931             mage             RJ   \n",
       "\n",
       "                           order_id order_status order_purchase_timestamp  \\\n",
       "0  00e7ee1b050b8499577073aeb2a297a1    delivered      2017-05-16 15:05:35   \n",
       "1  c1d2b34febe9cd269e378117d6681172    delivered      2017-11-09 00:50:13   \n",
       "2  c1d2b34febe9cd269e378117d6681172    delivered      2017-11-09 00:50:13   \n",
       "3  b1a5d5365d330d10485e0203d54ab9e8    delivered      2017-05-07 20:11:26   \n",
       "4  2e604b3614664aa66867856dba7e61b7    delivered      2018-02-03 19:45:40   \n",
       "\n",
       "    order_approved_at order_delivered_carrier_date        ...          \\\n",
       "0 2017-05-16 15:22:12          2017-05-23 10:47:57        ...           \n",
       "1 2017-11-10 00:47:48          2017-11-22 01:43:37        ...           \n",
       "2 2017-11-10 00:47:48          2017-11-22 01:43:37        ...           \n",
       "3 2017-05-08 22:22:56          2017-05-19 20:16:31        ...           \n",
       "4 2018-02-04 22:29:19          2018-02-19 18:21:47        ...           \n",
       "\n",
       "  payment_type payment_installments payment_value  review_score  \\\n",
       "0  credit_card                    2        146.87             4   \n",
       "1  credit_card                    1        275.79             1   \n",
       "2  credit_card                    1        275.79             1   \n",
       "3  credit_card                    7        140.61             3   \n",
       "4  credit_card                   10        137.58             4   \n",
       "\n",
       "       product_name  seller_zip_code_prefix      seller_city  seller_state  \\\n",
       "0  office_furniture                    8577  itaquaquecetuba            SP   \n",
       "1  office_furniture                    8577  itaquaquecetuba            SP   \n",
       "2  office_furniture                    8577  itaquaquecetuba            SP   \n",
       "3  office_furniture                    8577  itaquaquecetuba            SP   \n",
       "4  office_furniture                    8577  itaquaquecetuba            SP   \n",
       "\n",
       "   approval_timespan  delivery_timespan  \n",
       "0    0 days 00:16:37    8 days 19:13:23  \n",
       "1    0 days 23:57:35   17 days 23:22:02  \n",
       "2    0 days 23:57:35   17 days 23:22:02  \n",
       "3    1 days 02:11:30   17 days 11:31:08  \n",
       "4    1 days 02:43:39   23 days 22:39:41  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 192,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['delivery_timespan']=df['order_delivered_customer_date']-df['order_approved_at']\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "metadata": {},
   "outputs": [],
   "source": [
    "def order_on_time(estimated_date, delivery_date):\n",
    "    if estimated_date > delivery_date:\n",
    "        value = 'Yes'\n",
    "    else:\n",
    "        value = 'No'\n",
    "    return value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "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",
       "      <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>...</th>\n",
       "      <th>payment_installments</th>\n",
       "      <th>payment_value</th>\n",
       "      <th>review_score</th>\n",
       "      <th>product_name</th>\n",
       "      <th>seller_zip_code_prefix</th>\n",
       "      <th>seller_city</th>\n",
       "      <th>seller_state</th>\n",
       "      <th>approval_timespan</th>\n",
       "      <th>delivery_timespan</th>\n",
       "      <th>order_on_time</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",
       "      <td>00e7ee1b050b8499577073aeb2a297a1</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-16 15:05:35</td>\n",
       "      <td>2017-05-16 15:22:12</td>\n",
       "      <td>2017-05-23 10:47:57</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>146.87</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "      <td>0 days 00:16:37</td>\n",
       "      <td>8 days 19:13:23</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8912fc0c3bbf1e2fbf35819e21706718</td>\n",
       "      <td>9eae34bbd3a474ec5d07949ca7de67c0</td>\n",
       "      <td>68030</td>\n",
       "      <td>santarem</td>\n",
       "      <td>PA</td>\n",
       "      <td>c1d2b34febe9cd269e378117d6681172</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-09 00:50:13</td>\n",
       "      <td>2017-11-10 00:47:48</td>\n",
       "      <td>2017-11-22 01:43:37</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>275.79</td>\n",
       "      <td>1</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "      <td>0 days 23:57:35</td>\n",
       "      <td>17 days 23:22:02</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8912fc0c3bbf1e2fbf35819e21706718</td>\n",
       "      <td>9eae34bbd3a474ec5d07949ca7de67c0</td>\n",
       "      <td>68030</td>\n",
       "      <td>santarem</td>\n",
       "      <td>PA</td>\n",
       "      <td>c1d2b34febe9cd269e378117d6681172</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-09 00:50:13</td>\n",
       "      <td>2017-11-10 00:47:48</td>\n",
       "      <td>2017-11-22 01:43:37</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>275.79</td>\n",
       "      <td>1</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "      <td>0 days 23:57:35</td>\n",
       "      <td>17 days 23:22:02</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f0ac8e5a239118859b1734e1087cbb1f</td>\n",
       "      <td>3c799d181c34d51f6d44bbbc563024db</td>\n",
       "      <td>92480</td>\n",
       "      <td>nova santa rita</td>\n",
       "      <td>RS</td>\n",
       "      <td>b1a5d5365d330d10485e0203d54ab9e8</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-05-07 20:11:26</td>\n",
       "      <td>2017-05-08 22:22:56</td>\n",
       "      <td>2017-05-19 20:16:31</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>140.61</td>\n",
       "      <td>3</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "      <td>1 days 02:11:30</td>\n",
       "      <td>17 days 11:31:08</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6bc8d08963a135220ed6c6d098831f84</td>\n",
       "      <td>23397e992b09769faf5e66f9e171a241</td>\n",
       "      <td>25931</td>\n",
       "      <td>mage</td>\n",
       "      <td>RJ</td>\n",
       "      <td>2e604b3614664aa66867856dba7e61b7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-03 19:45:40</td>\n",
       "      <td>2018-02-04 22:29:19</td>\n",
       "      <td>2018-02-19 18:21:47</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>137.58</td>\n",
       "      <td>4</td>\n",
       "      <td>office_furniture</td>\n",
       "      <td>8577</td>\n",
       "      <td>itaquaquecetuba</td>\n",
       "      <td>SP</td>\n",
       "      <td>1 days 02:43:39</td>\n",
       "      <td>23 days 22:39:41</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        customer_id                customer_unique_id  \\\n",
       "0  06b8999e2fba1a1fbc88172c00ba8bc7  861eff4711a542e4b93843c6dd7febb0   \n",
       "1  8912fc0c3bbf1e2fbf35819e21706718  9eae34bbd3a474ec5d07949ca7de67c0   \n",
       "2  8912fc0c3bbf1e2fbf35819e21706718  9eae34bbd3a474ec5d07949ca7de67c0   \n",
       "3  f0ac8e5a239118859b1734e1087cbb1f  3c799d181c34d51f6d44bbbc563024db   \n",
       "4  6bc8d08963a135220ed6c6d098831f84  23397e992b09769faf5e66f9e171a241   \n",
       "\n",
       "   customer_zip_code_prefix    customer_city customer_state  \\\n",
       "0                     14409           franca             SP   \n",
       "1                     68030         santarem             PA   \n",
       "2                     68030         santarem             PA   \n",
       "3                     92480  nova santa rita             RS   \n",
       "4                     25931             mage             RJ   \n",
       "\n",
       "                           order_id order_status order_purchase_timestamp  \\\n",
       "0  00e7ee1b050b8499577073aeb2a297a1    delivered      2017-05-16 15:05:35   \n",
       "1  c1d2b34febe9cd269e378117d6681172    delivered      2017-11-09 00:50:13   \n",
       "2  c1d2b34febe9cd269e378117d6681172    delivered      2017-11-09 00:50:13   \n",
       "3  b1a5d5365d330d10485e0203d54ab9e8    delivered      2017-05-07 20:11:26   \n",
       "4  2e604b3614664aa66867856dba7e61b7    delivered      2018-02-03 19:45:40   \n",
       "\n",
       "    order_approved_at order_delivered_carrier_date      ...        \\\n",
       "0 2017-05-16 15:22:12          2017-05-23 10:47:57      ...         \n",
       "1 2017-11-10 00:47:48          2017-11-22 01:43:37      ...         \n",
       "2 2017-11-10 00:47:48          2017-11-22 01:43:37      ...         \n",
       "3 2017-05-08 22:22:56          2017-05-19 20:16:31      ...         \n",
       "4 2018-02-04 22:29:19          2018-02-19 18:21:47      ...         \n",
       "\n",
       "  payment_installments payment_value review_score      product_name  \\\n",
       "0                    2        146.87            4  office_furniture   \n",
       "1                    1        275.79            1  office_furniture   \n",
       "2                    1        275.79            1  office_furniture   \n",
       "3                    7        140.61            3  office_furniture   \n",
       "4                   10        137.58            4  office_furniture   \n",
       "\n",
       "   seller_zip_code_prefix      seller_city seller_state  approval_timespan  \\\n",
       "0                    8577  itaquaquecetuba           SP    0 days 00:16:37   \n",
       "1                    8577  itaquaquecetuba           SP    0 days 23:57:35   \n",
       "2                    8577  itaquaquecetuba           SP    0 days 23:57:35   \n",
       "3                    8577  itaquaquecetuba           SP    1 days 02:11:30   \n",
       "4                    8577  itaquaquecetuba           SP    1 days 02:43:39   \n",
       "\n",
       "   delivery_timespan  order_on_time  \n",
       "0    8 days 19:13:23            Yes  \n",
       "1   17 days 23:22:02            Yes  \n",
       "2   17 days 23:22:02            Yes  \n",
       "3   17 days 11:31:08            Yes  \n",
       "4   23 days 22:39:41            Yes  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['order_on_time'] = df.apply(lambda x: order_on_time(x['order_estimated_delivery_date'], x['order_delivered_customer_date']), axis=1)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save DataFrame as 'orders_dataset'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('orders_dataset.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}