data_code/convert_csv_data_v3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# OpenHebrewBible (OHB) CSV Data to SQL Conversion V2\n",
    "\n",
    "Eliran Wong used date from [ETCBC](https://github.com/ETCBC/bhsa) (Hebrew text BHSA, linguistic annotations, morphology, etc.), [OpenScriptures](https://github.com/openscriptures/morphhb) (Hebrew text WLC, Strong's numbers, morphology, etc.), and [Berean.bible](https://berean.bible) (interlinear translation, Berean Study Bible, etc.) to create a robust data repository called [OpenHebrewBible](https://github.com/eliranwong/OpenHebrewBible), consisting of CSV files that bridge the other three open-source projects.\n",
    "\n",
    "I will take his compiled data file, [BHSA-with-extended-features.csv](https://github.com/eliranwong/OpenHebrewBible/blob/master/BHSA-with-extended-features.csv.zip), clean it, and convert it into a SQL database that I can use in my Flutter app. He also converted the BHSA TF 4c word data into a SQL DB file, [ETCBC4c.db](https://github.com/eliranwong/ETCBC-recycle/blob/master/sqlite3/ETCBC4c.db.zip). I will convert both the CSV and DB files to dataframes and combine useful data into a new dataframe. Later I will compare the converted combined dataframe to a BHSA SQL file, which can be downloaded [here](https://www.adambaker.org/bhsa.sqlite). -- side node: See BHSA 4C generated by James Cuenod for direct DB creation from TF API - I will be using his method to add clause_atom data -- Finally, I will convert the dataframe to a SQL database (after testing its data). Added: Incorporate STEP's Strong number data into a dataframe and also add as a table to the database.\n",
    "\n",
    "**KEY**\n",
    "- OHB_EXTENDED: [BHSA-with-extended-features.csv](https://github.com/eliranwong/OpenHebrewBible/blob/master/BHSA-with-extended-features.csv.zip)\n",
    "- OHB_DB: [ETCBC4c.db](https://github.com/eliranwong/ETCBC-recycle/blob/master/sqlite3/ETCBC4c.db.zip)\n",
    "- BHSA_DB: [bhsa.sqlite](https://www.adambaker.org/bhsa.sqlite)\n",
    "- BH4C_DB: 4c.db, generated directly from TF API by [James](https://github.com/jcuenod/parabible-data-pipeline/blob/master/hb-bhs-pipe/scripts/create_sql_from_tf.py) (set version to c: A = use('bhsa', hoist=globals(), checkout='local', version='c'))\n",
    "- TBESH_DB: [TBESH.csv](https://github.com/STEPBible/STEPBible-Data/blob/master/TBESH%20-%20Translators%20Brief%20lexicon%20of%20Extended%20Strongs%20for%20Hebrew%20-%20STEPBible.org%20CC%20BY.txt) (after removing the descriptive text at the top of the document).\n",
    "\n",
    "### Why use OHB data when BHSA already exists?\n",
    "Some features that could be useful from OHB data that aren't present in BHSA are:\n",
    "- Strong's number mapped to each node in the BHS.\n",
    "- Data to align the BHS text with KJV and BSB translations.\n",
    "- Poetic divisions (not tested).\n",
    "- BSB gloss for a more accurate rendering of each word. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\npip3 install pandas\\npip3 install numpy\\npip3 install text-fabric\\npip3 install jupyter\\n'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Requirements: run in terminal. Change to 'pip' if on Windows OS. \n",
    "\"\"\"\n",
    "pip3 install pandas\n",
    "pip3 install numpy\n",
    "pip3 install text-fabric\n",
    "pip3 install jupyter\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3\n",
    "import numpy as np\n",
    "import copy\n",
    "from tf.app import use\n",
    "from IPython.display import display, HTML\n",
    "\n",
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Constants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Files\n",
    "BH4C_DB_PATH = '../data_files/BHSA/4c.db'\n",
    "BHSA_DB_PATH = '../data_files/BHSA/bhsa.sqlite'\n",
    "OHB_DB_PATH = '../data_files/OHB/ETCBC4c.db'\n",
    "OHB_EXTENDED_PATH = '../data_files/OHB/BHSA-with-extended-features.csv'\n",
    "TBESH_PATH = '../data_files/STEP/TBESH.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "BH4C_DB Data loaded\n",
      "BHSA_DB Data loaded\n",
      "OHB_DB Data loaded\n",
      "OHB_EXTENDED Data loaded\n",
      "TBESH_DF Data loaded\n"
     ]
    }
   ],
   "source": [
    "# DB Connections and Dataframes\n",
    "BH4C_DB_CON = sqlite3.connect(BH4C_DB_PATH)\n",
    "BH4C_DB_DF = pd.read_sql_query(\"SELECT * FROM word_features\", BH4C_DB_CON)\n",
    "print('BH4C_DB Data loaded')\n",
    "\n",
    "BHSA_DB_CON = sqlite3.connect(BHSA_DB_PATH)\n",
    "BHSA_DB_DF = pd.read_sql_query(\"SELECT * FROM word\", BHSA_DB_CON)\n",
    "print('BHSA_DB Data loaded')\n",
    "\n",
    "OHB_DB_CON = sqlite3.connect(OHB_DB_PATH)\n",
    "OHB_DB_DF = pd.read_sql_query(\"SELECT * FROM data\", OHB_DB_CON)\n",
    "print('OHB_DB Data loaded')\n",
    "\n",
    "# Set low_memory to False to deal with unexpected data types. \n",
    "# Converts those data to NaN.\n",
    "OHB_EXTENDED_DF = pd.read_csv(OHB_EXTENDED_PATH, sep='\\t', low_memory=False)\n",
    "print('OHB_EXTENDED Data loaded')\n",
    "\n",
    "TBESH_DF = pd.read_csv(TBESH_PATH, sep='\\t', low_memory=False, index_col=False)\n",
    "print('TBESH_DF Data loaded')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The Data\n",
    "\n",
    "The BHSA_DB file consists of all the TF word data from the BHSA dataset -- every word in the BHS Hebrew Old Testament as a node with dozens of features.\n",
    "\n",
    "The OHB_DB file consists of some of that data along with KJV verse and chapter alignment. \n",
    "\n",
    "The OHB_EXTENDED file consists of some of the BHSA data along with added features. It has 22 feature columns and uses tab-separated delineation. All of the data consists of strings or positive integers. \n",
    "\n",
    "You can view the three dataframes below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>_id</th>\n",
       "      <th>freq_lex</th>\n",
       "      <th>freq_occ</th>\n",
       "      <th>g_cons</th>\n",
       "      <th>g_cons_utf8</th>\n",
       "      <th>g_lex</th>\n",
       "      <th>g_lex_utf8</th>\n",
       "      <th>g_nme</th>\n",
       "      <th>g_nme_utf8</th>\n",
       "      <th>g_pfm</th>\n",
       "      <th>g_pfm_utf8</th>\n",
       "      <th>g_prs</th>\n",
       "      <th>g_prs_utf8</th>\n",
       "      <th>g_uvf</th>\n",
       "      <th>g_uvf_utf8</th>\n",
       "      <th>g_vbe</th>\n",
       "      <th>g_vbe_utf8</th>\n",
       "      <th>g_vbs</th>\n",
       "      <th>g_vbs_utf8</th>\n",
       "      <th>g_word</th>\n",
       "      <th>g_word_utf8</th>\n",
       "      <th>gloss</th>\n",
       "      <th>gn</th>\n",
       "      <th>kq_hybrid</th>\n",
       "      <th>kq_hybrid_utf8</th>\n",
       "      <th>language</th>\n",
       "      <th>languageISO</th>\n",
       "      <th>lex</th>\n",
       "      <th>lex0</th>\n",
       "      <th>lex_utf8</th>\n",
       "      <th>lexeme_count</th>\n",
       "      <th>ls</th>\n",
       "      <th>nametype</th>\n",
       "      <th>nme</th>\n",
       "      <th>nu</th>\n",
       "      <th>number</th>\n",
       "      <th>pdp</th>\n",
       "      <th>pfm</th>\n",
       "      <th>prs</th>\n",
       "      <th>prs_gn</th>\n",
       "      <th>prs_nu</th>\n",
       "      <th>prs_ps</th>\n",
       "      <th>ps</th>\n",
       "      <th>qere</th>\n",
       "      <th>qere_trailer</th>\n",
       "      <th>qere_trailer_utf8</th>\n",
       "      <th>qere_utf8</th>\n",
       "      <th>rank_lex</th>\n",
       "      <th>rank_occ</th>\n",
       "      <th>sp</th>\n",
       "      <th>st</th>\n",
       "      <th>suffix_gender</th>\n",
       "      <th>suffix_number</th>\n",
       "      <th>suffix_person</th>\n",
       "      <th>trailer</th>\n",
       "      <th>trailer_utf8</th>\n",
       "      <th>uvf</th>\n",
       "      <th>vbe</th>\n",
       "      <th>vbs</th>\n",
       "      <th>voc_lex</th>\n",
       "      <th>voc_lex_utf8</th>\n",
       "      <th>vs</th>\n",
       "      <th>vt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>15542</td>\n",
       "      <td>14194</td>\n",
       "      <td>B</td>\n",
       "      <td>ב</td>\n",
       "      <td>B.:-</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>B.:-</td>\n",
       "      <td>בְּ</td>\n",
       "      <td>in</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>B</td>\n",
       "      <td>B</td>\n",
       "      <td>ב</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td>n/a</td>\n",
       "      <td>NA</td>\n",
       "      <td>1</td>\n",
       "      <td>prep</td>\n",
       "      <td>n/a</td>\n",
       "      <td>absent</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>prep</td>\n",
       "      <td>NA</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>B.:</td>\n",
       "      <td>בְּ</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>51</td>\n",
       "      <td>45</td>\n",
       "      <td>R&gt;CJT</td>\n",
       "      <td>ראשׁית</td>\n",
       "      <td>R;&gt;CIJT</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>R;&gt;CI73JT</td>\n",
       "      <td>רֵאשִׁ֖ית</td>\n",
       "      <td>beginning</td>\n",
       "      <td>f</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>R&gt;CJT/</td>\n",
       "      <td>R&gt;CJT</td>\n",
       "      <td>ראשׁית</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>sg</td>\n",
       "      <td>2</td>\n",
       "      <td>subs</td>\n",
       "      <td>n/a</td>\n",
       "      <td>absent</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>706</td>\n",
       "      <td>868</td>\n",
       "      <td>subs</td>\n",
       "      <td>a</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>R;&gt;CIJT</td>\n",
       "      <td>רֵאשִׁית</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>48</td>\n",
       "      <td>15</td>\n",
       "      <td>BR&gt;</td>\n",
       "      <td>ברא</td>\n",
       "      <td>B.@R@&gt;</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>B.@R@74&gt;</td>\n",
       "      <td>בָּרָ֣א</td>\n",
       "      <td>create</td>\n",
       "      <td>m</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>BR&gt;[</td>\n",
       "      <td>BR&gt;</td>\n",
       "      <td>ברא</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td>absent</td>\n",
       "      <td>sg</td>\n",
       "      <td>3</td>\n",
       "      <td>verb</td>\n",
       "      <td>absent</td>\n",
       "      <td>absent</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>p3</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>745</td>\n",
       "      <td>2341</td>\n",
       "      <td>verb</td>\n",
       "      <td>NA</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>BR&gt;</td>\n",
       "      <td>ברא</td>\n",
       "      <td>qal</td>\n",
       "      <td>perf</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# BHSA_DB Word Data\n",
    "display(HTML(BHSA_DB_DF.head(n=3).to_html(index=False)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>word_ID</th>\n",
       "      <th>Book</th>\n",
       "      <th>ch_BHS</th>\n",
       "      <th>v_BHS</th>\n",
       "      <th>ch_KJV</th>\n",
       "      <th>v_KJV</th>\n",
       "      <th>manuscript</th>\n",
       "      <th>transliteration</th>\n",
       "      <th>lex_Hebrew</th>\n",
       "      <th>lex_number</th>\n",
       "      <th>gloss_Eng</th>\n",
       "      <th>lang</th>\n",
       "      <th>lang_def</th>\n",
       "      <th>morph_pdp</th>\n",
       "      <th>morph_pdp_def</th>\n",
       "      <th>morph_sp</th>\n",
       "      <th>morph_sp_def</th>\n",
       "      <th>morph_vs</th>\n",
       "      <th>morph_vs_def</th>\n",
       "      <th>morph_vt</th>\n",
       "      <th>morph_vt_def</th>\n",
       "      <th>morph_ps</th>\n",
       "      <th>morph_ps_def</th>\n",
       "      <th>morph_gn</th>\n",
       "      <th>morph_gn_def</th>\n",
       "      <th>morph_nu</th>\n",
       "      <th>morph_nu_def</th>\n",
       "      <th>morph_st</th>\n",
       "      <th>morph_st_def</th>\n",
       "      <th>prs_ps</th>\n",
       "      <th>prs_ps_def</th>\n",
       "      <th>prs_gn</th>\n",
       "      <th>prs_gn_def</th>\n",
       "      <th>prs_nu</th>\n",
       "      <th>prs_nu_def</th>\n",
       "      <th>clause_markers</th>\n",
       "      <th>clause_kind</th>\n",
       "      <th>clause_typ</th>\n",
       "      <th>clause_rela</th>\n",
       "      <th>phrase_markers</th>\n",
       "      <th>phrase_typ</th>\n",
       "      <th>phrase_rela</th>\n",
       "      <th>phrase_det</th>\n",
       "      <th>phrase_function</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Gen</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>בְּ</td>\n",
       "      <td>bᵊ</td>\n",
       "      <td>בְּ</td>\n",
       "      <td>L70001</td>\n",
       "      <td>in</td>\n",
       "      <td>hbo</td>\n",
       "      <td>Ancient Hebrew</td>\n",
       "      <td>prep</td>\n",
       "      <td>preposition</td>\n",
       "      <td>prep</td>\n",
       "      <td>preposition</td>\n",
       "      <td>NA</td>\n",
       "      <td>not applicable</td>\n",
       "      <td>NA</td>\n",
       "      <td>not applicable</td>\n",
       "      <td>NA</td>\n",
       "      <td>not applicable</td>\n",
       "      <td>NA</td>\n",
       "      <td>not applicable</td>\n",
       "      <td>NA</td>\n",
       "      <td>not applicable</td>\n",
       "      <td>NA</td>\n",
       "      <td>not applicable</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>「</td>\n",
       "      <td>Verbal clauses</td>\n",
       "      <td>x-qatal-X clause</td>\n",
       "      <td></td>\n",
       "      <td>『</td>\n",
       "      <td>Prepositional phrase</td>\n",
       "      <td></td>\n",
       "      <td>undetermined</td>\n",
       "      <td>Time reference</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Gen</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>רֵאשִׁ֖ית</td>\n",
       "      <td>rēšˌîṯ</td>\n",
       "      <td>רֵאשִׁית</td>\n",
       "      <td>L70002</td>\n",
       "      <td>beginning</td>\n",
       "      <td>hbo</td>\n",
       "      <td>Ancient Hebrew</td>\n",
       "      <td>subs</td>\n",
       "      <td>noun</td>\n",
       "      <td>subs</td>\n",
       "      <td>noun</td>\n",
       "      <td>NA</td>\n",
       "      <td>not applicable</td>\n",
       "      <td>NA</td>\n",
       "      <td>not applicable</td>\n",
       "      <td>NA</td>\n",
       "      <td>not applicable</td>\n",
       "      <td>f</td>\n",
       "      <td>feminine</td>\n",
       "      <td>sg</td>\n",
       "      <td>singular</td>\n",
       "      <td>a</td>\n",
       "      <td>absolute</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td>Verbal clauses</td>\n",
       "      <td>x-qatal-X clause</td>\n",
       "      <td></td>\n",
       "      <td>』</td>\n",
       "      <td>Prepositional phrase</td>\n",
       "      <td></td>\n",
       "      <td>undetermined</td>\n",
       "      <td>Time reference</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Gen</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>בָּרָ֣א</td>\n",
       "      <td>bārˈā</td>\n",
       "      <td>ברא</td>\n",
       "      <td>L70003</td>\n",
       "      <td>create</td>\n",
       "      <td>hbo</td>\n",
       "      <td>Ancient Hebrew</td>\n",
       "      <td>verb</td>\n",
       "      <td>verb</td>\n",
       "      <td>verb</td>\n",
       "      <td>verb</td>\n",
       "      <td>qal</td>\n",
       "      <td>qal</td>\n",
       "      <td>perf</td>\n",
       "      <td>perfect</td>\n",
       "      <td>p3</td>\n",
       "      <td>third person</td>\n",
       "      <td>m</td>\n",
       "      <td>masculine</td>\n",
       "      <td>sg</td>\n",
       "      <td>singular</td>\n",
       "      <td>NA</td>\n",
       "      <td>not applicable</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td>Verbal clauses</td>\n",
       "      <td>x-qatal-X clause</td>\n",
       "      <td></td>\n",
       "      <td>『』</td>\n",
       "      <td>Verbal phrase</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Predicate</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# OHB_DB Data\n",
    "display(HTML(OHB_DB_DF.head(n=3).to_html(index=False)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>BHSwordSort</th>\n",
       "      <th>paragraphMarker</th>\n",
       "      <th>poetryMarker</th>\n",
       "      <th>〔KJVverseSort｜KJVbook｜KJVchapter｜KJVverse〕</th>\n",
       "      <th>〔BHSverseSort｜BHSbook｜BHSchapter｜BHSverse〕</th>\n",
       "      <th>clauseID</th>\n",
       "      <th>clauseKind</th>\n",
       "      <th>clauseType</th>\n",
       "      <th>language</th>\n",
       "      <th>BHSwordPointed</th>\n",
       "      <th>BHSwordConsonantal</th>\n",
       "      <th>SBLstyleTransliteration</th>\n",
       "      <th>poneticTranscription</th>\n",
       "      <th>HebrewLexeme</th>\n",
       "      <th>lexemeID</th>\n",
       "      <th>StrongNumber</th>\n",
       "      <th>extendedStrongNumber</th>\n",
       "      <th>morphologyCode</th>\n",
       "      <th>morphologyDetail</th>\n",
       "      <th>ETCBCgloss</th>\n",
       "      <th>extendedGloss</th>\n",
       "      <th>〔BSBsort＠BSB〕</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>¶</td>\n",
       "      <td>NaN</td>\n",
       "      <td>〔1｜1｜1｜1〕</td>\n",
       "      <td>〔1｜1｜1｜1〕</td>\n",
       "      <td>c1</td>\n",
       "      <td>Verbal clauses</td>\n",
       "      <td>x-qatal-X clause</td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>&lt;heb&gt;בְּ&lt;/heb&gt;&lt;heb&gt;&lt;/heb&gt;</td>\n",
       "      <td>&lt;heb&gt;ב&lt;/heb&gt;&lt;heb&gt;&lt;/heb&gt;</td>\n",
       "      <td>bĕ</td>\n",
       "      <td>bᵊ</td>\n",
       "      <td>&lt;heb&gt;בְּ&lt;/heb&gt;</td>\n",
       "      <td>E70001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>H9003</td>\n",
       "      <td>prep</td>\n",
       "      <td>preposition</td>\n",
       "      <td>in</td>\n",
       "      <td>in</td>\n",
       "      <td>〔1＠In〕</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>〔1｜1｜1｜1〕</td>\n",
       "      <td>〔1｜1｜1｜1〕</td>\n",
       "      <td>c1</td>\n",
       "      <td>Verbal clauses</td>\n",
       "      <td>x-qatal-X clause</td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>&lt;heb&gt;רֵאשִׁ֖ית&lt;/heb&gt;&lt;heb&gt; &lt;/heb&gt;</td>\n",
       "      <td>&lt;heb&gt;ראשית&lt;/heb&gt;&lt;heb&gt; &lt;/heb&gt;</td>\n",
       "      <td>rēšît</td>\n",
       "      <td>rēšˌîṯ</td>\n",
       "      <td>&lt;heb&gt;רֵאשִׁית&lt;/heb&gt;</td>\n",
       "      <td>E70002</td>\n",
       "      <td>H7225</td>\n",
       "      <td>H7225</td>\n",
       "      <td>subs.f.sg.a</td>\n",
       "      <td>noun, feminine, singular, absolute</td>\n",
       "      <td>beginning</td>\n",
       "      <td>beginning</td>\n",
       "      <td>〔2＠the beginning〕</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>〔1｜1｜1｜1〕</td>\n",
       "      <td>〔1｜1｜1｜1〕</td>\n",
       "      <td>c1</td>\n",
       "      <td>Verbal clauses</td>\n",
       "      <td>x-qatal-X clause</td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>&lt;heb&gt;בָּרָ֣א&lt;/heb&gt;&lt;heb&gt; &lt;/heb&gt;</td>\n",
       "      <td>&lt;heb&gt;ברא&lt;/heb&gt;&lt;heb&gt; &lt;/heb&gt;</td>\n",
       "      <td>bārā</td>\n",
       "      <td>bārˈā</td>\n",
       "      <td>&lt;heb&gt;ברא&lt;/heb&gt;</td>\n",
       "      <td>E70003</td>\n",
       "      <td>H1254</td>\n",
       "      <td>H1254</td>\n",
       "      <td>verb.qal.perf.p3.m.sg</td>\n",
       "      <td>verb, qal, perfect, third person, masculine, singular</td>\n",
       "      <td>create</td>\n",
       "      <td>[he]+ create</td>\n",
       "      <td>〔4＠created〕</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# OHB_EXTENDED Data\n",
    "display(HTML(OHB_EXTENDED_DF.head(n=3).to_html(index=False)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>strongs</th>\n",
       "      <th>lex</th>\n",
       "      <th>transliteration</th>\n",
       "      <th>morph</th>\n",
       "      <th>gloss</th>\n",
       "      <th>definition</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>H0001</td>\n",
       "      <td>אָב</td>\n",
       "      <td>av</td>\n",
       "      <td>H:N-M</td>\n",
       "      <td>father</td>\n",
       "      <td>1) father of an individual&lt;br&gt;2) of God as father of his people&lt;br&gt;3) head or founder of a household, group, family, or clan&lt;br&gt;4) ancestor&lt;br&gt;4a) grandfather, forefathers - of person&lt;br&gt;4b) of people&lt;br&gt;5) originator or patron of a class, profession, or art&lt;br&gt;6) of producer, generator (fig.)&lt;br&gt;7) of benevolence and protection (fig.)&lt;br&gt;8) term of respect and honour&lt;br&gt;9) ruler or chief (spec.)&lt;br&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>H0002</td>\n",
       "      <td>אַב</td>\n",
       "      <td>av</td>\n",
       "      <td>A:N-M</td>\n",
       "      <td>father</td>\n",
       "      <td>1) father&lt;br&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>H0003</td>\n",
       "      <td>אֵב</td>\n",
       "      <td>ev</td>\n",
       "      <td>H:N-M</td>\n",
       "      <td>greenery</td>\n",
       "      <td>1) freshness, fresh green, green shoots, or greenery&lt;br&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# OHB_EXTENDED Data\n",
    "display(HTML(TBESH_DF.head(n=3).to_html(index=False)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning the Data (OHB_EXTENDED)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data to clean:\n",
    "```\n",
    "- 〔KJVverseSort｜KJVbook｜KJVchapter｜KJVverse〕: remove chars and place ints in new columns\n",
    "- 〔BHSverseSort｜BHSbook｜BHSchapter｜BHSverse〕: remove chars and place ints in new columns\n",
    "-  clauseID : remove 'c' prefix and convert to int\n",
    "-  BHSwordPointed : remove html tags, place word in list - trailer in new list -> new column\n",
    "-  BHSwordConsonantal : remove html tags, place word in list\n",
    "-  HebrewLexeme : remove html tags\n",
    "- 〔BSBsort＠BSB〕: remove chars and place int and string in new columns\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Clean Text and Clause Data\n",
    "\n",
    "**Important note:** certain nodes do not have a text value for *BHSwordPointed* or *BHSwordConsonantal* because of the nature of the Hebrew language. For example at BHS word node values 61-62 we have:\n",
    "```\n",
    "61  <heb>לָ</heb><heb></heb>     <heb>ל</heb><heb></heb>     <heb>לְ</heb>    H9005   prep    to\t\n",
    "62  <heb></heb><heb></heb>      <heb></heb><heb></heb>      <heb>הַ</heb>    H9009   art     the\t〔51＠the〕\n",
    "```\n",
    "This is from a clause in Genesis 5:1, with the Hebrew: וַיִּקְרָא אֱלֹהִים לָאוֹר יוֹם\n",
    "\n",
    "Node 62 is embedded into the word לָאוֹר, attached to the preposition via a patach, but the *he* (the) doesn't appear consonantly in the text.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Visualize pre-cleaned data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>BHSwordPointed</th>\n",
       "      <th>BHSwordConsonantal</th>\n",
       "      <th>HebrewLexeme</th>\n",
       "      <th>clauseID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>&lt;heb&gt;בְּ&lt;/heb&gt;&lt;heb&gt;&lt;/heb&gt;</td>\n",
       "      <td>&lt;heb&gt;ב&lt;/heb&gt;&lt;heb&gt;&lt;/heb&gt;</td>\n",
       "      <td>&lt;heb&gt;בְּ&lt;/heb&gt;</td>\n",
       "      <td>c1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>&lt;heb&gt;רֵאשִׁ֖ית&lt;/heb&gt;&lt;heb&gt; &lt;/heb&gt;</td>\n",
       "      <td>&lt;heb&gt;ראשית&lt;/heb&gt;&lt;heb&gt; &lt;/heb&gt;</td>\n",
       "      <td>&lt;heb&gt;רֵאשִׁית&lt;/heb&gt;</td>\n",
       "      <td>c1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>&lt;heb&gt;בָּרָ֣א&lt;/heb&gt;&lt;heb&gt; &lt;/heb&gt;</td>\n",
       "      <td>&lt;heb&gt;ברא&lt;/heb&gt;&lt;heb&gt; &lt;/heb&gt;</td>\n",
       "      <td>&lt;heb&gt;ברא&lt;/heb&gt;</td>\n",
       "      <td>c1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Data before being cleaned.\n",
    "display(HTML(\n",
    "    OHB_EXTENDED_DF[\n",
    "        [\"BHSwordPointed\", \n",
    "        \"BHSwordConsonantal\", \n",
    "        \"HebrewLexeme\", \n",
    "        \"clauseID\"]\n",
    "    ].head(n=3).to_html(index=False))\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Define functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Textual items in between word nodes, including paragraph markers, etc. \n",
    "text_extensions = {\n",
    "    '', '׃', '׃ ׆ ס ', ' ס ', '׃ ׆ ', '׃ ', ' ׀ ',\n",
    "    ' ', '׃ פ ', ' פ ', '׀ ', '׃ ׆ פ ', '־', '׃ ס '\n",
    "}\n",
    "\n",
    "# ---\n",
    "# Function that takes a column name from the original df and\n",
    "# returns cleaned text (word and extension) in two lists.\n",
    "# Use: BHS pointed and consonantal text, all of which is of a format similar\n",
    "# to : <heb>הָ</heb><heb></heb>. Be sure to update the df with the return value. \n",
    "def clean_text(col_name):\n",
    "    cleaned_text = []\n",
    "    trailers = []\n",
    "    # All of the junk html text present.\n",
    "    remove_items = \"/<arc>hebqrQR\"\n",
    "    # Either of these will appear between the word and extension.\n",
    "    seperator = [\"</heb><heb>\", \"</arc><arc>\"]\n",
    "    # Iterate over the original dataframe and clean the data. \n",
    "    for text_data in OHB_EXTENDED_DF[col_name]:\n",
    "        # Place | at center so we can later split the text data. \n",
    "        for sep in seperator:\n",
    "            if sep in text_data:\n",
    "                text_data = text_data.replace(sep, '|')\n",
    "        # Remove all extra items.\n",
    "        for char in remove_items:\n",
    "            if char in text_data:\n",
    "                text_data = text_data.replace(char, \"\")\n",
    "\n",
    "        # Note: I originally split each text and stored it in a list before \n",
    "        # appending to cleaned_text, but that caused an error when uploading \n",
    "        # to SQL because it needed an actual data type (e.g., string).\n",
    "        \n",
    "        # Add a text separated by | to cleaned text where pre '|' is \n",
    "        # a Heb word and post '|' is the trailer.\n",
    "        word = text_data.split('|')[0]\n",
    "        trailer = text_data.split('|')[1]\n",
    "        cleaned_text.append(word)\n",
    "        trailers.append(trailer)\n",
    "    \n",
    "    return cleaned_text, trailers\n",
    "\n",
    "\n",
    "# ---\n",
    "# Clean the text in the HebrewLexem column, all of which is\n",
    "# in a format similar to: <heb>הָ</heb>. \n",
    "def clean_lexemes(col_name):\n",
    "    cleaned_text = []\n",
    "    # Read comments from clean_text()\n",
    "    remove_items = \"/<arc>hebqrQR\"\n",
    "    # Iterate over the original dataframe and clean the data. \n",
    "    for text_data in OHB_EXTENDED_DF[col_name]:\n",
    "        # Remove all extra items.\n",
    "        for char in remove_items:\n",
    "            if char in text_data:\n",
    "                text_data = text_data.replace(char, \"\")\n",
    "        # Add the lexeme to the cleaned data.\n",
    "        cleaned_text.append(text_data)\n",
    "\n",
    "    return cleaned_text\n",
    "\n",
    "\n",
    "# ---\n",
    "# All clause data is of the format: c12. Remove the 'c's\n",
    "# in the clause data and convert to int type. \n",
    "def clean_clauses(col_name):\n",
    "    cleaned_ids = []\n",
    "    # Iterate over the original dataframe and clean the data. \n",
    "    for clause in OHB_EXTENDED_DF[col_name]:\n",
    "        cleaned_ids.append(int(clause.strip(\"c\")))\n",
    "        \n",
    "    return cleaned_ids\n",
    "\n",
    "# ---\n",
    "# All lexemeID data is of the format: E70001. Remove the 'E's\n",
    "# in the clause data and convert to int type - 70000. \n",
    "def clean_lex_ids(col_name):\n",
    "    cleaned_ids = []\n",
    "    # Iterate over the original dataframe and clean the data. \n",
    "    for clause in OHB_EXTENDED_DF[col_name]:\n",
    "        cleaned_ids.append(int(clause.strip(\"E\")) - 70000)\n",
    "        \n",
    "    return cleaned_ids"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Call the functions -> update dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Update the data frame with the cleaned text and clauses. \n",
    "words, trailers = clean_text(\"BHSwordPointed\")\n",
    "OHB_EXTENDED_DF[\"BHSwordPointed\"] = words\n",
    "OHB_EXTENDED_DF.insert(\n",
    "    OHB_EXTENDED_DF.columns.get_loc(\"BHSwordPointed\"), 'Trailer', trailers)\n",
    "OHB_EXTENDED_DF[\"BHSwordConsonantal\"] = clean_text(\"BHSwordConsonantal\")[0]\n",
    "OHB_EXTENDED_DF[\"HebrewLexeme\"] = clean_lexemes(\"HebrewLexeme\")\n",
    "OHB_EXTENDED_DF[\"clauseID\"] = clean_clauses(\"clauseID\")\n",
    "OHB_EXTENDED_DF[\"lexemeID\"] = clean_lex_ids(\"lexemeID\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Visualize cleaned data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>BHSwordPointed</th>\n",
       "      <th>BHSwordConsonantal</th>\n",
       "      <th>Trailer</th>\n",
       "      <th>HebrewLexeme</th>\n",
       "      <th>lexemeID</th>\n",
       "      <th>clauseID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>בְּ</td>\n",
       "      <td>ב</td>\n",
       "      <td></td>\n",
       "      <td>בְּ</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>רֵאשִׁ֖ית</td>\n",
       "      <td>ראשית</td>\n",
       "      <td></td>\n",
       "      <td>רֵאשִׁית</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>בָּרָ֣א</td>\n",
       "      <td>ברא</td>\n",
       "      <td></td>\n",
       "      <td>ברא</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Print the head with the cleaned data.\n",
    "display(HTML(\n",
    "    OHB_EXTENDED_DF[\n",
    "        [\"BHSwordPointed\", \n",
    "        \"BHSwordConsonantal\", \n",
    "        \"Trailer\",\n",
    "        \"HebrewLexeme\", \n",
    "        \"lexemeID\",\n",
    "        \"clauseID\"]\n",
    "    ].head(3).to_html(index=False))\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Expand KJV, BHS, and BSB columns\n",
    "\n",
    "In the original 22 columns there are three features that consist of concatenated values:\n",
    "\n",
    "- 〔KJVverseSort｜KJVbook｜KJVchapter｜KJVverse〕:〔1｜1｜1｜1〕  \n",
    "- 〔BHSverseSort｜BHSbook｜BHSchapter｜BHSverse〕:〔1｜1｜1｜1〕\n",
    "- 〔BSBsort＠BSB〕:〔1＠In〕\n",
    "\n",
    "I will convert each of them to new dataframes with separate columns for each value, and then merge them back into the original dataframe."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Visualize pre-cleaned data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>〔KJVverseSort｜KJVbook｜KJVchapter｜KJVverse〕</th>\n",
       "      <th>〔BHSverseSort｜BHSbook｜BHSchapter｜BHSverse〕</th>\n",
       "      <th>〔BSBsort＠BSB〕</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>〔1｜1｜1｜1〕</td>\n",
       "      <td>〔1｜1｜1｜1〕</td>\n",
       "      <td>〔1＠In〕</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>〔1｜1｜1｜1〕</td>\n",
       "      <td>〔1｜1｜1｜1〕</td>\n",
       "      <td>〔2＠the beginning〕</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>〔1｜1｜1｜1〕</td>\n",
       "      <td>〔1｜1｜1｜1〕</td>\n",
       "      <td>〔4＠created〕</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Data before being cleaned.\n",
    "display(HTML(\n",
    "    OHB_EXTENDED_DF[\n",
    "        ['〔KJVverseSort｜KJVbook｜KJVchapter｜KJVverse〕', \n",
    "        '〔BHSverseSort｜BHSbook｜BHSchapter｜BHSverse〕',\n",
    "        '〔BSBsort＠BSB〕']\n",
    "    ].head(3).to_html(index=False))\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Define functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Where the value is a list, convert the original \n",
    "# column to len(list) new columns. \n",
    "updated_col_names = {\n",
    "    '〔KJVverseSort｜KJVbook｜KJVchapter｜KJVverse〕': # cleaned\n",
    "        ['KVJvsNode', 'KJVbook', 'KJVchapter', 'KJVverse'], \n",
    "    '〔BHSverseSort｜BHSbook｜BHSchapter｜BHSverse〕': # cleaned\n",
    "        ['BHSvsNode', 'BHSbook', 'BHSchapter', 'BHSverse'], \n",
    "    '〔BSBsort＠BSB〕': # cleaned\n",
    "        ['BSBglossNode', 'BSBgloss']\n",
    "}\n",
    "\n",
    "# ---\n",
    "# Function that takes a column name from \n",
    "# the original df and returns cleaned data.\n",
    "# Use: convert the KJV ref or BHS ref column to new dataframe. \n",
    "def clean_references(col_name):\n",
    "    # Create a dict for each new name to the new values.\n",
    "    new_names = updated_col_names[col_name]\n",
    "    cleaned_data = {name:[] for name in new_names}\n",
    "    # Iterate over the original dataframe and clean the data. \n",
    "    for ref_data in OHB_EXTENDED_DF[col_name]:\n",
    "        # Remove the outsides of 〔1｜1｜1｜1〕.\n",
    "        ref_data = ref_data.strip('〕〔')\n",
    "        # Split 1｜1｜1｜1 and convert each item to an int.\n",
    "        ref_data = [int(data) for data in ref_data.split('｜')]\n",
    "        # Add data to the dictionary. \n",
    "        cleaned_data[new_names[0]].append(ref_data[0]) # vs node\n",
    "        cleaned_data[new_names[1]].append(ref_data[1]) # book\n",
    "        cleaned_data[new_names[2]].append(ref_data[2]) # chapter\n",
    "        cleaned_data[new_names[3]].append(ref_data[3]) # verse\n",
    "   \n",
    "    # Convert the dictionary to a dataframe and return.\n",
    "    new_df = pd.DataFrame(cleaned_data)\n",
    "    return new_df\n",
    "\n",
    "# ---\n",
    "# Clean the BSB gloss data and store in a new dataframe. \n",
    "def clean_gloss(col_name):\n",
    "    # Create a dict for each new name to the new values.\n",
    "    new_names = updated_col_names[col_name]\n",
    "    cleaned_data = {name:[] for name in new_names}\n",
    "    # Iterate over the original dataframe and clean the data. \n",
    "    for gloss_data in OHB_EXTENDED_DF[col_name]:\n",
    "        # Catch edge cases where gloss_data is NaN.\n",
    "        if isinstance(gloss_data, str):\n",
    "            # Remove the outsides of 〔1＠In〕.\n",
    "            gloss_data = gloss_data.strip('〕〔')\n",
    "            # Split 1＠In and convert first item to an int.\n",
    "            gloss_data = gloss_data.split('＠')\n",
    "            \n",
    "            # For some reason, gloss node 237839 is split into \n",
    "            # decimals .1 and .2, which is why I am using a float. \n",
    "\n",
    "            # Add data to the dictionary. \n",
    "            gloss_data[0] = float(gloss_data[0])\n",
    "            cleaned_data[new_names[0]].append(gloss_data[0]) # gloss node\n",
    "            cleaned_data[new_names[1]].append(gloss_data[1]) # gloss\n",
    "        else:\n",
    "            cleaned_data[new_names[0]].append(gloss_data) # gloss node\n",
    "            cleaned_data[new_names[1]].append(gloss_data) # gloss\n",
    "\n",
    "    # Convert the dictionary to a dataframe and return.\n",
    "    new_df = pd.DataFrame(cleaned_data)\n",
    "    return new_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Call the functions -> new dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Clean the reference data and store in two new dataframes. \n",
    "KJV_ref_df = clean_references(\n",
    "    '〔KJVverseSort｜KJVbook｜KJVchapter｜KJVverse〕')\n",
    "BHS_ref_df = clean_references(\n",
    "    '〔BHSverseSort｜BHSbook｜BHSchapter｜BHSverse〕')\n",
    "BSB_gloss_df = clean_gloss(\n",
    "    '〔BSBsort＠BSB〕')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Visualize cleaned data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>KVJvsNode</th>\n",
       "      <th>KJVbook</th>\n",
       "      <th>KJVchapter</th>\n",
       "      <th>KJVverse</th>\n",
       "      <th>BHSvsNode</th>\n",
       "      <th>BHSbook</th>\n",
       "      <th>BHSchapter</th>\n",
       "      <th>BHSverse</th>\n",
       "      <th>BSBglossNode</th>\n",
       "      <th>BSBgloss</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>In</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>the beginning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>created</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Print the head of the cleaned data.\n",
    "display(HTML(\n",
    "    pd.concat(\n",
    "        [KJV_ref_df, \n",
    "        BHS_ref_df, \n",
    "        BSB_gloss_df],\n",
    "        axis=1\n",
    "    ).head(3).to_html(index=False))\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Rename columns and combine dataframes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Define functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ---\n",
    "# Drop the three columns that I expanded into new dataframes.\n",
    "def drop_old_data(dateframe):\n",
    "    dateframe = dateframe.drop(\n",
    "        columns=[\n",
    "        '〔KJVverseSort｜KJVbook｜KJVchapter｜KJVverse〕', \n",
    "        '〔BHSverseSort｜BHSbook｜BHSchapter｜BHSverse〕',\n",
    "        '〔BSBsort＠BSB〕']\n",
    "    )\n",
    "    return dateframe\n",
    "\n",
    "# ---\n",
    "# Rename columns and add the three \n",
    "# new dataframes for a final df output. \n",
    "def combine_data():\n",
    "    df_copy = copy.deepcopy(OHB_EXTENDED_DF)\n",
    "    updated_df = pd.DataFrame()\n",
    "    # Make sure the replaced data gets dropped. \n",
    "    if \"〔BSBsort＠BSB〕\" in df_copy.columns:\n",
    "        df_copy = drop_old_data(df_copy)\n",
    "    # Rename the other columns and build updated_df.\n",
    "    for column in df_copy:\n",
    "        # If at the column before 〔KJVverseSort..., \n",
    "        # add the new reference dataframes.\n",
    "        if column == \"poetryMarker\":\n",
    "            updated_df = pd.concat(\n",
    "                [updated_df, \n",
    "                df_copy[column], \n",
    "                KJV_ref_df, \n",
    "                BHS_ref_df], \n",
    "                axis=1)\n",
    "        # If at the column before 〔BSBsort..., \n",
    "        # add the new BSB dataframe.\n",
    "        elif column == \"extendedGloss\":\n",
    "            updated_df = pd.concat(\n",
    "                [updated_df, \n",
    "                df_copy[column], \n",
    "                BSB_gloss_df], \n",
    "                axis=1)\n",
    "        # Otherwise add the current column \n",
    "        # from the original dataframe.\n",
    "        else:\n",
    "            updated_df[column] = df_copy[column]\n",
    "\n",
    "    return updated_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Call function -> combined dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store the combined data in a new dataframe. \n",
    "ohb_extended_cleaned = combine_data()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Visualize the final cleaned dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>BHSwordSort</th>\n",
       "      <th>paragraphMarker</th>\n",
       "      <th>poetryMarker</th>\n",
       "      <th>KVJvsNode</th>\n",
       "      <th>KJVbook</th>\n",
       "      <th>KJVchapter</th>\n",
       "      <th>KJVverse</th>\n",
       "      <th>BHSvsNode</th>\n",
       "      <th>BHSbook</th>\n",
       "      <th>BHSchapter</th>\n",
       "      <th>BHSverse</th>\n",
       "      <th>clauseID</th>\n",
       "      <th>clauseKind</th>\n",
       "      <th>clauseType</th>\n",
       "      <th>language</th>\n",
       "      <th>Trailer</th>\n",
       "      <th>BHSwordPointed</th>\n",
       "      <th>BHSwordConsonantal</th>\n",
       "      <th>SBLstyleTransliteration</th>\n",
       "      <th>poneticTranscription</th>\n",
       "      <th>HebrewLexeme</th>\n",
       "      <th>lexemeID</th>\n",
       "      <th>StrongNumber</th>\n",
       "      <th>extendedStrongNumber</th>\n",
       "      <th>morphologyCode</th>\n",
       "      <th>morphologyDetail</th>\n",
       "      <th>ETCBCgloss</th>\n",
       "      <th>extendedGloss</th>\n",
       "      <th>BSBglossNode</th>\n",
       "      <th>BSBgloss</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>¶</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Verbal clauses</td>\n",
       "      <td>x-qatal-X clause</td>\n",
       "      <td>Hebrew</td>\n",
       "      <td></td>\n",
       "      <td>בְּ</td>\n",
       "      <td>ב</td>\n",
       "      <td>bĕ</td>\n",
       "      <td>bᵊ</td>\n",
       "      <td>בְּ</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>H9003</td>\n",
       "      <td>prep</td>\n",
       "      <td>preposition</td>\n",
       "      <td>in</td>\n",
       "      <td>in</td>\n",
       "      <td>1.0</td>\n",
       "      <td>In</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Verbal clauses</td>\n",
       "      <td>x-qatal-X clause</td>\n",
       "      <td>Hebrew</td>\n",
       "      <td></td>\n",
       "      <td>רֵאשִׁ֖ית</td>\n",
       "      <td>ראשית</td>\n",
       "      <td>rēšît</td>\n",
       "      <td>rēšˌîṯ</td>\n",
       "      <td>רֵאשִׁית</td>\n",
       "      <td>2</td>\n",
       "      <td>H7225</td>\n",
       "      <td>H7225</td>\n",
       "      <td>subs.f.sg.a</td>\n",
       "      <td>noun, feminine, singular, absolute</td>\n",
       "      <td>beginning</td>\n",
       "      <td>beginning</td>\n",
       "      <td>2.0</td>\n",
       "      <td>the beginning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Verbal clauses</td>\n",
       "      <td>x-qatal-X clause</td>\n",
       "      <td>Hebrew</td>\n",
       "      <td></td>\n",
       "      <td>בָּרָ֣א</td>\n",
       "      <td>ברא</td>\n",
       "      <td>bārā</td>\n",
       "      <td>bārˈā</td>\n",
       "      <td>ברא</td>\n",
       "      <td>3</td>\n",
       "      <td>H1254</td>\n",
       "      <td>H1254</td>\n",
       "      <td>verb.qal.perf.p3.m.sg</td>\n",
       "      <td>verb, qal, perfect, third person, masculine, singular</td>\n",
       "      <td>create</td>\n",
       "      <td>[he]+ create</td>\n",
       "      <td>4.0</td>\n",
       "      <td>created</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Display our newly cleaned and labeled data.\n",
    "display(HTML(ohb_extended_cleaned.head(3).to_html(index=False)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Test OHB_EXTENDED Data Against OHB_DB\n",
    "\n",
    "I want to compare values in the OHB_EXTENDED data to the OHB_DB data, especially text values, to make sure that the data is usable and accurate before merging features."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Feature mappings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "col_map = {\n",
    "    'ch_KJV': 'KJVchapter',\n",
    "    'v_KJV': 'KJVverse',\n",
    "    'ch_BHS': 'BHSchapter',\n",
    "    'v_BHS': 'BHSverse',\n",
    "    'clause_kind': 'clauseKind',\n",
    "    'clause_typ': 'clauseType',\n",
    "    'manuscript': 'BHSwordPointed',\n",
    "    'lex_Hebrew': 'HebrewLexeme',\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_refs():\n",
    "    mismatches = {}\n",
    "    for col in ['ch_KJV', 'v_KJV', 'ch_BHS', 'v_BHS']:\n",
    "        ohb_ref = [r for r in OHB_DB_DF[col]]\n",
    "        ext_ref = [r for r in ohb_extended_cleaned[col_map[col]]]\n",
    "        for i, ref in enumerate(ohb_ref):\n",
    "            if ref != ext_ref[i]:\n",
    "                mismatches[str(i)+col] = (ref, ext_ref[i])\n",
    "    return mismatches\n",
    "\n",
    "def check_clauses():\n",
    "    mismatches = {}\n",
    "    for col in ['clause_kind', 'clause_typ']:\n",
    "        ohb_clause = [r for r in OHB_DB_DF[col]]\n",
    "        ext_clause = [r for r in ohb_extended_cleaned[col_map[col]]]\n",
    "        for i, clause in enumerate(ohb_clause):\n",
    "            if clause != ext_clause[i]:\n",
    "                mismatches[str(i)+col] = (clause, ext_clause[i])\n",
    "    return mismatches\n",
    "\n",
    "def check_text():\n",
    "    # OHB_DB manuscript value is the text + the trailer.\n",
    "    text_extensions = [\n",
    "    '׃', '׃ ׆ ס ', ' ס ', '׃ ׆ ', '׃ ', ' ׀ ',\n",
    "    ' ', '׃ פ ', ' פ ', '׀ ', '׃ ׆ פ ', '־', '׃ ס ', '׀', 'פ', 'ס', '<', 'Q', 'R', '>', 'q', 'r', '׆'\n",
    "    ]\n",
    "    mismatches = {}\n",
    "    for col in ['manuscript']:\n",
    "        ohb_text = [w for w in OHB_DB_DF[col]]\n",
    "        ext_text = [w for w in ohb_extended_cleaned[col_map[col]]]\n",
    "        for i, w in enumerate(ohb_text):\n",
    "            w2 = ext_text[i]\n",
    "            dif = [i for i in list(w2) if i not in list(w)]\n",
    "            dif_checked = [i for i in dif if i not in text_extensions]\n",
    "            if len(dif_checked) > 0:\n",
    "                mismatches[str(i)+col] = (w, w2)\n",
    "    return mismatches\n",
    "\n",
    "def check_lex():\n",
    "    mismatches = {}\n",
    "    for col in ['lex_Hebrew']:\n",
    "        ohb_text = [w for w in OHB_DB_DF[col]]\n",
    "        ext_text = [w for w in ohb_extended_cleaned[col_map[col]]]\n",
    "        for i, w in enumerate(ohb_text):\n",
    "            if w != ext_text[i]:\n",
    "                mismatches[str(i)+col] = (w, ext_text[i])\n",
    "    return mismatches"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Test Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Refs Unaligned\n",
      "{'152831v_KJV': (1, 2), '152832v_KJV': (1, 2), '152833v_KJV': (1, 2), '152834v_KJV': (1, 2), '152835v_KJV': (1, 2), '152836v_KJV': (1, 2), '152837v_KJV': (1, 2), '152838v_KJV': (1, 2), '191090v_KJV': (34, 33), '191091v_KJV': (34, 33), '191092v_KJV': (34, 33), '191093v_KJV': (34, 33), '191094v_KJV': (34, 33), '191095v_KJV': (34, 33), '191096v_KJV': (34, 33), '191097v_KJV': (34, 33), '191098v_KJV': (34, 33), '191099v_KJV': (34, 33), '191100v_KJV': (34, 33), '191101v_KJV': (34, 33), '191102v_KJV': (34, 33), '191103v_KJV': (34, 33), '191104v_KJV': (34, 33), '191962v_KJV': (3, 2), '191963v_KJV': (3, 2), '191964v_KJV': (3, 2), '191965v_KJV': (3, 2), '191966v_KJV': (3, 2), '191967v_KJV': (3, 2), '194111v_KJV': (21, 22), '194112v_KJV': (21, 22), '194113v_KJV': (21, 22), '194114v_KJV': (21, 22), '194115v_KJV': (21, 22), '194116v_KJV': (21, 22), '194578v_KJV': (44, 43), '194579v_KJV': (44, 43), '194580v_KJV': (44, 43), '194581v_KJV': (44, 43), '194582v_KJV': (44, 43), '194583v_KJV': (44, 43), '194584v_KJV': (44, 43), '194585v_KJV': (44, 43), '194586v_KJV': (44, 43), '194587v_KJV': (44, 43), '194588v_KJV': (44, 43), '194589v_KJV': (44, 43), '194590v_KJV': (44, 43), '194591v_KJV': (44, 43)}\n",
      "Clauses Aligned\n",
      "Text Aligned\n",
      "Lex Aligned\n"
     ]
    }
   ],
   "source": [
    "ref_mismatches = check_refs()\n",
    "message1 = f\"Refs Unaligned\\n{ref_mismatches}\" if len(ref_mismatches) > 0 else \"Refs Aligned\"\n",
    "print(message1)\n",
    "\n",
    "clause_mismatches = check_clauses()\n",
    "message2 = f\"Clauses Unaligned\\n{clause_mismatches}\" if len(clause_mismatches) > 0 else \"Clauses Aligned\"\n",
    "print(message2)\n",
    "\n",
    "text_mismatches = check_text()\n",
    "message3 = f\"Text Unaligned\\n{text_mismatches}\" if len(text_mismatches) > 0 else \"Text Aligned\"\n",
    "print(message3)\n",
    "\n",
    "lex_mismatches = check_lex()\n",
    "message4 = f\"Lex Unaligned\\n{lex_mismatches}\" if len(lex_mismatches) > 0 else \"Lex Aligned\"\n",
    "print(message4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**We have:**\n",
    "\n",
    "Refs Unaligned\n",
    "\n",
    "{'152831v_KJV': (1, 2), '152832v_KJV': (1, 2), '152833v_KJV': (1, 2), '152834v_KJV': (1, 2), '152835v_KJV': (1, 2), '152836v_KJV': (1, 2), '152837v_KJV': (1, 2), '152838v_KJV': (1, 2), '191090v_KJV': (34, 33), '191091v_KJV': (34, 33), '191092v_KJV': (34, 33), '191093v_KJV': (34, 33), '191094v_KJV': (34, 33), '191095v_KJV': (34, 33), '191096v_KJV': (34, 33), '191097v_KJV': (34, 33), '191098v_KJV': (34, 33), '191099v_KJV': (34, 33), '191100v_KJV': (34, 33), '191101v_KJV': (34, 33), '191102v_KJV': (34, 33), '191103v_KJV': (34, 33), '191104v_KJV': (34, 33), '191962v_KJV': (3, 2), '191963v_KJV': (3, 2), '191964v_KJV': (3, 2), '191965v_KJV': (3, 2), '191966v_KJV': (3, 2), '191967v_KJV': (3, 2), '194111v_KJV': (21, 22), '194112v_KJV': (21, 22), '194113v_KJV': (21, 22), '194114v_KJV': (21, 22), '194115v_KJV': (21, 22), '194116v_KJV': (21, 22), '194578v_KJV': (44, 43), '194579v_KJV': (44, 43), '194580v_KJV': (44, 43), '194581v_KJV': (44, 43), '194582v_KJV': (44, 43), '194583v_KJV': (44, 43), '194584v_KJV': (44, 43), '194585v_KJV': (44, 43), '194586v_KJV': (44, 43), '194587v_KJV': (44, 43), '194588v_KJV': (44, 43), '194589v_KJV': (44, 43), '194590v_KJV': (44, 43), '194591v_KJV': (44, 43)}\n",
    "\n",
    "Clauses Aligned\n",
    "\n",
    "Text Aligned\n",
    "\n",
    "Lex Aligned\n",
    "\n",
    "**Next Steps:**\n",
    "Check references against STEP Bible."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# To visualize which books and chapters the divergent refs occur in. \n",
    "if len(ref_mismatches) > 0:\n",
    "    formatted_refs = {}\n",
    "    df = ohb_extended_cleaned\n",
    "    for k in ref_mismatches:\n",
    "        # Get just the number (word id)\n",
    "        node = int(k[:6])\n",
    "        bk = df.iloc[node]['KJVbook']\n",
    "        ch = df.iloc[node]['KJVchapter']\n",
    "        vs = df.iloc[node]['KJVverse']\n",
    "        w = df.iloc[node]['BHSwordPointed']\n",
    "        formatted_refs[k] = f\"{bk}:{ch}:{vs} {w}\"\n",
    "\n",
    "# print(formatted_refs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Comparison to STEP Bible**\n",
    "\n",
    "Nodes 152831-152838:\n",
    "- ohb_db: 1\n",
    "- ohb_ext: 2\n",
    "- STEP: 2 (these words are in vs 1 of BHS)\n",
    "\n",
    "Nodes 191090-191104:\n",
    "- ohb_db: 34\n",
    "- ohb_ext: 33\n",
    "- STEP: 33 (these words are in vs 34 of BHS)\n",
    "\n",
    "Nodes 191962-191967:\n",
    "- ohb_db: 3\n",
    "- ohb_ext: 2\n",
    "- STEP: 2 (these words are in vs 3 of BHS)\n",
    "\n",
    "Nodes 194111-194116:\n",
    "- ohb_db: 21\n",
    "- ohb_ext: 22\n",
    "- STEP: 22 (these words are in vs 21 of BHS)\n",
    "\n",
    "Nodes 194578-194591:\n",
    "- ohb_db: 44\n",
    "- ohb_ext: 43\n",
    "- STEP: 43 (these words are in vs 43 of BHS)\n",
    "\n",
    "**Observations:** The OHB_EXTENDED data accurately reflects the KJV. In most divergent cases, the OHB_DB data is reflecting the BHS verse value for a node rather than the KJV verse value. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save OHB_EXTENDED as CSV file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "ohb_cleaned_path = '../data_files/combined/ohb_extended_cleaned.csv'\n",
    "ohb_extended_cleaned.to_csv(ohb_cleaned_path, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combine OHB_EXTENDED and OHB_DB and BHSA_DB features into new DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "OHB_COMBINED = copy.deepcopy(ohb_extended_cleaned)\n",
    "\n",
    "# Data from OHB_DB\n",
    "OHB_COMBINED['lang'] = OHB_DB_DF['lang']\n",
    "OHB_COMBINED['phrase_typ'] = OHB_DB_DF['phrase_typ']\n",
    "OHB_COMBINED['phrase_det'] = OHB_DB_DF['phrase_det']\n",
    "OHB_COMBINED['phrase_function'] = OHB_DB_DF['phrase_function']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>BHSwordSort</th>\n",
       "      <th>paragraphMarker</th>\n",
       "      <th>poetryMarker</th>\n",
       "      <th>KVJvsNode</th>\n",
       "      <th>KJVbook</th>\n",
       "      <th>KJVchapter</th>\n",
       "      <th>KJVverse</th>\n",
       "      <th>BHSvsNode</th>\n",
       "      <th>BHSbook</th>\n",
       "      <th>BHSchapter</th>\n",
       "      <th>BHSverse</th>\n",
       "      <th>clauseID</th>\n",
       "      <th>clauseKind</th>\n",
       "      <th>clauseType</th>\n",
       "      <th>language</th>\n",
       "      <th>Trailer</th>\n",
       "      <th>BHSwordPointed</th>\n",
       "      <th>BHSwordConsonantal</th>\n",
       "      <th>SBLstyleTransliteration</th>\n",
       "      <th>poneticTranscription</th>\n",
       "      <th>HebrewLexeme</th>\n",
       "      <th>lexemeID</th>\n",
       "      <th>StrongNumber</th>\n",
       "      <th>extendedStrongNumber</th>\n",
       "      <th>morphologyCode</th>\n",
       "      <th>morphologyDetail</th>\n",
       "      <th>ETCBCgloss</th>\n",
       "      <th>extendedGloss</th>\n",
       "      <th>BSBglossNode</th>\n",
       "      <th>BSBgloss</th>\n",
       "      <th>lang</th>\n",
       "      <th>phrase_typ</th>\n",
       "      <th>phrase_det</th>\n",
       "      <th>phrase_function</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>¶</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Verbal clauses</td>\n",
       "      <td>x-qatal-X clause</td>\n",
       "      <td>Hebrew</td>\n",
       "      <td></td>\n",
       "      <td>בְּ</td>\n",
       "      <td>ב</td>\n",
       "      <td>bĕ</td>\n",
       "      <td>bᵊ</td>\n",
       "      <td>בְּ</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>H9003</td>\n",
       "      <td>prep</td>\n",
       "      <td>preposition</td>\n",
       "      <td>in</td>\n",
       "      <td>in</td>\n",
       "      <td>1.0</td>\n",
       "      <td>In</td>\n",
       "      <td>hbo</td>\n",
       "      <td>Prepositional phrase</td>\n",
       "      <td>undetermined</td>\n",
       "      <td>Time reference</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Verbal clauses</td>\n",
       "      <td>x-qatal-X clause</td>\n",
       "      <td>Hebrew</td>\n",
       "      <td></td>\n",
       "      <td>רֵאשִׁ֖ית</td>\n",
       "      <td>ראשית</td>\n",
       "      <td>rēšît</td>\n",
       "      <td>rēšˌîṯ</td>\n",
       "      <td>רֵאשִׁית</td>\n",
       "      <td>2</td>\n",
       "      <td>H7225</td>\n",
       "      <td>H7225</td>\n",
       "      <td>subs.f.sg.a</td>\n",
       "      <td>noun, feminine, singular, absolute</td>\n",
       "      <td>beginning</td>\n",
       "      <td>beginning</td>\n",
       "      <td>2.0</td>\n",
       "      <td>the beginning</td>\n",
       "      <td>hbo</td>\n",
       "      <td>Prepositional phrase</td>\n",
       "      <td>undetermined</td>\n",
       "      <td>Time reference</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Verbal clauses</td>\n",
       "      <td>x-qatal-X clause</td>\n",
       "      <td>Hebrew</td>\n",
       "      <td></td>\n",
       "      <td>בָּרָ֣א</td>\n",
       "      <td>ברא</td>\n",
       "      <td>bārā</td>\n",
       "      <td>bārˈā</td>\n",
       "      <td>ברא</td>\n",
       "      <td>3</td>\n",
       "      <td>H1254</td>\n",
       "      <td>H1254</td>\n",
       "      <td>verb.qal.perf.p3.m.sg</td>\n",
       "      <td>verb, qal, perfect, third person, masculine, singular</td>\n",
       "      <td>create</td>\n",
       "      <td>[he]+ create</td>\n",
       "      <td>4.0</td>\n",
       "      <td>created</td>\n",
       "      <td>hbo</td>\n",
       "      <td>Verbal phrase</td>\n",
       "      <td></td>\n",
       "      <td>Predicate</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Display our newly combined data.\n",
    "display(HTML(OHB_COMBINED.head(3).to_html(index=False)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save as new file.\n",
    "ohb_combined_path = '../data_files/combined/ohb_combined.csv'\n",
    "OHB_COMBINED.to_csv(ohb_combined_path, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Align OHB_EXTENDED with BHSA_DB and Test\n",
    "\n",
    "See notes [here](https://docs.google.com/document/d/1WE59plLi8EQTaDijHkdgPCVAwOc_TlQU4GvDjyEsPAA/edit?usp=sharing).\n",
    "\n",
    "Drop node 16563 from the BHSA_DB_DF.\n",
    "\n",
    "Expand node 392485 into three nodes and save the data in ohb_combined_aligned.csv.\n",
    "\n",
    "Increment all node values that come after 392485. \n",
    "\n",
    "Set marked value 3924860 to 392489 and all after to i + 3."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the aligned file that I manually edited\n",
    "OHB_ALIGNED_PATH = '../data_files/combined/ohb_combined.csv'\n",
    "OHB_ALIGNED = pd.read_csv(OHB_ALIGNED_PATH, sep=',', low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the SKIP row to import data into the new DB with alignment.\n",
    "SKIP = 16563\n",
    "BHSA_V2 = BHSA_DB_DF.drop(BHSA_DB_DF.index[SKIP-1])\n",
    "BHSA_V2.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Update OHB_ALIGNED\n",
    "EXPANDED_NODE = 392485\n",
    "FIXED_DATA = {\n",
    "    EXPANDED_NODE: {\n",
    "        'BHSwordPointed': 'חֲצִ֥י',\n",
    "        'BHSwordConsonantal': 'חצי',\n",
    "        'Trailer': ' ',\n",
    "        'SBLstyleTransliteration': 'ḥăṣî',\n",
    "        'HebrewLexeme': 'חֲצִי',\n",
    "        'lexemeID': 2003,\n",
    "        'extendedStrongNumber': 'H2677',\n",
    "        'ETCBCgloss': 'half',\n",
    "        'extendedGloss': 'half',\n",
    "        'BSBgloss': np.nan,\n",
    "        'BSBglossNode': np.nan,\n",
    "    },\n",
    "    EXPANDED_NODE+1: {\n",
    "        'BHSwordPointed': 'הַ',\n",
    "        'BHSwordConsonantal': 'ה',\n",
    "        'Trailer': '',\n",
    "        'SBLstyleTransliteration': 'ha',\n",
    "        'HebrewLexeme': 'הַ',\n",
    "        'lexemeID': 6,\n",
    "        'extendedStrongNumber': 'H9009',\n",
    "        'ETCBCgloss': 'the',\n",
    "        'extendedGloss': 'the',\n",
    "        'BSBgloss': np.nan,\n",
    "        'BSBglossNode': np.nan,\n",
    "    },\n",
    "    EXPANDED_NODE+2: {\n",
    "        'BHSwordPointed': 'מְּנֻחֹֽות',\n",
    "        'BHSwordConsonantal': 'מנחות',\n",
    "        'Trailer': ' ׃',\n",
    "        'SBLstyleTransliteration': 'mĕnuḥôt',\n",
    "        'HebrewLexeme': 'מְּנֻחֹות',\n",
    "        'lexemeID': 8720,\n",
    "        'extendedStrongNumber': 'H4506a',\n",
    "        'ETCBCgloss': 'Manahathites',\n",
    "        'extendedGloss': 'Manahathites',\n",
    "        'BSBgloss': 'half the Manahathites',\n",
    "        'BSBglossNode': 145601.0,\n",
    "    },\n",
    "    392519: {'extendedStrongNumber': 'H4506a'}\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Increment nodes.\n",
    "def update_nodes(df):\n",
    "    nodes = [n for n in df['BHSwordSort']]\n",
    "    k = 0\n",
    "    # Update node values\n",
    "    for i, n in enumerate(nodes):\n",
    "        if n == SKIP:\n",
    "            nodes[i] += 1\n",
    "            k += 1\n",
    "        elif n == EXPANDED_NODE+1 and nodes[i-1] != EXPANDED_NODE+1:\n",
    "            nodes[i] += k+2\n",
    "            k += 2\n",
    "        else:\n",
    "            nodes[i] += k\n",
    "    return nodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "def expand_node():\n",
    "    df = copy.deepcopy(OHB_ALIGNED)\n",
    "    i = df.index[df['BHSwordSort'] == EXPANDED_NODE][0]\n",
    "    pre = df.loc[:i-1]\n",
    "    mid = df.iloc[[i]]\n",
    "    mid = pd.concat([mid]*3, ignore_index=True)\n",
    "    mid['BHSwordSort'] = [EXPANDED_NODE+i for i in range(3)]\n",
    "    post = df.loc[i+1:]\n",
    "    df = pd.concat([pre, mid, post], ignore_index=True)\n",
    "    df['BHSwordSort'] = update_nodes(df)\n",
    "\n",
    "    for node in FIXED_DATA:\n",
    "        for k in FIXED_DATA[node]:\n",
    "            index =  df.index[df['BHSwordSort'] == node+1][0]\n",
    "            df.at[index, k] = FIXED_DATA[node][k]\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "OHB_ALIGNED = expand_node()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "col_map = {\n",
    "    'BHSwordSort': '_id',\n",
    "    'BHSwordConsonantal': 'g_cons_utf8',\n",
    "    'BHSwordPointed': 'g_word_utf8',\n",
    "    'ETCBCgloss': 'gloss',\n",
    "    'lang': 'languageISO',\n",
    "    'Trailer': 'trailer_utf8',\n",
    "    'HebrewLexeme': 'voc_lex_utf8'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "for c in ['BHSwordConsonantal', 'BHSwordPointed', 'Trailer']:\n",
    "    OHB_ALIGNED[c] = OHB_ALIGNED[c].replace({np.nan: \"\"})\n",
    "\n",
    "def test_aligned():\n",
    "    mismatches = {k:[] for k in col_map}\n",
    "    qere_words = [i for i in BHSA_V2['qere_utf8']]  \n",
    "    qere_trailers = [i for i in BHSA_V2['qere_trailer_utf8']]\n",
    "    for col in col_map:\n",
    "        ohb_data = [i for i in OHB_ALIGNED[col]]\n",
    "        bhs_data = [i for i in BHSA_V2[col_map[col]]]\n",
    "        for i, d in enumerate(ohb_data):\n",
    "            # See https://etcbc.github.io/bhsa/features/qere_utf8/\n",
    "            if col == 'BHSwordPointed':\n",
    "                w = bhs_data[i] if not qere_words[i] else qere_words[i]\n",
    "                if d != w:\n",
    "                    mismatches[col].append((i+1, d, w))\n",
    "            elif col == 'Trailer':\n",
    "                t = bhs_data[i] if not qere_trailers[i] else qere_trailers[i]\n",
    "                if d != t:\n",
    "                    mismatches[col].append((i+1, d, t))\n",
    "            # bhsa uses <> rather than [] for values like object marker.\n",
    "            elif col == 'ETCBCgloss':\n",
    "                d2 = bhs_data[i]\n",
    "                dif = [i for i in list(d2) if i not in list(d)]\n",
    "                dif_checked = [i for i in dif if i not in ['<','>','[',']']]\n",
    "                if len(dif_checked) > 0:\n",
    "                    mismatches[col].append((i+1, d, bhs_data[i]))\n",
    "            elif d != bhs_data[i]:\n",
    "                mismatches[col].append((i+1, d, bhs_data[i]))\n",
    "    return mismatches"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Collect mismatch data and save csv files.\n",
    "mismatches = test_aligned()\n",
    "path = '../data_files/combined/mismatches/'\n",
    "def export_mismatches():\n",
    "    for k in mismatches:\n",
    "        # the cons vals in BHSA don't have shin/sin differentiation.\n",
    "        if k != 'BHSwordConsonantal' and len(mismatches[k]) > 0:\n",
    "            data = {'node':[], 'ohb':[], 'bhsa':[]}\n",
    "            for v in mismatches[k]:\n",
    "                n, o, b = v\n",
    "                data['node'].append(n)\n",
    "                data['ohb'].append(o)\n",
    "                data['bhsa'].append(b)\n",
    "            df = pd.DataFrame(data)\n",
    "            df.to_csv(f\"{path}{k}.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save csv files. \n",
    "export_mismatches()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Mismatch notes\n",
    "\n",
    "**word.csv:** 1869 mismatches that predominantly consist of g_word_utf8 lacking pointings. It seems best to use the OHB data in this case.\n",
    "\n",
    "**lex.csv:** 3 mismatches\n",
    "```\n",
    "node\tohb\tbhsa\n",
    "152522\tחַי\tחַיִּים\n",
    "392488\tמְּנֻחֹות\tמְנוּחָה\n",
    "394199\tושׁני֜\tוַשְׁנִי\n",
    "```\n",
    "**bhsa_gloss.csv:** 490 mismatches -- mostly repeats (e.g., where ohb has cloth and bhsa has clothe). BHSA seems to be more accurate here. \n",
    "\n",
    "**trailer.csv:** 150 mismatches\n",
    "\n",
    "**Note:** the BHSA has [features](https://etcbc.github.io/bhsa/features/qere_utf8/) qere_utf8 and qere_trailer_utf8 that provide vocalized data when it is lacking in the *ketiv* form. I've updated the mismatches code to chose those vocalized options in the BHSA data when the ketiv form is missing pointings.\n",
    "\n",
    "**UPDATED MISMATCHES**\n",
    "\n",
    "**word.csv:** 2 differences\n",
    "```\n",
    "node\tohb\tbhsa\n",
    "199283\t\tה\n",
    "205832\tשֻׁ֝֩בו \tשֻׁ֝֠בוּ\n",
    "```\n",
    "**trailer.csv:** 6 differences, bhsa following qere. \n",
    "```\n",
    "node\tohb\tbhsa\n",
    "137795\t''\t ' ' \n",
    "156164\t''\t־\n",
    "227810\t''\t ' '\n",
    "345548\t''\t ' '\n",
    "363613\t''\t ' '\n",
    "364988\t''\t ' '\n",
    "```\n",
    "\n",
    "**CONCLUSIONS**\n",
    "\n",
    "Most of these are insignificant. It is likely best to go with the BHSA gloss column rather than the OHB gloss column. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>BHSwordSort</th>\n",
       "      <th>paragraphMarker</th>\n",
       "      <th>poetryMarker</th>\n",
       "      <th>KVJvsNode</th>\n",
       "      <th>KJVbook</th>\n",
       "      <th>KJVchapter</th>\n",
       "      <th>KJVverse</th>\n",
       "      <th>BHSvsNode</th>\n",
       "      <th>BHSbook</th>\n",
       "      <th>BHSchapter</th>\n",
       "      <th>BHSverse</th>\n",
       "      <th>clauseID</th>\n",
       "      <th>clauseKind</th>\n",
       "      <th>clauseType</th>\n",
       "      <th>language</th>\n",
       "      <th>Trailer</th>\n",
       "      <th>BHSwordPointed</th>\n",
       "      <th>BHSwordConsonantal</th>\n",
       "      <th>SBLstyleTransliteration</th>\n",
       "      <th>poneticTranscription</th>\n",
       "      <th>HebrewLexeme</th>\n",
       "      <th>lexemeID</th>\n",
       "      <th>StrongNumber</th>\n",
       "      <th>extendedStrongNumber</th>\n",
       "      <th>morphologyCode</th>\n",
       "      <th>morphologyDetail</th>\n",
       "      <th>ETCBCgloss</th>\n",
       "      <th>extendedGloss</th>\n",
       "      <th>BSBglossNode</th>\n",
       "      <th>BSBgloss</th>\n",
       "      <th>lang</th>\n",
       "      <th>phrase_typ</th>\n",
       "      <th>phrase_det</th>\n",
       "      <th>phrase_function</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>335241</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16322</td>\n",
       "      <td>19</td>\n",
       "      <td>145</td>\n",
       "      <td>1</td>\n",
       "      <td>17597</td>\n",
       "      <td>19</td>\n",
       "      <td>145</td>\n",
       "      <td>1</td>\n",
       "      <td>71573</td>\n",
       "      <td>Nominal clauses</td>\n",
       "      <td>Nominal clause</td>\n",
       "      <td>Hebrew</td>\n",
       "      <td></td>\n",
       "      <td>דָ֫וִ֥ד</td>\n",
       "      <td>דוד</td>\n",
       "      <td>dāwid</td>\n",
       "      <td>ḏˈāwˌiḏ</td>\n",
       "      <td>דָּוִד</td>\n",
       "      <td>4258</td>\n",
       "      <td>H1732</td>\n",
       "      <td>H1732</td>\n",
       "      <td>nmpr.m.sg.a</td>\n",
       "      <td>proper noun, masculine, singular, absolute</td>\n",
       "      <td>David</td>\n",
       "      <td>David</td>\n",
       "      <td>209386.0</td>\n",
       "      <td>Of David.</td>\n",
       "      <td>hbo</td>\n",
       "      <td>Nominal phrase</td>\n",
       "      <td>undetermined</td>\n",
       "      <td>Predicate complement</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>335242</td>\n",
       "      <td>¶</td>\n",
       "      <td>‡</td>\n",
       "      <td>16322</td>\n",
       "      <td>19</td>\n",
       "      <td>145</td>\n",
       "      <td>1</td>\n",
       "      <td>17597</td>\n",
       "      <td>19</td>\n",
       "      <td>145</td>\n",
       "      <td>1</td>\n",
       "      <td>71574</td>\n",
       "      <td>Verbal clauses</td>\n",
       "      <td>Zero-yiqtol-null clause</td>\n",
       "      <td>Hebrew</td>\n",
       "      <td></td>\n",
       "      <td>אֲרֹומִמְךָ֣</td>\n",
       "      <td>ארוממך</td>\n",
       "      <td>ʾărômimĕkā</td>\n",
       "      <td>ʔᵃrômimᵊḵˈā</td>\n",
       "      <td>רום</td>\n",
       "      <td>413</td>\n",
       "      <td>H7311</td>\n",
       "      <td>H7311</td>\n",
       "      <td>verb.piel.impf.p1.u.sg.prs.p2.m.sg</td>\n",
       "      <td>verb, pi“el, imperfect, first person, unknown, singular, pronominal suffix, second person, masculine, singular</td>\n",
       "      <td>be high</td>\n",
       "      <td>[I]+ be high +[you]</td>\n",
       "      <td>209387.0</td>\n",
       "      <td>I will exalt You,</td>\n",
       "      <td>hbo</td>\n",
       "      <td>Verbal phrase</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Predicate with object suffix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>335243</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16322</td>\n",
       "      <td>19</td>\n",
       "      <td>145</td>\n",
       "      <td>1</td>\n",
       "      <td>17597</td>\n",
       "      <td>19</td>\n",
       "      <td>145</td>\n",
       "      <td>1</td>\n",
       "      <td>71575</td>\n",
       "      <td>Clauses without predication</td>\n",
       "      <td>Vocative clause</td>\n",
       "      <td>Hebrew</td>\n",
       "      <td></td>\n",
       "      <td>אֱלֹוהַ֣י</td>\n",
       "      <td>אלוהי</td>\n",
       "      <td>ʾĕlôhay</td>\n",
       "      <td>ʔᵉlôhˈay</td>\n",
       "      <td>אֱלֹהִים</td>\n",
       "      <td>4</td>\n",
       "      <td>H433</td>\n",
       "      <td>H433</td>\n",
       "      <td>subs.m.pl.a</td>\n",
       "      <td>noun, masculine, plural, absolute</td>\n",
       "      <td>god(s)</td>\n",
       "      <td>god [pl.]</td>\n",
       "      <td>209388.0</td>\n",
       "      <td>my God</td>\n",
       "      <td>hbo</td>\n",
       "      <td>Nominal phrase</td>\n",
       "      <td>determined</td>\n",
       "      <td>Vocative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>335244</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16322</td>\n",
       "      <td>19</td>\n",
       "      <td>145</td>\n",
       "      <td>1</td>\n",
       "      <td>17597</td>\n",
       "      <td>19</td>\n",
       "      <td>145</td>\n",
       "      <td>1</td>\n",
       "      <td>71575</td>\n",
       "      <td>Clauses without predication</td>\n",
       "      <td>Vocative clause</td>\n",
       "      <td>Hebrew</td>\n",
       "      <td></td>\n",
       "      <td>הַ</td>\n",
       "      <td>ה</td>\n",
       "      <td>ha</td>\n",
       "      <td>ha</td>\n",
       "      <td>הַ</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>H9009</td>\n",
       "      <td>art</td>\n",
       "      <td>article</td>\n",
       "      <td>the</td>\n",
       "      <td>the</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>hbo</td>\n",
       "      <td>Nominal phrase</td>\n",
       "      <td>determined</td>\n",
       "      <td>Vocative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>335245</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16322</td>\n",
       "      <td>19</td>\n",
       "      <td>145</td>\n",
       "      <td>1</td>\n",
       "      <td>17597</td>\n",
       "      <td>19</td>\n",
       "      <td>145</td>\n",
       "      <td>1</td>\n",
       "      <td>71575</td>\n",
       "      <td>Clauses without predication</td>\n",
       "      <td>Vocative clause</td>\n",
       "      <td>Hebrew</td>\n",
       "      <td></td>\n",
       "      <td>מֶּ֑לֶךְ</td>\n",
       "      <td>מלך</td>\n",
       "      <td>melek</td>\n",
       "      <td>mmˈeleḵ</td>\n",
       "      <td>מֶלֶךְ</td>\n",
       "      <td>671</td>\n",
       "      <td>H4428</td>\n",
       "      <td>H4428</td>\n",
       "      <td>subs.m.sg.a</td>\n",
       "      <td>noun, masculine, singular, absolute</td>\n",
       "      <td>king</td>\n",
       "      <td>king</td>\n",
       "      <td>209389.0</td>\n",
       "      <td>[and] King;</td>\n",
       "      <td>hbo</td>\n",
       "      <td>Nominal phrase</td>\n",
       "      <td>determined</td>\n",
       "      <td>Vocative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>335246</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16322</td>\n",
       "      <td>19</td>\n",
       "      <td>145</td>\n",
       "      <td>1</td>\n",
       "      <td>17597</td>\n",
       "      <td>19</td>\n",
       "      <td>145</td>\n",
       "      <td>1</td>\n",
       "      <td>71576</td>\n",
       "      <td>Verbal clauses</td>\n",
       "      <td>We-yiqtol-null clause</td>\n",
       "      <td>Hebrew</td>\n",
       "      <td></td>\n",
       "      <td>וַ</td>\n",
       "      <td>ו</td>\n",
       "      <td>wa</td>\n",
       "      <td>wa</td>\n",
       "      <td>וְ</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>H9000</td>\n",
       "      <td>conj</td>\n",
       "      <td>conjunction</td>\n",
       "      <td>and</td>\n",
       "      <td>and</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>hbo</td>\n",
       "      <td>Conjunctive phrase</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Conjunction</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# View specified rows in OHB and BHSA\n",
    "i = 335242-3\n",
    "display(HTML(OHB_ALIGNED.loc[i:i+5].to_html(index=False)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>_id</th>\n",
       "      <th>freq_lex</th>\n",
       "      <th>freq_occ</th>\n",
       "      <th>g_cons</th>\n",
       "      <th>g_cons_utf8</th>\n",
       "      <th>g_lex</th>\n",
       "      <th>g_lex_utf8</th>\n",
       "      <th>g_nme</th>\n",
       "      <th>g_nme_utf8</th>\n",
       "      <th>g_pfm</th>\n",
       "      <th>g_pfm_utf8</th>\n",
       "      <th>g_prs</th>\n",
       "      <th>g_prs_utf8</th>\n",
       "      <th>g_uvf</th>\n",
       "      <th>g_uvf_utf8</th>\n",
       "      <th>g_vbe</th>\n",
       "      <th>g_vbe_utf8</th>\n",
       "      <th>g_vbs</th>\n",
       "      <th>g_vbs_utf8</th>\n",
       "      <th>g_word</th>\n",
       "      <th>g_word_utf8</th>\n",
       "      <th>gloss</th>\n",
       "      <th>gn</th>\n",
       "      <th>kq_hybrid</th>\n",
       "      <th>kq_hybrid_utf8</th>\n",
       "      <th>language</th>\n",
       "      <th>languageISO</th>\n",
       "      <th>lex</th>\n",
       "      <th>lex0</th>\n",
       "      <th>lex_utf8</th>\n",
       "      <th>lexeme_count</th>\n",
       "      <th>ls</th>\n",
       "      <th>nametype</th>\n",
       "      <th>nme</th>\n",
       "      <th>nu</th>\n",
       "      <th>number</th>\n",
       "      <th>pdp</th>\n",
       "      <th>pfm</th>\n",
       "      <th>prs</th>\n",
       "      <th>prs_gn</th>\n",
       "      <th>prs_nu</th>\n",
       "      <th>prs_ps</th>\n",
       "      <th>ps</th>\n",
       "      <th>qere</th>\n",
       "      <th>qere_trailer</th>\n",
       "      <th>qere_trailer_utf8</th>\n",
       "      <th>qere_utf8</th>\n",
       "      <th>rank_lex</th>\n",
       "      <th>rank_occ</th>\n",
       "      <th>sp</th>\n",
       "      <th>st</th>\n",
       "      <th>suffix_gender</th>\n",
       "      <th>suffix_number</th>\n",
       "      <th>suffix_person</th>\n",
       "      <th>trailer</th>\n",
       "      <th>trailer_utf8</th>\n",
       "      <th>uvf</th>\n",
       "      <th>vbe</th>\n",
       "      <th>vbs</th>\n",
       "      <th>voc_lex</th>\n",
       "      <th>voc_lex_utf8</th>\n",
       "      <th>vs</th>\n",
       "      <th>vt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>335240</td>\n",
       "      <td>20069</td>\n",
       "      <td>15641</td>\n",
       "      <td>L</td>\n",
       "      <td>ל</td>\n",
       "      <td>L:-</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>L:-</td>\n",
       "      <td>לְ</td>\n",
       "      <td>to</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>L</td>\n",
       "      <td>L</td>\n",
       "      <td>ל</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td>n/a</td>\n",
       "      <td>NA</td>\n",
       "      <td>24591</td>\n",
       "      <td>prep</td>\n",
       "      <td>n/a</td>\n",
       "      <td>absent</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>prep</td>\n",
       "      <td>NA</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>L:</td>\n",
       "      <td>לְ</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>335241</td>\n",
       "      <td>1075</td>\n",
       "      <td>800</td>\n",
       "      <td>DWD</td>\n",
       "      <td>דוד</td>\n",
       "      <td>D@WID</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>D@60WI71D</td>\n",
       "      <td>דָ֫וִ֥ד</td>\n",
       "      <td>David</td>\n",
       "      <td>m</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>DWD==/</td>\n",
       "      <td>DWD</td>\n",
       "      <td>דוד</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>pers</td>\n",
       "      <td></td>\n",
       "      <td>sg</td>\n",
       "      <td>24592</td>\n",
       "      <td>nmpr</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>41</td>\n",
       "      <td>42</td>\n",
       "      <td>nmpr</td>\n",
       "      <td>a</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>D.@WID</td>\n",
       "      <td>דָּוִד</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>335242</td>\n",
       "      <td>188</td>\n",
       "      <td>4</td>\n",
       "      <td>&gt;RWMMK</td>\n",
       "      <td>ארוממך</td>\n",
       "      <td>ROWMIM:</td>\n",
       "      <td>רֹומִםְ</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>!&gt;:A!</td>\n",
       "      <td>אֲ</td>\n",
       "      <td>+K@</td>\n",
       "      <td>כָ</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>&gt;:AROWMIM:K@74</td>\n",
       "      <td>אֲרֹומִמְךָ֣</td>\n",
       "      <td>be high</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>RWM[</td>\n",
       "      <td>RWM</td>\n",
       "      <td>רום</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td>absent</td>\n",
       "      <td>sg</td>\n",
       "      <td>24593</td>\n",
       "      <td>verb</td>\n",
       "      <td>&gt;</td>\n",
       "      <td>K</td>\n",
       "      <td>m</td>\n",
       "      <td>sg</td>\n",
       "      <td>p2</td>\n",
       "      <td>p1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>251</td>\n",
       "      <td>6060</td>\n",
       "      <td>verb</td>\n",
       "      <td>NA</td>\n",
       "      <td>m</td>\n",
       "      <td>sg</td>\n",
       "      <td>p2</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>RWM</td>\n",
       "      <td>רום</td>\n",
       "      <td>piel</td>\n",
       "      <td>impf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>335243</td>\n",
       "      <td>2601</td>\n",
       "      <td>3</td>\n",
       "      <td>&gt;LWHJ</td>\n",
       "      <td>אלוהי</td>\n",
       "      <td>&gt;:ELOWH</td>\n",
       "      <td>אֱלֹוה</td>\n",
       "      <td>/AJ</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>&gt;:ELOWHA74J</td>\n",
       "      <td>אֱלֹוהַ֣י</td>\n",
       "      <td>god(s)</td>\n",
       "      <td>m</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>&gt;LHJM/</td>\n",
       "      <td>&gt;LHJM</td>\n",
       "      <td>אלהים</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td>J</td>\n",
       "      <td>pl</td>\n",
       "      <td>24594</td>\n",
       "      <td>subs</td>\n",
       "      <td>n/a</td>\n",
       "      <td>J</td>\n",
       "      <td>unknown</td>\n",
       "      <td>sg</td>\n",
       "      <td>p1</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>18</td>\n",
       "      <td>7211</td>\n",
       "      <td>subs</td>\n",
       "      <td>a</td>\n",
       "      <td>unknown</td>\n",
       "      <td>sg</td>\n",
       "      <td>p1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>&gt;:ELOHIJM</td>\n",
       "      <td>אֱלֹהִים</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>335244</td>\n",
       "      <td>30386</td>\n",
       "      <td>24664</td>\n",
       "      <td>H</td>\n",
       "      <td>ה</td>\n",
       "      <td>HA-</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>HA-</td>\n",
       "      <td>הַ</td>\n",
       "      <td>the</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>H</td>\n",
       "      <td>H</td>\n",
       "      <td>ה</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td>n/a</td>\n",
       "      <td>NA</td>\n",
       "      <td>24595</td>\n",
       "      <td>art</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>art</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>HA</td>\n",
       "      <td>הַ</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>335245</td>\n",
       "      <td>2523</td>\n",
       "      <td>2325</td>\n",
       "      <td>MLK</td>\n",
       "      <td>מלך</td>\n",
       "      <td>M.ELEK:</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>M.E92LEK:</td>\n",
       "      <td>מֶּ֑לֶךְ</td>\n",
       "      <td>king</td>\n",
       "      <td>m</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>MLK/</td>\n",
       "      <td>MLK</td>\n",
       "      <td>מלך</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>sg</td>\n",
       "      <td>24596</td>\n",
       "      <td>subs</td>\n",
       "      <td>n/a</td>\n",
       "      <td>absent</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>20</td>\n",
       "      <td>16</td>\n",
       "      <td>subs</td>\n",
       "      <td>a</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>MELEK:</td>\n",
       "      <td>מֶלֶךְ</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>335246</td>\n",
       "      <td>50272</td>\n",
       "      <td>50238</td>\n",
       "      <td>W</td>\n",
       "      <td>ו</td>\n",
       "      <td>WA-</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>WA-</td>\n",
       "      <td>וַ</td>\n",
       "      <td>and</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>W</td>\n",
       "      <td>W</td>\n",
       "      <td>ו</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td>n/a</td>\n",
       "      <td>NA</td>\n",
       "      <td>24597</td>\n",
       "      <td>conj</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>conj</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>W:</td>\n",
       "      <td>וְ</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>335247</td>\n",
       "      <td>327</td>\n",
       "      <td>3</td>\n",
       "      <td>&gt;BRKH</td>\n",
       "      <td>אברכה</td>\n",
       "      <td>B@R:AK</td>\n",
       "      <td>בָרֲך</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>!&gt;:A!</td>\n",
       "      <td>אֲ</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[@H</td>\n",
       "      <td>ָה</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>&gt;:AB@R:AK@71H</td>\n",
       "      <td>אֲבָרֲכָ֥ה</td>\n",
       "      <td>bless</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>BRK[</td>\n",
       "      <td>BRK</td>\n",
       "      <td>ברך</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td>absent</td>\n",
       "      <td>sg</td>\n",
       "      <td>24598</td>\n",
       "      <td>verb</td>\n",
       "      <td>&gt;</td>\n",
       "      <td>absent</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>p1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>152</td>\n",
       "      <td>7211</td>\n",
       "      <td>verb</td>\n",
       "      <td>NA</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>H=</td>\n",
       "      <td>absent</td>\n",
       "      <td>BRK</td>\n",
       "      <td>ברך</td>\n",
       "      <td>piel</td>\n",
       "      <td>impf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>335248</td>\n",
       "      <td>864</td>\n",
       "      <td>115</td>\n",
       "      <td>CMK</td>\n",
       "      <td>שׁמך</td>\n",
       "      <td>CIM:</td>\n",
       "      <td>שִׁםְ</td>\n",
       "      <td>/</td>\n",
       "      <td>֜</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>+K@</td>\n",
       "      <td>כָ</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>11CIM:K@81</td>\n",
       "      <td>שִׁ֝מְךָ֗</td>\n",
       "      <td>name</td>\n",
       "      <td>m</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>CM/</td>\n",
       "      <td>CM</td>\n",
       "      <td>שׁם</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>sg</td>\n",
       "      <td>24599</td>\n",
       "      <td>subs</td>\n",
       "      <td>n/a</td>\n",
       "      <td>K</td>\n",
       "      <td>m</td>\n",
       "      <td>sg</td>\n",
       "      <td>p2</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>55</td>\n",
       "      <td>367</td>\n",
       "      <td>subs</td>\n",
       "      <td>a</td>\n",
       "      <td>m</td>\n",
       "      <td>sg</td>\n",
       "      <td>p2</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>C;M</td>\n",
       "      <td>שֵׁם</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>335249</td>\n",
       "      <td>20069</td>\n",
       "      <td>15641</td>\n",
       "      <td>L</td>\n",
       "      <td>ל</td>\n",
       "      <td>L:-</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>L:-</td>\n",
       "      <td>לְ</td>\n",
       "      <td>to</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>L</td>\n",
       "      <td>L</td>\n",
       "      <td>ל</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td>n/a</td>\n",
       "      <td>NA</td>\n",
       "      <td>24600</td>\n",
       "      <td>prep</td>\n",
       "      <td>n/a</td>\n",
       "      <td>absent</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>prep</td>\n",
       "      <td>NA</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>L:</td>\n",
       "      <td>לְ</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>335250</td>\n",
       "      <td>438</td>\n",
       "      <td>405</td>\n",
       "      <td>&lt;WLM</td>\n",
       "      <td>עולם</td>\n",
       "      <td>&lt;OWL@M</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>&lt;OWL@71M</td>\n",
       "      <td>עֹולָ֥ם</td>\n",
       "      <td>eternity</td>\n",
       "      <td>m</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>&lt;WLM/</td>\n",
       "      <td>&lt;WLM</td>\n",
       "      <td>עולם</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>sg</td>\n",
       "      <td>24601</td>\n",
       "      <td>subs</td>\n",
       "      <td>n/a</td>\n",
       "      <td>absent</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>115</td>\n",
       "      <td>83</td>\n",
       "      <td>subs</td>\n",
       "      <td>a</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>&lt;OWL@M</td>\n",
       "      <td>עֹולָם</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(HTML(BHSA_DB_DF.loc[i:i+10].to_html(index=False)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "# SAVE DATA\n",
    "final_path = '../data_files/combined/ohb_combined_aligned.csv'\n",
    "OHB_ALIGNED.to_csv(final_path, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test Strong's Numbers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_strongs():\n",
    "    strongs = [s for s in OHB_ALIGNED['strongs']]\n",
    "    lexemes = [l for l in OHB_ALIGNED['lex']]\n",
    "    lex_ids = [i for i in OHB_ALIGNED['lexId']]\n",
    "    lex_mapped = []\n",
    "    for i, id in enumerate(lex_ids):\n",
    "        lex_mapped.append(f\"{id} {lexemes[i]}\")\n",
    "    # Each key is a lex_id paired with its lex word value.\n",
    "    lexIDs_with_mismatches = {id:{} for id in set(lex_mapped)}\n",
    "    # Keep track of the lex_ids we've already checked.\n",
    "    visited = set()\n",
    "    # Iterate over all nodes. \n",
    "    for i, cur_sn in enumerate(strongs):\n",
    "        cur_id = lex_ids[i]\n",
    "        key = lex_mapped[i]\n",
    "        lexIDs_with_mismatches[key][cur_sn] = [i+1]\n",
    "        # If we haven't visited the current lex_id, compare it to the rest of the nodes.\n",
    "        if cur_id not in visited:\n",
    "            for j, new_id in enumerate(lex_ids[i:]):\n",
    "                j += i\n",
    "                new_sn = strongs[j]\n",
    "                if new_id == cur_id:\n",
    "                    # If we've reached the same lex, check its strong number\n",
    "                    # against the current strong number and add the new sn as\n",
    "                    # a key mapped to a list of nodes that this sn occurs. \n",
    "                    if cur_sn != new_sn:\n",
    "                        if new_sn not in lexIDs_with_mismatches[key]:\n",
    "                            lexIDs_with_mismatches[key][new_sn] = [j+1]\n",
    "                        else:\n",
    "                            lexIDs_with_mismatches[key][new_sn].append(j+1)\n",
    "                    else:\n",
    "                        lexIDs_with_mismatches[key][cur_sn].append(j+1)\n",
    "        visited.add(cur_id)\n",
    "        if i % 5000 == 0 and i > 1:\n",
    "            print(i)\n",
    "    return {k:v for k, v in lexIDs_with_mismatches.items() if len(v) > 1}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# strongs_data = check_strongs()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print the mismatch data.\n",
    "def display_sn_mismatches():\n",
    "    sorted_data = dict(sorted(strongs_data.items(), key=lambda t: int(t[0].split(' ')[0])))\n",
    "    nodes_sn = [(k, [v for v in data[k]]) for k in sorted_data]\n",
    "    for nsn in nodes_sn:\n",
    "        lex_id, sns = nsn\n",
    "        print(f\"Lex_id {lex_id}: {sns}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Notes\n",
    "876 of the lexemes have strong number mismatches in OHB_EXTENDED. Most of these consist of a lexeme that appears with 2-3 other strong numbers, but a few have many mismatches, e.g.:\n",
    "\n",
    "Lex_id 363 שַׁ: ['H1571', 'H6965', 'H7945', 'H859', 'H5921', 'H1121', 'H2266', 'H8033', 'H2603', 'H1961', 'H3808', 'H6927', 'H3381', 'H5975', 'H5221', 'H8216', 'H270', 'H369', 'H410', 'H4428', 'H2654', 'H6315', 'H157', 'H8010', 'H5849', 'H1570', 'H7218', 'H2470', 'H3602', 'H1696', 'H5998', 'H5158', 'H559', 'H6213', 'H935', 'H3426', 'H4745', 'H3528', 'H1931', 'H398', 'H2896', 'H3372', 'H5307', 'H5087', 'H3117', 'H4191', 'H3318', 'H2111', 'H6960', 'H1992', 'H8074', 'H5414', 'H5973', 'H3754']\n",
    "\n",
    "Sometimes a lexeme will be assigned its suffix value rather than its actual value. Consider the following example:\n",
    "\n",
    "Lex_id 1 בְּ: ['H9003', 'H2004', 'H5221', 'H2657', 'H5674', 'H8055']\n",
    "\n",
    "H9003 (in/on/with) appears as בָּהֵ֖ן at node 9058 and gets assigned H2004 הֵן (they, fem.) because of the suffix. In this case Logos renders just בְּ and STEP has both בְּ as H9003 and הֶן as H9039 (Op3f, them).\n",
    "\n",
    "Other instances are quite mistaken. For example, at node 335242 (Ps 145:1), OHB_EXTENDED assigns lex 4 (אֱלֹהִים) H433 (false God) where STEP rightly keeps it as H430. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Keep Features / Data Structure\n",
    "\n",
    "Feature: Origin (origin column name)\n",
    "\n",
    "WORD TABLE\n",
    "- BHSA-aligned id: BHSA_DB (_id), excluding/skipping node 16563\n",
    "- Consonantal text: OHB_EXTENDED (word_cons), unpointed sin/shin as in Sefaria & elsewhere\n",
    "- Pointed text: OHB_EXTENDED (word)\n",
    "- Trailer: OHB_EXTENDED (trailer)\n",
    "- Lexeme id (FK): OHB_EXTENDED (lex_id)\n",
    "- Gloss (BSB or LEB): OHB_EXTENDED\n",
    "- Part of Speech: BHSA_DB (sp)\n",
    "- Person: BHSA_DB (ps)\n",
    "- Number: BHSA_DB (nu)\n",
    "- Gender: BHSA_DB (gn)\n",
    "- Verb Tense: BHSA_DB (vt)\n",
    "- Verb Stem: BHSA_DB (vs)\n",
    "- State: BHSA_DB (st)\n",
    "- Pronoun suffix number: BHSA_DB (prs_nu)\n",
    "- Pronoun suffix gender: BHSA_DB (prs_gn)\n",
    "- Pronoun suffix person: BHSA_DB (prs_ps)\n",
    "- **SUFFIX** BHSA_DB (g_prs_utf8) -- not quite right, but can be edited.\n",
    "- BHSA-phrase id (FK): TF API\n",
    "- BHSA-clause id (FK): TF API\n",
    "- BHSA_clause_atom id (FK): TF API\n",
    "- BHSA-sentence id: BH4C_DB (sentence_node_id)\n",
    "- BHS b:ch:v: OHB_EXTENDED\n",
    "- KJV b:ch:v: OHB_EXTENDED\n",
    "- Freq occurrence: BHSA_DB (freq_occ)\n",
    "- Rank occurrence: BHSA_DB (rank_occ)\n",
    "\n",
    "LEXEME TABLE\n",
    "- Lexeme id: OHB_EXTENDED (lex_id)\n",
    "- Lexeme: OHB_EXTENDED (lex)\n",
    "- Freq lex: BHSA_DB (freq_lex)\n",
    "- Rank lex: BHSA_DB (rank_lex)\n",
    "- Name type: BHSA_DB (nametype), used for proper nouns, w/ caution\n",
    "- Strong's (FK): OHB_EXTENDED (strongs)\n",
    "- Gloss (BHSA): OHB_EXTENDED \n",
    "- Gloss (STEP): \n",
    "\n",
    "PHRASE TABLE\n",
    "- Phrase id: BHSA_DB (_id)\n",
    "- Determined: BHSA_DB (det)\n",
    "- Function: BHSA_DB (function)\n",
    "- Number: BHSA_DB (number)\n",
    "- Type: BHSA_DB (typ)\n",
    "\n",
    "CLAUSE TABLE\n",
    "- Clause id: BHSA_DB (_id)\n",
    "- Domain: BHSA_DB (domain)\n",
    "- Kind: BHSA_DB (kind)\n",
    "- Number: BHSA_DB (number), pos in sentence\n",
    "- Relation: BHSA_DB (rela)\n",
    "- Type: typ\n",
    "\n",
    "CLAUSE ATOM TABLE\n",
    "- Clause atom id: BHSA_DB (_id)\n",
    "- Code: BHSA_DB (code)\n",
    "- Paragraph: BHSA_DB (pargr)\n",
    "- Tab: BHSA_DB (tab)\n",
    "- Type: BHSA_DB (typ)\n",
    "\n",
    "BOOK TABLE\n",
    "- Book id: BHSA_DB (_id)\n",
    "- OSIS abbrev: BHSA_DB (OSIS)\n",
    "- LEB abbrev: BHSA_DB (LEB)\n",
    "- Name: Self\n",
    "- Tanakh ordering: Self"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add Data from BHSA_DB and TF API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b title=\"local release\">TF-app:</b> <span title=\"rv2.4.0=#571428c995d309c43ff592f885a5ebb42a87be6f offline under ~/text-fabric-data\">~/text-fabric-data/annotation/app-bhsa/code</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<b title=\"local release\">data:</b> <span title=\"rv1.7.3=#af70cebe9d23ea736bb40e3ccd6768875b52a49d offline under ~/text-fabric-data\">~/text-fabric-data/etcbc/bhsa/tf/c</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<b title=\"local release\">data:</b> <span title=\"rv2.1=#aba4367b49750089e4e4122415a77cac43bd97bc offline under ~/text-fabric-data\">~/text-fabric-data/etcbc/phono/tf/c</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<b title=\"local release\">data:</b> <span title=\"rv2.1=#f45f6cc3c4f933dba6e649f49cdb14a40dcf333f offline under ~/text-fabric-data\">~/text-fabric-data/etcbc/parallels/tf/c</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This is Text-Fabric 9.1.1\n",
      "Api reference : https://annotation.github.io/text-fabric/tf/cheatsheet.html\n",
      "\n",
      "120 features found and 0 ignored\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<b>Text-Fabric:</b> <a target=\"_blank\" href=\"https://annotation.github.io/text-fabric/tf/cheatsheet.html\" title=\"text-fabric-api\">Text-Fabric API 9.1.1</a>, <a target=\"_blank\" href=\"https://github.com/annotation/app-bhsa\" title=\"bhsa TF-app\">app-bhsa v3</a>, <a target=\"_blank\" href=\"https://annotation.github.io/text-fabric/tf/about/searchusage.html\" title=\"Search Templates Introduction and Reference\">Search Reference</a><br><b>Data:</b> <a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/0_home\" title=\"provenance of BHSA = Biblia Hebraica Stuttgartensia Amstelodamensis\">BHSA</a>, <a target=\"_blank\" href=\"https://annotation.github.io/text-fabric/tf/writing/hebrew.html\" title=\"How TF features represent text\">Character table</a>, <a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/0_home\" title=\"BHSA feature documentation\">Feature docs</a><br><b>Features:</b><br><details><summary><b>Parallel Passages</b></summary><b><i><a target=\"_blank\" href=\"https://nbviewer.jupyter.org/github/etcbc/parallels/blob/master/programs/parallels.ipynb\" title=\"~/text-fabric-data/etcbc/parallels/tf/c/crossref.tf\">crossref</a></i></b><br></details><details><summary><b>BHSA = Biblia Hebraica Stuttgartensia Amstelodamensis</b></summary><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/book\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/book.tf\">book</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/book@ll\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/book@am.tf\">book@ll</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/chapter\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/chapter.tf\">chapter</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/code\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/code.tf\">code</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/det\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/det.tf\">det</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/domain\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/domain.tf\">domain</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/freq_lex\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/freq_lex.tf\">freq_lex</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/function\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/function.tf\">function</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/g_cons\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/g_cons.tf\">g_cons</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/g_cons_utf8\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/g_cons_utf8.tf\">g_cons_utf8</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/g_lex\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/g_lex.tf\">g_lex</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/g_lex_utf8\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/g_lex_utf8.tf\">g_lex_utf8</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/g_word\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/g_word.tf\">g_word</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/g_word_utf8\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/g_word_utf8.tf\">g_word_utf8</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/gloss\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/gloss.tf\">gloss</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/gn\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/gn.tf\">gn</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/label\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/label.tf\">label</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/language\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/language.tf\">language</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/lex\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/lex.tf\">lex</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/lex_utf8\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/lex_utf8.tf\">lex_utf8</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/ls\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/ls.tf\">ls</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/nametype\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/nametype.tf\">nametype</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/nme\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/nme.tf\">nme</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/nu\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/nu.tf\">nu</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/number\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/number.tf\">number</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/otype\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/otype.tf\">otype</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/pargr\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/pargr.tf\">pargr</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/pdp\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/pdp.tf\">pdp</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/pfm\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/pfm.tf\">pfm</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/prs\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/prs.tf\">prs</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/prs_gn\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/prs_gn.tf\">prs_gn</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/prs_nu\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/prs_nu.tf\">prs_nu</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/prs_ps\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/prs_ps.tf\">prs_ps</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/ps\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/ps.tf\">ps</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/qere\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/qere.tf\">qere</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/qere_trailer\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/qere_trailer.tf\">qere_trailer</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/qere_trailer_utf8\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/qere_trailer_utf8.tf\">qere_trailer_utf8</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/qere_utf8\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/qere_utf8.tf\">qere_utf8</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/rank_lex\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/rank_lex.tf\">rank_lex</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/rela\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/rela.tf\">rela</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/sp\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/sp.tf\">sp</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/st\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/st.tf\">st</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/tab\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/tab.tf\">tab</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/trailer\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/trailer.tf\">trailer</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/trailer_utf8\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/trailer_utf8.tf\">trailer_utf8</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/txt\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/txt.tf\">txt</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/typ\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/typ.tf\">typ</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/uvf\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/uvf.tf\">uvf</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/vbe\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/vbe.tf\">vbe</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/vbs\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/vbs.tf\">vbs</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/verse\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/verse.tf\">verse</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/voc_lex\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/voc_lex.tf\">voc_lex</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/voc_lex_utf8\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/voc_lex_utf8.tf\">voc_lex_utf8</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/vs\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/vs.tf\">vs</a><br><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/vt\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/vt.tf\">vt</a><br><b><i><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/mother\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/mother.tf\">mother</a></i></b><br><b><i><a target=\"_blank\" href=\"https://etcbc.github.io/bhsa/features/oslots\" title=\"~/text-fabric-data/etcbc/bhsa/tf/c/oslots.tf\">oslots</a></i></b><br></details><details><summary><b>Phonetic Transcriptions</b></summary><a target=\"_blank\" href=\"https://nbviewer.jupyter.org/github/etcbc/phono/blob/master/programs/phono.ipynb\" title=\"~/text-fabric-data/etcbc/phono/tf/c/phono.tf\">phono</a><br><a target=\"_blank\" href=\"https://nbviewer.jupyter.org/github/etcbc/phono/blob/master/programs/phono.ipynb\" title=\"~/text-fabric-data/etcbc/phono/tf/c/phono_trailer.tf\">phono_trailer</a><br></details>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<style>tr.tf.ltr, td.tf.ltr, th.tf.ltr { text-align: left ! important;}\n",
       "tr.tf.rtl, td.tf.rtl, th.tf.rtl { text-align: right ! important;}\n",
       "@font-face {\n",
       "  font-family: \"Gentium Plus\";\n",
       "  src: local('Gentium Plus'), local('GentiumPlus'),\n",
       "    url('/server/static/fonts/GentiumPlus-R.woff') format('woff'),\n",
       "    url('https://github.com/annotation/text-fabric/blob/master/tf/server/static/fonts/GentiumPlus-R.woff?raw=true') format('woff');\n",
       "}\n",
       "\n",
       "@font-face {\n",
       "  font-family: \"Ezra SIL\";\n",
       "  src: local('Ezra SIL'), local('EzraSIL'),\n",
       "    url('/server/static/fonts/SILEOT.woff') format('woff'),\n",
       "    url('https://github.com/annotation/text-fabric/blob/master/tf/server/static/fonts/SILEOT.woff?raw=true') format('woff');\n",
       "}\n",
       "\n",
       "@font-face {\n",
       "  font-family: \"SBL Hebrew\";\n",
       "  src: local('SBL Hebrew'), local('SBLHebrew'),\n",
       "    url('/server/static/fonts/SBL_Hbrw.woff') format('woff'),\n",
       "    url('https://github.com/annotation/text-fabric/blob/master/tf/server/static/fonts/SBL_Hbrw.woff?raw=true') format('woff');\n",
       "}\n",
       "\n",
       "@font-face {\n",
       "  font-family: \"Estrangelo Edessa\";\n",
       "  src: local('Estrangelo Edessa'), local('EstrangeloEdessa');\n",
       "    url('/server/static/fonts/SyrCOMEdessa.woff') format('woff'),\n",
       "    url('https://github.com/annotation/text-fabric/blob/master/tf/server/static/fonts/SyrCOMEdessa.woff?raw=true') format('woff');\n",
       "}\n",
       "\n",
       "@font-face {\n",
       "  font-family: AmiriQuran;\n",
       "  font-style: normal;\n",
       "  font-weight: 400;\n",
       "  src: local('Amiri Quran'), local('AmiriQuran'),\n",
       "    url('/server/static/fonts/AmiriQuran.woff') format('woff'),\n",
       "    url('https://github.com/annotation/text-fabric/blob/master/tf/server/static/fonts/AmiriQuran.woff?raw=true') format('woff');\n",
       "}\n",
       "\n",
       "@font-face {\n",
       "  font-family: AmiriQuranColored;\n",
       "  font-style: normal;\n",
       "  font-weight: 400;\n",
       "  src: local('Amiri Quran Colored'), local('AmiriQuranColored'),\n",
       "    url('/server/static/fonts/AmiriQuranColored.woff') format('woff'),\n",
       "    url('https://github.com/annotation/text-fabric/blob/master/tf/server/static/fonts/AmiriQuranColored.woff?raw=true') format('woff');\n",
       "}\n",
       "\n",
       "@font-face {\n",
       "  font-family: \"Santakku\";\n",
       "  src: local('Santakku'),\n",
       "    url('/server/static/fonts/Santakku.woff') format('woff'),\n",
       "    url('https://github.com/annotation/text-fabric/blob/master/tf/server/static/fonts/Santakku.woff?raw=true') format('woff');\n",
       "}\n",
       "\n",
       "@font-face {\n",
       "  font-family: \"SantakkuM\";\n",
       "  src: local('SantakkuM'),\n",
       "    url('/server/static/fonts/SantakkuM.woff') format('woff'),\n",
       "    url('https://github.com/annotation/text-fabric/blob/master/tf/server/static/fonts/SantakkuM.woff?raw=true') format('woff');\n",
       "}\n",
       "/* bypassing some classical notebook settings */\n",
       "div#notebook {\n",
       "  line-height: unset;\n",
       "}\n",
       "/* neutral text */\n",
       ".txtn,.txtn a:visited,.txtn a:link {\n",
       "    font-family: sans-serif;\n",
       "    font-size: medium;\n",
       "    direction: ltr;\n",
       "    unicode-bidi: embed;\n",
       "    text-decoration: none;\n",
       "    color: var(--text-color);\n",
       "}\n",
       "/* transcription text */\n",
       ".txtt,.txtt a:visited,.txtt a:link {\n",
       "    font-family: monospace;\n",
       "    font-size: medium;\n",
       "    direction: ltr;\n",
       "    unicode-bidi: embed;\n",
       "    text-decoration: none;\n",
       "    color: var(--text-color);\n",
       "}\n",
       "/* source text */\n",
       ".txto,.txto a:visited,.txto a:link {\n",
       "    font-family: serif;\n",
       "    font-size: medium;\n",
       "    direction: ltr;\n",
       "    unicode-bidi: embed;\n",
       "    text-decoration: none;\n",
       "    color: var(--text-color);\n",
       "}\n",
       "/* phonetic text */\n",
       ".txtp,.txtp a:visited,.txtp a:link {\n",
       "    font-family: Gentium, sans-serif;\n",
       "    font-size: medium;\n",
       "    direction: ltr;\n",
       "    unicode-bidi: embed;\n",
       "    text-decoration: none;\n",
       "    color: var(--text-color);\n",
       "}\n",
       "/* original script text */\n",
       ".txtu,.txtu a:visited,.txtu a:link {\n",
       "    font-family: Gentium, sans-serif;\n",
       "    font-size: medium;\n",
       "    text-decoration: none;\n",
       "    color: var(--text-color);\n",
       "}\n",
       "/* hebrew */\n",
       ".txtu.hbo,.lex.hbo {\n",
       "    font-family: \"Ezra SIL\", \"SBL Hebrew\", sans-serif;\n",
       "    font-size: large;\n",
       "    direction: rtl ! important;\n",
       "    unicode-bidi: embed;\n",
       "}\n",
       "/* syriac */\n",
       ".txtu.syc,.lex.syc {\n",
       "    font-family: \"Estrangelo Edessa\", sans-serif;\n",
       "    font-size: medium;\n",
       "    direction: rtl ! important;\n",
       "    unicode-bidi: embed;\n",
       "}\n",
       "/* neo aramaic */\n",
       ".txtu.cld,.lex.cld {\n",
       "    font-family: \"CharisSIL-R\", sans-serif;\n",
       "    font-size: medium;\n",
       "    direction: ltr ! important;\n",
       "    unicode-bidi: embed;\n",
       "}\n",
       "/* standard arabic */\n",
       ".txtu.ara,.lex.ara {\n",
       "    font-family: \"AmiriQuran\", sans-serif;\n",
       "    font-size: large;\n",
       "    direction: rtl ! important;\n",
       "    unicode-bidi: embed;\n",
       "}\n",
       "/* cuneiform */\n",
       ".txtu.akk,.lex.akk {\n",
       "    font-family: Santakku, sans-serif;\n",
       "    font-size: large;\n",
       "    direction: ltr ! important;\n",
       "    unicode-bidi: embed;\n",
       "}\n",
       "/* greek */\n",
       ".txtu.grc,.lex.grc a:link {\n",
       "    font-family: Gentium, sans-serif;\n",
       "    font-size: medium;\n",
       "    direction: ltr ! important;\n",
       "    unicode-bidi: embed;\n",
       "}\n",
       "a:hover {\n",
       "    text-decoration: underline | important;\n",
       "    color: #0000ff | important;\n",
       "}\n",
       ".ltr {\n",
       "    direction: ltr ! important;\n",
       "    unicode-bidi: embed;\n",
       "}\n",
       ".rtl {\n",
       "    direction: rtl ! important;\n",
       "    unicode-bidi: embed;\n",
       "}\n",
       ".ubd {\n",
       "    unicode-bidi: embed;\n",
       "}\n",
       ".col {\n",
       "   display: inline-block;\n",
       "}\n",
       ".features {\n",
       "    font-family: monospace;\n",
       "    font-size: medium;\n",
       "    font-weight: bold;\n",
       "    color: var(--features);\n",
       "    display: flex;\n",
       "    flex-flow: column nowrap;\n",
       "    justify-content: flex-start;\n",
       "    align-items: flex-start;\n",
       "    align-content: flex-start;\n",
       "    padding: 2px;\n",
       "    margin: 2px;\n",
       "    direction: ltr;\n",
       "    unicode-bidi: embed;\n",
       "    border: var(--meta-width) solid var(--meta-color);\n",
       "    border-radius: var(--meta-width);\n",
       "}\n",
       ".features div,.features span {\n",
       "    padding: 0;\n",
       "    margin: -2px 0;\n",
       "}\n",
       ".features .f {\n",
       "    font-family: sans-serif;\n",
       "    font-size: small;\n",
       "    font-weight: normal;\n",
       "    color: #5555bb;\n",
       "}\n",
       ".features .xft {\n",
       "  color: #000000;\n",
       "  background-color: #eeeeee;\n",
       "  font-size: medium;\n",
       "  margin: 2px 0px;\n",
       "}\n",
       ".features .xft .f {\n",
       "  color: #000000;\n",
       "  background-color: #eeeeee;\n",
       "  font-size: small;\n",
       "  font-weight: normal;\n",
       "}\n",
       ".tfsechead {\n",
       "    font-family: sans-serif;\n",
       "    font-size: small;\n",
       "    font-weight: bold;\n",
       "    color: var(--tfsechead);\n",
       "    unicode-bidi: embed;\n",
       "    text-align: start;\n",
       "}\n",
       ".structure {\n",
       "    font-family: sans-serif;\n",
       "    font-size: small;\n",
       "    font-weight: bold;\n",
       "    color: var(--structure);\n",
       "    unicode-bidi: embed;\n",
       "    text-align: start;\n",
       "}\n",
       ".comments {\n",
       "    display: flex;\n",
       "    justify-content: flex-start;\n",
       "    align-items: flex-start;\n",
       "    align-content: flex-start;\n",
       "    flex-flow: column nowrap;\n",
       "}\n",
       ".nd, a:link.nd {\n",
       "    font-family: sans-serif;\n",
       "    font-size: small;\n",
       "    color: var(--node);\n",
       "    vertical-align: super;\n",
       "    direction: ltr ! important;\n",
       "    unicode-bidi: embed;\n",
       "}\n",
       ".lex {\n",
       "  color: var(--lex-color);;\n",
       "}\n",
       ".children,.children.ltr {\n",
       "    display: flex;\n",
       "    border: 0;\n",
       "    background-color: #ffffff;\n",
       "    justify-content: flex-start;\n",
       "    align-items: flex-start;\n",
       "    align-content: flex-start;\n",
       "}\n",
       ".children.stretch {\n",
       "    align-items: stretch;\n",
       "}\n",
       ".children.hor {\n",
       "    flex-flow: row nowrap;\n",
       "}\n",
       ".children.hor.wrap {\n",
       "    flex-flow: row wrap;\n",
       "}\n",
       ".children.ver {\n",
       "    flex-flow: column nowrap;\n",
       "}\n",
       ".children.ver.wrap {\n",
       "    flex-flow: column wrap;\n",
       "}\n",
       ".contnr {\n",
       "    width: fit-content;\n",
       "    display: flex;\n",
       "    justify-content: flex-start;\n",
       "    align-items: flex-start;\n",
       "    align-content: flex-start;\n",
       "    flex-flow: column nowrap;\n",
       "    background: #ffffff none repeat scroll 0 0;\n",
       "    padding:  10px 2px 2px 2px;\n",
       "    margin: 16px 2px 2px 2px;\n",
       "    border-style: solid;\n",
       "    font-size: small;\n",
       "}\n",
       ".contnr.trm {\n",
       "    background-attachment: local;\n",
       "}\n",
       ".contnr.cnul {\n",
       "    padding:  0;\n",
       "    margin: 0;\n",
       "    border-style: solid;\n",
       "    font-size: xx-small;\n",
       "}\n",
       ".contnr.cnul,.lbl.cnul {\n",
       "    border-color: var(--border-color-nul);\n",
       "    border-width: var(--border-width-nul);\n",
       "    border-radius: var(--border-width-nul);\n",
       "}\n",
       ".contnr.c0,.lbl.c0 {\n",
       "    border-color: var(--border-color0);\n",
       "    border-width: var(--border-width0);\n",
       "    border-radius: var(--border-width0);\n",
       "}\n",
       ".contnr.c1,.lbl.c1 {\n",
       "    border-color: var(--border-color1);\n",
       "    border-width: var(--border-width1);\n",
       "    border-radius: var(--border-width1);\n",
       "}\n",
       ".contnr.c2,.lbl.c2 {\n",
       "    border-color: var(--border-color2);\n",
       "    border-width: var(--border-width2);\n",
       "    border-radius: var(--border-width2);\n",
       "}\n",
       ".contnr.c3,.lbl.c3 {\n",
       "    border-color: var(--border-color3);\n",
       "    border-width: var(--border-width3);\n",
       "    border-radius: var(--border-width3);\n",
       "}\n",
       ".contnr.c4,.lbl.c4 {\n",
       "    border-color: var(--border-color4);\n",
       "    border-width: var(--border-width4);\n",
       "    border-radius: var(--border-width4);\n",
       "}\n",
       "span.plain {\n",
       "    display: inline-block;\n",
       "    white-space: pre-wrap;\n",
       "}\n",
       ".plain {\n",
       "    background-color: #ffffff;\n",
       "}\n",
       ".plain.l,.contnr.l,.contnr.l>.lbl {\n",
       "    border-left-style: dotted\n",
       "}\n",
       ".plain.r,.contnr.r,.contnr.r>.lbl {\n",
       "    border-right-style: dotted\n",
       "}\n",
       ".plain.lno,.contnr.lno,.contnr.lno>.lbl {\n",
       "    border-left-style: none\n",
       "}\n",
       ".plain.rno,.contnr.rno,.contnr.rno>.lbl {\n",
       "    border-right-style: none\n",
       "}\n",
       ".plain.l {\n",
       "    padding-left: 4px;\n",
       "    margin-left: 2px;\n",
       "    border-width: var(--border-width-plain);\n",
       "}\n",
       ".plain.r {\n",
       "    padding-right: 4px;\n",
       "    margin-right: 2px;\n",
       "    border-width: var(--border-width-plain);\n",
       "}\n",
       ".lbl {\n",
       "    font-family: monospace;\n",
       "    margin-top: -24px;\n",
       "    margin-left: 20px;\n",
       "    background: #ffffff none repeat scroll 0 0;\n",
       "    padding: 0 6px;\n",
       "    border-style: solid;\n",
       "    display: block;\n",
       "    color: var(--label)\n",
       "}\n",
       ".lbl.trm {\n",
       "    background-attachment: local;\n",
       "    margin-top: 2px;\n",
       "    margin-left: 2px;\n",
       "    padding: 2px 2px;\n",
       "    border-style: none;\n",
       "}\n",
       ".lbl.cnul {\n",
       "    font-size: xx-small;\n",
       "}\n",
       ".lbl.c0 {\n",
       "    font-size: small;\n",
       "}\n",
       ".lbl.c1 {\n",
       "    font-size: small;\n",
       "}\n",
       ".lbl.c2 {\n",
       "    font-size: medium;\n",
       "}\n",
       ".lbl.c3 {\n",
       "    font-size: medium;\n",
       "}\n",
       ".lbl.c4 {\n",
       "    font-size: large;\n",
       "}\n",
       ".occs, a:link.occs {\n",
       "    font-size: small;\n",
       "}\n",
       "\n",
       "/* PROVENANCE */\n",
       "\n",
       "div.prov {\n",
       "\tmargin: 40px;\n",
       "\tpadding: 20px;\n",
       "\tborder: 2px solid var(--fog-rim);\n",
       "}\n",
       "div.pline {\n",
       "\tdisplay: flex;\n",
       "\tflex-flow: row nowrap;\n",
       "\tjustify-content: stretch;\n",
       "\talign-items: baseline;\n",
       "}\n",
       "div.p2line {\n",
       "\tmargin-left: 2em;\n",
       "\tdisplay: flex;\n",
       "\tflex-flow: row nowrap;\n",
       "\tjustify-content: stretch;\n",
       "\talign-items: baseline;\n",
       "}\n",
       "div.psline {\n",
       "\tdisplay: flex;\n",
       "\tflex-flow: row nowrap;\n",
       "\tjustify-content: stretch;\n",
       "\talign-items: baseline;\n",
       "\tbackground-color: var(--gold-mist-back);\n",
       "}\n",
       "div.pname {\n",
       "\tflex: 0 0 5rem;\n",
       "\tfont-weight: bold;\n",
       "}\n",
       "div.pval {\n",
       "    flex: 1 1 auto;\n",
       "}\n",
       "\n",
       ":root {\n",
       "\t--node:               hsla(120, 100%,  20%, 1.0  );\n",
       "\t--label:              hsla(  0, 100%,  20%, 1.0  );\n",
       "\t--tfsechead:          hsla(  0, 100%,  25%, 1.0  );\n",
       "\t--structure:          hsla(120, 100%,  25%, 1.0  );\n",
       "\t--features:           hsla(  0,   0%,  30%, 1.0  );\n",
       "  --text-color:         hsla( 60,  80%,  10%, 1.0  );\n",
       "  --lex-color:          hsla(220,  90%,  60%, 1.0  );\n",
       "  --meta-color:         hsla(  0,   0%,  90%, 0.7  );\n",
       "  --meta-width:         3px;\n",
       "  --border-color-nul:   hsla(  0,   0%,  90%, 0.5  );\n",
       "  --border-color0:      hsla(  0,   0%,  90%, 0.9  );\n",
       "  --border-color1:      hsla(  0,   0%,  80%, 0.9  );\n",
       "  --border-color2:      hsla(  0,   0%,  70%, 0.9  );\n",
       "  --border-color3:      hsla(  0,   0%,  80%, 0.8  );\n",
       "  --border-color4:      hsla(  0,   0%,  60%, 0.9  );\n",
       "  --border-width-nul:   2px;\n",
       "  --border-width0:      2px;\n",
       "  --border-width1:      3px;\n",
       "  --border-width2:      4px;\n",
       "  --border-width3:      6px;\n",
       "  --border-width4:      5px;\n",
       "  --border-width-plain: 2px;\n",
       "}\n",
       ".hl {\n",
       "  background-color: var(--hl-strong);\n",
       "}\n",
       "span.hl {\n",
       "\tbackground-color: var(--hl-strong);\n",
       "\tborder-width: 0;\n",
       "\tborder-radius: 2px;\n",
       "\tborder-style: solid;\n",
       "}\n",
       "div.contnr.hl,div.lbl.hl {\n",
       "  background-color: var(--hl-strong);\n",
       "}\n",
       "div.contnr.hl {\n",
       "  border-color: var(--hl-rim) ! important;\n",
       "\tborder-width: 4px ! important;\n",
       "}\n",
       "\n",
       "span.hlbx {\n",
       "\tborder-color: var(--hl-rim);\n",
       "\tborder-width: 4px ! important;\n",
       "\tborder-style: solid;\n",
       "\tborder-radius: 6px;\n",
       "  padding: 4px;\n",
       "  margin: 4px;\n",
       "}\n",
       "\n",
       "span.plain {\n",
       "  display: inline-block;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "\n",
       ":root {\n",
       "\t--hl-strong:        hsla( 60, 100%,  70%, 0.9  );\n",
       "\t--hl-rim:           hsla( 55,  80%,  50%, 1.0  );\n",
       "}\n",
       "</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div><b>Text-Fabric API:</b> names <a target=\"_blank\" href=\"https://annotation.github.io/text-fabric/tf/cheatsheet.html\" title=\"doc\">N F E L T S C TF</a> directly usable</div><hr>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "A = use('bhsa', hoist=globals(), checkout='local', version='c')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the aligned file that was cleaned\n",
    "OHB_ALIGNED_PATH = '../data_files/combined/ohb_combined_aligned.csv'\n",
    "OHB_ALIGNED = pd.read_csv(OHB_ALIGNED_PATH, sep=',', low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "SKIP = 16563\n",
    "\n",
    "words = [n for n in F.otype.s('word') if n != SKIP]\n",
    "LEX_IDS = [L.u(i, otype='lex')[0] for i in words]\n",
    "PHRASE_IDS = [L.u(i, otype='phrase')[0] for i in words]\n",
    "CLAUSE_ATOM_IDS = [L.u(i, otype='clause_atom')[0] for i in words]\n",
    "CLAUSE_IDS = [L.u(i, otype='clause')[0] for i in words]\n",
    "SENTENCE_IDS = [L.u(i, otype='sentence')[0] for i in words]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "lex data doesn't look right...\n",
      "Len: 426583-426583 Start: 1437567-1437567 End: 1437689-1446799\n",
      "phrase data looks good!\n",
      "clause_atom data looks good!\n",
      "clause data looks good!\n",
      "sentence data looks good!\n"
     ]
    }
   ],
   "source": [
    "# Test data\n",
    "data_map = {\n",
    "    'lex': LEX_IDS,\n",
    "    'phrase': PHRASE_IDS,\n",
    "    'clause_atom': CLAUSE_ATOM_IDS,\n",
    "    'clause': CLAUSE_IDS,\n",
    "    'sentence': SENTENCE_IDS\n",
    "}\n",
    "ohb_len = len(OHB_ALIGNED.index)\n",
    "for k in data_map:\n",
    "    data = data_map[k]\n",
    "    data_len = len(data)\n",
    "    start_node = data[0]\n",
    "    end_node = data[-1]\n",
    "    start_tf = F.otype.s(k)[0]\n",
    "    end_tf = F.otype.s(k)[-1]\n",
    "    if data_len == ohb_len \\\n",
    "    and start_node == start_tf \\\n",
    "    and end_node == end_tf:\n",
    "        print(f\"{k} data looks good!\")\n",
    "    else:\n",
    "        # 'lex' will fail because of the last node, but it IS accurate.\n",
    "        print(f\"{k} data doesn't look right...\")\n",
    "        print(f\"Len: {data_len}-{ohb_len} Start: {start_node}-{start_tf} End: {end_node}-{end_tf}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>_id</th>\n",
       "      <th>freq_lex</th>\n",
       "      <th>freq_occ</th>\n",
       "      <th>g_cons</th>\n",
       "      <th>g_cons_utf8</th>\n",
       "      <th>g_lex</th>\n",
       "      <th>g_lex_utf8</th>\n",
       "      <th>g_nme</th>\n",
       "      <th>g_nme_utf8</th>\n",
       "      <th>g_pfm</th>\n",
       "      <th>g_pfm_utf8</th>\n",
       "      <th>g_prs</th>\n",
       "      <th>g_prs_utf8</th>\n",
       "      <th>g_uvf</th>\n",
       "      <th>g_uvf_utf8</th>\n",
       "      <th>g_vbe</th>\n",
       "      <th>g_vbe_utf8</th>\n",
       "      <th>g_vbs</th>\n",
       "      <th>g_vbs_utf8</th>\n",
       "      <th>g_word</th>\n",
       "      <th>g_word_utf8</th>\n",
       "      <th>gloss</th>\n",
       "      <th>gn</th>\n",
       "      <th>kq_hybrid</th>\n",
       "      <th>kq_hybrid_utf8</th>\n",
       "      <th>language</th>\n",
       "      <th>languageISO</th>\n",
       "      <th>lex</th>\n",
       "      <th>lex0</th>\n",
       "      <th>lex_utf8</th>\n",
       "      <th>lexeme_count</th>\n",
       "      <th>ls</th>\n",
       "      <th>nametype</th>\n",
       "      <th>nme</th>\n",
       "      <th>nu</th>\n",
       "      <th>number</th>\n",
       "      <th>pdp</th>\n",
       "      <th>pfm</th>\n",
       "      <th>prs</th>\n",
       "      <th>prs_gn</th>\n",
       "      <th>prs_nu</th>\n",
       "      <th>prs_ps</th>\n",
       "      <th>ps</th>\n",
       "      <th>qere</th>\n",
       "      <th>qere_trailer</th>\n",
       "      <th>qere_trailer_utf8</th>\n",
       "      <th>qere_utf8</th>\n",
       "      <th>rank_lex</th>\n",
       "      <th>rank_occ</th>\n",
       "      <th>sp</th>\n",
       "      <th>st</th>\n",
       "      <th>suffix_gender</th>\n",
       "      <th>suffix_number</th>\n",
       "      <th>suffix_person</th>\n",
       "      <th>trailer</th>\n",
       "      <th>trailer_utf8</th>\n",
       "      <th>uvf</th>\n",
       "      <th>vbe</th>\n",
       "      <th>vbs</th>\n",
       "      <th>voc_lex</th>\n",
       "      <th>voc_lex_utf8</th>\n",
       "      <th>vs</th>\n",
       "      <th>vt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>16562</td>\n",
       "      <td>15542</td>\n",
       "      <td>14194</td>\n",
       "      <td>B</td>\n",
       "      <td>ב</td>\n",
       "      <td>B.A-</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>B.A-</td>\n",
       "      <td>בַּ</td>\n",
       "      <td>in</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>B</td>\n",
       "      <td>B</td>\n",
       "      <td>ב</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td>n/a</td>\n",
       "      <td>NA</td>\n",
       "      <td>16562</td>\n",
       "      <td>prep</td>\n",
       "      <td>n/a</td>\n",
       "      <td>absent</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>prep</td>\n",
       "      <td>NA</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>B.:</td>\n",
       "      <td>בְּ</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16563</td>\n",
       "      <td>30386</td>\n",
       "      <td>6487</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",
       "      <td></td>\n",
       "      <td>-</td>\n",
       "      <td></td>\n",
       "      <td>the</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>H</td>\n",
       "      <td>H</td>\n",
       "      <td>ה</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td>n/a</td>\n",
       "      <td>NA</td>\n",
       "      <td>16563</td>\n",
       "      <td>art</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>art</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>HA</td>\n",
       "      <td>הַ</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16564</td>\n",
       "      <td>65</td>\n",
       "      <td>35</td>\n",
       "      <td>XLWM</td>\n",
       "      <td>חלום</td>\n",
       "      <td>X:ALOWM</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>X:ALO73WM</td>\n",
       "      <td>חֲלֹ֖ום</td>\n",
       "      <td>dream</td>\n",
       "      <td>m</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>XLWM/</td>\n",
       "      <td>XLWM</td>\n",
       "      <td>חלום</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>sg</td>\n",
       "      <td>16564</td>\n",
       "      <td>subs</td>\n",
       "      <td>n/a</td>\n",
       "      <td>absent</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>586</td>\n",
       "      <td>1099</td>\n",
       "      <td>subs</td>\n",
       "      <td>a</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>X:ALOWM</td>\n",
       "      <td>חֲלֹום</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>_id</th>\n",
       "      <th>freq_lex</th>\n",
       "      <th>freq_occ</th>\n",
       "      <th>g_cons</th>\n",
       "      <th>g_cons_utf8</th>\n",
       "      <th>g_lex</th>\n",
       "      <th>g_lex_utf8</th>\n",
       "      <th>g_nme</th>\n",
       "      <th>g_nme_utf8</th>\n",
       "      <th>g_pfm</th>\n",
       "      <th>g_pfm_utf8</th>\n",
       "      <th>g_prs</th>\n",
       "      <th>g_prs_utf8</th>\n",
       "      <th>g_uvf</th>\n",
       "      <th>g_uvf_utf8</th>\n",
       "      <th>g_vbe</th>\n",
       "      <th>g_vbe_utf8</th>\n",
       "      <th>g_vbs</th>\n",
       "      <th>g_vbs_utf8</th>\n",
       "      <th>g_word</th>\n",
       "      <th>g_word_utf8</th>\n",
       "      <th>gloss</th>\n",
       "      <th>gn</th>\n",
       "      <th>kq_hybrid</th>\n",
       "      <th>kq_hybrid_utf8</th>\n",
       "      <th>language</th>\n",
       "      <th>languageISO</th>\n",
       "      <th>lex</th>\n",
       "      <th>lex0</th>\n",
       "      <th>lex_utf8</th>\n",
       "      <th>lexeme_count</th>\n",
       "      <th>ls</th>\n",
       "      <th>nametype</th>\n",
       "      <th>nme</th>\n",
       "      <th>nu</th>\n",
       "      <th>number</th>\n",
       "      <th>pdp</th>\n",
       "      <th>pfm</th>\n",
       "      <th>prs</th>\n",
       "      <th>prs_gn</th>\n",
       "      <th>prs_nu</th>\n",
       "      <th>prs_ps</th>\n",
       "      <th>ps</th>\n",
       "      <th>qere</th>\n",
       "      <th>qere_trailer</th>\n",
       "      <th>qere_trailer_utf8</th>\n",
       "      <th>qere_utf8</th>\n",
       "      <th>rank_lex</th>\n",
       "      <th>rank_occ</th>\n",
       "      <th>sp</th>\n",
       "      <th>st</th>\n",
       "      <th>suffix_gender</th>\n",
       "      <th>suffix_number</th>\n",
       "      <th>suffix_person</th>\n",
       "      <th>trailer</th>\n",
       "      <th>trailer_utf8</th>\n",
       "      <th>uvf</th>\n",
       "      <th>vbe</th>\n",
       "      <th>vbs</th>\n",
       "      <th>voc_lex</th>\n",
       "      <th>voc_lex_utf8</th>\n",
       "      <th>vs</th>\n",
       "      <th>vt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>16562</td>\n",
       "      <td>15542</td>\n",
       "      <td>14194</td>\n",
       "      <td>B</td>\n",
       "      <td>ב</td>\n",
       "      <td>B.A-</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>B.A-</td>\n",
       "      <td>בַּ</td>\n",
       "      <td>in</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>B</td>\n",
       "      <td>B</td>\n",
       "      <td>ב</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td>n/a</td>\n",
       "      <td>NA</td>\n",
       "      <td>16562</td>\n",
       "      <td>prep</td>\n",
       "      <td>n/a</td>\n",
       "      <td>absent</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>prep</td>\n",
       "      <td>NA</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>B.:</td>\n",
       "      <td>בְּ</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16564</td>\n",
       "      <td>65</td>\n",
       "      <td>35</td>\n",
       "      <td>XLWM</td>\n",
       "      <td>חלום</td>\n",
       "      <td>X:ALOWM</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>X:ALO73WM</td>\n",
       "      <td>חֲלֹ֖ום</td>\n",
       "      <td>dream</td>\n",
       "      <td>m</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>XLWM/</td>\n",
       "      <td>XLWM</td>\n",
       "      <td>חלום</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>sg</td>\n",
       "      <td>16564</td>\n",
       "      <td>subs</td>\n",
       "      <td>n/a</td>\n",
       "      <td>absent</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>586</td>\n",
       "      <td>1099</td>\n",
       "      <td>subs</td>\n",
       "      <td>a</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>X:ALOWM</td>\n",
       "      <td>חֲלֹום</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16565</td>\n",
       "      <td>349</td>\n",
       "      <td>345</td>\n",
       "      <td>J&lt;QB</td>\n",
       "      <td>יעקב</td>\n",
       "      <td>JA&lt;:AQOB</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>JA95&lt;:AQO92B</td>\n",
       "      <td>יַֽעֲקֹ֑ב</td>\n",
       "      <td>Jacob</td>\n",
       "      <td>m</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Hebrew</td>\n",
       "      <td>hbo</td>\n",
       "      <td>J&lt;QB/</td>\n",
       "      <td>J&lt;QB</td>\n",
       "      <td>יעקב</td>\n",
       "      <td>0</td>\n",
       "      <td>none</td>\n",
       "      <td>pers</td>\n",
       "      <td></td>\n",
       "      <td>sg</td>\n",
       "      <td>16565</td>\n",
       "      <td>nmpr</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>143</td>\n",
       "      <td>109</td>\n",
       "      <td>nmpr</td>\n",
       "      <td>a</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>absent</td>\n",
       "      <td>n/a</td>\n",
       "      <td>n/a</td>\n",
       "      <td>JA&lt;:AQOB</td>\n",
       "      <td>יַעֲקֹב</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Drop the SKIP row to import data into the new DB with alignment.\n",
    "display(HTML(BHSA_DB_DF.loc[SKIP-2:SKIP].to_html(index=False)))\n",
    "BHSA_V2=BHSA_DB_DF.drop(BHSA_DB_DF.index[SKIP-1])\n",
    "BHSA_V2.reset_index(drop=True, inplace=True)\n",
    "display(HTML(BHSA_V2.loc[SKIP-2:SKIP].to_html(index=False)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load in tables from the BHSA DB\n",
    "LEX_DF = pd.read_sql_query(\"SELECT * FROM lex\", BHSA_DB_CON)\n",
    "PHRASE_DF = pd.read_sql_query(\"SELECT * FROM phrase\", BHSA_DB_CON)\n",
    "CLAUSE_DF = pd.read_sql_query(\"SELECT * FROM clause\", BHSA_DB_CON)\n",
    "CLAUSE_ATOM_DF = pd.read_sql_query(\"SELECT * FROM clause_atom\", BHSA_DB_CON)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "WORD_COLS = {\n",
    "    'wordId': OHB_ALIGNED['BHSwordSort'],\n",
    "    'book': OHB_ALIGNED['KJVbook'],\n",
    "    'chKJV': OHB_ALIGNED['KJVchapter'],\n",
    "    'vsKJV': OHB_ALIGNED['KJVverse'],\n",
    "    'vsIdKJV': OHB_ALIGNED['KVJvsNode'],\n",
    "    'chBHS': OHB_ALIGNED['BHSchapter'],\n",
    "    'vsBHS': OHB_ALIGNED['BHSverse'],\n",
    "    'vsIdBHS': OHB_ALIGNED['BHSvsNode'],\n",
    "    # 'lang': OHB_ALIGNED['lang'], Get this from Lex\n",
    "    # 'speech': BHSA_V2['sp'], Get this from Lex\n",
    "    'person': BHSA_V2['ps'],\n",
    "    'gender': BHSA_V2['gn'],\n",
    "    'number': BHSA_V2['nu'],\n",
    "    'vTense': BHSA_V2['vt'],\n",
    "    'vStem': BHSA_V2['vs'],\n",
    "    'state': BHSA_V2['st'],\n",
    "    'prsPerson': BHSA_V2['prs_ps'],\n",
    "    'prsGender': BHSA_V2['prs_gn'],\n",
    "    'prsNumber': BHSA_V2['prs_nu'],\n",
    "    'suffix': BHSA_V2['g_prs_utf8'],\n",
    "    'text': OHB_ALIGNED['BHSwordPointed'],\n",
    "    'textCons': OHB_ALIGNED['BHSwordConsonantal'],\n",
    "    'trailer': OHB_ALIGNED['Trailer'],\n",
    "    'transliteration': OHB_ALIGNED['SBLstyleTransliteration'],\n",
    "    'glossExt': OHB_ALIGNED['extendedGloss'],\n",
    "    'glossBSB': OHB_ALIGNED['BSBgloss'],\n",
    "    'sortBSB': OHB_ALIGNED['BSBglossNode'],\n",
    "    'strongsId': OHB_ALIGNED['extendedStrongNumber'],\n",
    "    'lexId': LEX_IDS,\n",
    "    'phraseId': PHRASE_IDS,\n",
    "    'clauseAtomId': CLAUSE_ATOM_IDS,\n",
    "    'clauseId': CLAUSE_IDS,\n",
    "    'sentenceId': SENTENCE_IDS,\n",
    "    'freqOcc': BHSA_V2['freq_occ'],\n",
    "    'rankOcc': BHSA_V2['rank_occ'],\n",
    "    'poetryMarker': OHB_ALIGNED['poetryMarker'],\n",
    "    'parMarker': OHB_ALIGNED['paragraphMarker'],\n",
    "}\n",
    "\n",
    "LEX_COLS = {\n",
    "    'lexId': LEX_DF['_id'],\n",
    "    'language': [{'Hebrew':'hbo','Aramaic':'arc'}[k] for k in LEX_DF['language']],\n",
    "    'speech': LEX_DF['sp'],\n",
    "    'nameType': LEX_DF['nametype'],\n",
    "    'lexSet': LEX_DF['ls'],\n",
    "    'lexText': LEX_DF['voc_lex_utf8'],\n",
    "    'gloss': LEX_DF['gloss'],\n",
    "    'freqLex': LEX_DF['freq_lex'],\n",
    "    'rankLex': LEX_DF['rank_lex'],\n",
    "    # 'strongs': ...\n",
    "    # 'gloss_STEP': ...\n",
    "}\n",
    "\n",
    "PHRASE_COLS = {\n",
    "    'phraseId': PHRASE_DF['_id'],\n",
    "    'determined': PHRASE_DF['det'],\n",
    "    'function': PHRASE_DF['function'],\n",
    "    'phraseNumber': PHRASE_DF['number'], # position in phrase\n",
    "    'phraseType': PHRASE_DF['typ'],\n",
    "}\n",
    "\n",
    "CLAUSE_COLS = {\n",
    "    'clauseId': CLAUSE_DF['_id'],\n",
    "    'domain': CLAUSE_DF['domain'],\n",
    "    'kind': CLAUSE_DF['kind'],\n",
    "    'clauseNumber': CLAUSE_DF['number'], # position in sentence\n",
    "    'relation': CLAUSE_DF['rela'],\n",
    "    'clauseType': CLAUSE_DF['typ']\n",
    "}\n",
    "\n",
    "CLAUSE_ATOM_COLS = {\n",
    "    'clauseAtomId': CLAUSE_ATOM_DF['_id'],\n",
    "    'code': CLAUSE_ATOM_DF['code'],\n",
    "    'paragraph': CLAUSE_ATOM_DF['pargr'],\n",
    "    'tab': CLAUSE_ATOM_DF['tab'],\n",
    "    'clauseAtomType': CLAUSE_ATOM_DF['typ'],\n",
    "}\n",
    "\n",
    "STRONGS_COLS = {\n",
    "    'strongsId': TBESH_DF['strongs'],\n",
    "    'lexeme': TBESH_DF['lex'],\n",
    "    'transliterationSTEP': TBESH_DF['transliteration'],\n",
    "    'morphCode': TBESH_DF['morph'],\n",
    "    'glossSTEP': TBESH_DF['gloss'],\n",
    "    'definition': TBESH_DF['definition']\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "book_path = '../data_files/books.csv'\n",
    "lex_sentences_path = '../data_files/lex_sentences.csv'\n",
    "passages_path = '../data_files/passages.csv'\n",
    "WORD_TABLE = pd.DataFrame(WORD_COLS, index=None)\n",
    "LEX_TABLE = pd.DataFrame(LEX_COLS, index=None)\n",
    "PHRASE_TABLE = pd.DataFrame(PHRASE_COLS, index=None)\n",
    "CLAUSE_TABLE = pd.DataFrame(CLAUSE_COLS, index=None)\n",
    "CLAUSE_ATOM_TABLE = pd.DataFrame(CLAUSE_ATOM_COLS, index=None)\n",
    "LEX_SENTENCE_TABLE = pd.read_csv(lex_sentences_path, sep='\\t')\n",
    "STRONGS_TABLE = pd.DataFrame(STRONGS_COLS, index=None)\n",
    "PASSAGE_TABLE = pd.read_csv(passages_path, sep='\\t') # Generated in tf_passage_weights_v3\n",
    "BOOK_TABLE = pd.read_csv(book_path, sep=',', low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>wordId</th>\n",
       "      <th>book</th>\n",
       "      <th>chKJV</th>\n",
       "      <th>vsKJV</th>\n",
       "      <th>vsIdKJV</th>\n",
       "      <th>chBHS</th>\n",
       "      <th>vsBHS</th>\n",
       "      <th>vsIdBHS</th>\n",
       "      <th>person</th>\n",
       "      <th>gender</th>\n",
       "      <th>number</th>\n",
       "      <th>vTense</th>\n",
       "      <th>vStem</th>\n",
       "      <th>state</th>\n",
       "      <th>prsPerson</th>\n",
       "      <th>prsGender</th>\n",
       "      <th>prsNumber</th>\n",
       "      <th>suffix</th>\n",
       "      <th>text</th>\n",
       "      <th>textCons</th>\n",
       "      <th>trailer</th>\n",
       "      <th>transliteration</th>\n",
       "      <th>glossExt</th>\n",
       "      <th>glossBSB</th>\n",
       "      <th>sortBSB</th>\n",
       "      <th>strongsId</th>\n",
       "      <th>lexId</th>\n",
       "      <th>phraseId</th>\n",
       "      <th>clauseAtomId</th>\n",
       "      <th>clauseId</th>\n",
       "      <th>sentenceId</th>\n",
       "      <th>freqOcc</th>\n",
       "      <th>rankOcc</th>\n",
       "      <th>poetryMarker</th>\n",
       "      <th>parMarker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>16561.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>885.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>885.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>m</td>\n",
       "      <td>pl</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>a</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td>אֱלֹהִ֛ים</td>\n",
       "      <td>אלהים</td>\n",
       "      <td></td>\n",
       "      <td>ʾĕlōhîm</td>\n",
       "      <td>god [pl.]</td>\n",
       "      <td>of God</td>\n",
       "      <td>11455.0</td>\n",
       "      <td>H430</td>\n",
       "      <td>1437570</td>\n",
       "      <td>661699</td>\n",
       "      <td>519117</td>\n",
       "      <td>430891</td>\n",
       "      <td>1174875</td>\n",
       "      <td>1177</td>\n",
       "      <td>31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16562.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>885.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>885.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td>בַּ</td>\n",
       "      <td>ב</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ba</td>\n",
       "      <td>in</td>\n",
       "      <td>In</td>\n",
       "      <td>11452.0</td>\n",
       "      <td>H9003</td>\n",
       "      <td>1437567</td>\n",
       "      <td>661700</td>\n",
       "      <td>519117</td>\n",
       "      <td>430891</td>\n",
       "      <td>1174875</td>\n",
       "      <td>14194</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16563.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>885.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>885.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>m</td>\n",
       "      <td>sg</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>a</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td>חֲלֹ֖ום</td>\n",
       "      <td>חלום</td>\n",
       "      <td></td>\n",
       "      <td>ḥălôm</td>\n",
       "      <td>dream</td>\n",
       "      <td>that dream</td>\n",
       "      <td>11453.0</td>\n",
       "      <td>H2472</td>\n",
       "      <td>1438481</td>\n",
       "      <td>661700</td>\n",
       "      <td>519117</td>\n",
       "      <td>430891</td>\n",
       "      <td>1174875</td>\n",
       "      <td>35</td>\n",
       "      <td>1099</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16564.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>885.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>885.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>m</td>\n",
       "      <td>sg</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>a</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td>יַֽעֲקֹ֑ב</td>\n",
       "      <td>יעקב</td>\n",
       "      <td></td>\n",
       "      <td>yaʿăqōb</td>\n",
       "      <td>Jacob</td>\n",
       "      <td>‘Jacob!’</td>\n",
       "      <td>11458.0</td>\n",
       "      <td>H3290</td>\n",
       "      <td>1438668</td>\n",
       "      <td>661701</td>\n",
       "      <td>519118</td>\n",
       "      <td>430892</td>\n",
       "      <td>1174876</td>\n",
       "      <td>345</td>\n",
       "      <td>109</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16565.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>885.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>885.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td>וָ</td>\n",
       "      <td>ו</td>\n",
       "      <td>NaN</td>\n",
       "      <td>wā</td>\n",
       "      <td>and</td>\n",
       "      <td>And</td>\n",
       "      <td>11459.0</td>\n",
       "      <td>H9000</td>\n",
       "      <td>1437574</td>\n",
       "      <td>661702</td>\n",
       "      <td>519119</td>\n",
       "      <td>430893</td>\n",
       "      <td>1174877</td>\n",
       "      <td>50238</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16566.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>885.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>885.0</td>\n",
       "      <td>p1</td>\n",
       "      <td>unknown</td>\n",
       "      <td>sg</td>\n",
       "      <td>wayq</td>\n",
       "      <td>qal</td>\n",
       "      <td>NA</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td>unknown</td>\n",
       "      <td></td>\n",
       "      <td>אֹמַ֖ר</td>\n",
       "      <td>אמר</td>\n",
       "      <td></td>\n",
       "      <td>ʾōmar</td>\n",
       "      <td>[I]+ say</td>\n",
       "      <td>I replied,</td>\n",
       "      <td>11460.0</td>\n",
       "      <td>H559</td>\n",
       "      <td>1437586</td>\n",
       "      <td>661703</td>\n",
       "      <td>519119</td>\n",
       "      <td>430893</td>\n",
       "      <td>1174877</td>\n",
       "      <td>1911</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# display(HTML(WORD_TABLE.tail().to_html(index=False)))\n",
    "display(HTML(WORD_TABLE.loc[SKIP-3:SKIP+2].to_html(index=False)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing the Dataframe Into a SQL Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_map = {\n",
    "    'word': WORD_TABLE,\n",
    "    'lex': LEX_TABLE,\n",
    "    'phrase': PHRASE_TABLE,\n",
    "    'clause': CLAUSE_TABLE,\n",
    "    'clauseAtom': CLAUSE_ATOM_TABLE,\n",
    "    'lexSentence': LEX_SENTENCE_TABLE,\n",
    "    'strongs': STRONGS_TABLE,\n",
    "    'passage': PASSAGE_TABLE,\n",
    "    'book': BOOK_TABLE\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the data types for SQL.\n",
    "from sqlalchemy.types import Integer, Text, Float\n",
    "type_map = {\n",
    "    'word':\n",
    "        {'wordId': Integer(), 'book': Integer(), 'chKJV': Integer(), 'vsKJV': Integer(), 'vsIdKJV': Integer(), 'chBHS': Integer(), 'vsBHS': Integer(), 'vsIdBHS': Integer(), \n",
    "        'lang': Text(), 'speech': Text(), 'person': Text(), 'gender': Text(), 'number': Text(), 'vTense': Text(), 'vStem': Text(), 'state': Text(), \n",
    "        'prsPerson': Text(), 'prsGender': Text(), 'prsNumber': Text(), 'suffix': Text(), 'text': Text(), 'textCons':Text(), 'trailer': Text(), 'transliteration': Text(), \n",
    "        'glossExt': Text(), 'glossBSB': Text(), 'sortBSB': Float(), 'strongs': Text(), 'lexId': Integer(), 'phraseId': Integer(), 'clauseAtomId': Integer(), \n",
    "        'clauseId': Integer(), 'sentenceId': Integer(), 'freqOcc': Integer(), 'rankOcc': Integer(), 'poetryMarker': Text(), 'parMarker': Text()},\n",
    "    'lex':\n",
    "        {'lexId': Integer(), 'language': Text(), 'lexSpeech': Text(), 'nameType': Text(), 'lexSet': Text(), \n",
    "        'lexText': Text(), 'gloss': Text(), 'freqLex': Integer(), 'rankLex': Integer()},\n",
    "    'phrase':\n",
    "        {'phraseId': Integer(), 'determined': Text(), 'function': Text(), 'phraseNumber': Integer(), 'phraseType': Text()},\n",
    "    'clause':\n",
    "        {'clauseId': Integer(), 'domain':Text(), 'kind': Text(), 'clauseNumber': Integer(), 'relation': Text(), 'clauseType': Text()},\n",
    "    'clauseAtom':\n",
    "        {'clauseAtomId': Integer(), 'code': Integer(), 'paragraph': Text(), 'tab': Integer(), 'clauseAtomType': Text()},\n",
    "    'strongs':\n",
    "        {'strongsId': Text(), 'lexeme': Text(), 'transliterationSTEP': Text(), 'morphCode': Text(), 'glossSTEP': Text(), 'definition': Text()},\n",
    "    'lexSentence':\n",
    "        {'lexId': Integer(), 'sentenceId': Integer(), 'sentenceWeight': Float()},\n",
    "    'passage':\n",
    "        {'passageId': Integer(), 'wordCount': Integer(), 'weight': Float(), 'startVsNode': Integer(), 'endVsNode': Integer()},\n",
    "    'book':\n",
    "        {'bookId': Integer(), 'chapters': Integer(), 'abbrOSIS': Text(), 'abbrLEB': Text(), 'bookName': Text(), 'bookNameHeb': Text(), 'tanakhSort': Text()}\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "sql_file = '../data_files/bhsa4c_custom.db'\n",
    "# https://docs.sqlalchemy.org/en/14/core/engines.html\n",
    "# sqlite://<nohostname>/<path> where <path> is relative:\n",
    "con = create_engine(f\"sqlite:///{sql_file}\")\n",
    "# Convert the dataframes to tables in the database \n",
    "for table in table_map:\n",
    "    table_map[table].to_sql(\n",
    "        table, \n",
    "        con=con, \n",
    "        if_exists='replace', \n",
    "        index=False,\n",
    "        dtype=type_map[table]\n",
    "    )"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "7812ea015bdcee6f23a998adcdd2ef97c151c0c241b7b7070987d9313e41299d"
  },
  "kernelspec": {
   "display_name": "Python 3.9.5 64-bit",
   "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.9.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}