 YelpAnalysisSpark1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Yelp Data Analysis in Spark 1\n",
    "\n",
    "### The analysis have been performed on used business.json\n",
    "\n",
    "There are total of 5 objectives in this work.\n",
    "\n",
    "1. Learning to work with JSON hierarical data in SparkSQL\n",
    "2. Performing grouping aggregation using on data using SparkSQL\n",
    "3. Pivoting the data, this might not be the best use case for pivoting but in genreally there are a lot of use cases which require pivoting of data\n",
    "4. Filtering out data based on attribute values\n",
    "5. Performing Geospacial analysis\n",
    "\n",
    "A continuation of this of this notebook will be YelpAnalysisSpark2.ipynb which joins two datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import findspark\n",
    "findspark.init()\n",
    "from pyspark import SparkContext as sc\n",
    "import pandas as pd\n",
    "import os\n",
    "import numpy as np\n",
    "from pyspark.sql import SQLContext\n",
    "import json\n",
    "import pyspark\n",
    "\n",
    "data_path = ['../Data']\n",
    "Business_filepath = os.sep.join(data_path + ['business.json'])\n",
    "\n",
    "sc = sc(appName=\"Yelp\")\n",
    "sqlContext = SQLContext(sc)\n",
    "\n",
    "\n",
    "#Load Business data\n",
    "Business_data = sqlContext.read.json(Business_filepath)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+--------------------+--------------------+----------------+--------------------+-------+-------------+--------------+--------------------+-----------------+-----------+------------+-----+-----+\n",
      "|             address|          attributes|         business_id|          categories|            city|               hours|is_open|     latitude|     longitude|                name|     neighborhood|postal_code|review_count|stars|state|\n",
      "+--------------------+--------------------+--------------------+--------------------+----------------+--------------------+-------+-------------+--------------+--------------------+-----------------+-----------+------------+-----+-----+\n",
      "|     691 Richmond Rd|[null,null,null,n...|YDf95gJZaq05wvo7h...|[Shopping, Shoppi...|Richmond Heights|[10:00-21:00,10:0...|      1|   41.5417162|   -81.4931165|Richmond Town Square|                 |      44143|          17|  2.0|   OH|\n",
      "|      2824 Milton Rd|[null,null,null,[...|mLwM-h2YhXl2NCgdS...|[Food, Soul Food,...|       Charlotte|[10:00-22:00,10:0...|      0|     35.23687|   -80.7419759|South Florida Sty...|         Eastland|      28215|           4|  4.5|   NC|\n",
      "| 337 Danforth Avenue|[null,null,null,n...|v2WhjAB3PIBA8J8Vx...|[Food, Coffee & Tea]|         Toronto|[10:00-19:00,10:0...|      0|   43.6771258|   -79.3532848|    The Tea Emporium|        Riverdale|    M4K 1N7|           7|  4.5|   ON|\n",
      "|7702 E Doubletree...|[null,null,null,n...|CVtCbSB1zUcUWg-9T...|[Professional Ser...|      Scottsdale|[9:00-17:00,9:00-...|      1|   33.5650816|  -111.9164003|            TRUmatch|                 |      85258|           3|  3.0|   AZ|\n",
      "|      4719 N 20Th St|[null,null,none,[...|duHFBe87uNSXImQmv...|[Sandwiches, Rest...|         Phoenix|[null,null,null,n...|      0|   33.5059283|  -112.0388474|             Blimpie|                 |      85016|          10|  4.5|   AZ|\n",
      "| 2017 E Camelback Rd|[null,null,null,n...|uUEMrhJiL1a1pCA_I...|[Shopping, Tobacc...|         Phoenix|[10:00-21:00,10:0...|      0|   33.5080678|  -112.0375524|     Baxter's Cigars|                 |      85016|          35|  5.0|   AZ|\n",
      "|4425 N 24th St, S...|[true,null,null,n...|2eJEUJIP54tex7T9Y...|[Chiropractors, H...|         Phoenix|[9:00-12:00,14:30...|      1|   33.5028476|   -112.012696|Back-Health Chiro...|                 |      85016|          19|  5.0|   AZ|\n",
      "|      5770 Butler St|[null,null,null,n...|fEylCY3UEH8YJ0Xa7...|[Automotive, Oil ...|      Pittsburgh|[8:00-17:00,8:00-...|      1|   40.4859391|   -79.9437273|       Auto Bathouse|    Lawrenceville|      15201|          16|  2.5|   PA|\n",
      "|  3220 Washington Rd|[null,null,null,n...|kFtuYklkAIlmYw8RZ...|[Jewelry Repair, ...|        McMurray|[10:00-18:00,10:0...|      1|   40.2904976|   -80.1100212| JAB Jewelry Designs|                 |      15317|           7|  5.0|   PA|\n",
      "|1425 S Higley Rd,...|[true,null,null,n...|NqiQdFa93wzUJGo29...|[Health & Medical...|         Gilbert|[7:30-11:00,7:30-...|      1|   33.3245392|  -111.7204486|Neighborhood Visi...|                 |      85296|           8|  5.0|   AZ|\n",
      "|11022 W Charlesto...|[null,null,null,n...|N9BN9ldVl1FNzcB9_...|[Bowling, Active ...|       Las Vegas|[null,null,null,n...|      1|   36.1590984|  -115.3379148|Red Rock Bowling ...|        Summerlin|      89135|          13|  4.5|   NV|\n",
      "|9393 N 90th St, S...|[null,null,null,n...|6s3z3TlpHOIecuSyP...|[Barbers, Men's H...|      Scottsdale|[9:00-19:00,10:00...|      0|33.5704213907|-111.886491962|             Sq Cutz|                 |      85258|           7|  4.5|   AZ|\n",
      "|  9101 E Baseline Rd|[null,null,null,n...|n33Izvzk_z9_51H6N...|[Flowers & Gifts,...|            Mesa|[5:00-0:00,5:00-0...|      1|33.3774281068|-111.638108311|             Safeway|                 |      85209|          21|  2.0|   AZ|\n",
      "|     12417 W Pima St|[null,null,null,n...|m06OZRFTaKKi6U0om...|[Landscape Archit...|        Avondale|[8:00-17:00,8:00-...|      1|    33.431335|   -112.325863|Artificial Grass ...|                 |      85323|          41|  4.5|   AZ|\n",
      "|9616 E Independen...|[null,null,full_b...|SDMRxmcKPNt1AHPBK...|[Burgers, Bars, R...|        Matthews|[11:00-1:00,11:00...|      1|    35.135196|   -80.7146829|          Applebee's|                 |      28105|          21|  2.0|   NC|\n",
      "|     190 E Dallas Rd|[null,null,none,n...|iFEiMJoEqyB9O8OUN...|[Chinese, Restaur...|         Stanley|[11:30-22:30,11:3...|      1|   35.3550853|   -81.0872676|        China Garden|                 |      28164|           3|  3.0|   NC|\n",
      "|            62 S Ctr|[null,null,null,n...|qrAHt4wWRYWj1sEjx...|[Contractors, Roo...|            Mesa|[7:00-19:00,7:00-...|      1|   33.4132187|  -111.8314886|Beach Ventures Ro...|                 |      85210|           3|  3.5|   AZ|\n",
      "|2128 Warrensville...|[null,null,null,n...|jqp5TibmgJZVFKHTp...|[Car Wash, Automo...|       Cleveland|[8:30-18:00,8:30-...|      1|   41.5018198|    -81.536776|Alpaul Automobile...|                 |      44121|          25|  3.0|   OH|\n",
      "|      3710 Payne Ave|[null,null,null,n...|1cLXGXThDYZ5WK_Kp...|[Food, Bakeries, ...|       Cleveland|[9:00-20:00,9:00-...|      1|   41.5094078|   -81.6621618|         Koko Bakery|Goodrich Kirtland|      44114|         162|  4.0|   OH|\n",
      "|  2385 E Windmill Ln|[null,null,null,n...|BmSJ6C7Y9s3tHotRe...|[Financial Servic...|       Las Vegas|[10:00-19:00,10:0...|      1|   36.0404133|   -115.119104|           U.S. Bank|        Southeast|      89123|           3|  3.5|   NV|\n",
      "+--------------------+--------------------+--------------------+--------------------+----------------+--------------------+-------+-------------+--------------+--------------------+-----------------+-----------+------------+-----+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "Business_data.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Finding Average review count and stars by city and category"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------+--------------------+------------------+------------------+\n",
      "|            city|            category|     AverageReview|      AverageStars|\n",
      "+----------------+--------------------+------------------+------------------+\n",
      "|Richmond Heights|            Shopping|              10.5|               3.5|\n",
      "|         Madison|    Laundry Services| 6.851851851851852| 2.962962962962963|\n",
      "|          Elyria|             Doctors|               3.0|               2.5|\n",
      "|            Mesa|  Auto Customization|12.857142857142858|              4.25|\n",
      "|         Phoenix|                Pets| 21.04076086956522| 4.073369565217392|\n",
      "|         Toronto|  Financial Services|          6.296875|           2.96875|\n",
      "|           Tempe|     Hotels & Travel| 29.19298245614035| 3.258771929824561|\n",
      "|        Surprise|            Shopping|12.532374100719425|3.5719424460431655|\n",
      "|       Henderson|               Pizza|  96.3804347826087|3.3532608695652173|\n",
      "|       Etobicoke|      Sporting Goods| 6.583333333333333|             3.625|\n",
      "|         Phoenix|             Noodles|            162.75|           3.71875|\n",
      "|         Markham|                Gyms|              9.75|           3.40625|\n",
      "|        Matthews|              Bagels|              55.0|               3.5|\n",
      "|        Westlake|Pet Boarding/Pet ...|              11.5|               4.0|\n",
      "|           Solon|            Bakeries|              19.5|               3.5|\n",
      "|      Pittsburgh|   General Dentistry| 6.045454545454546|3.7954545454545454|\n",
      "|       Las Vegas|              Diners|170.54807692307693|3.3509615384615383|\n",
      "|            Mesa|Criminal Defense Law|            5.3125|           4.03125|\n",
      "|            Mesa|Lighting Fixtures...| 19.76923076923077| 4.538461538461538|\n",
      "|       El Mirage|         Auto Repair| 6.222222222222222| 3.888888888888889|\n",
      "+----------------+--------------------+------------------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#Filter out the attributes that you need\n",
    "Business = Business_data.select(pyspark.sql.functions.explode(Business_data.categories).alias(\"category\"), \n",
    "                               Business_data.state,  Business_data.city, Business_data.stars, Business_data.review_count)\n",
    "#Register as temp table\n",
    "Business.registerTempTable(\"Business_Agg\")\n",
    "\n",
    "#Run the SQL Query\n",
    "result = sqlContext.sql(\"SELECT Business_Agg.city, Business_Agg.category,\\\n",
    "AVG(Business_Agg.review_count) As AverageReview,\\\n",
    "AVG(Business_Agg.stars) as AverageStars FROM Business_Agg GROUP BY Business_Agg.city, Business_Agg.category\")\n",
    "\n",
    "#saving the result in a csv file\n",
    "result.coalesce(1).write.format('com.databricks.spark.csv').option(\"header\", \"true\").save('Question1')\n",
    "#Check the result\n",
    "result.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivoting the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "BusinessPivot = Business_data.select(pyspark.sql.functions.explode(Business_data.categories).alias(\"category\"),\\\n",
    "                                     Business_data.state,  Business_data.city, Business_data.stars, \\\n",
    "                                     Business_data.review_count)\n",
    "\n",
    "BusinessPivot.registerTempTable(\"AggreCate\")\n",
    "\n",
    "pivot_table = BusinessPivot.groupBy(\"city\", \"State\").pivot(\"category\").avg(\"stars\")\n",
    "#pivot_table.coalesce(1).write.format('com.databricks.spark.csv').option(\"header\", \"true\").save('Question2')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter out data based on attribute values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----------------+\n",
      "|category|            Stars|\n",
      "+--------+-----------------+\n",
      "| Mexican|3.436754507628294|\n",
      "+--------+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#Flatten the category array\n",
    "CategoryExplo = Business_data.select(pyspark.sql.functions.explode(Business_data.categories).alias(\"category\"),\\\n",
    "                                    Business_data.attributes, Business_data.stars)\n",
    "\n",
    "#Filter out Mexican and TakeOut\n",
    "CategoryAtt = CategoryExplo.select(CategoryExplo.attributes.RestaurantsTakeOut.alias(\"takeout\"),\\\n",
    "                                   CategoryExplo.category, CategoryExplo.stars)\n",
    "\n",
    "CategoryAtt.registerTempTable(\"CategoryAtt\")\n",
    "\n",
    "#Run the query on the table\n",
    "MexicanTakeout = \"SELECT category, AVG(stars) AS Stars FROM CategoryAtt WHERE category = 'Mexican' \\\n",
    "AND takeout = True GROUP BY category\"\n",
    "RatingMexicanTakeO = sqlContext.sql(MexicanTakeout)\n",
    "\n",
    "RatingMexicanTakeO.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "RatingMexicanTakeO.coalesce(1).write.format('com.databricks.spark.csv').option(\"header\", \"true\").save('Question3')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Geospacial Analysis\n",
    "### FIltering businesses near toronto specifically within 15 km of Toronto center"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+------------------+------------------+\n",
      "|            category|         stars_avg|  Review_count_avg|\n",
      "+--------------------+------------------+------------------+\n",
      "|         3D Printing|              3.25|               6.0|\n",
      "|          Acai Bowls|               4.5|               9.0|\n",
      "|         Accessories| 3.508130081300813| 7.800813008130081|\n",
      "|         Accountants|             3.875| 8.333333333333334|\n",
      "|      Acne Treatment|             2.875|              10.0|\n",
      "|         Active Life| 3.795514511873351|12.275725593667547|\n",
      "|         Acupuncture|  4.30827067669173| 8.774436090225564|\n",
      "|               Adult|3.8181818181818183| 8.363636363636363|\n",
      "|     Adult Education|2.6666666666666665| 4.166666666666667|\n",
      "| Adult Entertainment|2.7954545454545454|10.454545454545455|\n",
      "|         Advertising|               3.0| 4.666666666666667|\n",
      "|              Afghan|3.6964285714285716|23.321428571428573|\n",
      "|             African|3.8461538461538463|26.333333333333332|\n",
      "|            Airlines|              2.75|             175.0|\n",
      "|     Airport Lounges|               4.0|              43.0|\n",
      "|    Airport Shuttles|2.2222222222222223| 6.444444444444445|\n",
      "|            Airports|              2.75|              81.0|\n",
      "|             Airsoft|               5.0|               4.0|\n",
      "|Amateur Sports Teams| 4.055555555555555| 9.833333333333334|\n",
      "|      American (New)|3.3846153846153846| 84.07692307692308|\n",
      "+--------------------+------------------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#lattitude, longitude, exploded(category)\n",
    "LatLong = Business_data.select(pyspark.sql.functions.explode(Business_data.categories).alias(\"category\"), \n",
    "                              Business_data.latitude, Business_data.longitude, Business_data.stars,\n",
    "                              Business_data.review_count)\n",
    "\n",
    "#Register Temp Table\n",
    "LatLong.registerTempTable(\"LatLong\")\n",
    "\n",
    "#Then run a SQL query to filter out the ones only the ones within 15km distance from Toronto Center\n",
    "BusinessNearTorontofilt = sqlContext.sql(\"SELECT * FROM  LatLong WHERE \\\n",
    "acos(sin(0.763782941288) * sin(LatLong.latitude * 3.14159 /180) + \\\n",
    "cos(0.763782941288) * cos(LatLong.latitude * 3.14159 /180) * cos((LatLong.longitude * 3.14159 /180) \\\n",
    "- (-1.38598479111))) * 6371 <= 15\")\n",
    "\n",
    "#Then apply the aggregate function on BusinessNearTorontofilt.\n",
    "BusinessNearTorontofilt.registerTempTable(\"BusinessNearToronto\")\n",
    "AggQuery = sqlContext.sql(\"SELECT category,AVG(BusinessNearToronto.stars) AS stars_avg,\\\n",
    "AVG(BusinessNearToronto.review_count) AS Review_count_avg FROM BusinessNearToronto GROUP BY category ORDER BY CATEGORY\")\n",
    "\n",
    "AggQuery.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "791"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "AggQuery.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "AggQuery.coalesce(1).write.format('com.databricks.spark.csv').option(\"header\", \"true\").save('Question4')"
   ]
  }
 ],
 "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}