01_data_prep_scrapped_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/Deepthi/Documents/DSE/Capstone/Data/From Julius\n"
     ]
    }
   ],
   "source": [
    "%cd /Users/Deepthi/Documents/DSE/Capstone/Data/From Julius"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "womens_scrapped_cpp_br_pr = pd.read_csv('reviews_Women_scraped_cpp.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# womens_scrapped_cpp_br_pr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "womens_scrapped_all = pd.read_hdf('cleaned.hd5')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sklearn.feature_extraction.text \n",
    "from sklearn.feature_extraction.text  import TfidfVectorizer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "product_title = list(womens_scrapped_all['NAME'].drop_duplicates().dropna())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "my_stop_words = sklearn.feature_extraction.text.ENGLISH_STOP_WORDS.union({'women','womens'}) \n",
    "\n",
    "# my_stop_words = sklearn.feature_extraction.text.ENGLISH_STOP_WORDS\n",
    "corpus = product_title\n",
    "\n",
    "## Considered only 2-grams after trying 1-grams and 3-grams. 2-grams give more meaningful information\n",
    "vectorizer = TfidfVectorizer(stop_words = set(my_stop_words),ngram_range=(2,2)) \n",
    "\n",
    "X = vectorizer.fit_transform(corpus)\n",
    "idf = vectorizer.idf_\n",
    "\n",
    "# print dict(zip(vectorizer.get_feature_names(), idf))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# vectorizer.get_feature_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "z=vectorizer.inverse_transform(X)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "bi_grams = list(z[0])\n",
    "for i in range(1,len(z)):\n",
    "    bi_grams = bi_grams + list(z[i])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# list(z[0]) + list(z[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from collections import Counter\n",
    "d= Counter(bi_grams)\n",
    "\n",
    "df = pd.DataFrame.from_dict(d, orient='index').reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.columns = [['bigrams','count']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bigrams</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25083</th>\n",
       "      <td>sterling silver</td>\n",
       "      <td>963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40572</th>\n",
       "      <td>plus size</td>\n",
       "      <td>331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35742</th>\n",
       "      <td>long sleeve</td>\n",
       "      <td>245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43885</th>\n",
       "      <td>running shoe</td>\n",
       "      <td>231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13494</th>\n",
       "      <td>stainless steel</td>\n",
       "      <td>230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41248</th>\n",
       "      <td>pendant necklace</td>\n",
       "      <td>227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24944</th>\n",
       "      <td>stud earrings</td>\n",
       "      <td>211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38763</th>\n",
       "      <td>cubic zirconia</td>\n",
       "      <td>197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26107</th>\n",
       "      <td>925 sterling</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12886</th>\n",
       "      <td>sandal black</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3527</th>\n",
       "      <td>knee high</td>\n",
       "      <td>141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15507</th>\n",
       "      <td>new balance</td>\n",
       "      <td>136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31600</th>\n",
       "      <td>boot black</td>\n",
       "      <td>133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28876</th>\n",
       "      <td>wedge sandal</td>\n",
       "      <td>126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38117</th>\n",
       "      <td>necklace 18</td>\n",
       "      <td>119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25689</th>\n",
       "      <td>ballet flat</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13813</th>\n",
       "      <td>underwire bra</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35862</th>\n",
       "      <td>fashion sneaker</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7111</th>\n",
       "      <td>hoop earrings</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17610</th>\n",
       "      <td>flip flop</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22308</th>\n",
       "      <td>short sleeve</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22991</th>\n",
       "      <td>asics gel</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35818</th>\n",
       "      <td>yellow gold</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42492</th>\n",
       "      <td>thong sandal</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45513</th>\n",
       "      <td>gold tone</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22595</th>\n",
       "      <td>walking shoe</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18688</th>\n",
       "      <td>chain necklace</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4312</th>\n",
       "      <td>mary jane</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4158</th>\n",
       "      <td>black size</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25644</th>\n",
       "      <td>anne klein</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17114</th>\n",
       "      <td>flops brown</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17116</th>\n",
       "      <td>dcs cotton</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17085</th>\n",
       "      <td>jeans indigo</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17117</th>\n",
       "      <td>vintage cute</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17118</th>\n",
       "      <td>355sgsc ancher</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17119</th>\n",
       "      <td>charm 6mm</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17120</th>\n",
       "      <td>shorts coral</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17121</th>\n",
       "      <td>band high</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17122</th>\n",
       "      <td>saucony curve</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17123</th>\n",
       "      <td>set size</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17106</th>\n",
       "      <td>chain beads</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17105</th>\n",
       "      <td>wide hand</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17104</th>\n",
       "      <td>990v3 running</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17103</th>\n",
       "      <td>generation fashion</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17088</th>\n",
       "      <td>raspberry shock</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17089</th>\n",
       "      <td>luggage charm</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17090</th>\n",
       "      <td>pearl face</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17091</th>\n",
       "      <td>prom bridesmaid</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17092</th>\n",
       "      <td>necklace 4mm</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17093</th>\n",
       "      <td>ombre hoop</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17094</th>\n",
       "      <td>convertible dress</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17095</th>\n",
       "      <td>milano slingback</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17096</th>\n",
       "      <td>double hole</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17097</th>\n",
       "      <td>seamless tennis</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17098</th>\n",
       "      <td>chad knee</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17099</th>\n",
       "      <td>poplin pajama</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17100</th>\n",
       "      <td>zurich wool</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17101</th>\n",
       "      <td>jambu gourmet</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17102</th>\n",
       "      <td>steel mother</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45958</th>\n",
       "      <td>viscose fringe</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>45959 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  bigrams  count\n",
       "25083     sterling silver    963\n",
       "40572           plus size    331\n",
       "35742         long sleeve    245\n",
       "43885        running shoe    231\n",
       "13494     stainless steel    230\n",
       "41248    pendant necklace    227\n",
       "24944       stud earrings    211\n",
       "38763      cubic zirconia    197\n",
       "26107        925 sterling    150\n",
       "12886        sandal black    144\n",
       "3527            knee high    141\n",
       "15507         new balance    136\n",
       "31600          boot black    133\n",
       "28876        wedge sandal    126\n",
       "38117         necklace 18    119\n",
       "25689         ballet flat    102\n",
       "13813       underwire bra     98\n",
       "35862     fashion sneaker     98\n",
       "7111        hoop earrings     96\n",
       "17610           flip flop     93\n",
       "22308        short sleeve     92\n",
       "22991           asics gel     85\n",
       "35818         yellow gold     82\n",
       "42492        thong sandal     82\n",
       "45513           gold tone     81\n",
       "22595        walking shoe     79\n",
       "18688      chain necklace     79\n",
       "4312            mary jane     78\n",
       "4158           black size     77\n",
       "25644          anne klein     77\n",
       "...                   ...    ...\n",
       "17114         flops brown      1\n",
       "17116          dcs cotton      1\n",
       "17085        jeans indigo      1\n",
       "17117        vintage cute      1\n",
       "17118      355sgsc ancher      1\n",
       "17119           charm 6mm      1\n",
       "17120        shorts coral      1\n",
       "17121           band high      1\n",
       "17122       saucony curve      1\n",
       "17123            set size      1\n",
       "17106         chain beads      1\n",
       "17105           wide hand      1\n",
       "17104       990v3 running      1\n",
       "17103  generation fashion      1\n",
       "17088     raspberry shock      1\n",
       "17089       luggage charm      1\n",
       "17090          pearl face      1\n",
       "17091     prom bridesmaid      1\n",
       "17092        necklace 4mm      1\n",
       "17093          ombre hoop      1\n",
       "17094   convertible dress      1\n",
       "17095    milano slingback      1\n",
       "17096         double hole      1\n",
       "17097     seamless tennis      1\n",
       "17098           chad knee      1\n",
       "17099       poplin pajama      1\n",
       "17100         zurich wool      1\n",
       "17101       jambu gourmet      1\n",
       "17102        steel mother      1\n",
       "45958      viscose fringe      1\n",
       "\n",
       "[45959 rows x 2 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values('count',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45959"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4525"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df['count']>2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# df[df['count']>2]['bigrams']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sel_bigrams=list(df[df['count']>2]['bigrams'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4525"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(sel_bigrams)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# sel_bigrams"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for i in range(len(sel_bigrams)):\n",
    "    womens_scrapped_all[sel_bigrams[i]]=womens_scrapped_all['NAME'].fillna('0').str.lower()\\\n",
    "                                      .str.contains(sel_bigrams[i]).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# len(womens_scrapped_all.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# for i in range(1,10+1):\n",
    "#     if i < 10:\n",
    "#         print i\n",
    "#     else:\n",
    "#         print 'next number'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "## Create a feature vector field by concatenating each of the column generated using the 2-grams\n",
    "\n",
    "womens_scrapped_all['feature_vector'] = \"'\"+womens_scrapped_all[sel_bigrams[0]].astype(str)\n",
    "\n",
    "for i in range(1,len(sel_bigrams)):\n",
    "#     if i < len(sel_bigrams):\n",
    "        womens_scrapped_all['feature_vector'] = womens_scrapped_all['feature_vector'].astype(str)\\\n",
    "                                            +womens_scrapped_all[sel_bigrams[i]].astype(str)\n",
    "#     else:\n",
    "#         womens_scrapped_all['feature_vector']=womens_scrapped_all['feature_vector']\n",
    "\n",
    "# df_meta_only_womens['feature_vector']=df_meta_only_womens[0].str.cat(df_meta_only_womens[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "## Create a feature vector field by concatenating each of the column generated using the 2-grams\n",
    "\n",
    "womens_scrapped_all['cnt'] = womens_scrapped_all[sel_bigrams[0]].astype(int)\n",
    "\n",
    "for i in range(1,len(sel_bigrams)):\n",
    "    womens_scrapped_all['cnt'] = womens_scrapped_all['cnt'].astype(int)\\\n",
    "                                        +womens_scrapped_all[sel_bigrams[i]].astype(int)\n",
    "\n",
    "# df_meta_only_womens['feature_vector']=df_meta_only_womens[0].str.cat(df_meta_only_womens[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# womens_scrapped_all['cnt']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([              u'BRAND',     u'FEATURE_BULLETS',                u'NAME',\n",
       "            u'ORIGINAL_PRICE', u'PRODUCT_DESCRIPTION', u'PRODUCT_INFORMATION',\n",
       "                u'SALE_PRICE',                 u'URL',                u'asin',\n",
       "               u'cz eternity',\n",
       "       ...\n",
       "                u'flat brown',         u'style black',         u'lace ruffle',\n",
       "               u'silver bead',        u'button rings',          u'peace sign',\n",
       "              u'sleeve dress',          u'ip grooved',      u'feature_vector',\n",
       "                       u'cnt'],\n",
       "      dtype='object', length=4536)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "womens_scrapped_all.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>asin</th>\n",
       "      <th>feature_vector</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B000051SEN</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B000078CYM</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B00007FFL9</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B00007GD8X</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B00007GD9W</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>B00007GDAL</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>B00007GDD3</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>B00007IVVR</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>B00007GDG5</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>B0000862FI</td>\n",
       "      <td>'000000000000000000000000000000000000000100000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>B000086211</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>B0000865II</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>B0000866JI</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>B000086778</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>B0000867GG</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>B0000867ON</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>B0000868O9</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>B000086910</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>B0000869QI</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>B0000891IO</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>B0000891K0</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>B00008AALU</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>B00008I8YM</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>B00008ECKG</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>B00008ID0L</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>B00008ID1O</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>B00008ID39</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>B00008IEUW</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>B00008KH9C</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>B00008LUS5</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12909</th>\n",
       "      <td>B0007SUE56</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12911</th>\n",
       "      <td>B000GYR9G8</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12912</th>\n",
       "      <td>B00CWW1Q1Q</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12913</th>\n",
       "      <td>B00GJG91RM</td>\n",
       "      <td>'000000000000001000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12914</th>\n",
       "      <td>B00CN4NNJ6</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12915</th>\n",
       "      <td>B000TE2EM4</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12916</th>\n",
       "      <td>B000M3KYL0</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12917</th>\n",
       "      <td>B000UZAF14</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12918</th>\n",
       "      <td>B00E5GH5BM</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12919</th>\n",
       "      <td>B000GAW1BU</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12921</th>\n",
       "      <td>B000VK2L8S</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12922</th>\n",
       "      <td>B000PU3INQ</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12923</th>\n",
       "      <td>B004KHSA7O</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12924</th>\n",
       "      <td>B000K96U8M</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12926</th>\n",
       "      <td>B0009KNC5Q</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12927</th>\n",
       "      <td>B00518GZ1O</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12928</th>\n",
       "      <td>B000P50IAC</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12929</th>\n",
       "      <td>B009EN0NFQ</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12930</th>\n",
       "      <td>B000VA4L9U</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12931</th>\n",
       "      <td>B00H2VRZEY</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12932</th>\n",
       "      <td>B00EJZUCQY</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12934</th>\n",
       "      <td>B000B76XEO</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12935</th>\n",
       "      <td>B00DM05FV4</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12936</th>\n",
       "      <td>B000LWKD5E</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12937</th>\n",
       "      <td>B000ULSETS</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12938</th>\n",
       "      <td>B0007VU7HI</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12939</th>\n",
       "      <td>B000SMNK5C</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12940</th>\n",
       "      <td>B00DEVVP2Y</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12941</th>\n",
       "      <td>B000SMNKMK</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12942</th>\n",
       "      <td>B00551OXU2</td>\n",
       "      <td>'000000000000000000000000000000000000000000000...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>12725 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             asin                                     feature_vector\n",
       "0      B000051SEN  '000000000000000000000000000000000000000000000...\n",
       "1      B000078CYM  '000000000000000000000000000000000000000000000...\n",
       "2      B00007FFL9  '000000000000000000000000000000000000000000000...\n",
       "3      B00007GD8X  '000000000000000000000000000000000000000000000...\n",
       "4      B00007GD9W  '000000000000000000000000000000000000000000000...\n",
       "5      B00007GDAL  '000000000000000000000000000000000000000000000...\n",
       "6      B00007GDD3  '000000000000000000000000000000000000000000000...\n",
       "7      B00007IVVR  '000000000000000000000000000000000000000000000...\n",
       "8      B00007GDG5  '000000000000000000000000000000000000000000000...\n",
       "9      B0000862FI  '000000000000000000000000000000000000000100000...\n",
       "10     B000086211  '000000000000000000000000000000000000000000000...\n",
       "11     B0000865II  '000000000000000000000000000000000000000000000...\n",
       "12     B0000866JI  '000000000000000000000000000000000000000000000...\n",
       "13     B000086778  '000000000000000000000000000000000000000000000...\n",
       "14     B0000867GG  '000000000000000000000000000000000000000000000...\n",
       "15     B0000867ON  '000000000000000000000000000000000000000000000...\n",
       "16     B0000868O9  '000000000000000000000000000000000000000000000...\n",
       "17     B000086910  '000000000000000000000000000000000000000000000...\n",
       "18     B0000869QI  '000000000000000000000000000000000000000000000...\n",
       "19     B0000891IO  '000000000000000000000000000000000000000000000...\n",
       "20     B0000891K0  '000000000000000000000000000000000000000000000...\n",
       "21     B00008AALU  '000000000000000000000000000000000000000000000...\n",
       "22     B00008I8YM  '000000000000000000000000000000000000000000000...\n",
       "23     B00008ECKG  '000000000000000000000000000000000000000000000...\n",
       "24     B00008ID0L  '000000000000000000000000000000000000000000000...\n",
       "25     B00008ID1O  '000000000000000000000000000000000000000000000...\n",
       "26     B00008ID39  '000000000000000000000000000000000000000000000...\n",
       "27     B00008IEUW  '000000000000000000000000000000000000000000000...\n",
       "28     B00008KH9C  '000000000000000000000000000000000000000000000...\n",
       "29     B00008LUS5  '000000000000000000000000000000000000000000000...\n",
       "...           ...                                                ...\n",
       "12909  B0007SUE56  '000000000000000000000000000000000000000000000...\n",
       "12911  B000GYR9G8  '000000000000000000000000000000000000000000000...\n",
       "12912  B00CWW1Q1Q  '000000000000000000000000000000000000000000000...\n",
       "12913  B00GJG91RM  '000000000000001000000000000000000000000000000...\n",
       "12914  B00CN4NNJ6  '000000000000000000000000000000000000000000000...\n",
       "12915  B000TE2EM4  '000000000000000000000000000000000000000000000...\n",
       "12916  B000M3KYL0  '000000000000000000000000000000000000000000000...\n",
       "12917  B000UZAF14  '000000000000000000000000000000000000000000000...\n",
       "12918  B00E5GH5BM  '000000000000000000000000000000000000000000000...\n",
       "12919  B000GAW1BU  '000000000000000000000000000000000000000000000...\n",
       "12921  B000VK2L8S  '000000000000000000000000000000000000000000000...\n",
       "12922  B000PU3INQ  '000000000000000000000000000000000000000000000...\n",
       "12923  B004KHSA7O  '000000000000000000000000000000000000000000000...\n",
       "12924  B000K96U8M  '000000000000000000000000000000000000000000000...\n",
       "12926  B0009KNC5Q  '000000000000000000000000000000000000000000000...\n",
       "12927  B00518GZ1O  '000000000000000000000000000000000000000000000...\n",
       "12928  B000P50IAC  '000000000000000000000000000000000000000000000...\n",
       "12929  B009EN0NFQ  '000000000000000000000000000000000000000000000...\n",
       "12930  B000VA4L9U  '000000000000000000000000000000000000000000000...\n",
       "12931  B00H2VRZEY  '000000000000000000000000000000000000000000000...\n",
       "12932  B00EJZUCQY  '000000000000000000000000000000000000000000000...\n",
       "12934  B000B76XEO  '000000000000000000000000000000000000000000000...\n",
       "12935  B00DM05FV4  '000000000000000000000000000000000000000000000...\n",
       "12936  B000LWKD5E  '000000000000000000000000000000000000000000000...\n",
       "12937  B000ULSETS  '000000000000000000000000000000000000000000000...\n",
       "12938  B0007VU7HI  '000000000000000000000000000000000000000000000...\n",
       "12939  B000SMNK5C  '000000000000000000000000000000000000000000000...\n",
       "12940  B00DEVVP2Y  '000000000000000000000000000000000000000000000...\n",
       "12941  B000SMNKMK  '000000000000000000000000000000000000000000000...\n",
       "12942  B00551OXU2  '000000000000000000000000000000000000000000000...\n",
       "\n",
       "[12725 rows x 2 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "womens_scrapped_all[['asin','feature_vector']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# for i in range(len(womens_scrapped_all['feature_vector'])):\n",
    "#     if (len(womens_scrapped_all['feature_vector'][i])) == 0:\n",
    "#         print 1\n",
    "# #     else:\n",
    "# #         print 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# womens_scrapped_cpp_br_pr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "womens_scrapped_cpp_br_pr = womens_scrapped_cpp_br_pr.sort_values(by='asin')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "womens_scrapped_all = womens_scrapped_all.sort_values(by='asin')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# womens_scrapped_cpp_br_pr_for_merge=womens_scrapped_cpp_br_pr[womens_scrapped_cpp_br_pr['asin'].isin(list(womens_scrapped_all['asin']))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# womens_scrapped_cpp_br_pr_for_merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "final=womens_scrapped_cpp_br_pr.merge(womens_scrapped_all[['asin','feature_vector']], on='asin', how='left')\\\n",
    "[['reviewerID','asin','unixReviewTime','brand','price','feature_vector']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# final = pd.merge(womens_scrapped_cpp_br_pr,womens_scrapped_all[['asin','feature_vector']], on='asin',how='left')\\\n",
    "# [['reviewerId','asin','unixReviewTime','brand','price','feature_vector']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "final = final.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unnamed: 0        183973\n",
       "reviewerID        183973\n",
       "asin              183973\n",
       "unixReviewTime    183973\n",
       "brand             162065\n",
       "price             139150\n",
       "dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "womens_scrapped_cpp_br_pr.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# womens_scrapped_all.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "reviewerID        181329\n",
       "asin              181329\n",
       "unixReviewTime    181329\n",
       "brand             159421\n",
       "price             136746\n",
       "feature_vector    155757\n",
       "dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# 190695-183973"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'Unnamed: 0', u'reviewerID', u'asin', u'unixReviewTime', u'brand',\n",
       "       u'price'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "womens_scrapped_cpp_br_pr.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "final.to_csv('reviews_cellphone_scraped_cpp_fv_2.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "reviewerID        181329\n",
       "asin              181329\n",
       "unixReviewTime    181329\n",
       "brand             159421\n",
       "price             136746\n",
       "feature_vector    155757\n",
       "dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}