Build relevant yelp relational database for Neo4j.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <center> XOKind - Machine Learning/Data Science Intern Interview <center>\n",
    "## <center> Yelp rating predictions <center>\n",
    "### <center> Traditional Machine learning Vs Graph Machine Learning <center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Creating relational database that will be convered into graph database using Neo4j\n",
    "- Users.json\n",
    "- Restaurants.json\n",
    "- Categories.json\n",
    "- User-To-Restaurant.json\n",
    "- Restaurant-To-Category.json \n",
    "\n",
    "with all the relevant node and edge properties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "business_json_path = 'dataset/business.json'\n",
    "review_json_path = 'dataset/review.json'\n",
    "user_json_path = 'dataset/user.json'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# =============================================================================\n",
    "# Business data\n",
    "# =============================================================================\n",
    "#read full business file and extract information about top 10 restaurants only\n",
    "\n",
    "size = 500000\n",
    "\n",
    "business = pd.read_json(business_json_path, lines=True,\n",
    "                    dtype={'business_id':str,'name':str,\n",
    "                             'address':str,'city':str,\n",
    "                             'latitude':float,'longitude':float,\n",
    "                             'state':str,'postal_code':str,\n",
    "                             'stars':float,'review_count':int,\n",
    "                             'is_open':int,\n",
    "                             'attributes':object,'categories':object,\n",
    "                             'hours':object},\n",
    "                    chunksize=size)\n",
    "\n",
    "\n",
    "business_drop_columns = ['name', 'address', 'city', 'state', 'postal_code',\n",
    "                         'latitude', 'longitude', 'attributes', 'hours']\n",
    "chunk_list_business = []\n",
    "\n",
    "my_top_10_restaurants = '|'.join([ 'Food', 'Nightlife', 'Bars', 'American (Traditional)', 'American (New)', 'Breakfast & Brunch', \n",
    "                                  'Sandwiches', 'Mexican', 'Burgers', 'Pizza'])\n",
    "\n",
    "\n",
    "my_top_10_restaurants = 'Restaunrants&' + my_top_10_restaurants\n",
    "\n",
    "\n",
    "for chunk_business in business:\n",
    "    # Drop columns that aren't needed\n",
    "    chunk_business = chunk_business.drop(business_drop_columns, axis=1)\n",
    "    \n",
    "    # Renaming column name to avoid conflicts\n",
    "    chunk_business.rename(columns={'stars': 'business_stars', 'review_count': 'business_review_count',\n",
    "                                      'review_stars': 'business_review_stars'}, inplace=True)\n",
    "    \n",
    "    chunk_business = chunk_business[chunk_business['categories'].str.contains(my_top_10_restaurants, case=True,na=False)]\n",
    "    \n",
    "    chunk_list_business.append(chunk_business)\n",
    "\n",
    "    \n",
    "df_restaurants = pd.concat(chunk_list_business, ignore_index=True, join='outer', axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Save restaurants file with business id as primary key \n",
    "df_restaurants.drop('categories', axis = 1).to_json('neo4j_dataset/restaurants.json', orient='records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Delete non-essential columns to save memory\n",
    "\n",
    "del chunk_business\n",
    "del chunk_list_business"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# =============================================================================\n",
    "# Reviews data\n",
    "# =============================================================================\n",
    "size = 500000\n",
    "\n",
    "review = pd.read_json(review_json_path, lines=True,\n",
    "                      dtype={'review_id':str,'user_id':str,\n",
    "                             'business_id':str,'stars':int,\n",
    "                             'date':str,'text':str,'useful':int,\n",
    "                             'funny':int,'cool':int},\n",
    "                      chunksize=size)\n",
    "\n",
    "chunk_list = []\n",
    "for chunk_review in review:\n",
    "    \n",
    "    # Drop columns that aren't needed\n",
    "    chunk_review = chunk_review.drop(['text', 'date', 'review_id','useful','funny','cool'], axis=1)\n",
    "    \n",
    "    # Renaming column name to avoid conflicts\n",
    "    chunk_review = chunk_review.rename(columns={'stars': 'review_stars'})\n",
    "    \n",
    "    # Inner merge with edited business file so only reviews related to the restaurants remain\n",
    "    chunk_merged = pd.merge(df_restaurants, chunk_review, on='business_id', how='inner')\n",
    "    \n",
    "    # Show feedback on progress\n",
    "    print(f\"{chunk_merged.shape[0]} out of {size:,} related reviews\")\n",
    "    \n",
    "    chunk_list.append(chunk_merged)\n",
    "\n",
    "    \n",
    "# After trimming down the review file, concatenate all relevant data back to one dataframe\n",
    "df_restaurant_reviews = pd.concat(chunk_list, ignore_index=True, join='outer', axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Delete non-essential data to save memory\n",
    "\n",
    "del chunk_review\n",
    "del chunk_merged\n",
    "del chunk_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# =============================================================================\n",
    "# User data\n",
    "# =============================================================================\n",
    "\n",
    "size = 500000\n",
    "\n",
    "user = pd.read_json(user_json_path, lines=True,\n",
    "                      dtype={'user_id':str,'name':str,\n",
    "                             'yelping_since':str,'review_count':int,\n",
    "                             'friends':object,'useful':int,\n",
    "                             'funny':int,'cool':int,'fans':int,\n",
    "                             'elite':list, 'average_stars':float,'compliment_hot':int,\n",
    "                             'compliment_more':int,'compliment_more':int,'compliment_profile':int,\n",
    "                             'compliment_cute':int,'compliment_list':int,'compliment_note':int,\n",
    "                             'compliment_plain':int,'compliment_cool':int,'compliment_funny':int,\n",
    "                             'compliment_writer':int,'compliment_photos':int},\n",
    "                      chunksize=size)\n",
    "\n",
    "user_drop_columns = ['name', 'yelping_since', 'friends', 'elite']\n",
    "\n",
    "chunk_list_user = []\n",
    "\n",
    "for chunk_user in user:\n",
    "    # Drop columns that aren't needed\n",
    "    chunk_user = chunk_user.drop(user_drop_columns, axis=1)\n",
    "    \n",
    "    # Renaming column name to avoid conflicts\n",
    "    chunk_user.rename(columns={'review_count': 'user_review_count', 'average_stars': 'user_average_stars'})\n",
    "    \n",
    "    chunk_list_user.append(chunk_user)\n",
    "\n",
    "    \n",
    "# concatenate to one dataframe\n",
    "df_user = pd.concat(chunk_list_user, ignore_index=True, join='outer', axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Delete non-essential data to save memory\n",
    "\n",
    "del chunk_user\n",
    "del chunk_list_user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge users and restaurant reviews data --> this dataframe will contain information about user, restaurant and review\n",
    "\n",
    "df_users_top10 = df_user.merge(df_restaurant_reviews, how='inner', left_on=[\"user_id\"], right_on=[\"user_id\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Delete non-essential data to save memory\n",
    "\n",
    "del df_user\n",
    "del df_restaurant_reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new feature mean compliment score for each users\n",
    "\n",
    "merged_drop_columns = ['business_id', 'user_id', 'elite']\n",
    "\n",
    "df_users_top10.drop(merged_drop_columns, axis = 1, inplace = True)\n",
    "\n",
    "compliment_columns = ['compliment_cool', 'compliment_cute', 'compliment_funny', \n",
    "                               'compliment_hot', 'compliment_list', 'compliment_more',\n",
    "                               'compliment_note', 'compliment_photos', 'compliment_plain', \n",
    "                               'compliment_profile', 'compliment_writer']\n",
    "\n",
    "\n",
    "df_users_top10['mean_compliment_score'] = df_users_top10.loc[: , compliment_columns].mean(axis=1)\n",
    "\n",
    "df_users_top10.drop(compliment_columns, axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save users information - with user_id as primary key\n",
    "\n",
    "df_users_top10.drop_duplicates(inplace=True, ignore_index = True)\n",
    "\n",
    "df_users_top10.to_json('neo4j_dataset/users.json', orient='records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Expand by restaurant category to investigate restaurent categories and their overall count in data\n",
    "\n",
    "df_yelp_expand_by_category = df_restaurants.assign(categories = df_restaurants.categories\n",
    "                         .str.split(', ')).explode('categories')\n",
    "\n",
    "df_yelp_category_count = df_yelp_expand_by_category.categories.value_counts()\n",
    "\n",
    "\n",
    "top10 = [ 'Food', 'Nightlife', 'Bars', 'American (Traditional)', 'American (New)', 'Breakfast & Brunch', \n",
    "                                  'Sandwiches', 'Mexican', 'Burgers', 'Pizza']\n",
    "\n",
    "top_10_dict = {}\n",
    "\n",
    "#Dict with mapping between category name and category id\n",
    "\n",
    "for i, val in enumerate(top10):\n",
    "    top_10_dict[val] = i+1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#df_yelp_expand_by_category = df_yelp_expand_by_category[df_yelp_expand_by_category.categories.isin(top10)] already all are \n",
    "#in top 10 restaurant categories - i just ran this line to validate\n",
    "\n",
    "#df_yelp_category_count = df_yelp_expand_by_category.categories.value_counts()\n",
    "\n",
    "df_yelp_expand_by_category.drop(['business_stars','business_review_count','is_open'], axis = 1, inplace = True)\n",
    "\n",
    "#Replace category names with category id as it is needed to create edges in the graph database\n",
    "df_restaurants_category = df_yelp_expand_by_category.replace({'categories': top_10_dict})\n",
    "df_restaurants_category.rename(columns={'categories': 'category_id'}, inplace = True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save restaurant to category connections\n",
    "df_restaurants_category.reset_index(inplace=True)\n",
    "\n",
    "df_restaurants_category.to_json('neo4j_dataset/restaurant_to_category.json', orient='records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save user to restaurant connections - each line has unique combination of user_id and business_id - used to create edges\n",
    "df_user_restaurant_reviews.drop(['business_stars', 'business_review_count', 'is_open', \n",
    "                                                          'categories'], axis = 1, inplace = True)\n",
    "\n",
    "\n",
    "df_user_restaurant_reviews.to_json('neo4j_dataset/user_to_restaurant.json', orient='records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#save categories as json file\n",
    "imported_categories = pd.read_csv('dataset/categories_with_id.csv')\n",
    "\n",
    "imported_categories.to_json('neo4j_dataset/categories.json', orient='records')"
   ]
  }
 ],
 "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}