01.Processing_raw_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d0fa647c-48e9-4143-b0ce-7c038adb6c3f",
   "metadata": {},
   "source": [
    "## Processing raw data from Fragpipe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "53c16083-33ca-45c8-80fa-9cbdfae066df",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Download raw table from fragpipe \n",
    "## I manually removed additional replicates in compartiments that have more than 2 (some were lost)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "19bb705e-62e0-4195-9ebe-91889f288e96",
   "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>Protein</th>\n",
       "      <th>Protein_ID</th>\n",
       "      <th>Entry_Name</th>\n",
       "      <th>Gene</th>\n",
       "      <th>Protein_Length</th>\n",
       "      <th>Organism</th>\n",
       "      <th>Protein_Existence</th>\n",
       "      <th>Description</th>\n",
       "      <th>Protein_Probability</th>\n",
       "      <th>Top_Peptide_Probability</th>\n",
       "      <th>...</th>\n",
       "      <th>CITO_EGF_R2_MaxLFQIntensity</th>\n",
       "      <th>MEMB_CTRL_R1_MaxLFQIntensity</th>\n",
       "      <th>MEMB_CTRL_R2_MaxLFQIntensity</th>\n",
       "      <th>MEMB_EGF_R1_MaxLFQIntensity</th>\n",
       "      <th>MEMB_EGF_R2_MaxLFQIntensity</th>\n",
       "      <th>NUC_CTRL_R1_MaxLFQIntensity</th>\n",
       "      <th>NUC_CTRL_R2_MaxLFQIntensity</th>\n",
       "      <th>NUC_EGF_R1_MaxLFQIntensity</th>\n",
       "      <th>NUC_EGF_R2_MaxLFQIntensity</th>\n",
       "      <th>Indistinguishable_Proteins</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sp|A2VEC9|SSPO_HUMAN</td>\n",
       "      <td>A2VEC9</td>\n",
       "      <td>SSPO_HUMAN</td>\n",
       "      <td>SSPOP</td>\n",
       "      <td>5150.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>2:Experimental evidence at transcript level</td>\n",
       "      <td>SCO-spondin</td>\n",
       "      <td>0,517</td>\n",
       "      <td>0,9763</td>\n",
       "      <td>...</td>\n",
       "      <td>2,24E+07</td>\n",
       "      <td>1,58E+08</td>\n",
       "      <td>5,28E+08</td>\n",
       "      <td>2,91E+08</td>\n",
       "      <td>3,47E+08</td>\n",
       "      <td>5,94E+07</td>\n",
       "      <td>3,97E+07</td>\n",
       "      <td>2,92E+07</td>\n",
       "      <td>3,42E+07</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sp|A6NEC2|PSAL_HUMAN</td>\n",
       "      <td>A6NEC2</td>\n",
       "      <td>PSAL_HUMAN</td>\n",
       "      <td>NPEPPSL1</td>\n",
       "      <td>478.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>2:Experimental evidence at transcript level</td>\n",
       "      <td>Puromycin-sensitive aminopeptidase-like protein</td>\n",
       "      <td>0,9628</td>\n",
       "      <td>0,999</td>\n",
       "      <td>...</td>\n",
       "      <td>603055,4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>166716,14</td>\n",
       "      <td>0</td>\n",
       "      <td>138046,58</td>\n",
       "      <td>242806,28</td>\n",
       "      <td>sp|P55786|PSA_HUMAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sp|A8MWD9|RUXGL_HUMAN</td>\n",
       "      <td>A8MWD9</td>\n",
       "      <td>RUXGL_HUMAN</td>\n",
       "      <td>SNRPGP15</td>\n",
       "      <td>76.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>5:Protein uncertain</td>\n",
       "      <td>Putative small nuclear ribonucleoprotein G-lik...</td>\n",
       "      <td>0,8712</td>\n",
       "      <td>0,9962</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>540139,3</td>\n",
       "      <td>466551,6</td>\n",
       "      <td>429632,4</td>\n",
       "      <td>517614,8</td>\n",
       "      <td>sp|P62308|RUXG_HUMAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>sp|E9PAV3|NACAM_HUMAN</td>\n",
       "      <td>E9PAV3</td>\n",
       "      <td>NACAM_HUMAN</td>\n",
       "      <td>NACA</td>\n",
       "      <td>2078.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1:Experimental evidence at protein level</td>\n",
       "      <td>Nascent polypeptide-associated complex subunit...</td>\n",
       "      <td>1</td>\n",
       "      <td>0,999</td>\n",
       "      <td>...</td>\n",
       "      <td>692803,9</td>\n",
       "      <td>0</td>\n",
       "      <td>271649,72</td>\n",
       "      <td>756848,7</td>\n",
       "      <td>470896,66</td>\n",
       "      <td>664209,9</td>\n",
       "      <td>0</td>\n",
       "      <td>455872,25</td>\n",
       "      <td>447640,5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>sp|O00148|DX39A_HUMAN</td>\n",
       "      <td>O00148</td>\n",
       "      <td>DX39A_HUMAN</td>\n",
       "      <td>DDX39A</td>\n",
       "      <td>427.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1:Experimental evidence at protein level</td>\n",
       "      <td>ATP-dependent RNA helicase DDX39A</td>\n",
       "      <td>1</td>\n",
       "      <td>0,999</td>\n",
       "      <td>...</td>\n",
       "      <td>903027,6</td>\n",
       "      <td>0</td>\n",
       "      <td>288349,66</td>\n",
       "      <td>0</td>\n",
       "      <td>703015,2</td>\n",
       "      <td>614585,1</td>\n",
       "      <td>515612,9</td>\n",
       "      <td>606484,56</td>\n",
       "      <td>559466,1</td>\n",
       "      <td>sp|Q13838|DX39B_HUMAN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 87 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Protein Protein_ID   Entry_Name      Gene  Protein_Length  \\\n",
       "0   sp|A2VEC9|SSPO_HUMAN     A2VEC9   SSPO_HUMAN     SSPOP          5150.0   \n",
       "1   sp|A6NEC2|PSAL_HUMAN     A6NEC2   PSAL_HUMAN  NPEPPSL1           478.0   \n",
       "2  sp|A8MWD9|RUXGL_HUMAN     A8MWD9  RUXGL_HUMAN  SNRPGP15            76.0   \n",
       "3  sp|E9PAV3|NACAM_HUMAN     E9PAV3  NACAM_HUMAN      NACA          2078.0   \n",
       "4  sp|O00148|DX39A_HUMAN     O00148  DX39A_HUMAN    DDX39A           427.0   \n",
       "\n",
       "       Organism                            Protein_Existence  \\\n",
       "0  Homo sapiens  2:Experimental evidence at transcript level   \n",
       "1  Homo sapiens  2:Experimental evidence at transcript level   \n",
       "2  Homo sapiens                          5:Protein uncertain   \n",
       "3  Homo sapiens     1:Experimental evidence at protein level   \n",
       "4  Homo sapiens     1:Experimental evidence at protein level   \n",
       "\n",
       "                                         Description Protein_Probability  \\\n",
       "0                                        SCO-spondin               0,517   \n",
       "1    Puromycin-sensitive aminopeptidase-like protein              0,9628   \n",
       "2  Putative small nuclear ribonucleoprotein G-lik...              0,8712   \n",
       "3  Nascent polypeptide-associated complex subunit...                   1   \n",
       "4                  ATP-dependent RNA helicase DDX39A                   1   \n",
       "\n",
       "  Top_Peptide_Probability  ...  CITO_EGF_R2_MaxLFQIntensity  \\\n",
       "0                  0,9763  ...                     2,24E+07   \n",
       "1                   0,999  ...                     603055,4   \n",
       "2                  0,9962  ...                            0   \n",
       "3                   0,999  ...                     692803,9   \n",
       "4                   0,999  ...                     903027,6   \n",
       "\n",
       "   MEMB_CTRL_R1_MaxLFQIntensity  MEMB_CTRL_R2_MaxLFQIntensity  \\\n",
       "0                      1,58E+08                      5,28E+08   \n",
       "1                             0                             0   \n",
       "2                             0                             0   \n",
       "3                             0                     271649,72   \n",
       "4                             0                     288349,66   \n",
       "\n",
       "   MEMB_EGF_R1_MaxLFQIntensity  MEMB_EGF_R2_MaxLFQIntensity  \\\n",
       "0                     2,91E+08                     3,47E+08   \n",
       "1                            0                            0   \n",
       "2                            0                            0   \n",
       "3                     756848,7                    470896,66   \n",
       "4                            0                     703015,2   \n",
       "\n",
       "   NUC_CTRL_R1_MaxLFQIntensity  NUC_CTRL_R2_MaxLFQIntensity  \\\n",
       "0                     5,94E+07                     3,97E+07   \n",
       "1                    166716,14                            0   \n",
       "2                     540139,3                     466551,6   \n",
       "3                     664209,9                            0   \n",
       "4                     614585,1                     515612,9   \n",
       "\n",
       "   NUC_EGF_R1_MaxLFQIntensity  NUC_EGF_R2_MaxLFQIntensity  \\\n",
       "0                    2,92E+07                    3,42E+07   \n",
       "1                   138046,58                   242806,28   \n",
       "2                    429632,4                    517614,8   \n",
       "3                   455872,25                    447640,5   \n",
       "4                   606484,56                    559466,1   \n",
       "\n",
       "   Indistinguishable_Proteins  \n",
       "0                         NaN  \n",
       "1         sp|P55786|PSA_HUMAN  \n",
       "2        sp|P62308|RUXG_HUMAN  \n",
       "3                         NaN  \n",
       "4       sp|Q13838|DX39B_HUMAN  \n",
       "\n",
       "[5 rows x 87 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Load the file into a DataFrame\n",
    "df = pd.read_csv('data/MCF7_EGF_PROTEINS_COMBINED.tsv', sep='\\t')\n",
    "\n",
    "# Replace spaces with underscores in column names\n",
    "df.columns = df.columns.str.replace(' ', '_')\n",
    "\n",
    "# Display the first few rows to verify the changes\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f766e5a8-dec9-4181-b9a1-dbf9f50d1a8f",
   "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>Protein</th>\n",
       "      <th>Protein_ID</th>\n",
       "      <th>Entry_Name</th>\n",
       "      <th>Gene</th>\n",
       "      <th>Protein_Length</th>\n",
       "      <th>Organism</th>\n",
       "      <th>Protein_Existence</th>\n",
       "      <th>Description</th>\n",
       "      <th>Protein_Probability</th>\n",
       "      <th>Top_Peptide_Probability</th>\n",
       "      <th>...</th>\n",
       "      <th>CITO_EGF_R2_MaxLFQIntensity</th>\n",
       "      <th>MEMB_CTRL_R1_MaxLFQIntensity</th>\n",
       "      <th>MEMB_CTRL_R2_MaxLFQIntensity</th>\n",
       "      <th>MEMB_EGF_R1_MaxLFQIntensity</th>\n",
       "      <th>MEMB_EGF_R2_MaxLFQIntensity</th>\n",
       "      <th>NUC_CTRL_R1_MaxLFQIntensity</th>\n",
       "      <th>NUC_CTRL_R2_MaxLFQIntensity</th>\n",
       "      <th>NUC_EGF_R1_MaxLFQIntensity</th>\n",
       "      <th>NUC_EGF_R2_MaxLFQIntensity</th>\n",
       "      <th>Indistinguishable_Proteins</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sp|A2VEC9|SSPO_HUMAN</td>\n",
       "      <td>A2VEC9</td>\n",
       "      <td>SSPO_HUMAN</td>\n",
       "      <td>SSPOP</td>\n",
       "      <td>5150.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>2:Experimental evidence at transcript level</td>\n",
       "      <td>SCO-spondin</td>\n",
       "      <td>0.5170</td>\n",
       "      <td>0.9763</td>\n",
       "      <td>...</td>\n",
       "      <td>22400000.0</td>\n",
       "      <td>158000000.0</td>\n",
       "      <td>5.280000e+08</td>\n",
       "      <td>291000000.0</td>\n",
       "      <td>3.470000e+08</td>\n",
       "      <td>59400000.00</td>\n",
       "      <td>39700000.0</td>\n",
       "      <td>29200000.00</td>\n",
       "      <td>34200000.00</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sp|A6NEC2|PSAL_HUMAN</td>\n",
       "      <td>A6NEC2</td>\n",
       "      <td>PSAL_HUMAN</td>\n",
       "      <td>NPEPPSL1</td>\n",
       "      <td>478.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>2:Experimental evidence at transcript level</td>\n",
       "      <td>Puromycin-sensitive aminopeptidase-like protein</td>\n",
       "      <td>0.9628</td>\n",
       "      <td>0.9990</td>\n",
       "      <td>...</td>\n",
       "      <td>603055.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>166716.14</td>\n",
       "      <td>0.0</td>\n",
       "      <td>138046.58</td>\n",
       "      <td>242806.28</td>\n",
       "      <td>sp|P55786|PSA_HUMAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sp|A8MWD9|RUXGL_HUMAN</td>\n",
       "      <td>A8MWD9</td>\n",
       "      <td>RUXGL_HUMAN</td>\n",
       "      <td>SNRPGP15</td>\n",
       "      <td>76.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>5:Protein uncertain</td>\n",
       "      <td>Putative small nuclear ribonucleoprotein G-lik...</td>\n",
       "      <td>0.8712</td>\n",
       "      <td>0.9962</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>540139.30</td>\n",
       "      <td>466551.6</td>\n",
       "      <td>429632.40</td>\n",
       "      <td>517614.80</td>\n",
       "      <td>sp|P62308|RUXG_HUMAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>sp|E9PAV3|NACAM_HUMAN</td>\n",
       "      <td>E9PAV3</td>\n",
       "      <td>NACAM_HUMAN</td>\n",
       "      <td>NACA</td>\n",
       "      <td>2078.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1:Experimental evidence at protein level</td>\n",
       "      <td>Nascent polypeptide-associated complex subunit...</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.9990</td>\n",
       "      <td>...</td>\n",
       "      <td>692803.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.716497e+05</td>\n",
       "      <td>756848.7</td>\n",
       "      <td>4.708967e+05</td>\n",
       "      <td>664209.90</td>\n",
       "      <td>0.0</td>\n",
       "      <td>455872.25</td>\n",
       "      <td>447640.50</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>sp|O00148|DX39A_HUMAN</td>\n",
       "      <td>O00148</td>\n",
       "      <td>DX39A_HUMAN</td>\n",
       "      <td>DDX39A</td>\n",
       "      <td>427.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1:Experimental evidence at protein level</td>\n",
       "      <td>ATP-dependent RNA helicase DDX39A</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.9990</td>\n",
       "      <td>...</td>\n",
       "      <td>903027.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.883497e+05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.030152e+05</td>\n",
       "      <td>614585.10</td>\n",
       "      <td>515612.9</td>\n",
       "      <td>606484.56</td>\n",
       "      <td>559466.10</td>\n",
       "      <td>sp|Q13838|DX39B_HUMAN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 87 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Protein Protein_ID   Entry_Name      Gene  Protein_Length  \\\n",
       "0   sp|A2VEC9|SSPO_HUMAN     A2VEC9   SSPO_HUMAN     SSPOP          5150.0   \n",
       "1   sp|A6NEC2|PSAL_HUMAN     A6NEC2   PSAL_HUMAN  NPEPPSL1           478.0   \n",
       "2  sp|A8MWD9|RUXGL_HUMAN     A8MWD9  RUXGL_HUMAN  SNRPGP15            76.0   \n",
       "3  sp|E9PAV3|NACAM_HUMAN     E9PAV3  NACAM_HUMAN      NACA          2078.0   \n",
       "4  sp|O00148|DX39A_HUMAN     O00148  DX39A_HUMAN    DDX39A           427.0   \n",
       "\n",
       "       Organism                            Protein_Existence  \\\n",
       "0  Homo sapiens  2:Experimental evidence at transcript level   \n",
       "1  Homo sapiens  2:Experimental evidence at transcript level   \n",
       "2  Homo sapiens                          5:Protein uncertain   \n",
       "3  Homo sapiens     1:Experimental evidence at protein level   \n",
       "4  Homo sapiens     1:Experimental evidence at protein level   \n",
       "\n",
       "                                         Description  Protein_Probability  \\\n",
       "0                                        SCO-spondin               0.5170   \n",
       "1    Puromycin-sensitive aminopeptidase-like protein               0.9628   \n",
       "2  Putative small nuclear ribonucleoprotein G-lik...               0.8712   \n",
       "3  Nascent polypeptide-associated complex subunit...               1.0000   \n",
       "4                  ATP-dependent RNA helicase DDX39A               1.0000   \n",
       "\n",
       "   Top_Peptide_Probability  ...  CITO_EGF_R2_MaxLFQIntensity  \\\n",
       "0                   0.9763  ...                   22400000.0   \n",
       "1                   0.9990  ...                     603055.4   \n",
       "2                   0.9962  ...                          0.0   \n",
       "3                   0.9990  ...                     692803.9   \n",
       "4                   0.9990  ...                     903027.6   \n",
       "\n",
       "   MEMB_CTRL_R1_MaxLFQIntensity  MEMB_CTRL_R2_MaxLFQIntensity  \\\n",
       "0                   158000000.0                  5.280000e+08   \n",
       "1                           0.0                  0.000000e+00   \n",
       "2                           0.0                  0.000000e+00   \n",
       "3                           0.0                  2.716497e+05   \n",
       "4                           0.0                  2.883497e+05   \n",
       "\n",
       "   MEMB_EGF_R1_MaxLFQIntensity  MEMB_EGF_R2_MaxLFQIntensity  \\\n",
       "0                  291000000.0                 3.470000e+08   \n",
       "1                          0.0                 0.000000e+00   \n",
       "2                          0.0                 0.000000e+00   \n",
       "3                     756848.7                 4.708967e+05   \n",
       "4                          0.0                 7.030152e+05   \n",
       "\n",
       "   NUC_CTRL_R1_MaxLFQIntensity  NUC_CTRL_R2_MaxLFQIntensity  \\\n",
       "0                  59400000.00                   39700000.0   \n",
       "1                    166716.14                          0.0   \n",
       "2                    540139.30                     466551.6   \n",
       "3                    664209.90                          0.0   \n",
       "4                    614585.10                     515612.9   \n",
       "\n",
       "   NUC_EGF_R1_MaxLFQIntensity  NUC_EGF_R2_MaxLFQIntensity  \\\n",
       "0                 29200000.00                 34200000.00   \n",
       "1                   138046.58                   242806.28   \n",
       "2                   429632.40                   517614.80   \n",
       "3                   455872.25                   447640.50   \n",
       "4                   606484.56                   559466.10   \n",
       "\n",
       "   Indistinguishable_Proteins  \n",
       "0                         nan  \n",
       "1         sp|P55786|PSA_HUMAN  \n",
       "2        sp|P62308|RUXG_HUMAN  \n",
       "3                         nan  \n",
       "4       sp|Q13838|DX39B_HUMAN  \n",
       "\n",
       "[5 rows x 87 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Identify columns that are numerical (or should be numeric)\n",
    "df_filtered = df\n",
    "for col in df_filtered.columns:\n",
    "    # Replace commas with dots and convert to numeric, errors='coerce' handles non-numeric values gracefully\n",
    "    df_filtered[col] = df_filtered[col].astype(str).str.replace(',', '.').astype(float, errors='ignore')\n",
    "\n",
    "# Display the first few rows to verify the changes\n",
    "df_filtered.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8b26b0db-96b8-4434-b8f1-1a418a17985a",
   "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>Protein</th>\n",
       "      <th>Protein_ID</th>\n",
       "      <th>Entry_Name</th>\n",
       "      <th>Gene</th>\n",
       "      <th>Protein_Length</th>\n",
       "      <th>Organism</th>\n",
       "      <th>Protein_Existence</th>\n",
       "      <th>Description</th>\n",
       "      <th>Protein_Probability</th>\n",
       "      <th>Top_Peptide_Probability</th>\n",
       "      <th>...</th>\n",
       "      <th>CITO_EGF_R2_MaxLFQIntensity</th>\n",
       "      <th>MEMB_CTRL_R1_MaxLFQIntensity</th>\n",
       "      <th>MEMB_CTRL_R2_MaxLFQIntensity</th>\n",
       "      <th>MEMB_EGF_R1_MaxLFQIntensity</th>\n",
       "      <th>MEMB_EGF_R2_MaxLFQIntensity</th>\n",
       "      <th>NUC_CTRL_R1_MaxLFQIntensity</th>\n",
       "      <th>NUC_CTRL_R2_MaxLFQIntensity</th>\n",
       "      <th>NUC_EGF_R1_MaxLFQIntensity</th>\n",
       "      <th>NUC_EGF_R2_MaxLFQIntensity</th>\n",
       "      <th>Indistinguishable_Proteins</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sp|A6NEC2|PSAL_HUMAN</td>\n",
       "      <td>A6NEC2</td>\n",
       "      <td>PSAL_HUMAN</td>\n",
       "      <td>NPEPPSL1</td>\n",
       "      <td>478.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>2:Experimental evidence at transcript level</td>\n",
       "      <td>Puromycin-sensitive aminopeptidase-like protein</td>\n",
       "      <td>0.9628</td>\n",
       "      <td>0.9990</td>\n",
       "      <td>...</td>\n",
       "      <td>603055.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>166716.14</td>\n",
       "      <td>0.0</td>\n",
       "      <td>138046.58</td>\n",
       "      <td>242806.28</td>\n",
       "      <td>sp|P55786|PSA_HUMAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sp|A8MWD9|RUXGL_HUMAN</td>\n",
       "      <td>A8MWD9</td>\n",
       "      <td>RUXGL_HUMAN</td>\n",
       "      <td>SNRPGP15</td>\n",
       "      <td>76.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>5:Protein uncertain</td>\n",
       "      <td>Putative small nuclear ribonucleoprotein G-lik...</td>\n",
       "      <td>0.8712</td>\n",
       "      <td>0.9962</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>540139.30</td>\n",
       "      <td>466551.6</td>\n",
       "      <td>429632.40</td>\n",
       "      <td>517614.80</td>\n",
       "      <td>sp|P62308|RUXG_HUMAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>sp|E9PAV3|NACAM_HUMAN</td>\n",
       "      <td>E9PAV3</td>\n",
       "      <td>NACAM_HUMAN</td>\n",
       "      <td>NACA</td>\n",
       "      <td>2078.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1:Experimental evidence at protein level</td>\n",
       "      <td>Nascent polypeptide-associated complex subunit...</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.9990</td>\n",
       "      <td>...</td>\n",
       "      <td>692803.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>271649.72</td>\n",
       "      <td>756848.70</td>\n",
       "      <td>470896.66</td>\n",
       "      <td>664209.90</td>\n",
       "      <td>0.0</td>\n",
       "      <td>455872.25</td>\n",
       "      <td>447640.50</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>sp|O00148|DX39A_HUMAN</td>\n",
       "      <td>O00148</td>\n",
       "      <td>DX39A_HUMAN</td>\n",
       "      <td>DDX39A</td>\n",
       "      <td>427.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1:Experimental evidence at protein level</td>\n",
       "      <td>ATP-dependent RNA helicase DDX39A</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.9990</td>\n",
       "      <td>...</td>\n",
       "      <td>903027.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>288349.66</td>\n",
       "      <td>0.00</td>\n",
       "      <td>703015.20</td>\n",
       "      <td>614585.10</td>\n",
       "      <td>515612.9</td>\n",
       "      <td>606484.56</td>\n",
       "      <td>559466.10</td>\n",
       "      <td>sp|Q13838|DX39B_HUMAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>sp|O00186|STXB3_HUMAN</td>\n",
       "      <td>O00186</td>\n",
       "      <td>STXB3_HUMAN</td>\n",
       "      <td>STXBP3</td>\n",
       "      <td>592.0</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1:Experimental evidence at protein level</td>\n",
       "      <td>Syntaxin-binding protein 3</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.9968</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>533019.75</td>\n",
       "      <td>494184.90</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 87 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Protein Protein_ID   Entry_Name      Gene  Protein_Length  \\\n",
       "1   sp|A6NEC2|PSAL_HUMAN     A6NEC2   PSAL_HUMAN  NPEPPSL1           478.0   \n",
       "2  sp|A8MWD9|RUXGL_HUMAN     A8MWD9  RUXGL_HUMAN  SNRPGP15            76.0   \n",
       "3  sp|E9PAV3|NACAM_HUMAN     E9PAV3  NACAM_HUMAN      NACA          2078.0   \n",
       "4  sp|O00148|DX39A_HUMAN     O00148  DX39A_HUMAN    DDX39A           427.0   \n",
       "7  sp|O00186|STXB3_HUMAN     O00186  STXB3_HUMAN    STXBP3           592.0   \n",
       "\n",
       "       Organism                            Protein_Existence  \\\n",
       "1  Homo sapiens  2:Experimental evidence at transcript level   \n",
       "2  Homo sapiens                          5:Protein uncertain   \n",
       "3  Homo sapiens     1:Experimental evidence at protein level   \n",
       "4  Homo sapiens     1:Experimental evidence at protein level   \n",
       "7  Homo sapiens     1:Experimental evidence at protein level   \n",
       "\n",
       "                                         Description  Protein_Probability  \\\n",
       "1    Puromycin-sensitive aminopeptidase-like protein               0.9628   \n",
       "2  Putative small nuclear ribonucleoprotein G-lik...               0.8712   \n",
       "3  Nascent polypeptide-associated complex subunit...               1.0000   \n",
       "4                  ATP-dependent RNA helicase DDX39A               1.0000   \n",
       "7                         Syntaxin-binding protein 3               1.0000   \n",
       "\n",
       "   Top_Peptide_Probability  ...  CITO_EGF_R2_MaxLFQIntensity  \\\n",
       "1                   0.9990  ...                     603055.4   \n",
       "2                   0.9962  ...                          0.0   \n",
       "3                   0.9990  ...                     692803.9   \n",
       "4                   0.9990  ...                     903027.6   \n",
       "7                   0.9968  ...                          0.0   \n",
       "\n",
       "   MEMB_CTRL_R1_MaxLFQIntensity  MEMB_CTRL_R2_MaxLFQIntensity  \\\n",
       "1                           0.0                          0.00   \n",
       "2                           0.0                          0.00   \n",
       "3                           0.0                     271649.72   \n",
       "4                           0.0                     288349.66   \n",
       "7                           0.0                          0.00   \n",
       "\n",
       "   MEMB_EGF_R1_MaxLFQIntensity  MEMB_EGF_R2_MaxLFQIntensity  \\\n",
       "1                         0.00                         0.00   \n",
       "2                         0.00                         0.00   \n",
       "3                    756848.70                    470896.66   \n",
       "4                         0.00                    703015.20   \n",
       "7                    533019.75                    494184.90   \n",
       "\n",
       "   NUC_CTRL_R1_MaxLFQIntensity  NUC_CTRL_R2_MaxLFQIntensity  \\\n",
       "1                    166716.14                          0.0   \n",
       "2                    540139.30                     466551.6   \n",
       "3                    664209.90                          0.0   \n",
       "4                    614585.10                     515612.9   \n",
       "7                         0.00                          0.0   \n",
       "\n",
       "   NUC_EGF_R1_MaxLFQIntensity  NUC_EGF_R2_MaxLFQIntensity  \\\n",
       "1                   138046.58                   242806.28   \n",
       "2                   429632.40                   517614.80   \n",
       "3                   455872.25                   447640.50   \n",
       "4                   606484.56                   559466.10   \n",
       "7                        0.00                        0.00   \n",
       "\n",
       "   Indistinguishable_Proteins  \n",
       "1         sp|P55786|PSA_HUMAN  \n",
       "2        sp|P62308|RUXG_HUMAN  \n",
       "3                         nan  \n",
       "4       sp|Q13838|DX39B_HUMAN  \n",
       "7                         nan  \n",
       "\n",
       "[5 rows x 87 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Apply the filters on df_filtered\n",
    "df_filtered2 = df_filtered[(df_filtered['Combined_Total_Spectral_Count'] > 3) & \n",
    "                           (df_filtered['Protein_Probability'] > 0.7)]\n",
    "\n",
    "# Display the first few rows to verify the result\n",
    "df_filtered2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "05300c1b-3219-4b20-9b8e-20bba2291b36",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['CITO_CTRL_R1_MaxLFQIntensity',\n",
       " 'CITO_CTRL_R2_MaxLFQIntensity',\n",
       " 'CITO_EGF_R1_MaxLFQIntensity',\n",
       " 'CITO_EGF_R2_MaxLFQIntensity',\n",
       " 'MEMB_CTRL_R1_MaxLFQIntensity',\n",
       " 'MEMB_CTRL_R2_MaxLFQIntensity',\n",
       " 'MEMB_EGF_R1_MaxLFQIntensity',\n",
       " 'MEMB_EGF_R2_MaxLFQIntensity',\n",
       " 'NUC_CTRL_R1_MaxLFQIntensity',\n",
       " 'NUC_CTRL_R2_MaxLFQIntensity',\n",
       " 'NUC_EGF_R1_MaxLFQIntensity',\n",
       " 'NUC_EGF_R2_MaxLFQIntensity']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Normalization - relative abundance\n",
    "## Make a copy of df_filtered2 to apply normalization\n",
    "df_filtered2_norm = df_filtered2.copy()\n",
    "\n",
    "## Identify columns ending with '_MaxLFQIntensity'\n",
    "intensity_columns = [col for col in df_filtered2_norm.columns if col.endswith('_MaxLFQIntensity')]\n",
    "intensity_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "0ac300a2-92d1-4045-b1c0-0b1fadd4c158",
   "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>CITO_CTRL_R1_MaxLFQIntensity</th>\n",
       "      <th>CITO_CTRL_R2_MaxLFQIntensity</th>\n",
       "      <th>CITO_EGF_R1_MaxLFQIntensity</th>\n",
       "      <th>CITO_EGF_R2_MaxLFQIntensity</th>\n",
       "      <th>MEMB_CTRL_R1_MaxLFQIntensity</th>\n",
       "      <th>MEMB_CTRL_R2_MaxLFQIntensity</th>\n",
       "      <th>MEMB_EGF_R1_MaxLFQIntensity</th>\n",
       "      <th>MEMB_EGF_R2_MaxLFQIntensity</th>\n",
       "      <th>NUC_CTRL_R1_MaxLFQIntensity</th>\n",
       "      <th>NUC_CTRL_R2_MaxLFQIntensity</th>\n",
       "      <th>NUC_EGF_R1_MaxLFQIntensity</th>\n",
       "      <th>NUC_EGF_R2_MaxLFQIntensity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.066044</td>\n",
       "      <td>0.047393</td>\n",
       "      <td>0.074077</td>\n",
       "      <td>0.065495</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.019497</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.024098</td>\n",
       "      <td>0.037875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.063167</td>\n",
       "      <td>0.086218</td>\n",
       "      <td>0.074999</td>\n",
       "      <td>0.080742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.087929</td>\n",
       "      <td>0.064295</td>\n",
       "      <td>0.087301</td>\n",
       "      <td>0.075242</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.052282</td>\n",
       "      <td>0.128003</td>\n",
       "      <td>0.068991</td>\n",
       "      <td>0.077677</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.079580</td>\n",
       "      <td>0.069827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.074786</td>\n",
       "      <td>0.048003</td>\n",
       "      <td>0.132628</td>\n",
       "      <td>0.098074</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.055496</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.102998</td>\n",
       "      <td>0.071873</td>\n",
       "      <td>0.095285</td>\n",
       "      <td>0.105871</td>\n",
       "      <td>0.087270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000546</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.090148</td>\n",
       "      <td>0.072403</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CITO_CTRL_R1_MaxLFQIntensity  CITO_CTRL_R2_MaxLFQIntensity  \\\n",
       "1                      0.066044                      0.047393   \n",
       "2                      0.000000                      0.000000   \n",
       "3                      0.087929                      0.064295   \n",
       "4                      0.074786                      0.048003   \n",
       "7                      0.000000                      0.000000   \n",
       "\n",
       "   CITO_EGF_R1_MaxLFQIntensity  CITO_EGF_R2_MaxLFQIntensity  \\\n",
       "1                     0.074077                     0.065495   \n",
       "2                     0.000000                     0.000000   \n",
       "3                     0.087301                     0.075242   \n",
       "4                     0.132628                     0.098074   \n",
       "7                     0.000546                     0.000000   \n",
       "\n",
       "   MEMB_CTRL_R1_MaxLFQIntensity  MEMB_CTRL_R2_MaxLFQIntensity  \\\n",
       "1                           0.0                      0.000000   \n",
       "2                           0.0                      0.000000   \n",
       "3                           0.0                      0.052282   \n",
       "4                           0.0                      0.055496   \n",
       "7                           0.0                      0.000000   \n",
       "\n",
       "   MEMB_EGF_R1_MaxLFQIntensity  MEMB_EGF_R2_MaxLFQIntensity  \\\n",
       "1                     0.000000                     0.000000   \n",
       "2                     0.000000                     0.000000   \n",
       "3                     0.128003                     0.068991   \n",
       "4                     0.000000                     0.102998   \n",
       "7                     0.090148                     0.072403   \n",
       "\n",
       "   NUC_CTRL_R1_MaxLFQIntensity  NUC_CTRL_R2_MaxLFQIntensity  \\\n",
       "1                     0.019497                     0.000000   \n",
       "2                     0.063167                     0.086218   \n",
       "3                     0.077677                     0.000000   \n",
       "4                     0.071873                     0.095285   \n",
       "7                     0.000000                     0.000000   \n",
       "\n",
       "   NUC_EGF_R1_MaxLFQIntensity  NUC_EGF_R2_MaxLFQIntensity  \n",
       "1                    0.024098                    0.037875  \n",
       "2                    0.074999                    0.080742  \n",
       "3                    0.079580                    0.069827  \n",
       "4                    0.105871                    0.087270  \n",
       "7                    0.000000                    0.000000  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Normalize each '_MaxLFQIntensity' column in the new DataFrame\n",
    "for col in intensity_columns:\n",
    "    col_sum = df_filtered2_norm[col].sum()  # Sum of the column\n",
    "    df_filtered2_norm[col] = (df_filtered2_norm[col] / col_sum) * 100  # Relative abundance calculation\n",
    "\n",
    "## Display the first few rows to verify the result\n",
    "df_filtered2_norm[intensity_columns].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "45a1e9bb-4b86-4ce0-a64e-ec977133ddcf",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "## Specify the directory and file path\n",
    "output_dir = \"./data\"\n",
    "output_file = \"df_filtered2_norm.tsv\"\n",
    "\n",
    "## Save the DataFrame as a TSV file\n",
    "df_filtered2_norm.to_csv(os.path.join(output_dir, output_file), sep='\\t', index=False)"
   ]
  }
 ],
 "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.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}