Bestandsabfrage.ipynb

{
 "cells": [
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "# Bestandsabfrage von Judaica Zeitschriften \n",
    "über die DNB SRU Schnittstelle\n",
    "\n",
    "nutzt MARC21-XML (https://www.loc.gov/marc/bibliographic/)\n"
   ],
   "id": "7235d851770eefa8"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T21:03:20.257711Z",
     "start_time": "2024-04-29T21:03:20.255141Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# for the sake of readability remove the warnings\n",
    "import warnings\n",
    "\n",
    "warnings.filterwarnings('ignore')"
   ],
   "id": "102f0db9cf6a4a3c",
   "outputs": [],
   "execution_count": 3
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Import the libraries",
   "id": "ab384c1288050b62"
  },
  {
   "cell_type": "code",
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-29T21:03:22.393035Z",
     "start_time": "2024-04-29T21:03:21.706470Z"
    }
   },
   "id": "bf0c2cfdc0724188",
   "outputs": [],
   "execution_count": 4
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# Get the metadata",
   "id": "fa8a460acc3169f8"
  },
  {
   "cell_type": "code",
   "source": [
    "df_metadata = pd.read_excel('metadata/CM_Seiten_Metadaten.xlsx')\n",
    "df_metadata.head()"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "caf1e9116cdd8285",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Statistics on the Metadata  ",
   "id": "e0567c7bff63c2f1"
  },
  {
   "cell_type": "code",
   "source": "df_metadata.shape",
   "metadata": {
    "collapsed": false
   },
   "id": "f39752d436a5c28a",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "source": "df_metadata.sample(5)",
   "metadata": {
    "collapsed": false
   },
   "id": "fd89fd50ba25619a",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "source": [
    "# pandas get all headers\n",
    "df_metadata.columns"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "7b27ddeda07b5d15",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Bereinigung der Daten",
   "id": "d2f00a612fffca05"
  },
  {
   "cell_type": "code",
   "source": [
    "# drop columns VLID Seite, OT_PAHT, VLID_Zs\n",
    "df_metadata.drop(['VLID_Seite', 'OT_PATH', 'VLID_Zs', 'VLID_Parent', 'Parent Knotentyp', 'Seite (OT_SORT)', 'Seite_Caption', 'Seitenzahl_Caption'], axis=1, inplace=True)"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "2d0c32f68dc9b36",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "source": [
    "# list all values in column 'Parent-Type'\n",
    "df_metadata['Parent-Type'].unique()"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "c0a7082c062440bb",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Get all Journal titles   ",
   "id": "b0d2080c2c1ff375"
  },
  {
   "cell_type": "code",
   "source": "df_metadata['Zs_Caption'].unique()",
   "metadata": {
    "collapsed": false
   },
   "id": "3a61695ddc6f6b58",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "source": [
    "# remove duplicates in column 'Zs_Caption'\n",
    "df_metadata['Zs_Caption'] = df_metadata['Zs_Caption'].str.replace(' \\(.*\\)', '')"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "29e1316429c0dad5",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "source": "df_metadata['Zs_Caption'].unique()",
   "metadata": {
    "collapsed": false
   },
   "id": "7d8b7f9a8929a650",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "source": "df_metadata.sample(5)",
   "metadata": {
    "collapsed": false
   },
   "id": "8703ed02adadd1de",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "source": "df_metadata['Aufsatz_Caption'].unique()",
   "metadata": {
    "collapsed": false
   },
   "id": "1b632a2130858b69",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## load the pickle file",
   "id": "7675137e2bdd802e"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "import pickle\n",
    "\n",
    "# Open the Pickle file\n",
    "with open('metadata/journal_metadata/journal_list.pickle', 'rb') as file:\n",
    "    # Load the Pickle data\n",
    "    loaded_data = pickle.load(file)\n",
    "\n",
    "# Close the file\n",
    "file.close()\n",
    "\n",
    "# Now you can work with the loaded data\n",
    "print(loaded_data)\n"
   ],
   "id": "e8b5533cb58460af",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Load the JSON file",
   "id": "a90ea569f05cff03"
  },
  {
   "cell_type": "code",
   "source": [
    "import json\n",
    "\n",
    "with open('metadata/journal_metadata/journal_metadata_title_lang.json', 'r') as file:\n",
    "    data = json.load(file)"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "6bcfe20d0c239b83",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "source": [
    "import pprint\n",
    "pprint.pprint(data)"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "96a366be8d265e6e",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "source": [
    "# DNB SRU"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "e2c77732d7dcce71"
  },
  {
   "cell_type": "code",
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup as soup\n",
    "import unicodedata\n",
    "from lxml import etree\n",
    "import pandas as pd\n"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-30T17:46:29.316309Z",
     "start_time": "2024-04-30T17:46:28.924439Z"
    }
   },
   "id": "524ec391633a96fd",
   "outputs": [],
   "execution_count": 21
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# Funktion zur DNB SRU Abfrage",
   "id": "f6fbe79a387c133d"
  },
  {
   "cell_type": "code",
   "source": [
    "def dnb_sru(query, library='dnb', start=1, max_records=1000):\n",
    "    base_url = \"https://services.dnb.de/sru/\" + library\n",
    "    params = {'recordSchema' : 'MARC21-xml',\n",
    "              'operation': 'searchRetrieve',\n",
    "              'version': '1.1',\n",
    "              'maximumRecords': max_records,\n",
    "              'startRecord': start,\n",
    "              'query': query\n",
    "              }\n",
    "    r = requests.get(base_url, params=params)\n",
    "    xml = soup(r.content)\n",
    "    records = xml.find_all('record', {'type':'Bibliographic'})\n",
    "\n",
    "    if len(records) < 100:\n",
    "        return records\n",
    "\n",
    "    else:\n",
    "        num_results = 100\n",
    "        i = 101\n",
    "        while num_results == 100:\n",
    "            params.update({'startRecord': i})\n",
    "            r = requests.get(base_url, params=params)\n",
    "            xml = soup(r.content)\n",
    "            new_records = xml.find_all('record', {'type':'Bibliographic'})\n",
    "            records+=new_records\n",
    "            i+=100\n",
    "            num_results = len(new_records)\n",
    "\n",
    "        return records"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-30T17:47:37.854610Z",
     "start_time": "2024-04-30T17:47:37.849024Z"
    }
   },
   "id": "6ab6bf3cab697df5",
   "outputs": [],
   "execution_count": 22
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Alle Judaica ",
   "id": "23e42888eaefc179"
  },
  {
   "cell_type": "code",
   "source": [
    "records = dnb_sru(query='tit=Judaica', library='dnb', start=1, max_records=1000)\n",
    "print(len(records), 'Ergebnisse')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-30T18:00:12.427371Z",
     "start_time": "2024-04-30T17:59:55.624508Z"
    }
   },
   "id": "8290b83c6c477034",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1425 Ergebnisse\n"
     ]
    }
   ],
   "execution_count": 40
  },
  {
   "cell_type": "code",
   "source": [
    "import xml.dom.minidom\n",
    "temp = soup(open(records), \"xml\")\n",
    "new_xml = temp.prettify()\n",
    "print(new_xml)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-30T17:48:43.145273Z",
     "start_time": "2024-04-30T17:48:43.087898Z"
    }
   },
   "id": "d88c0e4586c23c6",
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "unhashable type: 'ResultSet'",
     "output_type": "error",
     "traceback": [
      "\u001B[0;31m---------------------------------------------------------------------------\u001B[0m",
      "\u001B[0;31mTypeError\u001B[0m                                 Traceback (most recent call last)",
      "Cell \u001B[0;32mIn[24], line 2\u001B[0m\n\u001B[1;32m      1\u001B[0m \u001B[38;5;28;01mimport\u001B[39;00m \u001B[38;5;21;01mxml\u001B[39;00m\u001B[38;5;21;01m.\u001B[39;00m\u001B[38;5;21;01mdom\u001B[39;00m\u001B[38;5;21;01m.\u001B[39;00m\u001B[38;5;21;01mminidom\u001B[39;00m\n\u001B[0;32m----> 2\u001B[0m temp \u001B[38;5;241m=\u001B[39m soup(\u001B[38;5;28;43mopen\u001B[39;49m\u001B[43m(\u001B[49m\u001B[43mrecords\u001B[49m\u001B[43m)\u001B[49m, \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mxml\u001B[39m\u001B[38;5;124m\"\u001B[39m)\n\u001B[1;32m      3\u001B[0m new_xml \u001B[38;5;241m=\u001B[39m temp\u001B[38;5;241m.\u001B[39mprettify()\n\u001B[1;32m      4\u001B[0m \u001B[38;5;28mprint\u001B[39m(new_xml)\n",
      "File \u001B[0;32m~/.venv/lib/python3.11/site-packages/IPython/core/interactiveshell.py:275\u001B[0m, in \u001B[0;36m_modified_open\u001B[0;34m(file, *args, **kwargs)\u001B[0m\n\u001B[1;32m    273\u001B[0m \u001B[38;5;129m@functools\u001B[39m\u001B[38;5;241m.\u001B[39mwraps(io_open)\n\u001B[1;32m    274\u001B[0m \u001B[38;5;28;01mdef\u001B[39;00m \u001B[38;5;21m_modified_open\u001B[39m(file, \u001B[38;5;241m*\u001B[39margs, \u001B[38;5;241m*\u001B[39m\u001B[38;5;241m*\u001B[39mkwargs):\n\u001B[0;32m--> 275\u001B[0m     \u001B[38;5;28;01mif\u001B[39;00m \u001B[43mfile\u001B[49m\u001B[43m \u001B[49m\u001B[38;5;129;43;01min\u001B[39;49;00m\u001B[43m \u001B[49m\u001B[43m{\u001B[49m\u001B[38;5;241;43m0\u001B[39;49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[38;5;241;43m1\u001B[39;49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[38;5;241;43m2\u001B[39;49m\u001B[43m}\u001B[49m:\n\u001B[1;32m    276\u001B[0m         \u001B[38;5;28;01mraise\u001B[39;00m \u001B[38;5;167;01mValueError\u001B[39;00m(\n\u001B[1;32m    277\u001B[0m             \u001B[38;5;124mf\u001B[39m\u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mIPython won\u001B[39m\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mt let you open fd=\u001B[39m\u001B[38;5;132;01m{\u001B[39;00mfile\u001B[38;5;132;01m}\u001B[39;00m\u001B[38;5;124m by default \u001B[39m\u001B[38;5;124m\"\u001B[39m\n\u001B[1;32m    278\u001B[0m             \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mas it is likely to crash IPython. If you know what you are doing, \u001B[39m\u001B[38;5;124m\"\u001B[39m\n\u001B[1;32m    279\u001B[0m             \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124myou can use builtins\u001B[39m\u001B[38;5;124m'\u001B[39m\u001B[38;5;124m open.\u001B[39m\u001B[38;5;124m\"\u001B[39m\n\u001B[1;32m    280\u001B[0m         )\n\u001B[1;32m    282\u001B[0m     \u001B[38;5;28;01mreturn\u001B[39;00m io_open(file, \u001B[38;5;241m*\u001B[39margs, \u001B[38;5;241m*\u001B[39m\u001B[38;5;241m*\u001B[39mkwargs)\n",
      "\u001B[0;31mTypeError\u001B[0m: unhashable type: 'ResultSet'"
     ]
    }
   ],
   "execution_count": 24
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# print the xml tree in the first result\n",
   "id": "2d2d45667f4a1fe3"
  },
  {
   "cell_type": "code",
   "source": [
    "def parse_record(record):\n",
    "\n",
    "    ns = {\"marc\":\"http://www.loc.gov/MARC21/slim\"}\n",
    "    xml = etree.fromstring(unicodedata.normalize(\"NFC\", str(record)))\n",
    "\n",
    "    #idn\n",
    "    idn = xml.xpath(\"marc:controlfield[@tag = '001']\", namespaces=ns)\n",
    "    try:\n",
    "        idn = idn[0].text\n",
    "    except:\n",
    "        idn = 'fail'\n",
    "\n",
    "    # title\n",
    "    title = xml.xpath(\"marc:datafield[@tag = '245']/marc:subfield[@code = 'a']\", namespaces=ns)\n",
    "    subtitle = xml.xpath(\"marc:datafield[@tag = '245']/marc:subfield[@code = 'b']\", namespaces=ns)\n",
    "   \n",
    "    # library\n",
    "    library = xml.xpath(\"marc:datafield[@tag = '040']/marc:subfield[@code = 'a']\", namespaces=ns)\n",
    "   \n",
    "    # category\n",
    "    category = xml.xpath(\"marc:datafield[@tag = '084']/marc:subfield[@code = 'a']\", namespaces=ns)\n",
    "   \n",
    "    # date\n",
    "    date = xml.xpath(\"marc:datafield[@tag = '264']/marc:subfield[@code = 'c']\", namespaces=ns)\n",
    "   \n",
    "    # person \n",
    "    person = xml.xpath(\"marc:datafield[@tag = '100']/marc:subfield[@code = 'a']\", namespaces=ns)\n",
    "   \n",
    "    # ddc\n",
    "    ddc = xml.xpath(\"marc:datafield[@tag = '082']/marc:subfield[@code= 'a']\", namespaces=ns)\n",
    "   \n",
    "    #issn\n",
    "    issn = xml.xpath(\"marc:datafield[@tag = '022']/marc:subfield[@code= 'a']\", namespaces=ns)\n",
    "   \n",
    "    # language\n",
    "    language = xml.xpath(\"marc:datafield[@tag = '041']/marc:subfield[@code= 'a']\", namespaces=ns)\n",
    "   \n",
    "    # GND Number\n",
    "    gnd_number = xml.xpath(\"marc:datafield[@tag = '024']/marc:subfield[@code = 'a']\", namespaces=ns)\n",
    "   \n",
    "    # Extract Statement of Responsibility\n",
    "    responsibility = xml.xpath(\"marc:datafield[@tag = '245']/marc:subfield[@code = 'c']\", namespaces=ns)\n",
    "   \n",
    "    # Extract Publication Information\n",
    "    publication_info = xml.xpath(\"marc:datafield[@tag = '260']\", namespaces=ns)\n",
    "   \n",
    "    # Extract Frequency and Regularity of Publication\n",
    "    frequency = xml.xpath(\"marc:datafield[@tag = '310']/marc:subfield[@code = 'a']\", namespaces=ns)\n",
    "   \n",
    "    # Extract Subject Access Points\n",
    "    subjects = xml.xpath(\"marc:datafield[@tag = '650']/marc:subfield[@code = 'a' or @code = 'x' or @code = 'v']\", namespaces=ns)\n",
    "   \n",
    "    # Extract Electronic Location and Access\n",
    "    electronic_access = xml.xpath(\"marc:datafield[@tag = '856']/marc:subfield[@code = 'u']\", namespaces=ns)\n",
    "    \n",
    "    # Extract Physical Description\n",
    "    physical_description = xml.xpath(\"marc:datafield[@tag = '300']/marc:subfield[@code = 'a']\", namespaces=ns)\n",
    "    \n",
    "    # Extract Series Statement\n",
    "    series_statement = xml.xpath(\"marc:datafield[@tag = '440']/marc:subfield[@code = 'a']\", namespaces=ns)\n",
    "    \n",
    "    # Extract Other Standard Identifier\n",
    "    other_identifier = xml.xpath(\"marc:datafield[@tag = '024']/marc:subfield[@code = 'a']\", namespaces=ns)\n",
    "    \n",
    "    # Extract Notes\n",
    "    notes = xml.xpath(\"marc:datafield[@tag = '500']/marc:subfield[@code = 'a']\", namespaces=ns)\n",
    "    \n",
    "    # Extract Subject Headings\n",
    "    subjects = xml.xpath(\"marc:datafield[@tag = '650']/marc:subfield[@code = 'a' or @code = 'x' or @code = 'v']\", namespaces=ns)\n",
    "    \n",
    "    # Extract Linking Entry Fields\n",
    "    linking_entry = xml.xpath(\"marc:datafield[starts-with(@tag, '76') or starts-with(@tag, '77') or starts-with(@tag, '78')]\", namespaces=ns)\n",
    "    \n",
    "    # Extract Classification Numbers (Dewey Decimal Classification)\n",
    "    dewey_classification = xml.xpath(\"marc:datafield[@tag = '082']/marc:subfield[@code = 'a']\", namespaces=ns)\n",
    "    \n",
    "    # Extract Classification Numbers (Government Document Classification)\n",
    "    gov_doc_classification = xml.xpath(\"marc:datafield[@tag = '086']/marc:subfield[@code = 'a']\", namespaces=ns)\n",
    "\n",
    "\n",
    "    try:\n",
    "        title = title[0].text\n",
    "        library = library\n",
    "        subtitle = subtitle[0].text if len(subtitle) > 0 else \"\"\n",
    "        category = category[0].text if len(category) > 0 else \"\"\n",
    "        date = date[0].text if len(date) > 0 else \"\"\n",
    "        person = person[0].text if len(person) > 0 else \"\"\n",
    "        ddc = ddc[0].text if len(ddc) > 0 else \"\"\n",
    "        issn = issn[0].text if len(issn) > 0 else \"\"\n",
    "        language = language[0].text if len(language) > 0 else \"\"\n",
    "        gnd_number = gnd_number[0].text if len(gnd_number) > 0 else \"\"\n",
    "        responsibility = responsibility[0].text if len(responsibility) > 0 else \"\"\n",
    "        publication_info = publication_info[0].text if len(publication_info) > 0 else \"\"\n",
    "        frequency = frequency[0].text if len(frequency) > 0 else \"\"\n",
    "        subjects = [subject.text for subject in subjects]\n",
    "        electronic_access = electronic_access[0].text if len(electronic_access) > 0 else \"\"\n",
    "        notes = notes[0].text if len(notes) > 0 else \"\"\n",
    "        physical_description = physical_description[0].text if len(physical_description) > 0 else \"\"\n",
    "        series_statement = series_statement[0].text if len(series_statement) > 0 else \"\"\n",
    "        other_identifier = other_identifier[0].text if len(other_identifier) > 0 else \"\"\n",
    "        linking_entry = [entry.text for entry in linking_entry]\n",
    "        dewey_classification = dewey_classification[0].text if len(dewey_classification) > 0 else \"\"\n",
    "        gov_doc_classification = gov_doc_classification[0].text if len(gov_doc_classification) > 0 else \"\"\n",
    "        subjects = [subject.text for subject in subjects]\n",
    "        \n",
    "        #titel = unicodedata.normalize(\"NFC\", titel)\n",
    "    except:\n",
    "        title = \"unkown\"\n",
    "\n",
    "    meta_dict = {\n",
    "        \"idn\":idn,\n",
    "        \"library\":library,\n",
    "        \"title\":title,\n",
    "        \"subtitle\":subtitle,\n",
    "        \"category\":category,\n",
    "        \"date\":date,\n",
    "        \"person\":person,\n",
    "        'ddc':ddc,\n",
    "        'issn':issn,\n",
    "        'language':language,\n",
    "        'gnd_number':gnd_number,\n",
    "        'responsibility':responsibility,\n",
    "        'publication_info':publication_info,\n",
    "        'frequency':frequency,\n",
    "        'subjects':subjects,\n",
    "        'electronic_access':electronic_access,\n",
    "        'notes':notes,\n",
    "        'physical_description':physical_description,\n",
    "        'series_statement':series_statement,\n",
    "        'other_identifier':other_identifier,\n",
    "        'linking_entry':linking_entry,\n",
    "        'dewey_classification':dewey_classification,\n",
    "        'gov_doc_classification':gov_doc_classification\n",
    "        }\n",
    "\n",
    "    return meta_dict"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "ad1705475c0808d",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "source": [
    "output = [parse_record(record) for record in records]\n",
    "df_test = pd.DataFrame(output)\n",
    "df_test"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "51ced9ebb11fc03e",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "## try to get the data for the zdb and the title Jeshurun",
   "id": "9d5dbe2f6f7737fb",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "zdb_records = dnb_sru(query='tit=Jeschurun', library='zdb', start=1, max_records=1000)\n",
    "print(len(zdb_records), 'Ergebnisse')\n",
    "\n",
    "zdb_output = [parse_record(zdb_record) for zdb_record in zdb_records]\n",
    "zdb_df = pd.DataFrame(zdb_output)\n",
    "zdb_df"
   ],
   "id": "c1a0e63a98a3dc7b",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# Daten zusammenführen",
   "id": "bbba29d4acd5edfe"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# Create an empty DataFrame to store the aggregated data\n",
    "df_aggregated = pd.DataFrame(columns=['idn', 'title', 'subtitle', 'date'])\n",
    "\n",
    "# Group by 'title' and 'subtitle' and aggregate 'idn' and 'date' into lists\n",
    "grouped_data = zdb_df.groupby(['title', 'subtitle']).agg({'idn': list, 'date': lambda x: x.unique().tolist() if len(x.unique()) == 1 else x.tolist()}).reset_index()\n",
    "\n",
    "# Populate the aggregated DataFrame\n",
    "df_aggregated['idn'] = grouped_data['idn']\n",
    "df_aggregated['date'] = grouped_data['date']\n",
    "df_aggregated['title'] = grouped_data['title']\n",
    "df_aggregated['subtitle'] = grouped_data['subtitle']\n",
    "\n",
    "\n",
    "# Display the aggregated DataFrame\n",
    "df_aggregated\n"
   ],
   "id": "f9a66f308ab8fd4",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "### Remove the temporary pickle file",
   "id": "a7d4c4f6a0840617"
  },
  {
   "cell_type": "code",
   "source": [
    "# remove the temp_data.pkl file\n",
    "if os.path.exists('temp_data.pkl'):\n",
    "    os.remove('temp_data.pkl')"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "ed46436dd7361e86",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Load the titles",
   "id": "9559f1c5aca671bf"
  },
  {
   "cell_type": "code",
   "source": [
    "# print Zs_Caption unique values\n",
    "titles = df_metadata['Zs_Caption'].unique()"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "c2baf65fca90417f",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "### Print the titles",
   "id": "189eb83855fdabf7"
  },
  {
   "cell_type": "code",
   "source": "print(titles)",
   "metadata": {
    "collapsed": false
   },
   "id": "a53f011c191cb4ae",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# go through all the titles in the column 'Zs_Caption'\n",
    "# Load an existing temporary DataFrame if it exists\n",
    "\n",
    "try:\n",
    "    temp_df = pd.read_pickle('temp_data.pkl')\n",
    "except FileNotFoundError:\n",
    "    temp_df = pd.DataFrame()\n",
    "    \n",
    "counter = 1\n",
    "length = len(titles)\n",
    "\n",
    "temp_df = pd.DataFrame()\n",
    "library = 'dnb'\n",
    "column_headers = ['library', 'idn',  'title', 'subtitle', 'category', 'date', 'person', 'ddc', 'issn']\n",
    "\n",
    "for title in titles:\n",
    "    # search for the title in the sru interface\n",
    "    print('Counter: ' + str(counter) + ' of ' + str(length))\n",
    "    # calculate the percentage of the progress\n",
    "    percentage = round(counter / length * 100, 2)\n",
    "    print('Progress: ' + str(percentage) + '%')\n",
    "    print('Searching for: ' + title)\n",
    "    records = dnb_sru('tit=' + title, library=library)\n",
    "    print(len(records), 'Ergebnisse')\n",
    "    \n",
    "    if len(records) > 0:\n",
    "\n",
    "        output = [parse_record(record) for record in records]\n",
    "        df_records = pd.DataFrame(output)\n",
    "        temp_df = pd.concat([temp_df, df_records], ignore_index=True)\n",
    "    counter = counter + 1\n",
    "    # Save the temporary DataFrame to a Pickle file\n",
    "    temp_df.to_pickle('temp_data.pkl')"
   ],
   "id": "89d86e14ce0cd2e7",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "source": [
    "# go through all the titles in the column 'Zs_Caption'\n",
    "# Load an existing temporary DataFrame if it exists\n",
    "\n",
    "try:\n",
    "    temp_df = pd.read_pickle('temp_data.pkl')\n",
    "except FileNotFoundError:\n",
    "    temp_df = pd.DataFrame()\n",
    "\n",
    "counter = 1\n",
    "length = len(titles)\n",
    "\n",
    "temp_df = pd.DataFrame()\n",
    "library = 'zdb'\n",
    "\n",
    "for title in titles:\n",
    "    # search for the title in the sru interface\n",
    "    print('Counter: ' + str(counter) + ' of ' + str(length))\n",
    "    # calculate the percentage of the progress\n",
    "    percentage = round(counter / length * 100, 2)\n",
    "    print('Progress: ' + str(percentage) + '%')\n",
    "    print('Searching for: ' + title)\n",
    "    records = dnb_sru('tit=' + title, library=library)\n",
    "    print(len(records), 'Ergebnisse')\n",
    "\n",
    "    if len(records) > 0:\n",
    "        output = [parse_record(record) for record in records]\n",
    "        df_records = pd.DataFrame(output)\n",
    "        temp_df = pd.concat([temp_df, df_records], ignore_index=True)\n",
    "    counter = counter + 1\n",
    "    # Save the temporary DataFrame to a Pickle file\n",
    "    temp_df.to_pickle('temp_data.pkl')"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "811d61eccad9cfb7",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "source": [
    "large_df = pd.DataFrame()\n",
    "# Load the temporary DataFrame from the Pickle file\n",
    "temp_df = pd.read_pickle('temp_data.pkl')\n",
    "\n",
    "# Append the temporary DataFrame to your large DataFrame\n",
    "large_df = pd.concat([large_df, temp_df], ignore_index=True)\n",
    "\n",
    "# remove duplicates\n",
    "large_df.drop_duplicates(subset=['title', 'date'], keep='first', inplace=True)\n",
    "\n",
    "# Save the large DataFrame to a CSV file\n",
    "large_df.to_csv('final_data.csv', index=True, sep=';')\n",
    "\n",
    "large_df.to_excel('final_data.xlsx')"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "77e392a6c84d4a09",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# Ergebnisse bereinigen",
   "id": "b1d4e9e7d5e735e9"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# Daten neu laden\n",
    "\n",
    "df_data = pd.read_csv('final_data.csv', index_col=0, sep=';')\n",
    "df_data.sample(5)"
   ],
   "id": "b3377055e3d2f250",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "df_data.to_csv('final_data_cleaned.csv', index=True, sep=';')\n",
    "df_data.to_excel('final_data_cleaned.xlsx')"
   ],
   "id": "c6d3489d793611a0",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# remove everything which is JUDAICA in the column \"category\"\n",
    "df_data = df_data[df_data['category'] != 'JUDAICA']\n",
    "df_data.sample(5)"
   ],
   "id": "f4bfc8759005b17",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# Remove rows containing \"jud\" or \"israel\" in the \"title\" column\n",
    "df_data = df_data[~df_data['title'].str.contains('jud|Jud|jüd|Jüd|israel|Israel|hebr|Hebr', case=False)]\n",
    "df_data = df_data[~df_data['subtitle'].str.contains('jud|Jud|jüd|Jüd|israel|Israel|hebr|Hebr', case=False)]"
   ],
   "id": "81f1da265460ff8b",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# Define the patterns to search for\n",
    "patterns = ['jüd*', 'jud*', 'Jüd*', 'Jud*', 'israel*', 'Israel*', 'hebr*', 'Hebr*']\n",
    "\n",
    "# Combine the patterns into a single regular expression pattern\n",
    "regex_pattern = '|'.join(patterns)\n",
    "\n",
    "# Create a boolean mask for rows that match the patterns in the \"title\" or \"subtitle\" columns\n",
    "mask = (df_data['title'].str.contains(regex_pattern, case=False, na=False) |\n",
    "        df_data['subtitle'].str.contains(regex_pattern, case=False, na=False))\n",
    "\n",
    "# Filter out the rows that match the patterns\n",
    "df_data = df_data[~mask]\n",
    "\n"
   ],
   "id": "755444f92c34aaea",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "with open(\"journal_titles.txt\", 'r', encoding='utf-8') as file:\n",
    "    lines = file.readlines()\n",
    "    titles = set(line.strip() for line in lines)"
   ],
   "id": "d9015975434bbf13",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "print(len(titles))",
   "id": "32d5ffb5eb22cea1",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "import re\n",
    "\n",
    "# Read the text file and store titles/subtitles in a set\n",
    "with open(\"journal_titles.txt\", 'r', encoding='utf-8') as file:\n",
    "    lines = file.readlines()\n",
    "    titles_to_remove = set(line.strip() for line in lines)\n",
    "\n",
    "# Escape special characters in each pattern and combine them into a single regular expression pattern\n",
    "escaped_patterns = [re.escape(pattern) for pattern in patterns]\n",
    "regex_pattern = '|'.join(escaped_patterns)\n",
    "\n",
    "# Create a boolean mask for rows where either the title or subtitle contains any string from the file\n",
    "mask_title = df_data['title'].str.contains(regex_pattern, case=False, na=False)\n",
    "mask_subtitle = df_data['subtitle'].str.contains(regex_pattern, case=False, na=False)\n",
    "\n",
    "# Filter out the rows that match any string from the file in the title or subtitle\n",
    "df_data = df_data[~(mask_title | mask_subtitle)]\n",
    "\n",
    "# Print or use the resulting DataFrame\n",
    "df_data"
   ],
   "id": "59285c9e8bd20300",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "df_data.to_csv('final_data_rest.csv', index=True, sep=';')\n",
    "df_data.to_excel('final_data_rest.xlsx')"
   ],
   "id": "4fe57b5f252e0891",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "print(len(df_data))",
   "id": "519fa424227dc80b",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "df_data",
   "id": "2421f4a509285bdc",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "# Manuelle Bereinigung\n",
    "Aufgrund der hohen Anzahl an unrelevanten Treffern ist eine manuelle Bereinigung notwendig.\n",
    "Die relevanten Treffer wurden aus der final_data_rest.csv entfernt und als final_data_manuel.csv gespeichert.\n",
    "Die unrelevanten Treffer wurden aufgrund der Masse belassen und werden dann von dem gesamten Bestand abgezogen.\n",
    "\n",
    "Es wurden über 78.000 Treffer gefunden, die manuell bereinigt werden müssen. Davon enthalten viele ein Stichwort wie Jahresbericht, Blätter, etc. und sind somit nicht relevant.\n",
    "\n",
    "Die Bereinigung ergab immerhin noch 1327 relevante Treffer. Das sind über 1000 Treffer mehr als in den vorhandenen Metadaten.\n",
    "\n",
    "Davon sind einige Titel trotzdem doppelt, die aber nicht bereinigt werden. Da sie manchmal verschiedene Untertitel enthalten, in verschiedenen Jahren erschienen (z.B. neue Folgen) sind oder es sich um verschiedene Zeitschriften mit gleichem Titel handelt."
   ],
   "id": "86ee641dd5899ed0"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T21:18:18.985548Z",
     "start_time": "2024-04-29T21:18:18.549047Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df_mask = pd.read_csv('final_data_manual.csv', index_col=0, sep=';')\n",
    "df_data = pd.read_csv('final_data.csv', index_col=0, sep=';')\n",
    "\n",
    "# Remove the manually filtered rows from the original DataFrame\n",
    "df_data = df_data[~df_data['idn'].isin(df_mask['idn'])]\n",
    "df_data.to_csv('final_data_aggregated.csv', index=True, sep=';')"
   ],
   "id": "ba2b78b543f94efe",
   "outputs": [],
   "execution_count": 20
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:36:39.558344Z",
     "start_time": "2024-04-29T19:36:39.555260Z"
    }
   },
   "cell_type": "code",
   "source": "print(len(df_data))",
   "id": "be7474644e16de95",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1327\n"
     ]
    }
   ],
   "execution_count": 4
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:36:40.964340Z",
     "start_time": "2024-04-29T19:36:40.948402Z"
    }
   },
   "cell_type": "code",
   "source": "df_data",
   "id": "ef4ef098c9510517",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "              idn library                                              title  \\\n",
       "0       012981613     zdb  Führer durch die jüdische Gemeindeverwaltung u...   \n",
       "2       015310019     zdb  Führer durch die jüdische Wohlfahrtspflege in ...   \n",
       "4      1147584931     zdb  Actes et conférences de la Société des Études ...   \n",
       "6       015223736     zdb               Jüdischer Almanach für Groß-Rumänien   \n",
       "7      1143848942     zdb                                 Jüdischer Almanach   \n",
       "...           ...     ...                                                ...   \n",
       "78704  1185662448     zdb                              Jüdischer Volksfreund   \n",
       "78705  1177932881     zdb                 Beilage in Wiedergutmachungsfragen   \n",
       "78706  1199950920     zdb  Bericht des Vorstandes der Israelitischen Ster...   \n",
       "78708   015236781     zdb  Gemeindeblatt für die jüdischen Gemeinden Preu...   \n",
       "78710  1196831912     zdb            Illustrirter jüdischer Familienkalender   \n",
       "\n",
       "                            subtitle category          date person  ddc issn  \n",
       "0                                NaN  JUDAICA     1932-1933    NaN  340  NaN  \n",
       "2                                NaN  JUDAICA     1928-1929    NaN  290  NaN  \n",
       "4                                NaN  JUDAICA     1886-1889    NaN  290  NaN  \n",
       "6                                NaN  JUDAICA         1922-    NaN  290  NaN  \n",
       "7                                NaN  JUDAICA     1902-1903    NaN  910  NaN  \n",
       "...                              ...      ...           ...    ...  ...  ...  \n",
       "78704  Monats-Beilage zum \"Israelit\"  JUDAICA          1906    NaN  290  NaN  \n",
       "78705                            NaN  JUDAICA     1947-1947    NaN  290  NaN  \n",
       "78706               für das Jahr ...      NaN  [1895?-1905]    NaN  360  NaN  \n",
       "78708                            NaN  JUDAICA     1934-1937    NaN  070  NaN  \n",
       "78710               für das Jahr ...      NaN     1882-1907    NaN  050  NaN  \n",
       "\n",
       "[1327 rows x 9 columns]"
      ],
      "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>idn</th>\n",
       "      <th>library</th>\n",
       "      <th>title</th>\n",
       "      <th>subtitle</th>\n",
       "      <th>category</th>\n",
       "      <th>date</th>\n",
       "      <th>person</th>\n",
       "      <th>ddc</th>\n",
       "      <th>issn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>012981613</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Führer durch die jüdische Gemeindeverwaltung u...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1932-1933</td>\n",
       "      <td>NaN</td>\n",
       "      <td>340</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>015310019</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Führer durch die jüdische Wohlfahrtspflege in ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1928-1929</td>\n",
       "      <td>NaN</td>\n",
       "      <td>290</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1147584931</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Actes et conférences de la Société des Études ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1886-1889</td>\n",
       "      <td>NaN</td>\n",
       "      <td>290</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>015223736</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Jüdischer Almanach für Groß-Rumänien</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1922-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>290</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1143848942</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Jüdischer Almanach</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1902-1903</td>\n",
       "      <td>NaN</td>\n",
       "      <td>910</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78704</th>\n",
       "      <td>1185662448</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Jüdischer Volksfreund</td>\n",
       "      <td>Monats-Beilage zum \"Israelit\"</td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1906</td>\n",
       "      <td>NaN</td>\n",
       "      <td>290</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78705</th>\n",
       "      <td>1177932881</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Beilage in Wiedergutmachungsfragen</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1947-1947</td>\n",
       "      <td>NaN</td>\n",
       "      <td>290</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78706</th>\n",
       "      <td>1199950920</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Bericht des Vorstandes der Israelitischen Ster...</td>\n",
       "      <td>für das Jahr ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[1895?-1905]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>360</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78708</th>\n",
       "      <td>015236781</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Gemeindeblatt für die jüdischen Gemeinden Preu...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1934-1937</td>\n",
       "      <td>NaN</td>\n",
       "      <td>070</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78710</th>\n",
       "      <td>1196831912</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Illustrirter jüdischer Familienkalender</td>\n",
       "      <td>für das Jahr ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1882-1907</td>\n",
       "      <td>NaN</td>\n",
       "      <td>050</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1327 rows × 9 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 5
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# Daten zusammenführen",
   "id": "500184690942f44f"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Vorbereiten der Daten",
   "id": "c259262fd5d67bce"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T21:04:02.358859Z",
     "start_time": "2024-04-29T21:04:02.352522Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# remove all NaN values and replace them with an empty string\n",
    "df_data.fillna('', inplace=True)"
   ],
   "id": "55d0918f05e82a92",
   "outputs": [],
   "execution_count": 7
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T21:04:02.699492Z",
     "start_time": "2024-04-29T21:04:02.676838Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# split the date column into two columns\n",
    "df_data['date'] = df_data['date'].str.replace('$b', '-')\n",
    "df_data['date'] = df_data['date'].str.replace('oder', '-') # Ist zwar nicht richtig, aber so ist es einfacher. Alternativ splitten\n",
    "\n",
    "df_data[['year_from', 'year_to']] = df_data['date'].str.split('-', expand=True)\n",
    "# remove all \"?\" from the date columns\n",
    "df_data['year_from'] = df_data['year_from'].str.replace('?', '')\n",
    "df_data['year_from'] = df_data['year_from'].str.replace('None', '')\n",
    "df_data['year_from'] = df_data['year_from'].str.replace('[', '')\n",
    "df_data['year_from'] = df_data['year_from'].str.replace(']', '')\n",
    "# convert to integer\n",
    "df_data['year_from'] = pd.to_numeric(df_data['year_from'], errors='coerce').fillna(0).astype(int) # Nicht optimal, da 0 ein gültiges Jahr sein kann\n",
    "\n",
    "df_data['year_to'] = df_data['year_to'].str.replace('?', '')\n",
    "df_data['year_to'] = df_data['year_to'].str.replace('None', '')\n",
    "df_data['year_to'] = df_data['year_to'].str.replace('[', '')\n",
    "df_data['year_to'] = df_data['year_to'].str.replace(']', '')\n",
    "# convert to integer\n",
    "df_data['year_to'] = pd.to_numeric(df_data['year_to'], errors='coerce').fillna(0).astype(int) # Nicht optimal, da 0 ein gültiges Jahr sein kann\n"
   ],
   "id": "6970465a2a4514a9",
   "outputs": [],
   "execution_count": 8
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T21:04:04.034830Z",
     "start_time": "2024-04-29T21:04:04.031920Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# if year_to is nan or NaT then set it to year_from\n",
    "df_data['year_to'] = df_data['year_to'].fillna(df_data['year_from'])"
   ],
   "id": "c20c35ceeb687ba1",
   "outputs": [],
   "execution_count": 9
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:37:37.988190Z",
     "start_time": "2024-04-29T19:37:37.977977Z"
    }
   },
   "cell_type": "code",
   "source": "df_data",
   "id": "ea65e1739af4c63d",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "              idn library                                              title  \\\n",
       "0       012981613     zdb  Führer durch die jüdische Gemeindeverwaltung u...   \n",
       "2       015310019     zdb  Führer durch die jüdische Wohlfahrtspflege in ...   \n",
       "4      1147584931     zdb  Actes et conférences de la Société des Études ...   \n",
       "6       015223736     zdb               Jüdischer Almanach für Groß-Rumänien   \n",
       "7      1143848942     zdb                                 Jüdischer Almanach   \n",
       "...           ...     ...                                                ...   \n",
       "78704  1185662448     zdb                              Jüdischer Volksfreund   \n",
       "78705  1177932881     zdb                 Beilage in Wiedergutmachungsfragen   \n",
       "78706  1199950920     zdb  Bericht des Vorstandes der Israelitischen Ster...   \n",
       "78708   015236781     zdb  Gemeindeblatt für die jüdischen Gemeinden Preu...   \n",
       "78710  1196831912     zdb            Illustrirter jüdischer Familienkalender   \n",
       "\n",
       "                            subtitle category          date person  ddc issn  \\\n",
       "0                                     JUDAICA     1932-1933         340        \n",
       "2                                     JUDAICA     1928-1929         290        \n",
       "4                                     JUDAICA     1886-1889         290        \n",
       "6                                     JUDAICA         1922-         290        \n",
       "7                                     JUDAICA     1902-1903         910        \n",
       "...                              ...      ...           ...    ...  ...  ...   \n",
       "78704  Monats-Beilage zum \"Israelit\"  JUDAICA          1906         290        \n",
       "78705                                 JUDAICA     1947-1947         290        \n",
       "78706               für das Jahr ...           [1895?-1905]         360        \n",
       "78708                                 JUDAICA     1934-1937         070        \n",
       "78710               für das Jahr ...              1882-1907         050        \n",
       "\n",
       "       year_from  year_to  \n",
       "0           1932     1933  \n",
       "2           1928     1929  \n",
       "4           1886     1889  \n",
       "6           1922        0  \n",
       "7           1902     1903  \n",
       "...          ...      ...  \n",
       "78704       1906        0  \n",
       "78705       1947     1947  \n",
       "78706       1895     1905  \n",
       "78708       1934     1937  \n",
       "78710       1882     1907  \n",
       "\n",
       "[1327 rows x 11 columns]"
      ],
      "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>idn</th>\n",
       "      <th>library</th>\n",
       "      <th>title</th>\n",
       "      <th>subtitle</th>\n",
       "      <th>category</th>\n",
       "      <th>date</th>\n",
       "      <th>person</th>\n",
       "      <th>ddc</th>\n",
       "      <th>issn</th>\n",
       "      <th>year_from</th>\n",
       "      <th>year_to</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>012981613</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Führer durch die jüdische Gemeindeverwaltung u...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1932-1933</td>\n",
       "      <td></td>\n",
       "      <td>340</td>\n",
       "      <td></td>\n",
       "      <td>1932</td>\n",
       "      <td>1933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>015310019</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Führer durch die jüdische Wohlfahrtspflege in ...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1928-1929</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1928</td>\n",
       "      <td>1929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1147584931</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Actes et conférences de la Société des Études ...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1886-1889</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1886</td>\n",
       "      <td>1889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>015223736</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Jüdischer Almanach für Groß-Rumänien</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1922-</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1922</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1143848942</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Jüdischer Almanach</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1902-1903</td>\n",
       "      <td></td>\n",
       "      <td>910</td>\n",
       "      <td></td>\n",
       "      <td>1902</td>\n",
       "      <td>1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78704</th>\n",
       "      <td>1185662448</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Jüdischer Volksfreund</td>\n",
       "      <td>Monats-Beilage zum \"Israelit\"</td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1906</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1906</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78705</th>\n",
       "      <td>1177932881</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Beilage in Wiedergutmachungsfragen</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1947-1947</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1947</td>\n",
       "      <td>1947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78706</th>\n",
       "      <td>1199950920</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Bericht des Vorstandes der Israelitischen Ster...</td>\n",
       "      <td>für das Jahr ...</td>\n",
       "      <td></td>\n",
       "      <td>[1895?-1905]</td>\n",
       "      <td></td>\n",
       "      <td>360</td>\n",
       "      <td></td>\n",
       "      <td>1895</td>\n",
       "      <td>1905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78708</th>\n",
       "      <td>015236781</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Gemeindeblatt für die jüdischen Gemeinden Preu...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1934-1937</td>\n",
       "      <td></td>\n",
       "      <td>070</td>\n",
       "      <td></td>\n",
       "      <td>1934</td>\n",
       "      <td>1937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78710</th>\n",
       "      <td>1196831912</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Illustrirter jüdischer Familienkalender</td>\n",
       "      <td>für das Jahr ...</td>\n",
       "      <td></td>\n",
       "      <td>1882-1907</td>\n",
       "      <td></td>\n",
       "      <td>050</td>\n",
       "      <td></td>\n",
       "      <td>1882</td>\n",
       "      <td>1907</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1327 rows × 11 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 8
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Statistische Auswertungen\n",
    "\n",
    "Welche Daten konnten vervollständigt werden\n"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "e5d7e9518e8721e"
  },
  {
   "cell_type": "markdown",
   "source": [
    "# ZDB (Zeitschriftendatenbank)\n"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "deb60049d8fca832"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "",
   "id": "4835aa44f044c32b"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# Interate over the titles",
   "id": "d9d4e6c9f8012ffb"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# go through all the titles\n",
    "\n",
    "df_records_per_title = pd.DataFrame(columns=['title', 'number_of_records'])\n",
    "zdb_data = pd.DataFrame()\n",
    "\n",
    "for title in titles:\n",
    "    query = f'tit={title}'\n",
    "    # fetch the number of the results\n",
    "    records = dnb_sru(query=query, library='zdb', start=1, max_records=1000)\n",
    "    length_records = len(records)\n",
    "    print(f'Tile: {title} Results: {length_records}')\n",
    "    # append to the dataframe\n",
    "    df_records_per_title =  df_records_per_title.append({'title': title, 'number_of_records': length_records}, ignore_index=True)\n",
    "    if 0 < length_records <= 100:\n",
    "         output = [parse_record(record) for record in records]\n",
    "         # append the records to the zdb_data\n",
    "         zdb_data = zdb_data.append(output, ignore_index=True)\n",
    "        \n",
    "# save the number of records to csv\n",
    "df_records_per_title.to_csv('number_records.csv')\n",
    "df_records_per_title"
   ],
   "id": "feff92ea3c8181d7",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "### list titles with no values\n",
    "Here we have no hits. That is fine."
   ],
   "id": "130391ac0b5e3e83"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "titles_with_zero_records = df_records_per_title[df_records_per_title['number_of_records'] == 0]['title']\n",
    "\n",
    "# Print the titles\n",
    "print(\"Titles with 0 number_of_records:\")\n",
    "for title in titles_with_zero_records:\n",
    "    print(title)\n"
   ],
   "id": "c7571d4c5eaf0b45",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "### list tiles with high values\n",
    "There are to much records for the title found. This needs manual inspection."
   ],
   "id": "ad721ccac24dfa3b"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "df_records_per_title[df_records_per_title['number_of_records'] >= 100]\n",
   "id": "4de54038860e64e2",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "",
   "id": "f836f751a261c59d"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "## XXX Search for subtitles\n",
    "We will check for the subtitles in the ZDB\n",
    "Es gibt keinen direkten Eintrag für die Untertitel in der ZDB.\n",
    "Daher prüfen wir den vollständigen Titel (tst)."
   ],
   "id": "636684212f82f9ab"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "df_records_per_title = pd.DataFrame(columns=['title', 'number_of_records'])\n",
    "zdb_data = pd.DataFrame()\n",
    "\n",
    "for title in titles:\n",
    "    query = f'tst={title}'\n",
    "    # fetch the number of the results\n",
    "    records = dnb_sru(query=query, library='zdb', start=1, max_records=1000)\n",
    "    length_records = len(records)\n",
    "    print(f'Tile: {title} Results: {length_records}')\n",
    "    # append to the dataframe\n",
    "    df_records_per_title =  df_records_per_title.append({'title': title, 'number_of_records': length_records}, ignore_index=True)\n",
    "    if 0 < length_records <= 100:\n",
    "        output = [parse_record(record) for record in records]\n",
    "        # append the records to the zdb_data\n",
    "        zdb_data = zdb_data.append(output, ignore_index=True)\n",
    "\n",
    "# save the number of records to csv\n",
    "df_records_per_title.to_csv('number_records.csv')\n",
    "df_records_per_title"
   ],
   "id": "4dabd15000fc85ff",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# print the records\n",
   "id": "f156df3bbcaf4114"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "zdb_data\n",
   "id": "2dde58ba644e333e",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## List all the entries which are available in CM",
   "id": "1e1e452f7383123a"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "cm_data = zdb_data[zdb_data['electronic_access'].str.contains('http://sammlungen.ub.uni-frankfurt.de/cm/')]\n",
    "cm_data"
   ],
   "id": "8f0564ff7c5c0e02",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "cm_data['title'].count()",
   "id": "9b721c074a825049",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "zdb_data['title'].count()",
   "id": "4d06e1bcbdb51429",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "zdb_data['title'].count() - cm_data['title'].count()",
   "id": "3b2cd87ad8253563",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## XXX Check if the titles with no records can be found ",
   "id": "fed1e6e3a5ab7ed0"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Save the data to a csv file",
   "id": "31ec4a0396c734e7"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "zdb_data.to_csv('zdb_data.csv')",
   "id": "cb7ab8b77ccea450",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Criteria 2: Dates",
   "id": "fbbf38a76b7d58c4"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# Unmatched titles\n",
   "id": "efff9575fbd15e11"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# clean dates in df_metadata\n",
    "\n",
    "# Check if values in 'date_column' match the yyyy-mm-dd format\n",
    "date_format_regex = r'^\\d{4}-\\d{2}-\\d{2}$'\n",
    "is_valid_date = df_metadata['Datum'].str.match(date_format_regex)\n",
    "\n",
    "# Filter the DataFrame to get rows where 'date_column' contains invalid dates\n",
    "invalid_dates_df = df_metadata[~is_valid_date]  # Inverting the boolean mask with ~\n",
    "\n",
    "# Print the rows with invalid dates\n",
    "print(\"Rows with invalid dates:\")\n",
    "invalid_dates_df"
   ],
   "id": "e270abbeec0b8fba",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "df_metadata['Datum'] = pd.to_datetime(df_metadata['Datum'], errors='coerce')\n",
    "\n",
    "# Filter the DataFrame to get rows where 'date_column' is before '1750-01-01' or is null\n",
    "filtered_df = df_metadata[(df_metadata['Datum'] < '1750-01-01') | df_metadata['Datum'].isnull()]\n",
    "\n",
    "# Print the filtered DataFrame\n",
    "print(\"Filtered DataFrame:\")\n",
    "filtered_df"
   ],
   "id": "1a85d18037c70192",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "print(df_metadata['Volume_Caption'].unique().tolist())\n",
   "id": "469ce06a9e1c1bc9",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# load the cleaned data for testing\n",
    "df_metadata = pd.read_csv(\"metadata/CM_Seiten_Metadaten_cleaned.csv\")\n",
    "df_metadata.head(5)"
   ],
   "id": "5547324921e661a8",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# Replace \"(null)\" strings with pd.NaT\n",
    "df_metadata['Datum'] = df_metadata['Datum'].replace('(null)', pd.NaT)\n",
    "\n",
    "# Convert 'date' column to datetime type\n",
    "df_metadata['Datum'] = pd.to_datetime(df_metadata['Datum'], errors='coerce')\n",
    "\n",
    "# Group by 'title' and aggregate 'date' to find the minimum and maximum dates\n",
    "grouped_df = df_metadata.groupby('Zs_Caption')['Datum'].agg(['min', 'max']).reset_index()\n",
    "\n",
    "# Rename the columns to 'date_from' and 'date_to'\n",
    "grouped_df.rename(columns={'min': 'date_from', 'max': 'date_to'}, inplace=True)\n",
    "\n",
    "grouped_df['year_from'] = grouped_df['date_from'].dt.year.astype('Int64')\n",
    "grouped_df['year_to'] = grouped_df['date_to'].dt.year.astype('Int64')\n",
    "\n",
    "# Print the result\n",
    "grouped_df"
   ],
   "id": "b06a9b35195601cd",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "print(len(df_metadata['Zs_Caption'].unique()))",
   "id": "3dbc0a00f703b02c",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "print(len(df_metadata['Zs_Caption'].unique()))",
   "id": "7f61276f17de4ddd",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "# XXX Ist in den bereinigten Daten die Spalte 'Zs_Caption' schon bereinigt von Duplikaten?\n",
    "Besser ohne Duplikate checken\n",
    "   "
   ],
   "id": "386eb76e847b2fe6"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# Clean the Volume_Caption column\n",
    "print(df_metadata['Volume_Caption'].unique().tolist())\n",
    "# Damit kann man eigentlich nicht arbeiten"
   ],
   "id": "4cd62a3dd9eb0af9",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# Clean the Heft_Caption column\n",
    "print(df_metadata['Heft_Caption'].unique().tolist())"
   ],
   "id": "55b655958b0fb00",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "import re\n",
    "import numpy as np\n",
    "\n",
    "def extract_year(text):\n",
    "    if isinstance(text, str):\n",
    "        pattern_heft = r'(Heft\\s)(\\d{4})'  # Match 4 digits after \"Heft \"\n",
    "        text = re.remove(pattern_heft, text)\n",
    "        \n",
    "        pattern = r'(?<=Heft\\s)(\\d{4}(?:-\\d{4})?)|(?<=\\()(\\d{4}(?:-\\d{4})?)(?=\\))'  # Match 4 digits or yyyy-yyyy range after \"Heft \" or within parentheses\n",
    "        matches = re.findall(pattern, text)\n",
    "        if matches:\n",
    "            return matches[-1]  # Return the last match found\n",
    "    return np.nan\n",
    "\n",
    "df_metadata['Volume_Year'] = df_metadata['Volume_Caption'].apply(extract_year)\n",
    "df_metadata"
   ],
   "id": "80668fe9fdb6bffb",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "df_metadata['Heft_Year'] = df_metadata['Heft_Caption'].apply(extract_year)\n",
    "df_metadata"
   ],
   "id": "167ba62c991951e0",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "print(df_metadata['Volume_Year'].unique().tolist())",
   "id": "25152be9901b612c",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "",
   "id": "fb45a124ad12396b"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "print(df_metadata['Volume_Year'].dtype)",
   "id": "a086c34db0ce7c71",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# convert the year if it is bigger than 5000 from Hebrew to gregorian\n",
    "import pandas as pd\n",
    "from jewish import JewishDate\n",
    "# Hebcal does not install\n",
    "\n",
    "# Convert non-missing values in the \"Volume_Year\" column to integers\n",
    "df_metadata['Volume_Year'] = pd.to_numeric(df_metadata['Volume_Year'], errors='coerce')\n",
    "\n",
    "def convert_to_gregorian(hebrew_year):\n",
    "    # Create a JewishDate object for the Hebrew year\n",
    "    jewish_date = JewishDate(year=hebrew_year, month=1, day=1)\n",
    "    # Convert JewishDate object to Gregorian date\n",
    "    gregorian_date = jewish_date.to_date()\n",
    "    # Extract the Gregorian year\n",
    "    gregorian_year = gregorian_date.year\n",
    "    return gregorian_year\n",
    "\n",
    "df_metadata['Volume_Year'].fillna(0, inplace=True)\n",
    "\n",
    "df_metadata.loc[df_metadata['Volume_Year'] > 5000, 'Volume_Year'] = df_metadata.loc[df_metadata['Volume_Year'] > 5000, 'Volume_Year'].apply(convert_to_gregorian)\n",
    "\n",
    "print(df_metadata)\n"
   ],
   "id": "6db595eb12a7d9d8",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "print(df_metadata['Heft_Year'].unique().tolist())",
   "id": "a9b1decd8741e6f1",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# Matching",
   "id": "5f626e4293809b81"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Load the data",
   "id": "52a9b1dfa9c047f0"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:40:27.618632Z",
     "start_time": "2024-04-29T19:40:27.602919Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# convert the year_from and the year_to in df_data to datetime\n",
    "df_data['year_from'] = pd.to_datetime(df_data['year_from'], format='%Y', errors='coerce')\n",
    "df_data['year_to'] = pd.to_datetime(df_data['year_to'], format='%Y', errors='coerce')\n"
   ],
   "id": "53b8bb6e33ae6288",
   "outputs": [],
   "execution_count": 14
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T21:03:32.631021Z",
     "start_time": "2024-04-29T21:03:32.610609Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata = pd.read_csv('metadata/CM_Seiten_Metadaten_aggregated.csv', sep='\\t')",
   "id": "e8a60266d51f8156",
   "outputs": [],
   "execution_count": 5
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Test the data",
   "id": "f6e98095629d2381"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:40:45.433879Z",
     "start_time": "2024-04-29T19:40:45.426625Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata.sample(5)",
   "id": "fba170e464e917f4",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                            Zs_Caption  Year_From  Year_To  \\\n",
       "13                    Antisemitisches Jahrbuch für ...     1847.0   1922.0   \n",
       "89                                         Dibre Emeth     1851.0   1939.0   \n",
       "495                                            Blätter        NaN      NaN   \n",
       "34   Bericht über die Jacobson-Schule, Realschule m...     1890.0   1890.0   \n",
       "324                        Freie jüdische Lehrerstimme        NaN      NaN   \n",
       "\n",
       "      Date_From     Date_To  \n",
       "13   1900-01-01  1903-01-01  \n",
       "89   1845-01-01  1906-01-01  \n",
       "495  1915-03-01  1921-12-22  \n",
       "34   1871-01-01  1930-01-01  \n",
       "324  1912-03-15  1920-10-15  "
      ],
      "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>Zs_Caption</th>\n",
       "      <th>Year_From</th>\n",
       "      <th>Year_To</th>\n",
       "      <th>Date_From</th>\n",
       "      <th>Date_To</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Antisemitisches Jahrbuch für ...</td>\n",
       "      <td>1847.0</td>\n",
       "      <td>1922.0</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>1903-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>Dibre Emeth</td>\n",
       "      <td>1851.0</td>\n",
       "      <td>1939.0</td>\n",
       "      <td>1845-01-01</td>\n",
       "      <td>1906-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>495</th>\n",
       "      <td>Blätter</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1915-03-01</td>\n",
       "      <td>1921-12-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Bericht über die Jacobson-Schule, Realschule m...</td>\n",
       "      <td>1890.0</td>\n",
       "      <td>1890.0</td>\n",
       "      <td>1871-01-01</td>\n",
       "      <td>1930-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>Freie jüdische Lehrerstimme</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1912-03-15</td>\n",
       "      <td>1920-10-15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 16
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T20:53:11.720835Z",
     "start_time": "2024-04-29T20:53:11.710435Z"
    }
   },
   "cell_type": "code",
   "source": "len(df_data)",
   "id": "154601270e8e0e9d",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1327"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 93
  },
  {
   "metadata": {},
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "              idn library                                              title  \\\n",
       "0       012981613     zdb  Führer durch die jüdische Gemeindeverwaltung u...   \n",
       "2       015310019     zdb  Führer durch die jüdische Wohlfahrtspflege in ...   \n",
       "4      1147584931     zdb  Actes et conférences de la Société des Études ...   \n",
       "6       015223736     zdb               Jüdischer Almanach für Groß-Rumänien   \n",
       "7      1143848942     zdb                                 Jüdischer Almanach   \n",
       "...           ...     ...                                                ...   \n",
       "78704  1185662448     zdb                              Jüdischer Volksfreund   \n",
       "78705  1177932881     zdb                 Beilage in Wiedergutmachungsfragen   \n",
       "78706  1199950920     zdb  Bericht des Vorstandes der Israelitischen Ster...   \n",
       "78708   015236781     zdb  Gemeindeblatt für die jüdischen Gemeinden Preu...   \n",
       "78710  1196831912     zdb            Illustrirter jüdischer Familienkalender   \n",
       "\n",
       "                            subtitle category          date person  ddc issn  \\\n",
       "0                                     JUDAICA     1932-1933         340        \n",
       "2                                     JUDAICA     1928-1929         290        \n",
       "4                                     JUDAICA     1886-1889         290        \n",
       "6                                     JUDAICA         1922-         290        \n",
       "7                                     JUDAICA     1902-1903         910        \n",
       "...                              ...      ...           ...    ...  ...  ...   \n",
       "78704  Monats-Beilage zum \"Israelit\"  JUDAICA          1906         290        \n",
       "78705                                 JUDAICA     1947-1947         290        \n",
       "78706               für das Jahr ...           [1895?-1905]         360        \n",
       "78708                                 JUDAICA     1934-1937         070        \n",
       "78710               für das Jahr ...              1882-1907         050        \n",
       "\n",
       "       year_from    year_to  \n",
       "0     1932-01-01 1933-01-01  \n",
       "2     1928-01-01 1929-01-01  \n",
       "4     1886-01-01 1889-01-01  \n",
       "6     1922-01-01 1922-01-01  \n",
       "7     1902-01-01 1903-01-01  \n",
       "...          ...        ...  \n",
       "78704 1906-01-01 1906-01-01  \n",
       "78705 1947-01-01 1947-01-01  \n",
       "78706 1895-01-01 1905-01-01  \n",
       "78708 1934-01-01 1937-01-01  \n",
       "78710 1882-01-01 1907-01-01  \n",
       "\n",
       "[1327 rows x 11 columns]"
      ],
      "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>idn</th>\n",
       "      <th>library</th>\n",
       "      <th>title</th>\n",
       "      <th>subtitle</th>\n",
       "      <th>category</th>\n",
       "      <th>date</th>\n",
       "      <th>person</th>\n",
       "      <th>ddc</th>\n",
       "      <th>issn</th>\n",
       "      <th>year_from</th>\n",
       "      <th>year_to</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>012981613</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Führer durch die jüdische Gemeindeverwaltung u...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1932-1933</td>\n",
       "      <td></td>\n",
       "      <td>340</td>\n",
       "      <td></td>\n",
       "      <td>1932-01-01</td>\n",
       "      <td>1933-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>015310019</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Führer durch die jüdische Wohlfahrtspflege in ...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1928-1929</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1928-01-01</td>\n",
       "      <td>1929-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1147584931</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Actes et conférences de la Société des Études ...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1886-1889</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1886-01-01</td>\n",
       "      <td>1889-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>015223736</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Jüdischer Almanach für Groß-Rumänien</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1922-</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1922-01-01</td>\n",
       "      <td>1922-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1143848942</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Jüdischer Almanach</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1902-1903</td>\n",
       "      <td></td>\n",
       "      <td>910</td>\n",
       "      <td></td>\n",
       "      <td>1902-01-01</td>\n",
       "      <td>1903-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78704</th>\n",
       "      <td>1185662448</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Jüdischer Volksfreund</td>\n",
       "      <td>Monats-Beilage zum \"Israelit\"</td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1906</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1906-01-01</td>\n",
       "      <td>1906-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78705</th>\n",
       "      <td>1177932881</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Beilage in Wiedergutmachungsfragen</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1947-1947</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1947-01-01</td>\n",
       "      <td>1947-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78706</th>\n",
       "      <td>1199950920</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Bericht des Vorstandes der Israelitischen Ster...</td>\n",
       "      <td>für das Jahr ...</td>\n",
       "      <td></td>\n",
       "      <td>[1895?-1905]</td>\n",
       "      <td></td>\n",
       "      <td>360</td>\n",
       "      <td></td>\n",
       "      <td>1895-01-01</td>\n",
       "      <td>1905-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78708</th>\n",
       "      <td>015236781</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Gemeindeblatt für die jüdischen Gemeinden Preu...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1934-1937</td>\n",
       "      <td></td>\n",
       "      <td>070</td>\n",
       "      <td></td>\n",
       "      <td>1934-01-01</td>\n",
       "      <td>1937-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78710</th>\n",
       "      <td>1196831912</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Illustrirter jüdischer Familienkalender</td>\n",
       "      <td>für das Jahr ...</td>\n",
       "      <td></td>\n",
       "      <td>1882-1907</td>\n",
       "      <td></td>\n",
       "      <td>050</td>\n",
       "      <td></td>\n",
       "      <td>1882-01-01</td>\n",
       "      <td>1907-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1327 rows × 11 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 42,
   "source": "df_data",
   "id": "eda45d80e68e5c5"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Merge the titles",
   "id": "a069592e5291fc81"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Criteria 1: Title or subtitle",
   "id": "e173ce63a99a1812"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                            Zs_Caption  Year_From  Year_To  \\\n",
       "0    ... Bericht der Jüdischen Haushaltungsschule z...     1835.0   1835.0   \n",
       "1    ... Bericht ueber den Verein für Westfalen und...     1932.0   1932.0   \n",
       "2    ... Bericht ueber den Verein für die Provinz W...     1871.0   1871.0   \n",
       "3    ... Jahresbericht der Israelitischen Waisenans...     1836.0   1836.0   \n",
       "4    ... Jahresbericht der Jüdischen Frauenvereinig...     1870.0   1870.0   \n",
       "..                                                 ...        ...      ...   \n",
       "993  Jüdisches Gemeindeblatt für den Verband der Ku...        NaN      NaN   \n",
       "994  Jüdisches Gemeindeblatt für den Verband der Ku...        NaN      NaN   \n",
       "995                                            Aḥiasaf        NaN      NaN   \n",
       "996                                            Aḥiasaf        NaN      NaN   \n",
       "997               Séance du comité de direction du ...        NaN      NaN   \n",
       "\n",
       "      Date_From     Date_To         idn library  \\\n",
       "0    1821-01-01  1910-01-01         NaN     NaN   \n",
       "1    1836-01-01  1836-01-01         NaN     NaN   \n",
       "2    1835-01-01  1835-01-01         NaN     NaN   \n",
       "3    1915-01-01  1917-01-01         NaN     NaN   \n",
       "4    1855-01-01  1922-01-01  1185652604     zdb   \n",
       "..          ...         ...         ...     ...   \n",
       "993  1937-08-01  1938-11-01   010269754     zdb   \n",
       "994  1937-08-01  1938-11-01   989711102     zdb   \n",
       "995  1893-01-01  1922-01-01  1201291062     zdb   \n",
       "996  1893-01-01  1922-01-01   010120858     zdb   \n",
       "997  1907-01-01  1914-01-01         NaN     NaN   \n",
       "\n",
       "                                                 title  \\\n",
       "0                                                  NaN   \n",
       "1                                                  NaN   \n",
       "2                                                  NaN   \n",
       "3                                                  NaN   \n",
       "4    ... Jahresbericht der Jüdischen Frauenvereinig...   \n",
       "..                                                 ...   \n",
       "993  Jüdisches Gemeindeblatt für den Verband der Ku...   \n",
       "994  Jüdisches Gemeindeblatt für den Verband der Ku...   \n",
       "995                                            Aḥiasaf   \n",
       "996                                            Aḥiasaf   \n",
       "997                                                NaN   \n",
       "\n",
       "                                              subtitle category         date  \\\n",
       "0                                                  NaN      NaN          NaN   \n",
       "1                                                  NaN      NaN          NaN   \n",
       "2                                                  NaN      NaN          NaN   \n",
       "3                                                  NaN      NaN          NaN   \n",
       "4                                                       JUDAICA         2018   \n",
       "..                                                 ...      ...          ...   \n",
       "993                                                     JUDAICA    1937-1938   \n",
       "994                                                     JUDAICA    1937-1937   \n",
       "995  luaḥ-ʿam sifruti ṿe-shimushi, ʿim temunot ṿe-t...  JUDAICA  [1893-1922]   \n",
       "996  luaḥ-ʿam sifruti ṿe-shimushi, ʿim temunot ṿe-t...  JUDAICA    1893-1924   \n",
       "997                                                NaN      NaN          NaN   \n",
       "\n",
       "    person  ddc issn  year_from    year_to  \n",
       "0      NaN  NaN  NaN        NaT        NaT  \n",
       "1      NaN  NaN  NaN        NaT        NaT  \n",
       "2      NaN  NaN  NaN        NaT        NaT  \n",
       "3      NaN  NaN  NaN        NaT        NaT  \n",
       "4           290      2018-01-01 2018-01-01  \n",
       "..     ...  ...  ...        ...        ...  \n",
       "993         290      1937-01-01 1938-01-01  \n",
       "994         070      1937-01-01 1937-01-01  \n",
       "995         290      1893-01-01 1922-01-01  \n",
       "996         290      1893-01-01 1924-01-01  \n",
       "997    NaN  NaN  NaN        NaT        NaT  \n",
       "\n",
       "[998 rows x 16 columns]"
      ],
      "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>Zs_Caption</th>\n",
       "      <th>Year_From</th>\n",
       "      <th>Year_To</th>\n",
       "      <th>Date_From</th>\n",
       "      <th>Date_To</th>\n",
       "      <th>idn</th>\n",
       "      <th>library</th>\n",
       "      <th>title</th>\n",
       "      <th>subtitle</th>\n",
       "      <th>category</th>\n",
       "      <th>date</th>\n",
       "      <th>person</th>\n",
       "      <th>ddc</th>\n",
       "      <th>issn</th>\n",
       "      <th>year_from</th>\n",
       "      <th>year_to</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>... Bericht der Jüdischen Haushaltungsschule z...</td>\n",
       "      <td>1835.0</td>\n",
       "      <td>1835.0</td>\n",
       "      <td>1821-01-01</td>\n",
       "      <td>1910-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>... Bericht ueber den Verein für Westfalen und...</td>\n",
       "      <td>1932.0</td>\n",
       "      <td>1932.0</td>\n",
       "      <td>1836-01-01</td>\n",
       "      <td>1836-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>1871.0</td>\n",
       "      <td>1871.0</td>\n",
       "      <td>1835-01-01</td>\n",
       "      <td>1835-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>... Jahresbericht der Israelitischen Waisenans...</td>\n",
       "      <td>1836.0</td>\n",
       "      <td>1836.0</td>\n",
       "      <td>1915-01-01</td>\n",
       "      <td>1917-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>... Jahresbericht der Jüdischen Frauenvereinig...</td>\n",
       "      <td>1870.0</td>\n",
       "      <td>1870.0</td>\n",
       "      <td>1855-01-01</td>\n",
       "      <td>1922-01-01</td>\n",
       "      <td>1185652604</td>\n",
       "      <td>zdb</td>\n",
       "      <td>... Jahresbericht der Jüdischen Frauenvereinig...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>2018</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>2018-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>993</th>\n",
       "      <td>Jüdisches Gemeindeblatt für den Verband der Ku...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1937-08-01</td>\n",
       "      <td>1938-11-01</td>\n",
       "      <td>010269754</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Jüdisches Gemeindeblatt für den Verband der Ku...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1937-1938</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1937-01-01</td>\n",
       "      <td>1938-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>994</th>\n",
       "      <td>Jüdisches Gemeindeblatt für den Verband der Ku...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1937-08-01</td>\n",
       "      <td>1938-11-01</td>\n",
       "      <td>989711102</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Jüdisches Gemeindeblatt für den Verband der Ku...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1937-1937</td>\n",
       "      <td></td>\n",
       "      <td>070</td>\n",
       "      <td></td>\n",
       "      <td>1937-01-01</td>\n",
       "      <td>1937-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>Aḥiasaf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1893-01-01</td>\n",
       "      <td>1922-01-01</td>\n",
       "      <td>1201291062</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Aḥiasaf</td>\n",
       "      <td>luaḥ-ʿam sifruti ṿe-shimushi, ʿim temunot ṿe-t...</td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>[1893-1922]</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1893-01-01</td>\n",
       "      <td>1922-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>Aḥiasaf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1893-01-01</td>\n",
       "      <td>1922-01-01</td>\n",
       "      <td>010120858</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Aḥiasaf</td>\n",
       "      <td>luaḥ-ʿam sifruti ṿe-shimushi, ʿim temunot ṿe-t...</td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1893-1924</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1893-01-01</td>\n",
       "      <td>1924-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1907-01-01</td>\n",
       "      <td>1914-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>998 rows × 16 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 90,
   "source": [
    "df_merged = pd.merge(df_metadata, df_data, left_on='Zs_Caption', right_on='title', how='left', copy=True)\n",
    "df_merged"
   ],
   "id": "5f8409c13dc60142"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                            Zs_Caption  Year_From  Year_To  \\\n",
       "0    ... Bericht der Jüdischen Haushaltungsschule z...     1835.0   1835.0   \n",
       "1    ... Bericht ueber den Verein für Westfalen und...     1932.0   1932.0   \n",
       "2    ... Bericht ueber den Verein für die Provinz W...     1871.0   1871.0   \n",
       "3    ... Jahresbericht der Israelitischen Waisenans...     1836.0   1836.0   \n",
       "5    ... Verwaltungsbericht des Haupt-Grenz-Comité'...     1928.0   1929.0   \n",
       "..                                                 ...        ...      ...   \n",
       "979  Bericht des Departements für Landwirtschaftlic...        NaN      NaN   \n",
       "980  Political report of the Executive of the Jewis...        NaN      NaN   \n",
       "985               Zuwachsverzeichnis für die Jahre ...        NaN      NaN   \n",
       "991  Jahresbericht der Jugendgruppe (begründet von ...        NaN      NaN   \n",
       "997               Séance du comité de direction du ...        NaN      NaN   \n",
       "\n",
       "      Date_From     Date_To  idn library title subtitle category date person  \\\n",
       "0    1821-01-01  1910-01-01  NaN     NaN   NaN      NaN      NaN  NaN    NaN   \n",
       "1    1836-01-01  1836-01-01  NaN     NaN   NaN      NaN      NaN  NaN    NaN   \n",
       "2    1835-01-01  1835-01-01  NaN     NaN   NaN      NaN      NaN  NaN    NaN   \n",
       "3    1915-01-01  1917-01-01  NaN     NaN   NaN      NaN      NaN  NaN    NaN   \n",
       "5    1870-01-01  1870-01-01  NaN     NaN   NaN      NaN      NaN  NaN    NaN   \n",
       "..          ...         ...  ...     ...   ...      ...      ...  ...    ...   \n",
       "979  1935-08-01  1935-08-01  NaN     NaN   NaN      NaN      NaN  NaN    NaN   \n",
       "980  1937-08-01  1937-08-01  NaN     NaN   NaN      NaN      NaN  NaN    NaN   \n",
       "985  1924-01-01  1934-01-01  NaN     NaN   NaN      NaN      NaN  NaN    NaN   \n",
       "991  1913-01-01  1913-01-01  NaN     NaN   NaN      NaN      NaN  NaN    NaN   \n",
       "997  1907-01-01  1914-01-01  NaN     NaN   NaN      NaN      NaN  NaN    NaN   \n",
       "\n",
       "     ddc issn year_from year_to  \n",
       "0    NaN  NaN       NaT     NaT  \n",
       "1    NaN  NaN       NaT     NaT  \n",
       "2    NaN  NaN       NaT     NaT  \n",
       "3    NaN  NaN       NaT     NaT  \n",
       "5    NaN  NaN       NaT     NaT  \n",
       "..   ...  ...       ...     ...  \n",
       "979  NaN  NaN       NaT     NaT  \n",
       "980  NaN  NaN       NaT     NaT  \n",
       "985  NaN  NaN       NaT     NaT  \n",
       "991  NaN  NaN       NaT     NaT  \n",
       "997  NaN  NaN       NaT     NaT  \n",
       "\n",
       "[214 rows x 16 columns]"
      ],
      "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>Zs_Caption</th>\n",
       "      <th>Year_From</th>\n",
       "      <th>Year_To</th>\n",
       "      <th>Date_From</th>\n",
       "      <th>Date_To</th>\n",
       "      <th>idn</th>\n",
       "      <th>library</th>\n",
       "      <th>title</th>\n",
       "      <th>subtitle</th>\n",
       "      <th>category</th>\n",
       "      <th>date</th>\n",
       "      <th>person</th>\n",
       "      <th>ddc</th>\n",
       "      <th>issn</th>\n",
       "      <th>year_from</th>\n",
       "      <th>year_to</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>... Bericht der Jüdischen Haushaltungsschule z...</td>\n",
       "      <td>1835.0</td>\n",
       "      <td>1835.0</td>\n",
       "      <td>1821-01-01</td>\n",
       "      <td>1910-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>... Bericht ueber den Verein für Westfalen und...</td>\n",
       "      <td>1932.0</td>\n",
       "      <td>1932.0</td>\n",
       "      <td>1836-01-01</td>\n",
       "      <td>1836-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>1871.0</td>\n",
       "      <td>1871.0</td>\n",
       "      <td>1835-01-01</td>\n",
       "      <td>1835-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>... Jahresbericht der Israelitischen Waisenans...</td>\n",
       "      <td>1836.0</td>\n",
       "      <td>1836.0</td>\n",
       "      <td>1915-01-01</td>\n",
       "      <td>1917-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>... Verwaltungsbericht des Haupt-Grenz-Comité'...</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>1929.0</td>\n",
       "      <td>1870-01-01</td>\n",
       "      <td>1870-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>979</th>\n",
       "      <td>Bericht des Departements für Landwirtschaftlic...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1935-08-01</td>\n",
       "      <td>1935-08-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>980</th>\n",
       "      <td>Political report of the Executive of the Jewis...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1937-08-01</td>\n",
       "      <td>1937-08-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>985</th>\n",
       "      <td>Zuwachsverzeichnis für die Jahre ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1924-01-01</td>\n",
       "      <td>1934-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>991</th>\n",
       "      <td>Jahresbericht der Jugendgruppe (begründet von ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1913-01-01</td>\n",
       "      <td>1913-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1907-01-01</td>\n",
       "      <td>1914-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>214 rows × 16 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 92,
   "source": [
    "# find all unmatched titles\n",
    "unmatched_titles = df_merged[df_merged['title'].isnull()]\n",
    "unmatched_titles"
   ],
   "id": "e63f20f476766104"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T21:04:27.333809Z",
     "start_time": "2024-04-29T21:04:27.327252Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata_title = df_metadata['Zs_Caption']",
   "id": "addcaeeb6d9dc4fd",
   "outputs": [],
   "execution_count": 12
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T20:13:55.412491Z",
     "start_time": "2024-04-29T20:13:55.404846Z"
    }
   },
   "cell_type": "code",
   "source": "len(df_metadata_title)",
   "id": "487fca0eb40ba240",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "616"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 64
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T20:18:24.437380Z",
     "start_time": "2024-04-29T20:18:24.427Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata_title.sample(5)",
   "id": "36d1a7ffd29ee403",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      ... Bericht der Jüdischen Haushaltungsschule z...\n",
       "1      ... Bericht ueber den Verein für Westfalen und...\n",
       "2      ... Bericht ueber den Verein für die Provinz W...\n",
       "3      ... Jahresbericht der Israelitischen Waisenans...\n",
       "4      ... Jahresbericht der Jüdischen Frauenvereinig...\n",
       "                             ...                        \n",
       "611              Illustrirter jüdischer Familienkalender\n",
       "612    Jahresbericht der Jugendgruppe (begründet von ...\n",
       "613    Jüdisches Gemeindeblatt für den Verband der Ku...\n",
       "614                                              Aḥiasaf\n",
       "615                 Séance du comité de direction du ...\n",
       "Name: Zs_Caption, Length: 616, dtype: object"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 70
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T21:04:18.847241Z",
     "start_time": "2024-04-29T21:04:18.840823Z"
    }
   },
   "cell_type": "code",
   "source": "df_data_title = df_data['title']",
   "id": "e70cb09e957493b6",
   "outputs": [],
   "execution_count": 10
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T21:08:51.310586Z",
     "start_time": "2024-04-29T21:08:51.283700Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Assuming df_metadata_title and df_data_title are the dataframes to be merged\n",
    "\n",
    "# Step 1: Merge dataframes on 'Zs_Caption' and 'title'\n",
    "merged_df = pd.merge(df_metadata_title, df_data_title, how='outer', left_on='Zs_Caption', right_on='title', suffixes=('_meta', '_data'))\n",
    "\n",
    "# Step 2: Identify where titles are the same\n",
    "same_titles = merged_df[merged_df['Zs_Ca ption'] == merged_df['title']]\n",
    "print(\"Titles that are the same in both dataframes:\")\n",
    "print(len(same_titles))\n",
    "same_titles[['Zs_Caption', 'title']]"
   ],
   "id": "5dc4e3a4b41070ef",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Titles that are the same in both dataframes:\n",
      "784\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "                                            Zs_Caption  \\\n",
       "8    ... Jahresbericht der Jüdischen Frauenvereinig...   \n",
       "9    ... Jahresbericht der Jüdischen Frauenvereinig...   \n",
       "12   Actes et conférences de la Société des Études ...   \n",
       "13   Actes et conférences de la Société des Études ...   \n",
       "14             Adressbuch für den jüdischen Buchhandel   \n",
       "..                                                 ...   \n",
       "987                 Zionist library for boys and girls   \n",
       "988                 Zionist library for boys and girls   \n",
       "989                 Zionist library for boys and girls   \n",
       "996                     Židovská ročenka pre Slovensko   \n",
       "997                     Židovská ročenka pre Slovensko   \n",
       "\n",
       "                                                 title  \n",
       "8    ... Jahresbericht der Jüdischen Frauenvereinig...  \n",
       "9    ... Jahresbericht der Jüdischen Frauenvereinig...  \n",
       "12   Actes et conférences de la Société des Études ...  \n",
       "13   Actes et conférences de la Société des Études ...  \n",
       "14             Adressbuch für den jüdischen Buchhandel  \n",
       "..                                                 ...  \n",
       "987                 Zionist library for boys and girls  \n",
       "988                 Zionist library for boys and girls  \n",
       "989                 Zionist library for boys and girls  \n",
       "996                     Židovská ročenka pre Slovensko  \n",
       "997                     Židovská ročenka pre Slovensko  \n",
       "\n",
       "[784 rows x 2 columns]"
      ],
      "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>Zs_Caption</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>... Jahresbericht der Jüdischen Frauenvereinig...</td>\n",
       "      <td>... Jahresbericht der Jüdischen Frauenvereinig...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>... Jahresbericht der Jüdischen Frauenvereinig...</td>\n",
       "      <td>... Jahresbericht der Jüdischen Frauenvereinig...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Actes et conférences de la Société des Études ...</td>\n",
       "      <td>Actes et conférences de la Société des Études ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Actes et conférences de la Société des Études ...</td>\n",
       "      <td>Actes et conférences de la Société des Études ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Adressbuch für den jüdischen Buchhandel</td>\n",
       "      <td>Adressbuch für den jüdischen Buchhandel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>987</th>\n",
       "      <td>Zionist library for boys and girls</td>\n",
       "      <td>Zionist library for boys and girls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>988</th>\n",
       "      <td>Zionist library for boys and girls</td>\n",
       "      <td>Zionist library for boys and girls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>989</th>\n",
       "      <td>Zionist library for boys and girls</td>\n",
       "      <td>Zionist library for boys and girls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>Židovská ročenka pre Slovensko</td>\n",
       "      <td>Židovská ročenka pre Slovensko</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>Židovská ročenka pre Slovensko</td>\n",
       "      <td>Židovská ročenka pre Slovensko</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>784 rows × 2 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 19
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T21:07:51.732983Z",
     "start_time": "2024-04-29T21:07:51.718711Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Step 3: Identify titles only in df_metadata\n",
    "metadata_only = merged_df[merged_df['title'].isnull()]\n",
    "print(\"\\nTitles only in df_metadata:\")\n",
    "print(len(metadata_only))\n",
    "metadata_only['Zs_Caption']89"
   ],
   "id": "ec057b24ed54900d",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Titles only in df_metadata:\n",
      "214\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0      ... Bericht der Jüdischen Haushaltungsschule z...\n",
       "1      ... Bericht der Jüdischen Haushaltungsschule z...\n",
       "2      ... Bericht ueber den Verein für Westfalen und...\n",
       "3      ... Bericht ueber den Verein für Westfalen und...\n",
       "4      ... Bericht ueber den Verein für die Provinz W...\n",
       "                             ...                        \n",
       "991                 Zuwachsverzeichnis für die Jahre ...\n",
       "992    [Verein zur Pflege und Unterstützung israeliti...\n",
       "993    [Verein zur Pflege und Unterstützung israeliti...\n",
       "994           Österreichisch-ungarische Cantoren-Zeitung\n",
       "995           Österreichisch-ungarische Cantoren-Zeitung\n",
       "Name: Zs_Caption, Length: 214, dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 16
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T21:08:02.521338Z",
     "start_time": "2024-04-29T21:08:02.508738Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Step 4: Identify titles only in df_data\n",
    "data_only = merged_df[merged_df['Zs_Caption'].isnull()]\n",
    "print(\"\\nTitles only in df_data:\")\n",
    "print(len(data_only))\n",
    "data_only['title']"
   ],
   "id": "5c65f212271ceb4b",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Titles only in df_data:\n",
      "935\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "998                  Jüdischer Almanach für Groß-Rumänien\n",
       "999                                                 Golem\n",
       "1000       Jüdischer Almanach ... des Leo-Baeck-Instituts\n",
       "1001                         KC-Blätter / Kartell-Convent\n",
       "1002                        Bar Kochba-Hakoah Nachrichten\n",
       "                              ...                        \n",
       "1928                          Das jüdische Centralblatt\n",
       "1929                          Das jüdische Centralblatt\n",
       "1930                             Der israelitische Bote\n",
       "1931    Statuten für die Achawa, Verein zur Unterstütz...\n",
       "1932    Gemeindeblatt für die jüdischen Gemeinden Preu...\n",
       "Name: title, Length: 935, dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 17
  },
  {
   "metadata": {},
   "cell_type": "code",
   "outputs": [],
   "execution_count": null,
   "source": "",
   "id": "1b1e8816d02c6c64"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T20:21:26.846900Z",
     "start_time": "2024-04-29T20:21:26.826482Z"
    }
   },
   "cell_type": "code",
   "source": "df_merged_title = pd.merge(df_metadata_title, df_data_title, how='outer', left_on=\"Zs_Caption\", right_on='title')",
   "id": "467a7d223886172c",
   "outputs": [],
   "execution_count": 80
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T20:21:28.183863Z",
     "start_time": "2024-04-29T20:21:28.175896Z"
    }
   },
   "cell_type": "code",
   "source": "df_merged_title.drop_duplicates(subset='title')",
   "id": "bd8cb06c33319f97",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                             Zs_Caption  \\\n",
       "0     ... Bericht der Jüdischen Haushaltungsschule z...   \n",
       "8     ... Jahresbericht der Jüdischen Frauenvereinig...   \n",
       "12    Actes et conférences de la Société des Études ...   \n",
       "14              Adressbuch für den jüdischen Buchhandel   \n",
       "16               Allgemeine Israelitische Wochenschrift   \n",
       "...                                                 ...   \n",
       "1927                                                NaN   \n",
       "1928                                                NaN   \n",
       "1930                                                NaN   \n",
       "1931                                                NaN   \n",
       "1932                                                NaN   \n",
       "\n",
       "                                                  title  \n",
       "0                                                   NaN  \n",
       "8     ... Jahresbericht der Jüdischen Frauenvereinig...  \n",
       "12    Actes et conférences de la Société des Études ...  \n",
       "14              Adressbuch für den jüdischen Buchhandel  \n",
       "16               Allgemeine Israelitische Wochenschrift  \n",
       "...                                                 ...  \n",
       "1927                                         Das Echo  \n",
       "1928                        Das jüdische Centralblatt  \n",
       "1930                           Der israelitische Bote  \n",
       "1931  Statuten für die Achawa, Verein zur Unterstütz...  \n",
       "1932  Gemeindeblatt für die jüdischen Gemeinden Preu...  \n",
       "\n",
       "[1013 rows x 2 columns]"
      ],
      "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>Zs_Caption</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>... Bericht der Jüdischen Haushaltungsschule z...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>... Jahresbericht der Jüdischen Frauenvereinig...</td>\n",
       "      <td>... Jahresbericht der Jüdischen Frauenvereinig...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Actes et conférences de la Société des Études ...</td>\n",
       "      <td>Actes et conférences de la Société des Études ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Adressbuch für den jüdischen Buchhandel</td>\n",
       "      <td>Adressbuch für den jüdischen Buchhandel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Allgemeine Israelitische Wochenschrift</td>\n",
       "      <td>Allgemeine Israelitische Wochenschrift</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1927</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Das Echo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1928</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Das jüdische Centralblatt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Der israelitische Bote</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1931</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Statuten für die Achawa, Verein zur Unterstütz...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1932</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Gemeindeblatt für die jüdischen Gemeinden Preu...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1013 rows × 2 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 81
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T20:21:29.174679Z",
     "start_time": "2024-04-29T20:21:29.169554Z"
    }
   },
   "cell_type": "code",
   "source": "df_merged_title",
   "id": "ae726129e86b31ca",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                             Zs_Caption  \\\n",
       "0     ... Bericht der Jüdischen Haushaltungsschule z...   \n",
       "1     ... Bericht der Jüdischen Haushaltungsschule z...   \n",
       "2     ... Bericht ueber den Verein für Westfalen und...   \n",
       "3     ... Bericht ueber den Verein für Westfalen und...   \n",
       "4     ... Bericht ueber den Verein für die Provinz W...   \n",
       "...                                                 ...   \n",
       "1928                                                NaN   \n",
       "1929                                                NaN   \n",
       "1930                                                NaN   \n",
       "1931                                                NaN   \n",
       "1932                                                NaN   \n",
       "\n",
       "                                                  title  \n",
       "0                                                   NaN  \n",
       "1                                                   NaN  \n",
       "2                                                   NaN  \n",
       "3                                                   NaN  \n",
       "4                                                   NaN  \n",
       "...                                                 ...  \n",
       "1928                        Das jüdische Centralblatt  \n",
       "1929                        Das jüdische Centralblatt  \n",
       "1930                           Der israelitische Bote  \n",
       "1931  Statuten für die Achawa, Verein zur Unterstütz...  \n",
       "1932  Gemeindeblatt für die jüdischen Gemeinden Preu...  \n",
       "\n",
       "[1933 rows x 2 columns]"
      ],
      "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>Zs_Caption</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>... Bericht der Jüdischen Haushaltungsschule z...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>... Bericht der Jüdischen Haushaltungsschule z...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>... Bericht ueber den Verein für Westfalen und...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>... Bericht ueber den Verein für Westfalen und...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1928</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Das jüdische Centralblatt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1929</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Das jüdische Centralblatt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Der israelitische Bote</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1931</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Statuten für die Achawa, Verein zur Unterstütz...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1932</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Gemeindeblatt für die jüdischen Gemeinden Preu...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1933 rows × 2 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 82
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T20:21:36.187554Z",
     "start_time": "2024-04-29T20:21:36.169416Z"
    }
   },
   "cell_type": "code",
   "source": "unmatched_titles = merged_df_title[merged_df_title['title'].isnull()]",
   "id": "402c519de52853ba",
   "outputs": [],
   "execution_count": 83
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T20:22:39.158532Z",
     "start_time": "2024-04-29T20:22:39.150343Z"
    }
   },
   "cell_type": "code",
   "source": "unmatched_titles",
   "id": "302816938cb0bd1f",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                            Zs_Caption title\n",
       "0    ... Bericht der Jüdischen Haushaltungsschule z...   NaN\n",
       "2    ... Bericht ueber den Verein für Westfalen und...   NaN\n",
       "4    ... Bericht ueber den Verein für die Provinz W...   NaN\n",
       "6    ... Jahresbericht der Israelitischen Waisenans...   NaN\n",
       "10   ... Verwaltungsbericht des Haupt-Grenz-Comité'...   NaN\n",
       "..                                                 ...   ...\n",
       "944  Yediʿot shel Irgun Yotsʾe Shlezyah ʿIlit be-Yi...   NaN\n",
       "946        Yediʿot shel Irgun ʿOle Breslau be-Yiśraʾel   NaN\n",
       "990               Zuwachsverzeichnis für die Jahre ...   NaN\n",
       "992  [Verein zur Pflege und Unterstützung israeliti...   NaN\n",
       "994         Österreichisch-ungarische Cantoren-Zeitung   NaN\n",
       "\n",
       "[107 rows x 2 columns]"
      ],
      "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>Zs_Caption</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>... Bericht der Jüdischen Haushaltungsschule z...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>... Bericht ueber den Verein für Westfalen und...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>... Jahresbericht der Israelitischen Waisenans...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>... Verwaltungsbericht des Haupt-Grenz-Comité'...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>944</th>\n",
       "      <td>Yediʿot shel Irgun Yotsʾe Shlezyah ʿIlit be-Yi...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>946</th>\n",
       "      <td>Yediʿot shel Irgun ʿOle Breslau be-Yiśraʾel</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>990</th>\n",
       "      <td>Zuwachsverzeichnis für die Jahre ...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>992</th>\n",
       "      <td>[Verein zur Pflege und Unterstützung israeliti...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>994</th>\n",
       "      <td>Österreichisch-ungarische Cantoren-Zeitung</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>107 rows × 2 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 86
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T20:22:36.491809Z",
     "start_time": "2024-04-29T20:22:36.470080Z"
    }
   },
   "cell_type": "code",
   "source": "unmatched_titles = unmatched_titles.drop_duplicates(subset='Zs_Caption')",
   "id": "fc7ab7f2f96e94b9",
   "outputs": [],
   "execution_count": 85
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:52:12.883897Z",
     "start_time": "2024-04-29T19:52:12.818864Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Assuming df_metadata_title and df_data_title are the dataframes to be merged\n",
    "\n",
    "# Step 1: Merge dataframes on titles\n",
    "merged_df_title = pd.merge(df_metadata_title, df_data_title, how='outer', left_on='Zs_Caption', right_on='title', suffixes=('_meta', '_data'))\n",
    "\n",
    "# Drop duplicate rows based on the 'title_data' column\n",
    "merged_df_title = merged_df_title.drop_duplicates(subset='title_data')\n",
    "\n",
    "# Step 2: Merge on subtitles for unmatched rows\n",
    "unmatched_titles = merged_df_title[merged_df_title['title_data'].isnull()]\n",
    "merged_df_subtitle = pd.merge(unmatched_titles, df_data_title, how='left', left_on='Zs_Caption', right_on='subtitle', suffixes=('_meta', '_data'))\n",
    "\n",
    "# Concatenate the merged dataframes\n",
    "merged_df = pd.concat([merged_df_title, merged_df_subtitle], ignore_index=True)\n",
    "\n",
    "# Step 3: Check for overlapping years\n",
    "merged_df['Year_From'] = pd.to_datetime(merged_df['Year_From'])\n",
    "merged_df['Year_To'] = pd.to_datetime(merged_df['Year_To'])\n",
    "merged_df['date'] = pd.to_datetime(merged_df['date'])\n",
    "\n",
    "# Create a function to check for year overlap\n",
    "def check_year_overlap(row):\n",
    "    if row['Year_To_meta'] < row['date'] or row['Year_From_meta'] > row['date']:\n",
    "        return False\n",
    "    return True\n",
    "\n",
    "# Apply the function to create a new column indicating year overlap\n",
    "merged_df['Year_Overlap'] = merged_df.apply(check_year_overlap, axis=1)\n",
    "\n",
    "# Step 4: Create a matrix showing the differences in overlapping years\n",
    "year_overlap_matrix = pd.pivot_table(merged_df, values='Year_Overlap', index='Zs_Caption', columns='title_data', aggfunc='first')\n",
    "\n",
    "# Display the matrix\n",
    "year_overlap_matrix.reset_index(inplace=True)\n",
    "print(year_overlap_matrix)\n"
   ],
   "id": "d8c3236cdee9aaec",
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "Index(['title_data'], dtype='object')",
     "output_type": "error",
     "traceback": [
      "\u001B[0;31m---------------------------------------------------------------------------\u001B[0m",
      "\u001B[0;31mKeyError\u001B[0m                                  Traceback (most recent call last)",
      "Cell \u001B[0;32mIn[40], line 7\u001B[0m\n\u001B[1;32m      4\u001B[0m merged_df_title \u001B[38;5;241m=\u001B[39m pd\u001B[38;5;241m.\u001B[39mmerge(df_metadata_title, df_data_title, how\u001B[38;5;241m=\u001B[39m\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mouter\u001B[39m\u001B[38;5;124m'\u001B[39m, left_on\u001B[38;5;241m=\u001B[39m\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mZs_Caption\u001B[39m\u001B[38;5;124m'\u001B[39m, right_on\u001B[38;5;241m=\u001B[39m\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mtitle\u001B[39m\u001B[38;5;124m'\u001B[39m, suffixes\u001B[38;5;241m=\u001B[39m(\u001B[38;5;124m'\u001B[39m\u001B[38;5;124m_meta\u001B[39m\u001B[38;5;124m'\u001B[39m, \u001B[38;5;124m'\u001B[39m\u001B[38;5;124m_data\u001B[39m\u001B[38;5;124m'\u001B[39m))\n\u001B[1;32m      6\u001B[0m \u001B[38;5;66;03m# Drop duplicate rows based on the 'title_data' column\u001B[39;00m\n\u001B[0;32m----> 7\u001B[0m merged_df_title \u001B[38;5;241m=\u001B[39m \u001B[43mmerged_df_title\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mdrop_duplicates\u001B[49m\u001B[43m(\u001B[49m\u001B[43msubset\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[38;5;124;43m'\u001B[39;49m\u001B[38;5;124;43mtitle_data\u001B[39;49m\u001B[38;5;124;43m'\u001B[39;49m\u001B[43m)\u001B[49m\n\u001B[1;32m      9\u001B[0m \u001B[38;5;66;03m# Step 2: Merge on subtitles for unmatched rows\u001B[39;00m\n\u001B[1;32m     10\u001B[0m unmatched_titles \u001B[38;5;241m=\u001B[39m merged_df_title[merged_df_title[\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mtitle_data\u001B[39m\u001B[38;5;124m'\u001B[39m]\u001B[38;5;241m.\u001B[39misnull()]\n",
      "File \u001B[0;32m~/.venv/lib/python3.11/site-packages/pandas/util/_decorators.py:311\u001B[0m, in \u001B[0;36mdeprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper\u001B[0;34m(*args, **kwargs)\u001B[0m\n\u001B[1;32m    305\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;28mlen\u001B[39m(args) \u001B[38;5;241m>\u001B[39m num_allow_args:\n\u001B[1;32m    306\u001B[0m     warnings\u001B[38;5;241m.\u001B[39mwarn(\n\u001B[1;32m    307\u001B[0m         msg\u001B[38;5;241m.\u001B[39mformat(arguments\u001B[38;5;241m=\u001B[39marguments),\n\u001B[1;32m    308\u001B[0m         \u001B[38;5;167;01mFutureWarning\u001B[39;00m,\n\u001B[1;32m    309\u001B[0m         stacklevel\u001B[38;5;241m=\u001B[39mstacklevel,\n\u001B[1;32m    310\u001B[0m     )\n\u001B[0;32m--> 311\u001B[0m \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[43mfunc\u001B[49m\u001B[43m(\u001B[49m\u001B[38;5;241;43m*\u001B[39;49m\u001B[43margs\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[38;5;241;43m*\u001B[39;49m\u001B[38;5;241;43m*\u001B[39;49m\u001B[43mkwargs\u001B[49m\u001B[43m)\u001B[49m\n",
      "File \u001B[0;32m~/.venv/lib/python3.11/site-packages/pandas/core/frame.py:6125\u001B[0m, in \u001B[0;36mDataFrame.drop_duplicates\u001B[0;34m(self, subset, keep, inplace, ignore_index)\u001B[0m\n\u001B[1;32m   6123\u001B[0m inplace \u001B[38;5;241m=\u001B[39m validate_bool_kwarg(inplace, \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124minplace\u001B[39m\u001B[38;5;124m\"\u001B[39m)\n\u001B[1;32m   6124\u001B[0m ignore_index \u001B[38;5;241m=\u001B[39m validate_bool_kwarg(ignore_index, \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mignore_index\u001B[39m\u001B[38;5;124m\"\u001B[39m)\n\u001B[0;32m-> 6125\u001B[0m duplicated \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mduplicated\u001B[49m\u001B[43m(\u001B[49m\u001B[43msubset\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mkeep\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mkeep\u001B[49m\u001B[43m)\u001B[49m\n\u001B[1;32m   6127\u001B[0m result \u001B[38;5;241m=\u001B[39m \u001B[38;5;28mself\u001B[39m[\u001B[38;5;241m-\u001B[39mduplicated]\n\u001B[1;32m   6128\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m ignore_index:\n",
      "File \u001B[0;32m~/.venv/lib/python3.11/site-packages/pandas/core/frame.py:6259\u001B[0m, in \u001B[0;36mDataFrame.duplicated\u001B[0;34m(self, subset, keep)\u001B[0m\n\u001B[1;32m   6257\u001B[0m diff \u001B[38;5;241m=\u001B[39m Index(subset)\u001B[38;5;241m.\u001B[39mdifference(\u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mcolumns)\n\u001B[1;32m   6258\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m diff\u001B[38;5;241m.\u001B[39mempty:\n\u001B[0;32m-> 6259\u001B[0m     \u001B[38;5;28;01mraise\u001B[39;00m \u001B[38;5;167;01mKeyError\u001B[39;00m(diff)\n\u001B[1;32m   6261\u001B[0m vals \u001B[38;5;241m=\u001B[39m (col\u001B[38;5;241m.\u001B[39mvalues \u001B[38;5;28;01mfor\u001B[39;00m name, col \u001B[38;5;129;01min\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mitems() \u001B[38;5;28;01mif\u001B[39;00m name \u001B[38;5;129;01min\u001B[39;00m subset)\n\u001B[1;32m   6262\u001B[0m labels, shape \u001B[38;5;241m=\u001B[39m \u001B[38;5;28mmap\u001B[39m(\u001B[38;5;28mlist\u001B[39m, \u001B[38;5;28mzip\u001B[39m(\u001B[38;5;241m*\u001B[39m\u001B[38;5;28mmap\u001B[39m(f, vals)))\n",
      "\u001B[0;31mKeyError\u001B[0m: Index(['title_data'], dtype='object')"
     ]
    }
   ],
   "execution_count": 40
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:53:44.001705Z",
     "start_time": "2024-04-29T19:53:43.985359Z"
    }
   },
   "cell_type": "code",
   "source": "merged_df_title",
   "id": "aa051165f7befa62",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                             Zs_Caption  \\\n",
       "0     ... Bericht der Jüdischen Haushaltungsschule z...   \n",
       "1     ... Bericht der Jüdischen Haushaltungsschule z...   \n",
       "2     ... Bericht ueber den Verein für Westfalen und...   \n",
       "3     ... Bericht ueber den Verein für Westfalen und...   \n",
       "4     ... Bericht ueber den Verein für die Provinz W...   \n",
       "...                                                 ...   \n",
       "1928                                                NaN   \n",
       "1929                                                NaN   \n",
       "1930                                                NaN   \n",
       "1931                                                NaN   \n",
       "1932                                                NaN   \n",
       "\n",
       "                                                  title  \n",
       "0                                                   NaN  \n",
       "1                                                   NaN  \n",
       "2                                                   NaN  \n",
       "3                                                   NaN  \n",
       "4                                                   NaN  \n",
       "...                                                 ...  \n",
       "1928                        Das jüdische Centralblatt  \n",
       "1929                        Das jüdische Centralblatt  \n",
       "1930                           Der israelitische Bote  \n",
       "1931  Statuten für die Achawa, Verein zur Unterstütz...  \n",
       "1932  Gemeindeblatt für die jüdischen Gemeinden Preu...  \n",
       "\n",
       "[1933 rows x 2 columns]"
      ],
      "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>Zs_Caption</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>... Bericht der Jüdischen Haushaltungsschule z...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>... Bericht der Jüdischen Haushaltungsschule z...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>... Bericht ueber den Verein für Westfalen und...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>... Bericht ueber den Verein für Westfalen und...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1928</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Das jüdische Centralblatt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1929</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Das jüdische Centralblatt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Der israelitische Bote</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1931</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Statuten für die Achawa, Verein zur Unterstütz...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1932</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Gemeindeblatt für die jüdischen Gemeinden Preu...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1933 rows × 2 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 41
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:41:49.880607Z",
     "start_time": "2024-04-29T19:41:49.862242Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Step 1: Merge dataframes on titles or subtitles\n",
    "merged_df_title = pd.merge(df_metadata, df_data, how='inner', left_on='Zs_Caption', right_on='title', suffixes=('_meta', '_data'))"
   ],
   "id": "4443c110dd647b8f",
   "outputs": [],
   "execution_count": 19
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:41:50.583805Z",
     "start_time": "2024-04-29T19:41:50.571979Z"
    }
   },
   "cell_type": "code",
   "source": "merged_df_title",
   "id": "7a97e57f2c0e2c9e",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                            Zs_Caption  Year_From  Year_To  \\\n",
       "0    ... Jahresbericht der Jüdischen Frauenvereinig...     1870.0   1870.0   \n",
       "1    ... Jahresbericht der Jüdischen Frauenvereinig...        NaN      NaN   \n",
       "2    Actes et conférences de la Société des Études ...     1892.0   1894.0   \n",
       "3    Actes et conférences de la Société des Études ...        NaN      NaN   \n",
       "4              Adressbuch für den jüdischen Buchhandel     1886.0   1889.0   \n",
       "..                                                 ...        ...      ...   \n",
       "779                 Zionist library for boys and girls     1881.0   1907.0   \n",
       "780                 Zionist library for boys and girls        NaN      NaN   \n",
       "781                 Zionist library for boys and girls        NaN      NaN   \n",
       "782                     Židovská ročenka pre Slovensko     1907.0   1914.0   \n",
       "783                     Židovská ročenka pre Slovensko        NaN      NaN   \n",
       "\n",
       "      Date_From     Date_To         idn library  \\\n",
       "0    1855-01-01  1922-01-01  1185652604     zdb   \n",
       "1    1855-01-01  1922-01-01  1185652604     zdb   \n",
       "2    1886-01-01  1889-01-01  1147584931     zdb   \n",
       "3    1886-01-01  1889-01-01  1147584931     zdb   \n",
       "4    1927-01-01  1927-01-01   013234129     zdb   \n",
       "..          ...         ...         ...     ...   \n",
       "779  1908-01-01  1908-01-01  1143836235     zdb   \n",
       "780  1908-01-01  1908-01-01   018756476     zdb   \n",
       "781  1908-01-01  1908-01-01  1143836235     zdb   \n",
       "782  1940-01-01  1940-01-01  1166255131     zdb   \n",
       "783  1940-01-01  1940-01-01  1166255131     zdb   \n",
       "\n",
       "                                                 title  \\\n",
       "0    ... Jahresbericht der Jüdischen Frauenvereinig...   \n",
       "1    ... Jahresbericht der Jüdischen Frauenvereinig...   \n",
       "2    Actes et conférences de la Société des Études ...   \n",
       "3    Actes et conférences de la Société des Études ...   \n",
       "4              Adressbuch für den jüdischen Buchhandel   \n",
       "..                                                 ...   \n",
       "779                 Zionist library for boys and girls   \n",
       "780                 Zionist library for boys and girls   \n",
       "781                 Zionist library for boys and girls   \n",
       "782                     Židovská ročenka pre Slovensko   \n",
       "783                     Židovská ročenka pre Slovensko   \n",
       "\n",
       "                                  subtitle category       date person  ddc  \\\n",
       "0                                           JUDAICA       2018         290   \n",
       "1                                           JUDAICA       2018         290   \n",
       "2                                           JUDAICA  1886-1889         290   \n",
       "3                                           JUDAICA  1886-1889         290   \n",
       "4                                           JUDAICA  1927-1927         920   \n",
       "..                                     ...      ...        ...    ...  ...   \n",
       "779                                         JUDAICA       2017         290   \n",
       "780                                         JUDAICA  1930-1949         290   \n",
       "781                                         JUDAICA       2017         290   \n",
       "782  = Jüdisches Jahrbuch für die Slovakei  JUDAICA  1940-1940         910   \n",
       "783  = Jüdisches Jahrbuch für die Slovakei  JUDAICA  1940-1940         910   \n",
       "\n",
       "    issn  year_from    year_to  \n",
       "0        2018-01-01 2018-01-01  \n",
       "1        2018-01-01 2018-01-01  \n",
       "2        1886-01-01 1889-01-01  \n",
       "3        1886-01-01 1889-01-01  \n",
       "4        1927-01-01 1927-01-01  \n",
       "..   ...        ...        ...  \n",
       "779      2017-01-01 2017-01-01  \n",
       "780      1930-01-01 1949-01-01  \n",
       "781      2017-01-01 2017-01-01  \n",
       "782      1940-01-01 1940-01-01  \n",
       "783      1940-01-01 1940-01-01  \n",
       "\n",
       "[784 rows x 16 columns]"
      ],
      "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>Zs_Caption</th>\n",
       "      <th>Year_From</th>\n",
       "      <th>Year_To</th>\n",
       "      <th>Date_From</th>\n",
       "      <th>Date_To</th>\n",
       "      <th>idn</th>\n",
       "      <th>library</th>\n",
       "      <th>title</th>\n",
       "      <th>subtitle</th>\n",
       "      <th>category</th>\n",
       "      <th>date</th>\n",
       "      <th>person</th>\n",
       "      <th>ddc</th>\n",
       "      <th>issn</th>\n",
       "      <th>year_from</th>\n",
       "      <th>year_to</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>... Jahresbericht der Jüdischen Frauenvereinig...</td>\n",
       "      <td>1870.0</td>\n",
       "      <td>1870.0</td>\n",
       "      <td>1855-01-01</td>\n",
       "      <td>1922-01-01</td>\n",
       "      <td>1185652604</td>\n",
       "      <td>zdb</td>\n",
       "      <td>... Jahresbericht der Jüdischen Frauenvereinig...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>2018</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>2018-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>... Jahresbericht der Jüdischen Frauenvereinig...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1855-01-01</td>\n",
       "      <td>1922-01-01</td>\n",
       "      <td>1185652604</td>\n",
       "      <td>zdb</td>\n",
       "      <td>... Jahresbericht der Jüdischen Frauenvereinig...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>2018</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>2018-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Actes et conférences de la Société des Études ...</td>\n",
       "      <td>1892.0</td>\n",
       "      <td>1894.0</td>\n",
       "      <td>1886-01-01</td>\n",
       "      <td>1889-01-01</td>\n",
       "      <td>1147584931</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Actes et conférences de la Société des Études ...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1886-1889</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1886-01-01</td>\n",
       "      <td>1889-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Actes et conférences de la Société des Études ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1886-01-01</td>\n",
       "      <td>1889-01-01</td>\n",
       "      <td>1147584931</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Actes et conférences de la Société des Études ...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1886-1889</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1886-01-01</td>\n",
       "      <td>1889-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Adressbuch für den jüdischen Buchhandel</td>\n",
       "      <td>1886.0</td>\n",
       "      <td>1889.0</td>\n",
       "      <td>1927-01-01</td>\n",
       "      <td>1927-01-01</td>\n",
       "      <td>013234129</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Adressbuch für den jüdischen Buchhandel</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1927-1927</td>\n",
       "      <td></td>\n",
       "      <td>920</td>\n",
       "      <td></td>\n",
       "      <td>1927-01-01</td>\n",
       "      <td>1927-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>779</th>\n",
       "      <td>Zionist library for boys and girls</td>\n",
       "      <td>1881.0</td>\n",
       "      <td>1907.0</td>\n",
       "      <td>1908-01-01</td>\n",
       "      <td>1908-01-01</td>\n",
       "      <td>1143836235</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Zionist library for boys and girls</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>2017</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>780</th>\n",
       "      <td>Zionist library for boys and girls</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1908-01-01</td>\n",
       "      <td>1908-01-01</td>\n",
       "      <td>018756476</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Zionist library for boys and girls</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1930-1949</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1930-01-01</td>\n",
       "      <td>1949-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>781</th>\n",
       "      <td>Zionist library for boys and girls</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1908-01-01</td>\n",
       "      <td>1908-01-01</td>\n",
       "      <td>1143836235</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Zionist library for boys and girls</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>2017</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>782</th>\n",
       "      <td>Židovská ročenka pre Slovensko</td>\n",
       "      <td>1907.0</td>\n",
       "      <td>1914.0</td>\n",
       "      <td>1940-01-01</td>\n",
       "      <td>1940-01-01</td>\n",
       "      <td>1166255131</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Židovská ročenka pre Slovensko</td>\n",
       "      <td>= Jüdisches Jahrbuch für die Slovakei</td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1940-1940</td>\n",
       "      <td></td>\n",
       "      <td>910</td>\n",
       "      <td></td>\n",
       "      <td>1940-01-01</td>\n",
       "      <td>1940-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>783</th>\n",
       "      <td>Židovská ročenka pre Slovensko</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1940-01-01</td>\n",
       "      <td>1940-01-01</td>\n",
       "      <td>1166255131</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Židovská ročenka pre Slovensko</td>\n",
       "      <td>= Jüdisches Jahrbuch für die Slovakei</td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1940-1940</td>\n",
       "      <td></td>\n",
       "      <td>910</td>\n",
       "      <td></td>\n",
       "      <td>1940-01-01</td>\n",
       "      <td>1940-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>784 rows × 16 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 20
  },
  {
   "metadata": {},
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Empty DataFrame\n",
      "Columns: []\n",
      "Index: []\n"
     ]
    }
   ],
   "execution_count": 23,
   "source": [
    "\n",
    "# Merge on subtitle if title didn't match\n",
    "unmatched_subtitle = merged_df[merged_df['title'].isnull()]\n",
    "merged_df = merged_df.dropna(subset=['title'])\n",
    "merged_df = pd.merge(unmatched_subtitle, df_data, how='left', left_on='Zs_Caption', right_on='subtitle')\n",
    "\n",
    "# Concatenate the merged dataframes\n",
    "merged_df = pd.concat([merged_df, unmatched_subtitle], ignore_index=True)\n",
    "\n",
    "# Step 2: Check for overlapping years\n",
    "merged_df['Year_From'] = pd.to_datetime(merged_df['Year_From'])\n",
    "merged_df['Year_To'] = pd.to_datetime(merged_df['Year_To'])\n",
    "merged_df['date'] = pd.to_datetime(merged_df['date'])\n",
    "\n",
    "# Create a function to check for year overlap\n",
    "def check_year_overlap(row):\n",
    "    if row['Year_To'] < row['date'] or row['Year_From'] > row['date']:\n",
    "        return False\n",
    "    return True\n",
    "\n",
    "# Apply the function to create a new column indicating year overlap\n",
    "merged_df['Year_Overlap'] = merged_df.apply(check_year_overlap, axis=1)\n",
    "\n",
    "# Step 3: Create a matrix showing the differences in overlapping years\n",
    "year_overlap_matrix = pd.pivot_table(merged_df, values='Year_Overlap', index='Zs_Caption', columns='title', aggfunc='first')\n",
    "\n",
    "# Display the matrix\n",
    "print(year_overlap_matrix)"
   ],
   "id": "8d3ea04183892a6b"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:26:30.566197Z",
     "start_time": "2024-04-29T19:26:30.550881Z"
    }
   },
   "cell_type": "code",
   "source": "merged_df",
   "id": "e441ea5f1ed5d538",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                             Zs_Caption  Year_From  Year_To  \\\n",
       "0     ... Bericht der Jüdischen Haushaltungsschule z...     1835.0   1835.0   \n",
       "1     ... Bericht der Jüdischen Haushaltungsschule z...        NaN      NaN   \n",
       "2     ... Bericht ueber den Verein für Westfalen und...     1932.0   1932.0   \n",
       "3     ... Bericht ueber den Verein für Westfalen und...        NaN      NaN   \n",
       "4     ... Bericht ueber den Verein für die Provinz W...     1871.0   1871.0   \n",
       "...                                                 ...        ...      ...   \n",
       "1928                                                NaN        NaN      NaN   \n",
       "1929                                                NaN        NaN      NaN   \n",
       "1930                                                NaN        NaN      NaN   \n",
       "1931                                                NaN        NaN      NaN   \n",
       "1932                                                NaN        NaN      NaN   \n",
       "\n",
       "       Date_From     Date_To         idn library  \\\n",
       "0     1821-01-01  1910-01-01         NaN     NaN   \n",
       "1     1821-01-01  1910-01-01         NaN     NaN   \n",
       "2     1836-01-01  1836-01-01         NaN     NaN   \n",
       "3     1836-01-01  1836-01-01         NaN     NaN   \n",
       "4     1835-01-01  1835-01-01         NaN     NaN   \n",
       "...          ...         ...         ...     ...   \n",
       "1928         NaN         NaN  1158522371     zdb   \n",
       "1929         NaN         NaN   995014035     zdb   \n",
       "1930         NaN         NaN  1161355766     zdb   \n",
       "1931         NaN         NaN   018610110     zdb   \n",
       "1932         NaN         NaN   015236781     zdb   \n",
       "\n",
       "                                                  title  \\\n",
       "0                                                   NaN   \n",
       "1                                                   NaN   \n",
       "2                                                   NaN   \n",
       "3                                                   NaN   \n",
       "4                                                   NaN   \n",
       "...                                                 ...   \n",
       "1928                        Das jüdische Centralblatt   \n",
       "1929                        Das jüdische Centralblatt   \n",
       "1930                           Der israelitische Bote   \n",
       "1931  Statuten für die Achawa, Verein zur Unterstütz...   \n",
       "1932  Gemeindeblatt für die jüdischen Gemeinden Preu...   \n",
       "\n",
       "                                               subtitle category       date  \\\n",
       "0                                                   NaN      NaN        NaN   \n",
       "1                                                   NaN      NaN        NaN   \n",
       "2                                                   NaN      NaN        NaN   \n",
       "3                                                   NaN      NaN        NaN   \n",
       "4                                                   NaN      NaN        NaN   \n",
       "...                                                 ...      ...        ...   \n",
       "1928  (zugleich Archiv für die Geschichte der Juden ...  JUDAICA  1882-1883   \n",
       "1929  zugleich Archiv für die Geschichte der Juden i...           1882-1892   \n",
       "1930                                                     JUDAICA  1876-1879   \n",
       "1931                                                     JUDAICA  1889-1904   \n",
       "1932                                                     JUDAICA  1934-1937   \n",
       "\n",
       "     person  ddc issn  year_from    year_to  \n",
       "0       NaN  NaN  NaN        NaT        NaT  \n",
       "1       NaN  NaN  NaN        NaT        NaT  \n",
       "2       NaN  NaN  NaN        NaT        NaT  \n",
       "3       NaN  NaN  NaN        NaT        NaT  \n",
       "4       NaN  NaN  NaN        NaT        NaT  \n",
       "...     ...  ...  ...        ...        ...  \n",
       "1928         910      1882-01-01 1883-01-01  \n",
       "1929         910      1882-01-01 1892-01-01  \n",
       "1930         290      1876-01-01 1879-01-01  \n",
       "1931         290      1889-01-01 1904-01-01  \n",
       "1932         070      1934-01-01 1937-01-01  \n",
       "\n",
       "[1933 rows x 16 columns]"
      ],
      "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>Zs_Caption</th>\n",
       "      <th>Year_From</th>\n",
       "      <th>Year_To</th>\n",
       "      <th>Date_From</th>\n",
       "      <th>Date_To</th>\n",
       "      <th>idn</th>\n",
       "      <th>library</th>\n",
       "      <th>title</th>\n",
       "      <th>subtitle</th>\n",
       "      <th>category</th>\n",
       "      <th>date</th>\n",
       "      <th>person</th>\n",
       "      <th>ddc</th>\n",
       "      <th>issn</th>\n",
       "      <th>year_from</th>\n",
       "      <th>year_to</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>... Bericht der Jüdischen Haushaltungsschule z...</td>\n",
       "      <td>1835.0</td>\n",
       "      <td>1835.0</td>\n",
       "      <td>1821-01-01</td>\n",
       "      <td>1910-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>... Bericht der Jüdischen Haushaltungsschule z...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1821-01-01</td>\n",
       "      <td>1910-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>... Bericht ueber den Verein für Westfalen und...</td>\n",
       "      <td>1932.0</td>\n",
       "      <td>1932.0</td>\n",
       "      <td>1836-01-01</td>\n",
       "      <td>1836-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>... Bericht ueber den Verein für Westfalen und...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1836-01-01</td>\n",
       "      <td>1836-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>1871.0</td>\n",
       "      <td>1871.0</td>\n",
       "      <td>1835-01-01</td>\n",
       "      <td>1835-01-01</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1928</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1158522371</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Das jüdische Centralblatt</td>\n",
       "      <td>(zugleich Archiv für die Geschichte der Juden ...</td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1882-1883</td>\n",
       "      <td></td>\n",
       "      <td>910</td>\n",
       "      <td></td>\n",
       "      <td>1882-01-01</td>\n",
       "      <td>1883-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1929</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>995014035</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Das jüdische Centralblatt</td>\n",
       "      <td>zugleich Archiv für die Geschichte der Juden i...</td>\n",
       "      <td></td>\n",
       "      <td>1882-1892</td>\n",
       "      <td></td>\n",
       "      <td>910</td>\n",
       "      <td></td>\n",
       "      <td>1882-01-01</td>\n",
       "      <td>1892-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1161355766</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Der israelitische Bote</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1876-1879</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1876-01-01</td>\n",
       "      <td>1879-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1931</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>018610110</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Statuten für die Achawa, Verein zur Unterstütz...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1889-1904</td>\n",
       "      <td></td>\n",
       "      <td>290</td>\n",
       "      <td></td>\n",
       "      <td>1889-01-01</td>\n",
       "      <td>1904-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1932</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>015236781</td>\n",
       "      <td>zdb</td>\n",
       "      <td>Gemeindeblatt für die jüdischen Gemeinden Preu...</td>\n",
       "      <td></td>\n",
       "      <td>JUDAICA</td>\n",
       "      <td>1934-1937</td>\n",
       "      <td></td>\n",
       "      <td>070</td>\n",
       "      <td></td>\n",
       "      <td>1934-01-01</td>\n",
       "      <td>1937-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1933 rows × 16 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 32
  },
  {
   "metadata": {},
   "cell_type": "code",
   "outputs": [],
   "execution_count": null,
   "source": "",
   "id": "18120bfbdd6f5f07"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}