formatdb/formadb.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd     \n",
    "from classyfire import *\n",
    "from rdkit import Chem\n",
    "from rdkit.Chem import rdMolDescriptors as rdMD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1508, 2)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"Euphorbia_Example_inhouse_database.txt\",  sep='\\t', header=None)\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CC1=C(O)C=C(OC)C(C(C)=O)=C1O</td>\n",
       "      <td>C10H12O4_Euphorbia_ebracteolata_1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CC(C1=C(O)C=C(OC)C=C1OC)=O,COC1=CC(OC)=CC(O)=C...</td>\n",
       "      <td>C10H12O4_Euphorbia_quinquecostata,portulacoides_2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>OC1=C(OC)C=C2C(OC(C=C2)=O)=C1</td>\n",
       "      <td>C10H8O4_Euphorbia_lunulata_3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>O=C1OC2=CC(OC)=C(O)C=C2C=C1</td>\n",
       "      <td>C10H8O4_Euphorbia_quinquecostata_4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CC(C1=C(OC)C=C(OC)C=C1OC)=O</td>\n",
       "      <td>C11H14O4_Euphorbia_portulacoides_5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   0  \\\n",
       "0                       CC1=C(O)C=C(OC)C(C(C)=O)=C1O   \n",
       "1  CC(C1=C(O)C=C(OC)C=C1OC)=O,COC1=CC(OC)=CC(O)=C...   \n",
       "2                      OC1=C(OC)C=C2C(OC(C=C2)=O)=C1   \n",
       "3                        O=C1OC2=CC(OC)=C(O)C=C2C=C1   \n",
       "4                        CC(C1=C(OC)C=C(OC)C=C1OC)=O   \n",
       "\n",
       "                                                   1  \n",
       "0                  C10H12O4_Euphorbia_ebracteolata_1  \n",
       "1  C10H12O4_Euphorbia_quinquecostata,portulacoides_2  \n",
       "2                       C10H8O4_Euphorbia_lunulata_3  \n",
       "3                 C10H8O4_Euphorbia_quinquecostata_4  \n",
       "4                 C11H14O4_Euphorbia_portulacoides_5  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "smi = list(df[0])\n",
    "m = [Chem.MolFromSmiles(x) for x in smi]\n",
    "inchi = []\n",
    "ikeys = []\n",
    "ikey1 = []\n",
    "ikey2 = []\n",
    "form = []\n",
    "exmass = []\n",
    "for i in range(len(m)):\n",
    "    try:\n",
    "        inchi.append(Chem.rdinchi.MolToInchi(m[i])[0])\n",
    "        ikey = Chem.rdinchi.InchiToInchiKey(inchi[i])\n",
    "        ikeys.append(ikey)\n",
    "        ikey1.append(ikey.split('-')[0])\n",
    "        ikey2.append(ikey.split('-')[1])\n",
    "    except:\n",
    "        ikeys.append('')\n",
    "        inchi.append('')\n",
    "        ikey1.append('')\n",
    "        ikey2.append('')\n",
    "    try:\n",
    "        form.append(rdMD.CalcMolFormula(m[i]))\n",
    "    except:\n",
    "        form.append('')\n",
    "    try:\n",
    "        exmass.append(rdMD.CalcExactMolWt(m[i]))\n",
    "    except:\n",
    "        exmass.append('')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>inchikey</th>\n",
       "      <th>MonoisotopicMass</th>\n",
       "      <th>InChI</th>\n",
       "      <th>SMILES</th>\n",
       "      <th>Identifier</th>\n",
       "      <th>InChIKey2</th>\n",
       "      <th>InChIKey1</th>\n",
       "      <th>MolecularFormula</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>RFKMWWMZUHXFBA-UHFFFAOYSA-N</td>\n",
       "      <td>196.074</td>\n",
       "      <td>InChI=1S/C10H12O4/c1-5-7(12)4-8(14-3)9(6(2)11)...</td>\n",
       "      <td>CC1=C(O)C=C(OC)C(C(C)=O)=C1O</td>\n",
       "      <td>C10H12O4_Euphorbia_ebracteolata_1</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>RFKMWWMZUHXFBA</td>\n",
       "      <td>C10H12O4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>CC(C1=C(O)C=C(OC)C=C1OC)=O,COC1=CC(OC)=CC(O)=C...</td>\n",
       "      <td>C10H12O4_Euphorbia_quinquecostata,portulacoides_2</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>RODXRVNMMDRFIK-UHFFFAOYSA-N</td>\n",
       "      <td>192.042</td>\n",
       "      <td>InChI=1S/C10H8O4/c1-13-9-4-6-2-3-10(12)14-8(6)...</td>\n",
       "      <td>OC1=C(OC)C=C2C(OC(C=C2)=O)=C1</td>\n",
       "      <td>C10H8O4_Euphorbia_lunulata_3</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>RODXRVNMMDRFIK</td>\n",
       "      <td>C10H8O4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SYTYLPHCLSSCOJ-UHFFFAOYSA-N</td>\n",
       "      <td>192.042</td>\n",
       "      <td>InChI=1S/C10H8O4/c1-13-9-5-8-6(4-7(9)11)2-3-10...</td>\n",
       "      <td>O=C1OC2=CC(OC)=C(O)C=C2C=C1</td>\n",
       "      <td>C10H8O4_Euphorbia_quinquecostata_4</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>SYTYLPHCLSSCOJ</td>\n",
       "      <td>C10H8O4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>KPZWHZSIXZXDMW-UHFFFAOYSA-N</td>\n",
       "      <td>210.089</td>\n",
       "      <td>InChI=1S/C11H14O4/c1-7(12)11-9(14-3)5-8(13-2)6...</td>\n",
       "      <td>CC(C1=C(OC)C=C(OC)C=C1OC)=O</td>\n",
       "      <td>C11H14O4_Euphorbia_portulacoides_5</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>KPZWHZSIXZXDMW</td>\n",
       "      <td>C11H14O4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      inchikey MonoisotopicMass  \\\n",
       "0  RFKMWWMZUHXFBA-UHFFFAOYSA-N          196.074   \n",
       "1                                                 \n",
       "2  RODXRVNMMDRFIK-UHFFFAOYSA-N          192.042   \n",
       "3  SYTYLPHCLSSCOJ-UHFFFAOYSA-N          192.042   \n",
       "4  KPZWHZSIXZXDMW-UHFFFAOYSA-N          210.089   \n",
       "\n",
       "                                               InChI  \\\n",
       "0  InChI=1S/C10H12O4/c1-5-7(12)4-8(14-3)9(6(2)11)...   \n",
       "1                                                      \n",
       "2  InChI=1S/C10H8O4/c1-13-9-4-6-2-3-10(12)14-8(6)...   \n",
       "3  InChI=1S/C10H8O4/c1-13-9-5-8-6(4-7(9)11)2-3-10...   \n",
       "4  InChI=1S/C11H14O4/c1-7(12)11-9(14-3)5-8(13-2)6...   \n",
       "\n",
       "                                              SMILES  \\\n",
       "0                       CC1=C(O)C=C(OC)C(C(C)=O)=C1O   \n",
       "1  CC(C1=C(O)C=C(OC)C=C1OC)=O,COC1=CC(OC)=CC(O)=C...   \n",
       "2                      OC1=C(OC)C=C2C(OC(C=C2)=O)=C1   \n",
       "3                        O=C1OC2=CC(OC)=C(O)C=C2C=C1   \n",
       "4                        CC(C1=C(OC)C=C(OC)C=C1OC)=O   \n",
       "\n",
       "                                          Identifier   InChIKey2  \\\n",
       "0                  C10H12O4_Euphorbia_ebracteolata_1  UHFFFAOYSA   \n",
       "1  C10H12O4_Euphorbia_quinquecostata,portulacoides_2               \n",
       "2                       C10H8O4_Euphorbia_lunulata_3  UHFFFAOYSA   \n",
       "3                 C10H8O4_Euphorbia_quinquecostata_4  UHFFFAOYSA   \n",
       "4                 C11H14O4_Euphorbia_portulacoides_5  UHFFFAOYSA   \n",
       "\n",
       "        InChIKey1 MolecularFormula  \n",
       "0  RFKMWWMZUHXFBA         C10H12O4  \n",
       "1                                   \n",
       "2  RODXRVNMMDRFIK          C10H8O4  \n",
       "3  SYTYLPHCLSSCOJ          C10H8O4  \n",
       "4  KPZWHZSIXZXDMW         C11H14O4  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = {'inchikey': ikeys, 'MonoisotopicMass': exmass, 'InChI': inchi, 'SMILES': list(df[0]),\n",
    "              'Identifier': list(df[1]), 'InChIKey2': ikey2, 'InChIKey1': ikey1, 'MolecularFormula': form}\n",
    "\n",
    "cn = [\"inchikey\", \"MonoisotopicMass\", \"InChI\", \"SMILES\", \"Identifier\", \"InChIKey2\", \"InChIKey1\", \"MolecularFormula\"]\n",
    "formdata = pd.DataFrame(data, columns=cn)\n",
    "formdata.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "classy = query_inchikey(ikeys)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# If the structure do not show a classification, try query\n",
    "#in_process = get_class(list(df[0]), chunksize=100)\n",
    "#classy = poll(in_process)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1508, 7)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "classy.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>class</th>\n",
       "      <th>direct_parent</th>\n",
       "      <th>inchikey</th>\n",
       "      <th>kingdom</th>\n",
       "      <th>molecular_framework</th>\n",
       "      <th>subclass</th>\n",
       "      <th>superclass</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Organooxygen compounds</td>\n",
       "      <td>Alkyl-phenylketones</td>\n",
       "      <td>RFKMWWMZUHXFBA-UHFFFAOYSA-N</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Aromatic homomonocyclic compounds</td>\n",
       "      <td>Carbonyl compounds</td>\n",
       "      <td>Organic oxygen compounds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Coumarins and derivatives</td>\n",
       "      <td>7-hydroxycoumarins</td>\n",
       "      <td>RODXRVNMMDRFIK-UHFFFAOYSA-N</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Aromatic heteropolycyclic compounds</td>\n",
       "      <td>Hydroxycoumarins</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Coumarins and derivatives</td>\n",
       "      <td>Hydroxycoumarins</td>\n",
       "      <td>SYTYLPHCLSSCOJ-UHFFFAOYSA-N</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Aromatic heteropolycyclic compounds</td>\n",
       "      <td>Hydroxycoumarins</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Organooxygen compounds</td>\n",
       "      <td>Alkyl-phenylketones</td>\n",
       "      <td>KPZWHZSIXZXDMW-UHFFFAOYSA-N</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Aromatic homomonocyclic compounds</td>\n",
       "      <td>Carbonyl compounds</td>\n",
       "      <td>Organic oxygen compounds</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       class        direct_parent  \\\n",
       "0     Organooxygen compounds  Alkyl-phenylketones   \n",
       "1                        NaN                  NaN   \n",
       "2  Coumarins and derivatives   7-hydroxycoumarins   \n",
       "3  Coumarins and derivatives     Hydroxycoumarins   \n",
       "4     Organooxygen compounds  Alkyl-phenylketones   \n",
       "\n",
       "                      inchikey            kingdom  \\\n",
       "0  RFKMWWMZUHXFBA-UHFFFAOYSA-N  Organic compounds   \n",
       "1                          NaN                NaN   \n",
       "2  RODXRVNMMDRFIK-UHFFFAOYSA-N  Organic compounds   \n",
       "3  SYTYLPHCLSSCOJ-UHFFFAOYSA-N  Organic compounds   \n",
       "4  KPZWHZSIXZXDMW-UHFFFAOYSA-N  Organic compounds   \n",
       "\n",
       "                   molecular_framework            subclass  \\\n",
       "0    Aromatic homomonocyclic compounds  Carbonyl compounds   \n",
       "1                                  NaN                 NaN   \n",
       "2  Aromatic heteropolycyclic compounds    Hydroxycoumarins   \n",
       "3  Aromatic heteropolycyclic compounds    Hydroxycoumarins   \n",
       "4    Aromatic homomonocyclic compounds  Carbonyl compounds   \n",
       "\n",
       "                         superclass  \n",
       "0          Organic oxygen compounds  \n",
       "1                               NaN  \n",
       "2  Phenylpropanoids and polyketides  \n",
       "3  Phenylpropanoids and polyketides  \n",
       "4          Organic oxygen compounds  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "classy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "classy = classy[['inchikey', 'kingdom', 'superclass', 'class', 'subclass']]\n",
    "classy.columns = ['inchikey', 'kingdom_name', 'superclass_name', 'class_name', 'subclass_name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>inchikey</th>\n",
       "      <th>MonoisotopicMass</th>\n",
       "      <th>InChI</th>\n",
       "      <th>SMILES</th>\n",
       "      <th>Identifier</th>\n",
       "      <th>InChIKey2</th>\n",
       "      <th>InChIKey1</th>\n",
       "      <th>MolecularFormula</th>\n",
       "      <th>kingdom_name</th>\n",
       "      <th>superclass_name</th>\n",
       "      <th>class_name</th>\n",
       "      <th>subclass_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1505</th>\n",
       "      <td>BWXDELRNNYLLKB-UHFFFAOYSA-N</td>\n",
       "      <td>153.115</td>\n",
       "      <td>InChI=1S/C9H15NO/c1-9-4-2-3-7(10-9)5-8(11)6-9/...</td>\n",
       "      <td>CC12CCCC(CC(=O)C1)N2</td>\n",
       "      <td>C9H15NO_Euphorbia_atoto_1504</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>BWXDELRNNYLLKB</td>\n",
       "      <td>C9H15NO</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Organoheterocyclic compounds</td>\n",
       "      <td>Piperidines</td>\n",
       "      <td>Piperidinones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1506</th>\n",
       "      <td>YEGNHFRGBNUOLZ-UHFFFAOYSA-N</td>\n",
       "      <td>185.105</td>\n",
       "      <td>InChI=1S/C9H15NO3/c1-3-4-5-13-9-7(11)6(2)8(12)...</td>\n",
       "      <td>CCCCOC1C(C(=C)C(=N1)O)O</td>\n",
       "      <td>C9H15NO3_Euphorbia_humifusa_1505</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>YEGNHFRGBNUOLZ</td>\n",
       "      <td>C9H15NO3</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Organoheterocyclic compounds</td>\n",
       "      <td>Pyrrolidines</td>\n",
       "      <td>Pyrrolidones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1507</th>\n",
       "      <td>JWUXJYZVKZKLTJ-UHFFFAOYSA-N</td>\n",
       "      <td>155.131</td>\n",
       "      <td>InChI=1S/C9H17NO/c1-8(2)5-7(11)6-9(3,4)10-8/h1...</td>\n",
       "      <td>CC1(C)CC(=O)CC(C)(C)N1</td>\n",
       "      <td>C9H17NO_Multiple__1506</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>JWUXJYZVKZKLTJ</td>\n",
       "      <td>C9H17NO</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Organoheterocyclic compounds</td>\n",
       "      <td>Piperidines</td>\n",
       "      <td>Piperidinones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1508</th>\n",
       "      <td>ORHBXUUXSCNDEV-UHFFFAOYSA-N</td>\n",
       "      <td>162.032</td>\n",
       "      <td>InChI=1S/C9H6O3/c10-7-3-1-6-2-4-9(11)12-8(6)5-...</td>\n",
       "      <td>c1cc(cc2c1ccc(=O)o2)O</td>\n",
       "      <td>C9H6O3_Multiple__1507</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>ORHBXUUXSCNDEV</td>\n",
       "      <td>C9H6O3</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "      <td>Coumarins and derivatives</td>\n",
       "      <td>Hydroxycoumarins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1509</th>\n",
       "      <td>GLEMZFGSPKRSOO-UHFFFAOYSA-N</td>\n",
       "      <td>178.027</td>\n",
       "      <td>InChI=1S/C9H6O4/c10-6-2-1-5-3-7(11)9(12)13-8(5...</td>\n",
       "      <td>c1cc(cc2c1cc(c(=O)o2)O)O</td>\n",
       "      <td>C9H6O4_Euphorbia_terracina_1508</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>GLEMZFGSPKRSOO</td>\n",
       "      <td>C9H6O4</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "      <td>Coumarins and derivatives</td>\n",
       "      <td>Hydroxycoumarins</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         inchikey MonoisotopicMass  \\\n",
       "1505  BWXDELRNNYLLKB-UHFFFAOYSA-N          153.115   \n",
       "1506  YEGNHFRGBNUOLZ-UHFFFAOYSA-N          185.105   \n",
       "1507  JWUXJYZVKZKLTJ-UHFFFAOYSA-N          155.131   \n",
       "1508  ORHBXUUXSCNDEV-UHFFFAOYSA-N          162.032   \n",
       "1509  GLEMZFGSPKRSOO-UHFFFAOYSA-N          178.027   \n",
       "\n",
       "                                                  InChI  \\\n",
       "1505  InChI=1S/C9H15NO/c1-9-4-2-3-7(10-9)5-8(11)6-9/...   \n",
       "1506  InChI=1S/C9H15NO3/c1-3-4-5-13-9-7(11)6(2)8(12)...   \n",
       "1507  InChI=1S/C9H17NO/c1-8(2)5-7(11)6-9(3,4)10-8/h1...   \n",
       "1508  InChI=1S/C9H6O3/c10-7-3-1-6-2-4-9(11)12-8(6)5-...   \n",
       "1509  InChI=1S/C9H6O4/c10-6-2-1-5-3-7(11)9(12)13-8(5...   \n",
       "\n",
       "                        SMILES                        Identifier   InChIKey2  \\\n",
       "1505      CC12CCCC(CC(=O)C1)N2      C9H15NO_Euphorbia_atoto_1504  UHFFFAOYSA   \n",
       "1506   CCCCOC1C(C(=C)C(=N1)O)O  C9H15NO3_Euphorbia_humifusa_1505  UHFFFAOYSA   \n",
       "1507    CC1(C)CC(=O)CC(C)(C)N1            C9H17NO_Multiple__1506  UHFFFAOYSA   \n",
       "1508     c1cc(cc2c1ccc(=O)o2)O             C9H6O3_Multiple__1507  UHFFFAOYSA   \n",
       "1509  c1cc(cc2c1cc(c(=O)o2)O)O   C9H6O4_Euphorbia_terracina_1508  UHFFFAOYSA   \n",
       "\n",
       "           InChIKey1 MolecularFormula       kingdom_name  \\\n",
       "1505  BWXDELRNNYLLKB          C9H15NO  Organic compounds   \n",
       "1506  YEGNHFRGBNUOLZ         C9H15NO3  Organic compounds   \n",
       "1507  JWUXJYZVKZKLTJ          C9H17NO  Organic compounds   \n",
       "1508  ORHBXUUXSCNDEV           C9H6O3  Organic compounds   \n",
       "1509  GLEMZFGSPKRSOO           C9H6O4  Organic compounds   \n",
       "\n",
       "                       superclass_name                 class_name  \\\n",
       "1505      Organoheterocyclic compounds                Piperidines   \n",
       "1506      Organoheterocyclic compounds               Pyrrolidines   \n",
       "1507      Organoheterocyclic compounds                Piperidines   \n",
       "1508  Phenylpropanoids and polyketides  Coumarins and derivatives   \n",
       "1509  Phenylpropanoids and polyketides  Coumarins and derivatives   \n",
       "\n",
       "         subclass_name  \n",
       "1505     Piperidinones  \n",
       "1506      Pyrrolidones  \n",
       "1507     Piperidinones  \n",
       "1508  Hydroxycoumarins  \n",
       "1509  Hydroxycoumarins  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "formfinal = pd.merge(formdata, classy, how='left', on=['inchikey'])\n",
    "formfinal.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MonoisotopicMass</th>\n",
       "      <th>InChI</th>\n",
       "      <th>SMILES</th>\n",
       "      <th>Identifier</th>\n",
       "      <th>InChIKey2</th>\n",
       "      <th>InChIKey1</th>\n",
       "      <th>MolecularFormula</th>\n",
       "      <th>kingdom_name</th>\n",
       "      <th>superclass_name</th>\n",
       "      <th>class_name</th>\n",
       "      <th>subclass_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>196.074</td>\n",
       "      <td>InChI=1S/C10H12O4/c1-5-7(12)4-8(14-3)9(6(2)11)...</td>\n",
       "      <td>CC1=C(O)C=C(OC)C(C(C)=O)=C1O</td>\n",
       "      <td>C10H12O4_Euphorbia_ebracteolata_1</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>RFKMWWMZUHXFBA</td>\n",
       "      <td>C10H12O4</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Organic oxygen compounds</td>\n",
       "      <td>Organooxygen compounds</td>\n",
       "      <td>Carbonyl compounds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>CC(C1=C(O)C=C(OC)C=C1OC)=O,COC1=CC(OC)=CC(O)=C...</td>\n",
       "      <td>C10H12O4_Euphorbia_quinquecostata,portulacoides_2</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>192.042</td>\n",
       "      <td>InChI=1S/C10H8O4/c1-13-9-4-6-2-3-10(12)14-8(6)...</td>\n",
       "      <td>OC1=C(OC)C=C2C(OC(C=C2)=O)=C1</td>\n",
       "      <td>C10H8O4_Euphorbia_lunulata_3</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>RODXRVNMMDRFIK</td>\n",
       "      <td>C10H8O4</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "      <td>Coumarins and derivatives</td>\n",
       "      <td>Hydroxycoumarins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>192.042</td>\n",
       "      <td>InChI=1S/C10H8O4/c1-13-9-5-8-6(4-7(9)11)2-3-10...</td>\n",
       "      <td>O=C1OC2=CC(OC)=C(O)C=C2C=C1</td>\n",
       "      <td>C10H8O4_Euphorbia_quinquecostata_4</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>SYTYLPHCLSSCOJ</td>\n",
       "      <td>C10H8O4</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "      <td>Coumarins and derivatives</td>\n",
       "      <td>Hydroxycoumarins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>210.089</td>\n",
       "      <td>InChI=1S/C11H14O4/c1-7(12)11-9(14-3)5-8(13-2)6...</td>\n",
       "      <td>CC(C1=C(OC)C=C(OC)C=C1OC)=O</td>\n",
       "      <td>C11H14O4_Euphorbia_portulacoides_5</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>KPZWHZSIXZXDMW</td>\n",
       "      <td>C11H14O4</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Organic oxygen compounds</td>\n",
       "      <td>Organooxygen compounds</td>\n",
       "      <td>Carbonyl compounds</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  MonoisotopicMass                                              InChI  \\\n",
       "0          196.074  InChI=1S/C10H12O4/c1-5-7(12)4-8(14-3)9(6(2)11)...   \n",
       "1                                                                       \n",
       "2          192.042  InChI=1S/C10H8O4/c1-13-9-4-6-2-3-10(12)14-8(6)...   \n",
       "3          192.042  InChI=1S/C10H8O4/c1-13-9-5-8-6(4-7(9)11)2-3-10...   \n",
       "4          210.089  InChI=1S/C11H14O4/c1-7(12)11-9(14-3)5-8(13-2)6...   \n",
       "\n",
       "                                              SMILES  \\\n",
       "0                       CC1=C(O)C=C(OC)C(C(C)=O)=C1O   \n",
       "1  CC(C1=C(O)C=C(OC)C=C1OC)=O,COC1=CC(OC)=CC(O)=C...   \n",
       "2                      OC1=C(OC)C=C2C(OC(C=C2)=O)=C1   \n",
       "3                        O=C1OC2=CC(OC)=C(O)C=C2C=C1   \n",
       "4                        CC(C1=C(OC)C=C(OC)C=C1OC)=O   \n",
       "\n",
       "                                          Identifier   InChIKey2  \\\n",
       "0                  C10H12O4_Euphorbia_ebracteolata_1  UHFFFAOYSA   \n",
       "1  C10H12O4_Euphorbia_quinquecostata,portulacoides_2               \n",
       "2                       C10H8O4_Euphorbia_lunulata_3  UHFFFAOYSA   \n",
       "3                 C10H8O4_Euphorbia_quinquecostata_4  UHFFFAOYSA   \n",
       "4                 C11H14O4_Euphorbia_portulacoides_5  UHFFFAOYSA   \n",
       "\n",
       "        InChIKey1 MolecularFormula       kingdom_name  \\\n",
       "0  RFKMWWMZUHXFBA         C10H12O4  Organic compounds   \n",
       "1                                                       \n",
       "2  RODXRVNMMDRFIK          C10H8O4  Organic compounds   \n",
       "3  SYTYLPHCLSSCOJ          C10H8O4  Organic compounds   \n",
       "4  KPZWHZSIXZXDMW         C11H14O4  Organic compounds   \n",
       "\n",
       "                    superclass_name                 class_name  \\\n",
       "0          Organic oxygen compounds     Organooxygen compounds   \n",
       "1                                                                \n",
       "2  Phenylpropanoids and polyketides  Coumarins and derivatives   \n",
       "3  Phenylpropanoids and polyketides  Coumarins and derivatives   \n",
       "4          Organic oxygen compounds     Organooxygen compounds   \n",
       "\n",
       "        subclass_name  \n",
       "0  Carbonyl compounds  \n",
       "1                      \n",
       "2    Hydroxycoumarins  \n",
       "3    Hydroxycoumarins  \n",
       "4  Carbonyl compounds  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "formfinal = formfinal.fillna('')\n",
    "formfinal.drop('inchikey', axis=1, inplace=True)\n",
    "formfinal.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MonoisotopicMass</th>\n",
       "      <th>InChI</th>\n",
       "      <th>SMILES</th>\n",
       "      <th>Identifier</th>\n",
       "      <th>InChIKey2</th>\n",
       "      <th>InChIKey1</th>\n",
       "      <th>MolecularFormula</th>\n",
       "      <th>kingdom_name</th>\n",
       "      <th>superclass_name</th>\n",
       "      <th>class_name</th>\n",
       "      <th>subclass_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>196.074</td>\n",
       "      <td>InChI=1S/C10H12O4/c1-5-7(12)4-8(14-3)9(6(2)11)...</td>\n",
       "      <td>CC1=C(O)C=C(OC)C(C(C)=O)=C1O</td>\n",
       "      <td>C10H12O4_Euphorbia_ebracteolata_1</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>RFKMWWMZUHXFBA</td>\n",
       "      <td>C10H12O4</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Organic oxygen compounds</td>\n",
       "      <td>Organooxygen compounds</td>\n",
       "      <td>Carbonyl compounds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>192.042</td>\n",
       "      <td>InChI=1S/C10H8O4/c1-13-9-4-6-2-3-10(12)14-8(6)...</td>\n",
       "      <td>OC1=C(OC)C=C2C(OC(C=C2)=O)=C1</td>\n",
       "      <td>C10H8O4_Euphorbia_lunulata_3</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>RODXRVNMMDRFIK</td>\n",
       "      <td>C10H8O4</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "      <td>Coumarins and derivatives</td>\n",
       "      <td>Hydroxycoumarins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>192.042</td>\n",
       "      <td>InChI=1S/C10H8O4/c1-13-9-5-8-6(4-7(9)11)2-3-10...</td>\n",
       "      <td>O=C1OC2=CC(OC)=C(O)C=C2C=C1</td>\n",
       "      <td>C10H8O4_Euphorbia_quinquecostata_4</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>SYTYLPHCLSSCOJ</td>\n",
       "      <td>C10H8O4</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Phenylpropanoids and polyketides</td>\n",
       "      <td>Coumarins and derivatives</td>\n",
       "      <td>Hydroxycoumarins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>210.089</td>\n",
       "      <td>InChI=1S/C11H14O4/c1-7(12)11-9(14-3)5-8(13-2)6...</td>\n",
       "      <td>CC(C1=C(OC)C=C(OC)C=C1OC)=O</td>\n",
       "      <td>C11H14O4_Euphorbia_portulacoides_5</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>KPZWHZSIXZXDMW</td>\n",
       "      <td>C11H14O4</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Organic oxygen compounds</td>\n",
       "      <td>Organooxygen compounds</td>\n",
       "      <td>Carbonyl compounds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>210.089</td>\n",
       "      <td>InChI=1S/C11H14O4/c1-6-8(14-3)5-9(15-4)10(7(2)...</td>\n",
       "      <td>CC(C1=C(O)C(C)=C(OC)C=C1OC)=O</td>\n",
       "      <td>C11H14O4_Euphorbia_portulacoides_6</td>\n",
       "      <td>UHFFFAOYSA</td>\n",
       "      <td>AAOFJKLTRKOQTQ</td>\n",
       "      <td>C11H14O4</td>\n",
       "      <td>Organic compounds</td>\n",
       "      <td>Organic oxygen compounds</td>\n",
       "      <td>Organooxygen compounds</td>\n",
       "      <td>Carbonyl compounds</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  MonoisotopicMass                                              InChI  \\\n",
       "0          196.074  InChI=1S/C10H12O4/c1-5-7(12)4-8(14-3)9(6(2)11)...   \n",
       "2          192.042  InChI=1S/C10H8O4/c1-13-9-4-6-2-3-10(12)14-8(6)...   \n",
       "3          192.042  InChI=1S/C10H8O4/c1-13-9-5-8-6(4-7(9)11)2-3-10...   \n",
       "4          210.089  InChI=1S/C11H14O4/c1-7(12)11-9(14-3)5-8(13-2)6...   \n",
       "5          210.089  InChI=1S/C11H14O4/c1-6-8(14-3)5-9(15-4)10(7(2)...   \n",
       "\n",
       "                          SMILES                          Identifier  \\\n",
       "0   CC1=C(O)C=C(OC)C(C(C)=O)=C1O   C10H12O4_Euphorbia_ebracteolata_1   \n",
       "2  OC1=C(OC)C=C2C(OC(C=C2)=O)=C1        C10H8O4_Euphorbia_lunulata_3   \n",
       "3    O=C1OC2=CC(OC)=C(O)C=C2C=C1  C10H8O4_Euphorbia_quinquecostata_4   \n",
       "4    CC(C1=C(OC)C=C(OC)C=C1OC)=O  C11H14O4_Euphorbia_portulacoides_5   \n",
       "5  CC(C1=C(O)C(C)=C(OC)C=C1OC)=O  C11H14O4_Euphorbia_portulacoides_6   \n",
       "\n",
       "    InChIKey2       InChIKey1 MolecularFormula       kingdom_name  \\\n",
       "0  UHFFFAOYSA  RFKMWWMZUHXFBA         C10H12O4  Organic compounds   \n",
       "2  UHFFFAOYSA  RODXRVNMMDRFIK          C10H8O4  Organic compounds   \n",
       "3  UHFFFAOYSA  SYTYLPHCLSSCOJ          C10H8O4  Organic compounds   \n",
       "4  UHFFFAOYSA  KPZWHZSIXZXDMW         C11H14O4  Organic compounds   \n",
       "5  UHFFFAOYSA  AAOFJKLTRKOQTQ         C11H14O4  Organic compounds   \n",
       "\n",
       "                    superclass_name                 class_name  \\\n",
       "0          Organic oxygen compounds     Organooxygen compounds   \n",
       "2  Phenylpropanoids and polyketides  Coumarins and derivatives   \n",
       "3  Phenylpropanoids and polyketides  Coumarins and derivatives   \n",
       "4          Organic oxygen compounds     Organooxygen compounds   \n",
       "5          Organic oxygen compounds     Organooxygen compounds   \n",
       "\n",
       "        subclass_name  \n",
       "0  Carbonyl compounds  \n",
       "2    Hydroxycoumarins  \n",
       "3    Hydroxycoumarins  \n",
       "4  Carbonyl compounds  \n",
       "5  Carbonyl compounds  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id = [x for x in range(len(ikeys)) if ikeys[x]=='']\n",
    "formfinal.drop(formfinal.index[id], inplace=True)\n",
    "formfinal.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "formfinal.to_csv('Euphorbia_Example_inhouse_database_FORMATED.txt', index=False, sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "formatdb",
   "language": "python",
   "name": "formatdb"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}