StarJeans_ETL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# BUSINESS PROBLEM STAR JEAN'S\n",
    "\n",
    "Eduardo and Marcelo are two Brazilians, friends and business partners. After several successful business, they are planning to enter the fashion market. US as an E-commerce business model.\n",
    "\n",
    "The initial idea is to enter the market with just one product and for a specific audience, in this case the product would be Jeans for the male audience. The objective is to maintain the operating cost low and scale as they get customers.\n",
    "\n",
    "However, even with the input product and audience defined, the two partners do not have experience in this fashion market and therefore they don't know how to define basic things like price, the type of pants and the material for the manufacture of each piece.\n",
    "\n",
    "Thus, the two partners hired a Data Science consultancy to answer the following questions:\n",
    "\n",
    "* What is the best selling price for the pants?\n",
    "* How many types of pants and yours colors for initial product?\n",
    "* What are the raw materials needed to make the pants?\n",
    "\n",
    "The main competitors of the Start Jeans company are the American companies H&M and Macys.\n",
    "\n",
    "# 1. Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-20T23:12:12.761465Z",
     "start_time": "2021-10-20T23:12:11.930527Z"
    }
   },
   "outputs": [],
   "source": [
    "# webscrapping\n",
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "# date\n",
    "from datetime import datetime\n",
    "\n",
    "# data manipulation\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# monitoring\n",
    "from tqdm import tqdm\n",
    "\n",
    "# regex\n",
    "import re\n",
    "\n",
    "# database\n",
    "import sqlite3\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. WebScrapping (EXTRACT)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# extract data from competitors\n",
    "url = 'https://www2.hm.com/en_us/men/products/jeans.html'\n",
    "headers = {\"User-Agent\": \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.71 Safari/537.36\"}\n",
    "\n",
    "# request\n",
    "page = requests.get(url, headers = headers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "36"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# instantiate BeautifulSoup\n",
    "soup = BeautifulSoup(page.text, 'html.parser')\n",
    "\n",
    "# find total number of products\n",
    "total_item = soup.find_all('h2', class_ = 'load-more-heading')[0].get('data-total')\n",
    "\n",
    "# find number of items shown in page\n",
    "items_page = soup.find_all('h2', class_ = 'load-more-heading')[0].get('data-items-shown')\n",
    "\n",
    "# get number of pages\n",
    "pages = np.ceil(int(total_item) / int(items_page))\n",
    "\n",
    "# get section with all products\n",
    "products = soup.find('ul', class_ = 'products-listing small')\n",
    "\n",
    "# get list of products overall\n",
    "product_list = soup.find_all('article', class_ = 'hm-product-item')\n",
    "\n",
    "len(product_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1. Generate table\n",
    "\n",
    "Table format: \n",
    "\n",
    "Id | Category | Name | Price | Color | Decomposition\n",
    "\n",
    "### 2.1.1 Id, Category, Name, Price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# product_id\n",
    "product_id = [p.get('data-articlecode') for p in product_list]\n",
    "\n",
    "# product_category\n",
    "product_category = [p.get('data-category') for p in product_list]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get list of products to get name\n",
    "product_list = products.find_all('a', class_ = 'link')\n",
    "\n",
    "# product_name\n",
    "product_name = [p.get_text() for p in product_list]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get list of products to get price\n",
    "product_list = products.find_all('span', class_ = 'price regular')\n",
    "\n",
    "# product_price\n",
    "product_price = [p.get_text() for p in product_list]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product_id</th>\n",
       "      <th>product_category</th>\n",
       "      <th>product_name</th>\n",
       "      <th>product_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0690449051</td>\n",
       "      <td>men_jeans_ripped</td>\n",
       "      <td>Skinny Jeans</td>\n",
       "      <td>$ 39.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1004476004</td>\n",
       "      <td>men_jeans_slim</td>\n",
       "      <td>Freefit® Slim Jeans</td>\n",
       "      <td>$ 49.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0938875007</td>\n",
       "      <td>men_jeans_slim</td>\n",
       "      <td>Slim Tapered Jeans</td>\n",
       "      <td>$ 39.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0985197005</td>\n",
       "      <td>men_jeans_slim</td>\n",
       "      <td>Slim Jeans</td>\n",
       "      <td>$ 19.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1013317004</td>\n",
       "      <td>men_jeans_regular</td>\n",
       "      <td>Hybrid Regular Tapered Joggers</td>\n",
       "      <td>$ 39.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id   product_category                    product_name product_price\n",
       "0  0690449051   men_jeans_ripped                    Skinny Jeans       $ 39.99\n",
       "1  1004476004     men_jeans_slim             Freefit® Slim Jeans       $ 49.99\n",
       "2  0938875007     men_jeans_slim              Slim Tapered Jeans       $ 39.99\n",
       "3  0985197005     men_jeans_slim                      Slim Jeans       $ 19.99\n",
       "4  1013317004  men_jeans_regular  Hybrid Regular Tapered Joggers       $ 39.99"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pass data to DataFrame\n",
    "df_products = pd.DataFrame([product_id, product_category, product_name, product_price]).T\n",
    "df_products.columns = ['product_id', 'product_category', 'product_name', 'product_price']\n",
    "\n",
    "df_products.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.1.2. Color, Decomposition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████████████████████████████████████████████████████| 36/36 [02:50<00:00,  4.74s/it]\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product_id</th>\n",
       "      <th>composition</th>\n",
       "      <th>fit</th>\n",
       "      <th>product_safety</th>\n",
       "      <th>size</th>\n",
       "      <th>more_sustainable_materials</th>\n",
       "      <th>product_name</th>\n",
       "      <th>product_price</th>\n",
       "      <th>color_name</th>\n",
       "      <th>style_id</th>\n",
       "      <th>color_id</th>\n",
       "      <th>scrapy_datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0690449051</td>\n",
       "      <td>Cotton 98%, Elastane 2%</td>\n",
       "      <td>Skinny fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Recycled cotton 20%</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans</td>\n",
       "      <td>39.99</td>\n",
       "      <td>Gray</td>\n",
       "      <td>0690449</td>\n",
       "      <td>051</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0690449001</td>\n",
       "      <td>Cotton 99%, Elastane 1%</td>\n",
       "      <td>Skinny fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans</td>\n",
       "      <td>16.99</td>\n",
       "      <td>Light denim blue/trashed</td>\n",
       "      <td>0690449</td>\n",
       "      <td>001</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0690449002</td>\n",
       "      <td>Cotton 98%, Elastane 2%</td>\n",
       "      <td>Skinny fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans</td>\n",
       "      <td>14.99</td>\n",
       "      <td>Denim blue</td>\n",
       "      <td>0690449</td>\n",
       "      <td>002</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0690449006</td>\n",
       "      <td>Cotton 100%</td>\n",
       "      <td>Skinny fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans</td>\n",
       "      <td>7.99</td>\n",
       "      <td>Black/washed</td>\n",
       "      <td>0690449</td>\n",
       "      <td>006</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0690449006</td>\n",
       "      <td>Cotton 98%, Elastane 2%</td>\n",
       "      <td>Skinny fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans</td>\n",
       "      <td>7.99</td>\n",
       "      <td>Black/washed</td>\n",
       "      <td>0690449</td>\n",
       "      <td>006</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id              composition         fit product_safety size  \\\n",
       "0  0690449051  Cotton 98%, Elastane 2%  Skinny fit            NaN  NaN   \n",
       "1  0690449001  Cotton 99%, Elastane 1%  Skinny fit            NaN  NaN   \n",
       "2  0690449002  Cotton 98%, Elastane 2%  Skinny fit            NaN  NaN   \n",
       "3  0690449006              Cotton 100%  Skinny fit            NaN  NaN   \n",
       "4  0690449006  Cotton 98%, Elastane 2%  Skinny fit            NaN  NaN   \n",
       "\n",
       "  more_sustainable_materials                    product_name product_price  \\\n",
       "0        Recycled cotton 20%  \\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans         39.99   \n",
       "1                        NaN  \\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans         16.99   \n",
       "2                        NaN  \\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans         14.99   \n",
       "3                        NaN  \\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans          7.99   \n",
       "4                        NaN  \\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans          7.99   \n",
       "\n",
       "                 color_name style_id color_id      scrapy_datetime  \n",
       "0                      Gray  0690449      051  2021-10-16 22:13:23  \n",
       "1  Light denim blue/trashed  0690449      001  2021-10-16 22:13:23  \n",
       "2                Denim blue  0690449      002  2021-10-16 22:13:23  \n",
       "3              Black/washed  0690449      006  2021-10-16 22:13:23  \n",
       "4              Black/washed  0690449      006  2021-10-16 22:13:23  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# getting color for all products\n",
    "headers = {\"User-Agent\": \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.71 Safari/537.36\"}\n",
    "cols = ['Art. No.', 'Composition', 'Fit', 'Product safety', 'Size', 'More sustainable materials']\n",
    "df_pattern = pd.DataFrame(columns = cols)\n",
    "df_compositions = pd.DataFrame()\n",
    "aux = []\n",
    "\n",
    "for index in tqdm(product_id):\n",
    "    url = f'https://www2.hm.com/en_us/productpage.{index}.html'\n",
    "    \n",
    "    # request\n",
    "    page = requests.get(url, headers = headers)\n",
    "    \n",
    "    # instantiate BeatifulSoup\n",
    "    soup = BeautifulSoup(page.text, 'html.parser')\n",
    "    \n",
    "    # COLOR\n",
    "    ## product list\n",
    "    product_list = soup.find_all('a', class_ = 'filter-option miniature active') + soup.find_all('a', class_ = 'filter-option miniature')\n",
    "    \n",
    "    ## color name\n",
    "    color_name = [p.get('data-color') for p in product_list]\n",
    "    \n",
    "    # ID FOR MERGE\n",
    "    ## product_id\n",
    "    color_product_id = [p.get('data-articlecode') for p in product_list]\n",
    "    \n",
    "    # pass to dataframe\n",
    "    df_color = pd.DataFrame([color_product_id, color_name]).T\n",
    "    df_color.columns = ['product_id', 'color_name']\n",
    "    \n",
    "    for color_index in df_color['product_id']:\n",
    "        url = f'https://www2.hm.com/en_us/productpage.{color_index}.html'\n",
    "    \n",
    "        # request\n",
    "        page = requests.get(url, headers = headers)\n",
    "\n",
    "        # instantiate BeatifulSoup\n",
    "        soup = BeautifulSoup(page.text, 'html.parser')\n",
    "        \n",
    "        # product name\n",
    "        product_name = soup.find_all('h1', class_ = 'primary product-item-headline')\n",
    "        product_name = product_name[0].get_text()\n",
    "        \n",
    "        # product price\n",
    "        product_price = soup.find_all('div', class_ = 'primary-row product-item-price')\n",
    "        product_price = re.findall(r'\\d+\\.?\\d+', product_price[0].get_text())[0]\n",
    "        \n",
    "        # COMPOSITION\n",
    "        ## composition list\n",
    "        product_composition_list = soup.find_all('div', class_ = 'pdp-description-list-item')\n",
    "\n",
    "        ## composition names\n",
    "        product_composition = [list(filter(None, c.get_text().split('\\n'))) for c in product_composition_list]\n",
    "\n",
    "        # pass to dataframe\n",
    "        df_composition = pd.DataFrame(product_composition).T\n",
    "\n",
    "        # set columns\n",
    "        df_composition.columns = df_composition.iloc[0]\n",
    "\n",
    "        # delete first row and fill na\n",
    "        df_composition = df_composition.iloc[1:].fillna(method = 'ffill')\n",
    "\n",
    "        # remove pocket lining, shell and lining\n",
    "        df_composition['Composition'] = df_composition['Composition'].str.replace('Pocket lining: ', '', regex = True)\n",
    "        df_composition['Composition'] = df_composition['Composition'].str.replace('Shell: ', '', regex = True)\n",
    "        df_composition['Composition'] = df_composition['Composition'].str.replace('Lining: ', '', regex = True)\n",
    "\n",
    "        # garantee same number of columns\n",
    "        df_composition = pd.concat([df_pattern, df_composition], axis = 0).reset_index(drop = True)\n",
    "\n",
    "        # rename columns\n",
    "        df_composition.columns = ['product_id', 'composition', 'fit', 'product_safety', 'size', 'more_sustainable_materials']\n",
    "        \n",
    "        # set product name and price\n",
    "        df_composition['product_name'] = product_name\n",
    "        df_composition['product_price'] = product_price   \n",
    "        \n",
    "        # keep track on new columns\n",
    "        aux = aux + df_composition.columns.to_list()\n",
    "\n",
    "        # merge color and composition\n",
    "        df_composition = df_composition.merge(df_color, how = 'left', on = 'product_id')\n",
    "\n",
    "        # all details from products\n",
    "        df_compositions = pd.concat([df_compositions, df_composition], axis = 0).reset_index(drop = True)\n",
    "\n",
    "# generate style id + color id\n",
    "df_compositions['style_id'] = df_compositions['product_id'].apply(lambda x: x[:-3])\n",
    "df_compositions['color_id'] = df_compositions['product_id'].apply(lambda x: x[-3:])\n",
    "\n",
    "# scrapy datetime\n",
    "df_compositions['scrapy_datetime'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')\n",
    "\n",
    "df_compositions.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.1.3. Merge with main product list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>composition</th>\n",
       "      <th>fit</th>\n",
       "      <th>product_safety</th>\n",
       "      <th>size</th>\n",
       "      <th>more_sustainable_materials</th>\n",
       "      <th>product_name</th>\n",
       "      <th>product_price</th>\n",
       "      <th>color_name</th>\n",
       "      <th>style_id</th>\n",
       "      <th>color_id</th>\n",
       "      <th>scrapy_datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0690449051</td>\n",
       "      <td>Cotton 98%, Elastane 2%</td>\n",
       "      <td>Skinny fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Recycled cotton 20%</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans</td>\n",
       "      <td>39.99</td>\n",
       "      <td>Gray</td>\n",
       "      <td>0690449</td>\n",
       "      <td>051</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0690449001</td>\n",
       "      <td>Cotton 99%, Elastane 1%</td>\n",
       "      <td>Skinny fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans</td>\n",
       "      <td>16.99</td>\n",
       "      <td>Light denim blue/trashed</td>\n",
       "      <td>0690449</td>\n",
       "      <td>001</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0690449002</td>\n",
       "      <td>Cotton 98%, Elastane 2%</td>\n",
       "      <td>Skinny fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans</td>\n",
       "      <td>14.99</td>\n",
       "      <td>Denim blue</td>\n",
       "      <td>0690449</td>\n",
       "      <td>002</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0690449006</td>\n",
       "      <td>Cotton 100%</td>\n",
       "      <td>Skinny fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans</td>\n",
       "      <td>7.99</td>\n",
       "      <td>Black/washed</td>\n",
       "      <td>0690449</td>\n",
       "      <td>006</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0690449006</td>\n",
       "      <td>Cotton 98%, Elastane 2%</td>\n",
       "      <td>Skinny fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans</td>\n",
       "      <td>7.99</td>\n",
       "      <td>Black/washed</td>\n",
       "      <td>0690449</td>\n",
       "      <td>006</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id              composition         fit product_safety size  \\\n",
       "0  0690449051  Cotton 98%, Elastane 2%  Skinny fit            NaN  NaN   \n",
       "1  0690449001  Cotton 99%, Elastane 1%  Skinny fit            NaN  NaN   \n",
       "2  0690449002  Cotton 98%, Elastane 2%  Skinny fit            NaN  NaN   \n",
       "3  0690449006              Cotton 100%  Skinny fit            NaN  NaN   \n",
       "4  0690449006  Cotton 98%, Elastane 2%  Skinny fit            NaN  NaN   \n",
       "\n",
       "  more_sustainable_materials                    product_name product_price  \\\n",
       "0        Recycled cotton 20%  \\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans         39.99   \n",
       "1                        NaN  \\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans         16.99   \n",
       "2                        NaN  \\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans         14.99   \n",
       "3                        NaN  \\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans          7.99   \n",
       "4                        NaN  \\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans          7.99   \n",
       "\n",
       "                 color_name style_id color_id      scrapy_datetime  \n",
       "0                      Gray  0690449      051  2021-10-16 22:13:23  \n",
       "1  Light denim blue/trashed  0690449      001  2021-10-16 22:13:23  \n",
       "2                Denim blue  0690449      002  2021-10-16 22:13:23  \n",
       "3              Black/washed  0690449      006  2021-10-16 22:13:23  \n",
       "4              Black/washed  0690449      006  2021-10-16 22:13:23  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# final merge\n",
    "data_raw = df_compositions.copy()\n",
    "data_raw.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(452, 12)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_raw.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# lower column names\n",
    "data_raw.columns = data_raw.columns.str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_raw.to_csv('./data/data_raw.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. Data Cleaning (TRANSFORMING)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>composition</th>\n",
       "      <th>fit</th>\n",
       "      <th>product_safety</th>\n",
       "      <th>size</th>\n",
       "      <th>more_sustainable_materials</th>\n",
       "      <th>product_name</th>\n",
       "      <th>product_price</th>\n",
       "      <th>color_name</th>\n",
       "      <th>style_id</th>\n",
       "      <th>color_id</th>\n",
       "      <th>scrapy_datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>690449051</td>\n",
       "      <td>Cotton 98%, Elastane 2%</td>\n",
       "      <td>Skinny fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Recycled cotton 20%</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans</td>\n",
       "      <td>39.99</td>\n",
       "      <td>Gray</td>\n",
       "      <td>690449</td>\n",
       "      <td>51</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>690449001</td>\n",
       "      <td>Cotton 99%, Elastane 1%</td>\n",
       "      <td>Skinny fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans</td>\n",
       "      <td>16.99</td>\n",
       "      <td>Light denim blue/trashed</td>\n",
       "      <td>690449</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>690449002</td>\n",
       "      <td>Cotton 98%, Elastane 2%</td>\n",
       "      <td>Skinny fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans</td>\n",
       "      <td>14.99</td>\n",
       "      <td>Denim blue</td>\n",
       "      <td>690449</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>690449006</td>\n",
       "      <td>Cotton 100%</td>\n",
       "      <td>Skinny fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans</td>\n",
       "      <td>7.99</td>\n",
       "      <td>Black/washed</td>\n",
       "      <td>690449</td>\n",
       "      <td>6</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>690449006</td>\n",
       "      <td>Cotton 98%, Elastane 2%</td>\n",
       "      <td>Skinny fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans</td>\n",
       "      <td>7.99</td>\n",
       "      <td>Black/washed</td>\n",
       "      <td>690449</td>\n",
       "      <td>6</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id              composition         fit product_safety size  \\\n",
       "0   690449051  Cotton 98%, Elastane 2%  Skinny fit            NaN  NaN   \n",
       "1   690449001  Cotton 99%, Elastane 1%  Skinny fit            NaN  NaN   \n",
       "2   690449002  Cotton 98%, Elastane 2%  Skinny fit            NaN  NaN   \n",
       "3   690449006              Cotton 100%  Skinny fit            NaN  NaN   \n",
       "4   690449006  Cotton 98%, Elastane 2%  Skinny fit            NaN  NaN   \n",
       "\n",
       "  more_sustainable_materials                    product_name  product_price  \\\n",
       "0        Recycled cotton 20%  \\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans          39.99   \n",
       "1                        NaN  \\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans          16.99   \n",
       "2                        NaN  \\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans          14.99   \n",
       "3                        NaN  \\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans           7.99   \n",
       "4                        NaN  \\n\\t\\t\\t\\t\\t\\t\\t  Skinny Jeans           7.99   \n",
       "\n",
       "                 color_name  style_id  color_id      scrapy_datetime  \n",
       "0                      Gray    690449        51  2021-10-16 22:13:23  \n",
       "1  Light denim blue/trashed    690449         1  2021-10-16 22:13:23  \n",
       "2                Denim blue    690449         2  2021-10-16 22:13:23  \n",
       "3              Black/washed    690449         6  2021-10-16 22:13:23  \n",
       "4              Black/washed    690449         6  2021-10-16 22:13:23  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('./data/data_raw.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "product_id                      0\n",
       "composition                     0\n",
       "fit                             0\n",
       "product_safety                396\n",
       "size                          262\n",
       "more_sustainable_materials    219\n",
       "product_name                    0\n",
       "product_price                   0\n",
       "color_name                      0\n",
       "style_id                        0\n",
       "color_id                        0\n",
       "scrapy_datetime                 0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check missing values\n",
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "product_id                      int64\n",
       "composition                    object\n",
       "fit                            object\n",
       "product_safety                 object\n",
       "size                           object\n",
       "more_sustainable_materials     object\n",
       "product_name                   object\n",
       "product_price                 float64\n",
       "color_name                     object\n",
       "style_id                        int64\n",
       "color_id                        int64\n",
       "scrapy_datetime                object\n",
       "dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check data types\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "110"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df['product_id'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "tags": []
   },
   "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>composition</th>\n",
       "      <th>fit</th>\n",
       "      <th>product_safety</th>\n",
       "      <th>size</th>\n",
       "      <th>more_sustainable_materials</th>\n",
       "      <th>product_name</th>\n",
       "      <th>product_price</th>\n",
       "      <th>color_name</th>\n",
       "      <th>style_id</th>\n",
       "      <th>color_id</th>\n",
       "      <th>scrapy_datetime</th>\n",
       "      <th>size_number</th>\n",
       "      <th>size_model</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0690449051</td>\n",
       "      <td>Cotton 98%, Elastane 2%</td>\n",
       "      <td>skinny_fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Recycled cotton 20%</td>\n",
       "      <td>skinny_jeans</td>\n",
       "      <td>39.99</td>\n",
       "      <td>gray</td>\n",
       "      <td>0690449</td>\n",
       "      <td>051</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0690449001</td>\n",
       "      <td>Cotton 99%, Elastane 1%</td>\n",
       "      <td>skinny_fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>skinny_jeans</td>\n",
       "      <td>16.99</td>\n",
       "      <td>light_denim_blue/trashed</td>\n",
       "      <td>0690449</td>\n",
       "      <td>001</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id              composition         fit product_safety size  \\\n",
       "0  0690449051  Cotton 98%, Elastane 2%  skinny_fit            NaN  NaN   \n",
       "1  0690449001  Cotton 99%, Elastane 1%  skinny_fit            NaN  NaN   \n",
       "\n",
       "  more_sustainable_materials  product_name  product_price  \\\n",
       "0        Recycled cotton 20%  skinny_jeans          39.99   \n",
       "1                        NaN  skinny_jeans          16.99   \n",
       "\n",
       "                 color_name style_id color_id      scrapy_datetime  \\\n",
       "0                      gray  0690449      051  2021-10-16 22:13:23   \n",
       "1  light_denim_blue/trashed  0690449      001  2021-10-16 22:13:23   \n",
       "\n",
       "  size_number size_model  \n",
       "0         NaN        NaN  \n",
       "1         NaN        NaN  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# product_id\n",
    "df_data = df_compositions.dropna(subset = ['product_id'])\n",
    "\n",
    "# product name - change format and remove tabs and newlines\n",
    "df_data['product_name'] = df_data['product_name'].str.replace('\\n', '')\n",
    "df_data['product_name'] = df_data['product_name'].str.replace('\\t', '')\n",
    "df_data['product_name'] = df_data['product_name'].str.replace('  ', '')\n",
    "df_data['product_name'] = df_data['product_name'].str.replace(' ', '_').str.lower()\n",
    "\n",
    "# product price - remove $\n",
    "df_data['product_price'] = df_data['product_price'].astype(float)\n",
    "\n",
    "# # scrapy datetime\n",
    "# df_data['scrapy_datetime'] = pd.to_datetime(df_data['scrapy_datetime'], errors = 'coerce')\n",
    "\n",
    "# # style id\n",
    "# df_data['style_id'] = df_data['style_id'].astype(int)\n",
    "\n",
    "# # color id\n",
    "# df_data['color_id'] = df_data['color_id'].astype(int)\n",
    "\n",
    "# color name - change format\n",
    "df_data['color_name'] = df_data['color_name'].str.replace(' ', '_').str.lower()\\\n",
    "\n",
    "# fit\n",
    "df_data['fit'] = df_data['fit'].apply(lambda x: x.lower().replace(' ', '_') if pd.notnull(x) else x)\n",
    "\n",
    "# size number\n",
    "df_data['size_number'] = df_data['size'].apply(lambda x: re.search('\\d{3}cm', x).group(0) if pd.notnull(x) else x)\n",
    "df_data['size_number'] = df_data['size_number'].apply(lambda x: re.search('\\d+', x).group(0) if pd.notnull(x) else x)\n",
    "\n",
    "# size model\n",
    "df_data['size_model'] = df_data['size'].str.extract('(\\d+/\\\\d+)')\n",
    "\n",
    "# # composition\n",
    "# df_data = df_data[~df_data['composition'].str.contains('Pocket lining:', na = False)]\n",
    "# df_data = df_data[~df_data['composition'].str.contains('Lining:', na = False)]\n",
    "# df_data = df_data[~df_data['composition'].str.contains('Shell:', na = False)]\n",
    "# df_data = df_data[~df_data['composition'].str.contains('Pocket:', na = False)]\n",
    "\n",
    "# # drop duplicates\n",
    "# df_data = df_data.drop_duplicates(subset = ['product_id', 'product_category', 'product_name', 'product_price',\n",
    "#                                   'scrapy_datetime', 'style_id', 'color_id', 'color_name', 'fit'],\n",
    "#                         keep = 'last')\n",
    "\n",
    "# # reset index\n",
    "# df_data = df_data.reset_index(drop = True)\n",
    "\n",
    "# break composition by comma\n",
    "df1 = df_data['composition'].str.split(',', expand = True).reset_index(drop = True)\n",
    "\n",
    "df_data.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(110, 15)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# cotton / polyester / elastane / elasterell\n",
    "df_ref = pd.DataFrame(index = np.arange(len(df_data)), columns = ['cotton', 'polyester', 'elastane', 'elasterell'])\n",
    "\n",
    "# cotton\n",
    "df_cotton_0 = df1.loc[df1[0].str.contains('Cotton', na = True), 0]\n",
    "df_cotton_0.name = 'cotton'\n",
    "\n",
    "df_cotton_1 = df1.loc[df1[1].str.contains('Cotton', na = True), 1]\n",
    "df_cotton_1.name = 'cotton'\n",
    "\n",
    "## combine cotton\n",
    "df_cotton = df_cotton_0.combine_first(df_cotton_1)\n",
    "\n",
    "df_ref = pd.concat([df_ref, df_cotton], axis = 1)\n",
    "df_ref = df_ref.iloc[:, ~df_ref.columns.duplicated(keep = 'last')]\n",
    "\n",
    "# polyester\n",
    "df_polyester_0 = df1.loc[df1[0].str.contains('Polyester', na = True), 0]\n",
    "df_polyester_0.name = 'polyester'\n",
    "\n",
    "df_polyester_1 = df1.loc[df1[1].str.contains('Polyester', na = True), 1]\n",
    "df_polyester_1.name = 'polyester'\n",
    "\n",
    "## combine polyester\n",
    "df_polyester = df_polyester_0.combine_first(df_polyester_1)\n",
    "\n",
    "df_ref = pd.concat([df_ref, df_polyester], axis = 1)\n",
    "df_ref = df_ref.iloc[:, ~df_ref.columns.duplicated(keep = 'last')]\n",
    "\n",
    "# elastane\n",
    "df_elastane_1 = df1.loc[df1[1].str.contains('Elastane', na = True), 1]\n",
    "df_elastane_1.name = 'elastane'\n",
    "\n",
    "df_elastane_2 = df1.loc[df1[2].str.contains('Elastane', na = True), 2]\n",
    "df_elastane_2.name = 'elastane'\n",
    "\n",
    "df_elastane_3 = df1.loc[df1[3].str.contains('Elastane', na = True), 3]\n",
    "df_elastane_3.name = 'elastane'\n",
    "\n",
    "## combine elastane\n",
    "df_elastane_c2 = df_elastane_1.combine_first(df_elastane_2)\n",
    "df_elastane = df_elastane_c2.combine_first(df_elastane_3)\n",
    "\n",
    "df_ref = pd.concat([df_ref, df_elastane], axis = 1)\n",
    "df_ref = df_ref.iloc[:, ~df_ref.columns.duplicated(keep = 'last')]\n",
    "\n",
    "# elasterell\n",
    "df_elasterell = df1.loc[df1[1].str.contains('Elasterell', na = True), 1]\n",
    "df_elasterell.name = 'elasterell'\n",
    "\n",
    "df_ref = pd.concat([df_ref, df_elasterell], axis = 1)\n",
    "df_ref = df_ref.iloc[:, ~df_ref.columns.duplicated(keep = 'last')]\n",
    "\n",
    "# join combine df with product_id\n",
    "df_aux = pd.concat([df_data['product_id'].reset_index(drop = True), df_ref], axis = 1)\n",
    "\n",
    "# format composition data\n",
    "df_aux['cotton'] = df_aux['cotton'].apply(lambda x: int(re.search('\\d+', x).group(0)) / 100 if pd.notnull(x) else x)\n",
    "df_aux['polyester'] = df_aux['polyester'].apply(lambda x: int(re.search('\\d+', x).group(0)) / 100 if pd.notnull(x) else x)\n",
    "df_aux['elastane'] = df_aux['elastane'].apply(lambda x: int(re.search('\\d+', x).group(0)) / 100 if pd.notnull(x) else x)\n",
    "df_aux['elasterell'] = df_aux['elasterell'].apply(lambda x: int(re.search('\\d+', x).group(0)) / 100 if pd.notnull(x) else x)\n",
    "\n",
    "# final join\n",
    "df_aux = df_aux.groupby('product_id').max().reset_index().fillna(0)\n",
    "\n",
    "df_data = pd.merge(df_data, df_aux, on = 'product_id', how = 'left')\n",
    "\n",
    "# drop columns\n",
    "df_data = df_data.drop(columns = ['size', 'product_safety', 'composition'])\n",
    "\n",
    "# drop duplicates\n",
    "df_data = df_data.drop_duplicates(subset = ['product_id'], keep = 'last').reset_index(drop = True)\n",
    "df_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>fit</th>\n",
       "      <th>more_sustainable_materials</th>\n",
       "      <th>product_name</th>\n",
       "      <th>product_price</th>\n",
       "      <th>color_name</th>\n",
       "      <th>style_id</th>\n",
       "      <th>color_id</th>\n",
       "      <th>scrapy_datetime</th>\n",
       "      <th>size_number</th>\n",
       "      <th>size_model</th>\n",
       "      <th>cotton</th>\n",
       "      <th>polyester</th>\n",
       "      <th>elastane</th>\n",
       "      <th>elasterell</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0938875007</td>\n",
       "      <td>slim_fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>slim_tapered_jeans</td>\n",
       "      <td>39.99</td>\n",
       "      <td>black</td>\n",
       "      <td>0938875</td>\n",
       "      <td>007</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "      <td>188</td>\n",
       "      <td>31/32</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0938875001</td>\n",
       "      <td>slim_fit</td>\n",
       "      <td>Recycled cotton 20%</td>\n",
       "      <td>slim_tapered_jeans</td>\n",
       "      <td>39.99</td>\n",
       "      <td>light_denim_blue</td>\n",
       "      <td>0938875</td>\n",
       "      <td>001</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "      <td>187</td>\n",
       "      <td>31/30</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0938875002</td>\n",
       "      <td>slim_fit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>slim_tapered_jeans</td>\n",
       "      <td>39.99</td>\n",
       "      <td>white</td>\n",
       "      <td>0938875</td>\n",
       "      <td>002</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0938875004</td>\n",
       "      <td>slim_fit</td>\n",
       "      <td>Recycled cotton 20%</td>\n",
       "      <td>slim_tapered_jeans</td>\n",
       "      <td>18.99</td>\n",
       "      <td>dark_gray</td>\n",
       "      <td>0938875</td>\n",
       "      <td>004</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0938875008</td>\n",
       "      <td>slim_fit</td>\n",
       "      <td>Recycled cotton 20%</td>\n",
       "      <td>slim_tapered_jeans</td>\n",
       "      <td>39.99</td>\n",
       "      <td>denim_blue</td>\n",
       "      <td>0938875</td>\n",
       "      <td>008</td>\n",
       "      <td>2021-10-16 22:13:23</td>\n",
       "      <td>187</td>\n",
       "      <td>31/32</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id       fit more_sustainable_materials        product_name  \\\n",
       "0  0938875007  slim_fit                        NaN  slim_tapered_jeans   \n",
       "1  0938875001  slim_fit        Recycled cotton 20%  slim_tapered_jeans   \n",
       "2  0938875002  slim_fit                        NaN  slim_tapered_jeans   \n",
       "3  0938875004  slim_fit        Recycled cotton 20%  slim_tapered_jeans   \n",
       "4  0938875008  slim_fit        Recycled cotton 20%  slim_tapered_jeans   \n",
       "\n",
       "   product_price        color_name style_id color_id      scrapy_datetime  \\\n",
       "0          39.99             black  0938875      007  2021-10-16 22:13:23   \n",
       "1          39.99  light_denim_blue  0938875      001  2021-10-16 22:13:23   \n",
       "2          39.99             white  0938875      002  2021-10-16 22:13:23   \n",
       "3          18.99         dark_gray  0938875      004  2021-10-16 22:13:23   \n",
       "4          39.99        denim_blue  0938875      008  2021-10-16 22:13:23   \n",
       "\n",
       "  size_number size_model  cotton  polyester  elastane  elasterell  \n",
       "0         188      31/32     1.0        0.0      0.01         0.0  \n",
       "1         187      31/30     1.0        0.0      0.01         0.0  \n",
       "2         NaN        NaN     1.0        0.0      0.01         0.0  \n",
       "3         NaN        NaN     1.0        0.0      0.01         0.0  \n",
       "4         187      31/32     1.0        0.0      0.01         0.0  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data.to_csv('./data/data_clean.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4. Save data in a database (LOAD)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_clean = pd.read_csv('./data/data_clean.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-20T23:12:29.590223Z",
     "start_time": "2021-10-20T23:12:29.581245Z"
    }
   },
   "outputs": [],
   "source": [
    "query_showroom_schema = \"\"\"\n",
    "CREATE TABLE showroom (\n",
    "    product_id INTEGER,\n",
    "    product_name TEXT, \n",
    "    product_price REAL,\n",
    "    scrapy_datetime TEXT, \n",
    "    style_id INTEGER, \n",
    "    color_id TEXT, \n",
    "    color_name TEXT, \n",
    "    fit TEXT,\n",
    "    more_sustainable_materials TEXT,\n",
    "    size_number REAL, \n",
    "    size_model TEXT, \n",
    "    cotton REAL,\n",
    "    polyester REAL,\n",
    "    elastane REAL,\n",
    "    elasterell REAL\n",
    "    )\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-20T23:12:30.266539Z",
     "start_time": "2021-10-20T23:12:30.255567Z"
    }
   },
   "outputs": [],
   "source": [
    "# create engine sqlalchemy\n",
    "# conn = create_engine('sqlite:///./database/hm_db.sqlite', echo = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-20T23:12:30.556759Z",
     "start_time": "2021-10-20T23:12:30.436082Z"
    }
   },
   "outputs": [],
   "source": [
    "# connect to database\n",
    "conn = sqlite3.connect('./database/hm_db.sqlite')\n",
    "# cursor = conn.execute(query_showroom_schema)\n",
    "# conn.commit()\n",
    "# conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert data to table\n",
    "df_data.to_sql('showroom', con = conn, if_exists = 'append', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product_id</th>\n",
       "      <th>product_category</th>\n",
       "      <th>product_name</th>\n",
       "      <th>product_price</th>\n",
       "      <th>scrapy_datetime</th>\n",
       "      <th>style_id</th>\n",
       "      <th>color_id</th>\n",
       "      <th>color_name</th>\n",
       "      <th>fit</th>\n",
       "      <th>more_sustainable_materials</th>\n",
       "      <th>size_number</th>\n",
       "      <th>size_model</th>\n",
       "      <th>cotton</th>\n",
       "      <th>polyester</th>\n",
       "      <th>elastane</th>\n",
       "      <th>elasterell</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>811993028</td>\n",
       "      <td>None</td>\n",
       "      <td>regular_jeans</td>\n",
       "      <td>29.99</td>\n",
       "      <td>2021-10-14 00:12:26</td>\n",
       "      <td>811993</td>\n",
       "      <td>028</td>\n",
       "      <td>denim_blue</td>\n",
       "      <td>regular_fit</td>\n",
       "      <td>Recycled cotton 20%</td>\n",
       "      <td>185.0</td>\n",
       "      <td>31/32</td>\n",
       "      <td>0.99</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>811993001</td>\n",
       "      <td>None</td>\n",
       "      <td>regular_jeans</td>\n",
       "      <td>12.99</td>\n",
       "      <td>2021-10-14 00:12:26</td>\n",
       "      <td>811993</td>\n",
       "      <td>001</td>\n",
       "      <td>black/washed_out</td>\n",
       "      <td>regular_fit</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>0.98</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>811993002</td>\n",
       "      <td>None</td>\n",
       "      <td>regular_jeans</td>\n",
       "      <td>29.99</td>\n",
       "      <td>2021-10-14 00:12:26</td>\n",
       "      <td>811993</td>\n",
       "      <td>002</td>\n",
       "      <td>denim_blue</td>\n",
       "      <td>regular_fit</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>0.98</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>811993003</td>\n",
       "      <td>None</td>\n",
       "      <td>regular_jeans</td>\n",
       "      <td>29.99</td>\n",
       "      <td>2021-10-14 00:12:26</td>\n",
       "      <td>811993</td>\n",
       "      <td>003</td>\n",
       "      <td>dark_denim_blue</td>\n",
       "      <td>regular_fit</td>\n",
       "      <td>Recycled cotton 20%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>0.98</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>811993006</td>\n",
       "      <td>None</td>\n",
       "      <td>regular_jeans</td>\n",
       "      <td>14.99</td>\n",
       "      <td>2021-10-14 00:12:26</td>\n",
       "      <td>811993</td>\n",
       "      <td>006</td>\n",
       "      <td>graphite_gray</td>\n",
       "      <td>regular_fit</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>0.98</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id product_category   product_name  product_price  \\\n",
       "0   811993028             None  regular_jeans          29.99   \n",
       "1   811993001             None  regular_jeans          12.99   \n",
       "2   811993002             None  regular_jeans          29.99   \n",
       "3   811993003             None  regular_jeans          29.99   \n",
       "4   811993006             None  regular_jeans          14.99   \n",
       "\n",
       "       scrapy_datetime  style_id color_id        color_name          fit  \\\n",
       "0  2021-10-14 00:12:26    811993      028        denim_blue  regular_fit   \n",
       "1  2021-10-14 00:12:26    811993      001  black/washed_out  regular_fit   \n",
       "2  2021-10-14 00:12:26    811993      002        denim_blue  regular_fit   \n",
       "3  2021-10-14 00:12:26    811993      003   dark_denim_blue  regular_fit   \n",
       "4  2021-10-14 00:12:26    811993      006     graphite_gray  regular_fit   \n",
       "\n",
       "  more_sustainable_materials  size_number size_model  cotton  polyester  \\\n",
       "0        Recycled cotton 20%        185.0      31/32    0.99        0.0   \n",
       "1                       None          NaN       None    0.98        0.0   \n",
       "2                       None          NaN       None    0.98        0.0   \n",
       "3        Recycled cotton 20%          NaN       None    0.98        0.0   \n",
       "4                       None          NaN       None    0.98        0.0   \n",
       "\n",
       "   elastane  elasterell  \n",
       "0      0.01         0.0  \n",
       "1      0.02         0.0  \n",
       "2      0.02         0.0  \n",
       "3      0.02         0.0  \n",
       "4      0.02         0.0  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# extract data from database\n",
    "query = \"\"\"\n",
    "SELECT\n",
    "    *\n",
    "FROM\n",
    "    showroom\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(341, 16)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # command - UPDATE\n",
    "# query = \"\"\"\n",
    "# UPDATE showroom\n",
    "# SET product_category = 'tshirt'\n",
    "# WHERE product_id = 690449051\n",
    "# \"\"\"\n",
    "\n",
    "#command - DROP\n",
    "# query = \"\"\"\n",
    "# DROP TABLE showroom\n",
    "# \"\"\"\n",
    "\n",
    "# command - ALTER TABLE\n",
    "# query = \"\"\"\n",
    "# ALTER TABLE showroom\n",
    "# RENAME showroom_two\n",
    "# \"\"\"\n",
    "\n",
    "# command - CREATE INDEX\n",
    "query = \"\"\"\n",
    "CREATE INDEX idx_product_id\n",
    "ON showroom (product_id)\n",
    "\"\"\"\n",
    "\n",
    "conn = sqlite3.connect('./database/hm_db.sqlite')\n",
    "cursor = conn.execute(query)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}