Online Review Import.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import requests\n",
    "import pandas as pd\n",
    "from pandas.io.json import json_normalize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#API key is removed in this file\n",
    "headers = {'x-api-key': ''}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "review_sites = ['BestBuy/en-US', 'Amazon/en-US', 'Amazon/de-DE', 'Amazon/en-GB', 'Amazon/jp-JP']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://customerreviewtracking-prod-we.azurewebsites.net/api/reviews/jabra/BestBuy/en-US\n",
      "200\n",
      "(23021, 9)\n",
      "https://customerreviewtracking-prod-we.azurewebsites.net/api/reviews/jabra/Amazon/en-US\n",
      "200\n",
      "(38447, 9)\n",
      "https://customerreviewtracking-prod-we.azurewebsites.net/api/reviews/jabra/Amazon/de-DE\n",
      "200\n",
      "(41148, 9)\n",
      "https://customerreviewtracking-prod-we.azurewebsites.net/api/reviews/jabra/Amazon/en-GB\n",
      "200\n",
      "(44829, 9)\n",
      "https://customerreviewtracking-prod-we.azurewebsites.net/api/reviews/jabra/Amazon/jp-JP\n",
      "200\n",
      "(45139, 9)\n"
     ]
    }
   ],
   "source": [
    "for review_site in review_sites:\n",
    "    url = 'https://customerreviewtracking-prod-we.azurewebsites.net/api/reviews/jabra/'+review_site\n",
    "    print (url)\n",
    "    response = requests.get(url, headers=headers)\n",
    "    print (response.status_code)\n",
    "    review_data = json_normalize(response.json(), 'reviews', ['productName'])\n",
    "    review_data['review_site'] = review_site\n",
    "    df = df.append(review_data, ignore_index=True)\n",
    "    print(df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>body</th>\n",
       "      <th>isVerifiedPurchase</th>\n",
       "      <th>rating</th>\n",
       "      <th>reviewId</th>\n",
       "      <th>reviewUrl</th>\n",
       "      <th>time</th>\n",
       "      <th>title</th>\n",
       "      <th>productName</th>\n",
       "      <th>review_site</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>I have run the gambit of Bluetooth headsets.\\n...</td>\n",
       "      <td>True</td>\n",
       "      <td>5</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>2018-05-13T00:00:00+00:00</td>\n",
       "      <td>I tried the premium Bose and Sony, jabra won</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td>BestBuy/en-US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>I liked almost everything about the headphones...</td>\n",
       "      <td>True</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>2018-05-16T00:00:00+00:00</td>\n",
       "      <td>Very good headset but noise canceling isn't great</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td>BestBuy/en-US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>I wasn't all that impressed with Jabra as a he...</td>\n",
       "      <td>False</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>2018-05-18T00:00:00+00:00</td>\n",
       "      <td>Another great headset from Jabra</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td>BestBuy/en-US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>I own a bunch of headphones and I can honestly...</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>2018-05-19T00:00:00+00:00</td>\n",
       "      <td>Outstanding Sound!</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td>BestBuy/en-US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Man, these headphones are a force to be reckon...</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>2018-05-19T00:00:00+00:00</td>\n",
       "      <td>Triple Threat Action!!!</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td>BestBuy/en-US</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                body  isVerifiedPurchase  \\\n",
       "0  I have run the gambit of Bluetooth headsets.\\n...                True   \n",
       "1  I liked almost everything about the headphones...                True   \n",
       "2  I wasn't all that impressed with Jabra as a he...               False   \n",
       "3  I own a bunch of headphones and I can honestly...               False   \n",
       "4  Man, these headphones are a force to be reckon...               False   \n",
       "\n",
       "   rating reviewId reviewUrl                       time  \\\n",
       "0       5               None  2018-05-13T00:00:00+00:00   \n",
       "1       4               None  2018-05-16T00:00:00+00:00   \n",
       "2       4               None  2018-05-18T00:00:00+00:00   \n",
       "3       5               None  2018-05-19T00:00:00+00:00   \n",
       "4       5               None  2018-05-19T00:00:00+00:00   \n",
       "\n",
       "                                               title productName  \\\n",
       "0       I tried the premium Bose and Sony, jabra won   Elite 65e   \n",
       "1  Very good headset but noise canceling isn't great   Elite 65e   \n",
       "2                   Another great headset from Jabra   Elite 65e   \n",
       "3                                 Outstanding Sound!   Elite 65e   \n",
       "4                            Triple Threat Action!!!   Elite 65e   \n",
       "\n",
       "     review_site  \n",
       "0  BestBuy/en-US  \n",
       "1  BestBuy/en-US  \n",
       "2  BestBuy/en-US  \n",
       "3  BestBuy/en-US  \n",
       "4  BestBuy/en-US  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sort = df.rename(columns={'body':'review_text', \n",
    "                             'isVerifiedPurchase':'verified_purchase', \n",
    "                             'rating':'review_rating',\n",
    "                             'reviewId':'review_id', \n",
    "                             'reviewUrl':'review_url', \n",
    "                             'time':'review_date',\n",
    "                             'title':'review_title',\n",
    "                             'productName':'product_name'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>review_text</th>\n",
       "      <th>verified_purchase</th>\n",
       "      <th>review_rating</th>\n",
       "      <th>review_id</th>\n",
       "      <th>review_url</th>\n",
       "      <th>review_date</th>\n",
       "      <th>review_title</th>\n",
       "      <th>product_name</th>\n",
       "      <th>review_site</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>I have run the gambit of Bluetooth headsets.\\n...</td>\n",
       "      <td>True</td>\n",
       "      <td>5</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>2018-05-13T00:00:00+00:00</td>\n",
       "      <td>I tried the premium Bose and Sony, jabra won</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td>BestBuy/en-US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>I liked almost everything about the headphones...</td>\n",
       "      <td>True</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>2018-05-16T00:00:00+00:00</td>\n",
       "      <td>Very good headset but noise canceling isn't great</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td>BestBuy/en-US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>I wasn't all that impressed with Jabra as a he...</td>\n",
       "      <td>False</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>2018-05-18T00:00:00+00:00</td>\n",
       "      <td>Another great headset from Jabra</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td>BestBuy/en-US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>I own a bunch of headphones and I can honestly...</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>2018-05-19T00:00:00+00:00</td>\n",
       "      <td>Outstanding Sound!</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td>BestBuy/en-US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Man, these headphones are a force to be reckon...</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>2018-05-19T00:00:00+00:00</td>\n",
       "      <td>Triple Threat Action!!!</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td>BestBuy/en-US</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         review_text  verified_purchase  \\\n",
       "0  I have run the gambit of Bluetooth headsets.\\n...               True   \n",
       "1  I liked almost everything about the headphones...               True   \n",
       "2  I wasn't all that impressed with Jabra as a he...              False   \n",
       "3  I own a bunch of headphones and I can honestly...              False   \n",
       "4  Man, these headphones are a force to be reckon...              False   \n",
       "\n",
       "   review_rating review_id review_url                review_date  \\\n",
       "0              5                 None  2018-05-13T00:00:00+00:00   \n",
       "1              4                 None  2018-05-16T00:00:00+00:00   \n",
       "2              4                 None  2018-05-18T00:00:00+00:00   \n",
       "3              5                 None  2018-05-19T00:00:00+00:00   \n",
       "4              5                 None  2018-05-19T00:00:00+00:00   \n",
       "\n",
       "                                        review_title product_name  \\\n",
       "0       I tried the premium Bose and Sony, jabra won    Elite 65e   \n",
       "1  Very good headset but noise canceling isn't great    Elite 65e   \n",
       "2                   Another great headset from Jabra    Elite 65e   \n",
       "3                                 Outstanding Sound!    Elite 65e   \n",
       "4                            Triple Threat Action!!!    Elite 65e   \n",
       "\n",
       "     review_site  \n",
       "0  BestBuy/en-US  \n",
       "1  BestBuy/en-US  \n",
       "2  BestBuy/en-US  \n",
       "3  BestBuy/en-US  \n",
       "4  BestBuy/en-US  "
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sort.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sort['review_date'] = df_sort['review_date'].astype('datetime64[ns]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>review_text</th>\n",
       "      <th>verified_purchase</th>\n",
       "      <th>review_rating</th>\n",
       "      <th>review_id</th>\n",
       "      <th>review_url</th>\n",
       "      <th>review_date</th>\n",
       "      <th>review_title</th>\n",
       "      <th>product_name</th>\n",
       "      <th>review_site</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>I have run the gambit of Bluetooth headsets.\\n...</td>\n",
       "      <td>True</td>\n",
       "      <td>5</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>2018-05-13</td>\n",
       "      <td>I tried the premium Bose and Sony, jabra won</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td>BestBuy/en-US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>I liked almost everything about the headphones...</td>\n",
       "      <td>True</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>2018-05-16</td>\n",
       "      <td>Very good headset but noise canceling isn't great</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td>BestBuy/en-US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>I wasn't all that impressed with Jabra as a he...</td>\n",
       "      <td>False</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>2018-05-18</td>\n",
       "      <td>Another great headset from Jabra</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td>BestBuy/en-US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>I own a bunch of headphones and I can honestly...</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>2018-05-19</td>\n",
       "      <td>Outstanding Sound!</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td>BestBuy/en-US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Man, these headphones are a force to be reckon...</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>2018-05-19</td>\n",
       "      <td>Triple Threat Action!!!</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td>BestBuy/en-US</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         review_text  verified_purchase  \\\n",
       "0  I have run the gambit of Bluetooth headsets.\\n...               True   \n",
       "1  I liked almost everything about the headphones...               True   \n",
       "2  I wasn't all that impressed with Jabra as a he...              False   \n",
       "3  I own a bunch of headphones and I can honestly...              False   \n",
       "4  Man, these headphones are a force to be reckon...              False   \n",
       "\n",
       "   review_rating review_id review_url review_date  \\\n",
       "0              5                 None  2018-05-13   \n",
       "1              4                 None  2018-05-16   \n",
       "2              4                 None  2018-05-18   \n",
       "3              5                 None  2018-05-19   \n",
       "4              5                 None  2018-05-19   \n",
       "\n",
       "                                        review_title product_name  \\\n",
       "0       I tried the premium Bose and Sony, jabra won    Elite 65e   \n",
       "1  Very good headset but noise canceling isn't great    Elite 65e   \n",
       "2                   Another great headset from Jabra    Elite 65e   \n",
       "3                                 Outstanding Sound!    Elite 65e   \n",
       "4                            Triple Threat Action!!!    Elite 65e   \n",
       "\n",
       "     review_site  \n",
       "0  BestBuy/en-US  \n",
       "1  BestBuy/en-US  \n",
       "2  BestBuy/en-US  \n",
       "3  BestBuy/en-US  \n",
       "4  BestBuy/en-US  "
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sort.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sort = df_sort[['review_site','product_name','review_id','review_date','review_rating','review_title','review_text','verified_purchase','review_url']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>review_site</th>\n",
       "      <th>product_name</th>\n",
       "      <th>review_id</th>\n",
       "      <th>review_date</th>\n",
       "      <th>review_rating</th>\n",
       "      <th>review_title</th>\n",
       "      <th>review_text</th>\n",
       "      <th>verified_purchase</th>\n",
       "      <th>review_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BestBuy/en-US</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td></td>\n",
       "      <td>2018-05-13</td>\n",
       "      <td>5</td>\n",
       "      <td>I tried the premium Bose and Sony, jabra won</td>\n",
       "      <td>I have run the gambit of Bluetooth headsets.\\n...</td>\n",
       "      <td>True</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BestBuy/en-US</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td></td>\n",
       "      <td>2018-05-16</td>\n",
       "      <td>4</td>\n",
       "      <td>Very good headset but noise canceling isn't great</td>\n",
       "      <td>I liked almost everything about the headphones...</td>\n",
       "      <td>True</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BestBuy/en-US</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td></td>\n",
       "      <td>2018-05-18</td>\n",
       "      <td>4</td>\n",
       "      <td>Another great headset from Jabra</td>\n",
       "      <td>I wasn't all that impressed with Jabra as a he...</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BestBuy/en-US</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td></td>\n",
       "      <td>2018-05-19</td>\n",
       "      <td>5</td>\n",
       "      <td>Outstanding Sound!</td>\n",
       "      <td>I own a bunch of headphones and I can honestly...</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BestBuy/en-US</td>\n",
       "      <td>Elite 65e</td>\n",
       "      <td></td>\n",
       "      <td>2018-05-19</td>\n",
       "      <td>5</td>\n",
       "      <td>Triple Threat Action!!!</td>\n",
       "      <td>Man, these headphones are a force to be reckon...</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     review_site product_name review_id review_date  review_rating  \\\n",
       "0  BestBuy/en-US    Elite 65e            2018-05-13              5   \n",
       "1  BestBuy/en-US    Elite 65e            2018-05-16              4   \n",
       "2  BestBuy/en-US    Elite 65e            2018-05-18              4   \n",
       "3  BestBuy/en-US    Elite 65e            2018-05-19              5   \n",
       "4  BestBuy/en-US    Elite 65e            2018-05-19              5   \n",
       "\n",
       "                                        review_title  \\\n",
       "0       I tried the premium Bose and Sony, jabra won   \n",
       "1  Very good headset but noise canceling isn't great   \n",
       "2                   Another great headset from Jabra   \n",
       "3                                 Outstanding Sound!   \n",
       "4                            Triple Threat Action!!!   \n",
       "\n",
       "                                         review_text  verified_purchase  \\\n",
       "0  I have run the gambit of Bluetooth headsets.\\n...               True   \n",
       "1  I liked almost everything about the headphones...               True   \n",
       "2  I wasn't all that impressed with Jabra as a he...              False   \n",
       "3  I own a bunch of headphones and I can honestly...              False   \n",
       "4  Man, these headphones are a force to be reckon...              False   \n",
       "\n",
       "  review_url  \n",
       "0       None  \n",
       "1       None  \n",
       "2       None  \n",
       "3       None  \n",
       "4       None  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sort.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sort.sort_values(by=['review_site','product_name','review_date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>review_site</th>\n",
       "      <th>product_name</th>\n",
       "      <th>review_id</th>\n",
       "      <th>review_date</th>\n",
       "      <th>review_rating</th>\n",
       "      <th>review_title</th>\n",
       "      <th>review_text</th>\n",
       "      <th>verified_purchase</th>\n",
       "      <th>review_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>40223</th>\n",
       "      <td>Amazon/de-DE</td>\n",
       "      <td>BT2045 - 1</td>\n",
       "      <td>R2I0GTBF8TA4TT</td>\n",
       "      <td>2011-11-06</td>\n",
       "      <td>3</td>\n",
       "      <td>Funktioniert zwar aber man versteht mich nicht</td>\n",
       "      <td>Neulich hab ich mir dieses Headset gekauft, we...</td>\n",
       "      <td>False</td>\n",
       "      <td>https://www.amazon.de/gp/customer-reviews/R2I0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40224</th>\n",
       "      <td>Amazon/de-DE</td>\n",
       "      <td>BT2045 - 1</td>\n",
       "      <td>R3QP4D2DYWD7XI</td>\n",
       "      <td>2012-09-13</td>\n",
       "      <td>1</td>\n",
       "      <td>BT2045</td>\n",
       "      <td>Das vorhandene Headset filtert keine Umgebungs...</td>\n",
       "      <td>False</td>\n",
       "      <td>https://www.amazon.de/gp/customer-reviews/R3QP...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40225</th>\n",
       "      <td>Amazon/de-DE</td>\n",
       "      <td>BT2045 - 1</td>\n",
       "      <td>R2YGIN0C81YBLI</td>\n",
       "      <td>2012-12-22</td>\n",
       "      <td>5</td>\n",
       "      <td>Wunderbar</td>\n",
       "      <td>Hatte dieses Headset ursprünglich für mein HTC...</td>\n",
       "      <td>True</td>\n",
       "      <td>https://www.amazon.de/gp/customer-reviews/R2YG...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40226</th>\n",
       "      <td>Amazon/de-DE</td>\n",
       "      <td>BT2045 - 1</td>\n",
       "      <td>R1WKO4WPHFAP54</td>\n",
       "      <td>2013-03-15</td>\n",
       "      <td>4</td>\n",
       "      <td>Freie Hände, sichere Fahrt!</td>\n",
       "      <td>Sehr praktisch! Ich hasse es, wenn Autofahre a...</td>\n",
       "      <td>True</td>\n",
       "      <td>https://www.amazon.de/gp/customer-reviews/R1WK...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40227</th>\n",
       "      <td>Amazon/de-DE</td>\n",
       "      <td>BT2045 - 1</td>\n",
       "      <td>R3JYR34ROA64D5</td>\n",
       "      <td>2013-04-13</td>\n",
       "      <td>4</td>\n",
       "      <td>Günstig und gut</td>\n",
       "      <td>Für den Preis ein wirklich gutes Headset, das ...</td>\n",
       "      <td>False</td>\n",
       "      <td>https://www.amazon.de/gp/customer-reviews/R3JY...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        review_site product_name       review_id review_date  review_rating  \\\n",
       "40223  Amazon/de-DE   BT2045 - 1  R2I0GTBF8TA4TT  2011-11-06              3   \n",
       "40224  Amazon/de-DE   BT2045 - 1  R3QP4D2DYWD7XI  2012-09-13              1   \n",
       "40225  Amazon/de-DE   BT2045 - 1  R2YGIN0C81YBLI  2012-12-22              5   \n",
       "40226  Amazon/de-DE   BT2045 - 1  R1WKO4WPHFAP54  2013-03-15              4   \n",
       "40227  Amazon/de-DE   BT2045 - 1  R3JYR34ROA64D5  2013-04-13              4   \n",
       "\n",
       "                                         review_title  \\\n",
       "40223  Funktioniert zwar aber man versteht mich nicht   \n",
       "40224                                          BT2045   \n",
       "40225                                       Wunderbar   \n",
       "40226                     Freie Hände, sichere Fahrt!   \n",
       "40227                                 Günstig und gut   \n",
       "\n",
       "                                             review_text  verified_purchase  \\\n",
       "40223  Neulich hab ich mir dieses Headset gekauft, we...              False   \n",
       "40224  Das vorhandene Headset filtert keine Umgebungs...              False   \n",
       "40225  Hatte dieses Headset ursprünglich für mein HTC...               True   \n",
       "40226  Sehr praktisch! Ich hasse es, wenn Autofahre a...               True   \n",
       "40227  Für den Preis ein wirklich gutes Headset, das ...              False   \n",
       "\n",
       "                                              review_url  \n",
       "40223  https://www.amazon.de/gp/customer-reviews/R2I0...  \n",
       "40224  https://www.amazon.de/gp/customer-reviews/R3QP...  \n",
       "40225  https://www.amazon.de/gp/customer-reviews/R2YG...  \n",
       "40226  https://www.amazon.de/gp/customer-reviews/R1WK...  \n",
       "40227  https://www.amazon.de/gp/customer-reviews/R3JY...  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sort.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>review_site</th>\n",
       "      <th>product_name</th>\n",
       "      <th>review_id</th>\n",
       "      <th>review_date</th>\n",
       "      <th>review_rating</th>\n",
       "      <th>review_title</th>\n",
       "      <th>review_text</th>\n",
       "      <th>verified_purchase</th>\n",
       "      <th>review_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20386</th>\n",
       "      <td>BestBuy/en-US</td>\n",
       "      <td>Style</td>\n",
       "      <td></td>\n",
       "      <td>2018-10-05</td>\n",
       "      <td>5</td>\n",
       "      <td>Easy to use</td>\n",
       "      <td>Great Bluetooth device for easy hands free ope...</td>\n",
       "      <td>True</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19527</th>\n",
       "      <td>BestBuy/en-US</td>\n",
       "      <td>Style</td>\n",
       "      <td></td>\n",
       "      <td>2018-10-06</td>\n",
       "      <td>4</td>\n",
       "      <td>Very nice ear piece for me</td>\n",
       "      <td>This ear piece is nice and clear. Easy to use ...</td>\n",
       "      <td>True</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20390</th>\n",
       "      <td>BestBuy/en-US</td>\n",
       "      <td>Style</td>\n",
       "      <td></td>\n",
       "      <td>2018-10-06</td>\n",
       "      <td>4</td>\n",
       "      <td>Very nice ear piece for me</td>\n",
       "      <td>This ear piece is nice and clear. Easy to use ...</td>\n",
       "      <td>True</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19530</th>\n",
       "      <td>BestBuy/en-US</td>\n",
       "      <td>Style</td>\n",
       "      <td></td>\n",
       "      <td>2018-10-12</td>\n",
       "      <td>5</td>\n",
       "      <td>Great, especially for the price</td>\n",
       "      <td>Nice ear piece. Easy to set up, easy to use. C...</td>\n",
       "      <td>True</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20393</th>\n",
       "      <td>BestBuy/en-US</td>\n",
       "      <td>Style</td>\n",
       "      <td></td>\n",
       "      <td>2018-10-12</td>\n",
       "      <td>5</td>\n",
       "      <td>Great, especially for the price</td>\n",
       "      <td>Nice ear piece. Easy to set up, easy to use. C...</td>\n",
       "      <td>True</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         review_site product_name review_id review_date  review_rating  \\\n",
       "20386  BestBuy/en-US        Style            2018-10-05              5   \n",
       "19527  BestBuy/en-US        Style            2018-10-06              4   \n",
       "20390  BestBuy/en-US        Style            2018-10-06              4   \n",
       "19530  BestBuy/en-US        Style            2018-10-12              5   \n",
       "20393  BestBuy/en-US        Style            2018-10-12              5   \n",
       "\n",
       "                          review_title  \\\n",
       "20386                      Easy to use   \n",
       "19527       Very nice ear piece for me   \n",
       "20390       Very nice ear piece for me   \n",
       "19530  Great, especially for the price   \n",
       "20393  Great, especially for the price   \n",
       "\n",
       "                                             review_text  verified_purchase  \\\n",
       "20386  Great Bluetooth device for easy hands free ope...               True   \n",
       "19527  This ear piece is nice and clear. Easy to use ...               True   \n",
       "20390  This ear piece is nice and clear. Easy to use ...               True   \n",
       "19530  Nice ear piece. Easy to set up, easy to use. C...               True   \n",
       "20393  Nice ear piece. Easy to set up, easy to use. C...               True   \n",
       "\n",
       "      review_url  \n",
       "20386       None  \n",
       "19527       None  \n",
       "20390       None  \n",
       "19530       None  \n",
       "20393       None  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sort.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sort.to_excel('review_extract.xlsx',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(45139, 9)"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sort.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [default]",
   "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}