testing_matchsiret.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2e9d564d-f705-42ff-a7d6-3f0cad78932e",
   "metadata": {},
   "source": [
    "# Evaluation of SIRET index on French RESTAURANTS open database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "7975fb91-a76c-4696-8d18-32a7218de6d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%reload_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "65619af8-3225-4f6b-be7d-5b544e9f969e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from worksites import WorkSiteName, GeoWorkSiteName, geocode_worksites\n",
    "from queries import request_elastic, get_answer\n",
    "import pandas as pd\n",
    "import os\n",
    "import elasticsearch as es"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "9dc4e9b4-82f3-4710-98bc-6e415d39e193",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--2022-12-03 16:07:05--  https://www.data.gouv.fr/fr/datasets/r/fff0cc27-977b-40d5-9c11-f7e4e79a0b72\n",
      "Resolving www.data.gouv.fr (www.data.gouv.fr)... 37.59.183.93\n",
      "Connecting to www.data.gouv.fr (www.data.gouv.fr)|37.59.183.93|:443... connected.\n",
      "HTTP request sent, awaiting response... 302 FOUND\n",
      "Location: https://dgal.opendatasoft.com/explore/dataset/export_alimconfiance/download/?format=csv&timezone=Europe/Berlin&use_labels_for_header=true [following]\n",
      "--2022-12-03 16:07:05--  https://dgal.opendatasoft.com/explore/dataset/export_alimconfiance/download/?format=csv&timezone=Europe/Berlin&use_labels_for_header=true\n",
      "Resolving dgal.opendatasoft.com (dgal.opendatasoft.com)... 34.249.199.226, 34.248.20.69\n",
      "Connecting to dgal.opendatasoft.com (dgal.opendatasoft.com)|34.249.199.226|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: unspecified [application/csv]\n",
      "Saving to: ‘export_alimconfiance.csv’\n",
      "\n",
      "export_alimconfianc     [             <=>    ]   6.44M   393KB/s    in 19s     \n",
      "\n",
      "2022-12-03 16:07:24 (351 KB/s) - ‘export_alimconfiance.csv’ saved [6749297]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    os.remove(\"export_alimconfiance.csv\")\n",
    "except:\n",
    "    pass\n",
    "!wget -c \"https://www.data.gouv.fr/fr/datasets/r/fff0cc27-977b-40d5-9c11-f7e4e79a0b72\" -O export_alimconfiance.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "046bcff6-dea5-49f0-81a3-129d5dc71b7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Preparing evaluatest dataset (database of French restaurants)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "59d4820e-65df-4d7b-814c-91a71653a088",
   "metadata": {},
   "outputs": [],
   "source": [
    "size_test = 10\n",
    "df = pd.read_csv(\"export_alimconfiance.csv\", sep=\";\", dtype=str)[[\"APP_Libelle_etablissement\", \"SIRET\", \"Libelle_commune\", \"Code_postal\", \"Adresse_2_UA\", \"geores\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "0380505a-75ee-470a-abcb-2dc2d5664450",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[(df[\"Code_postal\"].str[:2] != \"95\") & (df[\"Code_postal\"].str[:2] != \"94\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "07eccb7d-1d59-4505-a1fd-4020e4425459",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "179a26aa-5e3d-427e-b8ff-4058295be985",
   "metadata": {},
   "outputs": [],
   "source": [
    "wks = [\n",
    "    WorkSiteName(**{\n",
    "        \"siret\":row[\"SIRET\"],\n",
    "        \"name\": row[\"APP_Libelle_etablissement\"],\n",
    "        \"postcode\": row[\"Code_postal\"],\n",
    "        \"cityname\": row[\"Libelle_commune\"], \n",
    "        \"address\": row[\"Adresse_2_UA\"]\n",
    "    })\n",
    "    for k, row in df.head(size_test).iterrows()\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "312a2f5e-2c1b-4e76-92df-8fcf1dc20489",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "geowks = geocode_worksites(wks)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "0b33cd03-1abf-4a31-9114-a6ef64ff43aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "ELASTIC_URL = \"http://elasticsearch-master:9200\"\n",
    "conn = es.Elasticsearch(ELASTIC_URL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "a05f7ce0-82e1-48ad-aaaf-56cb15956f0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check if the first response of Elasticsearch"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "8463ae4a-5bc3-4932-a3a6-342ea6f0a048",
   "metadata": {},
   "outputs": [],
   "source": [
    "geo_threshold = 0.7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "6ae3492a-9f56-40c8-9280-0c57d4c5c427",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/mamba/lib/python3.10/site-packages/elasticsearch/connection/base.py:200: ElasticsearchWarning: Elasticsearch built-in security features are not enabled. Without authentication, your cluster could be accessible to anyone. See https://www.elastic.co/guide/en/elasticsearch/reference/7.17/security-minimal-setup.html to enable security.\n",
      "  warnings.warn(message, category=ElasticsearchWarning)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----\n",
      "LE SAINT JOSEPH (RESTAURANT DE LA BOULANGERIE)\n",
      "48.913036 - 31017429700013 - restaurant de la boulangerie\n",
      "----\n",
      "NATURALIA (NATURALIA FRANCE)\n",
      "73.55325 - 30247464801894 - naturalia france\n",
      "----\n",
      "SOCIETE CAPOCCI\n",
      "75.06476 - 31544532000031 - societe capocci\n",
      "----\n",
      "SOCIETE BISCUITERIE ERTE\n",
      "87.90961 - 30918204600024 - societe biscuiterie erte\n",
      "----\n",
      "LECLERC (PLOUDAL DISTRIBUTION)\n",
      "65.48759 - 30959801900052 - ploudal distribution\n",
      "----\n",
      "AUX PRODUITS SAVOYARDS (SICA L'ARVAN)\n",
      "73.71382 - 31739832900019 - sica l'arvan\n",
      "----\n",
      "INTERMARCHE (MADIF)\n",
      "57.327126 - 32676801700021 - madif\n",
      "----\n",
      "ETABLISSEMENTS MICHEL COME\n",
      "63.328156 - 31773315200019 - etablissements michel come\n",
      "----\n",
      "SICA BEAUFORT DES MONTAGNES DE MOUTIERS\n",
      "82.718056 - 32020196500011 - sica beaufort des montagnes de moutiers\n",
      "----\n",
      "LIDL (LIDL)\n",
      "40.758163 - 34326262223612 - lidl snc lidl\n",
      "\n",
      "\n",
      "SCORE OF RETRIEVAL: 100.0%\n"
     ]
    }
   ],
   "source": [
    "\n",
    "score = 0\n",
    "for geowk in geowks:\n",
    "    answer = get_answer(conn, geoworksite=geowk, siret_truth=geowk.siret, geo_threshold=geo_threshold)\n",
    "    print(\"----\")\n",
    "    print(geowk.name)\n",
    "    if answer:\n",
    "        score += 1\n",
    "        print(\"{} - {} - {}\".format(answer[\"_score\"], geowk.siret, answer[\"_source\"][\"nom_complet\"]))\n",
    "    else:\n",
    "        print(\"no match\")\n",
    "\n",
    "print(f\"\\n\\nSCORE OF RETRIEVAL: {score/size_test*100}%\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "d42c4488-4db0-4ca3-9958-5a6154dc010a",
   "metadata": {
    "tags": []
   },
   "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>APP_Libelle_etablissement</th>\n",
       "      <th>SIRET</th>\n",
       "      <th>Libelle_commune</th>\n",
       "      <th>Code_postal</th>\n",
       "      <th>Adresse_2_UA</th>\n",
       "      <th>geores</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LE SAINT JOSEPH (RESTAURANT DE LA BOULANGERIE)</td>\n",
       "      <td>31017429700013</td>\n",
       "      <td>LYON</td>\n",
       "      <td>69002</td>\n",
       "      <td>46 R MERCIERE</td>\n",
       "      <td>45.762926,4.832648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NATURALIA (NATURALIA FRANCE)</td>\n",
       "      <td>30247464801894</td>\n",
       "      <td>PUTEAUX</td>\n",
       "      <td>92800</td>\n",
       "      <td>36 RUE EUGENE EICHENBERGER</td>\n",
       "      <td>48.881317,2.23746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SOCIETE CAPOCCI</td>\n",
       "      <td>31544532000031</td>\n",
       "      <td>VERSAILLES</td>\n",
       "      <td>78000</td>\n",
       "      <td>CARRE A LA VIANDE</td>\n",
       "      <td>48.797148,2.127159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>SOCIETE BISCUITERIE ERTE</td>\n",
       "      <td>30918204600024</td>\n",
       "      <td>LE PERRAY EN YVELINES</td>\n",
       "      <td>78610</td>\n",
       "      <td>LIEUDIT LES HAPHLERIES</td>\n",
       "      <td>48.703268,1.855354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>LECLERC (PLOUDAL DISTRIBUTION)</td>\n",
       "      <td>30959801900052</td>\n",
       "      <td>PLOUDALMEZEAU</td>\n",
       "      <td>29830</td>\n",
       "      <td>KERUSCAT</td>\n",
       "      <td>48.540309,-4.66693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33837</th>\n",
       "      <td>HOTEL DIEU DE PARIS HOPITAL</td>\n",
       "      <td>26750045200599</td>\n",
       "      <td>Paris 4e  Arrondissement</td>\n",
       "      <td>75004</td>\n",
       "      <td>6 OFFICES / UNITES DE SOINS</td>\n",
       "      <td>48.854242,2.351101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33838</th>\n",
       "      <td>LYCEE GENERAL TECHNOLOGIQUE JULES FERRY</td>\n",
       "      <td>19781845300015</td>\n",
       "      <td>Conflans-Sainte-Honorine</td>\n",
       "      <td>78700</td>\n",
       "      <td>7, rue Jules Bouyssel</td>\n",
       "      <td>48.998733,2.096114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33839</th>\n",
       "      <td>LA FA BRICK</td>\n",
       "      <td>83833633700020</td>\n",
       "      <td>Paris 18e  Arrondissement</td>\n",
       "      <td>75018</td>\n",
       "      <td>47 RUE ORDENER</td>\n",
       "      <td>48.890917,2.352527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33840</th>\n",
       "      <td>LES ATELIERS DU GOUT</td>\n",
       "      <td>31933184900053</td>\n",
       "      <td>Barbezieux-Saint-Hilaire</td>\n",
       "      <td>16300</td>\n",
       "      <td>6, rte de Montmoreau</td>\n",
       "      <td>45.467673,-0.150544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33841</th>\n",
       "      <td>GAEC DES THIBAUDS</td>\n",
       "      <td>53903833100010</td>\n",
       "      <td>Miscon</td>\n",
       "      <td>26310</td>\n",
       "      <td>375 CHE DES TERRAIONS</td>\n",
       "      <td>44.6289,5.520219</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>32546 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            APP_Libelle_etablissement           SIRET  \\\n",
       "1      LE SAINT JOSEPH (RESTAURANT DE LA BOULANGERIE)  31017429700013   \n",
       "3                        NATURALIA (NATURALIA FRANCE)  30247464801894   \n",
       "4                                     SOCIETE CAPOCCI  31544532000031   \n",
       "5                            SOCIETE BISCUITERIE ERTE  30918204600024   \n",
       "6                      LECLERC (PLOUDAL DISTRIBUTION)  30959801900052   \n",
       "...                                               ...             ...   \n",
       "33837                     HOTEL DIEU DE PARIS HOPITAL  26750045200599   \n",
       "33838         LYCEE GENERAL TECHNOLOGIQUE JULES FERRY  19781845300015   \n",
       "33839                                     LA FA BRICK  83833633700020   \n",
       "33840                            LES ATELIERS DU GOUT  31933184900053   \n",
       "33841                               GAEC DES THIBAUDS  53903833100010   \n",
       "\n",
       "                 Libelle_commune Code_postal                 Adresse_2_UA  \\\n",
       "1                           LYON       69002                46 R MERCIERE   \n",
       "3                        PUTEAUX       92800   36 RUE EUGENE EICHENBERGER   \n",
       "4                     VERSAILLES       78000            CARRE A LA VIANDE   \n",
       "5          LE PERRAY EN YVELINES       78610       LIEUDIT LES HAPHLERIES   \n",
       "6                  PLOUDALMEZEAU       29830                     KERUSCAT   \n",
       "...                          ...         ...                          ...   \n",
       "33837   Paris 4e  Arrondissement       75004  6 OFFICES / UNITES DE SOINS   \n",
       "33838   Conflans-Sainte-Honorine       78700        7, rue Jules Bouyssel   \n",
       "33839  Paris 18e  Arrondissement       75018               47 RUE ORDENER   \n",
       "33840   Barbezieux-Saint-Hilaire       16300         6, rte de Montmoreau   \n",
       "33841                     Miscon       26310        375 CHE DES TERRAIONS   \n",
       "\n",
       "                    geores  \n",
       "1       45.762926,4.832648  \n",
       "3        48.881317,2.23746  \n",
       "4       48.797148,2.127159  \n",
       "5       48.703268,1.855354  \n",
       "6       48.540309,-4.66693  \n",
       "...                    ...  \n",
       "33837   48.854242,2.351101  \n",
       "33838   48.998733,2.096114  \n",
       "33839   48.890917,2.352527  \n",
       "33840  45.467673,-0.150544  \n",
       "33841     44.6289,5.520219  \n",
       "\n",
       "[32546 rows x 6 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60a15a91-40b9-4c40-a143-3030819609ea",
   "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.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}