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",
    "\n",
    "e.g.: Import the dataset with `YOUR IMPORT TEXT HERE`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "id": "e6f16eb0",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2024-05-29T18:02:53.401-0400\tconnected to: mongodb://localhost/\n",
      "2024-05-29T18:02:53.402-0400\tdropping: uk_food.establishments\n",
      "2024-05-29T18:02:55.136-0400\t39779 document(s) imported successfully. 0 document(s) failed to import.\n"
     ]
    }
   ],
   "source": [
    "! mongoimport --db uk_food --collection establishments --drop --jsonArray Resources/establishments.json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "id": "3ecdd008",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import dependencies\n",
    "from pymongo import MongoClient\n",
    "from pprint import pprint\n",
    "from decimal import Decimal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "id": "59390c4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create an instance of MongoClient\n",
    "mongo = MongoClient(port=27017)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "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": 255,
   "id": "71e373c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# assign the uk_food database to a variable name\n",
    "db = mongo['uk_food']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "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": 257,
   "id": "18ab463c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'AddressLine1': '1 The Droveway',\n",
      " 'AddressLine2': 'St Margarets Bay',\n",
      " 'AddressLine3': 'Kent',\n",
      " 'AddressLine4': '',\n",
      " 'BusinessName': 'FirstLight Bar & Café',\n",
      " 'BusinessType': 'Restaurant/Cafe/Canteen',\n",
      " 'BusinessTypeID': 1,\n",
      " 'ChangesByServerID': 0,\n",
      " 'Distance': 4587.64041272672,\n",
      " 'FHRSID': 1290215,\n",
      " 'LocalAuthorityBusinessID': 'PI/000083029',\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 6DH',\n",
      " 'RatingDate': '2020-11-09T00:00:00',\n",
      " 'RatingKey': 'fhrs_5_en-gb',\n",
      " 'RatingValue': '5',\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('6657a60df80d6b1f3ad69aa7'),\n",
      " 'geocode': {'latitude': '51.150625', 'longitude': '1.378231'},\n",
      " 'links': [{'href': 'https://api.ratings.food.gov.uk/establishments/1290215',\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": [
    "# review the collections in our new database\n",
    "pprint(db.establishments.find_one())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "id": "d23817c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# assign the collection to a variable\n",
    "establishments = db['establishments']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "id": "ec1212d6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('6657a60df80d6b1f3ad69aa7'), 'FHRSID': 1290215, 'ChangesByServerID': 0, 'LocalAuthorityBusinessID': 'PI/000083029', 'BusinessName': 'FirstLight Bar & Café', 'BusinessType': 'Restaurant/Cafe/Canteen', 'BusinessTypeID': 1, 'AddressLine1': '1 The Droveway', 'AddressLine2': 'St Margarets Bay', 'AddressLine3': 'Kent', 'AddressLine4': '', 'PostCode': 'CT15 6DH', 'Phone': '', 'RatingValue': '5', 'RatingKey': 'fhrs_5_en-gb', 'RatingDate': '2020-11-09T00:00:00', 'LocalAuthorityCode': '182', 'LocalAuthorityName': 'Dover', 'LocalAuthorityWebSite': 'http://www.dover.gov.uk/', 'LocalAuthorityEmailAddress': 'publicprotection@dover.gov.uk', 'scores': {'Hygiene': 5, 'Structural': 5, 'ConfidenceInManagement': 5}, 'SchemeType': 'FHRS', 'geocode': {'longitude': '1.378231', 'latitude': '51.150625'}, 'RightToReply': '', 'Distance': 4587.64041272672, 'NewRatingPending': False, 'meta': {'dataSource': None, 'extractDate': '0001-01-01T00:00:00', 'itemCount': 0, 'returncode': None, 'totalCount': 0, 'totalPages': 0, 'pageSize': 0, 'pageNumber': 0}, 'links': [{'rel': 'self', 'href': 'https://api.ratings.food.gov.uk/establishments/1290215'}]}\n"
     ]
    }
   ],
   "source": [
    "# review a document in the establishments collection\n",
    "query = {}\n",
    "document = establishments.find_one(query)\n",
    "print(document)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "id": "40da7b29",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Document found:\n",
      "{'AddressLine1': '1 The Droveway',\n",
      " 'AddressLine2': 'St Margarets Bay',\n",
      " 'AddressLine3': 'Kent',\n",
      " 'AddressLine4': '',\n",
      " 'BusinessName': 'FirstLight Bar & Café',\n",
      " 'BusinessType': 'Restaurant/Cafe/Canteen',\n",
      " 'BusinessTypeID': 1,\n",
      " 'ChangesByServerID': 0,\n",
      " 'Distance': 4587.64041272672,\n",
      " 'FHRSID': 1290215,\n",
      " 'LocalAuthorityBusinessID': 'PI/000083029',\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 6DH',\n",
      " 'RatingDate': '2020-11-09T00:00:00',\n",
      " 'RatingKey': 'fhrs_5_en-gb',\n",
      " 'RatingValue': '5',\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('6657a60df80d6b1f3ad69aa7'),\n",
      " 'geocode': {'latitude': '51.150625', 'longitude': '1.378231'},\n",
      " 'links': [{'href': 'https://api.ratings.food.gov.uk/establishments/1290215',\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": [
    "\n",
    "if document:\n",
    "    print(\"Document found:\")\n",
    "    pprint(document)\n",
    "else:\n",
    "    print(\"No document matches the query.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "id": "7d7c407c",
   "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": 262,
   "id": "c2da7648",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a dictionary for the new restaurant data\n",
    "# Connect to MongoDB\n",
    "client = MongoClient('mongodb://localhost:27017/')\n",
    "db = client['uk_food']\n",
    "\n",
    "# Define the new restaurant data\n",
    "new_restaurant_data = {\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",
    "}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "id": "a6e5cece",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "New restaurant data inserted successfully.\n"
     ]
    }
   ],
   "source": [
    "# Insert the new restaurant into the collection\n",
    "establishments = db['establishments']\n",
    "establishments.insert_one(new_restaurant_data)\n",
    "print(\"New restaurant data inserted successfully.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "id": "63bb10f9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'AddressLine1': 'Penang Flavours',\n",
      " 'AddressLine2': '146A Plumstead Rd',\n",
      " 'AddressLine3': 'London',\n",
      " 'AddressLine4': '',\n",
      " 'BusinessName': 'Penang Flavours',\n",
      " 'BusinessType': 'Restaurant/Cafe/Canteen',\n",
      " 'BusinessTypeID': '',\n",
      " 'Distance': 4623.972328074718,\n",
      " 'LocalAuthorityCode': '511',\n",
      " 'LocalAuthorityEmailAddress': 'health@royalgreenwich.gov.uk',\n",
      " 'LocalAuthorityName': 'Greenwich',\n",
      " 'LocalAuthorityWebSite': 'http://www.royalgreenwich.gov.uk',\n",
      " 'NewRatingPending': True,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'SE18 7DY',\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('6657a60f8e1a4f84b4cbc648'),\n",
      " 'geocode': {'latitude': '51.49014200', 'longitude': '0.08384000'},\n",
      " 'scores': {'ConfidenceInManagement': '', 'Hygiene': '', 'Structural': ''}}\n"
     ]
    }
   ],
   "source": [
    "# Check that the new restaurant was inserted\n",
    "pprint(establishments.find_one({\"BusinessName\": \"Penang Flavours\"}))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "id": "f9a3f7a1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "New restaurant found:\n",
      "{'AddressLine1': 'Penang Flavours',\n",
      " 'AddressLine2': '146A Plumstead Rd',\n",
      " 'AddressLine3': 'London',\n",
      " 'AddressLine4': '',\n",
      " 'BusinessName': 'Penang Flavours',\n",
      " 'BusinessType': 'Restaurant/Cafe/Canteen',\n",
      " 'BusinessTypeID': '',\n",
      " 'Distance': 4623.972328074718,\n",
      " 'LocalAuthorityCode': '511',\n",
      " 'LocalAuthorityEmailAddress': 'health@royalgreenwich.gov.uk',\n",
      " 'LocalAuthorityName': 'Greenwich',\n",
      " 'LocalAuthorityWebSite': 'http://www.royalgreenwich.gov.uk',\n",
      " 'NewRatingPending': True,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'SE18 7DY',\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('6657a60f8e1a4f84b4cbc648'),\n",
      " 'geocode': {'latitude': '51.49014200', 'longitude': '0.08384000'},\n",
      " 'scores': {'ConfidenceInManagement': '', 'Hygiene': '', 'Structural': ''}}\n"
     ]
    }
   ],
   "source": [
    "# Query the establishments collection for the new restaurant\n",
    "query = {\"BusinessName\": \"Penang Flavours\"}\n",
    "new_restaurant = establishments.find_one(query)\n",
    "\n",
    "# Check if the restaurant was found\n",
    "if new_restaurant:\n",
    "    print(\"New restaurant found:\")\n",
    "    pprint(new_restaurant)\n",
    "else:\n",
    "    print(\"New restaurant not found in the database.\")"
   ]
  },
  {
   "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": 266,
   "id": "93c63829",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'BusinessType': 'Restaurant/Cafe/Canteen', 'BusinessTypeID': 1}\n"
     ]
    }
   ],
   "source": [
    "# Find the BusinessTypeID for \"Restaurant/Cafe/Canteen\" and return only the BusinessTypeID and BusinessType fields\n",
    "query = {\"BusinessType\": \"Restaurant/Cafe/Canteen\"}\n",
    "projection = {\"BusinessTypeID\": 1, \"BusinessType\": 1, \"_id\": 0}\n",
    "\n",
    "# Execute the query\n",
    "result = establishments.find_one(query, projection)\n",
    "\n",
    "# Print the result\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e570caf",
   "metadata": {},
   "source": [
    "3. Update the new restaurant with the `BusinessTypeID` you found."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 267,
   "id": "7f599f3d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Restaurant updated successfully.\n"
     ]
    }
   ],
   "source": [
    "# Update the new restaurant with the correct BusinessTypeID\n",
    "# Define the updated BusinessTypeID\n",
    "updated_business_type_id = 1\n",
    "\n",
    "# Update the new restaurant with the correct BusinessTypeID\n",
    "update_query = {\"BusinessName\": \"Penang Flavours\"}\n",
    "update_values = {\"$set\": {\"BusinessTypeID\": updated_business_type_id}}\n",
    "\n",
    "# Perform the update operation\n",
    "update_result = establishments.update_one(update_query, update_values)\n",
    "\n",
    "# Check if the update was successful\n",
    "if update_result.modified_count > 0:\n",
    "    print(\"Restaurant updated successfully.\")\n",
    "else:\n",
    "    print(\"Restaurant not updated. It may not exist or the provided BusinessTypeID is incorrect.\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 268,
   "id": "a6ad16f7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'AddressLine1': 'Penang Flavours',\n",
      " 'AddressLine2': '146A Plumstead Rd',\n",
      " 'AddressLine3': 'London',\n",
      " 'AddressLine4': '',\n",
      " 'BusinessName': 'Penang Flavours',\n",
      " 'BusinessType': 'Restaurant/Cafe/Canteen',\n",
      " 'BusinessTypeID': 1,\n",
      " 'Distance': 4623.972328074718,\n",
      " 'LocalAuthorityCode': '511',\n",
      " 'LocalAuthorityEmailAddress': 'health@royalgreenwich.gov.uk',\n",
      " 'LocalAuthorityName': 'Greenwich',\n",
      " 'LocalAuthorityWebSite': 'http://www.royalgreenwich.gov.uk',\n",
      " 'NewRatingPending': True,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'SE18 7DY',\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('6657a60f8e1a4f84b4cbc648'),\n",
      " 'geocode': {'latitude': '51.49014200', 'longitude': '0.08384000'},\n",
      " 'scores': {'ConfidenceInManagement': '', 'Hygiene': '', 'Structural': ''}}\n"
     ]
    }
   ],
   "source": [
    "# Confirm that the new restaurant was updated\n",
    "pprint(establishments.find_one({\"BusinessName\": \"Penang Flavours\"}))"
   ]
  },
  {
   "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": 269,
   "id": "768474a6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of documents of Establishments in 'Dover': 994\n"
     ]
    }
   ],
   "source": [
    "# Find how many documents have LocalAuthorityName as \"Dover\"\n",
    "dover_count = establishments.count_documents({\"LocalAuthorityName\": \"Dover\"})\n",
    "\n",
    "print(\"Number of documents of Establishments in 'Dover':\", dover_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 270,
   "id": "a11d5f1b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of documents deleted: 994\n"
     ]
    }
   ],
   "source": [
    "# Delete all documents where LocalAuthorityName is \"Dover\"\n",
    "delete_result = establishments.delete_many({\"LocalAuthorityName\": \"Dover\"})\n",
    "\n",
    "# Check the number of documents deleted\n",
    "print(\"Number of documents deleted:\", delete_result.deleted_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "id": "2a51aba0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of establishments in Dover after deletion: 0\n"
     ]
    }
   ],
   "source": [
    "# Check if any remaining documents include Dover\n",
    "remaining_dover_count = establishments.count_documents({\"LocalAuthorityName\": \"Dover\"})\n",
    "print(f\"Number of establishments in Dover after deletion: {remaining_dover_count}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 272,
   "id": "8c356f7b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Remaining document:\n",
      "{'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('6657a60df80d6b1f3ad69d8d'),\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",
    "remaining_document = establishments.find_one()\n",
    "\n",
    "# Print the remaining document\n",
    "print(\"Remaining document:\")\n",
    "pprint(remaining_document)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "e9f1662d",
   "metadata": {},
   "source": [
    "5. Some of the number values are stored as strings, when they should be stored as numbers."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "c46df2df",
   "metadata": {},
   "source": [
    "Use `update_many` to convert `latitude` and `longitude` to decimal numbers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 273,
   "id": "48bba930",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "UpdateResult({'n': 38786, 'nModified': 38786, 'ok': 1.0, 'updatedExisting': True}, acknowledged=True)"
      ]
     },
     "execution_count": 273,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "establishments.update_many(\n",
    "    {},\n",
    "    [\n",
    "        {\"$set\": {\n",
    "            \"geocode.longitude\": {\"$toDouble\": \"$geocode.longitude\"},\n",
    "            \"geocode.latitude\": {\"$toDouble\": \"$geocode.latitude\"}\n",
    "        }}\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 274,
   "id": "fd4112ce",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of documents updated: 1\n"
     ]
    }
   ],
   "source": [
    "# Change the data type from String to Decimal for longitude and latitude\n",
    "updated_result = establishments.update_many(\n",
    "    {},\n",
    "    [\n",
    "        {\"$set\": {\n",
    "            \"geocode.longitude\": {\"$toDouble\": \"$geocode.longitude\"},\n",
    "            \"geocode.latitude\": {\"$toDouble\": \"$geocode.latitude\"}\n",
    "        }}\n",
    "    ]\n",
    ")\n",
    "\n",
    "print(\"Number of documents updated:\", update_result.modified_count)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "142e319c",
   "metadata": {},
   "source": [
    "Use `update_many` to convert `RatingValue` to integer numbers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "id": "04430a5b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "UpdateResult({'n': 4091, 'nModified': 4091, 'ok': 1.0, 'updatedExisting': True}, acknowledged=True)"
      ]
     },
     "execution_count": 275,
     "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": 276,
   "id": "92524ccc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "UpdateResult({'n': 34694, 'nModified': 34694, 'ok': 1.0, 'updatedExisting': True}, acknowledged=True)"
      ]
     },
     "execution_count": 276,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Change the data type from String to Integer for RatingValue\n",
    "establishments.update_many(\n",
    "    {\"RatingValue\": {\"$type\": \"string\"}},\n",
    "    [{\"$set\": {\"RatingValue\": {\"$toInt\": \"$RatingValue\"}}}]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 277,
   "id": "0deacaf9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'AddressLine1': 'Penang Flavours',\n",
      " 'AddressLine2': '146A Plumstead Rd',\n",
      " 'AddressLine3': 'London',\n",
      " 'AddressLine4': '',\n",
      " 'BusinessName': 'Penang Flavours',\n",
      " 'BusinessType': 'Restaurant/Cafe/Canteen',\n",
      " 'BusinessTypeID': 1,\n",
      " 'Distance': 4623.972328074718,\n",
      " 'LocalAuthorityCode': '511',\n",
      " 'LocalAuthorityEmailAddress': 'health@royalgreenwich.gov.uk',\n",
      " 'LocalAuthorityName': 'Greenwich',\n",
      " 'LocalAuthorityWebSite': 'http://www.royalgreenwich.gov.uk',\n",
      " 'NewRatingPending': True,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'SE18 7DY',\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('6657a60f8e1a4f84b4cbc648'),\n",
      " 'geocode': {'latitude': 51.490142, 'longitude': 0.08384},\n",
      " 'scores': {'ConfidenceInManagement': '', 'Hygiene': '', 'Structural': ''}}\n",
      "{'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('6657a60df80d6b1f3ad69d8d'),\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({\"BusinessName\": \"Penang Flavours\"}))\n",
    "pprint(establishments.find_one({\"RatingValue\": {\"$type\": \"int\"}}))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "dev",
   "language": "python",
   "name": "dev"
  },
  "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.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}