NoSQL_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "9a57c4c6",
   "metadata": {},
   "source": [
    "# Eat Safe, Love"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "064ef857",
   "metadata": {},
   "source": [
    "## Notebook Set Up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3ecdd008",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import dependencies\n",
    "from pymongo import MongoClient\n",
    "from pprint import pprint\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "59390c4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create an instance of MongoClient\n",
    "mongo = MongoClient(port=27017)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "71e373c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# assign the uk_food database to a variable name\n",
    "food_db = mongo['uk_food']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "18ab463c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['establishments']\n"
     ]
    }
   ],
   "source": [
    "# review the collections in our database\n",
    "print(food_db.list_collection_names())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "11c9f11b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# assign the collection to a variable\n",
    "establishments = food_db['establishments']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5671434",
   "metadata": {},
   "source": [
    "## Part 3: Exploratory Analysis\n",
    "Unless otherwise stated, for each question: \n",
    "* Use `count_documents` to display the number of documents contained in the result.\n",
    "* Display the first document in the results using `pprint`.\n",
    "* Convert the result to a Pandas DataFrame, print the number of rows in the DataFrame, and display the first 10 rows."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "314bd884",
   "metadata": {},
   "source": [
    "### 1. Which establishments have a hygiene score equal to 20?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c2da7648",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 41 establishments with a hygiene score of 20.\n",
      "{'AddressLine1': '5-6 Southfields Road',\n",
      " 'AddressLine2': 'Eastbourne',\n",
      " 'AddressLine3': 'East Sussex',\n",
      " 'AddressLine4': '',\n",
      " 'BusinessName': 'The Chase Rest Home',\n",
      " 'BusinessType': 'Caring Premises',\n",
      " 'BusinessTypeID': 5,\n",
      " 'ChangesByServerID': 0,\n",
      " 'Distance': 4613.888288172291,\n",
      " 'FHRSID': 110681,\n",
      " 'LocalAuthorityBusinessID': '4029',\n",
      " 'LocalAuthorityCode': '102',\n",
      " 'LocalAuthorityEmailAddress': 'Customerfirst@eastbourne.gov.uk',\n",
      " 'LocalAuthorityName': 'Eastbourne',\n",
      " 'LocalAuthorityWebSite': 'http://www.eastbourne.gov.uk/foodratings',\n",
      " 'NewRatingPending': False,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'BN21 1BU',\n",
      " 'RatingDate': '2021-09-23T00:00:00',\n",
      " 'RatingKey': 'fhrs_0_en-gb',\n",
      " 'RatingValue': '0',\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('6407e66d50f18daefab78b49'),\n",
      " 'geocode': {'latitude': 50.769705, 'longitude': 0.27694},\n",
      " 'links': [{'href': 'https://api.ratings.food.gov.uk/establishments/110681',\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': 20, 'Hygiene': 20, 'Structural': 20}}\n"
     ]
    }
   ],
   "source": [
    "# Find the establishments with a hygiene score of 20\n",
    "query = {'scores.Hygiene': 20}\n",
    "\n",
    "# Use count_documents to display the number of documents in the result\n",
    "count = establishments.count_documents(query)\n",
    "print(f\"There are {count} establishments with a hygiene score of 20.\")\n",
    "\n",
    "# Display the first document in the results using pprint\n",
    "pprint(establishments.find_one(query))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a6e5cece",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 41 rows in this DataFrame.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>FHRSID</th>\n",
       "      <th>ChangesByServerID</th>\n",
       "      <th>LocalAuthorityBusinessID</th>\n",
       "      <th>BusinessName</th>\n",
       "      <th>BusinessType</th>\n",
       "      <th>BusinessTypeID</th>\n",
       "      <th>AddressLine1</th>\n",
       "      <th>AddressLine2</th>\n",
       "      <th>AddressLine3</th>\n",
       "      <th>...</th>\n",
       "      <th>LocalAuthorityWebSite</th>\n",
       "      <th>LocalAuthorityEmailAddress</th>\n",
       "      <th>scores</th>\n",
       "      <th>SchemeType</th>\n",
       "      <th>geocode</th>\n",
       "      <th>RightToReply</th>\n",
       "      <th>Distance</th>\n",
       "      <th>NewRatingPending</th>\n",
       "      <th>meta</th>\n",
       "      <th>links</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6407e66d50f18daefab78b49</td>\n",
       "      <td>110681</td>\n",
       "      <td>0</td>\n",
       "      <td>4029</td>\n",
       "      <td>The Chase Rest Home</td>\n",
       "      <td>Caring Premises</td>\n",
       "      <td>5</td>\n",
       "      <td>5-6 Southfields Road</td>\n",
       "      <td>Eastbourne</td>\n",
       "      <td>East Sussex</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.eastbourne.gov.uk/foodratings</td>\n",
       "      <td>Customerfirst@eastbourne.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 20, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.27694, 'latitude': 50.769705}</td>\n",
       "      <td></td>\n",
       "      <td>4613.888288</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'https://api.ratings....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6407e66d50f18daefab78ecb</td>\n",
       "      <td>612039</td>\n",
       "      <td>0</td>\n",
       "      <td>1970/FOOD</td>\n",
       "      <td>Brenalwood</td>\n",
       "      <td>Caring Premises</td>\n",
       "      <td>5</td>\n",
       "      <td>Hall Lane</td>\n",
       "      <td>Walton-on-the-Naze</td>\n",
       "      <td>Essex</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.tendringdc.gov.uk/</td>\n",
       "      <td>fhsadmin@tendringdc.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 15, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 1.278721, 'latitude': 51.857536}</td>\n",
       "      <td></td>\n",
       "      <td>4617.965824</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'https://api.ratings....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6407e66d50f18daefab791d7</td>\n",
       "      <td>730933</td>\n",
       "      <td>0</td>\n",
       "      <td>1698/FOOD</td>\n",
       "      <td>Melrose Hotel</td>\n",
       "      <td>Hotel/bed &amp; breakfast/guest house</td>\n",
       "      <td>7842</td>\n",
       "      <td>53 Marine Parade East</td>\n",
       "      <td>Clacton On Sea</td>\n",
       "      <td>Essex</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.tendringdc.gov.uk/</td>\n",
       "      <td>fhsadmin@tendringdc.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 20, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 1.15927, 'latitude': 51.789429}</td>\n",
       "      <td></td>\n",
       "      <td>4619.656144</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'https://api.ratings....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6407e66d50f18daefab793c3</td>\n",
       "      <td>172735</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000023858</td>\n",
       "      <td>Seaford Pizza</td>\n",
       "      <td>Takeaway/sandwich shop</td>\n",
       "      <td>7844</td>\n",
       "      <td>4 High Street</td>\n",
       "      <td>Seaford</td>\n",
       "      <td>East Sussex</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.lewes-eastbourne.gov.uk/</td>\n",
       "      <td>ehealth.ldc@lewes-eastbourne.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 10, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.10202, 'latitude': 50.770885}</td>\n",
       "      <td></td>\n",
       "      <td>4620.421725</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'https://api.ratings....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6407e66d50f18daefab793d0</td>\n",
       "      <td>172953</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000024532</td>\n",
       "      <td>Golden Palace</td>\n",
       "      <td>Restaurant/Cafe/Canteen</td>\n",
       "      <td>1</td>\n",
       "      <td>5 South Street</td>\n",
       "      <td>Seaford</td>\n",
       "      <td>East Sussex</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.lewes-eastbourne.gov.uk/</td>\n",
       "      <td>ehealth.ldc@lewes-eastbourne.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 10, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.101446, 'latitude': 50.770724}</td>\n",
       "      <td></td>\n",
       "      <td>4620.437179</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'https://api.ratings....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6407e66d50f18daefab79d72</td>\n",
       "      <td>512854</td>\n",
       "      <td>0</td>\n",
       "      <td>12/00816/BUTH</td>\n",
       "      <td>Ashby's Butchers</td>\n",
       "      <td>Retailers - other</td>\n",
       "      <td>4613</td>\n",
       "      <td>777 Southchurch Road</td>\n",
       "      <td>Southend-On-Sea</td>\n",
       "      <td>Essex</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.southend.gov.uk</td>\n",
       "      <td>EnvironmentalHealth@southend.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 20, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.736349, 'latitude': 51.541448}</td>\n",
       "      <td></td>\n",
       "      <td>4625.565258</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'https://api.ratings....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6407e66d50f18daefab79f95</td>\n",
       "      <td>1537089</td>\n",
       "      <td>0</td>\n",
       "      <td>22/00224/RESTUN</td>\n",
       "      <td>South Sea Express Cuisine</td>\n",
       "      <td>Restaurant/Cafe/Canteen</td>\n",
       "      <td>1</td>\n",
       "      <td>33 Alexandra Street</td>\n",
       "      <td>Southend-on-sea</td>\n",
       "      <td>Essex</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.southend.gov.uk</td>\n",
       "      <td>EnvironmentalHealth@southend.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 20, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.7121671, 'latitude': 51.5350065}</td>\n",
       "      <td></td>\n",
       "      <td>4626.200132</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'https://api.ratings....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6407e66e50f18daefab7b4bc</td>\n",
       "      <td>155648</td>\n",
       "      <td>0</td>\n",
       "      <td>EH/00006058</td>\n",
       "      <td>Golden Palace</td>\n",
       "      <td>Takeaway/sandwich shop</td>\n",
       "      <td>7844</td>\n",
       "      <td>7 London Road</td>\n",
       "      <td>Rayleigh</td>\n",
       "      <td>Essex</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.rochford.gov.uk</td>\n",
       "      <td>customerservices@rochford.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 15, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.602364, 'latitude': 51.591515}</td>\n",
       "      <td></td>\n",
       "      <td>4632.276890</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'http://api.ratings.f...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>6407e66e50f18daefab7b908</td>\n",
       "      <td>1012883</td>\n",
       "      <td>0</td>\n",
       "      <td>17/00110/MIXED/S</td>\n",
       "      <td>The Tulip Tree</td>\n",
       "      <td>Restaurant/Cafe/Canteen</td>\n",
       "      <td>1</td>\n",
       "      <td>3 The Village</td>\n",
       "      <td>Chiddingstone</td>\n",
       "      <td>KENT</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.sevenoaks.gov.uk/</td>\n",
       "      <td>environmental.health@sevenoaks.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 5, 'ConfidenceIn...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.146449998021126, 'latitude': 5...</td>\n",
       "      <td></td>\n",
       "      <td>4633.934041</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'http://api.ratings.f...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>6407e66f50f18daefab7c113</td>\n",
       "      <td>644109</td>\n",
       "      <td>0</td>\n",
       "      <td>41811</td>\n",
       "      <td>F &amp; S</td>\n",
       "      <td>Retailers - other</td>\n",
       "      <td>4613</td>\n",
       "      <td></td>\n",
       "      <td>81 Southernhay</td>\n",
       "      <td>Basildon</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.basildon.gov.uk</td>\n",
       "      <td>ehs@basildon.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 20, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.462307, 'latitude': 51.57005}</td>\n",
       "      <td></td>\n",
       "      <td>4636.552523</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'http://api.ratings.f...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id   FHRSID  ChangesByServerID  \\\n",
       "0  6407e66d50f18daefab78b49   110681                  0   \n",
       "1  6407e66d50f18daefab78ecb   612039                  0   \n",
       "2  6407e66d50f18daefab791d7   730933                  0   \n",
       "3  6407e66d50f18daefab793c3   172735                  0   \n",
       "4  6407e66d50f18daefab793d0   172953                  0   \n",
       "5  6407e66d50f18daefab79d72   512854                  0   \n",
       "6  6407e66d50f18daefab79f95  1537089                  0   \n",
       "7  6407e66e50f18daefab7b4bc   155648                  0   \n",
       "8  6407e66e50f18daefab7b908  1012883                  0   \n",
       "9  6407e66f50f18daefab7c113   644109                  0   \n",
       "\n",
       "  LocalAuthorityBusinessID               BusinessName  \\\n",
       "0                     4029        The Chase Rest Home   \n",
       "1                1970/FOOD                 Brenalwood   \n",
       "2                1698/FOOD              Melrose Hotel   \n",
       "3             PI/000023858              Seaford Pizza   \n",
       "4             PI/000024532              Golden Palace   \n",
       "5            12/00816/BUTH           Ashby's Butchers   \n",
       "6          22/00224/RESTUN  South Sea Express Cuisine   \n",
       "7              EH/00006058              Golden Palace   \n",
       "8         17/00110/MIXED/S             The Tulip Tree   \n",
       "9                    41811                      F & S   \n",
       "\n",
       "                        BusinessType  BusinessTypeID           AddressLine1  \\\n",
       "0                    Caring Premises               5   5-6 Southfields Road   \n",
       "1                    Caring Premises               5              Hall Lane   \n",
       "2  Hotel/bed & breakfast/guest house            7842  53 Marine Parade East   \n",
       "3             Takeaway/sandwich shop            7844          4 High Street   \n",
       "4            Restaurant/Cafe/Canteen               1         5 South Street   \n",
       "5                  Retailers - other            4613   777 Southchurch Road   \n",
       "6            Restaurant/Cafe/Canteen               1    33 Alexandra Street   \n",
       "7             Takeaway/sandwich shop            7844          7 London Road   \n",
       "8            Restaurant/Cafe/Canteen               1          3 The Village   \n",
       "9                  Retailers - other            4613                          \n",
       "\n",
       "         AddressLine2 AddressLine3  ...  \\\n",
       "0          Eastbourne  East Sussex  ...   \n",
       "1  Walton-on-the-Naze        Essex  ...   \n",
       "2      Clacton On Sea        Essex  ...   \n",
       "3             Seaford  East Sussex  ...   \n",
       "4             Seaford  East Sussex  ...   \n",
       "5     Southend-On-Sea        Essex  ...   \n",
       "6     Southend-on-sea        Essex  ...   \n",
       "7            Rayleigh        Essex  ...   \n",
       "8       Chiddingstone         KENT  ...   \n",
       "9      81 Southernhay     Basildon  ...   \n",
       "\n",
       "                      LocalAuthorityWebSite  \\\n",
       "0  http://www.eastbourne.gov.uk/foodratings   \n",
       "1             http://www.tendringdc.gov.uk/   \n",
       "2             http://www.tendringdc.gov.uk/   \n",
       "3       http://www.lewes-eastbourne.gov.uk/   \n",
       "4       http://www.lewes-eastbourne.gov.uk/   \n",
       "5                http://www.southend.gov.uk   \n",
       "6                http://www.southend.gov.uk   \n",
       "7                http://www.rochford.gov.uk   \n",
       "8              http://www.sevenoaks.gov.uk/   \n",
       "9                http://www.basildon.gov.uk   \n",
       "\n",
       "              LocalAuthorityEmailAddress  \\\n",
       "0        Customerfirst@eastbourne.gov.uk   \n",
       "1             fhsadmin@tendringdc.gov.uk   \n",
       "2             fhsadmin@tendringdc.gov.uk   \n",
       "3    ehealth.ldc@lewes-eastbourne.gov.uk   \n",
       "4    ehealth.ldc@lewes-eastbourne.gov.uk   \n",
       "5    EnvironmentalHealth@southend.gov.uk   \n",
       "6    EnvironmentalHealth@southend.gov.uk   \n",
       "7       customerservices@rochford.gov.uk   \n",
       "8  environmental.health@sevenoaks.gov.uk   \n",
       "9                    ehs@basildon.gov.uk   \n",
       "\n",
       "                                              scores SchemeType  \\\n",
       "0  {'Hygiene': 20, 'Structural': 20, 'ConfidenceI...       FHRS   \n",
       "1  {'Hygiene': 20, 'Structural': 15, 'ConfidenceI...       FHRS   \n",
       "2  {'Hygiene': 20, 'Structural': 20, 'ConfidenceI...       FHRS   \n",
       "3  {'Hygiene': 20, 'Structural': 10, 'ConfidenceI...       FHRS   \n",
       "4  {'Hygiene': 20, 'Structural': 10, 'ConfidenceI...       FHRS   \n",
       "5  {'Hygiene': 20, 'Structural': 20, 'ConfidenceI...       FHRS   \n",
       "6  {'Hygiene': 20, 'Structural': 20, 'ConfidenceI...       FHRS   \n",
       "7  {'Hygiene': 20, 'Structural': 15, 'ConfidenceI...       FHRS   \n",
       "8  {'Hygiene': 20, 'Structural': 5, 'ConfidenceIn...       FHRS   \n",
       "9  {'Hygiene': 20, 'Structural': 20, 'ConfidenceI...       FHRS   \n",
       "\n",
       "                                             geocode RightToReply  \\\n",
       "0      {'longitude': 0.27694, 'latitude': 50.769705}                \n",
       "1     {'longitude': 1.278721, 'latitude': 51.857536}                \n",
       "2      {'longitude': 1.15927, 'latitude': 51.789429}                \n",
       "3      {'longitude': 0.10202, 'latitude': 50.770885}                \n",
       "4     {'longitude': 0.101446, 'latitude': 50.770724}                \n",
       "5     {'longitude': 0.736349, 'latitude': 51.541448}                \n",
       "6   {'longitude': 0.7121671, 'latitude': 51.5350065}                \n",
       "7     {'longitude': 0.602364, 'latitude': 51.591515}                \n",
       "8  {'longitude': 0.146449998021126, 'latitude': 5...                \n",
       "9      {'longitude': 0.462307, 'latitude': 51.57005}                \n",
       "\n",
       "      Distance NewRatingPending  \\\n",
       "0  4613.888288            False   \n",
       "1  4617.965824            False   \n",
       "2  4619.656144            False   \n",
       "3  4620.421725            False   \n",
       "4  4620.437179            False   \n",
       "5  4625.565258            False   \n",
       "6  4626.200132            False   \n",
       "7  4632.276890            False   \n",
       "8  4633.934041            False   \n",
       "9  4636.552523            False   \n",
       "\n",
       "                                                meta  \\\n",
       "0  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "1  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "2  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "3  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "4  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "5  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "6  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "7  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "8  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "9  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "\n",
       "                                               links  \n",
       "0  [{'rel': 'self', 'href': 'https://api.ratings....  \n",
       "1  [{'rel': 'self', 'href': 'https://api.ratings....  \n",
       "2  [{'rel': 'self', 'href': 'https://api.ratings....  \n",
       "3  [{'rel': 'self', 'href': 'https://api.ratings....  \n",
       "4  [{'rel': 'self', 'href': 'https://api.ratings....  \n",
       "5  [{'rel': 'self', 'href': 'https://api.ratings....  \n",
       "6  [{'rel': 'self', 'href': 'https://api.ratings....  \n",
       "7  [{'rel': 'self', 'href': 'http://api.ratings.f...  \n",
       "8  [{'rel': 'self', 'href': 'http://api.ratings.f...  \n",
       "9  [{'rel': 'self', 'href': 'http://api.ratings.f...  \n",
       "\n",
       "[10 rows x 28 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the result to a Pandas DataFrame\n",
    "df = pd.DataFrame(establishments.find(query))\n",
    "\n",
    "# Display the number of rows in the DataFrame\n",
    "print(f\"There are {len(df)} rows in this DataFrame.\")\n",
    "\n",
    "# Display the first 10 rows of the DataFrame\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ef4395a",
   "metadata": {},
   "source": [
    "### 2. Which establishments in London have a `RatingValue` greater than or equal to 4?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "93c63829",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 34 establishments in London that have a RatingValue greater than or equal to 4.\n",
      "{'AddressLine1': 'Oak Apple Farm Building 103 Sheernes Docks',\n",
      " 'AddressLine2': 'Sheppy Kent',\n",
      " 'AddressLine3': '',\n",
      " 'AddressLine4': '',\n",
      " 'BusinessName': \"Charlie's\",\n",
      " 'BusinessType': 'Other catering premises',\n",
      " 'BusinessTypeID': 7841,\n",
      " 'ChangesByServerID': 0,\n",
      " 'Distance': 4627.439467780196,\n",
      " 'FHRSID': 621707,\n",
      " 'LocalAuthorityBusinessID': 'PI/000025307',\n",
      " 'LocalAuthorityCode': '508',\n",
      " 'LocalAuthorityEmailAddress': 'publicprotection@cityoflondon.gov.uk',\n",
      " 'LocalAuthorityName': 'City of London Corporation',\n",
      " 'LocalAuthorityWebSite': 'http://www.cityoflondon.gov.uk/Corporation/homepage.htm',\n",
      " 'NewRatingPending': False,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'ME12',\n",
      " 'RatingDate': '2021-10-18T00:00:00',\n",
      " 'RatingKey': 'fhrs_4_en-gb',\n",
      " 'RatingValue': '4',\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('6407e66e50f18daefab7a562'),\n",
      " 'geocode': {'latitude': 51.369321, 'longitude': 0.508551},\n",
      " 'links': [{'href': 'https://api.ratings.food.gov.uk/establishments/621707',\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': 10}}\n"
     ]
    }
   ],
   "source": [
    "# Find the establishments with London as the Local Authority and has a RatingValue greater than or equal to 4.\n",
    "query2 = {'LocalAuthorityName': {'$regex': 'London'}, \n",
    "          'RatingValue': {'$gte': '4'}\n",
    "         }\n",
    "\n",
    "# Use count_documents to display the number of documents in the result\n",
    "count2 = establishments.count_documents(query2)\n",
    "print(f\"There are {count2} establishments in London that have a RatingValue greater than or equal to 4.\")\n",
    "\n",
    "# Display the first document in the results using pprint\n",
    "pprint(establishments.find_one(query2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "07e24ccb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 34 rows in this DataFrame.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>FHRSID</th>\n",
       "      <th>ChangesByServerID</th>\n",
       "      <th>LocalAuthorityBusinessID</th>\n",
       "      <th>BusinessName</th>\n",
       "      <th>BusinessType</th>\n",
       "      <th>BusinessTypeID</th>\n",
       "      <th>AddressLine1</th>\n",
       "      <th>AddressLine2</th>\n",
       "      <th>AddressLine3</th>\n",
       "      <th>...</th>\n",
       "      <th>LocalAuthorityWebSite</th>\n",
       "      <th>LocalAuthorityEmailAddress</th>\n",
       "      <th>scores</th>\n",
       "      <th>SchemeType</th>\n",
       "      <th>geocode</th>\n",
       "      <th>RightToReply</th>\n",
       "      <th>Distance</th>\n",
       "      <th>NewRatingPending</th>\n",
       "      <th>meta</th>\n",
       "      <th>links</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6407e66d50f18daefab78b49</td>\n",
       "      <td>110681</td>\n",
       "      <td>0</td>\n",
       "      <td>4029</td>\n",
       "      <td>The Chase Rest Home</td>\n",
       "      <td>Caring Premises</td>\n",
       "      <td>5</td>\n",
       "      <td>5-6 Southfields Road</td>\n",
       "      <td>Eastbourne</td>\n",
       "      <td>East Sussex</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.eastbourne.gov.uk/foodratings</td>\n",
       "      <td>Customerfirst@eastbourne.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 20, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.27694, 'latitude': 50.769705}</td>\n",
       "      <td></td>\n",
       "      <td>4613.888288</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'https://api.ratings....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6407e66d50f18daefab78ecb</td>\n",
       "      <td>612039</td>\n",
       "      <td>0</td>\n",
       "      <td>1970/FOOD</td>\n",
       "      <td>Brenalwood</td>\n",
       "      <td>Caring Premises</td>\n",
       "      <td>5</td>\n",
       "      <td>Hall Lane</td>\n",
       "      <td>Walton-on-the-Naze</td>\n",
       "      <td>Essex</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.tendringdc.gov.uk/</td>\n",
       "      <td>fhsadmin@tendringdc.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 15, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 1.278721, 'latitude': 51.857536}</td>\n",
       "      <td></td>\n",
       "      <td>4617.965824</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'https://api.ratings....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6407e66d50f18daefab791d7</td>\n",
       "      <td>730933</td>\n",
       "      <td>0</td>\n",
       "      <td>1698/FOOD</td>\n",
       "      <td>Melrose Hotel</td>\n",
       "      <td>Hotel/bed &amp; breakfast/guest house</td>\n",
       "      <td>7842</td>\n",
       "      <td>53 Marine Parade East</td>\n",
       "      <td>Clacton On Sea</td>\n",
       "      <td>Essex</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.tendringdc.gov.uk/</td>\n",
       "      <td>fhsadmin@tendringdc.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 20, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 1.15927, 'latitude': 51.789429}</td>\n",
       "      <td></td>\n",
       "      <td>4619.656144</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'https://api.ratings....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6407e66d50f18daefab793c3</td>\n",
       "      <td>172735</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000023858</td>\n",
       "      <td>Seaford Pizza</td>\n",
       "      <td>Takeaway/sandwich shop</td>\n",
       "      <td>7844</td>\n",
       "      <td>4 High Street</td>\n",
       "      <td>Seaford</td>\n",
       "      <td>East Sussex</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.lewes-eastbourne.gov.uk/</td>\n",
       "      <td>ehealth.ldc@lewes-eastbourne.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 10, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.10202, 'latitude': 50.770885}</td>\n",
       "      <td></td>\n",
       "      <td>4620.421725</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'https://api.ratings....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6407e66d50f18daefab793d0</td>\n",
       "      <td>172953</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000024532</td>\n",
       "      <td>Golden Palace</td>\n",
       "      <td>Restaurant/Cafe/Canteen</td>\n",
       "      <td>1</td>\n",
       "      <td>5 South Street</td>\n",
       "      <td>Seaford</td>\n",
       "      <td>East Sussex</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.lewes-eastbourne.gov.uk/</td>\n",
       "      <td>ehealth.ldc@lewes-eastbourne.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 10, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.101446, 'latitude': 50.770724}</td>\n",
       "      <td></td>\n",
       "      <td>4620.437179</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'https://api.ratings....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6407e66d50f18daefab79d72</td>\n",
       "      <td>512854</td>\n",
       "      <td>0</td>\n",
       "      <td>12/00816/BUTH</td>\n",
       "      <td>Ashby's Butchers</td>\n",
       "      <td>Retailers - other</td>\n",
       "      <td>4613</td>\n",
       "      <td>777 Southchurch Road</td>\n",
       "      <td>Southend-On-Sea</td>\n",
       "      <td>Essex</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.southend.gov.uk</td>\n",
       "      <td>EnvironmentalHealth@southend.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 20, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.736349, 'latitude': 51.541448}</td>\n",
       "      <td></td>\n",
       "      <td>4625.565258</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'https://api.ratings....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6407e66d50f18daefab79f95</td>\n",
       "      <td>1537089</td>\n",
       "      <td>0</td>\n",
       "      <td>22/00224/RESTUN</td>\n",
       "      <td>South Sea Express Cuisine</td>\n",
       "      <td>Restaurant/Cafe/Canteen</td>\n",
       "      <td>1</td>\n",
       "      <td>33 Alexandra Street</td>\n",
       "      <td>Southend-on-sea</td>\n",
       "      <td>Essex</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.southend.gov.uk</td>\n",
       "      <td>EnvironmentalHealth@southend.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 20, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.7121671, 'latitude': 51.5350065}</td>\n",
       "      <td></td>\n",
       "      <td>4626.200132</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'https://api.ratings....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6407e66e50f18daefab7b4bc</td>\n",
       "      <td>155648</td>\n",
       "      <td>0</td>\n",
       "      <td>EH/00006058</td>\n",
       "      <td>Golden Palace</td>\n",
       "      <td>Takeaway/sandwich shop</td>\n",
       "      <td>7844</td>\n",
       "      <td>7 London Road</td>\n",
       "      <td>Rayleigh</td>\n",
       "      <td>Essex</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.rochford.gov.uk</td>\n",
       "      <td>customerservices@rochford.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 15, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.602364, 'latitude': 51.591515}</td>\n",
       "      <td></td>\n",
       "      <td>4632.276890</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'http://api.ratings.f...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>6407e66e50f18daefab7b908</td>\n",
       "      <td>1012883</td>\n",
       "      <td>0</td>\n",
       "      <td>17/00110/MIXED/S</td>\n",
       "      <td>The Tulip Tree</td>\n",
       "      <td>Restaurant/Cafe/Canteen</td>\n",
       "      <td>1</td>\n",
       "      <td>3 The Village</td>\n",
       "      <td>Chiddingstone</td>\n",
       "      <td>KENT</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.sevenoaks.gov.uk/</td>\n",
       "      <td>environmental.health@sevenoaks.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 5, 'ConfidenceIn...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.146449998021126, 'latitude': 5...</td>\n",
       "      <td></td>\n",
       "      <td>4633.934041</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'http://api.ratings.f...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>6407e66f50f18daefab7c113</td>\n",
       "      <td>644109</td>\n",
       "      <td>0</td>\n",
       "      <td>41811</td>\n",
       "      <td>F &amp; S</td>\n",
       "      <td>Retailers - other</td>\n",
       "      <td>4613</td>\n",
       "      <td></td>\n",
       "      <td>81 Southernhay</td>\n",
       "      <td>Basildon</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.basildon.gov.uk</td>\n",
       "      <td>ehs@basildon.gov.uk</td>\n",
       "      <td>{'Hygiene': 20, 'Structural': 20, 'ConfidenceI...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.462307, 'latitude': 51.57005}</td>\n",
       "      <td></td>\n",
       "      <td>4636.552523</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'http://api.ratings.f...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id   FHRSID  ChangesByServerID  \\\n",
       "0  6407e66d50f18daefab78b49   110681                  0   \n",
       "1  6407e66d50f18daefab78ecb   612039                  0   \n",
       "2  6407e66d50f18daefab791d7   730933                  0   \n",
       "3  6407e66d50f18daefab793c3   172735                  0   \n",
       "4  6407e66d50f18daefab793d0   172953                  0   \n",
       "5  6407e66d50f18daefab79d72   512854                  0   \n",
       "6  6407e66d50f18daefab79f95  1537089                  0   \n",
       "7  6407e66e50f18daefab7b4bc   155648                  0   \n",
       "8  6407e66e50f18daefab7b908  1012883                  0   \n",
       "9  6407e66f50f18daefab7c113   644109                  0   \n",
       "\n",
       "  LocalAuthorityBusinessID               BusinessName  \\\n",
       "0                     4029        The Chase Rest Home   \n",
       "1                1970/FOOD                 Brenalwood   \n",
       "2                1698/FOOD              Melrose Hotel   \n",
       "3             PI/000023858              Seaford Pizza   \n",
       "4             PI/000024532              Golden Palace   \n",
       "5            12/00816/BUTH           Ashby's Butchers   \n",
       "6          22/00224/RESTUN  South Sea Express Cuisine   \n",
       "7              EH/00006058              Golden Palace   \n",
       "8         17/00110/MIXED/S             The Tulip Tree   \n",
       "9                    41811                      F & S   \n",
       "\n",
       "                        BusinessType  BusinessTypeID           AddressLine1  \\\n",
       "0                    Caring Premises               5   5-6 Southfields Road   \n",
       "1                    Caring Premises               5              Hall Lane   \n",
       "2  Hotel/bed & breakfast/guest house            7842  53 Marine Parade East   \n",
       "3             Takeaway/sandwich shop            7844          4 High Street   \n",
       "4            Restaurant/Cafe/Canteen               1         5 South Street   \n",
       "5                  Retailers - other            4613   777 Southchurch Road   \n",
       "6            Restaurant/Cafe/Canteen               1    33 Alexandra Street   \n",
       "7             Takeaway/sandwich shop            7844          7 London Road   \n",
       "8            Restaurant/Cafe/Canteen               1          3 The Village   \n",
       "9                  Retailers - other            4613                          \n",
       "\n",
       "         AddressLine2 AddressLine3  ...  \\\n",
       "0          Eastbourne  East Sussex  ...   \n",
       "1  Walton-on-the-Naze        Essex  ...   \n",
       "2      Clacton On Sea        Essex  ...   \n",
       "3             Seaford  East Sussex  ...   \n",
       "4             Seaford  East Sussex  ...   \n",
       "5     Southend-On-Sea        Essex  ...   \n",
       "6     Southend-on-sea        Essex  ...   \n",
       "7            Rayleigh        Essex  ...   \n",
       "8       Chiddingstone         KENT  ...   \n",
       "9      81 Southernhay     Basildon  ...   \n",
       "\n",
       "                      LocalAuthorityWebSite  \\\n",
       "0  http://www.eastbourne.gov.uk/foodratings   \n",
       "1             http://www.tendringdc.gov.uk/   \n",
       "2             http://www.tendringdc.gov.uk/   \n",
       "3       http://www.lewes-eastbourne.gov.uk/   \n",
       "4       http://www.lewes-eastbourne.gov.uk/   \n",
       "5                http://www.southend.gov.uk   \n",
       "6                http://www.southend.gov.uk   \n",
       "7                http://www.rochford.gov.uk   \n",
       "8              http://www.sevenoaks.gov.uk/   \n",
       "9                http://www.basildon.gov.uk   \n",
       "\n",
       "              LocalAuthorityEmailAddress  \\\n",
       "0        Customerfirst@eastbourne.gov.uk   \n",
       "1             fhsadmin@tendringdc.gov.uk   \n",
       "2             fhsadmin@tendringdc.gov.uk   \n",
       "3    ehealth.ldc@lewes-eastbourne.gov.uk   \n",
       "4    ehealth.ldc@lewes-eastbourne.gov.uk   \n",
       "5    EnvironmentalHealth@southend.gov.uk   \n",
       "6    EnvironmentalHealth@southend.gov.uk   \n",
       "7       customerservices@rochford.gov.uk   \n",
       "8  environmental.health@sevenoaks.gov.uk   \n",
       "9                    ehs@basildon.gov.uk   \n",
       "\n",
       "                                              scores SchemeType  \\\n",
       "0  {'Hygiene': 20, 'Structural': 20, 'ConfidenceI...       FHRS   \n",
       "1  {'Hygiene': 20, 'Structural': 15, 'ConfidenceI...       FHRS   \n",
       "2  {'Hygiene': 20, 'Structural': 20, 'ConfidenceI...       FHRS   \n",
       "3  {'Hygiene': 20, 'Structural': 10, 'ConfidenceI...       FHRS   \n",
       "4  {'Hygiene': 20, 'Structural': 10, 'ConfidenceI...       FHRS   \n",
       "5  {'Hygiene': 20, 'Structural': 20, 'ConfidenceI...       FHRS   \n",
       "6  {'Hygiene': 20, 'Structural': 20, 'ConfidenceI...       FHRS   \n",
       "7  {'Hygiene': 20, 'Structural': 15, 'ConfidenceI...       FHRS   \n",
       "8  {'Hygiene': 20, 'Structural': 5, 'ConfidenceIn...       FHRS   \n",
       "9  {'Hygiene': 20, 'Structural': 20, 'ConfidenceI...       FHRS   \n",
       "\n",
       "                                             geocode RightToReply  \\\n",
       "0      {'longitude': 0.27694, 'latitude': 50.769705}                \n",
       "1     {'longitude': 1.278721, 'latitude': 51.857536}                \n",
       "2      {'longitude': 1.15927, 'latitude': 51.789429}                \n",
       "3      {'longitude': 0.10202, 'latitude': 50.770885}                \n",
       "4     {'longitude': 0.101446, 'latitude': 50.770724}                \n",
       "5     {'longitude': 0.736349, 'latitude': 51.541448}                \n",
       "6   {'longitude': 0.7121671, 'latitude': 51.5350065}                \n",
       "7     {'longitude': 0.602364, 'latitude': 51.591515}                \n",
       "8  {'longitude': 0.146449998021126, 'latitude': 5...                \n",
       "9      {'longitude': 0.462307, 'latitude': 51.57005}                \n",
       "\n",
       "      Distance NewRatingPending  \\\n",
       "0  4613.888288            False   \n",
       "1  4617.965824            False   \n",
       "2  4619.656144            False   \n",
       "3  4620.421725            False   \n",
       "4  4620.437179            False   \n",
       "5  4625.565258            False   \n",
       "6  4626.200132            False   \n",
       "7  4632.276890            False   \n",
       "8  4633.934041            False   \n",
       "9  4636.552523            False   \n",
       "\n",
       "                                                meta  \\\n",
       "0  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "1  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "2  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "3  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "4  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "5  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "6  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "7  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "8  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "9  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "\n",
       "                                               links  \n",
       "0  [{'rel': 'self', 'href': 'https://api.ratings....  \n",
       "1  [{'rel': 'self', 'href': 'https://api.ratings....  \n",
       "2  [{'rel': 'self', 'href': 'https://api.ratings....  \n",
       "3  [{'rel': 'self', 'href': 'https://api.ratings....  \n",
       "4  [{'rel': 'self', 'href': 'https://api.ratings....  \n",
       "5  [{'rel': 'self', 'href': 'https://api.ratings....  \n",
       "6  [{'rel': 'self', 'href': 'https://api.ratings....  \n",
       "7  [{'rel': 'self', 'href': 'http://api.ratings.f...  \n",
       "8  [{'rel': 'self', 'href': 'http://api.ratings.f...  \n",
       "9  [{'rel': 'self', 'href': 'http://api.ratings.f...  \n",
       "\n",
       "[10 rows x 28 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the result to a Pandas DataFrame\n",
    "df2 = pd.DataFrame(establishments.find(query2))\n",
    "\n",
    "# Display the number of rows in the DataFrame\n",
    "print(f\"There are {len(df2)} rows in this DataFrame.\")\n",
    "\n",
    "# Display the first 10 rows of the DataFrame\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e570caf",
   "metadata": {},
   "source": [
    "### 3. What are the top 5 establishments with a `RatingValue` rating value of '5', sorted by lowest hygiene score, nearest to the new restaurant added, \"Penang Flavours\"?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ba45b804",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('6407e69d470db7a778f857d6'),\n",
      " 'geocode': {'latitude': 51.490142, 'longitude': 0.08384}}\n"
     ]
    }
   ],
   "source": [
    "# Find the latitude and longitude of \"Penang Flavours\"\n",
    "pprint(establishments.find_one({'BusinessName': 'Penang Flavours'}, {'geocode.latitude', 'geocode.longitude'}))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "7f599f3d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'AddressLine1': '130 - 132 Plumstead High Street',\n",
      " 'AddressLine2': '',\n",
      " 'AddressLine3': 'Plumstead',\n",
      " 'AddressLine4': 'Greenwich',\n",
      " 'BusinessName': 'Volunteer',\n",
      " 'BusinessType': 'Pub/bar/nightclub',\n",
      " 'BusinessTypeID': 7843,\n",
      " 'ChangesByServerID': 0,\n",
      " 'Distance': 4646.965634598608,\n",
      " 'FHRSID': 694609,\n",
      " 'LocalAuthorityBusinessID': 'PI/000116619',\n",
      " 'LocalAuthorityCode': '511',\n",
      " 'LocalAuthorityEmailAddress': 'health@royalgreenwich.gov.uk',\n",
      " 'LocalAuthorityName': 'Greenwich',\n",
      " 'LocalAuthorityWebSite': 'http://www.royalgreenwich.gov.uk',\n",
      " 'NewRatingPending': False,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'SE18 1JQ',\n",
      " 'RatingDate': '2019-08-05T00:00:00',\n",
      " 'RatingKey': 'fhrs_5_en-gb',\n",
      " 'RatingValue': '5',\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('6407e67050f18daefab7e3a8'),\n",
      " 'geocode': {'latitude': 51.4873437, 'longitude': 0.09208},\n",
      " 'links': [{'href': 'http://api.ratings.food.gov.uk/establishments/694609',\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",
      "{'AddressLine1': 'Plumstead Manor School Old Mill Road',\n",
      " 'AddressLine2': '',\n",
      " 'AddressLine3': 'Plumstead',\n",
      " 'AddressLine4': 'Greenwich',\n",
      " 'BusinessName': 'Plumstead Manor Nursery',\n",
      " 'BusinessType': 'Caring Premises',\n",
      " 'BusinessTypeID': 5,\n",
      " 'ChangesByServerID': 0,\n",
      " 'Distance': 4646.9740101340385,\n",
      " 'FHRSID': 695241,\n",
      " 'LocalAuthorityBusinessID': 'PI/000179088',\n",
      " 'LocalAuthorityCode': '511',\n",
      " 'LocalAuthorityEmailAddress': 'health@royalgreenwich.gov.uk',\n",
      " 'LocalAuthorityName': 'Greenwich',\n",
      " 'LocalAuthorityWebSite': 'http://www.royalgreenwich.gov.uk',\n",
      " 'NewRatingPending': False,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'SE18 1QG',\n",
      " 'RatingDate': '2021-06-16T00:00:00',\n",
      " 'RatingKey': 'fhrs_5_en-gb',\n",
      " 'RatingValue': '5',\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('6407e67050f18daefab7e3c1'),\n",
      " 'geocode': {'latitude': 51.481517791748, 'longitude': 0.0859939977526665},\n",
      " 'links': [{'href': 'http://api.ratings.food.gov.uk/establishments/695241',\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': 0, 'Structural': 0}}\n",
      "{'AddressLine1': '35 Lakedale Road',\n",
      " 'AddressLine2': '',\n",
      " 'AddressLine3': 'Plumstead',\n",
      " 'AddressLine4': 'Greenwich',\n",
      " 'BusinessName': 'Atlantic Fish Bar',\n",
      " 'BusinessType': 'Takeaway/sandwich shop',\n",
      " 'BusinessTypeID': 7844,\n",
      " 'ChangesByServerID': 0,\n",
      " 'Distance': 4646.974611855838,\n",
      " 'FHRSID': 694478,\n",
      " 'LocalAuthorityBusinessID': 'PI/000086506',\n",
      " 'LocalAuthorityCode': '511',\n",
      " 'LocalAuthorityEmailAddress': 'health@royalgreenwich.gov.uk',\n",
      " 'LocalAuthorityName': 'Greenwich',\n",
      " 'LocalAuthorityWebSite': 'http://www.royalgreenwich.gov.uk',\n",
      " 'NewRatingPending': False,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'SE18 1PR',\n",
      " 'RatingDate': '2021-06-16T00:00:00',\n",
      " 'RatingKey': 'fhrs_5_en-gb',\n",
      " 'RatingValue': '5',\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('6407e67050f18daefab7e3c4'),\n",
      " 'geocode': {'latitude': 51.4867296, 'longitude': 0.0912164},\n",
      " 'links': [{'href': 'http://api.ratings.food.gov.uk/establishments/694478',\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': 0, 'Structural': 0}}\n",
      "{'AddressLine1': '144 - 146 Plumstead High Street',\n",
      " 'AddressLine2': '',\n",
      " 'AddressLine3': 'Plumstead',\n",
      " 'AddressLine4': 'Greenwich',\n",
      " 'BusinessName': 'Iceland',\n",
      " 'BusinessType': 'Retailers - supermarkets/hypermarkets',\n",
      " 'BusinessTypeID': 7840,\n",
      " 'ChangesByServerID': 0,\n",
      " 'Distance': 4646.946071297699,\n",
      " 'FHRSID': 695223,\n",
      " 'LocalAuthorityBusinessID': 'PI/000178842',\n",
      " 'LocalAuthorityCode': '511',\n",
      " 'LocalAuthorityEmailAddress': 'health@royalgreenwich.gov.uk',\n",
      " 'LocalAuthorityName': 'Greenwich',\n",
      " 'LocalAuthorityWebSite': 'http://www.royalgreenwich.gov.uk',\n",
      " 'NewRatingPending': False,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'SE18 1JQ',\n",
      " 'RatingDate': '2019-11-13T00:00:00',\n",
      " 'RatingKey': 'fhrs_5_en-gb',\n",
      " 'RatingValue': '5',\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('6407e67050f18daefab7e37b'),\n",
      " 'geocode': {'latitude': 51.4871482849121, 'longitude': 0.0924199968576431},\n",
      " 'links': [{'href': 'http://api.ratings.food.gov.uk/establishments/695223',\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': 0, 'Structural': 5}}\n",
      "{'AddressLine1': 'Restaurant And Premises 107A Plumstead High Street',\n",
      " 'AddressLine2': '',\n",
      " 'AddressLine3': 'Plumstead',\n",
      " 'AddressLine4': 'Greenwich',\n",
      " 'BusinessName': 'Howe and Co Fish and Chips - Van 17',\n",
      " 'BusinessType': 'Mobile caterer',\n",
      " 'BusinessTypeID': 7846,\n",
      " 'ChangesByServerID': 0,\n",
      " 'Distance': 4646.95593107927,\n",
      " 'FHRSID': 1380578,\n",
      " 'LocalAuthorityBusinessID': '14425',\n",
      " 'LocalAuthorityCode': '511',\n",
      " 'LocalAuthorityEmailAddress': 'health@royalgreenwich.gov.uk',\n",
      " 'LocalAuthorityName': 'Greenwich',\n",
      " 'LocalAuthorityWebSite': 'http://www.royalgreenwich.gov.uk',\n",
      " 'NewRatingPending': False,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'SE18 1SE',\n",
      " 'RatingDate': '2021-11-11T00:00:00',\n",
      " 'RatingKey': 'fhrs_5_en-gb',\n",
      " 'RatingValue': '5',\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('6407e67050f18daefab7e38b'),\n",
      " 'geocode': {'latitude': 51.4875335693359, 'longitude': 0.0925370007753372},\n",
      " 'links': [{'href': 'http://api.ratings.food.gov.uk/establishments/1380578',\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": [
    "# Search within 0.01 degree on either side of the latitude and longitude.\n",
    "# Rating value must equal 5\n",
    "# Sort by hygiene score\n",
    "degree_search = 0.01\n",
    "latitude = 51.490142\n",
    "longitude = 0.08384\n",
    "\n",
    "query3 = {'RatingValue': '5', \n",
    "         'geocode.latitude': {'$lte': (latitude + degree_search), '$gte': (latitude - degree_search)}, \n",
    "         'geocode.longitude': {'$lte': (longitude + degree_search), '$gte': (longitude - degree_search)} \n",
    "         }\n",
    "sort =  [('scores.Hygiene', 1)]\n",
    "\n",
    "# Print the results\n",
    "for result in establishments.find(query3).sort(sort).limit(5):\n",
    "    pprint(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a6ad16f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>FHRSID</th>\n",
       "      <th>ChangesByServerID</th>\n",
       "      <th>LocalAuthorityBusinessID</th>\n",
       "      <th>BusinessName</th>\n",
       "      <th>BusinessType</th>\n",
       "      <th>BusinessTypeID</th>\n",
       "      <th>AddressLine1</th>\n",
       "      <th>AddressLine2</th>\n",
       "      <th>AddressLine3</th>\n",
       "      <th>...</th>\n",
       "      <th>LocalAuthorityWebSite</th>\n",
       "      <th>LocalAuthorityEmailAddress</th>\n",
       "      <th>scores</th>\n",
       "      <th>SchemeType</th>\n",
       "      <th>geocode</th>\n",
       "      <th>RightToReply</th>\n",
       "      <th>Distance</th>\n",
       "      <th>NewRatingPending</th>\n",
       "      <th>meta</th>\n",
       "      <th>links</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6407e67050f18daefab7e3a8</td>\n",
       "      <td>694609</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000116619</td>\n",
       "      <td>Volunteer</td>\n",
       "      <td>Pub/bar/nightclub</td>\n",
       "      <td>7843</td>\n",
       "      <td>130 - 132 Plumstead High Street</td>\n",
       "      <td></td>\n",
       "      <td>Plumstead</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.royalgreenwich.gov.uk</td>\n",
       "      <td>health@royalgreenwich.gov.uk</td>\n",
       "      <td>{'Hygiene': 0, 'Structural': 0, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.09208, 'latitude': 51.4873437}</td>\n",
       "      <td></td>\n",
       "      <td>4646.965635</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'http://api.ratings.f...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6407e67050f18daefab7e3c1</td>\n",
       "      <td>695241</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000179088</td>\n",
       "      <td>Plumstead Manor Nursery</td>\n",
       "      <td>Caring Premises</td>\n",
       "      <td>5</td>\n",
       "      <td>Plumstead Manor School Old Mill Road</td>\n",
       "      <td></td>\n",
       "      <td>Plumstead</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.royalgreenwich.gov.uk</td>\n",
       "      <td>health@royalgreenwich.gov.uk</td>\n",
       "      <td>{'Hygiene': 0, 'Structural': 0, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.0859939977526665, 'latitude': ...</td>\n",
       "      <td></td>\n",
       "      <td>4646.974010</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'http://api.ratings.f...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6407e67050f18daefab7e3c4</td>\n",
       "      <td>694478</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000086506</td>\n",
       "      <td>Atlantic Fish Bar</td>\n",
       "      <td>Takeaway/sandwich shop</td>\n",
       "      <td>7844</td>\n",
       "      <td>35 Lakedale Road</td>\n",
       "      <td></td>\n",
       "      <td>Plumstead</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.royalgreenwich.gov.uk</td>\n",
       "      <td>health@royalgreenwich.gov.uk</td>\n",
       "      <td>{'Hygiene': 0, 'Structural': 0, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.0912164, 'latitude': 51.4867296}</td>\n",
       "      <td></td>\n",
       "      <td>4646.974612</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'http://api.ratings.f...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6407e67050f18daefab7e37b</td>\n",
       "      <td>695223</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000178842</td>\n",
       "      <td>Iceland</td>\n",
       "      <td>Retailers - supermarkets/hypermarkets</td>\n",
       "      <td>7840</td>\n",
       "      <td>144 - 146 Plumstead High Street</td>\n",
       "      <td></td>\n",
       "      <td>Plumstead</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.royalgreenwich.gov.uk</td>\n",
       "      <td>health@royalgreenwich.gov.uk</td>\n",
       "      <td>{'Hygiene': 0, 'Structural': 5, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.0924199968576431, 'latitude': ...</td>\n",
       "      <td></td>\n",
       "      <td>4646.946071</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'http://api.ratings.f...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6407e67050f18daefab7e38b</td>\n",
       "      <td>1380578</td>\n",
       "      <td>0</td>\n",
       "      <td>14425</td>\n",
       "      <td>Howe and Co Fish and Chips - Van 17</td>\n",
       "      <td>Mobile caterer</td>\n",
       "      <td>7846</td>\n",
       "      <td>Restaurant And Premises 107A Plumstead High St...</td>\n",
       "      <td></td>\n",
       "      <td>Plumstead</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.royalgreenwich.gov.uk</td>\n",
       "      <td>health@royalgreenwich.gov.uk</td>\n",
       "      <td>{'Hygiene': 0, 'Structural': 0, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.0925370007753372, 'latitude': ...</td>\n",
       "      <td></td>\n",
       "      <td>4646.955931</td>\n",
       "      <td>False</td>\n",
       "      <td>{'dataSource': None, 'extractDate': '0001-01-0...</td>\n",
       "      <td>[{'rel': 'self', 'href': 'http://api.ratings.f...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id   FHRSID  ChangesByServerID  \\\n",
       "0  6407e67050f18daefab7e3a8   694609                  0   \n",
       "1  6407e67050f18daefab7e3c1   695241                  0   \n",
       "2  6407e67050f18daefab7e3c4   694478                  0   \n",
       "3  6407e67050f18daefab7e37b   695223                  0   \n",
       "4  6407e67050f18daefab7e38b  1380578                  0   \n",
       "\n",
       "  LocalAuthorityBusinessID                         BusinessName  \\\n",
       "0             PI/000116619                            Volunteer   \n",
       "1             PI/000179088              Plumstead Manor Nursery   \n",
       "2             PI/000086506                    Atlantic Fish Bar   \n",
       "3             PI/000178842                              Iceland   \n",
       "4                    14425  Howe and Co Fish and Chips - Van 17   \n",
       "\n",
       "                            BusinessType  BusinessTypeID  \\\n",
       "0                      Pub/bar/nightclub            7843   \n",
       "1                        Caring Premises               5   \n",
       "2                 Takeaway/sandwich shop            7844   \n",
       "3  Retailers - supermarkets/hypermarkets            7840   \n",
       "4                         Mobile caterer            7846   \n",
       "\n",
       "                                        AddressLine1 AddressLine2  \\\n",
       "0                    130 - 132 Plumstead High Street                \n",
       "1               Plumstead Manor School Old Mill Road                \n",
       "2                                   35 Lakedale Road                \n",
       "3                    144 - 146 Plumstead High Street                \n",
       "4  Restaurant And Premises 107A Plumstead High St...                \n",
       "\n",
       "  AddressLine3  ...             LocalAuthorityWebSite  \\\n",
       "0    Plumstead  ...  http://www.royalgreenwich.gov.uk   \n",
       "1    Plumstead  ...  http://www.royalgreenwich.gov.uk   \n",
       "2    Plumstead  ...  http://www.royalgreenwich.gov.uk   \n",
       "3    Plumstead  ...  http://www.royalgreenwich.gov.uk   \n",
       "4    Plumstead  ...  http://www.royalgreenwich.gov.uk   \n",
       "\n",
       "     LocalAuthorityEmailAddress  \\\n",
       "0  health@royalgreenwich.gov.uk   \n",
       "1  health@royalgreenwich.gov.uk   \n",
       "2  health@royalgreenwich.gov.uk   \n",
       "3  health@royalgreenwich.gov.uk   \n",
       "4  health@royalgreenwich.gov.uk   \n",
       "\n",
       "                                              scores SchemeType  \\\n",
       "0  {'Hygiene': 0, 'Structural': 0, 'ConfidenceInM...       FHRS   \n",
       "1  {'Hygiene': 0, 'Structural': 0, 'ConfidenceInM...       FHRS   \n",
       "2  {'Hygiene': 0, 'Structural': 0, 'ConfidenceInM...       FHRS   \n",
       "3  {'Hygiene': 0, 'Structural': 5, 'ConfidenceInM...       FHRS   \n",
       "4  {'Hygiene': 0, 'Structural': 0, 'ConfidenceInM...       FHRS   \n",
       "\n",
       "                                             geocode RightToReply  \\\n",
       "0     {'longitude': 0.09208, 'latitude': 51.4873437}                \n",
       "1  {'longitude': 0.0859939977526665, 'latitude': ...                \n",
       "2   {'longitude': 0.0912164, 'latitude': 51.4867296}                \n",
       "3  {'longitude': 0.0924199968576431, 'latitude': ...                \n",
       "4  {'longitude': 0.0925370007753372, 'latitude': ...                \n",
       "\n",
       "      Distance NewRatingPending  \\\n",
       "0  4646.965635            False   \n",
       "1  4646.974010            False   \n",
       "2  4646.974612            False   \n",
       "3  4646.946071            False   \n",
       "4  4646.955931            False   \n",
       "\n",
       "                                                meta  \\\n",
       "0  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "1  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "2  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "3  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "4  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "\n",
       "                                               links  \n",
       "0  [{'rel': 'self', 'href': 'http://api.ratings.f...  \n",
       "1  [{'rel': 'self', 'href': 'http://api.ratings.f...  \n",
       "2  [{'rel': 'self', 'href': 'http://api.ratings.f...  \n",
       "3  [{'rel': 'self', 'href': 'http://api.ratings.f...  \n",
       "4  [{'rel': 'self', 'href': 'http://api.ratings.f...  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert result to Pandas DataFrame\n",
    "pd.DataFrame(establishments.find(query3).sort(sort).limit(5))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67281254",
   "metadata": {},
   "source": [
    "### 4. How many establishments in each Local Authority area have a hygiene score of 0?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "4eeb227e",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 55 documents in the result.\n",
      "[{'_id': 'Thanet', 'count': 1130},\n",
      " {'_id': 'Greenwich', 'count': 882},\n",
      " {'_id': 'Maidstone', 'count': 713},\n",
      " {'_id': 'Newham', 'count': 711},\n",
      " {'_id': 'Swale', 'count': 686},\n",
      " {'_id': 'Chelmsford', 'count': 680},\n",
      " {'_id': 'Medway', 'count': 672},\n",
      " {'_id': 'Bexley', 'count': 607},\n",
      " {'_id': 'Southend-On-Sea', 'count': 586},\n",
      " {'_id': 'Tendring', 'count': 542}]\n"
     ]
    }
   ],
   "source": [
    "# Create a pipeline that: \n",
    "# 1. Matches establishments with a hygiene score of 0\n",
    "# 2. Groups the matches by Local Authority\n",
    "# 3. Sorts the matches from highest to lowest\n",
    "match_query = {'$match': {'scores.Hygiene': 0}}\n",
    "group_query = {'$group': {'_id': '$LocalAuthorityName', \n",
    "                          'count': {'$sum': 1}\n",
    "                         }\n",
    "              }\n",
    "sort_query = {'$sort': {'count': -1}}\n",
    "\n",
    "pipeline = [match_query, group_query, sort_query]\n",
    "\n",
    "results = list(establishments.aggregate(pipeline))\n",
    "\n",
    "# Print the number of documents in the result\n",
    "print(f\"There are {len(results)} documents in the result.\")\n",
    "\n",
    "# Print the first 10 results\n",
    "pprint(results[0:10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d8aab364",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 55 rows in the DataFrame.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Thanet</td>\n",
       "      <td>1130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Greenwich</td>\n",
       "      <td>882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Maidstone</td>\n",
       "      <td>713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Newham</td>\n",
       "      <td>711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Swale</td>\n",
       "      <td>686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Chelmsford</td>\n",
       "      <td>680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Medway</td>\n",
       "      <td>672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Bexley</td>\n",
       "      <td>607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Southend-On-Sea</td>\n",
       "      <td>586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Tendring</td>\n",
       "      <td>542</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               _id  count\n",
       "0           Thanet   1130\n",
       "1        Greenwich    882\n",
       "2        Maidstone    713\n",
       "3           Newham    711\n",
       "4            Swale    686\n",
       "5       Chelmsford    680\n",
       "6           Medway    672\n",
       "7           Bexley    607\n",
       "8  Southend-On-Sea    586\n",
       "9         Tendring    542"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the result to a Pandas DataFrame\n",
    "agg_df = pd.json_normalize(results)\n",
    "\n",
    "# Display the number of rows in the DataFrame\n",
    "print(f\"There are {len(agg_df)} rows in the DataFrame.\")\n",
    "\n",
    "# Display the first 10 rows of the DataFrame\n",
    "agg_df.head(10)"
   ]
  }
 ],
 "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}