Jewelry Appraisal Project.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ring Appraisal Project\n",
    "\n",
    "## URLs: \n",
    "### cartier_catalog.csv: https://www.kaggle.com/marcelopesse/cartier-jewelry-catalog\n",
    "#### data set created April 2020\n",
    "\n",
    "### jewelry.csv: https://www.kaggle.com/victormegir/jewelry-from-haritidisgr\n",
    "#### data set created March 2021"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import random as rand\n",
    "import datetime as dt\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_jewelry = pd.read_csv('jewelry.csv')\n",
    "df_cartier = pd.read_csv('cartier_catalog.csv', usecols = ['categorie', 'title', 'price', 'tags', 'description'])\n",
    "\n",
    "df_jewelry = df_jewelry.drop_duplicates().reset_index(drop=True)\n",
    "df_cartier = df_cartier.drop_duplicates().reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Converting jewelry.csv price column to USD using 3/15/2021 exchange rate because of the scrape date.\n",
    "\n",
    "#### Can be connected to an exchange rate API for live data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_jewelry['price_usd'] = round(df_jewelry['price'] * 1.19,2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>name</th>\n",
       "      <th>price</th>\n",
       "      <th>sex</th>\n",
       "      <th>jewel_type</th>\n",
       "      <th>brand</th>\n",
       "      <th>material</th>\n",
       "      <th>color</th>\n",
       "      <th>jewel_weight</th>\n",
       "      <th>rocks</th>\n",
       "      <th>rock_details</th>\n",
       "      <th>dimensions</th>\n",
       "      <th>chain_carat</th>\n",
       "      <th>chain_length</th>\n",
       "      <th>diameter</th>\n",
       "      <th>price_usd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Set Necklace &amp; Earrings Gloria Hope Silver / P...</td>\n",
       "      <td>75</td>\n",
       "      <td>Women</td>\n",
       "      <td>Necklaces</td>\n",
       "      <td>Gloria Hope</td>\n",
       "      <td>Metal Alloy</td>\n",
       "      <td>White</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>89.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Set Necklace &amp; Earrings Gloria Hope Silver / P...</td>\n",
       "      <td>109</td>\n",
       "      <td>Women</td>\n",
       "      <td>Necklaces</td>\n",
       "      <td>Gloria Hope</td>\n",
       "      <td>Metal Alloy</td>\n",
       "      <td>White</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>129.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Set Necklace &amp; Earrings Gloria Hope Silver / P...</td>\n",
       "      <td>99</td>\n",
       "      <td>Women</td>\n",
       "      <td>Necklaces</td>\n",
       "      <td>Gloria Hope</td>\n",
       "      <td>Metal Alloy</td>\n",
       "      <td>White</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>117.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Set Necklace &amp; Earrings Gloria Hope Silver</td>\n",
       "      <td>69</td>\n",
       "      <td>Women</td>\n",
       "      <td>Necklaces</td>\n",
       "      <td>Gloria Hope</td>\n",
       "      <td>Silver</td>\n",
       "      <td>White</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>82.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Necklace tear Gloria Hope Silver</td>\n",
       "      <td>69</td>\n",
       "      <td>Women</td>\n",
       "      <td>Necklaces</td>\n",
       "      <td>Gloria Hope</td>\n",
       "      <td>Metal Alloy</td>\n",
       "      <td>White</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Length 6cm</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>82.11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                name  price    sex jewel_type  \\\n",
       "0  Set Necklace & Earrings Gloria Hope Silver / P...     75  Women  Necklaces   \n",
       "1  Set Necklace & Earrings Gloria Hope Silver / P...    109  Women  Necklaces   \n",
       "2  Set Necklace & Earrings Gloria Hope Silver / P...     99  Women  Necklaces   \n",
       "3         Set Necklace & Earrings Gloria Hope Silver     69  Women  Necklaces   \n",
       "4                   Necklace tear Gloria Hope Silver     69  Women  Necklaces   \n",
       "\n",
       "         brand     material  color  jewel_weight rocks rock_details  \\\n",
       "0  Gloria Hope  Metal Alloy  White           NaN   NaN          NaN   \n",
       "1  Gloria Hope  Metal Alloy  White           NaN   NaN          NaN   \n",
       "2  Gloria Hope  Metal Alloy  White           NaN   NaN          NaN   \n",
       "3  Gloria Hope       Silver  White           NaN   NaN          NaN   \n",
       "4  Gloria Hope  Metal Alloy  White           NaN   NaN          NaN   \n",
       "\n",
       "   dimensions  chain_carat  chain_length  diameter  price_usd  \n",
       "0         NaN          NaN           NaN       NaN      89.25  \n",
       "1         NaN          NaN           NaN       NaN     129.71  \n",
       "2         NaN          NaN           NaN       NaN     117.81  \n",
       "3         NaN          NaN           NaN       NaN      82.11  \n",
       "4  Length 6cm          NaN           NaN       NaN      82.11  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_jewelry.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating brand column in Cartier dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cartier['brand'] = 'Cartier'"
   ]
  },
  {
   "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>categorie</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "      <th>tags</th>\n",
       "      <th>description</th>\n",
       "      <th>brand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love ring</td>\n",
       "      <td>1650.0</td>\n",
       "      <td>yellow gold</td>\n",
       "      <td>Love ring, 18K yellow gold. Width: 5.5mm.</td>\n",
       "      <td>Cartier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love ring</td>\n",
       "      <td>3650.0</td>\n",
       "      <td>platinum</td>\n",
       "      <td>Love ring, 950/1000 platinum. Width: 5.5mm.</td>\n",
       "      <td>Cartier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love ring</td>\n",
       "      <td>1650.0</td>\n",
       "      <td>pink gold</td>\n",
       "      <td>Love ring, 18K rose gold. Width: 5.5mm.</td>\n",
       "      <td>Cartier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love ring</td>\n",
       "      <td>1770.0</td>\n",
       "      <td>white gold</td>\n",
       "      <td>Love ring, 18K white gold. Width: 5.5mm.</td>\n",
       "      <td>Cartier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love wedding band</td>\n",
       "      <td>1070.0</td>\n",
       "      <td>yellow gold</td>\n",
       "      <td>Love wedding band, 18K yellow gold. Width: 3.6mm.</td>\n",
       "      <td>Cartier</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  categorie              title   price         tags  \\\n",
       "0     rings          Love ring  1650.0  yellow gold   \n",
       "1     rings          Love ring  3650.0     platinum   \n",
       "2     rings          Love ring  1650.0    pink gold   \n",
       "3     rings          Love ring  1770.0   white gold   \n",
       "4     rings  Love wedding band  1070.0  yellow gold   \n",
       "\n",
       "                                         description    brand  \n",
       "0          Love ring, 18K yellow gold. Width: 5.5mm.  Cartier  \n",
       "1        Love ring, 950/1000 platinum. Width: 5.5mm.  Cartier  \n",
       "2            Love ring, 18K rose gold. Width: 5.5mm.  Cartier  \n",
       "3           Love ring, 18K white gold. Width: 5.5mm.  Cartier  \n",
       "4  Love wedding band, 18K yellow gold. Width: 3.6mm.  Cartier  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cartier.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How df_jewelry columns map to df_cartier:\n",
    "\n",
    "### name = title, price_usd = price, jewel_type = categorie (kind of), other description columns = description (kind of) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['White', 'Gold', 'Rose', nan, 'White, Gold & Rose', 'White & Gold',\n",
       "       'White & Rose', 'Black', 'Gunmetal', 'Rose & Black', 'Gold & Rose'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_jewelry['color'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['yellow gold', 'platinum', 'pink gold', 'white gold',\n",
       "       'yellow gold, diamonds', 'pink gold, diamonds',\n",
       "       'white gold, diamonds', 'yellow gold, diamond',\n",
       "       'pink gold, diamond', 'white gold, diamond',\n",
       "       'white gold, ceramic, diamonds', 'pink gold, sapphire',\n",
       "       'pink gold, sapphires, garnets, amethyst',\n",
       "       'pink gold, white gold, diamonds',\n",
       "       'pink gold, diamonds, emeralds, onyx',\n",
       "       'yellow gold, tsavorite garnets, onyx',\n",
       "       'white gold, emeralds, onyx, diamonds',\n",
       "       'platinum, sapphires, emeralds, diamonds',\n",
       "       'pink gold, tsavorite garnets, onyx',\n",
       "       'yellow gold, onyx, tsavorite garnet',\n",
       "       'white gold, tsavorite garnets, onyx',\n",
       "       'yellow gold, lacquer, diamonds, tsavorite garnet',\n",
       "       'pink gold, tsavorite garnets, onyx, black lacquer, diamonds',\n",
       "       'pink gold, onyx, emeralds, diamonds',\n",
       "       'yellow gold, emeralds, onyx, diamonds',\n",
       "       'yellow gold, lacquer, tsavorite garnet, onyx',\n",
       "       'yellow gold, onyx, tsavorite garnets, diamonds',\n",
       "       'white gold, diamonds, emeralds, onyx',\n",
       "       'yellow gold, diamonds, emeralds, onyx',\n",
       "       'non-rhodiumized white gold, tsavorite garnets, onyx',\n",
       "       'yellow gold, lacquer, peridots, onyx',\n",
       "       'platinum, sapphires, emeralds, onyx, diamonds',\n",
       "       'platinum, emeralds, onyx, diamonds',\n",
       "       'white gold, sapphires, emeralds, onyx, black ceramic, diamonds',\n",
       "       'white gold, sapphires, emeralds, mother-of-pearl, diamonds',\n",
       "       'white gold, emeralds, diamonds', 'platinum, emeralds, diamonds',\n",
       "       'pink gold, spinels, diamonds',\n",
       "       'yellow gold, emeralds, carnelians, diamonds',\n",
       "       'yellow gold, chrysoprase, lapis lazuli, diamonds',\n",
       "       'white gold, yellow gold, pink gold, diamonds',\n",
       "       'white gold, yellow gold, pink gold',\n",
       "       'white gold, pink gold, yellow gold', 'white gold, ceramic',\n",
       "       'platinum, diamond', 'white gold, ceramic, platinum, diamonds',\n",
       "       'white gold, diamonds, ceramic', 'platinum, diamonds',\n",
       "       'platinum, sapphires, diamonds', 'pink gold, amazonite',\n",
       "       'yellow gold, coral',\n",
       "       'yellow gold, diamond, white mother-of-pearl',\n",
       "       'pink gold, malachite, diamond', 'pink gold, diamond, onyx',\n",
       "       'pink gold, onyx, white mother-of-pearl, diamond',\n",
       "       'pink gold, sapphires, garnets, amethysts',\n",
       "       'white gold, aquamarines, sapphires, spinels, amethysts',\n",
       "       'pink gold, ceramic, diamonds', 'pink gold, pink sapphire',\n",
       "       'yellow gold, tsavorite garnets, diamonds',\n",
       "       'yellow gold, lacquer, diamond, tsavorite garnet, onyx',\n",
       "       'yellow gold, lacquer, diamonds, tsavorite garnets, onyx',\n",
       "       'pink gold, tsavorite garnets, diamonds',\n",
       "       'yellow gold, lacquer, tsavorite garnets, onyx',\n",
       "       'pink gold, black lacquer, onyx, tsavorite garnets',\n",
       "       'white gold, lacquer, tsavorite garnets, onyx',\n",
       "       'pink gold, emeralds, obsidians, diamonds',\n",
       "       'yellow gold, emeralds, diamonds',\n",
       "       'yellow gold, emeralds, chrysoprases, carnelians, diamonds',\n",
       "       'white gold, yellow gold, pink gold, diamond',\n",
       "       'white gold, diamonds, sapphires', 'white gold, sapphires',\n",
       "       'yellow gold, diamonds, white mother-of-pearl',\n",
       "       'pink gold, onyx, diamonds', 'pink gold, onyx, diamond',\n",
       "       'yellow gold, lapis lazuli, diamond',\n",
       "       'pink gold, carnelian, diamond',\n",
       "       'yellow gold, chrysoprase, diamond',\n",
       "       'yellow gold, aventurine, diamonds',\n",
       "       'white gold, emeralds, diamonds, onyx',\n",
       "       'white gold, black lacquer, emeralds, diamonds',\n",
       "       'yellow gold, black lacquer, tsavorite garnets, onyx, diamonds',\n",
       "       'pink gold, tsavorite garnets, onyx, black lacquer, diamonds.',\n",
       "       'yellow gold, black lacquer, citrine, tsavorite garnets, diamonds',\n",
       "       'yellow gold, lacquer, diamonds, tsavorite garnet, onyx',\n",
       "       'white gold, emeralds, onyx',\n",
       "       'pink gold, black lacquer, tsavorite garnets',\n",
       "       'yellow gold, ceramic, lacquer, tsavorite garnet, diamonds',\n",
       "       'white gold, black lacquer, emeralds, onyx, diamonds',\n",
       "       'yellow gold, emeralds, onyx, diamonds.',\n",
       "       'white gold, emeralds, ceramic, onyx, diamonds',\n",
       "       'pink gold, tsavorite garnet, spessartite garnet, mother-of-pearl, brown diamonds, yellow diamonds, orange diamonds, diamonds',\n",
       "       'white gold, emeralds, mother-of-pearl, diamonds',\n",
       "       'white gold, rubies, emeralds, mother-of-pearl, diamonds',\n",
       "       'white gold, emeralds, sapphire, mother-of-pearl, diamonds',\n",
       "       'yellow gold, white mother-of-pearl, diamonds',\n",
       "       'yellow gold, diamonds, tsavorite garnets, onyx',\n",
       "       'platinum, sapphires, onyx, diamonds',\n",
       "       'white gold, emeralds, sapphires, mother-of-pearl, diamonds',\n",
       "       'white gold, sapphires, emeralds, gray mother-of-pearl, diamonds',\n",
       "       'pink gold, pink sapphires', 'pink gold, pearl',\n",
       "       'white gold, pearl'], dtype=object)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cartier['tags'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_jewelry['color'] = df_jewelry['color'].str.replace('Gold','Yellow')\n",
    "\n",
    "df_cartier['tags'] = df_cartier['tags'].str.replace('yellow gold','Yellow')\n",
    "df_cartier['tags'] = df_cartier['tags'].str.replace('white gold','White')\n",
    "df_cartier['tags'] = df_cartier['tags'].str.replace('pink gold','Rose')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_jewelry.fillna(np.nan,inplace=True)\n",
    "df_cartier.fillna(np.nan,inplace=True)\n",
    "\n",
    "df_jewelry.replace('nan',np.nan,inplace=True)\n",
    "df_cartier.replace('nan',np.nan,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Determining price inflation with 10 random rings from the Cartier dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "index_set = set()\n",
    "while True:\n",
    "    ind = rand.choice(df_cartier[df_cartier['categorie'] == 'rings'].index)\n",
    "    index_set.add(ind)\n",
    "    if len(index_set) == 10:\n",
    "        break\n",
    "\n",
    "index_list = list(index_set)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prices of 10 random cartier items from the dataset\n",
    "start = [1110,20700,6000,2020,13600,1040,8800,1130,1380,171000]\n",
    "# start = df_cartier.loc[index_list]['price'].values\n",
    "\n",
    "# Prices on Cartier website. Taken in June 2021\n",
    "end = [1220,22800,6600,2220,12800,1140,9650,1210,1520,192000]\n",
    "\n",
    "dif = list(map(lambda l: (l[1] - l[0]) / l[0], zip(start,end)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0828532254367677"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "average_price_increase = sum(dif)/len(dif)\n",
    "average_price_increase"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'The number of months between my webscrape and the original webscrape: 14'"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_after = dt.datetime.strptime('062021', \"%m%Y\").date() # date of my Cartier webscrape\n",
    "d_start = dt.datetime.strptime('042020', \"%m%Y\").date() # date of dataset Cartier webscrape\n",
    "num_of_months = (d_after.year - d_start.year) * 12 + d_after.month - d_start.month\n",
    "\n",
    "f'The number of months between my webscrape and the original webscrape: {num_of_months}'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The rate r = 6.84 % compounded monthly\n"
     ]
    }
   ],
   "source": [
    "# Determining the monthly price inflation rate using the compound interest formula\n",
    "P = 1 \n",
    "n = 12 # months\n",
    "A = average_price_increase + 1\n",
    "t = num_of_months/12\n",
    "r = round(n*((A/P)**(1/(t*n)) - 1),4)\n",
    "print(f'The rate r = {r*100:.2f} % compounded monthly')\n",
    "# This number will be used in the API for determining the current price"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checking jewelry types and keeping only rings:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Necklaces', 'Earrings', 'Bracelet', 'Ring', 'Cuff links',\n",
       "       'Religious jewels', 'Cross', 'Jewels ID', 'Safety pins', nan,\n",
       "       'Accessories', 'Link', 'Key ring', 'Pendant', 'Chain',\n",
       "       'Wedding rings', 'Brooches'], dtype=object)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_jewelry['jewel_type'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['rings', 'bracelets', 'necklaces', 'earrings'], dtype=object)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cartier['categorie'].unique()"
   ]
  },
  {
   "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>name</th>\n",
       "      <th>price</th>\n",
       "      <th>sex</th>\n",
       "      <th>jewel_type</th>\n",
       "      <th>brand</th>\n",
       "      <th>material</th>\n",
       "      <th>color</th>\n",
       "      <th>jewel_weight</th>\n",
       "      <th>rocks</th>\n",
       "      <th>rock_details</th>\n",
       "      <th>dimensions</th>\n",
       "      <th>chain_carat</th>\n",
       "      <th>chain_length</th>\n",
       "      <th>diameter</th>\n",
       "      <th>price_usd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Ring with emerald</td>\n",
       "      <td>1650</td>\n",
       "      <td>Women</td>\n",
       "      <td>Ring</td>\n",
       "      <td>Haritidis</td>\n",
       "      <td>Gold 18ct.</td>\n",
       "      <td>White</td>\n",
       "      <td>3.8</td>\n",
       "      <td>Diamond, Emerald</td>\n",
       "      <td>Recrystallized Emerald 2.93ct &amp; Diamond 0.56ct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1963.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Monoch Ring</td>\n",
       "      <td>1200</td>\n",
       "      <td>Women</td>\n",
       "      <td>Ring</td>\n",
       "      <td>Haritidis</td>\n",
       "      <td>Gold 18ct.</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>2.4</td>\n",
       "      <td>Diamond</td>\n",
       "      <td>Kent.Dress 0.11ct &amp; Other Diamonds 0.26ct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1428.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Ring halfway</td>\n",
       "      <td>1180</td>\n",
       "      <td>Women</td>\n",
       "      <td>Ring</td>\n",
       "      <td>Haritidis</td>\n",
       "      <td>Gold 18ct.</td>\n",
       "      <td>White</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Diamond</td>\n",
       "      <td>Diamonds 0.45ct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1404.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Ring halfway</td>\n",
       "      <td>980</td>\n",
       "      <td>Women</td>\n",
       "      <td>Ring</td>\n",
       "      <td>Haritidis</td>\n",
       "      <td>Gold 18ct.</td>\n",
       "      <td>White</td>\n",
       "      <td>2.3</td>\n",
       "      <td>Diamond</td>\n",
       "      <td>Diamonds 0.24ct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1166.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Ring halfway</td>\n",
       "      <td>1850</td>\n",
       "      <td>Women</td>\n",
       "      <td>Ring</td>\n",
       "      <td>Haritidis</td>\n",
       "      <td>Gold 18ct.</td>\n",
       "      <td>White</td>\n",
       "      <td>2.8</td>\n",
       "      <td>Diamond</td>\n",
       "      <td>Diamonds 0.66ct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2201.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 name  price    sex jewel_type      brand    material   color  \\\n",
       "7   Ring with emerald   1650  Women       Ring  Haritidis  Gold 18ct.   White   \n",
       "8         Monoch Ring   1200  Women       Ring  Haritidis  Gold 18ct.  Yellow   \n",
       "9        Ring halfway   1180  Women       Ring  Haritidis  Gold 18ct.   White   \n",
       "10       Ring halfway    980  Women       Ring  Haritidis  Gold 18ct.   White   \n",
       "11       Ring halfway   1850  Women       Ring  Haritidis  Gold 18ct.   White   \n",
       "\n",
       "    jewel_weight             rocks  \\\n",
       "7            3.8  Diamond, Emerald   \n",
       "8            2.4           Diamond   \n",
       "9            2.0           Diamond   \n",
       "10           2.3           Diamond   \n",
       "11           2.8           Diamond   \n",
       "\n",
       "                                      rock_details dimensions  chain_carat  \\\n",
       "7   Recrystallized Emerald 2.93ct & Diamond 0.56ct        NaN          NaN   \n",
       "8        Kent.Dress 0.11ct & Other Diamonds 0.26ct        NaN          NaN   \n",
       "9                                  Diamonds 0.45ct        NaN          NaN   \n",
       "10                                 Diamonds 0.24ct        NaN          NaN   \n",
       "11                                 Diamonds 0.66ct        NaN          NaN   \n",
       "\n",
       "    chain_length  diameter  price_usd  \n",
       "7            NaN       NaN     1963.5  \n",
       "8            NaN       NaN     1428.0  \n",
       "9            NaN       NaN     1404.2  \n",
       "10           NaN       NaN     1166.2  \n",
       "11           NaN       NaN     2201.5  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ring_j = df_jewelry[df_jewelry['jewel_type'].isin(['Wedding rings','Ring'])]\n",
    "df_ring_j.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>categorie</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "      <th>tags</th>\n",
       "      <th>description</th>\n",
       "      <th>brand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love ring</td>\n",
       "      <td>1650.0</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>Love ring, 18K yellow gold. Width: 5.5mm.</td>\n",
       "      <td>Cartier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love ring</td>\n",
       "      <td>3650.0</td>\n",
       "      <td>platinum</td>\n",
       "      <td>Love ring, 950/1000 platinum. Width: 5.5mm.</td>\n",
       "      <td>Cartier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love ring</td>\n",
       "      <td>1650.0</td>\n",
       "      <td>Rose</td>\n",
       "      <td>Love ring, 18K rose gold. Width: 5.5mm.</td>\n",
       "      <td>Cartier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love ring</td>\n",
       "      <td>1770.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Love ring, 18K white gold. Width: 5.5mm.</td>\n",
       "      <td>Cartier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love wedding band</td>\n",
       "      <td>1070.0</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>Love wedding band, 18K yellow gold. Width: 3.6mm.</td>\n",
       "      <td>Cartier</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  categorie              title   price      tags  \\\n",
       "0     rings          Love ring  1650.0    Yellow   \n",
       "1     rings          Love ring  3650.0  platinum   \n",
       "2     rings          Love ring  1650.0      Rose   \n",
       "3     rings          Love ring  1770.0     White   \n",
       "4     rings  Love wedding band  1070.0    Yellow   \n",
       "\n",
       "                                         description    brand  \n",
       "0          Love ring, 18K yellow gold. Width: 5.5mm.  Cartier  \n",
       "1        Love ring, 950/1000 platinum. Width: 5.5mm.  Cartier  \n",
       "2            Love ring, 18K rose gold. Width: 5.5mm.  Cartier  \n",
       "3           Love ring, 18K white gold. Width: 5.5mm.  Cartier  \n",
       "4  Love wedding band, 18K yellow gold. Width: 3.6mm.  Cartier  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ring_c = df_cartier[df_cartier['categorie'] == 'rings']\n",
    "df_ring_c.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Love wedding band, 18K yellow gold. Width: 3.6mm.',\n",
       "       'Love wedding band, 18K rose gold. Width: 3.6mm.',\n",
       "       'Love wedding band, 18K white gold. Width: 3.6mm.',\n",
       "       'Love wedding band, 950/1000 platinum. Width: 3.6mm.',\n",
       "       'Love wedding band, 18K yellow gold, set with 1 brilliant-cut diamonds totaling 0.02 carats. Width: 4mm.',\n",
       "       'Love wedding band, 18K rose gold, set with 1 brilliant-cut diamonds totaling 0.02 carats. Width: 4mm.',\n",
       "       'Love wedding band, 18K white gold, set with 1 brilliant-cut diamond totaling 0.02 carats. Width: 4mm.',\n",
       "       'Love wedding band, 18K yellow gold, set with 8 brilliant-cut diamonds totaling 0.19 carats. Width: 4mm.',\n",
       "       'Love wedding band, 18K rose gold, set with 8 brilliant-cut diamonds totaling 0.19 carats. Width: 4mm.',\n",
       "       'Love wedding band, 18K white gold, set with 8 brilliant-cut diamond totaling 0.19 carats. Width: 4mm.',\n",
       "       'Love wedding band, 18K yellow gold, set with 88 brilliant-cut diamonds totaling 0.31 carats. 4mm to 5mm width following metrics.',\n",
       "       'Love wedding band, 18K rose gold, set with 88 brilliant-cut diamonds totaling 0.31 carats. 4 to 5 width following sizes.',\n",
       "       'Love wedding band, 18K white gold, set with 88 brilliant-cut diamond totaling 0.31 carats. 4mm to 5mm width following sizes.',\n",
       "       'Cactus de Cartier wedding band, 18K yellow gold, set with 15 brilliant-cut diamonds totaling 0.32 carats.',\n",
       "       'Cactus de Cartier wedding band, 18K pink gold, set with 15 brilliant-cut diamonds totaling 0.3 carats.',\n",
       "       'Cactus de Cartier wedding band, 18K white gold, set with 15 brilliant-cut diamonds totaling 0.32 carats.',\n",
       "       'Trinity wedding band, width 2.5 mm, 18K pink gold, 18K white gold, 18K yellow gold.',\n",
       "       'Trinity wedding band, width 3.5 mm, 18K pink gold, 18K white gold, 18K yellow gold.',\n",
       "       'Trinity wedding band, width 4.8 mm, 18K pink gold, 18K white gold, 18K yellow gold.',\n",
       "       'Trinity de Cartier wedding band, width 4.8 mm, 18K white gold.',\n",
       "       'Trinity wedding band, width 3.5 mm, 18K pink gold, 18K white gold, 18K yellow gold, set with 53 brilliant-cut diamonds totaling 0.23 carat.',\n",
       "       'Trinity Ruban wedding band, 1 row, 950‰ platinum, set with 23 brilliant-cut diamonds totaling 0.22 carat. Width: 2.0 mm.',\n",
       "       'Trinity Ruban wedding band, 2 rows, 950‰ platinum, set with 49 brilliant-cut diamonds totaling 0.49 carats. Width: 2.5 mm.',\n",
       "       '1895 wedding band, width 2.5 mm, 18K yellow gold.',\n",
       "       '1895 wedding band, width 2.5 mm, 18K pink gold.',\n",
       "       '1895 wedding band, width 2.6 mm, 18K yellow gold, set with a brilliant-cut diamond of 0.01 carat.',\n",
       "       '1895 wedding band, width 2.6 mm, 18K pink gold, set with a brilliant-cut diamond of 0.01 carat.',\n",
       "       '1895 wedding band, width 2 mm, platinum 950‰.',\n",
       "       '1895 wedding band, width 3.5 mm, 18K yellow gold.',\n",
       "       '1895 wedding band, width 3.5 mm, 18K pink gold.',\n",
       "       '1895 wedding band, width 2.5 mm, platinum 950‰.',\n",
       "       '1895 wedding band, width 4 mm, 18K yellow gold.',\n",
       "       '1895 wedding band, width 2.6 mm, platinum 950‰, set with a brilliant-cut diamond of 0.01 carat.',\n",
       "       '1895 wedding band, width 5 mm, 18K yellow gold.',\n",
       "       '1895 wedding band, width 3.5 mm, platinum 950‰.',\n",
       "       '1895 wedding band, width 2.6 mm, platinum 950‰, set with 3 brilliant-cut diamonds totaling 0.03 carat.',\n",
       "       '1895 wedding band, width 4 mm, platinum 950‰.',\n",
       "       '1895 wedding band, width 3.5 mm, platinum 950‰, set with a brilliant-cut diamond of 0.04 carat.',\n",
       "       '1895 wedding band, width 5 mm, platinum 950‰.',\n",
       "       '1895 wedding band, width 2.6 mm, platinum 950‰, set with 19 brilliant-cut diamonds totaling 0.27 carat.',\n",
       "       'Étincelle de Cartier wedding band, 18K white gold, set with 38 brilliant-cut diamonds totaling 0.47 carats. Width: 2 mm.',\n",
       "       \"Cartier d'Amour wedding band, 18K pink gold. Width: 2.5 mm\",\n",
       "       \"Cartier d'Amour wedding band, platinum 950‰. Width: 1.8 mm\",\n",
       "       'C de Cartier wedding band, width 3 mm, 18K pink gold.',\n",
       "       \"Cartier d'Amour wedding band, platinum 950‰. Width 2.5 mm\",\n",
       "       'Ballerine wedding band, width 2.1 mm, platinum 950‰.',\n",
       "       'C de Cartier wedding band, width 3 mm, 18K pink gold, set with a brilliant-cut diamond of 0.03 carat.',\n",
       "       'C de Cartier wedding band, 18K pink gold, set with a brilliant-cut diamond of 0.02 carats. Width: 3 mm.',\n",
       "       'Logo de Cartier ring, 18K pink gold.',\n",
       "       'C de Cartier wedding band, width 4 mm, 18K pink gold.',\n",
       "       'Ballerine curved wedding band, width 2 mm, platinum 950‰.',\n",
       "       'Logo de Cartier ring, 18K white gold.',\n",
       "       'Ballerine wedding band, width 2.1 mm, platinum 950‰, set with a brilliant-cut diamond of 0.01 carat.',\n",
       "       'C de Cartier wedding band, width 3 mm, platinum 950‰.',\n",
       "       'C de Cartier wedding band, width 4 mm, 18K pink gold, set with a brilliant-cut diamond of 0.03 carats.',\n",
       "       'Ballerine curved wedding band, width 2 mm, platinum 950‰, set with 3 brilliant-cut diamonds totaling 0.01 carats.',\n",
       "       'C de Cartier wedding band, width 3 mm, platinum 950‰, set with a brilliant-cut diamond of 0.03 carat.',\n",
       "       'C de Cartier wedding band, 950‰ platinum, set with a brilliant-cut diamond of 0.02 carats. Width: 3 mm.',\n",
       "       'C de Cartier wedding band, width 4 mm, platinum 950‰.',\n",
       "       'Étincelle de Cartier wedding band, 18K pink gold, set with 49 brilliant-cut diamonds totaling 0.22 carats. 1.52 mm wide. Also available as a midi ring.',\n",
       "       'Étincelle de Cartier wedding band, 18K yellow gold, set with 49 brilliant-cut diamonds totaling 0.22 carats. 1.52 mm wide. Also available as a midi ring.',\n",
       "       \"Cartier d'Amour wedding band, width 5 mm, 950‰ platinum.\",\n",
       "       'Étincelle de Cartier wedding band, 18K white gold, set with 49 brilliant-cut diamonds totaling 0.22 carats. 1.52 mm wide. Also available as a midi ring.',\n",
       "       'C de Cartier wedding band, width 3 mm, platinum 950‰, set with 2 brilliant-cut diamonds totaling 0.07 carat.',\n",
       "       'Ballerine curved wedding band, width 2 mm, platinum 950‰, set with 27 brilliant-cut diamonds totaling 0.09 carats.',\n",
       "       'C de Cartier wedding band, width 4 mm, platinum 950‰, set with a brilliant-cut diamond of 0.03 carats.',\n",
       "       'C de Cartier wedding band, width 5 mm, 950‰ platinum.',\n",
       "       \"Cartier d'Amour wedding band, width 6 mm, 950‰ platinum.\",\n",
       "       'Etincelle de Cartier wedding band, 18K pink gold, set with 22 brilliant-cut diamonds totaling 0.27 carat. Width 2.0 mm.',\n",
       "       'C de Cartier wedding band, width 5 mm, 950‰ platinum, set with a brilliant-cut diamond of 0.04 carats.',\n",
       "       'Etincelle de Cartier wedding band, 950‰ platinum, set with 22 brilliant-cut diamonds totaling 0.27 carat. Width 2.00 mm.',\n",
       "       'Étincelle de Cartier wedding band, 950‰ platinum, set with 19 brilliant-cut diamonds totaling 0.24 carat and 19 brilliant-cut sapphires totaling 0.34 carat. Width: 2 mm.',\n",
       "       'Étincelle de Cartier wedding band, 18K yellow gold, set with 38 brilliant-cut diamonds totaling 0.47 carats. Width: 2 mm.',\n",
       "       'Étincelle de Cartier wedding band, 18K pink gold, set with 38 brilliant-cut diamonds totaling 0.47 carats. Width: 2 mm.',\n",
       "       'Ballerine wedding band, width 2.4 mm, platinum 950‰, set with 39 brilliant-cut diamonds totaling 0.38 carat.',\n",
       "       'Etincelle de Cartier wedding band, 18K pink gold, set with 16 brilliant-cut diamonds totaling 0.51 carat. Width 2.6 mm.',\n",
       "       'Etincelle de Cartier wedding band, 950‰ platinum, set with 16 brilliant-cut diamonds totaling 0.51 carat. Width 2.6 mm.',\n",
       "       'Étincelle de Cartier wedding band, 950‰ platinum, set with 14 brilliant-cut diamonds totaling 0.45 carats and 14 brilliant-cut sapphires totaling 0.63 carats. Width 2.6 mm.',\n",
       "       'Étincelle de Cartier wedding band, 18K yellow gold, set with 29 brilliant-cut diamonds totaling 0.94 carats. Width: 2.6 mm.',\n",
       "       'Étincelle de Cartier wedding band, 18K pink gold, set with 29 brilliant-cut diamonds totaling 0.94 carats. Width: 2.6 mm.',\n",
       "       'Étincelle de Cartier wedding band, 950‰ platinum, set with 29 brilliant-cut diamonds totaling 0.94 carats. Width: 2.6 mm.',\n",
       "       'Etincelle de Cartier wedding band, 18K pink gold, set with 14 brilliant-cut diamonds totaling 0.94 carat. Width 3.5 mm.',\n",
       "       'Cartier Destinée wedding band, width 3.27 mm, 18K pink gold, set with 22 brilliant-cut diamonds totaling 1.34 carats.',\n",
       "       'Etincelle de Cartier wedding band, 950‰ platinum, set with 14 brilliant-cut diamonds totaling 0.94 carats. Width 3.5 mm.',\n",
       "       'Cartier Destinée wedding band, width 3.3 mm, platinum 950‰, set with 22 brilliant-cut diamonds totaling 1.34 carats.',\n",
       "       'Reflection de Cartier wedding band, 18K white gold, set with 36 brilliant-cut diamonds totaling 1.58 carats, and with 18 baguette-cut diamonds totaling 1.94 carats',\n",
       "       'Maillon Panthère wedding band, 18K pink gold.',\n",
       "       'Maillon Panthère wedding band, 18K yellow gold.',\n",
       "       'Maillon Panthère wedding band, 18K white gold.',\n",
       "       'Maillon Panthère wedding band, 18K yellow gold, set with 4 brilliant-cut diamonds totaling 0.05 carat.',\n",
       "       'Maillon Panthère wedding band, 18K pink gold, set with 4 brilliant-cut diamonds totaling 0.05 carat.',\n",
       "       'Maillon Panthère wedding band, 18K white gold, set with 4 brilliant-cut diamonds totaling 0.05 carat.',\n",
       "       'Maillon Panthère wedding band, 18K yellow gold, set with 12 brilliant-cut diamonds totaling 0.15 carats.',\n",
       "       'Maillon Panthère wedding band, 18K pink gold, set with 12 brilliant-cut diamonds totaling 0.15 carats.',\n",
       "       'Maillon Panthère wedding band, 18K white gold, set with 12 brilliant-cut diamonds totaling 0.15 carats.',\n",
       "       'Maillon Panthère wedding band, 18K pink gold, set with 42 brilliant-cut diamonds totaling 0.30 carat.',\n",
       "       'Maillon Panthère wedding band, 18K yellow gold, set with 42 brilliant-cut diamonds totaling 0.3 carats.',\n",
       "       'Maillon Panthère wedding band, 18K white gold, set with 42 brilliant-cut diamonds totaling 0.30 carat.',\n",
       "       'Pluie de Cartier wedding band, 18K white gold, set with 90 brilliant-cut diamonds totaling 5.20 carats.'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ring_c.loc[df_ring_c['description'].str.contains('wedding',case=False,na=False) |\n",
    "              df_ring_c['title'].str.contains('wedding',case=False,na=False)]['description'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": false
   },
   "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>categorie</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "      <th>tags</th>\n",
       "      <th>description</th>\n",
       "      <th>brand</th>\n",
       "      <th>jewel_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love ring</td>\n",
       "      <td>1650.0</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>Love ring, 18K yellow gold. Width: 5.5mm.</td>\n",
       "      <td>Cartier</td>\n",
       "      <td>Ring</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love ring</td>\n",
       "      <td>3650.0</td>\n",
       "      <td>platinum</td>\n",
       "      <td>Love ring, 950/1000 platinum. Width: 5.5mm.</td>\n",
       "      <td>Cartier</td>\n",
       "      <td>Ring</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love ring</td>\n",
       "      <td>1650.0</td>\n",
       "      <td>Rose</td>\n",
       "      <td>Love ring, 18K rose gold. Width: 5.5mm.</td>\n",
       "      <td>Cartier</td>\n",
       "      <td>Ring</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love ring</td>\n",
       "      <td>1770.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Love ring, 18K white gold. Width: 5.5mm.</td>\n",
       "      <td>Cartier</td>\n",
       "      <td>Ring</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love wedding band</td>\n",
       "      <td>1070.0</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>Love wedding band, 18K yellow gold. Width: 3.6mm.</td>\n",
       "      <td>Cartier</td>\n",
       "      <td>Wedding rings</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  categorie              title   price      tags  \\\n",
       "0     rings          Love ring  1650.0    Yellow   \n",
       "1     rings          Love ring  3650.0  platinum   \n",
       "2     rings          Love ring  1650.0      Rose   \n",
       "3     rings          Love ring  1770.0     White   \n",
       "4     rings  Love wedding band  1070.0    Yellow   \n",
       "\n",
       "                                         description    brand     jewel_type  \n",
       "0          Love ring, 18K yellow gold. Width: 5.5mm.  Cartier           Ring  \n",
       "1        Love ring, 950/1000 platinum. Width: 5.5mm.  Cartier           Ring  \n",
       "2            Love ring, 18K rose gold. Width: 5.5mm.  Cartier           Ring  \n",
       "3           Love ring, 18K white gold. Width: 5.5mm.  Cartier           Ring  \n",
       "4  Love wedding band, 18K yellow gold. Width: 3.6mm.  Cartier  Wedding rings  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ring_c.loc[df_ring_c['description'].str.contains('wedding',case=False,na=False) |\n",
    "              df_ring_c['title'].str.contains('wedding',case=False,na=False), 'jewel_type'] = 'Wedding rings'\n",
    "df_ring_c.loc[df_ring_c['jewel_type'].isna(), 'jewel_type'] = 'Ring'\n",
    "\n",
    "df_ring_c.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Feature Creation and Homogenization \n",
    "### Cartier Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ring_c.loc[(df_ring_c['tags'].str.contains('White',na=False)),'color'] = 'White'\n",
    "\n",
    "df_ring_c.loc[(df_ring_c['tags'].str.contains('Rose',na=False)),'color'] = 'Rose'\n",
    "\n",
    "df_ring_c.loc[(df_ring_c['tags'].str.contains('Yellow',na=False)),'color'] = 'Yellow'\n",
    "\n",
    "df_ring_c.loc[(df_ring_c['tags'].str.contains('White',na=False)) \n",
    "              & (df_ring_c['tags'].str.contains('Rose',na=False)),'color'] = 'White & Rose'\n",
    "\n",
    "df_ring_c.loc[(df_ring_c['tags'].str.contains('White',na=False))\n",
    "              & (df_ring_c['tags'].str.contains('Yellow',na=False)),'color'] = 'White & Yellow'\n",
    "\n",
    "df_ring_c.loc[(df_ring_c['tags'].str.contains('Rose',na=False)) \n",
    "              & (df_ring_c['tags'].str.contains('Yellow',na=False)),'color'] = 'Yellow & Rose'\n",
    "\n",
    "df_ring_c.loc[(df_ring_c['tags'].str.contains('White',na=False)) \n",
    "              & (df_ring_c['tags'].str.contains('Rose',na=False)) \n",
    "              & (df_ring_c['tags'].str.contains('Yellow',na=False)),'color'] = 'White & Yellow & Rose'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ring_c_gold = df_ring_c[df_ring_c['color'].isin(['Yellow', 'Rose', 'White', 'Yellow & Rose', 'White & Yellow', \n",
    "                                                    'White & Rose', 'White & Yellow & Rose']) \n",
    "                           & ~df_ring_c['description'].str.contains('‰ platinum')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "c_diamond_carats = df_ring_c_gold['description'].str\\\n",
    "                    .extractall(r\"diamonds totaling (\\d+\\.\\d+) carat\"\n",
    "                               ,flags=re.IGNORECASE)\\\n",
    "                    .astype(float).droplevel('match')\n",
    "\n",
    "total_c_diamond_carats = c_diamond_carats.groupby(c_diamond_carats.index).agg({0: sum})\n",
    "df_ring_c_gold['total diamond carats'] = total_c_diamond_carats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "gold_carats_df = df_ring_c_gold['description'].str\\\n",
    "                    .extractall(r\"(\\d+)K\",\n",
    "                               flags=re.IGNORECASE)\\\n",
    "                    .astype(int).droplevel('match')\n",
    "\n",
    "final_gold_carats_df = gold_carats_df.groupby(gold_carats_df.index).agg({0: 'first'})\n",
    "df_ring_c_gold['gold carats'] = final_gold_carats_df\n",
    "df_ring_c_gold = df_ring_c_gold[~df_ring_c_gold['gold carats'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "c_jewel_weight = df_ring_c_gold['description'].str\\\n",
    "                    .extractall(r\"totaling (\\d+\\.\\d+) carat\"\n",
    "                                ,flags=re.IGNORECASE)\\\n",
    "                    .astype(float).droplevel('match')\n",
    "\n",
    "total_c_jewel_weight = c_jewel_weight.groupby(c_jewel_weight.index).agg({0: sum})\n",
    "df_ring_c_gold['jewel_weight'] = total_c_jewel_weight"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>categorie</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "      <th>tags</th>\n",
       "      <th>description</th>\n",
       "      <th>brand</th>\n",
       "      <th>jewel_type</th>\n",
       "      <th>color</th>\n",
       "      <th>total diamond carats</th>\n",
       "      <th>gold carats</th>\n",
       "      <th>jewel_weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love ring</td>\n",
       "      <td>1650.0</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>Love ring, 18K yellow gold. Width: 5.5mm.</td>\n",
       "      <td>Cartier</td>\n",
       "      <td>Ring</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love ring</td>\n",
       "      <td>1650.0</td>\n",
       "      <td>Rose</td>\n",
       "      <td>Love ring, 18K rose gold. Width: 5.5mm.</td>\n",
       "      <td>Cartier</td>\n",
       "      <td>Ring</td>\n",
       "      <td>Rose</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love ring</td>\n",
       "      <td>1770.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Love ring, 18K white gold. Width: 5.5mm.</td>\n",
       "      <td>Cartier</td>\n",
       "      <td>Ring</td>\n",
       "      <td>White</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love wedding band</td>\n",
       "      <td>1070.0</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>Love wedding band, 18K yellow gold. Width: 3.6mm.</td>\n",
       "      <td>Cartier</td>\n",
       "      <td>Wedding rings</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>rings</td>\n",
       "      <td>Love wedding band</td>\n",
       "      <td>1070.0</td>\n",
       "      <td>Rose</td>\n",
       "      <td>Love wedding band, 18K rose gold. Width: 3.6mm.</td>\n",
       "      <td>Cartier</td>\n",
       "      <td>Wedding rings</td>\n",
       "      <td>Rose</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  categorie              title   price    tags  \\\n",
       "0     rings          Love ring  1650.0  Yellow   \n",
       "2     rings          Love ring  1650.0    Rose   \n",
       "3     rings          Love ring  1770.0   White   \n",
       "4     rings  Love wedding band  1070.0  Yellow   \n",
       "5     rings  Love wedding band  1070.0    Rose   \n",
       "\n",
       "                                         description    brand     jewel_type  \\\n",
       "0          Love ring, 18K yellow gold. Width: 5.5mm.  Cartier           Ring   \n",
       "2            Love ring, 18K rose gold. Width: 5.5mm.  Cartier           Ring   \n",
       "3           Love ring, 18K white gold. Width: 5.5mm.  Cartier           Ring   \n",
       "4  Love wedding band, 18K yellow gold. Width: 3.6mm.  Cartier  Wedding rings   \n",
       "5    Love wedding band, 18K rose gold. Width: 3.6mm.  Cartier  Wedding rings   \n",
       "\n",
       "    color  total diamond carats  gold carats  jewel_weight  \n",
       "0  Yellow                   NaN         18.0           NaN  \n",
       "2    Rose                   NaN         18.0           NaN  \n",
       "3   White                   NaN         18.0           NaN  \n",
       "4  Yellow                   NaN         18.0           NaN  \n",
       "5    Rose                   NaN         18.0           NaN  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ring_c_gold.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "first = df_ring_c_gold['description'].str.split(',', n=1, expand=True)[1]\n",
    "second = first.str.split('gold.', n=1, expand=True)[1]\n",
    "df_ring_c_gold['rock_details'] = second.str.split('Width', n=1, expand=True)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "bad_set_c = ['wide from size']\n",
    "\n",
    "bad_dict_c = {\n",
    "              'brilliant-diamonds wide also available as midi ring':'brilliant-diamonds',\n",
    "              'brilliant-diamonds all-gold ring paved ring':'brilliant-diamonds',\n",
    "              'black-ceramic tto ring ceramic ring':'black-ceramic',\n",
    "              'chrysoprases lapis lazulis brilliant-diamonds': 'chrysoprases lapis-lazulis brilliant-diamonds'\n",
    "             }\n",
    "\n",
    "def replace_func_c(x):\n",
    "    val = x\n",
    "    for num in list(map(str,list(range(10)))):\n",
    "         val = val.replace(num,'')\n",
    "    val = val.lower()\n",
    "    \n",
    "    val = val.replace('.','')\n",
    "    val = val.replace(',','')\n",
    "    val = val.replace('totaling','')\n",
    "    val = val.replace('carats','')\n",
    "    val = val.replace('carat','')\n",
    "    val = val.replace('set with','')\n",
    "    val = val.replace('-cut','')\n",
    "    val = val.replace('mm','')\n",
    "    val = val.replace('width','')\n",
    "    val = val.replace(':','')\n",
    "    val = val.replace(' a ',' ')\n",
    "    val = val.replace(' to ',' ')\n",
    "    val = val.replace(' and ',' ')\n",
    "    val = val.replace('and ',' ')\n",
    "    val = val.replace(' an ',' ')\n",
    "    val = val.replace('following','')\n",
    "    val = val.replace('metrics','')\n",
    "    val = val.replace('k white gold','')\n",
    "    val = val.replace('k rose gold','')\n",
    "    val = val.replace('k pink gold','')\n",
    "    val = val.replace('k yellow gold','')\n",
    "    val = val.replace('white gold','')\n",
    "    val = val.replace('rose gold','')\n",
    "    val = val.replace('sizes','')\n",
    "    val = val.replace('all-gold ring paved ring','')\n",
    "    val = val.replace('beads','')\n",
    "    val = val.replace('eyes','')\n",
    "    val = val.replace('nose','')\n",
    "    val = val.replace('with','')\n",
    "    val = val.replace(' of','')\n",
    "    val = val.replace(' center stone ',' ') \n",
    "    val = val.replace(' tto ring ceramic ring','')\n",
    "              \n",
    "    val = val.replace('black ','black-')\n",
    "    val = val.replace('rose ','rose-')\n",
    "    val = val.replace('blue ','blue-')\n",
    "    val = val.replace('yellow ','yellow-')\n",
    "    val = val.replace('green ','green-')\n",
    "    val = val.replace('orange ','orange-')\n",
    "    val = val.replace('brilliant ','brilliant-')\n",
    "    val = val.replace('tsavorite ','tsavorite-')\n",
    "    val = val.replace('pear-shaped ','pear-shaped-') \n",
    "    val = val.replace('baguette ','baguette-')\n",
    "    val = val.replace('princess ','princess-')\n",
    "    val = val.replace('troidia ','troidia-')\n",
    "    val = val.replace('brilliant-pavé ','brilliant-pavé-')\n",
    "    val = val.replace('mother-of pearl','mother-of-pearl')\n",
    "    val = val.replace('gray mother-of-pearl','gray-mother-of-pearl')\n",
    "\n",
    "    val = val.replace('(','')\n",
    "    val = val.replace(')','')\n",
    "    \n",
    "    val = val.replace('    ',' ')\n",
    "    val = val.replace('   ',' ')\n",
    "    val = val.replace('  ',' ')\n",
    "    \n",
    "    val = val.strip()\n",
    "    \n",
    "#     if val in ('oval',):\n",
    "#         print(val,x)\n",
    "    \n",
    "    if val in bad_set_c:\n",
    "        return ''\n",
    "    elif val in bad_dict_c:\n",
    "        return bad_dict_c[val]\n",
    "#     elif val == 'nan':\n",
    "#         return np.nan\n",
    "#     else:\n",
    "#         return val\n",
    "    return val"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ring_c_gold['abbrev description'] = df_ring_c_gold['rock_details'].apply(lambda x: replace_func_c(str(x)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['', 'brilliant-diamonds', 'brilliant-diamond',\n",
       "       'black-ceramic brilliant-diamonds', 'rose-sapphire',\n",
       "       'rose-sapphire blue-sapphire yellow-sapphire green-garnet orange-garnet amethyst',\n",
       "       'brilliant-diamonds emeralds onyx',\n",
       "       'onyx black-lacquer tsavorite-garnets', 'onyx tsavorite-garnets',\n",
       "       'tsavorite-garnets onyx',\n",
       "       'black-lacquer brilliant-diamonds tsavorite-garnet',\n",
       "       'brilliant-diamonds tsavorite-garnets onyx black-lacquer',\n",
       "       'onyx emeralds brilliant-diamonds',\n",
       "       'black-lacquer tsavorite-garnets onyx',\n",
       "       'brilliant-diamonds tsavorite-garnets onyx',\n",
       "       'black-lacquer peridots onyx', 'diamonds emeralds onyx',\n",
       "       'gray-mother-of-pearl emeralds sapphire brilliant-diamonds',\n",
       "       'gray-mother-of-pearl emerald sapphire brilliant-diamonds',\n",
       "       'emeralds brilliant-diamonds', 'emeralds onyx brilliant-diamonds',\n",
       "       'spinels brilliant-diamonds',\n",
       "       'emeralds carnelians brilliant-diamond',\n",
       "       'chrysoprases lapis-lazulis brilliant-diamonds', 'black-ceramic',\n",
       "       'emerald diamond brilliant-diamonds',\n",
       "       'princess-diamond brilliant-pavé-diamonds',\n",
       "       'brilliant-pavé-diamonds',\n",
       "       'troidia-diamond princess-diamond baguette-diamonds',\n",
       "       'baguette-diamonds brilliant-diamonds',\n",
       "       'brilliant-diamonds baguette-diamonds',\n",
       "       'baguette-diamonds princess-diamonds troidia-diamonds',\n",
       "       'rhodium-finish', 'baguette-diamonds',\n",
       "       'pear-shaped-diamonds brilliant-diamonds', 'amazonite', 'coral'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ring_c_gold['rock_details'].apply(lambda x: replace_func_c(str(x))).unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Putting a value of 1 in every column that corresponds to a term in the ring description."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "for idx in df_ring_c_gold['abbrev description'].index:\n",
    "    description = df_ring_c_gold['abbrev description'].loc[idx]\n",
    "    if description is np.nan:\n",
    "        continue\n",
    "    for term in description.split():\n",
    "        df_ring_c_gold.loc[idx,term] = 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Jewelry Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Only Gold rings have sufficient data\n",
    "df_ring_j_gold = df_ring_j[df_ring_j['material'].isin(['Gold 18ct.', 'Gold 14ct.', 'Gold 9ct.'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "j_diamond_carats = df_ring_j_gold['rock_details'].str\\\n",
    "                    .extractall(r\"diamond.? (?:triangle |brilliant |baguette |princess )?(\\d+\\.\\d+)(?!\\.|mm| mm|-)\"\n",
    "                                ,flags=re.IGNORECASE)\\\n",
    "                    .astype(float).droplevel('match')\n",
    "\n",
    "total_j_diamond_carats = j_diamond_carats.groupby(j_diamond_carats.index).agg({0: sum})\n",
    "df_ring_j_gold['total diamond carats'] = total_j_diamond_carats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ring_j_gold['gold carats'] = df_ring_j_gold['material'].str\\\n",
    "                                    .extractall(r\"(\\d+)(?:ct)?\",\n",
    "                                                flags=re.IGNORECASE)\\\n",
    "                                    .astype(float).droplevel('match')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>price</th>\n",
       "      <th>sex</th>\n",
       "      <th>jewel_type</th>\n",
       "      <th>brand</th>\n",
       "      <th>material</th>\n",
       "      <th>color</th>\n",
       "      <th>jewel_weight</th>\n",
       "      <th>rocks</th>\n",
       "      <th>rock_details</th>\n",
       "      <th>dimensions</th>\n",
       "      <th>chain_carat</th>\n",
       "      <th>chain_length</th>\n",
       "      <th>diameter</th>\n",
       "      <th>price_usd</th>\n",
       "      <th>total diamond carats</th>\n",
       "      <th>gold carats</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Ring with emerald</td>\n",
       "      <td>1650</td>\n",
       "      <td>Women</td>\n",
       "      <td>Ring</td>\n",
       "      <td>Haritidis</td>\n",
       "      <td>Gold 18ct.</td>\n",
       "      <td>White</td>\n",
       "      <td>3.8</td>\n",
       "      <td>Diamond, Emerald</td>\n",
       "      <td>Recrystallized Emerald 2.93ct &amp; Diamond 0.56ct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1963.5</td>\n",
       "      <td>0.56</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Monoch Ring</td>\n",
       "      <td>1200</td>\n",
       "      <td>Women</td>\n",
       "      <td>Ring</td>\n",
       "      <td>Haritidis</td>\n",
       "      <td>Gold 18ct.</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>2.4</td>\n",
       "      <td>Diamond</td>\n",
       "      <td>Kent.Dress 0.11ct &amp; Other Diamonds 0.26ct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1428.0</td>\n",
       "      <td>0.26</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Ring halfway</td>\n",
       "      <td>1180</td>\n",
       "      <td>Women</td>\n",
       "      <td>Ring</td>\n",
       "      <td>Haritidis</td>\n",
       "      <td>Gold 18ct.</td>\n",
       "      <td>White</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Diamond</td>\n",
       "      <td>Diamonds 0.45ct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1404.2</td>\n",
       "      <td>0.45</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Ring halfway</td>\n",
       "      <td>980</td>\n",
       "      <td>Women</td>\n",
       "      <td>Ring</td>\n",
       "      <td>Haritidis</td>\n",
       "      <td>Gold 18ct.</td>\n",
       "      <td>White</td>\n",
       "      <td>2.3</td>\n",
       "      <td>Diamond</td>\n",
       "      <td>Diamonds 0.24ct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1166.2</td>\n",
       "      <td>0.24</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Ring halfway</td>\n",
       "      <td>1850</td>\n",
       "      <td>Women</td>\n",
       "      <td>Ring</td>\n",
       "      <td>Haritidis</td>\n",
       "      <td>Gold 18ct.</td>\n",
       "      <td>White</td>\n",
       "      <td>2.8</td>\n",
       "      <td>Diamond</td>\n",
       "      <td>Diamonds 0.66ct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2201.5</td>\n",
       "      <td>0.66</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 name  price    sex jewel_type      brand    material   color  \\\n",
       "7   Ring with emerald   1650  Women       Ring  Haritidis  Gold 18ct.   White   \n",
       "8         Monoch Ring   1200  Women       Ring  Haritidis  Gold 18ct.  Yellow   \n",
       "9        Ring halfway   1180  Women       Ring  Haritidis  Gold 18ct.   White   \n",
       "10       Ring halfway    980  Women       Ring  Haritidis  Gold 18ct.   White   \n",
       "11       Ring halfway   1850  Women       Ring  Haritidis  Gold 18ct.   White   \n",
       "\n",
       "    jewel_weight             rocks  \\\n",
       "7            3.8  Diamond, Emerald   \n",
       "8            2.4           Diamond   \n",
       "9            2.0           Diamond   \n",
       "10           2.3           Diamond   \n",
       "11           2.8           Diamond   \n",
       "\n",
       "                                      rock_details dimensions  chain_carat  \\\n",
       "7   Recrystallized Emerald 2.93ct & Diamond 0.56ct        NaN          NaN   \n",
       "8        Kent.Dress 0.11ct & Other Diamonds 0.26ct        NaN          NaN   \n",
       "9                                  Diamonds 0.45ct        NaN          NaN   \n",
       "10                                 Diamonds 0.24ct        NaN          NaN   \n",
       "11                                 Diamonds 0.66ct        NaN          NaN   \n",
       "\n",
       "    chain_length  diameter  price_usd  total diamond carats  gold carats  \n",
       "7            NaN       NaN     1963.5                  0.56         18.0  \n",
       "8            NaN       NaN     1428.0                  0.26         18.0  \n",
       "9            NaN       NaN     1404.2                  0.45         18.0  \n",
       "10           NaN       NaN     1166.2                  0.24         18.0  \n",
       "11           NaN       NaN     2201.5                  0.66         18.0  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ring_j_gold.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "bad_dict_j = {\n",
    "              'diamond diamonds perimeter': 'diamond perimeter-diamonds', \n",
    "              'file krishzafori sapphires':'sapphires', 'brilliant diamonds': 'brilliant-diamonds',\n",
    "              'diamonds rest': 'diamonds', 'dimensions stone': 'delete', 'topaz violac': 'violac-topaz',\n",
    "              'diamond diamonds princess': 'diamond princess-diamonds', 'sinusite': 'delete',\n",
    "              'baguette brilliant diamonds': 'baguette-brilliant-diamonds',\n",
    "              'brilliant diamonds marquise': 'marquise-brilliant-diamonds',  \n",
    "              'baguette brilliant sapphires': 'baguette-brilliant-sapphires', \n",
    "              'baguette brilliant emerald':'baguette-brilliant-emerald',\n",
    "              'diamond diamonds oval':'oval-diamond diamonds',\n",
    "              'baguette brilliant diamond diamonds': 'baguette-diamond brilliant-diamonds'\n",
    "              }\n",
    "\n",
    "def replace_func_j(x):\n",
    "    val = x\n",
    "#     for num in list(map(str,list(range(10)))):\n",
    "#          val = val.replace(num,'')\n",
    "    val = re.sub('\\d+\\.?\\d*\\.?(ct|mm|t|pm)','',val)\n",
    "    val = re.sub('\\d+.\\d+','',val)\n",
    "    val = val.lower()\n",
    "    \n",
    "    val = re.sub('\\d+\\.?\\d*\\.?(ct|mm|t|pm)','',val)\n",
    "    val = re.sub('(?<!vs)\\d','',val)\n",
    "    \n",
    "    val = val.replace('-','')\n",
    "    val = val.replace('centrally ','')\n",
    "    val = val.replace('central ','')\n",
    "    val = val.replace('f.w.p','fresh-water-pearl')\n",
    "    val = val.replace('kent','')\n",
    "    val = val.replace(' x','')\n",
    "    val = val.replace('diam.','diamond')\n",
    "    val = val.replace('diamonda','diamonds')\n",
    "    val = val.replace('&','')\n",
    "    val = val.replace('and','')\n",
    "    val = val.replace('kashmir','kashmir-')\n",
    "    val = val.replace('spinell','spinel')\n",
    "    val = val.replace('topozy','topaz')\n",
    "    val = val.replace('zisite','zoisite')\n",
    "    val = val.replace('sitrin','citrine')\n",
    "    val = val.replace('moronganic','morganite')\n",
    "    val = val.replace('mop','mother-of-pearl')\n",
    "    val = val.replace('mofpearl','mother-of-pearl')\n",
    "    val = val.replace('akoumara','aquamarine')\n",
    "    val = val.replace('coffee ','coffee-')\n",
    "    val = val.replace('black ','black-')\n",
    "    val = val.replace('baby ','baby-')\n",
    "    val = val.replace('white ','white-')\n",
    "    val = val.replace('whites ','white-')\n",
    "    val = val.replace('green ','green-')\n",
    "    val = val.replace('black ','black-')\n",
    "    val = val.replace('pearl akoya','akoya-pearl')\n",
    "    val = val.replace('aqua ','aqua-')\n",
    "    val = val.replace('blue ','blue-')\n",
    "    val = val.replace('red ','red-')\n",
    "    val = val.replace('pearl edison','edison-pearl')\n",
    "    val = val.replace('blazing ','blazing-')\n",
    "    val = val.replace('paraiba ','paraiba-')\n",
    "    val = val.replace('london ','london-')\n",
    "    val = val.replace('triangle ','triangle-')\n",
    "    val = val.replace('pink ','pink-')\n",
    "    val = val.replace('rainforest ','rainforest-')\n",
    "    val = val.replace('pearl south sea','south-sea-pearl')\n",
    "    val = val.replace('recrystallized ','recrystallized-')\n",
    "    val = val.replace('fresh water pearl','fresh-water-pearl')\n",
    "    val = val.replace('dress','')\n",
    "    \n",
    "    val = val.replace('diamonds white-','white-diamonds')\n",
    "    val = val.replace('diamonds coffee-','coffee-diamonds')\n",
    "\n",
    "    val = val.replace('gronada','')\n",
    "    val = val.replace('zer','')\n",
    "    val = val.replace('day','')\n",
    "\n",
    "    val = val.replace('(','')\n",
    "    val = val.replace(')','')\n",
    "\n",
    "    val = val.replace('.mm','')\n",
    "    val = val.replace(' mm','')\n",
    "    val = val.replace('kent.dress','')\n",
    "\n",
    "    val = val.replace('.ct','')\n",
    "    val = val.replace(' ct','')\n",
    "    val = val.replace(' other ',' ')\n",
    "    val = val.replace(' rest',' ')\n",
    "    \n",
    "    val = val.replace(' .. ',' ')\n",
    "    val = val.replace(' . ',' ')\n",
    "    val = val.replace('.','')\n",
    "    val = val.replace('   ',' ')\n",
    "    val = val.replace('  ',' ')\n",
    "    \n",
    "    val = val.strip()\n",
    "    \n",
    "    val = val.replace('pearl pm','pearl')\n",
    "    val = val.replace('pearl fresh-water-pearl','fresh-water-pearl')\n",
    "    \n",
    "    val = val.replace('- ','-')\n",
    "    val = val.replace(' -','-')\n",
    "    val = val.replace('/',' ')\n",
    "    \n",
    "    val = ' '.join(sorted(list(set(val.split()))))\n",
    "\n",
    "    if val in bad_dict_j:\n",
    "        return bad_dict_j[val]\n",
    "    elif val == 'nan':\n",
    "        return np.nan\n",
    "    else:\n",
    "        return val"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ring_j_gold['abbrev description'] = df_ring_j_gold['rock_details'].apply(lambda x: replace_func_j(str(x)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ring_j_gold = df_ring_j_gold[df_ring_j_gold['abbrev description'] != 'delete']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['diamond recrystallized-emerald', 'diamonds', 'diamond',\n",
       "       'diamond topaz', 'diamond emerald', 'diamonds sapphire',\n",
       "       'diamond white-topaz', 'diamond sapphire', 'diamond tanzanite',\n",
       "       'diamond london-blue-topaz', 'diamond morganite', 'pearl',\n",
       "       'diamonds ruby', 'diamond white-sapphire', 'blue-topaz diamonds',\n",
       "       'diamonds emerald', 'diamond ruby', 'blue-topaz diamond',\n",
       "       'citrine diamond', 'blue-sapphire diamonds', 'emerald',\n",
       "       'blue-sapphire diamond', 'diamond diamonds f vs2',\n",
       "       'amethyst diamond', 'topaz', 'diamonds qendrad', 'diamond e vs2',\n",
       "       'diamond f vs2', 'diamond g vs2', 'diamond e vvs1',\n",
       "       'diamond triangle-diamonds', 'diamonds spinel',\n",
       "       'diamonds emeralds', 'blue-pink-sapphires diamonds',\n",
       "       'blue-sapphires diamonds', 'diamonds tanzanite',\n",
       "       'diamond princess-diamonds', 'diamond diamonds emerald',\n",
       "       'black-diamond white-diamonds', 'diamond pink-sapphire',\n",
       "       'diamond opal', 'aquamarine diamonds', 'diamonds sapphires',\n",
       "       'baguette-brilliant-diamonds', 'diamond diamonds',\n",
       "       'diamonds white-topaz', 'diamonds green-sapphire',\n",
       "       'black-diamond diamonds', 'diamonds morganite',\n",
       "       'marquise-brilliant-diamonds', 'emeralds',\n",
       "       'diamonds london-blue-topaz', 'aquamarine diamond',\n",
       "       'diamond rodolite', 'coffee-diamonds white-diamonds',\n",
       "       'd diamond vvs2', 'akoya-pearl diamonds', 'oval-diamond diamonds',\n",
       "       'black-diamond', 'akoya-pearl', 'diamonds south-sea-pearl',\n",
       "       'baguette-brilliant-sapphires', 'baguette-brilliant-emerald',\n",
       "       'brilliant-diamonds', 'baguette-diamond brilliant-diamonds',\n",
       "       'd diamond si', 'd diamond vs1', 'diamond e vs1', 'diamond e vvs2',\n",
       "       'diamond f vs1', 'diamond f vvs2', 'sapphire', 'diamond g vs1',\n",
       "       'diamond f si', 'aquamarine', 'black-diamonds',\n",
       "       'black-diamond diamond', 'amethyst diamonds', 'black-sapphire',\n",
       "       'morganite', 'diamonds pearl', 'edison-fresh-water-pearl',\n",
       "       'fresh-water-pearl', 'edison-pearl', 'diamonds rodolite',\n",
       "       'black-diamond coffee-diamonds',\n",
       "       'black-diamond coffee-diamond white-diamond', 'rainforest-topaz',\n",
       "       'violac-topaz', 'blazing-red-topaz', 'paraiba-topaz',\n",
       "       'aqua-blue-topaz', 'baby-pink-topaz', 'white-topaz',\n",
       "       'kashmir-blue-topaz', 'diamond g vvs1', 'diamonds pink-sapphire',\n",
       "       'diamond g si', 'pearls', 'black-diamonds white-diamonds',\n",
       "       'diamonds f vs2', 'green-topaz', 'coffee-diamonds',\n",
       "       'blue-diamonds', 'diamonds green-sapphires',\n",
       "       'diamonds pink-sapphires', 'd diamond vvs1',\n",
       "       'diamond perimeter-diamonds', 'diamond e si',\n",
       "       'coffee-diamonds diamonds', 'diamond zoisite',\n",
       "       'blue-diamond diamond', 'ruby', 'sapphires', 'red-topaz',\n",
       "       'diamond pink-quartz', 'coffee-diamonds sapphire', 'd diamond vs2',\n",
       "       'amethyst coffee-diamonds diamonds', 'diamond diamonds g vvs2',\n",
       "       'blue-diamonds diamonds', 'coffee-diamonds pearl',\n",
       "       'coffee-diamonds diamond', 'diamond quartz', 'amethyst',\n",
       "       'diamond tourmaline', 'diamond i vs2', 'diamond f vvs1',\n",
       "       'rodolite', 'diamond pearl', 'diamond mother-of-pearl'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ring_j_gold['abbrev description'][~df_ring_j_gold['abbrev description'].isna()].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Putting a value of 1 in every column that corresponds to a term in the ring description."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "for idx in df_ring_j_gold['abbrev description'].index:\n",
    "    description = df_ring_j_gold['abbrev description'].loc[idx]\n",
    "    if description is np.nan:\n",
    "        continue\n",
    "    for term in description.split():\n",
    "        df_ring_j_gold.loc[idx,term] = 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combining datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['categorie', 'title', 'price', 'tags', 'description', 'brand', 'jewel_type', 'color', 'total diamond carats', 'gold carats', 'jewel_weight', 'rock_details', 'abbrev description', 'brilliant-diamonds', 'brilliant-diamond', 'black-ceramic', 'rose-sapphire', 'blue-sapphire', 'yellow-sapphire', 'green-garnet', 'orange-garnet', 'amethyst', 'emeralds', 'onyx', 'black-lacquer', 'tsavorite-garnets', 'tsavorite-garnet', 'peridots', 'diamonds', 'gray-mother-of-pearl', 'sapphire', 'emerald', 'spinels', 'carnelians', 'chrysoprases', 'lapis-lazulis', 'diamond', 'princess-diamond', 'brilliant-pavé-diamonds', 'troidia-diamond', 'baguette-diamonds', 'princess-diamonds', 'troidia-diamonds', 'rhodium-finish', 'pear-shaped-diamonds', 'amazonite', 'coral']\n"
     ]
    }
   ],
   "source": [
    "print(list(df_ring_c_gold.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "c_cols = df_ring_c_gold.drop(['categorie', 'title', 'tags', 'description', 'rock_details', 'abbrev description',\n",
    "                              'abbrev description', 'jewel_type'],axis=1).columns\n",
    "\n",
    "cartier_final = df_ring_c_gold[c_cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['name', 'price', 'sex', 'jewel_type', 'brand', 'material', 'color', 'jewel_weight', 'rocks', 'rock_details', 'dimensions', 'chain_carat', 'chain_length', 'diameter', 'price_usd', 'total diamond carats', 'gold carats', 'abbrev description', 'diamond', 'recrystallized-emerald', 'diamonds', 'topaz', 'emerald', 'sapphire', 'white-topaz', 'tanzanite', 'london-blue-topaz', 'morganite', 'pearl', 'ruby', 'white-sapphire', 'blue-topaz', 'citrine', 'blue-sapphire', 'f', 'vs2', 'amethyst', 'qendrad', 'e', 'g', 'vvs1', 'triangle-diamonds', 'spinel', 'emeralds', 'blue-pink-sapphires', 'blue-sapphires', 'princess-diamonds', 'black-diamond', 'white-diamonds', 'pink-sapphire', 'opal', 'aquamarine', 'sapphires', 'baguette-brilliant-diamonds', 'green-sapphire', 'marquise-brilliant-diamonds', 'rodolite', 'coffee-diamonds', 'd', 'vvs2', 'akoya-pearl', 'oval-diamond', 'south-sea-pearl', 'baguette-brilliant-sapphires', 'baguette-brilliant-emerald', 'brilliant-diamonds', 'baguette-diamond', 'si', 'vs1', 'black-diamonds', 'black-sapphire', 'edison-fresh-water-pearl', 'fresh-water-pearl', 'edison-pearl', 'coffee-diamond', 'white-diamond', 'rainforest-topaz', 'violac-topaz', 'blazing-red-topaz', 'paraiba-topaz', 'aqua-blue-topaz', 'baby-pink-topaz', 'kashmir-blue-topaz', 'pearls', 'green-topaz', 'blue-diamonds', 'green-sapphires', 'pink-sapphires', 'perimeter-diamonds', 'zoisite', 'blue-diamond', 'red-topaz', 'pink-quartz', 'quartz', 'tourmaline', 'i', 'mother-of-pearl']\n"
     ]
    }
   ],
   "source": [
    "print(list(df_ring_j_gold.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "j_cols = df_ring_j_gold.drop(['price','name','sex', 'material','rocks', 'rock_details', 'dimensions', 'chain_carat',\n",
    "                              'chain_length','diameter','abbrev description', 'jewel_type'],axis=1).columns\n",
    "\n",
    "jewelry_final = df_ring_j_gold[j_cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['price', 'brand', 'color', 'total diamond carats', 'gold carats',\n",
       "       'jewel_weight', 'brilliant-diamonds', 'brilliant-diamond',\n",
       "       'black-ceramic', 'rose-sapphire', 'blue-sapphire', 'yellow-sapphire',\n",
       "       'green-garnet', 'orange-garnet', 'amethyst', 'emeralds', 'onyx',\n",
       "       'black-lacquer', 'tsavorite-garnets', 'tsavorite-garnet', 'peridots',\n",
       "       'diamonds', 'gray-mother-of-pearl', 'sapphire', 'emerald', 'spinels',\n",
       "       'carnelians', 'chrysoprases', 'lapis-lazulis', 'diamond',\n",
       "       'princess-diamond', 'brilliant-pavé-diamonds', 'troidia-diamond',\n",
       "       'baguette-diamonds', 'princess-diamonds', 'troidia-diamonds',\n",
       "       'rhodium-finish', 'pear-shaped-diamonds', 'amazonite', 'coral'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['brand', 'color', 'jewel_weight', 'price_usd', 'total diamond carats',\n",
       "       'gold carats', 'diamond', 'recrystallized-emerald', 'diamonds', 'topaz',\n",
       "       'emerald', 'sapphire', 'white-topaz', 'tanzanite', 'london-blue-topaz',\n",
       "       'morganite', 'pearl', 'ruby', 'white-sapphire', 'blue-topaz', 'citrine',\n",
       "       'blue-sapphire', 'f', 'vs2', 'amethyst', 'qendrad', 'e', 'g', 'vvs1',\n",
       "       'triangle-diamonds', 'spinel', 'emeralds', 'blue-pink-sapphires',\n",
       "       'blue-sapphires', 'princess-diamonds', 'black-diamond',\n",
       "       'white-diamonds', 'pink-sapphire', 'opal', 'aquamarine', 'sapphires',\n",
       "       'baguette-brilliant-diamonds', 'green-sapphire',\n",
       "       'marquise-brilliant-diamonds', 'rodolite', 'coffee-diamonds', 'd',\n",
       "       'vvs2', 'akoya-pearl', 'oval-diamond', 'south-sea-pearl',\n",
       "       'baguette-brilliant-sapphires', 'baguette-brilliant-emerald',\n",
       "       'brilliant-diamonds', 'baguette-diamond', 'si', 'vs1', 'black-diamonds',\n",
       "       'black-sapphire', 'edison-fresh-water-pearl', 'fresh-water-pearl',\n",
       "       'edison-pearl', 'coffee-diamond', 'white-diamond', 'rainforest-topaz',\n",
       "       'violac-topaz', 'blazing-red-topaz', 'paraiba-topaz', 'aqua-blue-topaz',\n",
       "       'baby-pink-topaz', 'kashmir-blue-topaz', 'pearls', 'green-topaz',\n",
       "       'blue-diamonds', 'green-sapphires', 'pink-sapphires',\n",
       "       'perimeter-diamonds', 'zoisite', 'blue-diamond', 'red-topaz',\n",
       "       'pink-quartz', 'quartz', 'tourmaline', 'i', 'mother-of-pearl'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "j_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "jewelry_final.rename(columns={'price_usd':'price'}, inplace=True)\n",
    "jewelry_final['brand'] = jewelry_final['brand'].fillna('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = pd.concat([cartier_final,jewelry_final], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "print(final_df.isna().sum().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['price', 'brand', 'color', 'total diamond carats', 'gold carats', 'jewel_weight', 'brilliant-diamonds', 'brilliant-diamond', 'black-ceramic', 'rose-sapphire', 'blue-sapphire', 'yellow-sapphire', 'green-garnet', 'orange-garnet', 'amethyst', 'emeralds', 'onyx', 'black-lacquer', 'tsavorite-garnets', 'tsavorite-garnet', 'peridots', 'diamonds', 'gray-mother-of-pearl', 'sapphire', 'emerald', 'spinels', 'carnelians', 'chrysoprases', 'lapis-lazulis', 'diamond', 'princess-diamond', 'brilliant-pavé-diamonds', 'troidia-diamond', 'baguette-diamonds', 'princess-diamonds', 'troidia-diamonds', 'rhodium-finish', 'pear-shaped-diamonds', 'amazonite', 'coral', 'recrystallized-emerald', 'topaz', 'white-topaz', 'tanzanite', 'london-blue-topaz', 'morganite', 'pearl', 'ruby', 'white-sapphire', 'blue-topaz', 'citrine', 'f', 'vs2', 'qendrad', 'e', 'g', 'vvs1', 'triangle-diamonds', 'spinel', 'blue-pink-sapphires', 'blue-sapphires', 'black-diamond', 'white-diamonds', 'pink-sapphire', 'opal', 'aquamarine', 'sapphires', 'baguette-brilliant-diamonds', 'green-sapphire', 'marquise-brilliant-diamonds', 'rodolite', 'coffee-diamonds', 'd', 'vvs2', 'akoya-pearl', 'oval-diamond', 'south-sea-pearl', 'baguette-brilliant-sapphires', 'baguette-brilliant-emerald', 'baguette-diamond', 'si', 'vs1', 'black-diamonds', 'black-sapphire', 'edison-fresh-water-pearl', 'fresh-water-pearl', 'edison-pearl', 'coffee-diamond', 'white-diamond', 'rainforest-topaz', 'violac-topaz', 'blazing-red-topaz', 'paraiba-topaz', 'aqua-blue-topaz', 'baby-pink-topaz', 'kashmir-blue-topaz', 'pearls', 'green-topaz', 'blue-diamonds', 'green-sapphires', 'pink-sapphires', 'perimeter-diamonds', 'zoisite', 'blue-diamond', 'red-topaz', 'pink-quartz', 'quartz', 'tourmaline', 'i', 'mother-of-pearl']\n"
     ]
    }
   ],
   "source": [
    "print(list(final_df.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>price</th>\n",
       "      <th>brand</th>\n",
       "      <th>color</th>\n",
       "      <th>total diamond carats</th>\n",
       "      <th>gold carats</th>\n",
       "      <th>jewel_weight</th>\n",
       "      <th>brilliant-diamonds</th>\n",
       "      <th>brilliant-diamond</th>\n",
       "      <th>black-ceramic</th>\n",
       "      <th>rose-sapphire</th>\n",
       "      <th>...</th>\n",
       "      <th>pink-sapphires</th>\n",
       "      <th>perimeter-diamonds</th>\n",
       "      <th>zoisite</th>\n",
       "      <th>blue-diamond</th>\n",
       "      <th>red-topaz</th>\n",
       "      <th>pink-quartz</th>\n",
       "      <th>quartz</th>\n",
       "      <th>tourmaline</th>\n",
       "      <th>i</th>\n",
       "      <th>mother-of-pearl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1650.0</td>\n",
       "      <td>Cartier</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1650.0</td>\n",
       "      <td>Cartier</td>\n",
       "      <td>Rose</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1770.0</td>\n",
       "      <td>Cartier</td>\n",
       "      <td>White</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1070.0</td>\n",
       "      <td>Cartier</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1070.0</td>\n",
       "      <td>Cartier</td>\n",
       "      <td>Rose</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 110 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    price    brand   color  total diamond carats  gold carats  jewel_weight  \\\n",
       "0  1650.0  Cartier  Yellow                   0.0         18.0           0.0   \n",
       "1  1650.0  Cartier    Rose                   0.0         18.0           0.0   \n",
       "2  1770.0  Cartier   White                   0.0         18.0           0.0   \n",
       "3  1070.0  Cartier  Yellow                   0.0         18.0           0.0   \n",
       "4  1070.0  Cartier    Rose                   0.0         18.0           0.0   \n",
       "\n",
       "   brilliant-diamonds  brilliant-diamond  black-ceramic  rose-sapphire  ...  \\\n",
       "0                 0.0                0.0            0.0            0.0  ...   \n",
       "1                 0.0                0.0            0.0            0.0  ...   \n",
       "2                 0.0                0.0            0.0            0.0  ...   \n",
       "3                 0.0                0.0            0.0            0.0  ...   \n",
       "4                 0.0                0.0            0.0            0.0  ...   \n",
       "\n",
       "   pink-sapphires  perimeter-diamonds  zoisite  blue-diamond  red-topaz  \\\n",
       "0             0.0                 0.0      0.0           0.0        0.0   \n",
       "1             0.0                 0.0      0.0           0.0        0.0   \n",
       "2             0.0                 0.0      0.0           0.0        0.0   \n",
       "3             0.0                 0.0      0.0           0.0        0.0   \n",
       "4             0.0                 0.0      0.0           0.0        0.0   \n",
       "\n",
       "   pink-quartz  quartz  tourmaline    i  mother-of-pearl  \n",
       "0          0.0     0.0         0.0  0.0              0.0  \n",
       "1          0.0     0.0         0.0  0.0              0.0  \n",
       "2          0.0     0.0         0.0  0.0              0.0  \n",
       "3          0.0     0.0         0.0  0.0              0.0  \n",
       "4          0.0     0.0         0.0  0.0              0.0  \n",
       "\n",
       "[5 rows x 110 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = final_df[final_df['brand'].isin(['Haritidis', 'Cartier'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>price</th>\n",
       "      <th>total diamond carats</th>\n",
       "      <th>gold carats</th>\n",
       "      <th>jewel_weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1966.000000</td>\n",
       "      <td>1966.000000</td>\n",
       "      <td>1966.000000</td>\n",
       "      <td>1966.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2191.866109</td>\n",
       "      <td>0.212391</td>\n",
       "      <td>16.599695</td>\n",
       "      <td>2.819672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>6417.237121</td>\n",
       "      <td>0.444998</td>\n",
       "      <td>1.914450</td>\n",
       "      <td>1.865009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>77.350000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>368.900000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>1.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>873.460000</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>2.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1676.500000</td>\n",
       "      <td>0.270000</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>3.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>85500.000000</td>\n",
       "      <td>7.160000</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              price  total diamond carats  gold carats  jewel_weight\n",
       "count   1966.000000           1966.000000  1966.000000   1966.000000\n",
       "mean    2191.866109              0.212391    16.599695      2.819672\n",
       "std     6417.237121              0.444998     1.914450      1.865009\n",
       "min       77.350000              0.000000     9.000000      0.000000\n",
       "25%      368.900000              0.000000    14.000000      1.700000\n",
       "50%      873.460000              0.100000    18.000000      2.500000\n",
       "75%     1676.500000              0.270000    18.000000      3.600000\n",
       "max    85500.000000              7.160000    18.000000     15.000000"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df[['price', 'brand', 'color', 'total diamond carats', 'gold carats', 'jewel_weight']].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([18., 14.,  9.])"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df['gold carats'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "brand\n",
       "Cartier      11771.462264\n",
       "Haritidis     1034.012982\n",
       "Name: price, dtype: float64"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.groupby('brand')['price'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Haritidis    1754\n",
       "Cartier       212\n",
       "Name: brand, dtype: int64"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df['brand'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dummy Variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>price</th>\n",
       "      <th>total diamond carats</th>\n",
       "      <th>gold carats</th>\n",
       "      <th>jewel_weight</th>\n",
       "      <th>brilliant-diamonds</th>\n",
       "      <th>brilliant-diamond</th>\n",
       "      <th>black-ceramic</th>\n",
       "      <th>rose-sapphire</th>\n",
       "      <th>blue-sapphire</th>\n",
       "      <th>yellow-sapphire</th>\n",
       "      <th>...</th>\n",
       "      <th>brand_Cartier</th>\n",
       "      <th>brand_Haritidis</th>\n",
       "      <th>color_Black</th>\n",
       "      <th>color_Rose</th>\n",
       "      <th>color_White</th>\n",
       "      <th>color_White &amp; Rose</th>\n",
       "      <th>color_White &amp; Yellow</th>\n",
       "      <th>color_White &amp; Yellow &amp; Rose</th>\n",
       "      <th>color_White, Yellow &amp; Rose</th>\n",
       "      <th>color_Yellow</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1650.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1650.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1770.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1070.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1070.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 118 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    price  total diamond carats  gold carats  jewel_weight  \\\n",
       "0  1650.0                   0.0         18.0           0.0   \n",
       "1  1650.0                   0.0         18.0           0.0   \n",
       "2  1770.0                   0.0         18.0           0.0   \n",
       "3  1070.0                   0.0         18.0           0.0   \n",
       "4  1070.0                   0.0         18.0           0.0   \n",
       "\n",
       "   brilliant-diamonds  brilliant-diamond  black-ceramic  rose-sapphire  \\\n",
       "0                 0.0                0.0            0.0            0.0   \n",
       "1                 0.0                0.0            0.0            0.0   \n",
       "2                 0.0                0.0            0.0            0.0   \n",
       "3                 0.0                0.0            0.0            0.0   \n",
       "4                 0.0                0.0            0.0            0.0   \n",
       "\n",
       "   blue-sapphire  yellow-sapphire  ...  brand_Cartier  brand_Haritidis  \\\n",
       "0            0.0              0.0  ...              1                0   \n",
       "1            0.0              0.0  ...              1                0   \n",
       "2            0.0              0.0  ...              1                0   \n",
       "3            0.0              0.0  ...              1                0   \n",
       "4            0.0              0.0  ...              1                0   \n",
       "\n",
       "   color_Black  color_Rose  color_White  color_White & Rose  \\\n",
       "0            0           0            0                   0   \n",
       "1            0           1            0                   0   \n",
       "2            0           0            1                   0   \n",
       "3            0           0            0                   0   \n",
       "4            0           1            0                   0   \n",
       "\n",
       "   color_White & Yellow  color_White & Yellow & Rose  \\\n",
       "0                     0                            0   \n",
       "1                     0                            0   \n",
       "2                     0                            0   \n",
       "3                     0                            0   \n",
       "4                     0                            0   \n",
       "\n",
       "   color_White, Yellow & Rose  color_Yellow  \n",
       "0                           0             1  \n",
       "1                           0             0  \n",
       "2                           0             0  \n",
       "3                           0             1  \n",
       "4                           0             0  \n",
       "\n",
       "[5 rows x 118 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df_w_dummies = pd.get_dummies(final_df)\n",
    "final_df_w_dummies.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df_w_dummies.loc[final_df_w_dummies['color_White & Rose'] == 1, ['color_White','color_Rose']] = 1\n",
    "final_df_w_dummies.loc[final_df_w_dummies['color_White & Yellow'] == 1, ['color_White','color_Yellow']] = 1\n",
    "final_df_w_dummies.loc[final_df_w_dummies['color_White, Yellow & Rose'] == 1, ['color_White','color_Rose','color_Yellow']] = 1\n",
    "final_df_w_dummies.loc[final_df_w_dummies['color_White & Yellow & Rose'] == 1, ['color_White','color_Rose','color_Yellow']] = 1\n",
    "\n",
    "final_df_w_dummies.drop(['color_White & Rose', 'color_White & Yellow', 'color_White, Yellow & Rose',\n",
    "                           'color_White & Yellow & Rose', 'brand_Cartier'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['price',\n",
       " 'total diamond carats',\n",
       " 'gold carats',\n",
       " 'jewel_weight',\n",
       " 'brilliant-diamonds',\n",
       " 'brilliant-diamond',\n",
       " 'black-ceramic',\n",
       " 'rose-sapphire',\n",
       " 'blue-sapphire',\n",
       " 'yellow-sapphire',\n",
       " 'green-garnet',\n",
       " 'orange-garnet',\n",
       " 'amethyst',\n",
       " 'emeralds',\n",
       " 'onyx',\n",
       " 'black-lacquer',\n",
       " 'tsavorite-garnets',\n",
       " 'tsavorite-garnet',\n",
       " 'peridots',\n",
       " 'diamonds',\n",
       " 'gray-mother-of-pearl',\n",
       " 'sapphire',\n",
       " 'emerald',\n",
       " 'spinels',\n",
       " 'carnelians',\n",
       " 'chrysoprases',\n",
       " 'lapis-lazulis',\n",
       " 'diamond',\n",
       " 'princess-diamond',\n",
       " 'brilliant-pavé-diamonds',\n",
       " 'troidia-diamond',\n",
       " 'baguette-diamonds',\n",
       " 'princess-diamonds',\n",
       " 'troidia-diamonds',\n",
       " 'rhodium-finish',\n",
       " 'pear-shaped-diamonds',\n",
       " 'amazonite',\n",
       " 'coral',\n",
       " 'recrystallized-emerald',\n",
       " 'topaz',\n",
       " 'white-topaz',\n",
       " 'tanzanite',\n",
       " 'london-blue-topaz',\n",
       " 'morganite',\n",
       " 'pearl',\n",
       " 'ruby',\n",
       " 'white-sapphire',\n",
       " 'blue-topaz',\n",
       " 'citrine',\n",
       " 'f',\n",
       " 'vs2',\n",
       " 'qendrad',\n",
       " 'e',\n",
       " 'g',\n",
       " 'vvs1',\n",
       " 'triangle-diamonds',\n",
       " 'spinel',\n",
       " 'blue-pink-sapphires',\n",
       " 'blue-sapphires',\n",
       " 'black-diamond',\n",
       " 'white-diamonds',\n",
       " 'pink-sapphire',\n",
       " 'opal',\n",
       " 'aquamarine',\n",
       " 'sapphires',\n",
       " 'baguette-brilliant-diamonds',\n",
       " 'green-sapphire',\n",
       " 'marquise-brilliant-diamonds',\n",
       " 'rodolite',\n",
       " 'coffee-diamonds',\n",
       " 'd',\n",
       " 'vvs2',\n",
       " 'akoya-pearl',\n",
       " 'oval-diamond',\n",
       " 'south-sea-pearl',\n",
       " 'baguette-brilliant-sapphires',\n",
       " 'baguette-brilliant-emerald',\n",
       " 'baguette-diamond',\n",
       " 'si',\n",
       " 'vs1',\n",
       " 'black-diamonds',\n",
       " 'black-sapphire',\n",
       " 'edison-fresh-water-pearl',\n",
       " 'fresh-water-pearl',\n",
       " 'edison-pearl',\n",
       " 'coffee-diamond',\n",
       " 'white-diamond',\n",
       " 'rainforest-topaz',\n",
       " 'violac-topaz',\n",
       " 'blazing-red-topaz',\n",
       " 'paraiba-topaz',\n",
       " 'aqua-blue-topaz',\n",
       " 'baby-pink-topaz',\n",
       " 'kashmir-blue-topaz',\n",
       " 'pearls',\n",
       " 'green-topaz',\n",
       " 'blue-diamonds',\n",
       " 'green-sapphires',\n",
       " 'pink-sapphires',\n",
       " 'perimeter-diamonds',\n",
       " 'zoisite',\n",
       " 'blue-diamond',\n",
       " 'red-topaz',\n",
       " 'pink-quartz',\n",
       " 'quartz',\n",
       " 'tourmaline',\n",
       " 'i',\n",
       " 'mother-of-pearl',\n",
       " 'brand_Haritidis',\n",
       " 'color_Black',\n",
       " 'color_Rose',\n",
       " 'color_White',\n",
       " 'color_Yellow']"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(final_df_w_dummies.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Model Creation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.linear_model import Ridge\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "\n",
    "from sklearn.metrics import mean_squared_error, mean_absolute_error, r2_score\n",
    "\n",
    "def scorers(target,pred):\n",
    "    print('MSE:', mean_squared_error(target,pred))\n",
    "    print('MAE:', mean_absolute_error(target,pred))\n",
    "    print('R^2:', r2_score(target,pred))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Oversampling for models"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "y = final_df_w_dummies['price']\n",
    "X = final_df_w_dummies.drop('price',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.20, random_state = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "oversampling_count = X_train.groupby('brand_Haritidis')['total diamond carats'].count()[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "underrep_idxs = X_train[X_train['brand_Haritidis'] == 0].index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "idx_list = []\n",
    "for i in range(oversampling_count):\n",
    "    idx_list.append(rand.choice(underrep_idxs))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "cartier_rows_X = X_train.loc[idx_list]\n",
    "haritidis_rows_X = X_train[X_train['brand_Haritidis'] == 1] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "cartier_rows_y = y_train.loc[idx_list]\n",
    "haritidis_rows_y = y_train[X_train['brand_Haritidis'] == 1] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "oversampled_df_X = pd.concat([cartier_rows_X,haritidis_rows_X], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "oversampled_df_y = pd.concat([cartier_rows_y,haritidis_rows_y], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1410\n",
       "1    1410\n",
       "Name: brand_Haritidis, dtype: int64"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "oversampled_df_X['brand_Haritidis'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    344\n",
       "0     50\n",
       "Name: brand_Haritidis, dtype: int64"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X_test['brand_Haritidis'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Previously trained models with cross validation:\n",
    "#### Same hyperparameters are used in final model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "# clf_ridge = Ridge(random_state=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "# clf_rfr = RandomForestRegressor(random_state=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "# from sklearn.model_selection import GridSearchCV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "# alphas = np.array([1,0.1,0.01,0.001,0.0001,0])\n",
    "\n",
    "# grid_ridge = GridSearchCV(estimator=clf_ridge, param_grid=dict(alpha=alphas),\n",
    "#                           verbose=2, n_jobs=-1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "# params = {'bootstrap': [True, False],\n",
    "#          'max_depth': [10, 20, 30, 40, 50, 60, 70, 80, 90, 100, None],\n",
    "#          'max_features': ['auto', 'sqrt'],\n",
    "#          'min_samples_leaf': [1, 2, 4],\n",
    "#          'min_samples_split': [2, 5, 10],\n",
    "#          'n_estimators': [200, 400, 600, 800, 1000, 1200, 1400, 1600, 1800, 2000]}\n",
    "\n",
    "# grid_rfr = GridSearchCV(estimator=clf_rfr, param_grid=params,\n",
    "#                         verbose=2, n_jobs=-1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "# grid_ridge.fit(oversampled_df_X, oversampled_df_y);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "# print(grid_ridge.best_score_)\n",
    "# print(grid_ridge.best_estimator_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "# grid_rfr.fit(oversampled_df_X, oversampled_df_y);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "# print(grid_rfr.best_score_)\n",
    "# print(grid_rfr.best_estimator_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "# y_pred_ridge = grid_ridge.predict(X_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "# y_pred_rfr = grid_rfr.predict(X_test)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Results from previous training:\n",
    "\n",
    "#### Ridge:\n",
    "##### R^2\n",
    "0.8565809888502182 \n",
    "<br>\n",
    "##### Best parameters\n",
    "Ridge(alpha=0.1, copy_X=True, fit_intercept=True, max_iter=None,\n",
    "      normalize=False, random_state=0, solver='auto', tol=0.001)\n",
    "\n",
    "#### Random Forest:\n",
    "##### R^2\n",
    "0.9889098573710595\n",
    "<br>\n",
    "##### Best parameters\n",
    "RandomForestRegressor(bootstrap=False, criterion='mse', max_depth=30,\n",
    "                      max_features='sqrt', max_leaf_nodes=None,\n",
    "                      min_impurity_decrease=0.0, min_impurity_split=None,\n",
    "                      min_samples_leaf=1, min_samples_split=5,\n",
    "                      min_weight_fraction_leaf=0.0, n_estimators=200,\n",
    "                      n_jobs=None, oob_score=False, random_state=0, verbose=0,\n",
    "                      warm_start=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "clf_ridge = Ridge(alpha=0.1, copy_X=True, fit_intercept=True, max_iter=None, normalize=False, random_state=0,\n",
    "                  solver='auto', tol=0.001)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "clf_rfr = RandomForestRegressor(bootstrap=False, criterion='mse', max_depth=30, max_features='sqrt', max_leaf_nodes=None,\n",
    "                                min_impurity_decrease=0.0, min_samples_leaf=1, min_samples_split=5,\n",
    "                                min_weight_fraction_leaf=0.0, n_estimators=200, n_jobs=None, oob_score=False, random_state=0,\n",
    "                                verbose=0, warm_start=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "clf_ridge.fit(oversampled_df_X, oversampled_df_y);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "clf_rfr.fit(oversampled_df_X, oversampled_df_y);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "y_pred_ridge = clf_ridge.predict(X_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "y_pred_rfr = clf_rfr.predict(X_test)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MSE: 15492538.616741594\n",
      "MAE: 1574.0272423054141\n",
      "R^2: 0.685374810171129\n"
     ]
    }
   ],
   "source": [
    "scorers(y_test, y_pred_ridge)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MSE: 7778246.469094671\n",
      "MAE: 736.6735262542561\n",
      "R^2: 0.8420380073004872\n"
     ]
    }
   ],
   "source": [
    "scorers(y_test, y_pred_rfr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exporting Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "import joblib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "joblib.dump(clf_ridge, 'ridge_model.pkl', compress=9)\n",
    "joblib.dump(clf_rfr, 'rfr_model.pkl', compress=9);"
   ]
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}