Amazon Product Reviews Exploration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analysis of Amazon product reviews for Electronics category (approx. 7M reviews/ratings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<script src=\"//code.highcharts.com/stock/highstock.js\"></script>\n",
       "<script src=\"//code.highcharts.com/highcharts-more.js\"></script>\n",
       "<script src=\"//code.highcharts.com/modules/exporting.js\"></script>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib\n",
    "import gzip\n",
    "import json\n",
    "import datetime\n",
    "from IPython.display import display, display_pretty, Javascript, HTML\n",
    "from pandas_highcharts.core import serialize\n",
    "from pandas_highcharts.display import display_charts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Parsing & unpacking JSON for zip/json reviews file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def parse_zipfile(path):\n",
    "    file = gzip.open(path, 'rb')\n",
    "    for line in file:\n",
    "        yield eval(line)\n",
    "\n",
    "def get_PD_dataframe(path):\n",
    "    j = 0\n",
    "    dframe = {}\n",
    "    for d in parse_zipfile(path):\n",
    "        dframe[j] = d\n",
    "        j += 1\n",
    "    return pd.DataFrame.from_dict(dframe, orient='index')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Get pandas dataframe for the table\n",
    "df = get_PD_dataframe('reviews_Electronics.json.gz')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>reviewerID</th>\n",
       "      <th>reviewerName</th>\n",
       "      <th>summary</th>\n",
       "      <th>reviewTime</th>\n",
       "      <th>asin</th>\n",
       "      <th>reviewText</th>\n",
       "      <th>overall</th>\n",
       "      <th>unixReviewTime</th>\n",
       "      <th>helpful</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AKM1MP6P0OYPR</td>\n",
       "      <td>Vicki Gibson \"momo4\"</td>\n",
       "      <td>Very thorough</td>\n",
       "      <td>04 13, 2013</td>\n",
       "      <td>0132793040</td>\n",
       "      <td>Corey Barker does a great job of explaining Bl...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1365811200</td>\n",
       "      <td>[1, 1]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2CX7LUOHB2NDG</td>\n",
       "      <td>Bernie</td>\n",
       "      <td>Adobe Photoshop CS5 Crash Course with master P...</td>\n",
       "      <td>07 1, 2012</td>\n",
       "      <td>0321732944</td>\n",
       "      <td>While many beginner DVDs try to teach you ever...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1341100800</td>\n",
       "      <td>[0, 0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2NWSAGRHCP8N5</td>\n",
       "      <td>bowmans2007</td>\n",
       "      <td>absolutely horrible</td>\n",
       "      <td>04 29, 2013</td>\n",
       "      <td>0439886341</td>\n",
       "      <td>It never worked. My daughter worked to earn th...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1367193600</td>\n",
       "      <td>[1, 1]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A2WNBOD3WNDNKT</td>\n",
       "      <td>JAL</td>\n",
       "      <td>Disappointing</td>\n",
       "      <td>07 22, 2013</td>\n",
       "      <td>0439886341</td>\n",
       "      <td>Some of the functions did not work properly.  ...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1374451200</td>\n",
       "      <td>[1, 1]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A1GI0U4ZRJA8WN</td>\n",
       "      <td>Truthfull</td>\n",
       "      <td>TERRIBLE DONT WASTE YOUR MONEY</td>\n",
       "      <td>04 18, 2012</td>\n",
       "      <td>0439886341</td>\n",
       "      <td>Do not waste your money on this thing it is te...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1334707200</td>\n",
       "      <td>[4, 4]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       reviewerID          reviewerName  \\\n",
       "0   AKM1MP6P0OYPR  Vicki Gibson \"momo4\"   \n",
       "1  A2CX7LUOHB2NDG                Bernie   \n",
       "2  A2NWSAGRHCP8N5           bowmans2007   \n",
       "3  A2WNBOD3WNDNKT                   JAL   \n",
       "4  A1GI0U4ZRJA8WN             Truthfull   \n",
       "\n",
       "                                             summary   reviewTime        asin  \\\n",
       "0                                      Very thorough  04 13, 2013  0132793040   \n",
       "1  Adobe Photoshop CS5 Crash Course with master P...   07 1, 2012  0321732944   \n",
       "2                                absolutely horrible  04 29, 2013  0439886341   \n",
       "3                                      Disappointing  07 22, 2013  0439886341   \n",
       "4                     TERRIBLE DONT WASTE YOUR MONEY  04 18, 2012  0439886341   \n",
       "\n",
       "                                          reviewText  overall  unixReviewTime  \\\n",
       "0  Corey Barker does a great job of explaining Bl...      5.0      1365811200   \n",
       "1  While many beginner DVDs try to teach you ever...      5.0      1341100800   \n",
       "2  It never worked. My daughter worked to earn th...      1.0      1367193600   \n",
       "3  Some of the functions did not work properly.  ...      3.0      1374451200   \n",
       "4  Do not waste your money on this thing it is te...      1.0      1334707200   \n",
       "\n",
       "  helpful  \n",
       "0  [1, 1]  \n",
       "1  [0, 0]  \n",
       "2  [1, 1]  \n",
       "3  [1, 1]  \n",
       "4  [4, 4]  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7824482, 9)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Convert data/timestamp to pd format\n",
    "df['reviewdate']=pd.to_datetime(df['unixReviewTime'], unit='s')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#create additional data points of interest\n",
    "df['month'] = pd.DatetimeIndex(df['reviewdate']).month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['year'] = pd.DatetimeIndex(df['reviewdate']).year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "reviewerID                object\n",
       "reviewerName              object\n",
       "summary                   object\n",
       "reviewTime                object\n",
       "asin                      object\n",
       "reviewText                object\n",
       "overall                  float64\n",
       "unixReviewTime             int64\n",
       "helpful                   object\n",
       "reviewdate        datetime64[ns]\n",
       "month                      int32\n",
       "year                       int32\n",
       "dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Summarize the mean rating and review count over years \n",
    "df2=df.groupby('year').agg({'overall':np.mean,'reviewerID':np.size})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#df2.reset_index(level=0, inplace=True)\n",
    "df2=df2.rename(columns = {'overall':'avg_rating','reviewerID':'num_review'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2=df2.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>year</th>\n",
       "      <th>num_review</th>\n",
       "      <th>avg_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1998</td>\n",
       "      <td>4</td>\n",
       "      <td>4.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1999</td>\n",
       "      <td>1212</td>\n",
       "      <td>4.066007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2000</td>\n",
       "      <td>9214</td>\n",
       "      <td>4.113957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2001</td>\n",
       "      <td>14753</td>\n",
       "      <td>3.928489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2002</td>\n",
       "      <td>18648</td>\n",
       "      <td>3.850118</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  num_review  avg_rating\n",
       "0  1998           4    4.500000\n",
       "1  1999        1212    4.066007\n",
       "2  2000        9214    4.113957\n",
       "3  2001       14753    3.928489\n",
       "4  2002       18648    3.850118"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>year</th>\n",
       "      <th>num_review</th>\n",
       "      <th>avg_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>17.000000</td>\n",
       "      <td>1.700000e+01</td>\n",
       "      <td>17.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2006.000000</td>\n",
       "      <td>4.602636e+05</td>\n",
       "      <td>3.944268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>5.049752</td>\n",
       "      <td>7.410750e+05</td>\n",
       "      <td>0.212570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1998.000000</td>\n",
       "      <td>4.000000e+00</td>\n",
       "      <td>3.556025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2002.000000</td>\n",
       "      <td>1.864800e+04</td>\n",
       "      <td>3.850118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2006.000000</td>\n",
       "      <td>8.665900e+04</td>\n",
       "      <td>3.958765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2010.000000</td>\n",
       "      <td>4.756260e+05</td>\n",
       "      <td>4.053685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2014.000000</td>\n",
       "      <td>2.626582e+06</td>\n",
       "      <td>4.500000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              year    num_review  avg_rating\n",
       "count    17.000000  1.700000e+01   17.000000\n",
       "mean   2006.000000  4.602636e+05    3.944268\n",
       "std       5.049752  7.410750e+05    0.212570\n",
       "min    1998.000000  4.000000e+00    3.556025\n",
       "25%    2002.000000  1.864800e+04    3.850118\n",
       "50%    2006.000000  8.665900e+04    3.958765\n",
       "75%    2010.000000  4.756260e+05    4.053685\n",
       "max    2014.000000  2.626582e+06    4.500000"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#summary statistics\n",
    "df2.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div id=\"chart_idRViaYHzd\"</div>\n",
       "    <script type=\"text/javascript\">new Highcharts.Chart({\"legend\":{\"enabled\":true},\"series\":[{\"name\":\"avg_rating\",\"yAxis\":0,\"data\":[[1998,4.5],[1999,4.0660066007],[2000,4.1139570219],[2001,3.9284891209],[2002,3.8501179751],[2003,3.7616905308],[2004,3.556024964],[2005,3.6293441779],[2006,3.7380537509],[2007,3.9902910614],[2008,3.9896738652],[2009,3.9587651361],[2010,3.9160579951],[2011,3.9367941157],[2012,3.9924005803],[2013,4.0712100365],[2014,4.0536847625]]},{\"name\":\"num_review (right)\",\"yAxis\":1,\"data\":[[1998,4],[1999,1212],[2000,9214],[2001,14753],[2002,18648],[2003,23117],[2004,31245],[2005,56311],[2006,86659],[2007,193430],[2008,238521],[2009,325380],[2010,475626],[2011,783503],[2012,1231673],[2013,2626582],[2014,1708604]]}],\"title\":{\"text\":\"Amazon Ratings - Electronics Category\"},\"chart\":{\"renderTo\":\"chart_idRViaYHzd\",\"zoomType\":\"xy\",\"type\":\"column\"},\"xAxis\":{\"title\":{\"text\":\"year\"}},\"yAxis\":[{},{\"opposite\":true}]});</script>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Chart trend over the years\n",
    "df2 = df2.set_index(df2['year'])\n",
    "display_charts(df2,kind='bar',y=['avg_rating','num_review'], secondary_y=['num_review'], title=\"Amazon Ratings - Electronics Category\",zoom=\"xy\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The average rating over the years is around 4, majority reviews for the products are positive. How many reviews were negative in 2013?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['ratings']=['Positive' if x>=4 else 'Neutral' if x==3 else 'Negative' for x in df['overall']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1=df.groupby(['year','ratings']).agg({'overall':np.mean,'reviewerID':np.size})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df1=df1.rename(columns = {'overall':'avg_rating','reviewerID':'num_review'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1=df1.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "year            int64\n",
       "ratings        object\n",
       "num_review      int64\n",
       "avg_rating    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div id=\"chart_idIfTVQXLt\"</div>\n",
       "    <script type=\"text/javascript\">new Highcharts.Chart({\"legend\":{\"enabled\":true},\"series\":[{\"name\":\"avg_rating\",\"yAxis\":0,\"data\":[[\"1998-Positive\",4.5],[\"1999-Negative\",1.3626373626],[\"1999-Neutral\",3.0],[\"1999-Positive\",4.7005347594],[\"2000-Negative\",1.3792822186],[\"2000-Neutral\",3.0],[\"2000-Positive\",4.680521262],[\"2001-Negative\",1.3579970105],[\"2001-Neutral\",3.0],[\"2001-Positive\",4.6703905457],[\"2002-Negative\",1.3446298227],[\"2002-Neutral\",3.0],[\"2002-Positive\",4.6793737236],[\"2003-Negative\",1.3310498056],[\"2003-Neutral\",3.0],[\"2003-Positive\",4.6862173803],[\"2004-Negative\",1.3084018671],[\"2004-Neutral\",3.0],[\"2004-Positive\",4.6734610053],[\"2005-Negative\",1.3091891892],[\"2005-Neutral\",3.0],[\"2005-Positive\",4.6593391514],[\"2006-Negative\",1.318800823],[\"2006-Neutral\",3.0],[\"2006-Positive\",4.6751721552],[\"2007-Negative\",1.3462544084],[\"2007-Neutral\",3.0],[\"2007-Positive\",4.6954963153],[\"2008-Negative\",1.3369116433],[\"2008-Neutral\",3.0],[\"2008-Positive\",4.7036867133],[\"2009-Negative\",1.3347960152],[\"2009-Neutral\",3.0],[\"2009-Positive\",4.7080370377],[\"2010-Negative\",1.3338526182],[\"2010-Neutral\",3.0],[\"2010-Positive\",4.708256774],[\"2011-Negative\",1.3258050746],[\"2011-Neutral\",3.0],[\"2011-Positive\",4.7293844163],[\"2012-Negative\",1.3246541144],[\"2012-Neutral\",3.0],[\"2012-Positive\",4.754712239],[\"2013-Negative\",1.3505007566],[\"2013-Neutral\",3.0],[\"2013-Positive\",4.7547017012],[\"2014-Negative\",1.3318753169],[\"2014-Neutral\",3.0],[\"2014-Positive\",4.7684690008]]},{\"name\":\"num_review (right)\",\"yAxis\":1,\"data\":[[\"1998-Positive\",4],[\"1999-Negative\",182],[\"1999-Neutral\",95],[\"1999-Positive\",935],[\"2000-Negative\",1226],[\"2000-Neutral\",698],[\"2000-Positive\",7290],[\"2001-Negative\",2676],[\"2001-Neutral\",1246],[\"2001-Positive\",10831],[\"2002-Negative\",3836],[\"2002-Neutral\",1591],[\"2002-Positive\",13221],[\"2003-Negative\",5401],[\"2003-Neutral\",1928],[\"2003-Positive\",15788],[\"2004-Negative\",8998],[\"2004-Neutral\",2770],[\"2004-Positive\",19477],[\"2005-Negative\",14800],[\"2005-Neutral\",5073],[\"2005-Positive\",36438],[\"2006-Negative\",20414],[\"2006-Neutral\",7577],[\"2006-Positive\",58668],[\"2007-Negative\",32892],[\"2007-Neutral\",15479],[\"2007-Positive\",145059],[\"2008-Negative\",41174],[\"2008-Neutral\",18597],[\"2008-Positive\",178750],[\"2009-Negative\",59024],[\"2009-Neutral\",26168],[\"2009-Positive\",240188],[\"2010-Negative\",91151],[\"2010-Neutral\",40515],[\"2010-Positive\",343960],[\"2011-Negative\",149924],[\"2011-Neutral\",64022],[\"2011-Positive\",569557],[\"2012-Negative\",224713],[\"2012-Neutral\",95822],[\"2012-Positive\",911138],[\"2013-Negative\",415671],[\"2013-Neutral\",216686],[\"2013-Positive\",1994225],[\"2014-Negative\",286005],[\"2014-Neutral\",134806],[\"2014-Positive\",1287793]]}],\"title\":{\"text\":\"Average Ratings & Review Count by Year\"},\"chart\":{\"renderTo\":\"chart_idIfTVQXLt\",\"zoomType\":\"xy\",\"type\":\"column\"},\"xAxis\":{\"title\":{\"text\":\"average_ratings_in_year\"},\"categories\":[\"1998-Positive\",\"1999-Negative\",\"1999-Neutral\",\"1999-Positive\",\"2000-Negative\",\"2000-Neutral\",\"2000-Positive\",\"2001-Negative\",\"2001-Neutral\",\"2001-Positive\",\"2002-Negative\",\"2002-Neutral\",\"2002-Positive\",\"2003-Negative\",\"2003-Neutral\",\"2003-Positive\",\"2004-Negative\",\"2004-Neutral\",\"2004-Positive\",\"2005-Negative\",\"2005-Neutral\",\"2005-Positive\",\"2006-Negative\",\"2006-Neutral\",\"2006-Positive\",\"2007-Negative\",\"2007-Neutral\",\"2007-Positive\",\"2008-Negative\",\"2008-Neutral\",\"2008-Positive\",\"2009-Negative\",\"2009-Neutral\",\"2009-Positive\",\"2010-Negative\",\"2010-Neutral\",\"2010-Positive\",\"2011-Negative\",\"2011-Neutral\",\"2011-Positive\",\"2012-Negative\",\"2012-Neutral\",\"2012-Positive\",\"2013-Negative\",\"2013-Neutral\",\"2013-Positive\",\"2014-Negative\",\"2014-Neutral\",\"2014-Positive\"]},\"yAxis\":[{},{\"opposite\":true}]});</script>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df1['average_ratings_in_year']=df1.apply(lambda x: str(x['year']) + '-' + x['ratings'],axis=1)\n",
    "df1=df1.set_index(df1['average_ratings_in_year'])\n",
    "display_charts(df1,y=['avg_rating','num_review'], secondary_y=['num_review'],kind='bar', title='Average Ratings & Review Count by Year',zoom='xy')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df5=df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div id=\"chart_idjVZvllPk\"</div>\n",
       "    <script type=\"text/javascript\">new Highcharts.Chart({\"legend\":{\"enabled\":true},\"series\":[{\"name\":\"num_review\",\"yAxis\":0,\"data\":[[\"1998-Positive\",4],[\"1999-Negative\",182],[\"1999-Neutral\",95],[\"1999-Positive\",935],[\"2000-Negative\",1226],[\"2000-Neutral\",698],[\"2000-Positive\",7290],[\"2001-Negative\",2676],[\"2001-Neutral\",1246],[\"2001-Positive\",10831],[\"2002-Negative\",3836],[\"2002-Neutral\",1591],[\"2002-Positive\",13221],[\"2003-Negative\",5401],[\"2003-Neutral\",1928],[\"2003-Positive\",15788],[\"2004-Negative\",8998],[\"2004-Neutral\",2770],[\"2004-Positive\",19477],[\"2005-Negative\",14800],[\"2005-Neutral\",5073],[\"2005-Positive\",36438],[\"2006-Negative\",20414],[\"2006-Neutral\",7577],[\"2006-Positive\",58668],[\"2007-Negative\",32892],[\"2007-Neutral\",15479],[\"2007-Positive\",145059],[\"2008-Negative\",41174],[\"2008-Neutral\",18597],[\"2008-Positive\",178750],[\"2009-Negative\",59024],[\"2009-Neutral\",26168],[\"2009-Positive\",240188],[\"2010-Negative\",91151],[\"2010-Neutral\",40515],[\"2010-Positive\",343960],[\"2011-Negative\",149924],[\"2011-Neutral\",64022],[\"2011-Positive\",569557],[\"2012-Negative\",224713],[\"2012-Neutral\",95822],[\"2012-Positive\",911138],[\"2013-Negative\",415671],[\"2013-Neutral\",216686],[\"2013-Positive\",1994225],[\"2014-Negative\",286005],[\"2014-Neutral\",134806],[\"2014-Positive\",1287793]]}],\"title\":{\"text\":\"# Reviews by Rating\"},\"chart\":{\"renderTo\":\"chart_idjVZvllPk\",\"type\":\"pie\"},\"xAxis\":{\"title\":{\"text\":\"average_ratings_in_year\"},\"categories\":[\"1998-Positive\",\"1999-Negative\",\"1999-Neutral\",\"1999-Positive\",\"2000-Negative\",\"2000-Neutral\",\"2000-Positive\",\"2001-Negative\",\"2001-Neutral\",\"2001-Positive\",\"2002-Negative\",\"2002-Neutral\",\"2002-Positive\",\"2003-Negative\",\"2003-Neutral\",\"2003-Positive\",\"2004-Negative\",\"2004-Neutral\",\"2004-Positive\",\"2005-Negative\",\"2005-Neutral\",\"2005-Positive\",\"2006-Negative\",\"2006-Neutral\",\"2006-Positive\",\"2007-Negative\",\"2007-Neutral\",\"2007-Positive\",\"2008-Negative\",\"2008-Neutral\",\"2008-Positive\",\"2009-Negative\",\"2009-Neutral\",\"2009-Positive\",\"2010-Negative\",\"2010-Neutral\",\"2010-Positive\",\"2011-Negative\",\"2011-Neutral\",\"2011-Positive\",\"2012-Negative\",\"2012-Neutral\",\"2012-Positive\",\"2013-Negative\",\"2013-Neutral\",\"2013-Positive\",\"2014-Negative\",\"2014-Neutral\",\"2014-Positive\"]},\"yAxis\":[{}]});</script>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df5=df5.set_index(df5['average_ratings_in_year'])\n",
    "display_charts(df5,y=['num_review'],kind='pie', title='# Reviews by Rating')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Why did reviews count dropped in 2014? Is there a seasonal trend for increase in reviews? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df3=df.groupby(['year','month'])['reviewerID'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df3=df3.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df3=df3.sort_values(['year','month'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df3['datekey']=df3.apply(lambda x: str(x['month'])+'-'+str(x['year']),axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df3=df3.set_index(df3['datekey'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div id=\"chart_idGkwjerRh\"</div>\n",
       "    <script type=\"text/javascript\">new Highcharts.Chart({\"legend\":{\"enabled\":true},\"series\":[{\"name\":\"num_review\",\"yAxis\":0,\"data\":[[\"12-1998\",4],[\"5-1999\",2],[\"6-1999\",17],[\"7-1999\",69],[\"8-1999\",41],[\"9-1999\",43],[\"10-1999\",166],[\"11-1999\",335],[\"12-1999\",539],[\"1-2000\",492],[\"2-2000\",366],[\"3-2000\",395],[\"4-2000\",466],[\"5-2000\",712],[\"6-2000\",1227],[\"7-2000\",878],[\"8-2000\",860],[\"9-2000\",618],[\"10-2000\",928],[\"11-2000\",938],[\"12-2000\",1334],[\"1-2001\",1165],[\"2-2001\",931],[\"3-2001\",910],[\"4-2001\",855],[\"5-2001\",1054],[\"6-2001\",1182],[\"7-2001\",1299],[\"8-2001\",1331],[\"9-2001\",1104],[\"10-2001\",1139],[\"11-2001\",1547],[\"12-2001\",2236],[\"1-2002\",1909],[\"2-2002\",1409],[\"3-2002\",1528],[\"4-2002\",1321],[\"5-2002\",1353],[\"6-2002\",1276],[\"7-2002\",1403],[\"8-2002\",1334],[\"9-2002\",1374],[\"10-2002\",1628],[\"11-2002\",1691],[\"12-2002\",2422],[\"1-2003\",2414],[\"2-2003\",1669],[\"3-2003\",1725],[\"4-2003\",1657],[\"5-2003\",1752],[\"6-2003\",1803],[\"7-2003\",1757],[\"8-2003\",1801],[\"9-2003\",1808],[\"10-2003\",1917],[\"11-2003\",2029],[\"12-2003\",2785],[\"1-2004\",2664],[\"2-2004\",2122],[\"3-2004\",2296],[\"4-2004\",2089],[\"5-2004\",1990],[\"6-2004\",2128],[\"7-2004\",2453],[\"8-2004\",2601],[\"9-2004\",2637],[\"10-2004\",2767],[\"11-2004\",3186],[\"12-2004\",4312],[\"1-2005\",4632],[\"2-2005\",3504],[\"3-2005\",3846],[\"4-2005\",3218],[\"5-2005\",3084],[\"6-2005\",3276],[\"7-2005\",4484],[\"8-2005\",7044],[\"9-2005\",7313],[\"10-2005\",5896],[\"11-2005\",4013],[\"12-2005\",6001],[\"1-2006\",7496],[\"2-2006\",7927],[\"3-2006\",10100],[\"4-2006\",4398],[\"5-2006\",4429],[\"6-2006\",5211],[\"7-2006\",7838],[\"8-2006\",8998],[\"9-2006\",4520],[\"10-2006\",4662],[\"11-2006\",12098],[\"12-2006\",8982],[\"1-2007\",30695],[\"2-2007\",12682],[\"3-2007\",16986],[\"4-2007\",9929],[\"5-2007\",17947],[\"6-2007\",12001],[\"7-2007\",14259],[\"8-2007\",14183],[\"9-2007\",14121],[\"10-2007\",13939],[\"11-2007\",14915],[\"12-2007\",21773],[\"1-2008\",25661],[\"2-2008\",19398],[\"3-2008\",17253],[\"4-2008\",18715],[\"5-2008\",18867],[\"6-2008\",18462],[\"7-2008\",18925],[\"8-2008\",18620],[\"9-2008\",18434],[\"10-2008\",18585],[\"11-2008\",18855],[\"12-2008\",26746],[\"1-2009\",37899],[\"2-2009\",27746],[\"3-2009\",25679],[\"4-2009\",24921],[\"5-2009\",24321],[\"6-2009\",25012],[\"7-2009\",20910],[\"8-2009\",22796],[\"9-2009\",29001],[\"10-2009\",25648],[\"11-2009\",26458],[\"12-2009\",34989],[\"1-2010\",38302],[\"2-2010\",39918],[\"3-2010\",35897],[\"4-2010\",31536],[\"5-2010\",32047],[\"6-2010\",31625],[\"7-2010\",37779],[\"8-2010\",39151],[\"9-2010\",42744],[\"10-2010\",42588],[\"11-2010\",44471],[\"12-2010\",59568],[\"1-2011\",68637],[\"2-2011\",54526],[\"3-2011\",58147],[\"4-2011\",55244],[\"5-2011\",53057],[\"6-2011\",55189],[\"7-2011\",59350],[\"8-2011\",64850],[\"9-2011\",68088],[\"10-2011\",66996],[\"11-2011\",80797],[\"12-2011\",98622],[\"1-2012\",109439],[\"2-2012\",83677],[\"3-2012\",79165],[\"4-2012\",73476],[\"5-2012\",74106],[\"6-2012\",75914],[\"7-2012\",84325],[\"8-2012\",86046],[\"9-2012\",87627],[\"10-2012\",95864],[\"11-2012\",130161],[\"12-2012\",251873],[\"1-2013\",295510],[\"2-2013\",223517],[\"3-2013\",224275],[\"4-2013\",201603],[\"5-2013\",201717],[\"6-2013\",192199],[\"7-2013\",204603],[\"8-2013\",214780],[\"9-2013\",187266],[\"10-2013\",205333],[\"11-2013\",198067],[\"12-2013\",277712],[\"1-2014\",337704],[\"2-2014\",257464],[\"3-2014\",262784],[\"4-2014\",226297],[\"5-2014\",217031],[\"6-2014\",220686],[\"7-2014\",186638]]}],\"title\":{\"text\":\"# Reviews by Year - Electronics Category\"},\"chart\":{\"renderTo\":\"chart_idGkwjerRh\",\"zoomType\":\"xy\"},\"xAxis\":{\"title\":{\"text\":\"datekey\"},\"categories\":[\"12-1998\",\"5-1999\",\"6-1999\",\"7-1999\",\"8-1999\",\"9-1999\",\"10-1999\",\"11-1999\",\"12-1999\",\"1-2000\",\"2-2000\",\"3-2000\",\"4-2000\",\"5-2000\",\"6-2000\",\"7-2000\",\"8-2000\",\"9-2000\",\"10-2000\",\"11-2000\",\"12-2000\",\"1-2001\",\"2-2001\",\"3-2001\",\"4-2001\",\"5-2001\",\"6-2001\",\"7-2001\",\"8-2001\",\"9-2001\",\"10-2001\",\"11-2001\",\"12-2001\",\"1-2002\",\"2-2002\",\"3-2002\",\"4-2002\",\"5-2002\",\"6-2002\",\"7-2002\",\"8-2002\",\"9-2002\",\"10-2002\",\"11-2002\",\"12-2002\",\"1-2003\",\"2-2003\",\"3-2003\",\"4-2003\",\"5-2003\",\"6-2003\",\"7-2003\",\"8-2003\",\"9-2003\",\"10-2003\",\"11-2003\",\"12-2003\",\"1-2004\",\"2-2004\",\"3-2004\",\"4-2004\",\"5-2004\",\"6-2004\",\"7-2004\",\"8-2004\",\"9-2004\",\"10-2004\",\"11-2004\",\"12-2004\",\"1-2005\",\"2-2005\",\"3-2005\",\"4-2005\",\"5-2005\",\"6-2005\",\"7-2005\",\"8-2005\",\"9-2005\",\"10-2005\",\"11-2005\",\"12-2005\",\"1-2006\",\"2-2006\",\"3-2006\",\"4-2006\",\"5-2006\",\"6-2006\",\"7-2006\",\"8-2006\",\"9-2006\",\"10-2006\",\"11-2006\",\"12-2006\",\"1-2007\",\"2-2007\",\"3-2007\",\"4-2007\",\"5-2007\",\"6-2007\",\"7-2007\",\"8-2007\",\"9-2007\",\"10-2007\",\"11-2007\",\"12-2007\",\"1-2008\",\"2-2008\",\"3-2008\",\"4-2008\",\"5-2008\",\"6-2008\",\"7-2008\",\"8-2008\",\"9-2008\",\"10-2008\",\"11-2008\",\"12-2008\",\"1-2009\",\"2-2009\",\"3-2009\",\"4-2009\",\"5-2009\",\"6-2009\",\"7-2009\",\"8-2009\",\"9-2009\",\"10-2009\",\"11-2009\",\"12-2009\",\"1-2010\",\"2-2010\",\"3-2010\",\"4-2010\",\"5-2010\",\"6-2010\",\"7-2010\",\"8-2010\",\"9-2010\",\"10-2010\",\"11-2010\",\"12-2010\",\"1-2011\",\"2-2011\",\"3-2011\",\"4-2011\",\"5-2011\",\"6-2011\",\"7-2011\",\"8-2011\",\"9-2011\",\"10-2011\",\"11-2011\",\"12-2011\",\"1-2012\",\"2-2012\",\"3-2012\",\"4-2012\",\"5-2012\",\"6-2012\",\"7-2012\",\"8-2012\",\"9-2012\",\"10-2012\",\"11-2012\",\"12-2012\",\"1-2013\",\"2-2013\",\"3-2013\",\"4-2013\",\"5-2013\",\"6-2013\",\"7-2013\",\"8-2013\",\"9-2013\",\"10-2013\",\"11-2013\",\"12-2013\",\"1-2014\",\"2-2014\",\"3-2014\",\"4-2014\",\"5-2014\",\"6-2014\",\"7-2014\"]},\"yAxis\":[{}]});</script>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df3=df3.rename(columns = {'reviewerID':'num_review'})\n",
    "display_charts(df3,y=['num_review'], title=\"# Reviews by Year - Electronics Category\",zoom=\"xy\")"
   ]
  }
 ],
 "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}