1.2-Data-Engineering-Final.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "64a2db26",
   "metadata": {},
   "source": [
    "### Data Engineering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "98b71f44",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json as json\n",
    "import glob as glob"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "cb82f7df",
   "metadata": {},
   "outputs": [],
   "source": [
    "files= glob.glob(\"data/aac-data/data/*.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "35a8b2b4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['data/aac-data/data/8_4.json',\n",
       " 'data/aac-data/data/8_8.json',\n",
       " 'data/aac-data/data/8_9.json',\n",
       " 'data/aac-data/data/9_11.json',\n",
       " 'data/aac-data/data/8_10.json',\n",
       " 'data/aac-data/data/9_7.json',\n",
       " 'data/aac-data/data/9_10.json',\n",
       " 'data/aac-data/data/6_16.json',\n",
       " 'data/aac-data/data/9_8.json',\n",
       " 'data/aac-data/data/9_4.json',\n",
       " 'data/aac-data/data/9_13.json',\n",
       " 'data/aac-data/data/9_12.json',\n",
       " 'data/aac-data/data/9_5.json',\n",
       " 'data/aac-data/data/9_9.json',\n",
       " 'data/aac-data/data/9_2.json',\n",
       " 'data/aac-data/data/8_14.json',\n",
       " 'data/aac-data/data/6_11.json',\n",
       " 'data/aac-data/data/8_15.json',\n",
       " 'data/aac-data/data/8_7.json']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "252edd1f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'aac-data'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "files[1].split(\"/\")[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "92308db3",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i,file in enumerate(files):\n",
    "    with open(file, 'r') as f:\n",
    "        data = json.load(f)\n",
    "        if i ==0:\n",
    "            df = pd.DataFrame(data)\n",
    "        else:\n",
    "            idf = pd.DataFrame(data)\n",
    "            df = pd.concat([df,idf])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "dc961362",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(59623, 10)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "00f20b70",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"data/aac-data/alldata.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5c3fb45c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15455"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "IDs= df['Uniprot_AC'].unique()\n",
    "len(IDs)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d763aa6b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 59623 entries, 0 to 3972\n",
      "Data columns (total 10 columns):\n",
      " #   Column                    Non-Null Count  Dtype \n",
      "---  ------                    --------------  ----- \n",
      " 0   Organism                  59623 non-null  object\n",
      " 1   Uniprot_AC                59623 non-null  object\n",
      " 2   position                  59623 non-null  int64 \n",
      " 3   standard_seq              59623 non-null  object\n",
      " 4   PTM_Catalogy              59623 non-null  object\n",
      " 5   PTM_Type                  59623 non-null  object\n",
      " 6   ref                       59623 non-null  object\n",
      " 7   cell_line                 59623 non-null  object\n",
      " 8   Identification_Strategy   59623 non-null  object\n",
      " 9   Identification_Technique  59623 non-null  object\n",
      "dtypes: int64(1), object(9)\n",
      "memory usage: 5.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "eeb55e8c",
   "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>Organism</th>\n",
       "      <th>Uniprot_AC</th>\n",
       "      <th>position</th>\n",
       "      <th>standard_seq</th>\n",
       "      <th>PTM_Catalogy</th>\n",
       "      <th>PTM_Type</th>\n",
       "      <th>ref</th>\n",
       "      <th>cell_line</th>\n",
       "      <th>Identification_Strategy</th>\n",
       "      <th>Identification_Technique</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>Q61598</td>\n",
       "      <td>317</td>\n",
       "      <td>EKVGQVIRVICILSHPIKNTNDANSCQIIIPQNQVNRKSDIYVCMI...</td>\n",
       "      <td>Oxidation PTM</td>\n",
       "      <td>S-nitrosylation</td>\n",
       "      <td>28710281</td>\n",
       "      <td>Ischemia heart cells</td>\n",
       "      <td>Thiol isotope labeling</td>\n",
       "      <td>SNOxICAT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>Q61598</td>\n",
       "      <td>414</td>\n",
       "      <td>PKDLGTDSQIFISRAYDATTHFETTCDDIKDIYKRMTGSEFDFEEM...</td>\n",
       "      <td>Oxidation PTM</td>\n",
       "      <td>S-nitrosylation</td>\n",
       "      <td>28710281</td>\n",
       "      <td>Ischemia heart cells</td>\n",
       "      <td>Thiol isotope labeling</td>\n",
       "      <td>SNOxICAT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>Q61598</td>\n",
       "      <td>302</td>\n",
       "      <td>CKQLICDPSYVKDRVEKVGQVIRVICILSHPIKNTNDANSCQIIIP...</td>\n",
       "      <td>Oxidation PTM</td>\n",
       "      <td>S-nitrosylation</td>\n",
       "      <td>28710281</td>\n",
       "      <td>Ischemia heart cells</td>\n",
       "      <td>Thiol isotope labeling</td>\n",
       "      <td>SNOxICAT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>Q9QXS1</td>\n",
       "      <td>4078</td>\n",
       "      <td>LQLQEGLTSIEEVTKNLQKFLEGTSCIAGVFVDATKERLSVYQAMK...</td>\n",
       "      <td>Oxidation PTM</td>\n",
       "      <td>S-nitrosylation</td>\n",
       "      <td>28710281</td>\n",
       "      <td>Ischemia heart cells</td>\n",
       "      <td>Thiol isotope labeling</td>\n",
       "      <td>SNOxICAT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>Q9QXS1</td>\n",
       "      <td>958</td>\n",
       "      <td>AKAIVQLKPRNPAHPVRGHVPLIAVCDYKQVEVTVHKGDQCQLVGP...</td>\n",
       "      <td>Oxidation PTM</td>\n",
       "      <td>S-nitrosylation</td>\n",
       "      <td>28710281</td>\n",
       "      <td>Ischemia heart cells</td>\n",
       "      <td>Thiol isotope labeling</td>\n",
       "      <td>SNOxICAT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>Q9QXS1</td>\n",
       "      <td>541</td>\n",
       "      <td>KEWGKLHVAILEREKQLRSEFERLECLQRIVSKLQMEAGLCEEQLN...</td>\n",
       "      <td>Oxidation PTM</td>\n",
       "      <td>S-nitrosylation</td>\n",
       "      <td>28710281</td>\n",
       "      <td>Ischemia heart cells</td>\n",
       "      <td>Thiol isotope labeling</td>\n",
       "      <td>SNOxICAT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>Q9QXS1</td>\n",
       "      <td>4461</td>\n",
       "      <td>VSITEAMHRNLVDNITGQRLLEAQACTGGIIDPSTGERFPVTEAVN...</td>\n",
       "      <td>Oxidation PTM</td>\n",
       "      <td>S-nitrosylation</td>\n",
       "      <td>28710281</td>\n",
       "      <td>Ischemia heart cells</td>\n",
       "      <td>Thiol isotope labeling</td>\n",
       "      <td>SNOxICAT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>Q9QXS1</td>\n",
       "      <td>1386</td>\n",
       "      <td>ADPLSAWLQDAKRRQEQIQAVPIANCQAAREQLRQEKALLEEIERH...</td>\n",
       "      <td>Oxidation PTM</td>\n",
       "      <td>S-nitrosylation</td>\n",
       "      <td>28710281</td>\n",
       "      <td>Ischemia heart cells</td>\n",
       "      <td>Thiol isotope labeling</td>\n",
       "      <td>SNOxICAT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>Q9QXS1</td>\n",
       "      <td>4581</td>\n",
       "      <td>LQRGTVDARTAQKLRDVSAYSKYLTCPKTKLKISYKDALDRSMVEE...</td>\n",
       "      <td>Oxidation PTM</td>\n",
       "      <td>S-nitrosylation</td>\n",
       "      <td>28710281</td>\n",
       "      <td>Ischemia heart cells</td>\n",
       "      <td>Thiol isotope labeling</td>\n",
       "      <td>SNOxICAT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>P11983</td>\n",
       "      <td>76</td>\n",
       "      <td>DVTITNDGATILKLLEVEHPAAKVLCELADLQDKEVGDGTTSVVII...</td>\n",
       "      <td>Oxidation PTM</td>\n",
       "      <td>S-nitrosylation</td>\n",
       "      <td>28710281</td>\n",
       "      <td>Ischemia heart cells</td>\n",
       "      <td>Thiol isotope labeling</td>\n",
       "      <td>SNOxICAT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Organism Uniprot_AC  position  \\\n",
       "0  Mus musculus     Q61598       317   \n",
       "1  Mus musculus     Q61598       414   \n",
       "2  Mus musculus     Q61598       302   \n",
       "3  Mus musculus     Q9QXS1      4078   \n",
       "4  Mus musculus     Q9QXS1       958   \n",
       "5  Mus musculus     Q9QXS1       541   \n",
       "6  Mus musculus     Q9QXS1      4461   \n",
       "7  Mus musculus     Q9QXS1      1386   \n",
       "8  Mus musculus     Q9QXS1      4581   \n",
       "9  Mus musculus     P11983        76   \n",
       "\n",
       "                                        standard_seq   PTM_Catalogy  \\\n",
       "0  EKVGQVIRVICILSHPIKNTNDANSCQIIIPQNQVNRKSDIYVCMI...  Oxidation PTM   \n",
       "1  PKDLGTDSQIFISRAYDATTHFETTCDDIKDIYKRMTGSEFDFEEM...  Oxidation PTM   \n",
       "2  CKQLICDPSYVKDRVEKVGQVIRVICILSHPIKNTNDANSCQIIIP...  Oxidation PTM   \n",
       "3  LQLQEGLTSIEEVTKNLQKFLEGTSCIAGVFVDATKERLSVYQAMK...  Oxidation PTM   \n",
       "4  AKAIVQLKPRNPAHPVRGHVPLIAVCDYKQVEVTVHKGDQCQLVGP...  Oxidation PTM   \n",
       "5  KEWGKLHVAILEREKQLRSEFERLECLQRIVSKLQMEAGLCEEQLN...  Oxidation PTM   \n",
       "6  VSITEAMHRNLVDNITGQRLLEAQACTGGIIDPSTGERFPVTEAVN...  Oxidation PTM   \n",
       "7  ADPLSAWLQDAKRRQEQIQAVPIANCQAAREQLRQEKALLEEIERH...  Oxidation PTM   \n",
       "8  LQRGTVDARTAQKLRDVSAYSKYLTCPKTKLKISYKDALDRSMVEE...  Oxidation PTM   \n",
       "9  DVTITNDGATILKLLEVEHPAAKVLCELADLQDKEVGDGTTSVVII...  Oxidation PTM   \n",
       "\n",
       "          PTM_Type       ref             cell_line Identification_Strategy  \\\n",
       "0  S-nitrosylation  28710281  Ischemia heart cells  Thiol isotope labeling   \n",
       "1  S-nitrosylation  28710281  Ischemia heart cells  Thiol isotope labeling   \n",
       "2  S-nitrosylation  28710281  Ischemia heart cells  Thiol isotope labeling   \n",
       "3  S-nitrosylation  28710281  Ischemia heart cells  Thiol isotope labeling   \n",
       "4  S-nitrosylation  28710281  Ischemia heart cells  Thiol isotope labeling   \n",
       "5  S-nitrosylation  28710281  Ischemia heart cells  Thiol isotope labeling   \n",
       "6  S-nitrosylation  28710281  Ischemia heart cells  Thiol isotope labeling   \n",
       "7  S-nitrosylation  28710281  Ischemia heart cells  Thiol isotope labeling   \n",
       "8  S-nitrosylation  28710281  Ischemia heart cells  Thiol isotope labeling   \n",
       "9  S-nitrosylation  28710281  Ischemia heart cells  Thiol isotope labeling   \n",
       "\n",
       "  Identification_Technique  \n",
       "0                 SNOxICAT  \n",
       "1                 SNOxICAT  \n",
       "2                 SNOxICAT  \n",
       "3                 SNOxICAT  \n",
       "4                 SNOxICAT  \n",
       "5                 SNOxICAT  \n",
       "6                 SNOxICAT  \n",
       "7                 SNOxICAT  \n",
       "8                 SNOxICAT  \n",
       "9                 SNOxICAT  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a50d5b14",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['LID'] =  df['position'].astype(str) + df['standard_seq'].apply(lambda x: \"-\" + x[0:5]+\"-\" ) + df['PTM_Type']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "6c894368",
   "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>Organism</th>\n",
       "      <th>Uniprot_AC</th>\n",
       "      <th>position</th>\n",
       "      <th>standard_seq</th>\n",
       "      <th>PTM_Catalogy</th>\n",
       "      <th>PTM_Type</th>\n",
       "      <th>ref</th>\n",
       "      <th>cell_line</th>\n",
       "      <th>Identification_Strategy</th>\n",
       "      <th>Identification_Technique</th>\n",
       "      <th>LID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>Q61598</td>\n",
       "      <td>317</td>\n",
       "      <td>EKVGQVIRVICILSHPIKNTNDANSCQIIIPQNQVNRKSDIYVCMI...</td>\n",
       "      <td>Oxidation PTM</td>\n",
       "      <td>S-nitrosylation</td>\n",
       "      <td>28710281</td>\n",
       "      <td>Ischemia heart cells</td>\n",
       "      <td>Thiol isotope labeling</td>\n",
       "      <td>SNOxICAT</td>\n",
       "      <td>317-EKVGQ-S-nitrosylation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>Q61598</td>\n",
       "      <td>414</td>\n",
       "      <td>PKDLGTDSQIFISRAYDATTHFETTCDDIKDIYKRMTGSEFDFEEM...</td>\n",
       "      <td>Oxidation PTM</td>\n",
       "      <td>S-nitrosylation</td>\n",
       "      <td>28710281</td>\n",
       "      <td>Ischemia heart cells</td>\n",
       "      <td>Thiol isotope labeling</td>\n",
       "      <td>SNOxICAT</td>\n",
       "      <td>414-PKDLG-S-nitrosylation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>Q61598</td>\n",
       "      <td>302</td>\n",
       "      <td>CKQLICDPSYVKDRVEKVGQVIRVICILSHPIKNTNDANSCQIIIP...</td>\n",
       "      <td>Oxidation PTM</td>\n",
       "      <td>S-nitrosylation</td>\n",
       "      <td>28710281</td>\n",
       "      <td>Ischemia heart cells</td>\n",
       "      <td>Thiol isotope labeling</td>\n",
       "      <td>SNOxICAT</td>\n",
       "      <td>302-CKQLI-S-nitrosylation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>Q9QXS1</td>\n",
       "      <td>4078</td>\n",
       "      <td>LQLQEGLTSIEEVTKNLQKFLEGTSCIAGVFVDATKERLSVYQAMK...</td>\n",
       "      <td>Oxidation PTM</td>\n",
       "      <td>S-nitrosylation</td>\n",
       "      <td>28710281</td>\n",
       "      <td>Ischemia heart cells</td>\n",
       "      <td>Thiol isotope labeling</td>\n",
       "      <td>SNOxICAT</td>\n",
       "      <td>4078-LQLQE-S-nitrosylation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>Q9QXS1</td>\n",
       "      <td>958</td>\n",
       "      <td>AKAIVQLKPRNPAHPVRGHVPLIAVCDYKQVEVTVHKGDQCQLVGP...</td>\n",
       "      <td>Oxidation PTM</td>\n",
       "      <td>S-nitrosylation</td>\n",
       "      <td>28710281</td>\n",
       "      <td>Ischemia heart cells</td>\n",
       "      <td>Thiol isotope labeling</td>\n",
       "      <td>SNOxICAT</td>\n",
       "      <td>958-AKAIV-S-nitrosylation</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3968</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>D6REF3</td>\n",
       "      <td>98</td>\n",
       "      <td>KGGEDKLKMIREYRQMVETELKLICCDILDVLDKHLIPAANTGWQK...</td>\n",
       "      <td>Lipid PTM</td>\n",
       "      <td>S-palmitoylation</td>\n",
       "      <td>28680068</td>\n",
       "      <td>Forebrain cells</td>\n",
       "      <td>Thiol blocking</td>\n",
       "      <td>ABE</td>\n",
       "      <td>98-KGGED-S-palmitoylation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3969</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>D3Z656</td>\n",
       "      <td>1091</td>\n",
       "      <td>LPQHLQPSSSSGLGTSPSSSPRTSPCQSPTVPEYSAPSLPIRPSRA...</td>\n",
       "      <td>Lipid PTM</td>\n",
       "      <td>S-palmitoylation</td>\n",
       "      <td>28680068</td>\n",
       "      <td>Forebrain cells</td>\n",
       "      <td>Thiol blocking</td>\n",
       "      <td>ABE</td>\n",
       "      <td>1091-LPQHL-S-palmitoylation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3970</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>E9Q7S0</td>\n",
       "      <td>1051</td>\n",
       "      <td>LPQHLQPSSSSGLGTSPSSSPRTSPCQSPTVPEYSAPSLPIRPSRA...</td>\n",
       "      <td>Lipid PTM</td>\n",
       "      <td>S-palmitoylation</td>\n",
       "      <td>28680068</td>\n",
       "      <td>Forebrain cells</td>\n",
       "      <td>Thiol blocking</td>\n",
       "      <td>ABE</td>\n",
       "      <td>1051-LPQHL-S-palmitoylation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3971</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>F7BQW7</td>\n",
       "      <td>1066</td>\n",
       "      <td>LPQHLQPSSSSGLGTSPSSSPRTSPCQSPTVPEYSAPSLPIRPSRA...</td>\n",
       "      <td>Lipid PTM</td>\n",
       "      <td>S-palmitoylation</td>\n",
       "      <td>28680068</td>\n",
       "      <td>Forebrain cells</td>\n",
       "      <td>Thiol blocking</td>\n",
       "      <td>ABE</td>\n",
       "      <td>1066-LPQHL-S-palmitoylation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3972</th>\n",
       "      <td>Mus musculus</td>\n",
       "      <td>Q8CHC4</td>\n",
       "      <td>1051</td>\n",
       "      <td>LPQHLQPSSSSGLGTSPSSSPRTSPCQSPTVPEYSAPSLPIRPSRA...</td>\n",
       "      <td>Lipid PTM</td>\n",
       "      <td>S-palmitoylation</td>\n",
       "      <td>28680068</td>\n",
       "      <td>Forebrain cells</td>\n",
       "      <td>Thiol blocking</td>\n",
       "      <td>ABE</td>\n",
       "      <td>1051-LPQHL-S-palmitoylation</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>59623 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Organism Uniprot_AC  position  \\\n",
       "0     Mus musculus     Q61598       317   \n",
       "1     Mus musculus     Q61598       414   \n",
       "2     Mus musculus     Q61598       302   \n",
       "3     Mus musculus     Q9QXS1      4078   \n",
       "4     Mus musculus     Q9QXS1       958   \n",
       "...            ...        ...       ...   \n",
       "3968  Mus musculus     D6REF3        98   \n",
       "3969  Mus musculus     D3Z656      1091   \n",
       "3970  Mus musculus     E9Q7S0      1051   \n",
       "3971  Mus musculus     F7BQW7      1066   \n",
       "3972  Mus musculus     Q8CHC4      1051   \n",
       "\n",
       "                                           standard_seq   PTM_Catalogy  \\\n",
       "0     EKVGQVIRVICILSHPIKNTNDANSCQIIIPQNQVNRKSDIYVCMI...  Oxidation PTM   \n",
       "1     PKDLGTDSQIFISRAYDATTHFETTCDDIKDIYKRMTGSEFDFEEM...  Oxidation PTM   \n",
       "2     CKQLICDPSYVKDRVEKVGQVIRVICILSHPIKNTNDANSCQIIIP...  Oxidation PTM   \n",
       "3     LQLQEGLTSIEEVTKNLQKFLEGTSCIAGVFVDATKERLSVYQAMK...  Oxidation PTM   \n",
       "4     AKAIVQLKPRNPAHPVRGHVPLIAVCDYKQVEVTVHKGDQCQLVGP...  Oxidation PTM   \n",
       "...                                                 ...            ...   \n",
       "3968  KGGEDKLKMIREYRQMVETELKLICCDILDVLDKHLIPAANTGWQK...      Lipid PTM   \n",
       "3969  LPQHLQPSSSSGLGTSPSSSPRTSPCQSPTVPEYSAPSLPIRPSRA...      Lipid PTM   \n",
       "3970  LPQHLQPSSSSGLGTSPSSSPRTSPCQSPTVPEYSAPSLPIRPSRA...      Lipid PTM   \n",
       "3971  LPQHLQPSSSSGLGTSPSSSPRTSPCQSPTVPEYSAPSLPIRPSRA...      Lipid PTM   \n",
       "3972  LPQHLQPSSSSGLGTSPSSSPRTSPCQSPTVPEYSAPSLPIRPSRA...      Lipid PTM   \n",
       "\n",
       "              PTM_Type       ref             cell_line  \\\n",
       "0      S-nitrosylation  28710281  Ischemia heart cells   \n",
       "1      S-nitrosylation  28710281  Ischemia heart cells   \n",
       "2      S-nitrosylation  28710281  Ischemia heart cells   \n",
       "3      S-nitrosylation  28710281  Ischemia heart cells   \n",
       "4      S-nitrosylation  28710281  Ischemia heart cells   \n",
       "...                ...       ...                   ...   \n",
       "3968  S-palmitoylation  28680068       Forebrain cells   \n",
       "3969  S-palmitoylation  28680068       Forebrain cells   \n",
       "3970  S-palmitoylation  28680068       Forebrain cells   \n",
       "3971  S-palmitoylation  28680068       Forebrain cells   \n",
       "3972  S-palmitoylation  28680068       Forebrain cells   \n",
       "\n",
       "     Identification_Strategy Identification_Technique  \\\n",
       "0     Thiol isotope labeling                 SNOxICAT   \n",
       "1     Thiol isotope labeling                 SNOxICAT   \n",
       "2     Thiol isotope labeling                 SNOxICAT   \n",
       "3     Thiol isotope labeling                 SNOxICAT   \n",
       "4     Thiol isotope labeling                 SNOxICAT   \n",
       "...                      ...                      ...   \n",
       "3968          Thiol blocking                      ABE   \n",
       "3969          Thiol blocking                      ABE   \n",
       "3970          Thiol blocking                      ABE   \n",
       "3971          Thiol blocking                      ABE   \n",
       "3972          Thiol blocking                      ABE   \n",
       "\n",
       "                              LID  \n",
       "0       317-EKVGQ-S-nitrosylation  \n",
       "1       414-PKDLG-S-nitrosylation  \n",
       "2       302-CKQLI-S-nitrosylation  \n",
       "3      4078-LQLQE-S-nitrosylation  \n",
       "4       958-AKAIV-S-nitrosylation  \n",
       "...                           ...  \n",
       "3968    98-KGGED-S-palmitoylation  \n",
       "3969  1091-LPQHL-S-palmitoylation  \n",
       "3970  1051-LPQHL-S-palmitoylation  \n",
       "3971  1066-LPQHL-S-palmitoylation  \n",
       "3972  1051-LPQHL-S-palmitoylation  \n",
       "\n",
       "[59623 rows x 11 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "41d69fdf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "All_PTM_Type = df['PTM_Type'].unique()\n",
    "len(All_PTM_Type)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "e7bc423d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item = df['cell_line'].unique()\n",
    "len(item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "74d68ac4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "27"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item = df['ref'].unique()\n",
    "len(item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "784e1da7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save dataframe as aac_kg.csv\n",
    "df.to_csv(\"data/aac-data/aac_kg.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "95d0ba82",
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert dataframe to dictionary\n",
    "df_dict = df.to_dict(orient='records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "9f5ccb33",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Organism': 'Mus musculus',\n",
       " 'Uniprot_AC': 'Q61598',\n",
       " 'position': 317,\n",
       " 'standard_seq': 'EKVGQVIRVICILSHPIKNTNDANSCQIIIPQNQVNRKSDIYVCMISFAHN',\n",
       " 'PTM_Catalogy': 'Oxidation PTM',\n",
       " 'PTM_Type': 'S-nitrosylation',\n",
       " 'ref': '28710281',\n",
       " 'cell_line': 'Ischemia heart cells',\n",
       " 'Identification_Strategy': 'Thiol isotope labeling',\n",
       " 'Identification_Technique': 'SNOxICAT'}"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dict[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "fdce8e9b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "59623"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "f0c6cc6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#save df_dict to json file\n",
    "with open('data/aac-data/data.json', 'w') as f:\n",
    "    json.dump(df_dict, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75358919",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0cf02196",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  },
  "vscode": {
   "interpreter": {
    "hash": "a46d3808ce7f3f92d20203132b60af20d084112e7b3027b56a392bdfd2bca4cb"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}