code_01_identify_CyTOF_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Import library\n",
    "import mysql.connector as MC\n",
    "import pandas as pd\n",
    "import re\n",
    "import math\n",
    "import matplotlib.pyplot as plt\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### setup connection #####\n",
    "mydb = MC.connect(\n",
    "                  host=\"your own immport data base\",\n",
    "                  user=\"your user name\",\n",
    "                  database = \"the immport database\",\n",
    "                  passwd=\"your very secure password\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### find all the CyTOF data #####\n",
    "mycursor = mydb.cursor()\n",
    "cytof_files = (\"SELECT * FROM  file_info \" +\n",
    "               \"WHERE detail LIKE 'CyTOF%'\")\n",
    "\n",
    "mycursor.execute(cytof_files)\n",
    "myresult = mycursor.fetchall()\n",
    "cytof_files = pd.DataFrame(myresult,\n",
    "                           columns=mycursor.column_names)\n",
    "print(cytof_files.shape)\n",
    "display(cytof_files.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### Join with expsample_2_file_info #####\n",
    "mycursor = mydb.cursor()\n",
    "qury = (\"SELECT * FROM  expsample_2_file_info;\")\n",
    "mycursor.execute(qury)\n",
    "myresult = mycursor.fetchall()\n",
    "expsample_2_file_info = pd.DataFrame(myresult,\n",
    "                           columns=mycursor.column_names)\n",
    "cytof_files = cytof_files.merge(expsample_2_file_info, how = \"left\",\n",
    "                                on=\"file_info_id\")\n",
    "cytof_files = cytof_files[['file_info_id','name','expsample_accession']]\n",
    "print(cytof_files.shape)\n",
    "display(cytof_files.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### Join with biosample accession #####\n",
    "mycursor = mydb.cursor()\n",
    "qury = (\"SELECT * FROM  expsample_2_biosample;\")\n",
    "mycursor.execute(qury)\n",
    "myresult = mycursor.fetchall()\n",
    "expsample_2_biosample = pd.DataFrame(myresult,\n",
    "                                     columns=mycursor.column_names)\n",
    "cytof_files = cytof_files.merge(expsample_2_biosample,how = \"left\",\n",
    "                                on=\"expsample_accession\")\n",
    "print(cytof_files.shape)\n",
    "display(cytof_files.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### Join with biosample information #####\n",
    "mycursor = mydb.cursor()\n",
    "qury = (\"SELECT biosample_accession,study_accession,study_time_collected,\"+\n",
    "        \"subject_accession,type,subtype FROM biosample;\")\n",
    "mycursor.execute(qury)\n",
    "myresult = mycursor.fetchall()\n",
    "biosample = pd.DataFrame(myresult,columns=mycursor.column_names)\n",
    "cytof_files = cytof_files.merge(biosample,how = \"left\",\n",
    "                                on=\"biosample_accession\")\n",
    "cytof_files = cytof_files[cytof_files.study_time_collected==0.0]\n",
    "\n",
    "print(cytof_files.shape)\n",
    "display(cytof_files.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### Join with subject information #####\n",
    "mycursor = mydb.cursor()\n",
    "qury = (\"SELECT subject_accession,gender,race \"+\n",
    "        \"FROM subject;\")\n",
    "mycursor.execute(qury)\n",
    "myresult = mycursor.fetchall()\n",
    "subject = pd.DataFrame(myresult,columns=mycursor.column_names)\n",
    "cytof_files = cytof_files.merge(subject,how = \"left\",\n",
    "                                on=\"subject_accession\")\n",
    "print(cytof_files.shape)\n",
    "display(cytof_files.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### Join with age information #####\n",
    "mycursor = mydb.cursor()\n",
    "\n",
    "table1 = (\"(SELECT arm_accession,subject_accession,min_subject_age as age,subject_phenotype \"+\n",
    "        \"FROM arm_2_subject) AS t1\")\n",
    "table2 = (\"(SELECT arm_accession, study_accession \"+\n",
    "        \"FROM arm_or_cohort) AS t2\")\n",
    "\n",
    "qury = (\"SELECT * FROM \"+\n",
    "       table1+\" INNER JOIN \"+ table2+\" USING (arm_accession)\")\n",
    "\n",
    "mycursor.execute(qury)\n",
    "myresult = mycursor.fetchall()\n",
    "arm_2_subject = pd.DataFrame(myresult,columns=mycursor.column_names)\n",
    "\n",
    "cytof_files = cytof_files.merge(arm_2_subject,how = \"left\",\n",
    "                                on=[\"subject_accession\",\"study_accession\"])\n",
    "print(cytof_files.shape)\n",
    "display(cytof_files.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### Join with cytokine information #####\n",
    "\n",
    "mycursor = mydb.cursor()\n",
    "table1 = (\"(SELECT analyte_preferred as analyte_accession,\"+\n",
    "        \"concentration_value_reported as cytokine_level,\"+\n",
    "        #\"experiment_accession,\"+\n",
    "        \"subject_accession, study_time_collected, study_accession \"+\n",
    "        \"FROM mbaa_result) as t1\")\n",
    "\n",
    "table2 = (\"(SELECT analyte_accession, \"+\n",
    "        \"gene_symbol as analyte \"+\n",
    "        \"FROM lk_analyte) as t2\")\n",
    "\n",
    "qury = (\"SELECT * FROM \"+\n",
    "       table1+\" INNER JOIN \"+table2 + \n",
    "        \" USING (analyte_accession)\")\n",
    "\n",
    "mycursor.execute(qury)\n",
    "myresult = mycursor.fetchall()\n",
    "mbaa_result = pd.DataFrame(myresult,columns=mycursor.column_names)\n",
    "mbaa_result.cytokine_level= pd.to_numeric(mbaa_result.cytokine_level,\n",
    "                                          errors='coerce')\n",
    "mbaa_result = mbaa_result[mbaa_result.study_time_collected==0.0]\n",
    "index1 = list(mbaa_result.columns)\n",
    "index1.remove(\"cytokine_level\")\n",
    "mbaa_result = mbaa_result.groupby(index1).agg('mean').reset_index()\n",
    "display(mbaa_result.head())\n",
    "cytof_files = cytof_files.merge(mbaa_result,how = \"left\",\n",
    "                                on=[\"subject_accession\",\n",
    "                                    \"study_time_collected\",\n",
    "                                     \"study_accession\"])\n",
    "cytof_files = cytof_files.drop(columns=\"analyte_accession\")\n",
    "print(cytof_files.shape)\n",
    "display(cytof_files.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### reshape the MBAA information #####\n",
    "index1 = list(cytof_files.columns)\n",
    "index1.remove(\"cytokine_level\")\n",
    "\n",
    "cytof_files = (cytof_files.set_index(index1).cytokine_level.\n",
    "               unstack(\"analyte\").rename_axis(None,1).reset_index())\n",
    "\n",
    "cols = [c for c in cytof_files.columns if c==c]\n",
    "cytof_files=cytof_files[cols]\n",
    "cytof_files.columns = [c.replace(' ', '_') for c in cytof_files.columns]\n",
    "#display(cytof_files.head())\n",
    "print(cytof_files.shape)\n",
    "display(cytof_files.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### Join with CMV information #####\n",
    "mycursor = mydb.cursor()\n",
    "table1 = (\"(SELECT * FROM lab_test WHERE \"+\n",
    "        \"name_reported IN ('EBV ab','CMV ab')) AS t1 \")\n",
    "table2 = (\"(SELECT biosample_accession, subject_accession, study_accession FROM \" +\n",
    "        \"biosample) AS t2 \")\n",
    "qury = (\"SELECT name_reported as virus_Ab_type, \"+\n",
    "        \" study_accession, \" +\n",
    "        \"result_value_preferred as virus_titer, subject_accession  FROM \"+\n",
    "       table1+\" INNER JOIN \"+ table2+\" USING (biosample_accession)\")\n",
    "mycursor.execute(qury)\n",
    "myresult = mycursor.fetchall()\n",
    "CMV_result = pd.DataFrame(myresult,columns=mycursor.column_names)\n",
    "cytof_files = cytof_files.merge(CMV_result,how = \"left\",\n",
    "                                on=[\"subject_accession\",\"study_accession\"])\n",
    "print(cytof_files.shape)\n",
    "display(cytof_files.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### reshape the CMV information #####\n",
    "index1 = list(cytof_files.columns)\n",
    "index1.remove(\"virus_titer\")\n",
    "\n",
    "cytof_files = (cytof_files.set_index(index1).virus_titer.\n",
    "               unstack(\"virus_Ab_type\").rename_axis(None,1).reset_index())\n",
    "cols = [c for c in cytof_files.columns if c==c]\n",
    "cytof_files=cytof_files[cols]\n",
    "cytof_files.columns = [c.replace(' ', '_') for c in cytof_files.columns]\n",
    "print(cytof_files.shape)\n",
    "display(cytof_files.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### prepare HAI information #####\n",
    "mycursor = mydb.cursor()\n",
    "qury = (\"SELECT study_accession, \"+\n",
    "        #\"virus_strain_preferred, \"+\n",
    "        \"subject_accession, study_time_collected, value_preferred \"+\n",
    "        \"FROM hai_result\")\n",
    "\n",
    "mycursor.execute(qury)\n",
    "myresult = mycursor.fetchall()\n",
    "HAI_result = pd.DataFrame(myresult,columns=mycursor.column_names)\n",
    "HAI_result.value_preferred = HAI_result.value_preferred.apply(math.log2)\n",
    "\n",
    "plt.hist(HAI_result.value_preferred)\n",
    "\n",
    "index1 = list(HAI_result.columns)\n",
    "index1.remove(\"value_preferred\")\n",
    "print(index1)\n",
    "\n",
    "HAI_result = HAI_result.groupby(index1).agg('mean').reset_index()\n",
    "HAI_result = HAI_result[HAI_result.study_time_collected.isin([0.0,28.0])]\n",
    "HAI_result = (HAI_result.set_index(index1).value_preferred.\n",
    "               unstack(\"study_time_collected\").rename_axis(None,1).reset_index())\n",
    "HAI_result.columns = [\"study_accession\",\"subject_accession\",\"HAI_0\",\"HAI_28\"]\n",
    "HAI_result['HAI_diff'] = HAI_result.HAI_28 - HAI_result.HAI_0\n",
    "display(HAI_result.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### join with HAI information #####\n",
    "\n",
    "cytof_files = cytof_files.drop(\"study_time_collected\",axis=1)\n",
    "cytof_files = cytof_files.merge(HAI_result,how = \"left\",\n",
    "                                on=[\"subject_accession\",\n",
    "                                     \"study_accession\"])\n",
    "display(cytof_files.head())\n",
    "print(cytof_files.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### save to csv #####\n",
    "idx1 = list(cytof_files.columns).index(\"CMV_Ab\")\n",
    "cytof_files = cytof_files.dropna(subset=cytof_files.columns[idx1:],\n",
    "                                 how=\"all\")\n",
    "r1 = [bool(re.search(\"fcs$|FCS$\", i)) for i in cytof_files.name]\n",
    "print(cytof_files.isna().sum())\n",
    "c1 = (cytof_files.isna().sum()<=236)\n",
    "cytof_files = cytof_files.loc[r1,c1]\n",
    "\n",
    "display(cytof_files.head())\n",
    "cytof_files.to_csv(\"result_01_cytof_file_info.csv\", \n",
    "                   encoding='utf-8', index=False)\n",
    "print(cytof_files.shape)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "CyTOF_DL",
   "language": "python",
   "name": "cytof_dl"
  },
  "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.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}