NoSQL_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "67a2c6e8",
   "metadata": {},
   "source": [
    "# Eat Safe, Love"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed00305f",
   "metadata": {},
   "source": [
    "## Notebook Set Up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7119dacb",
   "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": "e35c7ec9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create an instance of MongoClient\n",
    "mongo = MongoClient(port=27017)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c2a7373d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# assign the uk_food database to a variable name\n",
    "db = mongo['uk_food']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "58dce5f4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['establishments']\n"
     ]
    }
   ],
   "source": [
    "# review the collections in our database\n",
    "print(db.list_collection_names())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3f1c97cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# assign the collection to a variable\n",
    "establishments = db['establishments']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c75fcca",
   "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": "5848ce8c",
   "metadata": {},
   "source": [
    "### 1. Which establishments have a hygiene score equal to 20?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "de772f5f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of documents in result: 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('6418e9ef32212e0cec47ede1'),\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\": {'$eq':20}}\n",
    "results = establishments.find(query)\n",
    "\n",
    "# Use count_documents to display the number of documents in the result\n",
    "print(\"Number of documents in result:\", establishments.count_documents(query))\n",
    "\n",
    "# Display the first document in the results using pprint\n",
    "pprint(results[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b4d12911",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows in hygiene_df:  41\n"
     ]
    }
   ],
   "source": [
    "# Convert the result to a Pandas DataFrame\n",
    "hygiene_df = pd.DataFrame(results)\n",
    "\n",
    "# Display the number of rows in the DataFrame\n",
    "print(\"Number of rows in hygiene_df: \", len(hygiene_df.index))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "c66eebf0",
   "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>6418e9ef32212e0cec47ede1</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>6418e9f032212e0cec47f162</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>6418e9f032212e0cec47f46c</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>6418e9f032212e0cec47f65a</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>6418e9f032212e0cec47f669</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>6418e9f032212e0cec480008</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>6418e9f032212e0cec480229</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>6418e9f232212e0cec481754</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>6418e9f232212e0cec481b98</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>6418e9f232212e0cec4823aa</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  6418e9ef32212e0cec47ede1   110681                  0   \n",
       "1  6418e9f032212e0cec47f162   612039                  0   \n",
       "2  6418e9f032212e0cec47f46c   730933                  0   \n",
       "3  6418e9f032212e0cec47f65a   172735                  0   \n",
       "4  6418e9f032212e0cec47f669   172953                  0   \n",
       "5  6418e9f032212e0cec480008   512854                  0   \n",
       "6  6418e9f032212e0cec480229  1537089                  0   \n",
       "7  6418e9f232212e0cec481754   155648                  0   \n",
       "8  6418e9f232212e0cec481b98  1012883                  0   \n",
       "9  6418e9f232212e0cec4823aa   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": [
    "# Display the first 10 rows of the DataFrame\n",
    "hygiene_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a480a0b",
   "metadata": {},
   "source": [
    "### 2. Which establishments in London have a `RatingValue` greater than or equal to 4?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ca2649dc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of documents in result: 34\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('6418e9f132212e0cec4807fc'),\n",
      " 'geocode': {'latitude': 51.369321, 'longitude': 0.508551},\n",
      " 'links': [{'href': 'https://api.ratings.food.gov.uk/establishments/621707',\n",
      "            'rel': 'self'}],\n",
      " 'meta': {'dataSource': None,\n",
      "          'extractDate': '0001-01-01T00:00:00',\n",
      "          'itemCount': 0,\n",
      "          'pageNumber': 0,\n",
      "          'pageSize': 0,\n",
      "          'returncode': None,\n",
      "          'totalCount': 0,\n",
      "          'totalPages': 0},\n",
      " 'scores': {'ConfidenceInManagement': 5, 'Hygiene': 5, 'Structural': 10}}\n"
     ]
    }
   ],
   "source": [
    "# Find the establishments with London as the Local Authority and has a RatingValue greater than or equal to 4.\n",
    "query2 = {'LocalAuthorityName':{'$regex': 'London'},\n",
    "        'RatingValue':{'$gte':'4'}}\n",
    "\n",
    "results2 = list(establishments.find(query2))\n",
    "\n",
    "# Use count_documents to display the number of documents in the result\n",
    "print(\"Number of documents in result:\", len(results2))\n",
    "\n",
    "# Display the first document in the results using pprint\n",
    "pprint(results2[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f658faa8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows in London_ratings_df:  34\n"
     ]
    }
   ],
   "source": [
    "# Convert the result to a Pandas DataFrame\n",
    "London_ratings_df = pd.DataFrame(results2)\n",
    "\n",
    "# Display the number of rows in the DataFrame\n",
    "print(\"Number of rows in London_ratings_df: \", len(London_ratings_df.index))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b97bcdb3",
   "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>6418e9f132212e0cec4807fc</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>6418e9f132212e0cec480b22</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>6418e9f232212e0cec48166f</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>6418e9f232212e0cec48246f</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>6418e9f232212e0cec482470</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>6418e9f332212e0cec482b78</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>6418e9f432212e0cec484c82</td>\n",
       "      <td>1252616</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000035653</td>\n",
       "      <td>Froottree</td>\n",
       "      <td>Other catering premises</td>\n",
       "      <td>7841</td>\n",
       "      <td>Tereza Joanne King George V Lock</td>\n",
       "      <td>Woolwich Manor Way London</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': None, 'Structural': None, 'Confide...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.0693250000476837, 'latitude': ...</td>\n",
       "      <td></td>\n",
       "      <td>4648.300000</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>6418e9f432212e0cec484c86</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>8</th>\n",
       "      <td>6418e9f432212e0cec48504d</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>9</th>\n",
       "      <td>6418e9f432212e0cec485067</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id   FHRSID  ChangesByServerID  \\\n",
       "0  6418e9f132212e0cec4807fc   621707                  0   \n",
       "1  6418e9f132212e0cec480b22  1130836                  0   \n",
       "2  6418e9f232212e0cec48166f   293783                  0   \n",
       "3  6418e9f232212e0cec48246f  1315095                  0   \n",
       "4  6418e9f232212e0cec482470   294474                  0   \n",
       "5  6418e9f332212e0cec482b78   294900                  0   \n",
       "6  6418e9f432212e0cec484c82  1252616                  0   \n",
       "7  6418e9f432212e0cec484c86   293756                  0   \n",
       "8  6418e9f432212e0cec48504d   878523                  0   \n",
       "9  6418e9f432212e0cec485067   293772                  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/000035653                             Froottree   \n",
       "7             PI/000002538                         Tereza Joanne   \n",
       "8             PI/000029844                      Brick Lane Brews   \n",
       "9             PI/000002585         The Nuance Group (UK) Limited   \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  Other catering premises            7841   \n",
       "8  Restaurant/Cafe/Canteen               1   \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                   Tereza Joanne King George V Lock   \n",
       "7  Funcraft UK Ltd King George V Dock Woolwich Ma...   \n",
       "8                       Air side London City Airport   \n",
       "9                  Duty Free Shop Passenger Terminal   \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             Woolwich Manor Way London     Charlton  ...   \n",
       "7                                London               ...   \n",
       "8                                London               ...   \n",
       "9  London City AirportRoyal DocksLondon               ...   \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': None, 'Structural': None, 'Confide...       FHRS   \n",
       "7  {'Hygiene': 0, 'Structural': 5, 'ConfidenceInM...       FHRS   \n",
       "8  {'Hygiene': 10, 'Structural': 5, 'ConfidenceIn...       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.0693250000476837, 'latitude': ...                \n",
       "7     {'longitude': 0.069286, 'latitude': 51.501121}                \n",
       "8     {'longitude': 0.048006, 'latitude': 51.503733}                \n",
       "9     {'longitude': 0.047966, 'latitude': 51.504112}                \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.300000            False   \n",
       "7  4648.301822            False   \n",
       "8  4649.173485            False   \n",
       "9  4649.188826            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": [
    "# Display the first 10 rows of the DataFrame\n",
    "London_ratings_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3dc76fb4",
   "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": "12e32898",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('6418ecb0ad7f3782cc99f353'),\n",
      " 'geocode': {'latitude': 51.490142, 'longitude': 0.08384}}\n"
     ]
    }
   ],
   "source": [
    "# lat and long of Penang Flavours\n",
    "pprint(establishments.find_one({'BusinessName': 'Penang Flavours'}, {'geocode.latitude', 'geocode.longitude'}))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "69019bd1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'AddressLine1': '101 Plumstead High Street',\n",
      "  'AddressLine2': '',\n",
      "  'AddressLine3': 'Plumstead',\n",
      "  'AddressLine4': 'Greenwich',\n",
      "  'BusinessName': 'Lucky Food & Wine',\n",
      "  'BusinessType': 'Retailers - other',\n",
      "  'BusinessTypeID': 4613,\n",
      "  'ChangesByServerID': 0,\n",
      "  'Distance': 4647.024793263386,\n",
      "  'FHRSID': 695287,\n",
      "  'LocalAuthorityBusinessID': 'PI/000182135',\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 1SB',\n",
      "  'RatingDate': '2022-06-25T00:00:00',\n",
      "  'RatingKey': 'fhrs_5_en-gb',\n",
      "  'RatingValue': '5',\n",
      "  'RightToReply': '',\n",
      "  'SchemeType': 'FHRS',\n",
      "  '_id': ObjectId('6418e9f432212e0cec4846df'),\n",
      "  'geocode': {'latitude': 51.4878934, 'longitude': 0.0910104},\n",
      "  'links': [{'href': 'http://api.ratings.food.gov.uk/establishments/695287',\n",
      "             'rel': 'self'}],\n",
      "  'meta': {'dataSource': None,\n",
      "           'extractDate': '0001-01-01T00:00:00',\n",
      "           'itemCount': 0,\n",
      "           'pageNumber': 0,\n",
      "           'pageSize': 0,\n",
      "           'returncode': None,\n",
      "           'totalCount': 0,\n",
      "           'totalPages': 0},\n",
      "  'scores': {'ConfidenceInManagement': 5, 'Hygiene': 5, 'Structural': 5}},\n",
      " {'AddressLine1': '112 Plumstead High Street',\n",
      "  'AddressLine2': '',\n",
      "  'AddressLine3': 'Plumstead',\n",
      "  'AddressLine4': 'Greenwich',\n",
      "  'BusinessName': 'Fineway Cash & Carry',\n",
      "  'BusinessType': 'Retailers - other',\n",
      "  'BusinessTypeID': 4613,\n",
      "  'ChangesByServerID': 0,\n",
      "  'Distance': 4647.016995291011,\n",
      "  'FHRSID': 694606,\n",
      "  'LocalAuthorityBusinessID': 'PI/000116584',\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 1SJ',\n",
      "  'RatingDate': '2020-10-20T00:00:00',\n",
      "  'RatingKey': 'fhrs_5_en-gb',\n",
      "  'RatingValue': '5',\n",
      "  'RightToReply': '',\n",
      "  'SchemeType': 'FHRS',\n",
      "  '_id': ObjectId('6418e9f432212e0cec4846cb'),\n",
      "  'geocode': {'latitude': 51.4876089, 'longitude': 0.0909382},\n",
      "  'links': [{'href': 'http://api.ratings.food.gov.uk/establishments/694606',\n",
      "             'rel': 'self'}],\n",
      "  'meta': {'dataSource': None,\n",
      "           'extractDate': '0001-01-01T00:00:00',\n",
      "           'itemCount': 0,\n",
      "           'pageNumber': 0,\n",
      "           'pageSize': 0,\n",
      "           'returncode': None,\n",
      "           'totalCount': 0,\n",
      "           'totalPages': 0},\n",
      "  'scores': {'ConfidenceInManagement': 5, 'Hygiene': 5, 'Structural': 5}},\n",
      " {'AddressLine1': '104 Plumstead High Street',\n",
      "  'AddressLine2': '',\n",
      "  'AddressLine3': 'Plumstead',\n",
      "  'AddressLine4': 'Greenwich',\n",
      "  'BusinessName': 'Everest Stores Ltd',\n",
      "  'BusinessType': 'Retailers - other',\n",
      "  'BusinessTypeID': 4613,\n",
      "  'ChangesByServerID': 0,\n",
      "  'Distance': 4647.041728368117,\n",
      "  'FHRSID': 695033,\n",
      "  'LocalAuthorityBusinessID': 'PI/000172986',\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 1SJ',\n",
      "  'RatingDate': '2021-09-13T00:00:00',\n",
      "  'RatingKey': 'fhrs_5_en-gb',\n",
      "  'RatingValue': '5',\n",
      "  'RightToReply': '',\n",
      "  'SchemeType': 'FHRS',\n",
      "  '_id': ObjectId('6418e9f432212e0cec484702'),\n",
      "  'geocode': {'latitude': 51.4876718, 'longitude': 0.0903232},\n",
      "  'links': [{'href': 'http://api.ratings.food.gov.uk/establishments/695033',\n",
      "             'rel': 'self'}],\n",
      "  'meta': {'dataSource': None,\n",
      "           'extractDate': '0001-01-01T00:00:00',\n",
      "           'itemCount': 0,\n",
      "           'pageNumber': 0,\n",
      "           'pageSize': 0,\n",
      "           'returncode': None,\n",
      "           'totalCount': 0,\n",
      "           'totalPages': 0},\n",
      "  'scores': {'ConfidenceInManagement': 5, 'Hygiene': 5, 'Structural': 5}},\n",
      " {'AddressLine1': '102 Plumstead High Street',\n",
      "  'AddressLine2': '',\n",
      "  'AddressLine3': 'Plumstead',\n",
      "  'AddressLine4': 'Greenwich',\n",
      "  'BusinessName': 'Premier Express',\n",
      "  'BusinessType': 'Retailers - other',\n",
      "  'BusinessTypeID': 4613,\n",
      "  'ChangesByServerID': 0,\n",
      "  'Distance': 4647.038806595731,\n",
      "  'FHRSID': 695566,\n",
      "  'LocalAuthorityBusinessID': 'PI/000188497',\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 1SJ',\n",
      "  'RatingDate': '2021-06-10T00:00:00',\n",
      "  'RatingKey': 'fhrs_5_en-gb',\n",
      "  'RatingValue': '5',\n",
      "  'RightToReply': '',\n",
      "  'SchemeType': 'FHRS',\n",
      "  '_id': ObjectId('6418e9f432212e0cec4846fa'),\n",
      "  'geocode': {'latitude': 51.4877234, 'longitude': 0.0904552},\n",
      "  'links': [{'href': 'http://api.ratings.food.gov.uk/establishments/695566',\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': 5, 'Structural': 0}},\n",
      " {'AddressLine1': '152 Plumstead High Street',\n",
      "  'AddressLine2': '',\n",
      "  'AddressLine3': 'Plumstead',\n",
      "  'AddressLine4': 'Greenwich',\n",
      "  'BusinessName': 'TIWA N TIWA African Restaurant Ltd',\n",
      "  'BusinessType': 'Restaurant/Cafe/Canteen',\n",
      "  'BusinessTypeID': 1,\n",
      "  'ChangesByServerID': 0,\n",
      "  'Distance': 4646.930146257832,\n",
      "  'FHRSID': 1069652,\n",
      "  'LocalAuthorityBusinessID': 'PI/000206841',\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': '2020-10-24T00:00:00',\n",
      "  'RatingKey': 'fhrs_5_en-gb',\n",
      "  'RatingValue': '5',\n",
      "  'RightToReply': '',\n",
      "  'SchemeType': 'FHRS',\n",
      "  '_id': ObjectId('6418e9f432212e0cec4845ec'),\n",
      "  'geocode': {'latitude': 51.4870351, 'longitude': 0.0927429},\n",
      "  'links': [{'href': 'http://api.ratings.food.gov.uk/establishments/1069652',\n",
      "             'rel': 'self'}],\n",
      "  'meta': {'dataSource': None,\n",
      "           'extractDate': '0001-01-01T00:00:00',\n",
      "           'itemCount': 0,\n",
      "           'pageNumber': 0,\n",
      "           'pageSize': 0,\n",
      "           'returncode': None,\n",
      "           'totalCount': 0,\n",
      "           'totalPages': 0},\n",
      "  'scores': {'ConfidenceInManagement': 5, 'Hygiene': 5, 'Structural': 5}}]\n"
     ]
    }
   ],
   "source": [
    "# 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.49014200\n",
    "longitude = 0.08384000\n",
    "\n",
    "query3 = {'RatingValue':'5',\n",
    "         'geocode.latitude': {'$lte': (latitude + degree_search), '$gte': (latitude - degree_search)},\n",
    "         'geocode.longitude': {'$lte': (longitude + degree_search), '$gte': (longitude - degree_search)}}\n",
    "\n",
    "sort =  [(\"scores.Hygiene\", -1)]\n",
    "\n",
    "results3 = list(establishments.find(query3).sort(sort).limit(5))\n",
    "\n",
    "# Print the results\n",
    "pprint(results3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "0e050dd4",
   "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>6418e9f432212e0cec4846df</td>\n",
       "      <td>695287</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000182135</td>\n",
       "      <td>Lucky Food &amp; Wine</td>\n",
       "      <td>Retailers - other</td>\n",
       "      <td>4613</td>\n",
       "      <td>101 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': 5, 'Structural': 5, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.0910104, 'latitude': 51.4878934}</td>\n",
       "      <td></td>\n",
       "      <td>4647.024793</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>6418e9f432212e0cec4846cb</td>\n",
       "      <td>694606</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000116584</td>\n",
       "      <td>Fineway Cash &amp; Carry</td>\n",
       "      <td>Retailers - other</td>\n",
       "      <td>4613</td>\n",
       "      <td>112 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': 5, 'Structural': 5, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.0909382, 'latitude': 51.4876089}</td>\n",
       "      <td></td>\n",
       "      <td>4647.016995</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>6418e9f432212e0cec484702</td>\n",
       "      <td>695033</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000172986</td>\n",
       "      <td>Everest Stores Ltd</td>\n",
       "      <td>Retailers - other</td>\n",
       "      <td>4613</td>\n",
       "      <td>104 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': 5, 'Structural': 5, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.0903232, 'latitude': 51.4876718}</td>\n",
       "      <td></td>\n",
       "      <td>4647.041728</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>6418e9f432212e0cec4846fa</td>\n",
       "      <td>695566</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000188497</td>\n",
       "      <td>Premier Express</td>\n",
       "      <td>Retailers - other</td>\n",
       "      <td>4613</td>\n",
       "      <td>102 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': 5, 'Structural': 0, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.0904552, 'latitude': 51.4877234}</td>\n",
       "      <td></td>\n",
       "      <td>4647.038807</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>6418e9f432212e0cec4845ec</td>\n",
       "      <td>1069652</td>\n",
       "      <td>0</td>\n",
       "      <td>PI/000206841</td>\n",
       "      <td>TIWA N TIWA African Restaurant Ltd</td>\n",
       "      <td>Restaurant/Cafe/Canteen</td>\n",
       "      <td>1</td>\n",
       "      <td>152 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': 5, 'Structural': 5, 'ConfidenceInM...</td>\n",
       "      <td>FHRS</td>\n",
       "      <td>{'longitude': 0.0927429, 'latitude': 51.4870351}</td>\n",
       "      <td></td>\n",
       "      <td>4646.930146</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  6418e9f432212e0cec4846df   695287                  0   \n",
       "1  6418e9f432212e0cec4846cb   694606                  0   \n",
       "2  6418e9f432212e0cec484702   695033                  0   \n",
       "3  6418e9f432212e0cec4846fa   695566                  0   \n",
       "4  6418e9f432212e0cec4845ec  1069652                  0   \n",
       "\n",
       "  LocalAuthorityBusinessID                        BusinessName  \\\n",
       "0             PI/000182135                   Lucky Food & Wine   \n",
       "1             PI/000116584                Fineway Cash & Carry   \n",
       "2             PI/000172986                  Everest Stores Ltd   \n",
       "3             PI/000188497                     Premier Express   \n",
       "4             PI/000206841  TIWA N TIWA African Restaurant Ltd   \n",
       "\n",
       "              BusinessType  BusinessTypeID               AddressLine1  \\\n",
       "0        Retailers - other            4613  101 Plumstead High Street   \n",
       "1        Retailers - other            4613  112 Plumstead High Street   \n",
       "2        Retailers - other            4613  104 Plumstead High Street   \n",
       "3        Retailers - other            4613  102 Plumstead High Street   \n",
       "4  Restaurant/Cafe/Canteen               1  152 Plumstead High Street   \n",
       "\n",
       "  AddressLine2 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': 5, 'Structural': 5, 'ConfidenceInM...       FHRS   \n",
       "1  {'Hygiene': 5, 'Structural': 5, 'ConfidenceInM...       FHRS   \n",
       "2  {'Hygiene': 5, 'Structural': 5, 'ConfidenceInM...       FHRS   \n",
       "3  {'Hygiene': 5, 'Structural': 0, 'ConfidenceInM...       FHRS   \n",
       "4  {'Hygiene': 5, 'Structural': 5, 'ConfidenceInM...       FHRS   \n",
       "\n",
       "                                            geocode RightToReply     Distance  \\\n",
       "0  {'longitude': 0.0910104, 'latitude': 51.4878934}               4647.024793   \n",
       "1  {'longitude': 0.0909382, 'latitude': 51.4876089}               4647.016995   \n",
       "2  {'longitude': 0.0903232, 'latitude': 51.4876718}               4647.041728   \n",
       "3  {'longitude': 0.0904552, 'latitude': 51.4877234}               4647.038807   \n",
       "4  {'longitude': 0.0927429, 'latitude': 51.4870351}               4646.930146   \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': '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": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert result to Pandas DataFrame\n",
    "Penang_Flavours_proximity_df = pd.DataFrame(results3)\n",
    "Penang_Flavours_proximity_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64f5c19d",
   "metadata": {},
   "source": [
    "### 4. How many establishments in each Local Authority area have a hygiene score of 0?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "ebd398e1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of documents:  55\n",
      "[{'_id': {'Local Authority Name': 'Thanet'}, 'count': 1130},\n",
      " {'_id': {'Local Authority Name': 'Greenwich'}, 'count': 882},\n",
      " {'_id': {'Local Authority Name': 'Maidstone'}, 'count': 713},\n",
      " {'_id': {'Local Authority Name': 'Newham'}, 'count': 711},\n",
      " {'_id': {'Local Authority Name': 'Swale'}, 'count': 686},\n",
      " {'_id': {'Local Authority Name': 'Chelmsford'}, 'count': 680},\n",
      " {'_id': {'Local Authority Name': 'Medway'}, 'count': 672},\n",
      " {'_id': {'Local Authority Name': 'Bexley'}, 'count': 607},\n",
      " {'_id': {'Local Authority Name': 'Southend-On-Sea'}, 'count': 586},\n",
      " {'_id': {'Local Authority Name': '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",
    "\n",
    "match_query = {\"$match\": {\"scores.Hygiene\": {'$eq': 0}}\n",
    "              }\n",
    "\n",
    "group_query = {\"$group\": \n",
    "                   {'_id': {\"Local Authority Name\": \"$LocalAuthorityName\"},\n",
    "                    'count': {'$sum': 1}\n",
    "                   }\n",
    "              }\n",
    "\n",
    "sort_hilo = {\"$sort\":\n",
    "                 {'count': -1}\n",
    "            }\n",
    "\n",
    "pipeline = [match_query, group_query, sort_hilo]\n",
    "\n",
    "results4 = list(establishments.aggregate(pipeline))\n",
    "\n",
    "# Print the number of documents in the result\n",
    "print(\"Number of documents:\", len(results4))\n",
    "\n",
    "# Print the first 10 results\n",
    "pprint(results4[0:10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "3e9c6318",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows: 55\n"
     ]
    }
   ],
   "source": [
    "# Convert the result to a Pandas DataFrame\n",
    "LocalAuthority_hygienescore_df = pd.DataFrame(results4)\n",
    "\n",
    "# Display the number of rows in the DataFrame\n",
    "print(\"Number of rows:\", len(LocalAuthority_hygienescore_df))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "40ee60d8",
   "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'Local Authority Name': 'Thanet'}</td>\n",
       "      <td>1130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'Local Authority Name': 'Greenwich'}</td>\n",
       "      <td>882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'Local Authority Name': 'Maidstone'}</td>\n",
       "      <td>713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'Local Authority Name': 'Newham'}</td>\n",
       "      <td>711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'Local Authority Name': 'Swale'}</td>\n",
       "      <td>686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>{'Local Authority Name': 'Chelmsford'}</td>\n",
       "      <td>680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>{'Local Authority Name': 'Medway'}</td>\n",
       "      <td>672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>{'Local Authority Name': 'Bexley'}</td>\n",
       "      <td>607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>{'Local Authority Name': 'Southend-On-Sea'}</td>\n",
       "      <td>586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>{'Local Authority Name': 'Tendring'}</td>\n",
       "      <td>542</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           _id  count\n",
       "0           {'Local Authority Name': 'Thanet'}   1130\n",
       "1        {'Local Authority Name': 'Greenwich'}    882\n",
       "2        {'Local Authority Name': 'Maidstone'}    713\n",
       "3           {'Local Authority Name': 'Newham'}    711\n",
       "4            {'Local Authority Name': 'Swale'}    686\n",
       "5       {'Local Authority Name': 'Chelmsford'}    680\n",
       "6           {'Local Authority Name': 'Medway'}    672\n",
       "7           {'Local Authority Name': 'Bexley'}    607\n",
       "8  {'Local Authority Name': 'Southend-On-Sea'}    586\n",
       "9         {'Local Authority Name': 'Tendring'}    542"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display the first 10 rows of the DataFrame\n",
    "LocalAuthority_hygienescore_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b71e5c9",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}