Notebooks/Data manipulation and combining.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1><center>Joining data</center></h1>\n",
    "<h3><center>Joining data from Olist database for analysis</center></h3>\n",
    "\n",
    "Olist is a Brazilian E-Commerce giant. It sells products of all categories from furniture to electronics. Olist published their database of customers and orders placed between 2016 and 2018. The database consists of multiple files giving cutomer locations, seller locations, orders, payments and shipping details - <a href=\"https://www.kaggle.com/olistbr/brazilian-ecommerce?select=olist_orders_dataset.csv\">Link</a><br>\n",
    "\n",
    "These files containing various data has to be joined to perform meaningful analysis on them. This notebook containts code for combining the files and get the data ready for descriptive and predictive analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from googletrans import Translator"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Customer data\n",
    "\n",
    "The olist_customers_dataset contains location informations of the customer. customer_unique_id is a unique id identifying customers in the system. It is an id generated at the time of Signup. The customer_id is a temporary id generated everytime the customer places an order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>customer_zip_code_prefix</th>\n",
       "      <th>customer_city</th>\n",
       "      <th>customer_state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>06b8999e2fba1a1fbc88172c00ba8bc7</td>\n",
       "      <td>861eff4711a542e4b93843c6dd7febb0</td>\n",
       "      <td>14409</td>\n",
       "      <td>franca</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18955e83d337fd6b2def6b18a428ac77</td>\n",
       "      <td>290c77bc529b7ac935b93aa66c333dc3</td>\n",
       "      <td>9790</td>\n",
       "      <td>sao bernardo do campo</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4e7b3e00288586ebd08712fdd0374a03</td>\n",
       "      <td>060e732b5b29e8181a18229c7b0b2b5e</td>\n",
       "      <td>1151</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b2b6027bc5c5109e529d4dc6358b12c3</td>\n",
       "      <td>259dac757896d24d7702b9acbbff3f3c</td>\n",
       "      <td>8775</td>\n",
       "      <td>mogi das cruzes</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4f2d8ab171c80ec8364f7c12e35b23ad</td>\n",
       "      <td>345ecd01c38d18a9036ed96c73b8d066</td>\n",
       "      <td>13056</td>\n",
       "      <td>campinas</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        customer_id                customer_unique_id  \\\n",
       "0  06b8999e2fba1a1fbc88172c00ba8bc7  861eff4711a542e4b93843c6dd7febb0   \n",
       "1  18955e83d337fd6b2def6b18a428ac77  290c77bc529b7ac935b93aa66c333dc3   \n",
       "2  4e7b3e00288586ebd08712fdd0374a03  060e732b5b29e8181a18229c7b0b2b5e   \n",
       "3  b2b6027bc5c5109e529d4dc6358b12c3  259dac757896d24d7702b9acbbff3f3c   \n",
       "4  4f2d8ab171c80ec8364f7c12e35b23ad  345ecd01c38d18a9036ed96c73b8d066   \n",
       "\n",
       "   customer_zip_code_prefix          customer_city customer_state  \n",
       "0                     14409                 franca             SP  \n",
       "1                      9790  sao bernardo do campo             SP  \n",
       "2                      1151              sao paulo             SP  \n",
       "3                      8775        mogi das cruzes             SP  \n",
       "4                     13056               campinas             SP  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cust = pd.read_csv('/home/raj/Downloads/Olist-Ecomm data/olist_customers_dataset.csv')\n",
    "cust.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The olist_geolocation_dataset contains latitude and longitude points, city and state for the zipcodes. Every customer in the customer dataset has a zipcode associated as their location within the state and city."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>geolocation_zip_code_prefix</th>\n",
       "      <th>geolocation_lat</th>\n",
       "      <th>geolocation_lng</th>\n",
       "      <th>geolocation_city</th>\n",
       "      <th>geolocation_state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1037</td>\n",
       "      <td>-23.545621</td>\n",
       "      <td>-46.639292</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1046</td>\n",
       "      <td>-23.546081</td>\n",
       "      <td>-46.644820</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1046</td>\n",
       "      <td>-23.546129</td>\n",
       "      <td>-46.642951</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1041</td>\n",
       "      <td>-23.544392</td>\n",
       "      <td>-46.639499</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1035</td>\n",
       "      <td>-23.541578</td>\n",
       "      <td>-46.641607</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   geolocation_zip_code_prefix  geolocation_lat  geolocation_lng  \\\n",
       "0                         1037       -23.545621       -46.639292   \n",
       "1                         1046       -23.546081       -46.644820   \n",
       "2                         1046       -23.546129       -46.642951   \n",
       "3                         1041       -23.544392       -46.639499   \n",
       "4                         1035       -23.541578       -46.641607   \n",
       "\n",
       "  geolocation_city geolocation_state  \n",
       "0        sao paulo                SP  \n",
       "1        sao paulo                SP  \n",
       "2        sao paulo                SP  \n",
       "3        sao paulo                SP  \n",
       "4        sao paulo                SP  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geoloc = pd.read_csv('/home/raj/Downloads/Olist-Ecomm data/olist_geolocation_dataset.csv')\n",
    "geoloc.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The customer dataset and geolocation dataset are joined on zipcodes. Since one zipcode has multiple locations, the data is grouped on zipcode, and a centroid of all latitude and longitude points for that zipcode is reported. This way customers from one zipcode are mapped to one latitude and longitude point."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>geolocation_lat</th>\n",
       "      <th>geolocation_lng</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>-20.498489</td>\n",
       "      <td>-47.396929</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>-23.727992</td>\n",
       "      <td>-46.542848</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>-23.531642</td>\n",
       "      <td>-46.656289</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>-23.499702</td>\n",
       "      <td>-46.185233</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>-22.975100</td>\n",
       "      <td>-47.142925</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",
       "   geolocation_lat  geolocation_lng  \n",
       "0       -20.498489       -47.396929  \n",
       "1       -23.727992       -46.542848  \n",
       "2       -23.531642       -46.656289  \n",
       "3       -23.499702       -46.185233  \n",
       "4       -22.975100       -47.142925  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loc_coordnts = geoloc[[\n",
    "    'geolocation_zip_code_prefix',\n",
    "    'geolocation_lat',\n",
    "    'geolocation_lng'\n",
    "]].groupby('geolocation_zip_code_prefix').mean().reset_index()\n",
    "\n",
    "cols= [\n",
    "    'customer_id',\n",
    "    'customer_unique_id',\n",
    "    'customer_zip_code_prefix',\n",
    "    'customer_city',\n",
    "    'customer_state',\n",
    "    'geolocation_lat',\n",
    "    'geolocation_lng'\n",
    "]\n",
    "\n",
    "customer_data = pd.merge(\n",
    "    left= cust,\n",
    "    right= loc_coordnts,\n",
    "    left_on= 'customer_zip_code_prefix',\n",
    "    right_on= 'geolocation_zip_code_prefix',\n",
    "    how= 'left',\n",
    ")[cols]\n",
    "\n",
    "customer_data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This combined data gives the location (latitude and longitude) of the customer at the time of order placement narrowed down to the centroid of zipcode."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [],
   "source": [
    "customer_data.to_csv('./Olist_data/customer_data.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Order data\n",
    "\n",
    "The olist_orders_dataset contains mapping of temporary customer_id and the order_id. A new customer_id is generated everytime an order is placed at the same time, a new order_id is generated. Apart from the mapping of customer to the order, the dataset contains timestamps for various milestones during delivery of the product such as purchase date, approval date, shipping date, local carrier date, delivery date and finally as estimated delivery date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "scrolled": true
   },
   "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 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>53cdb2fc8bc7dce0b6741e2150273451</td>\n",
       "      <td>b0830fb4747a6c6d20dea0b8c802d7ef</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-07-24 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 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>47770eb9100c2d0c44946d9cf07ec65d</td>\n",
       "      <td>41ce2a54c0b03bf3443c3d931a367089</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-08-08 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 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>949d5b44dbf5de918fe9c16f97b45f8a</td>\n",
       "      <td>f88197465ea7920adcdbec7375364d82</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-18 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 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ad21c59c0840e6cb83a9ceb5573f8159</td>\n",
       "      <td>8ab97904e6daea8866dbdbc4fb7aad2c</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-13 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 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           order_id                       customer_id  \\\n",
       "0  e481f51cbdc54678b7cc49136f2d6af7  9ef432eb6251297304e76186b10a928d   \n",
       "1  53cdb2fc8bc7dce0b6741e2150273451  b0830fb4747a6c6d20dea0b8c802d7ef   \n",
       "2  47770eb9100c2d0c44946d9cf07ec65d  41ce2a54c0b03bf3443c3d931a367089   \n",
       "3  949d5b44dbf5de918fe9c16f97b45f8a  f88197465ea7920adcdbec7375364d82   \n",
       "4  ad21c59c0840e6cb83a9ceb5573f8159  8ab97904e6daea8866dbdbc4fb7aad2c   \n",
       "\n",
       "  order_status order_purchase_timestamp    order_approved_at  \\\n",
       "0    delivered      2017-10-02 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 00:00:00  \n",
       "1           2018-08-13 00:00:00  \n",
       "2           2018-09-04 00:00:00  \n",
       "3           2017-12-15 00:00:00  \n",
       "4           2018-02-26 00:00:00  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders = pd.read_csv('/home/raj/Downloads/Olist-Ecomm data/olist_orders_dataset.csv')\n",
    "orders.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The olist_order_items_dataset contains a mapping of the order_id and the products bought via product_id. For multiple items bought together or same item bought in quantity more than 1, order_id is repeated for that many rows with a sequence number order_item_id becoming the primary key. The dataset contains price of each product and freight value for it. The seller who has listed the product and the deadline for shipment is also recorded individually for each product."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_item_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>seller_id</th>\n",
       "      <th>shipping_limit_date</th>\n",
       "      <th>price</th>\n",
       "      <th>freight_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00010242fe8c5a6d1ba2dd792cb16214</td>\n",
       "      <td>1</td>\n",
       "      <td>4244733e06e7ecb4970a6e2683c13e61</td>\n",
       "      <td>48436dade18ac8b2bce089ec2a041202</td>\n",
       "      <td>2017-09-19 09:45:35</td>\n",
       "      <td>58.90</td>\n",
       "      <td>13.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00018f77f2f0320c557190d7a144bdd3</td>\n",
       "      <td>1</td>\n",
       "      <td>e5f2d52b802189ee658865ca93d83a8f</td>\n",
       "      <td>dd7ddc04e1b6c2c614352b383efe2d36</td>\n",
       "      <td>2017-05-03 11:05:13</td>\n",
       "      <td>239.90</td>\n",
       "      <td>19.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000229ec398224ef6ca0657da4fc703e</td>\n",
       "      <td>1</td>\n",
       "      <td>c777355d18b72b67abbeef9df44fd0fd</td>\n",
       "      <td>5b51032eddd242adc84c38acab88f23d</td>\n",
       "      <td>2018-01-18 14:48:30</td>\n",
       "      <td>199.00</td>\n",
       "      <td>17.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00024acbcdf0a6daa1e931b038114c75</td>\n",
       "      <td>1</td>\n",
       "      <td>7634da152a4610f1595efa32f14722fc</td>\n",
       "      <td>9d7a1d34a5052409006425275ba1c2b4</td>\n",
       "      <td>2018-08-15 10:10:18</td>\n",
       "      <td>12.99</td>\n",
       "      <td>12.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00042b26cf59d7ce69dfabb4e55b4fd9</td>\n",
       "      <td>1</td>\n",
       "      <td>ac6c3623068f30de03045865e4e10089</td>\n",
       "      <td>df560393f3a51e74553ab94004ba5c87</td>\n",
       "      <td>2017-02-13 13:57:51</td>\n",
       "      <td>199.90</td>\n",
       "      <td>18.14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           order_id  order_item_id  \\\n",
       "0  00010242fe8c5a6d1ba2dd792cb16214              1   \n",
       "1  00018f77f2f0320c557190d7a144bdd3              1   \n",
       "2  000229ec398224ef6ca0657da4fc703e              1   \n",
       "3  00024acbcdf0a6daa1e931b038114c75              1   \n",
       "4  00042b26cf59d7ce69dfabb4e55b4fd9              1   \n",
       "\n",
       "                         product_id                         seller_id  \\\n",
       "0  4244733e06e7ecb4970a6e2683c13e61  48436dade18ac8b2bce089ec2a041202   \n",
       "1  e5f2d52b802189ee658865ca93d83a8f  dd7ddc04e1b6c2c614352b383efe2d36   \n",
       "2  c777355d18b72b67abbeef9df44fd0fd  5b51032eddd242adc84c38acab88f23d   \n",
       "3  7634da152a4610f1595efa32f14722fc  9d7a1d34a5052409006425275ba1c2b4   \n",
       "4  ac6c3623068f30de03045865e4e10089  df560393f3a51e74553ab94004ba5c87   \n",
       "\n",
       "   shipping_limit_date   price  freight_value  \n",
       "0  2017-09-19 09:45:35   58.90          13.29  \n",
       "1  2017-05-03 11:05:13  239.90          19.93  \n",
       "2  2018-01-18 14:48:30  199.00          17.87  \n",
       "3  2018-08-15 10:10:18   12.99          12.79  \n",
       "4  2017-02-13 13:57:51  199.90          18.14  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ord_items = pd.read_csv('/home/raj/Downloads/Olist-Ecomm data/olist_order_items_dataset.csv')\n",
    "ord_items.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The two datasets above are combined on order_id to get a mapping of customer to the products purchased and the price of the products. The timestamp of the purchase date and freight value are also taken. This new combined dataset gives information on customer's orders - what was purchased? How much was it for? and when was it purchased?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>order_item_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>price</th>\n",
       "      <th>freight_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>e481f51cbdc54678b7cc49136f2d6af7</td>\n",
       "      <td>9ef432eb6251297304e76186b10a928d</td>\n",
       "      <td>2017-10-02 10:56:33</td>\n",
       "      <td>1</td>\n",
       "      <td>87285b34884572647811a353c7ac498a</td>\n",
       "      <td>29.99</td>\n",
       "      <td>8.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>53cdb2fc8bc7dce0b6741e2150273451</td>\n",
       "      <td>b0830fb4747a6c6d20dea0b8c802d7ef</td>\n",
       "      <td>2018-07-24 20:41:37</td>\n",
       "      <td>1</td>\n",
       "      <td>595fac2a385ac33a80bd5114aec74eb8</td>\n",
       "      <td>118.70</td>\n",
       "      <td>22.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>47770eb9100c2d0c44946d9cf07ec65d</td>\n",
       "      <td>41ce2a54c0b03bf3443c3d931a367089</td>\n",
       "      <td>2018-08-08 08:38:49</td>\n",
       "      <td>1</td>\n",
       "      <td>aa4383b373c6aca5d8797843e5594415</td>\n",
       "      <td>159.90</td>\n",
       "      <td>19.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>949d5b44dbf5de918fe9c16f97b45f8a</td>\n",
       "      <td>f88197465ea7920adcdbec7375364d82</td>\n",
       "      <td>2017-11-18 19:28:06</td>\n",
       "      <td>1</td>\n",
       "      <td>d0b61bfb1de832b15ba9d266ca96e5b0</td>\n",
       "      <td>45.00</td>\n",
       "      <td>27.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ad21c59c0840e6cb83a9ceb5573f8159</td>\n",
       "      <td>8ab97904e6daea8866dbdbc4fb7aad2c</td>\n",
       "      <td>2018-02-13 21:18:39</td>\n",
       "      <td>1</td>\n",
       "      <td>65266b2da20d04dbe00c5c2d3bb7859e</td>\n",
       "      <td>19.90</td>\n",
       "      <td>8.72</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_purchase_timestamp  order_item_id                        product_id  \\\n",
       "0      2017-10-02 10:56:33              1  87285b34884572647811a353c7ac498a   \n",
       "1      2018-07-24 20:41:37              1  595fac2a385ac33a80bd5114aec74eb8   \n",
       "2      2018-08-08 08:38:49              1  aa4383b373c6aca5d8797843e5594415   \n",
       "3      2017-11-18 19:28:06              1  d0b61bfb1de832b15ba9d266ca96e5b0   \n",
       "4      2018-02-13 21:18:39              1  65266b2da20d04dbe00c5c2d3bb7859e   \n",
       "\n",
       "    price  freight_value  \n",
       "0   29.99           8.72  \n",
       "1  118.70          22.76  \n",
       "2  159.90          19.22  \n",
       "3   45.00          27.20  \n",
       "4   19.90           8.72  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols= [\n",
    "    'order_id',\n",
    "    'customer_id',\n",
    "    'order_purchase_timestamp',\n",
    "    'order_item_id',\n",
    "    'product_id',\n",
    "    'price',\n",
    "    'freight_value'\n",
    "]\n",
    "\n",
    "complete_order = pd.merge(\n",
    "    left= orders,\n",
    "    right= ord_items,\n",
    "    on= 'order_id',\n",
    "    how= 'inner'\n",
    ")[cols]\n",
    "\n",
    "complete_order.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above dataset is combined with the customer dataset to get a mapping of the customer_unique_id and the orders joined on customer_id. This gives a clear mapping of every customer and the orders the customers have placed over the duration of 2 years (2016 - 1028)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>order_item_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>price</th>\n",
       "      <th>freight_value</th>\n",
       "      <th>customer_unique_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>e481f51cbdc54678b7cc49136f2d6af7</td>\n",
       "      <td>2017-10-02 10:56:33</td>\n",
       "      <td>1</td>\n",
       "      <td>87285b34884572647811a353c7ac498a</td>\n",
       "      <td>29.99</td>\n",
       "      <td>8.72</td>\n",
       "      <td>7c396fd4830fd04220f754e42b4e5bff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>53cdb2fc8bc7dce0b6741e2150273451</td>\n",
       "      <td>2018-07-24 20:41:37</td>\n",
       "      <td>1</td>\n",
       "      <td>595fac2a385ac33a80bd5114aec74eb8</td>\n",
       "      <td>118.70</td>\n",
       "      <td>22.76</td>\n",
       "      <td>af07308b275d755c9edb36a90c618231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>47770eb9100c2d0c44946d9cf07ec65d</td>\n",
       "      <td>2018-08-08 08:38:49</td>\n",
       "      <td>1</td>\n",
       "      <td>aa4383b373c6aca5d8797843e5594415</td>\n",
       "      <td>159.90</td>\n",
       "      <td>19.22</td>\n",
       "      <td>3a653a41f6f9fc3d2a113cf8398680e8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>949d5b44dbf5de918fe9c16f97b45f8a</td>\n",
       "      <td>2017-11-18 19:28:06</td>\n",
       "      <td>1</td>\n",
       "      <td>d0b61bfb1de832b15ba9d266ca96e5b0</td>\n",
       "      <td>45.00</td>\n",
       "      <td>27.20</td>\n",
       "      <td>7c142cf63193a1473d2e66489a9ae977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ad21c59c0840e6cb83a9ceb5573f8159</td>\n",
       "      <td>2018-02-13 21:18:39</td>\n",
       "      <td>1</td>\n",
       "      <td>65266b2da20d04dbe00c5c2d3bb7859e</td>\n",
       "      <td>19.90</td>\n",
       "      <td>8.72</td>\n",
       "      <td>72632f0f9dd73dfee390c9b22eb56dd6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           order_id order_purchase_timestamp  order_item_id  \\\n",
       "0  e481f51cbdc54678b7cc49136f2d6af7      2017-10-02 10:56:33              1   \n",
       "1  53cdb2fc8bc7dce0b6741e2150273451      2018-07-24 20:41:37              1   \n",
       "2  47770eb9100c2d0c44946d9cf07ec65d      2018-08-08 08:38:49              1   \n",
       "3  949d5b44dbf5de918fe9c16f97b45f8a      2017-11-18 19:28:06              1   \n",
       "4  ad21c59c0840e6cb83a9ceb5573f8159      2018-02-13 21:18:39              1   \n",
       "\n",
       "                         product_id   price  freight_value  \\\n",
       "0  87285b34884572647811a353c7ac498a   29.99           8.72   \n",
       "1  595fac2a385ac33a80bd5114aec74eb8  118.70          22.76   \n",
       "2  aa4383b373c6aca5d8797843e5594415  159.90          19.22   \n",
       "3  d0b61bfb1de832b15ba9d266ca96e5b0   45.00          27.20   \n",
       "4  65266b2da20d04dbe00c5c2d3bb7859e   19.90           8.72   \n",
       "\n",
       "                 customer_unique_id  \n",
       "0  7c396fd4830fd04220f754e42b4e5bff  \n",
       "1  af07308b275d755c9edb36a90c618231  \n",
       "2  3a653a41f6f9fc3d2a113cf8398680e8  \n",
       "3  7c142cf63193a1473d2e66489a9ae977  \n",
       "4  72632f0f9dd73dfee390c9b22eb56dd6  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = [\n",
    "    'order_id',\n",
    "    'order_purchase_timestamp',\n",
    "    'order_item_id',\n",
    "    'product_id',\n",
    "    'price',\n",
    "    'freight_value',\n",
    "    'customer_unique_id'\n",
    "]\n",
    "\n",
    "cust_orders = pd.merge(\n",
    "    left= complete_order,\n",
    "    right= cust,\n",
    "    on= 'customer_id',\n",
    "    how= 'inner'\n",
    ")[cols]\n",
    "\n",
    "cust_orders.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {},
   "outputs": [],
   "source": [
    "cust_orders.to_csv('./Olist_data/customer_order.csv', index= False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Product category data\n",
    "\n",
    "For every product listed on the website, a unique product_id is given. The database doesnot contain the name of the products rather just the category of the product is given along with dimensions of the product and other descriptive statistics. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product_id</th>\n",
       "      <th>product_category_name</th>\n",
       "      <th>product_name_lenght</th>\n",
       "      <th>product_description_lenght</th>\n",
       "      <th>product_photos_qty</th>\n",
       "      <th>product_weight_g</th>\n",
       "      <th>product_length_cm</th>\n",
       "      <th>product_height_cm</th>\n",
       "      <th>product_width_cm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1e9e8ef04dbcff4541ed26657ea517e5</td>\n",
       "      <td>perfumaria</td>\n",
       "      <td>40.0</td>\n",
       "      <td>287.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3aa071139cb16b67ca9e5dea641aaa2f</td>\n",
       "      <td>artes</td>\n",
       "      <td>44.0</td>\n",
       "      <td>276.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>96bd76ec8810374ed1b65e291975717f</td>\n",
       "      <td>esporte_lazer</td>\n",
       "      <td>46.0</td>\n",
       "      <td>250.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>cef67bcfe19066a932b7673e239eb23d</td>\n",
       "      <td>bebes</td>\n",
       "      <td>27.0</td>\n",
       "      <td>261.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>371.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9dc1a7de274444849c219cff195d0b71</td>\n",
       "      <td>utilidades_domesticas</td>\n",
       "      <td>37.0</td>\n",
       "      <td>402.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>625.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         product_id  product_category_name  \\\n",
       "0  1e9e8ef04dbcff4541ed26657ea517e5             perfumaria   \n",
       "1  3aa071139cb16b67ca9e5dea641aaa2f                  artes   \n",
       "2  96bd76ec8810374ed1b65e291975717f          esporte_lazer   \n",
       "3  cef67bcfe19066a932b7673e239eb23d                  bebes   \n",
       "4  9dc1a7de274444849c219cff195d0b71  utilidades_domesticas   \n",
       "\n",
       "   product_name_lenght  product_description_lenght  product_photos_qty  \\\n",
       "0                 40.0                       287.0                 1.0   \n",
       "1                 44.0                       276.0                 1.0   \n",
       "2                 46.0                       250.0                 1.0   \n",
       "3                 27.0                       261.0                 1.0   \n",
       "4                 37.0                       402.0                 4.0   \n",
       "\n",
       "   product_weight_g  product_length_cm  product_height_cm  product_width_cm  \n",
       "0             225.0               16.0               10.0              14.0  \n",
       "1            1000.0               30.0               18.0              20.0  \n",
       "2             154.0               18.0                9.0              15.0  \n",
       "3             371.0               26.0                4.0              26.0  \n",
       "4             625.0               20.0               17.0              13.0  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products = pd.read_csv('/home/raj/Downloads/Olist-Ecomm data/olist_products_dataset.csv')\n",
    "products.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A separate file gives the translation of the product category names from Portuguese to English."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product_category_name</th>\n",
       "      <th>product_category_name_english</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>beleza_saude</td>\n",
       "      <td>health_beauty</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>informatica_acessorios</td>\n",
       "      <td>computers_accessories</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>automotivo</td>\n",
       "      <td>auto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>cama_mesa_banho</td>\n",
       "      <td>bed_bath_table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>moveis_decoracao</td>\n",
       "      <td>furniture_decor</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    product_category_name product_category_name_english\n",
       "0            beleza_saude                 health_beauty\n",
       "1  informatica_acessorios         computers_accessories\n",
       "2              automotivo                          auto\n",
       "3         cama_mesa_banho                bed_bath_table\n",
       "4        moveis_decoracao               furniture_decor"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transln = pd.read_csv('/home/raj/Downloads/Olist-Ecomm data/product_category_name_translation.csv')\n",
    "transln.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The two datasets above our combined to get the product category names in English."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>product_id</th>\n",
       "      <th>product_category_name</th>\n",
       "      <th>product_name_lenght</th>\n",
       "      <th>product_description_lenght</th>\n",
       "      <th>product_photos_qty</th>\n",
       "      <th>product_weight_g</th>\n",
       "      <th>product_length_cm</th>\n",
       "      <th>product_height_cm</th>\n",
       "      <th>product_width_cm</th>\n",
       "      <th>product_category_name_english</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1e9e8ef04dbcff4541ed26657ea517e5</td>\n",
       "      <td>perfumaria</td>\n",
       "      <td>40.0</td>\n",
       "      <td>287.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>perfumery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3aa071139cb16b67ca9e5dea641aaa2f</td>\n",
       "      <td>artes</td>\n",
       "      <td>44.0</td>\n",
       "      <td>276.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>art</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>96bd76ec8810374ed1b65e291975717f</td>\n",
       "      <td>esporte_lazer</td>\n",
       "      <td>46.0</td>\n",
       "      <td>250.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>sports_leisure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>cef67bcfe19066a932b7673e239eb23d</td>\n",
       "      <td>bebes</td>\n",
       "      <td>27.0</td>\n",
       "      <td>261.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>371.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>baby</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9dc1a7de274444849c219cff195d0b71</td>\n",
       "      <td>utilidades_domesticas</td>\n",
       "      <td>37.0</td>\n",
       "      <td>402.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>625.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>housewares</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         product_id  product_category_name  \\\n",
       "0  1e9e8ef04dbcff4541ed26657ea517e5             perfumaria   \n",
       "1  3aa071139cb16b67ca9e5dea641aaa2f                  artes   \n",
       "2  96bd76ec8810374ed1b65e291975717f          esporte_lazer   \n",
       "3  cef67bcfe19066a932b7673e239eb23d                  bebes   \n",
       "4  9dc1a7de274444849c219cff195d0b71  utilidades_domesticas   \n",
       "\n",
       "   product_name_lenght  product_description_lenght  product_photos_qty  \\\n",
       "0                 40.0                       287.0                 1.0   \n",
       "1                 44.0                       276.0                 1.0   \n",
       "2                 46.0                       250.0                 1.0   \n",
       "3                 27.0                       261.0                 1.0   \n",
       "4                 37.0                       402.0                 4.0   \n",
       "\n",
       "   product_weight_g  product_length_cm  product_height_cm  product_width_cm  \\\n",
       "0             225.0               16.0               10.0              14.0   \n",
       "1            1000.0               30.0               18.0              20.0   \n",
       "2             154.0               18.0                9.0              15.0   \n",
       "3             371.0               26.0                4.0              26.0   \n",
       "4             625.0               20.0               17.0              13.0   \n",
       "\n",
       "  product_category_name_english  \n",
       "0                     perfumery  \n",
       "1                           art  \n",
       "2                sports_leisure  \n",
       "3                          baby  \n",
       "4                    housewares  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products = pd.merge(\n",
    "    left= products,\n",
    "    right= transln,\n",
    "    on= 'product_category_name',\n",
    "    how= 'left'\n",
    ")\n",
    "\n",
    "products.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This product information has to be now added to the customer order mapping created before this. The previously joined and created dataset which contains informations on orders of every customer is read in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>order_item_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>price</th>\n",
       "      <th>freight_value</th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>product_category_name_english</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>e481f51cbdc54678b7cc49136f2d6af7</td>\n",
       "      <td>2017-10-02 10:56:33</td>\n",
       "      <td>1</td>\n",
       "      <td>87285b34884572647811a353c7ac498a</td>\n",
       "      <td>29.99</td>\n",
       "      <td>8.72</td>\n",
       "      <td>7c396fd4830fd04220f754e42b4e5bff</td>\n",
       "      <td>housewares</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>128e10d95713541c87cd1a2e48201934</td>\n",
       "      <td>2017-08-15 18:29:31</td>\n",
       "      <td>1</td>\n",
       "      <td>87285b34884572647811a353c7ac498a</td>\n",
       "      <td>29.99</td>\n",
       "      <td>7.78</td>\n",
       "      <td>3a51803cc0d012c3b5dc8b7528cb05f7</td>\n",
       "      <td>housewares</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0e7e841ddf8f8f2de2bad69267ecfbcf</td>\n",
       "      <td>2017-08-02 18:24:47</td>\n",
       "      <td>1</td>\n",
       "      <td>87285b34884572647811a353c7ac498a</td>\n",
       "      <td>29.99</td>\n",
       "      <td>7.78</td>\n",
       "      <td>ef0996a1a279c26e7ecbd737be23d235</td>\n",
       "      <td>housewares</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bfc39df4f36c3693ff3b63fcbea9e90a</td>\n",
       "      <td>2017-10-23 23:26:46</td>\n",
       "      <td>1</td>\n",
       "      <td>87285b34884572647811a353c7ac498a</td>\n",
       "      <td>29.99</td>\n",
       "      <td>14.10</td>\n",
       "      <td>e781fdcc107d13d865fc7698711cc572</td>\n",
       "      <td>housewares</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53cdb2fc8bc7dce0b6741e2150273451</td>\n",
       "      <td>2018-07-24 20:41:37</td>\n",
       "      <td>1</td>\n",
       "      <td>595fac2a385ac33a80bd5114aec74eb8</td>\n",
       "      <td>118.70</td>\n",
       "      <td>22.76</td>\n",
       "      <td>af07308b275d755c9edb36a90c618231</td>\n",
       "      <td>perfumery</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           order_id order_purchase_timestamp  order_item_id  \\\n",
       "0  e481f51cbdc54678b7cc49136f2d6af7      2017-10-02 10:56:33              1   \n",
       "1  128e10d95713541c87cd1a2e48201934      2017-08-15 18:29:31              1   \n",
       "2  0e7e841ddf8f8f2de2bad69267ecfbcf      2017-08-02 18:24:47              1   \n",
       "3  bfc39df4f36c3693ff3b63fcbea9e90a      2017-10-23 23:26:46              1   \n",
       "4  53cdb2fc8bc7dce0b6741e2150273451      2018-07-24 20:41:37              1   \n",
       "\n",
       "                         product_id   price  freight_value  \\\n",
       "0  87285b34884572647811a353c7ac498a   29.99           8.72   \n",
       "1  87285b34884572647811a353c7ac498a   29.99           7.78   \n",
       "2  87285b34884572647811a353c7ac498a   29.99           7.78   \n",
       "3  87285b34884572647811a353c7ac498a   29.99          14.10   \n",
       "4  595fac2a385ac33a80bd5114aec74eb8  118.70          22.76   \n",
       "\n",
       "                 customer_unique_id product_category_name_english  \n",
       "0  7c396fd4830fd04220f754e42b4e5bff                    housewares  \n",
       "1  3a51803cc0d012c3b5dc8b7528cb05f7                    housewares  \n",
       "2  ef0996a1a279c26e7ecbd737be23d235                    housewares  \n",
       "3  e781fdcc107d13d865fc7698711cc572                    housewares  \n",
       "4  af07308b275d755c9edb36a90c618231                     perfumery  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cust_order = pd.read_csv('/home/raj/Github/Olist-business-analysis/Joined data/customer_order.csv')\n",
    "cust_order.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The customer orders dataset is combined with the products dataset to get information on the category of purchase/order by the customer. Now the new dataset contains information of every order in terms of product, product category, price and shipping costs of the product and the finally the timestamp of purchase."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>order_item_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>price</th>\n",
       "      <th>freight_value</th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>product_category_name_english_x</th>\n",
       "      <th>product_category_name_english_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>e481f51cbdc54678b7cc49136f2d6af7</td>\n",
       "      <td>2017-10-02 10:56:33</td>\n",
       "      <td>1</td>\n",
       "      <td>87285b34884572647811a353c7ac498a</td>\n",
       "      <td>29.99</td>\n",
       "      <td>8.72</td>\n",
       "      <td>7c396fd4830fd04220f754e42b4e5bff</td>\n",
       "      <td>housewares</td>\n",
       "      <td>housewares</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>128e10d95713541c87cd1a2e48201934</td>\n",
       "      <td>2017-08-15 18:29:31</td>\n",
       "      <td>1</td>\n",
       "      <td>87285b34884572647811a353c7ac498a</td>\n",
       "      <td>29.99</td>\n",
       "      <td>7.78</td>\n",
       "      <td>3a51803cc0d012c3b5dc8b7528cb05f7</td>\n",
       "      <td>housewares</td>\n",
       "      <td>housewares</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0e7e841ddf8f8f2de2bad69267ecfbcf</td>\n",
       "      <td>2017-08-02 18:24:47</td>\n",
       "      <td>1</td>\n",
       "      <td>87285b34884572647811a353c7ac498a</td>\n",
       "      <td>29.99</td>\n",
       "      <td>7.78</td>\n",
       "      <td>ef0996a1a279c26e7ecbd737be23d235</td>\n",
       "      <td>housewares</td>\n",
       "      <td>housewares</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bfc39df4f36c3693ff3b63fcbea9e90a</td>\n",
       "      <td>2017-10-23 23:26:46</td>\n",
       "      <td>1</td>\n",
       "      <td>87285b34884572647811a353c7ac498a</td>\n",
       "      <td>29.99</td>\n",
       "      <td>14.10</td>\n",
       "      <td>e781fdcc107d13d865fc7698711cc572</td>\n",
       "      <td>housewares</td>\n",
       "      <td>housewares</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53cdb2fc8bc7dce0b6741e2150273451</td>\n",
       "      <td>2018-07-24 20:41:37</td>\n",
       "      <td>1</td>\n",
       "      <td>595fac2a385ac33a80bd5114aec74eb8</td>\n",
       "      <td>118.70</td>\n",
       "      <td>22.76</td>\n",
       "      <td>af07308b275d755c9edb36a90c618231</td>\n",
       "      <td>perfumery</td>\n",
       "      <td>perfumery</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           order_id order_purchase_timestamp  order_item_id  \\\n",
       "0  e481f51cbdc54678b7cc49136f2d6af7      2017-10-02 10:56:33              1   \n",
       "1  128e10d95713541c87cd1a2e48201934      2017-08-15 18:29:31              1   \n",
       "2  0e7e841ddf8f8f2de2bad69267ecfbcf      2017-08-02 18:24:47              1   \n",
       "3  bfc39df4f36c3693ff3b63fcbea9e90a      2017-10-23 23:26:46              1   \n",
       "4  53cdb2fc8bc7dce0b6741e2150273451      2018-07-24 20:41:37              1   \n",
       "\n",
       "                         product_id   price  freight_value  \\\n",
       "0  87285b34884572647811a353c7ac498a   29.99           8.72   \n",
       "1  87285b34884572647811a353c7ac498a   29.99           7.78   \n",
       "2  87285b34884572647811a353c7ac498a   29.99           7.78   \n",
       "3  87285b34884572647811a353c7ac498a   29.99          14.10   \n",
       "4  595fac2a385ac33a80bd5114aec74eb8  118.70          22.76   \n",
       "\n",
       "                 customer_unique_id product_category_name_english_x  \\\n",
       "0  7c396fd4830fd04220f754e42b4e5bff                      housewares   \n",
       "1  3a51803cc0d012c3b5dc8b7528cb05f7                      housewares   \n",
       "2  ef0996a1a279c26e7ecbd737be23d235                      housewares   \n",
       "3  e781fdcc107d13d865fc7698711cc572                      housewares   \n",
       "4  af07308b275d755c9edb36a90c618231                       perfumery   \n",
       "\n",
       "  product_category_name_english_y  \n",
       "0                      housewares  \n",
       "1                      housewares  \n",
       "2                      housewares  \n",
       "3                      housewares  \n",
       "4                       perfumery  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cust_order = pd.merge(\n",
    "    left= cust_order,\n",
    "    right= products[['product_id','product_category_name_english']],\n",
    "    on= 'product_id',\n",
    "    how= 'inner'\n",
    ")\n",
    "\n",
    "cust_order.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "metadata": {},
   "outputs": [],
   "source": [
    "cust_order.to_csv('./Olist_data/customer_order.csv', index= False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transaction data\n",
    "\n",
    "For the analysis of the products and to find associations between the various products and product categories sold on the website, a transaction dataset has to be created. For this, the existing dataset of all customers and their orders is manipulated.<br>\n",
    "\n",
    "The orders are grouped by customer_unique_id and timestamp giving an idea of which customer bought which products and when. The product category is used to create dummy columns which are summed together based on groups. The resultant dataset now gives inforamtion on a customer's order - the time and the quantity of products bought in each order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>catg_agro_industry_and_commerce</th>\n",
       "      <th>catg_air_conditioning</th>\n",
       "      <th>catg_art</th>\n",
       "      <th>catg_arts_and_craftmanship</th>\n",
       "      <th>catg_audio</th>\n",
       "      <th>catg_auto</th>\n",
       "      <th>catg_baby</th>\n",
       "      <th>catg_bed_bath_table</th>\n",
       "      <th>...</th>\n",
       "      <th>catg_security_and_services</th>\n",
       "      <th>catg_signaling_and_security</th>\n",
       "      <th>catg_small_appliances</th>\n",
       "      <th>catg_small_appliances_home_oven_and_coffee</th>\n",
       "      <th>catg_sports_leisure</th>\n",
       "      <th>catg_stationery</th>\n",
       "      <th>catg_tablets_printing_image</th>\n",
       "      <th>catg_telephony</th>\n",
       "      <th>catg_toys</th>\n",
       "      <th>catg_watches_gifts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000366f3b9a7992bf8c76cfdf3221e2</td>\n",
       "      <td>2018-05-10 10:56:27</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000b849f77a49e4a4ce2b2a4ca5be3f</td>\n",
       "      <td>2018-05-07 11:11:27</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000f46a3911fa3c0805444483337064</td>\n",
       "      <td>2017-03-10 21:05:03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000f6ccb0745a6a4b88665a16c9f078</td>\n",
       "      <td>2017-10-12 20:29:41</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0004aac84e0df4da2b147fca70cf8255</td>\n",
       "      <td>2017-11-14 19:45:42</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>98376</th>\n",
       "      <td>fffcf5a5ff07b0908bd4e2dbc735a684</td>\n",
       "      <td>2017-06-08 21:00:36</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98377</th>\n",
       "      <td>fffea47cd6d3cc0a88bd621562a9d061</td>\n",
       "      <td>2017-12-10 20:07:56</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98378</th>\n",
       "      <td>ffff371b4d645b6ecea244b27531430a</td>\n",
       "      <td>2017-02-07 15:49:16</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98379</th>\n",
       "      <td>ffff5962728ec6157033ef9805bacc48</td>\n",
       "      <td>2018-05-02 15:17:41</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98380</th>\n",
       "      <td>ffffd2657e2aad2907e67c3e9daecbeb</td>\n",
       "      <td>2017-05-02 20:18:45</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>98381 rows × 73 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     customer_unique_id order_purchase_timestamp  \\\n",
       "0      0000366f3b9a7992bf8c76cfdf3221e2      2018-05-10 10:56:27   \n",
       "1      0000b849f77a49e4a4ce2b2a4ca5be3f      2018-05-07 11:11:27   \n",
       "2      0000f46a3911fa3c0805444483337064      2017-03-10 21:05:03   \n",
       "3      0000f6ccb0745a6a4b88665a16c9f078      2017-10-12 20:29:41   \n",
       "4      0004aac84e0df4da2b147fca70cf8255      2017-11-14 19:45:42   \n",
       "...                                 ...                      ...   \n",
       "98376  fffcf5a5ff07b0908bd4e2dbc735a684      2017-06-08 21:00:36   \n",
       "98377  fffea47cd6d3cc0a88bd621562a9d061      2017-12-10 20:07:56   \n",
       "98378  ffff371b4d645b6ecea244b27531430a      2017-02-07 15:49:16   \n",
       "98379  ffff5962728ec6157033ef9805bacc48      2018-05-02 15:17:41   \n",
       "98380  ffffd2657e2aad2907e67c3e9daecbeb      2017-05-02 20:18:45   \n",
       "\n",
       "       catg_agro_industry_and_commerce  catg_air_conditioning  catg_art  \\\n",
       "0                                    0                      0         0   \n",
       "1                                    0                      0         0   \n",
       "2                                    0                      0         0   \n",
       "3                                    0                      0         0   \n",
       "4                                    0                      0         0   \n",
       "...                                ...                    ...       ...   \n",
       "98376                                0                      0         0   \n",
       "98377                                0                      0         0   \n",
       "98378                                0                      0         0   \n",
       "98379                                0                      0         0   \n",
       "98380                                0                      0         0   \n",
       "\n",
       "       catg_arts_and_craftmanship  catg_audio  catg_auto  catg_baby  \\\n",
       "0                               0           0          0          0   \n",
       "1                               0           0          0          0   \n",
       "2                               0           0          0          0   \n",
       "3                               0           0          0          0   \n",
       "4                               0           0          0          0   \n",
       "...                           ...         ...        ...        ...   \n",
       "98376                           0           0          0          0   \n",
       "98377                           0           0          0          1   \n",
       "98378                           0           0          1          0   \n",
       "98379                           0           0          0          0   \n",
       "98380                           0           0          0          0   \n",
       "\n",
       "       catg_bed_bath_table  ...  catg_security_and_services  \\\n",
       "0                        1  ...                           0   \n",
       "1                        0  ...                           0   \n",
       "2                        0  ...                           0   \n",
       "3                        0  ...                           0   \n",
       "4                        0  ...                           0   \n",
       "...                    ...  ...                         ...   \n",
       "98376                    0  ...                           0   \n",
       "98377                    0  ...                           0   \n",
       "98378                    0  ...                           0   \n",
       "98379                    0  ...                           0   \n",
       "98380                    0  ...                           0   \n",
       "\n",
       "       catg_signaling_and_security  catg_small_appliances  \\\n",
       "0                                0                      0   \n",
       "1                                0                      0   \n",
       "2                                0                      0   \n",
       "3                                0                      0   \n",
       "4                                0                      0   \n",
       "...                            ...                    ...   \n",
       "98376                            0                      0   \n",
       "98377                            0                      0   \n",
       "98378                            0                      0   \n",
       "98379                            0                      0   \n",
       "98380                            0                      0   \n",
       "\n",
       "       catg_small_appliances_home_oven_and_coffee  catg_sports_leisure  \\\n",
       "0                                               0                    0   \n",
       "1                                               0                    0   \n",
       "2                                               0                    0   \n",
       "3                                               0                    0   \n",
       "4                                               0                    0   \n",
       "...                                           ...                  ...   \n",
       "98376                                           0                    0   \n",
       "98377                                           0                    0   \n",
       "98378                                           0                    0   \n",
       "98379                                           0                    0   \n",
       "98380                                           0                    0   \n",
       "\n",
       "       catg_stationery  catg_tablets_printing_image  catg_telephony  \\\n",
       "0                    0                            0               0   \n",
       "1                    0                            0               0   \n",
       "2                    1                            0               0   \n",
       "3                    0                            0               1   \n",
       "4                    0                            0               1   \n",
       "...                ...                          ...             ...   \n",
       "98376                0                            0               0   \n",
       "98377                0                            0               0   \n",
       "98378                0                            0               0   \n",
       "98379                0                            0               0   \n",
       "98380                0                            0               0   \n",
       "\n",
       "       catg_toys  catg_watches_gifts  \n",
       "0              0                   0  \n",
       "1              0                   0  \n",
       "2              0                   0  \n",
       "3              0                   0  \n",
       "4              0                   0  \n",
       "...          ...                 ...  \n",
       "98376          0                   0  \n",
       "98377          0                   0  \n",
       "98378          0                   0  \n",
       "98379          0                   1  \n",
       "98380          0                   0  \n",
       "\n",
       "[98381 rows x 73 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "txn_data = pd.get_dummies(\n",
    "    data= cust_order[[\n",
    "        'customer_unique_id',\n",
    "        'order_purchase_timestamp',\n",
    "        'product_category_name_english'\n",
    "    ]], \n",
    "    columns= ['product_category_name_english'],\n",
    "    prefix= 'catg'\n",
    ").groupby([\n",
    "    'customer_unique_id',\n",
    "    'order_purchase_timestamp'\n",
    "]).sum()\n",
    "\n",
    "txn_data = txn_data.reset_index()\n",
    "txn_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "metadata": {},
   "outputs": [],
   "source": [
    "txn_data.to_csv('./Olist_data/transaction_data.csv', index= False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Payment data\n",
    "\n",
    "The olist_order_payments_dataset contains information on the payments carried out by the customer. Every order_id has a payment linked to it. The payment can be done via multiple sources such as credit cards or vouchers etc and can be done in installments. If a payment for an order is broken down and paid via two or more different payment methods then that many rows with a sequence number is recorded."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>payment_sequential</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>payment_installments</th>\n",
       "      <th>payment_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b81ef226f3fe1789b1e8b2acac839d17</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>8</td>\n",
       "      <td>99.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a9810da82917af2d9aefd1278f1dcfa0</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>24.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25e8ea4e93396b6fa0d3dd708e76c1bd</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>65.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ba78997921bbcdc1373bb41e913ab953</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>8</td>\n",
       "      <td>107.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>42fdf880ba16b47b59251dd489d4441a</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>2</td>\n",
       "      <td>128.45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           order_id  payment_sequential payment_type  \\\n",
       "0  b81ef226f3fe1789b1e8b2acac839d17                   1  credit_card   \n",
       "1  a9810da82917af2d9aefd1278f1dcfa0                   1  credit_card   \n",
       "2  25e8ea4e93396b6fa0d3dd708e76c1bd                   1  credit_card   \n",
       "3  ba78997921bbcdc1373bb41e913ab953                   1  credit_card   \n",
       "4  42fdf880ba16b47b59251dd489d4441a                   1  credit_card   \n",
       "\n",
       "   payment_installments  payment_value  \n",
       "0                     8          99.33  \n",
       "1                     1          24.39  \n",
       "2                     1          65.71  \n",
       "3                     8         107.78  \n",
       "4                     2         128.45  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "payments = pd.read_csv('/home/raj/Downloads/Olist-Ecomm data/olist_order_payments_dataset.csv')\n",
    "payments.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "About 75% of the payment methods are via Credit cards and the rest 25% divided between other forms. Due to this, the payment method is not an important attribute when analyzing payments. Hence neglecting payment methods and the sequence associated with it. The rows are grouped by order_id and the installments and payment value are summed. This reduces every order to one entry and giving the total payment value and total number of installments value was paid in irrespective of method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</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>00010242fe8c5a6d1ba2dd792cb16214</td>\n",
       "      <td>2</td>\n",
       "      <td>72.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00018f77f2f0320c557190d7a144bdd3</td>\n",
       "      <td>3</td>\n",
       "      <td>259.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000229ec398224ef6ca0657da4fc703e</td>\n",
       "      <td>5</td>\n",
       "      <td>216.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00024acbcdf0a6daa1e931b038114c75</td>\n",
       "      <td>2</td>\n",
       "      <td>25.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00042b26cf59d7ce69dfabb4e55b4fd9</td>\n",
       "      <td>3</td>\n",
       "      <td>218.04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           order_id  payment_installments  payment_value\n",
       "0  00010242fe8c5a6d1ba2dd792cb16214                     2          72.19\n",
       "1  00018f77f2f0320c557190d7a144bdd3                     3         259.83\n",
       "2  000229ec398224ef6ca0657da4fc703e                     5         216.87\n",
       "3  00024acbcdf0a6daa1e931b038114c75                     2          25.78\n",
       "4  00042b26cf59d7ce69dfabb4e55b4fd9                     3         218.04"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_payment = payments[[\n",
    "    'order_id',\n",
    "    'payment_installments',\n",
    "    'payment_value'\n",
    "]].groupby('order_id').sum().reset_index()\n",
    "\n",
    "order_payment.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This payment information is again joined with the customer order dataset. Now the customer order dataset gives information on payments made for each order as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>order_item_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>price</th>\n",
       "      <th>freight_value</th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>product_category_name_english</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>e481f51cbdc54678b7cc49136f2d6af7</td>\n",
       "      <td>2017-10-02 10:56:33</td>\n",
       "      <td>1</td>\n",
       "      <td>87285b34884572647811a353c7ac498a</td>\n",
       "      <td>29.99</td>\n",
       "      <td>8.72</td>\n",
       "      <td>7c396fd4830fd04220f754e42b4e5bff</td>\n",
       "      <td>housewares</td>\n",
       "      <td>3.0</td>\n",
       "      <td>38.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>128e10d95713541c87cd1a2e48201934</td>\n",
       "      <td>2017-08-15 18:29:31</td>\n",
       "      <td>1</td>\n",
       "      <td>87285b34884572647811a353c7ac498a</td>\n",
       "      <td>29.99</td>\n",
       "      <td>7.78</td>\n",
       "      <td>3a51803cc0d012c3b5dc8b7528cb05f7</td>\n",
       "      <td>housewares</td>\n",
       "      <td>3.0</td>\n",
       "      <td>37.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0e7e841ddf8f8f2de2bad69267ecfbcf</td>\n",
       "      <td>2017-08-02 18:24:47</td>\n",
       "      <td>1</td>\n",
       "      <td>87285b34884572647811a353c7ac498a</td>\n",
       "      <td>29.99</td>\n",
       "      <td>7.78</td>\n",
       "      <td>ef0996a1a279c26e7ecbd737be23d235</td>\n",
       "      <td>housewares</td>\n",
       "      <td>1.0</td>\n",
       "      <td>37.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bfc39df4f36c3693ff3b63fcbea9e90a</td>\n",
       "      <td>2017-10-23 23:26:46</td>\n",
       "      <td>1</td>\n",
       "      <td>87285b34884572647811a353c7ac498a</td>\n",
       "      <td>29.99</td>\n",
       "      <td>14.10</td>\n",
       "      <td>e781fdcc107d13d865fc7698711cc572</td>\n",
       "      <td>housewares</td>\n",
       "      <td>1.0</td>\n",
       "      <td>44.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53cdb2fc8bc7dce0b6741e2150273451</td>\n",
       "      <td>2018-07-24 20:41:37</td>\n",
       "      <td>1</td>\n",
       "      <td>595fac2a385ac33a80bd5114aec74eb8</td>\n",
       "      <td>118.70</td>\n",
       "      <td>22.76</td>\n",
       "      <td>af07308b275d755c9edb36a90c618231</td>\n",
       "      <td>perfumery</td>\n",
       "      <td>1.0</td>\n",
       "      <td>141.46</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           order_id order_purchase_timestamp  order_item_id  \\\n",
       "0  e481f51cbdc54678b7cc49136f2d6af7      2017-10-02 10:56:33              1   \n",
       "1  128e10d95713541c87cd1a2e48201934      2017-08-15 18:29:31              1   \n",
       "2  0e7e841ddf8f8f2de2bad69267ecfbcf      2017-08-02 18:24:47              1   \n",
       "3  bfc39df4f36c3693ff3b63fcbea9e90a      2017-10-23 23:26:46              1   \n",
       "4  53cdb2fc8bc7dce0b6741e2150273451      2018-07-24 20:41:37              1   \n",
       "\n",
       "                         product_id   price  freight_value  \\\n",
       "0  87285b34884572647811a353c7ac498a   29.99           8.72   \n",
       "1  87285b34884572647811a353c7ac498a   29.99           7.78   \n",
       "2  87285b34884572647811a353c7ac498a   29.99           7.78   \n",
       "3  87285b34884572647811a353c7ac498a   29.99          14.10   \n",
       "4  595fac2a385ac33a80bd5114aec74eb8  118.70          22.76   \n",
       "\n",
       "                 customer_unique_id product_category_name_english  \\\n",
       "0  7c396fd4830fd04220f754e42b4e5bff                    housewares   \n",
       "1  3a51803cc0d012c3b5dc8b7528cb05f7                    housewares   \n",
       "2  ef0996a1a279c26e7ecbd737be23d235                    housewares   \n",
       "3  e781fdcc107d13d865fc7698711cc572                    housewares   \n",
       "4  af07308b275d755c9edb36a90c618231                     perfumery   \n",
       "\n",
       "   payment_installments  payment_value  \n",
       "0                   3.0          38.71  \n",
       "1                   3.0          37.77  \n",
       "2                   1.0          37.77  \n",
       "3                   1.0          44.09  \n",
       "4                   1.0         141.46  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cust_ord_payment = pd.merge(\n",
    "    left= cust_order,\n",
    "    right= order_payment,\n",
    "    on= 'order_id',\n",
    "    how= 'left'\n",
    ")\n",
    "\n",
    "cust_ord_payment.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above dataset doesnot give a complete idea as to what the customers have bought over time and how do they usually pay. Such habits are important when we want to understand customer behaviours. Thus dummy columns for every product category are created and summed for each customer (grouped by customer_unique_id).\n",
    "One row here shows what the customer has bought over the 2 years summed up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>catg_agro_industry_and_commerce</th>\n",
       "      <th>catg_air_conditioning</th>\n",
       "      <th>catg_art</th>\n",
       "      <th>catg_arts_and_craftmanship</th>\n",
       "      <th>catg_audio</th>\n",
       "      <th>catg_auto</th>\n",
       "      <th>catg_baby</th>\n",
       "      <th>catg_bed_bath_table</th>\n",
       "      <th>catg_books_general_interest</th>\n",
       "      <th>...</th>\n",
       "      <th>catg_security_and_services</th>\n",
       "      <th>catg_signaling_and_security</th>\n",
       "      <th>catg_small_appliances</th>\n",
       "      <th>catg_small_appliances_home_oven_and_coffee</th>\n",
       "      <th>catg_sports_leisure</th>\n",
       "      <th>catg_stationery</th>\n",
       "      <th>catg_tablets_printing_image</th>\n",
       "      <th>catg_telephony</th>\n",
       "      <th>catg_toys</th>\n",
       "      <th>catg_watches_gifts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000366f3b9a7992bf8c76cfdf3221e2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000b849f77a49e4a4ce2b2a4ca5be3f</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000f46a3911fa3c0805444483337064</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000f6ccb0745a6a4b88665a16c9f078</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0004aac84e0df4da2b147fca70cf8255</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 72 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 customer_unique_id  catg_agro_industry_and_commerce  \\\n",
       "0  0000366f3b9a7992bf8c76cfdf3221e2                                0   \n",
       "1  0000b849f77a49e4a4ce2b2a4ca5be3f                                0   \n",
       "2  0000f46a3911fa3c0805444483337064                                0   \n",
       "3  0000f6ccb0745a6a4b88665a16c9f078                                0   \n",
       "4  0004aac84e0df4da2b147fca70cf8255                                0   \n",
       "\n",
       "   catg_air_conditioning  catg_art  catg_arts_and_craftmanship  catg_audio  \\\n",
       "0                      0         0                           0           0   \n",
       "1                      0         0                           0           0   \n",
       "2                      0         0                           0           0   \n",
       "3                      0         0                           0           0   \n",
       "4                      0         0                           0           0   \n",
       "\n",
       "   catg_auto  catg_baby  catg_bed_bath_table  catg_books_general_interest  \\\n",
       "0          0          0                    1                            0   \n",
       "1          0          0                    0                            0   \n",
       "2          0          0                    0                            0   \n",
       "3          0          0                    0                            0   \n",
       "4          0          0                    0                            0   \n",
       "\n",
       "   ...  catg_security_and_services  catg_signaling_and_security  \\\n",
       "0  ...                           0                            0   \n",
       "1  ...                           0                            0   \n",
       "2  ...                           0                            0   \n",
       "3  ...                           0                            0   \n",
       "4  ...                           0                            0   \n",
       "\n",
       "   catg_small_appliances  catg_small_appliances_home_oven_and_coffee  \\\n",
       "0                      0                                           0   \n",
       "1                      0                                           0   \n",
       "2                      0                                           0   \n",
       "3                      0                                           0   \n",
       "4                      0                                           0   \n",
       "\n",
       "   catg_sports_leisure  catg_stationery  catg_tablets_printing_image  \\\n",
       "0                    0                0                            0   \n",
       "1                    0                0                            0   \n",
       "2                    0                1                            0   \n",
       "3                    0                0                            0   \n",
       "4                    0                0                            0   \n",
       "\n",
       "   catg_telephony  catg_toys  catg_watches_gifts  \n",
       "0               0          0                   0  \n",
       "1               0          0                   0  \n",
       "2               0          0                   0  \n",
       "3               1          0                   0  \n",
       "4               1          0                   0  \n",
       "\n",
       "[5 rows x 72 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cust_txn = pd.get_dummies(\n",
    "    data= cust_ord_payment[[\n",
    "        'customer_unique_id',\n",
    "        'product_category_name_english',\n",
    "    ]], \n",
    "    columns= ['product_category_name_english'],\n",
    "    prefix= \"catg\"\n",
    ").groupby('customer_unique_id').sum().reset_index()\n",
    "\n",
    "cust_txn.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similarly for every customer an average of payment installments and payment value is calculated. This shows on average how much does the customer shop for and in how many installments does the customer pay the amount."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>payment_installments</th>\n",
       "      <th>payment_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000366f3b9a7992bf8c76cfdf3221e2</td>\n",
       "      <td>8.0</td>\n",
       "      <td>141.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000b849f77a49e4a4ce2b2a4ca5be3f</td>\n",
       "      <td>1.0</td>\n",
       "      <td>27.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000f46a3911fa3c0805444483337064</td>\n",
       "      <td>8.0</td>\n",
       "      <td>86.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000f6ccb0745a6a4b88665a16c9f078</td>\n",
       "      <td>4.0</td>\n",
       "      <td>43.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0004aac84e0df4da2b147fca70cf8255</td>\n",
       "      <td>6.0</td>\n",
       "      <td>196.89</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 customer_unique_id  payment_installments  payment_value\n",
       "0  0000366f3b9a7992bf8c76cfdf3221e2                   8.0         141.90\n",
       "1  0000b849f77a49e4a4ce2b2a4ca5be3f                   1.0          27.19\n",
       "2  0000f46a3911fa3c0805444483337064                   8.0          86.22\n",
       "3  0000f6ccb0745a6a4b88665a16c9f078                   4.0          43.62\n",
       "4  0004aac84e0df4da2b147fca70cf8255                   6.0         196.89"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cust_mean = cust_ord_payment[[\n",
    "    'customer_unique_id',\n",
    "    'payment_installments',\n",
    "    'payment_value'\n",
    "]].groupby('customer_unique_id').mean().reset_index()\n",
    "\n",
    "cust_mean.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The two datasets above are combined to show what customers have bought over the span of 2 years and on average how do they pay the amount."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>catg_agro_industry_and_commerce</th>\n",
       "      <th>catg_air_conditioning</th>\n",
       "      <th>catg_art</th>\n",
       "      <th>catg_arts_and_craftmanship</th>\n",
       "      <th>catg_audio</th>\n",
       "      <th>catg_auto</th>\n",
       "      <th>catg_baby</th>\n",
       "      <th>catg_bed_bath_table</th>\n",
       "      <th>catg_books_general_interest</th>\n",
       "      <th>...</th>\n",
       "      <th>catg_small_appliances</th>\n",
       "      <th>catg_small_appliances_home_oven_and_coffee</th>\n",
       "      <th>catg_sports_leisure</th>\n",
       "      <th>catg_stationery</th>\n",
       "      <th>catg_tablets_printing_image</th>\n",
       "      <th>catg_telephony</th>\n",
       "      <th>catg_toys</th>\n",
       "      <th>catg_watches_gifts</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>0000366f3b9a7992bf8c76cfdf3221e2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>141.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000b849f77a49e4a4ce2b2a4ca5be3f</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>27.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000f46a3911fa3c0805444483337064</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>86.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000f6ccb0745a6a4b88665a16c9f078</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>43.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0004aac84e0df4da2b147fca70cf8255</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>196.89</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 74 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 customer_unique_id  catg_agro_industry_and_commerce  \\\n",
       "0  0000366f3b9a7992bf8c76cfdf3221e2                                0   \n",
       "1  0000b849f77a49e4a4ce2b2a4ca5be3f                                0   \n",
       "2  0000f46a3911fa3c0805444483337064                                0   \n",
       "3  0000f6ccb0745a6a4b88665a16c9f078                                0   \n",
       "4  0004aac84e0df4da2b147fca70cf8255                                0   \n",
       "\n",
       "   catg_air_conditioning  catg_art  catg_arts_and_craftmanship  catg_audio  \\\n",
       "0                      0         0                           0           0   \n",
       "1                      0         0                           0           0   \n",
       "2                      0         0                           0           0   \n",
       "3                      0         0                           0           0   \n",
       "4                      0         0                           0           0   \n",
       "\n",
       "   catg_auto  catg_baby  catg_bed_bath_table  catg_books_general_interest  \\\n",
       "0          0          0                    1                            0   \n",
       "1          0          0                    0                            0   \n",
       "2          0          0                    0                            0   \n",
       "3          0          0                    0                            0   \n",
       "4          0          0                    0                            0   \n",
       "\n",
       "   ...  catg_small_appliances  catg_small_appliances_home_oven_and_coffee  \\\n",
       "0  ...                      0                                           0   \n",
       "1  ...                      0                                           0   \n",
       "2  ...                      0                                           0   \n",
       "3  ...                      0                                           0   \n",
       "4  ...                      0                                           0   \n",
       "\n",
       "   catg_sports_leisure  catg_stationery  catg_tablets_printing_image  \\\n",
       "0                    0                0                            0   \n",
       "1                    0                0                            0   \n",
       "2                    0                1                            0   \n",
       "3                    0                0                            0   \n",
       "4                    0                0                            0   \n",
       "\n",
       "   catg_telephony  catg_toys  catg_watches_gifts  payment_installments  \\\n",
       "0               0          0                   0                   8.0   \n",
       "1               0          0                   0                   1.0   \n",
       "2               0          0                   0                   8.0   \n",
       "3               1          0                   0                   4.0   \n",
       "4               1          0                   0                   6.0   \n",
       "\n",
       "   payment_value  \n",
       "0         141.90  \n",
       "1          27.19  \n",
       "2          86.22  \n",
       "3          43.62  \n",
       "4         196.89  \n",
       "\n",
       "[5 rows x 74 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cust_payment = pd.merge(\n",
    "    left= cust_txn,\n",
    "    right= cust_mean,\n",
    "    on= 'customer_unique_id',\n",
    "    how= 'inner'\n",
    ")\n",
    "\n",
    "cust_payment.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "metadata": {},
   "outputs": [],
   "source": [
    "cust_payment.to_csv('./Olist_data/customer_payment.csv', index= False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Seller location data\n",
    "\n",
    "The olist_sellers_dataset contains locations information the sellers in terms of zip code, city and state. The sellers list their products on the website and are then responsible for dispatching the delivery."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>seller_id</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>3442f8959a84dea7ee197c632cb2df15</td>\n",
       "      <td>13023</td>\n",
       "      <td>campinas</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>d1b65fc7debc3361ea86b5f14c68d2e2</td>\n",
       "      <td>13844</td>\n",
       "      <td>mogi guacu</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ce3ad9de960102d0677a81f5d0bb7b2d</td>\n",
       "      <td>20031</td>\n",
       "      <td>rio de janeiro</td>\n",
       "      <td>RJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>c0f3eea2e14555b6faeea3dd58c1b1c3</td>\n",
       "      <td>4195</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>51a04a8a6bdcb23deccc82b0b80742cf</td>\n",
       "      <td>12914</td>\n",
       "      <td>braganca paulista</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          seller_id  seller_zip_code_prefix  \\\n",
       "0  3442f8959a84dea7ee197c632cb2df15                   13023   \n",
       "1  d1b65fc7debc3361ea86b5f14c68d2e2                   13844   \n",
       "2  ce3ad9de960102d0677a81f5d0bb7b2d                   20031   \n",
       "3  c0f3eea2e14555b6faeea3dd58c1b1c3                    4195   \n",
       "4  51a04a8a6bdcb23deccc82b0b80742cf                   12914   \n",
       "\n",
       "         seller_city seller_state  \n",
       "0           campinas           SP  \n",
       "1         mogi guacu           SP  \n",
       "2     rio de janeiro           RJ  \n",
       "3          sao paulo           SP  \n",
       "4  braganca paulista           SP  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sellers = pd.read_csv('/home/raj/Downloads/Olist-Ecomm data/olist_sellers_dataset.csv')\n",
    "sellers.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similar to customer, the sellers dataset is also combined with geolocation dataset to get latitude and longitude of the centroid of zipcode in which the seller resides."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>seller_id</th>\n",
       "      <th>seller_zip_code_prefix</th>\n",
       "      <th>seller_city</th>\n",
       "      <th>seller_state</th>\n",
       "      <th>geolocation_zip_code_prefix</th>\n",
       "      <th>geolocation_lat</th>\n",
       "      <th>geolocation_lng</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3442f8959a84dea7ee197c632cb2df15</td>\n",
       "      <td>13023</td>\n",
       "      <td>campinas</td>\n",
       "      <td>SP</td>\n",
       "      <td>13023.0</td>\n",
       "      <td>-22.893848</td>\n",
       "      <td>-47.061337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>d1b65fc7debc3361ea86b5f14c68d2e2</td>\n",
       "      <td>13844</td>\n",
       "      <td>mogi guacu</td>\n",
       "      <td>SP</td>\n",
       "      <td>13844.0</td>\n",
       "      <td>-22.383437</td>\n",
       "      <td>-46.947927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ce3ad9de960102d0677a81f5d0bb7b2d</td>\n",
       "      <td>20031</td>\n",
       "      <td>rio de janeiro</td>\n",
       "      <td>RJ</td>\n",
       "      <td>20031.0</td>\n",
       "      <td>-22.909572</td>\n",
       "      <td>-43.177703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>c0f3eea2e14555b6faeea3dd58c1b1c3</td>\n",
       "      <td>4195</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "      <td>4195.0</td>\n",
       "      <td>-23.657242</td>\n",
       "      <td>-46.612831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>51a04a8a6bdcb23deccc82b0b80742cf</td>\n",
       "      <td>12914</td>\n",
       "      <td>braganca paulista</td>\n",
       "      <td>SP</td>\n",
       "      <td>12914.0</td>\n",
       "      <td>-22.964803</td>\n",
       "      <td>-46.534419</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          seller_id  seller_zip_code_prefix  \\\n",
       "0  3442f8959a84dea7ee197c632cb2df15                   13023   \n",
       "1  d1b65fc7debc3361ea86b5f14c68d2e2                   13844   \n",
       "2  ce3ad9de960102d0677a81f5d0bb7b2d                   20031   \n",
       "3  c0f3eea2e14555b6faeea3dd58c1b1c3                    4195   \n",
       "4  51a04a8a6bdcb23deccc82b0b80742cf                   12914   \n",
       "\n",
       "         seller_city seller_state  geolocation_zip_code_prefix  \\\n",
       "0           campinas           SP                      13023.0   \n",
       "1         mogi guacu           SP                      13844.0   \n",
       "2     rio de janeiro           RJ                      20031.0   \n",
       "3          sao paulo           SP                       4195.0   \n",
       "4  braganca paulista           SP                      12914.0   \n",
       "\n",
       "   geolocation_lat  geolocation_lng  \n",
       "0       -22.893848       -47.061337  \n",
       "1       -22.383437       -46.947927  \n",
       "2       -22.909572       -43.177703  \n",
       "3       -23.657242       -46.612831  \n",
       "4       -22.964803       -46.534419  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "seller_loc = pd.merge(\n",
    "    left= sellers,\n",
    "    right= loc_coordnts,\n",
    "    left_on= 'seller_zip_code_prefix',\n",
    "    right_on= 'geolocation_zip_code_prefix', \n",
    "    how= 'left'\n",
    ")\n",
    "\n",
    "seller_loc.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The ord_items dataset is joined with orders dataset to get order_id, seller_id and customer_id mappings. This shows that for every temporary customer_id, which seller is assigned for the order and various milestones of delivery in timestamp."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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_purchase_timestamp</th>\n",
       "      <th>order_delivered_customer_date</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "      <th>seller_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00010242fe8c5a6d1ba2dd792cb16214</td>\n",
       "      <td>3ce436f183e68e07877b285a838db11a</td>\n",
       "      <td>2017-09-13 08:59:02</td>\n",
       "      <td>2017-09-20 23:43:48</td>\n",
       "      <td>2017-09-29 00:00:00</td>\n",
       "      <td>48436dade18ac8b2bce089ec2a041202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00018f77f2f0320c557190d7a144bdd3</td>\n",
       "      <td>f6dd3ec061db4e3987629fe6b26e5cce</td>\n",
       "      <td>2017-04-26 10:53:06</td>\n",
       "      <td>2017-05-12 16:04:24</td>\n",
       "      <td>2017-05-15 00:00:00</td>\n",
       "      <td>dd7ddc04e1b6c2c614352b383efe2d36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000229ec398224ef6ca0657da4fc703e</td>\n",
       "      <td>6489ae5e4333f3693df5ad4372dab6d3</td>\n",
       "      <td>2018-01-14 14:33:31</td>\n",
       "      <td>2018-01-22 13:19:16</td>\n",
       "      <td>2018-02-05 00:00:00</td>\n",
       "      <td>5b51032eddd242adc84c38acab88f23d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00024acbcdf0a6daa1e931b038114c75</td>\n",
       "      <td>d4eb9395c8c0431ee92fce09860c5a06</td>\n",
       "      <td>2018-08-08 10:00:35</td>\n",
       "      <td>2018-08-14 13:32:39</td>\n",
       "      <td>2018-08-20 00:00:00</td>\n",
       "      <td>9d7a1d34a5052409006425275ba1c2b4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00042b26cf59d7ce69dfabb4e55b4fd9</td>\n",
       "      <td>58dbd0b2d70206bf40e62cd34e84d795</td>\n",
       "      <td>2017-02-04 13:57:51</td>\n",
       "      <td>2017-03-01 16:42:31</td>\n",
       "      <td>2017-03-17 00:00:00</td>\n",
       "      <td>df560393f3a51e74553ab94004ba5c87</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           order_id                       customer_id  \\\n",
       "0  00010242fe8c5a6d1ba2dd792cb16214  3ce436f183e68e07877b285a838db11a   \n",
       "1  00018f77f2f0320c557190d7a144bdd3  f6dd3ec061db4e3987629fe6b26e5cce   \n",
       "2  000229ec398224ef6ca0657da4fc703e  6489ae5e4333f3693df5ad4372dab6d3   \n",
       "3  00024acbcdf0a6daa1e931b038114c75  d4eb9395c8c0431ee92fce09860c5a06   \n",
       "4  00042b26cf59d7ce69dfabb4e55b4fd9  58dbd0b2d70206bf40e62cd34e84d795   \n",
       "\n",
       "  order_purchase_timestamp order_delivered_customer_date  \\\n",
       "0      2017-09-13 08:59:02           2017-09-20 23:43:48   \n",
       "1      2017-04-26 10:53:06           2017-05-12 16:04:24   \n",
       "2      2018-01-14 14:33:31           2018-01-22 13:19:16   \n",
       "3      2018-08-08 10:00:35           2018-08-14 13:32:39   \n",
       "4      2017-02-04 13:57:51           2017-03-01 16:42:31   \n",
       "\n",
       "  order_estimated_delivery_date                         seller_id  \n",
       "0           2017-09-29 00:00:00  48436dade18ac8b2bce089ec2a041202  \n",
       "1           2017-05-15 00:00:00  dd7ddc04e1b6c2c614352b383efe2d36  \n",
       "2           2018-02-05 00:00:00  5b51032eddd242adc84c38acab88f23d  \n",
       "3           2018-08-20 00:00:00  9d7a1d34a5052409006425275ba1c2b4  \n",
       "4           2017-03-17 00:00:00  df560393f3a51e74553ab94004ba5c87  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols= [\n",
    "    'order_id',\n",
    "    'customer_id',\n",
    "    'order_purchase_timestamp',\n",
    "    'order_delivered_customer_date',\n",
    "    'order_estimated_delivery_date',\n",
    "    'seller_id'\n",
    "]\n",
    "\n",
    "order_seller = pd.merge(\n",
    "    left= ord_items,\n",
    "    right= orders,\n",
    "    on= 'order_id',\n",
    "    how= 'left'\n",
    ")[cols]\n",
    "\n",
    "order_seller.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This dataset is now joined with customer data to get the unique customer id mapped to seller and location of the customer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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_purchase_timestamp</th>\n",
       "      <th>order_delivered_customer_date</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "      <th>seller_id</th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>geolocation_lat</th>\n",
       "      <th>geolocation_lng</th>\n",
       "      <th>customer_city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00010242fe8c5a6d1ba2dd792cb16214</td>\n",
       "      <td>3ce436f183e68e07877b285a838db11a</td>\n",
       "      <td>2017-09-13 08:59:02</td>\n",
       "      <td>2017-09-20 23:43:48</td>\n",
       "      <td>2017-09-29 00:00:00</td>\n",
       "      <td>48436dade18ac8b2bce089ec2a041202</td>\n",
       "      <td>871766c5855e863f6eccc05f988b23cb</td>\n",
       "      <td>-21.762775</td>\n",
       "      <td>-41.309633</td>\n",
       "      <td>campos dos goytacazes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00018f77f2f0320c557190d7a144bdd3</td>\n",
       "      <td>f6dd3ec061db4e3987629fe6b26e5cce</td>\n",
       "      <td>2017-04-26 10:53:06</td>\n",
       "      <td>2017-05-12 16:04:24</td>\n",
       "      <td>2017-05-15 00:00:00</td>\n",
       "      <td>dd7ddc04e1b6c2c614352b383efe2d36</td>\n",
       "      <td>eb28e67c4c0b83846050ddfb8a35d051</td>\n",
       "      <td>-20.220527</td>\n",
       "      <td>-50.903424</td>\n",
       "      <td>santa fe do sul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000229ec398224ef6ca0657da4fc703e</td>\n",
       "      <td>6489ae5e4333f3693df5ad4372dab6d3</td>\n",
       "      <td>2018-01-14 14:33:31</td>\n",
       "      <td>2018-01-22 13:19:16</td>\n",
       "      <td>2018-02-05 00:00:00</td>\n",
       "      <td>5b51032eddd242adc84c38acab88f23d</td>\n",
       "      <td>3818d81c6709e39d06b2738a8d3a2474</td>\n",
       "      <td>-19.870305</td>\n",
       "      <td>-44.593326</td>\n",
       "      <td>para de minas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00024acbcdf0a6daa1e931b038114c75</td>\n",
       "      <td>d4eb9395c8c0431ee92fce09860c5a06</td>\n",
       "      <td>2018-08-08 10:00:35</td>\n",
       "      <td>2018-08-14 13:32:39</td>\n",
       "      <td>2018-08-20 00:00:00</td>\n",
       "      <td>9d7a1d34a5052409006425275ba1c2b4</td>\n",
       "      <td>af861d436cfc08b2c2ddefd0ba074622</td>\n",
       "      <td>-23.089925</td>\n",
       "      <td>-46.611654</td>\n",
       "      <td>atibaia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00042b26cf59d7ce69dfabb4e55b4fd9</td>\n",
       "      <td>58dbd0b2d70206bf40e62cd34e84d795</td>\n",
       "      <td>2017-02-04 13:57:51</td>\n",
       "      <td>2017-03-01 16:42:31</td>\n",
       "      <td>2017-03-17 00:00:00</td>\n",
       "      <td>df560393f3a51e74553ab94004ba5c87</td>\n",
       "      <td>64b576fb70d441e8f1b2d7d446e483c5</td>\n",
       "      <td>-23.243402</td>\n",
       "      <td>-46.827614</td>\n",
       "      <td>varzea paulista</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           order_id                       customer_id  \\\n",
       "0  00010242fe8c5a6d1ba2dd792cb16214  3ce436f183e68e07877b285a838db11a   \n",
       "1  00018f77f2f0320c557190d7a144bdd3  f6dd3ec061db4e3987629fe6b26e5cce   \n",
       "2  000229ec398224ef6ca0657da4fc703e  6489ae5e4333f3693df5ad4372dab6d3   \n",
       "3  00024acbcdf0a6daa1e931b038114c75  d4eb9395c8c0431ee92fce09860c5a06   \n",
       "4  00042b26cf59d7ce69dfabb4e55b4fd9  58dbd0b2d70206bf40e62cd34e84d795   \n",
       "\n",
       "  order_purchase_timestamp order_delivered_customer_date  \\\n",
       "0      2017-09-13 08:59:02           2017-09-20 23:43:48   \n",
       "1      2017-04-26 10:53:06           2017-05-12 16:04:24   \n",
       "2      2018-01-14 14:33:31           2018-01-22 13:19:16   \n",
       "3      2018-08-08 10:00:35           2018-08-14 13:32:39   \n",
       "4      2017-02-04 13:57:51           2017-03-01 16:42:31   \n",
       "\n",
       "  order_estimated_delivery_date                         seller_id  \\\n",
       "0           2017-09-29 00:00:00  48436dade18ac8b2bce089ec2a041202   \n",
       "1           2017-05-15 00:00:00  dd7ddc04e1b6c2c614352b383efe2d36   \n",
       "2           2018-02-05 00:00:00  5b51032eddd242adc84c38acab88f23d   \n",
       "3           2018-08-20 00:00:00  9d7a1d34a5052409006425275ba1c2b4   \n",
       "4           2017-03-17 00:00:00  df560393f3a51e74553ab94004ba5c87   \n",
       "\n",
       "                 customer_unique_id  geolocation_lat  geolocation_lng  \\\n",
       "0  871766c5855e863f6eccc05f988b23cb       -21.762775       -41.309633   \n",
       "1  eb28e67c4c0b83846050ddfb8a35d051       -20.220527       -50.903424   \n",
       "2  3818d81c6709e39d06b2738a8d3a2474       -19.870305       -44.593326   \n",
       "3  af861d436cfc08b2c2ddefd0ba074622       -23.089925       -46.611654   \n",
       "4  64b576fb70d441e8f1b2d7d446e483c5       -23.243402       -46.827614   \n",
       "\n",
       "           customer_city  \n",
       "0  campos dos goytacazes  \n",
       "1        santa fe do sul  \n",
       "2          para de minas  \n",
       "3                atibaia  \n",
       "4        varzea paulista  "
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols= [\n",
    "    'customer_id',\n",
    "    'customer_unique_id',\n",
    "    'geolocation_lat',\n",
    "    'geolocation_lng',\n",
    "    'customer_city'\n",
    "]\n",
    "\n",
    "cust_seller = pd.merge(\n",
    "    left= order_seller,\n",
    "    right= customer_data[cols],\n",
    "    on= 'customer_id',\n",
    "    how= 'left'\n",
    ")\n",
    "\n",
    "cust_seller.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally the above dataset is merged with the seller information to get a mapping of customer locations to seller locations for every order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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_purchase_timestamp</th>\n",
       "      <th>order_delivered_customer_date</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "      <th>seller_id</th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>geolocation_lat_x</th>\n",
       "      <th>geolocation_lng_x</th>\n",
       "      <th>customer_city</th>\n",
       "      <th>seller_city</th>\n",
       "      <th>geolocation_lat_y</th>\n",
       "      <th>geolocation_lng_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00010242fe8c5a6d1ba2dd792cb16214</td>\n",
       "      <td>3ce436f183e68e07877b285a838db11a</td>\n",
       "      <td>2017-09-13 08:59:02</td>\n",
       "      <td>2017-09-20 23:43:48</td>\n",
       "      <td>2017-09-29 00:00:00</td>\n",
       "      <td>48436dade18ac8b2bce089ec2a041202</td>\n",
       "      <td>871766c5855e863f6eccc05f988b23cb</td>\n",
       "      <td>-21.762775</td>\n",
       "      <td>-41.309633</td>\n",
       "      <td>campos dos goytacazes</td>\n",
       "      <td>volta redonda</td>\n",
       "      <td>-22.496953</td>\n",
       "      <td>-44.127492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00018f77f2f0320c557190d7a144bdd3</td>\n",
       "      <td>f6dd3ec061db4e3987629fe6b26e5cce</td>\n",
       "      <td>2017-04-26 10:53:06</td>\n",
       "      <td>2017-05-12 16:04:24</td>\n",
       "      <td>2017-05-15 00:00:00</td>\n",
       "      <td>dd7ddc04e1b6c2c614352b383efe2d36</td>\n",
       "      <td>eb28e67c4c0b83846050ddfb8a35d051</td>\n",
       "      <td>-20.220527</td>\n",
       "      <td>-50.903424</td>\n",
       "      <td>santa fe do sul</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>-23.565096</td>\n",
       "      <td>-46.518565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000229ec398224ef6ca0657da4fc703e</td>\n",
       "      <td>6489ae5e4333f3693df5ad4372dab6d3</td>\n",
       "      <td>2018-01-14 14:33:31</td>\n",
       "      <td>2018-01-22 13:19:16</td>\n",
       "      <td>2018-02-05 00:00:00</td>\n",
       "      <td>5b51032eddd242adc84c38acab88f23d</td>\n",
       "      <td>3818d81c6709e39d06b2738a8d3a2474</td>\n",
       "      <td>-19.870305</td>\n",
       "      <td>-44.593326</td>\n",
       "      <td>para de minas</td>\n",
       "      <td>borda da mata</td>\n",
       "      <td>-22.262584</td>\n",
       "      <td>-46.171124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00024acbcdf0a6daa1e931b038114c75</td>\n",
       "      <td>d4eb9395c8c0431ee92fce09860c5a06</td>\n",
       "      <td>2018-08-08 10:00:35</td>\n",
       "      <td>2018-08-14 13:32:39</td>\n",
       "      <td>2018-08-20 00:00:00</td>\n",
       "      <td>9d7a1d34a5052409006425275ba1c2b4</td>\n",
       "      <td>af861d436cfc08b2c2ddefd0ba074622</td>\n",
       "      <td>-23.089925</td>\n",
       "      <td>-46.611654</td>\n",
       "      <td>atibaia</td>\n",
       "      <td>franca</td>\n",
       "      <td>-20.553624</td>\n",
       "      <td>-47.387359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00042b26cf59d7ce69dfabb4e55b4fd9</td>\n",
       "      <td>58dbd0b2d70206bf40e62cd34e84d795</td>\n",
       "      <td>2017-02-04 13:57:51</td>\n",
       "      <td>2017-03-01 16:42:31</td>\n",
       "      <td>2017-03-17 00:00:00</td>\n",
       "      <td>df560393f3a51e74553ab94004ba5c87</td>\n",
       "      <td>64b576fb70d441e8f1b2d7d446e483c5</td>\n",
       "      <td>-23.243402</td>\n",
       "      <td>-46.827614</td>\n",
       "      <td>varzea paulista</td>\n",
       "      <td>loanda</td>\n",
       "      <td>-22.929384</td>\n",
       "      <td>-53.135873</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           order_id                       customer_id  \\\n",
       "0  00010242fe8c5a6d1ba2dd792cb16214  3ce436f183e68e07877b285a838db11a   \n",
       "1  00018f77f2f0320c557190d7a144bdd3  f6dd3ec061db4e3987629fe6b26e5cce   \n",
       "2  000229ec398224ef6ca0657da4fc703e  6489ae5e4333f3693df5ad4372dab6d3   \n",
       "3  00024acbcdf0a6daa1e931b038114c75  d4eb9395c8c0431ee92fce09860c5a06   \n",
       "4  00042b26cf59d7ce69dfabb4e55b4fd9  58dbd0b2d70206bf40e62cd34e84d795   \n",
       "\n",
       "  order_purchase_timestamp order_delivered_customer_date  \\\n",
       "0      2017-09-13 08:59:02           2017-09-20 23:43:48   \n",
       "1      2017-04-26 10:53:06           2017-05-12 16:04:24   \n",
       "2      2018-01-14 14:33:31           2018-01-22 13:19:16   \n",
       "3      2018-08-08 10:00:35           2018-08-14 13:32:39   \n",
       "4      2017-02-04 13:57:51           2017-03-01 16:42:31   \n",
       "\n",
       "  order_estimated_delivery_date                         seller_id  \\\n",
       "0           2017-09-29 00:00:00  48436dade18ac8b2bce089ec2a041202   \n",
       "1           2017-05-15 00:00:00  dd7ddc04e1b6c2c614352b383efe2d36   \n",
       "2           2018-02-05 00:00:00  5b51032eddd242adc84c38acab88f23d   \n",
       "3           2018-08-20 00:00:00  9d7a1d34a5052409006425275ba1c2b4   \n",
       "4           2017-03-17 00:00:00  df560393f3a51e74553ab94004ba5c87   \n",
       "\n",
       "                 customer_unique_id  geolocation_lat_x  geolocation_lng_x  \\\n",
       "0  871766c5855e863f6eccc05f988b23cb         -21.762775         -41.309633   \n",
       "1  eb28e67c4c0b83846050ddfb8a35d051         -20.220527         -50.903424   \n",
       "2  3818d81c6709e39d06b2738a8d3a2474         -19.870305         -44.593326   \n",
       "3  af861d436cfc08b2c2ddefd0ba074622         -23.089925         -46.611654   \n",
       "4  64b576fb70d441e8f1b2d7d446e483c5         -23.243402         -46.827614   \n",
       "\n",
       "           customer_city    seller_city  geolocation_lat_y  geolocation_lng_y  \n",
       "0  campos dos goytacazes  volta redonda         -22.496953         -44.127492  \n",
       "1        santa fe do sul      sao paulo         -23.565096         -46.518565  \n",
       "2          para de minas  borda da mata         -22.262584         -46.171124  \n",
       "3                atibaia         franca         -20.553624         -47.387359  \n",
       "4        varzea paulista         loanda         -22.929384         -53.135873  "
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols= [\n",
    "    'seller_id',\n",
    "    'seller_city',\n",
    "    'geolocation_lat',\n",
    "    'geolocation_lng'\n",
    "]\n",
    "\n",
    "delivery = pd.merge(\n",
    "    left= cust_seller,\n",
    "    right= seller_loc[cols],\n",
    "    on= 'seller_id',\n",
    "    how= 'left'\n",
    ")\n",
    "\n",
    "delivery.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All irrelevant columns are removed. The resultant dataset now contains information on customer's purchase dates, delivery dates and seller assigned."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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_purchase_timestamp</th>\n",
       "      <th>order_delivered_customer_date</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "      <th>seller_id</th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>customer_lat</th>\n",
       "      <th>customer_lng</th>\n",
       "      <th>customer_city</th>\n",
       "      <th>seller_city</th>\n",
       "      <th>seller_lat</th>\n",
       "      <th>seller_lng</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-09-13 08:59:02</td>\n",
       "      <td>2017-09-20 23:43:48</td>\n",
       "      <td>2017-09-29 00:00:00</td>\n",
       "      <td>48436dade18ac8b2bce089ec2a041202</td>\n",
       "      <td>871766c5855e863f6eccc05f988b23cb</td>\n",
       "      <td>-21.762775</td>\n",
       "      <td>-41.309633</td>\n",
       "      <td>campos dos goytacazes</td>\n",
       "      <td>volta redonda</td>\n",
       "      <td>-22.496953</td>\n",
       "      <td>-44.127492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-04-26 10:53:06</td>\n",
       "      <td>2017-05-12 16:04:24</td>\n",
       "      <td>2017-05-15 00:00:00</td>\n",
       "      <td>dd7ddc04e1b6c2c614352b383efe2d36</td>\n",
       "      <td>eb28e67c4c0b83846050ddfb8a35d051</td>\n",
       "      <td>-20.220527</td>\n",
       "      <td>-50.903424</td>\n",
       "      <td>santa fe do sul</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>-23.565096</td>\n",
       "      <td>-46.518565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-14 14:33:31</td>\n",
       "      <td>2018-01-22 13:19:16</td>\n",
       "      <td>2018-02-05 00:00:00</td>\n",
       "      <td>5b51032eddd242adc84c38acab88f23d</td>\n",
       "      <td>3818d81c6709e39d06b2738a8d3a2474</td>\n",
       "      <td>-19.870305</td>\n",
       "      <td>-44.593326</td>\n",
       "      <td>para de minas</td>\n",
       "      <td>borda da mata</td>\n",
       "      <td>-22.262584</td>\n",
       "      <td>-46.171124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-08-08 10:00:35</td>\n",
       "      <td>2018-08-14 13:32:39</td>\n",
       "      <td>2018-08-20 00:00:00</td>\n",
       "      <td>9d7a1d34a5052409006425275ba1c2b4</td>\n",
       "      <td>af861d436cfc08b2c2ddefd0ba074622</td>\n",
       "      <td>-23.089925</td>\n",
       "      <td>-46.611654</td>\n",
       "      <td>atibaia</td>\n",
       "      <td>franca</td>\n",
       "      <td>-20.553624</td>\n",
       "      <td>-47.387359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-02-04 13:57:51</td>\n",
       "      <td>2017-03-01 16:42:31</td>\n",
       "      <td>2017-03-17 00:00:00</td>\n",
       "      <td>df560393f3a51e74553ab94004ba5c87</td>\n",
       "      <td>64b576fb70d441e8f1b2d7d446e483c5</td>\n",
       "      <td>-23.243402</td>\n",
       "      <td>-46.827614</td>\n",
       "      <td>varzea paulista</td>\n",
       "      <td>loanda</td>\n",
       "      <td>-22.929384</td>\n",
       "      <td>-53.135873</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_purchase_timestamp order_delivered_customer_date  \\\n",
       "0      2017-09-13 08:59:02           2017-09-20 23:43:48   \n",
       "1      2017-04-26 10:53:06           2017-05-12 16:04:24   \n",
       "2      2018-01-14 14:33:31           2018-01-22 13:19:16   \n",
       "3      2018-08-08 10:00:35           2018-08-14 13:32:39   \n",
       "4      2017-02-04 13:57:51           2017-03-01 16:42:31   \n",
       "\n",
       "  order_estimated_delivery_date                         seller_id  \\\n",
       "0           2017-09-29 00:00:00  48436dade18ac8b2bce089ec2a041202   \n",
       "1           2017-05-15 00:00:00  dd7ddc04e1b6c2c614352b383efe2d36   \n",
       "2           2018-02-05 00:00:00  5b51032eddd242adc84c38acab88f23d   \n",
       "3           2018-08-20 00:00:00  9d7a1d34a5052409006425275ba1c2b4   \n",
       "4           2017-03-17 00:00:00  df560393f3a51e74553ab94004ba5c87   \n",
       "\n",
       "                 customer_unique_id  customer_lat  customer_lng  \\\n",
       "0  871766c5855e863f6eccc05f988b23cb    -21.762775    -41.309633   \n",
       "1  eb28e67c4c0b83846050ddfb8a35d051    -20.220527    -50.903424   \n",
       "2  3818d81c6709e39d06b2738a8d3a2474    -19.870305    -44.593326   \n",
       "3  af861d436cfc08b2c2ddefd0ba074622    -23.089925    -46.611654   \n",
       "4  64b576fb70d441e8f1b2d7d446e483c5    -23.243402    -46.827614   \n",
       "\n",
       "           customer_city    seller_city  seller_lat  seller_lng  \n",
       "0  campos dos goytacazes  volta redonda  -22.496953  -44.127492  \n",
       "1        santa fe do sul      sao paulo  -23.565096  -46.518565  \n",
       "2          para de minas  borda da mata  -22.262584  -46.171124  \n",
       "3                atibaia         franca  -20.553624  -47.387359  \n",
       "4        varzea paulista         loanda  -22.929384  -53.135873  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "delivery.drop(columns=[\n",
    "    'order_id',\n",
    "    'customer_id'\n",
    "], inplace= True)\n",
    "\n",
    "delivery.rename(columns={\n",
    "    'geolocation_lat_x':'customer_lat',\n",
    "    'geolocation_lng_x':'customer_lng',\n",
    "    'geolocation_lat_y':'seller_lat',\n",
    "    'geolocation_lng_y':'seller_lng'\n",
    "}, inplace= True)\n",
    "\n",
    "delivery.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "delivery.to_csv('/home/raj/Github/Olist-business-analysis/Joined data/delivery_data.csv', index= False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Order reviews\n",
    "\n",
    "The olist_order_reviews dataset contains the reviews and stars recieved for products based on order_id. The reviews are in Portuguese and need to be translated to English. Furthermore it contains information on when the review was posted and when was it replied to by concerned authorities."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "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>review_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>review_score</th>\n",
       "      <th>review_comment_title</th>\n",
       "      <th>review_comment_message</th>\n",
       "      <th>review_creation_date</th>\n",
       "      <th>review_answer_timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7bc2406110b926393aa56f80a40eba40</td>\n",
       "      <td>73fc7af87114b39712e6da79b0a377eb</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-01-18 00:00:00</td>\n",
       "      <td>2018-01-18 21:46:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>80e641a11e56f04c1ad469d5645fdfde</td>\n",
       "      <td>a548910a1c6147796b98fdf73dbeba33</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-03-10 00:00:00</td>\n",
       "      <td>2018-03-11 03:05:13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>228ce5500dc1d8e020d8d1322874b6f0</td>\n",
       "      <td>f9e4b658b201a9f2ecdecbb34bed034b</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-02-17 00:00:00</td>\n",
       "      <td>2018-02-18 14:36:24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>e64fb393e7b32834bb789ff8bb30750e</td>\n",
       "      <td>658677c97b385a9be170737859d3511b</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Recebi bem antes do prazo estipulado.</td>\n",
       "      <td>2017-04-21 00:00:00</td>\n",
       "      <td>2017-04-21 22:02:06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>f7c4243c7fe1938f181bec41a392bdeb</td>\n",
       "      <td>8e6bfb81e283fa7e4f11123a3fb894f1</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Parabéns lojas lannister adorei comprar pela I...</td>\n",
       "      <td>2018-03-01 00:00:00</td>\n",
       "      <td>2018-03-02 10:26:53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          review_id                          order_id  \\\n",
       "0  7bc2406110b926393aa56f80a40eba40  73fc7af87114b39712e6da79b0a377eb   \n",
       "1  80e641a11e56f04c1ad469d5645fdfde  a548910a1c6147796b98fdf73dbeba33   \n",
       "2  228ce5500dc1d8e020d8d1322874b6f0  f9e4b658b201a9f2ecdecbb34bed034b   \n",
       "3  e64fb393e7b32834bb789ff8bb30750e  658677c97b385a9be170737859d3511b   \n",
       "4  f7c4243c7fe1938f181bec41a392bdeb  8e6bfb81e283fa7e4f11123a3fb894f1   \n",
       "\n",
       "   review_score review_comment_title  \\\n",
       "0             4                  NaN   \n",
       "1             5                  NaN   \n",
       "2             5                  NaN   \n",
       "3             5                  NaN   \n",
       "4             5                  NaN   \n",
       "\n",
       "                              review_comment_message review_creation_date  \\\n",
       "0                                                NaN  2018-01-18 00:00:00   \n",
       "1                                                NaN  2018-03-10 00:00:00   \n",
       "2                                                NaN  2018-02-17 00:00:00   \n",
       "3              Recebi bem antes do prazo estipulado.  2017-04-21 00:00:00   \n",
       "4  Parabéns lojas lannister adorei comprar pela I...  2018-03-01 00:00:00   \n",
       "\n",
       "  review_answer_timestamp  \n",
       "0     2018-01-18 21:46:59  \n",
       "1     2018-03-11 03:05:13  \n",
       "2     2018-02-18 14:36:24  \n",
       "3     2017-04-21 22:02:06  \n",
       "4     2018-03-02 10:26:53  "
      ]
     },
     "execution_count": 245,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews = pd.read_csv('/home/raj/Downloads/Olist-Ecomm data/olist_order_reviews_dataset.csv')\n",
    "reviews.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The reviews here are given for each order. The aim is to collect the reviews product wise so as to perform analysis on how each product bought is percieved by the customers.<br>\n",
    "\n",
    "For each product the orders are found and thus the corresponding reviews. These are grouped together and mapped to product_id. This was for each product_id we have a set of reviews retieved from orders containing those products."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 340,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        None\n",
       "1        None\n",
       "2        None\n",
       "3        None\n",
       "4        None\n",
       "         ... \n",
       "32946    None\n",
       "32947    None\n",
       "32948    None\n",
       "32949    None\n",
       "32950    None\n",
       "Name: product_id, Length: 32951, dtype: object"
      ]
     },
     "execution_count": 340,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = list(reviews.columns)\n",
    "cols.append('product_id')\n",
    "prod_review = pd.DataFrame(columns= cols)\n",
    "\n",
    "def product_review(prod):\n",
    "    \n",
    "    global prod_review\n",
    "    oid = ord_items[ord_items.product_id == prod].order_id\n",
    "    rid = reviews[reviews.order_id.isin(oid)].review_id\n",
    "    data = reviews[reviews.review_id.isin(rid)].copy()\n",
    "    data['product_id'] = prod\n",
    "    prod_review = pd.concat([prod_review,data], ignore_index=True)\n",
    "    \n",
    "products.product_id.apply(product_review)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 353,
   "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>review_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>review_score</th>\n",
       "      <th>review_comment_title</th>\n",
       "      <th>review_comment_message</th>\n",
       "      <th>review_creation_date</th>\n",
       "      <th>review_answer_timestamp</th>\n",
       "      <th>product_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>d71da8fd8c6e3adef26be965f065b8a1</td>\n",
       "      <td>e17e4f88e31525f7deef66779844ddce</td>\n",
       "      <td>5</td>\n",
       "      <td>Vale a pena</td>\n",
       "      <td>Super adooorei o delineador ele é bem preto e ...</td>\n",
       "      <td>2018-04-28 00:00:00</td>\n",
       "      <td>2018-05-04 00:55:02</td>\n",
       "      <td>1e9e8ef04dbcff4541ed26657ea517e5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19866247db89845ef558ef19a6c26fcd</td>\n",
       "      <td>5236307716393b7114b53ee991f36956</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Excelente apontador! Em 3 segundos uma ponta p...</td>\n",
       "      <td>2018-02-17 00:00:00</td>\n",
       "      <td>2018-02-18 23:24:14</td>\n",
       "      <td>3aa071139cb16b67ca9e5dea641aaa2f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>32921b4e09c9a19d61fe46ce04231b84</td>\n",
       "      <td>01f66e58769f84129811d43eefd187fb</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-07-14 00:00:00</td>\n",
       "      <td>2018-07-16 16:20:04</td>\n",
       "      <td>96bd76ec8810374ed1b65e291975717f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70949d129965fb1ee32aaccec720a19e</td>\n",
       "      <td>143d00a4f2dde4e0364ee1821577adb3</td>\n",
       "      <td>1</td>\n",
       "      <td>produto divergente</td>\n",
       "      <td>modelo comprado: travesseiro memoria carinho B...</td>\n",
       "      <td>2018-08-07 00:00:00</td>\n",
       "      <td>2018-08-08 11:53:04</td>\n",
       "      <td>cef67bcfe19066a932b7673e239eb23d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>74c9c7d0bc153bbbc92e4c1597b8ba55</td>\n",
       "      <td>86cafb8794cb99a9b1b77fc8e48fbbbb</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-04-17 00:00:00</td>\n",
       "      <td>2018-04-18 04:29:47</td>\n",
       "      <td>9dc1a7de274444849c219cff195d0b71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          review_id                          order_id  \\\n",
       "0  d71da8fd8c6e3adef26be965f065b8a1  e17e4f88e31525f7deef66779844ddce   \n",
       "1  19866247db89845ef558ef19a6c26fcd  5236307716393b7114b53ee991f36956   \n",
       "2  32921b4e09c9a19d61fe46ce04231b84  01f66e58769f84129811d43eefd187fb   \n",
       "3  70949d129965fb1ee32aaccec720a19e  143d00a4f2dde4e0364ee1821577adb3   \n",
       "4  74c9c7d0bc153bbbc92e4c1597b8ba55  86cafb8794cb99a9b1b77fc8e48fbbbb   \n",
       "\n",
       "  review_score review_comment_title  \\\n",
       "0            5         Vale a pena    \n",
       "1            5                  NaN   \n",
       "2            5                  NaN   \n",
       "3            1   produto divergente   \n",
       "4            5                  NaN   \n",
       "\n",
       "                              review_comment_message review_creation_date  \\\n",
       "0  Super adooorei o delineador ele é bem preto e ...  2018-04-28 00:00:00   \n",
       "1  Excelente apontador! Em 3 segundos uma ponta p...  2018-02-17 00:00:00   \n",
       "2                                                NaN  2018-07-14 00:00:00   \n",
       "3  modelo comprado: travesseiro memoria carinho B...  2018-08-07 00:00:00   \n",
       "4                                                NaN  2018-04-17 00:00:00   \n",
       "\n",
       "  review_answer_timestamp                        product_id  \n",
       "0     2018-05-04 00:55:02  1e9e8ef04dbcff4541ed26657ea517e5  \n",
       "1     2018-02-18 23:24:14  3aa071139cb16b67ca9e5dea641aaa2f  \n",
       "2     2018-07-16 16:20:04  96bd76ec8810374ed1b65e291975717f  \n",
       "3     2018-08-08 11:53:04  cef67bcfe19066a932b7673e239eb23d  \n",
       "4     2018-04-18 04:29:47  9dc1a7de274444849c219cff195d0b71  "
      ]
     },
     "execution_count": 353,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prod_review.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 354,
   "metadata": {},
   "outputs": [],
   "source": [
    "prod_review.to_csv('./Olist_data/porduct_review.csv', index= False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}