Processing transcriptomics data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/sanu/.local/lib/python2.7/site-packages/IPython/core/interactiveshell.py:2714: DtypeWarning: Columns (0,2,3,13,14) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv(\"/home/sanu/Documents/Scripts/git/Data/Soy/gmax_leaf_CO2_normExprn_1.txt\",sep=\"\\t\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.read_excel(\"/home/sanu/Documents/Scripts/git/Data/Soy/Transcriptomics_key.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "GSM1088939\n",
      "GSM1088940\n",
      "GSM1088948\n",
      "GSM1088949\n",
      "GSM1088950\n",
      "GSM1088954\n",
      "GSM1088955\n",
      "GSM1088956\n",
      "GSM1088963\n",
      "GSM1088964\n",
      "GSM209574\n",
      "GSM209575\n",
      "GSM209576\n",
      "GSM209577\n",
      "GSM209580\n",
      "GSM209581\n",
      "GSM209582\n",
      "GSM209583\n",
      "GSM209584\n",
      "GSM209585\n",
      "GSM209586\n",
      "GSM209587\n",
      "GSM209588\n",
      "GSM209590\n",
      "GSM209591\n",
      "GSM209592\n",
      "GSM209593\n",
      "GSM209594\n",
      "GSM209595\n",
      "GSM209596\n",
      "GSM209597\n",
      "GSM209598\n",
      "GSM209599\n",
      "GSM569808\n",
      "GSM569809\n",
      "GSM569810\n",
      "GSM569811\n",
      "GSM569812\n",
      "GSM569813\n",
      "GSM569814\n",
      "GSM569815\n",
      "GSM569816\n",
      "GSM569817\n",
      "GSM569818\n",
      "GSM569819\n",
      "GSM569820\n",
      "GSM569821\n",
      "GSM569822\n",
      "GSM569824\n",
      "GSM569825\n",
      "GSM569826\n",
      "GSM569827\n",
      "GSM569828\n",
      "GSM569829\n",
      "GSM643111\n",
      "GSM643112\n",
      "GSM643113\n",
      "GSM643114\n",
      "GSM643115\n",
      "GSM643116\n",
      "GSM643117\n",
      "GSM643118\n",
      "GSM643119\n",
      "GSM643120\n",
      "GSM643121\n",
      "GSM643123\n",
      "GSM643124\n",
      "GSM643125\n",
      "GSM643126\n",
      "GSM737244\n",
      "GSM737245\n",
      "GSM737246\n",
      "GSM737247\n",
      "GSM737248\n",
      "GSM737249\n",
      "GSM737250\n",
      "GSM737251\n",
      "GSM737252\n",
      "GSM737253\n",
      "GSM737254\n",
      "GSM737255\n",
      "GSM737256\n",
      "GSM737257\n",
      "GSM737258\n",
      "GSM737259\n",
      "GSM737260\n",
      "GSM737261\n",
      "GSM737262\n",
      "GSM737263\n",
      "GSM737264\n",
      "GSM737265\n",
      "GSM737266\n",
      "GSM737267\n",
      "GSM737268\n",
      "GSM737269\n",
      "GSM737270\n",
      "GSM737271\n",
      "GSM737272\n",
      "GSM737273\n",
      "GSM737274\n",
      "GSM737275\n",
      "GSM737276\n",
      "GSM737277\n",
      "GSM737278\n",
      "GSM737279\n",
      "GSM737280\n",
      "GSM737281\n",
      "GSM737282\n",
      "GSM737283\n",
      "GSM737284\n",
      "GSM737285\n",
      "GSM737286\n",
      "GSM737287\n",
      "GSM737288\n",
      "GSM737289\n",
      "GSM737290\n",
      "GSM737291\n",
      "GSM737292\n",
      "GSM737293\n",
      "GSM737294\n",
      "GSM737295\n",
      "GSM737296\n",
      "GSM737298\n",
      "GSM737299\n",
      "GSM737300\n",
      "GSM737301\n",
      "GSM737302\n",
      "GSM737303\n",
      "GSM737304\n",
      "GSM737305\n",
      "GSM737306\n",
      "GSM737307\n",
      "GSM737308\n",
      "GSM737309\n",
      "GSM737310\n",
      "GSM737311\n",
      "GSM737312\n",
      "GSM737313\n",
      "GSM737314\n",
      "GSM737315\n",
      "GSM737316\n",
      "GSM737317\n",
      "GSM737318\n",
      "GSM737319\n",
      "GSM737320\n",
      "GSM737321\n",
      "GSM737322\n",
      "GSM737323\n",
      "GSM737324\n",
      "GSM737325\n",
      "GSM737326\n",
      "GSM737327\n",
      "GSM737328\n",
      "GSM737329\n",
      "GSM737332\n",
      "GSM737333\n",
      "GSM737334\n",
      "GSM737335\n",
      "GSM737336\n",
      "GSM737337\n",
      "GSM737338\n",
      "GSM737339\n",
      "GSM737340\n",
      "GSM737341\n",
      "GSM737342\n",
      "GSM737343\n",
      "GSM737344\n",
      "GSM737346\n",
      "GSM737347\n",
      "GSM737348\n",
      "GSM737349\n",
      "GSM737350\n",
      "GSM737351\n",
      "GSM737352\n",
      "GSM737353\n",
      "GSM737354\n",
      "GSM737355\n",
      "GSM737356\n",
      "GSM737357\n",
      "GSM737358\n",
      "GSM737359\n",
      "GSM737360\n",
      "GSM737361\n",
      "GSM737362\n",
      "GSM737363\n",
      "GSM737364\n",
      "GSM737365\n",
      "GSM737366\n",
      "GSM737367\n",
      "GSM737369\n",
      "GSM868113\n",
      "GSM868114\n",
      "GSM868115\n",
      "GSM868116\n",
      "GSM868117\n",
      "GSM868118\n",
      "GSM868119\n",
      "GSM868121\n",
      "GSM868122\n",
      "GSM868123\n",
      "GSM868124\n",
      "GSM868125\n",
      "GSM868126\n",
      "GSM868127\n",
      "GSM868128\n",
      "GSM868129\n",
      "GSM868130\n",
      "GSM868131\n",
      "GSM868132\n",
      "GSM868133\n",
      "GSM868134\n",
      "GSM868135\n",
      "GSM868136\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['-',\n",
       " 'GSM209574.CEL',\n",
       " 'GSM209575.CEL',\n",
       " 'GSM209576.CEL',\n",
       " 'GSM209577.CEL',\n",
       " '-',\n",
       " '-',\n",
       " 'GSM209580.CEL',\n",
       " 'GSM209581.CEL',\n",
       " 'GSM209582.CEL',\n",
       " 'GSM209583.CEL',\n",
       " 'GSM209584.CEL',\n",
       " 'GSM209585.CEL',\n",
       " 'GSM209586.CEL',\n",
       " 'GSM209587.CEL',\n",
       " 'GSM209588.CEL',\n",
       " '-',\n",
       " 'GSM209590.CEL',\n",
       " 'GSM209591.CEL',\n",
       " 'GSM209592.CEL',\n",
       " 'GSM209593.CEL',\n",
       " 'GSM209594.CEL',\n",
       " 'GSM209595.CEL',\n",
       " 'GSM209596.CEL',\n",
       " 'GSM209597.CEL',\n",
       " 'GSM209598.CEL',\n",
       " 'GSM209599.CEL',\n",
       " 'GSM569808.CEL',\n",
       " 'GSM569809.CEL',\n",
       " 'GSM569810.CEL',\n",
       " 'GSM569811.CEL',\n",
       " 'GSM569812.CEL',\n",
       " 'GSM569813.CEL',\n",
       " 'GSM569814.CEL',\n",
       " 'GSM569815.CEL',\n",
       " 'GSM569816.CEL',\n",
       " 'GSM569817.CEL',\n",
       " 'GSM569818.CEL',\n",
       " 'GSM569819.CEL',\n",
       " 'GSM569820.CEL',\n",
       " 'GSM569821.CEL',\n",
       " 'GSM569822.CEL',\n",
       " '-',\n",
       " 'GSM569824.CEL',\n",
       " 'GSM569825.CEL',\n",
       " 'GSM569826.CEL',\n",
       " 'GSM569827.CEL',\n",
       " 'GSM569828.CEL',\n",
       " 'GSM569829.CEL',\n",
       " 'GSM643111.CEL',\n",
       " 'GSM643112.CEL',\n",
       " 'GSM643113.CEL',\n",
       " 'GSM643114.CEL',\n",
       " 'GSM643115.CEL',\n",
       " 'GSM643116.CEL',\n",
       " 'GSM643117.CEL',\n",
       " 'GSM643118.CEL',\n",
       " 'GSM643119.CEL',\n",
       " 'GSM643120.CEL',\n",
       " 'GSM643121.CEL',\n",
       " '-',\n",
       " 'GSM643123.CEL',\n",
       " 'GSM643124.CEL',\n",
       " 'GSM643125.CEL',\n",
       " 'GSM643126.CEL',\n",
       " 'GSM737244.CEL',\n",
       " 'GSM737245.CEL',\n",
       " 'GSM737246.CEL',\n",
       " 'GSM737247.CEL',\n",
       " 'GSM737248.CEL',\n",
       " 'GSM737249.CEL',\n",
       " 'GSM737250.CEL',\n",
       " 'GSM737251.CEL',\n",
       " 'GSM737252.CEL',\n",
       " 'GSM737253.CEL',\n",
       " 'GSM737254.CEL',\n",
       " 'GSM737255.CEL',\n",
       " 'GSM737256.CEL',\n",
       " 'GSM737257.CEL',\n",
       " 'GSM737258.CEL',\n",
       " 'GSM737259.CEL',\n",
       " 'GSM737260.CEL',\n",
       " 'GSM737261.CEL',\n",
       " 'GSM737262.CEL',\n",
       " 'GSM737263.CEL',\n",
       " 'GSM737264.CEL',\n",
       " 'GSM737265.CEL',\n",
       " 'GSM737266.CEL',\n",
       " 'GSM737267.CEL',\n",
       " 'GSM737268.CEL',\n",
       " 'GSM737269.CEL',\n",
       " 'GSM737270.CEL',\n",
       " 'GSM737271.CEL',\n",
       " 'GSM737272.CEL',\n",
       " 'GSM737273.CEL',\n",
       " 'GSM737274.CEL',\n",
       " 'GSM737275.CEL',\n",
       " 'GSM737276.CEL',\n",
       " 'GSM737277.CEL',\n",
       " 'GSM737278.CEL',\n",
       " 'GSM737279.CEL',\n",
       " 'GSM737280.CEL',\n",
       " 'GSM737281.CEL',\n",
       " 'GSM737282.CEL',\n",
       " 'GSM737283.CEL',\n",
       " 'GSM737284.CEL',\n",
       " 'GSM737285.CEL',\n",
       " 'GSM737286.CEL',\n",
       " 'GSM737287.CEL',\n",
       " 'GSM737288.CEL',\n",
       " 'GSM737289.CEL',\n",
       " 'GSM737290.CEL',\n",
       " 'GSM737291.CEL',\n",
       " 'GSM737292.CEL',\n",
       " 'GSM737293.CEL',\n",
       " 'GSM737294.CEL',\n",
       " 'GSM737295.CEL',\n",
       " 'GSM737296.CEL',\n",
       " '-',\n",
       " 'GSM737298.CEL',\n",
       " 'GSM737299.CEL',\n",
       " 'GSM737300.CEL',\n",
       " 'GSM737301.CEL',\n",
       " 'GSM737302.CEL',\n",
       " 'GSM737303.CEL',\n",
       " 'GSM737304.CEL',\n",
       " 'GSM737305.CEL',\n",
       " 'GSM737306.CEL',\n",
       " 'GSM737307.CEL',\n",
       " 'GSM737308.CEL',\n",
       " 'GSM737309.CEL',\n",
       " 'GSM737310.CEL',\n",
       " 'GSM737311.CEL',\n",
       " 'GSM737312.CEL',\n",
       " 'GSM737313.CEL',\n",
       " 'GSM737314.CEL',\n",
       " 'GSM737315.CEL',\n",
       " 'GSM737316.CEL',\n",
       " 'GSM737317.CEL',\n",
       " 'GSM737318.CEL',\n",
       " 'GSM737319.CEL',\n",
       " 'GSM737320.CEL',\n",
       " 'GSM737321.CEL',\n",
       " 'GSM737322.CEL',\n",
       " 'GSM737323.CEL',\n",
       " 'GSM737324.CEL',\n",
       " 'GSM737325.CEL',\n",
       " 'GSM737326.CEL',\n",
       " 'GSM737327.CEL',\n",
       " 'GSM737328.CEL',\n",
       " 'GSM737329.CEL',\n",
       " '-',\n",
       " '-',\n",
       " 'GSM737332.CEL',\n",
       " 'GSM737333.CEL',\n",
       " 'GSM737334.CEL',\n",
       " 'GSM737335.CEL',\n",
       " 'GSM737336.CEL',\n",
       " 'GSM737337.CEL',\n",
       " 'GSM737338.CEL',\n",
       " 'GSM737339.CEL',\n",
       " 'GSM737340.CEL',\n",
       " 'GSM737341.CEL',\n",
       " 'GSM737342.CEL',\n",
       " 'GSM737343.CEL',\n",
       " 'GSM737344.CEL',\n",
       " '-',\n",
       " 'GSM737346.CEL',\n",
       " 'GSM737347.CEL',\n",
       " 'GSM737348.CEL',\n",
       " 'GSM737349.CEL',\n",
       " 'GSM737350.CEL',\n",
       " 'GSM737351.CEL',\n",
       " 'GSM737352.CEL',\n",
       " 'GSM737353.CEL',\n",
       " 'GSM737354.CEL',\n",
       " 'GSM737355.CEL',\n",
       " 'GSM737356.CEL',\n",
       " 'GSM737357.CEL',\n",
       " 'GSM737358.CEL',\n",
       " 'GSM737359.CEL',\n",
       " 'GSM737360.CEL',\n",
       " 'GSM737361.CEL',\n",
       " 'GSM737362.CEL',\n",
       " 'GSM737363.CEL',\n",
       " 'GSM737364.CEL',\n",
       " 'GSM737365.CEL',\n",
       " 'GSM737366.CEL',\n",
       " 'GSM737367.CEL',\n",
       " '-',\n",
       " 'GSM737369.CEL',\n",
       " 'GSM868113_Gm1_d7.sa3.CEL',\n",
       " 'GSM868114_Gm2_d1.sa2.CEL',\n",
       " 'GSM868115_Gm3_d1.rc3.CEL',\n",
       " 'GSM868116_Gm4_d1.sa3.CEL',\n",
       " 'GSM868117_Gm5_d7.rc2.CEL',\n",
       " 'GSM868118_Gm6_d7.sa1.CEL',\n",
       " 'GSM868119_Gm7_d1.sc3.CEL',\n",
       " '-',\n",
       " 'GSM868121_Gm9_d7.sa2.CEL',\n",
       " 'GSM868122_Gm10_d1.ra3.CEL',\n",
       " 'GSM868123_Gm11_d7.sc1.CEL',\n",
       " 'GSM868124_Gm12_d7.rc1.CEL',\n",
       " 'GSM868125_Gm13_d7.ra3.CEL',\n",
       " 'GSM868126_Gm14_d7.ra1.CEL',\n",
       " 'GSM868127_Gm15_d1.rc1.CEL',\n",
       " 'GSM868128_Gm16_d1.ra2.CEL',\n",
       " 'GSM868129_Gm17_d7.rc3.CEL',\n",
       " 'GSM868130_Gm18_d7.sc3.CEL',\n",
       " 'GSM868131_Gm19_d1.rc2.CEL',\n",
       " 'GSM868132_Gm20_d7.ra2.CEL',\n",
       " 'GSM868133_Gm21_d1.sa1.CEL',\n",
       " 'GSM868134_Gm22_d1.sc2.CEL',\n",
       " 'GSM868135_Gm23_d7.sc2.CEL',\n",
       " 'GSM868136_Gm24_d1.ra1.CEL',\n",
       " '-',\n",
       " '-',\n",
       " '-',\n",
       " 'GSM1088939_Schaar_19_wt_c1_S.CEL',\n",
       " 'GSM1088940_Schaar_20_wt_c2_S.CEL',\n",
       " '-',\n",
       " '-',\n",
       " '-',\n",
       " '-',\n",
       " '-',\n",
       " '-',\n",
       " '-',\n",
       " 'GSM1088948_Schaar_22_wt_myc1_S.CEL',\n",
       " 'GSM1088949_Schaar_23_wt_myc2_S.CEL',\n",
       " 'GSM1088950_Schaar_24_wt_myc3_S.CEL',\n",
       " '-',\n",
       " '-',\n",
       " '-',\n",
       " 'GSM1088954_Schaar_25_nts_c1_S.CEL',\n",
       " 'GSM1088955_Schaar_26_nts_c2_S.CEL',\n",
       " 'GSM1088956_Schaar_27_nts_c3_S.CEL',\n",
       " '-',\n",
       " '-',\n",
       " '-',\n",
       " '-',\n",
       " '-',\n",
       " '-',\n",
       " 'GSM1088963_Schaar_28_nts_myc1_S.CEL',\n",
       " 'GSM1088964_Schaar_29_nts_myc2_S.CEL',\n",
       " '-']"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import re\n",
    "ColIDdict = dict()\n",
    "for x in df.columns[33:]:\n",
    "    y = re.search(\"^GSM[0-9]*\",x)\n",
    "    z = y.group()\n",
    "    print z\n",
    "    ColIDdict[z]=x\n",
    "\n",
    "ColIDlist = list()\n",
    "for x in df2[\"Sample ID\"]:\n",
    "    if x in ColIDdict.keys():\n",
    "        ColIDlist.append(ColIDdict[x])\n",
    "    else:\n",
    "        ColIDlist.append(\"-\")\n",
    "ColIDlist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2[\"Transcriptomics dataset ID\"] = ColIDlist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.to_excel(\"/home/sanu/Documents/Scripts/git/Data/Soy/Transcriptomics_key_with_columnnames.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}