3.1_making_top_snp_table_FG.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7b8719ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os\n",
    "from collections import defaultdict\n",
    "from tqdm import tqdm\n",
    "from pprint import pprint\n",
    "\n",
    "from Bio import Entrez\n",
    "Entrez.email = \"sample@bioinf.me\"\n",
    "\n",
    "from functools import lru_cache\n",
    "\n",
    "pd.options.mode.chained_assignment = None  # default='warn'\n",
    "tqdm.pandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "febaafda",
   "metadata": {},
   "source": [
    "## For meta data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "76685734",
   "metadata": {},
   "outputs": [],
   "source": [
    "@lru_cache(maxsize=4096)\n",
    "def return_gene_by_id(uid):\n",
    "    handle = Entrez.esummary(db=\"gene\", id=uid)\n",
    "    uid_record = Entrez.read(handle)\n",
    "    handle.close()\n",
    "    uid_summary = uid_record[\"DocumentSummarySet\"]['DocumentSummary'][0]\n",
    "    return uid_summary['Name']\n",
    "\n",
    "@lru_cache(maxsize=4096)\n",
    "def return_gene_by_rsid(snp_id):\n",
    "    answer = []\n",
    "    record = Entrez.read(Entrez.elink(dbfrom=\"snp\", \n",
    "                                  id=snp_id.replace('rs',''), \n",
    "                                  db=\"gene\"))\n",
    "    results = record[0]['LinkSetDb']\n",
    "    if len(results) < 1:\n",
    "        return None\n",
    "    results = results[0]['Link']\n",
    "    for result in results:\n",
    "        uid = result['Id']\n",
    "        gene_name = return_gene_by_id(uid)\n",
    "        answer.append(gene_name)\n",
    "    return ','.join(answer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "92f401f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # works for server\n",
    "# datas005, best005 = return_best(0.05)\n",
    "# best005.to_csv('top_top_snps05.csv', index=False)\n",
    "# best005"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3508a425",
   "metadata": {},
   "source": [
    "# For FinnData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "5a931857",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<timed exec>:8: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "100%|█████████████████████████████████████████████| 6/6 [00:05<00:00,  1.00it/s]\n",
      "<timed exec>:8: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "100%|█████████████████████████████████████████████| 1/1 [00:01<00:00,  1.80s/it]\n",
      "<timed exec>:8: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "100%|█████████████████████████████████████████| 909/909 [28:48<00:00,  1.90s/it]\n",
      "<timed exec>:8: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "100%|█████████████████████████████████████████| 126/126 [01:44<00:00,  1.21it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 35 s, sys: 6.24 s, total: 41.3 s\n",
      "Wall time: 31min 17s\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "DIR = './data/f_special'\n",
    "all_datas = defaultdict()\n",
    "datas = []\n",
    "files = os.listdir(DIR)\n",
    "for f in files:\n",
    "    if 'hg19lifted' not in f:\n",
    "        continue\n",
    "    d = pd.read_csv(f'{DIR}/{f}', sep='\\t')\n",
    "    trait = f.replace('maf_fg_', '').replace('_hg19lifted.tsv_.tsv', '')\n",
    "    all_datas[trait] = d\n",
    "    d = d[d.pval<0.05/d.shape[0]]\n",
    "    d['trait'] = trait\n",
    "    d['gene'] = d.rsid.progress_apply(return_gene_by_rsid)\n",
    "    datas.append(d[['rsid', 'chr', 'gene', 'pos', 'ref', 'alt', 'maf', 'pval', 'trait']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "35b695ca",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "I9_HYPTENSPREG\t6110809\t8.182222681154002e-09\n",
      "O15_GESTAT_HYPERT\t6110674\t8.182403446821087e-09\n",
      "O15_EXCESS_VOMIT_PREG\t6110714\t8.18234988579076e-09\n",
      "GEST_DIABETES\t6110809\t8.182222681154002e-09\n"
     ]
    }
   ],
   "source": [
    "for t in traits:\n",
    "    print(t, all_datas[t].shape[0], 0.05/all_datas[t].shape[0], sep='\\t')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "caa435ad",
   "metadata": {},
   "source": [
    "## Prepare to sort"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "7aa8403d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'1': 0,\n",
       " '2': 1,\n",
       " '3': 2,\n",
       " '4': 3,\n",
       " '5': 4,\n",
       " '6': 5,\n",
       " '7': 6,\n",
       " '8': 7,\n",
       " '9': 8,\n",
       " '10': 9,\n",
       " '11': 10,\n",
       " '12': 11,\n",
       " '13': 12,\n",
       " '14': 13,\n",
       " '15': 14,\n",
       " '16': 15,\n",
       " '17': 16,\n",
       " '18': 17,\n",
       " '19': 18,\n",
       " '20': 19,\n",
       " '21': 20,\n",
       " '22': 21,\n",
       " 'X': 22,\n",
       " 'Y': 23}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import re\n",
    "\n",
    "def atof(text):\n",
    "    try:\n",
    "        retval = float(text)\n",
    "    except ValueError:\n",
    "        retval = text\n",
    "    return retval\n",
    "\n",
    "def natural_keys(text):\n",
    "    '''\n",
    "    alist.sort(key=natural_keys) sorts in human order\n",
    "    http://nedbatchelder.com/blog/200712/human_sorting.html\n",
    "    (See Toothy's implementation in the comments)\n",
    "    float regex comes from https://stackoverflow.com/a/12643073/190597\n",
    "    '''\n",
    "    return [ atof(c) for c in re.split(r'[+-]?([0-9]+(?:[.][0-9]*)?|[.][0-9]+)', text) ]\n",
    "kk = list(all_datas[trait].chr.astype(str).unique())\n",
    "kk.sort(key=natural_keys)\n",
    "order = {i:j for i, j in zip(kk, range(len(kk)))}\n",
    "order"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8bf9d592",
   "metadata": {},
   "source": [
    "## Sort and polish"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ad9b4e11",
   "metadata": {},
   "outputs": [],
   "source": [
    "STARTING_GROUP = 0\n",
    "\n",
    "def make_group_col(df, col, group_col='group'):\n",
    "    global STARTING_GROUP\n",
    "    df[group_col] = None\n",
    "    df.groupby((df[col].shift() != df[col]).cumsum())\n",
    "    for k, v in df.groupby((df[col].shift() != df[col]).cumsum()):\n",
    "        df.loc[v.index,group_col] = STARTING_GROUP+k\n",
    "    STARTING_GROUP += k"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "dc222157",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(datas)):\n",
    "    datas[i].pos = datas[i].pos.astype(int)\n",
    "    datas[i].chr = datas[i].chr.astype(str)\n",
    "    datas[i]['chr_order'] = datas[i].chr.apply(lambda x: order[x])\n",
    "    datas[i]['chr_gene'] = datas[i].chr + '_' + datas[i].gene.astype(str)\n",
    "    datas[i] = datas[i].sort_values(by=['chr_order', 'pos'])\n",
    "    make_group_col(datas[i], 'chr_gene', 'group')\n",
    "    datas[i] = datas[i].drop(['chr_order', 'chr_gene'], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b94f38d1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(51, 10)\n"
     ]
    },
    {
     "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>rsid</th>\n",
       "      <th>chr</th>\n",
       "      <th>gene</th>\n",
       "      <th>pos</th>\n",
       "      <th>ref</th>\n",
       "      <th>alt</th>\n",
       "      <th>maf</th>\n",
       "      <th>pval</th>\n",
       "      <th>trait</th>\n",
       "      <th>group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5547625</th>\n",
       "      <td>rs58835482,rs796221113</td>\n",
       "      <td>19</td>\n",
       "      <td>None</td>\n",
       "      <td>18493064</td>\n",
       "      <td>TGGGGGCACCCTGA</td>\n",
       "      <td>T</td>\n",
       "      <td>0.2560</td>\n",
       "      <td>6.060000e-09</td>\n",
       "      <td>O15_EXCESS_VOMIT_PREG</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5547631</th>\n",
       "      <td>rs117110356</td>\n",
       "      <td>19</td>\n",
       "      <td>GDF15</td>\n",
       "      <td>18494944</td>\n",
       "      <td>T</td>\n",
       "      <td>A</td>\n",
       "      <td>0.2550</td>\n",
       "      <td>7.970000e-09</td>\n",
       "      <td>O15_EXCESS_VOMIT_PREG</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5547635</th>\n",
       "      <td>rs75347775</td>\n",
       "      <td>19</td>\n",
       "      <td>MIR3189,GDF15</td>\n",
       "      <td>18495908</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>0.2560</td>\n",
       "      <td>6.110000e-09</td>\n",
       "      <td>O15_EXCESS_VOMIT_PREG</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5547648</th>\n",
       "      <td>rs1058587</td>\n",
       "      <td>19</td>\n",
       "      <td>GDF15</td>\n",
       "      <td>18499422</td>\n",
       "      <td>C</td>\n",
       "      <td>G</td>\n",
       "      <td>0.2700</td>\n",
       "      <td>7.290000e-09</td>\n",
       "      <td>O15_EXCESS_VOMIT_PREG</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5547675</th>\n",
       "      <td>rs45543339</td>\n",
       "      <td>19</td>\n",
       "      <td>LRRC25</td>\n",
       "      <td>18503194</td>\n",
       "      <td>C</td>\n",
       "      <td>T</td>\n",
       "      <td>0.2650</td>\n",
       "      <td>8.180000e-09</td>\n",
       "      <td>O15_EXCESS_VOMIT_PREG</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5740980</th>\n",
       "      <td>rs2208589</td>\n",
       "      <td>20</td>\n",
       "      <td>PREX1</td>\n",
       "      <td>47408414</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>0.1120</td>\n",
       "      <td>6.330000e-09</td>\n",
       "      <td>O15_GESTAT_HYPERT</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>785161</th>\n",
       "      <td>rs780094</td>\n",
       "      <td>2</td>\n",
       "      <td>GCKR</td>\n",
       "      <td>27741237</td>\n",
       "      <td>T</td>\n",
       "      <td>C</td>\n",
       "      <td>0.3560</td>\n",
       "      <td>2.670000e-09</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2428901</th>\n",
       "      <td>rs9268403</td>\n",
       "      <td>6</td>\n",
       "      <td>TSBP1-AS1,TSBP1</td>\n",
       "      <td>32341473</td>\n",
       "      <td>T</td>\n",
       "      <td>C</td>\n",
       "      <td>0.2010</td>\n",
       "      <td>1.360000e-09</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2428967</th>\n",
       "      <td>rs1980495</td>\n",
       "      <td>6</td>\n",
       "      <td>TSBP1-AS1</td>\n",
       "      <td>32346794</td>\n",
       "      <td>A</td>\n",
       "      <td>C</td>\n",
       "      <td>0.2020</td>\n",
       "      <td>8.800000e-10</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2429050</th>\n",
       "      <td>rs9268474</td>\n",
       "      <td>6</td>\n",
       "      <td>TSBP1-AS1,HCG23</td>\n",
       "      <td>32357165</td>\n",
       "      <td>T</td>\n",
       "      <td>C</td>\n",
       "      <td>0.2010</td>\n",
       "      <td>1.400000e-09</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2429324</th>\n",
       "      <td>rs3806155</td>\n",
       "      <td>6</td>\n",
       "      <td>TSBP1-AS1,BTNL2</td>\n",
       "      <td>32373378</td>\n",
       "      <td>A</td>\n",
       "      <td>T</td>\n",
       "      <td>0.0545</td>\n",
       "      <td>2.110000e-17</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2429353</th>\n",
       "      <td>rs3763312</td>\n",
       "      <td>6</td>\n",
       "      <td>BTNL2</td>\n",
       "      <td>32376348</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>0.1910</td>\n",
       "      <td>1.190000e-09</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2429619</th>\n",
       "      <td>rs2395175</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>32405026</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>0.1530</td>\n",
       "      <td>1.280000e-10</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2429626</th>\n",
       "      <td>rs9268627</td>\n",
       "      <td>6</td>\n",
       "      <td>HLA-DRA</td>\n",
       "      <td>32405821</td>\n",
       "      <td>T</td>\n",
       "      <td>C</td>\n",
       "      <td>0.1880</td>\n",
       "      <td>2.640000e-09</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2430379</th>\n",
       "      <td>rs373088803,rs34992176</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>32448052</td>\n",
       "      <td>A</td>\n",
       "      <td>AG</td>\n",
       "      <td>0.1710</td>\n",
       "      <td>9.140000e-12</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2430474</th>\n",
       "      <td>rs116384190</td>\n",
       "      <td>6</td>\n",
       "      <td>HLA-DRB6,HLA-DRB4</td>\n",
       "      <td>32520337</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>0.2440</td>\n",
       "      <td>2.260000e-10</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2430488</th>\n",
       "      <td>rs113276642</td>\n",
       "      <td>6</td>\n",
       "      <td>HLA-DRB6</td>\n",
       "      <td>32525000</td>\n",
       "      <td>T</td>\n",
       "      <td>G</td>\n",
       "      <td>0.2380</td>\n",
       "      <td>1.950000e-10</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2430580</th>\n",
       "      <td>rs114335056</td>\n",
       "      <td>6</td>\n",
       "      <td>HLA-DRB1</td>\n",
       "      <td>32559191</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>0.1520</td>\n",
       "      <td>3.160000e-13</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2430763</th>\n",
       "      <td>rs17804379</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>32561928</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "      <td>0.1520</td>\n",
       "      <td>2.810000e-13</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2432549</th>\n",
       "      <td>rs9272131</td>\n",
       "      <td>6</td>\n",
       "      <td>LOC107987459,LOC107987449</td>\n",
       "      <td>32600671</td>\n",
       "      <td>C</td>\n",
       "      <td>T</td>\n",
       "      <td>0.1140</td>\n",
       "      <td>3.420000e-17</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2432768</th>\n",
       "      <td>rs9272353</td>\n",
       "      <td>6</td>\n",
       "      <td>HLA-DQA1</td>\n",
       "      <td>32604456</td>\n",
       "      <td>G</td>\n",
       "      <td>C</td>\n",
       "      <td>0.1940</td>\n",
       "      <td>2.640000e-10</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2432806</th>\n",
       "      <td>rs1846183</td>\n",
       "      <td>6</td>\n",
       "      <td>LOC107986589,HLA-DQA1</td>\n",
       "      <td>32611467</td>\n",
       "      <td>A</td>\n",
       "      <td>C</td>\n",
       "      <td>0.1530</td>\n",
       "      <td>1.990000e-12</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2433009</th>\n",
       "      <td>rs2858877</td>\n",
       "      <td>6</td>\n",
       "      <td>HLA-DQA1</td>\n",
       "      <td>32620549</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "      <td>0.1920</td>\n",
       "      <td>1.840000e-10</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2433039</th>\n",
       "      <td>rs2395533</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>32623277</td>\n",
       "      <td>C</td>\n",
       "      <td>T</td>\n",
       "      <td>0.1780</td>\n",
       "      <td>3.870000e-12</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2433069</th>\n",
       "      <td>rs6905775</td>\n",
       "      <td>6</td>\n",
       "      <td>HLA-DQB1-AS1</td>\n",
       "      <td>32626021</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>0.1770</td>\n",
       "      <td>4.020000e-12</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2433127</th>\n",
       "      <td>rs9273401</td>\n",
       "      <td>6</td>\n",
       "      <td>HLA-DQB1-AS1,HLA-DQB1</td>\n",
       "      <td>32627129</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>0.1220</td>\n",
       "      <td>9.010000e-18</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2433423</th>\n",
       "      <td>rs9274619</td>\n",
       "      <td>6</td>\n",
       "      <td>HLA-DQB1</td>\n",
       "      <td>32635954</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>0.1200</td>\n",
       "      <td>3.360000e-18</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2434534</th>\n",
       "      <td>rs9275373</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>32668411</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>0.1220</td>\n",
       "      <td>4.360000e-19</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2435035</th>\n",
       "      <td>rs9275638</td>\n",
       "      <td>6</td>\n",
       "      <td>LOC102725019</td>\n",
       "      <td>32684760</td>\n",
       "      <td>C</td>\n",
       "      <td>T</td>\n",
       "      <td>0.1470</td>\n",
       "      <td>3.770000e-12</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2435935</th>\n",
       "      <td>rs115018313</td>\n",
       "      <td>6</td>\n",
       "      <td>HLA-DQA2</td>\n",
       "      <td>32710407</td>\n",
       "      <td>T</td>\n",
       "      <td>C</td>\n",
       "      <td>0.0521</td>\n",
       "      <td>1.960000e-16</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2436357</th>\n",
       "      <td>rs57912571</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>32750921</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>0.0530</td>\n",
       "      <td>5.000000e-15</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2436514</th>\n",
       "      <td>rs58327373</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>32757589</td>\n",
       "      <td>A</td>\n",
       "      <td>T</td>\n",
       "      <td>0.0530</td>\n",
       "      <td>5.000000e-15</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2436566</th>\n",
       "      <td>rs2067578</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>32762461</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>0.0530</td>\n",
       "      <td>5.000000e-15</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2436792</th>\n",
       "      <td>rs55888430</td>\n",
       "      <td>6</td>\n",
       "      <td>HLA-DOB</td>\n",
       "      <td>32785142</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>0.0544</td>\n",
       "      <td>7.260000e-15</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2436817</th>\n",
       "      <td>rs17220157</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>32787592</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>0.0544</td>\n",
       "      <td>7.190000e-15</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2436818</th>\n",
       "      <td>rs17213749</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>32787651</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>0.0544</td>\n",
       "      <td>7.190000e-15</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2436855</th>\n",
       "      <td>rs73410789</td>\n",
       "      <td>6</td>\n",
       "      <td>TAP2</td>\n",
       "      <td>32793721</td>\n",
       "      <td>G</td>\n",
       "      <td>C</td>\n",
       "      <td>0.0543</td>\n",
       "      <td>7.960000e-15</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2437845</th>\n",
       "      <td>rs112393700</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>33017895</td>\n",
       "      <td>C</td>\n",
       "      <td>T</td>\n",
       "      <td>0.0510</td>\n",
       "      <td>2.140000e-09</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3575354</th>\n",
       "      <td>rs10659211</td>\n",
       "      <td>10</td>\n",
       "      <td>TCF7L2</td>\n",
       "      <td>114782581</td>\n",
       "      <td>G</td>\n",
       "      <td>GCT</td>\n",
       "      <td>0.1950</td>\n",
       "      <td>1.690000e-18</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4130705</th>\n",
       "      <td>rs144683127,rs71305389</td>\n",
       "      <td>11</td>\n",
       "      <td>None</td>\n",
       "      <td>92695708</td>\n",
       "      <td>T</td>\n",
       "      <td>TTGAGAAATCTCTATACTATTTTCCATAGTGACTG</td>\n",
       "      <td>0.3520</td>\n",
       "      <td>8.010000e-60</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4130724</th>\n",
       "      <td>rs10830963</td>\n",
       "      <td>11</td>\n",
       "      <td>MTNR1B</td>\n",
       "      <td>92708710</td>\n",
       "      <td>C</td>\n",
       "      <td>G</td>\n",
       "      <td>0.3580</td>\n",
       "      <td>7.860000e-84</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4130753</th>\n",
       "      <td>rs6483212</td>\n",
       "      <td>11</td>\n",
       "      <td>None</td>\n",
       "      <td>92725240</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>0.4210</td>\n",
       "      <td>4.080000e-30</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42458</th>\n",
       "      <td>rs17367504</td>\n",
       "      <td>1</td>\n",
       "      <td>MTHFR</td>\n",
       "      <td>11862778</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>0.1440</td>\n",
       "      <td>2.080000e-10</td>\n",
       "      <td>I9_HYPTENSPREG</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42479</th>\n",
       "      <td>rs13306561</td>\n",
       "      <td>1</td>\n",
       "      <td>MTHFR,CLCN6</td>\n",
       "      <td>11865804</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>0.1450</td>\n",
       "      <td>1.820000e-10</td>\n",
       "      <td>I9_HYPTENSPREG</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42740</th>\n",
       "      <td>rs12567136</td>\n",
       "      <td>1</td>\n",
       "      <td>CLCN6</td>\n",
       "      <td>11883731</td>\n",
       "      <td>C</td>\n",
       "      <td>T</td>\n",
       "      <td>0.1450</td>\n",
       "      <td>3.320000e-10</td>\n",
       "      <td>I9_HYPTENSPREG</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1771837</th>\n",
       "      <td>rs10857147</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>81181072</td>\n",
       "      <td>A</td>\n",
       "      <td>T</td>\n",
       "      <td>0.3130</td>\n",
       "      <td>1.770000e-11</td>\n",
       "      <td>I9_HYPTENSPREG</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1771857</th>\n",
       "      <td>rs35954793</td>\n",
       "      <td>4</td>\n",
       "      <td>FGF5</td>\n",
       "      <td>81188513</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "      <td>0.2570</td>\n",
       "      <td>1.600000e-11</td>\n",
       "      <td>I9_HYPTENSPREG</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3839165</th>\n",
       "      <td>rs10882398</td>\n",
       "      <td>10</td>\n",
       "      <td>PLCE1</td>\n",
       "      <td>95892788</td>\n",
       "      <td>T</td>\n",
       "      <td>A</td>\n",
       "      <td>0.4060</td>\n",
       "      <td>3.480000e-09</td>\n",
       "      <td>I9_HYPTENSPREG</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5530423</th>\n",
       "      <td>rs167479</td>\n",
       "      <td>19</td>\n",
       "      <td>RGL3</td>\n",
       "      <td>11526765</td>\n",
       "      <td>T</td>\n",
       "      <td>G</td>\n",
       "      <td>0.4260</td>\n",
       "      <td>5.130000e-12</td>\n",
       "      <td>I9_HYPTENSPREG</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5767640</th>\n",
       "      <td>rs796318265</td>\n",
       "      <td>20</td>\n",
       "      <td>None</td>\n",
       "      <td>57712334</td>\n",
       "      <td>C</td>\n",
       "      <td>CT</td>\n",
       "      <td>0.1420</td>\n",
       "      <td>1.380000e-09</td>\n",
       "      <td>I9_HYPTENSPREG</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5767686</th>\n",
       "      <td>rs259983</td>\n",
       "      <td>20</td>\n",
       "      <td>ZNF831</td>\n",
       "      <td>57735457</td>\n",
       "      <td>A</td>\n",
       "      <td>C</td>\n",
       "      <td>0.1810</td>\n",
       "      <td>1.620000e-09</td>\n",
       "      <td>I9_HYPTENSPREG</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           rsid chr                       gene        pos  \\\n",
       "5547625  rs58835482,rs796221113  19                       None   18493064   \n",
       "5547631             rs117110356  19                      GDF15   18494944   \n",
       "5547635              rs75347775  19              MIR3189,GDF15   18495908   \n",
       "5547648               rs1058587  19                      GDF15   18499422   \n",
       "5547675              rs45543339  19                     LRRC25   18503194   \n",
       "5740980               rs2208589  20                      PREX1   47408414   \n",
       "785161                 rs780094   2                       GCKR   27741237   \n",
       "2428901               rs9268403   6            TSBP1-AS1,TSBP1   32341473   \n",
       "2428967               rs1980495   6                  TSBP1-AS1   32346794   \n",
       "2429050               rs9268474   6            TSBP1-AS1,HCG23   32357165   \n",
       "2429324               rs3806155   6            TSBP1-AS1,BTNL2   32373378   \n",
       "2429353               rs3763312   6                      BTNL2   32376348   \n",
       "2429619               rs2395175   6                       None   32405026   \n",
       "2429626               rs9268627   6                    HLA-DRA   32405821   \n",
       "2430379  rs373088803,rs34992176   6                       None   32448052   \n",
       "2430474             rs116384190   6          HLA-DRB6,HLA-DRB4   32520337   \n",
       "2430488             rs113276642   6                   HLA-DRB6   32525000   \n",
       "2430580             rs114335056   6                   HLA-DRB1   32559191   \n",
       "2430763              rs17804379   6                       None   32561928   \n",
       "2432549               rs9272131   6  LOC107987459,LOC107987449   32600671   \n",
       "2432768               rs9272353   6                   HLA-DQA1   32604456   \n",
       "2432806               rs1846183   6      LOC107986589,HLA-DQA1   32611467   \n",
       "2433009               rs2858877   6                   HLA-DQA1   32620549   \n",
       "2433039               rs2395533   6                       None   32623277   \n",
       "2433069               rs6905775   6               HLA-DQB1-AS1   32626021   \n",
       "2433127               rs9273401   6      HLA-DQB1-AS1,HLA-DQB1   32627129   \n",
       "2433423               rs9274619   6                   HLA-DQB1   32635954   \n",
       "2434534               rs9275373   6                       None   32668411   \n",
       "2435035               rs9275638   6               LOC102725019   32684760   \n",
       "2435935             rs115018313   6                   HLA-DQA2   32710407   \n",
       "2436357              rs57912571   6                       None   32750921   \n",
       "2436514              rs58327373   6                       None   32757589   \n",
       "2436566               rs2067578   6                       None   32762461   \n",
       "2436792              rs55888430   6                    HLA-DOB   32785142   \n",
       "2436817              rs17220157   6                       None   32787592   \n",
       "2436818              rs17213749   6                       None   32787651   \n",
       "2436855              rs73410789   6                       TAP2   32793721   \n",
       "2437845             rs112393700   6                       None   33017895   \n",
       "3575354              rs10659211  10                     TCF7L2  114782581   \n",
       "4130705  rs144683127,rs71305389  11                       None   92695708   \n",
       "4130724              rs10830963  11                     MTNR1B   92708710   \n",
       "4130753               rs6483212  11                       None   92725240   \n",
       "42458                rs17367504   1                      MTHFR   11862778   \n",
       "42479                rs13306561   1                MTHFR,CLCN6   11865804   \n",
       "42740                rs12567136   1                      CLCN6   11883731   \n",
       "1771837              rs10857147   4                       None   81181072   \n",
       "1771857              rs35954793   4                       FGF5   81188513   \n",
       "3839165              rs10882398  10                      PLCE1   95892788   \n",
       "5530423                rs167479  19                       RGL3   11526765   \n",
       "5767640             rs796318265  20                       None   57712334   \n",
       "5767686                rs259983  20                     ZNF831   57735457   \n",
       "\n",
       "                    ref                                  alt     maf  \\\n",
       "5547625  TGGGGGCACCCTGA                                    T  0.2560   \n",
       "5547631               T                                    A  0.2550   \n",
       "5547635               G                                    A  0.2560   \n",
       "5547648               C                                    G  0.2700   \n",
       "5547675               C                                    T  0.2650   \n",
       "5740980               A                                    G  0.1120   \n",
       "785161                T                                    C  0.3560   \n",
       "2428901               T                                    C  0.2010   \n",
       "2428967               A                                    C  0.2020   \n",
       "2429050               T                                    C  0.2010   \n",
       "2429324               A                                    T  0.0545   \n",
       "2429353               G                                    A  0.1910   \n",
       "2429619               G                                    A  0.1530   \n",
       "2429626               T                                    C  0.1880   \n",
       "2430379               A                                   AG  0.1710   \n",
       "2430474               A                                    G  0.2440   \n",
       "2430488               T                                    G  0.2380   \n",
       "2430580               A                                    G  0.1520   \n",
       "2430763               C                                    A  0.1520   \n",
       "2432549               C                                    T  0.1140   \n",
       "2432768               G                                    C  0.1940   \n",
       "2432806               A                                    C  0.1530   \n",
       "2433009               C                                    A  0.1920   \n",
       "2433039               C                                    T  0.1780   \n",
       "2433069               G                                    A  0.1770   \n",
       "2433127               A                                    G  0.1220   \n",
       "2433423               G                                    A  0.1200   \n",
       "2434534               G                                    A  0.1220   \n",
       "2435035               C                                    T  0.1470   \n",
       "2435935               T                                    C  0.0521   \n",
       "2436357               A                                    G  0.0530   \n",
       "2436514               A                                    T  0.0530   \n",
       "2436566               G                                    A  0.0530   \n",
       "2436792               G                                    A  0.0544   \n",
       "2436817               G                                    A  0.0544   \n",
       "2436818               G                                    A  0.0544   \n",
       "2436855               G                                    C  0.0543   \n",
       "2437845               C                                    T  0.0510   \n",
       "3575354               G                                  GCT  0.1950   \n",
       "4130705               T  TTGAGAAATCTCTATACTATTTTCCATAGTGACTG  0.3520   \n",
       "4130724               C                                    G  0.3580   \n",
       "4130753               A                                    G  0.4210   \n",
       "42458                 A                                    G  0.1440   \n",
       "42479                 A                                    G  0.1450   \n",
       "42740                 C                                    T  0.1450   \n",
       "1771837               A                                    T  0.3130   \n",
       "1771857               C                                    A  0.2570   \n",
       "3839165               T                                    A  0.4060   \n",
       "5530423               T                                    G  0.4260   \n",
       "5767640               C                                   CT  0.1420   \n",
       "5767686               A                                    C  0.1810   \n",
       "\n",
       "                 pval                  trait group  \n",
       "5547625  6.060000e-09  O15_EXCESS_VOMIT_PREG     1  \n",
       "5547631  7.970000e-09  O15_EXCESS_VOMIT_PREG     2  \n",
       "5547635  6.110000e-09  O15_EXCESS_VOMIT_PREG     3  \n",
       "5547648  7.290000e-09  O15_EXCESS_VOMIT_PREG     4  \n",
       "5547675  8.180000e-09  O15_EXCESS_VOMIT_PREG     5  \n",
       "5740980  6.330000e-09      O15_GESTAT_HYPERT     6  \n",
       "785161   2.670000e-09          GEST_DIABETES     7  \n",
       "2428901  1.360000e-09          GEST_DIABETES     8  \n",
       "2428967  8.800000e-10          GEST_DIABETES     9  \n",
       "2429050  1.400000e-09          GEST_DIABETES    10  \n",
       "2429324  2.110000e-17          GEST_DIABETES    11  \n",
       "2429353  1.190000e-09          GEST_DIABETES    12  \n",
       "2429619  1.280000e-10          GEST_DIABETES    13  \n",
       "2429626  2.640000e-09          GEST_DIABETES    14  \n",
       "2430379  9.140000e-12          GEST_DIABETES    15  \n",
       "2430474  2.260000e-10          GEST_DIABETES    16  \n",
       "2430488  1.950000e-10          GEST_DIABETES    17  \n",
       "2430580  3.160000e-13          GEST_DIABETES    18  \n",
       "2430763  2.810000e-13          GEST_DIABETES    19  \n",
       "2432549  3.420000e-17          GEST_DIABETES    20  \n",
       "2432768  2.640000e-10          GEST_DIABETES    21  \n",
       "2432806  1.990000e-12          GEST_DIABETES    22  \n",
       "2433009  1.840000e-10          GEST_DIABETES    23  \n",
       "2433039  3.870000e-12          GEST_DIABETES    24  \n",
       "2433069  4.020000e-12          GEST_DIABETES    25  \n",
       "2433127  9.010000e-18          GEST_DIABETES    26  \n",
       "2433423  3.360000e-18          GEST_DIABETES    27  \n",
       "2434534  4.360000e-19          GEST_DIABETES    28  \n",
       "2435035  3.770000e-12          GEST_DIABETES    29  \n",
       "2435935  1.960000e-16          GEST_DIABETES    30  \n",
       "2436357  5.000000e-15          GEST_DIABETES    31  \n",
       "2436514  5.000000e-15          GEST_DIABETES    31  \n",
       "2436566  5.000000e-15          GEST_DIABETES    31  \n",
       "2436792  7.260000e-15          GEST_DIABETES    32  \n",
       "2436817  7.190000e-15          GEST_DIABETES    33  \n",
       "2436818  7.190000e-15          GEST_DIABETES    33  \n",
       "2436855  7.960000e-15          GEST_DIABETES    34  \n",
       "2437845  2.140000e-09          GEST_DIABETES    35  \n",
       "3575354  1.690000e-18          GEST_DIABETES    36  \n",
       "4130705  8.010000e-60          GEST_DIABETES    37  \n",
       "4130724  7.860000e-84          GEST_DIABETES    38  \n",
       "4130753  4.080000e-30          GEST_DIABETES    39  \n",
       "42458    2.080000e-10         I9_HYPTENSPREG    40  \n",
       "42479    1.820000e-10         I9_HYPTENSPREG    41  \n",
       "42740    3.320000e-10         I9_HYPTENSPREG    42  \n",
       "1771837  1.770000e-11         I9_HYPTENSPREG    43  \n",
       "1771857  1.600000e-11         I9_HYPTENSPREG    44  \n",
       "3839165  3.480000e-09         I9_HYPTENSPREG    45  \n",
       "5530423  5.130000e-12         I9_HYPTENSPREG    46  \n",
       "5767640  1.380000e-09         I9_HYPTENSPREG    47  \n",
       "5767686  1.620000e-09         I9_HYPTENSPREG    48  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "best_datas = []\n",
    "for data in datas:\n",
    "    data\n",
    "    best = data.groupby('group').agg(\n",
    "        pval=pd.NamedAgg(column=\"pval\", aggfunc=\"min\"))\n",
    "    filtered_data = data[data.pval.isin(best.pval)]\n",
    "    # может произойти что в одной хромосоме данный пвал макс, а в другой нет \n",
    "    # - важно взять нужную и только ее! \n",
    "    flags = []\n",
    "    for key, value in filtered_data.iterrows():\n",
    "        flags.append(best[best.pval==value.pval].index[0]==value.group)\n",
    "    best_datas.append(filtered_data[flags])\n",
    "final = pd.concat(best_datas)\n",
    "final.to_csv('./data/finn_top.tsv', sep='\\t', index=False)\n",
    "print(final.shape)\n",
    "final"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5911c36a",
   "metadata": {},
   "source": [
    "Next, you should remove unnecessary SNPs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "7a8e7ee8",
   "metadata": {},
   "outputs": [],
   "source": [
    "traits_order = {'I9_HYPTENSPREG':0, 'O15_GESTAT_HYPERT':1, 'O15_EXCESS_VOMIT_PREG':2,  'GEST_DIABETES':3}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "f916146a",
   "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>rsid</th>\n",
       "      <th>chr</th>\n",
       "      <th>gene</th>\n",
       "      <th>pos</th>\n",
       "      <th>ref</th>\n",
       "      <th>alt</th>\n",
       "      <th>maf</th>\n",
       "      <th>pval</th>\n",
       "      <th>trait</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>rs13306561</td>\n",
       "      <td>1</td>\n",
       "      <td>MTHFR,CLCN6</td>\n",
       "      <td>11865804</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>0.145</td>\n",
       "      <td>1.820000e-10</td>\n",
       "      <td>I9_HYPTENSPREG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>rs35954793</td>\n",
       "      <td>4</td>\n",
       "      <td>FGF5</td>\n",
       "      <td>81188513</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "      <td>0.257</td>\n",
       "      <td>1.600000e-11</td>\n",
       "      <td>I9_HYPTENSPREG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>rs10882398</td>\n",
       "      <td>10</td>\n",
       "      <td>PLCE1</td>\n",
       "      <td>95892788</td>\n",
       "      <td>T</td>\n",
       "      <td>A</td>\n",
       "      <td>0.406</td>\n",
       "      <td>3.480000e-09</td>\n",
       "      <td>I9_HYPTENSPREG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>rs167479</td>\n",
       "      <td>19</td>\n",
       "      <td>RGL3</td>\n",
       "      <td>11526765</td>\n",
       "      <td>T</td>\n",
       "      <td>G</td>\n",
       "      <td>0.426</td>\n",
       "      <td>5.130000e-12</td>\n",
       "      <td>I9_HYPTENSPREG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>rs259983</td>\n",
       "      <td>20</td>\n",
       "      <td>ZNF831</td>\n",
       "      <td>57735457</td>\n",
       "      <td>A</td>\n",
       "      <td>C</td>\n",
       "      <td>0.181</td>\n",
       "      <td>1.620000e-09</td>\n",
       "      <td>I9_HYPTENSPREG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>rs2208589</td>\n",
       "      <td>20</td>\n",
       "      <td>PREX1</td>\n",
       "      <td>47408414</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>0.112</td>\n",
       "      <td>6.330000e-09</td>\n",
       "      <td>O15_GESTAT_HYPERT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>rs58835482</td>\n",
       "      <td>19</td>\n",
       "      <td>GDF15</td>\n",
       "      <td>18493064</td>\n",
       "      <td>TGGGGGCACCCTGA</td>\n",
       "      <td>T</td>\n",
       "      <td>0.256</td>\n",
       "      <td>6.060000e-09</td>\n",
       "      <td>O15_EXCESS_VOMIT_PREG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>rs45543339</td>\n",
       "      <td>19</td>\n",
       "      <td>LRRC25</td>\n",
       "      <td>18503194</td>\n",
       "      <td>C</td>\n",
       "      <td>T</td>\n",
       "      <td>0.265</td>\n",
       "      <td>8.180000e-09</td>\n",
       "      <td>O15_EXCESS_VOMIT_PREG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>rs780094</td>\n",
       "      <td>2</td>\n",
       "      <td>GCKR</td>\n",
       "      <td>27741237</td>\n",
       "      <td>T</td>\n",
       "      <td>C</td>\n",
       "      <td>0.356</td>\n",
       "      <td>2.670000e-09</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>rs9275373</td>\n",
       "      <td>6</td>\n",
       "      <td>MTCO3P1, HLA-*</td>\n",
       "      <td>32668411</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>0.122</td>\n",
       "      <td>4.360000e-19</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>rs10659211</td>\n",
       "      <td>10</td>\n",
       "      <td>TCF7L2</td>\n",
       "      <td>114782581</td>\n",
       "      <td>G</td>\n",
       "      <td>GCT</td>\n",
       "      <td>0.195</td>\n",
       "      <td>1.690000e-18</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>rs10830963</td>\n",
       "      <td>11</td>\n",
       "      <td>MTNR1B</td>\n",
       "      <td>92708710</td>\n",
       "      <td>C</td>\n",
       "      <td>G</td>\n",
       "      <td>0.358</td>\n",
       "      <td>7.860000e-84</td>\n",
       "      <td>GEST_DIABETES</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          rsid  chr            gene        pos             ref  alt    maf  \\\n",
       "7   rs13306561    1     MTHFR,CLCN6   11865804               A    G  0.145   \n",
       "8   rs35954793    4            FGF5   81188513               C    A  0.257   \n",
       "9   rs10882398   10           PLCE1   95892788               T    A  0.406   \n",
       "10    rs167479   19            RGL3   11526765               T    G  0.426   \n",
       "11    rs259983   20          ZNF831   57735457               A    C  0.181   \n",
       "2    rs2208589   20           PREX1   47408414               A    G  0.112   \n",
       "0   rs58835482   19           GDF15   18493064  TGGGGGCACCCTGA    T  0.256   \n",
       "1   rs45543339   19          LRRC25   18503194               C    T  0.265   \n",
       "3     rs780094    2            GCKR   27741237               T    C  0.356   \n",
       "4    rs9275373    6  MTCO3P1, HLA-*   32668411               G    A  0.122   \n",
       "5   rs10659211   10          TCF7L2  114782581               G  GCT  0.195   \n",
       "6   rs10830963   11          MTNR1B   92708710               C    G  0.358   \n",
       "\n",
       "            pval                  trait  \n",
       "7   1.820000e-10         I9_HYPTENSPREG  \n",
       "8   1.600000e-11         I9_HYPTENSPREG  \n",
       "9   3.480000e-09         I9_HYPTENSPREG  \n",
       "10  5.130000e-12         I9_HYPTENSPREG  \n",
       "11  1.620000e-09         I9_HYPTENSPREG  \n",
       "2   6.330000e-09      O15_GESTAT_HYPERT  \n",
       "0   6.060000e-09  O15_EXCESS_VOMIT_PREG  \n",
       "1   8.180000e-09  O15_EXCESS_VOMIT_PREG  \n",
       "3   2.670000e-09          GEST_DIABETES  \n",
       "4   4.360000e-19          GEST_DIABETES  \n",
       "5   1.690000e-18          GEST_DIABETES  \n",
       "6   7.860000e-84          GEST_DIABETES  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "_final = pd.read_csv('./data/finn_top_short.csv', sep='\\t')\n",
    "_final['order'] = _final.trait.apply(lambda x: traits_order[x])\n",
    "_final = _final.sort_values(by=['order', 'chr', 'pos']).drop(['group', 'order'], axis=1, errors='ignore')\n",
    "_final.to_csv('finn_top_short.csv')\n",
    "_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "400ed987",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "c('rs13306561', 'rs35954793', 'rs10882398', 'rs167479', 'rs259983'),\n",
      "c('rs2208589'),\n",
      "c('rs58835482', 'rs45543339'),\n",
      "c('rs780094', 'rs9275373', 'rs10659211', 'rs10830963')\n",
      "\n",
      "c('MTHFR,CLCN6', 'FGF5', 'PLCE1', 'RGL3', 'ZNF831'),\n",
      "c('PREX1'),\n",
      "c('GDF15', 'LRRC25'),\n",
      "c('GCKR', 'MTCO3P1, HLA-*', 'TCF7L2', 'MTNR1B')\n",
      "\n",
      "'./data/f_special/maf_fg_I9_HYPTENSPREG_hg19lifted.tsv_.tsv',\n",
      "'./data/f_special/maf_fg_O15_GESTAT_HYPERT_hg19lifted.tsv_.tsv',\n",
      "'./data/f_special/maf_fg_O15_EXCESS_VOMIT_PREG_hg19lifted.tsv_.tsv',\n",
      "'./data/f_special/maf_fg_GEST_DIABETES_hg19lifted.tsv_.tsv'\n",
      "\n",
      "'_FG_I9_HYPTENSPREG',\n",
      "'_FG_O15_GESTAT_HYPERT',\n",
      "'_FG_O15_EXCESS_VOMIT_PREG',\n",
      "'_FG_GEST_DIABETES'\n",
      "\n"
     ]
    }
   ],
   "source": [
    "traits = _final.trait.unique()\n",
    "s=''\n",
    "for t in traits:\n",
    "    s+=\"c('\"+\"', '\".join(list(_final[_final.trait==t].rsid))+\"'),\\n\"\n",
    "print(s[:-2], end='\\n\\n')\n",
    "s=''\n",
    "for t in traits:\n",
    "    s+=\"c('\"+\"', '\".join(list(_final[_final.trait==t].gene))+\"'),\\n\"\n",
    "print(s[:-2], end='\\n\\n')\n",
    "s=''\n",
    "for t in traits:\n",
    "    s+=f\"'{DIR}/{next(filter(lambda f: t in f and 'maf_fg' in f, files))}',\\n\"\n",
    "print(s[:-2], end='\\n\\n')\n",
    "s=''\n",
    "for t in traits:\n",
    "    s+=f\"'_FG_{t}',\\n\"\n",
    "print(s[:-2], end='\\n\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5855034",
   "metadata": {},
   "source": [
    "## Sort in chr:pos ascending order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "d1b13828",
   "metadata": {},
   "outputs": [],
   "source": [
    "traits = final.trait.unique()\n",
    "\n",
    "for t in traits:\n",
    "    d = all_datas[t]\n",
    "    d.pos = d.pos.astype(int)\n",
    "    d.chr = d.chr.astype(str)\n",
    "    d['chr_order'] = d.chr.apply(lambda x: order[x])\n",
    "    kkkk = d.sort_values(by=['chr_order', 'pos'])[['rsid', 'chr', 'pos', 'ref', 'alt', 'maf', 'pval']]\n",
    "    cur_f = f\"data/f_special/{t}.tsv\"\n",
    "    kkkk.to_csv(cur_f, sep='\\t', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e8765c2",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}