p_q_table_generation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from IPython.display import display, HTML\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_rows', None)\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_colwidth', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to merge columns with similar names\n",
    "def merge_columns(df):\n",
    "    # Group by the first word in the column name\n",
    "    grouped = df.groupby(by=lambda x: x.split(\" \")[0], axis=1)\n",
    "    \n",
    "    # Combine grouped columns\n",
    "    for name, group in grouped:\n",
    "        if len(group.columns) > 1:\n",
    "            df[name] = group.apply(lambda row: ' '.join(row.dropna().astype(str)), axis=1)\n",
    "\n",
    "    # Drop the original columns, keeping only the merged ones\n",
    "    for name, group in grouped:\n",
    "        if len(group.columns) > 1:\n",
    "            df.drop(columns=group.columns[1:], inplace=True)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "excel_path = 'CanIL Analysis of NT Conditionals by book220831.xlsx'\n",
    "\n",
    "# Read all sheet names in the Excel file\n",
    "xl = pd.ExcelFile(excel_path)\n",
    "sheet_names = xl.sheet_names\n",
    "\n",
    "# Skip the first sheet and last sheet, as it's just introductory material\n",
    "sheet_names = sheet_names[1:-1]\n",
    "\n",
    "# Read and concatenate all the sheets, making the column names lowercase\n",
    "df_list = []\n",
    "for sheet in sheet_names:\n",
    "    df = pd.read_excel(excel_path, sheet_name=sheet)\n",
    "    df.columns = map(str.lower, df.columns)  # Making column names lowercase\n",
    "    df = merge_columns(df)  # Merge similar columns\n",
    "    \n",
    "    # Need to process the 'reference' column to add the sheet name + ' ' to the beginning of each value\n",
    "    try:\n",
    "        df['reference'] = sheet + ' ' + df['reference'].astype(str)\n",
    "    except KeyError:\n",
    "        print('Sheet name', sheet, 'does not have a \"reference\" column.')\n",
    "    \n",
    "    df_list.append(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Concatenate all DataFrames in df_list\n",
    "concatenated_df = pd.concat(df_list, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>reference</th>\n",
       "      <th>scope of conditional (esv unless noted)</th>\n",
       "      <th>class</th>\n",
       "      <th>inv.</th>\n",
       "      <th>probability</th>\n",
       "      <th>time orientation</th>\n",
       "      <th>illocutionary force</th>\n",
       "      <th>english translations</th>\n",
       "      <th>notes</th>\n",
       "      <th>parallel passage(s)</th>\n",
       "      <th>unnamed: 10</th>\n",
       "      <th>unnamed:</th>\n",
       "      <th>parallel passages</th>\n",
       "      <th>unnamed: 9</th>\n",
       "      <th>scope of conditional (esv unless otherwise indicated)</th>\n",
       "      <th>scope of conditional (esv)</th>\n",
       "      <th>scope of conditional (esv unless stated otherwise)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MAT 4:3</td>\n",
       "      <td>p: (if you are the Son of God)\\nq: (command these stones to become bread)</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Factual</td>\n",
       "      <td>Present</td>\n",
       "      <td>Exhort</td>\n",
       "      <td>ESV, NASB, NRSV, NIV, NLT: \"if\"</td>\n",
       "      <td>P presents a fact that the temptor knew to be true. The devil certainly knows Jesus' identity. Hagner (1993) notes that this might as well be translated \"since\", as the devil is testing Jesus' obedience to the Father rather than questioning his identity. Q presents the subsequent command Jesus is being tempted to obey. The conditional as a whole amounts to a taunt or a challenge. Fong (2014: 30-31) discusses this as an example given by Young (1994) of a rhetorical conditional. That is, it is not being presented as a real condition, but the conditional is used for its logical connection in order to affect some other speech act, in this case \"manipulation\", according to Young. Fong, however, thinks it would be better classified as a \"challenge.\"</td>\n",
       "      <td>Luke 4:3</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MAT 4:6</td>\n",
       "      <td>p: (if you are the Son of God)\\nq: (throw yourself down)</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Factual</td>\n",
       "      <td>Present</td>\n",
       "      <td>Exhort</td>\n",
       "      <td>ESV, NASB, NRSV, NIV, NLT: \"if\"</td>\n",
       "      <td>As with 4:3, this conditional expresses no doubt about Jesus' identity, but is being used to manipulate, tempt, and challenge him.</td>\n",
       "      <td>Luke 4:9</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MAT 4:9</td>\n",
       "      <td>q:(All these I will give you)\\np: (if you will fall down and worship me)</td>\n",
       "      <td>3</td>\n",
       "      <td>x</td>\n",
       "      <td>Very Unlikely</td>\n",
       "      <td>Present</td>\n",
       "      <td>Promise / Exhort</td>\n",
       "      <td>ESV, NASB, NRSV, NIV, NLT: \"if\"</td>\n",
       "      <td>In 4:3 and 4:6 the exhortations (or temptations) are direct and occur in q; in this verse the exhortation (or temptation) is made indirectly in p. Though highly unlikely, and though it did not come to pass, this was still a (remote) possibility when the devil spoke it. It was a real option for Jesus, though he did not take it. Unlike the two previous temptations, this one is a third class, using εαν with the subjunctive because ( according to Hagner 1993) it involves an actual unmet condition.</td>\n",
       "      <td>Luke 4:7</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MAT 5:13a</td>\n",
       "      <td>p: (if salt has lost its taste)\\nq: (how shall its saltiness be restored?)</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unlikely</td>\n",
       "      <td>Gnomic</td>\n",
       "      <td>Assert</td>\n",
       "      <td>ESV, NASB, NRSV, NIV, NLT: \"if\"</td>\n",
       "      <td>This conditional must be read in its context of describing discipleship. The connection between salt and discipleship is that, as Marshall (1978) puts it, \"a false form of discipleship may look like salt, but the gradual process of leeching leaves only a zestless pile of waste.\"  Nolland (2005) claims that discussions of how salt of the day may have lost its flavour are pointless, as the point being made is that such a thing would be \"bizarre and unnatural.\" It would be terrible for salt to lose saltiness because it is itself the thing that adds flavour to tasteless food.</td>\n",
       "      <td>Mark 9:50; Luke 14:34</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MAT 5:20</td>\n",
       "      <td>p: (unless your righteousness exceeds that of the scribes and Pharisees)\\nq: (you will never enter the kingdom of heaven)</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Very Unlikely</td>\n",
       "      <td>Gnomic</td>\n",
       "      <td>Warn</td>\n",
       "      <td>ESV, NASB, NRSV, NIV, NLT: \"unless\"</td>\n",
       "      <td>Greek: εαν μη. Nolland, Hagner, and others agree that the righteousness Jesus discusses here cannot mean that he is actually commanding the disciples to live by the letter of the law as the Pharisees do (and to a greater extent, which is nigh impossible). \\nParaphrase (to avoid multiple negatives in languages without 'unless' or 'except'): For I say to you that only if you are more righteous than the scribes and Pharisees, will you enter the kingdom of heaven.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   reference  \\\n",
       "0    MAT 4:3   \n",
       "1    MAT 4:6   \n",
       "2    MAT 4:9   \n",
       "3  MAT 5:13a   \n",
       "4   MAT 5:20   \n",
       "\n",
       "                                                                                     scope of conditional (esv unless noted)  \\\n",
       "0                                                  p: (if you are the Son of God)\\nq: (command these stones to become bread)   \n",
       "1                                                                   p: (if you are the Son of God)\\nq: (throw yourself down)   \n",
       "2                                                   q:(All these I will give you)\\np: (if you will fall down and worship me)   \n",
       "3                                                 p: (if salt has lost its taste)\\nq: (how shall its saltiness be restored?)   \n",
       "4  p: (unless your righteousness exceeds that of the scribes and Pharisees)\\nq: (you will never enter the kingdom of heaven)   \n",
       "\n",
       "  class inv.    probability time orientation illocutionary force  \\\n",
       "0     1  NaN        Factual          Present              Exhort   \n",
       "1     1  NaN        Factual          Present              Exhort   \n",
       "2     3    x  Very Unlikely          Present    Promise / Exhort   \n",
       "3     3  NaN       Unlikely           Gnomic              Assert   \n",
       "4     3  NaN  Very Unlikely           Gnomic                Warn   \n",
       "\n",
       "                  english translations  \\\n",
       "0      ESV, NASB, NRSV, NIV, NLT: \"if\"   \n",
       "1      ESV, NASB, NRSV, NIV, NLT: \"if\"   \n",
       "2      ESV, NASB, NRSV, NIV, NLT: \"if\"   \n",
       "3      ESV, NASB, NRSV, NIV, NLT: \"if\"   \n",
       "4  ESV, NASB, NRSV, NIV, NLT: \"unless\"   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               notes  \\\n",
       "0  P presents a fact that the temptor knew to be true. The devil certainly knows Jesus' identity. Hagner (1993) notes that this might as well be translated \"since\", as the devil is testing Jesus' obedience to the Father rather than questioning his identity. Q presents the subsequent command Jesus is being tempted to obey. The conditional as a whole amounts to a taunt or a challenge. Fong (2014: 30-31) discusses this as an example given by Young (1994) of a rhetorical conditional. That is, it is not being presented as a real condition, but the conditional is used for its logical connection in order to affect some other speech act, in this case \"manipulation\", according to Young. Fong, however, thinks it would be better classified as a \"challenge.\"   \n",
       "1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 As with 4:3, this conditional expresses no doubt about Jesus' identity, but is being used to manipulate, tempt, and challenge him.   \n",
       "2                                                                                                                                                                                                                                                                 In 4:3 and 4:6 the exhortations (or temptations) are direct and occur in q; in this verse the exhortation (or temptation) is made indirectly in p. Though highly unlikely, and though it did not come to pass, this was still a (remote) possibility when the devil spoke it. It was a real option for Jesus, though he did not take it. Unlike the two previous temptations, this one is a third class, using εαν with the subjunctive because ( according to Hagner 1993) it involves an actual unmet condition.   \n",
       "3                                                                                                                                                                                 This conditional must be read in its context of describing discipleship. The connection between salt and discipleship is that, as Marshall (1978) puts it, \"a false form of discipleship may look like salt, but the gradual process of leeching leaves only a zestless pile of waste.\"  Nolland (2005) claims that discussions of how salt of the day may have lost its flavour are pointless, as the point being made is that such a thing would be \"bizarre and unnatural.\" It would be terrible for salt to lose saltiness because it is itself the thing that adds flavour to tasteless food.   \n",
       "4                                                                                                                                                                                                                                                                                                   Greek: εαν μη. Nolland, Hagner, and others agree that the righteousness Jesus discusses here cannot mean that he is actually commanding the disciples to live by the letter of the law as the Pharisees do (and to a greater extent, which is nigh impossible). \\nParaphrase (to avoid multiple negatives in languages without 'unless' or 'except'): For I say to you that only if you are more righteous than the scribes and Pharisees, will you enter the kingdom of heaven.   \n",
       "\n",
       "     parallel passage(s)  unnamed: 10 unnamed: parallel passages  unnamed: 9  \\\n",
       "0               Luke 4:3          NaN                        NaN         NaN   \n",
       "1               Luke 4:9          NaN                        NaN         NaN   \n",
       "2               Luke 4:7          NaN                        NaN         NaN   \n",
       "3  Mark 9:50; Luke 14:34          NaN                        NaN         NaN   \n",
       "4                    NaN          NaN                        NaN         NaN   \n",
       "\n",
       "  scope of conditional (esv unless otherwise indicated)  \\\n",
       "0                                                   NaN   \n",
       "1                                                   NaN   \n",
       "2                                                   NaN   \n",
       "3                                                   NaN   \n",
       "4                                                   NaN   \n",
       "\n",
       "  scope of conditional (esv)  \\\n",
       "0                        NaN   \n",
       "1                        NaN   \n",
       "2                        NaN   \n",
       "3                        NaN   \n",
       "4                        NaN   \n",
       "\n",
       "  scope of conditional (esv unless stated otherwise)  \n",
       "0                                                NaN  \n",
       "1                                                NaN  \n",
       "2                                                NaN  \n",
       "3                                                NaN  \n",
       "4                                                NaN  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concatenated_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['{\"vref\": \"MAT 4:3\", \"data\": {\"protasis\": [[\"n40004003007\", \"If\", \"Εἰ\"], [\"n40004003008\", \"Son\", \"υἱὸς\"], [\"n40004003009\", \"You are\", \"εἶ\"], [\"n40004003010\", \"-\", \"τοῦ\"], [\"n40004003011\", \"of God\", \"θεοῦ\"]], \"apodosis\": [[\"n40004003012\", \"speak\", \"εἰπὲ\"], [\"n40004003013\", \"that\", \"ἵνα\"], [\"n40004003014\", \"the\", \"οἱ\"], [\"n40004003015\", \"stones\", \"λίθοι\"], [\"n40004003016\", \"these\", \"οὗτοι\"], [\"n40004003017\", \"loaves of bread\", \"ἄρτοι\"], [\"n40004003018\", \"might become\", \"γένωνται\"]]}}\\n',\n",
       " '{\"vref\": \"MAT 4:6\", \"data\": {\"protasis\": [[\"n40004006004\", \"If\", \"Εἰ\"], [\"n40004006005\", \"Son\", \"υἱὸς\"], [\"n40004006006\", \"You are\", \"εἶ\"], [\"n40004006007\", \"-\", \"τοῦ\"], [\"n40004006008\", \"of God\", \"θεοῦ\"]], \"apodosis\": [[\"n40004006009\", \"throw\", \"βάλε\"], [\"n40004006010\", \"Yourself\", \"σεαυτὸν\"], [\"n40004006011\", \"down\", \"κάτω\"]]}}\\n',\n",
       " '{\"vref\": \"MAT 4:9\", \"data\": {\"protasis\": [[\"n40004009008\", \"if\", \"ἐὰν\"], [\"n40004009009\", \"falling down\", \"πεσὼν\"], [\"n40004009010\", \"You will worship\", \"προσκυνήσῃς\"], [\"n40004009011\", \"me\", \"μοι\"]], \"apodosis\": [[\"n40004009004\", \"These things\", \"Ταῦτά\"], [\"n40004009005\", \"to You\", \"σοι\"], [\"n40004009006\", \"all\", \"πάντα\"], [\"n40004009007\", \"will I give\", \"δώσω\"]]}}\\n']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('output_Nov_6_ryder.jsonl', 'r', encoding='utf-8') as file:\n",
    "    gloss_data = file.readlines()\n",
    "    \n",
    "gloss_data[:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Parse JSON strings into dictionaries\n",
    "dict_list = [json.loads(json_str) for json_str in gloss_data]\n",
    "\n",
    "# Flatten the dictionaries and create DataFrame\n",
    "df = pd.json_normalize(dict_list)\n",
    "\n",
    "# Rename the columns\n",
    "df = df.rename(columns={'data.protasis': 'protasis', 'data.apodosis': 'apodosis'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>vref</th>\n",
       "      <th>protasis</th>\n",
       "      <th>apodosis</th>\n",
       "      <th>data.error</th>\n",
       "      <th>error</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MAT 4:3</td>\n",
       "      <td>[[n40004003007, If, Εἰ], [n40004003008, Son, υἱὸς], [n40004003009, You are, εἶ], [n40004003010, -, τοῦ], [n40004003011, of God, θεοῦ]]</td>\n",
       "      <td>[[n40004003012, speak, εἰπὲ], [n40004003013, that, ἵνα], [n40004003014, the, οἱ], [n40004003015, stones, λίθοι], [n40004003016, these, οὗτοι], [n40004003017, loaves of bread, ἄρτοι], [n40004003018, might become, γένωνται]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MAT 4:6</td>\n",
       "      <td>[[n40004006004, If, Εἰ], [n40004006005, Son, υἱὸς], [n40004006006, You are, εἶ], [n40004006007, -, τοῦ], [n40004006008, of God, θεοῦ]]</td>\n",
       "      <td>[[n40004006009, throw, βάλε], [n40004006010, Yourself, σεαυτὸν], [n40004006011, down, κάτω]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MAT 4:9</td>\n",
       "      <td>[[n40004009008, if, ἐὰν], [n40004009009, falling down, πεσὼν], [n40004009010, You will worship, προσκυνήσῃς], [n40004009011, me, μοι]]</td>\n",
       "      <td>[[n40004009004, These things, Ταῦτά], [n40004009005, to You, σοι], [n40004009006, all, πάντα], [n40004009007, will I give, δώσω]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MAT 5:13</td>\n",
       "      <td>[[n40005013007, if, ἐὰν], [n40005013010, the, τὸ], [n40005013011, becomes tasteless, μωρανθῇ]]</td>\n",
       "      <td>[[n40005013019, if, εἰ], [n40005013020, not, μὴ], [n40005013021, having been cast, βληθὲν], [n40005013022, out, ἔξω], [n40005013023, to be trampled upon, καταπατεῖσθαι]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MAT 5:20</td>\n",
       "      <td>[[n40005020004, that, ὅτι], [n40005020005, if, ἐὰν], [n40005020006, not, μὴ], [n40005020007, shall abound, περισσεύσῃ], [n40005020008, your, ὑμῶν], [n40005020009, -, ἡ], [n40005020010, righteousness, δικαιοσύνη], [n40005020011, above [that], πλεῖον], [n40005020012, of the, τῶν], [n40005020013, scribes, γραμματέων], [n40005020014, and, καὶ], [n40005020015, Pharisees, Φαρισαίων]]</td>\n",
       "      <td>[[n40005020016, no, οὐ], [n40005020017, not, μὴ], [n40005020018, shall you enter, εἰσέλθητε], [n40005020019, into, εἰς], [n40005020020, the, τὴν], [n40005020021, kingdom, βασιλείαν], [n40005020022, of the, τῶν], [n40005020023, heavens, οὐρανῶν]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       vref  \\\n",
       "0   MAT 4:3   \n",
       "1   MAT 4:6   \n",
       "2   MAT 4:9   \n",
       "3  MAT 5:13   \n",
       "4  MAT 5:20   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                       protasis  \\\n",
       "0                                                                                                                                                                                                                                                        [[n40004003007, If, Εἰ], [n40004003008, Son, υἱὸς], [n40004003009, You are, εἶ], [n40004003010, -, τοῦ], [n40004003011, of God, θεοῦ]]   \n",
       "1                                                                                                                                                                                                                                                        [[n40004006004, If, Εἰ], [n40004006005, Son, υἱὸς], [n40004006006, You are, εἶ], [n40004006007, -, τοῦ], [n40004006008, of God, θεοῦ]]   \n",
       "2                                                                                                                                                                                                                                                        [[n40004009008, if, ἐὰν], [n40004009009, falling down, πεσὼν], [n40004009010, You will worship, προσκυνήσῃς], [n40004009011, me, μοι]]   \n",
       "3                                                                                                                                                                                                                                                                                                [[n40005013007, if, ἐὰν], [n40005013010, the, τὸ], [n40005013011, becomes tasteless, μωρανθῇ]]   \n",
       "4  [[n40005020004, that, ὅτι], [n40005020005, if, ἐὰν], [n40005020006, not, μὴ], [n40005020007, shall abound, περισσεύσῃ], [n40005020008, your, ὑμῶν], [n40005020009, -, ἡ], [n40005020010, righteousness, δικαιοσύνη], [n40005020011, above [that], πλεῖον], [n40005020012, of the, τῶν], [n40005020013, scribes, γραμματέων], [n40005020014, and, καὶ], [n40005020015, Pharisees, Φαρισαίων]]   \n",
       "\n",
       "                                                                                                                                                                                                                                                apodosis  \\\n",
       "0                         [[n40004003012, speak, εἰπὲ], [n40004003013, that, ἵνα], [n40004003014, the, οἱ], [n40004003015, stones, λίθοι], [n40004003016, these, οὗτοι], [n40004003017, loaves of bread, ἄρτοι], [n40004003018, might become, γένωνται]]   \n",
       "1                                                                                                                                                           [[n40004006009, throw, βάλε], [n40004006010, Yourself, σεαυτὸν], [n40004006011, down, κάτω]]   \n",
       "2                                                                                                                      [[n40004009004, These things, Ταῦτά], [n40004009005, to You, σοι], [n40004009006, all, πάντα], [n40004009007, will I give, δώσω]]   \n",
       "3                                                                              [[n40005013019, if, εἰ], [n40005013020, not, μὴ], [n40005013021, having been cast, βληθὲν], [n40005013022, out, ἔξω], [n40005013023, to be trampled upon, καταπατεῖσθαι]]   \n",
       "4  [[n40005020016, no, οὐ], [n40005020017, not, μὴ], [n40005020018, shall you enter, εἰσέλθητε], [n40005020019, into, εἰς], [n40005020020, the, τὴν], [n40005020021, kingdom, βασιλείαν], [n40005020022, of the, τῶν], [n40005020023, heavens, οὐρανῶν]]   \n",
       "\n",
       "  data.error error  \n",
       "0        NaN   NaN  \n",
       "1        NaN   NaN  \n",
       "2        NaN   NaN  \n",
       "3        NaN   NaN  \n",
       "4        NaN   NaN  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge df and concatenated_df on 'reference'\n",
    "merged_df = pd.merge(df, concatenated_df[['reference', 'scope of conditional (esv unless noted)']], left_on='vref', right_on='reference', how='left')\n",
    "\n",
    "# Drop 'data.error', 'error', and 'reference' columns\n",
    "merged_df = merged_df.drop(columns=['data.error', 'error', 'reference'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>vref</th>\n",
       "      <th>protasis</th>\n",
       "      <th>apodosis</th>\n",
       "      <th>p_q</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MAT 4:3</td>\n",
       "      <td>[[n40004003007, If, Εἰ], [n40004003008, Son, υἱὸς], [n40004003009, You are, εἶ], [n40004003010, -, τοῦ], [n40004003011, of God, θεοῦ]]</td>\n",
       "      <td>[[n40004003012, speak, εἰπὲ], [n40004003013, that, ἵνα], [n40004003014, the, οἱ], [n40004003015, stones, λίθοι], [n40004003016, these, οὗτοι], [n40004003017, loaves of bread, ἄρτοι], [n40004003018, might become, γένωνται]]</td>\n",
       "      <td>p: (if you are the Son of God)\\nq: (command these stones to become bread)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MAT 4:6</td>\n",
       "      <td>[[n40004006004, If, Εἰ], [n40004006005, Son, υἱὸς], [n40004006006, You are, εἶ], [n40004006007, -, τοῦ], [n40004006008, of God, θεοῦ]]</td>\n",
       "      <td>[[n40004006009, throw, βάλε], [n40004006010, Yourself, σεαυτὸν], [n40004006011, down, κάτω]]</td>\n",
       "      <td>p: (if you are the Son of God)\\nq: (throw yourself down)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MAT 4:9</td>\n",
       "      <td>[[n40004009008, if, ἐὰν], [n40004009009, falling down, πεσὼν], [n40004009010, You will worship, προσκυνήσῃς], [n40004009011, me, μοι]]</td>\n",
       "      <td>[[n40004009004, These things, Ταῦτά], [n40004009005, to You, σοι], [n40004009006, all, πάντα], [n40004009007, will I give, δώσω]]</td>\n",
       "      <td>q:(All these I will give you)\\np: (if you will fall down and worship me)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MAT 5:13</td>\n",
       "      <td>[[n40005013007, if, ἐὰν], [n40005013010, the, τὸ], [n40005013011, becomes tasteless, μωρανθῇ]]</td>\n",
       "      <td>[[n40005013019, if, εἰ], [n40005013020, not, μὴ], [n40005013021, having been cast, βληθὲν], [n40005013022, out, ἔξω], [n40005013023, to be trampled upon, καταπατεῖσθαι]]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MAT 5:20</td>\n",
       "      <td>[[n40005020004, that, ὅτι], [n40005020005, if, ἐὰν], [n40005020006, not, μὴ], [n40005020007, shall abound, περισσεύσῃ], [n40005020008, your, ὑμῶν], [n40005020009, -, ἡ], [n40005020010, righteousness, δικαιοσύνη], [n40005020011, above [that], πλεῖον], [n40005020012, of the, τῶν], [n40005020013, scribes, γραμματέων], [n40005020014, and, καὶ], [n40005020015, Pharisees, Φαρισαίων]]</td>\n",
       "      <td>[[n40005020016, no, οὐ], [n40005020017, not, μὴ], [n40005020018, shall you enter, εἰσέλθητε], [n40005020019, into, εἰς], [n40005020020, the, τὴν], [n40005020021, kingdom, βασιλείαν], [n40005020022, of the, τῶν], [n40005020023, heavens, οὐρανῶν]]</td>\n",
       "      <td>p: (unless your righteousness exceeds that of the scribes and Pharisees)\\nq: (you will never enter the kingdom of heaven)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       vref  \\\n",
       "0   MAT 4:3   \n",
       "1   MAT 4:6   \n",
       "2   MAT 4:9   \n",
       "3  MAT 5:13   \n",
       "4  MAT 5:20   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                       protasis  \\\n",
       "0                                                                                                                                                                                                                                                        [[n40004003007, If, Εἰ], [n40004003008, Son, υἱὸς], [n40004003009, You are, εἶ], [n40004003010, -, τοῦ], [n40004003011, of God, θεοῦ]]   \n",
       "1                                                                                                                                                                                                                                                        [[n40004006004, If, Εἰ], [n40004006005, Son, υἱὸς], [n40004006006, You are, εἶ], [n40004006007, -, τοῦ], [n40004006008, of God, θεοῦ]]   \n",
       "2                                                                                                                                                                                                                                                        [[n40004009008, if, ἐὰν], [n40004009009, falling down, πεσὼν], [n40004009010, You will worship, προσκυνήσῃς], [n40004009011, me, μοι]]   \n",
       "3                                                                                                                                                                                                                                                                                                [[n40005013007, if, ἐὰν], [n40005013010, the, τὸ], [n40005013011, becomes tasteless, μωρανθῇ]]   \n",
       "4  [[n40005020004, that, ὅτι], [n40005020005, if, ἐὰν], [n40005020006, not, μὴ], [n40005020007, shall abound, περισσεύσῃ], [n40005020008, your, ὑμῶν], [n40005020009, -, ἡ], [n40005020010, righteousness, δικαιοσύνη], [n40005020011, above [that], πλεῖον], [n40005020012, of the, τῶν], [n40005020013, scribes, γραμματέων], [n40005020014, and, καὶ], [n40005020015, Pharisees, Φαρισαίων]]   \n",
       "\n",
       "                                                                                                                                                                                                                                                apodosis  \\\n",
       "0                         [[n40004003012, speak, εἰπὲ], [n40004003013, that, ἵνα], [n40004003014, the, οἱ], [n40004003015, stones, λίθοι], [n40004003016, these, οὗτοι], [n40004003017, loaves of bread, ἄρτοι], [n40004003018, might become, γένωνται]]   \n",
       "1                                                                                                                                                           [[n40004006009, throw, βάλε], [n40004006010, Yourself, σεαυτὸν], [n40004006011, down, κάτω]]   \n",
       "2                                                                                                                      [[n40004009004, These things, Ταῦτά], [n40004009005, to You, σοι], [n40004009006, all, πάντα], [n40004009007, will I give, δώσω]]   \n",
       "3                                                                              [[n40005013019, if, εἰ], [n40005013020, not, μὴ], [n40005013021, having been cast, βληθὲν], [n40005013022, out, ἔξω], [n40005013023, to be trampled upon, καταπατεῖσθαι]]   \n",
       "4  [[n40005020016, no, οὐ], [n40005020017, not, μὴ], [n40005020018, shall you enter, εἰσέλθητε], [n40005020019, into, εἰς], [n40005020020, the, τὴν], [n40005020021, kingdom, βασιλείαν], [n40005020022, of the, τῶν], [n40005020023, heavens, οὐρανῶν]]   \n",
       "\n",
       "                                                                                                                         p_q  \n",
       "0                                                  p: (if you are the Son of God)\\nq: (command these stones to become bread)  \n",
       "1                                                                   p: (if you are the Son of God)\\nq: (throw yourself down)  \n",
       "2                                                   q:(All these I will give you)\\np: (if you will fall down and worship me)  \n",
       "3                                                                                                                        NaN  \n",
       "4  p: (unless your righteousness exceeds that of the scribes and Pharisees)\\nq: (you will never enter the kingdom of heaven)  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df = merged_df.rename(columns={'scope of conditional (esv unless noted)': 'p_q'})\n",
    "merged_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use regular expressions to extract 'p' and 'q' data\n",
    "merged_df['p'] = merged_df['p_q'].str.extract(r'p:\\s*\\((.*?)\\)', expand=False)\n",
    "merged_df['q'] = merged_df['p_q'].str.extract(r'q:\\s*\\((.*?)\\)', expand=False)\n",
    "\n",
    "# Fill NaN values in both 'p' and 'q' columns with NaN\n",
    "merged_df['p'].fillna(\"NaN\", inplace=True)\n",
    "merged_df['q'].fillna(\"NaN\", inplace=True)\n",
    "\n",
    "# Drop the original column\n",
    "merged_df.drop('p_q', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df = merged_df.rename(columns={'protasis': 'protasis gloss'})\n",
    "merged_df = merged_df.rename(columns={'apodosis': 'apodosis gloss'})\n",
    "merged_df = merged_df.rename(columns={'p': 'protasis'})\n",
    "merged_df = merged_df.rename(columns={'q': 'apodosis'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>protasis</th>\n",
       "      <th>protasis gloss</th>\n",
       "      <th>apodosis</th>\n",
       "      <th>apodosis gloss</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>if you are the Son of God</td>\n",
       "      <td>[[n40004003007, If, Εἰ], [n40004003008, Son, υἱὸς], [n40004003009, You are, εἶ], [n40004003010, -, τοῦ], [n40004003011, of God, θεοῦ]]</td>\n",
       "      <td>command these stones to become bread</td>\n",
       "      <td>[[n40004003012, speak, εἰπὲ], [n40004003013, that, ἵνα], [n40004003014, the, οἱ], [n40004003015, stones, λίθοι], [n40004003016, these, οὗτοι], [n40004003017, loaves of bread, ἄρτοι], [n40004003018, might become, γένωνται]]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>if you are the Son of God</td>\n",
       "      <td>[[n40004006004, If, Εἰ], [n40004006005, Son, υἱὸς], [n40004006006, You are, εἶ], [n40004006007, -, τοῦ], [n40004006008, of God, θεοῦ]]</td>\n",
       "      <td>throw yourself down</td>\n",
       "      <td>[[n40004006009, throw, βάλε], [n40004006010, Yourself, σεαυτὸν], [n40004006011, down, κάτω]]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>if you will fall down and worship me</td>\n",
       "      <td>[[n40004009008, if, ἐὰν], [n40004009009, falling down, πεσὼν], [n40004009010, You will worship, προσκυνήσῃς], [n40004009011, me, μοι]]</td>\n",
       "      <td>All these I will give you</td>\n",
       "      <td>[[n40004009004, These things, Ταῦτά], [n40004009005, to You, σοι], [n40004009006, all, πάντα], [n40004009007, will I give, δώσω]]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>[[n40005013007, if, ἐὰν], [n40005013010, the, τὸ], [n40005013011, becomes tasteless, μωρανθῇ]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[[n40005013019, if, εἰ], [n40005013020, not, μὴ], [n40005013021, having been cast, βληθὲν], [n40005013022, out, ἔξω], [n40005013023, to be trampled upon, καταπατεῖσθαι]]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>unless your righteousness exceeds that of the scribes and Pharisees</td>\n",
       "      <td>[[n40005020004, that, ὅτι], [n40005020005, if, ἐὰν], [n40005020006, not, μὴ], [n40005020007, shall abound, περισσεύσῃ], [n40005020008, your, ὑμῶν], [n40005020009, -, ἡ], [n40005020010, righteousness, δικαιοσύνη], [n40005020011, above [that], πλεῖον], [n40005020012, of the, τῶν], [n40005020013, scribes, γραμματέων], [n40005020014, and, καὶ], [n40005020015, Pharisees, Φαρισαίων]]</td>\n",
       "      <td>you will never enter the kingdom of heaven</td>\n",
       "      <td>[[n40005020016, no, οὐ], [n40005020017, not, μὴ], [n40005020018, shall you enter, εἰσέλθητε], [n40005020019, into, εἰς], [n40005020020, the, τὴν], [n40005020021, kingdom, βασιλείαν], [n40005020022, of the, τῶν], [n40005020023, heavens, οὐρανῶν]]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                              protasis  \\\n",
       "0                                            if you are the Son of God   \n",
       "1                                            if you are the Son of God   \n",
       "2                                 if you will fall down and worship me   \n",
       "3                                                                  NaN   \n",
       "4  unless your righteousness exceeds that of the scribes and Pharisees   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                 protasis gloss  \\\n",
       "0                                                                                                                                                                                                                                                        [[n40004003007, If, Εἰ], [n40004003008, Son, υἱὸς], [n40004003009, You are, εἶ], [n40004003010, -, τοῦ], [n40004003011, of God, θεοῦ]]   \n",
       "1                                                                                                                                                                                                                                                        [[n40004006004, If, Εἰ], [n40004006005, Son, υἱὸς], [n40004006006, You are, εἶ], [n40004006007, -, τοῦ], [n40004006008, of God, θεοῦ]]   \n",
       "2                                                                                                                                                                                                                                                        [[n40004009008, if, ἐὰν], [n40004009009, falling down, πεσὼν], [n40004009010, You will worship, προσκυνήσῃς], [n40004009011, me, μοι]]   \n",
       "3                                                                                                                                                                                                                                                                                                [[n40005013007, if, ἐὰν], [n40005013010, the, τὸ], [n40005013011, becomes tasteless, μωρανθῇ]]   \n",
       "4  [[n40005020004, that, ὅτι], [n40005020005, if, ἐὰν], [n40005020006, not, μὴ], [n40005020007, shall abound, περισσεύσῃ], [n40005020008, your, ὑμῶν], [n40005020009, -, ἡ], [n40005020010, righteousness, δικαιοσύνη], [n40005020011, above [that], πλεῖον], [n40005020012, of the, τῶν], [n40005020013, scribes, γραμματέων], [n40005020014, and, καὶ], [n40005020015, Pharisees, Φαρισαίων]]   \n",
       "\n",
       "                                     apodosis  \\\n",
       "0        command these stones to become bread   \n",
       "1                         throw yourself down   \n",
       "2                   All these I will give you   \n",
       "3                                         NaN   \n",
       "4  you will never enter the kingdom of heaven   \n",
       "\n",
       "                                                                                                                                                                                                                                          apodosis gloss  \n",
       "0                         [[n40004003012, speak, εἰπὲ], [n40004003013, that, ἵνα], [n40004003014, the, οἱ], [n40004003015, stones, λίθοι], [n40004003016, these, οὗτοι], [n40004003017, loaves of bread, ἄρτοι], [n40004003018, might become, γένωνται]]  \n",
       "1                                                                                                                                                           [[n40004006009, throw, βάλε], [n40004006010, Yourself, σεαυτὸν], [n40004006011, down, κάτω]]  \n",
       "2                                                                                                                      [[n40004009004, These things, Ταῦτά], [n40004009005, to You, σοι], [n40004009006, all, πάντα], [n40004009007, will I give, δώσω]]  \n",
       "3                                                                              [[n40005013019, if, εἰ], [n40005013020, not, μὴ], [n40005013021, having been cast, βληθὲν], [n40005013022, out, ἔξω], [n40005013023, to be trampled upon, καταπατεῖσθαι]]  \n",
       "4  [[n40005020016, no, οὐ], [n40005020017, not, μὴ], [n40005020018, shall you enter, εἰσέλθητε], [n40005020019, into, εἰς], [n40005020020, the, τὴν], [n40005020021, kingdom, βασιλείαν], [n40005020022, of the, τῶν], [n40005020023, heavens, οὐρανῶν]]  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df = merged_df[['protasis', 'protasis gloss', 'apodosis', 'apodosis gloss']]\n",
    "merged_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert DataFrame to HTML\n",
    "html_table = df.to_html(index=False)\n",
    "with open('protasis_apodosis_table.html', 'a', encoding='utf-8') as file:\n",
    "    file.write(html_table)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.11"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}