NoSQL_analysis.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "9a57c4c6",
   "metadata": {},
   "source": [
    "# Eat Safe, Love"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "064ef857",
   "metadata": {},
   "source": [
    "## Notebook Set Up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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']"
   ]
  },
  {
   "attachments": {},
   "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."
   ]
  },
  {
   "attachments": {},
   "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": [
      "41\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('6458120c9d207b53d8594470'),\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",
    "hygiene_num = establishments.count_documents(query)\n",
    "\n",
    "# Display the first document in the results using pprint\n",
    "document = establishments.find_one(query)\n",
    "\n",
    "print(hygiene_num)\n",
    "pprint(document)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a6e5cece",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "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>6458120c9d207b53d8594470</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>6458120c9d207b53d85947f0</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>6458120c9d207b53d8594afb</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>6458120c9d207b53d8594ce8</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>6458120c9d207b53d8594cf6</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>6458120c9d207b53d8595698</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>6458120c9d207b53d85958bf</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>6458120c9d207b53d8596dea</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>6458120c9d207b53d8597228</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>6458120c9d207b53d8597a35</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  6458120c9d207b53d8594470   110681                  0   \n",
       "1  6458120c9d207b53d85947f0   612039                  0   \n",
       "2  6458120c9d207b53d8594afb   730933                  0   \n",
       "3  6458120c9d207b53d8594ce8   172735                  0   \n",
       "4  6458120c9d207b53d8594cf6   172953                  0   \n",
       "5  6458120c9d207b53d8595698   512854                  0   \n",
       "6  6458120c9d207b53d85958bf  1537089                  0   \n",
       "7  6458120c9d207b53d8596dea   155648                  0   \n",
       "8  6458120c9d207b53d8597228  1012883                  0   \n",
       "9  6458120c9d207b53d8597a35   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": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the result to a Pandas DataFrame\n",
    "df = pd.DataFrame(establishments.find(query))\n",
    "# Display the number of rows in the DataFrame\n",
    "print(len(df))\n",
    "# Display the first 10 rows of the DataFrame\n",
    "df.head(10)"
   ]
  },
  {
   "attachments": {},
   "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": 14,
   "id": "93c63829",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "33\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('6458120c9d207b53d8595e95'),\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",
    "query = {\"LocalAuthorityName\": {\"$regex\": \"London\"}, \"RatingValue\": {\"$gte\": 4}}\n",
    "\n",
    "# Use count_documents to display the number of documents in the result\n",
    "count_docs = establishments.count_documents(query)\n",
    "print(count_docs)\n",
    "\n",
    "# Display the first document in the results using pprint\n",
    "pprint(establishments.find_one(query))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "07e24ccb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "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>6458120c9d207b53d8595e95</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>6458120c9d207b53d85961c1</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>6458120c9d207b53d8596d00</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>6458120c9d207b53d8597aff</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>4</th>\n",
       "      <td>6458120c9d207b53d8597b01</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>5</th>\n",
       "      <td>6458120c9d207b53d859820d</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>6458120d9d207b53d859a314</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>6458120d9d207b53d859a6db</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>6458120d9d207b53d859a6f4</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>6458120d9d207b53d859a6f6</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  6458120c9d207b53d8595e95   621707                  0   \n",
       "1  6458120c9d207b53d85961c1  1130836                  0   \n",
       "2  6458120c9d207b53d8596d00   293783                  0   \n",
       "3  6458120c9d207b53d8597aff   294474                  0   \n",
       "4  6458120c9d207b53d8597b01  1315095                  0   \n",
       "5  6458120c9d207b53d859820d   294900                  0   \n",
       "6  6458120d9d207b53d859a314   293756                  0   \n",
       "7  6458120d9d207b53d859a6db   878523                  0   \n",
       "8  6458120d9d207b53d859a6f4   293772                  0   \n",
       "9  6458120d9d207b53d859a6f6   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/000014647              Tilbury Seafarers Centre   \n",
       "4             PI/000036464  Coombs Catering t/a The Lock and Key   \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                          Tenants Row Tilbury Docks   \n",
       "4                                  Leslie Ford House   \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 Essex               ...   \n",
       "4                               Tilbury     Charlton  ...   \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': 0, 'ConfidenceInM...        FHRS   \n",
       "4  {'Hygiene': 0, 'Structural': 5, '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.346325, 'latitude': 51.464078}                \n",
       "4  {'longitude': 0.346336990594864, 'latitude': 5...                \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.847650            False   \n",
       "4  4636.846754            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": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the result to a Pandas DataFrame\n",
    "london_df = pd.DataFrame(establishments.find(query))\n",
    "\n",
    "# Display the number of rows in the DataFrame\n",
    "print(len(london_df))\n",
    "\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": 16,
   "id": "a9040821",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('64581bccdf2fcb629de65902'),\n",
      " 'geocode': {'latitude': 51.490142, 'longitude': 0.08384}}\n"
     ]
    }
   ],
   "source": [
    "pprint(establishments.find_one({\"BusinessName\": \"Penang Flavours\"}, {\"geocode.latitude\", \"geocode.longitude\"}))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "7f599f3d",
   "metadata": {},
   "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('6458120d9d207b53d8599cd0'),\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': '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('6458120d9d207b53d8599cb5'),\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",
      "{'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('6458120d9d207b53d8599ca1'),\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"
     ]
    }
   ],
   "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",
    "degree_search = 0.01\n",
    "latitude = 51.490142\n",
    "longitude = 0.08384\n",
    "\n",
    "query = {'RatingValue': 5, 'geocode.latitude': {'$lte': (latitude + degree_search), '$gte': (latitude - degree_search)}, 'geocode.longitude': {'$lte': (longitude + degree_search), '$gte': (longitude - degree_search)}}\n",
    "sort =  [(\"scores.Hygiene\", 1)]\n",
    "\n",
    "# Print the results\n",
    "for result in establishments.find(query).sort(sort).limit(3):\n",
    "    pprint(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>6458120d9d207b53d8599cb5</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",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6458120d9d207b53d8599cec</td>\n",
       "      <td>909912</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000201100</td>\n",
       "      <td>Lumbini Grocery Ltd T/A Al-Iman</td>\n",
       "      <td>Retailers - other</td>\n",
       "      <td>4613</td>\n",
       "      <td>17 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': 5, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.0916256, 'latitude': 51.4871632}</td>\n",
       "      <td></td>\n",
       "      <td>4646.975587</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>6458120d9d207b53d8599ce6</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>3</th>\n",
       "      <td>6458120d9d207b53d8599ca1</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>6458120d9d207b53d8599cd0</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id   FHRSID  ChangesByServerID  \\\n",
       "0  6458120d9d207b53d8599cb5  1380578                  0   \n",
       "1  6458120d9d207b53d8599cec   909912                  0   \n",
       "2  6458120d9d207b53d8599ce6   695241                  0   \n",
       "3  6458120d9d207b53d8599ca1   695223                  0   \n",
       "4  6458120d9d207b53d8599cd0   694609                  0   \n",
       "\n",
       "  LocalAuthorityBusinessID                         BusinessName  \\\n",
       "0                    14425  Howe and Co Fish and Chips - Van 17   \n",
       "1             PI/000201100      Lumbini Grocery Ltd T/A Al-Iman   \n",
       "2             PI/000179088              Plumstead Manor Nursery   \n",
       "3             PI/000178842                              Iceland   \n",
       "4             PI/000116619                            Volunteer   \n",
       "\n",
       "                            BusinessType  BusinessTypeID  \\\n",
       "0                         Mobile caterer            7846   \n",
       "1                      Retailers - other            4613   \n",
       "2                        Caring Premises               5   \n",
       "3  Retailers - supermarkets/hypermarkets            7840   \n",
       "4                      Pub/bar/nightclub            7843   \n",
       "\n",
       "                                        AddressLine1 AddressLine2  \\\n",
       "0  Restaurant And Premises 107A Plumstead High St...                \n",
       "1                                   17 Lakedale Road                \n",
       "2               Plumstead Manor School Old Mill Road                \n",
       "3                    144 - 146 Plumstead High Street                \n",
       "4                    130 - 132 Plumstead High Street                \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': 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",
       "\n",
       "                                             geocode RightToReply  \\\n",
       "0  {'longitude': 0.0925370007753372, 'latitude': ...                \n",
       "1   {'longitude': 0.0916256, 'latitude': 51.4871632}                \n",
       "2  {'longitude': 0.0859939977526665, 'latitude': ...                \n",
       "3  {'longitude': 0.0924199968576431, 'latitude': ...                \n",
       "4     {'longitude': 0.09208, 'latitude': 51.4873437}                \n",
       "\n",
       "      Distance NewRatingPending  \\\n",
       "0  4646.955931            False   \n",
       "1  4646.975587            False   \n",
       "2  4646.974010            False   \n",
       "3  4646.946071            False   \n",
       "4  4646.965635            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": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "penang_df = pd.DataFrame(establishments.find(query).sort(sort).limit(5))\n",
    "penang_df.head()"
   ]
  },
  {
   "attachments": {},
   "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": 23,
   "id": "4eeb227e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "55\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",
    "query = {\"$match\": {\"scores.Hygiene\": 0}}\n",
    "group = {\"$group\": {\"_id\": \"$LocalAuthorityName\", \"count\": {\"$sum\": 1}}}\n",
    "sort = {\"$sort\": {\"count\": -1}}\n",
    "combined_list = [query, group, sort]\n",
    "results = list(establishments.aggregate(combined_list))\n",
    "\n",
    "# Print the number of documents in the result\n",
    "print(len(results))\n",
    "\n",
    "# Print the first 10 results\n",
    "pprint(results[:10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "d8aab364",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "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": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the result to a Pandas DataFrame\n",
    "hygiene_df = pd.json_normalize(results)\n",
    "\n",
    "# Display the number of rows in the DataFrame\n",
    "print(len(hygiene_df))\n",
    "\n",
    "# Display the first 10 rows of the DataFrame\n",
    "hygiene_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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}