pdresult_reader.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "00bba68b-6db4-4dcb-904b-af872509ac6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import glob\n",
    "import sqlite3\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b27f020-1ef8-4dad-a421-bc035ea02f9c",
   "metadata": {},
   "source": [
    "### **Set study directory (relative to execution path)**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "84f36903-b5ff-45ab-8e81-f6cb664866f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "study_folder = \"study_folder\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3335ed2c-a7b9-4a84-b020-3e7fc35ee04e",
   "metadata": {},
   "source": [
    "### **The following code retrieves all pdResult files from the study directory**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3e5dbac3-8331-4fe7-ab33-b6fe23e2f8ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "pdresult_files = []\n",
    "for f in glob.glob(f\"{study_folder}/*.pdResult\"):\n",
    "    pdresult_files.append(f)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cf7d16b-78e7-426f-8d50-b04b49b1930d",
   "metadata": {},
   "source": [
    "### **In my case this is 2 files**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8bc3f08e-b0e0-4335-8f71-1fbdbdc02027",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(pdresult_files)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2aa3aa38-2fdd-429f-b657-e0ce8aae9272",
   "metadata": {},
   "source": [
    "### **Their names are:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ede19a0d-38cd-4059-a265-b65365f115cc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['study_folder\\\\XLpeplib_Beveridge_QEx-HFX_DSS_R1-(1).pdResult',\n",
       " 'study_folder\\\\XLpeplib_Beveridge_QEx-HFX_DSS_R1.pdResult']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pdresult_files"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ddfc77d-f993-4d71-822b-46ae9d6683ae",
   "metadata": {},
   "source": [
    "### **The needed tables can now be read from the result files using sqlite. Here I only read the CSMs and Crosslinks from the result file**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4e51d4d4-5420-40e3-b6a1-6a7e0d837002",
   "metadata": {},
   "outputs": [],
   "source": [
    "csm_tables = dict()\n",
    "crosslink_tables = dict()\n",
    "\n",
    "for pdresult_file in pdresult_files:\n",
    "    conn = sqlite3.connect(pdresult_file)\n",
    "    csms = pd.read_sql_query(\"SELECT * FROM CSMs\", conn)\n",
    "    crosslinks = pd.read_sql_query(\"SELECT * FROM Crosslinks\", conn)\n",
    "    csm_tables[pdresult_file] = csms\n",
    "    crosslink_tables[pdresult_file] = crosslinks"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7c24318-e261-4f28-957d-158b4bbf5765",
   "metadata": {},
   "source": [
    "### **The tables can then be retrieved via the result file name from the specific list**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "75db2897-7446-4869-9dde-800c7d07ffbe",
   "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>WorkflowId</th>\n",
       "      <th>ID</th>\n",
       "      <th>RawFileId</th>\n",
       "      <th>UniqueSequenceID</th>\n",
       "      <th>IdentifyingNodeNumber</th>\n",
       "      <th>Sequence</th>\n",
       "      <th>Crosslinker</th>\n",
       "      <th>LinkType</th>\n",
       "      <th>CrosslinkDetectionStrategy</th>\n",
       "      <th>Mz</th>\n",
       "      <th>...</th>\n",
       "      <th>ScoreAlpha</th>\n",
       "      <th>ScoreBeta</th>\n",
       "      <th>CombinedScore</th>\n",
       "      <th>AlphaTD</th>\n",
       "      <th>BetaTD</th>\n",
       "      <th>MatchedIonsA</th>\n",
       "      <th>SearchSpaceA</th>\n",
       "      <th>MatchedIonsB</th>\n",
       "      <th>SearchSpaceB</th>\n",
       "      <th>DeltaCn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-132</td>\n",
       "      <td>1</td>\n",
       "      <td>623</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>GQKNSR-GQKNSR</td>\n",
       "      <td>DSS</td>\n",
       "      <td>0</td>\n",
       "      <td>MS Annika</td>\n",
       "      <td>505.937866</td>\n",
       "      <td>...</td>\n",
       "      <td>119.825490</td>\n",
       "      <td>119.825478</td>\n",
       "      <td>119.825478</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-132</td>\n",
       "      <td>2</td>\n",
       "      <td>623</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>GQKNSR-GSQKDR</td>\n",
       "      <td>DSS</td>\n",
       "      <td>0</td>\n",
       "      <td>MS Annika</td>\n",
       "      <td>506.265778</td>\n",
       "      <td>...</td>\n",
       "      <td>108.852455</td>\n",
       "      <td>13.909301</td>\n",
       "      <td>13.909301</td>\n",
       "      <td>T</td>\n",
       "      <td>D</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-132</td>\n",
       "      <td>3</td>\n",
       "      <td>623</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>SDKNR-SDKNR</td>\n",
       "      <td>DSS</td>\n",
       "      <td>0</td>\n",
       "      <td>MS Annika</td>\n",
       "      <td>459.235626</td>\n",
       "      <td>...</td>\n",
       "      <td>114.428277</td>\n",
       "      <td>114.428279</td>\n",
       "      <td>114.428277</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>8.021972e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-132</td>\n",
       "      <td>7</td>\n",
       "      <td>623</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>DKQSGK-DKQSGK</td>\n",
       "      <td>DSS</td>\n",
       "      <td>0</td>\n",
       "      <td>MS Annika</td>\n",
       "      <td>487.923004</td>\n",
       "      <td>...</td>\n",
       "      <td>200.976009</td>\n",
       "      <td>200.976004</td>\n",
       "      <td>200.976004</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-132</td>\n",
       "      <td>9</td>\n",
       "      <td>623</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>DKQSGK-HSIKK</td>\n",
       "      <td>DSS</td>\n",
       "      <td>0</td>\n",
       "      <td>MS Annika</td>\n",
       "      <td>471.268341</td>\n",
       "      <td>...</td>\n",
       "      <td>94.470614</td>\n",
       "      <td>156.884113</td>\n",
       "      <td>94.470614</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>2.782231e-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>821</th>\n",
       "      <td>-132</td>\n",
       "      <td>2331</td>\n",
       "      <td>623</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MDGTEELLVKLNR-MDGTEELLVKLNR</td>\n",
       "      <td>DSS</td>\n",
       "      <td>0</td>\n",
       "      <td>MS Annika</td>\n",
       "      <td>793.920593</td>\n",
       "      <td>...</td>\n",
       "      <td>286.047627</td>\n",
       "      <td>286.047571</td>\n",
       "      <td>286.047571</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>822</th>\n",
       "      <td>-132</td>\n",
       "      <td>2332</td>\n",
       "      <td>623</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>TILDFLKSDGFANR-KIECFDSVEISGVEDR</td>\n",
       "      <td>DSS</td>\n",
       "      <td>0</td>\n",
       "      <td>MS Annika</td>\n",
       "      <td>904.951172</td>\n",
       "      <td>...</td>\n",
       "      <td>393.873401</td>\n",
       "      <td>376.151460</td>\n",
       "      <td>376.151460</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1.235949e-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>823</th>\n",
       "      <td>-132</td>\n",
       "      <td>2334</td>\n",
       "      <td>623</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>SSFEKNPIDFLEAK-SSFEKNPIDFLEAK</td>\n",
       "      <td>DSS</td>\n",
       "      <td>0</td>\n",
       "      <td>MS Annika</td>\n",
       "      <td>847.431274</td>\n",
       "      <td>...</td>\n",
       "      <td>412.440598</td>\n",
       "      <td>412.440643</td>\n",
       "      <td>412.440598</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>824</th>\n",
       "      <td>-132</td>\n",
       "      <td>2335</td>\n",
       "      <td>623</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>SSFEKNPIDFLEAK-SSFEKNPIDFLEAK</td>\n",
       "      <td>DSS</td>\n",
       "      <td>0</td>\n",
       "      <td>MS Annika</td>\n",
       "      <td>847.430603</td>\n",
       "      <td>...</td>\n",
       "      <td>437.097623</td>\n",
       "      <td>437.097788</td>\n",
       "      <td>437.097623</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>825</th>\n",
       "      <td>-132</td>\n",
       "      <td>2336</td>\n",
       "      <td>623</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>SSFEKNPIDFLEAK-MEDESKLHKFKDFK</td>\n",
       "      <td>DSS</td>\n",
       "      <td>1</td>\n",
       "      <td>MS Annika</td>\n",
       "      <td>1181.927002</td>\n",
       "      <td>...</td>\n",
       "      <td>151.040290</td>\n",
       "      <td>15.891407</td>\n",
       "      <td>15.891407</td>\n",
       "      <td>T</td>\n",
       "      <td>D</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>826 rows × 56 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     WorkflowId    ID  RawFileId  UniqueSequenceID  IdentifyingNodeNumber  \\\n",
       "0          -132     1        623                 0                      0   \n",
       "1          -132     2        623                 0                      0   \n",
       "2          -132     3        623                 0                      0   \n",
       "3          -132     7        623                 0                      0   \n",
       "4          -132     9        623                 0                      0   \n",
       "..          ...   ...        ...               ...                    ...   \n",
       "821        -132  2331        623                 0                      0   \n",
       "822        -132  2332        623                 0                      0   \n",
       "823        -132  2334        623                 0                      0   \n",
       "824        -132  2335        623                 0                      0   \n",
       "825        -132  2336        623                 0                      0   \n",
       "\n",
       "                            Sequence Crosslinker  LinkType  \\\n",
       "0                      GQKNSR-GQKNSR         DSS         0   \n",
       "1                      GQKNSR-GSQKDR         DSS         0   \n",
       "2                        SDKNR-SDKNR         DSS         0   \n",
       "3                      DKQSGK-DKQSGK         DSS         0   \n",
       "4                       DKQSGK-HSIKK         DSS         0   \n",
       "..                               ...         ...       ...   \n",
       "821      MDGTEELLVKLNR-MDGTEELLVKLNR         DSS         0   \n",
       "822  TILDFLKSDGFANR-KIECFDSVEISGVEDR         DSS         0   \n",
       "823    SSFEKNPIDFLEAK-SSFEKNPIDFLEAK         DSS         0   \n",
       "824    SSFEKNPIDFLEAK-SSFEKNPIDFLEAK         DSS         0   \n",
       "825    SSFEKNPIDFLEAK-MEDESKLHKFKDFK         DSS         1   \n",
       "\n",
       "    CrosslinkDetectionStrategy           Mz  ...  ScoreAlpha   ScoreBeta  \\\n",
       "0                    MS Annika   505.937866  ...  119.825490  119.825478   \n",
       "1                    MS Annika   506.265778  ...  108.852455   13.909301   \n",
       "2                    MS Annika   459.235626  ...  114.428277  114.428279   \n",
       "3                    MS Annika   487.923004  ...  200.976009  200.976004   \n",
       "4                    MS Annika   471.268341  ...   94.470614  156.884113   \n",
       "..                         ...          ...  ...         ...         ...   \n",
       "821                  MS Annika   793.920593  ...  286.047627  286.047571   \n",
       "822                  MS Annika   904.951172  ...  393.873401  376.151460   \n",
       "823                  MS Annika   847.431274  ...  412.440598  412.440643   \n",
       "824                  MS Annika   847.430603  ...  437.097623  437.097788   \n",
       "825                  MS Annika  1181.927002  ...  151.040290   15.891407   \n",
       "\n",
       "     CombinedScore  AlphaTD  BetaTD MatchedIonsA  SearchSpaceA MatchedIonsB  \\\n",
       "0       119.825478        T       T            5             1            5   \n",
       "1        13.909301        T       D            4             1            0   \n",
       "2       114.428277        T       T            5             3            5   \n",
       "3       200.976004        T       T            7             1            7   \n",
       "4        94.470614        T       T            4             1            6   \n",
       "..             ...      ...     ...          ...           ...          ...   \n",
       "821     286.047571        T       T            9             1            9   \n",
       "822     376.151460        T       T           11             1           10   \n",
       "823     412.440598        T       T           12             1           12   \n",
       "824     437.097623        T       T           12             1           12   \n",
       "825      15.891407        T       D            6             1            0   \n",
       "\n",
       "    SearchSpaceB       DeltaCn  \n",
       "0              1  0.000000e+00  \n",
       "1              1  0.000000e+00  \n",
       "2              3  8.021972e-01  \n",
       "3              1  0.000000e+00  \n",
       "4              1  2.782231e-08  \n",
       "..           ...           ...  \n",
       "821            1  0.000000e+00  \n",
       "822            1  1.235949e-07  \n",
       "823            1  0.000000e+00  \n",
       "824            1  0.000000e+00  \n",
       "825            2  0.000000e+00  \n",
       "\n",
       "[826 rows x 56 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "csm_tables['study_folder\\\\XLpeplib_Beveridge_QEx-HFX_DSS_R1.pdResult']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "993f910c-322a-482b-bbfb-abd8955d6a07",
   "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>ID</th>\n",
       "      <th>WorkflowId</th>\n",
       "      <th>PsmAmbiguity</th>\n",
       "      <th>ParentProteinCount</th>\n",
       "      <th>SequenceA</th>\n",
       "      <th>AccessionA</th>\n",
       "      <th>PositionA</th>\n",
       "      <th>SequenceB</th>\n",
       "      <th>AccessionB</th>\n",
       "      <th>PositionB</th>\n",
       "      <th>...</th>\n",
       "      <th>ProteinDescriptionB</th>\n",
       "      <th>ExcludedBy</th>\n",
       "      <th>BestCSMScore</th>\n",
       "      <th>InproteinA</th>\n",
       "      <th>InproteinB</th>\n",
       "      <th>Decoy</th>\n",
       "      <th>ModificationsA</th>\n",
       "      <th>ModificationsB</th>\n",
       "      <th>FileID</th>\n",
       "      <th>Confidence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>-132</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>GQ[K]NSR</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>3</td>\n",
       "      <td>GQ[K]NSR</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>-1</td>\n",
       "      <td>119.825478</td>\n",
       "      <td>779</td>\n",
       "      <td>779</td>\n",
       "      <td>0</td>\n",
       "      <td>K3(DSS)</td>\n",
       "      <td>K3(DSS)</td>\n",
       "      <td>623</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>-132</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>GQ[K]NSR</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>3</td>\n",
       "      <td>GSQ[K]DR</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td>13.909301</td>\n",
       "      <td>779</td>\n",
       "      <td>696</td>\n",
       "      <td>1</td>\n",
       "      <td>K3(DSS)</td>\n",
       "      <td>K4(DSS)</td>\n",
       "      <td>623</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>-132</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>SD[K]NR</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>3</td>\n",
       "      <td>SD[K]NR</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>-1</td>\n",
       "      <td>114.428277</td>\n",
       "      <td>866</td>\n",
       "      <td>866</td>\n",
       "      <td>0</td>\n",
       "      <td>K3(DSS)</td>\n",
       "      <td>K3(DSS)</td>\n",
       "      <td>623</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>-132</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>D[K]QSGK</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>2</td>\n",
       "      <td>D[K]QSGK</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>-1</td>\n",
       "      <td>200.976004</td>\n",
       "      <td>677</td>\n",
       "      <td>677</td>\n",
       "      <td>0</td>\n",
       "      <td>K2(DSS)</td>\n",
       "      <td>K2(DSS)</td>\n",
       "      <td>623</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>-132</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>D[K]QSGK</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>2</td>\n",
       "      <td>HSI[K]K</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>-1</td>\n",
       "      <td>94.470614</td>\n",
       "      <td>677</td>\n",
       "      <td>48</td>\n",
       "      <td>0</td>\n",
       "      <td>K2(DSS)</td>\n",
       "      <td>K4(DSS)</td>\n",
       "      <td>623</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>295</th>\n",
       "      <td>296</td>\n",
       "      <td>-132</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>IEEGI[K]ELGSQILK</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>6</td>\n",
       "      <td>SSFE[K]NPIDFLEAK</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>-1</td>\n",
       "      <td>288.359123</td>\n",
       "      <td>793</td>\n",
       "      <td>1180</td>\n",
       "      <td>0</td>\n",
       "      <td>K6(DSS)</td>\n",
       "      <td>K5(DSS)</td>\n",
       "      <td>623</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>297</td>\n",
       "      <td>-132</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>SNDIAAALI[K]DR</td>\n",
       "      <td>sp</td>\n",
       "      <td>10</td>\n",
       "      <td>FKHLKSEDEM[K]ASEDLK</td>\n",
       "      <td>sp</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td>24.964349</td>\n",
       "      <td>290</td>\n",
       "      <td>56</td>\n",
       "      <td>1</td>\n",
       "      <td>K10(DSS)</td>\n",
       "      <td>M10(Oxidation);K11(DSS)</td>\n",
       "      <td>623</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297</th>\n",
       "      <td>298</td>\n",
       "      <td>-132</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>TILDFL[K]SDGFANR</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>7</td>\n",
       "      <td>[K]IECFDSVEISGVEDR</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>-1</td>\n",
       "      <td>376.151460</td>\n",
       "      <td>688</td>\n",
       "      <td>575</td>\n",
       "      <td>0</td>\n",
       "      <td>K7(DSS)</td>\n",
       "      <td>K1(DSS);C4(Carbamidomethyl)</td>\n",
       "      <td>623</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>299</td>\n",
       "      <td>-132</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>SSFE[K]NPIDFLEAK</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>5</td>\n",
       "      <td>SSFE[K]NPIDFLEAK</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>-1</td>\n",
       "      <td>437.097623</td>\n",
       "      <td>1180</td>\n",
       "      <td>1180</td>\n",
       "      <td>0</td>\n",
       "      <td>K5(DSS)</td>\n",
       "      <td>K5(DSS)</td>\n",
       "      <td>623</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>299</th>\n",
       "      <td>300</td>\n",
       "      <td>-132</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>SSFE[K]NPIDFLEAK</td>\n",
       "      <td>Cas9</td>\n",
       "      <td>5</td>\n",
       "      <td>MEDESKLHKF[K]DFK</td>\n",
       "      <td>sp</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>-1</td>\n",
       "      <td>15.891407</td>\n",
       "      <td>1180</td>\n",
       "      <td>109</td>\n",
       "      <td>1</td>\n",
       "      <td>K5(DSS)</td>\n",
       "      <td>K11(DSS)</td>\n",
       "      <td>623</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>300 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      ID  WorkflowId  PsmAmbiguity  ParentProteinCount         SequenceA  \\\n",
       "0      1        -132             0                   1          GQ[K]NSR   \n",
       "1      2        -132             0                   1          GQ[K]NSR   \n",
       "2      3        -132             0                   1           SD[K]NR   \n",
       "3      4        -132             0                   1          D[K]QSGK   \n",
       "4      5        -132             0                   1          D[K]QSGK   \n",
       "..   ...         ...           ...                 ...               ...   \n",
       "295  296        -132             0                   1  IEEGI[K]ELGSQILK   \n",
       "296  297        -132             0                   1    SNDIAAALI[K]DR   \n",
       "297  298        -132             0                   1  TILDFL[K]SDGFANR   \n",
       "298  299        -132             0                   1  SSFE[K]NPIDFLEAK   \n",
       "299  300        -132             0                   2  SSFE[K]NPIDFLEAK   \n",
       "\n",
       "    AccessionA  PositionA            SequenceB AccessionB  PositionB  ...  \\\n",
       "0         Cas9          3             GQ[K]NSR       Cas9          3  ...   \n",
       "1         Cas9          3             GSQ[K]DR       Cas9          4  ...   \n",
       "2         Cas9          3              SD[K]NR       Cas9          3  ...   \n",
       "3         Cas9          2             D[K]QSGK       Cas9          2  ...   \n",
       "4         Cas9          2              HSI[K]K       Cas9          4  ...   \n",
       "..         ...        ...                  ...        ...        ...  ...   \n",
       "295       Cas9          6     SSFE[K]NPIDFLEAK       Cas9          5  ...   \n",
       "296         sp         10  FKHLKSEDEM[K]ASEDLK         sp         11  ...   \n",
       "297       Cas9          7   [K]IECFDSVEISGVEDR       Cas9          1  ...   \n",
       "298       Cas9          5     SSFE[K]NPIDFLEAK       Cas9          5  ...   \n",
       "299       Cas9          5     MEDESKLHKF[K]DFK         sp         11  ...   \n",
       "\n",
       "    ProteinDescriptionB  ExcludedBy  BestCSMScore InproteinA InproteinB  \\\n",
       "0                  Cas9          -1    119.825478        779        779   \n",
       "1                                -1     13.909301        779        696   \n",
       "2                  Cas9          -1    114.428277        866        866   \n",
       "3                  Cas9          -1    200.976004        677        677   \n",
       "4                  Cas9          -1     94.470614        677         48   \n",
       "..                  ...         ...           ...        ...        ...   \n",
       "295                Cas9          -1    288.359123        793       1180   \n",
       "296                              -1     24.964349        290         56   \n",
       "297                Cas9          -1    376.151460        688        575   \n",
       "298                Cas9          -1    437.097623       1180       1180   \n",
       "299                              -1     15.891407       1180        109   \n",
       "\n",
       "     Decoy  ModificationsA               ModificationsB FileID  Confidence  \n",
       "0        0         K3(DSS)                      K3(DSS)    623           3  \n",
       "1        1         K3(DSS)                      K4(DSS)    623           1  \n",
       "2        0         K3(DSS)                      K3(DSS)    623           3  \n",
       "3        0         K2(DSS)                      K2(DSS)    623           3  \n",
       "4        0         K2(DSS)                      K4(DSS)    623           3  \n",
       "..     ...             ...                          ...    ...         ...  \n",
       "295      0         K6(DSS)                      K5(DSS)    623           3  \n",
       "296      1        K10(DSS)      M10(Oxidation);K11(DSS)    623           1  \n",
       "297      0         K7(DSS)  K1(DSS);C4(Carbamidomethyl)    623           3  \n",
       "298      0         K5(DSS)                      K5(DSS)    623           3  \n",
       "299      1         K5(DSS)                     K11(DSS)    623           1  \n",
       "\n",
       "[300 rows x 24 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crosslink_tables['study_folder\\\\XLpeplib_Beveridge_QEx-HFX_DSS_R1.pdResult']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f614307f-fbd1-4d23-97f3-4b8dbb7e679f",
   "metadata": {},
   "source": [
    "### **Of course it's also possible to iterate over all tables to e.g. get an average number of 1% FDR CSMs**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "607bce2e-80a6-488f-bcb4-b76e80e9ed5c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The average number of 1% FDR CSMs is: 715.0\n"
     ]
    }
   ],
   "source": [
    "nr_of_csms_at_1_perc_FDR = 0\n",
    "for result_file in pdresult_files:\n",
    "    csms = csm_tables[result_file]\n",
    "    csms_1_perc_FDR = csms[csms[\"MatchConfidence\"] == 3]\n",
    "    nr_of_csms_at_1_perc_FDR += csms_1_perc_FDR.shape[0]\n",
    "print(f\"The average number of 1% FDR CSMs is: {nr_of_csms_at_1_perc_FDR / len(pdresult_files)}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e46d636-8640-4786-be42-f6c08ecd2bd7",
   "metadata": {},
   "source": [
    "### **If you need other tables from the result file I can recommend opening the result file with [https://sqlitebrowser.org/](https://sqlitebrowser.org/) and check what the table is named**\n",
    "### **The table can then be read like this:**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0315e7a3-43fc-4c58-be10-60de5ded3176",
   "metadata": {},
   "source": [
    "```python\n",
    "conn = sqlite3.connect(pdresult_file)\n",
    "table = pd.read_sql_query(\"SELECT * FROM TABLE_NAME\", conn)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7abb1792-f570-42f9-91df-ca0bb21191f3",
   "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.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}