1_create_features.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import libraries and initial dataset\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import time\n",
    "import sys\n",
    "try:\n",
    "    sys.getwindowsversion()\n",
    "except AttributeError:\n",
    "    isWindows = False\n",
    "else:\n",
    "    isWindows = True\n",
    "#use more RAM\n",
    "if isWindows:\n",
    "    import win32api,win32process,win32con\n",
    "    pid = win32api.GetCurrentProcessId()\n",
    "    handle = win32api.OpenProcess(win32con.PROCESS_ALL_ACCESS, True, pid)\n",
    "    win32process.SetPriorityClass(handle, win32process.HIGH_PRIORITY_CLASS)\n",
    "from datetime import datetime\n",
    "import nltk\n",
    "# nltk.download('stopwords')\n",
    "from nltk.corpus import stopwords\n",
    "stop = stopwords.words('english')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'C:\\\\Users\\\\Yao\\\\Google Drive\\\\capstone'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "regular_reviews = pd.read_csv('data/reg_reviews_full_NLP.csv')\n",
    "not_recommended_reviews= pd.read_csv('data/not_reviews_full_NLP.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combine the datasets\n",
    "\n",
    "New feature, 'regular', is added to distinguish regular review from not recommended reviews\n",
    "\n",
    "- 1 represent regular reviews\n",
    "- 0 represent not recommended reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "regular_reviews['regular'] = 1\n",
    "not_recommended_reviews['regular'] = 0\n",
    "\n",
    "#I'm trying to keep some more data for analysis, esp the location and date\n",
    "reviews = pd.concat([regular_reviews,not_recommended_reviews], join=\"inner\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Drop missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "len_before 257587 len_after 251477 diff 6110\n"
     ]
    }
   ],
   "source": [
    "len_before = len(reviews)\n",
    "reviews = reviews.replace(-1, np.nan).replace('-1', np.nan).dropna()\n",
    "len_after = len(reviews)\n",
    "print('len_before', len_before, 'len_after', len_after, 'diff', len_before-len_after)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# testing, remove duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# doesnt matter...very little difference after equal balancing,\n",
    "# diff of 12 rows elimnated at then end and also reduces r2 slightly...even though a lot is removed initially\n",
    "\n",
    "# len_before = len(reviews)\n",
    "# reviews=reviews.drop_duplicates()\n",
    "# len_after = len(reviews)\n",
    "# print('len_before', len_before, 'len_after', len_after, 'diff', len_before-len_after)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <span style=\"color:blue\"> Data cleaning\n",
    "\n",
    "- converted sentiment frequency count into frequency ratio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Replace boolean to numerical value for 'has_photo'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "reviews['has_photo'] = reviews['has_photo'].astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Remove Sfreq 0-5, perhaps if we make extreme values for sentiment\n",
    "\n",
    "which we found these features are less significant"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#add features if very postive or very negative before dropping, similar to extreme feature\n",
    "reviews['Text Very Negative'] = reviews['Sfreq0']/reviews['numSentence']\n",
    "reviews['Text Negative'] = reviews['Sfreq1']/reviews['numSentence']\n",
    "reviews['Text Neutral'] = reviews['Sfreq2']/reviews['numSentence']\n",
    "reviews['Text Positive'] = reviews['Sfreq3']/reviews['numSentence']\n",
    "reviews['Text Very Positive'] = reviews['Sfreq4']/reviews['numSentence']\n",
    "\n",
    "reviews.drop(columns=['totSentiment', 'avgSentiment'], inplace=True)\n",
    "\n",
    "reviews['totSentiment'] = reviews['Sfreq0']+reviews['Sfreq1']*2+reviews['Sfreq2']*3+reviews['Sfreq3']*4+reviews['Sfreq4']*5\n",
    "reviews['avgSentiment'] = reviews['totSentiment']/reviews['numSentence']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "reviews.drop(columns=['Sfreq5','user_name', 'Sfreq0','Sfreq1','Sfreq2','Sfreq3','Sfreq4'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "reviews.to_csv('data/reviewsBind.csv', index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <span style=\"color:blue\"> New Features\n",
    "## -  'extreme' : \n",
    " - 1 for rating either 1 or 5,   \n",
    " - 2 for the rest rating values\n",
    " \n",
    "## - 'storeReviewTot' : \n",
    "- total number of sampled reviews for each restaurant\n",
    "\n",
    "## - 'storeReviewRatio' : \n",
    "- For each restaurant review, the ratio of not-recommended/regular reviews"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 'extreme' feature where rating == 1|5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#doesn't contribute, rating and rating diff already shows a good picture \n",
    "#reviews['Rating_Extreme'] = (reviews['rating'].isin([1,5])).astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Date has a hidden feature to show if the user updated his review, extract\n",
    "\n",
    "make a new feature for updated review"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#there is a hidden feature in the date where if an user updates his post, it says the date and 'Updated_review', create extra feature\n",
    "\n",
    "reviews.loc[reviews['date'].str.contains('Updated review'), 'Edited_Review_Bool'] = 1\n",
    "#add 0s if not updated\n",
    "reviews['Edited_Review_Bool'].fillna(0, inplace=True)\n",
    "#remove extra formating\n",
    "reviews['date'] = reviews['date'].map(lambda x: x.rstrip('Updated review').rstrip())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def compare_dates(date):\n",
    "    date_format = '%m/%d/%Y'\n",
    "    current_date = datetime.strptime(date, date_format)  \n",
    "    start = datetime(2004,10,1)\n",
    "    diff = current_date - start\n",
    "    return diff.days\n",
    "\n",
    "#apply this function to your pandas dataframe\n",
    "reviews['Days_Since_10_2004'] = reviews['date'].apply(compare_dates)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# remove stopwords and compare word count of reviews\n",
    "\n",
    "ETA 1-3 min"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'00:00:55'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#remove returns and newlines from review\n",
    "reviews['review'] = reviews['review'].replace('\\n',' ', regex=True).replace('\\r',' ', regex=True)\n",
    "\n",
    "#remove stopwords\n",
    "start = time.time()\n",
    "reviews['review_no_stopwords'] = reviews['review'].apply(lambda x: ' '.join([word for word in x.split() if word not in (stop)]))\n",
    "time.strftime(\"%H:%M:%S\", time.gmtime(time.time() - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#count words of reviews and ratio of no stop words, to prevent colinearity\n",
    "reviews['Review_Words'] = reviews['review'].str.split().str.len()\n",
    "reviews['Review_Words_No_Stopwords'] = reviews['review_no_stopwords'].str.split().str.len()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Export reviews for later libraries/API\n",
    "\n",
    "TODO add indexing row for later merge, as necessary from other NLP API\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#make new dataframe for just reviews indexed\n",
    "\n",
    "reviewsOnly = reviews[['review','review_no_stopwords']]\n",
    "# reviewsOnly.to_csv('data/1_reviewsOnly.csv', index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "reviews.drop(columns=['date','review','review_no_stopwords'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 'storeReviewTot' feature total number of reviews for each restaurantID is counted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "restaurants = pd.read_csv('data/restaurants.csv').set_index('Unnamed: 0')\n",
    "reviews = reviews.set_index('restaurant_id').join(restaurants, rsuffix='_restaurant')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ratio of recommended and not recommended per restraurant"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "regularMeanByID = reviews.groupby(reviews.index)['regular'].mean()\n",
    "df_regularMeanByID = pd.DataFrame({'restaurant_id': regularMeanByID.index, 'regularRatio': regularMeanByID})\n",
    "reviews['restaurant_id'] =  reviews.index\n",
    "reviews = reviews.merge(df_regularMeanByID, left_on = 'restaurant_id', right_on = 'restaurant_id', how = 'outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Make word counts for address, link, and name of restaurant\n",
    "\n",
    "just to see if it matters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "#remove returns and newlines from address and phone number\n",
    "reviews['address'] = reviews['address'].replace('\\n',' ', regex=True).replace('\\r',' ', regex=True)\n",
    "reviews['address'] = reviews['address'].str.split('(').str[0]\n",
    "\n",
    "#count words of address and name for restaurant and drop them, can't drop address yet b/c coordinates later\n",
    "reviews['Rest_Address_Words'] = reviews['address'].str.split().str.len()\n",
    "reviews['Rest_Name_Words'] = reviews['restaurant_name'].str.split().str.len()\n",
    "\n",
    "reviews.drop(columns=['restaurant_link','restaurant_name'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create df of restaurant locations for coordinates\n",
    "\n",
    "rest_addresses = pd.DataFrame()\n",
    "rest_addresses['address'] = reviews['address']\n",
    "rest_addresses = rest_addresses.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rest_addresses.to_csv('1_rest_address.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create df of user locations for coordinates\n",
    "\n",
    "user_addresses = pd.DataFrame()\n",
    "user_addresses['localtion']=reviews['localtion']\n",
    "user_addresses = user_addresses.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# user_addresses.to_csv('1_user_address.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Write result\n",
    "\n",
    "note, 7 restaurants observations are not rated so this is why we remove NAs again...debatable...but it's only 7 of 251696"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "len_before 251477 len_after 251427 diff 50\n"
     ]
    }
   ],
   "source": [
    "len_before = len(reviews)\n",
    "reviews = reviews.replace(-1, np.nan).replace('-1', np.nan).dropna()\n",
    "len_after = len(reviews)\n",
    "print('len_before', len_before, 'len_after', len_after, 'diff', len_before-len_after)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# find diff between user and restaurant rating\n",
    "drop restaurant rating, the diff can be -1, so that is why we add a feature and drop restaurant rating after cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "reviews['User_Rating_Diff'] = reviews['rating']-reviews['rating_restaurant']\n",
    "reviews['Sentiment To Average Rating'] = reviews['avgSentiment']-reviews['rating_restaurant']\n",
    "reviews['Sentiment To User Rating'] = reviews['avgSentiment']-reviews['rating']\n",
    "reviews.drop(columns=['rating_restaurant'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "reviews.to_csv('data/reviews.csv', index=False)"
   ]
  }
 ],
 "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}