analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# This is an investigatory analysis into the sales data of a restaurant... "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It has been done mainly SQL but on top of pyspark..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So lets get started..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First we import the pyspark and the findspark modules... and also initialize the findspark module..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import findspark\n",
    "findspark.init('/Users/nishantuzir/spark-2.3.0-bin-hadoop2.7')\n",
    "import pyspark"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "now we initialize the SparkContext objects..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "sc = pyspark.SparkContext()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "here we read both the json files...using the wholeTextFiles method...\n",
    "\n",
    "one thing to know here is that the wholeTextFiles method produces a tuple RDD whose 1st element is a filename and the 2nd element is the data with lines separated by whitespace. \n",
    "\n",
    "We use map to create the new RDD using the 2nd element of the tuple."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "data1 = sc.wholeTextFiles('/data/orders.json').map(lambda x: x[1])\n",
    "data2 = sc.wholeTextFiles('/data/order_items.json').map(lambda x: x[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "as said earlier that the data is in the form of lines separated by whitespace, we need to remove these useless white spaces. \n",
    "\n",
    "That we will do using the re package..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "data1 = data1.map(lambda x: re.sub('\\s+','',x))\n",
    "data2 = data2.map(lambda x: re.sub('\\s+','',x))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "after that we import the SQLContext and initialize it..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SQLContext\n",
    "sqlcontext = SQLContext(sc)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "now its time to conume the RDD using the SQLContext object named sqlcontext...\n",
    "\n",
    "after that, we create a temporary table using registerTempTable and pass the name of the tables inside it.."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders = sqlcontext.read.json(data1)\n",
    "order_items = sqlcontext.read.json(data2)\n",
    "orders.registerTempTable('orders')\n",
    "order_items.registerTempTable('order_items')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So we are done with the preparation part...now lets do some analysis using good old SQL running on top of Spark!!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+-----------------+------------+---+----------+---------------+\n",
      "|delivered_at|delivered_at_time|delivered_to| id|ordered_at|ordered_at_time|\n",
      "+------------+-----------------+------------+---+----------+---------------+\n",
      "|  2015-08-19|         22:19:41|         767|  0|2015-08-19|       22:01:25|\n",
      "|  2015-11-16|         00:40:18|         847|  1|2015-11-16|       00:12:29|\n",
      "|  2015-11-27|         08:08:15|         972|  2|2015-11-27|       08:06:06|\n",
      "|  2015-10-14|         01:25:10|         390|  3|2015-10-14|       01:16:42|\n",
      "|  2015-11-28|         05:16:24|         914|  4|2015-11-28|       05:10:27|\n",
      "+------------+-----------------+------------+---+----------+---------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sqlcontext.sql('select * from orders' ).show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+---+--------------------+--------+\n",
      "|amount_paid| id|                name|order_id|\n",
      "+-----------+---+--------------------+--------+\n",
      "|        205|  0|      chicken-burger|     114|\n",
      "|        225|  1|chicken-tikka-san...|    2825|\n",
      "|        185|  2|almond-choco-dip-...|    4717|\n",
      "|        105|  3|               juice|    1035|\n",
      "|        185|  4|grilled-cheese-sa...|    1023|\n",
      "+-----------+---+--------------------+--------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sqlcontext.sql('select * from order_items').show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, everything is working just fine!! We are good to go..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see how many orders are placed per day..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------------+\n",
      "|ordered_at|total_orders|\n",
      "+----------+------------+\n",
      "|2015-08-09|           1|\n",
      "|2015-08-11|           5|\n",
      "|2015-08-12|           1|\n",
      "|2015-08-13|           2|\n",
      "|2015-08-14|           9|\n",
      "|2015-08-15|           4|\n",
      "|2015-08-16|           5|\n",
      "|2015-08-17|           6|\n",
      "|2015-08-18|           4|\n",
      "|2015-08-19|          10|\n",
      "+----------+------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sqlcontext.sql('select ordered_at,count(1) as total_orders from orders group by 1 order by 1').show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's see the total revenue collected from all the orders per day...\n",
    "\n",
    "to do that, we will have to use the join command..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-----------------+\n",
      "|ordered_at|revenue_collected|\n",
      "+----------+-----------------+\n",
      "|2015-08-23|              175|\n",
      "|2015-08-26|              175|\n",
      "|2015-08-27|              175|\n",
      "|2015-09-01|              175|\n",
      "|2015-09-03|              175|\n",
      "|2015-09-05|              175|\n",
      "|2015-09-10|              175|\n",
      "|2015-09-11|              175|\n",
      "|2015-09-12|              175|\n",
      "|2015-09-13|              175|\n",
      "|2015-09-20|              350|\n",
      "|2015-09-23|              175|\n",
      "|2015-09-26|              525|\n",
      "|2015-09-28|              175|\n",
      "|2015-09-29|              175|\n",
      "|2015-09-30|              175|\n",
      "|2015-10-04|              175|\n",
      "|2015-10-06|              175|\n",
      "|2015-10-09|              175|\n",
      "|2015-10-10|              350|\n",
      "+----------+-----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sqlcontext.sql('select ordered_at, round(sum(amount_paid),2) as revenue_collected from orders join order_items on orders.id = order_items.order_id where name = \"kale-smoothie\" group by ordered_at order by ordered_at').show(20)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "well we cant say much from this....lets break this down...\n",
    "\n",
    "we will see the total revenue collected per food item for the entire duration of the time that has been depicted in the dataset...and we will arrannge it in descending order..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-------------+\n",
      "|                name|total_revenue|\n",
      "+--------------------+-------------+\n",
      "|chicken-tikka-san...|      1130400|\n",
      "|grilled-cheese-sa...|       770155|\n",
      "|      chicken-burger|       711350|\n",
      "|almond-choco-dip-...|       525955|\n",
      "|                soda|       195525|\n",
      "|               juice|       104685|\n",
      "|                cake|        96660|\n",
      "|     banana-smoothie|        18900|\n",
      "|       kale-smoothie|        12600|\n",
      "+--------------------+-------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sqlcontext.sql('select name, round(sum(amount_paid), 2) as total_revenue from order_items group by name order by 2 desc').show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "it seems to be somewhat comprehensible now... "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "lets now see the percentage of revenue each of the food items represent...\n",
    "\n",
    "this will give a better idea.."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-----+\n",
      "|                name|  pct|\n",
      "+--------------------+-----+\n",
      "|chicken-tikka-san...| 31.7|\n",
      "|grilled-cheese-sa...| 21.6|\n",
      "|      chicken-burger|19.95|\n",
      "|almond-choco-dip-...|14.75|\n",
      "|                soda| 5.48|\n",
      "|               juice| 2.94|\n",
      "|                cake| 2.71|\n",
      "|     banana-smoothie| 0.53|\n",
      "|       kale-smoothie| 0.35|\n",
      "+--------------------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sqlcontext.sql('select name, round(sum(amount_paid) /(select sum(amount_paid) from order_items) * 100.0, 2) as pct from order_items group by 1 order by 2 desc').show()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "whoa!! looks like smoothies are not bringing in much revenue!\n",
    "\n",
    "lets be absolutely sure about this..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "to do this we need to group the food items into food categries such as these sandwich, burger, juice etc..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+---+--------------------+--------+--------+\n",
      "|amount_paid| id|                name|order_id|category|\n",
      "+-----------+---+--------------------+--------+--------+\n",
      "|        205|  0|      chicken-burger|     114|  burger|\n",
      "|        225|  1|chicken-tikka-san...|    2825|sandwich|\n",
      "|        185|  2|almond-choco-dip-...|    4717|  desert|\n",
      "|        105|  3|               juice|    1035|  desert|\n",
      "|        185|  4|grilled-cheese-sa...|    1023|sandwich|\n",
      "|        205|  5|      chicken-burger|    4359|  burger|\n",
      "|        225|  6|chicken-tikka-san...|    3929|sandwich|\n",
      "|        205|  7|      chicken-burger|    3704|  burger|\n",
      "|        185|  8|grilled-cheese-sa...|    1666|sandwich|\n",
      "|        225|  9|chicken-tikka-san...|    1477|sandwich|\n",
      "|        225| 10|chicken-tikka-san...|    4369|sandwich|\n",
      "|        185| 11|grilled-cheese-sa...|     998|sandwich|\n",
      "|        205| 12|      chicken-burger|    2730|  burger|\n",
      "|        225| 13|chicken-tikka-san...|    3038|sandwich|\n",
      "|        205| 14|      chicken-burger|    3602|  burger|\n",
      "|        225| 15|chicken-tikka-san...|    1484|sandwich|\n",
      "|        225| 16|chicken-tikka-san...|    4382|sandwich|\n",
      "|        225| 17|chicken-tikka-san...|    4778|sandwich|\n",
      "|        185| 18|grilled-cheese-sa...|     646|sandwich|\n",
      "|        205| 19|      chicken-burger|    4692|  burger|\n",
      "+-----------+---+--------------------+--------+--------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sqlcontext.sql('select *,case name when \"kale-smoothie\" then \"smoothie\" when \"banana-smoothie\" then \"smoothie\" when \"orange-juice\" then \"drink\" when \"soda\" then \"drink\" when \"almond-choco-dip-biscotti\" then \"desert\" when \"grilled-cheese-sandwich\" then \"sandwich\" when \"chicken-tikka-sandwich\" then \"sandwich\" when \"chicken-burger\" then \"burger\" else \"desert\" end as category from order_items order by id limit 100').show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "now we will see the percentage sales of each of the categories of food items that we prepared in the last command..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----+\n",
      "|category|  pct|\n",
      "+--------+-----+\n",
      "|sandwich|53.29|\n",
      "|  desert|20.39|\n",
      "|  burger|19.95|\n",
      "|   drink| 5.48|\n",
      "|smoothie| 0.88|\n",
      "+--------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sqlcontext.sql('select case name when \"kale-smoothie\" then \"smoothie\" when \"banana-smoothie\" then \"smoothie\" when \"orange-juice\" then \"drink\" when \"soda\" then \"drink\" when \"almond-choco-dip-biscotti\" then \"desert\" when \"grilled-cheese-sandwich\" then \"sandwich\" when \"chicken-tikka-sandwich\" then \"sandwich\" when \"chicken-burger\" then \"burger\" else \"desert\" end as category, round(1.0 * sum(amount_paid) /(select sum(amount_paid) from order_items) * 100, 2) as pct from order_items group by 1 order by 2 desc').show(20)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So it looks like smoothies are really not bringing in the big bucks for the restaurant...\n",
    "\n",
    "So should they remove the items al together???\n",
    "\n",
    "Well, lets get a closer look..\n",
    "\n",
    "Infact, before taking them out of the menu, we need to figure out how many customers ordered them..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+------------------+\n",
      "|                name|distinct_order_ids|\n",
      "+--------------------+------------------+\n",
      "|chicken-tikka-san...|              3168|\n",
      "|grilled-cheese-sa...|              2832|\n",
      "|      chicken-burger|              2487|\n",
      "|almond-choco-dip-...|              2175|\n",
      "|                soda|              2041|\n",
      "|               juice|               905|\n",
      "|                cake|               669|\n",
      "|     banana-smoothie|               105|\n",
      "|       kale-smoothie|                72|\n",
      "+--------------------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sqlcontext.sql('select name, count(distinct order_id) as distinct_order_ids from order_items group by 1 order by 2 desc').show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "well looks like smoothies are not ordered by many people, especially kale-smoothies!\n",
    "\n",
    "What might be the reason? Don't they like it?\n",
    "\n",
    "what about the 72 people who ordered the smoothie in the course of 5 months...!\n",
    "\n",
    "Lets have a look at the reorder rate of kale-smoothie...\n",
    "\n",
    "the reorder rate can be defined as the ratio of total number of distinct orders for a food item to the total number customers purchasing them...if the ratio is high that means a high reorder rate and vice-versa..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+------------+\n",
      "|                name|reorder_rate|\n",
      "+--------------------+------------+\n",
      "|       kale-smoothie|        1.00|\n",
      "|     banana-smoothie|        0.97|\n",
      "|                cake|        0.93|\n",
      "|               juice|        0.91|\n",
      "|                soda|        0.78|\n",
      "|almond-choco-dip-...|        0.76|\n",
      "|      chicken-burger|        0.72|\n",
      "|grilled-cheese-sa...|        0.68|\n",
      "|chicken-tikka-san...|        0.63|\n",
      "+--------------------+------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sqlcontext.sql('select name, round(1.0 * count(distinct order_id) / count(delivered_to), 2) as reorder_rate from order_items join orders on orders.id = order_items.order_id group by 1 order by 2 desc').show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "well well well!!!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "we can clearly see that the smoothies have a very high reorder rate!!\n",
    "\n",
    "so definitely cannot be taken out of the menu as doing so would upset a lot of loyal smoothie loving customers!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "so how to increase the sales?     to be continued..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}