ipynb/2. names.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a56c4043-52ec-44c3-b01d-48899a39f27a",
   "metadata": {},
   "source": [
    "# Names\n",
    "\n",
    "Date: 2024/01/21, 2024/04/19(SQLite)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7b4e4c39-9d3c-49cf-b729-d01db4e617f8",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/bitsandbytes/cextension.py:34: UserWarning: The installed version of bitsandbytes was compiled without GPU support. 8-bit optimizers, 8-bit multiplication, and GPU quantization are unavailable.\n",
      "  warn(\"The installed version of bitsandbytes was compiled without GPU support. \"\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "'NoneType' object has no attribute 'cadam32bit_grad_fp32'\n"
     ]
    }
   ],
   "source": [
    "import spacy\n",
    "from spacy import displacy\n",
    "import re\n",
    "nlp = spacy.load('en_core_web_trf')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82613bb2-e12a-4741-b896-8004264cbaf3",
   "metadata": {},
   "source": [
    "## Preprocessing and Paragraph-Level Splitting of the Original Text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1d24b78c-dade-44b7-b733-e31a6f0cdab5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['If there is such a thing as inherited aptitude for art it certainly showed itself in the family of Bach.  For six successive generations scarcely two or three of its members are found whom nature had not endowed with remarkable musical talent, and who did not make music their profession.',\n",
       " 'Veit Bach, ancestor of this famous family, gained a livelihood as a baker at Pressburg in Hungary.  When the religious troubles of the sixteenth century broke out he was driven to seek another place of abode, and having got together as much of his small property as he could, retired with it to Thuringia, hoping to find peace and security there.  He settled at Wechmar, a village near Gotha, where he continued to ply his trade as a baker and miller.In his leisure hours he was wont to amuse himself with the lute,playing it amid the noise and clatter of the mill.  His taste for music descended to his two sonsand their children, and in time the Bachs grew to be a very numerous family of professional musicians,Cantors, Organists, and Town Musicians,throughout  Thuringia.',\n",
       " \"Not all the Bachs, however, were great musicians.  But every generation boasted some of them who were more than usually distinguished.  In the first quarter of the seventeenth century three of Veit Bach's grandchildren showed such exceptional talent that the Count of Schwarzburg-Arnstadt thought it worth while to send them at his expense to Italy, then the chief school of music, to perfect themselves in the art.\"]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import yaml\n",
    "\n",
    "with open('../data/pg35041-paragraphs.yaml', 'r', encoding='utf-8') as f:\n",
    "    paragraphs = yaml.safe_load(f.read())    \n",
    "\n",
    "paragraphs[:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "cb2dcfd4-8a32-4fa1-857d-fd99df94ed5e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "159"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(paragraphs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c6c5f25e-3b54-4565-b344-124a5d842725",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span class=\"tex2jax_ignore\"><div class=\"entities\" style=\"line-height: 2.5; direction: ltr\">At the beginning of his \n",
       "<mark class=\"entity\" style=\"background: #7aecec; padding: 0.45em 0.6em; margin: 0 0.25em; line-height: 1; border-radius: 0.35em;\">\n",
       "    Cantorate\n",
       "    <span style=\"font-size: 0.8em; font-weight: bold; line-height: 1; border-radius: 0.35em; vertical-align: middle; margin-left: 0.5rem\">ORG</span>\n",
       "</mark>\n",
       " \n",
       "<mark class=\"entity\" style=\"background: #aa9cfc; padding: 0.45em 0.6em; margin: 0 0.25em; line-height: 1; border-radius: 0.35em;\">\n",
       "    Bach\n",
       "    <span style=\"font-size: 0.8em; font-weight: bold; line-height: 1; border-radius: 0.35em; vertical-align: middle; margin-left: 0.5rem\">PERSON</span>\n",
       "</mark>\n",
       " worked amid discouraging and unsatisfactory conditions.  The Rector, \n",
       "<mark class=\"entity\" style=\"background: #aa9cfc; padding: 0.45em 0.6em; margin: 0 0.25em; line-height: 1; border-radius: 0.35em;\">\n",
       "    Johann Heinrich Ernesti\n",
       "    <span style=\"font-size: 0.8em; font-weight: bold; line-height: 1; border-radius: 0.35em; vertical-align: middle; margin-left: 0.5rem\">PERSON</span>\n",
       "</mark>\n",
       ", was \n",
       "<mark class=\"entity\" style=\"background: #bfe1d9; padding: 0.45em 0.6em; margin: 0 0.25em; line-height: 1; border-radius: 0.35em;\">\n",
       "    over seventy years of age\n",
       "    <span style=\"font-size: 0.8em; font-weight: bold; line-height: 1; border-radius: 0.35em; vertical-align: middle; margin-left: 0.5rem\">DATE</span>\n",
       "</mark>\n",
       " in \n",
       "<mark class=\"entity\" style=\"background: #bfe1d9; padding: 0.45em 0.6em; margin: 0 0.25em; line-height: 1; border-radius: 0.35em;\">\n",
       "    1723\n",
       "    <span style=\"font-size: 0.8em; font-weight: bold; line-height: 1; border-radius: 0.35em; vertical-align: middle; margin-left: 0.5rem\">DATE</span>\n",
       "</mark>\n",
       ".  The School was badly managed, its discipline was relaxed, the better-to-do citizens withheld their sons from it, and its numbers were seriously diminished.  In \n",
       "<mark class=\"entity\" style=\"background: #bfe1d9; padding: 0.45em 0.6em; margin: 0 0.25em; line-height: 1; border-radius: 0.35em;\">\n",
       "    1717\n",
       "    <span style=\"font-size: 0.8em; font-weight: bold; line-height: 1; border-radius: 0.35em; vertical-align: middle; margin-left: 0.5rem\">DATE</span>\n",
       "</mark>\n",
       " the junior classes contained \n",
       "<mark class=\"entity\" style=\"background: #e4e7d2; padding: 0.45em 0.6em; margin: 0 0.25em; line-height: 1; border-radius: 0.35em;\">\n",
       "    only 53\n",
       "    <span style=\"font-size: 0.8em; font-weight: bold; line-height: 1; border-radius: 0.35em; vertical-align: middle; margin-left: 0.5rem\">CARDINAL</span>\n",
       "</mark>\n",
       " as against \n",
       "<mark class=\"entity\" style=\"background: #e4e7d2; padding: 0.45em 0.6em; margin: 0 0.25em; line-height: 1; border-radius: 0.35em;\">\n",
       "    120\n",
       "    <span style=\"font-size: 0.8em; font-weight: bold; line-height: 1; border-radius: 0.35em; vertical-align: middle; margin-left: 0.5rem\">CARDINAL</span>\n",
       "</mark>\n",
       " in \n",
       "<mark class=\"entity\" style=\"background: #aa9cfc; padding: 0.45em 0.6em; margin: 0 0.25em; line-height: 1; border-radius: 0.35em;\">\n",
       "    Ernesti\n",
       "    <span style=\"font-size: 0.8em; font-weight: bold; line-height: 1; border-radius: 0.35em; vertical-align: middle; margin-left: 0.5rem\">PERSON</span>\n",
       "</mark>\n",
       "'s earlier years.  The proximity and operatic traditions of \n",
       "<mark class=\"entity\" style=\"background: #aa9cfc; padding: 0.45em 0.6em; margin: 0 0.25em; line-height: 1; border-radius: 0.35em;\">\n",
       "    Dresden\n",
       "    <span style=\"font-size: 0.8em; font-weight: bold; line-height: 1; border-radius: 0.35em; vertical-align: middle; margin-left: 0.5rem\">PERSON</span>\n",
       "</mark>\n",
       " and \n",
       "<mark class=\"entity\" style=\"background: #aa9cfc; padding: 0.45em 0.6em; margin: 0 0.25em; line-height: 1; border-radius: 0.35em;\">\n",
       "    Weissenfels\n",
       "    <span style=\"font-size: 0.8em; font-weight: bold; line-height: 1; border-radius: 0.35em; vertical-align: middle; margin-left: 0.5rem\">PERSON</span>\n",
       "</mark>\n",
       " also had a bad effect; \n",
       "<mark class=\"entity\" style=\"background: #7aecec; padding: 0.45em 0.6em; margin: 0 0.25em; line-height: 1; border-radius: 0.35em;\">\n",
       "    the St. Thomas'\n",
       "    <span style=\"font-size: 0.8em; font-weight: bold; line-height: 1; border-radius: 0.35em; vertical-align: middle; margin-left: 0.5rem\">ORG</span>\n",
       "</mark>\n",
       " boys, after attaining musical proficiency, were apt to become restless, demanding release from their indentures, and even running away to more attractive and lucrative occupations.  Moreover, the governors of the School were \n",
       "<mark class=\"entity\" style=\"background: #7aecec; padding: 0.45em 0.6em; margin: 0 0.25em; line-height: 1; border-radius: 0.35em;\">\n",
       "    the Town Council\n",
       "    <span style=\"font-size: 0.8em; font-weight: bold; line-height: 1; border-radius: 0.35em; vertical-align: middle; margin-left: 0.5rem\">ORG</span>\n",
       "</mark>\n",
       ", a body which had little sympathy with or appreciation of \n",
       "<mark class=\"entity\" style=\"background: #aa9cfc; padding: 0.45em 0.6em; margin: 0 0.25em; line-height: 1; border-radius: 0.35em;\">\n",
       "    Bach\n",
       "    <span style=\"font-size: 0.8em; font-weight: bold; line-height: 1; border-radius: 0.35em; vertical-align: middle; margin-left: 0.5rem\">PERSON</span>\n",
       "</mark>\n",
       "'s artistic aims and temperament.  To these difficulties must be added another.  \n",
       "<mark class=\"entity\" style=\"background: #7aecec; padding: 0.45em 0.6em; margin: 0 0.25em; line-height: 1; border-radius: 0.35em;\">\n",
       "    The Town Musicians\n",
       "    <span style=\"font-size: 0.8em; font-weight: bold; line-height: 1; border-radius: 0.35em; vertical-align: middle; margin-left: 0.5rem\">ORG</span>\n",
       "</mark>\n",
       ", on whom \n",
       "<mark class=\"entity\" style=\"background: #aa9cfc; padding: 0.45em 0.6em; margin: 0 0.25em; line-height: 1; border-radius: 0.35em;\">\n",
       "    Bach\n",
       "    <span style=\"font-size: 0.8em; font-weight: bold; line-height: 1; border-radius: 0.35em; vertical-align: middle; margin-left: 0.5rem\">PERSON</span>\n",
       "</mark>\n",
       " relied for the nucleus of his orchestra, were few in number and inefficient.</div></span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "doc = nlp(paragraphs[36])\n",
    "displacy.render(doc, style=\"ent\", jupyter=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "905a9673-fd63-4228-804f-696c92f29851",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Altnikol',\n",
       " 'Amalia',\n",
       " 'Anna Magdalena',\n",
       " 'August Friedrich Müller',\n",
       " 'Augustus II.',\n",
       " 'Augustus III',\n",
       " 'Bach',\n",
       " 'Bachs',\n",
       " 'Bass',\n",
       " 'Beethoven',\n",
       " 'Benda',\n",
       " 'Berardi',\n",
       " 'Bruhns',\n",
       " 'Buononcini',\n",
       " 'Buxtehude',\n",
       " 'Böhm',\n",
       " 'CARL PHILIPP EMMANUEL BACH',\n",
       " 'Caldara',\n",
       " 'Cantor',\n",
       " 'Carl Philipp',\n",
       " 'Carl Philipp Emmanuel',\n",
       " \"Carl Philipp Emmanuel Bach's\",\n",
       " \"Carl Philipp Emmanuel's\",\n",
       " \"Carl Philipp's\",\n",
       " 'Charles',\n",
       " 'Charles III.',\n",
       " 'Christian',\n",
       " 'Christian Weiss',\n",
       " 'Christiana Eberhardine',\n",
       " 'Clavichord',\n",
       " 'Clavier',\n",
       " 'Clavierhe',\n",
       " 'Contrabasso',\n",
       " 'Couperin',\n",
       " 'Couperinhad',\n",
       " 'Courantes',\n",
       " 'Cöthen',\n",
       " 'Dietrich Buxtehude',\n",
       " 'Dresden',\n",
       " 'Elias Gottlieb Haussmann',\n",
       " 'Erdmann',\n",
       " 'Ernesti',\n",
       " 'Ernst August',\n",
       " 'Fagotti',\n",
       " 'Faustina',\n",
       " 'Fischer',\n",
       " 'Flemming',\n",
       " 'Flutes',\n",
       " 'Frederick the Great',\n",
       " 'Frescobaldi',\n",
       " 'Fritz Volbach',\n",
       " 'Froberger',\n",
       " 'Fux',\n",
       " 'Georg Philipp Telemann',\n",
       " 'Gesner',\n",
       " 'Goldberg',\n",
       " 'Gottlieb Kortte',\n",
       " 'Grauns',\n",
       " 'Görner',\n",
       " 'H.Bach',\n",
       " 'Handel',\n",
       " 'Harpsichord',\n",
       " 'Hasse',\n",
       " 'Haussmann',\n",
       " 'Hoffmeister',\n",
       " 'Hurlebusch',\n",
       " 'Hymn',\n",
       " 'J. C. Gottsched',\n",
       " 'JOHANN CASPAR VOGLER',\n",
       " 'JOHANN CHRISTIAN BACH',\n",
       " 'JOHANN CHRISTOPH FRIEDRICH BACH',\n",
       " 'Johann Abraham Birnbaum',\n",
       " 'Johann Ambrosius',\n",
       " 'Johann Ambrosius Bach',\n",
       " 'Johann August Ernesti',\n",
       " 'Johann Caspar Kerl',\n",
       " 'Johann Christian Hoffmann',\n",
       " 'Johann Christoph',\n",
       " \"Johann Christoph Friedrich's\",\n",
       " 'Johann Gottlieb Görner',\n",
       " 'Johann Heinrich Ernesti',\n",
       " 'Johann Matthias Gesner',\n",
       " 'Johann Sebastian',\n",
       " 'Johann Sebastian Bach',\n",
       " \"Johann Sebastian Bach's\",\n",
       " 'JohannAdam Reinken',\n",
       " 'Kaiserling',\n",
       " 'Kerl',\n",
       " 'Kirchhoff',\n",
       " 'Kirnberger',\n",
       " 'Kittel',\n",
       " 'Kuhnau',\n",
       " 'Kühnel',\n",
       " 'Leopold',\n",
       " 'Marchand',\n",
       " 'Maria Amalia',\n",
       " 'Marianne von Ziegler',\n",
       " 'Mizler',\n",
       " 'Müller',\n",
       " 'Oboe',\n",
       " 'Ohrdruf',\n",
       " 'Organ',\n",
       " 'Organist',\n",
       " 'Organs Bach',\n",
       " 'Pachelbel',\n",
       " 'Paul Wagner',\n",
       " 'Peters',\n",
       " 'Philipp Emmanuel',\n",
       " 'Picander',\n",
       " 'Professoriate',\n",
       " 'Providence',\n",
       " 'Quantzhas',\n",
       " 'Quartet',\n",
       " 'Regine Susanna',\n",
       " 'Reinhard Keiser',\n",
       " 'Reinken',\n",
       " 'Scheibe',\n",
       " 'Schiller',\n",
       " 'Silbermann',\n",
       " 'Sorge',\n",
       " 'Strungk',\n",
       " 'Taille',\n",
       " 'Telemann',\n",
       " 'Timpani',\n",
       " 'Tromba',\n",
       " 'Trombe',\n",
       " 'Veit Bach',\n",
       " \"Veit Bach's\",\n",
       " 'Viola',\n",
       " 'Violas',\n",
       " 'Violins',\n",
       " 'Violoncelli',\n",
       " 'Violoncello',\n",
       " 'Vivaldi',\n",
       " 'Volumier',\n",
       " 'WILHELM FRIEDEMANN BACH',\n",
       " 'Weissenfels',\n",
       " 'Wilhelm Friedemann',\n",
       " 'Winkler',\n",
       " 'Zachau',\n",
       " 'Zelenka',\n",
       " 'modelsBach',\n",
       " 'whenBach'}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_names = set()\n",
    "for doc in nlp.pipe(paragraphs):\n",
    "    for name in [ent.text for ent in doc.ents if ent.label_ == 'PERSON']:\n",
    "        all_names.add(name)\n",
    "all_names"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ecc5b28-b820-4a03-967d-4fabfafc4f54",
   "metadata": {},
   "source": [
    "There are some items being mistakenly detected as names, such as Clavier (piano). From now on, these will be removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "bf7cfea1-1d63-45b4-bb0b-e12f853f9e46",
   "metadata": {},
   "outputs": [],
   "source": [
    "# List of personal names for Removing Named Entities incorrectly detected as PERSON by spaCy\n",
    "CLEANED_NAMES =['Altnikol',\n",
    " 'Amalia',\n",
    " 'Anna Magdalena',\n",
    " 'August Friedrich Müller',\n",
    " 'Augustus II.',\n",
    " 'Augustus III',\n",
    " 'Bach',\n",
    " 'Beethoven',\n",
    " 'Benda',\n",
    " 'Berardi',\n",
    " 'Bruhns',\n",
    " 'Buononcini',\n",
    " 'Buxtehude',\n",
    " 'Böhm',\n",
    " 'CARL PHILIPP EMMANUEL BACH',\n",
    " 'Caldara',\n",
    " 'Carl Philipp',\n",
    " 'Carl Philipp Emmanuel',\n",
    " \"Carl Philipp Emmanuel Bach's\",\n",
    " \"Carl Philipp Emmanuel's\",\n",
    " \"Carl Philipp's\",\n",
    " 'Charles',\n",
    " 'Charles III.',\n",
    " 'Christian',\n",
    " 'Christian Weiss',\n",
    " 'Christiana Eberhardine',\n",
    " 'Couperin',\n",
    " 'Dietrich Buxtehude',\n",
    " 'Elias Gottlieb Haussmann',\n",
    " 'Erdmann',\n",
    " 'Ernesti',\n",
    " 'Ernst August',\n",
    " 'Faustina',\n",
    " 'Fischer',\n",
    " 'Flemming',\n",
    " 'Frederick the Great',\n",
    " 'Frescobaldi',\n",
    " 'Fritz Volbach',\n",
    " 'Froberger',\n",
    " 'Fux',\n",
    " 'Georg Philipp Telemann',\n",
    " 'Gesner',\n",
    " 'Goldberg',\n",
    " 'Gottlieb Kortte',\n",
    " 'Grauns',\n",
    " 'Görner',\n",
    " 'H.Bach',\n",
    " 'Handel',\n",
    " 'Hasse',\n",
    " 'Haussmann',\n",
    " 'Hoffmeister',\n",
    " 'Hurlebusch',\n",
    " 'J. C. Gottsched',\n",
    " 'JOHANN CASPAR VOGLER',\n",
    " 'JOHANN CHRISTIAN BACH',\n",
    " 'JOHANN CHRISTOPH FRIEDRICH BACH',\n",
    " 'Johann Abraham Birnbaum',\n",
    " 'Johann Ambrosius',\n",
    " 'Johann Ambrosius Bach',\n",
    " 'Johann August Ernesti',\n",
    " 'Johann Caspar Kerl',\n",
    " 'Johann Christian Hoffmann',\n",
    " 'Johann Christoph',\n",
    " \"Johann Christoph Friedrich's\",\n",
    " 'Johann Gottlieb Görner',\n",
    " 'Johann Heinrich Ernesti',\n",
    " 'Johann Matthias Gesner',\n",
    " 'Johann Sebastian',\n",
    " 'Johann Sebastian Bach',\n",
    " \"Johann Sebastian Bach's\",\n",
    " 'JohannAdam Reinken',\n",
    " 'Kaiserling',\n",
    " 'Kerl',\n",
    " 'Kirchhoff',\n",
    " 'Kirnberger',\n",
    " 'Kittel',\n",
    " 'Kuhnau',\n",
    " 'Kühnel',\n",
    " 'Leopold',\n",
    " 'Marchand',\n",
    " 'Maria Amalia',\n",
    " 'Marianne von Ziegler',\n",
    " 'Mizler',\n",
    " 'Müller',\n",
    " 'Pachelbel',\n",
    " 'Paul Wagner',\n",
    " 'Peters',\n",
    " 'Philipp Emmanuel',\n",
    " 'Picander',\n",
    " 'Regine Susanna',\n",
    " 'Reinhard Keiser',\n",
    " 'Reinken',\n",
    " 'Scheibe',\n",
    " 'Schiller',\n",
    " 'Silbermann',\n",
    " 'Strungk',\n",
    " 'Taille',\n",
    " 'Telemann',\n",
    " 'Veit Bach',\n",
    " \"Veit Bach's\",\n",
    " 'Vivaldi',\n",
    " 'Volumier',\n",
    " 'WILHELM FRIEDEMANN BACH',\n",
    " 'Wilhelm Friedemann',\n",
    " 'Winkler',\n",
    " 'Zelenka']\n",
    "\n",
    "# A list to convert Bach family names into full names\n",
    "REPLACE = {'Bach': 'Johann Sebastian Bach',\n",
    "           'Johann Sebastian': 'Johann Sebastian Bach',\n",
    "           'Sebastian Bach': 'Johann Sebastian Bach',\n",
    "           'Carl Philipp': 'Carl Philipp Emanuel Bach',\n",
    "           'Carl Philipp Emanuel': 'Carl Philipp Emanuel Bach',\n",
    "           'Carl Philipp Emmanuel': 'Carl Philipp Emanuel Bach',\n",
    "           'CARL PHILIPP EMMANUEL BACH': 'Carl Philipp Emanuel Bach',\n",
    "           'Emmanuel Bach': 'Carl Philipp Emanuel Bach',\n",
    "            \"Carl Philipp Emmanuel Bach's\": 'Carl Philipp Emanuel Bach',\n",
    "            \"Carl Philipp Emmanuel's\": 'Carl Philipp Emanuel Bach',\n",
    "            \"Carl Philipp's\": 'Carl Philipp Emanuel Bach',\n",
    "           'Wilhelm Friedemann': 'Wilhelm Friedemann Bach',\n",
    "           'WILHELM FRIEDEMANN BACH': 'Wilhelm Friedemann Bach',\n",
    "           'Friedemann': 'Wilhelm Friedemann Bach',\n",
    "           'Johann Christoph': 'Johann Christoph Bach',\n",
    "           'Anna Magdalena': 'Anna Magdalena Bach',\n",
    "           'Regine Susanna': 'Regine Susanna Bach',\n",
    "           'Johann Ambrosius': 'Johann Ambrosius Bach',\n",
    "           'Frederick II': 'Frederick the Great',\n",
    "           'Telemann': 'Georg Philipp Telemann',\n",
    "           'Amalia': 'Maria Amalia',\n",
    "           'Müller': 'August Friedrich Müller',\n",
    "           'Charles': 'Charles III.',\n",
    "           'Christian': 'Christian Weiss',\n",
    "           'Buxtehude': 'Dietrich Buxtehude',\n",
    "           'Haussmann': 'Elias Gottlieb Haussmann',\n",
    "           'JOHANN CASPAR VOGLER':'Johann Caspar Vogler',\n",
    "           'JOHANN CHRISTIAN BACH': 'Johann Christian Bach',\n",
    "           'JOHANN CHRISTOPH FRIEDRICH BACH': 'Johann Christoph Friedrich Bach',\n",
    "           'Görner': 'Johann Gottlieb Görner'\n",
    "          }"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54abc34c-4176-4c8e-9fab-bc73a47ac934",
   "metadata": {},
   "source": [
    "## Export data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "bb7ee235-39da-4f9b-a186-c8bae7ab0a99",
   "metadata": {},
   "outputs": [],
   "source": [
    "export_data = {\n",
    "    'named_entity': CLEANED_NAMES,\n",
    "    'replace': REPLACE\n",
    "}\n",
    "\n",
    "import sqlite3\n",
    "import pandas as pd\n",
    "\n",
    "names = pd.Series(CLEANED_NAMES, name='name')\n",
    "names_replace = pd.DataFrame({'original': REPLACE.keys(), 'replace': REPLACE.values()})\n",
    "with sqlite3.connect('../data/bach.db') as conn:\n",
    "    names.to_sql('names', conn, if_exists='replace')    \n",
    "    names_replace.to_sql('names_replace', conn, if_exists='replace')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}