import.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 这个文件是下载并导入的文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import GEOparse\n",
    "import datetime\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "GEONAME = 'GSE6269'\n",
    "# GSE63990  Profiling of bacterial respiratory infection, viral respiratory infection, and non-infectious illness\n",
    "# GSE6269   Gene expression patterns in blood leukocytes discriminate patients with acute infections\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "e:\\app\\Scoop\\apps\\miniconda3\\current\\envs\\igem\\lib\\site-packages\\GEOparse\\GEOparse.py:113: DtypeWarning: Columns (2) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  return parse_GSE(filepath, open_kwargs=open_kwargs)\n"
     ]
    }
   ],
   "source": [
    "gse = GEOparse.get_GEO(geo=GEONAME, destdir=\"./datasets\", silent=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Gene expression patterns in blood leukocytes discriminate patients with acute infections'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gse.get_metadata_attribute(\"title\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>ID_REF</th>\n",
       "      <th>1007_s_at</th>\n",
       "      <th>1053_at</th>\n",
       "      <th>117_at</th>\n",
       "      <th>121_at</th>\n",
       "      <th>1255_g_at</th>\n",
       "      <th>1294_at</th>\n",
       "      <th>1316_at</th>\n",
       "      <th>1320_at</th>\n",
       "      <th>1405_i_at</th>\n",
       "      <th>1431_at</th>\n",
       "      <th>...</th>\n",
       "      <th>hmm9678-S</th>\n",
       "      <th>hmm9715-S</th>\n",
       "      <th>hmm9738-S</th>\n",
       "      <th>hmm974-S</th>\n",
       "      <th>hmm9750-S</th>\n",
       "      <th>hmm9917-S</th>\n",
       "      <th>hmm9988-S</th>\n",
       "      <th>lysA</th>\n",
       "      <th>pheA</th>\n",
       "      <th>thrB</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>GSM173314</th>\n",
       "      <td>497.6</td>\n",
       "      <td>568.4</td>\n",
       "      <td>224.2</td>\n",
       "      <td>850.1</td>\n",
       "      <td>74.8</td>\n",
       "      <td>1188.4</td>\n",
       "      <td>135.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>4089.9</td>\n",
       "      <td>18.2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173315</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>3.3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-5.6</td>\n",
       "      <td>2.8</td>\n",
       "      <td>-6.5</td>\n",
       "      <td>23.0</td>\n",
       "      <td>-5.4</td>\n",
       "      <td>5.4</td>\n",
       "      <td>1.1</td>\n",
       "      <td>-0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173316</th>\n",
       "      <td>399.9</td>\n",
       "      <td>499.5</td>\n",
       "      <td>463.8</td>\n",
       "      <td>1119.4</td>\n",
       "      <td>73.8</td>\n",
       "      <td>1429.3</td>\n",
       "      <td>366.1</td>\n",
       "      <td>15.7</td>\n",
       "      <td>18524.4</td>\n",
       "      <td>66.1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173317</th>\n",
       "      <td>167.1</td>\n",
       "      <td>827.9</td>\n",
       "      <td>633.7</td>\n",
       "      <td>644.2</td>\n",
       "      <td>63.0</td>\n",
       "      <td>1081.1</td>\n",
       "      <td>46.2</td>\n",
       "      <td>13.4</td>\n",
       "      <td>8046.2</td>\n",
       "      <td>81.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173318</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>-3.4</td>\n",
       "      <td>5.0</td>\n",
       "      <td>-1.2</td>\n",
       "      <td>2.9</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>4.2</td>\n",
       "      <td>-7.2</td>\n",
       "      <td>6.9</td>\n",
       "      <td>2.7</td>\n",
       "      <td>-3.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 101973 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "ID_REF     1007_s_at  1053_at  117_at  121_at  1255_g_at  1294_at  1316_at  \\\n",
       "name                                                                         \n",
       "GSM173314      497.6    568.4   224.2   850.1       74.8   1188.4    135.0   \n",
       "GSM173315        NaN      NaN     NaN     NaN        NaN      NaN      NaN   \n",
       "GSM173316      399.9    499.5   463.8  1119.4       73.8   1429.3    366.1   \n",
       "GSM173317      167.1    827.9   633.7   644.2       63.0   1081.1     46.2   \n",
       "GSM173318        NaN      NaN     NaN     NaN        NaN      NaN      NaN   \n",
       "\n",
       "ID_REF     1320_at  1405_i_at  1431_at  ...  hmm9678-S  hmm9715-S  hmm9738-S  \\\n",
       "name                                    ...                                    \n",
       "GSM173314     13.0     4089.9     18.2  ...        NaN        NaN        NaN   \n",
       "GSM173315      NaN        NaN      NaN  ...        3.3        1.0       -5.6   \n",
       "GSM173316     15.7    18524.4     66.1  ...        NaN        NaN        NaN   \n",
       "GSM173317     13.4     8046.2     81.0  ...        NaN        NaN        NaN   \n",
       "GSM173318      NaN        NaN      NaN  ...       -3.4        5.0       -1.2   \n",
       "\n",
       "ID_REF     hmm974-S  hmm9750-S  hmm9917-S  hmm9988-S  lysA  pheA  thrB  \n",
       "name                                                                    \n",
       "GSM173314       NaN        NaN        NaN        NaN   NaN   NaN   NaN  \n",
       "GSM173315       2.8       -6.5       23.0       -5.4   5.4   1.1  -0.6  \n",
       "GSM173316       NaN        NaN        NaN        NaN   NaN   NaN   NaN  \n",
       "GSM173317       NaN        NaN        NaN        NaN   NaN   NaN   NaN  \n",
       "GSM173318       2.9      -12.0        4.2       -7.2   6.9   2.7  -3.3  \n",
       "\n",
       "[5 rows x 101973 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 先把一个个样本的Value值提取出来，然后再转置，这样就可以得到一个样本对应的所有基因的表达值。\n",
    "sample_value = gse.pivot_samples('VALUE').T\n",
    "sample_value.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Ovler\\AppData\\Local\\Temp\\ipykernel_102856\\1121286197.py:2: DtypeWarning: Columns (2) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  GPL570 = GEOparse.get_GEO(geo='GPL570', destdir=\"./datasets\", silent=True)\n"
     ]
    }
   ],
   "source": [
    "GPL96 = GEOparse.get_GEO(geo='GPL96', destdir=\"./datasets\", silent=True)\n",
    "GPL570 = GEOparse.get_GEO(geo='GPL570', destdir=\"./datasets\", silent=True)\n",
    "GPL571 = GEOparse.get_GEO(geo='GPL571', destdir=\"./datasets\", silent=True)\n",
    "GPL2507 = GEOparse.get_GEO(geo='GPL2507', destdir=\"./datasets\", silent=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<SERIES: GSE6269 - 143 SAMPLES, 3 d(s)>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "gse.gpls[next(iter(gse.gpls))]\n",
    "merged = gse.merge_and_average(gse.gpls[next(iter(gse.gpls))], \"VALUE\", \"Gene Symbol\", gpl_on=\"ID\", gsm_on=\"ID_REF\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>GB_ACC</th>\n",
       "      <th>NM_000014.3</th>\n",
       "      <th>NM_000015.1</th>\n",
       "      <th>NM_000016.2</th>\n",
       "      <th>NM_000017.1</th>\n",
       "      <th>NM_000018.1</th>\n",
       "      <th>NM_000019.2</th>\n",
       "      <th>NM_000020.1</th>\n",
       "      <th>NM_000021.2</th>\n",
       "      <th>NM_000022.1</th>\n",
       "      <th>NM_000023.1</th>\n",
       "      <th>...</th>\n",
       "      <th>XM_380151.1</th>\n",
       "      <th>XM_380152.1</th>\n",
       "      <th>XM_380153.1</th>\n",
       "      <th>XM_380154.1</th>\n",
       "      <th>XM_380155.1</th>\n",
       "      <th>XM_380156.1</th>\n",
       "      <th>XM_380157.1</th>\n",
       "      <th>XM_380159.1</th>\n",
       "      <th>XM_380160.1</th>\n",
       "      <th>XM_380162.1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>GSM173176</th>\n",
       "      <td>-12.8</td>\n",
       "      <td>-15.6</td>\n",
       "      <td>126.3</td>\n",
       "      <td>7.3</td>\n",
       "      <td>1339.4</td>\n",
       "      <td>119.1</td>\n",
       "      <td>3.4</td>\n",
       "      <td>15.3</td>\n",
       "      <td>149.9</td>\n",
       "      <td>-6.8</td>\n",
       "      <td>...</td>\n",
       "      <td>184.4</td>\n",
       "      <td>-2.9</td>\n",
       "      <td>-1.8</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>9.9</td>\n",
       "      <td>-3.2</td>\n",
       "      <td>0.3</td>\n",
       "      <td>1.7</td>\n",
       "      <td>-4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173283</th>\n",
       "      <td>-4.8</td>\n",
       "      <td>3.4</td>\n",
       "      <td>357.9</td>\n",
       "      <td>16.5</td>\n",
       "      <td>1225.6</td>\n",
       "      <td>171.9</td>\n",
       "      <td>9.1</td>\n",
       "      <td>9.1</td>\n",
       "      <td>513.7</td>\n",
       "      <td>-6.8</td>\n",
       "      <td>...</td>\n",
       "      <td>283.2</td>\n",
       "      <td>-2.4</td>\n",
       "      <td>-0.8</td>\n",
       "      <td>6.2</td>\n",
       "      <td>-1.6</td>\n",
       "      <td>0.4</td>\n",
       "      <td>4.1</td>\n",
       "      <td>-0.8</td>\n",
       "      <td>-0.4</td>\n",
       "      <td>-3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173285</th>\n",
       "      <td>0.5</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>1503.6</td>\n",
       "      <td>148.8</td>\n",
       "      <td>5.8</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>506.4</td>\n",
       "      <td>-7.6</td>\n",
       "      <td>...</td>\n",
       "      <td>306.0</td>\n",
       "      <td>-11.3</td>\n",
       "      <td>2.3</td>\n",
       "      <td>4.9</td>\n",
       "      <td>-4.9</td>\n",
       "      <td>2.9</td>\n",
       "      <td>0.2</td>\n",
       "      <td>-2.4</td>\n",
       "      <td>-8.2</td>\n",
       "      <td>-1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173290</th>\n",
       "      <td>-11.5</td>\n",
       "      <td>12.5</td>\n",
       "      <td>263.9</td>\n",
       "      <td>19.6</td>\n",
       "      <td>2012.8</td>\n",
       "      <td>221.2</td>\n",
       "      <td>21.5</td>\n",
       "      <td>24.0</td>\n",
       "      <td>127.5</td>\n",
       "      <td>-18.1</td>\n",
       "      <td>...</td>\n",
       "      <td>990.1</td>\n",
       "      <td>-13.5</td>\n",
       "      <td>-6.3</td>\n",
       "      <td>-9.9</td>\n",
       "      <td>-12.6</td>\n",
       "      <td>0.3</td>\n",
       "      <td>-4.5</td>\n",
       "      <td>14.9</td>\n",
       "      <td>7.3</td>\n",
       "      <td>-9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173291</th>\n",
       "      <td>-18.9</td>\n",
       "      <td>-15.2</td>\n",
       "      <td>371.4</td>\n",
       "      <td>27.0</td>\n",
       "      <td>2017.7</td>\n",
       "      <td>198.0</td>\n",
       "      <td>13.3</td>\n",
       "      <td>-0.2</td>\n",
       "      <td>232.0</td>\n",
       "      <td>-14.8</td>\n",
       "      <td>...</td>\n",
       "      <td>464.1</td>\n",
       "      <td>-6.7</td>\n",
       "      <td>-2.5</td>\n",
       "      <td>-12.1</td>\n",
       "      <td>-17.6</td>\n",
       "      <td>3.3</td>\n",
       "      <td>0.7</td>\n",
       "      <td>5.5</td>\n",
       "      <td>5.8</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173292</th>\n",
       "      <td>2.4</td>\n",
       "      <td>-3.4</td>\n",
       "      <td>346.8</td>\n",
       "      <td>37.1</td>\n",
       "      <td>2080.6</td>\n",
       "      <td>301.7</td>\n",
       "      <td>16.8</td>\n",
       "      <td>15.9</td>\n",
       "      <td>190.9</td>\n",
       "      <td>5.7</td>\n",
       "      <td>...</td>\n",
       "      <td>696.2</td>\n",
       "      <td>-15.2</td>\n",
       "      <td>15.8</td>\n",
       "      <td>4.4</td>\n",
       "      <td>-13.8</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>-2.7</td>\n",
       "      <td>-14.0</td>\n",
       "      <td>13.6</td>\n",
       "      <td>-4.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173293</th>\n",
       "      <td>-16.5</td>\n",
       "      <td>-3.7</td>\n",
       "      <td>514.4</td>\n",
       "      <td>25.5</td>\n",
       "      <td>1964.0</td>\n",
       "      <td>259.6</td>\n",
       "      <td>14.7</td>\n",
       "      <td>28.0</td>\n",
       "      <td>194.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>...</td>\n",
       "      <td>284.2</td>\n",
       "      <td>-11.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>-15.2</td>\n",
       "      <td>-4.5</td>\n",
       "      <td>-1.6</td>\n",
       "      <td>20.6</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>0.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173294</th>\n",
       "      <td>-9.3</td>\n",
       "      <td>-9.6</td>\n",
       "      <td>652.6</td>\n",
       "      <td>21.1</td>\n",
       "      <td>3058.1</td>\n",
       "      <td>384.6</td>\n",
       "      <td>16.8</td>\n",
       "      <td>9.1</td>\n",
       "      <td>421.1</td>\n",
       "      <td>-5.2</td>\n",
       "      <td>...</td>\n",
       "      <td>353.9</td>\n",
       "      <td>-10.1</td>\n",
       "      <td>-18.8</td>\n",
       "      <td>8.9</td>\n",
       "      <td>4.3</td>\n",
       "      <td>3.2</td>\n",
       "      <td>-6.6</td>\n",
       "      <td>-7.5</td>\n",
       "      <td>-4.7</td>\n",
       "      <td>-8.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173295</th>\n",
       "      <td>-5.1</td>\n",
       "      <td>-4.3</td>\n",
       "      <td>536.3</td>\n",
       "      <td>21.0</td>\n",
       "      <td>1386.9</td>\n",
       "      <td>301.3</td>\n",
       "      <td>0.1</td>\n",
       "      <td>6.5</td>\n",
       "      <td>249.4</td>\n",
       "      <td>-6.1</td>\n",
       "      <td>...</td>\n",
       "      <td>341.0</td>\n",
       "      <td>-4.9</td>\n",
       "      <td>1.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>-1.6</td>\n",
       "      <td>1.9</td>\n",
       "      <td>-2.4</td>\n",
       "      <td>0.4</td>\n",
       "      <td>3.3</td>\n",
       "      <td>-6.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173296</th>\n",
       "      <td>4.6</td>\n",
       "      <td>-0.1</td>\n",
       "      <td>612.8</td>\n",
       "      <td>58.2</td>\n",
       "      <td>3414.5</td>\n",
       "      <td>595.0</td>\n",
       "      <td>22.6</td>\n",
       "      <td>22.6</td>\n",
       "      <td>275.0</td>\n",
       "      <td>-15.5</td>\n",
       "      <td>...</td>\n",
       "      <td>759.5</td>\n",
       "      <td>-13.4</td>\n",
       "      <td>-8.4</td>\n",
       "      <td>-8.5</td>\n",
       "      <td>-0.6</td>\n",
       "      <td>0.6</td>\n",
       "      <td>12.3</td>\n",
       "      <td>19.5</td>\n",
       "      <td>-1.4</td>\n",
       "      <td>-15.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173297</th>\n",
       "      <td>-3.2</td>\n",
       "      <td>2.1</td>\n",
       "      <td>374.1</td>\n",
       "      <td>32.0</td>\n",
       "      <td>2245.9</td>\n",
       "      <td>285.5</td>\n",
       "      <td>-1.5</td>\n",
       "      <td>21.8</td>\n",
       "      <td>182.7</td>\n",
       "      <td>-12.4</td>\n",
       "      <td>...</td>\n",
       "      <td>558.0</td>\n",
       "      <td>-4.1</td>\n",
       "      <td>1.2</td>\n",
       "      <td>-0.6</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.3</td>\n",
       "      <td>-3.8</td>\n",
       "      <td>7.4</td>\n",
       "      <td>4.5</td>\n",
       "      <td>7.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173298</th>\n",
       "      <td>-2.8</td>\n",
       "      <td>-1.7</td>\n",
       "      <td>179.6</td>\n",
       "      <td>15.6</td>\n",
       "      <td>1602.4</td>\n",
       "      <td>154.5</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.4</td>\n",
       "      <td>110.6</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>334.5</td>\n",
       "      <td>2.3</td>\n",
       "      <td>-1.8</td>\n",
       "      <td>6.6</td>\n",
       "      <td>-2.5</td>\n",
       "      <td>-3.4</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>2.1</td>\n",
       "      <td>-3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173299</th>\n",
       "      <td>-6.9</td>\n",
       "      <td>-2.7</td>\n",
       "      <td>328.3</td>\n",
       "      <td>17.2</td>\n",
       "      <td>2452.0</td>\n",
       "      <td>313.8</td>\n",
       "      <td>4.5</td>\n",
       "      <td>10.1</td>\n",
       "      <td>206.4</td>\n",
       "      <td>-8.6</td>\n",
       "      <td>...</td>\n",
       "      <td>516.4</td>\n",
       "      <td>-6.7</td>\n",
       "      <td>-4.7</td>\n",
       "      <td>-3.5</td>\n",
       "      <td>-10.4</td>\n",
       "      <td>3.0</td>\n",
       "      <td>-4.5</td>\n",
       "      <td>8.4</td>\n",
       "      <td>-7.8</td>\n",
       "      <td>-5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173300</th>\n",
       "      <td>-9.7</td>\n",
       "      <td>-3.7</td>\n",
       "      <td>283.7</td>\n",
       "      <td>22.6</td>\n",
       "      <td>2211.7</td>\n",
       "      <td>184.4</td>\n",
       "      <td>13.1</td>\n",
       "      <td>20.1</td>\n",
       "      <td>261.2</td>\n",
       "      <td>-3.9</td>\n",
       "      <td>...</td>\n",
       "      <td>506.3</td>\n",
       "      <td>-8.2</td>\n",
       "      <td>6.9</td>\n",
       "      <td>-6.3</td>\n",
       "      <td>4.9</td>\n",
       "      <td>3.0</td>\n",
       "      <td>12.2</td>\n",
       "      <td>8.2</td>\n",
       "      <td>-2.4</td>\n",
       "      <td>-3.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173302</th>\n",
       "      <td>-3.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>292.9</td>\n",
       "      <td>7.0</td>\n",
       "      <td>886.1</td>\n",
       "      <td>164.5</td>\n",
       "      <td>13.3</td>\n",
       "      <td>-3.6</td>\n",
       "      <td>279.4</td>\n",
       "      <td>-4.7</td>\n",
       "      <td>...</td>\n",
       "      <td>200.1</td>\n",
       "      <td>-0.3</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>-2.4</td>\n",
       "      <td>6.6</td>\n",
       "      <td>1.7</td>\n",
       "      <td>3.8</td>\n",
       "      <td>11.1</td>\n",
       "      <td>-3.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173303</th>\n",
       "      <td>-3.7</td>\n",
       "      <td>7.9</td>\n",
       "      <td>134.4</td>\n",
       "      <td>24.4</td>\n",
       "      <td>2660.2</td>\n",
       "      <td>203.8</td>\n",
       "      <td>3.1</td>\n",
       "      <td>20.4</td>\n",
       "      <td>141.4</td>\n",
       "      <td>-13.8</td>\n",
       "      <td>...</td>\n",
       "      <td>680.3</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-12.5</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>-1.4</td>\n",
       "      <td>-0.7</td>\n",
       "      <td>6.4</td>\n",
       "      <td>7.7</td>\n",
       "      <td>-5.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173304</th>\n",
       "      <td>-11.3</td>\n",
       "      <td>13.6</td>\n",
       "      <td>294.7</td>\n",
       "      <td>53.9</td>\n",
       "      <td>2626.6</td>\n",
       "      <td>330.4</td>\n",
       "      <td>16.3</td>\n",
       "      <td>7.8</td>\n",
       "      <td>432.8</td>\n",
       "      <td>4.1</td>\n",
       "      <td>...</td>\n",
       "      <td>831.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>-12.1</td>\n",
       "      <td>0.5</td>\n",
       "      <td>-14.3</td>\n",
       "      <td>9.7</td>\n",
       "      <td>1.3</td>\n",
       "      <td>13.2</td>\n",
       "      <td>9.2</td>\n",
       "      <td>-1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173306</th>\n",
       "      <td>-2.6</td>\n",
       "      <td>2.3</td>\n",
       "      <td>237.1</td>\n",
       "      <td>19.4</td>\n",
       "      <td>973.1</td>\n",
       "      <td>119.0</td>\n",
       "      <td>13.7</td>\n",
       "      <td>12.1</td>\n",
       "      <td>192.8</td>\n",
       "      <td>-15.1</td>\n",
       "      <td>...</td>\n",
       "      <td>126.3</td>\n",
       "      <td>0.3</td>\n",
       "      <td>-9.8</td>\n",
       "      <td>5.9</td>\n",
       "      <td>1.4</td>\n",
       "      <td>3.0</td>\n",
       "      <td>-1.8</td>\n",
       "      <td>3.3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173307</th>\n",
       "      <td>-4.7</td>\n",
       "      <td>3.5</td>\n",
       "      <td>102.2</td>\n",
       "      <td>13.6</td>\n",
       "      <td>1667.6</td>\n",
       "      <td>152.8</td>\n",
       "      <td>8.4</td>\n",
       "      <td>7.4</td>\n",
       "      <td>96.3</td>\n",
       "      <td>-7.1</td>\n",
       "      <td>...</td>\n",
       "      <td>393.8</td>\n",
       "      <td>-4.5</td>\n",
       "      <td>3.3</td>\n",
       "      <td>-6.7</td>\n",
       "      <td>-1.8</td>\n",
       "      <td>5.6</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.8</td>\n",
       "      <td>3.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173308</th>\n",
       "      <td>-5.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>114.7</td>\n",
       "      <td>13.8</td>\n",
       "      <td>1353.9</td>\n",
       "      <td>81.9</td>\n",
       "      <td>16.1</td>\n",
       "      <td>15.4</td>\n",
       "      <td>210.2</td>\n",
       "      <td>-9.7</td>\n",
       "      <td>...</td>\n",
       "      <td>285.5</td>\n",
       "      <td>-10.6</td>\n",
       "      <td>-4.8</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-8.2</td>\n",
       "      <td>5.6</td>\n",
       "      <td>0.2</td>\n",
       "      <td>10.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173311</th>\n",
       "      <td>-7.6</td>\n",
       "      <td>1.6</td>\n",
       "      <td>176.6</td>\n",
       "      <td>33.4</td>\n",
       "      <td>1024.4</td>\n",
       "      <td>85.2</td>\n",
       "      <td>-2.9</td>\n",
       "      <td>10.3</td>\n",
       "      <td>123.3</td>\n",
       "      <td>-4.3</td>\n",
       "      <td>...</td>\n",
       "      <td>160.9</td>\n",
       "      <td>-1.2</td>\n",
       "      <td>3.7</td>\n",
       "      <td>-2.9</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>5.2</td>\n",
       "      <td>-4.9</td>\n",
       "      <td>-2.8</td>\n",
       "      <td>5.4</td>\n",
       "      <td>-9.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173313</th>\n",
       "      <td>-7.8</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>145.3</td>\n",
       "      <td>22.7</td>\n",
       "      <td>1157.6</td>\n",
       "      <td>96.7</td>\n",
       "      <td>-5.1</td>\n",
       "      <td>14.8</td>\n",
       "      <td>157.1</td>\n",
       "      <td>-3.8</td>\n",
       "      <td>...</td>\n",
       "      <td>173.7</td>\n",
       "      <td>-10.8</td>\n",
       "      <td>-0.8</td>\n",
       "      <td>12.1</td>\n",
       "      <td>6.2</td>\n",
       "      <td>-2.9</td>\n",
       "      <td>4.4</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>-0.3</td>\n",
       "      <td>-3.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173315</th>\n",
       "      <td>-3.3</td>\n",
       "      <td>4.4</td>\n",
       "      <td>266.2</td>\n",
       "      <td>17.5</td>\n",
       "      <td>1273.1</td>\n",
       "      <td>154.5</td>\n",
       "      <td>6.7</td>\n",
       "      <td>17.6</td>\n",
       "      <td>367.4</td>\n",
       "      <td>-3.9</td>\n",
       "      <td>...</td>\n",
       "      <td>281.4</td>\n",
       "      <td>-9.8</td>\n",
       "      <td>3.9</td>\n",
       "      <td>0.5</td>\n",
       "      <td>-1.6</td>\n",
       "      <td>3.2</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>2.1</td>\n",
       "      <td>4.4</td>\n",
       "      <td>-13.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM173318</th>\n",
       "      <td>-0.8</td>\n",
       "      <td>8.3</td>\n",
       "      <td>355.2</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1039.7</td>\n",
       "      <td>243.9</td>\n",
       "      <td>2.4</td>\n",
       "      <td>13.1</td>\n",
       "      <td>190.5</td>\n",
       "      <td>-7.5</td>\n",
       "      <td>...</td>\n",
       "      <td>212.1</td>\n",
       "      <td>4.6</td>\n",
       "      <td>1.2</td>\n",
       "      <td>1.4</td>\n",
       "      <td>-5.8</td>\n",
       "      <td>5.9</td>\n",
       "      <td>11.6</td>\n",
       "      <td>3.7</td>\n",
       "      <td>5.1</td>\n",
       "      <td>-11.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>24 rows × 25359 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "GB_ACC     NM_000014.3  NM_000015.1  NM_000016.2  NM_000017.1  NM_000018.1  \\\n",
       "GSM173176        -12.8        -15.6        126.3          7.3       1339.4   \n",
       "GSM173283         -4.8          3.4        357.9         16.5       1225.6   \n",
       "GSM173285          0.5         -3.0        340.0         17.0       1503.6   \n",
       "GSM173290        -11.5         12.5        263.9         19.6       2012.8   \n",
       "GSM173291        -18.9        -15.2        371.4         27.0       2017.7   \n",
       "GSM173292          2.4         -3.4        346.8         37.1       2080.6   \n",
       "GSM173293        -16.5         -3.7        514.4         25.5       1964.0   \n",
       "GSM173294         -9.3         -9.6        652.6         21.1       3058.1   \n",
       "GSM173295         -5.1         -4.3        536.3         21.0       1386.9   \n",
       "GSM173296          4.6         -0.1        612.8         58.2       3414.5   \n",
       "GSM173297         -3.2          2.1        374.1         32.0       2245.9   \n",
       "GSM173298         -2.8         -1.7        179.6         15.6       1602.4   \n",
       "GSM173299         -6.9         -2.7        328.3         17.2       2452.0   \n",
       "GSM173300         -9.7         -3.7        283.7         22.6       2211.7   \n",
       "GSM173302         -3.0          4.8        292.9          7.0        886.1   \n",
       "GSM173303         -3.7          7.9        134.4         24.4       2660.2   \n",
       "GSM173304        -11.3         13.6        294.7         53.9       2626.6   \n",
       "GSM173306         -2.6          2.3        237.1         19.4        973.1   \n",
       "GSM173307         -4.7          3.5        102.2         13.6       1667.6   \n",
       "GSM173308         -5.7          0.0        114.7         13.8       1353.9   \n",
       "GSM173311         -7.6          1.6        176.6         33.4       1024.4   \n",
       "GSM173313         -7.8         -3.0        145.3         22.7       1157.6   \n",
       "GSM173315         -3.3          4.4        266.2         17.5       1273.1   \n",
       "GSM173318         -0.8          8.3        355.2         12.0       1039.7   \n",
       "\n",
       "GB_ACC     NM_000019.2  NM_000020.1  NM_000021.2  NM_000022.1  NM_000023.1  \\\n",
       "GSM173176        119.1          3.4         15.3        149.9         -6.8   \n",
       "GSM173283        171.9          9.1          9.1        513.7         -6.8   \n",
       "GSM173285        148.8          5.8         -2.0        506.4         -7.6   \n",
       "GSM173290        221.2         21.5         24.0        127.5        -18.1   \n",
       "GSM173291        198.0         13.3         -0.2        232.0        -14.8   \n",
       "GSM173292        301.7         16.8         15.9        190.9          5.7   \n",
       "GSM173293        259.6         14.7         28.0        194.0          0.3   \n",
       "GSM173294        384.6         16.8          9.1        421.1         -5.2   \n",
       "GSM173295        301.3          0.1          6.5        249.4         -6.1   \n",
       "GSM173296        595.0         22.6         22.6        275.0        -15.5   \n",
       "GSM173297        285.5         -1.5         21.8        182.7        -12.4   \n",
       "GSM173298        154.5          5.0         10.4        110.6         -3.0   \n",
       "GSM173299        313.8          4.5         10.1        206.4         -8.6   \n",
       "GSM173300        184.4         13.1         20.1        261.2         -3.9   \n",
       "GSM173302        164.5         13.3         -3.6        279.4         -4.7   \n",
       "GSM173303        203.8          3.1         20.4        141.4        -13.8   \n",
       "GSM173304        330.4         16.3          7.8        432.8          4.1   \n",
       "GSM173306        119.0         13.7         12.1        192.8        -15.1   \n",
       "GSM173307        152.8          8.4          7.4         96.3         -7.1   \n",
       "GSM173308         81.9         16.1         15.4        210.2         -9.7   \n",
       "GSM173311         85.2         -2.9         10.3        123.3         -4.3   \n",
       "GSM173313         96.7         -5.1         14.8        157.1         -3.8   \n",
       "GSM173315        154.5          6.7         17.6        367.4         -3.9   \n",
       "GSM173318        243.9          2.4         13.1        190.5         -7.5   \n",
       "\n",
       "GB_ACC     ...  XM_380151.1  XM_380152.1  XM_380153.1  XM_380154.1  \\\n",
       "GSM173176  ...        184.4         -2.9         -1.8         -4.0   \n",
       "GSM173283  ...        283.2         -2.4         -0.8          6.2   \n",
       "GSM173285  ...        306.0        -11.3          2.3          4.9   \n",
       "GSM173290  ...        990.1        -13.5         -6.3         -9.9   \n",
       "GSM173291  ...        464.1         -6.7         -2.5        -12.1   \n",
       "GSM173292  ...        696.2        -15.2         15.8          4.4   \n",
       "GSM173293  ...        284.2        -11.9          0.0         -1.1   \n",
       "GSM173294  ...        353.9        -10.1        -18.8          8.9   \n",
       "GSM173295  ...        341.0         -4.9          1.7          3.7   \n",
       "GSM173296  ...        759.5        -13.4         -8.4         -8.5   \n",
       "GSM173297  ...        558.0         -4.1          1.2         -0.6   \n",
       "GSM173298  ...        334.5          2.3         -1.8          6.6   \n",
       "GSM173299  ...        516.4         -6.7         -4.7         -3.5   \n",
       "GSM173300  ...        506.3         -8.2          6.9         -6.3   \n",
       "GSM173302  ...        200.1         -0.3         -2.0          4.9   \n",
       "GSM173303  ...        680.3          3.2          1.0        -12.5   \n",
       "GSM173304  ...        831.0          2.0        -12.1          0.5   \n",
       "GSM173306  ...        126.3          0.3         -9.8          5.9   \n",
       "GSM173307  ...        393.8         -4.5          3.3         -6.7   \n",
       "GSM173308  ...        285.5        -10.6         -4.8         -5.0   \n",
       "GSM173311  ...        160.9         -1.2          3.7         -2.9   \n",
       "GSM173313  ...        173.7        -10.8         -0.8         12.1   \n",
       "GSM173315  ...        281.4         -9.8          3.9          0.5   \n",
       "GSM173318  ...        212.1          4.6          1.2          1.4   \n",
       "\n",
       "GB_ACC     XM_380155.1  XM_380156.1  XM_380157.1  XM_380159.1  XM_380160.1  \\\n",
       "GSM173176        -12.0          9.9         -3.2          0.3          1.7   \n",
       "GSM173283         -1.6          0.4          4.1         -0.8         -0.4   \n",
       "GSM173285         -4.9          2.9          0.2         -2.4         -8.2   \n",
       "GSM173290        -12.6          0.3         -4.5         14.9          7.3   \n",
       "GSM173291        -17.6          3.3          0.7          5.5          5.8   \n",
       "GSM173292        -13.8         -5.0         -2.7        -14.0         13.6   \n",
       "GSM173293        -15.2         -4.5         -1.6         20.6        -10.0   \n",
       "GSM173294          4.3          3.2         -6.6         -7.5         -4.7   \n",
       "GSM173295         -1.6          1.9         -2.4          0.4          3.3   \n",
       "GSM173296         -0.6          0.6         12.3         19.5         -1.4   \n",
       "GSM173297          4.0          6.3         -3.8          7.4          4.5   \n",
       "GSM173298         -2.5         -3.4          2.0          1.8          2.1   \n",
       "GSM173299        -10.4          3.0         -4.5          8.4         -7.8   \n",
       "GSM173300          4.9          3.0         12.2          8.2         -2.4   \n",
       "GSM173302         -2.4          6.6          1.7          3.8         11.1   \n",
       "GSM173303         -9.0         -1.4         -0.7          6.4          7.7   \n",
       "GSM173304        -14.3          9.7          1.3         13.2          9.2   \n",
       "GSM173306          1.4          3.0         -1.8          3.3          2.0   \n",
       "GSM173307         -1.8          5.6          3.5          1.8          3.7   \n",
       "GSM173308          0.1          0.0         -8.2          5.6          0.2   \n",
       "GSM173311         -1.0          5.2         -4.9         -2.8          5.4   \n",
       "GSM173313          6.2         -2.9          4.4        -10.0         -0.3   \n",
       "GSM173315         -1.6          3.2         -2.0          2.1          4.4   \n",
       "GSM173318         -5.8          5.9         11.6          3.7          5.1   \n",
       "\n",
       "GB_ACC     XM_380162.1  \n",
       "GSM173176         -4.0  \n",
       "GSM173283         -3.0  \n",
       "GSM173285         -1.4  \n",
       "GSM173290         -9.0  \n",
       "GSM173291         -1.0  \n",
       "GSM173292         -4.9  \n",
       "GSM173293          0.7  \n",
       "GSM173294         -8.3  \n",
       "GSM173295         -6.1  \n",
       "GSM173296        -15.6  \n",
       "GSM173297          7.2  \n",
       "GSM173298         -3.0  \n",
       "GSM173299         -5.3  \n",
       "GSM173300         -3.1  \n",
       "GSM173302         -3.1  \n",
       "GSM173303         -5.7  \n",
       "GSM173304         -1.8  \n",
       "GSM173306          7.2  \n",
       "GSM173307          0.0  \n",
       "GSM173308         10.8  \n",
       "GSM173311         -9.9  \n",
       "GSM173313         -3.3  \n",
       "GSM173315        -13.8  \n",
       "GSM173318        -11.5  \n",
       "\n",
       "[24 rows x 25359 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged = gse.merge_and_average(\n",
    "    d=GPL2507,\n",
    "    expression_column='VALUE',\n",
    "    group_by_column='GB_ACC',\n",
    "    force=True,\n",
    "    gpl_on='ID',\n",
    "    gsm_on='ID_REF'\n",
    ").T\n",
    "merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "\"None of [Index(['value'], dtype='object')] are in the [columns]\"",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[11], line 4\u001b[0m\n\u001b[0;32m      2\u001b[0m n \u001b[39m=\u001b[39m \u001b[39m0\u001b[39m\n\u001b[0;32m      3\u001b[0m \u001b[39m# value = [] we want 2-d dataframe\u001b[39;00m\n\u001b[1;32m----> 4\u001b[0m merged \u001b[39m=\u001b[39m gse\u001b[39m.\u001b[39;49mmerge_and_average(\n\u001b[0;32m      5\u001b[0m     GPL570,\n\u001b[0;32m      6\u001b[0m     \u001b[39m'\u001b[39;49m\u001b[39mvalue\u001b[39;49m\u001b[39m'\u001b[39;49m,\n\u001b[0;32m      7\u001b[0m     \u001b[39m'\u001b[39;49m\u001b[39mGB_ACC\u001b[39;49m\u001b[39m'\u001b[39;49m,\n\u001b[0;32m      8\u001b[0m     gpl_on\u001b[39m=\u001b[39;49m\u001b[39m'\u001b[39;49m\u001b[39mID\u001b[39;49m\u001b[39m'\u001b[39;49m,\n\u001b[0;32m      9\u001b[0m     gsm_on\u001b[39m=\u001b[39;49m\u001b[39m'\u001b[39;49m\u001b[39mID_REF\u001b[39;49m\u001b[39m'\u001b[39;49m\n\u001b[0;32m     10\u001b[0m )\n\u001b[0;32m     11\u001b[0m merged \u001b[39m=\u001b[39m merged[\n\u001b[0;32m     12\u001b[0m     \u001b[39msorted\u001b[39m(merged\u001b[39m.\u001b[39mcolumns)\n\u001b[0;32m     13\u001b[0m ] \n\u001b[0;32m     15\u001b[0m value \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39mDataFrame()\n",
      "File \u001b[1;32me:\\app\\Scoop\\apps\\miniconda3\\current\\envs\\igem\\lib\\site-packages\\GEOparse\\GEOTypes.py:813\u001b[0m, in \u001b[0;36mGSE.merge_and_average\u001b[1;34m(self, d, expression_column, group_by_column, force, merge_on_column, gsm_on, gpl_on)\u001b[0m\n\u001b[0;32m    810\u001b[0m \u001b[39mfor\u001b[39;00m gsm \u001b[39min\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mgsms\u001b[39m.\u001b[39mvalues():\n\u001b[0;32m    811\u001b[0m     \u001b[39mif\u001b[39;00m gpl\u001b[39m.\u001b[39mname \u001b[39m==\u001b[39m gsm\u001b[39m.\u001b[39mmetadata[\u001b[39m\"\u001b[39m\u001b[39mplatform_id\u001b[39m\u001b[39m\"\u001b[39m][\u001b[39m0\u001b[39m]:\n\u001b[0;32m    812\u001b[0m         data\u001b[39m.\u001b[39mappend(\n\u001b[1;32m--> 813\u001b[0m             gsm\u001b[39m.\u001b[39;49mannotate_and_average(\n\u001b[0;32m    814\u001b[0m                 gpl\u001b[39m=\u001b[39;49mgpl,\n\u001b[0;32m    815\u001b[0m                 merge_on_column\u001b[39m=\u001b[39;49mmerge_on_column,\n\u001b[0;32m    816\u001b[0m                 expression_column\u001b[39m=\u001b[39;49mexpression_column,\n\u001b[0;32m    817\u001b[0m                 group_by_column\u001b[39m=\u001b[39;49mgroup_by_column,\n\u001b[0;32m    818\u001b[0m                 force\u001b[39m=\u001b[39;49mforce,\n\u001b[0;32m    819\u001b[0m                 gpl_on\u001b[39m=\u001b[39;49mgpl_on,\n\u001b[0;32m    820\u001b[0m                 gsm_on\u001b[39m=\u001b[39;49mgsm_on,\n\u001b[0;32m    821\u001b[0m             )\n\u001b[0;32m    822\u001b[0m         )\n\u001b[0;32m    823\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mlen\u001b[39m(data) \u001b[39m==\u001b[39m \u001b[39m0\u001b[39m:\n\u001b[0;32m    824\u001b[0m     logger\u001b[39m.\u001b[39mwarning(\u001b[39m\"\u001b[39m\u001b[39mNo samples for the d were found\u001b[39m\u001b[39m\\n\u001b[39;00m\u001b[39m\"\u001b[39m)\n",
      "File \u001b[1;32me:\\app\\Scoop\\apps\\miniconda3\\current\\envs\\igem\\lib\\site-packages\\GEOparse\\GEOTypes.py:445\u001b[0m, in \u001b[0;36mGSM.annotate_and_average\u001b[1;34m(self, gpl, expression_column, group_by_column, rename, force, merge_on_column, gsm_on, gpl_on)\u001b[0m\n\u001b[0;32m    438\u001b[0m         \u001b[39mraise\u001b[39;00m \u001b[39mException\u001b[39;00m(\n\u001b[0;32m    439\u001b[0m             \u001b[39m\"\u001b[39m\u001b[39mPlease provide both gpl_on and gsm_on or \u001b[39m\u001b[39m\"\u001b[39m\n\u001b[0;32m    440\u001b[0m             \u001b[39m\"\u001b[39m\u001b[39mprovide merge_on_column only\u001b[39m\u001b[39m\"\u001b[39m\n\u001b[0;32m    441\u001b[0m         )\n\u001b[0;32m    442\u001b[0m     tmp_data \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mtable\u001b[39m.\u001b[39mmerge(\n\u001b[0;32m    443\u001b[0m         gpl\u001b[39m.\u001b[39mtable, left_on\u001b[39m=\u001b[39mgsm_on, right_on\u001b[39m=\u001b[39mgpl_on, how\u001b[39m=\u001b[39m\u001b[39m\"\u001b[39m\u001b[39mouter\u001b[39m\u001b[39m\"\u001b[39m\n\u001b[0;32m    444\u001b[0m     )\n\u001b[1;32m--> 445\u001b[0m     tmp_data \u001b[39m=\u001b[39m tmp_data\u001b[39m.\u001b[39;49mgroupby(group_by_column)\u001b[39m.\u001b[39;49mmean()[[expression_column]]\n\u001b[0;32m    446\u001b[0m \u001b[39mif\u001b[39;00m rename:\n\u001b[0;32m    447\u001b[0m     tmp_data\u001b[39m.\u001b[39mcolumns \u001b[39m=\u001b[39m [\u001b[39mself\u001b[39m\u001b[39m.\u001b[39mname]\n",
      "File \u001b[1;32me:\\app\\Scoop\\apps\\miniconda3\\current\\envs\\igem\\lib\\site-packages\\pandas\\core\\frame.py:2806\u001b[0m, in \u001b[0;36mDataFrame.__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   2804\u001b[0m     \u001b[39mif\u001b[39;00m is_iterator(key):\n\u001b[0;32m   2805\u001b[0m         key \u001b[39m=\u001b[39m \u001b[39mlist\u001b[39m(key)\n\u001b[1;32m-> 2806\u001b[0m     indexer \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mloc\u001b[39m.\u001b[39;49m_get_listlike_indexer(key, axis\u001b[39m=\u001b[39;49m\u001b[39m1\u001b[39;49m, raise_missing\u001b[39m=\u001b[39;49m\u001b[39mTrue\u001b[39;49;00m)[\u001b[39m1\u001b[39m]\n\u001b[0;32m   2808\u001b[0m \u001b[39m# take() does not accept boolean indexers\u001b[39;00m\n\u001b[0;32m   2809\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mgetattr\u001b[39m(indexer, \u001b[39m\"\u001b[39m\u001b[39mdtype\u001b[39m\u001b[39m\"\u001b[39m, \u001b[39mNone\u001b[39;00m) \u001b[39m==\u001b[39m \u001b[39mbool\u001b[39m:\n",
      "File \u001b[1;32me:\\app\\Scoop\\apps\\miniconda3\\current\\envs\\igem\\lib\\site-packages\\pandas\\core\\indexing.py:1552\u001b[0m, in \u001b[0;36m_NDFrameIndexer._get_listlike_indexer\u001b[1;34m(self, key, axis, raise_missing)\u001b[0m\n\u001b[0;32m   1549\u001b[0m \u001b[39melse\u001b[39;00m:\n\u001b[0;32m   1550\u001b[0m     keyarr, indexer, new_indexer \u001b[39m=\u001b[39m ax\u001b[39m.\u001b[39m_reindex_non_unique(keyarr)\n\u001b[1;32m-> 1552\u001b[0m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_validate_read_indexer(\n\u001b[0;32m   1553\u001b[0m     keyarr, indexer, o\u001b[39m.\u001b[39;49m_get_axis_number(axis), raise_missing\u001b[39m=\u001b[39;49mraise_missing\n\u001b[0;32m   1554\u001b[0m )\n\u001b[0;32m   1555\u001b[0m \u001b[39mreturn\u001b[39;00m keyarr, indexer\n",
      "File \u001b[1;32me:\\app\\Scoop\\apps\\miniconda3\\current\\envs\\igem\\lib\\site-packages\\pandas\\core\\indexing.py:1640\u001b[0m, in \u001b[0;36m_NDFrameIndexer._validate_read_indexer\u001b[1;34m(self, key, indexer, axis, raise_missing)\u001b[0m\n\u001b[0;32m   1638\u001b[0m \u001b[39mif\u001b[39;00m missing \u001b[39m==\u001b[39m \u001b[39mlen\u001b[39m(indexer):\n\u001b[0;32m   1639\u001b[0m     axis_name \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mobj\u001b[39m.\u001b[39m_get_axis_name(axis)\n\u001b[1;32m-> 1640\u001b[0m     \u001b[39mraise\u001b[39;00m \u001b[39mKeyError\u001b[39;00m(\u001b[39mf\u001b[39m\u001b[39m\"\u001b[39m\u001b[39mNone of [\u001b[39m\u001b[39m{\u001b[39;00mkey\u001b[39m}\u001b[39;00m\u001b[39m] are in the [\u001b[39m\u001b[39m{\u001b[39;00maxis_name\u001b[39m}\u001b[39;00m\u001b[39m]\u001b[39m\u001b[39m\"\u001b[39m)\n\u001b[0;32m   1642\u001b[0m \u001b[39m# We (temporarily) allow for some missing keys with .loc, except in\u001b[39;00m\n\u001b[0;32m   1643\u001b[0m \u001b[39m# some cases (e.g. setting) in which \"raise_missing\" will be False\u001b[39;00m\n\u001b[0;32m   1644\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mnot\u001b[39;00m (\u001b[39mself\u001b[39m\u001b[39m.\u001b[39mname \u001b[39m==\u001b[39m \u001b[39m\"\u001b[39m\u001b[39mloc\u001b[39m\u001b[39m\"\u001b[39m \u001b[39mand\u001b[39;00m \u001b[39mnot\u001b[39;00m raise_missing):\n",
      "\u001b[1;31mKeyError\u001b[0m: \"None of [Index(['value'], dtype='object')] are in the [columns]\""
     ]
    }
   ],
   "source": [
    "infection = []\n",
    "n = 0\n",
    "# value = [] we want 2-d dataframe\n",
    "merged = gse.merge_and_average(\n",
    "    GPL570,\n",
    "    'value',\n",
    "    'GB_ACC',\n",
    "    gpl_on='ID',\n",
    "    gsm_on='ID_REF'\n",
    ")\n",
    "merged = merged[\n",
    "    sorted(merged.columns)\n",
    "] \n",
    "\n",
    "value = pd.DataFrame()\n",
    "for i in gse.gsms:\n",
    "    n += 1\n",
    "\n",
    "    # 感染状态的处理\n",
    "    characteristics_ch1 = gse.gsms[i].metadata['characteristics_ch1']   # the infection_status is in the characteristics_ch1\n",
    "    infection_status = characteristics_ch1[-1].split(':')[1].strip()            # 但characteristics_ch1有的有两个值，有的有三个，取最后一个\n",
    "    infection.append(infection_status)\n",
    "    \n",
    "    # 对数等的处理 # 可能不同做法\n",
    "    if gse.gsms[i].metadata['platform_id'] == ['GPL96']: #  MAS5-calculated Signal intensityzs\n",
    "        value = pd.concat([value, sample_value.iloc[n-1]], axis=1)\n",
    "        #部分样品跑偏严重\n",
    "        # infection.pop() \n",
    "    elif gse.gsms[i].metadata['platform_id'] == ['GPL570']: #  MAS5-calculated Signal intensity\n",
    "        # value = pd.concat([value, sample_value.iloc[n-1]], axis=1)\n",
    "        # 直接代入导致分数分布大量超过1（最大值为1），所以先不用\n",
    "        infection.pop() \n",
    "    elif gse.gsms[i].metadata['platform_id'] == ['GPL571']:\n",
    "        value = pd.concat([value, 2**sample_value.iloc[n-1]], axis=1)\n",
    "    elif gse.gsms[i].metadata['platform_id'] == ['GPL2507']: #  Normalized value from BeadStudio software\n",
    "        # value = pd.concat([value, 2**sample_value.iloc[n-1]], axis=1)\n",
    "        # 名不一样，大量nan\n",
    "        infection.pop() # 把刚刚加进去的感染状态也删了\n",
    "    else:\n",
    "        print(gse.gsms[i].columns[\"description\"][1])\n",
    "        print('Now just use the raw value')\n",
    "        print('Samples: ', sample_value.iloc[n-1].head())\n",
    "        print('-'*50)\n",
    "        value = pd.concat([value, sample_value.iloc[n-1]], axis=1)\n",
    "\n",
    "value = value.T\n",
    "\n",
    "# merge the infection status and value\n",
    "value.insert(0, 'infection_status', infection)\n",
    "value.dropna(axis=1, how='all', inplace=True)\n",
    "value.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the data and Show the first 5 rows\n",
    "value.to_csv('./datasets/' + GEONAME + '.csv', index=False)\n",
    "value.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 不用了"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "不清楚什么时候再把这个id转化成通用的Gene Symbol。\n",
    "\n",
    "可能在这里就转，也可以分析结束了再转。\n",
    "\n",
    "大概需要10分钟"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get all Series_platform_id\n",
    "gpls = gse.metadata['platform_id']\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# GPL571 = gse.gpls['GPL571']\n",
    "# n = 0\n",
    "# for rows in sample_value:\n",
    "#     Gene_Symbol = GPL571.table[GPL571.table['ID'] == rows]['Gene Symbol']\n",
    "#     if Gene_Symbol.empty:\n",
    "#         Gene_Symbol[0] = rows\n",
    "#     sample_value.rename(columns={rows: Gene_Symbol.values[0]}, inplace=True)\n",
    "#     n += 1\n",
    "#     if n % 100 == 0:\n",
    "#         print(\"Finished %d rows of %d at %s\" % (n, sample_value.shape[1], datetime.datetime.now()))\n",
    "# sample_value.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# gse.gpls['GPL571'].table.columns # GPL571, 也就是这个GEO数据集的平台，的列。\n",
    "# # 从这个平台可以直接读取Gene Symbol，Target Description等等有用的东西。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# gse.gpls['GPL571'].table.head() # 前5行"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "igem",
   "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.16"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}