preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## GWAS catalog filtering notebook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The purpose of this notebook is to filter GWAS associations and traits based on the curation comments and our needs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl\n",
    "from pathlib import *"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setting up paths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "base = Path(\".\")\n",
    "data = base / \"data\"\n",
    "input = data / \"input\"\n",
    "output = data / \"output\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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",
       "\n",
       "    .dataframe td {\n",
       "        white-space: pre;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-top: 0;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-bottom: 0;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\" >\n",
       "<small>shape: (3, 6)</small>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>\n",
       "trait\n",
       "</th>\n",
       "<th>\n",
       "trait_uri\n",
       "</th>\n",
       "<th>\n",
       "studies\n",
       "</th>\n",
       "<th>\n",
       "association_counts\n",
       "</th>\n",
       "<th>\n",
       "papers\n",
       "</th>\n",
       "<th>\n",
       "Olga's comments\n",
       "</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;clinical treat...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "687\n",
       "</td>\n",
       "<td>\n",
       "31\n",
       "</td>\n",
       "<td>\n",
       "687\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;employment sta...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "687\n",
       "</td>\n",
       "<td>\n",
       "10\n",
       "</td>\n",
       "<td>\n",
       "687\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;cortical thick...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "325\n",
       "</td>\n",
       "<td>\n",
       "1292\n",
       "</td>\n",
       "<td>\n",
       "325\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "shape: (3, 6)\n",
       "┌────────────────────┬────────────────────┬─────────┬───────────────────┬────────┬─────────────────┐\n",
       "│ trait              ┆ trait_uri          ┆ studies ┆ association_count ┆ papers ┆ Olga's comments │\n",
       "│ ---                ┆ ---                ┆ ---     ┆ s                 ┆ ---    ┆ ---             │\n",
       "│ str                ┆ str                ┆ i64     ┆ ---               ┆ i64    ┆ str             │\n",
       "│                    ┆                    ┆         ┆ i64               ┆        ┆                 │\n",
       "╞════════════════════╪════════════════════╪═════════╪═══════════════════╪════════╪═════════════════╡\n",
       "│ clinical treatment ┆ http://www.ebi.ac. ┆ 687     ┆ 31                ┆ 687    ┆ null            │\n",
       "│                    ┆ uk/efo/EFO_000...  ┆         ┆                   ┆        ┆                 │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
       "│ employment status  ┆ http://www.ebi.ac. ┆ 687     ┆ 10                ┆ 687    ┆ null            │\n",
       "│                    ┆ uk/efo/EFO_000...  ┆         ┆                   ┆        ┆                 │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
       "│ cortical thickness ┆ http://www.ebi.ac. ┆ 325     ┆ 1292              ┆ 325    ┆ null            │\n",
       "│                    ┆ uk/efo/EFO_000...  ┆         ┆                   ┆        ┆                 │\n",
       "└────────────────────┴────────────────────┴─────────┴───────────────────┴────────┴─────────────────┘"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "traits_path = input / \"traits_no_measurement.tsv\"\n",
    "traits_df = pl.read_csv(str(traits_path), sep=\"\\t\")\n",
    "traits_df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Filter by curators comments and fix typos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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",
       "\n",
       "    .dataframe td {\n",
       "        white-space: pre;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-top: 0;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-bottom: 0;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\" >\n",
       "<small>shape: (10, 6)</small>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>\n",
       "trait\n",
       "</th>\n",
       "<th>\n",
       "trait_uri\n",
       "</th>\n",
       "<th>\n",
       "studies\n",
       "</th>\n",
       "<th>\n",
       "association_counts\n",
       "</th>\n",
       "<th>\n",
       "papers\n",
       "</th>\n",
       "<th>\n",
       "curator_comments\n",
       "</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;type 2 diabete...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://purl.ob...\n",
       "</td>\n",
       "<td>\n",
       "175\n",
       "</td>\n",
       "<td>\n",
       "5003\n",
       "</td>\n",
       "<td>\n",
       "175\n",
       "</td>\n",
       "<td>\n",
       "&quot;glucose_homeos...\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;body mass inde...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "151\n",
       "</td>\n",
       "<td>\n",
       "8912\n",
       "</td>\n",
       "<td>\n",
       "151\n",
       "</td>\n",
       "<td>\n",
       "&quot;glucose_homeos...\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;covid-19&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://purl.ob...\n",
       "</td>\n",
       "<td>\n",
       "123\n",
       "</td>\n",
       "<td>\n",
       "659\n",
       "</td>\n",
       "<td>\n",
       "123\n",
       "</td>\n",
       "<td>\n",
       "&quot;lung&quot;\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;asthma&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://purl.ob...\n",
       "</td>\n",
       "<td>\n",
       "112\n",
       "</td>\n",
       "<td>\n",
       "1475\n",
       "</td>\n",
       "<td>\n",
       "112\n",
       "</td>\n",
       "<td>\n",
       "&quot;lung&quot;\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;alzheimer dise...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://purl.ob...\n",
       "</td>\n",
       "<td>\n",
       "89\n",
       "</td>\n",
       "<td>\n",
       "669\n",
       "</td>\n",
       "<td>\n",
       "89\n",
       "</td>\n",
       "<td>\n",
       "&quot;mental&quot;\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;bone fracture&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "85\n",
       "</td>\n",
       "<td>\n",
       "53\n",
       "</td>\n",
       "<td>\n",
       "85\n",
       "</td>\n",
       "<td>\n",
       "&quot;musculoskeleta...\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;systolic blood...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "73\n",
       "</td>\n",
       "<td>\n",
       "3435\n",
       "</td>\n",
       "<td>\n",
       "73\n",
       "</td>\n",
       "<td>\n",
       "&quot;cardiovascular...\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;coronary arter...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "72\n",
       "</td>\n",
       "<td>\n",
       "2408\n",
       "</td>\n",
       "<td>\n",
       "72\n",
       "</td>\n",
       "<td>\n",
       "&quot;cardiovascular...\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;diastolic bloo...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "68\n",
       "</td>\n",
       "<td>\n",
       "2316\n",
       "</td>\n",
       "<td>\n",
       "68\n",
       "</td>\n",
       "<td>\n",
       "&quot;cardiovascular...\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;osteoarthritis...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://purl.ob...\n",
       "</td>\n",
       "<td>\n",
       "60\n",
       "</td>\n",
       "<td>\n",
       "99\n",
       "</td>\n",
       "<td>\n",
       "60\n",
       "</td>\n",
       "<td>\n",
       "&quot;musculoskeleta...\n",
       "</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "shape: (10, 6)\n",
       "┌───────────────────┬───────────────────┬─────────┬───────────────────┬────────┬───────────────────┐\n",
       "│ trait             ┆ trait_uri         ┆ studies ┆ association_count ┆ papers ┆ curator_comments  │\n",
       "│ ---               ┆ ---               ┆ ---     ┆ s                 ┆ ---    ┆ ---               │\n",
       "│ str               ┆ str               ┆ i64     ┆ ---               ┆ i64    ┆ str               │\n",
       "│                   ┆                   ┆         ┆ i64               ┆        ┆                   │\n",
       "╞═══════════════════╪═══════════════════╪═════════╪═══════════════════╪════════╪═══════════════════╡\n",
       "│ type 2 diabetes   ┆ http://purl.oboli ┆ 175     ┆ 5003              ┆ 175    ┆ glucose_homeostas │\n",
       "│ mellitus          ┆ brary.org/obo/M.. ┆         ┆                   ┆        ┆ is_and_obesity    │\n",
       "│                   ┆ .                 ┆         ┆                   ┆        ┆                   │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
       "│ body mass index   ┆ http://www.ebi.ac ┆ 151     ┆ 8912              ┆ 151    ┆ glucose_homeostas │\n",
       "│                   ┆ .uk/efo/EFO_000.. ┆         ┆                   ┆        ┆ is_and_obesity    │\n",
       "│                   ┆ .                 ┆         ┆                   ┆        ┆                   │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
       "│ covid-19          ┆ http://purl.oboli ┆ 123     ┆ 659               ┆ 123    ┆ lung              │\n",
       "│                   ┆ brary.org/obo/M.. ┆         ┆                   ┆        ┆                   │\n",
       "│                   ┆ .                 ┆         ┆                   ┆        ┆                   │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
       "│ asthma            ┆ http://purl.oboli ┆ 112     ┆ 1475              ┆ 112    ┆ lung              │\n",
       "│                   ┆ brary.org/obo/M.. ┆         ┆                   ┆        ┆                   │\n",
       "│                   ┆ .                 ┆         ┆                   ┆        ┆                   │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
       "│ ...               ┆ ...               ┆ ...     ┆ ...               ┆ ...    ┆ ...               │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
       "│ systolic blood    ┆ http://www.ebi.ac ┆ 73      ┆ 3435              ┆ 73     ┆ cardiovascular    │\n",
       "│ pressure          ┆ .uk/efo/EFO_000.. ┆         ┆                   ┆        ┆                   │\n",
       "│                   ┆ .                 ┆         ┆                   ┆        ┆                   │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
       "│ coronary artery   ┆ http://www.ebi.ac ┆ 72      ┆ 2408              ┆ 72     ┆ cardiovascular    │\n",
       "│ disease           ┆ .uk/efo/EFO_000.. ┆         ┆                   ┆        ┆                   │\n",
       "│                   ┆ .                 ┆         ┆                   ┆        ┆                   │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
       "│ diastolic blood   ┆ http://www.ebi.ac ┆ 68      ┆ 2316              ┆ 68     ┆ cardiovascular    │\n",
       "│ pressure          ┆ .uk/efo/EFO_000.. ┆         ┆                   ┆        ┆                   │\n",
       "│                   ┆ .                 ┆         ┆                   ┆        ┆                   │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
       "│ osteoarthritis    ┆ http://purl.oboli ┆ 60      ┆ 99                ┆ 60     ┆ musculoskeletal   │\n",
       "│                   ┆ brary.org/obo/M.. ┆         ┆                   ┆        ┆                   │\n",
       "│                   ┆ .                 ┆         ┆                   ┆        ┆                   │\n",
       "└───────────────────┴───────────────────┴─────────┴───────────────────┴────────┴───────────────────┘"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments = traits_df[\"Olga's comments\"].to_list()\n",
    "\n",
    "curator_comments = (\n",
    "    pl.col(\"Olga's comments\")\n",
    "        .str.strip() # steam leading and ending spaces, they are often added accidentally and cause troubles\n",
    "        .str.to_lowercase()\n",
    "        .str.replace(\" \", \"_\") # let's go dash instead of spaces\n",
    "        .str.replace(\"/\", \", \").str.replace(\"\\?\",  \"\") #clean up from some redundant symbols\n",
    "        .str.replace(\"muskuloskeletal\", \"musculoskeletal\").str.replace('musculosceletal', \"musculoskeletal\") # typos correction\n",
    "        .str.replace(\"obesiy\", 'obesity')\n",
    "        .str.replace(\"homeostais\",\"homeostasis\")\n",
    "        .str.replace(\", \", \"_and_\").str.replace(\",\", \"_and_\") #let's first use end and then check if we can go further\n",
    ") # cleaned up Olga's comments column\n",
    "\n",
    "traits_filtered: pl.DataFrame = traits_df.filter(pl.col(\"Olga's comments\").is_not_null())\\\n",
    "    .with_column(curator_comments).rename({\"Olga's comments\": \"curator_comments\"})\n",
    "traits_filtered = traits_filtered.with_column(pl.col('trait').str.to_lowercase())\n",
    "traits_filtered.head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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",
       "\n",
       "    .dataframe td {\n",
       "        white-space: pre;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-top: 0;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-bottom: 0;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\" >\n",
       "<small>shape: (10, 6)</small>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>\n",
       "curator_comments\n",
       "</th>\n",
       "<th>\n",
       "trait\n",
       "</th>\n",
       "<th>\n",
       "trait_uri\n",
       "</th>\n",
       "<th>\n",
       "studies\n",
       "</th>\n",
       "<th>\n",
       "association_counts\n",
       "</th>\n",
       "<th>\n",
       "papers\n",
       "</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;musculoskeleta...\n",
       "</td>\n",
       "<td>\n",
       "&quot;bone fracture&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "85\n",
       "</td>\n",
       "<td>\n",
       "53\n",
       "</td>\n",
       "<td>\n",
       "85\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;musculoskeleta...\n",
       "</td>\n",
       "<td>\n",
       "&quot;osteoarthritis...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://purl.ob...\n",
       "</td>\n",
       "<td>\n",
       "60\n",
       "</td>\n",
       "<td>\n",
       "99\n",
       "</td>\n",
       "<td>\n",
       "60\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;musculoskeleta...\n",
       "</td>\n",
       "<td>\n",
       "&quot;bone density&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "53\n",
       "</td>\n",
       "<td>\n",
       "692\n",
       "</td>\n",
       "<td>\n",
       "53\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;musculoskeleta...\n",
       "</td>\n",
       "<td>\n",
       "&quot;lean body mass...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "45\n",
       "</td>\n",
       "<td>\n",
       "523\n",
       "</td>\n",
       "<td>\n",
       "45\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;musculoskeleta...\n",
       "</td>\n",
       "<td>\n",
       "&quot;osteoporosis&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "33\n",
       "</td>\n",
       "<td>\n",
       "45\n",
       "</td>\n",
       "<td>\n",
       "33\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;musculoskeleta...\n",
       "</td>\n",
       "<td>\n",
       "&quot;heel bone mine...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "25\n",
       "</td>\n",
       "<td>\n",
       "4690\n",
       "</td>\n",
       "<td>\n",
       "25\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;musculoskeleta...\n",
       "</td>\n",
       "<td>\n",
       "&quot;osteoarthritis...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "21\n",
       "</td>\n",
       "<td>\n",
       "64\n",
       "</td>\n",
       "<td>\n",
       "21\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;musculoskeleta...\n",
       "</td>\n",
       "<td>\n",
       "&quot;femoral neck b...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "17\n",
       "</td>\n",
       "<td>\n",
       "197\n",
       "</td>\n",
       "<td>\n",
       "17\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;musculoskeleta...\n",
       "</td>\n",
       "<td>\n",
       "&quot;spine bone min...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "17\n",
       "</td>\n",
       "<td>\n",
       "145\n",
       "</td>\n",
       "<td>\n",
       "17\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;musculoskeleta...\n",
       "</td>\n",
       "<td>\n",
       "&quot;hip bone miner...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "10\n",
       "</td>\n",
       "<td>\n",
       "88\n",
       "</td>\n",
       "<td>\n",
       "10\n",
       "</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "shape: (10, 6)\n",
       "┌──────────────────┬────────────────────┬───────────────────┬─────────┬───────────────────┬────────┐\n",
       "│ curator_comments ┆ trait              ┆ trait_uri         ┆ studies ┆ association_count ┆ papers │\n",
       "│ ---              ┆ ---                ┆ ---               ┆ ---     ┆ s                 ┆ ---    │\n",
       "│ str              ┆ str                ┆ str               ┆ i64     ┆ ---               ┆ i64    │\n",
       "│                  ┆                    ┆                   ┆         ┆ i64               ┆        │\n",
       "╞══════════════════╪════════════════════╪═══════════════════╪═════════╪═══════════════════╪════════╡\n",
       "│ musculoskeletal  ┆ bone fracture      ┆ http://www.ebi.ac ┆ 85      ┆ 53                ┆ 85     │\n",
       "│                  ┆                    ┆ .uk/efo/EFO_000.. ┆         ┆                   ┆        │\n",
       "│                  ┆                    ┆ .                 ┆         ┆                   ┆        │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤\n",
       "│ musculoskeletal  ┆ osteoarthritis     ┆ http://purl.oboli ┆ 60      ┆ 99                ┆ 60     │\n",
       "│                  ┆                    ┆ brary.org/obo/M.. ┆         ┆                   ┆        │\n",
       "│                  ┆                    ┆ .                 ┆         ┆                   ┆        │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤\n",
       "│ musculoskeletal  ┆ bone density       ┆ http://www.ebi.ac ┆ 53      ┆ 692               ┆ 53     │\n",
       "│                  ┆                    ┆ .uk/efo/EFO_000.. ┆         ┆                   ┆        │\n",
       "│                  ┆                    ┆ .                 ┆         ┆                   ┆        │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤\n",
       "│ musculoskeletal  ┆ lean body mass     ┆ http://www.ebi.ac ┆ 45      ┆ 523               ┆ 45     │\n",
       "│                  ┆                    ┆ .uk/efo/EFO_000.. ┆         ┆                   ┆        │\n",
       "│                  ┆                    ┆ .                 ┆         ┆                   ┆        │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤\n",
       "│ ...              ┆ ...                ┆ ...               ┆ ...     ┆ ...               ┆ ...    │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤\n",
       "│ musculoskeletal  ┆ osteoarthritis,    ┆ http://www.ebi.ac ┆ 21      ┆ 64                ┆ 21     │\n",
       "│                  ┆ knee               ┆ .uk/efo/EFO_000.. ┆         ┆                   ┆        │\n",
       "│                  ┆                    ┆ .                 ┆         ┆                   ┆        │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤\n",
       "│ musculoskeletal  ┆ femoral neck bone  ┆ http://www.ebi.ac ┆ 17      ┆ 197               ┆ 17     │\n",
       "│                  ┆ mineral densit...  ┆ .uk/efo/EFO_000.. ┆         ┆                   ┆        │\n",
       "│                  ┆                    ┆ .                 ┆         ┆                   ┆        │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤\n",
       "│ musculoskeletal  ┆ spine bone mineral ┆ http://www.ebi.ac ┆ 17      ┆ 145               ┆ 17     │\n",
       "│                  ┆ density            ┆ .uk/efo/EFO_000.. ┆         ┆                   ┆        │\n",
       "│                  ┆                    ┆ .                 ┆         ┆                   ┆        │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤\n",
       "│ musculoskeletal  ┆ hip bone mineral   ┆ http://www.ebi.ac ┆ 10      ┆ 88                ┆ 10     │\n",
       "│                  ┆ density            ┆ .uk/efo/EFO_000.. ┆         ┆                   ┆        │\n",
       "│                  ┆                    ┆ .                 ┆         ┆                   ┆        │\n",
       "└──────────────────┴────────────────────┴───────────────────┴─────────┴───────────────────┴────────┘"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "traits_grouped = traits_filtered.groupby(\"curator_comments\").agg(pl.all())\n",
    "\n",
    "traits = traits_grouped.explode([c for c in traits_grouped.columns if c != \"curator_comments\"])\n",
    "traits.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Writing results to folders"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "let's clean the output first"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "import shutil\n",
    "if output.exists():\n",
    "    shutil.rmtree(output)\n",
    "output.mkdir()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's create folders for modules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['metabolic_health_and_obesity',\n",
       " 'longevity',\n",
       " 'lung',\n",
       " 'other',\n",
       " 'musculoskeletal',\n",
       " 'cardiovascular',\n",
       " 'inflammation',\n",
       " 'mental',\n",
       " 'glucose_homeostasis_and_obesity']"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "modules = traits_filtered.select([pl.col(\"curator_comments\").unique()]).to_series().to_list()\n",
    "modules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "writing module metabolic_health_and_obesity to data\\output\\metabolic_health_and_obesity with traits file data\\output\\metabolic_health_and_obesity\\metabolic_health_and_obesity.tsv\n",
      "\n",
      "writing module longevity to data\\output\\longevity with traits file data\\output\\longevity\\longevity.tsv\n",
      "\n",
      "writing module lung to data\\output\\lung with traits file data\\output\\lung\\lung.tsv\n",
      "\n",
      "writing module other to data\\output\\other with traits file data\\output\\other\\other.tsv\n",
      "\n",
      "writing module musculoskeletal to data\\output\\musculoskeletal with traits file data\\output\\musculoskeletal\\musculoskeletal.tsv\n",
      "\n",
      "writing module cardiovascular to data\\output\\cardiovascular with traits file data\\output\\cardiovascular\\cardiovascular.tsv\n",
      "\n",
      "writing module inflammation to data\\output\\inflammation with traits file data\\output\\inflammation\\inflammation.tsv\n",
      "\n",
      "writing module mental to data\\output\\mental with traits file data\\output\\mental\\mental.tsv\n",
      "\n",
      "writing module glucose_homeostasis_and_obesity to data\\output\\glucose_homeostasis_and_obesity with traits file data\\output\\glucose_homeostasis_and_obesity\\glucose_homeostasis_and_obesity.tsv\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for item in modules:\n",
    "    item_folder = output / item\n",
    "    item_folder.mkdir()\n",
    "    file_name = item + '.tsv'\n",
    "    file_path = item_folder / file_name\n",
    "    print(f\"writing module {item} to {item_folder} with traits file {file_path}\\n\")\n",
    "    module_df = traits.filter(pl.col('curator_comments')==item)\n",
    "\n",
    "    module_df.write_csv(file = str(file_path), sep='\\t')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "dowloading GWASS catalogs to input folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "import urllib.request\n",
    "\n",
    "\n",
    "local_associations = \"gwass_associations.tsv\"\n",
    "path_associations = input / local_associations\n",
    "\n",
    "local_studies = \"gwass_studies.tsv\"\n",
    "path_studies = input / local_studies\n",
    "\n",
    "if not path_associations.is_file():\n",
    "    # Associations file v1.0.2\n",
    "    associations_link = \"https://www.ebi.ac.uk/gwas/api/search/downloads/alternative\"\n",
    "    urllib.request.urlretrieve(associations_link, path_associations)\n",
    "\n",
    "if not path_studies.is_file():\n",
    "    # Studies file v1.0.3\n",
    "    studies_link = \"https://www.ebi.ac.uk/gwas/api/search/downloads/studies_new\"\n",
    "    urllib.request.urlretrieve(studies_link, path_studies)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "reading gwass catalogues and filtering needed rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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",
       "\n",
       "    .dataframe td {\n",
       "        white-space: pre;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-top: 0;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-bottom: 0;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\" >\n",
       "<small>shape: (3, 38)</small>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>\n",
       "DATE ADDED TO CATALOG\n",
       "</th>\n",
       "<th>\n",
       "PUBMEDID\n",
       "</th>\n",
       "<th>\n",
       "FIRST AUTHOR\n",
       "</th>\n",
       "<th>\n",
       "DATE\n",
       "</th>\n",
       "<th>\n",
       "JOURNAL\n",
       "</th>\n",
       "<th>\n",
       "LINK\n",
       "</th>\n",
       "<th>\n",
       "STUDY\n",
       "</th>\n",
       "<th>\n",
       "DISEASE/TRAIT\n",
       "</th>\n",
       "<th>\n",
       "INITIAL SAMPLE SIZE\n",
       "</th>\n",
       "<th>\n",
       "REPLICATION SAMPLE SIZE\n",
       "</th>\n",
       "<th>\n",
       "REGION\n",
       "</th>\n",
       "<th>\n",
       "CHR_ID\n",
       "</th>\n",
       "<th>\n",
       "CHR_POS\n",
       "</th>\n",
       "<th>\n",
       "REPORTED GENE(S)\n",
       "</th>\n",
       "<th>\n",
       "MAPPED_GENE\n",
       "</th>\n",
       "<th>\n",
       "UPSTREAM_GENE_ID\n",
       "</th>\n",
       "<th>\n",
       "DOWNSTREAM_GENE_ID\n",
       "</th>\n",
       "<th>\n",
       "SNP_GENE_IDS\n",
       "</th>\n",
       "<th>\n",
       "UPSTREAM_GENE_DISTANCE\n",
       "</th>\n",
       "<th>\n",
       "DOWNSTREAM_GENE_DISTANCE\n",
       "</th>\n",
       "<th>\n",
       "STRONGEST SNP-RISK ALLELE\n",
       "</th>\n",
       "<th>\n",
       "SNPS\n",
       "</th>\n",
       "<th>\n",
       "MERGED\n",
       "</th>\n",
       "<th>\n",
       "SNP_ID_CURRENT\n",
       "</th>\n",
       "<th>\n",
       "CONTEXT\n",
       "</th>\n",
       "<th>\n",
       "INTERGENIC\n",
       "</th>\n",
       "<th>\n",
       "RISK ALLELE FREQUENCY\n",
       "</th>\n",
       "<th>\n",
       "P-VALUE\n",
       "</th>\n",
       "<th>\n",
       "PVALUE_MLOG\n",
       "</th>\n",
       "<th>\n",
       "P-VALUE (TEXT)\n",
       "</th>\n",
       "<th>\n",
       "OR or BETA\n",
       "</th>\n",
       "<th>\n",
       "95% CI (TEXT)\n",
       "</th>\n",
       "<th>\n",
       "PLATFORM [SNPS PASSING QC]\n",
       "</th>\n",
       "<th>\n",
       "CNV\n",
       "</th>\n",
       "<th>\n",
       "MAPPED_TRAIT\n",
       "</th>\n",
       "<th>\n",
       "MAPPED_TRAIT_URI\n",
       "</th>\n",
       "<th>\n",
       "STUDY ACCESSION\n",
       "</th>\n",
       "<th>\n",
       "GENOTYPING TECHNOLOGY\n",
       "</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "f64\n",
       "</td>\n",
       "<td>\n",
       "f64\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "f64\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;2016-01-27&quot;\n",
       "</td>\n",
       "<td>\n",
       "25673413\n",
       "</td>\n",
       "<td>\n",
       "&quot;Locke AE&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;2015-02-12&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;Nature&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;www.ncbi.nlm.n...\n",
       "</td>\n",
       "<td>\n",
       "&quot;Genetic studie...\n",
       "</td>\n",
       "<td>\n",
       "&quot;body mass inde...\n",
       "</td>\n",
       "<td>\n",
       "&quot;up to 104,666 ...\n",
       "</td>\n",
       "<td>\n",
       "&quot;up to 48,274 E...\n",
       "</td>\n",
       "<td>\n",
       "&quot;5q33.2&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;5&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;154158333&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;GALNT10&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;MFAP3&quot;\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "&quot;ENSG0000003774...\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "&quot;rs7715256-G&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;rs7715256&quot;\n",
       "</td>\n",
       "<td>\n",
       "0\n",
       "</td>\n",
       "<td>\n",
       "&quot;7715256&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;non_coding_tra...\n",
       "</td>\n",
       "<td>\n",
       "0\n",
       "</td>\n",
       "<td>\n",
       "&quot;0.421&quot;\n",
       "</td>\n",
       "<td>\n",
       "0.0000002\n",
       "</td>\n",
       "<td>\n",
       "6.69897\n",
       "</td>\n",
       "<td>\n",
       "&quot;(EA)&quot;\n",
       "</td>\n",
       "<td>\n",
       "0.016\n",
       "</td>\n",
       "<td>\n",
       "&quot;[0.01-0.022] k...\n",
       "</td>\n",
       "<td>\n",
       "&quot;Affymetrix, Il...\n",
       "</td>\n",
       "<td>\n",
       "&quot;N&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;body mass inde...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "&quot;GCST002783&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;Genome-wide ge...\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;2016-01-27&quot;\n",
       "</td>\n",
       "<td>\n",
       "25673413\n",
       "</td>\n",
       "<td>\n",
       "&quot;Locke AE&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;2015-02-12&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;Nature&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;www.ncbi.nlm.n...\n",
       "</td>\n",
       "<td>\n",
       "&quot;Genetic studie...\n",
       "</td>\n",
       "<td>\n",
       "&quot;body mass inde...\n",
       "</td>\n",
       "<td>\n",
       "&quot;up to 104,666 ...\n",
       "</td>\n",
       "<td>\n",
       "&quot;up to 48,274 E...\n",
       "</td>\n",
       "<td>\n",
       "&quot;8p12&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;8&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;34646258&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;UNC5D&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;RPL10AP3 - LIN...\n",
       "</td>\n",
       "<td>\n",
       "&quot;ENSG0000021526...\n",
       "</td>\n",
       "<td>\n",
       "&quot;ENSG0000025434...\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "322616\n",
       "</td>\n",
       "<td>\n",
       "137770\n",
       "</td>\n",
       "<td>\n",
       "&quot;rs7844647-T&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;rs7844647&quot;\n",
       "</td>\n",
       "<td>\n",
       "0\n",
       "</td>\n",
       "<td>\n",
       "&quot;7844647&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;regulatory_reg...\n",
       "</td>\n",
       "<td>\n",
       "1\n",
       "</td>\n",
       "<td>\n",
       "&quot;0.739&quot;\n",
       "</td>\n",
       "<td>\n",
       "0.000005\n",
       "</td>\n",
       "<td>\n",
       "5.30103\n",
       "</td>\n",
       "<td>\n",
       "&quot;(EA)&quot;\n",
       "</td>\n",
       "<td>\n",
       "0.016\n",
       "</td>\n",
       "<td>\n",
       "&quot;[0.0089-0.0227...\n",
       "</td>\n",
       "<td>\n",
       "&quot;Affymetrix, Il...\n",
       "</td>\n",
       "<td>\n",
       "&quot;N&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;body mass inde...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "&quot;GCST002783&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;Genome-wide ge...\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;2016-01-27&quot;\n",
       "</td>\n",
       "<td>\n",
       "25673413\n",
       "</td>\n",
       "<td>\n",
       "&quot;Locke AE&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;2015-02-12&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;Nature&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;www.ncbi.nlm.n...\n",
       "</td>\n",
       "<td>\n",
       "&quot;Genetic studie...\n",
       "</td>\n",
       "<td>\n",
       "&quot;body mass inde...\n",
       "</td>\n",
       "<td>\n",
       "&quot;up to 104,666 ...\n",
       "</td>\n",
       "<td>\n",
       "&quot;up to 48,274 E...\n",
       "</td>\n",
       "<td>\n",
       "&quot;10q23.1&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;10&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;85651147&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;GRID1&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;GRID1&quot;\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "&quot;ENSG0000018277...\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "&quot;rs7899106-G&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;rs7899106&quot;\n",
       "</td>\n",
       "<td>\n",
       "0\n",
       "</td>\n",
       "<td>\n",
       "&quot;7899106&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;intron_variant...\n",
       "</td>\n",
       "<td>\n",
       "0\n",
       "</td>\n",
       "<td>\n",
       "&quot;0.052&quot;\n",
       "</td>\n",
       "<td>\n",
       "3.0000e-8\n",
       "</td>\n",
       "<td>\n",
       "7.522879\n",
       "</td>\n",
       "<td>\n",
       "&quot;(EA)&quot;\n",
       "</td>\n",
       "<td>\n",
       "0.04\n",
       "</td>\n",
       "<td>\n",
       "&quot;[0.026-0.053] ...\n",
       "</td>\n",
       "<td>\n",
       "&quot;Affymetrix, Il...\n",
       "</td>\n",
       "<td>\n",
       "&quot;N&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;body mass inde...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "&quot;GCST002783&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;Genome-wide ge...\n",
       "</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "shape: (3, 38)\n",
       "┌────────────┬──────────┬────────────┬───┬─────┬────────────┬────────────┬────────────┬────────────┐\n",
       "│ DATE ADDED ┆ PUBMEDID ┆ FIRST      ┆ D ┆ ... ┆ MAPPED_TRA ┆ MAPPED_TRA ┆ STUDY      ┆ GENOTYPING │\n",
       "│ TO CATALOG ┆ ---      ┆ AUTHOR     ┆ A ┆     ┆ IT         ┆ IT_URI     ┆ ACCESSION  ┆ TECHNOLOGY │\n",
       "│ ---        ┆ i64      ┆ ---        ┆ T ┆     ┆ ---        ┆ ---        ┆ ---        ┆ ---        │\n",
       "│ str        ┆          ┆ str        ┆ E ┆     ┆ str        ┆ str        ┆ str        ┆ str        │\n",
       "│            ┆          ┆            ┆ - ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ - ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ - ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ s ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ t ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ r ┆     ┆            ┆            ┆            ┆            │\n",
       "╞════════════╪══════════╪════════════╪═══╪═════╪════════════╪════════════╪════════════╪════════════╡\n",
       "│ 2016-01-27 ┆ 25673413 ┆ Locke AE   ┆ 2 ┆ ... ┆ body mass  ┆ http://www ┆ GCST002783 ┆ Genome-wid │\n",
       "│            ┆          ┆            ┆ 0 ┆     ┆ index      ┆ .ebi.ac.uk ┆            ┆ e          │\n",
       "│            ┆          ┆            ┆ 1 ┆     ┆            ┆ /efo/EFO_0 ┆            ┆ genotyping │\n",
       "│            ┆          ┆            ┆ 5 ┆     ┆            ┆ 00...      ┆            ┆ array      │\n",
       "│            ┆          ┆            ┆ - ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ 0 ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ 2 ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ - ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ 1 ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ 2 ┆     ┆            ┆            ┆            ┆            │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
       "│ 2016-01-27 ┆ 25673413 ┆ Locke AE   ┆ 2 ┆ ... ┆ body mass  ┆ http://www ┆ GCST002783 ┆ Genome-wid │\n",
       "│            ┆          ┆            ┆ 0 ┆     ┆ index      ┆ .ebi.ac.uk ┆            ┆ e          │\n",
       "│            ┆          ┆            ┆ 1 ┆     ┆            ┆ /efo/EFO_0 ┆            ┆ genotyping │\n",
       "│            ┆          ┆            ┆ 5 ┆     ┆            ┆ 00...      ┆            ┆ array      │\n",
       "│            ┆          ┆            ┆ - ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ 0 ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ 2 ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ - ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ 1 ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ 2 ┆     ┆            ┆            ┆            ┆            │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
       "│ 2016-01-27 ┆ 25673413 ┆ Locke AE   ┆ 2 ┆ ... ┆ body mass  ┆ http://www ┆ GCST002783 ┆ Genome-wid │\n",
       "│            ┆          ┆            ┆ 0 ┆     ┆ index      ┆ .ebi.ac.uk ┆            ┆ e          │\n",
       "│            ┆          ┆            ┆ 1 ┆     ┆            ┆ /efo/EFO_0 ┆            ┆ genotyping │\n",
       "│            ┆          ┆            ┆ 5 ┆     ┆            ┆ 00...      ┆            ┆ array      │\n",
       "│            ┆          ┆            ┆ - ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ 0 ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ 2 ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ - ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ 1 ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆          ┆            ┆ 2 ┆     ┆            ┆            ┆            ┆            │\n",
       "└────────────┴──────────┴────────────┴───┴─────┴────────────┴────────────┴────────────┴────────────┘"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "associations_df = pl.read_csv(str(path_associations),dtypes = {'CHR_ID': pl.Utf8,'CHR_POS': pl.Utf8, 'SNP_ID_CURRENT': pl.Utf8}, sep=\"\\t\")\n",
    "associations_df = associations_df.with_column(pl.col('DISEASE/TRAIT').str.to_lowercase())\n",
    "associations_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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",
       "\n",
       "    .dataframe td {\n",
       "        white-space: pre;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-top: 0;\n",
       "    }\n",
       "\n",
       "    .dataframe td {\n",
       "        padding-bottom: 0;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\" >\n",
       "<small>shape: (3, 22)</small>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>\n",
       "DATE ADDED TO CATALOG\n",
       "</th>\n",
       "<th>\n",
       "PUBMED ID\n",
       "</th>\n",
       "<th>\n",
       "FIRST AUTHOR\n",
       "</th>\n",
       "<th>\n",
       "DATE\n",
       "</th>\n",
       "<th>\n",
       "JOURNAL\n",
       "</th>\n",
       "<th>\n",
       "LINK\n",
       "</th>\n",
       "<th>\n",
       "STUDY\n",
       "</th>\n",
       "<th>\n",
       "DISEASE/TRAIT\n",
       "</th>\n",
       "<th>\n",
       "INITIAL SAMPLE SIZE\n",
       "</th>\n",
       "<th>\n",
       "REPLICATION SAMPLE SIZE\n",
       "</th>\n",
       "<th>\n",
       "PLATFORM [SNPS PASSING QC]\n",
       "</th>\n",
       "<th>\n",
       "ASSOCIATION COUNT\n",
       "</th>\n",
       "<th>\n",
       "MAPPED_TRAIT\n",
       "</th>\n",
       "<th>\n",
       "MAPPED_TRAIT_URI\n",
       "</th>\n",
       "<th>\n",
       "STUDY ACCESSION\n",
       "</th>\n",
       "<th>\n",
       "GENOTYPING TECHNOLOGY\n",
       "</th>\n",
       "<th>\n",
       "SUMMARY STATS LOCATION\n",
       "</th>\n",
       "<th>\n",
       "SUBMISSION DATE\n",
       "</th>\n",
       "<th>\n",
       "STATISTICAL MODEL\n",
       "</th>\n",
       "<th>\n",
       "BACKGROUND TRAIT\n",
       "</th>\n",
       "<th>\n",
       "MAPPED BACKGROUND TRAIT\n",
       "</th>\n",
       "<th>\n",
       "MAPPED BACKGROUND TRAIT URI\n",
       "</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "i64\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "<td>\n",
       "str\n",
       "</td>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;2016-10-14&quot;\n",
       "</td>\n",
       "<td>\n",
       "26626624\n",
       "</td>\n",
       "<td>\n",
       "&quot;Stuart PE&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;2015-11-28&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;Am J Hum Genet...\n",
       "</td>\n",
       "<td>\n",
       "&quot;www.ncbi.nlm.n...\n",
       "</td>\n",
       "<td>\n",
       "&quot;Genome-wide As...\n",
       "</td>\n",
       "<td>\n",
       "&quot;cutaneous psor...\n",
       "</td>\n",
       "<td>\n",
       "&quot;1,363 European...\n",
       "</td>\n",
       "<td>\n",
       "&quot;up to 2,969 Eu...\n",
       "</td>\n",
       "<td>\n",
       "&quot;Illumina [1153...\n",
       "</td>\n",
       "<td>\n",
       "10\n",
       "</td>\n",
       "<td>\n",
       "&quot;cutaneous psor...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "&quot;GCST003269&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;Genome-wide ge...\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;2016-10-14&quot;\n",
       "</td>\n",
       "<td>\n",
       "26626624\n",
       "</td>\n",
       "<td>\n",
       "&quot;Stuart PE&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;2015-11-28&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;Am J Hum Genet...\n",
       "</td>\n",
       "<td>\n",
       "&quot;www.ncbi.nlm.n...\n",
       "</td>\n",
       "<td>\n",
       "&quot;Genome-wide As...\n",
       "</td>\n",
       "<td>\n",
       "&quot;psoriasis vulg...\n",
       "</td>\n",
       "<td>\n",
       "&quot;4,007 European...\n",
       "</td>\n",
       "<td>\n",
       "&quot;up to 9,075 Eu...\n",
       "</td>\n",
       "<td>\n",
       "&quot;Illumina [up t...\n",
       "</td>\n",
       "<td>\n",
       "39\n",
       "</td>\n",
       "<td>\n",
       "&quot;psoriasis vulg...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "&quot;GCST003268&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;Genome-wide ge...\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>\n",
       "&quot;2016-10-14&quot;\n",
       "</td>\n",
       "<td>\n",
       "26626624\n",
       "</td>\n",
       "<td>\n",
       "&quot;Stuart PE&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;2015-11-28&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;Am J Hum Genet...\n",
       "</td>\n",
       "<td>\n",
       "&quot;www.ncbi.nlm.n...\n",
       "</td>\n",
       "<td>\n",
       "&quot;Genome-wide As...\n",
       "</td>\n",
       "<td>\n",
       "&quot;psoriatic arth...\n",
       "</td>\n",
       "<td>\n",
       "&quot;1,946 European...\n",
       "</td>\n",
       "<td>\n",
       "&quot;up to 2,883 Eu...\n",
       "</td>\n",
       "<td>\n",
       "&quot;Illumina [1153...\n",
       "</td>\n",
       "<td>\n",
       "14\n",
       "</td>\n",
       "<td>\n",
       "&quot;psoriatic arth...\n",
       "</td>\n",
       "<td>\n",
       "&quot;http://www.ebi...\n",
       "</td>\n",
       "<td>\n",
       "&quot;GCST003270&quot;\n",
       "</td>\n",
       "<td>\n",
       "&quot;Genome-wide ge...\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "<td>\n",
       "null\n",
       "</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "shape: (3, 22)\n",
       "┌────────────┬─────────┬────────────┬────┬─────┬────────────┬────────────┬────────────┬────────────┐\n",
       "│ DATE ADDED ┆ PUBMED  ┆ FIRST      ┆ DA ┆ ... ┆ STATISTICA ┆ BACKGROUND ┆ MAPPED     ┆ MAPPED     │\n",
       "│ TO CATALOG ┆ ID      ┆ AUTHOR     ┆ TE ┆     ┆ L MODEL    ┆ TRAIT      ┆ BACKGROUND ┆ BACKGROUND │\n",
       "│ ---        ┆ ---     ┆ ---        ┆ -- ┆     ┆ ---        ┆ ---        ┆ TRAIT      ┆ TRAIT URI  │\n",
       "│ str        ┆ i64     ┆ str        ┆ -  ┆     ┆ str        ┆ str        ┆ ---        ┆ ---        │\n",
       "│            ┆         ┆            ┆ st ┆     ┆            ┆            ┆ str        ┆ str        │\n",
       "│            ┆         ┆            ┆ r  ┆     ┆            ┆            ┆            ┆            │\n",
       "╞════════════╪═════════╪════════════╪════╪═════╪════════════╪════════════╪════════════╪════════════╡\n",
       "│ 2016-10-14 ┆ 2662662 ┆ Stuart PE  ┆ 20 ┆ ... ┆ null       ┆ null       ┆ null       ┆ null       │\n",
       "│            ┆ 4       ┆            ┆ 15 ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆         ┆            ┆ -1 ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆         ┆            ┆ 1- ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆         ┆            ┆ 28 ┆     ┆            ┆            ┆            ┆            │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌┼╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
       "│ 2016-10-14 ┆ 2662662 ┆ Stuart PE  ┆ 20 ┆ ... ┆ null       ┆ null       ┆ null       ┆ null       │\n",
       "│            ┆ 4       ┆            ┆ 15 ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆         ┆            ┆ -1 ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆         ┆            ┆ 1- ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆         ┆            ┆ 28 ┆     ┆            ┆            ┆            ┆            │\n",
       "├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌┼╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
       "│ 2016-10-14 ┆ 2662662 ┆ Stuart PE  ┆ 20 ┆ ... ┆ null       ┆ null       ┆ null       ┆ null       │\n",
       "│            ┆ 4       ┆            ┆ 15 ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆         ┆            ┆ -1 ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆         ┆            ┆ 1- ┆     ┆            ┆            ┆            ┆            │\n",
       "│            ┆         ┆            ┆ 28 ┆     ┆            ┆            ┆            ┆            │\n",
       "└────────────┴─────────┴────────────┴────┴─────┴────────────┴────────────┴────────────┴────────────┘"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "studies_df = pl.read_csv(str(path_studies), sep=\"\\t\")\n",
    "studies_df = studies_df.with_column(pl.col(['MAPPED_TRAIT','DISEASE/TRAIT']).str.to_lowercase())\n",
    "studies_df.head(3)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "let's join tables and creat new db files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pycomfort.files import *\n",
    "\n",
    "folders = dirs(output)\n",
    "for f in folders:\n",
    "    file = with_ext(f, 'tsv')[0]\n",
    "    df = pl.read_csv(str(file), sep='\\t')\n",
    "\n",
    "    file_prefix = file.stem\n",
    "    file_name_associations = file_prefix + \"_associations\" + \".tsv\"\n",
    "    file_name_studies = file_prefix + \"_studies\" + \".tsv\"\n",
    "\n",
    "    path_associations_output = f / file_name_associations\n",
    "    path_studies_output = f / file_name_studies\n",
    "\n",
    "    df_studies = (\n",
    "    df\n",
    "    .join(\n",
    "        studies_df,\n",
    "        left_on=\"trait_uri\",\n",
    "        right_on=\"MAPPED_TRAIT_URI\",\n",
    "        how=\"inner\")\n",
    "    )\n",
    "    df_studies.write_csv(file = str(path_studies_output), sep='\\t')\n",
    "    df_associations = (\n",
    "    df\n",
    "    .join(\n",
    "        associations_df,\n",
    "        left_on=\"trait_uri\",\n",
    "        right_on=\"MAPPED_TRAIT_URI\",\n",
    "        how=\"inner\")\n",
    "    )\n",
    "\n",
    "    # filtering only big df\n",
    "    if df_associations.height > 100:\n",
    "        df_associations =  df_associations.filter(pl.col(\"P-VALUE\") < 0.05)\n",
    "        df_associations.write_csv(file = str(path_associations_output), sep='\\t')\n",
    "    else:\n",
    "        df_associations.write_csv(file = str(path_associations_output), sep='\\t')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py39",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  },
  "vscode": {
   "interpreter": {
    "hash": "fb10130602bb05aefc1715443c69216395f3a51f9dbcef2ac8453c6b338625f2"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}