Linking_and_Geolocating_Clinical_Trials.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Linking and Geolocating Clinical Trials.ipynb",
      "provenance": [],
      "machine_shape": "hm"
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "id": "6AqMEMMMKipd",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/gdrive')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9DnLzl_OpZBH",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "cd My Drive"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SYBDUUFyKkeT",
        "colab_type": "code",
        "outputId": "7de083aa-985a-4db0-d0fa-01a212f654de",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 73
        }
      },
      "source": [
        "!gsutil cp gs://coronawhy/NLPDatasets/v6_preprocessed/v6_text.zip .\n",
        "!unzip v6_text.zip"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Copying gs://coronawhy/NLPDatasets/v6_preprocessed/v6_text.zip...\n",
            "\\ [1 files][  1.0 GiB/  1.0 GiB]   44.9 MiB/s                                   \n",
            "Operation completed over 1 objects/1.0 GiB.                                      \n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LPz9hv-cY4Ju",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import numpy as np \n",
        "import pandas as pd \n",
        "import os\n",
        "import re\n",
        "import numpy as np\n",
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt\n",
        "import tensorflow as tf\n",
        "import xml.etree.ElementTree as ET"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Q23yp8jRZG2q",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df = pd.read_pickle(\"/content/gdrive/My Drive/v6_text/v6_text_0.pkl\", compression = \"gzip\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yUgy49dtZzjB",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#Let me load my clinical trials and get the unique IDs. This is the publicly available WHO clinical trials dataset\n",
        "trials = pd.read_csv(\"/content/gdrive/My Drive/COVID19.csv\")\n",
        "trials = trials['TrialID']\n",
        "trials = pd.unique(trials)\n",
        "import re\n",
        "def trial_cleaner(data):\n",
        "\n",
        "  data = re.sub(r'\\W+', '', data).lower()\n",
        "  return data\n",
        "trials = trials.apply(trial_cleaner)\n",
        "trials = list(trials)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aJUYUUQirABS",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#Global set of paper Ids\n",
        "pid = dict()\n",
        "for trial in trials:\n",
        "  pid[trial] = list()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ev1xWlqEkB4G",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#Master Code\n",
        "root_dir = \"v6_text\"\n",
        "x = os.walk(\"v6_text\",topdown=False)\n",
        "for root,dir,file in x:\n",
        "  for name in file:\n",
        "    file_name = os.path.join(root_dir,name)\n",
        "    df = pd.read_pickle(file_name, compression = \"gzip\")\n",
        "    df = df.dropna(subset=['sentence'])\n",
        "    df2 = df.groupby('paper_id')\n",
        "    grouped_lists = df2['sentence'].agg(lambda x: \" \".join(x))\n",
        "    grouped_lists = grouped_lists.reset_index(name=\"sentence\")\n",
        "    grouped_lists['sentence']=grouped_lists['sentence'].str.lower()\n",
        "    for k in pid.keys():\n",
        "      for item in grouped_lists[grouped_lists['sentence'].str.contains(k)]['paper_id']:\n",
        "        pid[k].append(item)\n",
        "    "
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9rle-Bxux2gG",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import json\n",
        "json.dump(pid,open(\"mappings.json\", 'w'))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MmpD8Z7DMlpg",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import json\n",
        "with open('mappings.json', 'r') as fp:\n",
        "    data = json.load(fp)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0achvDOVPeCV",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "matches = pd.DataFrame([(key, var) for (key, L) in data.items() for var in L], columns=['key', 'paper_id'])\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "av40ooYpPxMv",
        "colab_type": "code",
        "outputId": "903de855-32a1-4d3a-b333-94f777ea2b42",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        }
      },
      "source": [
        "matches"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>key</th>\n",
              "      <th>paper_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>nct04246242</td>\n",
              "      <td>e3194f34badda0d3da79d2c00ec844bc24269e73</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>nct04252885</td>\n",
              "      <td>f6b9fd7dbed6516888eca71004caaa62aa0132d4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>chictr2000029935</td>\n",
              "      <td>4b2b9841f4d7064851b89540f55df916f279a82b</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>nct04261907</td>\n",
              "      <td>58be092086c74c58e9067121a6ba4836468e7ec3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>chictr2000029939</td>\n",
              "      <td>4b2b9841f4d7064851b89540f55df916f279a82b</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>78</th>\n",
              "      <td>nct04303507</td>\n",
              "      <td>eff6ce20122149117020cda90ad17f7cff0e9fd4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79</th>\n",
              "      <td>nct04283461</td>\n",
              "      <td>51cf515868c3f98cacf6c8fe12625714b3483331</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>80</th>\n",
              "      <td>nct04280705</td>\n",
              "      <td>05d47dd5b46f86428de058db4ecc8bca76a9ad16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>81</th>\n",
              "      <td>nct04280705</td>\n",
              "      <td>eba1da4b90ee7cff2811391e12e773baa5cd6c2e</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>82</th>\n",
              "      <td>nct04280705</td>\n",
              "      <td>57e5dbed53a99d2c2d0e5cccc22c7689a58c5557</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>83 rows × 2 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                 key                                  paper_id\n",
              "0        nct04246242  e3194f34badda0d3da79d2c00ec844bc24269e73\n",
              "1        nct04252885  f6b9fd7dbed6516888eca71004caaa62aa0132d4\n",
              "2   chictr2000029935  4b2b9841f4d7064851b89540f55df916f279a82b\n",
              "3        nct04261907  58be092086c74c58e9067121a6ba4836468e7ec3\n",
              "4   chictr2000029939  4b2b9841f4d7064851b89540f55df916f279a82b\n",
              "..               ...                                       ...\n",
              "78       nct04303507  eff6ce20122149117020cda90ad17f7cff0e9fd4\n",
              "79       nct04283461  51cf515868c3f98cacf6c8fe12625714b3483331\n",
              "80       nct04280705  05d47dd5b46f86428de058db4ecc8bca76a9ad16\n",
              "81       nct04280705  eba1da4b90ee7cff2811391e12e773baa5cd6c2e\n",
              "82       nct04280705  57e5dbed53a99d2c2d0e5cccc22c7689a58c5557\n",
              "\n",
              "[83 rows x 2 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 32
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_TFzlb9Ga2WB",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "linked = trials.merge(matches, left_on='cleaned_trial_id', right_on='key',how='left')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "09Xw9lbtchwk",
        "colab_type": "code",
        "outputId": "7b73e469-13be-4884-8efa-e22167a8596a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 54
        }
      },
      "source": [
        "!pip install geopy"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Requirement already satisfied: geopy in /usr/local/lib/python3.6/dist-packages (1.17.0)\n",
            "Requirement already satisfied: geographiclib<2,>=1.49 in /usr/local/lib/python3.6/dist-packages (from geopy) (1.50)\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vSuanrxGgDKw",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "from geopy.geocoders import Nominatim, GeoNames, DataBC, ArcGIS, TomTom, Bing, GoogleV3\n",
        "geolocator = Bing(api_key=\"API_KEY\")\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TxjOVzSUgVS_",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "from geopy.extra.rate_limiter import RateLimiter\n",
        "def locparse(s):\n",
        "    try:\n",
        "      geoc = RateLimiter(geolocator.geocode, min_delay_seconds=2)\n",
        "      return geoc(s).raw['address']['countryRegion']\n",
        "\n",
        "    except:\n",
        "      return np.nan\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "V3k31YuehS8k",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "linked['parsed_country'] = linked.apply(lambda x: locparse(x['Primary sponsor']) if not pd.notnull(x['Countries']) else x['Countries'], axis=1)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iCwTSTqdrCtv",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "linked_compressed = linked.loc[:, ['TrialID', 'paper_id','parsed_country']]"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6uwS1Dm2sH-x",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "linked_compressed.to_csv(\"ClinicalTrialsLinked.csv\")"
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}