Untitled.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = sqlite3.connect(\"shop/test.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "addproduct=pd.read_sql_query(\"SELECT * from addproduct\", con)\n",
    "brand=pd.read_sql_query(\"SELECT * from brand\", con)\n",
    "events=pd.read_sql_query(\"SELECT * from events\", con)\n",
    "specsvalues=pd.read_sql_query(\"SELECT * from specsvalues\", con)\n",
    "category=pd.read_sql_query(\"SELECT * from category\", con)\n",
    "subcategories=pd.read_sql_query(\"SELECT * from subcategories\", con)\n",
    "specs=pd.read_sql_query(\"SELECT * from specs\", con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Men's Clothing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Women's Clothing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Kids</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id              name\n",
       "0   1    Men's Clothing\n",
       "1   2  Women's Clothing\n",
       "2   3              Kids"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "category.head()"
   ]
  },
  {
   "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>id</th>\n",
       "      <th>category_id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Shirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Jeans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Watch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>Wallet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>Shoes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  category_id    name\n",
       "0   2            1   Shirt\n",
       "1   3            1   Jeans\n",
       "2   4            1   Watch\n",
       "3   5            1  Wallet\n",
       "4   6            1   Shoes"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subcategories.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_subcat=pd.merge(category,subcategories,how='left',left_on='id',right_on='category_id')"
   ]
  },
  {
   "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>category_id</th>\n",
       "      <th>category</th>\n",
       "      <th>subcategory_id</th>\n",
       "      <th>subcategory</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Men's Clothing</td>\n",
       "      <td>2</td>\n",
       "      <td>Shirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Men's Clothing</td>\n",
       "      <td>3</td>\n",
       "      <td>Jeans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Men's Clothing</td>\n",
       "      <td>4</td>\n",
       "      <td>Watch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Men's Clothing</td>\n",
       "      <td>5</td>\n",
       "      <td>Wallet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Men's Clothing</td>\n",
       "      <td>6</td>\n",
       "      <td>Shoes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>Men's Clothing</td>\n",
       "      <td>24</td>\n",
       "      <td>T-shirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>Women's Clothing</td>\n",
       "      <td>7</td>\n",
       "      <td>Tops</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>Women's Clothing</td>\n",
       "      <td>9</td>\n",
       "      <td>Jeans &amp; Jeggings</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>Women's Clothing</td>\n",
       "      <td>10</td>\n",
       "      <td>Ethnic Dresses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>Women's Clothing</td>\n",
       "      <td>11</td>\n",
       "      <td>Fashion Accessories</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   category_id          category  subcategory_id          subcategory\n",
       "0            1    Men's Clothing               2                Shirt\n",
       "1            1    Men's Clothing               3                Jeans\n",
       "2            1    Men's Clothing               4                Watch\n",
       "3            1    Men's Clothing               5               Wallet\n",
       "4            1    Men's Clothing               6                Shoes\n",
       "5            1    Men's Clothing              24              T-shirt\n",
       "6            2  Women's Clothing               7                 Tops\n",
       "7            2  Women's Clothing               9     Jeans & Jeggings\n",
       "8            2  Women's Clothing              10       Ethnic Dresses\n",
       "9            2  Women's Clothing              11  Fashion Accessories"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cat_subcat=cat_subcat[['category_id','name_x','id_y','name_y']]\n",
    "cat_subcat.columns=['category_id','category','subcategory_id','subcategory']\n",
    "cat_subcat.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>price</th>\n",
       "      <th>discount</th>\n",
       "      <th>stock</th>\n",
       "      <th>colors</th>\n",
       "      <th>desc</th>\n",
       "      <th>pub_date</th>\n",
       "      <th>category_id</th>\n",
       "      <th>subcategory_id</th>\n",
       "      <th>brand_id</th>\n",
       "      <th>category</th>\n",
       "      <th>subcategory</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Allen Solly Men's Plain Slim Fit Casual Shirt</td>\n",
       "      <td>699</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>Blue, Purple, Black</td>\n",
       "      <td>Allen solly Mens Cotton Full sleeve Overdyed s...</td>\n",
       "      <td>2020-07-08 15:54:29.990947</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>Men's Clothing</td>\n",
       "      <td>Shirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Park Avenue Men's Solid Slim Fit Formal Shirt</td>\n",
       "      <td>563</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>White, Blue, Khaki</td>\n",
       "      <td>This classic shirt from Park Avenue is a versa...</td>\n",
       "      <td>2020-07-08 15:57:00.755305</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>Men's Clothing</td>\n",
       "      <td>Shirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Park Avenue Men's Plain Slim Fit Formal Shirt</td>\n",
       "      <td>654</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>White, Blue, Black</td>\n",
       "      <td>Be noticed at work by enhancing your looks wit...</td>\n",
       "      <td>2020-07-08 15:58:57.811980</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>Men's Clothing</td>\n",
       "      <td>Shirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>Wrangler Men's Relaxed Fit Jeans</td>\n",
       "      <td>1198</td>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>Blue, Black</td>\n",
       "      <td>Wrangler is the everyday performance denim bra...</td>\n",
       "      <td>2020-07-08 16:02:12.713819</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>Men's Clothing</td>\n",
       "      <td>Jeans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Casio G-Shock Analog-Digital Blue Dial Men's W...</td>\n",
       "      <td>6396</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>Blue, Black</td>\n",
       "      <td>Mineral Glass Magnetic Resistant Shock Resista...</td>\n",
       "      <td>2020-07-08 16:05:11.097813</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>Men's Clothing</td>\n",
       "      <td>Watch</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                               name  price  discount  \\\n",
       "0   1      Allen Solly Men's Plain Slim Fit Casual Shirt    699         0   \n",
       "1   2      Park Avenue Men's Solid Slim Fit Formal Shirt    563         0   \n",
       "2   3      Park Avenue Men's Plain Slim Fit Formal Shirt    654         0   \n",
       "3   4                   Wrangler Men's Relaxed Fit Jeans   1198         5   \n",
       "4   5  Casio G-Shock Analog-Digital Blue Dial Men's W...   6396         0   \n",
       "\n",
       "   stock               colors  \\\n",
       "0     25  Blue, Purple, Black   \n",
       "1     15   White, Blue, Khaki   \n",
       "2     15   White, Blue, Black   \n",
       "3     25          Blue, Black   \n",
       "4     25          Blue, Black   \n",
       "\n",
       "                                                desc  \\\n",
       "0  Allen solly Mens Cotton Full sleeve Overdyed s...   \n",
       "1  This classic shirt from Park Avenue is a versa...   \n",
       "2  Be noticed at work by enhancing your looks wit...   \n",
       "3  Wrangler is the everyday performance denim bra...   \n",
       "4  Mineral Glass Magnetic Resistant Shock Resista...   \n",
       "\n",
       "                     pub_date  category_id  subcategory_id  brand_id  \\\n",
       "0  2020-07-08 15:54:29.990947            1               2         8   \n",
       "1  2020-07-08 15:57:00.755305            1               2        11   \n",
       "2  2020-07-08 15:58:57.811980            1               2        11   \n",
       "3  2020-07-08 16:02:12.713819            1               3        12   \n",
       "4  2020-07-08 16:05:11.097813            1               4        13   \n",
       "\n",
       "         category subcategory  \n",
       "0  Men's Clothing       Shirt  \n",
       "1  Men's Clothing       Shirt  \n",
       "2  Men's Clothing       Shirt  \n",
       "3  Men's Clothing       Jeans  \n",
       "4  Men's Clothing       Watch  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product=pd.merge(addproduct,cat_subcat[['category','subcategory_id','subcategory']],how='left',on='subcategory_id')\n",
    "product=product.drop(['image_1','image_2','image_3'],axis='columns')\n",
    "product.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>id_x</th>\n",
       "      <th>category_id_x</th>\n",
       "      <th>subcategory_id_x</th>\n",
       "      <th>name</th>\n",
       "      <th>id_y</th>\n",
       "      <th>product_id</th>\n",
       "      <th>category_id_y</th>\n",
       "      <th>subcategory_id_y</th>\n",
       "      <th>spec_id</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>Fit : Regular Fit</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>Fit : Regular Fit</td>\n",
       "      <td>3</td>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>Fit : Regular Fit</td>\n",
       "      <td>9</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>Fit : Regular Fit</td>\n",
       "      <td>15</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>Fit : Regular Fit</td>\n",
       "      <td>84</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>191</td>\n",
       "      <td>112</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>Letter Sounds</td>\n",
       "      <td>210</td>\n",
       "      <td>30</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>112</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>192</td>\n",
       "      <td>113</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>Spelling</td>\n",
       "      <td>207</td>\n",
       "      <td>29</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>113</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>193</td>\n",
       "      <td>113</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>Spelling</td>\n",
       "      <td>211</td>\n",
       "      <td>30</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>113</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>194</td>\n",
       "      <td>114</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>Reading Skills</td>\n",
       "      <td>208</td>\n",
       "      <td>29</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>114</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>195</td>\n",
       "      <td>114</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>Reading Skills</td>\n",
       "      <td>212</td>\n",
       "      <td>30</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>114</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>196 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     id_x  category_id_x subcategory_id_x               name  id_y  \\\n",
       "0       1              1             None  Fit : Regular Fit     1   \n",
       "1       1              1             None  Fit : Regular Fit     3   \n",
       "2       1              1             None  Fit : Regular Fit     9   \n",
       "3       1              1             None  Fit : Regular Fit    15   \n",
       "4       1              1             None  Fit : Regular Fit    84   \n",
       "..    ...            ...              ...                ...   ...   \n",
       "191   112              3             None      Letter Sounds   210   \n",
       "192   113              3             None           Spelling   207   \n",
       "193   113              3             None           Spelling   211   \n",
       "194   114              3             None     Reading Skills   208   \n",
       "195   114              3             None     Reading Skills   212   \n",
       "\n",
       "     product_id  category_id_y subcategory_id_y  spec_id value  \n",
       "0             1              1             None        1  True  \n",
       "1            31              1             None        1  True  \n",
       "2            32              1             None        1  True  \n",
       "3            33              1             None        1  True  \n",
       "4            10              1             None        1  True  \n",
       "..          ...            ...              ...      ...   ...  \n",
       "191          30              3             None      112  True  \n",
       "192          29              3             None      113  True  \n",
       "193          30              3             None      113  True  \n",
       "194          29              3             None      114  True  \n",
       "195          30              3             None      114  True  \n",
       "\n",
       "[196 rows x 10 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "specifications=pd.merge(specs,specsvalues,left_on='id',right_on='spec_id')\n",
    "specifications"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "specifications=specifications.drop(['subcategory_id_x','subcategory_id_y','category_id_y','category_id_x','value','id_x','id_y'],axis='columns')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>product_id</th>\n",
       "      <th>spec_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Fit : Regular Fit</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Fit : Regular Fit</td>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Fit : Regular Fit</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Fit : Regular Fit</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Fit : Regular Fit</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                name  product_id  spec_id\n",
       "0  Fit : Regular Fit           1        1\n",
       "1  Fit : Regular Fit          31        1\n",
       "2  Fit : Regular Fit          32        1\n",
       "3  Fit : Regular Fit          33        1\n",
       "4  Fit : Regular Fit          10        1"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "specifications.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>spec_id</th>\n",
       "      <th>specs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Fit : Regular Fit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "      <td>Fit : Regular Fit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>Fit : Regular Fit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>Fit : Regular Fit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>Fit : Regular Fit</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  spec_id              specs\n",
       "0   1        1  Fit : Regular Fit\n",
       "1  31        1  Fit : Regular Fit\n",
       "2  32        1  Fit : Regular Fit\n",
       "3  33        1  Fit : Regular Fit\n",
       "4  10        1  Fit : Regular Fit"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "specifications=specifications[['product_id','spec_id','name']]\n",
    "specifications.columns=['id','spec_id','specs']\n",
    "specifications.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>price</th>\n",
       "      <th>discount</th>\n",
       "      <th>stock</th>\n",
       "      <th>colors</th>\n",
       "      <th>desc</th>\n",
       "      <th>pub_date</th>\n",
       "      <th>category_id</th>\n",
       "      <th>subcategory_id</th>\n",
       "      <th>brand_id</th>\n",
       "      <th>category</th>\n",
       "      <th>subcategory</th>\n",
       "      <th>spec_id</th>\n",
       "      <th>specs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Allen Solly Men's Plain Slim Fit Casual Shirt</td>\n",
       "      <td>699</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>Blue, Purple, Black</td>\n",
       "      <td>Allen solly Mens Cotton Full sleeve Overdyed s...</td>\n",
       "      <td>2020-07-08 15:54:29.990947</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>Men's Clothing</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>1</td>\n",
       "      <td>Fit : Regular Fit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Allen Solly Men's Plain Slim Fit Casual Shirt</td>\n",
       "      <td>699</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>Blue, Purple, Black</td>\n",
       "      <td>Allen solly Mens Cotton Full sleeve Overdyed s...</td>\n",
       "      <td>2020-07-08 15:54:29.990947</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>Men's Clothing</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>2</td>\n",
       "      <td>Sleeve Length : Long Sleeves</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Park Avenue Men's Solid Slim Fit Formal Shirt</td>\n",
       "      <td>563</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>White, Blue, Khaki</td>\n",
       "      <td>This classic shirt from Park Avenue is a versa...</td>\n",
       "      <td>2020-07-08 15:57:00.755305</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>Men's Clothing</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>2</td>\n",
       "      <td>Sleeve Length : Long Sleeves</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>Park Avenue Men's Solid Slim Fit Formal Shirt</td>\n",
       "      <td>563</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>White, Blue, Khaki</td>\n",
       "      <td>This classic shirt from Park Avenue is a versa...</td>\n",
       "      <td>2020-07-08 15:57:00.755305</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>Men's Clothing</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>3</td>\n",
       "      <td>Material : Cotton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>Park Avenue Men's Solid Slim Fit Formal Shirt</td>\n",
       "      <td>563</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>White, Blue, Khaki</td>\n",
       "      <td>This classic shirt from Park Avenue is a versa...</td>\n",
       "      <td>2020-07-08 15:57:00.755305</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>Men's Clothing</td>\n",
       "      <td>Shirt</td>\n",
       "      <td>4</td>\n",
       "      <td>Collar : Button-Down Collar</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                           name  price  discount  stock  \\\n",
       "0   1  Allen Solly Men's Plain Slim Fit Casual Shirt    699         0     25   \n",
       "1   1  Allen Solly Men's Plain Slim Fit Casual Shirt    699         0     25   \n",
       "2   2  Park Avenue Men's Solid Slim Fit Formal Shirt    563         0     15   \n",
       "3   2  Park Avenue Men's Solid Slim Fit Formal Shirt    563         0     15   \n",
       "4   2  Park Avenue Men's Solid Slim Fit Formal Shirt    563         0     15   \n",
       "\n",
       "                colors                                               desc  \\\n",
       "0  Blue, Purple, Black  Allen solly Mens Cotton Full sleeve Overdyed s...   \n",
       "1  Blue, Purple, Black  Allen solly Mens Cotton Full sleeve Overdyed s...   \n",
       "2   White, Blue, Khaki  This classic shirt from Park Avenue is a versa...   \n",
       "3   White, Blue, Khaki  This classic shirt from Park Avenue is a versa...   \n",
       "4   White, Blue, Khaki  This classic shirt from Park Avenue is a versa...   \n",
       "\n",
       "                     pub_date  category_id  subcategory_id  brand_id  \\\n",
       "0  2020-07-08 15:54:29.990947            1               2         8   \n",
       "1  2020-07-08 15:54:29.990947            1               2         8   \n",
       "2  2020-07-08 15:57:00.755305            1               2        11   \n",
       "3  2020-07-08 15:57:00.755305            1               2        11   \n",
       "4  2020-07-08 15:57:00.755305            1               2        11   \n",
       "\n",
       "         category subcategory  spec_id                         specs  \n",
       "0  Men's Clothing       Shirt        1             Fit : Regular Fit  \n",
       "1  Men's Clothing       Shirt        2  Sleeve Length : Long Sleeves  \n",
       "2  Men's Clothing       Shirt        2  Sleeve Length : Long Sleeves  \n",
       "3  Men's Clothing       Shirt        3             Material : Cotton  \n",
       "4  Men's Clothing       Shirt        4   Collar : Button-Down Collar  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product=pd.merge(product,specifications,how='left',on='id')\n",
    "product.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "spec_list=product.groupby('id')['specs'].apply(list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Fit : Regular Fit|Sleeve Length : Long Sleeves',\n",
       " 'Sleeve Length : Long Sleeves|Material : Cotton|Collar : Button-Down Collar|Pattern : Solid|Occasion : Formal|Fit : Slim Fit|Collar : Mandarin Collar',\n",
       " 'Sleeve Length : Long Sleeves|Material : Cotton|Pattern : Solid|Occasion : Formal|Fit : Slim Fit|Collar : Ainsley Collar',\n",
       " 'Distress: Clean Look|Fade: Heavy Fade|Fit: Slim Fit|Waist Rise: Mid-Rise|Length: Regular|Strech: Strechable',\n",
       " 'Display: Analogue and Digital|Movement : Automatic|Type: Hybrid|Features: Multi Function|Dial Shape: Round|Dial Pattern: Round',\n",
       " 'Display: Analogue and Digital|Movement : Automatic|Type: Hybrid|Features: Multi Function|Dial Shape: Round|Dial Pattern: Round',\n",
       " 'Length: Short|Number of Compartments: 2|Number of Slip Pockets: 1|Material: Leather|Number of Card Holder: 4',\n",
       " 'Toe Shape: Round Toe|Ankle Height: Mid Top|Warranty: 2 months|Pattern: Solid|Sole Material: TPR|Fastening: Lace Ups',\n",
       " 'Fastening: Lace Ups|Sport: Running|Outsole Type: Marking|Material: Mesh|Ankle Height: Regular',\n",
       " 'Fit : Regular Fit|Material : Cotton|Pattern : Solid|Occasion : Casual|Sleeves : Half Sleeves|Neck: Pollo Collar',\n",
       " 'Sleeves Length: Three-Quarter Sleeves|Type: Peplum|Transparency: Opaque|Print or Pattern Type: Self Design|Length:Regular',\n",
       " 'Sleeves Length: Three-Quarter Sleeves|Type: Peplum|Transparency: Opaque|Print or Pattern Type: Self Design|Length:Regular',\n",
       " 'Distress: Clean  Look|Fade: Light Fade|Fit: Skinny Fit|Waistbend: With Belt Loops|Waist: Low-Rise|Shade: Dark|Length: Cropped|Strech:  Strechable',\n",
       " 'Top Fabric:Net| Bottom Fabric: Santoon | Dupatta Fabric: Net| Inner Fabric: Santoon|Work: Embroidered|Sleeve: Full Sleeve|Neck: Round Neck|Style: Anarkali Gown with Dupatta and Bottom|Size: Free Size',\n",
       " 'Dial Color: Blue| Case Shape: Tonneau| Band Material: Patent Leather|Case Shape: Special|water-resistant for up to 3 ATM|Watch Movement Type: Quartz|Watch Display Type: Analog',\n",
       " ' Case Shape: Tonneau| Band Material: Patent Leather|Case Shape: Special|water-resistant for up to 3 ATM|Watch Movement Type: Quartz|Watch Display Type: Analog',\n",
       " 'Sport:  Running|Fastening: Lace-Ups|Outsole Type: Non-Marking|Pronation for Running Shoes: Neutral|Material:  Mesh|Ankle Height:  Regular|Cleats: No Cleats|Arch Type: Medium',\n",
       " 'Sleeve Length: Long Sleeves|Fit: Regular Fit|Occasion: Casual|Hemline: Curved|Collar: Spread Collar|Print or Pattern Type: Solid|Length:  Regular',\n",
       " 'Sleeve Length: Long Sleeves|Fit: Regular Fit|Occasion: Casual|Hemline: Curved|Collar: Spread Collar|Print or Pattern Type: Solid|Length:  Regular',\n",
       " 'Length:  Regular|Distress:  Clean Look|Fade:  Light Fade|Fit:  Regular Fit|Waistband:  With belt loops|Shade:  Medium|Stretch:  Stretchable',\n",
       " 'Distress:  Clean Look|Fade:  Light Fade|Fit:  Regular Fit|Waistband:  With belt loops|Shade:  Medium|Stretch:  Stretchable',\n",
       " 'Sleeve Length: No Sleeves|Type: Regular|Lining: Has a lining|Neck: Shoulder Straps|Print or Pattern Type:  Polka Dots|Transparency:  Opaque',\n",
       " 'Sleeve Length: No Sleeves|Type: Regular|Lining: Has a lining|Neck: Shoulder Straps|Print or Pattern Type:  Polka Dots|Transparency:  Opaque',\n",
       " 'Distress:  Clean Look|Fade:  Light Fade|Fit:  Regular Fit|Waistband:  With belt loops|Shade:  Medium|Stretch:  Stretchable',\n",
       " 'Type: Flatforms|Pattern: Printed|Shoe Width: Regular|Insole: Comfort Insole|Toe Shape:  Round Toe|Ankle Height:   Regular|Sole Material:  TPR',\n",
       " 'Type: Flatforms|Shoe Width: Regular|Insole: Comfort Insole|Toe Shape:  Round Toe|Ankle Height:   Regular|Sole Material:  TPR',\n",
       " 'Type: Flatforms|Shoe Width: Regular|Insole: Comfort Insole|Toe Shape:  Round Toe|Ankle Height:   Regular|Sole Material:  TPR',\n",
       " 'Type: Flatforms|Shoe Width: Regular|Insole: Comfort Insole|Toe Shape:  Round Toe|Ankle Height:   Regular|Sole Material:  TPR',\n",
       " 'Letter Recognition|Letter Sounds|Spelling|Reading Skills',\n",
       " 'Letter Recognition|Letter Sounds|Spelling|Reading Skills',\n",
       " 'Fit : Regular Fit|Sleeve Length : Long Sleeves|Material : Cotton|Collar : Spread Collar|Occasion : Casual|Pattern : Buffalo Checks',\n",
       " 'Fit : Regular Fit|Sleeve Length : Long Sleeves|Material : Cotton|Collar : Spread Collar|Pattern : Textured|Pattern : Tartan Checks',\n",
       " 'Fit : Regular Fit|Material : Cotton|Occasion : Casual|Neck : Round Neck|Pattern : Printed|Sleeves : Half Sleeves']"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spec_str=[]\n",
    "for i in spec_list:\n",
    "    spec_str.append('|'.join(map(str,i)))\n",
    "spec_str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        Fit : Regular Fit|Sleeve Length : Long Sleeves\n",
       "1     Sleeve Length : Long Sleeves|Material : Cotton...\n",
       "2     Sleeve Length : Long Sleeves|Material : Cotton...\n",
       "3     Distress: Clean Look|Fade: Heavy Fade|Fit: Sli...\n",
       "4     Display: Analogue and Digital|Movement : Autom...\n",
       "5     Display: Analogue and Digital|Movement : Autom...\n",
       "6     Length: Short|Number of Compartments: 2|Number...\n",
       "7     Toe Shape: Round Toe|Ankle Height: Mid Top|War...\n",
       "8     Fastening: Lace Ups|Sport: Running|Outsole Typ...\n",
       "9     Fit : Regular Fit|Material : Cotton|Pattern : ...\n",
       "10    Sleeves Length: Three-Quarter Sleeves|Type: Pe...\n",
       "11    Sleeves Length: Three-Quarter Sleeves|Type: Pe...\n",
       "12    Distress: Clean  Look|Fade: Light Fade|Fit: Sk...\n",
       "13    Top Fabric:Net| Bottom Fabric: Santoon | Dupat...\n",
       "14    Dial Color: Blue| Case Shape: Tonneau| Band Ma...\n",
       "15     Case Shape: Tonneau| Band Material: Patent Le...\n",
       "16    Sport:  Running|Fastening: Lace-Ups|Outsole Ty...\n",
       "17    Sleeve Length: Long Sleeves|Fit: Regular Fit|O...\n",
       "18    Sleeve Length: Long Sleeves|Fit: Regular Fit|O...\n",
       "19    Length:  Regular|Distress:  Clean Look|Fade:  ...\n",
       "20    Distress:  Clean Look|Fade:  Light Fade|Fit:  ...\n",
       "21    Sleeve Length: No Sleeves|Type: Regular|Lining...\n",
       "22    Sleeve Length: No Sleeves|Type: Regular|Lining...\n",
       "23    Distress:  Clean Look|Fade:  Light Fade|Fit:  ...\n",
       "24    Type: Flatforms|Pattern: Printed|Shoe Width: R...\n",
       "25    Type: Flatforms|Shoe Width: Regular|Insole: Co...\n",
       "26    Type: Flatforms|Shoe Width: Regular|Insole: Co...\n",
       "27    Type: Flatforms|Shoe Width: Regular|Insole: Co...\n",
       "28    Letter Recognition|Letter Sounds|Spelling|Read...\n",
       "29    Letter Recognition|Letter Sounds|Spelling|Read...\n",
       "30    Fit : Regular Fit|Sleeve Length : Long Sleeves...\n",
       "31    Fit : Regular Fit|Sleeve Length : Long Sleeves...\n",
       "32    Fit : Regular Fit|Material : Cotton|Occasion :...\n",
       "Name: specs, dtype: object"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "addproduct['specs']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id    \n",
       "1   0                 Fit\n",
       "    1                   :\n",
       "    2             Regular\n",
       "    3          Fit|Sleeve\n",
       "    4              Length\n",
       "               ...       \n",
       "33  11                  :\n",
       "    12    Printed|Sleeves\n",
       "    13                  :\n",
       "    14               Half\n",
       "    15            Sleeves\n",
       "Length: 513, dtype: object"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned = addproduct.set_index('id').specs.str.split(' ', expand=True).stack()\n",
    "cleaned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>price</th>\n",
       "      <th>discount</th>\n",
       "      <th>stock</th>\n",
       "      <th>colors</th>\n",
       "      <th>desc</th>\n",
       "      <th>pub_date</th>\n",
       "      <th>category_id</th>\n",
       "      <th>subcategory_id</th>\n",
       "      <th>brand_id</th>\n",
       "      <th>image_1</th>\n",
       "      <th>image_2</th>\n",
       "      <th>image_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Allen Solly Men's Plain Slim Fit Casual Shirt</td>\n",
       "      <td>699</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>Blue, Purple, Black</td>\n",
       "      <td>Allen solly Mens Cotton Full sleeve Overdyed s...</td>\n",
       "      <td>2020-07-08 15:54:29.990947</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>58a16e6bee54bb2cddbf.jpg</td>\n",
       "      <td>a983bf257c13c61f6600.jpg</td>\n",
       "      <td>d2bfc40b87bceda78823.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Park Avenue Men's Solid Slim Fit Formal Shirt</td>\n",
       "      <td>563</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>White, Blue, Khaki</td>\n",
       "      <td>This classic shirt from Park Avenue is a versa...</td>\n",
       "      <td>2020-07-08 15:57:00.755305</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>52e5a9ab0ec7bb30a1d8.jpg</td>\n",
       "      <td>6bc2786d351652afcfaf.jpg</td>\n",
       "      <td>b0fe79b775b77d9f15fb.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Park Avenue Men's Plain Slim Fit Formal Shirt</td>\n",
       "      <td>654</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>White, Blue, Black</td>\n",
       "      <td>Be noticed at work by enhancing your looks wit...</td>\n",
       "      <td>2020-07-08 15:58:57.811980</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>53a09ec68d5f5ab9627c.jpg</td>\n",
       "      <td>896324c13cde4988b3be.jpg</td>\n",
       "      <td>4537b9a61d8f4013fe9c.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>31</td>\n",
       "      <td>Time Travlr Men Maroon &amp; Black Regular Fit Che...</td>\n",
       "      <td>649</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>Red</td>\n",
       "      <td>Maroon and black checked casual shirt, has a s...</td>\n",
       "      <td>2020-07-09 16:36:52.039533</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>60484a12700fd4e28862.jpg</td>\n",
       "      <td>8aeef96d6a4331b41fe7.jpg</td>\n",
       "      <td>dfb132b6330e3a1d2517.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>32</td>\n",
       "      <td>Time Travlr Men Black &amp; Grey Checked Casual Shirt</td>\n",
       "      <td>649</td>\n",
       "      <td>0</td>\n",
       "      <td>150</td>\n",
       "      <td>Black</td>\n",
       "      <td>Black and grey checked casual shirt, has a spr...</td>\n",
       "      <td>2020-07-09 16:47:15.603108</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>3218c3582e825e5ec4f3.jpg</td>\n",
       "      <td>876efeac71732801e589.jpg</td>\n",
       "      <td>ca915e2ebc1f4e8bbfd8.jpg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id                                               name  price  discount  \\\n",
       "0    1      Allen Solly Men's Plain Slim Fit Casual Shirt    699         0   \n",
       "1    2      Park Avenue Men's Solid Slim Fit Formal Shirt    563         0   \n",
       "2    3      Park Avenue Men's Plain Slim Fit Formal Shirt    654         0   \n",
       "30  31  Time Travlr Men Maroon & Black Regular Fit Che...    649         0   \n",
       "31  32  Time Travlr Men Black & Grey Checked Casual Shirt    649         0   \n",
       "\n",
       "    stock               colors  \\\n",
       "0      25  Blue, Purple, Black   \n",
       "1      15   White, Blue, Khaki   \n",
       "2      15   White, Blue, Black   \n",
       "30    100                  Red   \n",
       "31    150                Black   \n",
       "\n",
       "                                                 desc  \\\n",
       "0   Allen solly Mens Cotton Full sleeve Overdyed s...   \n",
       "1   This classic shirt from Park Avenue is a versa...   \n",
       "2   Be noticed at work by enhancing your looks wit...   \n",
       "30  Maroon and black checked casual shirt, has a s...   \n",
       "31  Black and grey checked casual shirt, has a spr...   \n",
       "\n",
       "                      pub_date  category_id  subcategory_id  brand_id  \\\n",
       "0   2020-07-08 15:54:29.990947            1               2         8   \n",
       "1   2020-07-08 15:57:00.755305            1               2        11   \n",
       "2   2020-07-08 15:58:57.811980            1               2        11   \n",
       "30  2020-07-09 16:36:52.039533            1               2        11   \n",
       "31  2020-07-09 16:47:15.603108            1               2        11   \n",
       "\n",
       "                     image_1                   image_2  \\\n",
       "0   58a16e6bee54bb2cddbf.jpg  a983bf257c13c61f6600.jpg   \n",
       "1   52e5a9ab0ec7bb30a1d8.jpg  6bc2786d351652afcfaf.jpg   \n",
       "2   53a09ec68d5f5ab9627c.jpg  896324c13cde4988b3be.jpg   \n",
       "30  60484a12700fd4e28862.jpg  8aeef96d6a4331b41fe7.jpg   \n",
       "31  3218c3582e825e5ec4f3.jpg  876efeac71732801e589.jpg   \n",
       "\n",
       "                     image_3  \n",
       "0   d2bfc40b87bceda78823.jpg  \n",
       "1   b0fe79b775b77d9f15fb.jpg  \n",
       "2   4537b9a61d8f4013fe9c.jpg  \n",
       "30  dfb132b6330e3a1d2517.jpg  \n",
       "31  ca915e2ebc1f4e8bbfd8.jpg  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product_id=1\n",
    "subcategory=addproduct[addproduct['id']==product_id]['subcategory_id'][0]\n",
    "items=addproduct[addproduct['subcategory_id']==subcategory]\n",
    "items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'pd' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-1-04cab546c955>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     26\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0mitems\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdrop\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'product_id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0maxis\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'columns'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'id'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     27\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 28\u001b[1;33m \u001b[0mcontent_based_filtering\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     29\u001b[0m \u001b[0mcon\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<ipython-input-1-04cab546c955>\u001b[0m in \u001b[0;36mcontent_based_filtering\u001b[1;34m(product_id)\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0mcontent_based_filtering\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mproduct_id\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m     \u001b[0mcat_subcat\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmerge\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcategory\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0msubcategories\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mhow\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'left'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mleft_on\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mright_on\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'category_id'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m     \u001b[0mcat_subcat\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mcat_subcat\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'category_id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'name_x'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'id_y'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'name_y'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m     \u001b[0mcat_subcat\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'category_id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'category'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'subcategory_id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'subcategory'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m     \u001b[0mspecifications\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmerge\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mspecs\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'name'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mspecsvalues\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'spec_id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'product_id'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mleft_on\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mright_on\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'spec_id'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'pd' is not defined"
     ]
    }
   ],
   "source": [
    "def content_based_filtering(product_id):\n",
    "    cat_subcat=pd.merge(category,subcategories,how='left',left_on='id',right_on='category_id')\n",
    "    cat_subcat=cat_subcat[['category_id','name_x','id_y','name_y']]\n",
    "    cat_subcat.columns=['category_id','category','subcategory_id','subcategory']\n",
    "    specifications=pd.merge(specs[['id','name']],specsvalues[['spec_id','product_id']],left_on='id',right_on='spec_id')\n",
    "\n",
    "    subcategory_id=addproduct[addproduct['id']==product_id]['subcategory_id'][0]\n",
    "    items=addproduct[addproduct['subcategory_id']==subcategory_id]\n",
    "\n",
    "    if not subcategory:\n",
    "        category_id=addproduct[addproduct['id']==product_id]['category_id'][0]\n",
    "        items=addproduct[addproduct['category_id']==category_id]\n",
    "    product=pd.merge(items,cat_subcat[['category','subcategory_id','subcategory']],how='left',on='subcategory_id')\n",
    "    product=product.drop(['image_1','image_2','image_3'],axis='columns')\n",
    "    product=pd.merge(product,specifications,how='left',on='id')\n",
    "    spec_list=product.groupby('id')['name_y'].apply(list)\n",
    "    spec_str=[]\n",
    "    for i in spec_list:\n",
    "        spec_str.append('|'.join(map(str,i)))\n",
    "    spec_str\n",
    "    cleaned = items.set_index('id').specs.str.split('|', expand=True).stack()\n",
    "    spec=pd.get_dummies(cleaned).groupby(level=0).sum()\n",
    "    spec['product_id']=spec.index\n",
    "    items=pd.merge(items,spec,left_on=\"id\",right_on='product_id')\n",
    "    \n",
    "    return items.drop('product_id',axis='columns')['id']\n",
    "\n",
    "content_based_filtering(1)\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 1\n",
      "1 0\n",
      "1 0\n",
      "0 0\n",
      "0 0\n",
      "1 1\n",
      "1 1\n",
      "0 0\n",
      "1 1\n",
      "0 0\n",
      "1 1\n",
      "0 0\n",
      "0 0\n",
      "1 1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "5"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spec.iloc[0].dot(spec.iloc[3])\n",
    "for i in range(len(spec.iloc[0])):\n",
    "    print(spec.iloc[1][i],spec.iloc[2][i])\n",
    "spec.iloc[1].dot(spec.iloc[2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "spec=spec.drop('product_id',axis='columns')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_subcat=pd.merge(category,subcategories,how='left',left_on='id',right_on='category_id')\n",
    "cat_subcat=cat_subcat[['category_id','name_x','id_y','name_y']]\n",
    "cat_subcat.columns=['category_id','category','subcategory_id','subcategory']\n",
    "specifications=pd.merge(specs[['id','name']],specsvalues[['spec_id','product_id']],left_on='id',right_on='spec_id')\n",
    "\n",
    "subcategory_id=addproduct[addproduct['id']==product_id]['subcategory_id'][0]\n",
    "items=addproduct[addproduct['subcategory_id']==subcategory_id]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>price</th>\n",
       "      <th>discount</th>\n",
       "      <th>stock</th>\n",
       "      <th>colors</th>\n",
       "      <th>desc</th>\n",
       "      <th>pub_date</th>\n",
       "      <th>category_id</th>\n",
       "      <th>subcategory_id</th>\n",
       "      <th>brand_id</th>\n",
       "      <th>image_1</th>\n",
       "      <th>image_2</th>\n",
       "      <th>image_3</th>\n",
       "      <th>specs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Allen Solly Men's Plain Slim Fit Casual Shirt</td>\n",
       "      <td>699</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>Blue, Purple, Black</td>\n",
       "      <td>Allen solly Mens Cotton Full sleeve Overdyed s...</td>\n",
       "      <td>2020-07-08 15:54:29.990947</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>58a16e6bee54bb2cddbf.jpg</td>\n",
       "      <td>a983bf257c13c61f6600.jpg</td>\n",
       "      <td>d2bfc40b87bceda78823.jpg</td>\n",
       "      <td>Fit : Regular Fit|Sleeve Length : Long Sleeves</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Park Avenue Men's Solid Slim Fit Formal Shirt</td>\n",
       "      <td>563</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>White, Blue, Khaki</td>\n",
       "      <td>This classic shirt from Park Avenue is a versa...</td>\n",
       "      <td>2020-07-08 15:57:00.755305</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>52e5a9ab0ec7bb30a1d8.jpg</td>\n",
       "      <td>6bc2786d351652afcfaf.jpg</td>\n",
       "      <td>b0fe79b775b77d9f15fb.jpg</td>\n",
       "      <td>Sleeve Length : Long Sleeves|Material : Cotton...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Park Avenue Men's Plain Slim Fit Formal Shirt</td>\n",
       "      <td>654</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>White, Blue, Black</td>\n",
       "      <td>Be noticed at work by enhancing your looks wit...</td>\n",
       "      <td>2020-07-08 15:58:57.811980</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>53a09ec68d5f5ab9627c.jpg</td>\n",
       "      <td>896324c13cde4988b3be.jpg</td>\n",
       "      <td>4537b9a61d8f4013fe9c.jpg</td>\n",
       "      <td>Sleeve Length : Long Sleeves|Material : Cotton...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>31</td>\n",
       "      <td>Time Travlr Men Maroon &amp; Black Regular Fit Che...</td>\n",
       "      <td>649</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>Red</td>\n",
       "      <td>Maroon and black checked casual shirt, has a s...</td>\n",
       "      <td>2020-07-09 16:36:52.039533</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>60484a12700fd4e28862.jpg</td>\n",
       "      <td>8aeef96d6a4331b41fe7.jpg</td>\n",
       "      <td>dfb132b6330e3a1d2517.jpg</td>\n",
       "      <td>Fit : Regular Fit|Sleeve Length : Long Sleeves...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>32</td>\n",
       "      <td>Time Travlr Men Black &amp; Grey Checked Casual Shirt</td>\n",
       "      <td>649</td>\n",
       "      <td>0</td>\n",
       "      <td>150</td>\n",
       "      <td>Black</td>\n",
       "      <td>Black and grey checked casual shirt, has a spr...</td>\n",
       "      <td>2020-07-09 16:47:15.603108</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>3218c3582e825e5ec4f3.jpg</td>\n",
       "      <td>876efeac71732801e589.jpg</td>\n",
       "      <td>ca915e2ebc1f4e8bbfd8.jpg</td>\n",
       "      <td>Fit : Regular Fit|Sleeve Length : Long Sleeves...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id                                               name  price  discount  \\\n",
       "0    1      Allen Solly Men's Plain Slim Fit Casual Shirt    699         0   \n",
       "1    2      Park Avenue Men's Solid Slim Fit Formal Shirt    563         0   \n",
       "2    3      Park Avenue Men's Plain Slim Fit Formal Shirt    654         0   \n",
       "30  31  Time Travlr Men Maroon & Black Regular Fit Che...    649         0   \n",
       "31  32  Time Travlr Men Black & Grey Checked Casual Shirt    649         0   \n",
       "\n",
       "    stock               colors  \\\n",
       "0      25  Blue, Purple, Black   \n",
       "1      15   White, Blue, Khaki   \n",
       "2      15   White, Blue, Black   \n",
       "30    100                  Red   \n",
       "31    150                Black   \n",
       "\n",
       "                                                 desc  \\\n",
       "0   Allen solly Mens Cotton Full sleeve Overdyed s...   \n",
       "1   This classic shirt from Park Avenue is a versa...   \n",
       "2   Be noticed at work by enhancing your looks wit...   \n",
       "30  Maroon and black checked casual shirt, has a s...   \n",
       "31  Black and grey checked casual shirt, has a spr...   \n",
       "\n",
       "                      pub_date  category_id  subcategory_id  brand_id  \\\n",
       "0   2020-07-08 15:54:29.990947            1               2         8   \n",
       "1   2020-07-08 15:57:00.755305            1               2        11   \n",
       "2   2020-07-08 15:58:57.811980            1               2        11   \n",
       "30  2020-07-09 16:36:52.039533            1               2        11   \n",
       "31  2020-07-09 16:47:15.603108            1               2        11   \n",
       "\n",
       "                     image_1                   image_2  \\\n",
       "0   58a16e6bee54bb2cddbf.jpg  a983bf257c13c61f6600.jpg   \n",
       "1   52e5a9ab0ec7bb30a1d8.jpg  6bc2786d351652afcfaf.jpg   \n",
       "2   53a09ec68d5f5ab9627c.jpg  896324c13cde4988b3be.jpg   \n",
       "30  60484a12700fd4e28862.jpg  8aeef96d6a4331b41fe7.jpg   \n",
       "31  3218c3582e825e5ec4f3.jpg  876efeac71732801e589.jpg   \n",
       "\n",
       "                     image_3  \\\n",
       "0   d2bfc40b87bceda78823.jpg   \n",
       "1   b0fe79b775b77d9f15fb.jpg   \n",
       "2   4537b9a61d8f4013fe9c.jpg   \n",
       "30  dfb132b6330e3a1d2517.jpg   \n",
       "31  ca915e2ebc1f4e8bbfd8.jpg   \n",
       "\n",
       "                                                specs  \n",
       "0      Fit : Regular Fit|Sleeve Length : Long Sleeves  \n",
       "1   Sleeve Length : Long Sleeves|Material : Cotton...  \n",
       "2   Sleeve Length : Long Sleeves|Material : Cotton...  \n",
       "30  Fit : Regular Fit|Sleeve Length : Long Sleeves...  \n",
       "31  Fit : Regular Fit|Sleeve Length : Long Sleeves...  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Fit : Regular Fit|Fit : Regular Fit|Fit : Regular Fit|Fit : Regular Fit|Fit : Regular Fit',\n",
       " 'Sleeve Length : Long Sleeves|Sleeve Length : Long Sleeves|Sleeve Length : Long Sleeves|Sleeve Length : Long Sleeves|Sleeve Length : Long Sleeves',\n",
       " 'Material : Cotton|Material : Cotton|Material : Cotton|Material : Cotton|Material : Cotton|Material : Cotton',\n",
       " 'Dial Shape: Round|Dial Shape: Round',\n",
       " 'Dial Pattern: Round|Dial Pattern: Round']"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product=pd.merge(items,cat_subcat[['category','subcategory_id','subcategory']],how='left',on='subcategory_id')\n",
    "product=product.drop(['image_1','image_2','image_3'],axis='columns')\n",
    "product=pd.merge(product,specifications,how='left',on='id')\n",
    "spec_list=product.groupby('id')['name_y'].apply(list)\n",
    "spec_str=[]\n",
    "for i in spec_list:\n",
    "    spec_str.append('|'.join(map(str,i)))\n",
    "spec_str    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaned = items.set_index('id').specs.str.split('|', expand=True).stack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id   \n",
       "1   0               Fit : Regular Fit\n",
       "    1    Sleeve Length : Long Sleeves\n",
       "2   0    Sleeve Length : Long Sleeves\n",
       "    1               Material : Cotton\n",
       "    2     Collar : Button-Down Collar\n",
       "    3                 Pattern : Solid\n",
       "    4               Occasion : Formal\n",
       "    5                  Fit : Slim Fit\n",
       "    6        Collar : Mandarin Collar\n",
       "3   0    Sleeve Length : Long Sleeves\n",
       "    1               Material : Cotton\n",
       "    2                 Pattern : Solid\n",
       "    3               Occasion : Formal\n",
       "    4                  Fit : Slim Fit\n",
       "    5         Collar : Ainsley Collar\n",
       "31  0               Fit : Regular Fit\n",
       "    1    Sleeve Length : Long Sleeves\n",
       "    2               Material : Cotton\n",
       "    3          Collar : Spread Collar\n",
       "    4               Occasion : Casual\n",
       "    5        Pattern : Buffalo Checks\n",
       "32  0               Fit : Regular Fit\n",
       "    1    Sleeve Length : Long Sleeves\n",
       "    2               Material : Cotton\n",
       "    3          Collar : Spread Collar\n",
       "    4              Pattern : Textured\n",
       "    5         Pattern : Tartan Checks\n",
       "dtype: object"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>Collar : Ainsley Collar</th>\n",
       "      <th>Collar : Button-Down Collar</th>\n",
       "      <th>Collar : Mandarin Collar</th>\n",
       "      <th>Collar : Spread Collar</th>\n",
       "      <th>Fit : Regular Fit</th>\n",
       "      <th>Fit : Slim Fit</th>\n",
       "      <th>Material : Cotton</th>\n",
       "      <th>Occasion : Casual</th>\n",
       "      <th>Occasion : Formal</th>\n",
       "      <th>Pattern : Buffalo Checks</th>\n",
       "      <th>Pattern : Solid</th>\n",
       "      <th>Pattern : Tartan Checks</th>\n",
       "      <th>Pattern : Textured</th>\n",
       "      <th>Sleeve Length : Long Sleeves</th>\n",
       "      <th>product_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</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>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Collar : Ainsley Collar  Collar : Button-Down Collar  \\\n",
       "id                                                         \n",
       "1                         0                            0   \n",
       "2                         0                            1   \n",
       "3                         1                            0   \n",
       "31                        0                            0   \n",
       "32                        0                            0   \n",
       "\n",
       "    Collar : Mandarin Collar  Collar : Spread Collar  Fit : Regular Fit  \\\n",
       "id                                                                        \n",
       "1                          0                       0                  1   \n",
       "2                          1                       0                  0   \n",
       "3                          0                       0                  0   \n",
       "31                         0                       1                  1   \n",
       "32                         0                       1                  1   \n",
       "\n",
       "    Fit : Slim Fit  Material : Cotton  Occasion : Casual  Occasion : Formal  \\\n",
       "id                                                                            \n",
       "1                0                  0                  0                  0   \n",
       "2                1                  1                  0                  1   \n",
       "3                1                  1                  0                  1   \n",
       "31               0                  1                  1                  0   \n",
       "32               0                  1                  0                  0   \n",
       "\n",
       "    Pattern : Buffalo Checks  Pattern : Solid  Pattern : Tartan Checks  \\\n",
       "id                                                                       \n",
       "1                          0                0                        0   \n",
       "2                          0                1                        0   \n",
       "3                          0                1                        0   \n",
       "31                         1                0                        0   \n",
       "32                         0                0                        1   \n",
       "\n",
       "    Pattern : Textured  Sleeve Length : Long Sleeves  product_id  \n",
       "id                                                                \n",
       "1                    0                             1           1  \n",
       "2                    0                             1           2  \n",
       "3                    0                             1           3  \n",
       "31                   0                             1          31  \n",
       "32                   1                             1          32  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spec=pd.get_dummies(cleaned).groupby(level=0).sum()\n",
    "spec['product_id']=spec.index\n",
    "spec"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>price</th>\n",
       "      <th>discount</th>\n",
       "      <th>stock</th>\n",
       "      <th>colors</th>\n",
       "      <th>desc</th>\n",
       "      <th>pub_date</th>\n",
       "      <th>category_id</th>\n",
       "      <th>subcategory_id</th>\n",
       "      <th>...</th>\n",
       "      <th>Fit : Slim Fit</th>\n",
       "      <th>Material : Cotton</th>\n",
       "      <th>Occasion : Casual</th>\n",
       "      <th>Occasion : Formal</th>\n",
       "      <th>Pattern : Buffalo Checks</th>\n",
       "      <th>Pattern : Solid</th>\n",
       "      <th>Pattern : Tartan Checks</th>\n",
       "      <th>Pattern : Textured</th>\n",
       "      <th>Sleeve Length : Long Sleeves</th>\n",
       "      <th>product_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Allen Solly Men's Plain Slim Fit Casual Shirt</td>\n",
       "      <td>699</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>Blue, Purple, Black</td>\n",
       "      <td>Allen solly Mens Cotton Full sleeve Overdyed s...</td>\n",
       "      <td>2020-07-08 15:54:29.990947</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Park Avenue Men's Solid Slim Fit Formal Shirt</td>\n",
       "      <td>563</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>White, Blue, Khaki</td>\n",
       "      <td>This classic shirt from Park Avenue is a versa...</td>\n",
       "      <td>2020-07-08 15:57:00.755305</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Park Avenue Men's Plain Slim Fit Formal Shirt</td>\n",
       "      <td>654</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>White, Blue, Black</td>\n",
       "      <td>Be noticed at work by enhancing your looks wit...</td>\n",
       "      <td>2020-07-08 15:58:57.811980</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>31</td>\n",
       "      <td>Time Travlr Men Maroon &amp; Black Regular Fit Che...</td>\n",
       "      <td>649</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>Red</td>\n",
       "      <td>Maroon and black checked casual shirt, has a s...</td>\n",
       "      <td>2020-07-09 16:36:52.039533</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>32</td>\n",
       "      <td>Time Travlr Men Black &amp; Grey Checked Casual Shirt</td>\n",
       "      <td>649</td>\n",
       "      <td>0</td>\n",
       "      <td>150</td>\n",
       "      <td>Black</td>\n",
       "      <td>Black and grey checked casual shirt, has a spr...</td>\n",
       "      <td>2020-07-09 16:47:15.603108</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</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>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                               name  price  discount  \\\n",
       "0   1      Allen Solly Men's Plain Slim Fit Casual Shirt    699         0   \n",
       "1   2      Park Avenue Men's Solid Slim Fit Formal Shirt    563         0   \n",
       "2   3      Park Avenue Men's Plain Slim Fit Formal Shirt    654         0   \n",
       "3  31  Time Travlr Men Maroon & Black Regular Fit Che...    649         0   \n",
       "4  32  Time Travlr Men Black & Grey Checked Casual Shirt    649         0   \n",
       "\n",
       "   stock               colors  \\\n",
       "0     25  Blue, Purple, Black   \n",
       "1     15   White, Blue, Khaki   \n",
       "2     15   White, Blue, Black   \n",
       "3    100                  Red   \n",
       "4    150                Black   \n",
       "\n",
       "                                                desc  \\\n",
       "0  Allen solly Mens Cotton Full sleeve Overdyed s...   \n",
       "1  This classic shirt from Park Avenue is a versa...   \n",
       "2  Be noticed at work by enhancing your looks wit...   \n",
       "3  Maroon and black checked casual shirt, has a s...   \n",
       "4  Black and grey checked casual shirt, has a spr...   \n",
       "\n",
       "                     pub_date  category_id  subcategory_id  ...  \\\n",
       "0  2020-07-08 15:54:29.990947            1               2  ...   \n",
       "1  2020-07-08 15:57:00.755305            1               2  ...   \n",
       "2  2020-07-08 15:58:57.811980            1               2  ...   \n",
       "3  2020-07-09 16:36:52.039533            1               2  ...   \n",
       "4  2020-07-09 16:47:15.603108            1               2  ...   \n",
       "\n",
       "   Fit : Slim Fit Material : Cotton Occasion : Casual Occasion : Formal  \\\n",
       "0               0                 0                 0                 0   \n",
       "1               1                 1                 0                 1   \n",
       "2               1                 1                 0                 1   \n",
       "3               0                 1                 1                 0   \n",
       "4               0                 1                 0                 0   \n",
       "\n",
       "  Pattern : Buffalo Checks  Pattern : Solid  Pattern : Tartan Checks  \\\n",
       "0                        0                0                        0   \n",
       "1                        0                1                        0   \n",
       "2                        0                1                        0   \n",
       "3                        1                0                        0   \n",
       "4                        0                0                        1   \n",
       "\n",
       "   Pattern : Textured  Sleeve Length : Long Sleeves  product_id  \n",
       "0                   0                             1           1  \n",
       "1                   0                             1           2  \n",
       "2                   0                             1           3  \n",
       "3                   0                             1          31  \n",
       "4                   1                             1          32  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "items=pd.merge(items,spec,left_on=\"id\",right_on='product_id')\n",
    "items"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}