jupyter_notebooks/3_further_process_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b9ba013e",
   "metadata": {},
   "source": [
    "# SAMPLE/DEMO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ad9238ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "da2426bc",
   "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>entry_#</th>\n",
       "      <th>SMILES</th>\n",
       "      <th>pka_type</th>\n",
       "      <th>pka_value</th>\n",
       "      <th>T</th>\n",
       "      <th>remarks</th>\n",
       "      <th>method</th>\n",
       "      <th>assessment</th>\n",
       "      <th>ref</th>\n",
       "      <th>ref_remarks</th>\n",
       "      <th>entry_remarks</th>\n",
       "      <th>original_IUPAC_names</th>\n",
       "      <th>original_IUPAC_nicknames</th>\n",
       "      <th>name_contributors</th>\n",
       "      <th>num_name_contributors</th>\n",
       "      <th>source</th>\n",
       "      <th>type_of_entry</th>\n",
       "      <th>unique_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004</td>\n",
       "      <td>CO</td>\n",
       "      <td>pKa</td>\n",
       "      <td>15.50</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C3</td>\n",
       "      <td>Uncert.</td>\n",
       "      <td>B8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methanol</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004</td>\n",
       "      <td>CO</td>\n",
       "      <td>pKa</td>\n",
       "      <td>15.09</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KIN</td>\n",
       "      <td>Uncert.</td>\n",
       "      <td>M126</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methanol</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2005</td>\n",
       "      <td>COO</td>\n",
       "      <td>pKa</td>\n",
       "      <td>11.50</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O5</td>\n",
       "      <td>Uncert.</td>\n",
       "      <td>E27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methyl hydroperoxide</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2006</td>\n",
       "      <td>CS</td>\n",
       "      <td>pKa</td>\n",
       "      <td>10.33</td>\n",
       "      <td>25.0</td>\n",
       "      <td>1% ethanol, gas solubility method</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Uncert.</td>\n",
       "      <td>K57</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methanethiol</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pKa</td>\n",
       "      <td>0.14</td>\n",
       "      <td>20.0</td>\n",
       "      <td>In aqueous HClO4 Ho scale</td>\n",
       "      <td>O6</td>\n",
       "      <td>Uncert.</td>\n",
       "      <td>T58a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived ...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pKa</td>\n",
       "      <td>0.06</td>\n",
       "      <td>25.5</td>\n",
       "      <td>Mixed constant</td>\n",
       "      <td>O5</td>\n",
       "      <td>Uncert.</td>\n",
       "      <td>H5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived ...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pKa</td>\n",
       "      <td>0.05</td>\n",
       "      <td>9.6</td>\n",
       "      <td>Mixed constant</td>\n",
       "      <td>O5</td>\n",
       "      <td>Uncert.</td>\n",
       "      <td>H5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived ...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pKa</td>\n",
       "      <td>0.23</td>\n",
       "      <td>5.0</td>\n",
       "      <td>In aqueous HCl Ho scale</td>\n",
       "      <td>O6</td>\n",
       "      <td>Uncert.</td>\n",
       "      <td>N39, S82</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived ...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pKa</td>\n",
       "      <td>0.17</td>\n",
       "      <td>20.0</td>\n",
       "      <td>In aqueous HCl Ho scale</td>\n",
       "      <td>O6</td>\n",
       "      <td>Uncert.</td>\n",
       "      <td>N39, S82</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived ...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pKa</td>\n",
       "      <td>0.11</td>\n",
       "      <td>40.0</td>\n",
       "      <td>In aqueous HCl Ho scale</td>\n",
       "      <td>O6</td>\n",
       "      <td>Uncert.</td>\n",
       "      <td>N39, S82</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived ...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pKa</td>\n",
       "      <td>3.63</td>\n",
       "      <td>20.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approx.</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pKa</td>\n",
       "      <td>3.72</td>\n",
       "      <td>5.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approx.</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pKa</td>\n",
       "      <td>3.60</td>\n",
       "      <td>20.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approx.</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pKa</td>\n",
       "      <td>3.51</td>\n",
       "      <td>40.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approx.</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pKa</td>\n",
       "      <td>3.43</td>\n",
       "      <td>60.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approx.</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pKa</td>\n",
       "      <td>3.57</td>\n",
       "      <td>25.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approx.</td>\n",
       "      <td>A7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2009</td>\n",
       "      <td>C[N+](=O)[O-]</td>\n",
       "      <td>pKa</td>\n",
       "      <td>10.45</td>\n",
       "      <td>10.0</td>\n",
       "      <td>c=0.005-0.017, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approx.</td>\n",
       "      <td>T67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, nitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2009</td>\n",
       "      <td>C[N+](=O)[O-]</td>\n",
       "      <td>pKa</td>\n",
       "      <td>10.33</td>\n",
       "      <td>18.0</td>\n",
       "      <td>c=0.005-0.017, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approx.</td>\n",
       "      <td>T67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, nitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2009</td>\n",
       "      <td>C[N+](=O)[O-]</td>\n",
       "      <td>pKa</td>\n",
       "      <td>10.21</td>\n",
       "      <td>25.0</td>\n",
       "      <td>c=0.005-0.017, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approx.</td>\n",
       "      <td>T67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, nitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2009</td>\n",
       "      <td>C[N+](=O)[O-]</td>\n",
       "      <td>pKa</td>\n",
       "      <td>10.24</td>\n",
       "      <td>25.0</td>\n",
       "      <td>c=0.005-0.017, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approx.</td>\n",
       "      <td>W22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, nitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pKa</td>\n",
       "      <td>8.65</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I=0.2(NaCl)</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Uncert.</td>\n",
       "      <td>C72</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methanohydroxamic acid</td>\n",
       "      <td>Formohydroxamic acid</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2011</td>\n",
       "      <td>NC(=S)S</td>\n",
       "      <td>pKa</td>\n",
       "      <td>2.95</td>\n",
       "      <td>25.0</td>\n",
       "      <td>c=0.002-0.01</td>\n",
       "      <td>C2</td>\n",
       "      <td>Approx.</td>\n",
       "      <td>G4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methanedithioic acid, amino-</td>\n",
       "      <td>Dithiocarbamic acid</td>\n",
       "      <td>['opsin_name1', 'opsin_nickname1', 'pubchem_ni...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2012</td>\n",
       "      <td>O=S(O)CO</td>\n",
       "      <td>pKa</td>\n",
       "      <td>1.65</td>\n",
       "      <td>20.0</td>\n",
       "      <td>c=0.1, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approx.</td>\n",
       "      <td>R54</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methanesulfinic acid, hydroxy-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1']</td>\n",
       "      <td>1.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    entry_#                                   SMILES pka_type  pka_value  \\\n",
       "0      2004                                       CO      pKa      15.50   \n",
       "1      2004                                       CO      pKa      15.09   \n",
       "2      2005                                      COO      pKa      11.50   \n",
       "3      2006                                       CS      pKa      10.33   \n",
       "4      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pKa       0.14   \n",
       "5      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pKa       0.06   \n",
       "6      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pKa       0.05   \n",
       "7      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pKa       0.23   \n",
       "8      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pKa       0.17   \n",
       "9      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pKa       0.11   \n",
       "10     2008                O=[N+]([O-])C[N+](=O)[O-]      pKa       3.63   \n",
       "11     2008                O=[N+]([O-])C[N+](=O)[O-]      pKa       3.72   \n",
       "12     2008                O=[N+]([O-])C[N+](=O)[O-]      pKa       3.60   \n",
       "13     2008                O=[N+]([O-])C[N+](=O)[O-]      pKa       3.51   \n",
       "14     2008                O=[N+]([O-])C[N+](=O)[O-]      pKa       3.43   \n",
       "15     2008                O=[N+]([O-])C[N+](=O)[O-]      pKa       3.57   \n",
       "16     2009                            C[N+](=O)[O-]      pKa      10.45   \n",
       "17     2009                            C[N+](=O)[O-]      pKa      10.33   \n",
       "18     2009                            C[N+](=O)[O-]      pKa      10.21   \n",
       "19     2009                            C[N+](=O)[O-]      pKa      10.24   \n",
       "20     2010                                      NaN      pKa       8.65   \n",
       "21     2011                                  NC(=S)S      pKa       2.95   \n",
       "22     2012                                 O=S(O)CO      pKa       1.65   \n",
       "\n",
       "       T                            remarks method assessment       ref  \\\n",
       "0   25.0                                NaN     C3    Uncert.        B8   \n",
       "1   25.0                                NaN    KIN    Uncert.      M126   \n",
       "2   20.0                                NaN     O5    Uncert.       E27   \n",
       "3   25.0  1% ethanol, gas solubility method    NaN    Uncert.       K57   \n",
       "4   20.0          In aqueous HClO4 Ho scale     O6    Uncert.      T58a   \n",
       "5   25.5                     Mixed constant     O5    Uncert.        H5   \n",
       "6    9.6                     Mixed constant     O5    Uncert.        H5   \n",
       "7    5.0            In aqueous HCl Ho scale     O6    Uncert.  N39, S82   \n",
       "8   20.0            In aqueous HCl Ho scale     O6    Uncert.  N39, S82   \n",
       "9   40.0            In aqueous HCl Ho scale     O6    Uncert.  N39, S82   \n",
       "10  20.0                             I=0.06     O5    Approx.       N39   \n",
       "11   5.0                             I=0.06     O5    Approx.       N39   \n",
       "12  20.0                             I=0.06     O5    Approx.       N39   \n",
       "13  40.0                             I=0.06     O5    Approx.       N39   \n",
       "14  60.0                             I=0.06     O5    Approx.       N39   \n",
       "15  25.0                             I=0.06     O5    Approx.        A7   \n",
       "16  10.0      c=0.005-0.017, mixed constant   E3bg    Approx.       T67   \n",
       "17  18.0      c=0.005-0.017, mixed constant   E3bg    Approx.       T67   \n",
       "18  25.0      c=0.005-0.017, mixed constant   E3bg    Approx.       T67   \n",
       "19  25.0      c=0.005-0.017, mixed constant   E3bg    Approx.       W22   \n",
       "20   NaN                        I=0.2(NaCl)   E3bg    Uncert.       C72   \n",
       "21  25.0                       c=0.002-0.01     C2    Approx.        G4   \n",
       "22  20.0              c=0.1, mixed constant   E3bg    Approx.       R54   \n",
       "\n",
       "                                          ref_remarks  \\\n",
       "0                                                 NaN   \n",
       "1                                                 NaN   \n",
       "2                                                 NaN   \n",
       "3                                                 NaN   \n",
       "4                                                 NaN   \n",
       "5                                                 NaN   \n",
       "6                                                 NaN   \n",
       "7   Thermodynamic quantities are derived from the ...   \n",
       "8   Thermodynamic quantities are derived from the ...   \n",
       "9   Thermodynamic quantities are derived from the ...   \n",
       "10  Thermodynamic quantities are derived from the ...   \n",
       "11  Thermodynamic quantities are derived from the ...   \n",
       "12  Thermodynamic quantities are derived from the ...   \n",
       "13  Thermodynamic quantities are derived from the ...   \n",
       "14  Thermodynamic quantities are derived from the ...   \n",
       "15                                                NaN   \n",
       "16                                                NaN   \n",
       "17                                                NaN   \n",
       "18                                                NaN   \n",
       "19                                                NaN   \n",
       "20                                                NaN   \n",
       "21                                                NaN   \n",
       "22                                                NaN   \n",
       "\n",
       "                                        entry_remarks  \\\n",
       "0                                                 NaN   \n",
       "1                                                 NaN   \n",
       "2                                                 NaN   \n",
       "3                                                 NaN   \n",
       "4    - 0.02  Thermodynamic quantities are derived ...   \n",
       "5    - 0.02  Thermodynamic quantities are derived ...   \n",
       "6    - 0.02  Thermodynamic quantities are derived ...   \n",
       "7    - 0.02  Thermodynamic quantities are derived ...   \n",
       "8    - 0.02  Thermodynamic quantities are derived ...   \n",
       "9    - 0.02  Thermodynamic quantities are derived ...   \n",
       "10                                                NaN   \n",
       "11                                                NaN   \n",
       "12                                                NaN   \n",
       "13                                                NaN   \n",
       "14                                                NaN   \n",
       "15                                                NaN   \n",
       "16                                                NaN   \n",
       "17                                                NaN   \n",
       "18                                                NaN   \n",
       "19                                                NaN   \n",
       "20                                                NaN   \n",
       "21                                                NaN   \n",
       "22                                                NaN   \n",
       "\n",
       "              original_IUPAC_names original_IUPAC_nicknames  \\\n",
       "0                         Methanol                      NaN   \n",
       "1                         Methanol                      NaN   \n",
       "2             Methyl hydroperoxide                      NaN   \n",
       "3                     Methanethiol                      NaN   \n",
       "4               methane, trinitro-                      NaN   \n",
       "5               methane, trinitro-                      NaN   \n",
       "6               methane, trinitro-                      NaN   \n",
       "7               methane, trinitro-                      NaN   \n",
       "8               methane, trinitro-                      NaN   \n",
       "9               methane, trinitro-                      NaN   \n",
       "10               methane, dinitro-                      NaN   \n",
       "11               methane, dinitro-                      NaN   \n",
       "12               methane, dinitro-                      NaN   \n",
       "13               methane, dinitro-                      NaN   \n",
       "14               methane, dinitro-                      NaN   \n",
       "15               methane, dinitro-                      NaN   \n",
       "16                 methane, nitro-                      NaN   \n",
       "17                 methane, nitro-                      NaN   \n",
       "18                 methane, nitro-                      NaN   \n",
       "19                 methane, nitro-                      NaN   \n",
       "20          Methanohydroxamic acid     Formohydroxamic acid   \n",
       "21    methanedithioic acid, amino-      Dithiocarbamic acid   \n",
       "22  methanesulfinic acid, hydroxy-                      NaN   \n",
       "\n",
       "                                    name_contributors  num_name_contributors  \\\n",
       "0     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "1     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "2     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "3     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "4     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "5     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "6     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "7     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "8     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "9     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "10    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "11    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "12    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "13    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "14    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "15    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "16    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "17    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "18    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "19    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "20                                                NaN                    NaN   \n",
       "21  ['opsin_name1', 'opsin_nickname1', 'pubchem_ni...                    3.0   \n",
       "22                                    ['opsin_name1']                    1.0   \n",
       "\n",
       "      source type_of_entry unique_ID  \n",
       "0   serjeant            in            \n",
       "1   serjeant            in            \n",
       "2   serjeant            in            \n",
       "3   serjeant            in            \n",
       "4   serjeant            in            \n",
       "5   serjeant            in            \n",
       "6   serjeant            in            \n",
       "7   serjeant            in            \n",
       "8   serjeant            in            \n",
       "9   serjeant            in            \n",
       "10  serjeant            in            \n",
       "11  serjeant            in            \n",
       "12  serjeant            in            \n",
       "13  serjeant            in            \n",
       "14  serjeant            in            \n",
       "15  serjeant            in            \n",
       "16  serjeant            in            \n",
       "17  serjeant            in            \n",
       "18  serjeant            in            \n",
       "19  serjeant            in            \n",
       "20  serjeant            in            \n",
       "21  serjeant            in            \n",
       "22  serjeant            in            "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"sample_done.csv\")\n",
    "df['source'] = 'serjeant'\n",
    "df['type_of_entry'] = 'in'\n",
    "df['unique_ID'] = ''\n",
    "PLUS_PATTERN = r'\\+[0-9.]*'\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b159340c",
   "metadata": {},
   "source": [
    "### Clean up all entries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "295418e5",
   "metadata": {
    "scrolled": true
   },
   "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>entry_#</th>\n",
       "      <th>SMILES</th>\n",
       "      <th>pka_type</th>\n",
       "      <th>pka_value</th>\n",
       "      <th>T</th>\n",
       "      <th>remarks</th>\n",
       "      <th>method</th>\n",
       "      <th>assessment</th>\n",
       "      <th>ref</th>\n",
       "      <th>ref_remarks</th>\n",
       "      <th>entry_remarks</th>\n",
       "      <th>original_IUPAC_names</th>\n",
       "      <th>original_IUPAC_nicknames</th>\n",
       "      <th>name_contributors</th>\n",
       "      <th>num_name_contributors</th>\n",
       "      <th>source</th>\n",
       "      <th>type_of_entry</th>\n",
       "      <th>unique_ID</th>\n",
       "      <th>pressure</th>\n",
       "      <th>acidity_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004</td>\n",
       "      <td>CO</td>\n",
       "      <td>pK1</td>\n",
       "      <td>15.50</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C3</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>B8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methanol</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004</td>\n",
       "      <td>CO</td>\n",
       "      <td>pK1</td>\n",
       "      <td>15.09</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KIN</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>M126</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methanol</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2005</td>\n",
       "      <td>COO</td>\n",
       "      <td>pK1</td>\n",
       "      <td>11.50</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O5</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>E27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methyl hydroperoxide</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2006</td>\n",
       "      <td>CS</td>\n",
       "      <td>pK1</td>\n",
       "      <td>10.33</td>\n",
       "      <td>25.0</td>\n",
       "      <td>1% ethanol, gas solubility method</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>K57</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methanethiol</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.14</td>\n",
       "      <td>20.0</td>\n",
       "      <td>In aqueous HClO4 Ho scale</td>\n",
       "      <td>O6</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>T58a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.06</td>\n",
       "      <td>25.5</td>\n",
       "      <td>Mixed constant</td>\n",
       "      <td>O5</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>H5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.05</td>\n",
       "      <td>9.6</td>\n",
       "      <td>Mixed constant</td>\n",
       "      <td>O5</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>H5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.23</td>\n",
       "      <td>5.0</td>\n",
       "      <td>In aqueous HCl Ho scale</td>\n",
       "      <td>O6</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>N39, S82</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.17</td>\n",
       "      <td>20.0</td>\n",
       "      <td>In aqueous HCl Ho scale</td>\n",
       "      <td>O6</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>N39, S82</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.11</td>\n",
       "      <td>40.0</td>\n",
       "      <td>In aqueous HCl Ho scale</td>\n",
       "      <td>O6</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>N39, S82</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.63</td>\n",
       "      <td>20.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.72</td>\n",
       "      <td>5.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.60</td>\n",
       "      <td>20.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.51</td>\n",
       "      <td>40.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.43</td>\n",
       "      <td>60.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.57</td>\n",
       "      <td>25.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>A7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2009</td>\n",
       "      <td>C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>10.45</td>\n",
       "      <td>10.0</td>\n",
       "      <td>C=0.005-0.017, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>T67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, nitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2009</td>\n",
       "      <td>C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>10.33</td>\n",
       "      <td>18.0</td>\n",
       "      <td>C=0.005-0.017, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>T67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, nitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2009</td>\n",
       "      <td>C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>10.21</td>\n",
       "      <td>25.0</td>\n",
       "      <td>C=0.005-0.017, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>T67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, nitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2009</td>\n",
       "      <td>C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>10.24</td>\n",
       "      <td>25.0</td>\n",
       "      <td>C=0.005-0.017, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>W22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, nitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pK1</td>\n",
       "      <td>8.65</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I=0.2(NaCl)</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>C72</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methanohydroxamic acid</td>\n",
       "      <td>Formohydroxamic acid</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2011</td>\n",
       "      <td>NC(=S)S</td>\n",
       "      <td>pK1</td>\n",
       "      <td>2.95</td>\n",
       "      <td>25.0</td>\n",
       "      <td>C=0.002-0.01</td>\n",
       "      <td>C2</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>G4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methanedithioic acid, amino-</td>\n",
       "      <td>Dithiocarbamic acid</td>\n",
       "      <td>['opsin_name1', 'opsin_nickname1', 'pubchem_ni...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2012</td>\n",
       "      <td>O=S(O)CO</td>\n",
       "      <td>pK1</td>\n",
       "      <td>1.65</td>\n",
       "      <td>20.0</td>\n",
       "      <td>C=0.1, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>R54</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methanesulfinic acid, hydroxy-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1']</td>\n",
       "      <td>1.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>in</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    entry_#                                   SMILES pka_type  pka_value  \\\n",
       "0      2004                                       CO      pK1      15.50   \n",
       "1      2004                                       CO      pK1      15.09   \n",
       "2      2005                                      COO      pK1      11.50   \n",
       "3      2006                                       CS      pK1      10.33   \n",
       "4      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.14   \n",
       "5      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.06   \n",
       "6      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.05   \n",
       "7      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.23   \n",
       "8      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.17   \n",
       "9      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.11   \n",
       "10     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.63   \n",
       "11     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.72   \n",
       "12     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.60   \n",
       "13     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.51   \n",
       "14     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.43   \n",
       "15     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.57   \n",
       "16     2009                            C[N+](=O)[O-]      pK1      10.45   \n",
       "17     2009                            C[N+](=O)[O-]      pK1      10.33   \n",
       "18     2009                            C[N+](=O)[O-]      pK1      10.21   \n",
       "19     2009                            C[N+](=O)[O-]      pK1      10.24   \n",
       "20     2010                                      NaN      pK1       8.65   \n",
       "21     2011                                  NC(=S)S      pK1       2.95   \n",
       "22     2012                                 O=S(O)CO      pK1       1.65   \n",
       "\n",
       "       T                            remarks method   assessment       ref  \\\n",
       "0   25.0                                NaN     C3    Uncertain        B8   \n",
       "1   25.0                                NaN    KIN    Uncertain      M126   \n",
       "2   20.0                                NaN     O5    Uncertain       E27   \n",
       "3   25.0  1% ethanol, gas solubility method    NaN    Uncertain       K57   \n",
       "4   20.0          In aqueous HClO4 Ho scale     O6    Uncertain      T58a   \n",
       "5   25.5                     Mixed constant     O5    Uncertain        H5   \n",
       "6    9.6                     Mixed constant     O5    Uncertain        H5   \n",
       "7    5.0            In aqueous HCl Ho scale     O6    Uncertain  N39, S82   \n",
       "8   20.0            In aqueous HCl Ho scale     O6    Uncertain  N39, S82   \n",
       "9   40.0            In aqueous HCl Ho scale     O6    Uncertain  N39, S82   \n",
       "10  20.0                             I=0.06     O5  Approximate       N39   \n",
       "11   5.0                             I=0.06     O5  Approximate       N39   \n",
       "12  20.0                             I=0.06     O5  Approximate       N39   \n",
       "13  40.0                             I=0.06     O5  Approximate       N39   \n",
       "14  60.0                             I=0.06     O5  Approximate       N39   \n",
       "15  25.0                             I=0.06     O5  Approximate        A7   \n",
       "16  10.0      C=0.005-0.017, mixed constant   E3bg  Approximate       T67   \n",
       "17  18.0      C=0.005-0.017, mixed constant   E3bg  Approximate       T67   \n",
       "18  25.0      C=0.005-0.017, mixed constant   E3bg  Approximate       T67   \n",
       "19  25.0      C=0.005-0.017, mixed constant   E3bg  Approximate       W22   \n",
       "20   NaN                        I=0.2(NaCl)   E3bg    Uncertain       C72   \n",
       "21  25.0                       C=0.002-0.01     C2  Approximate        G4   \n",
       "22  20.0              C=0.1, mixed constant   E3bg  Approximate       R54   \n",
       "\n",
       "                                          ref_remarks  \\\n",
       "0                                                 NaN   \n",
       "1                                                 NaN   \n",
       "2                                                 NaN   \n",
       "3                                                 NaN   \n",
       "4                                                 NaN   \n",
       "5                                                 NaN   \n",
       "6                                                 NaN   \n",
       "7   Thermodynamic quantities are derived from the ...   \n",
       "8   Thermodynamic quantities are derived from the ...   \n",
       "9   Thermodynamic quantities are derived from the ...   \n",
       "10  Thermodynamic quantities are derived from the ...   \n",
       "11  Thermodynamic quantities are derived from the ...   \n",
       "12  Thermodynamic quantities are derived from the ...   \n",
       "13  Thermodynamic quantities are derived from the ...   \n",
       "14  Thermodynamic quantities are derived from the ...   \n",
       "15                                                NaN   \n",
       "16                                                NaN   \n",
       "17                                                NaN   \n",
       "18                                                NaN   \n",
       "19                                                NaN   \n",
       "20                                                NaN   \n",
       "21                                                NaN   \n",
       "22                                                NaN   \n",
       "\n",
       "                                        entry_remarks  \\\n",
       "0                                                 NaN   \n",
       "1                                                 NaN   \n",
       "2                                                 NaN   \n",
       "3                                                 NaN   \n",
       "4   - 0.02  Thermodynamic quantities are derived f...   \n",
       "5   - 0.02  Thermodynamic quantities are derived f...   \n",
       "6   - 0.02  Thermodynamic quantities are derived f...   \n",
       "7   - 0.02  Thermodynamic quantities are derived f...   \n",
       "8   - 0.02  Thermodynamic quantities are derived f...   \n",
       "9   - 0.02  Thermodynamic quantities are derived f...   \n",
       "10                                                NaN   \n",
       "11                                                NaN   \n",
       "12                                                NaN   \n",
       "13                                                NaN   \n",
       "14                                                NaN   \n",
       "15                                                NaN   \n",
       "16                                                NaN   \n",
       "17                                                NaN   \n",
       "18                                                NaN   \n",
       "19                                                NaN   \n",
       "20                                                NaN   \n",
       "21                                                NaN   \n",
       "22                                                NaN   \n",
       "\n",
       "              original_IUPAC_names original_IUPAC_nicknames  \\\n",
       "0                         Methanol                      NaN   \n",
       "1                         Methanol                      NaN   \n",
       "2             Methyl hydroperoxide                      NaN   \n",
       "3                     Methanethiol                      NaN   \n",
       "4               methane, trinitro-                      NaN   \n",
       "5               methane, trinitro-                      NaN   \n",
       "6               methane, trinitro-                      NaN   \n",
       "7               methane, trinitro-                      NaN   \n",
       "8               methane, trinitro-                      NaN   \n",
       "9               methane, trinitro-                      NaN   \n",
       "10               methane, dinitro-                      NaN   \n",
       "11               methane, dinitro-                      NaN   \n",
       "12               methane, dinitro-                      NaN   \n",
       "13               methane, dinitro-                      NaN   \n",
       "14               methane, dinitro-                      NaN   \n",
       "15               methane, dinitro-                      NaN   \n",
       "16                 methane, nitro-                      NaN   \n",
       "17                 methane, nitro-                      NaN   \n",
       "18                 methane, nitro-                      NaN   \n",
       "19                 methane, nitro-                      NaN   \n",
       "20          Methanohydroxamic acid     Formohydroxamic acid   \n",
       "21    methanedithioic acid, amino-      Dithiocarbamic acid   \n",
       "22  methanesulfinic acid, hydroxy-                      NaN   \n",
       "\n",
       "                                    name_contributors  num_name_contributors  \\\n",
       "0     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "1     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "2     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "3     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "4     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "5     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "6     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "7     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "8     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "9     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "10    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "11    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "12    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "13    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "14    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "15    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "16    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "17    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "18    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "19    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "20                                                NaN                    NaN   \n",
       "21  ['opsin_name1', 'opsin_nickname1', 'pubchem_ni...                    3.0   \n",
       "22                                    ['opsin_name1']                    1.0   \n",
       "\n",
       "      source type_of_entry unique_ID pressure acidity_label  \n",
       "0   serjeant            in               None             A  \n",
       "1   serjeant            in               None             A  \n",
       "2   serjeant            in               None             A  \n",
       "3   serjeant            in               None             A  \n",
       "4   serjeant            in               None             A  \n",
       "5   serjeant            in               None             A  \n",
       "6   serjeant            in               None             A  \n",
       "7   serjeant            in               None             A  \n",
       "8   serjeant            in               None             A  \n",
       "9   serjeant            in               None             A  \n",
       "10  serjeant            in               None             A  \n",
       "11  serjeant            in               None             A  \n",
       "12  serjeant            in               None             A  \n",
       "13  serjeant            in               None             A  \n",
       "14  serjeant            in               None             A  \n",
       "15  serjeant            in               None             A  \n",
       "16  serjeant            in               None             A  \n",
       "17  serjeant            in               None             A  \n",
       "18  serjeant            in               None             A  \n",
       "19  serjeant            in               None             A  \n",
       "20  serjeant            in               None             A  \n",
       "21  serjeant            in               None             A  \n",
       "22  serjeant            in               None             A  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "P_MATCH = r\"P=[0-9]*[a-z ().]*\"\n",
    "\n",
    "pka_replacements = [\n",
    "    ('pk', 'pK'),\n",
    "    ('pka', 'pK1'),\n",
    "    ('pKa', 'pK1'),\n",
    "    ('cooh', 'COOH'),\n",
    "    ('pKa(H2O)', 'pK1'),\n",
    "    ('pK1(H2O)', 'pK1'),\n",
    "    ('pK2(H2O)', 'pK2'),\n",
    "    ('pK3(H2O)', 'pK3'),\n",
    "    ('pKb', 'pKB')\n",
    "]\n",
    "\n",
    "def fix_pka_type(entry, source):\n",
    "    \"\"\"\n",
    "    Fix/reformats several pKa types in the dataset and returns the reformatted entry.\n",
    "    Works differently based on the reference source.\n",
    "    \"\"\"\n",
    "    if pd.isnull(entry):\n",
    "        return entry\n",
    "    else:\n",
    "        for replacement in pka_replacements:\n",
    "            entry = entry.replace(replacement[0], replacement[1])\n",
    "            \n",
    "        if 'pK(' in entry and (source == 'perrin' or source == 'perrin_supp'): # as Perrin and Perrin Supp. are for basic pK's.\n",
    "            entry = entry.replace('pK(', 'pKAH(')\n",
    "        \n",
    "        return entry\n",
    "\n",
    "remarks_replacements = [\n",
    "    ('\\n. ', ''),\n",
    "    ('( (', '('),\n",
    "    ('( ', '('),\n",
    "    (' )', ')'),\n",
    "    ('c = ', 'C='),\n",
    "    ('c =', 'C='),\n",
    "    ('c= ', 'C='),\n",
    "    ('c=', 'C='),\n",
    "    ('c~', 'C~'),\n",
    "    ('c<', 'C<'),\n",
    "    ('c <', 'C<'),\n",
    "    ('c < ', 'C<'),\n",
    "    ('C = ', 'C='),\n",
    "    ('c - ', 'C='),\n",
    "    ('C= ', 'C='),\n",
    "    ('C =', 'C='),\n",
    "    ('concentration\\nconstant', 'concentration constant'),\n",
    "    ('concentration, constant', 'concentration constant'),\n",
    "    ('I = ', 'I='),\n",
    "    ('I =', 'I='),\n",
    "    ('I= ', 'I='),\n",
    "    ('m = ', 'm='),\n",
    "    ('m= ', 'm='),\n",
    "    ('m =', 'm='),\n",
    "    ('C< ', 'C<'),\n",
    "    ('C <', 'C<'),\n",
    "    ('C < ', 'C<'),\n",
    "    ('I< ', 'I<'),\n",
    "    ('1m solution', '1M solution'),\n",
    "    ('I <', 'I<'),\n",
    "    ('I < ', 'I<'),\n",
    "    ('S04', 'SO4'),\n",
    "    ('KCI', 'KCl'),\n",
    "    ('DCI', 'DCl'),\n",
    "    ('c between', 'C between'),\n",
    "    ('HCI', 'HCl'),\n",
    "    ('NaCI', 'NaCl'),\n",
    "    ('from, thermo', 'from thermo'),\n",
    "    (' (KCl)', ' (KCl)'),\n",
    "    (' (HCl', ' (HCl'),\n",
    "    (' (KBr)', ' (KBr)'),\n",
    "    (' (K2SO4)', '(K2SO4)'),\n",
    "    (' (NaClO4)', '(NaClO4)'),\n",
    "    (' (KNO3)', '(KNO3)'),\n",
    "    (' (NaCl)', '(NaCl)'),\n",
    "    (',,', ','),\n",
    "    ('()', ''),\n",
    "    ('. mixed', ', mixed'),\n",
    "    (': mixed', ', mixed'),\n",
    "    (') mixed', '), mixed'),\n",
    "    ('. conc', ', conc'),\n",
    "    (') conc', '), conc'),\n",
    "    ('\\n\\n\\n', '\\n')\n",
    "]\n",
    "\n",
    "def fix_remarks(entry):\n",
    "    \"\"\"\n",
    "    Fix some additional common mistakes that were found in the data, and returns the reformatted entry.\n",
    "    \"\"\"\n",
    "    if pd.isnull(entry):\n",
    "        return entry\n",
    "    else:\n",
    "        for replacement in remarks_replacements:\n",
    "            entry = entry.replace(replacement[0], replacement[1])\n",
    "    \n",
    "        return entry\n",
    "\n",
    "def fix_entry_remarks(entry):\n",
    "    \"\"\"Fixes typos in an entry's remarks, and returns the fixed entry.\"\"\"\n",
    "    if pd.isnull(entry):\n",
    "        return entry\n",
    "    else:\n",
    "        entry = entry.replace('- Thermodynamic data are derived from the results','')\n",
    "        entry = entry.strip('.\\n ')\n",
    "        entry = entry.replace('- \\n', ' - ')\n",
    "        return entry\n",
    "\n",
    "def fix_ref_remarks(entry):\n",
    "    \"\"\"Fixes typos in an entry's references, eliminating trailing semicolons, and returns the fixed entry.\"\"\"\n",
    "    if pd.isnull(entry):\n",
    "        return entry\n",
    "    else:\n",
    "        entry = entry.strip('; ')\n",
    "        return entry\n",
    "\n",
    "def isolate_P(entry):\n",
    "    \"\"\"Finds pressure in an entry's remark if it is available. Returns P if found, otherwise returns None.\"\"\"\n",
    "    if pd.isnull(entry):\n",
    "        return None\n",
    "    match = re.search(P_MATCH,entry)\n",
    "    if match:\n",
    "        return match.group().replace('P=','')\n",
    "    return None\n",
    "\n",
    "def standardize_refs(entry):\n",
    "    \"\"\"Fixes typos in an entry's refs by stripping trailing spaces, and returns the fixed entry.\"\"\"    \n",
    "    if pd.isnull(entry):\n",
    "        return entry\n",
    "    return entry.strip()\n",
    "\n",
    "def standardize_assessment(entry):\n",
    "    \"\"\"Fixes typos in an entry's uncertainty assessments, and returns the fixed entry.\"\"\"\n",
    "\n",
    "    if pd.isnull(entry):\n",
    "        return entry\n",
    "    entry = entry.replace('.','')\n",
    "    entry = entry.replace('Approx','Approximate')\n",
    "    entry = entry.replace('approx','approximate')\n",
    "    if entry == 'Uncert':\n",
    "        entry = 'Uncertain'\n",
    "    entry = entry.replace('Rel','Reliable')\n",
    "    entry = entry.replace('Very Uncertain','Very uncertain')\n",
    "    entry = entry.replace('Very Uncert','Very uncertain')\n",
    "    entry = entry.replace('V Uncert','Very uncertain')\n",
    "    entry = entry.replace('VUncert','Very uncertain')\n",
    "    entry = entry.replace('Vuncert','Very uncertain')\n",
    "    if entry == 'Very uncert':\n",
    "        entry = 'Very uncertain'\n",
    "    return entry\n",
    "    \n",
    "def make_acidity_label(entry, source) -> str:\n",
    "    \"\"\"\n",
    "    Given an entry and its original reference text source, returns a label that assesses whether the pKa entry\n",
    "    is for an acidic pK or a basic one. AH = conjugate acid, A = acid, B = base.\n",
    "    \"\"\"\n",
    "    if pd.isnull(entry):\n",
    "        return None\n",
    "    if 'pKAH' in entry:\n",
    "        return 'AH'\n",
    "    elif 'pKB' in entry or source == 'perrin' or source == 'perrin_supp':\n",
    "        return 'B'\n",
    "    else:\n",
    "        return 'A'\n",
    "    \n",
    "df['remarks'] = df['remarks'].apply(lambda x: fix_remarks(x))\n",
    "df['entry_remarks'] = df['entry_remarks'].apply(lambda x: fix_entry_remarks(x))\n",
    "df['ref_remarks'] = df['ref_remarks'].apply(lambda x: fix_ref_remarks(x))\n",
    "df['pka_type'] = df.apply(lambda x: fix_pka_type(x.pka_type, x.source), axis=1)\n",
    "df['pressure'] = df['remarks'].apply(lambda x: isolate_P(x))\n",
    "df['ref'] = df['ref'].apply(lambda x: standardize_refs(x))\n",
    "df['assessment'] = df['assessment'].apply(lambda x: standardize_assessment(x))\n",
    "df['acidity_label'] = df.apply(lambda x: make_acidity_label(x.pka_type, x.source), axis=1)\n",
    "\n",
    "df.to_csv(\"all_cat.csv\",index=False)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12187b69",
   "metadata": {},
   "source": [
    "### clean up and just get specific types of data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "400de472",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ">> Dropping N/A smiles\n"
     ]
    }
   ],
   "source": [
    "# An example of filtering the dataframe\n",
    "for ct, row in df.iterrows():\n",
    "    nullsmiles = False\n",
    "    unique_id = row['source'] + str(row['entry_#'])\n",
    "    df.loc[ct,'unique_ID'] = unique_id\n",
    "    pka = row['pka_value']\n",
    "    \n",
    "    try:\n",
    "        '.' in row['SMILES'] # can also add code here that checks if the SMILES is a salt with a period in it.\n",
    "    except TypeError:\n",
    "        print(\">> Dropping N/A smiles\")\n",
    "        df.loc[ct,'type_of_entry'] = 'out' # Basically filter out the entry if it's not a str.\n",
    "        \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5db5ce49",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Isolate the DF to all the entries with converged SMILES\n",
    "# (High and medium confidence values.)\n",
    "in_df = df.loc[df['type_of_entry'] == 'in']\n",
    "in_df = in_df.drop(['type_of_entry'],axis=1)\n",
    "\n",
    "# Isolate the DF to only the entries with converged SMILES and also multiple translation sources that agree.\n",
    "# (High-confidence).\n",
    "in_df_2 = df.loc[(df['type_of_entry'] == 'in') & (df['num_name_contributors'] >= 2)]\n",
    "in_df_2 = in_df_2.drop(['type_of_entry'],axis=1)\n",
    "\n",
    "# Isolate the DF to only the entries that did NOT converge. These are\n",
    "out_df = df.loc[df['type_of_entry'] == 'out']\n",
    "out_df = out_df.drop(['type_of_entry'],axis=1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e04824d3",
   "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>entry_#</th>\n",
       "      <th>SMILES</th>\n",
       "      <th>pka_type</th>\n",
       "      <th>pka_value</th>\n",
       "      <th>T</th>\n",
       "      <th>remarks</th>\n",
       "      <th>method</th>\n",
       "      <th>assessment</th>\n",
       "      <th>ref</th>\n",
       "      <th>ref_remarks</th>\n",
       "      <th>entry_remarks</th>\n",
       "      <th>original_IUPAC_names</th>\n",
       "      <th>original_IUPAC_nicknames</th>\n",
       "      <th>name_contributors</th>\n",
       "      <th>num_name_contributors</th>\n",
       "      <th>source</th>\n",
       "      <th>unique_ID</th>\n",
       "      <th>pressure</th>\n",
       "      <th>acidity_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004</td>\n",
       "      <td>CO</td>\n",
       "      <td>pK1</td>\n",
       "      <td>15.50</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C3</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>B8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methanol</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2004</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004</td>\n",
       "      <td>CO</td>\n",
       "      <td>pK1</td>\n",
       "      <td>15.09</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KIN</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>M126</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methanol</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2004</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2005</td>\n",
       "      <td>COO</td>\n",
       "      <td>pK1</td>\n",
       "      <td>11.50</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O5</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>E27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methyl hydroperoxide</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2005</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2006</td>\n",
       "      <td>CS</td>\n",
       "      <td>pK1</td>\n",
       "      <td>10.33</td>\n",
       "      <td>25.0</td>\n",
       "      <td>1% ethanol, gas solubility method</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>K57</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methanethiol</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2006</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.14</td>\n",
       "      <td>20.0</td>\n",
       "      <td>In aqueous HClO4 Ho scale</td>\n",
       "      <td>O6</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>T58a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2007</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.06</td>\n",
       "      <td>25.5</td>\n",
       "      <td>Mixed constant</td>\n",
       "      <td>O5</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>H5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2007</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.05</td>\n",
       "      <td>9.6</td>\n",
       "      <td>Mixed constant</td>\n",
       "      <td>O5</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>H5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2007</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.23</td>\n",
       "      <td>5.0</td>\n",
       "      <td>In aqueous HCl Ho scale</td>\n",
       "      <td>O6</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>N39, S82</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2007</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.17</td>\n",
       "      <td>20.0</td>\n",
       "      <td>In aqueous HCl Ho scale</td>\n",
       "      <td>O6</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>N39, S82</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2007</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.11</td>\n",
       "      <td>40.0</td>\n",
       "      <td>In aqueous HCl Ho scale</td>\n",
       "      <td>O6</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>N39, S82</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2007</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.63</td>\n",
       "      <td>20.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2008</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.72</td>\n",
       "      <td>5.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2008</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.60</td>\n",
       "      <td>20.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2008</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.51</td>\n",
       "      <td>40.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2008</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.43</td>\n",
       "      <td>60.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2008</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.57</td>\n",
       "      <td>25.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>A7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2008</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2009</td>\n",
       "      <td>C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>10.45</td>\n",
       "      <td>10.0</td>\n",
       "      <td>C=0.005-0.017, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>T67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, nitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2009</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2009</td>\n",
       "      <td>C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>10.33</td>\n",
       "      <td>18.0</td>\n",
       "      <td>C=0.005-0.017, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>T67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, nitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2009</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2009</td>\n",
       "      <td>C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>10.21</td>\n",
       "      <td>25.0</td>\n",
       "      <td>C=0.005-0.017, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>T67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, nitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2009</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2009</td>\n",
       "      <td>C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>10.24</td>\n",
       "      <td>25.0</td>\n",
       "      <td>C=0.005-0.017, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>W22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, nitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2009</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2011</td>\n",
       "      <td>NC(=S)S</td>\n",
       "      <td>pK1</td>\n",
       "      <td>2.95</td>\n",
       "      <td>25.0</td>\n",
       "      <td>C=0.002-0.01</td>\n",
       "      <td>C2</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>G4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methanedithioic acid, amino-</td>\n",
       "      <td>Dithiocarbamic acid</td>\n",
       "      <td>['opsin_name1', 'opsin_nickname1', 'pubchem_ni...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2011</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2012</td>\n",
       "      <td>O=S(O)CO</td>\n",
       "      <td>pK1</td>\n",
       "      <td>1.65</td>\n",
       "      <td>20.0</td>\n",
       "      <td>C=0.1, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>R54</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methanesulfinic acid, hydroxy-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1']</td>\n",
       "      <td>1.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2012</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    entry_#                                   SMILES pka_type  pka_value  \\\n",
       "0      2004                                       CO      pK1      15.50   \n",
       "1      2004                                       CO      pK1      15.09   \n",
       "2      2005                                      COO      pK1      11.50   \n",
       "3      2006                                       CS      pK1      10.33   \n",
       "4      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.14   \n",
       "5      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.06   \n",
       "6      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.05   \n",
       "7      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.23   \n",
       "8      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.17   \n",
       "9      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.11   \n",
       "10     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.63   \n",
       "11     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.72   \n",
       "12     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.60   \n",
       "13     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.51   \n",
       "14     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.43   \n",
       "15     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.57   \n",
       "16     2009                            C[N+](=O)[O-]      pK1      10.45   \n",
       "17     2009                            C[N+](=O)[O-]      pK1      10.33   \n",
       "18     2009                            C[N+](=O)[O-]      pK1      10.21   \n",
       "19     2009                            C[N+](=O)[O-]      pK1      10.24   \n",
       "21     2011                                  NC(=S)S      pK1       2.95   \n",
       "22     2012                                 O=S(O)CO      pK1       1.65   \n",
       "\n",
       "       T                            remarks method   assessment       ref  \\\n",
       "0   25.0                                NaN     C3    Uncertain        B8   \n",
       "1   25.0                                NaN    KIN    Uncertain      M126   \n",
       "2   20.0                                NaN     O5    Uncertain       E27   \n",
       "3   25.0  1% ethanol, gas solubility method    NaN    Uncertain       K57   \n",
       "4   20.0          In aqueous HClO4 Ho scale     O6    Uncertain      T58a   \n",
       "5   25.5                     Mixed constant     O5    Uncertain        H5   \n",
       "6    9.6                     Mixed constant     O5    Uncertain        H5   \n",
       "7    5.0            In aqueous HCl Ho scale     O6    Uncertain  N39, S82   \n",
       "8   20.0            In aqueous HCl Ho scale     O6    Uncertain  N39, S82   \n",
       "9   40.0            In aqueous HCl Ho scale     O6    Uncertain  N39, S82   \n",
       "10  20.0                             I=0.06     O5  Approximate       N39   \n",
       "11   5.0                             I=0.06     O5  Approximate       N39   \n",
       "12  20.0                             I=0.06     O5  Approximate       N39   \n",
       "13  40.0                             I=0.06     O5  Approximate       N39   \n",
       "14  60.0                             I=0.06     O5  Approximate       N39   \n",
       "15  25.0                             I=0.06     O5  Approximate        A7   \n",
       "16  10.0      C=0.005-0.017, mixed constant   E3bg  Approximate       T67   \n",
       "17  18.0      C=0.005-0.017, mixed constant   E3bg  Approximate       T67   \n",
       "18  25.0      C=0.005-0.017, mixed constant   E3bg  Approximate       T67   \n",
       "19  25.0      C=0.005-0.017, mixed constant   E3bg  Approximate       W22   \n",
       "21  25.0                       C=0.002-0.01     C2  Approximate        G4   \n",
       "22  20.0              C=0.1, mixed constant   E3bg  Approximate       R54   \n",
       "\n",
       "                                          ref_remarks  \\\n",
       "0                                                 NaN   \n",
       "1                                                 NaN   \n",
       "2                                                 NaN   \n",
       "3                                                 NaN   \n",
       "4                                                 NaN   \n",
       "5                                                 NaN   \n",
       "6                                                 NaN   \n",
       "7   Thermodynamic quantities are derived from the ...   \n",
       "8   Thermodynamic quantities are derived from the ...   \n",
       "9   Thermodynamic quantities are derived from the ...   \n",
       "10  Thermodynamic quantities are derived from the ...   \n",
       "11  Thermodynamic quantities are derived from the ...   \n",
       "12  Thermodynamic quantities are derived from the ...   \n",
       "13  Thermodynamic quantities are derived from the ...   \n",
       "14  Thermodynamic quantities are derived from the ...   \n",
       "15                                                NaN   \n",
       "16                                                NaN   \n",
       "17                                                NaN   \n",
       "18                                                NaN   \n",
       "19                                                NaN   \n",
       "21                                                NaN   \n",
       "22                                                NaN   \n",
       "\n",
       "                                        entry_remarks  \\\n",
       "0                                                 NaN   \n",
       "1                                                 NaN   \n",
       "2                                                 NaN   \n",
       "3                                                 NaN   \n",
       "4   - 0.02  Thermodynamic quantities are derived f...   \n",
       "5   - 0.02  Thermodynamic quantities are derived f...   \n",
       "6   - 0.02  Thermodynamic quantities are derived f...   \n",
       "7   - 0.02  Thermodynamic quantities are derived f...   \n",
       "8   - 0.02  Thermodynamic quantities are derived f...   \n",
       "9   - 0.02  Thermodynamic quantities are derived f...   \n",
       "10                                                NaN   \n",
       "11                                                NaN   \n",
       "12                                                NaN   \n",
       "13                                                NaN   \n",
       "14                                                NaN   \n",
       "15                                                NaN   \n",
       "16                                                NaN   \n",
       "17                                                NaN   \n",
       "18                                                NaN   \n",
       "19                                                NaN   \n",
       "21                                                NaN   \n",
       "22                                                NaN   \n",
       "\n",
       "              original_IUPAC_names original_IUPAC_nicknames  \\\n",
       "0                         Methanol                      NaN   \n",
       "1                         Methanol                      NaN   \n",
       "2             Methyl hydroperoxide                      NaN   \n",
       "3                     Methanethiol                      NaN   \n",
       "4               methane, trinitro-                      NaN   \n",
       "5               methane, trinitro-                      NaN   \n",
       "6               methane, trinitro-                      NaN   \n",
       "7               methane, trinitro-                      NaN   \n",
       "8               methane, trinitro-                      NaN   \n",
       "9               methane, trinitro-                      NaN   \n",
       "10               methane, dinitro-                      NaN   \n",
       "11               methane, dinitro-                      NaN   \n",
       "12               methane, dinitro-                      NaN   \n",
       "13               methane, dinitro-                      NaN   \n",
       "14               methane, dinitro-                      NaN   \n",
       "15               methane, dinitro-                      NaN   \n",
       "16                 methane, nitro-                      NaN   \n",
       "17                 methane, nitro-                      NaN   \n",
       "18                 methane, nitro-                      NaN   \n",
       "19                 methane, nitro-                      NaN   \n",
       "21    methanedithioic acid, amino-      Dithiocarbamic acid   \n",
       "22  methanesulfinic acid, hydroxy-                      NaN   \n",
       "\n",
       "                                    name_contributors  num_name_contributors  \\\n",
       "0     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "1     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "2     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "3     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "4     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "5     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "6     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "7     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "8     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "9     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "10    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "11    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "12    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "13    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "14    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "15    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "16    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "17    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "18    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "19    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "21  ['opsin_name1', 'opsin_nickname1', 'pubchem_ni...                    3.0   \n",
       "22                                    ['opsin_name1']                    1.0   \n",
       "\n",
       "      source     unique_ID pressure acidity_label  \n",
       "0   serjeant  serjeant2004     None             A  \n",
       "1   serjeant  serjeant2004     None             A  \n",
       "2   serjeant  serjeant2005     None             A  \n",
       "3   serjeant  serjeant2006     None             A  \n",
       "4   serjeant  serjeant2007     None             A  \n",
       "5   serjeant  serjeant2007     None             A  \n",
       "6   serjeant  serjeant2007     None             A  \n",
       "7   serjeant  serjeant2007     None             A  \n",
       "8   serjeant  serjeant2007     None             A  \n",
       "9   serjeant  serjeant2007     None             A  \n",
       "10  serjeant  serjeant2008     None             A  \n",
       "11  serjeant  serjeant2008     None             A  \n",
       "12  serjeant  serjeant2008     None             A  \n",
       "13  serjeant  serjeant2008     None             A  \n",
       "14  serjeant  serjeant2008     None             A  \n",
       "15  serjeant  serjeant2008     None             A  \n",
       "16  serjeant  serjeant2009     None             A  \n",
       "17  serjeant  serjeant2009     None             A  \n",
       "18  serjeant  serjeant2009     None             A  \n",
       "19  serjeant  serjeant2009     None             A  \n",
       "21  serjeant  serjeant2011     None             A  \n",
       "22  serjeant  serjeant2012     None             A  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "in_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "539a1504",
   "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>entry_#</th>\n",
       "      <th>SMILES</th>\n",
       "      <th>pka_type</th>\n",
       "      <th>pka_value</th>\n",
       "      <th>T</th>\n",
       "      <th>remarks</th>\n",
       "      <th>method</th>\n",
       "      <th>assessment</th>\n",
       "      <th>ref</th>\n",
       "      <th>ref_remarks</th>\n",
       "      <th>entry_remarks</th>\n",
       "      <th>original_IUPAC_names</th>\n",
       "      <th>original_IUPAC_nicknames</th>\n",
       "      <th>name_contributors</th>\n",
       "      <th>num_name_contributors</th>\n",
       "      <th>source</th>\n",
       "      <th>unique_ID</th>\n",
       "      <th>pressure</th>\n",
       "      <th>acidity_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004</td>\n",
       "      <td>CO</td>\n",
       "      <td>pK1</td>\n",
       "      <td>15.50</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C3</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>B8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methanol</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2004</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004</td>\n",
       "      <td>CO</td>\n",
       "      <td>pK1</td>\n",
       "      <td>15.09</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KIN</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>M126</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methanol</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2004</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2005</td>\n",
       "      <td>COO</td>\n",
       "      <td>pK1</td>\n",
       "      <td>11.50</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O5</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>E27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methyl hydroperoxide</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2005</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2006</td>\n",
       "      <td>CS</td>\n",
       "      <td>pK1</td>\n",
       "      <td>10.33</td>\n",
       "      <td>25.0</td>\n",
       "      <td>1% ethanol, gas solubility method</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>K57</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methanethiol</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2006</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.14</td>\n",
       "      <td>20.0</td>\n",
       "      <td>In aqueous HClO4 Ho scale</td>\n",
       "      <td>O6</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>T58a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2007</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.06</td>\n",
       "      <td>25.5</td>\n",
       "      <td>Mixed constant</td>\n",
       "      <td>O5</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>H5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2007</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.05</td>\n",
       "      <td>9.6</td>\n",
       "      <td>Mixed constant</td>\n",
       "      <td>O5</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>H5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2007</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.23</td>\n",
       "      <td>5.0</td>\n",
       "      <td>In aqueous HCl Ho scale</td>\n",
       "      <td>O6</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>N39, S82</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2007</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.17</td>\n",
       "      <td>20.0</td>\n",
       "      <td>In aqueous HCl Ho scale</td>\n",
       "      <td>O6</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>N39, S82</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2007</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2007</td>\n",
       "      <td>O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>0.11</td>\n",
       "      <td>40.0</td>\n",
       "      <td>In aqueous HCl Ho scale</td>\n",
       "      <td>O6</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>N39, S82</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>- 0.02  Thermodynamic quantities are derived f...</td>\n",
       "      <td>methane, trinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2007</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.63</td>\n",
       "      <td>20.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2008</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.72</td>\n",
       "      <td>5.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2008</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.60</td>\n",
       "      <td>20.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2008</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.51</td>\n",
       "      <td>40.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2008</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.43</td>\n",
       "      <td>60.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>N39</td>\n",
       "      <td>Thermodynamic quantities are derived from the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2008</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2008</td>\n",
       "      <td>O=[N+]([O-])C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>3.57</td>\n",
       "      <td>25.0</td>\n",
       "      <td>I=0.06</td>\n",
       "      <td>O5</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>A7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, dinitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2008</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2009</td>\n",
       "      <td>C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>10.45</td>\n",
       "      <td>10.0</td>\n",
       "      <td>C=0.005-0.017, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>T67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, nitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2009</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2009</td>\n",
       "      <td>C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>10.33</td>\n",
       "      <td>18.0</td>\n",
       "      <td>C=0.005-0.017, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>T67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, nitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2009</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2009</td>\n",
       "      <td>C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>10.21</td>\n",
       "      <td>25.0</td>\n",
       "      <td>C=0.005-0.017, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>T67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, nitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2009</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2009</td>\n",
       "      <td>C[N+](=O)[O-]</td>\n",
       "      <td>pK1</td>\n",
       "      <td>10.24</td>\n",
       "      <td>25.0</td>\n",
       "      <td>C=0.005-0.017, mixed constant</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>W22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methane, nitro-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['opsin_name1', 'cirpy_name1', 'pubchem_name1']</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2009</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2011</td>\n",
       "      <td>NC(=S)S</td>\n",
       "      <td>pK1</td>\n",
       "      <td>2.95</td>\n",
       "      <td>25.0</td>\n",
       "      <td>C=0.002-0.01</td>\n",
       "      <td>C2</td>\n",
       "      <td>Approximate</td>\n",
       "      <td>G4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>methanedithioic acid, amino-</td>\n",
       "      <td>Dithiocarbamic acid</td>\n",
       "      <td>['opsin_name1', 'opsin_nickname1', 'pubchem_ni...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2011</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    entry_#                                   SMILES pka_type  pka_value  \\\n",
       "0      2004                                       CO      pK1      15.50   \n",
       "1      2004                                       CO      pK1      15.09   \n",
       "2      2005                                      COO      pK1      11.50   \n",
       "3      2006                                       CS      pK1      10.33   \n",
       "4      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.14   \n",
       "5      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.06   \n",
       "6      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.05   \n",
       "7      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.23   \n",
       "8      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.17   \n",
       "9      2007  O=[N+]([O-])C([N+](=O)[O-])[N+](=O)[O-]      pK1       0.11   \n",
       "10     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.63   \n",
       "11     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.72   \n",
       "12     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.60   \n",
       "13     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.51   \n",
       "14     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.43   \n",
       "15     2008                O=[N+]([O-])C[N+](=O)[O-]      pK1       3.57   \n",
       "16     2009                            C[N+](=O)[O-]      pK1      10.45   \n",
       "17     2009                            C[N+](=O)[O-]      pK1      10.33   \n",
       "18     2009                            C[N+](=O)[O-]      pK1      10.21   \n",
       "19     2009                            C[N+](=O)[O-]      pK1      10.24   \n",
       "21     2011                                  NC(=S)S      pK1       2.95   \n",
       "\n",
       "       T                            remarks method   assessment       ref  \\\n",
       "0   25.0                                NaN     C3    Uncertain        B8   \n",
       "1   25.0                                NaN    KIN    Uncertain      M126   \n",
       "2   20.0                                NaN     O5    Uncertain       E27   \n",
       "3   25.0  1% ethanol, gas solubility method    NaN    Uncertain       K57   \n",
       "4   20.0          In aqueous HClO4 Ho scale     O6    Uncertain      T58a   \n",
       "5   25.5                     Mixed constant     O5    Uncertain        H5   \n",
       "6    9.6                     Mixed constant     O5    Uncertain        H5   \n",
       "7    5.0            In aqueous HCl Ho scale     O6    Uncertain  N39, S82   \n",
       "8   20.0            In aqueous HCl Ho scale     O6    Uncertain  N39, S82   \n",
       "9   40.0            In aqueous HCl Ho scale     O6    Uncertain  N39, S82   \n",
       "10  20.0                             I=0.06     O5  Approximate       N39   \n",
       "11   5.0                             I=0.06     O5  Approximate       N39   \n",
       "12  20.0                             I=0.06     O5  Approximate       N39   \n",
       "13  40.0                             I=0.06     O5  Approximate       N39   \n",
       "14  60.0                             I=0.06     O5  Approximate       N39   \n",
       "15  25.0                             I=0.06     O5  Approximate        A7   \n",
       "16  10.0      C=0.005-0.017, mixed constant   E3bg  Approximate       T67   \n",
       "17  18.0      C=0.005-0.017, mixed constant   E3bg  Approximate       T67   \n",
       "18  25.0      C=0.005-0.017, mixed constant   E3bg  Approximate       T67   \n",
       "19  25.0      C=0.005-0.017, mixed constant   E3bg  Approximate       W22   \n",
       "21  25.0                       C=0.002-0.01     C2  Approximate        G4   \n",
       "\n",
       "                                          ref_remarks  \\\n",
       "0                                                 NaN   \n",
       "1                                                 NaN   \n",
       "2                                                 NaN   \n",
       "3                                                 NaN   \n",
       "4                                                 NaN   \n",
       "5                                                 NaN   \n",
       "6                                                 NaN   \n",
       "7   Thermodynamic quantities are derived from the ...   \n",
       "8   Thermodynamic quantities are derived from the ...   \n",
       "9   Thermodynamic quantities are derived from the ...   \n",
       "10  Thermodynamic quantities are derived from the ...   \n",
       "11  Thermodynamic quantities are derived from the ...   \n",
       "12  Thermodynamic quantities are derived from the ...   \n",
       "13  Thermodynamic quantities are derived from the ...   \n",
       "14  Thermodynamic quantities are derived from the ...   \n",
       "15                                                NaN   \n",
       "16                                                NaN   \n",
       "17                                                NaN   \n",
       "18                                                NaN   \n",
       "19                                                NaN   \n",
       "21                                                NaN   \n",
       "\n",
       "                                        entry_remarks  \\\n",
       "0                                                 NaN   \n",
       "1                                                 NaN   \n",
       "2                                                 NaN   \n",
       "3                                                 NaN   \n",
       "4   - 0.02  Thermodynamic quantities are derived f...   \n",
       "5   - 0.02  Thermodynamic quantities are derived f...   \n",
       "6   - 0.02  Thermodynamic quantities are derived f...   \n",
       "7   - 0.02  Thermodynamic quantities are derived f...   \n",
       "8   - 0.02  Thermodynamic quantities are derived f...   \n",
       "9   - 0.02  Thermodynamic quantities are derived f...   \n",
       "10                                                NaN   \n",
       "11                                                NaN   \n",
       "12                                                NaN   \n",
       "13                                                NaN   \n",
       "14                                                NaN   \n",
       "15                                                NaN   \n",
       "16                                                NaN   \n",
       "17                                                NaN   \n",
       "18                                                NaN   \n",
       "19                                                NaN   \n",
       "21                                                NaN   \n",
       "\n",
       "            original_IUPAC_names original_IUPAC_nicknames  \\\n",
       "0                       Methanol                      NaN   \n",
       "1                       Methanol                      NaN   \n",
       "2           Methyl hydroperoxide                      NaN   \n",
       "3                   Methanethiol                      NaN   \n",
       "4             methane, trinitro-                      NaN   \n",
       "5             methane, trinitro-                      NaN   \n",
       "6             methane, trinitro-                      NaN   \n",
       "7             methane, trinitro-                      NaN   \n",
       "8             methane, trinitro-                      NaN   \n",
       "9             methane, trinitro-                      NaN   \n",
       "10             methane, dinitro-                      NaN   \n",
       "11             methane, dinitro-                      NaN   \n",
       "12             methane, dinitro-                      NaN   \n",
       "13             methane, dinitro-                      NaN   \n",
       "14             methane, dinitro-                      NaN   \n",
       "15             methane, dinitro-                      NaN   \n",
       "16               methane, nitro-                      NaN   \n",
       "17               methane, nitro-                      NaN   \n",
       "18               methane, nitro-                      NaN   \n",
       "19               methane, nitro-                      NaN   \n",
       "21  methanedithioic acid, amino-      Dithiocarbamic acid   \n",
       "\n",
       "                                    name_contributors  num_name_contributors  \\\n",
       "0     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "1     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "2     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "3     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "4     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "5     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "6     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "7     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "8     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "9     ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "10    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "11    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "12    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "13    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "14    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "15    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "16    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "17    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "18    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "19    ['opsin_name1', 'cirpy_name1', 'pubchem_name1']                    3.0   \n",
       "21  ['opsin_name1', 'opsin_nickname1', 'pubchem_ni...                    3.0   \n",
       "\n",
       "      source     unique_ID pressure acidity_label  \n",
       "0   serjeant  serjeant2004     None             A  \n",
       "1   serjeant  serjeant2004     None             A  \n",
       "2   serjeant  serjeant2005     None             A  \n",
       "3   serjeant  serjeant2006     None             A  \n",
       "4   serjeant  serjeant2007     None             A  \n",
       "5   serjeant  serjeant2007     None             A  \n",
       "6   serjeant  serjeant2007     None             A  \n",
       "7   serjeant  serjeant2007     None             A  \n",
       "8   serjeant  serjeant2007     None             A  \n",
       "9   serjeant  serjeant2007     None             A  \n",
       "10  serjeant  serjeant2008     None             A  \n",
       "11  serjeant  serjeant2008     None             A  \n",
       "12  serjeant  serjeant2008     None             A  \n",
       "13  serjeant  serjeant2008     None             A  \n",
       "14  serjeant  serjeant2008     None             A  \n",
       "15  serjeant  serjeant2008     None             A  \n",
       "16  serjeant  serjeant2009     None             A  \n",
       "17  serjeant  serjeant2009     None             A  \n",
       "18  serjeant  serjeant2009     None             A  \n",
       "19  serjeant  serjeant2009     None             A  \n",
       "21  serjeant  serjeant2011     None             A  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "in_df_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b62010f1",
   "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>entry_#</th>\n",
       "      <th>SMILES</th>\n",
       "      <th>pka_type</th>\n",
       "      <th>pka_value</th>\n",
       "      <th>T</th>\n",
       "      <th>remarks</th>\n",
       "      <th>method</th>\n",
       "      <th>assessment</th>\n",
       "      <th>ref</th>\n",
       "      <th>ref_remarks</th>\n",
       "      <th>entry_remarks</th>\n",
       "      <th>original_IUPAC_names</th>\n",
       "      <th>original_IUPAC_nicknames</th>\n",
       "      <th>name_contributors</th>\n",
       "      <th>num_name_contributors</th>\n",
       "      <th>source</th>\n",
       "      <th>unique_ID</th>\n",
       "      <th>pressure</th>\n",
       "      <th>acidity_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pK1</td>\n",
       "      <td>8.65</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I=0.2(NaCl)</td>\n",
       "      <td>E3bg</td>\n",
       "      <td>Uncertain</td>\n",
       "      <td>C72</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Methanohydroxamic acid</td>\n",
       "      <td>Formohydroxamic acid</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>serjeant</td>\n",
       "      <td>serjeant2010</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    entry_# SMILES pka_type  pka_value   T      remarks method assessment  \\\n",
       "20     2010    NaN      pK1       8.65 NaN  I=0.2(NaCl)   E3bg  Uncertain   \n",
       "\n",
       "    ref ref_remarks entry_remarks    original_IUPAC_names  \\\n",
       "20  C72         NaN           NaN  Methanohydroxamic acid   \n",
       "\n",
       "   original_IUPAC_nicknames name_contributors  num_name_contributors  \\\n",
       "20     Formohydroxamic acid               NaN                    NaN   \n",
       "\n",
       "      source     unique_ID pressure acidity_label  \n",
       "20  serjeant  serjeant2010     None             A  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "67547b48",
   "metadata": {},
   "outputs": [],
   "source": [
    "in_df.to_csv(\"SAMPLE_in.csv\",index=False)\n",
    "in_df_2.to_csv(\"SAMPLE_in-high-confidence.csv\",index=False)\n",
    "out_df.to_csv(\"SAMPLE_rejected.csv\",index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afe64ab9-a645-4833-af5e-43d5c1391304",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}