Data Cleaning Task & Loading.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "367b86b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d9a58171",
   "metadata": {},
   "outputs": [],
   "source": [
    "customer_data = pd.read_csv(r\"C:\\Users\\vishal\\OneDrive\\Desktop\\Data Warehouse Project\\archive (4)\\olist_customers_dataset.csv\")\n",
    "geolocation_data = pd.read_csv(r\"C:\\Users\\vishal\\OneDrive\\Desktop\\Data Warehouse Project\\archive (4)\\olist_geolocation_dataset.csv\")\n",
    "order_item_data = pd.read_csv(r\"C:\\Users\\vishal\\OneDrive\\Desktop\\Data Warehouse Project\\archive (4)\\olist_order_items_dataset.csv\")\n",
    "order_payment_data = pd.read_csv(r\"C:\\Users\\vishal\\OneDrive\\Desktop\\Data Warehouse Project\\archive (4)\\olist_order_payments_dataset.csv\")\n",
    "order_review_data = pd.read_csv(r\"C:\\Users\\vishal\\OneDrive\\Desktop\\Data Warehouse Project\\archive (4)\\olist_order_reviews_dataset.csv\")\n",
    "orders_data = pd.read_csv(r\"C:\\Users\\vishal\\OneDrive\\Desktop\\Data Warehouse Project\\archive (4)\\olist_orders_dataset.csv\")\n",
    "product_data = pd.read_csv(r\"C:\\Users\\vishal\\OneDrive\\Desktop\\Data Warehouse Project\\archive (4)\\olist_products_dataset.csv\")\n",
    "sellers_data = pd.read_csv(r\"C:\\Users\\vishal\\OneDrive\\Desktop\\Data Warehouse Project\\archive (4)\\olist_sellers_dataset.csv\")\n",
    "category_name_dat = pd.read_csv(r\"C:\\Users\\vishal\\OneDrive\\Desktop\\Data Warehouse Project\\archive (4)\\product_category_name_translation.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "917eb866",
   "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",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99436</th>\n",
       "      <td>17ddf5dd5d51696bb3d7c6291687be6f</td>\n",
       "      <td>1a29b476fee25c95fbafc67c5ac95cf8</td>\n",
       "      <td>3937</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99437</th>\n",
       "      <td>e7b71a9017aa05c9a7fd292d714858e8</td>\n",
       "      <td>d52a67c98be1cf6a5c84435bd38d095d</td>\n",
       "      <td>6764</td>\n",
       "      <td>taboao da serra</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99438</th>\n",
       "      <td>5e28dfe12db7fb50a4b2f691faecea5e</td>\n",
       "      <td>e9f50caf99f032f0bf3c55141f019d99</td>\n",
       "      <td>60115</td>\n",
       "      <td>fortaleza</td>\n",
       "      <td>CE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99439</th>\n",
       "      <td>56b18e2166679b8a959d72dd06da27f9</td>\n",
       "      <td>73c2643a0a458b49f58cea58833b192e</td>\n",
       "      <td>92120</td>\n",
       "      <td>canoas</td>\n",
       "      <td>RS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99440</th>\n",
       "      <td>274fa6071e5e17fe303b9748641082c8</td>\n",
       "      <td>84732c5050c01db9b23e19ba39899398</td>\n",
       "      <td>6703</td>\n",
       "      <td>cotia</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99441 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            customer_id                customer_unique_id  \\\n",
       "0      06b8999e2fba1a1fbc88172c00ba8bc7  861eff4711a542e4b93843c6dd7febb0   \n",
       "1      18955e83d337fd6b2def6b18a428ac77  290c77bc529b7ac935b93aa66c333dc3   \n",
       "2      4e7b3e00288586ebd08712fdd0374a03  060e732b5b29e8181a18229c7b0b2b5e   \n",
       "3      b2b6027bc5c5109e529d4dc6358b12c3  259dac757896d24d7702b9acbbff3f3c   \n",
       "4      4f2d8ab171c80ec8364f7c12e35b23ad  345ecd01c38d18a9036ed96c73b8d066   \n",
       "...                                 ...                               ...   \n",
       "99436  17ddf5dd5d51696bb3d7c6291687be6f  1a29b476fee25c95fbafc67c5ac95cf8   \n",
       "99437  e7b71a9017aa05c9a7fd292d714858e8  d52a67c98be1cf6a5c84435bd38d095d   \n",
       "99438  5e28dfe12db7fb50a4b2f691faecea5e  e9f50caf99f032f0bf3c55141f019d99   \n",
       "99439  56b18e2166679b8a959d72dd06da27f9  73c2643a0a458b49f58cea58833b192e   \n",
       "99440  274fa6071e5e17fe303b9748641082c8  84732c5050c01db9b23e19ba39899398   \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",
       "99436                      3937              sao paulo             SP  \n",
       "99437                      6764        taboao da serra             SP  \n",
       "99438                     60115              fortaleza             CE  \n",
       "99439                     92120                 canoas             RS  \n",
       "99440                      6703                  cotia             SP  \n",
       "\n",
       "[99441 rows x 5 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customer_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7173a007",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Empty DataFrame\n",
      "Columns: [customer_id, customer_unique_id, customer_zip_code_prefix, customer_city, customer_state]\n",
      "Index: []\n"
     ]
    }
   ],
   "source": [
    "duplicates = customer_data[customer_data.duplicated()]\n",
    "# To display the duplicate rows\n",
    "print(duplicates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d23e84c2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "customer_id                 0\n",
      "customer_unique_id          0\n",
      "customer_zip_code_prefix    0\n",
      "customer_city               0\n",
      "customer_state              0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#Checking for null values\n",
    "null_check = customer_data.isnull().sum()\n",
    "print(null_check)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c19097df",
   "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",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99436</th>\n",
       "      <td>17ddf5dd5d51696bb3d7c6291687be6f</td>\n",
       "      <td>1a29b476fee25c95fbafc67c5ac95cf8</td>\n",
       "      <td>3937</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99437</th>\n",
       "      <td>e7b71a9017aa05c9a7fd292d714858e8</td>\n",
       "      <td>d52a67c98be1cf6a5c84435bd38d095d</td>\n",
       "      <td>6764</td>\n",
       "      <td>taboao da serra</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99438</th>\n",
       "      <td>5e28dfe12db7fb50a4b2f691faecea5e</td>\n",
       "      <td>e9f50caf99f032f0bf3c55141f019d99</td>\n",
       "      <td>60115</td>\n",
       "      <td>fortaleza</td>\n",
       "      <td>CE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99439</th>\n",
       "      <td>56b18e2166679b8a959d72dd06da27f9</td>\n",
       "      <td>73c2643a0a458b49f58cea58833b192e</td>\n",
       "      <td>92120</td>\n",
       "      <td>canoas</td>\n",
       "      <td>RS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99440</th>\n",
       "      <td>274fa6071e5e17fe303b9748641082c8</td>\n",
       "      <td>84732c5050c01db9b23e19ba39899398</td>\n",
       "      <td>6703</td>\n",
       "      <td>cotia</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99441 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            customer_id                customer_unique_id  \\\n",
       "0      06b8999e2fba1a1fbc88172c00ba8bc7  861eff4711a542e4b93843c6dd7febb0   \n",
       "1      18955e83d337fd6b2def6b18a428ac77  290c77bc529b7ac935b93aa66c333dc3   \n",
       "2      4e7b3e00288586ebd08712fdd0374a03  060e732b5b29e8181a18229c7b0b2b5e   \n",
       "3      b2b6027bc5c5109e529d4dc6358b12c3  259dac757896d24d7702b9acbbff3f3c   \n",
       "4      4f2d8ab171c80ec8364f7c12e35b23ad  345ecd01c38d18a9036ed96c73b8d066   \n",
       "...                                 ...                               ...   \n",
       "99436  17ddf5dd5d51696bb3d7c6291687be6f  1a29b476fee25c95fbafc67c5ac95cf8   \n",
       "99437  e7b71a9017aa05c9a7fd292d714858e8  d52a67c98be1cf6a5c84435bd38d095d   \n",
       "99438  5e28dfe12db7fb50a4b2f691faecea5e  e9f50caf99f032f0bf3c55141f019d99   \n",
       "99439  56b18e2166679b8a959d72dd06da27f9  73c2643a0a458b49f58cea58833b192e   \n",
       "99440  274fa6071e5e17fe303b9748641082c8  84732c5050c01db9b23e19ba39899398   \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",
       "99436                      3937              sao paulo             SP  \n",
       "99437                      6764        taboao da serra             SP  \n",
       "99438                     60115              fortaleza             CE  \n",
       "99439                     92120                 canoas             RS  \n",
       "99440                      6703                  cotia             SP  \n",
       "\n",
       "[99441 rows x 5 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Removing Duplicates from data set\n",
    "customer_data.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5eeb73f0",
   "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",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000158</th>\n",
       "      <td>99950</td>\n",
       "      <td>-28.068639</td>\n",
       "      <td>-52.010705</td>\n",
       "      <td>tapejara</td>\n",
       "      <td>RS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000159</th>\n",
       "      <td>99900</td>\n",
       "      <td>-27.877125</td>\n",
       "      <td>-52.224882</td>\n",
       "      <td>getulio vargas</td>\n",
       "      <td>RS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000160</th>\n",
       "      <td>99950</td>\n",
       "      <td>-28.071855</td>\n",
       "      <td>-52.014716</td>\n",
       "      <td>tapejara</td>\n",
       "      <td>RS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000161</th>\n",
       "      <td>99980</td>\n",
       "      <td>-28.388932</td>\n",
       "      <td>-51.846871</td>\n",
       "      <td>david canabarro</td>\n",
       "      <td>RS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000162</th>\n",
       "      <td>99950</td>\n",
       "      <td>-28.070104</td>\n",
       "      <td>-52.018658</td>\n",
       "      <td>tapejara</td>\n",
       "      <td>RS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000163 rows × 5 columns</p>\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",
       "1000158                        99950       -28.068639       -52.010705   \n",
       "1000159                        99900       -27.877125       -52.224882   \n",
       "1000160                        99950       -28.071855       -52.014716   \n",
       "1000161                        99980       -28.388932       -51.846871   \n",
       "1000162                        99950       -28.070104       -52.018658   \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  \n",
       "...                  ...               ...  \n",
       "1000158         tapejara                RS  \n",
       "1000159   getulio vargas                RS  \n",
       "1000160         tapejara                RS  \n",
       "1000161  david canabarro                RS  \n",
       "1000162         tapejara                RS  \n",
       "\n",
       "[1000163 rows x 5 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geolocation_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "31b0da5c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "geolocation_zip_code_prefix    0\n",
      "geolocation_lat                0\n",
      "geolocation_lng                0\n",
      "geolocation_city               0\n",
      "geolocation_state              0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#Checking for null values\n",
    "null = geolocation_data.isnull().sum()\n",
    "print(null)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "79e7b7c2",
   "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",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112645</th>\n",
       "      <td>fffc94f6ce00a00581880bf54a75a037</td>\n",
       "      <td>1</td>\n",
       "      <td>4aa6014eceb682077f9dc4bffebc05b0</td>\n",
       "      <td>b8bc237ba3788b23da09c0f1f3a3288c</td>\n",
       "      <td>2018-05-02 04:11:01</td>\n",
       "      <td>299.99</td>\n",
       "      <td>43.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112646</th>\n",
       "      <td>fffcd46ef2263f404302a634eb57f7eb</td>\n",
       "      <td>1</td>\n",
       "      <td>32e07fd915822b0765e448c4dd74c828</td>\n",
       "      <td>f3c38ab652836d21de61fb8314b69182</td>\n",
       "      <td>2018-07-20 04:31:48</td>\n",
       "      <td>350.00</td>\n",
       "      <td>36.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112647</th>\n",
       "      <td>fffce4705a9662cd70adb13d4a31832d</td>\n",
       "      <td>1</td>\n",
       "      <td>72a30483855e2eafc67aee5dc2560482</td>\n",
       "      <td>c3cfdc648177fdbbbb35635a37472c53</td>\n",
       "      <td>2017-10-30 17:14:25</td>\n",
       "      <td>99.90</td>\n",
       "      <td>16.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112648</th>\n",
       "      <td>fffe18544ffabc95dfada21779c9644f</td>\n",
       "      <td>1</td>\n",
       "      <td>9c422a519119dcad7575db5af1ba540e</td>\n",
       "      <td>2b3e4a2a3ea8e01938cabda2a3e5cc79</td>\n",
       "      <td>2017-08-21 00:04:32</td>\n",
       "      <td>55.99</td>\n",
       "      <td>8.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112649</th>\n",
       "      <td>fffe41c64501cc87c801fd61db3f6244</td>\n",
       "      <td>1</td>\n",
       "      <td>350688d9dc1e75ff97be326363655e01</td>\n",
       "      <td>f7ccf836d21b2fb1de37564105216cc1</td>\n",
       "      <td>2018-06-12 17:10:13</td>\n",
       "      <td>43.00</td>\n",
       "      <td>12.79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>112650 rows × 7 columns</p>\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",
       "112645  fffc94f6ce00a00581880bf54a75a037              1   \n",
       "112646  fffcd46ef2263f404302a634eb57f7eb              1   \n",
       "112647  fffce4705a9662cd70adb13d4a31832d              1   \n",
       "112648  fffe18544ffabc95dfada21779c9644f              1   \n",
       "112649  fffe41c64501cc87c801fd61db3f6244              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",
       "112645  4aa6014eceb682077f9dc4bffebc05b0  b8bc237ba3788b23da09c0f1f3a3288c   \n",
       "112646  32e07fd915822b0765e448c4dd74c828  f3c38ab652836d21de61fb8314b69182   \n",
       "112647  72a30483855e2eafc67aee5dc2560482  c3cfdc648177fdbbbb35635a37472c53   \n",
       "112648  9c422a519119dcad7575db5af1ba540e  2b3e4a2a3ea8e01938cabda2a3e5cc79   \n",
       "112649  350688d9dc1e75ff97be326363655e01  f7ccf836d21b2fb1de37564105216cc1   \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  \n",
       "...                     ...     ...            ...  \n",
       "112645  2018-05-02 04:11:01  299.99          43.41  \n",
       "112646  2018-07-20 04:31:48  350.00          36.53  \n",
       "112647  2017-10-30 17:14:25   99.90          16.95  \n",
       "112648  2017-08-21 00:04:32   55.99           8.72  \n",
       "112649  2018-06-12 17:10:13   43.00          12.79  \n",
       "\n",
       "[112650 rows x 7 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_item_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d559885b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                order_id  order_item_id  \\\n",
      "14      0008288aa423d2a3f00fcb17cd7d8719              2   \n",
      "33      00143d0f86d6fbd9f9b38ab440ac16f5              2   \n",
      "34      00143d0f86d6fbd9f9b38ab440ac16f5              3   \n",
      "43      001ab0a7578dd66cd4b0a71f5b6e1e41              2   \n",
      "44      001ab0a7578dd66cd4b0a71f5b6e1e41              3   \n",
      "...                                  ...            ...   \n",
      "112617  ffecd5a79a0084f6a592288c67e3c298              3   \n",
      "112635  fff8287bbae429a99bb7e8c21d151c41              2   \n",
      "112641  fffb9224b6fc7c43ebb0904318b10b5f              2   \n",
      "112642  fffb9224b6fc7c43ebb0904318b10b5f              3   \n",
      "112643  fffb9224b6fc7c43ebb0904318b10b5f              4   \n",
      "\n",
      "                              product_id                         seller_id  \\\n",
      "14      368c6c730842d78016ad823897a372db  1f50f920176fa81dab994f9023523100   \n",
      "33      e95ee6822b66ac6058e2e4aff656071a  a17f621c590ea0fab3d5d883e1630ec6   \n",
      "34      e95ee6822b66ac6058e2e4aff656071a  a17f621c590ea0fab3d5d883e1630ec6   \n",
      "43      0b0172eb0fd18479d29c3bc122c058c2  5656537e588803a555b8eb41f07a944b   \n",
      "44      0b0172eb0fd18479d29c3bc122c058c2  5656537e588803a555b8eb41f07a944b   \n",
      "...                                  ...                               ...   \n",
      "112617  50fd2b788dc166edd20512370dac54df  8b321bb669392f5163d04c59e235e066   \n",
      "112635  bee2e070c39f3dd2f6883a17a5f0da45  4e922959ae960d389249c378d1c939f5   \n",
      "112641  43423cdffde7fda63d0414ed38c11a73  b1fc4f64df5a0e8b6913ab38803c57a9   \n",
      "112642  43423cdffde7fda63d0414ed38c11a73  b1fc4f64df5a0e8b6913ab38803c57a9   \n",
      "112643  43423cdffde7fda63d0414ed38c11a73  b1fc4f64df5a0e8b6913ab38803c57a9   \n",
      "\n",
      "        shipping_limit_date   price  freight_value  \n",
      "14      2018-02-21 02:55:52   49.90          13.37  \n",
      "33      2017-10-20 16:07:52   21.33          15.10  \n",
      "34      2017-10-20 16:07:52   21.33          15.10  \n",
      "43      2018-01-04 02:33:42   24.89          17.63  \n",
      "44      2018-01-04 02:33:42   24.89          17.63  \n",
      "...                     ...     ...            ...  \n",
      "112617  2018-03-05 20:15:27   21.90          15.79  \n",
      "112635  2018-03-27 12:29:22  180.00          48.14  \n",
      "112641  2017-11-03 02:55:58   55.00          34.19  \n",
      "112642  2017-11-03 02:55:58   55.00          34.19  \n",
      "112643  2017-11-03 02:55:58   55.00          34.19  \n",
      "\n",
      "[10225 rows x 7 columns]\n"
     ]
    }
   ],
   "source": [
    "#Checkinh for duplicates\n",
    "duplicates = order_item_data[order_item_data.duplicated(subset=['product_id', 'order_id'])]\n",
    "print(duplicates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f342004a",
   "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",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103881</th>\n",
       "      <td>0406037ad97740d563a178ecc7a2075c</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>363.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103882</th>\n",
       "      <td>7b905861d7c825891d6347454ea7863f</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>2</td>\n",
       "      <td>96.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103883</th>\n",
       "      <td>32609bbb3dd69b3c066a6860554a77bf</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>47.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103884</th>\n",
       "      <td>b8b61059626efa996a60be9bb9320e10</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>5</td>\n",
       "      <td>369.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103885</th>\n",
       "      <td>28bbae6599b09d39ca406b747b6632b1</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>191.58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>103886 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                order_id  payment_sequential payment_type  \\\n",
       "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",
       "103881  0406037ad97740d563a178ecc7a2075c                   1       boleto   \n",
       "103882  7b905861d7c825891d6347454ea7863f                   1  credit_card   \n",
       "103883  32609bbb3dd69b3c066a6860554a77bf                   1  credit_card   \n",
       "103884  b8b61059626efa996a60be9bb9320e10                   1  credit_card   \n",
       "103885  28bbae6599b09d39ca406b747b6632b1                   1       boleto   \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  \n",
       "...                      ...            ...  \n",
       "103881                     1         363.31  \n",
       "103882                     2          96.80  \n",
       "103883                     1          47.77  \n",
       "103884                     5         369.54  \n",
       "103885                     1         191.58  \n",
       "\n",
       "[103886 rows x 5 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_payment_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "aa67edaf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "order_id                0\n",
      "payment_sequential      0\n",
      "payment_type            0\n",
      "payment_installments    0\n",
      "payment_value           0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Checking for null values in 'payment_type' and 'payment_sequential' columns\n",
    "null = order_payment_data.isnull().sum()\n",
    "print(null)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "119f8783",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                order_id  payment_sequential payment_type  \\\n",
      "1456    683bf306149bb869980b68d48a1bd6ab                   1  credit_card   \n",
      "2324    e6a66a8350bb88497954d37688ab123e                   2      voucher   \n",
      "2393    8e5148bee82a7e42c5f9ba76161dc51a                   1  credit_card   \n",
      "2414    816ccd9d21435796e8ffa9802b2a782f                   1  credit_card   \n",
      "2497    2cbcb371aee438c59b722a21d83597e0                   2      voucher   \n",
      "...                                  ...                 ...          ...   \n",
      "103778  fd86c80924b4be8fb7f58c4ecc680dae                   1  credit_card   \n",
      "103817  6d4616de4341417e17978fe57aec1c46                   1  credit_card   \n",
      "103860  31bc09fdbd701a7a4f9b55b5955b8687                   6      voucher   \n",
      "103869  c9b01bef18eb84888f0fd071b8413b38                   1  credit_card   \n",
      "103874  d41f1036712e18ee525939d08b7a1c98                   1  credit_card   \n",
      "\n",
      "        payment_installments  payment_value  \n",
      "1456                       1           8.58  \n",
      "2324                       1          10.51  \n",
      "2393                       1           0.67  \n",
      "2414                       1           5.65  \n",
      "2497                       1           7.80  \n",
      "...                      ...            ...  \n",
      "103778                     1          76.10  \n",
      "103817                     1          19.18  \n",
      "103860                     1          77.99  \n",
      "103869                     6         238.16  \n",
      "103874                     1           3.57  \n",
      "\n",
      "[4446 rows x 5 columns]\n"
     ]
    }
   ],
   "source": [
    "#Checking for duplicates\n",
    "duplicates = order_payment_data[order_payment_data.duplicated(subset=['order_id'])]\n",
    "print(duplicates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ed6c593b",
   "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",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99219</th>\n",
       "      <td>574ed12dd733e5fa530cfd4bbf39d7c9</td>\n",
       "      <td>2a8c23fee101d4d5662fa670396eb8da</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-07-07 00:00:00</td>\n",
       "      <td>2018-07-14 17:18:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99220</th>\n",
       "      <td>f3897127253a9592a73be9bdfdf4ed7a</td>\n",
       "      <td>22ec9f0669f784db00fa86d035cf8602</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-12-09 00:00:00</td>\n",
       "      <td>2017-12-11 20:06:42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99221</th>\n",
       "      <td>b3de70c89b1510c4cd3d0649fd302472</td>\n",
       "      <td>55d4004744368f5571d1f590031933e4</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Excelente mochila, entrega super rápida. Super...</td>\n",
       "      <td>2018-03-22 00:00:00</td>\n",
       "      <td>2018-03-23 09:10:43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99222</th>\n",
       "      <td>1adeb9d84d72fe4e337617733eb85149</td>\n",
       "      <td>7725825d039fc1f0ceb7635e3f7d9206</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-07-01 00:00:00</td>\n",
       "      <td>2018-07-02 12:59:13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99223</th>\n",
       "      <td>efe49f1d6f951dd88b51e6ccd4cc548f</td>\n",
       "      <td>90531360ecb1eec2a1fbb265a0db0508</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>meu produto chegou e ja tenho que devolver, po...</td>\n",
       "      <td>2017-07-03 00:00:00</td>\n",
       "      <td>2017-07-03 21:01:49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99224 rows × 7 columns</p>\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",
       "99219  574ed12dd733e5fa530cfd4bbf39d7c9  2a8c23fee101d4d5662fa670396eb8da   \n",
       "99220  f3897127253a9592a73be9bdfdf4ed7a  22ec9f0669f784db00fa86d035cf8602   \n",
       "99221  b3de70c89b1510c4cd3d0649fd302472  55d4004744368f5571d1f590031933e4   \n",
       "99222  1adeb9d84d72fe4e337617733eb85149  7725825d039fc1f0ceb7635e3f7d9206   \n",
       "99223  efe49f1d6f951dd88b51e6ccd4cc548f  90531360ecb1eec2a1fbb265a0db0508   \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",
       "99219             5                  NaN   \n",
       "99220             5                  NaN   \n",
       "99221             5                  NaN   \n",
       "99222             4                  NaN   \n",
       "99223             1                  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",
       "99219                                                NaN  2018-07-07 00:00:00   \n",
       "99220                                                NaN  2017-12-09 00:00:00   \n",
       "99221  Excelente mochila, entrega super rápida. Super...  2018-03-22 00:00:00   \n",
       "99222                                                NaN  2018-07-01 00:00:00   \n",
       "99223  meu produto chegou e ja tenho que devolver, po...  2017-07-03 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  \n",
       "...                       ...  \n",
       "99219     2018-07-14 17:18:30  \n",
       "99220     2017-12-11 20:06:42  \n",
       "99221     2018-03-23 09:10:43  \n",
       "99222     2018-07-02 12:59:13  \n",
       "99223     2017-07-03 21:01:49  \n",
       "\n",
       "[99224 rows x 7 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_review_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "5f0771a5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "review_id                      0\n",
      "order_id                       0\n",
      "review_score                   0\n",
      "review_comment_title       87656\n",
      "review_comment_message     58247\n",
      "review_creation_date           0\n",
      "review_answer_timestamp        0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Checking for null values in 'payment_type' and 'payment_sequential' columns\n",
    "null = order_review_data.isnull().sum()\n",
    "print(null)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "0c162054",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Empty DataFrame\n",
      "Columns: [review_id, order_id, review_score, review_comment_title, review_comment_message, review_creation_date, review_answer_timestamp]\n",
      "Index: []\n"
     ]
    }
   ],
   "source": [
    "# Checking for duplicates in 'review_id' and 'order_id' columns\n",
    "duplicates = order_review_data[order_review_data.duplicated()]\n",
    "print(duplicates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "2ddd823e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop columns 'review_comment_title' and 'review_comment_message' if they contain only null values\n",
    "order_review_data = order_review_data.drop(columns=['review_comment_title', 'review_comment_message'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "b26bffe5",
   "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_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>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>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>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>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>2018-03-01 00:00:00</td>\n",
       "      <td>2018-03-02 10:26:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99219</th>\n",
       "      <td>574ed12dd733e5fa530cfd4bbf39d7c9</td>\n",
       "      <td>2a8c23fee101d4d5662fa670396eb8da</td>\n",
       "      <td>5</td>\n",
       "      <td>2018-07-07 00:00:00</td>\n",
       "      <td>2018-07-14 17:18:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99220</th>\n",
       "      <td>f3897127253a9592a73be9bdfdf4ed7a</td>\n",
       "      <td>22ec9f0669f784db00fa86d035cf8602</td>\n",
       "      <td>5</td>\n",
       "      <td>2017-12-09 00:00:00</td>\n",
       "      <td>2017-12-11 20:06:42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99221</th>\n",
       "      <td>b3de70c89b1510c4cd3d0649fd302472</td>\n",
       "      <td>55d4004744368f5571d1f590031933e4</td>\n",
       "      <td>5</td>\n",
       "      <td>2018-03-22 00:00:00</td>\n",
       "      <td>2018-03-23 09:10:43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99222</th>\n",
       "      <td>1adeb9d84d72fe4e337617733eb85149</td>\n",
       "      <td>7725825d039fc1f0ceb7635e3f7d9206</td>\n",
       "      <td>4</td>\n",
       "      <td>2018-07-01 00:00:00</td>\n",
       "      <td>2018-07-02 12:59:13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99223</th>\n",
       "      <td>efe49f1d6f951dd88b51e6ccd4cc548f</td>\n",
       "      <td>90531360ecb1eec2a1fbb265a0db0508</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-07-03 00:00:00</td>\n",
       "      <td>2017-07-03 21:01:49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99224 rows × 5 columns</p>\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",
       "99219  574ed12dd733e5fa530cfd4bbf39d7c9  2a8c23fee101d4d5662fa670396eb8da   \n",
       "99220  f3897127253a9592a73be9bdfdf4ed7a  22ec9f0669f784db00fa86d035cf8602   \n",
       "99221  b3de70c89b1510c4cd3d0649fd302472  55d4004744368f5571d1f590031933e4   \n",
       "99222  1adeb9d84d72fe4e337617733eb85149  7725825d039fc1f0ceb7635e3f7d9206   \n",
       "99223  efe49f1d6f951dd88b51e6ccd4cc548f  90531360ecb1eec2a1fbb265a0db0508   \n",
       "\n",
       "       review_score review_creation_date review_answer_timestamp  \n",
       "0                 4  2018-01-18 00:00:00     2018-01-18 21:46:59  \n",
       "1                 5  2018-03-10 00:00:00     2018-03-11 03:05:13  \n",
       "2                 5  2018-02-17 00:00:00     2018-02-18 14:36:24  \n",
       "3                 5  2017-04-21 00:00:00     2017-04-21 22:02:06  \n",
       "4                 5  2018-03-01 00:00:00     2018-03-02 10:26:53  \n",
       "...             ...                  ...                     ...  \n",
       "99219             5  2018-07-07 00:00:00     2018-07-14 17:18:30  \n",
       "99220             5  2017-12-09 00:00:00     2017-12-11 20:06:42  \n",
       "99221             5  2018-03-22 00:00:00     2018-03-23 09:10:43  \n",
       "99222             4  2018-07-01 00:00:00     2018-07-02 12:59:13  \n",
       "99223             1  2017-07-03 00:00:00     2017-07-03 21:01:49  \n",
       "\n",
       "[99224 rows x 5 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_review_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "92542da9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>order_status</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>order_approved_at</th>\n",
       "      <th>order_delivered_carrier_date</th>\n",
       "      <th>order_delivered_customer_date</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>e481f51cbdc54678b7cc49136f2d6af7</td>\n",
       "      <td>9ef432eb6251297304e76186b10a928d</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-10-02 10:56:33</td>\n",
       "      <td>2017-10-02 11:07:15</td>\n",
       "      <td>2017-10-04 19:55:00</td>\n",
       "      <td>2017-10-10 21:25:13</td>\n",
       "      <td>2017-10-18 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",
       "    <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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99436</th>\n",
       "      <td>9c5dedf39a927c1b2549525ed64a053c</td>\n",
       "      <td>39bd1228ee8140590ac3aca26f2dfe00</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-03-09 09:54:05</td>\n",
       "      <td>2017-03-09 09:54:05</td>\n",
       "      <td>2017-03-10 11:18:03</td>\n",
       "      <td>2017-03-17 15:08:01</td>\n",
       "      <td>2017-03-28 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99437</th>\n",
       "      <td>63943bddc261676b46f01ca7ac2f7bd8</td>\n",
       "      <td>1fca14ff2861355f6e5f14306ff977a7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-06 12:58:58</td>\n",
       "      <td>2018-02-06 13:10:37</td>\n",
       "      <td>2018-02-07 23:22:42</td>\n",
       "      <td>2018-02-28 17:37:56</td>\n",
       "      <td>2018-03-02 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99438</th>\n",
       "      <td>83c1379a015df1e13d02aae0204711ab</td>\n",
       "      <td>1aa71eb042121263aafbe80c1b562c9c</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-08-27 14:46:43</td>\n",
       "      <td>2017-08-27 15:04:16</td>\n",
       "      <td>2017-08-28 20:52:26</td>\n",
       "      <td>2017-09-21 11:24:17</td>\n",
       "      <td>2017-09-27 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99439</th>\n",
       "      <td>11c177c8e97725db2631073c19f07b62</td>\n",
       "      <td>b331b74b18dc79bcdf6532d51e1637c1</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-01-08 21:28:27</td>\n",
       "      <td>2018-01-08 21:36:21</td>\n",
       "      <td>2018-01-12 15:35:03</td>\n",
       "      <td>2018-01-25 23:32:54</td>\n",
       "      <td>2018-02-15 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99440</th>\n",
       "      <td>66dea50a8b16d9b4dee7af250b4be1a5</td>\n",
       "      <td>edb027a75a1449115f6b43211ae02a24</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-03-08 20:57:30</td>\n",
       "      <td>2018-03-09 11:20:28</td>\n",
       "      <td>2018-03-09 22:11:59</td>\n",
       "      <td>2018-03-16 13:08:30</td>\n",
       "      <td>2018-04-03 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99441 rows × 8 columns</p>\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",
       "99436  9c5dedf39a927c1b2549525ed64a053c  39bd1228ee8140590ac3aca26f2dfe00   \n",
       "99437  63943bddc261676b46f01ca7ac2f7bd8  1fca14ff2861355f6e5f14306ff977a7   \n",
       "99438  83c1379a015df1e13d02aae0204711ab  1aa71eb042121263aafbe80c1b562c9c   \n",
       "99439  11c177c8e97725db2631073c19f07b62  b331b74b18dc79bcdf6532d51e1637c1   \n",
       "99440  66dea50a8b16d9b4dee7af250b4be1a5  edb027a75a1449115f6b43211ae02a24   \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",
       "99436    delivered      2017-03-09 09:54:05  2017-03-09 09:54:05   \n",
       "99437    delivered      2018-02-06 12:58:58  2018-02-06 13:10:37   \n",
       "99438    delivered      2017-08-27 14:46:43  2017-08-27 15:04:16   \n",
       "99439    delivered      2018-01-08 21:28:27  2018-01-08 21:36:21   \n",
       "99440    delivered      2018-03-08 20:57:30  2018-03-09 11:20:28   \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",
       "99436          2017-03-10 11:18:03           2017-03-17 15:08:01   \n",
       "99437          2018-02-07 23:22:42           2018-02-28 17:37:56   \n",
       "99438          2017-08-28 20:52:26           2017-09-21 11:24:17   \n",
       "99439          2018-01-12 15:35:03           2018-01-25 23:32:54   \n",
       "99440          2018-03-09 22:11:59           2018-03-16 13:08:30   \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  \n",
       "...                             ...  \n",
       "99436           2017-03-28 00:00:00  \n",
       "99437           2018-03-02 00:00:00  \n",
       "99438           2017-09-27 00:00:00  \n",
       "99439           2018-02-15 00:00:00  \n",
       "99440           2018-04-03 00:00:00  \n",
       "\n",
       "[99441 rows x 8 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "fc23dda5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert 'order_purchase_timestamp' to string type\n",
    "orders_data['order_purchase_timestamp'] = orders_data['order_purchase_timestamp'].astype(str)\n",
    "# Split 'order_delivered_customer_date' column into date and time\n",
    "orders_data[['order_purchase_date', 'order_purchase_time']] = orders_data['order_delivered_customer_date'].str.split(\" \", expand=True)\n",
    "\n",
    "\n",
    "# Convert 'order_delivered_customer_date' to string type\n",
    "orders_data['order_delivered_customer_date'] = orders_data['order_delivered_customer_date'].astype(str)\n",
    "# Split 'order_delivered_customer_date' column into date and time\n",
    "orders_data[['order_delivered_date', 'order_delivered_time']] = orders_data['order_delivered_customer_date'].str.split(\" \", expand=True)\n",
    "\n",
    "\n",
    "# Convert 'order_estimated_delivery_date' to string type\n",
    "orders_data['order_estimated_delivery_date'] = orders_data['order_estimated_delivery_date'].astype(str)\n",
    "# Split 'order_delivered_customer_date' column into date and time\n",
    "orders_data[['order_estimated_delivery_date', 'order_estimated_delivery_time']] = orders_data['order_estimated_delivery_date'].str.split(\" \", expand=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "3a29d397",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>order_status</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>order_approved_at</th>\n",
       "      <th>order_delivered_carrier_date</th>\n",
       "      <th>order_delivered_customer_date</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "      <th>order_purchase_date</th>\n",
       "      <th>order_purchase_time</th>\n",
       "      <th>order_delivered_date</th>\n",
       "      <th>order_delivered_time</th>\n",
       "      <th>order_estimated_delivery_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>e481f51cbdc54678b7cc49136f2d6af7</td>\n",
       "      <td>9ef432eb6251297304e76186b10a928d</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-10-02 10:56:33</td>\n",
       "      <td>2017-10-02 11:07:15</td>\n",
       "      <td>2017-10-04 19:55:00</td>\n",
       "      <td>2017-10-10 21:25:13</td>\n",
       "      <td>2017-10-18</td>\n",
       "      <td>2017-10-10</td>\n",
       "      <td>21:25:13</td>\n",
       "      <td>2017-10-10</td>\n",
       "      <td>21:25:13</td>\n",
       "      <td>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</td>\n",
       "      <td>2018-08-07</td>\n",
       "      <td>15:27:45</td>\n",
       "      <td>2018-08-07</td>\n",
       "      <td>15:27:45</td>\n",
       "      <td>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</td>\n",
       "      <td>2018-08-17</td>\n",
       "      <td>18:06:29</td>\n",
       "      <td>2018-08-17</td>\n",
       "      <td>18:06:29</td>\n",
       "      <td>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</td>\n",
       "      <td>2017-12-02</td>\n",
       "      <td>00:28:42</td>\n",
       "      <td>2017-12-02</td>\n",
       "      <td>00:28:42</td>\n",
       "      <td>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</td>\n",
       "      <td>2018-02-16</td>\n",
       "      <td>18:17:02</td>\n",
       "      <td>2018-02-16</td>\n",
       "      <td>18:17:02</td>\n",
       "      <td>00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99436</th>\n",
       "      <td>9c5dedf39a927c1b2549525ed64a053c</td>\n",
       "      <td>39bd1228ee8140590ac3aca26f2dfe00</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-03-09 09:54:05</td>\n",
       "      <td>2017-03-09 09:54:05</td>\n",
       "      <td>2017-03-10 11:18:03</td>\n",
       "      <td>2017-03-17 15:08:01</td>\n",
       "      <td>2017-03-28</td>\n",
       "      <td>2017-03-17</td>\n",
       "      <td>15:08:01</td>\n",
       "      <td>2017-03-17</td>\n",
       "      <td>15:08:01</td>\n",
       "      <td>00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99437</th>\n",
       "      <td>63943bddc261676b46f01ca7ac2f7bd8</td>\n",
       "      <td>1fca14ff2861355f6e5f14306ff977a7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-06 12:58:58</td>\n",
       "      <td>2018-02-06 13:10:37</td>\n",
       "      <td>2018-02-07 23:22:42</td>\n",
       "      <td>2018-02-28 17:37:56</td>\n",
       "      <td>2018-03-02</td>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>17:37:56</td>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>17:37:56</td>\n",
       "      <td>00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99438</th>\n",
       "      <td>83c1379a015df1e13d02aae0204711ab</td>\n",
       "      <td>1aa71eb042121263aafbe80c1b562c9c</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-08-27 14:46:43</td>\n",
       "      <td>2017-08-27 15:04:16</td>\n",
       "      <td>2017-08-28 20:52:26</td>\n",
       "      <td>2017-09-21 11:24:17</td>\n",
       "      <td>2017-09-27</td>\n",
       "      <td>2017-09-21</td>\n",
       "      <td>11:24:17</td>\n",
       "      <td>2017-09-21</td>\n",
       "      <td>11:24:17</td>\n",
       "      <td>00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99439</th>\n",
       "      <td>11c177c8e97725db2631073c19f07b62</td>\n",
       "      <td>b331b74b18dc79bcdf6532d51e1637c1</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-01-08 21:28:27</td>\n",
       "      <td>2018-01-08 21:36:21</td>\n",
       "      <td>2018-01-12 15:35:03</td>\n",
       "      <td>2018-01-25 23:32:54</td>\n",
       "      <td>2018-02-15</td>\n",
       "      <td>2018-01-25</td>\n",
       "      <td>23:32:54</td>\n",
       "      <td>2018-01-25</td>\n",
       "      <td>23:32:54</td>\n",
       "      <td>00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99440</th>\n",
       "      <td>66dea50a8b16d9b4dee7af250b4be1a5</td>\n",
       "      <td>edb027a75a1449115f6b43211ae02a24</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-03-08 20:57:30</td>\n",
       "      <td>2018-03-09 11:20:28</td>\n",
       "      <td>2018-03-09 22:11:59</td>\n",
       "      <td>2018-03-16 13:08:30</td>\n",
       "      <td>2018-04-03</td>\n",
       "      <td>2018-03-16</td>\n",
       "      <td>13:08:30</td>\n",
       "      <td>2018-03-16</td>\n",
       "      <td>13:08:30</td>\n",
       "      <td>00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99441 rows × 13 columns</p>\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",
       "99436  9c5dedf39a927c1b2549525ed64a053c  39bd1228ee8140590ac3aca26f2dfe00   \n",
       "99437  63943bddc261676b46f01ca7ac2f7bd8  1fca14ff2861355f6e5f14306ff977a7   \n",
       "99438  83c1379a015df1e13d02aae0204711ab  1aa71eb042121263aafbe80c1b562c9c   \n",
       "99439  11c177c8e97725db2631073c19f07b62  b331b74b18dc79bcdf6532d51e1637c1   \n",
       "99440  66dea50a8b16d9b4dee7af250b4be1a5  edb027a75a1449115f6b43211ae02a24   \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",
       "99436    delivered      2017-03-09 09:54:05  2017-03-09 09:54:05   \n",
       "99437    delivered      2018-02-06 12:58:58  2018-02-06 13:10:37   \n",
       "99438    delivered      2017-08-27 14:46:43  2017-08-27 15:04:16   \n",
       "99439    delivered      2018-01-08 21:28:27  2018-01-08 21:36:21   \n",
       "99440    delivered      2018-03-08 20:57:30  2018-03-09 11:20:28   \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",
       "99436          2017-03-10 11:18:03           2017-03-17 15:08:01   \n",
       "99437          2018-02-07 23:22:42           2018-02-28 17:37:56   \n",
       "99438          2017-08-28 20:52:26           2017-09-21 11:24:17   \n",
       "99439          2018-01-12 15:35:03           2018-01-25 23:32:54   \n",
       "99440          2018-03-09 22:11:59           2018-03-16 13:08:30   \n",
       "\n",
       "      order_estimated_delivery_date order_purchase_date order_purchase_time  \\\n",
       "0                        2017-10-18          2017-10-10            21:25:13   \n",
       "1                        2018-08-13          2018-08-07            15:27:45   \n",
       "2                        2018-09-04          2018-08-17            18:06:29   \n",
       "3                        2017-12-15          2017-12-02            00:28:42   \n",
       "4                        2018-02-26          2018-02-16            18:17:02   \n",
       "...                             ...                 ...                 ...   \n",
       "99436                    2017-03-28          2017-03-17            15:08:01   \n",
       "99437                    2018-03-02          2018-02-28            17:37:56   \n",
       "99438                    2017-09-27          2017-09-21            11:24:17   \n",
       "99439                    2018-02-15          2018-01-25            23:32:54   \n",
       "99440                    2018-04-03          2018-03-16            13:08:30   \n",
       "\n",
       "      order_delivered_date order_delivered_time order_estimated_delivery_time  \n",
       "0               2017-10-10             21:25:13                      00:00:00  \n",
       "1               2018-08-07             15:27:45                      00:00:00  \n",
       "2               2018-08-17             18:06:29                      00:00:00  \n",
       "3               2017-12-02             00:28:42                      00:00:00  \n",
       "4               2018-02-16             18:17:02                      00:00:00  \n",
       "...                    ...                  ...                           ...  \n",
       "99436           2017-03-17             15:08:01                      00:00:00  \n",
       "99437           2018-02-28             17:37:56                      00:00:00  \n",
       "99438           2017-09-21             11:24:17                      00:00:00  \n",
       "99439           2018-01-25             23:32:54                      00:00:00  \n",
       "99440           2018-03-16             13:08:30                      00:00:00  \n",
       "\n",
       "[99441 rows x 13 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "2e378932",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop columns which is not in use\n",
    "orders_data = orders_data.drop(columns=['order_purchase_timestamp', 'order_approved_at', 'order_delivered_carrier_date', 'order_delivered_customer_date', 'order_estimated_delivery_time' ])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "dc1cf830",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>order_status</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "      <th>order_purchase_date</th>\n",
       "      <th>order_purchase_time</th>\n",
       "      <th>order_delivered_date</th>\n",
       "      <th>order_delivered_time</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-18</td>\n",
       "      <td>2017-10-10</td>\n",
       "      <td>21:25:13</td>\n",
       "      <td>2017-10-10</td>\n",
       "      <td>21:25:13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>53cdb2fc8bc7dce0b6741e2150273451</td>\n",
       "      <td>b0830fb4747a6c6d20dea0b8c802d7ef</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-08-13</td>\n",
       "      <td>2018-08-07</td>\n",
       "      <td>15:27:45</td>\n",
       "      <td>2018-08-07</td>\n",
       "      <td>15:27:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>47770eb9100c2d0c44946d9cf07ec65d</td>\n",
       "      <td>41ce2a54c0b03bf3443c3d931a367089</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-09-04</td>\n",
       "      <td>2018-08-17</td>\n",
       "      <td>18:06:29</td>\n",
       "      <td>2018-08-17</td>\n",
       "      <td>18:06:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>949d5b44dbf5de918fe9c16f97b45f8a</td>\n",
       "      <td>f88197465ea7920adcdbec7375364d82</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-12-15</td>\n",
       "      <td>2017-12-02</td>\n",
       "      <td>00:28:42</td>\n",
       "      <td>2017-12-02</td>\n",
       "      <td>00:28:42</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-26</td>\n",
       "      <td>2018-02-16</td>\n",
       "      <td>18:17:02</td>\n",
       "      <td>2018-02-16</td>\n",
       "      <td>18:17:02</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99436</th>\n",
       "      <td>9c5dedf39a927c1b2549525ed64a053c</td>\n",
       "      <td>39bd1228ee8140590ac3aca26f2dfe00</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-03-28</td>\n",
       "      <td>2017-03-17</td>\n",
       "      <td>15:08:01</td>\n",
       "      <td>2017-03-17</td>\n",
       "      <td>15:08:01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99437</th>\n",
       "      <td>63943bddc261676b46f01ca7ac2f7bd8</td>\n",
       "      <td>1fca14ff2861355f6e5f14306ff977a7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-03-02</td>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>17:37:56</td>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>17:37:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99438</th>\n",
       "      <td>83c1379a015df1e13d02aae0204711ab</td>\n",
       "      <td>1aa71eb042121263aafbe80c1b562c9c</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-09-27</td>\n",
       "      <td>2017-09-21</td>\n",
       "      <td>11:24:17</td>\n",
       "      <td>2017-09-21</td>\n",
       "      <td>11:24:17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99439</th>\n",
       "      <td>11c177c8e97725db2631073c19f07b62</td>\n",
       "      <td>b331b74b18dc79bcdf6532d51e1637c1</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-15</td>\n",
       "      <td>2018-01-25</td>\n",
       "      <td>23:32:54</td>\n",
       "      <td>2018-01-25</td>\n",
       "      <td>23:32:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99440</th>\n",
       "      <td>66dea50a8b16d9b4dee7af250b4be1a5</td>\n",
       "      <td>edb027a75a1449115f6b43211ae02a24</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-04-03</td>\n",
       "      <td>2018-03-16</td>\n",
       "      <td>13:08:30</td>\n",
       "      <td>2018-03-16</td>\n",
       "      <td>13:08:30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99441 rows × 8 columns</p>\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",
       "99436  9c5dedf39a927c1b2549525ed64a053c  39bd1228ee8140590ac3aca26f2dfe00   \n",
       "99437  63943bddc261676b46f01ca7ac2f7bd8  1fca14ff2861355f6e5f14306ff977a7   \n",
       "99438  83c1379a015df1e13d02aae0204711ab  1aa71eb042121263aafbe80c1b562c9c   \n",
       "99439  11c177c8e97725db2631073c19f07b62  b331b74b18dc79bcdf6532d51e1637c1   \n",
       "99440  66dea50a8b16d9b4dee7af250b4be1a5  edb027a75a1449115f6b43211ae02a24   \n",
       "\n",
       "      order_status order_estimated_delivery_date order_purchase_date  \\\n",
       "0        delivered                    2017-10-18          2017-10-10   \n",
       "1        delivered                    2018-08-13          2018-08-07   \n",
       "2        delivered                    2018-09-04          2018-08-17   \n",
       "3        delivered                    2017-12-15          2017-12-02   \n",
       "4        delivered                    2018-02-26          2018-02-16   \n",
       "...            ...                           ...                 ...   \n",
       "99436    delivered                    2017-03-28          2017-03-17   \n",
       "99437    delivered                    2018-03-02          2018-02-28   \n",
       "99438    delivered                    2017-09-27          2017-09-21   \n",
       "99439    delivered                    2018-02-15          2018-01-25   \n",
       "99440    delivered                    2018-04-03          2018-03-16   \n",
       "\n",
       "      order_purchase_time order_delivered_date order_delivered_time  \n",
       "0                21:25:13           2017-10-10             21:25:13  \n",
       "1                15:27:45           2018-08-07             15:27:45  \n",
       "2                18:06:29           2018-08-17             18:06:29  \n",
       "3                00:28:42           2017-12-02             00:28:42  \n",
       "4                18:17:02           2018-02-16             18:17:02  \n",
       "...                   ...                  ...                  ...  \n",
       "99436            15:08:01           2017-03-17             15:08:01  \n",
       "99437            17:37:56           2018-02-28             17:37:56  \n",
       "99438            11:24:17           2017-09-21             11:24:17  \n",
       "99439            23:32:54           2018-01-25             23:32:54  \n",
       "99440            13:08:30           2018-03-16             13:08:30  \n",
       "\n",
       "[99441 rows x 8 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "8c258036",
   "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>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",
       "    <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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32946</th>\n",
       "      <td>a0b7d5a992ccda646f2d34e418fff5a0</td>\n",
       "      <td>moveis_decoracao</td>\n",
       "      <td>45.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>12300.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32947</th>\n",
       "      <td>bf4538d88321d0fd4412a93c974510e6</td>\n",
       "      <td>construcao_ferramentas_iluminacao</td>\n",
       "      <td>41.0</td>\n",
       "      <td>971.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1700.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32948</th>\n",
       "      <td>9a7c6041fa9592d9d9ef6cfe62a71f8c</td>\n",
       "      <td>cama_mesa_banho</td>\n",
       "      <td>50.0</td>\n",
       "      <td>799.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32949</th>\n",
       "      <td>83808703fc0706a22e264b9d75f04a2e</td>\n",
       "      <td>informatica_acessorios</td>\n",
       "      <td>60.0</td>\n",
       "      <td>156.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32950</th>\n",
       "      <td>106392145fca363410d287a815be6de4</td>\n",
       "      <td>cama_mesa_banho</td>\n",
       "      <td>58.0</td>\n",
       "      <td>309.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2083.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>32951 rows × 9 columns</p>\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",
       "32946  a0b7d5a992ccda646f2d34e418fff5a0                   moveis_decoracao   \n",
       "32947  bf4538d88321d0fd4412a93c974510e6  construcao_ferramentas_iluminacao   \n",
       "32948  9a7c6041fa9592d9d9ef6cfe62a71f8c                    cama_mesa_banho   \n",
       "32949  83808703fc0706a22e264b9d75f04a2e             informatica_acessorios   \n",
       "32950  106392145fca363410d287a815be6de4                    cama_mesa_banho   \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",
       "32946                 45.0                        67.0                 2.0   \n",
       "32947                 41.0                       971.0                 1.0   \n",
       "32948                 50.0                       799.0                 1.0   \n",
       "32949                 60.0                       156.0                 2.0   \n",
       "32950                 58.0                       309.0                 1.0   \n",
       "\n",
       "       product_weight_g  product_length_cm  product_height_cm  \\\n",
       "0                 225.0               16.0               10.0   \n",
       "1                1000.0               30.0               18.0   \n",
       "2                 154.0               18.0                9.0   \n",
       "3                 371.0               26.0                4.0   \n",
       "4                 625.0               20.0               17.0   \n",
       "...                 ...                ...                ...   \n",
       "32946           12300.0               40.0               40.0   \n",
       "32947            1700.0               16.0               19.0   \n",
       "32948            1400.0               27.0                7.0   \n",
       "32949             700.0               31.0               13.0   \n",
       "32950            2083.0               12.0                2.0   \n",
       "\n",
       "       product_width_cm  \n",
       "0                  14.0  \n",
       "1                  20.0  \n",
       "2                  15.0  \n",
       "3                  26.0  \n",
       "4                  13.0  \n",
       "...                 ...  \n",
       "32946              40.0  \n",
       "32947              16.0  \n",
       "32948              27.0  \n",
       "32949              20.0  \n",
       "32950               7.0  \n",
       "\n",
       "[32951 rows x 9 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "a1696eb4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "product_id                      0\n",
      "product_category_name         610\n",
      "product_name_lenght           610\n",
      "product_description_lenght    610\n",
      "product_photos_qty            610\n",
      "product_weight_g                2\n",
      "product_length_cm               2\n",
      "product_height_cm               2\n",
      "product_width_cm                2\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#Checking for null values\n",
    "null = product_data.isnull().sum()\n",
    "print(null)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "d665d584",
   "metadata": {},
   "outputs": [],
   "source": [
    "product_data.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "41fc4ddb",
   "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",
       "    <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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32946</th>\n",
       "      <td>a0b7d5a992ccda646f2d34e418fff5a0</td>\n",
       "      <td>moveis_decoracao</td>\n",
       "      <td>45.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>12300.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32947</th>\n",
       "      <td>bf4538d88321d0fd4412a93c974510e6</td>\n",
       "      <td>construcao_ferramentas_iluminacao</td>\n",
       "      <td>41.0</td>\n",
       "      <td>971.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1700.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32948</th>\n",
       "      <td>9a7c6041fa9592d9d9ef6cfe62a71f8c</td>\n",
       "      <td>cama_mesa_banho</td>\n",
       "      <td>50.0</td>\n",
       "      <td>799.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32949</th>\n",
       "      <td>83808703fc0706a22e264b9d75f04a2e</td>\n",
       "      <td>informatica_acessorios</td>\n",
       "      <td>60.0</td>\n",
       "      <td>156.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32950</th>\n",
       "      <td>106392145fca363410d287a815be6de4</td>\n",
       "      <td>cama_mesa_banho</td>\n",
       "      <td>58.0</td>\n",
       "      <td>309.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2083.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>32340 rows × 9 columns</p>\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",
       "32946  a0b7d5a992ccda646f2d34e418fff5a0                   moveis_decoracao   \n",
       "32947  bf4538d88321d0fd4412a93c974510e6  construcao_ferramentas_iluminacao   \n",
       "32948  9a7c6041fa9592d9d9ef6cfe62a71f8c                    cama_mesa_banho   \n",
       "32949  83808703fc0706a22e264b9d75f04a2e             informatica_acessorios   \n",
       "32950  106392145fca363410d287a815be6de4                    cama_mesa_banho   \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",
       "32946                 45.0                        67.0                 2.0   \n",
       "32947                 41.0                       971.0                 1.0   \n",
       "32948                 50.0                       799.0                 1.0   \n",
       "32949                 60.0                       156.0                 2.0   \n",
       "32950                 58.0                       309.0                 1.0   \n",
       "\n",
       "       product_weight_g  product_length_cm  product_height_cm  \\\n",
       "0                 225.0               16.0               10.0   \n",
       "1                1000.0               30.0               18.0   \n",
       "2                 154.0               18.0                9.0   \n",
       "3                 371.0               26.0                4.0   \n",
       "4                 625.0               20.0               17.0   \n",
       "...                 ...                ...                ...   \n",
       "32946           12300.0               40.0               40.0   \n",
       "32947            1700.0               16.0               19.0   \n",
       "32948            1400.0               27.0                7.0   \n",
       "32949             700.0               31.0               13.0   \n",
       "32950            2083.0               12.0                2.0   \n",
       "\n",
       "       product_width_cm  \n",
       "0                  14.0  \n",
       "1                  20.0  \n",
       "2                  15.0  \n",
       "3                  26.0  \n",
       "4                  13.0  \n",
       "...                 ...  \n",
       "32946              40.0  \n",
       "32947              16.0  \n",
       "32948              27.0  \n",
       "32949              20.0  \n",
       "32950               7.0  \n",
       "\n",
       "[32340 rows x 9 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "cc8e8d4d",
   "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",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3090</th>\n",
       "      <td>98dddbc4601dd4443ca174359b237166</td>\n",
       "      <td>87111</td>\n",
       "      <td>sarandi</td>\n",
       "      <td>PR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3091</th>\n",
       "      <td>f8201cab383e484733266d1906e2fdfa</td>\n",
       "      <td>88137</td>\n",
       "      <td>palhoca</td>\n",
       "      <td>SC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3092</th>\n",
       "      <td>74871d19219c7d518d0090283e03c137</td>\n",
       "      <td>4650</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3093</th>\n",
       "      <td>e603cf3fec55f8697c9059638d6c8eb5</td>\n",
       "      <td>96080</td>\n",
       "      <td>pelotas</td>\n",
       "      <td>RS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3094</th>\n",
       "      <td>9e25199f6ef7e7c347120ff175652c3b</td>\n",
       "      <td>12051</td>\n",
       "      <td>taubate</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3095 rows × 4 columns</p>\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",
       "3090  98dddbc4601dd4443ca174359b237166                   87111   \n",
       "3091  f8201cab383e484733266d1906e2fdfa                   88137   \n",
       "3092  74871d19219c7d518d0090283e03c137                    4650   \n",
       "3093  e603cf3fec55f8697c9059638d6c8eb5                   96080   \n",
       "3094  9e25199f6ef7e7c347120ff175652c3b                   12051   \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  \n",
       "...                 ...          ...  \n",
       "3090            sarandi           PR  \n",
       "3091            palhoca           SC  \n",
       "3092          sao paulo           SP  \n",
       "3093            pelotas           RS  \n",
       "3094            taubate           SP  \n",
       "\n",
       "[3095 rows x 4 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sellers_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "adc60dd5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "seller_id                 0\n",
      "seller_zip_code_prefix    0\n",
      "seller_city               0\n",
      "seller_state              0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#Checking for null values\n",
    "null = sellers_data.isnull().sum()\n",
    "print(null)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "20f30723",
   "metadata": {},
   "outputs": [],
   "source": [
    "#There is no need to check duplicates maybe all data were different because any seller can oreder another product from same city or state"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "591dcf94",
   "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",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>flores</td>\n",
       "      <td>flowers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>artes_e_artesanato</td>\n",
       "      <td>arts_and_craftmanship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>fraldas_higiene</td>\n",
       "      <td>diapers_and_hygiene</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>fashion_roupa_infanto_juvenil</td>\n",
       "      <td>fashion_childrens_clothes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>seguros_e_servicos</td>\n",
       "      <td>security_and_services</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>71 rows × 2 columns</p>\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\n",
       "..                            ...                           ...\n",
       "66                         flores                       flowers\n",
       "67             artes_e_artesanato         arts_and_craftmanship\n",
       "68                fraldas_higiene           diapers_and_hygiene\n",
       "69  fashion_roupa_infanto_juvenil     fashion_childrens_clothes\n",
       "70             seguros_e_servicos         security_and_services\n",
       "\n",
       "[71 rows x 2 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "category_name_dat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "fc9efada",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "product_category_name            0\n",
      "product_category_name_english    0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#Checking for null values\n",
    "null = category_name_dat.isnull().sum()\n",
    "print(null)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "c0cb7ea4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Empty DataFrame\n",
      "Columns: [product_category_name, product_category_name_english]\n",
      "Index: []\n"
     ]
    }
   ],
   "source": [
    "#Checking for duplicates\n",
    "duplicates = category_name_dat[category_name_dat.duplicated(subset=['product_category_name', 'product_category_name_english'])]\n",
    "print(duplicates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "eb241992",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Data Loading Task"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "46c90430",
   "metadata": {},
   "outputs": [],
   "source": [
    "#installing library"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "cb165b11",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['customer_id', 'customer_unique_id', 'customer_zip_code_prefix',\n",
       "       'customer_city', 'customer_state'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customer_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "5d204f30",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customer_data['customer_unique_id'].dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2dc0d6cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyodbc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a5a6cfd3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['SQL Server',\n",
       " 'SQL Server Native Client 11.0',\n",
       " 'SQL Server Native Client RDA 11.0',\n",
       " 'ODBC Driver 17 for SQL Server',\n",
       " 'Microsoft Access Driver (*.mdb, *.accdb)',\n",
       " 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)',\n",
       " 'Microsoft Access Text Driver (*.txt, *.csv)']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pyodbc.drivers()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "884b4641",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pyodbc\n",
    "\n",
    "conn = pyodbc.connect(\n",
    "    Trusted_Connection = \"Yes\",  # Corrected the parameter name\n",
    "    Driver = '{ODBC Driver 17 for SQL Server}',  # Corrected the parameter format\n",
    "    Server = 'LAPTOP-TOC9H3F0\\\\VISHALDEMODB',  # Use double backslashes to escape the backslash in the server name\n",
    "    Database = 'Data Warehouse'\n",
    ")\n",
    "\n",
    "cursor = conn.cursor()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "f14bee80",
   "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",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99436</th>\n",
       "      <td>17ddf5dd5d51696bb3d7c6291687be6f</td>\n",
       "      <td>1a29b476fee25c95fbafc67c5ac95cf8</td>\n",
       "      <td>3937</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99437</th>\n",
       "      <td>e7b71a9017aa05c9a7fd292d714858e8</td>\n",
       "      <td>d52a67c98be1cf6a5c84435bd38d095d</td>\n",
       "      <td>6764</td>\n",
       "      <td>taboao da serra</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99438</th>\n",
       "      <td>5e28dfe12db7fb50a4b2f691faecea5e</td>\n",
       "      <td>e9f50caf99f032f0bf3c55141f019d99</td>\n",
       "      <td>60115</td>\n",
       "      <td>fortaleza</td>\n",
       "      <td>CE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99439</th>\n",
       "      <td>56b18e2166679b8a959d72dd06da27f9</td>\n",
       "      <td>73c2643a0a458b49f58cea58833b192e</td>\n",
       "      <td>92120</td>\n",
       "      <td>canoas</td>\n",
       "      <td>RS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99440</th>\n",
       "      <td>274fa6071e5e17fe303b9748641082c8</td>\n",
       "      <td>84732c5050c01db9b23e19ba39899398</td>\n",
       "      <td>6703</td>\n",
       "      <td>cotia</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99441 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            customer_id                customer_unique_id  \\\n",
       "0      06b8999e2fba1a1fbc88172c00ba8bc7  861eff4711a542e4b93843c6dd7febb0   \n",
       "1      18955e83d337fd6b2def6b18a428ac77  290c77bc529b7ac935b93aa66c333dc3   \n",
       "2      4e7b3e00288586ebd08712fdd0374a03  060e732b5b29e8181a18229c7b0b2b5e   \n",
       "3      b2b6027bc5c5109e529d4dc6358b12c3  259dac757896d24d7702b9acbbff3f3c   \n",
       "4      4f2d8ab171c80ec8364f7c12e35b23ad  345ecd01c38d18a9036ed96c73b8d066   \n",
       "...                                 ...                               ...   \n",
       "99436  17ddf5dd5d51696bb3d7c6291687be6f  1a29b476fee25c95fbafc67c5ac95cf8   \n",
       "99437  e7b71a9017aa05c9a7fd292d714858e8  d52a67c98be1cf6a5c84435bd38d095d   \n",
       "99438  5e28dfe12db7fb50a4b2f691faecea5e  e9f50caf99f032f0bf3c55141f019d99   \n",
       "99439  56b18e2166679b8a959d72dd06da27f9  73c2643a0a458b49f58cea58833b192e   \n",
       "99440  274fa6071e5e17fe303b9748641082c8  84732c5050c01db9b23e19ba39899398   \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",
       "99436                      3937              sao paulo             SP  \n",
       "99437                      6764        taboao da serra             SP  \n",
       "99438                     60115              fortaleza             CE  \n",
       "99439                     92120                 canoas             RS  \n",
       "99440                      6703                  cotia             SP  \n",
       "\n",
       "[99441 rows x 5 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customer_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "c3620e5c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['customer_id', 'customer_unique_id', 'customer_zip_code_prefix',\n",
       "       'customer_city', 'customer_state'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customer_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "0bbc51f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyodbc.Cursor at 0x1477a8ba630>"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"\"\"\n",
    "    CREATE TABLE customer_data (\n",
    "        customer_id NVARCHAR(50),\n",
    "        customer_unique_id NVARCHAR(50),\n",
    "        customer_zip_code_prefix INT,\n",
    "        customer_city NVARCHAR(50),\n",
    "        customer_state NVARCHAR(10)\n",
    "    )\n",
    "\"\"\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b324d2c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in customer_data.itertuples():\n",
    "    cursor.execute(''' \n",
    "    INSERT INTO [Data Warehouse].dbo.customer_data (customer_id, customer_unique_id, customer_zip_code_prefix, customer_city, customer_state)\n",
    "    VALUES (?, ?, ?, ?, ?)\n",
    "    ''', \n",
    "    (row.customer_id, row.customer_unique_id, row.customer_zip_code_prefix, row.customer_city, row.customer_state)\n",
    "    )\n",
    "\n",
    "conn.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "44dce853",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['geolocation_zip_code_prefix', 'geolocation_lat', 'geolocation_lng',\n",
       "       'geolocation_city', 'geolocation_state'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geolocation_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "6688533b",
   "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",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000158</th>\n",
       "      <td>99950</td>\n",
       "      <td>-28.068639</td>\n",
       "      <td>-52.010705</td>\n",
       "      <td>tapejara</td>\n",
       "      <td>RS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000159</th>\n",
       "      <td>99900</td>\n",
       "      <td>-27.877125</td>\n",
       "      <td>-52.224882</td>\n",
       "      <td>getulio vargas</td>\n",
       "      <td>RS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000160</th>\n",
       "      <td>99950</td>\n",
       "      <td>-28.071855</td>\n",
       "      <td>-52.014716</td>\n",
       "      <td>tapejara</td>\n",
       "      <td>RS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000161</th>\n",
       "      <td>99980</td>\n",
       "      <td>-28.388932</td>\n",
       "      <td>-51.846871</td>\n",
       "      <td>david canabarro</td>\n",
       "      <td>RS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000162</th>\n",
       "      <td>99950</td>\n",
       "      <td>-28.070104</td>\n",
       "      <td>-52.018658</td>\n",
       "      <td>tapejara</td>\n",
       "      <td>RS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000163 rows × 5 columns</p>\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",
       "1000158                        99950       -28.068639       -52.010705   \n",
       "1000159                        99900       -27.877125       -52.224882   \n",
       "1000160                        99950       -28.071855       -52.014716   \n",
       "1000161                        99980       -28.388932       -51.846871   \n",
       "1000162                        99950       -28.070104       -52.018658   \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  \n",
       "...                  ...               ...  \n",
       "1000158         tapejara                RS  \n",
       "1000159   getulio vargas                RS  \n",
       "1000160         tapejara                RS  \n",
       "1000161  david canabarro                RS  \n",
       "1000162         tapejara                RS  \n",
       "\n",
       "[1000163 rows x 5 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geolocation_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "6ea12d41",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"\"\"\n",
    "    CREATE TABLE geolocation_data (\n",
    "        geolocation_zip_code_prefix INT,\n",
    "        geolocation_lat FLOAT,\n",
    "        geolocation_lng FLOAT,\n",
    "        geolocation_city NVARCHAR(50),\n",
    "        geolocation_state NVARCHAR(10)\n",
    "    )\n",
    "\"\"\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "f1911498",
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in geolocation_data.itertuples():\n",
    "    cursor.execute(''' \n",
    "    INSERT INTO [Data Warehouse].dbo.geolocation_data (geolocation_zip_code_prefix, geolocation_lat, geolocation_lng, geolocation_city, geolocation_state)\n",
    "    VALUES (?, ?, ?, ?, ?)\n",
    "    ''', \n",
    "    row.geolocation_zip_code_prefix,\n",
    "    row.geolocation_lat,\n",
    "    row.geolocation_lng,\n",
    "    row.geolocation_city,\n",
    "    row.geolocation_state\n",
    "    )\n",
    "    \n",
    "conn.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "22f2f7ac",
   "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",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112645</th>\n",
       "      <td>fffc94f6ce00a00581880bf54a75a037</td>\n",
       "      <td>1</td>\n",
       "      <td>4aa6014eceb682077f9dc4bffebc05b0</td>\n",
       "      <td>b8bc237ba3788b23da09c0f1f3a3288c</td>\n",
       "      <td>2018-05-02 04:11:01</td>\n",
       "      <td>299.99</td>\n",
       "      <td>43.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112646</th>\n",
       "      <td>fffcd46ef2263f404302a634eb57f7eb</td>\n",
       "      <td>1</td>\n",
       "      <td>32e07fd915822b0765e448c4dd74c828</td>\n",
       "      <td>f3c38ab652836d21de61fb8314b69182</td>\n",
       "      <td>2018-07-20 04:31:48</td>\n",
       "      <td>350.00</td>\n",
       "      <td>36.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112647</th>\n",
       "      <td>fffce4705a9662cd70adb13d4a31832d</td>\n",
       "      <td>1</td>\n",
       "      <td>72a30483855e2eafc67aee5dc2560482</td>\n",
       "      <td>c3cfdc648177fdbbbb35635a37472c53</td>\n",
       "      <td>2017-10-30 17:14:25</td>\n",
       "      <td>99.90</td>\n",
       "      <td>16.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112648</th>\n",
       "      <td>fffe18544ffabc95dfada21779c9644f</td>\n",
       "      <td>1</td>\n",
       "      <td>9c422a519119dcad7575db5af1ba540e</td>\n",
       "      <td>2b3e4a2a3ea8e01938cabda2a3e5cc79</td>\n",
       "      <td>2017-08-21 00:04:32</td>\n",
       "      <td>55.99</td>\n",
       "      <td>8.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112649</th>\n",
       "      <td>fffe41c64501cc87c801fd61db3f6244</td>\n",
       "      <td>1</td>\n",
       "      <td>350688d9dc1e75ff97be326363655e01</td>\n",
       "      <td>f7ccf836d21b2fb1de37564105216cc1</td>\n",
       "      <td>2018-06-12 17:10:13</td>\n",
       "      <td>43.00</td>\n",
       "      <td>12.79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>112650 rows × 7 columns</p>\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",
       "112645  fffc94f6ce00a00581880bf54a75a037              1   \n",
       "112646  fffcd46ef2263f404302a634eb57f7eb              1   \n",
       "112647  fffce4705a9662cd70adb13d4a31832d              1   \n",
       "112648  fffe18544ffabc95dfada21779c9644f              1   \n",
       "112649  fffe41c64501cc87c801fd61db3f6244              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",
       "112645  4aa6014eceb682077f9dc4bffebc05b0  b8bc237ba3788b23da09c0f1f3a3288c   \n",
       "112646  32e07fd915822b0765e448c4dd74c828  f3c38ab652836d21de61fb8314b69182   \n",
       "112647  72a30483855e2eafc67aee5dc2560482  c3cfdc648177fdbbbb35635a37472c53   \n",
       "112648  9c422a519119dcad7575db5af1ba540e  2b3e4a2a3ea8e01938cabda2a3e5cc79   \n",
       "112649  350688d9dc1e75ff97be326363655e01  f7ccf836d21b2fb1de37564105216cc1   \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  \n",
       "...                     ...     ...            ...  \n",
       "112645  2018-05-02 04:11:01  299.99          43.41  \n",
       "112646  2018-07-20 04:31:48  350.00          36.53  \n",
       "112647  2017-10-30 17:14:25   99.90          16.95  \n",
       "112648  2017-08-21 00:04:32   55.99           8.72  \n",
       "112649  2018-06-12 17:10:13   43.00          12.79  \n",
       "\n",
       "[112650 rows x 7 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_item_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5ea3572c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['order_id', 'order_item_id', 'product_id', 'seller_id',\n",
       "       'shipping_limit_date', 'price', 'freight_value'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_item_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2280682f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyodbc.Cursor at 0x1477a8ba630>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"\"\"\n",
    "    CREATE TABLE order_item_data (\n",
    "        order_id NVARCHAR(50),\n",
    "        order_item_id int,\n",
    "        product_id NVARCHAR(50),\n",
    "        seller_id NVARCHAR(50),\n",
    "        shipping_limit_date DATETIME,\n",
    "        price float,\n",
    "        freight_value float\n",
    "        \n",
    "    )\n",
    "\"\"\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a0c7172f",
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in order_item_data.itertuples():\n",
    "    cursor.execute(''' \n",
    "    INSERT INTO [Data Warehouse].dbo.order_item_data (order_id, order_item_id, product_id, seller_id, shipping_limit_date, price, freight_value)\n",
    "    VALUES (\n",
    "        ?,                           -- order_id\n",
    "        ?,                           -- order_item_id\n",
    "        ?,                           -- product_id\n",
    "        ?,                           -- seller_id\n",
    "        ?,                           -- shipping_limit_date\n",
    "        ?,                           -- price\n",
    "        ?                            -- freight_value\n",
    "    )\n",
    "    ''', \n",
    "    row.order_id,\n",
    "    row.order_item_id,\n",
    "    row.product_id,\n",
    "    row.seller_id,\n",
    "    row.shipping_limit_date,\n",
    "    row.price,\n",
    "    row.freight_value\n",
    "    )\n",
    "    \n",
    "conn.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "2b913f1d",
   "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",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103881</th>\n",
       "      <td>0406037ad97740d563a178ecc7a2075c</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>363.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103882</th>\n",
       "      <td>7b905861d7c825891d6347454ea7863f</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>2</td>\n",
       "      <td>96.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103883</th>\n",
       "      <td>32609bbb3dd69b3c066a6860554a77bf</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>1</td>\n",
       "      <td>47.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103884</th>\n",
       "      <td>b8b61059626efa996a60be9bb9320e10</td>\n",
       "      <td>1</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>5</td>\n",
       "      <td>369.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103885</th>\n",
       "      <td>28bbae6599b09d39ca406b747b6632b1</td>\n",
       "      <td>1</td>\n",
       "      <td>boleto</td>\n",
       "      <td>1</td>\n",
       "      <td>191.58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>103886 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                order_id  payment_sequential payment_type  \\\n",
       "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",
       "103881  0406037ad97740d563a178ecc7a2075c                   1       boleto   \n",
       "103882  7b905861d7c825891d6347454ea7863f                   1  credit_card   \n",
       "103883  32609bbb3dd69b3c066a6860554a77bf                   1  credit_card   \n",
       "103884  b8b61059626efa996a60be9bb9320e10                   1  credit_card   \n",
       "103885  28bbae6599b09d39ca406b747b6632b1                   1       boleto   \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  \n",
       "...                      ...            ...  \n",
       "103881                     1         363.31  \n",
       "103882                     2          96.80  \n",
       "103883                     1          47.77  \n",
       "103884                     5         369.54  \n",
       "103885                     1         191.58  \n",
       "\n",
       "[103886 rows x 5 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_payment_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "2fd0a49a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['order_id', 'payment_sequential', 'payment_type',\n",
       "       'payment_installments', 'payment_value'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_payment_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "14fb5e21",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"\"\"\n",
    "    CREATE TABLE order_payment_data (\n",
    "        order_id VARBINARY(MAX),\n",
    "        payment_sequential int,\n",
    "        payment_type NVARCHAR(50),\n",
    "        payment_installments INT,\n",
    "        payment_value float\n",
    "        \n",
    "    )\n",
    "\"\"\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "71b90318",
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in order_payment_data.itertuples():\n",
    "    cursor.execute(''' \n",
    "    INSERT INTO [Data Warehouse].dbo.order_payment_data (order_id, payment_sequential, payment_type, payment_installments, payment_value)\n",
    "    VALUES (\n",
    "        CONVERT(VARBINARY(MAX), ?),  -- order_id\n",
    "        ?,                           -- payment_sequential\n",
    "        CONVERT(NVARCHAR(MAX), ?),   -- payment_type (assuming it's NVARCHAR)\n",
    "        ?,                           -- payment_installments\n",
    "        ?                            -- payment_value\n",
    "    )\n",
    "    ''', \n",
    "    row.order_id,\n",
    "    row.payment_sequential,\n",
    "    row.payment_type,\n",
    "    row.payment_installments,\n",
    "    row.payment_value\n",
    "    )\n",
    "    \n",
    "conn.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "c15bff46",
   "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_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>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>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>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>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>2018-03-01 00:00:00</td>\n",
       "      <td>2018-03-02 10:26:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99219</th>\n",
       "      <td>574ed12dd733e5fa530cfd4bbf39d7c9</td>\n",
       "      <td>2a8c23fee101d4d5662fa670396eb8da</td>\n",
       "      <td>5</td>\n",
       "      <td>2018-07-07 00:00:00</td>\n",
       "      <td>2018-07-14 17:18:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99220</th>\n",
       "      <td>f3897127253a9592a73be9bdfdf4ed7a</td>\n",
       "      <td>22ec9f0669f784db00fa86d035cf8602</td>\n",
       "      <td>5</td>\n",
       "      <td>2017-12-09 00:00:00</td>\n",
       "      <td>2017-12-11 20:06:42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99221</th>\n",
       "      <td>b3de70c89b1510c4cd3d0649fd302472</td>\n",
       "      <td>55d4004744368f5571d1f590031933e4</td>\n",
       "      <td>5</td>\n",
       "      <td>2018-03-22 00:00:00</td>\n",
       "      <td>2018-03-23 09:10:43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99222</th>\n",
       "      <td>1adeb9d84d72fe4e337617733eb85149</td>\n",
       "      <td>7725825d039fc1f0ceb7635e3f7d9206</td>\n",
       "      <td>4</td>\n",
       "      <td>2018-07-01 00:00:00</td>\n",
       "      <td>2018-07-02 12:59:13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99223</th>\n",
       "      <td>efe49f1d6f951dd88b51e6ccd4cc548f</td>\n",
       "      <td>90531360ecb1eec2a1fbb265a0db0508</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-07-03 00:00:00</td>\n",
       "      <td>2017-07-03 21:01:49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99224 rows × 5 columns</p>\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",
       "99219  574ed12dd733e5fa530cfd4bbf39d7c9  2a8c23fee101d4d5662fa670396eb8da   \n",
       "99220  f3897127253a9592a73be9bdfdf4ed7a  22ec9f0669f784db00fa86d035cf8602   \n",
       "99221  b3de70c89b1510c4cd3d0649fd302472  55d4004744368f5571d1f590031933e4   \n",
       "99222  1adeb9d84d72fe4e337617733eb85149  7725825d039fc1f0ceb7635e3f7d9206   \n",
       "99223  efe49f1d6f951dd88b51e6ccd4cc548f  90531360ecb1eec2a1fbb265a0db0508   \n",
       "\n",
       "       review_score review_creation_date review_answer_timestamp  \n",
       "0                 4  2018-01-18 00:00:00     2018-01-18 21:46:59  \n",
       "1                 5  2018-03-10 00:00:00     2018-03-11 03:05:13  \n",
       "2                 5  2018-02-17 00:00:00     2018-02-18 14:36:24  \n",
       "3                 5  2017-04-21 00:00:00     2017-04-21 22:02:06  \n",
       "4                 5  2018-03-01 00:00:00     2018-03-02 10:26:53  \n",
       "...             ...                  ...                     ...  \n",
       "99219             5  2018-07-07 00:00:00     2018-07-14 17:18:30  \n",
       "99220             5  2017-12-09 00:00:00     2017-12-11 20:06:42  \n",
       "99221             5  2018-03-22 00:00:00     2018-03-23 09:10:43  \n",
       "99222             4  2018-07-01 00:00:00     2018-07-02 12:59:13  \n",
       "99223             1  2017-07-03 00:00:00     2017-07-03 21:01:49  \n",
       "\n",
       "[99224 rows x 5 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_review_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "efb056f4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['review_id', 'order_id', 'review_score', 'review_comment_title',\n",
       "       'review_comment_message', 'review_creation_date',\n",
       "       'review_answer_timestamp'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_review_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "047837ac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyodbc.Cursor at 0x1477a8ba630>"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"\"\"\n",
    "    CREATE TABLE order_review_data (\n",
    "        review_id NVARCHAR(50),\n",
    "        order_id NVARCHAR(50),\n",
    "        review_score int,\n",
    "        review_creation_date DATETIME,\n",
    "        review_answer_timestamp DATETIME\n",
    "        \n",
    "    )\n",
    "\"\"\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "325d9341",
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in order_review_data.itertuples():\n",
    "    cursor.execute(''' \n",
    "    INSERT INTO [Data Warehouse].dbo.order_review_data (review_id, order_id, review_score, review_creation_date, review_answer_timestamp)\n",
    "    VALUES (\n",
    "        ?,                           -- review_id\n",
    "        ?,                           -- order_id\n",
    "        ?,                           -- review_score\n",
    "        ?,                           -- review_creation_date\n",
    "        ?                            -- review_answer_timestamp\n",
    "    )\n",
    "    ''', \n",
    "    row.review_id,\n",
    "    row.order_id,\n",
    "    row.review_score,\n",
    "    row.review_creation_date,\n",
    "    row.review_answer_timestamp\n",
    "    )\n",
    "    \n",
    "conn.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "839d68ae",
   "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",
       "    <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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32946</th>\n",
       "      <td>a0b7d5a992ccda646f2d34e418fff5a0</td>\n",
       "      <td>moveis_decoracao</td>\n",
       "      <td>45.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>12300.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32947</th>\n",
       "      <td>bf4538d88321d0fd4412a93c974510e6</td>\n",
       "      <td>construcao_ferramentas_iluminacao</td>\n",
       "      <td>41.0</td>\n",
       "      <td>971.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1700.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32948</th>\n",
       "      <td>9a7c6041fa9592d9d9ef6cfe62a71f8c</td>\n",
       "      <td>cama_mesa_banho</td>\n",
       "      <td>50.0</td>\n",
       "      <td>799.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32949</th>\n",
       "      <td>83808703fc0706a22e264b9d75f04a2e</td>\n",
       "      <td>informatica_acessorios</td>\n",
       "      <td>60.0</td>\n",
       "      <td>156.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32950</th>\n",
       "      <td>106392145fca363410d287a815be6de4</td>\n",
       "      <td>cama_mesa_banho</td>\n",
       "      <td>58.0</td>\n",
       "      <td>309.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2083.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>32340 rows × 9 columns</p>\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",
       "32946  a0b7d5a992ccda646f2d34e418fff5a0                   moveis_decoracao   \n",
       "32947  bf4538d88321d0fd4412a93c974510e6  construcao_ferramentas_iluminacao   \n",
       "32948  9a7c6041fa9592d9d9ef6cfe62a71f8c                    cama_mesa_banho   \n",
       "32949  83808703fc0706a22e264b9d75f04a2e             informatica_acessorios   \n",
       "32950  106392145fca363410d287a815be6de4                    cama_mesa_banho   \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",
       "32946                 45.0                        67.0                 2.0   \n",
       "32947                 41.0                       971.0                 1.0   \n",
       "32948                 50.0                       799.0                 1.0   \n",
       "32949                 60.0                       156.0                 2.0   \n",
       "32950                 58.0                       309.0                 1.0   \n",
       "\n",
       "       product_weight_g  product_length_cm  product_height_cm  \\\n",
       "0                 225.0               16.0               10.0   \n",
       "1                1000.0               30.0               18.0   \n",
       "2                 154.0               18.0                9.0   \n",
       "3                 371.0               26.0                4.0   \n",
       "4                 625.0               20.0               17.0   \n",
       "...                 ...                ...                ...   \n",
       "32946           12300.0               40.0               40.0   \n",
       "32947            1700.0               16.0               19.0   \n",
       "32948            1400.0               27.0                7.0   \n",
       "32949             700.0               31.0               13.0   \n",
       "32950            2083.0               12.0                2.0   \n",
       "\n",
       "       product_width_cm  \n",
       "0                  14.0  \n",
       "1                  20.0  \n",
       "2                  15.0  \n",
       "3                  26.0  \n",
       "4                  13.0  \n",
       "...                 ...  \n",
       "32946              40.0  \n",
       "32947              16.0  \n",
       "32948              27.0  \n",
       "32949              20.0  \n",
       "32950               7.0  \n",
       "\n",
       "[32340 rows x 9 columns]"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "8b9ff52c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['product_id', 'product_category_name', 'product_name_lenght',\n",
       "       'product_description_lenght', 'product_photos_qty', 'product_weight_g',\n",
       "       'product_length_cm', 'product_height_cm', 'product_width_cm'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "eb3f8498",
   "metadata": {},
   "outputs": [
    {
     "ename": "ProgrammingError",
     "evalue": "('42S01', \"[42S01] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'product_data' in the database. (2714) (SQLExecDirectW)\")",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[92], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m \u001b[43mcursor\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\"\"\u001b[39;49m\n\u001b[0;32m      2\u001b[0m \u001b[38;5;124;43m    CREATE TABLE product_data (\u001b[39;49m\n\u001b[0;32m      3\u001b[0m \u001b[38;5;124;43m        product_id NVARCHAR(50),\u001b[39;49m\n\u001b[0;32m      4\u001b[0m \u001b[38;5;124;43m        product_category_name VARCHAR(50),\u001b[39;49m\n\u001b[0;32m      5\u001b[0m \u001b[38;5;124;43m        product_name_lenght float,\u001b[39;49m\n\u001b[0;32m      6\u001b[0m \u001b[38;5;124;43m        product_description_lenght float,\u001b[39;49m\n\u001b[0;32m      7\u001b[0m \u001b[38;5;124;43m        product_photos_qty float,\u001b[39;49m\n\u001b[0;32m      8\u001b[0m \u001b[38;5;124;43m        product_weight_g float,\u001b[39;49m\n\u001b[0;32m      9\u001b[0m \u001b[38;5;124;43m        product_length_cm float,\u001b[39;49m\n\u001b[0;32m     10\u001b[0m \u001b[38;5;124;43m        product_height_cm float,\u001b[39;49m\n\u001b[0;32m     11\u001b[0m \u001b[38;5;124;43m        product_width_cm float      \u001b[39;49m\n\u001b[0;32m     12\u001b[0m \u001b[38;5;124;43m    )\u001b[39;49m\n\u001b[0;32m     13\u001b[0m \u001b[38;5;124;43m\"\"\"\u001b[39;49m\u001b[43m)\u001b[49m\n",
      "\u001b[1;31mProgrammingError\u001b[0m: ('42S01', \"[42S01] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'product_data' in the database. (2714) (SQLExecDirectW)\")"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"\"\"\n",
    "    CREATE TABLE product_data (\n",
    "        product_id NVARCHAR(50),\n",
    "        product_category_name VARCHAR(50),\n",
    "        product_name_lenght float,\n",
    "        product_description_lenght float,\n",
    "        product_photos_qty float,\n",
    "        product_weight_g float,\n",
    "        product_length_cm float,\n",
    "        product_height_cm float,\n",
    "        product_width_cm float      \n",
    "    )\n",
    "\"\"\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "668e1720",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Close the connection\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9b950f51",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'product_data' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[1], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m row \u001b[38;5;129;01min\u001b[39;00m \u001b[43mproduct_data\u001b[49m\u001b[38;5;241m.\u001b[39mitertuples():\n\u001b[0;32m      2\u001b[0m     cursor\u001b[38;5;241m.\u001b[39mexecute(\u001b[38;5;124m'''\u001b[39m\n\u001b[0;32m      3\u001b[0m \u001b[38;5;124m        INSERT INTO [Data Warehouse].dbo.product_data (\u001b[39m\n\u001b[0;32m      4\u001b[0m \u001b[38;5;124m            product_id,\u001b[39m\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m     24\u001b[0m     row\u001b[38;5;241m.\u001b[39mproduct_width_cm  \u001b[38;5;66;03m# Replace with the actual column name from your DataFrame\u001b[39;00m\n\u001b[0;32m     25\u001b[0m     )\n\u001b[0;32m     27\u001b[0m \u001b[38;5;66;03m# Commit the transaction\u001b[39;00m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'product_data' is not defined"
     ]
    }
   ],
   "source": [
    "for row in product_data.itertuples():\n",
    "    cursor.execute('''\n",
    "        INSERT INTO [Data Warehouse].dbo.product_data (\n",
    "            product_id,\n",
    "            product_category_name,\n",
    "            product_name_length,  -- Replace with the actual column name from your DataFrame\n",
    "            product_description_length,  -- Replace with the actual column name from your DataFrame\n",
    "            product_photos_qty,  -- Replace with the actual column name from your DataFrame\n",
    "            product_weight_g,  -- Replace with the actual column name from your DataFrame\n",
    "            product_length_cm,  -- Replace with the actual column name from your DataFrame\n",
    "            product_height_cm,  -- Replace with the actual column name from your DataFrame\n",
    "            product_width_cm  -- Replace with the actual column name from your DataFrame\n",
    "        )\n",
    "        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)\n",
    "    ''', \n",
    "    row.product_id,\n",
    "    row.product_category_name,\n",
    "    row.product_name_length,  # Replace with the actual column name from your DataFrame\n",
    "    row.product_description_length,  # Replace with the actual column name from your DataFrame\n",
    "    row.product_photos_qty,  # Replace with the actual column name from your DataFrame\n",
    "    row.product_weight_g,  # Replace with the actual column name from your DataFrame\n",
    "    row.product_length_cm,  # Replace with the actual column name from your DataFrame\n",
    "    row.product_height_cm,  # Replace with the actual column name from your DataFrame\n",
    "    row.product_width_cm  # Replace with the actual column name from your DataFrame\n",
    "    )\n",
    "\n",
    "# Commit the transaction\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d5949c82",
   "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",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>c240c4061717ac1806ae6ee72be3533b</td>\n",
       "      <td>20920</td>\n",
       "      <td>rio de janeiro</td>\n",
       "      <td>RJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>e49c26c3edfa46d227d5121a6b6e4d37</td>\n",
       "      <td>55325</td>\n",
       "      <td>brejao</td>\n",
       "      <td>PE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1b938a7ec6ac5061a66a3766e0e75f90</td>\n",
       "      <td>16304</td>\n",
       "      <td>penapolis</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>768a86e36ad6aae3d03ee3c6433d61df</td>\n",
       "      <td>1529</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ccc4bbb5f32a6ab2b7066a4130f114e3</td>\n",
       "      <td>80310</td>\n",
       "      <td>curitiba</td>\n",
       "      <td>PR</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",
       "5  c240c4061717ac1806ae6ee72be3533b                   20920   \n",
       "6  e49c26c3edfa46d227d5121a6b6e4d37                   55325   \n",
       "7  1b938a7ec6ac5061a66a3766e0e75f90                   16304   \n",
       "8  768a86e36ad6aae3d03ee3c6433d61df                    1529   \n",
       "9  ccc4bbb5f32a6ab2b7066a4130f114e3                   80310   \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  \n",
       "5     rio de janeiro           RJ  \n",
       "6             brejao           PE  \n",
       "7          penapolis           SP  \n",
       "8          sao paulo           SP  \n",
       "9           curitiba           PR  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sellers_data.head(10)                  #The table is delerted mistakenly but it is present in Staginf layer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e758aa79",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['seller_id', 'seller_zip_code_prefix', 'seller_city', 'seller_state'], dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sellers_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a33cee6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"\"\"\n",
    "    CREATE TABLE sellers_data (\n",
    "        seller_id NVARCHAR(50),\n",
    "        seller_zip_code_prefix NVARCHAR(10),\n",
    "        seller_city NVARCHAR(50),\n",
    "        seller_state NVARCHAR(10)     \n",
    "    )\n",
    "\"\"\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ec13ffc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in sellers_data.itertuples():\n",
    "    cursor.execute(''' \n",
    "    INSERT INTO [Data Warehouse].dbo.sellers_data (seller_id, seller_zip_code_prefix, seller_city, seller_state)\n",
    "    VALUES (\n",
    "        ?,                          \n",
    "        ?,                           \n",
    "        ?,                           \n",
    "        ?                           \n",
    "    )\n",
    "    ''', \n",
    "    row.seller_id,\n",
    "    row.seller_zip_code_prefix,\n",
    "    row.seller_city,\n",
    "    row.seller_state\n",
    "    )\n",
    "    \n",
    "conn.commit()\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}