NoSQL_setup_starter.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "9a57c4c6",
   "metadata": {},
   "source": [
    "# Eat Safe, Love"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "064ef857",
   "metadata": {},
   "source": [
    "## Part 1: Database and Jupyter Notebook Set Up"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "664e3c1f",
   "metadata": {},
   "source": [
    "Import the data provided in the `establishments.json` file from your Terminal. Name the database `uk_food` and the collection `establishments`.\n",
    "\n",
    "Within this markdown cell, copy the line of text you used to import the data from your Terminal. This way, future analysts will be able to repeat your process.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c791f58",
   "metadata": {},
   "source": [
    "Import the data first:\n",
    "\n",
    "```shell\n",
    "mongoimport --type json -d uk_food -c establishments --jsonArray establishments.json\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2261266a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-11-21T18:34:00.553-0500\tconnected to: mongodb://localhost/\n",
      "2023-11-21T18:34:00.553-0500\tdropping: uk_food.establishments\n",
      "2023-11-21T18:34:02.065-0500\t39779 document(s) imported successfully. 0 document(s) failed to import.\n"
     ]
    }
   ],
   "source": [
    "!mongoimport --type json -d uk_food -c establishments --drop --jsonArray /Users/dalyalami/Desktop/Starter_Code/Resources/establishments.json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3ecdd008",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import dependencies\n",
    "from pymongo import MongoClient\n",
    "from pprint import pprint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "59390c4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create an instance of MongoClient\n",
    "mongo = MongoClient(port=27017)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7f9ececa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['admin', 'config', 'local', 'uk_food']\n"
     ]
    }
   ],
   "source": [
    "# confirm that our new database was created\n",
    "print(mongo.list_database_names())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "71e373c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# assign the uk_food database to a variable name\n",
    "db = mongo['uk_food']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ef1b89ed",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['establishments']\n"
     ]
    }
   ],
   "source": [
    "# review the collections in our new database\n",
    "print(db.list_collection_names())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6a0d92df",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'AddressLine1': 'The Pines Garden',\n",
      " 'AddressLine2': 'Beach Road',\n",
      " 'AddressLine3': 'St Margarets Bay',\n",
      " 'AddressLine4': 'Kent',\n",
      " 'BusinessName': 'The Tea Room',\n",
      " 'BusinessType': 'Restaurant/Cafe/Canteen',\n",
      " 'BusinessTypeID': 1,\n",
      " 'ChangesByServerID': 0,\n",
      " 'Distance': 4587.362402580997,\n",
      " 'FHRSID': 551803,\n",
      " 'LocalAuthorityBusinessID': 'PI/000070948',\n",
      " 'LocalAuthorityCode': '182',\n",
      " 'LocalAuthorityEmailAddress': 'publicprotection@dover.gov.uk',\n",
      " 'LocalAuthorityName': 'Dover',\n",
      " 'LocalAuthorityWebSite': 'http://www.dover.gov.uk/',\n",
      " 'NewRatingPending': False,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'CT15 6DZ',\n",
      " 'RatingDate': '2021-08-17T00:00:00',\n",
      " 'RatingKey': 'fhrs_5_en-gb',\n",
      " 'RatingValue': '5',\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('655d3e68743b29cf06dfa3d5'),\n",
      " 'geocode': {'latitude': '51.148133', 'longitude': '1.383298'},\n",
      " 'links': [{'href': 'https://api.ratings.food.gov.uk/establishments/551803',\n",
      "            'rel': 'self'}],\n",
      " 'meta': {'dataSource': None,\n",
      "          'extractDate': '0001-01-01T00:00:00',\n",
      "          'itemCount': 0,\n",
      "          'pageNumber': 0,\n",
      "          'pageSize': 0,\n",
      "          'returncode': None,\n",
      "          'totalCount': 0,\n",
      "          'totalPages': 0},\n",
      " 'scores': {'ConfidenceInManagement': 0, 'Hygiene': 0, 'Structural': 0}}\n"
     ]
    }
   ],
   "source": [
    "# review a document in the establishments collection\n",
    "pprint(db.establishments.find_one())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "11c9f11b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# assign the collection to a variable\n",
    "establishments = db['establishments']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5671434",
   "metadata": {},
   "source": [
    "## Part 2: Update the Database"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "314bd884",
   "metadata": {},
   "source": [
    "1. An exciting new halal restaurant just opened in Greenwich, but hasn't been rated yet. The magazine has asked you to include it in your analysis. Add the following restaurant \"Penang Flavours\" to the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c2da7648",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a dictionary for the new restaurant data\n",
    "new_restaurant = {\n",
    "    \"BusinessName\":\"Penang Flavours\",\n",
    "    \"BusinessType\":\"Restaurant/Cafe/Canteen\",\n",
    "    \"BusinessTypeID\":\"\",\n",
    "    \"AddressLine1\":\"Penang Flavours\",\n",
    "    \"AddressLine2\":\"146A Plumstead Rd\",\n",
    "    \"AddressLine3\":\"London\",\n",
    "    \"AddressLine4\":\"\",\n",
    "    \"PostCode\":\"SE18 7DY\",\n",
    "    \"Phone\":\"\",\n",
    "    \"LocalAuthorityCode\":\"511\",\n",
    "    \"LocalAuthorityName\":\"Greenwich\",\n",
    "    \"LocalAuthorityWebSite\":\"http://www.royalgreenwich.gov.uk\",\n",
    "    \"LocalAuthorityEmailAddress\":\"health@royalgreenwich.gov.uk\",\n",
    "    \"scores\":{\n",
    "        \"Hygiene\":\"\",\n",
    "        \"Structural\":\"\",\n",
    "        \"ConfidenceInManagement\":\"\"\n",
    "    },\n",
    "    \"SchemeType\":\"FHRS\",\n",
    "    \"geocode\":{\n",
    "        \"longitude\":\"0.08384000\",\n",
    "        \"latitude\":\"51.49014200\"\n",
    "    },\n",
    "    \"RightToReply\":\"\",\n",
    "    \"Distance\":4623.9723280747176,\n",
    "    \"NewRatingPending\":True\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a6e5cece",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertOneResult at 0x10f0456c0>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Insert the new restaurant into the collection\n",
    "establishments.insert_one(new_restaurant)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "63bb10f9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': ObjectId('655d3e6a6e5d85eef92f8ab5'),\n",
       " 'BusinessName': 'Penang Flavours',\n",
       " 'BusinessType': 'Restaurant/Cafe/Canteen',\n",
       " 'BusinessTypeID': '',\n",
       " 'AddressLine1': 'Penang Flavours',\n",
       " 'AddressLine2': '146A Plumstead Rd',\n",
       " 'AddressLine3': 'London',\n",
       " 'AddressLine4': '',\n",
       " 'PostCode': 'SE18 7DY',\n",
       " 'Phone': '',\n",
       " 'LocalAuthorityCode': '511',\n",
       " 'LocalAuthorityName': 'Greenwich',\n",
       " 'LocalAuthorityWebSite': 'http://www.royalgreenwich.gov.uk',\n",
       " 'LocalAuthorityEmailAddress': 'health@royalgreenwich.gov.uk',\n",
       " 'scores': {'Hygiene': '', 'Structural': '', 'ConfidenceInManagement': ''},\n",
       " 'SchemeType': 'FHRS',\n",
       " 'geocode': {'longitude': '0.08384000', 'latitude': '51.49014200'},\n",
       " 'RightToReply': '',\n",
       " 'Distance': 4623.972328074718,\n",
       " 'NewRatingPending': True}"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that the new restaurant was inserted\n",
    "check_restaurant = establishments.find_one({\"BusinessName\":\"Penang Flavours\"})\n",
    "check_restaurant"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ef4395a",
   "metadata": {},
   "source": [
    "2. Find the BusinessTypeID for \"Restaurant/Cafe/Canteen\" and return only the `BusinessTypeID` and `BusinessType` fields."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "93c63829",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find the BusinessTypeID for \"Restaurant/Cafe/Canteen\" and return only the BusinessTypeID and BusinessType fields\n",
    "query = {\"BusinessTypeID\":\"Restaurant/Cafe/Canteen\"}\n",
    "fields = {'BusinessTypeID': 1, 'BusinessType': 1}\n",
    "\n",
    "results = establishments.find(query,fields)\n",
    "for result in results:\n",
    "    pprint(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e570caf",
   "metadata": {},
   "source": [
    "3. Update the new restaurant with the `BusinessTypeID` you found."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "7f599f3d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.UpdateResult at 0x10eeaa0c0>"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Update the new restaurant with the correct BusinessTypeID\n",
    "query = {\"BusinessName\":\"Penang Flavours\"}\n",
    "BusinessTypeID = {'$set':{ \"BusinessTypeID\": 1}}\n",
    "\n",
    "establishments.update_one(query,BusinessTypeID)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a6ad16f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': ObjectId('655d3e6a6e5d85eef92f8ab5'),\n",
       " 'BusinessName': 'Penang Flavours',\n",
       " 'BusinessType': 'Restaurant/Cafe/Canteen',\n",
       " 'BusinessTypeID': 1,\n",
       " 'AddressLine1': 'Penang Flavours',\n",
       " 'AddressLine2': '146A Plumstead Rd',\n",
       " 'AddressLine3': 'London',\n",
       " 'AddressLine4': '',\n",
       " 'PostCode': 'SE18 7DY',\n",
       " 'Phone': '',\n",
       " 'LocalAuthorityCode': '511',\n",
       " 'LocalAuthorityName': 'Greenwich',\n",
       " 'LocalAuthorityWebSite': 'http://www.royalgreenwich.gov.uk',\n",
       " 'LocalAuthorityEmailAddress': 'health@royalgreenwich.gov.uk',\n",
       " 'scores': {'Hygiene': '', 'Structural': '', 'ConfidenceInManagement': ''},\n",
       " 'SchemeType': 'FHRS',\n",
       " 'geocode': {'longitude': '0.08384000', 'latitude': '51.49014200'},\n",
       " 'RightToReply': '',\n",
       " 'Distance': 4623.972328074718,\n",
       " 'NewRatingPending': True}"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Confirm that the new restaurant was updated\n",
    "check_updated_restaurant = establishments.find_one({\"BusinessName\":\"Penang Flavours\"})\n",
    "check_updated_restaurant"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67281254",
   "metadata": {},
   "source": [
    "4. The magazine is not interested in any establishments in Dover, so check how many documents contain the Dover Local Authority. Then, remove any establishments within the Dover Local Authority from the database, and check the number of documents to ensure they were deleted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "768474a6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "994\n"
     ]
    }
   ],
   "source": [
    "# Find how many documents have LocalAuthorityName as \"Dover\"\n",
    "query = {'LocalAuthorityName': 'Dover'}\n",
    "\n",
    "results = establishments.count_documents(query)\n",
    "\n",
    "pprint(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a11d5f1b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.DeleteResult at 0x10f0510c0>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Delete all documents where LocalAuthorityName is \"Dover\"\n",
    "establishments.delete_many(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "2a51aba0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "None\n"
     ]
    }
   ],
   "source": [
    "# Check if any remaining documents include Dover\n",
    "results = establishments.find_one(query)\n",
    "pprint(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "8c356f7b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'AddressLine1': 'East Cliff Pavilion',\n",
      " 'AddressLine2': 'Wear Bay Road',\n",
      " 'AddressLine3': 'Folkestone',\n",
      " 'AddressLine4': 'Kent',\n",
      " 'BusinessName': 'The Pavilion',\n",
      " 'BusinessType': 'Restaurant/Cafe/Canteen',\n",
      " 'BusinessTypeID': 1,\n",
      " 'ChangesByServerID': 0,\n",
      " 'Distance': 4591.765489457773,\n",
      " 'FHRSID': 1043695,\n",
      " 'LocalAuthorityBusinessID': 'PI/000073616',\n",
      " 'LocalAuthorityCode': '188',\n",
      " 'LocalAuthorityEmailAddress': 'foodteam@folkestone-hythe.gov.uk',\n",
      " 'LocalAuthorityName': 'Folkestone and Hythe',\n",
      " 'LocalAuthorityWebSite': 'http://www.folkestone-hythe.gov.uk',\n",
      " 'NewRatingPending': False,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'CT19 6BL',\n",
      " 'RatingDate': '2018-04-04T00:00:00',\n",
      " 'RatingKey': 'fhrs_5_en-gb',\n",
      " 'RatingValue': '5',\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('655d3e68743b29cf06dfa6bc'),\n",
      " 'geocode': {'latitude': '51.083812', 'longitude': '1.195625'},\n",
      " 'links': [{'href': 'https://api.ratings.food.gov.uk/establishments/1043695',\n",
      "            'rel': 'self'}],\n",
      " 'meta': {'dataSource': None,\n",
      "          'extractDate': '0001-01-01T00:00:00',\n",
      "          'itemCount': 0,\n",
      "          'pageNumber': 0,\n",
      "          'pageSize': 0,\n",
      "          'returncode': None,\n",
      "          'totalCount': 0,\n",
      "          'totalPages': 0},\n",
      " 'scores': {'ConfidenceInManagement': 5, 'Hygiene': 5, 'Structural': 5}}\n"
     ]
    }
   ],
   "source": [
    "# Check that other documents remain with 'find_one'\n",
    "pprint(establishments.find_one())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9f1662d",
   "metadata": {},
   "source": [
    "5. Some of the number values are stored as strings, when they should be stored as numbers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c46df2df",
   "metadata": {},
   "source": [
    "Use `update_many` to convert `latitude` and `longitude` to decimal numbers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "fd4112ce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.UpdateResult at 0x10f051b00>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Change the data type from String to Decimal for longitude and latitude\n",
    "establishments.update_many({}, [ {'$set':{ \"geocode.longitude\" : {'$toDouble': \"$geocode.longitude\"}}} ]) \n",
    "establishments.update_many({}, [ {'$set':{ \"geocode.latitude\" : {'$toDouble': \"$geocode.latitude\"}}} ]) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "142e319c",
   "metadata": {},
   "source": [
    "Use `update_many` to convert `RatingValue` to integer numbers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "04430a5b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.UpdateResult at 0x10eedff40>"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Set non 1-5 Rating Values to Null\n",
    "non_ratings = [\"AwaitingInspection\", \"Awaiting Inspection\", \"AwaitingPublication\", \"Pass\", \"Exempt\"]\n",
    "establishments.update_many({\"RatingValue\": {\"$in\": non_ratings}}, [ {'$set':{ \"RatingValue\" : None}} ])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "92524ccc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.UpdateResult at 0x10f059f40>"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Change the data type from String to Integer for RatingValue\n",
    "establishments.update_many({}, [ {'$set':{ \"RatingValue\" : {'$toDouble': \"$RatingValue\"}}} ]) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "0deacaf9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'AddressLine1': 'East Cliff Pavilion',\n",
      " 'AddressLine2': 'Wear Bay Road',\n",
      " 'AddressLine3': 'Folkestone',\n",
      " 'AddressLine4': 'Kent',\n",
      " 'BusinessName': 'The Pavilion',\n",
      " 'BusinessType': 'Restaurant/Cafe/Canteen',\n",
      " 'BusinessTypeID': 1,\n",
      " 'ChangesByServerID': 0,\n",
      " 'Distance': 4591.765489457773,\n",
      " 'FHRSID': 1043695,\n",
      " 'LocalAuthorityBusinessID': 'PI/000073616',\n",
      " 'LocalAuthorityCode': '188',\n",
      " 'LocalAuthorityEmailAddress': 'foodteam@folkestone-hythe.gov.uk',\n",
      " 'LocalAuthorityName': 'Folkestone and Hythe',\n",
      " 'LocalAuthorityWebSite': 'http://www.folkestone-hythe.gov.uk',\n",
      " 'NewRatingPending': False,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'CT19 6BL',\n",
      " 'RatingDate': '2018-04-04T00:00:00',\n",
      " 'RatingKey': 'fhrs_5_en-gb',\n",
      " 'RatingValue': 5.0,\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('655d3e68743b29cf06dfa6bc'),\n",
      " 'geocode': {'latitude': 51.083812, 'longitude': 1.195625},\n",
      " 'links': [{'href': 'https://api.ratings.food.gov.uk/establishments/1043695',\n",
      "            'rel': 'self'}],\n",
      " 'meta': {'dataSource': None,\n",
      "          'extractDate': '0001-01-01T00:00:00',\n",
      "          'itemCount': 0,\n",
      "          'pageNumber': 0,\n",
      "          'pageSize': 0,\n",
      "          'returncode': None,\n",
      "          'totalCount': 0,\n",
      "          'totalPages': 0},\n",
      " 'scores': {'ConfidenceInManagement': 5, 'Hygiene': 5, 'Structural': 5}}\n"
     ]
    }
   ],
   "source": [
    "# Check that the coordinates and rating value are now numbers\n",
    "pprint(establishments.find_one())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}