01_mdv_search.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Hybrid search for the metadata catalogue of the Canton of Zurich\n",
    "\n",
    "In this notebook we prepare the data for our search application and setup the search index.\n",
    "\n",
    "- We load the data from the API.\n",
    "- We prepare the data and lemmatize the text data (for lexical search).\n",
    "- We embed via the OpenAI embedding API.\n",
    "- We test the embeddings with cosine similarity.\n",
    "- We setup the Weaviate index.\n",
    "- We create a collection with our data.\n",
    "- We test the index in regard to lexical, and vector search as well as the combination of both - hybrid search. The latter is what we use in the app.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Imports\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO: Pandarallel will run on 2 workers.\n",
      "INFO: Pandarallel will use Memory file system to transfer data between the main process and workers.\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from pandarallel import pandarallel\n",
    "import numpy as np\n",
    "\n",
    "pd.options.mode.chained_assignment = None\n",
    "pd.options.display.max_rows = 500\n",
    "pd.options.display.max_seq_items = 500\n",
    "pandarallel.initialize(progress_bar=False)\n",
    "\n",
    "from time import time\n",
    "import time\n",
    "import os\n",
    "import re\n",
    "import requests\n",
    "from dotenv import load_dotenv\n",
    "\n",
    "import pyarrow.parquet as pq\n",
    "\n",
    "import warnings\n",
    "from bs4 import MarkupResemblesLocatorWarning\n",
    "from sklearn.metrics.pairwise import cosine_similarity\n",
    "import weaviate\n",
    "from weaviate.classes.config import Property, DataType\n",
    "import weaviate.classes as wvc\n",
    "import weaviate.classes.config as wc\n",
    "\n",
    "import spacy\n",
    "from openai import OpenAI\n",
    "import json\n",
    "\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "warnings.filterwarnings(\"ignore\", category=MarkupResemblesLocatorWarning)\n",
    "\n",
    "load_dotenv()\n",
    "OPENAI_API_KEY = os.getenv(\"OPENAI_API_KEY\")\n",
    "client_openai = OpenAI(api_key=OPENAI_API_KEY)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Constants**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "DATA_FOLDER = \"_data/\"\n",
    "DATASETS = DATA_FOLDER + \"01_data.parq\"\n",
    "DATA_WITH_EMBEDDINGS = DATA_FOLDER + \"02_data_embedded.parq\"\n",
    "\n",
    "BASELINK_API = \"https://daten.berlin.de/datensaetze/\"\n",
    "\n",
    "# Dataset links are composed of this baselink and the identifier for each dataset.\n",
    "BASELINK_DATASHOP = (\n",
    "    \"https://datenregister.berlin.de/api/3/action/current_package_list_with_resources\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 packages retrieved.\n",
      "500 packages retrieved.\n",
      "1000 packages retrieved.\n",
      "1500 packages retrieved.\n",
      "2000 packages retrieved.\n",
      "2500 packages retrieved.\n",
      "3000 packages retrieved.\n",
      "3500 packages retrieved.\n"
     ]
    }
   ],
   "source": [
    "# FUNCTIONS ------------------------------------------------------------------ #\n",
    "\n",
    "\n",
    "# Retrieving data from the Berlin data register API, processing it, and saving it to CSV files\n",
    "def get_full_package_list(limit=500, sleep=2):\n",
    "    \"\"\"Get full package list from CKAN API\"\"\"\n",
    "    offset = 0\n",
    "    frames = []\n",
    "    while True:\n",
    "        print(f\"{offset} packages retrieved.\")\n",
    "        url = BASELINK_DATASHOP + f\"?limit={limit}&offset={offset}\"\n",
    "        res = requests.get(url)\n",
    "        data = json.loads(res.content)\n",
    "        if data[\"result\"] == []:\n",
    "            break\n",
    "        data = pd.DataFrame(pd.json_normalize(data[\"result\"]))\n",
    "        frames.append(data)\n",
    "        offset += limit\n",
    "        time.sleep(sleep)\n",
    "    data = pd.concat(frames)\n",
    "    data.reset_index(drop=True, inplace=True)\n",
    "    return data\n",
    "\n",
    "\n",
    "all_packages = get_full_package_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "raw = all_packages.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert the DataFrame to a list of dictionaries\n",
    "raw = (all_packages.copy()).to_dict(orient=\"records\")\n",
    "\n",
    "# Convert the DataFrame to a list of strings\n",
    "raw = (\n",
    "    (all_packages.copy()).apply(lambda row: \" \".join(row.astype(str)), axis=1).tolist()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Senatsverwaltung für Arbeit, Soziales, Gleichstellung, Intergartion, Vielfalt und Antidiskriminierung bildungszeit@senasgiva.berlin.de simplesearch datensatz 50e8a594-1945-487e-b77e-3baa14f6a3c7 2015-10-19 2024-02-14 Berlin Berlin a6828125-ae95-404f-9fb0-52eb91302fd8 True cc-zero Creative Commons Zero https://opendefinition.org/licenses/cc-zero/ n.n. bildungszeit@senasgiva.berlin.de 2023-11-01T14:01:17.996380 2024-08-17T00:28:35.151018 simple_search_wwwberlindesenarbeitweiterbildungbildungszeitsuche In Berlin anerkannte Weiterbildungsveranstaltungen. 5 4 e2f57191-ec25-4249-ab08-98366b339ef5 False active 2024-01-01 2024-01-31 Keine Suche nach anerkannten Veranstaltungen dataset https://www.berlin.de/sen/arbeit/weiterbildung/bildungszeit/suche/ None [{'description': '', 'display_name': 'Arbeitsmarkt', 'id': 'e727a5a0-adae-465f-8c5b-1d84b681cc0d', 'image_display_url': '', 'name': 'arbeit', 'title': 'Arbeitsmarkt'}] [{'cache_last_updated': None, 'cache_url': None, 'created': '2024-08-17T00:28:35.201062', 'description': 'Webseite von \"Suche nach anerkannten Veranstaltungen\". Hier können Sie bereits Anfragen abschicken und Daten ansehen.\\nlang: de\\ndoc: https://support-2023.berlin.de/wiki/index.php/SimpleSearch_API', 'format': 'HTML', 'hash': '', 'id': '5af0e6b1-99c4-4bbc-b50a-44fc15688ee3', 'language': 'de', 'last_modified': None, 'metadata_modified': '2024-08-17T00:28:35.166548', 'mimetype': None, 'mimetype_inner': None, 'name': None, 'package_id': 'a6828125-ae95-404f-9fb0-52eb91302fd8', 'position': 0, 'resource_type': None, 'size': None, 'state': 'active', 'url': 'https://www.berlin.de/sen/arbeit/weiterbildung/bildungszeit/suche/index.php', 'url_type': None}, {'cache_last_updated': None, 'cache_url': None, 'created': '2024-08-17T00:28:35.201068', 'description': 'REST-API mit JSON-Ausgabe für \"Suche nach anerkannten Veranstaltungen\". Es existieren weitere GET-Parameter für spezielle Anfragen.\\nlang: de\\ndoc: https://support-2023.berlin.de/wiki/index.php/SimpleSearch_JSON', 'format': 'JSON', 'hash': '', 'id': '7d38bee1-2b8e-4a90-a005-cc6f7d4814fd', 'language': 'de', 'last_modified': None, 'metadata_modified': '2024-08-17T00:28:35.166787', 'mimetype': None, 'mimetype_inner': None, 'name': None, 'package_id': 'a6828125-ae95-404f-9fb0-52eb91302fd8', 'position': 1, 'resource_type': None, 'size': None, 'state': 'active', 'url': 'https://www.berlin.de/sen/arbeit/weiterbildung/bildungszeit/suche/index.php/index/all.json?q=', 'url_type': None}, {'cache_last_updated': None, 'cache_url': None, 'created': '2024-08-17T00:28:35.201087', 'description': 'REST-API mit XML-Ausgabe für \"Suche nach anerkannten Veranstaltungen\". Es existieren weitere GET-Parameter für spezielle Anfragen.\\nlang: de\\ndoc: https://support-2023.berlin.de/wiki/index.php/SimpleSearch_XML', 'format': 'XML', 'hash': '', 'id': '28a7d992-6ae4-45c7-9c8a-6022c9cde829', 'language': 'de', 'last_modified': None, 'metadata_modified': '2024-08-17T00:28:35.166939', 'mimetype': None, 'mimetype_inner': None, 'name': None, 'package_id': 'a6828125-ae95-404f-9fb0-52eb91302fd8', 'position': 2, 'resource_type': None, 'size': None, 'state': 'active', 'url': 'https://www.berlin.de/sen/arbeit/weiterbildung/bildungszeit/suche/index.php/index/all.xml?q=', 'url_type': None}, {'cache_last_updated': None, 'cache_url': None, 'created': '2024-08-17T00:28:35.201088', 'description': 'REST-API mit Excel(XLS)-Ausgabe für \"Suche nach anerkannten Veranstaltungen\". Es existieren weitere GET-Parameter für spezielle Anfragen.\\nlang: de\\ndoc: https://support-2023.berlin.de/wiki/index.php/SimpleSearch_API', 'format': 'XLS', 'hash': '', 'id': '70f83b44-32f0-4a6c-90a9-aa9a3acb69a3', 'language': 'de', 'last_modified': None, 'metadata_modified': '2024-08-17T00:28:35.167084', 'mimetype': None, 'mimetype_inner': None, 'name': None, 'package_id': 'a6828125-ae95-404f-9fb0-52eb91302fd8', 'position': 3, 'resource_type': None, 'size': None, 'state': 'active', 'url': 'https://www.berlin.de/sen/arbeit/weiterbildung/bildungszeit/suche/index.php/index/all.xls?q=', 'url_type': None}, {'cache_last_updated': None, 'cache_url': None, 'created': '2024-08-17T00:28:35.201090', 'description': 'REST-API mit CSV-Ausgabe für \"Suche nach anerkannten Veranstaltungen\". Es existieren weitere GET-Parameter für spezielle Anfragen.\\nlang: de\\ndoc: https://support-2023.berlin.de/wiki/index.php/SimpleSearch_API', 'format': 'CSV', 'hash': '', 'id': '53aff9b3-f410-44a3-8580-7e5b07ebed75', 'language': 'de', 'last_modified': None, 'metadata_modified': '2024-08-17T00:28:35.167284', 'mimetype': None, 'mimetype_inner': None, 'name': None, 'package_id': 'a6828125-ae95-404f-9fb0-52eb91302fd8', 'position': 4, 'resource_type': None, 'size': None, 'state': 'active', 'url': 'https://www.berlin.de/sen/arbeit/weiterbildung/bildungszeit/suche/index.php/index/all.csv?q=', 'url_type': None}] [{'display_name': 'bildungszeit', 'id': 'ca145253-6fa9-49ab-8bda-0214da05ac48', 'name': 'bildungszeit', 'state': 'active', 'vocabulary_id': None}, {'display_name': 'simplesearch', 'id': 'e828768a-5d10-4426-b6a9-d4ef3cd14101', 'name': 'simplesearch', 'state': 'active', 'vocabulary_id': None}, {'display_name': 'weiterbildung', 'id': '4415c04f-9eff-4873-ac39-a8d493b342a6', 'name': 'weiterbildung', 'state': 'active', 'vocabulary_id': None}, {'display_name': 'weiterbildungsveranstaltung', 'id': 'a9e3ae48-9bb9-4489-83da-3c08baf5ca2b', 'name': 'weiterbildungsveranstaltung', 'state': 'active', 'vocabulary_id': None}] [] [] e2f57191-ec25-4249-ab08-98366b339ef5 simplesearch SimpleSearch organization   2016-01-29T17:11:35.360140 True approved active nan nan nan nan nan nan\n",
      "<class 'str'>\n"
     ]
    }
   ],
   "source": [
    "# Print the result\n",
    "print(raw[0])\n",
    "\n",
    "print(type(raw[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "# def clean_text(d):\n",
    "#     \"\"\"Clean text. Remove square brackets that often enclose units or other information. Replace multiple spaces with single spaces and strip leading and trailing spaces.\n",
    "\n",
    "#     Args:\n",
    "#         d (str): Text to clean.\n",
    "\n",
    "#     Returns:\n",
    "#         str: Cleaned text.\n",
    "#     \"\"\"\n",
    "#     # Remove square brackets.\n",
    "#     d = re.sub(r\"(\\[|\\])\", \" \", d)\n",
    "#     # Replace multiple spaces with single space.\n",
    "#     d = re.sub(r\"[\\s]+\", \" \", d)\n",
    "#     d = d.strip()\n",
    "#     return d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(all_packages)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['author', 'author_email', 'berlin_source', 'berlin_type',\n",
       "       'creator_user_id', 'date_released', 'date_updated',\n",
       "       'geographical_coverage', 'geographical_granularity', 'id', 'isopen',\n",
       "       'license_id', 'license_title', 'license_url', 'maintainer',\n",
       "       'maintainer_email', 'metadata_created', 'metadata_modified', 'name',\n",
       "       'notes', 'num_resources', 'num_tags', 'owner_org', 'private', 'state',\n",
       "       'temporal_coverage_from', 'temporal_coverage_to',\n",
       "       'temporal_granularity', 'title', 'type', 'url', 'version', 'groups',\n",
       "       'resources', 'tags', 'relationships_as_subject',\n",
       "       'relationships_as_object', 'organization.id', 'organization.name',\n",
       "       'organization.title', 'organization.type', 'organization.description',\n",
       "       'organization.image_url', 'organization.created',\n",
       "       'organization.is_organization', 'organization.approval_status',\n",
       "       'organization.state', 'attribution_text', 'hvd_category',\n",
       "       'preview_image', 'sample_record', 'username', 'extras'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_packages.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>author</th>\n",
       "      <th>author_email</th>\n",
       "      <th>berlin_source</th>\n",
       "      <th>berlin_type</th>\n",
       "      <th>creator_user_id</th>\n",
       "      <th>date_released</th>\n",
       "      <th>date_updated</th>\n",
       "      <th>geographical_coverage</th>\n",
       "      <th>geographical_granularity</th>\n",
       "      <th>id</th>\n",
       "      <th>...</th>\n",
       "      <th>organization.created</th>\n",
       "      <th>organization.is_organization</th>\n",
       "      <th>organization.approval_status</th>\n",
       "      <th>organization.state</th>\n",
       "      <th>attribution_text</th>\n",
       "      <th>hvd_category</th>\n",
       "      <th>preview_image</th>\n",
       "      <th>sample_record</th>\n",
       "      <th>username</th>\n",
       "      <th>extras</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Senatsverwaltung für Arbeit, Soziales, Gleichs...</td>\n",
       "      <td>bildungszeit@senasgiva.berlin.de</td>\n",
       "      <td>simplesearch</td>\n",
       "      <td>datensatz</td>\n",
       "      <td>50e8a594-1945-487e-b77e-3baa14f6a3c7</td>\n",
       "      <td>2015-10-19</td>\n",
       "      <td>2024-02-14</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>a6828125-ae95-404f-9fb0-52eb91302fd8</td>\n",
       "      <td>...</td>\n",
       "      <td>2016-01-29T17:11:35.360140</td>\n",
       "      <td>True</td>\n",
       "      <td>approved</td>\n",
       "      <td>active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>VBB - Verkehrsverbund Berlin-Brandenburg GmbH</td>\n",
       "      <td>None</td>\n",
       "      <td>webform</td>\n",
       "      <td>datensatz</td>\n",
       "      <td>b136bb34-d39b-4be2-8dd7-8779c2d92fe6</td>\n",
       "      <td>2013-07-01</td>\n",
       "      <td>2024-08-16</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>GPS-Koordinaten</td>\n",
       "      <td>96643363-971f-43f9-b93e-e721c5b84872</td>\n",
       "      <td>...</td>\n",
       "      <td>2016-01-29T17:11:40.469439</td>\n",
       "      <td>True</td>\n",
       "      <td>approved</td>\n",
       "      <td>active</td>\n",
       "      <td>VBB Verkehrsverbund Berlin-Brandenburg GmbH</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LAGeSo</td>\n",
       "      <td>webmaster@lageso.berlin.de</td>\n",
       "      <td>simplesearch</td>\n",
       "      <td>datensatz</td>\n",
       "      <td>b1e7b5de-7dab-497a-ba76-867bf93f22b3</td>\n",
       "      <td>2015-07-22</td>\n",
       "      <td>2024-07-26</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>4bd75f18-20bf-45e9-af17-3bf03ab6dc58</td>\n",
       "      <td>...</td>\n",
       "      <td>2016-01-29T17:11:35.360140</td>\n",
       "      <td>True</td>\n",
       "      <td>approved</td>\n",
       "      <td>active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Servicezentrum der Berliner Volkshochschulen</td>\n",
       "      <td>None</td>\n",
       "      <td>webform</td>\n",
       "      <td>datensatz</td>\n",
       "      <td>49414a9f-43ba-4e0e-b6ec-f0cfb7d791f4</td>\n",
       "      <td>2019-10-14</td>\n",
       "      <td>2024-08-14</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>f90b3f13-b3bf-4420-88d0-c6397efc62b9</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-11-14T13:58:47.621467</td>\n",
       "      <td>True</td>\n",
       "      <td>approved</td>\n",
       "      <td>active</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Senatsverwaltung für Wirtschaft, Energie und B...</td>\n",
       "      <td>Abdulkadir.Dagci@senweb.berlin.de</td>\n",
       "      <td>simplesearch</td>\n",
       "      <td>datensatz</td>\n",
       "      <td>b1e7b5de-7dab-497a-ba76-867bf93f22b3</td>\n",
       "      <td>2014-04-15</td>\n",
       "      <td>2024-08-14</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>6a2acf9e-e911-4d78-b571-c75091d07086</td>\n",
       "      <td>...</td>\n",
       "      <td>2016-01-29T17:11:35.360140</td>\n",
       "      <td>True</td>\n",
       "      <td>approved</td>\n",
       "      <td>active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 53 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              author  \\\n",
       "0  Senatsverwaltung für Arbeit, Soziales, Gleichs...   \n",
       "1      VBB - Verkehrsverbund Berlin-Brandenburg GmbH   \n",
       "2                                             LAGeSo   \n",
       "3       Servicezentrum der Berliner Volkshochschulen   \n",
       "4  Senatsverwaltung für Wirtschaft, Energie und B...   \n",
       "\n",
       "                        author_email berlin_source berlin_type  \\\n",
       "0   bildungszeit@senasgiva.berlin.de  simplesearch   datensatz   \n",
       "1                               None       webform   datensatz   \n",
       "2         webmaster@lageso.berlin.de  simplesearch   datensatz   \n",
       "3                               None       webform   datensatz   \n",
       "4  Abdulkadir.Dagci@senweb.berlin.de  simplesearch   datensatz   \n",
       "\n",
       "                        creator_user_id date_released date_updated  \\\n",
       "0  50e8a594-1945-487e-b77e-3baa14f6a3c7    2015-10-19   2024-02-14   \n",
       "1  b136bb34-d39b-4be2-8dd7-8779c2d92fe6    2013-07-01   2024-08-16   \n",
       "2  b1e7b5de-7dab-497a-ba76-867bf93f22b3    2015-07-22   2024-07-26   \n",
       "3  49414a9f-43ba-4e0e-b6ec-f0cfb7d791f4    2019-10-14   2024-08-14   \n",
       "4  b1e7b5de-7dab-497a-ba76-867bf93f22b3    2014-04-15   2024-08-14   \n",
       "\n",
       "  geographical_coverage geographical_granularity  \\\n",
       "0                Berlin                   Berlin   \n",
       "1                Berlin          GPS-Koordinaten   \n",
       "2                Berlin                   Berlin   \n",
       "3                Berlin                   Berlin   \n",
       "4                Berlin                   Berlin   \n",
       "\n",
       "                                     id  ...        organization.created  \\\n",
       "0  a6828125-ae95-404f-9fb0-52eb91302fd8  ...  2016-01-29T17:11:35.360140   \n",
       "1  96643363-971f-43f9-b93e-e721c5b84872  ...  2016-01-29T17:11:40.469439   \n",
       "2  4bd75f18-20bf-45e9-af17-3bf03ab6dc58  ...  2016-01-29T17:11:35.360140   \n",
       "3  f90b3f13-b3bf-4420-88d0-c6397efc62b9  ...  2018-11-14T13:58:47.621467   \n",
       "4  6a2acf9e-e911-4d78-b571-c75091d07086  ...  2016-01-29T17:11:35.360140   \n",
       "\n",
       "  organization.is_organization organization.approval_status  \\\n",
       "0                         True                     approved   \n",
       "1                         True                     approved   \n",
       "2                         True                     approved   \n",
       "3                         True                     approved   \n",
       "4                         True                     approved   \n",
       "\n",
       "  organization.state                             attribution_text  \\\n",
       "0             active                                          NaN   \n",
       "1             active  VBB Verkehrsverbund Berlin-Brandenburg GmbH   \n",
       "2             active                                          NaN   \n",
       "3             active                                                \n",
       "4             active                                          NaN   \n",
       "\n",
       "  hvd_category preview_image sample_record username extras  \n",
       "0          NaN           NaN           NaN      NaN    NaN  \n",
       "1                                               NaN    NaN  \n",
       "2          NaN           NaN           NaN      NaN    NaN  \n",
       "3                                               NaN    NaN  \n",
       "4          NaN           NaN           NaN      NaN    NaN  \n",
       "\n",
       "[5 rows x 53 columns]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_packages.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load and parse data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "def parse_and_prepare_metadata(df):\n",
    "    \"\"\"Parse and prepare metadata for all available datasets.\n",
    "\n",
    "    Args:\n",
    "        df (pd.DataFrame): Raw metadata for all available datasets.\n",
    "\n",
    "    Returns:\n",
    "        pd.DataFrame: Parsed and prepared metadata for all datasets.\n",
    "    \"\"\"\n",
    "\n",
    "    # This function processes the 'resources' column to extract distribution info and check for PDFs\n",
    "    def process_distribution(resources):\n",
    "        tmp_res = []\n",
    "        is_pdf = False\n",
    "        if isinstance(resources, list):\n",
    "            for resource in resources:\n",
    "                if isinstance(resource, dict):\n",
    "                    if resource.get(\"format\") == \"PDF\":\n",
    "                        is_pdf = True\n",
    "                    # Convert to string and use empty string if None\n",
    "                    name = str(resource.get(\"name\", \"\")) or \"\"\n",
    "                    description = str(resource.get(\"description\", \"\")) or \"\"\n",
    "                    tmp_res.append(name)\n",
    "                    tmp_res.append(description)\n",
    "        # Filter out empty strings before joining\n",
    "        return \" \".join(filter(bool, tmp_res)), is_pdf\n",
    "\n",
    "    # Create a new DataFrame with the required columns\n",
    "    new_df = pd.DataFrame()\n",
    "\n",
    "    # CHANGES AND EXPLANATIONS:\n",
    "\n",
    "    # 1. Using 'id' instead of 'identifier'\n",
    "    # The original function expected 'identifier', but the DataFrame has 'id'\n",
    "    new_df[\"identifier\"] = df[\"id\"]\n",
    "\n",
    "    # 2. Constructing the link using 'id'\n",
    "    # This remains the same, but now uses 'id' instead of 'identifier'\n",
    "    new_df[\"link\"] = BASELINK_DATASHOP + df[\"id\"]\n",
    "\n",
    "    # 3. Title remains the same, just stripped of whitespace\n",
    "    new_df[\"title\"] = df[\"title\"].str.strip()\n",
    "\n",
    "    # 4. Using 'notes' for description instead of 'description'\n",
    "    # The DataFrame has 'notes' which likely contains the description\n",
    "    new_df[\"description\"] = (\n",
    "        df[\"notes\"]\n",
    "        .fillna(\"\")\n",
    "        .apply(lambda x: x.strip() if x not in [\"NA\", \"keine\", \"null\"] else \"\")\n",
    "    )\n",
    "\n",
    "    # 5. Processing 'tags' for keywords\n",
    "    # The original function expected 'keyword', but the DataFrame has 'tags'\n",
    "    # Assuming 'tags' is a list of dictionaries with a 'name' key\n",
    "    new_df[\"keyword\"] = df[\"tags\"].apply(\n",
    "        lambda x: (\n",
    "            \" \".join([str(tag.get(\"name\", \"\")) for tag in x])\n",
    "            if isinstance(x, list)\n",
    "            else \"\"\n",
    "        )\n",
    "    )\n",
    "\n",
    "    # 6. Processing 'resources' for distribution and is_study\n",
    "    # The original function expected a 'distribution' column, which doesn't exist in the DataFrame\n",
    "    # Instead, we process the 'resources' column to extract this information\n",
    "    distribution_and_study = df[\"resources\"].apply(process_distribution)\n",
    "    new_df[\"distribution\"] = distribution_and_study.apply(lambda x: x[0])\n",
    "    new_df[\"is_study\"] = distribution_and_study.apply(lambda x: x[1])\n",
    "\n",
    "    return new_df\n",
    "\n",
    "\n",
    "# OVERALL CHANGES:\n",
    "# - The function now takes a DataFrame as input instead of a list of dictionaries\n",
    "# - It maps the existing columns in the DataFrame to the required output format\n",
    "# - It handles missing or differently named columns (like 'notes' for description)\n",
    "# - It processes complex columns like 'tags' and 'resources' to extract required information\n",
    "# - The former 'theme' column is is deleted as there's no corresponding data in the input DataFrame\n",
    "# - In the `process_distribution` function:\n",
    "#   * We now convert 'name' and 'description' to strings and use an empty string if they are None\n",
    "#   * We use `filter(bool, tmp_res)` before joining to remove any empty strings\n",
    "# - In the 'keyword' processing:\n",
    "#   * We've added a `str()` conversion and `.get()` method to handle potential None values in the tag names\n",
    "\n",
    "# These changes are necessary because the structure of the input data (your DataFrame)\n",
    "# doesn't exactly match what the original function expected. This new version bridges\n",
    "# that gap, allowing you to process your DataFrame and get the output in the format\n",
    "# you originally designed, despite the differences in input structure.\n",
    "# The additional error handling for None values and empty strings ensures that the function\n",
    "# can process data with missing or null values without raising exceptions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>author</th>\n",
       "      <th>author_email</th>\n",
       "      <th>berlin_source</th>\n",
       "      <th>berlin_type</th>\n",
       "      <th>creator_user_id</th>\n",
       "      <th>date_released</th>\n",
       "      <th>date_updated</th>\n",
       "      <th>geographical_coverage</th>\n",
       "      <th>geographical_granularity</th>\n",
       "      <th>id</th>\n",
       "      <th>...</th>\n",
       "      <th>organization.created</th>\n",
       "      <th>organization.is_organization</th>\n",
       "      <th>organization.approval_status</th>\n",
       "      <th>organization.state</th>\n",
       "      <th>attribution_text</th>\n",
       "      <th>hvd_category</th>\n",
       "      <th>preview_image</th>\n",
       "      <th>sample_record</th>\n",
       "      <th>username</th>\n",
       "      <th>extras</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Senatsverwaltung für Arbeit, Soziales, Gleichs...</td>\n",
       "      <td>bildungszeit@senasgiva.berlin.de</td>\n",
       "      <td>simplesearch</td>\n",
       "      <td>datensatz</td>\n",
       "      <td>50e8a594-1945-487e-b77e-3baa14f6a3c7</td>\n",
       "      <td>2015-10-19</td>\n",
       "      <td>2024-02-14</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>a6828125-ae95-404f-9fb0-52eb91302fd8</td>\n",
       "      <td>...</td>\n",
       "      <td>2016-01-29T17:11:35.360140</td>\n",
       "      <td>True</td>\n",
       "      <td>approved</td>\n",
       "      <td>active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>VBB - Verkehrsverbund Berlin-Brandenburg GmbH</td>\n",
       "      <td>None</td>\n",
       "      <td>webform</td>\n",
       "      <td>datensatz</td>\n",
       "      <td>b136bb34-d39b-4be2-8dd7-8779c2d92fe6</td>\n",
       "      <td>2013-07-01</td>\n",
       "      <td>2024-08-16</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>GPS-Koordinaten</td>\n",
       "      <td>96643363-971f-43f9-b93e-e721c5b84872</td>\n",
       "      <td>...</td>\n",
       "      <td>2016-01-29T17:11:40.469439</td>\n",
       "      <td>True</td>\n",
       "      <td>approved</td>\n",
       "      <td>active</td>\n",
       "      <td>VBB Verkehrsverbund Berlin-Brandenburg GmbH</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LAGeSo</td>\n",
       "      <td>webmaster@lageso.berlin.de</td>\n",
       "      <td>simplesearch</td>\n",
       "      <td>datensatz</td>\n",
       "      <td>b1e7b5de-7dab-497a-ba76-867bf93f22b3</td>\n",
       "      <td>2015-07-22</td>\n",
       "      <td>2024-07-26</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>4bd75f18-20bf-45e9-af17-3bf03ab6dc58</td>\n",
       "      <td>...</td>\n",
       "      <td>2016-01-29T17:11:35.360140</td>\n",
       "      <td>True</td>\n",
       "      <td>approved</td>\n",
       "      <td>active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Servicezentrum der Berliner Volkshochschulen</td>\n",
       "      <td>None</td>\n",
       "      <td>webform</td>\n",
       "      <td>datensatz</td>\n",
       "      <td>49414a9f-43ba-4e0e-b6ec-f0cfb7d791f4</td>\n",
       "      <td>2019-10-14</td>\n",
       "      <td>2024-08-14</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>f90b3f13-b3bf-4420-88d0-c6397efc62b9</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-11-14T13:58:47.621467</td>\n",
       "      <td>True</td>\n",
       "      <td>approved</td>\n",
       "      <td>active</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Senatsverwaltung für Wirtschaft, Energie und B...</td>\n",
       "      <td>Abdulkadir.Dagci@senweb.berlin.de</td>\n",
       "      <td>simplesearch</td>\n",
       "      <td>datensatz</td>\n",
       "      <td>b1e7b5de-7dab-497a-ba76-867bf93f22b3</td>\n",
       "      <td>2014-04-15</td>\n",
       "      <td>2024-08-14</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>6a2acf9e-e911-4d78-b571-c75091d07086</td>\n",
       "      <td>...</td>\n",
       "      <td>2016-01-29T17:11:35.360140</td>\n",
       "      <td>True</td>\n",
       "      <td>approved</td>\n",
       "      <td>active</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 53 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              author  \\\n",
       "0  Senatsverwaltung für Arbeit, Soziales, Gleichs...   \n",
       "1      VBB - Verkehrsverbund Berlin-Brandenburg GmbH   \n",
       "2                                             LAGeSo   \n",
       "3       Servicezentrum der Berliner Volkshochschulen   \n",
       "4  Senatsverwaltung für Wirtschaft, Energie und B...   \n",
       "\n",
       "                        author_email berlin_source berlin_type  \\\n",
       "0   bildungszeit@senasgiva.berlin.de  simplesearch   datensatz   \n",
       "1                               None       webform   datensatz   \n",
       "2         webmaster@lageso.berlin.de  simplesearch   datensatz   \n",
       "3                               None       webform   datensatz   \n",
       "4  Abdulkadir.Dagci@senweb.berlin.de  simplesearch   datensatz   \n",
       "\n",
       "                        creator_user_id date_released date_updated  \\\n",
       "0  50e8a594-1945-487e-b77e-3baa14f6a3c7    2015-10-19   2024-02-14   \n",
       "1  b136bb34-d39b-4be2-8dd7-8779c2d92fe6    2013-07-01   2024-08-16   \n",
       "2  b1e7b5de-7dab-497a-ba76-867bf93f22b3    2015-07-22   2024-07-26   \n",
       "3  49414a9f-43ba-4e0e-b6ec-f0cfb7d791f4    2019-10-14   2024-08-14   \n",
       "4  b1e7b5de-7dab-497a-ba76-867bf93f22b3    2014-04-15   2024-08-14   \n",
       "\n",
       "  geographical_coverage geographical_granularity  \\\n",
       "0                Berlin                   Berlin   \n",
       "1                Berlin          GPS-Koordinaten   \n",
       "2                Berlin                   Berlin   \n",
       "3                Berlin                   Berlin   \n",
       "4                Berlin                   Berlin   \n",
       "\n",
       "                                     id  ...        organization.created  \\\n",
       "0  a6828125-ae95-404f-9fb0-52eb91302fd8  ...  2016-01-29T17:11:35.360140   \n",
       "1  96643363-971f-43f9-b93e-e721c5b84872  ...  2016-01-29T17:11:40.469439   \n",
       "2  4bd75f18-20bf-45e9-af17-3bf03ab6dc58  ...  2016-01-29T17:11:35.360140   \n",
       "3  f90b3f13-b3bf-4420-88d0-c6397efc62b9  ...  2018-11-14T13:58:47.621467   \n",
       "4  6a2acf9e-e911-4d78-b571-c75091d07086  ...  2016-01-29T17:11:35.360140   \n",
       "\n",
       "  organization.is_organization organization.approval_status  \\\n",
       "0                         True                     approved   \n",
       "1                         True                     approved   \n",
       "2                         True                     approved   \n",
       "3                         True                     approved   \n",
       "4                         True                     approved   \n",
       "\n",
       "  organization.state                             attribution_text  \\\n",
       "0             active                                          NaN   \n",
       "1             active  VBB Verkehrsverbund Berlin-Brandenburg GmbH   \n",
       "2             active                                          NaN   \n",
       "3             active                                                \n",
       "4             active                                          NaN   \n",
       "\n",
       "  hvd_category preview_image sample_record username extras  \n",
       "0          NaN           NaN           NaN      NaN    NaN  \n",
       "1                                               NaN    NaN  \n",
       "2          NaN           NaN           NaN      NaN    NaN  \n",
       "3                                               NaN    NaN  \n",
       "4          NaN           NaN           NaN      NaN    NaN  \n",
       "\n",
       "[5 rows x 53 columns]"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_packages.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = all_packages.loc[\n",
    "    ~all_packages.apply(lambda x: x.astype(str).str.isspace().all(), axis=1)\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_parsed = parse_and_prepare_metadata(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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>identifier</th>\n",
       "      <th>link</th>\n",
       "      <th>title</th>\n",
       "      <th>description</th>\n",
       "      <th>keyword</th>\n",
       "      <th>distribution</th>\n",
       "      <th>is_study</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a6828125-ae95-404f-9fb0-52eb91302fd8</td>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "      <td>Suche nach anerkannten Veranstaltungen</td>\n",
       "      <td>In Berlin anerkannte Weiterbildungsveranstaltu...</td>\n",
       "      <td>bildungszeit simplesearch weiterbildung weiter...</td>\n",
       "      <td>None Webseite von \"Suche nach anerkannten Vera...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>96643363-971f-43f9-b93e-e721c5b84872</td>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "      <td>VBB-Fahrplandaten via GTFS</td>\n",
       "      <td>Der Verkehrsverbund Berlin-Brandenburg (VBB) s...</td>\n",
       "      <td>Bahn Bus Fahrplan Fähre S-Bahn Straßenbahn Tra...</td>\n",
       "      <td>VBB-Fahrplandaten</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4bd75f18-20bf-45e9-af17-3bf03ab6dc58</td>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "      <td>Liste der Badestellen</td>\n",
       "      <td>Die auf der Badestellenkarte für Berlin ausgew...</td>\n",
       "      <td>badegewässer baden badeseen simplesearch</td>\n",
       "      <td>None Webseite von \"Liste der Badestellen\". Hie...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f90b3f13-b3bf-4420-88d0-c6397efc62b9</td>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "      <td>Kurse der Berliner Volkshochschulen</td>\n",
       "      <td>Hier kann man alle aktuellen Kursangebote der ...</td>\n",
       "      <td>Dienstleistungen im Bereich Bildung Volkshochs...</td>\n",
       "      <td>Aktuelle Berliner Volkshochschul Kurse Dieser ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6a2acf9e-e911-4d78-b571-c75091d07086</td>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "      <td>Berliner und Brandenburger Straßen- und Volksf...</td>\n",
       "      <td>Berliner Straßen- und Volksfeste</td>\n",
       "      <td>simplesearch strassenfeste straßenfeste volksf...</td>\n",
       "      <td>None Webseite von \"Berliner und Brandenburger ...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             identifier  \\\n",
       "0  a6828125-ae95-404f-9fb0-52eb91302fd8   \n",
       "1  96643363-971f-43f9-b93e-e721c5b84872   \n",
       "2  4bd75f18-20bf-45e9-af17-3bf03ab6dc58   \n",
       "3  f90b3f13-b3bf-4420-88d0-c6397efc62b9   \n",
       "4  6a2acf9e-e911-4d78-b571-c75091d07086   \n",
       "\n",
       "                                                link  \\\n",
       "0  https://datenregister.berlin.de/api/3/action/c...   \n",
       "1  https://datenregister.berlin.de/api/3/action/c...   \n",
       "2  https://datenregister.berlin.de/api/3/action/c...   \n",
       "3  https://datenregister.berlin.de/api/3/action/c...   \n",
       "4  https://datenregister.berlin.de/api/3/action/c...   \n",
       "\n",
       "                                               title  \\\n",
       "0             Suche nach anerkannten Veranstaltungen   \n",
       "1                         VBB-Fahrplandaten via GTFS   \n",
       "2                              Liste der Badestellen   \n",
       "3                Kurse der Berliner Volkshochschulen   \n",
       "4  Berliner und Brandenburger Straßen- und Volksf...   \n",
       "\n",
       "                                         description  \\\n",
       "0  In Berlin anerkannte Weiterbildungsveranstaltu...   \n",
       "1  Der Verkehrsverbund Berlin-Brandenburg (VBB) s...   \n",
       "2  Die auf der Badestellenkarte für Berlin ausgew...   \n",
       "3  Hier kann man alle aktuellen Kursangebote der ...   \n",
       "4                   Berliner Straßen- und Volksfeste   \n",
       "\n",
       "                                             keyword  \\\n",
       "0  bildungszeit simplesearch weiterbildung weiter...   \n",
       "1  Bahn Bus Fahrplan Fähre S-Bahn Straßenbahn Tra...   \n",
       "2           badegewässer baden badeseen simplesearch   \n",
       "3  Dienstleistungen im Bereich Bildung Volkshochs...   \n",
       "4  simplesearch strassenfeste straßenfeste volksf...   \n",
       "\n",
       "                                        distribution  is_study  \n",
       "0  None Webseite von \"Suche nach anerkannten Vera...     False  \n",
       "1                                  VBB-Fahrplandaten     False  \n",
       "2  None Webseite von \"Liste der Badestellen\". Hie...     False  \n",
       "3  Aktuelle Berliner Volkshochschul Kurse Dieser ...     False  \n",
       "4  None Webseite von \"Berliner und Brandenburger ...     False  "
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_parsed.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df_parsed.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 3311 entries, 0 to 3310\n",
      "Data columns (total 8 columns):\n",
      " #   Column              Non-Null Count  Dtype \n",
      "---  ------              --------------  ----- \n",
      " 0   identifier          3311 non-null   object\n",
      " 1   link                3311 non-null   object\n",
      " 2   title               3311 non-null   object\n",
      " 3   description         3311 non-null   object\n",
      " 4   keyword             3311 non-null   object\n",
      " 5   distribution        3311 non-null   object\n",
      " 6   is_study            3311 non-null   bool  \n",
      " 7   text_for_embedding  3311 non-null   object\n",
      "dtypes: bool(1), object(7)\n",
      "memory usage: 9.0 MB\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>identifier</th>\n",
       "      <th>link</th>\n",
       "      <th>title</th>\n",
       "      <th>description</th>\n",
       "      <th>keyword</th>\n",
       "      <th>distribution</th>\n",
       "      <th>is_study</th>\n",
       "      <th>text_for_embedding</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a6828125-ae95-404f-9fb0-52eb91302fd8</td>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "      <td>Suche nach anerkannten Veranstaltungen</td>\n",
       "      <td>In Berlin anerkannte Weiterbildungsveranstaltu...</td>\n",
       "      <td>bildungszeit simplesearch weiterbildung weiter...</td>\n",
       "      <td>None Webseite von \"Suche nach anerkannten Vera...</td>\n",
       "      <td>False</td>\n",
       "      <td>Suche nach anerkannten Veranstaltungen In Berl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>96643363-971f-43f9-b93e-e721c5b84872</td>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "      <td>VBB-Fahrplandaten via GTFS</td>\n",
       "      <td>Der Verkehrsverbund Berlin-Brandenburg (VBB) s...</td>\n",
       "      <td>Bahn Bus Fahrplan Fähre S-Bahn Straßenbahn Tra...</td>\n",
       "      <td>VBB-Fahrplandaten</td>\n",
       "      <td>False</td>\n",
       "      <td>VBB-Fahrplandaten via GTFS Der Verkehrsverbund...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4bd75f18-20bf-45e9-af17-3bf03ab6dc58</td>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "      <td>Liste der Badestellen</td>\n",
       "      <td>Die auf der Badestellenkarte für Berlin ausgew...</td>\n",
       "      <td>badegewässer baden badeseen simplesearch</td>\n",
       "      <td>None Webseite von \"Liste der Badestellen\". Hie...</td>\n",
       "      <td>False</td>\n",
       "      <td>Liste der Badestellen Die auf der Badestellenk...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>f90b3f13-b3bf-4420-88d0-c6397efc62b9</td>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "      <td>Kurse der Berliner Volkshochschulen</td>\n",
       "      <td>Hier kann man alle aktuellen Kursangebote der ...</td>\n",
       "      <td>Dienstleistungen im Bereich Bildung Volkshochs...</td>\n",
       "      <td>Aktuelle Berliner Volkshochschul Kurse Dieser ...</td>\n",
       "      <td>False</td>\n",
       "      <td>Kurse der Berliner Volkshochschulen Hier kann ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6a2acf9e-e911-4d78-b571-c75091d07086</td>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "      <td>Berliner und Brandenburger Straßen- und Volksf...</td>\n",
       "      <td>Berliner Straßen- und Volksfeste</td>\n",
       "      <td>simplesearch strassenfeste straßenfeste volksf...</td>\n",
       "      <td>None Webseite von \"Berliner und Brandenburger ...</td>\n",
       "      <td>False</td>\n",
       "      <td>Berliner und Brandenburger Straßen- und Volksf...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             identifier  \\\n",
       "0  a6828125-ae95-404f-9fb0-52eb91302fd8   \n",
       "1  96643363-971f-43f9-b93e-e721c5b84872   \n",
       "2  4bd75f18-20bf-45e9-af17-3bf03ab6dc58   \n",
       "3  f90b3f13-b3bf-4420-88d0-c6397efc62b9   \n",
       "4  6a2acf9e-e911-4d78-b571-c75091d07086   \n",
       "\n",
       "                                                link  \\\n",
       "0  https://datenregister.berlin.de/api/3/action/c...   \n",
       "1  https://datenregister.berlin.de/api/3/action/c...   \n",
       "2  https://datenregister.berlin.de/api/3/action/c...   \n",
       "3  https://datenregister.berlin.de/api/3/action/c...   \n",
       "4  https://datenregister.berlin.de/api/3/action/c...   \n",
       "\n",
       "                                               title  \\\n",
       "0             Suche nach anerkannten Veranstaltungen   \n",
       "1                         VBB-Fahrplandaten via GTFS   \n",
       "2                              Liste der Badestellen   \n",
       "3                Kurse der Berliner Volkshochschulen   \n",
       "4  Berliner und Brandenburger Straßen- und Volksf...   \n",
       "\n",
       "                                         description  \\\n",
       "0  In Berlin anerkannte Weiterbildungsveranstaltu...   \n",
       "1  Der Verkehrsverbund Berlin-Brandenburg (VBB) s...   \n",
       "2  Die auf der Badestellenkarte für Berlin ausgew...   \n",
       "3  Hier kann man alle aktuellen Kursangebote der ...   \n",
       "4                   Berliner Straßen- und Volksfeste   \n",
       "\n",
       "                                             keyword  \\\n",
       "0  bildungszeit simplesearch weiterbildung weiter...   \n",
       "1  Bahn Bus Fahrplan Fähre S-Bahn Straßenbahn Tra...   \n",
       "2           badegewässer baden badeseen simplesearch   \n",
       "3  Dienstleistungen im Bereich Bildung Volkshochs...   \n",
       "4  simplesearch strassenfeste straßenfeste volksf...   \n",
       "\n",
       "                                        distribution  is_study  \\\n",
       "0  None Webseite von \"Suche nach anerkannten Vera...     False   \n",
       "1                                  VBB-Fahrplandaten     False   \n",
       "2  None Webseite von \"Liste der Badestellen\". Hie...     False   \n",
       "3  Aktuelle Berliner Volkshochschul Kurse Dieser ...     False   \n",
       "4  None Webseite von \"Berliner und Brandenburger ...     False   \n",
       "\n",
       "                                  text_for_embedding  \n",
       "0  Suche nach anerkannten Veranstaltungen In Berl...  \n",
       "1  VBB-Fahrplandaten via GTFS Der Verkehrsverbund...  \n",
       "2  Liste der Badestellen Die auf der Badestellenk...  \n",
       "3  Kurse der Berliner Volkshochschulen Hier kann ...  \n",
       "4  Berliner und Brandenburger Straßen- und Volksf...  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# raw = retrieve_mdv_metadata()\n",
    "# df = parse_and_prepare_metadata(raw)\n",
    "df.title = df.title.apply(lambda x: x.strip())\n",
    "df.description = df.description.apply(lambda x: x.strip())\n",
    "\n",
    "# Combine title and description for embedding.\n",
    "df[\"text_for_embedding\"] = df.title + \" \" + df.description\n",
    "df[\"text_for_embedding\"] = df[\"text_for_embedding\"].apply(lambda x: x.strip())\n",
    "df.to_parquet(DATASETS)\n",
    "df.info(memory_usage=\"deep\")\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lemmatize text\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "# nlp = spacy.load(\"de_core_news_lg\")\n",
    "nlp = spacy.load(\"de_core_news_sm\", disable=[\"ner\", \"parser\"])\n",
    "\n",
    "LETTERS_AND_DIGITS = re.compile(r\"[^a-zäüöA-ZÜÄÖ0-9.]\")\n",
    "MULTIPLE_SPACES = re.compile(r\"\\s+\")\n",
    "\n",
    "\n",
    "def prepare_for_lexical_search(text, lower=False, remove_umlauts=False):\n",
    "    \"\"\"Lemmatize text, and optionally lower case and remove umlauts for lexical search.\n",
    "\n",
    "    Args:\n",
    "        text (str): Text to process.\n",
    "\n",
    "    Keyword Arguments:\n",
    "        lower (bool): Lower case text (default: {True}).\n",
    "        remove_umlauts (bool): Remove umlauts from text (default: {True}).\n",
    "\n",
    "    Returns:\n",
    "        str: Lemmatized text, optionally lower cased, and without umlauts.\n",
    "    \"\"\"\n",
    "    doc = nlp(text)\n",
    "    text = \" \".join([token.lemma_ if token.is_alpha else token.text for token in doc])\n",
    "    text = re.sub(LETTERS_AND_DIGITS, \" \", text)\n",
    "    text = re.sub(MULTIPLE_SPACES, \" \", text)\n",
    "    if lower:\n",
    "        text = text.lower()\n",
    "    if remove_umlauts:\n",
    "        text = text.replace(\"ä\", \"ae\").replace(\"ö\", \"oe\").replace(\"ü\", \"ue\")\n",
    "    return text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"title_lemma\"] = df.title.parallel_apply(lambda x: prepare_for_lexical_search(x))\n",
    "df[\"description_lemma\"] = df.description.parallel_apply(\n",
    "    lambda x: prepare_for_lexical_search(x)\n",
    ")\n",
    "\n",
    "df[\"distribution_lemma\"] = df.distribution.parallel_apply(\n",
    "    lambda x: prepare_for_lexical_search(x)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Have a quick look at the data.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "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>2655</th>\n",
       "      <th>3080</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>identifier</th>\n",
       "      <td>043a8358-a407-4ba4-bc66-057d71bb4e9a</td>\n",
       "      <td>46e30354-b019-4137-8166-975dbce006b5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>link</th>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <td>Einwohnerinnen und Einwohner in den Ortsteilen...</td>\n",
       "      <td>Gesundheitsberichterstattung Berlin: Kosten -&gt;...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>description</th>\n",
       "      <td>Einwohnerinnen und Einwohner in den Ortsteilen...</td>\n",
       "      <td>Bereitstellung von 54 Dokumenten (51 XLS-Datei...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>keyword</th>\n",
       "      <td>Einwohner Ortsteile kleinräumige Einwohnerzahl</td>\n",
       "      <td>Behandlungsfälle Berlin Berufsgruppen Beschäft...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>distribution</th>\n",
       "      <td>Datenmatrix Datenmatrix Metadaten Metadaten</td>\n",
       "      <td>None Zugriff über das Gesundheits- und Soziali...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_study</th>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>text_for_embedding</th>\n",
       "      <td>Einwohnerinnen und Einwohner in den Ortsteilen...</td>\n",
       "      <td>Gesundheitsberichterstattung Berlin: Kosten -&gt;...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title_lemma</th>\n",
       "      <td>Einwohnerin und Einwohner in der Ortsteil Berl...</td>\n",
       "      <td>Gesundheitsberichterstattung Berlin Kosten Kos...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>description_lemma</th>\n",
       "      <td>Einwohnerin und Einwohner in der Ortsteil Berl...</td>\n",
       "      <td>Bereitstellung von 54 Dokument 51 XLS Dateien ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>distribution_lemma</th>\n",
       "      <td>Datenmatrix Datenmatrix Metadat Metadat</td>\n",
       "      <td>None Zugriff über der Gesundheits und Sozialin...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                 2655  \\\n",
       "identifier                       043a8358-a407-4ba4-bc66-057d71bb4e9a   \n",
       "link                https://datenregister.berlin.de/api/3/action/c...   \n",
       "title               Einwohnerinnen und Einwohner in den Ortsteilen...   \n",
       "description         Einwohnerinnen und Einwohner in den Ortsteilen...   \n",
       "keyword                Einwohner Ortsteile kleinräumige Einwohnerzahl   \n",
       "distribution              Datenmatrix Datenmatrix Metadaten Metadaten   \n",
       "is_study                                                         True   \n",
       "text_for_embedding  Einwohnerinnen und Einwohner in den Ortsteilen...   \n",
       "title_lemma         Einwohnerin und Einwohner in der Ortsteil Berl...   \n",
       "description_lemma   Einwohnerin und Einwohner in der Ortsteil Berl...   \n",
       "distribution_lemma            Datenmatrix Datenmatrix Metadat Metadat   \n",
       "\n",
       "                                                                 3080  \n",
       "identifier                       46e30354-b019-4137-8166-975dbce006b5  \n",
       "link                https://datenregister.berlin.de/api/3/action/c...  \n",
       "title               Gesundheitsberichterstattung Berlin: Kosten ->...  \n",
       "description         Bereitstellung von 54 Dokumenten (51 XLS-Datei...  \n",
       "keyword             Behandlungsfälle Berlin Berufsgruppen Beschäft...  \n",
       "distribution        None Zugriff über das Gesundheits- und Soziali...  \n",
       "is_study                                                        False  \n",
       "text_for_embedding  Gesundheitsberichterstattung Berlin: Kosten ->...  \n",
       "title_lemma         Gesundheitsberichterstattung Berlin Kosten Kos...  \n",
       "description_lemma   Bereitstellung von 54 Dokument 51 XLS Dateien ...  \n",
       "distribution_lemma  None Zugriff über der Gesundheits und Sozialin...  "
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Using transposed view to give a better overview of the data\n",
    "df.sample(2).T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Embed data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For our prototype app we use OpenAIs embeddings for convienience.\n",
    "\n",
    "We also tested these open source models with [SentenceTransformers](https://sbert.net/) with very good results:\n",
    "\n",
    "- [PM-AI/bi-encoder_msmarco_bert-base_german](https://huggingface.co/PM-AI/bi-encoder_msmarco_bert-base_german) - 350 tokens context length\n",
    "- [Jina AI jina-embeddings-v2-base-de](https://huggingface.co/jinaai/jina-embeddings-v2-base-de) - 8192 tokens context length\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "def embed_with_openai(texts, model=\"text-embedding-3-small\"):\n",
    "    \"\"\"Embed text using OpenAIs embedding API.\n",
    "\n",
    "    Args:\n",
    "        texts (list): List of texts to embed.\n",
    "        model (str): OpenAI model to use (default: {\"text-embedding-3-small\"}).\n",
    "\n",
    "    Returns:\n",
    "        list: List of embeddings.\n",
    "    \"\"\"\n",
    "    response = client_openai.embeddings.create(input=texts, model=model)\n",
    "    return [x.embedding for x in response.data]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Suche nach anerkannten Veranstaltungen In Berlin anerkannte Weiterbildungsveranstaltungen.',\n",
       "       'VBB-Fahrplandaten via GTFS Der Verkehrsverbund Berlin-Brandenburg (VBB) stellt regelmäßig die aktuellen Bus- und Bahn-Fahrplandaten aus Berlin und Brandenburg im GTFS-Format zur Verfügung. \\r\\nMehr zu dem Format ist unter https://developers.google.com/transit/gtfs/ (auf Englisch)) zu finden.\\r\\nDeutschlandweite Haltestellen (DHID) können im zentralen Haltestellenverzeichnis (https://zhv.wvigmbh.de) abgerufen werden.\\r\\nVBB-Logos für die erforderliche Namensnennung „VBB Verkehrsverbund Berlin-Brandenburg GmbH“ gibt es unter https://www.vbb.de/presse/media-service/logos?slug=logos.\\r\\nVeraltete Fahrplandaten können unter api@vbb.de angefragt werden.',\n",
       "       'Liste der Badestellen Die auf der Badestellenkarte für Berlin ausgewiesenen Bäder und Badestellen werden regelmäßig hinsichtlich der Badegewässerqualität vom 15. Mai bis 15. September (Badesaison) eines jeden Jahres in 14tägigen Abständen entsprechend einer von der Europäischen Union (EU) erlassenen Richtlinie über die Qualität der Badegewässer sowie der Berliner Badegewässerverordnung überwacht. Darüber hinaus werden aus Vorsorgegründen weitere Gewässer z.B. im Rahmen des Gewässerschutzes vierwöchentlich beprobt.',\n",
       "       ...,\n",
       "       'Gesundheitsberichterstattung Berlin: Kontext Einschulungsuntersuchung Berlin 2011 Bereitstellung von 87 Dokumenten (86 XLS-Dateien, 1 PDF-Datei) zum Datenstand 15.02.2012 mit 3 verschiedenen Raumbezügen.',\n",
       "       'Gesundheitsberichterstattung Berlin: Kosten -> Aufwendungen für Leistungen in Pflegeeinrichtungen Bereitstellung von 24 Dokumenten (23 XLS-Dateien, 1 PDF-Datei) für den Zeitraum vom 31.12.2001 bis 15.12.2011 mit 3 verschiedenen Raumbezügen.',\n",
       "       'Gesundheitsberichterstattung Berlin: Gesundheitsindikator 3.17 - Beeinträchtigung der sozialen Rollenfunktion durch eingeschränkte körperliche Gesundheit oder seelische Beschwerden innerhalb der letzten vier Wochen nach Alter und Geschlecht, Deutschl... Bereitstellung von 13 Dokumenten (13 XLS-Dateien) für den Zeitraum vom 31.12.1998 bis 31.12.2012 mit dem Raumbezug Deutschland.'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.text_for_embedding.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Checking column: text_for_embedding\n",
      "\n",
      "Data type: object\n",
      "\n",
      "Null values: 0\n",
      "Empty strings: 0\n",
      "Whitespace-only strings: 0\n",
      "\n",
      "Unique data types in the column:\n",
      "- <class 'str'>\n",
      "\n",
      "Sample values:\n",
      "481     Verwaltungseinheiten im INSPIRE-Datenmodell - ...\n",
      "2962    Gesundheitsberichterstattung Berlin: Gesundhei...\n",
      "658     Standardeinspeiseprofil BHKW 2020 Betreiber vo...\n",
      "1820    Langjährige Entwicklung Luftqualität NOx-Emiss...\n",
      "1560    Jugendarbeitslosigkeit 2018 - [WMS] Anteil der...\n",
      "\n",
      "Value counts:\n",
      "text_for_embedding\n",
      "Senatsvorlagen der Senatsverwaltung für Wirtschaft, Energie und Betriebe Parlamentsdokumentation                                                                                                                                                                               2\n",
      "Prüfberichte der Berliner Heimaufsicht Die Heimaufsicht ist verpflichtet, Prüfberichte über die Ergebnisse der von ihr durchgeführten Prüfungen zu erstellen und diese Prüfberichte zu veröffentlichen. Die veröffentlichten Prüfberichte werden regelmäßig aktualisiert.      2\n",
      "Statistische Blöcke des RBS - [WFS] Statistische Blöcke des RBS als kleinste flächige Unterteilung des Berliner Stadtgebiets.\\n\\n![Vorschaugrafik zu Datensatz 'Statistische Blöcke des RBS'](https://fbinter.stadt-berlin.de/fb_daten/vorschau/sachdaten/svor_default.gif)    2\n",
      "Berlin-Zoom (SW-Ausgabe) Berlin in verschiedenen Kartengrundlagen. Detaillierung beim Zoomen.\\n\\n![Vorschaugrafik zu Datensatz 'Berlin-Zoom (SW-Ausgabe)'](https://fbinter.stadt-berlin.de/fb_daten/vorschau/karten/kvor_berlinzoom_sw.gif)                                    2\n",
      "ALKIS Berlin Flurstücke - [WFS] Daten des amtlichen Liegenschaftskatsterinformationssystems (ALKIS) - Das Flurstück ist als geometrisch eindeutig begrenzter Teil der Erdoberfläche Buchungseinheit des Liegenschaftskatasters.                                                2\n",
      "Name: count, dtype: int64\n",
      "\n",
      "Basic statistics:\n",
      "count                                                  3311\n",
      "unique                                                 3303\n",
      "top       Senatsvorlagen der Senatsverwaltung für Wirtsc...\n",
      "freq                                                      2\n",
      "Name: text_for_embedding, dtype: object\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# Assuming your dataframe is named 'df'\n",
    "\n",
    "\n",
    "def check_column(df, column_name=\"text_for_embedding\"):\n",
    "    print(f\"Checking column: {column_name}\")\n",
    "\n",
    "    # Check data type\n",
    "    print(f\"\\nData type: {df[column_name].dtype}\")\n",
    "\n",
    "    # Check for null values\n",
    "    null_count = df[column_name].isnull().sum()\n",
    "    print(f\"\\nNull values: {null_count}\")\n",
    "\n",
    "    # Check for empty strings\n",
    "    empty_count = (df[column_name] == \"\").sum()\n",
    "    print(f\"Empty strings: {empty_count}\")\n",
    "\n",
    "    # Check for whitespace-only strings\n",
    "    whitespace_count = (df[column_name].str.isspace()).sum()\n",
    "    print(f\"Whitespace-only strings: {whitespace_count}\")\n",
    "\n",
    "    # Get unique data types within the column\n",
    "    unique_types = df[column_name].apply(type).unique()\n",
    "    print(\"\\nUnique data types in the column:\")\n",
    "    for t in unique_types:\n",
    "        print(f\"- {t}\")\n",
    "\n",
    "    # Sample of values\n",
    "    print(\"\\nSample values:\")\n",
    "    print(df[column_name].sample(min(5, len(df))).to_string())\n",
    "\n",
    "    # Statistics\n",
    "    print(\"\\nValue counts:\")\n",
    "    print(df[column_name].value_counts().head())\n",
    "\n",
    "    print(\"\\nBasic statistics:\")\n",
    "    print(df[column_name].describe())\n",
    "\n",
    "\n",
    "# Run the check\n",
    "check_column(df)\n",
    "\n",
    "# If you want to check a different column, you can specify it:\n",
    "# check_column(df, 'some_other_column')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openai import OpenAI\n",
    "import time\n",
    "from typing import List, Any\n",
    "import numpy as np\n",
    "\n",
    "client = OpenAI()  # Ensure your API key is set in environment variables\n",
    "\n",
    "\n",
    "def embed_with_openai(\n",
    "    texts: List[str],\n",
    "    model: str = \"text-embedding-3-small\",\n",
    "    batch_size: int = 100,\n",
    "    max_retries: int = 3,\n",
    "    retry_delay: float = 1.0,\n",
    ") -> List[List[float]]:\n",
    "    \"\"\"\n",
    "    Embed text using OpenAI's embedding API with batching, retries, and error handling.\n",
    "\n",
    "    Args:\n",
    "        texts (List[str]): List of texts to embed.\n",
    "        model (str): OpenAI model to use (default: \"text-embedding-3-small\").\n",
    "        batch_size (int): Number of texts to process in each API call (default: 100).\n",
    "        max_retries (int): Maximum number of retries for failed API calls (default: 3).\n",
    "        retry_delay (float): Delay in seconds between retries (default: 1.0).\n",
    "\n",
    "    Returns:\n",
    "        List[List[float]]: List of embeddings.\n",
    "\n",
    "    Raises:\n",
    "        ValueError: If input texts are not valid.\n",
    "        RuntimeError: If all retries fail.\n",
    "    \"\"\"\n",
    "    if not texts or not all(isinstance(text, str) for text in texts):\n",
    "        raise ValueError(\"Input must be a non-empty list of strings\")\n",
    "\n",
    "    all_embeddings = []\n",
    "\n",
    "    for i in range(0, len(texts), batch_size):\n",
    "        batch = texts[i : i + batch_size]\n",
    "\n",
    "        for attempt in range(max_retries):\n",
    "            try:\n",
    "                response = client.embeddings.create(input=batch, model=model)\n",
    "                batch_embeddings = [item.embedding for item in response.data]\n",
    "                all_embeddings.extend(batch_embeddings)\n",
    "                break\n",
    "            except Exception as e:\n",
    "                print(\n",
    "                    f\"Error on attempt {attempt + 1} for batch {i//batch_size + 1}: {str(e)}\"\n",
    "                )\n",
    "                if attempt == max_retries - 1:\n",
    "                    raise RuntimeError(\n",
    "                        f\"Failed to embed batch after {max_retries} attempts\"\n",
    "                    ) from e\n",
    "                time.sleep(retry_delay * (2**attempt))  # Exponential backoff\n",
    "\n",
    "    # Verify the output\n",
    "    if len(all_embeddings) != len(texts):\n",
    "        raise RuntimeError(\n",
    "            f\"Mismatch in number of embeddings ({len(all_embeddings)}) and input texts ({len(texts)})\"\n",
    "        )\n",
    "\n",
    "    return all_embeddings\n",
    "\n",
    "\n",
    "# Usage example\n",
    "def process_dataframe_embeddings(df, text_column=\"text_for_embedding\"):\n",
    "    try:\n",
    "        texts = df[text_column].astype(str).tolist()\n",
    "        embeddings = embed_with_openai(texts)\n",
    "        df[\"embedding_openai\"] = embeddings\n",
    "        print(f\"Successfully embedded {len(embeddings)} texts\")\n",
    "    except Exception as e:\n",
    "        print(f\"Error in embedding process: {str(e)}\")\n",
    "        # Optionally, save problematic texts for further investigation\n",
    "        df[df[text_column].apply(lambda x: not isinstance(x, str) or not x)].to_csv(\n",
    "            \"problematic_texts.csv\", index=False\n",
    "        )\n",
    "    return df\n",
    "\n",
    "\n",
    "# Example usage\n",
    "# df = process_dataframe_embeddings(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "\n",
    "def process_dataframe_embeddings(texts, model=\"text-embedding-3-small\"):\n",
    "    try:\n",
    "        # Ensure texts are strings\n",
    "        texts = [str(text) for text in texts]\n",
    "\n",
    "        # Get embeddings\n",
    "        embeddings = embed_with_openai(texts, model)\n",
    "\n",
    "        # Return the embeddings directly\n",
    "        return embeddings\n",
    "\n",
    "    except Exception as e:\n",
    "        print(f\"Error in embedding process: {str(e)}\")\n",
    "        # Save problematic texts\n",
    "        pd.DataFrame(\n",
    "            {\n",
    "                \"problematic_text\": [\n",
    "                    text for text in texts if not isinstance(text, str) or not text\n",
    "                ]\n",
    "            }\n",
    "        ).to_csv(\"problematic_texts.csv\", index=False)\n",
    "        return None\n",
    "\n",
    "\n",
    "# Usage\n",
    "embeddings = process_dataframe_embeddings(df.text_for_embedding.values)\n",
    "\n",
    "if embeddings is not None:\n",
    "    df[\"embedding_openai\"] = embeddings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successfully embedded 3311 texts\n"
     ]
    },
    {
     "ename": "ValueError",
     "evalue": "Columns must be same length as key",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m/tmp/ipykernel_17855/1439091843.py\u001b[0m in \u001b[0;36m?\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"embedding_openai\"\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mprocess_dataframe_embeddings\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdf\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/Projekte/ogd_ai-search/env/lib/python3.10/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m?\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m   4297\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_setitem_frame\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4298\u001b[0m         \u001b[0;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mSeries\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mnp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mndarray\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlist\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mIndex\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4299\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_setitem_array\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4300\u001b[0m         \u001b[0;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mDataFrame\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 4301\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_set_item_frame_value\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   4302\u001b[0m         elif (\n\u001b[1;32m   4303\u001b[0m             \u001b[0mis_list_like\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4304\u001b[0m             \u001b[0;32mand\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mis_unique\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/Projekte/ogd_ai-search/env/lib/python3.10/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m?\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m   4425\u001b[0m             \u001b[0mloc\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4426\u001b[0m             \u001b[0mcols\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mloc\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4427\u001b[0m             \u001b[0mlen_cols\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;36m1\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mis_scalar\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcols\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcols\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtuple\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32melse\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcols\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4428\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mlen_cols\u001b[0m \u001b[0;34m!=\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 4429\u001b[0;31m                 \u001b[0;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Columns must be same length as key\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   4430\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4431\u001b[0m             \u001b[0;31m# align right-hand-side columns if self.columns\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4432\u001b[0m             \u001b[0;31m# is multi-index and self[key] is a sub-frame\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mValueError\u001b[0m: Columns must be same length as key"
     ]
    }
   ],
   "source": [
    "df[\"embedding_openai\"] = process_dataframe_embeddings(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['identifier', 'link', 'title', 'description', 'keyword', 'distribution',\n",
       "       'is_study', 'text_for_embedding', 'title_lemma', 'description_lemma',\n",
       "       'distribution_lemma', 'embedding_openai'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[-0.055915284901857376,\n",
       " 0.011851861141622066,\n",
       " -0.010663777589797974,\n",
       " 0.005630358587950468,\n",
       " -0.034332726150751114,\n",
       " 0.04221465066075325,\n",
       " -0.03398499637842178,\n",
       " 0.026334894821047783,\n",
       " 0.03136541694402695,\n",
       " 0.040452808141708374,\n",
       " 0.011104238219559193,\n",
       " -0.02313576079905033,\n",
       " 0.05308706685900688,\n",
       " -0.0077544208616018295,\n",
       " 0.015358158387243748,\n",
       " 0.023541448637843132,\n",
       " -0.006728611886501312,\n",
       " 0.03171314671635628,\n",
       " 0.027331724762916565,\n",
       " 0.08679387718439102,\n",
       " 0.05878986790776253,\n",
       " -0.029881758615374565,\n",
       " -0.03899233415722847,\n",
       " 0.038575056940317154,\n",
       " 0.0490533784031868,\n",
       " -0.037021853029727936,\n",
       " 0.04117145389318466,\n",
       " -0.02120005339384079,\n",
       " 0.05141795426607132,\n",
       " 0.004007610026746988,\n",
       " 0.08549568057060242,\n",
       " -0.023367581889033318,\n",
       " -0.026288529857993126,\n",
       " 0.030438130721449852,\n",
       " -0.024781692773103714,\n",
       " 0.02538442797958851,\n",
       " -0.028514014557003975,\n",
       " 0.04648016020655632,\n",
       " -0.02589443325996399,\n",
       " 0.00331794167868793,\n",
       " 0.006641678977757692,\n",
       " -0.035329557955265045,\n",
       " -0.01047832053154707,\n",
       " 0.03502818942070007,\n",
       " -0.05535891652107239,\n",
       " -0.0005846242420375347,\n",
       " -0.0275635477155447,\n",
       " -0.018163194879889488,\n",
       " 0.005679620895534754,\n",
       " 0.01157947164028883,\n",
       " 0.019948218017816544,\n",
       " -0.02552351914346218,\n",
       " 0.0442778579890728,\n",
       " 0.021327555179595947,\n",
       " 0.026589898392558098,\n",
       " 0.008397724479436874,\n",
       " -0.03829687088727951,\n",
       " -0.005288422107696533,\n",
       " 0.013491996563971043,\n",
       " 0.04984156787395477,\n",
       " 0.03131905198097229,\n",
       " 0.012993581593036652,\n",
       " 0.041078727692365646,\n",
       " 0.06421449035406113,\n",
       " -0.03778686374425888,\n",
       " 0.0037178334314376116,\n",
       " 0.009904563426971436,\n",
       " 0.00907000619918108,\n",
       " 0.004398808348923922,\n",
       " -0.013584725558757782,\n",
       " 0.03287225216627121,\n",
       " 0.007684874348342419,\n",
       " 0.028815381228923798,\n",
       " -0.016888178884983063,\n",
       " -0.0398268923163414,\n",
       " 0.036859579384326935,\n",
       " 0.030762679874897003,\n",
       " -0.003993120975792408,\n",
       " -0.018232742324471474,\n",
       " 0.021026186645030975,\n",
       " 0.026103073731064796,\n",
       " 0.004077156074345112,\n",
       " -0.0653272271156311,\n",
       " -0.014569966122508049,\n",
       " -0.009916153736412525,\n",
       " -0.02997448854148388,\n",
       " 0.028189463540911674,\n",
       " 0.009574217721819878,\n",
       " 0.0014851049054414034,\n",
       " -0.02570897713303566,\n",
       " -0.025314880535006523,\n",
       " -0.031226322054862976,\n",
       " 0.02466578036546707,\n",
       " 0.07144731283187866,\n",
       " -0.00476972246542573,\n",
       " -0.0026239268481731415,\n",
       " -0.02879219874739647,\n",
       " -0.008867163211107254,\n",
       " -0.02015685848891735,\n",
       " 0.038065049797296524,\n",
       " 0.030924955382943153,\n",
       " -0.03475000709295273,\n",
       " 0.04205237329006195,\n",
       " -0.0599026121199131,\n",
       " 0.02999766916036606,\n",
       " -0.03894596919417381,\n",
       " 0.011695382185280323,\n",
       " 0.0002053791395155713,\n",
       " 0.029510844498872757,\n",
       " 0.01883547753095627,\n",
       " -0.059021688997745514,\n",
       " 0.020226404070854187,\n",
       " 0.013341313228011131,\n",
       " 0.006850318051874638,\n",
       " -0.036349572241306305,\n",
       " -0.01669112965464592,\n",
       " 0.041611913591623306,\n",
       " -0.030716316774487495,\n",
       " -0.05123249813914299,\n",
       " -0.009348192252218723,\n",
       " -0.03655821084976196,\n",
       " 0.0015082870377227664,\n",
       " 0.007951469160616398,\n",
       " 0.017827054485678673,\n",
       " -0.01787341944873333,\n",
       " 0.020539363846182823,\n",
       " 0.02036549709737301,\n",
       " 0.06277719140052795,\n",
       " -5.12542283104267e-05,\n",
       " -0.02999766916036606,\n",
       " 0.008959891274571419,\n",
       " -0.026589898392558098,\n",
       " 0.02315894328057766,\n",
       " -0.014569966122508049,\n",
       " -0.026265347376465797,\n",
       " -0.027192633599042892,\n",
       " -0.025477156043052673,\n",
       " 0.0245266892015934,\n",
       " -0.014233824796974659,\n",
       " -0.03333589434623718,\n",
       " 0.03238542750477791,\n",
       " 0.01227493491023779,\n",
       " -0.00035189741174690425,\n",
       " 0.04919246956706047,\n",
       " -0.05521982163190842,\n",
       " -0.006653269752860069,\n",
       " 0.004708869382739067,\n",
       " 0.005149329546838999,\n",
       " -0.0037612998858094215,\n",
       " -0.03491228073835373,\n",
       " -0.039757344871759415,\n",
       " -0.02093345858156681,\n",
       " -0.03203769773244858,\n",
       " 0.01776909828186035,\n",
       " -0.03134223446249962,\n",
       " -0.06486358493566513,\n",
       " -0.0554516427218914,\n",
       " 0.0216521043330431,\n",
       " 0.05313343182206154,\n",
       " -0.047082897275686264,\n",
       " -0.0191020704805851,\n",
       " 0.0027369395829737186,\n",
       " 0.004601651802659035,\n",
       " 0.015960892662405968,\n",
       " -0.011915612034499645,\n",
       " 0.0017401082441210747,\n",
       " -0.013526770286262035,\n",
       " -0.008218063041567802,\n",
       " -0.0503515750169754,\n",
       " 0.024920783936977386,\n",
       " 0.041078727692365646,\n",
       " -0.03639593720436096,\n",
       " 0.05730621516704559,\n",
       " 0.006293946877121925,\n",
       " -0.031434960663318634,\n",
       " 0.03658139333128929,\n",
       " 0.048636097460985184,\n",
       " 0.0342399999499321,\n",
       " -0.022208476439118385,\n",
       " 0.016783857718110085,\n",
       " 0.01309790089726448,\n",
       " 0.048126090317964554,\n",
       " -0.01717795431613922,\n",
       " 0.0008338320767506957,\n",
       " 0.010756505653262138,\n",
       " 0.05290161073207855,\n",
       " -0.015277020633220673,\n",
       " 0.008797616697847843,\n",
       " -0.0008686052751727402,\n",
       " 0.017166363075375557,\n",
       " 0.014361326582729816,\n",
       " -0.013932457193732262,\n",
       " 0.016088394448161125,\n",
       " 0.013828137889504433,\n",
       " 0.0013706431491300464,\n",
       " 0.03915460780262947,\n",
       " -0.0016444820212200284,\n",
       " -0.07047366350889206,\n",
       " 0.03996598348021507,\n",
       " -0.00036638625897467136,\n",
       " -0.0059404196217656136,\n",
       " -0.10571049153804779,\n",
       " 0.0010497155599296093,\n",
       " 0.03384590148925781,\n",
       " -0.015740662813186646,\n",
       " 0.004975463729351759,\n",
       " -0.0123444814234972,\n",
       " -0.03556137904524803,\n",
       " 0.017780689522624016,\n",
       " -0.03301134705543518,\n",
       " 0.058836232870817184,\n",
       " -0.016737494617700577,\n",
       " 0.0031064047943800688,\n",
       " 0.011365036480128765,\n",
       " -0.014964061789214611,\n",
       " -0.012367663905024529,\n",
       " 0.004569776356220245,\n",
       " 0.004442274570465088,\n",
       " 0.038575056940317154,\n",
       " 0.00902943778783083,\n",
       " 0.023761678487062454,\n",
       " -0.04240010678768158,\n",
       " 0.038227323442697525,\n",
       " 0.02026117779314518,\n",
       " 0.022347569465637207,\n",
       " -0.003155666869133711,\n",
       " 0.032594069838523865,\n",
       " -0.01607680320739746,\n",
       " -0.02858356013894081,\n",
       " -0.05688893422484398,\n",
       " -0.03250133991241455,\n",
       " 0.02640444040298462,\n",
       " -0.008838185109198093,\n",
       " 0.004801597911864519,\n",
       " 0.008838185109198093,\n",
       " 0.019820716232061386,\n",
       " -0.040081895887851715,\n",
       " -0.017027270048856735,\n",
       " 0.009435124695301056,\n",
       " -0.044185131788253784,\n",
       " -0.018337061628699303,\n",
       " -0.00924387201666832,\n",
       " 0.03845914453268051,\n",
       " -0.030739497393369675,\n",
       " -0.022440297529101372,\n",
       " -0.008142721839249134,\n",
       " -0.0041206227615475655,\n",
       " -0.0003930094535462558,\n",
       " 0.02758672833442688,\n",
       " 0.02366895042359829,\n",
       " -0.004323466215282679,\n",
       " -0.0025268516037613153,\n",
       " 0.024271685630083084,\n",
       " -0.025314880535006523,\n",
       " -0.010032064281404018,\n",
       " -0.023715313524007797,\n",
       " -0.02589443325996399,\n",
       " -0.003993120975792408,\n",
       " 1.5745281416457146e-05,\n",
       " -0.024642599746584892,\n",
       " 0.03317362070083618,\n",
       " 0.015775436535477638,\n",
       " -0.019948218017816544,\n",
       " -0.009562626481056213,\n",
       " -0.04036007821559906,\n",
       " -0.0063171288929879665,\n",
       " -0.03815777972340584,\n",
       " -0.009156939573585987,\n",
       " -0.013804955407977104,\n",
       " -0.02117687091231346,\n",
       " -0.01683022268116474,\n",
       " -0.017108408734202385,\n",
       " -0.00012623392103705555,\n",
       " 0.05169614031910896,\n",
       " -0.06045898422598839,\n",
       " -0.030206309631466866,\n",
       " 0.05786258354783058,\n",
       " 0.0296267569065094,\n",
       " 0.007122708018869162,\n",
       " -0.005056601017713547,\n",
       " -0.036697305738925934,\n",
       " 0.009597400203347206,\n",
       " 0.030948137864470482,\n",
       " -0.07988560199737549,\n",
       " 0.033822719007730484,\n",
       " 0.04353602975606918,\n",
       " -0.03062358684837818,\n",
       " -0.03621048107743263,\n",
       " -0.04455604404211044,\n",
       " 0.02109573408961296,\n",
       " 0.017514094710350037,\n",
       " -0.04135691002011299,\n",
       " 0.0199134461581707,\n",
       " -0.006717020645737648,\n",
       " 0.029904941096901894,\n",
       " -0.011023100465536118,\n",
       " 0.0602271631360054,\n",
       " 0.006346106994897127,\n",
       " -0.000794712221249938,\n",
       " 0.004233635496348143,\n",
       " -0.04288693144917488,\n",
       " -0.021907107904553413,\n",
       " 0.004282897803932428,\n",
       " -0.004465457051992416,\n",
       " -0.023761678487062454,\n",
       " 0.018209559842944145,\n",
       " 0.010310250334441662,\n",
       " 0.022173702716827393,\n",
       " 0.051000677049160004,\n",
       " -0.0026905753184109926,\n",
       " 0.013422450050711632,\n",
       " 0.001478584948927164,\n",
       " -0.004453865811228752,\n",
       " -0.019716396927833557,\n",
       " 0.006421448662877083,\n",
       " -0.014268598519265652,\n",
       " 0.023147352039813995,\n",
       " 0.0487288273870945,\n",
       " 0.07761375606060028,\n",
       " -0.05410708114504814,\n",
       " -0.009858198463916779,\n",
       " -0.029696302488446236,\n",
       " 0.022046200931072235,\n",
       " 0.0027818549424409866,\n",
       " -0.01730545610189438,\n",
       " 0.03563092648983002,\n",
       " -0.025569884106516838,\n",
       " -0.023379173129796982,\n",
       " 0.0267521720379591,\n",
       " 0.03384590148925781,\n",
       " -0.04209873825311661,\n",
       " 0.021756423637270927,\n",
       " -0.03579320013523102,\n",
       " 0.04270147532224655,\n",
       " -0.010970940813422203,\n",
       " -0.002293581608682871,\n",
       " 0.00858897715806961,\n",
       " 0.02289234846830368,\n",
       " 0.005624562967568636,\n",
       " 0.003929370082914829,\n",
       " -0.03013676218688488,\n",
       " -0.009104779921472073,\n",
       " -0.01217061560600996,\n",
       " -0.028004007413983345,\n",
       " 0.0497024767100811,\n",
       " 0.020991414785385132,\n",
       " 0.005923032760620117,\n",
       " -0.010849234648048878,\n",
       " -0.020377088338136673,\n",
       " -0.0020762491039931774,\n",
       " -0.003268679603934288,\n",
       " -0.019554123282432556,\n",
       " 0.017166363075375557,\n",
       " -0.021269598975777626,\n",
       " -0.005731780547648668,\n",
       " -0.009742287918925285,\n",
       " -0.01819796860218048,\n",
       " 0.0348890982568264,\n",
       " -0.03968779742717743,\n",
       " 0.02839810401201248,\n",
       " 0.014117914251983166,\n",
       " 0.0027137575671076775,\n",
       " 0.050490669906139374,\n",
       " -0.009249667637050152,\n",
       " -0.023738496005535126,\n",
       " 0.05137158930301666,\n",
       " -0.08118380606174469,\n",
       " 0.02995130605995655,\n",
       " 0.019067298620939255,\n",
       " -0.020805956795811653,\n",
       " 0.001929911901243031,\n",
       " -0.020469816401600838,\n",
       " 0.04527468979358673,\n",
       " 0.016575219109654427,\n",
       " -0.01583339087665081,\n",
       " 0.003793175332248211,\n",
       " 0.044718317687511444,\n",
       " 0.01677226833999157,\n",
       " -0.0015734867192804813,\n",
       " -0.006774975918233395,\n",
       " 0.024967148900032043,\n",
       " -0.048589736223220825,\n",
       " -0.02399349957704544,\n",
       " -0.020979823544621468,\n",
       " 0.0036511847283691168,\n",
       " 0.01061161793768406,\n",
       " -0.015589979477226734,\n",
       " -0.016273852437734604,\n",
       " -0.00830499641597271,\n",
       " 0.008229654282331467,\n",
       " -0.009730696678161621,\n",
       " 0.019171617925167084,\n",
       " 0.021211644634604454,\n",
       " 0.004222044721245766,\n",
       " -0.02366895042359829,\n",
       " -0.0043611372821033,\n",
       " 0.023019850254058838,\n",
       " 0.020574135705828667,\n",
       " -0.04133372753858566,\n",
       " -0.021744834259152412,\n",
       " -0.014349735341966152,\n",
       " -0.022799620404839516,\n",
       " -0.010008882731199265,\n",
       " 0.013422450050711632,\n",
       " -0.018928205594420433,\n",
       " 0.01034502312541008,\n",
       " -0.01388609316200018,\n",
       " 5.6913930166047066e-05,\n",
       " -0.012043113820254803,\n",
       " 0.024642599746584892,\n",
       " -0.018522517755627632,\n",
       " -0.03808823227882385,\n",
       " 0.009950927458703518,\n",
       " -0.00740668922662735,\n",
       " -0.0506761260330677,\n",
       " -0.01658681035041809,\n",
       " -0.027470817789435387,\n",
       " 0.04084690287709236,\n",
       " 0.007725443225353956,\n",
       " -0.06732089072465897,\n",
       " 0.0710763931274414,\n",
       " -0.01589134708046913,\n",
       " -0.028328556567430496,\n",
       " -0.036511845886707306,\n",
       " 0.03764777258038521,\n",
       " -0.026682626456022263,\n",
       " 0.003883006051182747,\n",
       " -0.01447723712772131,\n",
       " -0.012912443839013577,\n",
       " -0.053550709038972855,\n",
       " -0.03454136475920677,\n",
       " 0.021315963938832283,\n",
       " -0.005560812074691057,\n",
       " -0.038876425474882126,\n",
       " -0.010715937241911888,\n",
       " -0.04529787227511406,\n",
       " 0.02638125792145729,\n",
       " -0.00554922129958868,\n",
       " 0.02640444040298462,\n",
       " -0.015763845294713974,\n",
       " 0.0030339607037603855,\n",
       " -0.02060890942811966,\n",
       " 0.00020900134404655546,\n",
       " -0.007151685655117035,\n",
       " -0.03556137904524803,\n",
       " -0.01980912685394287,\n",
       " -0.06300901621580124,\n",
       " 0.053226158022880554,\n",
       " 0.013225402683019638,\n",
       " 0.015149518847465515,\n",
       " -0.005311604589223862,\n",
       " -0.03099450096487999,\n",
       " -0.012216979637742043,\n",
       " -0.029904941096901894,\n",
       " 0.010773892514407635,\n",
       " -0.018510926514863968,\n",
       " 0.02791127935051918,\n",
       " -0.010142180137336254,\n",
       " 0.02964993752539158,\n",
       " -0.02584807015955448,\n",
       " 0.013213811442255974,\n",
       " 0.05118613317608833,\n",
       " 0.0017154773231595755,\n",
       " 0.03384590148925781,\n",
       " 0.03913142904639244,\n",
       " 0.015555205754935741,\n",
       " -0.030275855213403702,\n",
       " 0.01593771018087864,\n",
       " 0.06523449718952179,\n",
       " -0.06537359207868576,\n",
       " 0.048079729080200195,\n",
       " 0.06769180297851562,\n",
       " 0.01327176671475172,\n",
       " -0.004143804777413607,\n",
       " -0.00689668208360672,\n",
       " 0.023379173129796982,\n",
       " 0.033637262880802155,\n",
       " -0.062406282871961594,\n",
       " -0.0061200810596346855,\n",
       " -0.0484042763710022,\n",
       " 0.014361326582729816,\n",
       " 0.024967148900032043,\n",
       " 0.011295490898191929,\n",
       " 0.010032064281404018,\n",
       " 0.027656275779008865,\n",
       " 0.02450350672006607,\n",
       " 0.015392931178212166,\n",
       " -0.0548025444149971,\n",
       " -0.012495165690779686,\n",
       " -0.018939796835184097,\n",
       " -0.030600404366850853,\n",
       " 0.01591452956199646,\n",
       " -0.002973107621073723,\n",
       " -0.010431956499814987,\n",
       " 0.02522215247154236,\n",
       " -0.061200808733701706,\n",
       " -0.04747699201107025,\n",
       " -0.01524224691092968,\n",
       " -0.03509773686528206,\n",
       " 0.021721651777625084,\n",
       " 0.003048449521884322,\n",
       " -0.012738578021526337,\n",
       " 0.009296032600104809,\n",
       " 0.04172782599925995,\n",
       " 0.03389226645231247,\n",
       " 0.0025688691530376673,\n",
       " -0.04005871340632439,\n",
       " -0.006531563587486744,\n",
       " 0.004080053884536028,\n",
       " 0.006676452234387398,\n",
       " 0.029534026980400085,\n",
       " -0.03477318957448006,\n",
       " -0.01926434598863125,\n",
       " -0.011440378613770008,\n",
       " 0.0016053621657192707,\n",
       " 0.027123086154460907,\n",
       " 0.024619417265057564,\n",
       " 0.011220148764550686,\n",
       " -0.04886791855096817,\n",
       " 0.052669789642095566,\n",
       " -0.0055897897109389305,\n",
       " -0.03845914453268051,\n",
       " -0.012425619177520275,\n",
       " -0.00549416383728385,\n",
       " 0.009035233408212662,\n",
       " -0.004088747315108776,\n",
       " -0.04376785084605217,\n",
       " 0.003906188067048788,\n",
       " 0.013016763143241405,\n",
       " -0.03681321442127228,\n",
       " 0.003630900289863348,\n",
       " 0.024943966418504715,\n",
       " 0.01013058889657259,\n",
       " -0.07001002132892609,\n",
       " -0.006908273324370384,\n",
       " -0.006948842201381922,\n",
       " -0.04731471836566925,\n",
       " 0.013086309656500816,\n",
       " -0.029371753334999084,\n",
       " 0.024596234783530235,\n",
       " 0.020006174221634865,\n",
       " 0.03331271559000015,\n",
       " -0.011689586564898491,\n",
       " -0.005528937093913555,\n",
       " -0.01720113679766655,\n",
       " -0.02570897713303566,\n",
       " 0.031040865927934647,\n",
       " -0.01738659478724003,\n",
       " -0.01178231555968523,\n",
       " -0.010310250334441662,\n",
       " 0.007980446331202984,\n",
       " -0.03607138618826866,\n",
       " 0.011081055738031864,\n",
       " 0.006792362779378891,\n",
       " 0.008055788464844227,\n",
       " 0.005111658480018377,\n",
       " 0.02689126506447792,\n",
       " -0.003816357348114252,\n",
       " 0.008252836763858795,\n",
       " -0.0025369939394295216,\n",
       " 0.010136384516954422,\n",
       " -0.010953553952276707,\n",
       " 0.018615245819091797,\n",
       " 0.015045199543237686,\n",
       " -0.008073175325989723,\n",
       " 0.035167284309864044,\n",
       " -0.007105321157723665,\n",
       " 0.008119539357721806,\n",
       " -0.0195888951420784,\n",
       " -0.011631631292402744,\n",
       " 0.01972798816859722,\n",
       " -0.0245266892015934,\n",
       " -0.0014249762753024697,\n",
       " 0.06291628628969193,\n",
       " -0.02842128463089466,\n",
       " 0.01601884886622429,\n",
       " 0.024943966418504715,\n",
       " -0.0037844819016754627,\n",
       " 0.013631089590489864,\n",
       " 0.010675368830561638,\n",
       " -0.004972565919160843,\n",
       " -0.011602654121816158,\n",
       " -0.00841511134058237,\n",
       " 0.011846065521240234,\n",
       " -0.034981828182935715,\n",
       " -0.019020933657884598,\n",
       " 0.025106241926550865,\n",
       " 0.005372457671910524,\n",
       " -0.007708056829869747,\n",
       " -0.030693134292960167,\n",
       " 0.038575056940317154,\n",
       " 0.012958807870745659,\n",
       " 0.02238234132528305,\n",
       " -0.004726255778223276,\n",
       " -0.006717020645737648,\n",
       " -0.00940614752471447,\n",
       " -0.031040865927934647,\n",
       " 0.0173634123057127,\n",
       " -0.04388376325368881,\n",
       " -0.00802681129425764,\n",
       " -0.0021052267402410507,\n",
       " 0.0038453349843621254,\n",
       " 0.020295951515436172,\n",
       " -0.026682626456022263,\n",
       " 0.0007831211551092565,\n",
       " 0.013515179045498371,\n",
       " -0.004161191638559103,\n",
       " -0.030020851641893387,\n",
       " -0.020469816401600838,\n",
       " -0.009910358116030693,\n",
       " -0.005071090068668127,\n",
       " -0.006682247389107943,\n",
       " -0.013758591376245022,\n",
       " 0.0010134935146197677,\n",
       " 0.023680541664361954,\n",
       " -0.008142721839249134,\n",
       " -0.046897441148757935,\n",
       " 0.015589979477226734,\n",
       " 0.01430337131023407,\n",
       " 0.018232742324471474,\n",
       " 0.012842897325754166,\n",
       " -0.014419281855225563,\n",
       " -0.007470439653843641,\n",
       " 0.021431874483823776,\n",
       " 0.03210724517703056,\n",
       " 0.021153688430786133,\n",
       " 0.002862992463633418,\n",
       " -0.015879755839705467,\n",
       " -0.002931090071797371,\n",
       " -0.01505679078400135,\n",
       " -0.002276194980368018,\n",
       " 0.024990331381559372,\n",
       " 0.025500338524580002,\n",
       " -0.0663008764386177,\n",
       " 0.029348570853471756,\n",
       " 0.014071550220251083,\n",
       " -0.0013822342734783888,\n",
       " -0.035468652844429016,\n",
       " -0.016482491046190262,\n",
       " -0.00525944447144866,\n",
       " -0.010878211818635464,\n",
       " 0.005262342281639576,\n",
       " -0.002200853079557419,\n",
       " -0.043837398290634155,\n",
       " 0.04798699915409088,\n",
       " 0.017595233395695686,\n",
       " 0.011451969854533672,\n",
       " 0.022660527378320694,\n",
       " -0.03604820370674133,\n",
       " -0.009017846547067165,\n",
       " -0.03468045964837074,\n",
       " -0.008432498201727867,\n",
       " -0.028838563710451126,\n",
       " 0.034819550812244415,\n",
       " 0.013909274712204933,\n",
       " 0.007348733488470316,\n",
       " -0.008142721839249134,\n",
       " 0.016980906948447227,\n",
       " -0.007986241951584816,\n",
       " 0.008293405175209045,\n",
       " -0.008113743737339973,\n",
       " 0.013422450050711632,\n",
       " 0.02023799531161785,\n",
       " -0.04235374182462692,\n",
       " 0.025593066588044167,\n",
       " 0.013063127174973488,\n",
       " 0.03479636833071709,\n",
       " -0.009881380945444107,\n",
       " -0.06778453290462494,\n",
       " 0.013735408894717693,\n",
       " -0.028908109292387962,\n",
       " 0.0029875964391976595,\n",
       " 0.030183127149939537,\n",
       " -0.03769413381814957,\n",
       " 0.024990331381559372,\n",
       " -0.023923953995108604,\n",
       " 0.00195599184371531,\n",
       " -0.030948137864470482,\n",
       " -0.027470817789435387,\n",
       " -0.020945049822330475,\n",
       " -0.0393400676548481,\n",
       " 0.03769413381814957,\n",
       " 0.009852402843534946,\n",
       " 0.004016302991658449,\n",
       " -0.021663695573806763,\n",
       " 0.006067921407520771,\n",
       " 0.054246172308921814,\n",
       " -0.028514014557003975,\n",
       " 0.005633256398141384,\n",
       " 0.027656275779008865,\n",
       " -0.001815450144931674,\n",
       " -0.02074800245463848,\n",
       " 0.02533806301653385,\n",
       " -0.004256817512214184,\n",
       " 0.020226404070854187,\n",
       " 0.025430791079998016,\n",
       " -0.03437909111380577,\n",
       " -0.029835395514965057,\n",
       " -0.03366044536232948,\n",
       " -0.0538288950920105,\n",
       " 0.03213042393326759,\n",
       " 0.021211644634604454,\n",
       " 0.013978821225464344,\n",
       " -0.041101906448602676,\n",
       " 0.012726986780762672,\n",
       " -0.004129316192120314,\n",
       " 0.022950304672122,\n",
       " 0.012309708632528782,\n",
       " 0.0748782679438591,\n",
       " -0.04495014250278473,\n",
       " 0.011440378613770008,\n",
       " 0.008142721839249134,\n",
       " 0.009035233408212662,\n",
       " -0.0014952471246942878,\n",
       " 0.011573676019906998,\n",
       " -0.01926434598863125,\n",
       " 0.040128257125616074,\n",
       " -0.007035775110125542,\n",
       " 9.519612831354607e-06,\n",
       " -0.026010343804955482,\n",
       " 0.020887095481157303,\n",
       " 0.010165361687541008,\n",
       " 0.00581002002581954,\n",
       " 0.004019200801849365,\n",
       " 0.02791127935051918,\n",
       " 0.01591452956199646,\n",
       " 0.054570723325014114,\n",
       " 0.014407690614461899,\n",
       " -0.03324316814541817,\n",
       " -0.062406282871961594,\n",
       " 0.010907189920544624,\n",
       " 0.010269681923091412,\n",
       " -0.03648866340517998,\n",
       " 0.04087008535861969,\n",
       " 0.009632172994315624,\n",
       " 0.020087311044335365,\n",
       " -0.009046824648976326,\n",
       " -0.005850588902831078,\n",
       " -0.03164359927177429,\n",
       " -0.03757822513580322,\n",
       " 0.020550953224301338,\n",
       " -0.010710141621530056,\n",
       " -0.01969321444630623,\n",
       " -0.008339769206941128,\n",
       " 0.004616140853613615,\n",
       " -0.003500500926747918,\n",
       " -0.00995672307908535,\n",
       " -0.009707515127956867,\n",
       " -0.03370681032538414,\n",
       " -0.047082897275686264,\n",
       " 0.024225320667028427,\n",
       " 0.0059201354160904884,\n",
       " -0.00948728434741497,\n",
       " -0.0012083682231605053,\n",
       " 0.020945049822330475,\n",
       " 0.018638428300619125,\n",
       " 0.0337531752884388,\n",
       " -0.022672118619084358,\n",
       " -0.01763000711798668,\n",
       " -0.03013676218688488,\n",
       " 0.0027528773061931133,\n",
       " -0.00766748795285821,\n",
       " 0.02587125264108181,\n",
       " 0.013816546648740768,\n",
       " -0.0021197155583649874,\n",
       " 0.006415653042495251,\n",
       " -0.014859742484986782,\n",
       " -0.01634339801967144,\n",
       " 0.015949301421642303,\n",
       " 0.01575225405395031,\n",
       " 0.0032136221416294575,\n",
       " -0.04142645746469498,\n",
       " -0.014674285426735878,\n",
       " 0.008849776349961758,\n",
       " 0.03486591577529907,\n",
       " 0.025662612169981003,\n",
       " 0.0008707785746082664,\n",
       " -0.020863912999629974,\n",
       " 0.011283899657428265,\n",
       " 0.005746269132941961,\n",
       " 0.00940614752471447,\n",
       " -0.005592687521129847,\n",
       " -0.01523065660148859,\n",
       " 0.015798619017004967,\n",
       " -0.004749437794089317,\n",
       " -0.030924955382943153,\n",
       " 0.00418727146461606,\n",
       " -0.02018003910779953,\n",
       " 0.022393932566046715,\n",
       " -0.0350513719022274,\n",
       " -0.029580391943454742,\n",
       " -0.014882924035191536,\n",
       " 0.04191328212618828,\n",
       " -0.007522599771618843,\n",
       " -0.03029903769493103,\n",
       " -0.05229887366294861,\n",
       " -0.002836912637576461,\n",
       " 0.019032524898648262,\n",
       " -0.03866778314113617,\n",
       " 0.006618496961891651,\n",
       " 0.007719647604972124,\n",
       " 0.03338225930929184,\n",
       " -0.024920783936977386,\n",
       " 0.003112200414761901,\n",
       " 0.010692755691707134,\n",
       " 0.0025065673980861902,\n",
       " 0.012483574450016022,\n",
       " 0.0006371462368406355,\n",
       " -0.021999835968017578,\n",
       " -0.012077887542545795,\n",
       " 0.01171276904642582,\n",
       " -0.010594231076538563,\n",
       " -0.020597318187355995,\n",
       " 0.020377088338136673,\n",
       " -0.01824433170258999,\n",
       " 0.02726217918097973,\n",
       " 0.003219417529180646,\n",
       " 0.015520432963967323,\n",
       " -0.004358239471912384,\n",
       " 0.030090399086475372,\n",
       " -0.007145890034735203,\n",
       " -0.04309557005763054,\n",
       " 0.005630358587950468,\n",
       " 0.011214353144168854,\n",
       " 0.01746773160994053,\n",
       " -0.015137927606701851,\n",
       " 0.006734407506883144,\n",
       " -0.014604738913476467,\n",
       " -0.012541529722511768,\n",
       " 0.013468815013766289,\n",
       " -0.005016032140702009,\n",
       " 0.01591452956199646,\n",
       " -0.011422991752624512,\n",
       " -0.0031904398929327726,\n",
       " 0.03298816457390785,\n",
       " 0.003880108240991831,\n",
       " -0.014430873095989227,\n",
       " 0.014743831939995289,\n",
       " -0.009382965043187141,\n",
       " -0.011869248002767563,\n",
       " -0.01320222020149231,\n",
       " 0.006914068944752216,\n",
       " 0.012193798087537289,\n",
       " -0.014639511704444885,\n",
       " -0.020759593695402145,\n",
       " 0.03289543464779854,\n",
       " -0.0016010155668482184,\n",
       " -0.0018661611247807741,\n",
       " -0.02705354057252407,\n",
       " 0.016297034919261932,\n",
       " 0.023900771513581276,\n",
       " -0.003587433835491538,\n",
       " 0.02138551138341427,\n",
       " 0.012332890182733536,\n",
       " -0.028143100440502167,\n",
       " -0.020319132134318352,\n",
       " 0.020701637491583824,\n",
       " 0.03945597633719444,\n",
       " -0.0028905211947858334,\n",
       " -0.025477156043052673,\n",
       " -0.01811682991683483,\n",
       " -0.007429871242493391,\n",
       " 0.0077544208616018295,\n",
       " -0.034448638558387756,\n",
       " 0.06523449718952179,\n",
       " -0.004413296934217215,\n",
       " 0.027540365234017372,\n",
       " 0.02042345143854618,\n",
       " -0.003471523290500045,\n",
       " -0.035491835325956345,\n",
       " -0.003155666869133711,\n",
       " -0.0061200810596346855,\n",
       " 0.009156939573585987,\n",
       " -0.010542071424424648,\n",
       " 0.006531563587486744,\n",
       " 0.04891428351402283,\n",
       " 0.0035990248434245586,\n",
       " -0.011892430484294891,\n",
       " 0.019171617925167084,\n",
       " -0.03419363498687744,\n",
       " 0.021478239446878433,\n",
       " -0.019519349560141563,\n",
       " -0.023355990648269653,\n",
       " -0.003685957984998822,\n",
       " -0.0246889628469944,\n",
       " -0.033822719007730484,\n",
       " -0.05220614746212959,\n",
       " 0.010994123294949532,\n",
       " 0.022927122190594673,\n",
       " 0.031064048409461975,\n",
       " -0.008021015673875809,\n",
       " -0.008136926218867302,\n",
       " 0.02415577508509159,\n",
       " -0.026497168466448784,\n",
       " -0.016436126083135605,\n",
       " -0.0029846986290067434,\n",
       " 0.006832931190729141,\n",
       " -0.009817630052566528,\n",
       " -0.0050450097769498825,\n",
       " 0.007006797473877668,\n",
       " -0.03282589092850685,\n",
       " 9.481127199251205e-05,\n",
       " -0.014314962550997734,\n",
       " 0.0028977657202631235,\n",
       " -0.009910358116030693,\n",
       " 0.03862142190337181,\n",
       " -0.031736329197883606,\n",
       " 0.06699634343385696,\n",
       " 0.021049369126558304,\n",
       " 0.0076616923324763775,\n",
       " -0.01268062274903059,\n",
       " 0.031040865927934647,\n",
       " -0.0035323763731867075,\n",
       " -0.029163112863898277,\n",
       " 0.010698550380766392,\n",
       " -0.007708056829869747,\n",
       " -0.014013594947755337,\n",
       " 0.022845983505249023,\n",
       " -0.007702261209487915,\n",
       " 0.0014452605973929167,\n",
       " 0.017247501760721207,\n",
       " 0.02587125264108181,\n",
       " -0.06504904478788376,\n",
       " -0.0191020704805851,\n",
       " -0.01717795431613922,\n",
       " -0.028861746191978455,\n",
       " 0.007974650710821152,\n",
       " -0.0010620311368256807,\n",
       " 0.02342553809285164,\n",
       " -0.002550033852458,\n",
       " -0.010646390728652477,\n",
       " 0.028815381228923798,\n",
       " -0.01371222734451294,\n",
       " -0.00530580896884203,\n",
       " 0.05823349952697754,\n",
       " 0.024318048730492592,\n",
       " 0.018986159935593605,\n",
       " 0.02417895570397377,\n",
       " -0.03162042051553726,\n",
       " 0.026520350947976112,\n",
       " 0.028884928673505783,\n",
       " 0.008368747308850288,\n",
       " 0.02176801487803459,\n",
       " -0.04891428351402283,\n",
       " 0.02009890228509903,\n",
       " 0.00537535548210144,\n",
       " -0.02117687091231346,\n",
       " 0.016760677099227905,\n",
       " 0.025593066588044167,\n",
       " -0.008849776349961758,\n",
       " -0.05299433693289757,\n",
       " -0.017398184165358543,\n",
       " 0.015381339937448502,\n",
       " -0.0006925659836269915,\n",
       " 0.006502585951238871,\n",
       " -0.03848232701420784,\n",
       " -0.006433039903640747,\n",
       " 0.0028963168151676655,\n",
       " 0.023355990648269653,\n",
       " 0.02536124549806118,\n",
       " 0.009985700249671936,\n",
       " -0.012657440267503262,\n",
       " 0.02399349957704544,\n",
       " -0.03046131320297718,\n",
       " 0.022370750084519386,\n",
       " -0.008536817505955696,\n",
       " -0.0006780771655030549,\n",
       " 0.001429322874173522,\n",
       " -0.04942429065704346,\n",
       " 0.017004089429974556,\n",
       " 0.00885557197034359,\n",
       " 0.0386909656226635,\n",
       " -0.03284906968474388,\n",
       " -0.018337061628699303,\n",
       " -0.012356072664260864,\n",
       " -0.011289695277810097,\n",
       " 0.004479945637285709,\n",
       " 0.00958001334220171,\n",
       " 0.004169884603470564,\n",
       " 0.018592065200209618,\n",
       " -0.020226404070854187,\n",
       " 0.0072965738363564014,\n",
       " 0.017293864861130714,\n",
       " 0.01250675693154335,\n",
       " -0.00226750154979527,\n",
       " 0.021988246589899063,\n",
       " -0.023738496005535126,\n",
       " 0.01421064231544733,\n",
       " 0.04490377753973007,\n",
       " -0.004459661431610584,\n",
       " -0.024248503148555756,\n",
       " -0.006908273324370384,\n",
       " -0.013909274712204933,\n",
       " -0.05141795426607132,\n",
       " 0.0446024090051651,\n",
       " -0.02552351914346218,\n",
       " -0.037531860172748566,\n",
       " -0.09699401259422302,\n",
       " -0.02109573408961296,\n",
       " 0.02705354057252407,\n",
       " ...]"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"embedding_openai\"][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "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>identifier</th>\n",
       "      <th>link</th>\n",
       "      <th>title</th>\n",
       "      <th>description</th>\n",
       "      <th>keyword</th>\n",
       "      <th>distribution</th>\n",
       "      <th>is_study</th>\n",
       "      <th>text_for_embedding</th>\n",
       "      <th>title_lemma</th>\n",
       "      <th>description_lemma</th>\n",
       "      <th>distribution_lemma</th>\n",
       "      <th>embedding_openai</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a6828125-ae95-404f-9fb0-52eb91302fd8</td>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "      <td>Suche nach anerkannten Veranstaltungen</td>\n",
       "      <td>In Berlin anerkannte Weiterbildungsveranstaltu...</td>\n",
       "      <td>bildungszeit simplesearch weiterbildung weiter...</td>\n",
       "      <td>None Webseite von \"Suche nach anerkannten Vera...</td>\n",
       "      <td>False</td>\n",
       "      <td>Suche nach anerkannten Veranstaltungen In Berl...</td>\n",
       "      <td>Suche nach anerkannt Veranstaltung</td>\n",
       "      <td>in Berlin anerkannt Weiterbildungsveranstaltung .</td>\n",
       "      <td>None Webseite von Suche nach anerkannt Veranst...</td>\n",
       "      <td>[-0.055915284901857376, 0.011851861141622066, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>96643363-971f-43f9-b93e-e721c5b84872</td>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "      <td>VBB-Fahrplandaten via GTFS</td>\n",
       "      <td>Der Verkehrsverbund Berlin-Brandenburg (VBB) s...</td>\n",
       "      <td>Bahn Bus Fahrplan Fähre S-Bahn Straßenbahn Tra...</td>\n",
       "      <td>VBB-Fahrplandaten</td>\n",
       "      <td>False</td>\n",
       "      <td>VBB-Fahrplandaten via GTFS Der Verkehrsverbund...</td>\n",
       "      <td>VBB Fahrplandaten via GTFS</td>\n",
       "      <td>der Verkehrsverbund Berlin Brandenburg VBB ste...</td>\n",
       "      <td>VBB Fahrplandaten</td>\n",
       "      <td>[-0.046875447034835815, -0.03891636058688164, ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             identifier  \\\n",
       "0  a6828125-ae95-404f-9fb0-52eb91302fd8   \n",
       "1  96643363-971f-43f9-b93e-e721c5b84872   \n",
       "\n",
       "                                                link  \\\n",
       "0  https://datenregister.berlin.de/api/3/action/c...   \n",
       "1  https://datenregister.berlin.de/api/3/action/c...   \n",
       "\n",
       "                                    title  \\\n",
       "0  Suche nach anerkannten Veranstaltungen   \n",
       "1              VBB-Fahrplandaten via GTFS   \n",
       "\n",
       "                                         description  \\\n",
       "0  In Berlin anerkannte Weiterbildungsveranstaltu...   \n",
       "1  Der Verkehrsverbund Berlin-Brandenburg (VBB) s...   \n",
       "\n",
       "                                             keyword  \\\n",
       "0  bildungszeit simplesearch weiterbildung weiter...   \n",
       "1  Bahn Bus Fahrplan Fähre S-Bahn Straßenbahn Tra...   \n",
       "\n",
       "                                        distribution  is_study  \\\n",
       "0  None Webseite von \"Suche nach anerkannten Vera...     False   \n",
       "1                                  VBB-Fahrplandaten     False   \n",
       "\n",
       "                                  text_for_embedding  \\\n",
       "0  Suche nach anerkannten Veranstaltungen In Berl...   \n",
       "1  VBB-Fahrplandaten via GTFS Der Verkehrsverbund...   \n",
       "\n",
       "                          title_lemma  \\\n",
       "0  Suche nach anerkannt Veranstaltung   \n",
       "1          VBB Fahrplandaten via GTFS   \n",
       "\n",
       "                                   description_lemma  \\\n",
       "0  in Berlin anerkannt Weiterbildungsveranstaltung .   \n",
       "1  der Verkehrsverbund Berlin Brandenburg VBB ste...   \n",
       "\n",
       "                                  distribution_lemma  \\\n",
       "0  None Webseite von Suche nach anerkannt Veranst...   \n",
       "1                                  VBB Fahrplandaten   \n",
       "\n",
       "                                    embedding_openai  \n",
       "0  [-0.055915284901857376, 0.011851861141622066, ...  \n",
       "1  [-0.046875447034835815, -0.03891636058688164, ...  "
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = [\n",
    "    \"identifier\",\n",
    "    \"link\",\n",
    "    \"title\",\n",
    "    \"title_lemma\",\n",
    "    \"description\",\n",
    "    \"description_lemma\",\n",
    "    \"text_for_embedding\",\n",
    "    \"keyword\",\n",
    "    \"distribution\",\n",
    "    \"distribution_lemma\",\n",
    "    \"is_study\",\n",
    "    \"embedding_openai\",\n",
    "]\n",
    "\n",
    "df = df[cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "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>identifier</th>\n",
       "      <th>link</th>\n",
       "      <th>title</th>\n",
       "      <th>title_lemma</th>\n",
       "      <th>description</th>\n",
       "      <th>description_lemma</th>\n",
       "      <th>text_for_embedding</th>\n",
       "      <th>keyword</th>\n",
       "      <th>distribution</th>\n",
       "      <th>distribution_lemma</th>\n",
       "      <th>is_study</th>\n",
       "      <th>embedding_openai</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a6828125-ae95-404f-9fb0-52eb91302fd8</td>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "      <td>Suche nach anerkannten Veranstaltungen</td>\n",
       "      <td>Suche nach anerkannt Veranstaltung</td>\n",
       "      <td>In Berlin anerkannte Weiterbildungsveranstaltu...</td>\n",
       "      <td>in Berlin anerkannt Weiterbildungsveranstaltung .</td>\n",
       "      <td>Suche nach anerkannten Veranstaltungen In Berl...</td>\n",
       "      <td>bildungszeit simplesearch weiterbildung weiter...</td>\n",
       "      <td>None Webseite von \"Suche nach anerkannten Vera...</td>\n",
       "      <td>None Webseite von Suche nach anerkannt Veranst...</td>\n",
       "      <td>False</td>\n",
       "      <td>[-0.055915284901857376, 0.011851861141622066, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>96643363-971f-43f9-b93e-e721c5b84872</td>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "      <td>VBB-Fahrplandaten via GTFS</td>\n",
       "      <td>VBB Fahrplandaten via GTFS</td>\n",
       "      <td>Der Verkehrsverbund Berlin-Brandenburg (VBB) s...</td>\n",
       "      <td>der Verkehrsverbund Berlin Brandenburg VBB ste...</td>\n",
       "      <td>VBB-Fahrplandaten via GTFS Der Verkehrsverbund...</td>\n",
       "      <td>Bahn Bus Fahrplan Fähre S-Bahn Straßenbahn Tra...</td>\n",
       "      <td>VBB-Fahrplandaten</td>\n",
       "      <td>VBB Fahrplandaten</td>\n",
       "      <td>False</td>\n",
       "      <td>[-0.046875447034835815, -0.03891636058688164, ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             identifier  \\\n",
       "0  a6828125-ae95-404f-9fb0-52eb91302fd8   \n",
       "1  96643363-971f-43f9-b93e-e721c5b84872   \n",
       "\n",
       "                                                link  \\\n",
       "0  https://datenregister.berlin.de/api/3/action/c...   \n",
       "1  https://datenregister.berlin.de/api/3/action/c...   \n",
       "\n",
       "                                    title                         title_lemma  \\\n",
       "0  Suche nach anerkannten Veranstaltungen  Suche nach anerkannt Veranstaltung   \n",
       "1              VBB-Fahrplandaten via GTFS          VBB Fahrplandaten via GTFS   \n",
       "\n",
       "                                         description  \\\n",
       "0  In Berlin anerkannte Weiterbildungsveranstaltu...   \n",
       "1  Der Verkehrsverbund Berlin-Brandenburg (VBB) s...   \n",
       "\n",
       "                                   description_lemma  \\\n",
       "0  in Berlin anerkannt Weiterbildungsveranstaltung .   \n",
       "1  der Verkehrsverbund Berlin Brandenburg VBB ste...   \n",
       "\n",
       "                                  text_for_embedding  \\\n",
       "0  Suche nach anerkannten Veranstaltungen In Berl...   \n",
       "1  VBB-Fahrplandaten via GTFS Der Verkehrsverbund...   \n",
       "\n",
       "                                             keyword  \\\n",
       "0  bildungszeit simplesearch weiterbildung weiter...   \n",
       "1  Bahn Bus Fahrplan Fähre S-Bahn Straßenbahn Tra...   \n",
       "\n",
       "                                        distribution  \\\n",
       "0  None Webseite von \"Suche nach anerkannten Vera...   \n",
       "1                                  VBB-Fahrplandaten   \n",
       "\n",
       "                                  distribution_lemma  is_study  \\\n",
       "0  None Webseite von Suche nach anerkannt Veranst...     False   \n",
       "1                                  VBB Fahrplandaten     False   \n",
       "\n",
       "                                    embedding_openai  \n",
       "0  [-0.055915284901857376, 0.011851861141622066, ...  \n",
       "1  [-0.046875447034835815, -0.03891636058688164, ...  "
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "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>442</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>identifier</th>\n",
       "      <td>f7e4c358-989b-4c0f-8d47-7ccbb921b459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>link</th>\n",
       "      <td>https://datenregister.berlin.de/api/3/action/c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <td>ATKIS Leitung (Linien) - [WFS]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title_lemma</th>\n",
       "      <td>ATKIS Leitung Linie WFS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>description</th>\n",
       "      <td>Eine aus Drähten oder Fasern hergestellte Leit...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>description_lemma</th>\n",
       "      <td>ein aus Dräht oder Faser hergestellt Leitung z...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>text_for_embedding</th>\n",
       "      <td>ATKIS Leitung (Linien) - [WFS] Eine aus Drähte...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>keyword</th>\n",
       "      <td>ATKIS Basis-DLM Berlin DLM Digitales Landschaf...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>distribution</th>\n",
       "      <td>Endpunkt-Beschreibung des WFS-Service Maschine...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>distribution_lemma</th>\n",
       "      <td>Endpunkt Beschreibung der WFS Service Maschine...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_study</th>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>embedding_openai</th>\n",
       "      <td>[-0.046587295830249786, -0.05070564150810242, ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                  442\n",
       "identifier                       f7e4c358-989b-4c0f-8d47-7ccbb921b459\n",
       "link                https://datenregister.berlin.de/api/3/action/c...\n",
       "title                                  ATKIS Leitung (Linien) - [WFS]\n",
       "title_lemma                                  ATKIS Leitung Linie WFS \n",
       "description         Eine aus Drähten oder Fasern hergestellte Leit...\n",
       "description_lemma   ein aus Dräht oder Faser hergestellt Leitung z...\n",
       "text_for_embedding  ATKIS Leitung (Linien) - [WFS] Eine aus Drähte...\n",
       "keyword             ATKIS Basis-DLM Berlin DLM Digitales Landschaf...\n",
       "distribution        Endpunkt-Beschreibung des WFS-Service Maschine...\n",
       "distribution_lemma  Endpunkt Beschreibung der WFS Service Maschine...\n",
       "is_study                                                        False\n",
       "embedding_openai    [-0.046587295830249786, -0.05070564150810242, ..."
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sample(1).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 3311 entries, 0 to 3310\n",
      "Data columns (total 12 columns):\n",
      " #   Column              Non-Null Count  Dtype \n",
      "---  ------              --------------  ----- \n",
      " 0   identifier          3311 non-null   object\n",
      " 1   link                3311 non-null   object\n",
      " 2   title               3311 non-null   object\n",
      " 3   title_lemma         3311 non-null   object\n",
      " 4   description         3311 non-null   object\n",
      " 5   description_lemma   3311 non-null   object\n",
      " 6   text_for_embedding  3311 non-null   object\n",
      " 7   keyword             3311 non-null   object\n",
      " 8   distribution        3311 non-null   object\n",
      " 9   distribution_lemma  3311 non-null   object\n",
      " 10  is_study            3311 non-null   bool  \n",
      " 11  embedding_openai    3311 non-null   object\n",
      "dtypes: bool(1), object(11)\n",
      "memory usage: 52.2 MB\n"
     ]
    }
   ],
   "source": [
    "df.to_parquet(DATA_WITH_EMBEDDINGS)\n",
    "df.info(memory_usage=\"deep\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Test embeddings\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Perform a quick check with scikit's cosine similarity function.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_parquet(DATA_WITH_EMBEDDINGS)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "ename": "RuntimeError",
     "evalue": "Mismatch in number of embeddings (1) and input texts (19)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mRuntimeError\u001b[0m                              Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[7], line 6\u001b[0m\n\u001b[1;32m      2\u001b[0m show_n_results \u001b[38;5;241m=\u001b[39m \u001b[38;5;241m20\u001b[39m\n\u001b[1;32m      4\u001b[0m \u001b[38;5;66;03m# Vector search based on OpenAI embeddings.\u001b[39;00m\n\u001b[1;32m      5\u001b[0m \u001b[38;5;66;03m# now = time()\u001b[39;00m\n\u001b[0;32m----> 6\u001b[0m embedding_openai \u001b[38;5;241m=\u001b[39m \u001b[43membed_with_openai\u001b[49m\u001b[43m(\u001b[49m\u001b[43mquery\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m      7\u001b[0m cosine_sim \u001b[38;5;241m=\u001b[39m cosine_similarity(embedding_openai, df\u001b[38;5;241m.\u001b[39membedding_openai\u001b[38;5;241m.\u001b[39mtolist())\n\u001b[1;32m      8\u001b[0m top_k \u001b[38;5;241m=\u001b[39m np\u001b[38;5;241m.\u001b[39margsort(cosine_sim[\u001b[38;5;241m0\u001b[39m])\n",
      "Cell \u001b[0;32mIn[6], line 59\u001b[0m, in \u001b[0;36membed_with_openai\u001b[0;34m(texts, model, batch_size, max_retries, retry_delay)\u001b[0m\n\u001b[1;32m     57\u001b[0m \u001b[38;5;66;03m# Verify the output\u001b[39;00m\n\u001b[1;32m     58\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mlen\u001b[39m(all_embeddings) \u001b[38;5;241m!=\u001b[39m \u001b[38;5;28mlen\u001b[39m(texts):\n\u001b[0;32m---> 59\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mRuntimeError\u001b[39;00m(\n\u001b[1;32m     60\u001b[0m         \u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mMismatch in number of embeddings (\u001b[39m\u001b[38;5;132;01m{\u001b[39;00m\u001b[38;5;28mlen\u001b[39m(all_embeddings)\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m) and input texts (\u001b[39m\u001b[38;5;132;01m{\u001b[39;00m\u001b[38;5;28mlen\u001b[39m(texts)\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m)\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m     61\u001b[0m     )\n\u001b[1;32m     63\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m all_embeddings\n",
      "\u001b[0;31mRuntimeError\u001b[0m: Mismatch in number of embeddings (1) and input texts (19)"
     ]
    }
   ],
   "source": [
    "query = \"fahrräder in berlin\"\n",
    "show_n_results = 20\n",
    "\n",
    "# Vector search based on OpenAI embeddings.\n",
    "# now = time()\n",
    "embedding_openai = embed_with_openai(query)\n",
    "cosine_sim = cosine_similarity(embedding_openai, df.embedding_openai.tolist())\n",
    "top_k = np.argsort(cosine_sim[0])\n",
    "top_k = top_k[::-1]\n",
    "display(df.iloc[top_k][[\"title\"]].head(show_n_results).values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1. 14 Radrouten und Radverkehrsanlagen - GPS-Tracks für die Radrouten durch Berlin\n",
      "2. Fahrradstraßen - [WFS]\n",
      "3. Fahrradstraßen - [WMS]\n",
      "4. Radverkehrsmaßnahmen in Berlin\n",
      "5. Radzähldaten in Berlin\n",
      "6. Fahrraddiebstahl in Berlin\n",
      "7. Fahrradreparaturstationen - [WFS]\n",
      "8. Radverkehrsnetz - [WFS]\n",
      "9. Berlin zählt Mobilität\n",
      "10. Reparaturführer Charlottenburg-Wilmersdorf\n",
      "11. Berliner und Brandenburger Wochen- und Trödelmärkte\n",
      "12. Fahrradreparaturstationen - [WMS]\n",
      "13. E-Mobility Ladesäulen in Berlin\n",
      "14. Radverkehrsnetz - [WMS]\n",
      "15. Berliner und Brandenburger Straßen- und Volksfeste\n",
      "16. Standorte der Verkehrsinformationstafeln in Berlin\n",
      "17. Berliner und Brandenburger Weihnachtsmärkte\n",
      "18. Parkverbotszonen für Mietfahrzeuge der Mikromobilität - [WFS]\n",
      "19. Berliner Straßen- und Volksfeste 2017\n",
      "20. Parkverbotszonen für Mietfahrzeuge der Mikromobilität - [WMS]\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "from sklearn.metrics.pairwise import cosine_similarity\n",
    "from typing import List\n",
    "\n",
    "\n",
    "def search_similar_documents(query: str, df, n_results: int = 20) -> List[str]:\n",
    "    \"\"\"\n",
    "    Search for documents similar to the query using cosine similarity.\n",
    "\n",
    "    Args:\n",
    "    query (str): The search query.\n",
    "    df (pd.DataFrame): DataFrame containing document embeddings.\n",
    "    n_results (int): Number of results to return.\n",
    "\n",
    "    Returns:\n",
    "    List[str]: Titles of the most similar documents.\n",
    "    \"\"\"\n",
    "    # Generate embedding for the query\n",
    "    query_embedding = np.array(embed_with_openai([query])[0]).reshape(1, -1)\n",
    "\n",
    "    # Ensure df.embedding_openai contains lists, not strings\n",
    "    if isinstance(df.embedding_openai.iloc[0], str):\n",
    "        df[\"embedding_openai\"] = df[\"embedding_openai\"].apply(eval)\n",
    "\n",
    "    # Convert DataFrame embeddings to 2D numpy array\n",
    "    doc_embeddings = np.array(df.embedding_openai.tolist())\n",
    "\n",
    "    # Compute cosine similarity\n",
    "    cosine_sim = cosine_similarity(query_embedding, doc_embeddings)[0]\n",
    "\n",
    "    # Get indices of top results\n",
    "    top_indices = np.argsort(cosine_sim)[::-1][:n_results]\n",
    "\n",
    "    # Return titles of top results\n",
    "    return df.iloc[top_indices][\"title\"].tolist()\n",
    "\n",
    "\n",
    "# Usage\n",
    "query = \"fahrräder in berlin\"\n",
    "show_n_results = 20\n",
    "\n",
    "similar_documents = search_similar_documents(query, df, show_n_results)\n",
    "\n",
    "# Display results\n",
    "for i, title in enumerate(similar_documents, 1):\n",
    "    print(f\"{i}. {title}\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "standard",
   "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.12"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}