00b_Cleanup Missing Values.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning\n",
    "\n",
    "This notebook uses the scraped data to fill in missing values in the dataset. The output is a csv file that is a combination of reviews and product features. These files are consumed by the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Populating the interactive namespace from numpy and matplotlib\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import gzip\n",
    "%pylab inline\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# configure file names\n",
    "reviews_file = 'reviews-women-full.csv'\n",
    "output_file = 'reviews_Women_all_final' + '_scraped.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Reading Julian's files\n",
    "def parse(path):\n",
    "    g = gzip.open(path, 'rb')\n",
    "    for l in g:\n",
    "        yield eval(l)\n",
    "\n",
    "def getDF(path):\n",
    "    i = 0\n",
    "    df = {}\n",
    "    for d in parse(path):\n",
    "        df[i] = d\n",
    "        i += 1\n",
    "    return pd.DataFrame.from_dict(df, orient='index')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index([u'reviewerId', u'asin', u'unixtime'], dtype='object')\n",
      "(2855258, 3)\n"
     ]
    }
   ],
   "source": [
    "# read reviews file\n",
    "# reviews = getDF(reviews_file)\n",
    "reviews = pd.read_csv(reviews_file, names=['reviewerId', 'asin', 'unixtime'])\n",
    "print reviews.columns\n",
    "print reviews.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(603277, 16)\n",
      "Index([u'asin', u'categories', u'title', u'price', u'salesRank', u'imUrl',\n",
      "       u'brand', u'related', u'description', u'BRAND', u'FEATURE_BULLETS',\n",
      "       u'NAME', u'ORIGINAL_PRICE', u'PRODUCT_DESCRIPTION',\n",
      "       u'PRODUCT_INFORMATION', u'SALE_PRICE'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "# read product metadata\n",
    "meta = pd.read_hdf('womens_Meta_scraped.hdf5', key='data')\n",
    "print meta.shape\n",
    "print meta.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean Up Price, Brand, Description"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import re\n",
    "\n",
    "# the following functions are used to get the final value of the price from the scraped data\n",
    "\n",
    "def is_number(s):\n",
    "    try:\n",
    "        complex(s) # for int, long, float and complex\n",
    "    except ValueError:\n",
    "        return False\n",
    "\n",
    "    return True\n",
    "getMoney = lambda text : [ ''.join(x[0]) for x in re.findall(r'(\\$?\\d+([,\\.]\\d+)?k?)', text.replace(',', '').replace('$',''))]\n",
    "\n",
    "def getAvg(x):\n",
    "    if np.isnan(x.price) and (pd.isnull(x.SALE_PRICE) == False or \n",
    "                              pd.isnull(x.ORIGINAL_PRICE) == False):\n",
    "        # each of these could be a high/low range\n",
    "        s = None\n",
    "        o = None\n",
    "        if not pd.isnull(x.SALE_PRICE): \n",
    "            s = average([(float(y) if is_number(y) else -1) for y in getMoney(x.SALE_PRICE)])\n",
    "        if not pd.isnull(x.ORIGINAL_PRICE):\n",
    "            o = average([(float(y) if is_number(y) else -1) for y in getMoney(x.ORIGINAL_PRICE)])\n",
    "        return average([s if not pd.isnull(x.SALE_PRICE) else o, o if not pd.isnull(x.ORIGINAL_PRICE) else s])\n",
    "    else:\n",
    "        return x.price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "length: 603277\n",
      "missing price: 349160\n"
     ]
    }
   ],
   "source": [
    "print 'length:', meta.shape[0]\n",
    "print 'missing price:', meta[(meta.price.isnull()) | (meta.price == -1)].shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "missing price 278835\n"
     ]
    }
   ],
   "source": [
    "# fill in missing price with scraped price\n",
    "meta[['price']] = meta.apply(lambda x: getAvg(x), axis='columns')\n",
    "print 'missing price', meta[(meta.price.isnull()) | (meta.price == -1)].shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "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>categories</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "      <th>salesRank</th>\n",
       "      <th>imUrl</th>\n",
       "      <th>brand</th>\n",
       "      <th>related</th>\n",
       "      <th>description</th>\n",
       "      <th>BRAND</th>\n",
       "      <th>FEATURE_BULLETS</th>\n",
       "      <th>NAME</th>\n",
       "      <th>ORIGINAL_PRICE</th>\n",
       "      <th>PRODUCT_DESCRIPTION</th>\n",
       "      <th>PRODUCT_INFORMATION</th>\n",
       "      <th>SALE_PRICE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0456844570</td>\n",
       "      <td>[[Clothing, Shoes &amp; Jewelry, Women, Accessorie...</td>\n",
       "      <td>RiZ Women's Beautify Crafted &amp;frac12; Rimmed F...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{u'Clothing': 1180499}</td>\n",
       "      <td>http://ecx.images-amazon.com/images/I/31QZTHxv...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{u'also_viewed': [u'B008MTRT1O', u'B00BUG47S4'...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Riz</td>\n",
       "      <td>[Plastic frame, Plastic lens, non-polarized, L...</td>\n",
       "      <td>RiZ Women's Beautify Crafted ½ Rimmed Floral C...</td>\n",
       "      <td>None</td>\n",
       "      <td>The perfect accessory for that summer dress. 4...</td>\n",
       "      <td>{}</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0641674791</td>\n",
       "      <td>[[Clothing, Shoes &amp; Jewelry, Women, Accessorie...</td>\n",
       "      <td>Red Crocodile Embossed Leather Travel Wallet</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://ecx.images-amazon.com/images/I/31wW2pPm...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ISBN: 0641674791\\nISBN-13: 9780641674792\\nManu...</td>\n",
       "      <td>Punctuate</td>\n",
       "      <td>[This genuine leather travel wallet, covered i...</td>\n",
       "      <td>Red Crocodile Embossed Leather Travel Wallet</td>\n",
       "      <td>None</td>\n",
       "      <td>Product Description\\nISBN: 0641674791\\nISBN-13...</td>\n",
       "      <td>{}</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0681358173</td>\n",
       "      <td>[[Clothing, Shoes &amp; Jewelry, Women, Petite, Fa...</td>\n",
       "      <td>Bear Ear Hoodie Jacket &amp;amp; Poms on drawstrin...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{u'Toys &amp; Games': 600198}</td>\n",
       "      <td>http://ecx.images-amazon.com/images/I/51JCLkQk...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Adorable fleece teddy bear ear sweater for adu...</td>\n",
       "      <td>Tasman</td>\n",
       "      <td>[Note: Korean Fashionwear sizes run smaller, S...</td>\n",
       "      <td>Bear Ear Hoodie Jacket &amp; Poms on drawstrings ~...</td>\n",
       "      <td>None</td>\n",
       "      <td>Product Description\\nAdorable fleece teddy bea...</td>\n",
       "      <td>{}</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1304743594</td>\n",
       "      <td>[[Clothing, Shoes &amp; Jewelry, Women, Watches, W...</td>\n",
       "      <td>Watch Black Match Iphone</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://ecx.images-amazon.com/images/I/4135gTwj...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{u'also_viewed': [u'1300827327', u'1304743128'...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>alexgurman.com</td>\n",
       "      <td>[]</td>\n",
       "      <td>Watch Black Match Iphone</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>{}</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1394751931</td>\n",
       "      <td>[[Clothing, Shoes &amp; Jewelry, Women, Watches, W...</td>\n",
       "      <td>Luxury Dark Angel's Heart Women Crystal Bracel...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://ecx.images-amazon.com/images/I/51wCNVS%...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>new brand</td>\n",
       "      <td>[]</td>\n",
       "      <td>Luxury Dark Angel's Heart Women Crystal Bracel...</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>{}</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         asin                                         categories  \\\n",
       "0  0456844570  [[Clothing, Shoes & Jewelry, Women, Accessorie...   \n",
       "1  0641674791  [[Clothing, Shoes & Jewelry, Women, Accessorie...   \n",
       "2  0681358173  [[Clothing, Shoes & Jewelry, Women, Petite, Fa...   \n",
       "4  1304743594  [[Clothing, Shoes & Jewelry, Women, Watches, W...   \n",
       "5  1394751931  [[Clothing, Shoes & Jewelry, Women, Watches, W...   \n",
       "\n",
       "                                               title  price  \\\n",
       "0  RiZ Women's Beautify Crafted &frac12; Rimmed F...    NaN   \n",
       "1       Red Crocodile Embossed Leather Travel Wallet    NaN   \n",
       "2  Bear Ear Hoodie Jacket &amp; Poms on drawstrin...    NaN   \n",
       "4                           Watch Black Match Iphone    NaN   \n",
       "5  Luxury Dark Angel's Heart Women Crystal Bracel...    NaN   \n",
       "\n",
       "                   salesRank  \\\n",
       "0     {u'Clothing': 1180499}   \n",
       "1                        NaN   \n",
       "2  {u'Toys & Games': 600198}   \n",
       "4                        NaN   \n",
       "5                        NaN   \n",
       "\n",
       "                                               imUrl brand  \\\n",
       "0  http://ecx.images-amazon.com/images/I/31QZTHxv...   NaN   \n",
       "1  http://ecx.images-amazon.com/images/I/31wW2pPm...   NaN   \n",
       "2  http://ecx.images-amazon.com/images/I/51JCLkQk...   NaN   \n",
       "4  http://ecx.images-amazon.com/images/I/4135gTwj...   NaN   \n",
       "5  http://ecx.images-amazon.com/images/I/51wCNVS%...   NaN   \n",
       "\n",
       "                                             related  \\\n",
       "0  {u'also_viewed': [u'B008MTRT1O', u'B00BUG47S4'...   \n",
       "1                                                NaN   \n",
       "2                                                NaN   \n",
       "4  {u'also_viewed': [u'1300827327', u'1304743128'...   \n",
       "5                                                NaN   \n",
       "\n",
       "                                         description           BRAND  \\\n",
       "0                                                NaN             Riz   \n",
       "1  ISBN: 0641674791\\nISBN-13: 9780641674792\\nManu...       Punctuate   \n",
       "2  Adorable fleece teddy bear ear sweater for adu...          Tasman   \n",
       "4                                                NaN  alexgurman.com   \n",
       "5                                                NaN       new brand   \n",
       "\n",
       "                                     FEATURE_BULLETS  \\\n",
       "0  [Plastic frame, Plastic lens, non-polarized, L...   \n",
       "1  [This genuine leather travel wallet, covered i...   \n",
       "2  [Note: Korean Fashionwear sizes run smaller, S...   \n",
       "4                                                 []   \n",
       "5                                                 []   \n",
       "\n",
       "                                                NAME ORIGINAL_PRICE  \\\n",
       "0  RiZ Women's Beautify Crafted ½ Rimmed Floral C...           None   \n",
       "1       Red Crocodile Embossed Leather Travel Wallet           None   \n",
       "2  Bear Ear Hoodie Jacket & Poms on drawstrings ~...           None   \n",
       "4                           Watch Black Match Iphone           None   \n",
       "5  Luxury Dark Angel's Heart Women Crystal Bracel...           None   \n",
       "\n",
       "                                 PRODUCT_DESCRIPTION PRODUCT_INFORMATION  \\\n",
       "0  The perfect accessory for that summer dress. 4...                  {}   \n",
       "1  Product Description\\nISBN: 0641674791\\nISBN-13...                  {}   \n",
       "2  Product Description\\nAdorable fleece teddy bea...                  {}   \n",
       "4                                                                     {}   \n",
       "5                                                                     {}   \n",
       "\n",
       "  SALE_PRICE  \n",
       "0       None  \n",
       "1       None  \n",
       "2       None  \n",
       "4       None  \n",
       "5       None  "
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meta[(meta.price.isnull()) ].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Brand"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "length: 603277\n",
      "missing brand: 579363\n"
     ]
    }
   ],
   "source": [
    "print 'length:', meta.shape[0]\n",
    "print 'missing brand:', meta[(meta.brand == '') | (meta.brand.isnull()) | (meta.brand.str.contains('nknown'))].shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "missing brand: 1084\n"
     ]
    }
   ],
   "source": [
    "brand = lambda x: ' '.join([y.strip() for y in unicode(x.BRAND if pd.isnull(x.brand) or str(x.brand).find('nknown') >= 0 or str(x.brand) == '' else x.brand).split(',')])\n",
    "meta.brand = meta.apply(lambda x: brand(x), axis='columns')\n",
    "print 'missing brand:', meta[(meta.brand == '') | (meta.brand.isnull()) | (meta.brand.str.contains('nknown'))].shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "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>brand</th>\n",
       "      <th>BRAND</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168127</th>\n",
       "      <td>nknown</td>\n",
       "      <td>nknown</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          brand    BRAND\n",
       "183     Unknown  Unknown\n",
       "168127   nknown   nknown"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meta[(meta.brand == '') | (meta.brand.isnull()) | (meta.brand.str.contains('nknown'))][['brand', 'BRAND']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Description"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "length: 603277\n",
      "missing description: 587975\n"
     ]
    }
   ],
   "source": [
    "print 'length:', meta.shape[0]\n",
    "print 'missing description:', meta[(meta.description == '') | (meta.description.isnull())].shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "missing description: 82541\n"
     ]
    }
   ],
   "source": [
    "description = lambda x: ' '.join([y.strip() for y in x.PRODUCT_DESCRIPTION.split(',')]) if pd.isnull(x.description) or x.description.strip() == '' else x.description\n",
    "meta.description = meta.apply(lambda x: description(x), axis='columns')\n",
    "print 'missing description:', meta[(meta.description == '') | (meta.description.isnull())].shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create output file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Desired Output columns and order:\n",
    "```python\n",
    "[u'reviewerId', u'asin', u'unixReviewTime', u'brand', u'price']\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'reviewerId', u'asin', u'unixtime'], dtype='object')"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'asin', u'categories', u'title', u'price', u'salesRank', u'imUrl',\n",
       "       u'brand', u'related', u'description', u'BRAND', u'FEATURE_BULLETS',\n",
       "       u'NAME', u'ORIGINAL_PRICE', u'PRODUCT_DESCRIPTION',\n",
       "       u'PRODUCT_INFORMATION', u'SALE_PRICE'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meta.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "output = pd.merge(reviews[[c for c in reviews.columns if c not in ['brand', 'price']]], \n",
    "                  meta, \n",
    "                  on='asin', \n",
    "                  how='left')[[u'reviewerId', u'asin', u'unixtime', u'brand', u'price', u'description']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "output.to_csv(output_file, index=False, encoding='utf-8')"
   ]
  },
  {
   "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}