NoSQL_analysis_part3.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",
    "db = mongo['uk_food']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "18ab463c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['establishments']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# review the collections in our database\n",
    "db.list_collection_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "11c9f11b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# assign the collection to a variable\n",
    "establishments = 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": [
      "The number of establishments with a hygiene score of 20 is: 41\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",
    "print(f\"The number of establishments with a hygiene score of 20 is: {establishments.count_documents(query)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d84b1780",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The first document in the result is: \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('65b59b1bbeca9933a5cdbe66'),\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": [
    "# Display the first document in the results using pprint\n",
    "result = establishments.find_one(query)\n",
    "\n",
    "print(\"The first document in the result is: \")\n",
    "pprint(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a6e5cece",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The number of rows in the DataFrame is: 41\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>65b59b1bbeca9933a5cdbe66</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>65b59b1bbeca9933a5cdc1e7</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>65b59b1bbeca9933a5cdc4f1</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>65b59b1bbeca9933a5cdc6df</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>65b59b1bbeca9933a5cdc6ee</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>65b59b1bbeca9933a5cdd08d</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>65b59b1bbeca9933a5cdd2ae</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>65b59b1cbeca9933a5cde7d9</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>65b59b1cbeca9933a5cdec1d</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>65b59b1dbeca9933a5cdf42f</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  65b59b1bbeca9933a5cdbe66   110681                  0   \n",
       "1  65b59b1bbeca9933a5cdc1e7   612039                  0   \n",
       "2  65b59b1bbeca9933a5cdc4f1   730933                  0   \n",
       "3  65b59b1bbeca9933a5cdc6df   172735                  0   \n",
       "4  65b59b1bbeca9933a5cdc6ee   172953                  0   \n",
       "5  65b59b1bbeca9933a5cdd08d   512854                  0   \n",
       "6  65b59b1bbeca9933a5cdd2ae  1537089                  0   \n",
       "7  65b59b1cbeca9933a5cde7d9   155648                  0   \n",
       "8  65b59b1cbeca9933a5cdec1d  1012883                  0   \n",
       "9  65b59b1dbeca9933a5cdf42f   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": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the result to a Pandas DataFrame\n",
    "hygiene20 = establishments.find(query)\n",
    "\n",
    "hygiene20_df = pd.DataFrame(hygiene20)\n",
    "\n",
    "# Display the number of rows in the DataFrame\n",
    "print(f\"The number of rows in the DataFrame is: {hygiene20_df.shape[0]}\")\n",
    "\n",
    "# Display the first 10 rows of the DataFrame\n",
    "hygiene20_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": 9,
   "id": "ca49607b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find the establishments with London as the Local Authority and has a RatingValue greater than or equal to 4.\n",
    "query2 = {\n",
    "    'LocalAuthorityName': {'$regex': 'London'},\n",
    "    'RatingValue': {'$gte': 4}\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "93c63829",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The number of establishments with London as the Local Authority and a RatingValue >= to 4 is: 33\n"
     ]
    }
   ],
   "source": [
    "# Use count_documents to display the number of documents in the result\n",
    "print(f\"The number of establishments with London as the Local Authority and a RatingValue >= to 4 is: {establishments.count_documents(query2)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "12a61b0c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The first document in the result is: \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('65b59b1cbeca9933a5cdd881'),\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": [
    "# Display the first document in the results using pprint\n",
    "result2 = establishments.find_one(query2)\n",
    "\n",
    "print(\"The first document in the result is: \")\n",
    "pprint(result2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "07e24ccb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The number of rows in the DataFrame is: 33\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>65b59b1cbeca9933a5cdd881</td>\n",
       "      <td>621707</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000025307</td>\n",
       "      <td>Charlie's</td>\n",
       "      <td>Other catering premises</td>\n",
       "      <td>7841</td>\n",
       "      <td>Oak Apple Farm Building 103 Sheernes Docks</td>\n",
       "      <td>Sheppy Kent</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.cityoflondon.gov.uk/Corporation/hom...</td>\n",
       "      <td>publicprotection@cityoflondon.gov.uk</td>\n",
       "      <td>{'Hygiene': 5, 'Structural': 10, 'ConfidenceIn...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.508551, 'latitude': 51.369321}</td>\n",
       "      <td></td>\n",
       "      <td>4627.439468</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>65b59b1cbeca9933a5cddba7</td>\n",
       "      <td>1130836</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000034075</td>\n",
       "      <td>Mv City Cruises Erasmus</td>\n",
       "      <td>Other catering premises</td>\n",
       "      <td>7841</td>\n",
       "      <td>Cherry Garden Pier</td>\n",
       "      <td>Cherry Garden Street Rotherhithe</td>\n",
       "      <td>Charlton</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.cityoflondon.gov.uk/Corporation/hom...</td>\n",
       "      <td>publicprotection@cityoflondon.gov.uk</td>\n",
       "      <td>{'Hygiene': 0, 'Structural': 5, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.249255999922752, 'latitude': 5...</td>\n",
       "      <td></td>\n",
       "      <td>4627.873179</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>65b59b1cbeca9933a5cde6f4</td>\n",
       "      <td>293783</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000002614</td>\n",
       "      <td>Benfleet Motor Yacht Club</td>\n",
       "      <td>Other catering premises</td>\n",
       "      <td>7841</td>\n",
       "      <td>Ferry Road</td>\n",
       "      <td>South Benfleet Essex</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.cityoflondon.gov.uk/Corporation/hom...</td>\n",
       "      <td>publicprotection@cityoflondon.gov.uk</td>\n",
       "      <td>{'Hygiene': 0, 'Structural': 0, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.561954, 'latitude': 51.543831}</td>\n",
       "      <td></td>\n",
       "      <td>4631.968418</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>65b59b1dbeca9933a5cdf4f4</td>\n",
       "      <td>1315095</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000036464</td>\n",
       "      <td>Coombs Catering t/a The Lock and Key</td>\n",
       "      <td>Restaurant/Cafe/Canteen</td>\n",
       "      <td>1</td>\n",
       "      <td>Leslie Ford House</td>\n",
       "      <td>Tilbury</td>\n",
       "      <td>Charlton</td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.cityoflondon.gov.uk/Corporation/hom...</td>\n",
       "      <td>publicprotection@cityoflondon.gov.uk</td>\n",
       "      <td>{'Hygiene': 0, 'Structural': 5, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.346336990594864, 'latitude': 5...</td>\n",
       "      <td></td>\n",
       "      <td>4636.846754</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>65b59b1dbeca9933a5cdf4f5</td>\n",
       "      <td>294474</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000014647</td>\n",
       "      <td>Tilbury Seafarers Centre</td>\n",
       "      <td>Restaurant/Cafe/Canteen</td>\n",
       "      <td>1</td>\n",
       "      <td>Tenants Row Tilbury Docks</td>\n",
       "      <td>Tilbury Essex</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.cityoflondon.gov.uk/Corporation/hom...</td>\n",
       "      <td>publicprotection@cityoflondon.gov.uk</td>\n",
       "      <td>{'Hygiene': 0, 'Structural': 0, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.346325, 'latitude': 51.464078}</td>\n",
       "      <td></td>\n",
       "      <td>4636.847650</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>5</th>\n",
       "      <td>65b59b1dbeca9933a5cdfbfd</td>\n",
       "      <td>294900</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000019066</td>\n",
       "      <td>Mv Valulla</td>\n",
       "      <td>Other catering premises</td>\n",
       "      <td>7841</td>\n",
       "      <td>Reeds River Cruises LtdKings ReachRiver Thames...</td>\n",
       "      <td>London</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.cityoflondon.gov.uk/Corporation/hom...</td>\n",
       "      <td>publicprotection@cityoflondon.gov.uk</td>\n",
       "      <td>{'Hygiene': 0, 'Structural': 0, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.287555, 'latitude': 51.504071}</td>\n",
       "      <td></td>\n",
       "      <td>4640.460834</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>6</th>\n",
       "      <td>65b59b1ebeca9933a5ce1d0b</td>\n",
       "      <td>293756</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000002538</td>\n",
       "      <td>Tereza Joanne</td>\n",
       "      <td>Other catering premises</td>\n",
       "      <td>7841</td>\n",
       "      <td>Funcraft UK Ltd King George V Dock Woolwich Ma...</td>\n",
       "      <td>London</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.cityoflondon.gov.uk/Corporation/hom...</td>\n",
       "      <td>publicprotection@cityoflondon.gov.uk</td>\n",
       "      <td>{'Hygiene': 0, 'Structural': 5, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.069286, 'latitude': 51.501121}</td>\n",
       "      <td></td>\n",
       "      <td>4648.301822</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>7</th>\n",
       "      <td>65b59b1ebeca9933a5ce20d2</td>\n",
       "      <td>878523</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000029844</td>\n",
       "      <td>Brick Lane Brews</td>\n",
       "      <td>Restaurant/Cafe/Canteen</td>\n",
       "      <td>1</td>\n",
       "      <td>Air side London City Airport</td>\n",
       "      <td>London</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.cityoflondon.gov.uk/Corporation/hom...</td>\n",
       "      <td>publicprotection@cityoflondon.gov.uk</td>\n",
       "      <td>{'Hygiene': 10, 'Structural': 5, 'ConfidenceIn...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.048006, 'latitude': 51.503733}</td>\n",
       "      <td></td>\n",
       "      <td>4649.173485</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>65b59b1ebeca9933a5ce20ec</td>\n",
       "      <td>293772</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000002585</td>\n",
       "      <td>The Nuance Group (UK) Limited</td>\n",
       "      <td>Retailers - other</td>\n",
       "      <td>4613</td>\n",
       "      <td>Duty Free Shop Passenger Terminal</td>\n",
       "      <td>London City AirportRoyal DocksLondon</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.cityoflondon.gov.uk/Corporation/hom...</td>\n",
       "      <td>publicprotection@cityoflondon.gov.uk</td>\n",
       "      <td>{'Hygiene': 0, 'Structural': 0, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.047966, 'latitude': 51.504112}</td>\n",
       "      <td></td>\n",
       "      <td>4649.188826</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>65b59b1ebeca9933a5ce20ed</td>\n",
       "      <td>294606</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000016185</td>\n",
       "      <td>WH Smith</td>\n",
       "      <td>Retailers - other</td>\n",
       "      <td>4613</td>\n",
       "      <td>London City Airport</td>\n",
       "      <td>Silvertown London</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>http://www.cityoflondon.gov.uk/Corporation/hom...</td>\n",
       "      <td>publicprotection@cityoflondon.gov.uk</td>\n",
       "      <td>{'Hygiene': 0, 'Structural': 0, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.0479655750095844, 'latitude': ...</td>\n",
       "      <td></td>\n",
       "      <td>4649.188850</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  65b59b1cbeca9933a5cdd881   621707                  0   \n",
       "1  65b59b1cbeca9933a5cddba7  1130836                  0   \n",
       "2  65b59b1cbeca9933a5cde6f4   293783                  0   \n",
       "3  65b59b1dbeca9933a5cdf4f4  1315095                  0   \n",
       "4  65b59b1dbeca9933a5cdf4f5   294474                  0   \n",
       "5  65b59b1dbeca9933a5cdfbfd   294900                  0   \n",
       "6  65b59b1ebeca9933a5ce1d0b   293756                  0   \n",
       "7  65b59b1ebeca9933a5ce20d2   878523                  0   \n",
       "8  65b59b1ebeca9933a5ce20ec   293772                  0   \n",
       "9  65b59b1ebeca9933a5ce20ed   294606                  0   \n",
       "\n",
       "  LocalAuthorityBusinessID                          BusinessName  \\\n",
       "0             PI/000025307                             Charlie's   \n",
       "1             PI/000034075               Mv City Cruises Erasmus   \n",
       "2             PI/000002614             Benfleet Motor Yacht Club   \n",
       "3             PI/000036464  Coombs Catering t/a The Lock and Key   \n",
       "4             PI/000014647              Tilbury Seafarers Centre   \n",
       "5             PI/000019066                            Mv Valulla   \n",
       "6             PI/000002538                         Tereza Joanne   \n",
       "7             PI/000029844                      Brick Lane Brews   \n",
       "8             PI/000002585         The Nuance Group (UK) Limited   \n",
       "9             PI/000016185                              WH Smith   \n",
       "\n",
       "              BusinessType  BusinessTypeID  \\\n",
       "0  Other catering premises            7841   \n",
       "1  Other catering premises            7841   \n",
       "2  Other catering premises            7841   \n",
       "3  Restaurant/Cafe/Canteen               1   \n",
       "4  Restaurant/Cafe/Canteen               1   \n",
       "5  Other catering premises            7841   \n",
       "6  Other catering premises            7841   \n",
       "7  Restaurant/Cafe/Canteen               1   \n",
       "8        Retailers - other            4613   \n",
       "9        Retailers - other            4613   \n",
       "\n",
       "                                        AddressLine1  \\\n",
       "0         Oak Apple Farm Building 103 Sheernes Docks   \n",
       "1                                 Cherry Garden Pier   \n",
       "2                                         Ferry Road   \n",
       "3                                  Leslie Ford House   \n",
       "4                          Tenants Row Tilbury Docks   \n",
       "5  Reeds River Cruises LtdKings ReachRiver Thames...   \n",
       "6  Funcraft UK Ltd King George V Dock Woolwich Ma...   \n",
       "7                       Air side London City Airport   \n",
       "8                  Duty Free Shop Passenger Terminal   \n",
       "9                                London City Airport   \n",
       "\n",
       "                           AddressLine2 AddressLine3  ...  \\\n",
       "0                           Sheppy Kent               ...   \n",
       "1      Cherry Garden Street Rotherhithe     Charlton  ...   \n",
       "2                  South Benfleet Essex               ...   \n",
       "3                               Tilbury     Charlton  ...   \n",
       "4                         Tilbury Essex               ...   \n",
       "5                                London               ...   \n",
       "6                                London               ...   \n",
       "7                                London               ...   \n",
       "8  London City AirportRoyal DocksLondon               ...   \n",
       "9                     Silvertown London               ...   \n",
       "\n",
       "                               LocalAuthorityWebSite  \\\n",
       "0  http://www.cityoflondon.gov.uk/Corporation/hom...   \n",
       "1  http://www.cityoflondon.gov.uk/Corporation/hom...   \n",
       "2  http://www.cityoflondon.gov.uk/Corporation/hom...   \n",
       "3  http://www.cityoflondon.gov.uk/Corporation/hom...   \n",
       "4  http://www.cityoflondon.gov.uk/Corporation/hom...   \n",
       "5  http://www.cityoflondon.gov.uk/Corporation/hom...   \n",
       "6  http://www.cityoflondon.gov.uk/Corporation/hom...   \n",
       "7  http://www.cityoflondon.gov.uk/Corporation/hom...   \n",
       "8  http://www.cityoflondon.gov.uk/Corporation/hom...   \n",
       "9  http://www.cityoflondon.gov.uk/Corporation/hom...   \n",
       "\n",
       "             LocalAuthorityEmailAddress  \\\n",
       "0  publicprotection@cityoflondon.gov.uk   \n",
       "1  publicprotection@cityoflondon.gov.uk   \n",
       "2  publicprotection@cityoflondon.gov.uk   \n",
       "3  publicprotection@cityoflondon.gov.uk   \n",
       "4  publicprotection@cityoflondon.gov.uk   \n",
       "5  publicprotection@cityoflondon.gov.uk   \n",
       "6  publicprotection@cityoflondon.gov.uk   \n",
       "7  publicprotection@cityoflondon.gov.uk   \n",
       "8  publicprotection@cityoflondon.gov.uk   \n",
       "9  publicprotection@cityoflondon.gov.uk   \n",
       "\n",
       "                                              scores  SchemeType  \\\n",
       "0  {'Hygiene': 5, 'Structural': 10, 'ConfidenceIn...        FHRS   \n",
       "1  {'Hygiene': 0, 'Structural': 5, '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",
       "5  {'Hygiene': 0, 'Structural': 0, 'ConfidenceInM...        FHRS   \n",
       "6  {'Hygiene': 0, 'Structural': 5, 'ConfidenceInM...        FHRS   \n",
       "7  {'Hygiene': 10, 'Structural': 5, 'ConfidenceIn...        FHRS   \n",
       "8  {'Hygiene': 0, 'Structural': 0, 'ConfidenceInM...        FHRS   \n",
       "9  {'Hygiene': 0, 'Structural': 0, 'ConfidenceInM...        FHRS   \n",
       "\n",
       "                                             geocode RightToReply  \\\n",
       "0     {'longitude': 0.508551, 'latitude': 51.369321}                \n",
       "1  {'longitude': 0.249255999922752, 'latitude': 5...                \n",
       "2     {'longitude': 0.561954, 'latitude': 51.543831}                \n",
       "3  {'longitude': 0.346336990594864, 'latitude': 5...                \n",
       "4     {'longitude': 0.346325, 'latitude': 51.464078}                \n",
       "5     {'longitude': 0.287555, 'latitude': 51.504071}                \n",
       "6     {'longitude': 0.069286, 'latitude': 51.501121}                \n",
       "7     {'longitude': 0.048006, 'latitude': 51.503733}                \n",
       "8     {'longitude': 0.047966, 'latitude': 51.504112}                \n",
       "9  {'longitude': 0.0479655750095844, 'latitude': ...                \n",
       "\n",
       "      Distance NewRatingPending  \\\n",
       "0  4627.439468            False   \n",
       "1  4627.873179            False   \n",
       "2  4631.968418            False   \n",
       "3  4636.846754            False   \n",
       "4  4636.847650            False   \n",
       "5  4640.460834            False   \n",
       "6  4648.301822            False   \n",
       "7  4649.173485            False   \n",
       "8  4649.188826            False   \n",
       "9  4649.188850            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': '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",
       "5  [{'rel': 'self', 'href': 'http://api.ratings.f...  \n",
       "6  [{'rel': 'self', 'href': 'http://api.ratings.f...  \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": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the result to a Pandas DataFrame\n",
    "london = establishments.find(query2)\n",
    "\n",
    "london_df = pd.DataFrame(london)\n",
    "\n",
    "# Display the number of rows in the DataFrame\n",
    "print(f\"The number of rows in the DataFrame is: {london_df.shape[0]}\")\n",
    "# Display the first 10 rows of the DataFrame\n",
    "london_df.head(10)"
   ]
  },
  {
   "attachments": {},
   "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": 13,
   "id": "2cecdb17",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'AddressLine1': '53 Marine Parade East',\n",
      " 'AddressLine2': 'Clacton On Sea',\n",
      " 'AddressLine3': 'Essex',\n",
      " 'AddressLine4': '',\n",
      " 'BusinessName': 'Melrose Hotel',\n",
      " 'BusinessType': 'Hotel/bed & breakfast/guest house',\n",
      " 'BusinessTypeID': 7842,\n",
      " 'ChangesByServerID': 0,\n",
      " 'Distance': 4619.6561440278165,\n",
      " 'FHRSID': 730933,\n",
      " 'LocalAuthorityBusinessID': '1698/FOOD',\n",
      " 'LocalAuthorityCode': '120',\n",
      " 'LocalAuthorityEmailAddress': 'fhsadmin@tendringdc.gov.uk',\n",
      " 'LocalAuthorityName': 'Tendring',\n",
      " 'LocalAuthorityWebSite': 'http://www.tendringdc.gov.uk/',\n",
      " 'NewRatingPending': False,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'CO15 6AD',\n",
      " 'RatingDate': '2021-08-13T00:00:00',\n",
      " 'RatingKey': 'fhrs_0_en-gb',\n",
      " 'RatingValue': 0,\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('65b59b1bbeca9933a5cdc4f1'),\n",
      " 'geocode': {'latitude': 51.789429, 'longitude': 1.15927},\n",
      " 'links': [{'href': 'https://api.ratings.food.gov.uk/establishments/730933',\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",
      "{'AddressLine1': '5 South Street',\n",
      " 'AddressLine2': 'Seaford',\n",
      " 'AddressLine3': 'East Sussex',\n",
      " 'AddressLine4': '',\n",
      " 'BusinessName': 'Golden Palace',\n",
      " 'BusinessType': 'Restaurant/Cafe/Canteen',\n",
      " 'BusinessTypeID': 1,\n",
      " 'ChangesByServerID': 0,\n",
      " 'Distance': 4620.437179097401,\n",
      " 'FHRSID': 172953,\n",
      " 'LocalAuthorityBusinessID': 'PI/000024532',\n",
      " 'LocalAuthorityCode': '105',\n",
      " 'LocalAuthorityEmailAddress': 'ehealth.ldc@lewes-eastbourne.gov.uk',\n",
      " 'LocalAuthorityName': 'Lewes',\n",
      " 'LocalAuthorityWebSite': 'http://www.lewes-eastbourne.gov.uk/',\n",
      " 'NewRatingPending': False,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'BN25 1HP',\n",
      " 'RatingDate': '2021-10-07T00:00:00',\n",
      " 'RatingKey': 'fhrs_1_en-gb',\n",
      " 'RatingValue': 1,\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('65b59b1bbeca9933a5cdc6ee'),\n",
      " 'geocode': {'latitude': 50.770724, 'longitude': 0.101446},\n",
      " 'links': [{'href': 'https://api.ratings.food.gov.uk/establishments/172953',\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': 10}}\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('65b59b1bbeca9933a5cdbe66'),\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",
      "{'AddressLine1': 'Hall Lane',\n",
      " 'AddressLine2': 'Walton-on-the-Naze',\n",
      " 'AddressLine3': 'Essex',\n",
      " 'AddressLine4': '',\n",
      " 'BusinessName': 'Brenalwood',\n",
      " 'BusinessType': 'Caring Premises',\n",
      " 'BusinessTypeID': 5,\n",
      " 'ChangesByServerID': 0,\n",
      " 'Distance': 4617.965823673803,\n",
      " 'FHRSID': 612039,\n",
      " 'LocalAuthorityBusinessID': '1970/FOOD',\n",
      " 'LocalAuthorityCode': '120',\n",
      " 'LocalAuthorityEmailAddress': 'fhsadmin@tendringdc.gov.uk',\n",
      " 'LocalAuthorityName': 'Tendring',\n",
      " 'LocalAuthorityWebSite': 'http://www.tendringdc.gov.uk/',\n",
      " 'NewRatingPending': False,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'CO14 8HN',\n",
      " 'RatingDate': '2022-06-22T00:00:00',\n",
      " 'RatingKey': 'fhrs_0_en-gb',\n",
      " 'RatingValue': 0,\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('65b59b1bbeca9933a5cdc1e7'),\n",
      " 'geocode': {'latitude': 51.857536, 'longitude': 1.278721},\n",
      " 'links': [{'href': 'https://api.ratings.food.gov.uk/establishments/612039',\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': 30, 'Hygiene': 20, 'Structural': 15}}\n",
      "{'AddressLine1': '4 High Street',\n",
      " 'AddressLine2': 'Seaford',\n",
      " 'AddressLine3': 'East Sussex',\n",
      " 'AddressLine4': '',\n",
      " 'BusinessName': 'Seaford Pizza',\n",
      " 'BusinessType': 'Takeaway/sandwich shop',\n",
      " 'BusinessTypeID': 7844,\n",
      " 'ChangesByServerID': 0,\n",
      " 'Distance': 4620.421724673841,\n",
      " 'FHRSID': 172735,\n",
      " 'LocalAuthorityBusinessID': 'PI/000023858',\n",
      " 'LocalAuthorityCode': '105',\n",
      " 'LocalAuthorityEmailAddress': 'ehealth.ldc@lewes-eastbourne.gov.uk',\n",
      " 'LocalAuthorityName': 'Lewes',\n",
      " 'LocalAuthorityWebSite': 'http://www.lewes-eastbourne.gov.uk/',\n",
      " 'NewRatingPending': False,\n",
      " 'Phone': '',\n",
      " 'PostCode': 'BN25 1PG',\n",
      " 'RatingDate': '2021-12-23T00:00:00',\n",
      " 'RatingKey': 'fhrs_1_en-gb',\n",
      " 'RatingValue': 1,\n",
      " 'RightToReply': '',\n",
      " 'SchemeType': 'FHRS',\n",
      " '_id': ObjectId('65b59b1bbeca9933a5cdc6df'),\n",
      " 'geocode': {'latitude': 50.770885, 'longitude': 0.10202},\n",
      " 'links': [{'href': 'https://api.ratings.food.gov.uk/establishments/172735',\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': 10}}\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",
    "\n",
    "# Extract the 'geocode' field from the document with 'BusinessName' 'Penang Flavours'\n",
    "lat_long = establishments.find_one({\"BusinessName\":\"Penang Flavours\"},{\"geocode\":1})\n",
    "\n",
    "\n",
    "degree_search = 0.01\n",
    "# Assign the lat and long values to variables\n",
    "latitude = lat_long['geocode']['latitude']\n",
    "longitude = lat_long['geocode']['longitude']\n",
    "\n",
    "# Calculate the lat/long search area by +/- the 'degree_search' from the lat/long variables\n",
    "query3 = {\"geocode.latitude\":{'$gte': latitude - degree_search, '$lte': latitude + degree_search},\n",
    "        \"geocode.longitude\":{'$gte': longitude - degree_search, '$lte': longitude + degree_search},\n",
    "        \"RatingValue\":5}\n",
    "sort = [(\"scores.Hygiene\",1)]\n",
    "limit = 5\n",
    "\n",
    "# Print the results\n",
    "results3 = list(establishments.find(query).sort(sort).limit(limit))\n",
    "for r in results3:\n",
    "    pprint(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>65b59b1bbeca9933a5cdc4f1</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>1</th>\n",
       "      <td>65b59b1bbeca9933a5cdc6ee</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>2</th>\n",
       "      <td>65b59b1bbeca9933a5cdbe66</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>3</th>\n",
       "      <td>65b59b1bbeca9933a5cdc1e7</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>4</th>\n",
       "      <td>65b59b1bbeca9933a5cdc6df</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id  FHRSID  ChangesByServerID  \\\n",
       "0  65b59b1bbeca9933a5cdc4f1  730933                  0   \n",
       "1  65b59b1bbeca9933a5cdc6ee  172953                  0   \n",
       "2  65b59b1bbeca9933a5cdbe66  110681                  0   \n",
       "3  65b59b1bbeca9933a5cdc1e7  612039                  0   \n",
       "4  65b59b1bbeca9933a5cdc6df  172735                  0   \n",
       "\n",
       "  LocalAuthorityBusinessID         BusinessName  \\\n",
       "0                1698/FOOD        Melrose Hotel   \n",
       "1             PI/000024532        Golden Palace   \n",
       "2                     4029  The Chase Rest Home   \n",
       "3                1970/FOOD           Brenalwood   \n",
       "4             PI/000023858        Seaford Pizza   \n",
       "\n",
       "                        BusinessType  BusinessTypeID           AddressLine1  \\\n",
       "0  Hotel/bed & breakfast/guest house            7842  53 Marine Parade East   \n",
       "1            Restaurant/Cafe/Canteen               1         5 South Street   \n",
       "2                    Caring Premises               5   5-6 Southfields Road   \n",
       "3                    Caring Premises               5              Hall Lane   \n",
       "4             Takeaway/sandwich shop            7844          4 High Street   \n",
       "\n",
       "         AddressLine2 AddressLine3  ...  \\\n",
       "0      Clacton On Sea        Essex  ...   \n",
       "1             Seaford  East Sussex  ...   \n",
       "2          Eastbourne  East Sussex  ...   \n",
       "3  Walton-on-the-Naze        Essex  ...   \n",
       "4             Seaford  East Sussex  ...   \n",
       "\n",
       "                      LocalAuthorityWebSite  \\\n",
       "0             http://www.tendringdc.gov.uk/   \n",
       "1       http://www.lewes-eastbourne.gov.uk/   \n",
       "2  http://www.eastbourne.gov.uk/foodratings   \n",
       "3             http://www.tendringdc.gov.uk/   \n",
       "4       http://www.lewes-eastbourne.gov.uk/   \n",
       "\n",
       "            LocalAuthorityEmailAddress  \\\n",
       "0           fhsadmin@tendringdc.gov.uk   \n",
       "1  ehealth.ldc@lewes-eastbourne.gov.uk   \n",
       "2      Customerfirst@eastbourne.gov.uk   \n",
       "3           fhsadmin@tendringdc.gov.uk   \n",
       "4  ehealth.ldc@lewes-eastbourne.gov.uk   \n",
       "\n",
       "                                              scores  SchemeType  \\\n",
       "0  {'Hygiene': 20, 'Structural': 20, 'ConfidenceI...        FHRS   \n",
       "1  {'Hygiene': 20, 'Structural': 10, 'ConfidenceI...        FHRS   \n",
       "2  {'Hygiene': 20, 'Structural': 20, 'ConfidenceI...        FHRS   \n",
       "3  {'Hygiene': 20, 'Structural': 15, 'ConfidenceI...        FHRS   \n",
       "4  {'Hygiene': 20, 'Structural': 10, 'ConfidenceI...        FHRS   \n",
       "\n",
       "                                          geocode RightToReply     Distance  \\\n",
       "0   {'longitude': 1.15927, 'latitude': 51.789429}               4619.656144   \n",
       "1  {'longitude': 0.101446, 'latitude': 50.770724}               4620.437179   \n",
       "2   {'longitude': 0.27694, 'latitude': 50.769705}               4613.888288   \n",
       "3  {'longitude': 1.278721, 'latitude': 51.857536}               4617.965824   \n",
       "4   {'longitude': 0.10202, 'latitude': 50.770885}               4620.421725   \n",
       "\n",
       "  NewRatingPending                                               meta  \\\n",
       "0            False  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "1            False  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "2            False  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "3            False  {'dataSource': None, 'extractDate': '0001-01-0...   \n",
       "4            False  {'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",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert result to Pandas DataFrame\n",
    "\n",
    "near_penang_df = pd.DataFrame(results3)\n",
    "\n",
    "near_penang_df\n"
   ]
  },
  {
   "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": 15,
   "id": "4eeb227e",
   "metadata": {},
   "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",
    "# Pipeline\n",
    "match = {'$match': {\"scores.Hygiene\":0}}\n",
    "\n",
    "group = {'$group': {\"_id\" : \"$LocalAuthorityName\",\n",
    "                   \"count\": {'$sum':1}}}\n",
    "\n",
    "sort = {'$sort':{\"count\":-1}}\n",
    "\n",
    "pipeline = [match, group, sort]\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": 16,
   "id": "d8aab364",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The number of rows in the DataFrame is: 55\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": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the result to a Pandas DataFrame\n",
    "hygiene0_df = pd.DataFrame(results)\n",
    "\n",
    "# Display the number of rows in the DataFrame\n",
    "print(f\"The number of rows in the DataFrame is: {hygiene0_df.shape[0]}\")\n",
    "\n",
    "# Display the first 10 rows of the DataFrame\n",
    "hygiene0_df.head(10)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}