3_generate_population_cohort.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Loading-data\" data-toc-modified-id=\"Loading-data-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Loading data</a></span><ul class=\"toc-item\"><li><span><a href=\"#Sanity-check\" data-toc-modified-id=\"Sanity-check-1.1\"><span class=\"toc-item-num\">1.1&nbsp;&nbsp;</span>Sanity check</a></span></li><li><span><a href=\"#Loading-all-reprots\" data-toc-modified-id=\"Loading-all-reprots-1.2\"><span class=\"toc-item-num\">1.2&nbsp;&nbsp;</span>Loading all reprots</a></span></li><li><span><a href=\"#Narrow-down-based-on-country-and-qualification\" data-toc-modified-id=\"Narrow-down-based-on-country-and-qualification-1.3\"><span class=\"toc-item-num\">1.3&nbsp;&nbsp;</span>Narrow down based on country and qualification</a></span></li></ul></li><li><span><a href=\"#Generate-data-for-all-patients\" data-toc-modified-id=\"Generate-data-for-all-patients-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Generate data for all patients</a></span><ul class=\"toc-item\"><li><span><a href=\"#Generate-data\" data-toc-modified-id=\"Generate-data-2.1\"><span class=\"toc-item-num\">2.1&nbsp;&nbsp;</span>Generate data</a></span><ul class=\"toc-item\"><li><span><a href=\"#Sanity-check\" data-toc-modified-id=\"Sanity-check-2.1.1\"><span class=\"toc-item-num\">2.1.1&nbsp;&nbsp;</span>Sanity check</a></span></li></ul></li><li><span><a href=\"#Population-distribution\" data-toc-modified-id=\"Population-distribution-2.2\"><span class=\"toc-item-num\">2.2&nbsp;&nbsp;</span>Population distribution</a></span></li></ul></li><li><span><a href=\"#Conditioned-on-Gender\" data-toc-modified-id=\"Conditioned-on-Gender-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Conditioned on Gender</a></span><ul class=\"toc-item\"><li><span><a href=\"#Male\" data-toc-modified-id=\"Male-3.1\"><span class=\"toc-item-num\">3.1&nbsp;&nbsp;</span>Male</a></span></li><li><span><a href=\"#Female\" data-toc-modified-id=\"Female-3.2\"><span class=\"toc-item-num\">3.2&nbsp;&nbsp;</span>Female</a></span></li></ul></li><li><span><a href=\"#Conditioned-on-Age\" data-toc-modified-id=\"Conditioned-on-Age-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>Conditioned on Age</a></span><ul class=\"toc-item\"><li><span><a href=\"#Bin-age-into-groups\" data-toc-modified-id=\"Bin-age-into-groups-4.1\"><span class=\"toc-item-num\">4.1&nbsp;&nbsp;</span>Bin age into groups</a></span></li><li><span><a href=\"#Three-age-groups\" data-toc-modified-id=\"Three-age-groups-4.2\"><span class=\"toc-item-num\">4.2&nbsp;&nbsp;</span>Three age groups</a></span></li></ul></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Loading data\n",
    "\n",
    "We investigate period from 03-11 to 09-30 from 2013 to 2020. If anyone want to analyze different time period, just replace the start or end time. For example, replace '09-30' by '12-31' to study the period from March 11 to December 31. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import itertools\n",
    "from tqdm import tqdm\n",
    "import pandas as pd\n",
    "import pickle\n",
    "import numpy as np\n",
    "from collections import Counter\n",
    "import scipy.stats as stats\n",
    "from statsmodels.stats.multitest import multipletests\n",
    "# %matplotlib notebook\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_rows', None)\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "# load the dictionaries for drugs, AE\n",
    "se_dic = pickle.load(open('../Data/curated/AE_dic.pk', 'rb'))\n",
    "drug_dic = pickle.load(open('../Data/curated/drug_mapping.pk', 'rb'))\n",
    "\n",
    "# In this MeDRA_dic, key is string of PT_name, value is a list:\n",
    "# [PT, PT_name, HLT,HLT_name,HLGT,HLGT_name,SOC,SOC_name,SOC_abbr]\n",
    "meddra_pd_all = pickle.load(open('../Data/curated/AE_mapping.pk', 'rb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def chi_sq(A, B,C,D):\n",
    "    A, B, C,D = A.astype(float), B.astype(float), C.astype(float), D.astype(float)\n",
    "    numerator =(A*D-B*C)**2\n",
    "    denominator = (A+B)*(C+D)*(A+C)*(B+D)\n",
    "    numerator = numerator/denominator\n",
    "    numerator_ = numerator*(A+B+C+D)\n",
    "    return numerator_\n",
    "\n",
    "def lower_CI(A, B,C,D, ROR):\n",
    "    A, B, C,D, ROR = A.astype(float), B.astype(float), C.astype(float), D.astype(float), ROR.astype(float)\n",
    "    s = np.sqrt(1/A+ 1/B + 1/C +1/D)\n",
    "    CI_low = np.e**(np.log(ROR)-1.96*s)\n",
    "#     CI_high = e**(np.log(ROR)+1.96*s)\n",
    "    return CI_low"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Sanity check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "hidden": true
   },
   "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>PT</th>\n",
       "      <th>PT_name</th>\n",
       "      <th>HLT</th>\n",
       "      <th>HLT_name</th>\n",
       "      <th>HLGT</th>\n",
       "      <th>HLGT_name</th>\n",
       "      <th>SOC</th>\n",
       "      <th>SOC_name</th>\n",
       "      <th>SOC_abbr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000002</td>\n",
       "      <td>11-beta-hydroxylase deficiency</td>\n",
       "      <td>10021608</td>\n",
       "      <td>inborn errors of steroid synthesis</td>\n",
       "      <td>10027424</td>\n",
       "      <td>metabolic and nutritional disorders congenital</td>\n",
       "      <td>10010331</td>\n",
       "      <td>congenital, familial and genetic disorders</td>\n",
       "      <td>Cong</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         PT                         PT_name       HLT  \\\n",
       "0  10000002  11-beta-hydroxylase deficiency  10021608   \n",
       "\n",
       "                             HLT_name      HLGT  \\\n",
       "0  inborn errors of steroid synthesis  10027424   \n",
       "\n",
       "                                        HLGT_name       SOC  \\\n",
       "0  metabolic and nutritional disorders congenital  10010331   \n",
       "\n",
       "                                     SOC_name SOC_abbr  \n",
       "0  congenital, familial and genetic disorders     Cong  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meddra_pd_all.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "hidden": true
   },
   "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>PT</th>\n",
       "      <th>PT_name</th>\n",
       "      <th>HLT</th>\n",
       "      <th>HLT_name</th>\n",
       "      <th>HLGT</th>\n",
       "      <th>HLGT_name</th>\n",
       "      <th>SOC</th>\n",
       "      <th>SOC_name</th>\n",
       "      <th>SOC_abbr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10018358</td>\n",
       "      <td>glomerular filtration rate decreased</td>\n",
       "      <td>10038454</td>\n",
       "      <td>renal function analyses</td>\n",
       "      <td>10038362</td>\n",
       "      <td>renal and urinary tract investigations and uri...</td>\n",
       "      <td>10022891</td>\n",
       "      <td>investigations</td>\n",
       "      <td>Inv</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         PT                               PT_name       HLT  \\\n",
       "0  10018358  glomerular filtration rate decreased  10038454   \n",
       "\n",
       "                  HLT_name      HLGT  \\\n",
       "0  renal function analyses  10038362   \n",
       "\n",
       "                                           HLGT_name       SOC  \\\n",
       "0  renal and urinary tract investigations and uri...  10022891   \n",
       "\n",
       "         SOC_name SOC_abbr  \n",
       "0  investigations      Inv  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meddra_pd_all[meddra_pd_all.PT=='10018358']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "hidden": true
   },
   "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>drugbank_ID</th>\n",
       "      <th>code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>etanercept</th>\n",
       "      <td>DB00005</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zoledronic acid</th>\n",
       "      <td>DB00399</td>\n",
       "      <td>334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>atorvastatin</th>\n",
       "      <td>DB01076</td>\n",
       "      <td>931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>metformin</th>\n",
       "      <td>DB00331</td>\n",
       "      <td>274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rosiglitazone</th>\n",
       "      <td>DB00412</td>\n",
       "      <td>343</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                drugbank_ID code\n",
       "etanercept          DB00005    4\n",
       "zoledronic acid     DB00399  334\n",
       "atorvastatin        DB01076  931\n",
       "metformin           DB00331  274\n",
       "rosiglitazone       DB00412  343"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drug dictionary to df\n",
    "drug_dic_pd = pd.DataFrame(drug_dic)\n",
    "drug_dic_df = drug_dic_pd.T\n",
    "\n",
    "drug_dic_df.columns=['drugbank_ID', 'code']\n",
    "drug_dic_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "hidden": true,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(4, ['etanercept', 'DB00005']),\n",
       " (334, ['zoledronic acid', 'DB00399']),\n",
       " (931, ['atorvastatin', 'DB01076']),\n",
       " (274, ['metformin', 'DB00331']),\n",
       " (343, ['rosiglitazone', 'DB00412']),\n",
       " (174, ['glimepiride', 'DB00222']),\n",
       " (305, ['levonorgestrel', 'DB00367']),\n",
       " (1077, ['epoprostenol', 'DB01240']),\n",
       " (1060, ['budesonide', 'DB01222']),\n",
       " (626, ['imiquimod', 'DB00724'])]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drug_dic_df[drug_dic_df.code == 931]\n",
    "# drug_dic['remdesivir']\n",
    "\n",
    "# Make a drug_code_dic to find the drug name by code\n",
    "drug_code_dic = {}\n",
    "for key, value in drug_dic.items():\n",
    "    drug_code_dic[value[-1]]=[key, value[0]]\n",
    "pickle.dump(drug_code_dic, open('../Data/parsed/drug_code_dic.pk', 'wb'))\n",
    "list(drug_code_dic.items())[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading all reprots"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>version</th>\n",
       "      <th>report_id</th>\n",
       "      <th>case_id</th>\n",
       "      <th>country</th>\n",
       "      <th>qualify</th>\n",
       "      <th>serious</th>\n",
       "      <th>s1</th>\n",
       "      <th>s2</th>\n",
       "      <th>s3</th>\n",
       "      <th>s4</th>\n",
       "      <th>s5</th>\n",
       "      <th>s6</th>\n",
       "      <th>receivedate</th>\n",
       "      <th>receiptdate</th>\n",
       "      <th>age</th>\n",
       "      <th>gender</th>\n",
       "      <th>weight</th>\n",
       "      <th>SE</th>\n",
       "      <th>drugs</th>\n",
       "      <th>indications</th>\n",
       "      <th>lastingdays</th>\n",
       "      <th>date</th>\n",
       "      <th>receipt_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18</td>\n",
       "      <td>6161368</td>\n",
       "      <td>PHEH2006US13340</td>\n",
       "      <td>US</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2498</td>\n",
       "      <td>4776</td>\n",
       "      <td>68</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>[10001488, 10002034, 10002855, 10002948, 10003...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>2278</td>\n",
       "      <td>2006-11-03</td>\n",
       "      <td>2013-01-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>7820167</td>\n",
       "      <td>US-ROCHE-735882</td>\n",
       "      <td>US</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4069</td>\n",
       "      <td>4764</td>\n",
       "      <td>39</td>\n",
       "      <td>2</td>\n",
       "      <td>68</td>\n",
       "      <td>[10000081, 10003246, 10009900, 10012378, 10013...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>695</td>\n",
       "      <td>2011-02-21</td>\n",
       "      <td>2013-01-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>7683841</td>\n",
       "      <td>PHHY2009CA17701</td>\n",
       "      <td>CA</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3982</td>\n",
       "      <td>4777</td>\n",
       "      <td>-1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>[10000081, 10000087, 10001949, 10003445, 10006...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>795</td>\n",
       "      <td>2010-11-26</td>\n",
       "      <td>2013-01-29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  version report_id          case_id country qualify serious s1 s2 s3 s4 s5  \\\n",
       "0      18   6161368  PHEH2006US13340      US       5       1  1  0  1  0  0   \n",
       "1       2   7820167  US-ROCHE-735882      US       4       1  0  0  1  0  0   \n",
       "2      11   7683841  PHHY2009CA17701      CA       3       1  0  0  1  0  0   \n",
       "\n",
       "  s6  receivedate  receiptdate age gender weight  \\\n",
       "0  1         2498         4776  68      2      0   \n",
       "1  1         4069         4764  39      2     68   \n",
       "2  1         3982         4777  -1      2      0   \n",
       "\n",
       "                                                  SE drugs indications  \\\n",
       "0  [10001488, 10002034, 10002855, 10002948, 10003...    []          []   \n",
       "1  [10000081, 10003246, 10009900, 10012378, 10013...    []          []   \n",
       "2  [10000081, 10000087, 10001949, 10003445, 10006...    []          []   \n",
       "\n",
       "   lastingdays        date receipt_date  \n",
       "0         2278  2006-11-03   2013-01-28  \n",
       "1          695  2011-02-21   2013-01-16  \n",
       "2          795  2010-11-26   2013-01-29  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_pd = pickle.load(open('../Data/curated/patient_safety.pk', 'rb'))  \n",
    "\n",
    "all_pd.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Narrow down based on country and qualification"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Focus on US, reports # (6351817, 23)\n",
      "Focus on professional qualification, reports # (2551071, 23)\n"
     ]
    }
   ],
   "source": [
    "all_pd_US = all_pd[all_pd.country=='US']\n",
    "print('Focus on US, reports #', all_pd_US.shape)\n",
    "\n",
    "id_qua = [i in ['1', '2', '3'] for i in all_pd_US.qualify ]\n",
    "all_pd_US_pro = all_pd_US[id_qua]  # professional: 1,2,3\n",
    "print('Focus on professional qualification, reports #', all_pd_US_pro.shape)\n",
    "\n",
    "pickle.dump(all_pd_US_pro, open('../Data/pandemic/all_pd_US_pro.pk', 'wb'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate data for all patients\n",
    "\n",
    "The overall population (all patients) is denoted by 'uncondition'. Variables with 'uncondition' in name refers to the population of all patients."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "code_folding": []
   },
   "source": [
    "## Generate data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_pd_US_pro = pickle.load(open('../Data/pandemic/all_pd_US_pro.pk', 'rb'))  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "#-SE in US pro 15454\n",
      "(15454, 9)\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>PT</th>\n",
       "      <th>PT_name</th>\n",
       "      <th>HLT</th>\n",
       "      <th>HLT_name</th>\n",
       "      <th>HLGT</th>\n",
       "      <th>HLGT_name</th>\n",
       "      <th>SOC</th>\n",
       "      <th>SOC_name</th>\n",
       "      <th>SOC_abbr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000014</td>\n",
       "      <td>17-alpha-hydroxylase deficiency</td>\n",
       "      <td>10021608</td>\n",
       "      <td>inborn errors of steroid synthesis</td>\n",
       "      <td>10027424</td>\n",
       "      <td>metabolic and nutritional disorders congenital</td>\n",
       "      <td>10010331</td>\n",
       "      <td>congenital, familial and genetic disorders</td>\n",
       "      <td>Cong</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000029</td>\n",
       "      <td>5-alpha-reductase deficiency</td>\n",
       "      <td>10021608</td>\n",
       "      <td>inborn errors of steroid synthesis</td>\n",
       "      <td>10027424</td>\n",
       "      <td>metabolic and nutritional disorders congenital</td>\n",
       "      <td>10010331</td>\n",
       "      <td>congenital, familial and genetic disorders</td>\n",
       "      <td>Cong</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000044</td>\n",
       "      <td>abdomen crushing</td>\n",
       "      <td>10083613</td>\n",
       "      <td>abdominal and gastrointestinal injuries nec</td>\n",
       "      <td>10022114</td>\n",
       "      <td>injuries nec</td>\n",
       "      <td>10022117</td>\n",
       "      <td>injury, poisoning and procedural complications</td>\n",
       "      <td>Inj&amp;P</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         PT                          PT_name       HLT  \\\n",
       "0  10000014  17-alpha-hydroxylase deficiency  10021608   \n",
       "0  10000029     5-alpha-reductase deficiency  10021608   \n",
       "0  10000044                 abdomen crushing  10083613   \n",
       "\n",
       "                                      HLT_name      HLGT  \\\n",
       "0           inborn errors of steroid synthesis  10027424   \n",
       "0           inborn errors of steroid synthesis  10027424   \n",
       "0  abdominal and gastrointestinal injuries nec  10022114   \n",
       "\n",
       "                                        HLGT_name       SOC  \\\n",
       "0  metabolic and nutritional disorders congenital  10010331   \n",
       "0  metabolic and nutritional disorders congenital  10010331   \n",
       "0                                    injuries nec  10022117   \n",
       "\n",
       "                                         SOC_name SOC_abbr  \n",
       "0      congenital, familial and genetic disorders     Cong  \n",
       "0      congenital, familial and genetic disorders     Cong  \n",
       "0  injury, poisoning and procedural complications    Inj&P  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# SE_list = list(SE_dic_df.code)\n",
    "DF = all_pd_US_pro\n",
    "SE_list = list(sorted(set(list(itertools.chain(*DF.SE)))))\n",
    "print('#-SE in US pro',len(SE_list))\n",
    "\n",
    "ind = [i in SE_list for i in meddra_pd_all.PT]\n",
    "whatever_ = meddra_pd_all[ind]\n",
    "print(whatever_.shape)\n",
    "whatever_.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2013:89334\n",
      "rows in 2014:97804\n",
      "rows in 2015:179383\n",
      "rows in 2016:169233\n",
      "rows in 2017:210736\n",
      "rows in 2018:244005\n",
      "rows in 2019:220920\n",
      "rows in 2020:211152\n"
     ]
    }
   ],
   "source": [
    "# reports in 2020 \n",
    "yr_list = [2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020]\n",
    "n_re = []\n",
    "for yr in yr_list:\n",
    "    st = str(yr)+'-03-10'\n",
    "    end = str(yr)+'-09-31'      \n",
    "    ind = [st<i<end for i in all_pd_US_pro['receipt_date']]  # all ['date'] --> ['receipt_date']\n",
    "    locals()['all_pd_US_pro_'+str(yr)]= all_pd_US_pro[ind]\n",
    "    n_reports = len(locals()['all_pd_US_pro_'+str(yr)])\n",
    "    n_re.append(n_reports)\n",
    "    print('rows in {}:{}'.format(yr,n_reports))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "  0%|          | 9/15454 [00:00<02:58, 86.76it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2013 year has 89334 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 15454/15454 [02:35<00:00, 99.16it/s] \n",
      "  0%|          | 9/15454 [00:00<03:06, 82.72it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2014 year has 97804 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 15454/15454 [02:57<00:00, 87.20it/s]\n",
      "  0%|          | 5/15454 [00:00<05:15, 48.90it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2015 year has 179383 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 15454/15454 [05:10<00:00, 49.74it/s]\n",
      "  0%|          | 6/15454 [00:00<04:55, 52.19it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2016 year has 169233 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 15454/15454 [04:55<00:00, 52.24it/s]\n",
      "  0%|          | 5/15454 [00:00<06:21, 40.50it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017 year has 210736 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 15454/15454 [06:06<00:00, 42.19it/s]\n",
      "  0%|          | 4/15454 [00:00<06:43, 38.31it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2018 year has 244005 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 15454/15454 [06:52<00:00, 37.50it/s]\n",
      "  0%|          | 5/15454 [00:00<06:17, 40.87it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019 year has 220920 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 15454/15454 [06:18<00:00, 40.82it/s]\n",
      "  0%|          | 5/15454 [00:00<06:10, 41.71it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020 year has 211152 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 15454/15454 [06:11<00:00, 41.65it/s]\n"
     ]
    }
   ],
   "source": [
    "yr_list = [2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020]\n",
    "\n",
    "\"\"\"initialize the Data frame \"\"\"\n",
    "se_matrix = pd.DataFrame({'SE': list(whatever_.PT), 'name':list(whatever_['PT_name']) }) #'medra_ID': list(SE_dic_df['medra_ID'])\n",
    "\n",
    "for yr in yr_list:\n",
    "    n_report = len(locals()['all_pd_US_pro_'+str(yr)])\n",
    "    print('{} year has {} reports'.format(yr, n_report))\n",
    "    A =[]\n",
    "\n",
    "    for se in tqdm(SE_list):\n",
    "        name = locals()['all_pd_US_pro_'+str(yr)]\n",
    "        indx = [se in j for j in name.SE]\n",
    "        n_A = sum(indx)\n",
    "        A.append(n_A)\n",
    "    B = [n_report - i for i in A]\n",
    "    se_matrix[str(yr)+'_A'] = A\n",
    "    se_matrix[str(yr)+'_B'] = B"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "pickle.dump(se_matrix, open('../Data/pandemic/SE_uncondition_raw.pk', 'wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>SE</th>\n",
       "      <th>name</th>\n",
       "      <th>2013_A</th>\n",
       "      <th>2013_B</th>\n",
       "      <th>2014_A</th>\n",
       "      <th>2014_B</th>\n",
       "      <th>2015_A</th>\n",
       "      <th>2015_B</th>\n",
       "      <th>2016_A</th>\n",
       "      <th>2016_B</th>\n",
       "      <th>2017_A</th>\n",
       "      <th>2017_B</th>\n",
       "      <th>2018_A</th>\n",
       "      <th>2018_B</th>\n",
       "      <th>2019_A</th>\n",
       "      <th>2019_B</th>\n",
       "      <th>2020_A</th>\n",
       "      <th>2020_B</th>\n",
       "      <th>2013_ROR</th>\n",
       "      <th>2014_ROR</th>\n",
       "      <th>2015_ROR</th>\n",
       "      <th>2016_ROR</th>\n",
       "      <th>2017_ROR</th>\n",
       "      <th>2018_ROR</th>\n",
       "      <th>2019_ROR</th>\n",
       "      <th>2013_Delta</th>\n",
       "      <th>2014_Delta</th>\n",
       "      <th>2015_Delta</th>\n",
       "      <th>2016_Delta</th>\n",
       "      <th>2017_Delta</th>\n",
       "      <th>2018_Delta</th>\n",
       "      <th>2019_Delta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000014</td>\n",
       "      <td>17-alpha-hydroxylase deficiency</td>\n",
       "      <td>0</td>\n",
       "      <td>89334</td>\n",
       "      <td>0</td>\n",
       "      <td>97804</td>\n",
       "      <td>1</td>\n",
       "      <td>179382</td>\n",
       "      <td>0</td>\n",
       "      <td>169233</td>\n",
       "      <td>0</td>\n",
       "      <td>210736</td>\n",
       "      <td>0</td>\n",
       "      <td>244005</td>\n",
       "      <td>0</td>\n",
       "      <td>220920</td>\n",
       "      <td>0</td>\n",
       "      <td>211152</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000029</td>\n",
       "      <td>5-alpha-reductase deficiency</td>\n",
       "      <td>0</td>\n",
       "      <td>89334</td>\n",
       "      <td>0</td>\n",
       "      <td>97804</td>\n",
       "      <td>0</td>\n",
       "      <td>179383</td>\n",
       "      <td>0</td>\n",
       "      <td>169233</td>\n",
       "      <td>0</td>\n",
       "      <td>210736</td>\n",
       "      <td>0</td>\n",
       "      <td>244005</td>\n",
       "      <td>0</td>\n",
       "      <td>220920</td>\n",
       "      <td>1</td>\n",
       "      <td>211151</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000044</td>\n",
       "      <td>abdomen crushing</td>\n",
       "      <td>0</td>\n",
       "      <td>89334</td>\n",
       "      <td>0</td>\n",
       "      <td>97804</td>\n",
       "      <td>0</td>\n",
       "      <td>179383</td>\n",
       "      <td>0</td>\n",
       "      <td>169233</td>\n",
       "      <td>0</td>\n",
       "      <td>210736</td>\n",
       "      <td>0</td>\n",
       "      <td>244005</td>\n",
       "      <td>0</td>\n",
       "      <td>220920</td>\n",
       "      <td>1</td>\n",
       "      <td>211151</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000050</td>\n",
       "      <td>abdominal adhesions</td>\n",
       "      <td>14</td>\n",
       "      <td>89320</td>\n",
       "      <td>14</td>\n",
       "      <td>97790</td>\n",
       "      <td>16</td>\n",
       "      <td>179367</td>\n",
       "      <td>7</td>\n",
       "      <td>169226</td>\n",
       "      <td>21</td>\n",
       "      <td>210715</td>\n",
       "      <td>16</td>\n",
       "      <td>243989</td>\n",
       "      <td>15</td>\n",
       "      <td>220905</td>\n",
       "      <td>12</td>\n",
       "      <td>211140</td>\n",
       "      <td>0.362603</td>\n",
       "      <td>0.396988</td>\n",
       "      <td>0.637138</td>\n",
       "      <td>1.373978</td>\n",
       "      <td>0.570278</td>\n",
       "      <td>0.866684</td>\n",
       "      <td>0.836999</td>\n",
       "      <td>-0.142857</td>\n",
       "      <td>-0.142857</td>\n",
       "      <td>-0.250000</td>\n",
       "      <td>0.714286</td>\n",
       "      <td>-0.428571</td>\n",
       "      <td>-0.250000</td>\n",
       "      <td>-0.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000059</td>\n",
       "      <td>abdominal discomfort</td>\n",
       "      <td>559</td>\n",
       "      <td>88775</td>\n",
       "      <td>613</td>\n",
       "      <td>97191</td>\n",
       "      <td>1402</td>\n",
       "      <td>177981</td>\n",
       "      <td>1446</td>\n",
       "      <td>167787</td>\n",
       "      <td>1999</td>\n",
       "      <td>208737</td>\n",
       "      <td>2400</td>\n",
       "      <td>241605</td>\n",
       "      <td>2145</td>\n",
       "      <td>218775</td>\n",
       "      <td>1993</td>\n",
       "      <td>209159</td>\n",
       "      <td>1.513246</td>\n",
       "      <td>1.510763</td>\n",
       "      <td>1.209641</td>\n",
       "      <td>1.105658</td>\n",
       "      <td>0.994987</td>\n",
       "      <td>0.959236</td>\n",
       "      <td>0.971854</td>\n",
       "      <td>2.565295</td>\n",
       "      <td>2.251223</td>\n",
       "      <td>0.421541</td>\n",
       "      <td>0.378285</td>\n",
       "      <td>-0.003002</td>\n",
       "      <td>-0.169583</td>\n",
       "      <td>-0.070862</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         SE                             name  2013_A  2013_B  2014_A  2014_B  \\\n",
       "0  10000014  17-alpha-hydroxylase deficiency       0   89334       0   97804   \n",
       "1  10000029     5-alpha-reductase deficiency       0   89334       0   97804   \n",
       "2  10000044                 abdomen crushing       0   89334       0   97804   \n",
       "3  10000050              abdominal adhesions      14   89320      14   97790   \n",
       "4  10000059             abdominal discomfort     559   88775     613   97191   \n",
       "\n",
       "   2015_A  2015_B  2016_A  2016_B  2017_A  2017_B  2018_A  2018_B  2019_A  \\\n",
       "0       1  179382       0  169233       0  210736       0  244005       0   \n",
       "1       0  179383       0  169233       0  210736       0  244005       0   \n",
       "2       0  179383       0  169233       0  210736       0  244005       0   \n",
       "3      16  179367       7  169226      21  210715      16  243989      15   \n",
       "4    1402  177981    1446  167787    1999  208737    2400  241605    2145   \n",
       "\n",
       "   2019_B  2020_A  2020_B  2013_ROR  2014_ROR  2015_ROR  2016_ROR  2017_ROR  \\\n",
       "0  220920       0  211152       NaN       NaN  0.000000       NaN       NaN   \n",
       "1  220920       1  211151       inf       inf       inf       inf       inf   \n",
       "2  220920       1  211151       inf       inf       inf       inf       inf   \n",
       "3  220905      12  211140  0.362603  0.396988  0.637138  1.373978  0.570278   \n",
       "4  218775    1993  209159  1.513246  1.510763  1.209641  1.105658  0.994987   \n",
       "\n",
       "   2018_ROR  2019_ROR  2013_Delta  2014_Delta  2015_Delta  2016_Delta  \\\n",
       "0       NaN       NaN         NaN         NaN   -1.000000         NaN   \n",
       "1       inf       inf         inf         inf         inf         inf   \n",
       "2       inf       inf         inf         inf         inf         inf   \n",
       "3  0.866684  0.836999   -0.142857   -0.142857   -0.250000    0.714286   \n",
       "4  0.959236  0.971854    2.565295    2.251223    0.421541    0.378285   \n",
       "\n",
       "   2017_Delta  2018_Delta  2019_Delta  \n",
       "0         NaN         NaN         NaN  \n",
       "1         inf         inf         inf  \n",
       "2         inf         inf         inf  \n",
       "3   -0.428571   -0.250000   -0.200000  \n",
       "4   -0.003002   -0.169583   -0.070862  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "para = se_matrix\n",
    "yr_list = [2013, 2014, 2015, 2016, 2017, 2018, 2019]\n",
    "for yr in yr_list:  # calculate ROR  \n",
    "    para[str(yr)+'_ROR'] = (para['2020_A']*para[str(yr)+'_B'])/(para['2020_B']*para[str(yr)+'_A'])\n",
    "\n",
    "for yr in yr_list:  # calculate Delta: average difference  \n",
    "    para[str(yr)+'_Delta'] = (para['2020_A'] - para[str(yr)+'_A'])/para[str(yr)+'_A']\n",
    "\n",
    "pd.set_option('display.max_columns', None)\n",
    "\"\"\"Note: 0/0 = NaN, 1/0 = inf\"\"\"\n",
    "para.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "pickle.dump(para, open('../Data/pandemic/SE_uncondition.pk', 'wb'))  # update the dataframe with ROR and Delta"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sanity check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "uncondition_2019_history = pickle.load(open('../Data/pandemic/SE_uncondition.pk', 'rb')) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>SE</th>\n",
       "      <th>name</th>\n",
       "      <th>2013_A</th>\n",
       "      <th>2013_B</th>\n",
       "      <th>2014_A</th>\n",
       "      <th>2014_B</th>\n",
       "      <th>2015_A</th>\n",
       "      <th>2015_B</th>\n",
       "      <th>2016_A</th>\n",
       "      <th>2016_B</th>\n",
       "      <th>2017_A</th>\n",
       "      <th>2017_B</th>\n",
       "      <th>2018_A</th>\n",
       "      <th>2018_B</th>\n",
       "      <th>2019_A</th>\n",
       "      <th>2019_B</th>\n",
       "      <th>2020_A</th>\n",
       "      <th>2020_B</th>\n",
       "      <th>2013_ROR</th>\n",
       "      <th>2014_ROR</th>\n",
       "      <th>2015_ROR</th>\n",
       "      <th>2016_ROR</th>\n",
       "      <th>2017_ROR</th>\n",
       "      <th>2018_ROR</th>\n",
       "      <th>2019_ROR</th>\n",
       "      <th>2013_Delta</th>\n",
       "      <th>2014_Delta</th>\n",
       "      <th>2015_Delta</th>\n",
       "      <th>2016_Delta</th>\n",
       "      <th>2017_Delta</th>\n",
       "      <th>2018_Delta</th>\n",
       "      <th>2019_Delta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1123</th>\n",
       "      <td>10007515</td>\n",
       "      <td>cardiac arrest</td>\n",
       "      <td>525</td>\n",
       "      <td>88809</td>\n",
       "      <td>750</td>\n",
       "      <td>97054</td>\n",
       "      <td>637</td>\n",
       "      <td>178746</td>\n",
       "      <td>867</td>\n",
       "      <td>168366</td>\n",
       "      <td>714</td>\n",
       "      <td>210022</td>\n",
       "      <td>667</td>\n",
       "      <td>243338</td>\n",
       "      <td>725</td>\n",
       "      <td>220195</td>\n",
       "      <td>1331</td>\n",
       "      <td>209821</td>\n",
       "      <td>1.073067</td>\n",
       "      <td>0.820883</td>\n",
       "      <td>1.780025</td>\n",
       "      <td>1.231869</td>\n",
       "      <td>1.865931</td>\n",
       "      <td>2.314266</td>\n",
       "      <td>1.926631</td>\n",
       "      <td>1.535238</td>\n",
       "      <td>0.774667</td>\n",
       "      <td>1.089482</td>\n",
       "      <td>0.535179</td>\n",
       "      <td>0.864146</td>\n",
       "      <td>0.995502</td>\n",
       "      <td>0.835862</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            SE            name  2013_A  2013_B  2014_A  2014_B  2015_A  \\\n",
       "1123  10007515  cardiac arrest     525   88809     750   97054     637   \n",
       "\n",
       "      2015_B  2016_A  2016_B  2017_A  2017_B  2018_A  2018_B  2019_A  2019_B  \\\n",
       "1123  178746     867  168366     714  210022     667  243338     725  220195   \n",
       "\n",
       "      2020_A  2020_B  2013_ROR  2014_ROR  2015_ROR  2016_ROR  2017_ROR  \\\n",
       "1123    1331  209821  1.073067  0.820883  1.780025  1.231869  1.865931   \n",
       "\n",
       "      2018_ROR  2019_ROR  2013_Delta  2014_Delta  2015_Delta  2016_Delta  \\\n",
       "1123  2.314266  1.926631    1.535238    0.774667    1.089482    0.535179   \n",
       "\n",
       "      2017_Delta  2018_Delta  2019_Delta  \n",
       "1123    0.864146    0.995502    0.835862  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "uncondition_2019_history[uncondition_2019_history.name=='cardiac arrest']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(5724, 32)\n",
      "(78, 32)\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>SE</th>\n",
       "      <th>name</th>\n",
       "      <th>2018_A</th>\n",
       "      <th>2019_A</th>\n",
       "      <th>2020_A</th>\n",
       "      <th>2019_Delta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>10001551</td>\n",
       "      <td>alanine aminotransferase increased</td>\n",
       "      <td>389</td>\n",
       "      <td>436</td>\n",
       "      <td>1222</td>\n",
       "      <td>1.802752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>10002034</td>\n",
       "      <td>anaemia</td>\n",
       "      <td>1406</td>\n",
       "      <td>1482</td>\n",
       "      <td>1629</td>\n",
       "      <td>0.099190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355</th>\n",
       "      <td>10002855</td>\n",
       "      <td>anxiety</td>\n",
       "      <td>1781</td>\n",
       "      <td>1777</td>\n",
       "      <td>2113</td>\n",
       "      <td>0.189083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>433</th>\n",
       "      <td>10003239</td>\n",
       "      <td>arthralgia</td>\n",
       "      <td>4331</td>\n",
       "      <td>3266</td>\n",
       "      <td>3744</td>\n",
       "      <td>0.146356</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>470</th>\n",
       "      <td>10003549</td>\n",
       "      <td>asthenia</td>\n",
       "      <td>3164</td>\n",
       "      <td>2957</td>\n",
       "      <td>3074</td>\n",
       "      <td>0.039567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>534</th>\n",
       "      <td>10003988</td>\n",
       "      <td>back pain</td>\n",
       "      <td>3119</td>\n",
       "      <td>2121</td>\n",
       "      <td>2322</td>\n",
       "      <td>0.094767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>903</th>\n",
       "      <td>10005750</td>\n",
       "      <td>blood pressure increased</td>\n",
       "      <td>1199</td>\n",
       "      <td>1020</td>\n",
       "      <td>1095</td>\n",
       "      <td>0.073529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1123</th>\n",
       "      <td>10007515</td>\n",
       "      <td>cardiac arrest</td>\n",
       "      <td>667</td>\n",
       "      <td>725</td>\n",
       "      <td>1331</td>\n",
       "      <td>0.835862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1237</th>\n",
       "      <td>10008190</td>\n",
       "      <td>cerebrovascular accident</td>\n",
       "      <td>1050</td>\n",
       "      <td>1098</td>\n",
       "      <td>1197</td>\n",
       "      <td>0.090164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1273</th>\n",
       "      <td>10008469</td>\n",
       "      <td>chest discomfort</td>\n",
       "      <td>876</td>\n",
       "      <td>826</td>\n",
       "      <td>1005</td>\n",
       "      <td>0.216707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1274</th>\n",
       "      <td>10008479</td>\n",
       "      <td>chest pain</td>\n",
       "      <td>1765</td>\n",
       "      <td>1595</td>\n",
       "      <td>1790</td>\n",
       "      <td>0.122257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1281</th>\n",
       "      <td>10008531</td>\n",
       "      <td>chills</td>\n",
       "      <td>1266</td>\n",
       "      <td>931</td>\n",
       "      <td>1042</td>\n",
       "      <td>0.119227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1454</th>\n",
       "      <td>10010144</td>\n",
       "      <td>completed suicide</td>\n",
       "      <td>847</td>\n",
       "      <td>1250</td>\n",
       "      <td>1483</td>\n",
       "      <td>0.186400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1471</th>\n",
       "      <td>10010264</td>\n",
       "      <td>condition aggravated</td>\n",
       "      <td>2382</td>\n",
       "      <td>2607</td>\n",
       "      <td>3072</td>\n",
       "      <td>0.178366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1475</th>\n",
       "      <td>10010305</td>\n",
       "      <td>confusional state</td>\n",
       "      <td>1254</td>\n",
       "      <td>1229</td>\n",
       "      <td>1638</td>\n",
       "      <td>0.332791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1544</th>\n",
       "      <td>10010774</td>\n",
       "      <td>constipation</td>\n",
       "      <td>2149</td>\n",
       "      <td>2341</td>\n",
       "      <td>2399</td>\n",
       "      <td>0.024776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1601</th>\n",
       "      <td>10011224</td>\n",
       "      <td>cough</td>\n",
       "      <td>2636</td>\n",
       "      <td>2451</td>\n",
       "      <td>2874</td>\n",
       "      <td>0.172583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1715</th>\n",
       "      <td>10011906</td>\n",
       "      <td>death</td>\n",
       "      <td>7781</td>\n",
       "      <td>7744</td>\n",
       "      <td>10010</td>\n",
       "      <td>0.292614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1756</th>\n",
       "      <td>10012378</td>\n",
       "      <td>depression</td>\n",
       "      <td>1767</td>\n",
       "      <td>1767</td>\n",
       "      <td>1807</td>\n",
       "      <td>0.022637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1878</th>\n",
       "      <td>10013654</td>\n",
       "      <td>drug abuse</td>\n",
       "      <td>391</td>\n",
       "      <td>952</td>\n",
       "      <td>1185</td>\n",
       "      <td>0.244748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1884</th>\n",
       "      <td>10013710</td>\n",
       "      <td>drug interaction</td>\n",
       "      <td>1038</td>\n",
       "      <td>1269</td>\n",
       "      <td>1339</td>\n",
       "      <td>0.055162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1930</th>\n",
       "      <td>10013968</td>\n",
       "      <td>dyspnoea</td>\n",
       "      <td>5205</td>\n",
       "      <td>5414</td>\n",
       "      <td>6130</td>\n",
       "      <td>0.132250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2244</th>\n",
       "      <td>10016173</td>\n",
       "      <td>fall</td>\n",
       "      <td>2851</td>\n",
       "      <td>2844</td>\n",
       "      <td>3669</td>\n",
       "      <td>0.290084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2272</th>\n",
       "      <td>10016322</td>\n",
       "      <td>feeling abnormal</td>\n",
       "      <td>2328</td>\n",
       "      <td>2233</td>\n",
       "      <td>2371</td>\n",
       "      <td>0.061800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2324</th>\n",
       "      <td>10016807</td>\n",
       "      <td>fluid retention</td>\n",
       "      <td>678</td>\n",
       "      <td>826</td>\n",
       "      <td>1138</td>\n",
       "      <td>0.377724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2327</th>\n",
       "      <td>10016825</td>\n",
       "      <td>flushing</td>\n",
       "      <td>841</td>\n",
       "      <td>980</td>\n",
       "      <td>1094</td>\n",
       "      <td>0.116327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2389</th>\n",
       "      <td>10017577</td>\n",
       "      <td>gait disturbance</td>\n",
       "      <td>1540</td>\n",
       "      <td>1347</td>\n",
       "      <td>1758</td>\n",
       "      <td>0.305122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2466</th>\n",
       "      <td>10017955</td>\n",
       "      <td>gastrointestinal haemorrhage</td>\n",
       "      <td>904</td>\n",
       "      <td>914</td>\n",
       "      <td>1462</td>\n",
       "      <td>0.599562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2609</th>\n",
       "      <td>10018884</td>\n",
       "      <td>haemoglobin decreased</td>\n",
       "      <td>896</td>\n",
       "      <td>957</td>\n",
       "      <td>1241</td>\n",
       "      <td>0.296761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2640</th>\n",
       "      <td>10019063</td>\n",
       "      <td>hallucination</td>\n",
       "      <td>633</td>\n",
       "      <td>699</td>\n",
       "      <td>1610</td>\n",
       "      <td>1.303290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2872</th>\n",
       "      <td>10020772</td>\n",
       "      <td>hypertension</td>\n",
       "      <td>1517</td>\n",
       "      <td>1358</td>\n",
       "      <td>1638</td>\n",
       "      <td>0.206186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2898</th>\n",
       "      <td>10020937</td>\n",
       "      <td>hypoaesthesia</td>\n",
       "      <td>1293</td>\n",
       "      <td>1270</td>\n",
       "      <td>1284</td>\n",
       "      <td>0.011024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2944</th>\n",
       "      <td>10021097</td>\n",
       "      <td>hypotension</td>\n",
       "      <td>2596</td>\n",
       "      <td>2286</td>\n",
       "      <td>2570</td>\n",
       "      <td>0.124234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3049</th>\n",
       "      <td>10022000</td>\n",
       "      <td>influenza</td>\n",
       "      <td>1543</td>\n",
       "      <td>1284</td>\n",
       "      <td>1432</td>\n",
       "      <td>0.115265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3097</th>\n",
       "      <td>10022437</td>\n",
       "      <td>insomnia</td>\n",
       "      <td>2065</td>\n",
       "      <td>1971</td>\n",
       "      <td>2238</td>\n",
       "      <td>0.135464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3200</th>\n",
       "      <td>10023232</td>\n",
       "      <td>joint swelling</td>\n",
       "      <td>1168</td>\n",
       "      <td>939</td>\n",
       "      <td>1003</td>\n",
       "      <td>0.068158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3569</th>\n",
       "      <td>10027175</td>\n",
       "      <td>memory impairment</td>\n",
       "      <td>1145</td>\n",
       "      <td>1152</td>\n",
       "      <td>1482</td>\n",
       "      <td>0.286458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3667</th>\n",
       "      <td>10027599</td>\n",
       "      <td>migraine</td>\n",
       "      <td>873</td>\n",
       "      <td>986</td>\n",
       "      <td>1085</td>\n",
       "      <td>0.100406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3900</th>\n",
       "      <td>10029331</td>\n",
       "      <td>neuropathy peripheral</td>\n",
       "      <td>1000</td>\n",
       "      <td>994</td>\n",
       "      <td>1817</td>\n",
       "      <td>0.827968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3974</th>\n",
       "      <td>10030124</td>\n",
       "      <td>oedema peripheral</td>\n",
       "      <td>871</td>\n",
       "      <td>815</td>\n",
       "      <td>1168</td>\n",
       "      <td>0.433129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4130</th>\n",
       "      <td>10033295</td>\n",
       "      <td>overdose</td>\n",
       "      <td>1076</td>\n",
       "      <td>1159</td>\n",
       "      <td>1175</td>\n",
       "      <td>0.013805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4140</th>\n",
       "      <td>10033371</td>\n",
       "      <td>pain</td>\n",
       "      <td>4859</td>\n",
       "      <td>4316</td>\n",
       "      <td>5169</td>\n",
       "      <td>0.197637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4141</th>\n",
       "      <td>10033425</td>\n",
       "      <td>pain in extremity</td>\n",
       "      <td>3449</td>\n",
       "      <td>2613</td>\n",
       "      <td>3014</td>\n",
       "      <td>0.153463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4150</th>\n",
       "      <td>10033557</td>\n",
       "      <td>palpitations</td>\n",
       "      <td>876</td>\n",
       "      <td>914</td>\n",
       "      <td>1073</td>\n",
       "      <td>0.173961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4190</th>\n",
       "      <td>10033775</td>\n",
       "      <td>paraesthesia</td>\n",
       "      <td>1235</td>\n",
       "      <td>1122</td>\n",
       "      <td>1176</td>\n",
       "      <td>0.048128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4401</th>\n",
       "      <td>10035528</td>\n",
       "      <td>platelet count decreased</td>\n",
       "      <td>959</td>\n",
       "      <td>1003</td>\n",
       "      <td>1065</td>\n",
       "      <td>0.061815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4425</th>\n",
       "      <td>10035664</td>\n",
       "      <td>pneumonia</td>\n",
       "      <td>3493</td>\n",
       "      <td>3708</td>\n",
       "      <td>4021</td>\n",
       "      <td>0.084412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4642</th>\n",
       "      <td>10037153</td>\n",
       "      <td>psoriasis</td>\n",
       "      <td>2073</td>\n",
       "      <td>2134</td>\n",
       "      <td>2328</td>\n",
       "      <td>0.090909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4735</th>\n",
       "      <td>10037660</td>\n",
       "      <td>pyrexia</td>\n",
       "      <td>2925</td>\n",
       "      <td>2762</td>\n",
       "      <td>3160</td>\n",
       "      <td>0.144098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4893</th>\n",
       "      <td>10038695</td>\n",
       "      <td>respiratory failure</td>\n",
       "      <td>907</td>\n",
       "      <td>871</td>\n",
       "      <td>1130</td>\n",
       "      <td>0.297359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5088</th>\n",
       "      <td>10039906</td>\n",
       "      <td>seizure</td>\n",
       "      <td>1281</td>\n",
       "      <td>1635</td>\n",
       "      <td>1684</td>\n",
       "      <td>0.029969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5110</th>\n",
       "      <td>10040047</td>\n",
       "      <td>sepsis</td>\n",
       "      <td>1159</td>\n",
       "      <td>1247</td>\n",
       "      <td>1388</td>\n",
       "      <td>0.113071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5163</th>\n",
       "      <td>10040753</td>\n",
       "      <td>sinusitis</td>\n",
       "      <td>1215</td>\n",
       "      <td>1048</td>\n",
       "      <td>1388</td>\n",
       "      <td>0.324427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5248</th>\n",
       "      <td>10041349</td>\n",
       "      <td>somnolence</td>\n",
       "      <td>1459</td>\n",
       "      <td>1580</td>\n",
       "      <td>1668</td>\n",
       "      <td>0.055696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5646</th>\n",
       "      <td>10044565</td>\n",
       "      <td>tremor</td>\n",
       "      <td>1459</td>\n",
       "      <td>1260</td>\n",
       "      <td>1439</td>\n",
       "      <td>0.142063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5777</th>\n",
       "      <td>10046571</td>\n",
       "      <td>urinary tract infection</td>\n",
       "      <td>1989</td>\n",
       "      <td>1735</td>\n",
       "      <td>2272</td>\n",
       "      <td>0.309510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5940</th>\n",
       "      <td>10047513</td>\n",
       "      <td>vision blurred</td>\n",
       "      <td>1018</td>\n",
       "      <td>1024</td>\n",
       "      <td>1144</td>\n",
       "      <td>0.117188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6018</th>\n",
       "      <td>10047895</td>\n",
       "      <td>weight decreased</td>\n",
       "      <td>2546</td>\n",
       "      <td>2374</td>\n",
       "      <td>2635</td>\n",
       "      <td>0.109941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6020</th>\n",
       "      <td>10047899</td>\n",
       "      <td>weight increased</td>\n",
       "      <td>1963</td>\n",
       "      <td>2075</td>\n",
       "      <td>2197</td>\n",
       "      <td>0.058795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6028</th>\n",
       "      <td>10047942</td>\n",
       "      <td>white blood cell count decreased</td>\n",
       "      <td>1355</td>\n",
       "      <td>1386</td>\n",
       "      <td>1606</td>\n",
       "      <td>0.158730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6237</th>\n",
       "      <td>10048959</td>\n",
       "      <td>peripheral swelling</td>\n",
       "      <td>2305</td>\n",
       "      <td>2136</td>\n",
       "      <td>2511</td>\n",
       "      <td>0.175562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7783</th>\n",
       "      <td>10053762</td>\n",
       "      <td>off label use</td>\n",
       "      <td>22918</td>\n",
       "      <td>8550</td>\n",
       "      <td>8844</td>\n",
       "      <td>0.034386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7893</th>\n",
       "      <td>10054112</td>\n",
       "      <td>hospitalisation</td>\n",
       "      <td>2093</td>\n",
       "      <td>2594</td>\n",
       "      <td>3198</td>\n",
       "      <td>0.232845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9699</th>\n",
       "      <td>10061428</td>\n",
       "      <td>decreased appetite</td>\n",
       "      <td>2283</td>\n",
       "      <td>2117</td>\n",
       "      <td>2274</td>\n",
       "      <td>0.074162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9826</th>\n",
       "      <td>10061623</td>\n",
       "      <td>adverse drug reaction</td>\n",
       "      <td>1948</td>\n",
       "      <td>1131</td>\n",
       "      <td>1290</td>\n",
       "      <td>0.140584</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10269</th>\n",
       "      <td>10062355</td>\n",
       "      <td>unevaluable event</td>\n",
       "      <td>902</td>\n",
       "      <td>930</td>\n",
       "      <td>1228</td>\n",
       "      <td>0.320430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10628</th>\n",
       "      <td>10063829</td>\n",
       "      <td>device malfunction</td>\n",
       "      <td>439</td>\n",
       "      <td>821</td>\n",
       "      <td>1739</td>\n",
       "      <td>1.118149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11387</th>\n",
       "      <td>10066377</td>\n",
       "      <td>therapy interrupted</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2054</td>\n",
       "      <td>2053.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11486</th>\n",
       "      <td>10066901</td>\n",
       "      <td>treatment failure</td>\n",
       "      <td>1189</td>\n",
       "      <td>930</td>\n",
       "      <td>4304</td>\n",
       "      <td>3.627957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12163</th>\n",
       "      <td>10069216</td>\n",
       "      <td>device adhesion issue</td>\n",
       "      <td>4</td>\n",
       "      <td>189</td>\n",
       "      <td>2039</td>\n",
       "      <td>9.788360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12823</th>\n",
       "      <td>10071408</td>\n",
       "      <td>maternal exposure during pregnancy</td>\n",
       "      <td>540</td>\n",
       "      <td>700</td>\n",
       "      <td>1042</td>\n",
       "      <td>0.488571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13149</th>\n",
       "      <td>10072878</td>\n",
       "      <td>device use error</td>\n",
       "      <td>23</td>\n",
       "      <td>266</td>\n",
       "      <td>2754</td>\n",
       "      <td>9.353383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13276</th>\n",
       "      <td>10073317</td>\n",
       "      <td>accidental exposure to product</td>\n",
       "      <td>2210</td>\n",
       "      <td>2236</td>\n",
       "      <td>2532</td>\n",
       "      <td>0.132379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13694</th>\n",
       "      <td>10074903</td>\n",
       "      <td>intentional product misuse</td>\n",
       "      <td>1569</td>\n",
       "      <td>806</td>\n",
       "      <td>1032</td>\n",
       "      <td>0.280397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14080</th>\n",
       "      <td>10076308</td>\n",
       "      <td>intentional product use issue</td>\n",
       "      <td>591</td>\n",
       "      <td>1186</td>\n",
       "      <td>2109</td>\n",
       "      <td>0.778246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14112</th>\n",
       "      <td>10076476</td>\n",
       "      <td>product use in unapproved indication</td>\n",
       "      <td>2307</td>\n",
       "      <td>2834</td>\n",
       "      <td>3816</td>\n",
       "      <td>0.346507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15156</th>\n",
       "      <td>10081572</td>\n",
       "      <td>inappropriate schedule of product administration</td>\n",
       "      <td>22</td>\n",
       "      <td>1772</td>\n",
       "      <td>1811</td>\n",
       "      <td>0.022009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15440</th>\n",
       "      <td>10084268</td>\n",
       "      <td>covid-19</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1674</td>\n",
       "      <td>inf</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             SE                                              name  2018_A  \\\n",
       "169    10001551                alanine aminotransferase increased     389   \n",
       "246    10002034                                           anaemia    1406   \n",
       "355    10002855                                           anxiety    1781   \n",
       "433    10003239                                        arthralgia    4331   \n",
       "470    10003549                                          asthenia    3164   \n",
       "534    10003988                                         back pain    3119   \n",
       "903    10005750                          blood pressure increased    1199   \n",
       "1123   10007515                                    cardiac arrest     667   \n",
       "1237   10008190                          cerebrovascular accident    1050   \n",
       "1273   10008469                                  chest discomfort     876   \n",
       "1274   10008479                                        chest pain    1765   \n",
       "1281   10008531                                            chills    1266   \n",
       "1454   10010144                                 completed suicide     847   \n",
       "1471   10010264                              condition aggravated    2382   \n",
       "1475   10010305                                 confusional state    1254   \n",
       "1544   10010774                                      constipation    2149   \n",
       "1601   10011224                                             cough    2636   \n",
       "1715   10011906                                             death    7781   \n",
       "1756   10012378                                        depression    1767   \n",
       "1878   10013654                                        drug abuse     391   \n",
       "1884   10013710                                  drug interaction    1038   \n",
       "1930   10013968                                          dyspnoea    5205   \n",
       "2244   10016173                                              fall    2851   \n",
       "2272   10016322                                  feeling abnormal    2328   \n",
       "2324   10016807                                   fluid retention     678   \n",
       "2327   10016825                                          flushing     841   \n",
       "2389   10017577                                  gait disturbance    1540   \n",
       "2466   10017955                      gastrointestinal haemorrhage     904   \n",
       "2609   10018884                             haemoglobin decreased     896   \n",
       "2640   10019063                                     hallucination     633   \n",
       "2872   10020772                                      hypertension    1517   \n",
       "2898   10020937                                     hypoaesthesia    1293   \n",
       "2944   10021097                                       hypotension    2596   \n",
       "3049   10022000                                         influenza    1543   \n",
       "3097   10022437                                          insomnia    2065   \n",
       "3200   10023232                                    joint swelling    1168   \n",
       "3569   10027175                                 memory impairment    1145   \n",
       "3667   10027599                                          migraine     873   \n",
       "3900   10029331                             neuropathy peripheral    1000   \n",
       "3974   10030124                                 oedema peripheral     871   \n",
       "4130   10033295                                          overdose    1076   \n",
       "4140   10033371                                              pain    4859   \n",
       "4141   10033425                                 pain in extremity    3449   \n",
       "4150   10033557                                      palpitations     876   \n",
       "4190   10033775                                      paraesthesia    1235   \n",
       "4401   10035528                          platelet count decreased     959   \n",
       "4425   10035664                                         pneumonia    3493   \n",
       "4642   10037153                                         psoriasis    2073   \n",
       "4735   10037660                                           pyrexia    2925   \n",
       "4893   10038695                               respiratory failure     907   \n",
       "5088   10039906                                           seizure    1281   \n",
       "5110   10040047                                            sepsis    1159   \n",
       "5163   10040753                                         sinusitis    1215   \n",
       "5248   10041349                                        somnolence    1459   \n",
       "5646   10044565                                            tremor    1459   \n",
       "5777   10046571                           urinary tract infection    1989   \n",
       "5940   10047513                                    vision blurred    1018   \n",
       "6018   10047895                                  weight decreased    2546   \n",
       "6020   10047899                                  weight increased    1963   \n",
       "6028   10047942                  white blood cell count decreased    1355   \n",
       "6237   10048959                               peripheral swelling    2305   \n",
       "7783   10053762                                     off label use   22918   \n",
       "7893   10054112                                   hospitalisation    2093   \n",
       "9699   10061428                                decreased appetite    2283   \n",
       "9826   10061623                             adverse drug reaction    1948   \n",
       "10269  10062355                                 unevaluable event     902   \n",
       "10628  10063829                                device malfunction     439   \n",
       "11387  10066377                               therapy interrupted       0   \n",
       "11486  10066901                                 treatment failure    1189   \n",
       "12163  10069216                             device adhesion issue       4   \n",
       "12823  10071408                maternal exposure during pregnancy     540   \n",
       "13149  10072878                                  device use error      23   \n",
       "13276  10073317                    accidental exposure to product    2210   \n",
       "13694  10074903                        intentional product misuse    1569   \n",
       "14080  10076308                     intentional product use issue     591   \n",
       "14112  10076476              product use in unapproved indication    2307   \n",
       "15156  10081572  inappropriate schedule of product administration      22   \n",
       "15440  10084268                                          covid-19       0   \n",
       "\n",
       "       2019_A  2020_A   2019_Delta  \n",
       "169       436    1222     1.802752  \n",
       "246      1482    1629     0.099190  \n",
       "355      1777    2113     0.189083  \n",
       "433      3266    3744     0.146356  \n",
       "470      2957    3074     0.039567  \n",
       "534      2121    2322     0.094767  \n",
       "903      1020    1095     0.073529  \n",
       "1123      725    1331     0.835862  \n",
       "1237     1098    1197     0.090164  \n",
       "1273      826    1005     0.216707  \n",
       "1274     1595    1790     0.122257  \n",
       "1281      931    1042     0.119227  \n",
       "1454     1250    1483     0.186400  \n",
       "1471     2607    3072     0.178366  \n",
       "1475     1229    1638     0.332791  \n",
       "1544     2341    2399     0.024776  \n",
       "1601     2451    2874     0.172583  \n",
       "1715     7744   10010     0.292614  \n",
       "1756     1767    1807     0.022637  \n",
       "1878      952    1185     0.244748  \n",
       "1884     1269    1339     0.055162  \n",
       "1930     5414    6130     0.132250  \n",
       "2244     2844    3669     0.290084  \n",
       "2272     2233    2371     0.061800  \n",
       "2324      826    1138     0.377724  \n",
       "2327      980    1094     0.116327  \n",
       "2389     1347    1758     0.305122  \n",
       "2466      914    1462     0.599562  \n",
       "2609      957    1241     0.296761  \n",
       "2640      699    1610     1.303290  \n",
       "2872     1358    1638     0.206186  \n",
       "2898     1270    1284     0.011024  \n",
       "2944     2286    2570     0.124234  \n",
       "3049     1284    1432     0.115265  \n",
       "3097     1971    2238     0.135464  \n",
       "3200      939    1003     0.068158  \n",
       "3569     1152    1482     0.286458  \n",
       "3667      986    1085     0.100406  \n",
       "3900      994    1817     0.827968  \n",
       "3974      815    1168     0.433129  \n",
       "4130     1159    1175     0.013805  \n",
       "4140     4316    5169     0.197637  \n",
       "4141     2613    3014     0.153463  \n",
       "4150      914    1073     0.173961  \n",
       "4190     1122    1176     0.048128  \n",
       "4401     1003    1065     0.061815  \n",
       "4425     3708    4021     0.084412  \n",
       "4642     2134    2328     0.090909  \n",
       "4735     2762    3160     0.144098  \n",
       "4893      871    1130     0.297359  \n",
       "5088     1635    1684     0.029969  \n",
       "5110     1247    1388     0.113071  \n",
       "5163     1048    1388     0.324427  \n",
       "5248     1580    1668     0.055696  \n",
       "5646     1260    1439     0.142063  \n",
       "5777     1735    2272     0.309510  \n",
       "5940     1024    1144     0.117188  \n",
       "6018     2374    2635     0.109941  \n",
       "6020     2075    2197     0.058795  \n",
       "6028     1386    1606     0.158730  \n",
       "6237     2136    2511     0.175562  \n",
       "7783     8550    8844     0.034386  \n",
       "7893     2594    3198     0.232845  \n",
       "9699     2117    2274     0.074162  \n",
       "9826     1131    1290     0.140584  \n",
       "10269     930    1228     0.320430  \n",
       "10628     821    1739     1.118149  \n",
       "11387       1    2054  2053.000000  \n",
       "11486     930    4304     3.627957  \n",
       "12163     189    2039     9.788360  \n",
       "12823     700    1042     0.488571  \n",
       "13149     266    2754     9.353383  \n",
       "13276    2236    2532     0.132379  \n",
       "13694     806    1032     0.280397  \n",
       "14080    1186    2109     0.778246  \n",
       "14112    2834    3816     0.346507  \n",
       "15156    1772    1811     0.022009  \n",
       "15440       0    1674          inf  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = uncondition_2019_history[uncondition_2019_history['2019_Delta']>0]\n",
    "print(x.shape)\n",
    "y = x[x['2020_A']>1000]\n",
    "print(y.shape)\n",
    "\n",
    "y[['SE', 'name','2018_A', '2019_A', '2020_A', '2019_Delta']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>SE</th>\n",
       "      <th>name</th>\n",
       "      <th>2013_A</th>\n",
       "      <th>2013_B</th>\n",
       "      <th>2014_A</th>\n",
       "      <th>2014_B</th>\n",
       "      <th>2015_A</th>\n",
       "      <th>2015_B</th>\n",
       "      <th>2016_A</th>\n",
       "      <th>2016_B</th>\n",
       "      <th>2017_A</th>\n",
       "      <th>2017_B</th>\n",
       "      <th>2018_A</th>\n",
       "      <th>2018_B</th>\n",
       "      <th>2019_A</th>\n",
       "      <th>2019_B</th>\n",
       "      <th>2020_A</th>\n",
       "      <th>2020_B</th>\n",
       "      <th>2013_ROR</th>\n",
       "      <th>2014_ROR</th>\n",
       "      <th>2015_ROR</th>\n",
       "      <th>2016_ROR</th>\n",
       "      <th>2017_ROR</th>\n",
       "      <th>2018_ROR</th>\n",
       "      <th>2019_ROR</th>\n",
       "      <th>2013_Delta</th>\n",
       "      <th>2014_Delta</th>\n",
       "      <th>2015_Delta</th>\n",
       "      <th>2016_Delta</th>\n",
       "      <th>2017_Delta</th>\n",
       "      <th>2018_Delta</th>\n",
       "      <th>2019_Delta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1601</th>\n",
       "      <td>10011224</td>\n",
       "      <td>cough</td>\n",
       "      <td>1113</td>\n",
       "      <td>88221</td>\n",
       "      <td>1269</td>\n",
       "      <td>96535</td>\n",
       "      <td>2474</td>\n",
       "      <td>176909</td>\n",
       "      <td>2140</td>\n",
       "      <td>167093</td>\n",
       "      <td>2503</td>\n",
       "      <td>208233</td>\n",
       "      <td>2636</td>\n",
       "      <td>241369</td>\n",
       "      <td>2451</td>\n",
       "      <td>218469</td>\n",
       "      <td>2874</td>\n",
       "      <td>208278</td>\n",
       "      <td>1.093755</td>\n",
       "      <td>1.049703</td>\n",
       "      <td>0.986719</td>\n",
       "      <td>1.077427</td>\n",
       "      <td>1.147974</td>\n",
       "      <td>1.263512</td>\n",
       "      <td>1.229957</td>\n",
       "      <td>1.58221</td>\n",
       "      <td>1.264775</td>\n",
       "      <td>0.161681</td>\n",
       "      <td>0.342991</td>\n",
       "      <td>0.148222</td>\n",
       "      <td>0.090288</td>\n",
       "      <td>0.172583</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            SE   name  2013_A  2013_B  2014_A  2014_B  2015_A  2015_B  2016_A  \\\n",
       "1601  10011224  cough    1113   88221    1269   96535    2474  176909    2140   \n",
       "\n",
       "      2016_B  2017_A  2017_B  2018_A  2018_B  2019_A  2019_B  2020_A  2020_B  \\\n",
       "1601  167093    2503  208233    2636  241369    2451  218469    2874  208278   \n",
       "\n",
       "      2013_ROR  2014_ROR  2015_ROR  2016_ROR  2017_ROR  2018_ROR  2019_ROR  \\\n",
       "1601  1.093755  1.049703  0.986719  1.077427  1.147974  1.263512  1.229957   \n",
       "\n",
       "      2013_Delta  2014_Delta  2015_Delta  2016_Delta  2017_Delta  2018_Delta  \\\n",
       "1601     1.58221    1.264775    0.161681    0.342991    0.148222    0.090288   \n",
       "\n",
       "      2019_Delta  \n",
       "1601    0.172583  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "uncondition_2019_history[uncondition_2019_history.name=='cough']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Population distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2551071"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_pd_US_pro = pickle.load(open('../Data/pandemic/all_pd_US_pro.pk', 'rb'))  # update the dataframe with ROR and Delta\n",
    "len(all_pd_US_pro)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "the #-reports during March 11-Sept 30, accmulated from 2013-2020 1425371\n",
      "#-male reports 484649 0.3400160379297741\n",
      "#-female reports 784230 0.5501935987192107\n",
      "unknown sex:  156492 0.10979036335101527\n",
      "#-young reports 35987 0.025247461888869633\n",
      "#-adult reports 508983 0.357088084435561\n",
      "#-elderly reports 305685 0.21445995463637185\n",
      "unknown age 574716 0.40320449903919753\n"
     ]
    }
   ],
   "source": [
    "st = '-03-10'\n",
    "end = '-09-31'\n",
    "ind = [st<i[4:]<end for i in all_pd_US_pro['receipt_date']]\n",
    "all_pd_US_pro_period= all_pd_US_pro[ind]\n",
    "n_all = len(all_pd_US_pro_period)\n",
    "print('the #-reports during March 11-Sept 30, accmulated from 2013-2020', n_all)\n",
    "\n",
    "n_male = len(all_pd_US_pro_period[all_pd_US_pro_period.gender=='1'])\n",
    "n_female = len(all_pd_US_pro_period[all_pd_US_pro_period.gender=='2'])\n",
    "\n",
    "in_young = [str(0)<str(i)<str(20) for i in all_pd_US_pro_period.age]\n",
    "\n",
    "in_adult = [str(19)<str(i)<str(65) for i in all_pd_US_pro_period.age]\n",
    "\n",
    "in_elderly = [str(64)<str(i) for i in all_pd_US_pro_period.age]\n",
    "n_young = len(all_pd_US_pro_period[in_young])\n",
    "n_adult = len(all_pd_US_pro_period[in_adult])\n",
    "n_elderly = len(all_pd_US_pro_period[in_elderly])\n",
    "\n",
    "# unknown sex: \n",
    "n_unknownsex = n_all-n_male-n_female\n",
    "n_unknownage = n_all - n_young -n_adult-n_elderly\n",
    "print('#-male reports',n_male, n_male/n_all)\n",
    "print('#-female reports',n_female, n_female/n_all)\n",
    "print('unknown sex: ', n_unknownsex, n_unknownsex/n_all)\n",
    "\n",
    "# unknown age\n",
    "print('#-young reports', n_young, n_young/n_all)\n",
    "\n",
    "print('#-adult reports', n_adult, n_adult/n_all)\n",
    "\n",
    "print('#-elderly reports',n_elderly, n_elderly/n_all)\n",
    "print('unknown age', n_unknownage, n_unknownage/n_all)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13.988801511656987 9.009386593710554\n",
      "47.78402422084824 13.198427571170923\n",
      "72.98214501856486 11.0588864576358\n"
     ]
    }
   ],
   "source": [
    "## mean and std average\n",
    "young_age = np.array(list(all_pd_US_pro_period[in_young].age))\n",
    "print(young_age.mean(), young_age.std())\n",
    "\n",
    "adult_age = np.array(list(all_pd_US_pro_period[in_adult].age))\n",
    "print(adult_age.mean(), adult_age.std())\n",
    "\n",
    "elderly_age = np.array(list(all_pd_US_pro_period[in_elderly].age))\n",
    "print(elderly_age.mean(), elderly_age.std())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "#-of all reports (9325731, 23)\n",
      "Focus on US, reports # (6351817, 23)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "5    2045491\n",
       "1     594787\n",
       "3     548261\n",
       "2     282323\n",
       "6     124447\n",
       "4     113744\n",
       "0        478\n",
       "Name: qualify, dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# \"1= Physician\n",
    "# 2= Pharmacist\n",
    "# 3= Other Health Professional\n",
    "# 4= Lawyer\n",
    "# 5= Consumer\"\n",
    "\n",
    "## Qualification/reporter distribution\n",
    "all_pd = pickle.load(open('../Data/curated/patient_safety.pk', 'rb'))  \n",
    "print('#-of all reports',all_pd.shape)\n",
    "\n",
    "all_pd_US = all_pd[all_pd.country=='US']\n",
    "print('Focus on US, reports #', all_pd_US.shape)\n",
    "\n",
    "st = '-03-10'\n",
    "end = '-09-31'\n",
    "ind = [st<i[4:]<end for i in all_pd_US['receipt_date']]\n",
    "all_pd_US_period= all_pd_US[ind]\n",
    "\n",
    "all_pd_US_period['qualify'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conditioned on Gender"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Male"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2551071\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "  0%|          | 27/12439 [00:00<00:47, 262.17it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2013:30468\n",
      "2013 year has 30468 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 12439/12439 [00:43<00:00, 285.09it/s]\n",
      "  0%|          | 24/12439 [00:00<00:52, 238.62it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2014:33824\n",
      "2014 year has 33824 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 12439/12439 [00:48<00:00, 254.18it/s]\n",
      "  0%|          | 13/12439 [00:00<01:41, 121.82it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2015:63073\n",
      "2015 year has 63073 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 12439/12439 [01:42<00:00, 121.06it/s]\n",
      "  0%|          | 13/12439 [00:00<01:36, 128.45it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2016:58998\n",
      "2016 year has 58998 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 12439/12439 [01:37<00:00, 128.22it/s]\n",
      "  0%|          | 10/12439 [00:00<02:05, 98.80it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2017:71237\n",
      "2017 year has 71237 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 12439/12439 [02:00<00:00, 103.45it/s]\n",
      "  0%|          | 10/12439 [00:00<02:13, 93.29it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2018:79623\n",
      "2018 year has 79623 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 12439/12439 [02:14<00:00, 92.35it/s]\n",
      "  0%|          | 10/12439 [00:00<02:07, 97.17it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2019:73675\n",
      "2019 year has 73675 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 12439/12439 [02:06<00:00, 98.48it/s] \n",
      "  0%|          | 11/12439 [00:00<02:03, 100.80it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2020:72733\n",
      "2020 year has 72733 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 12439/12439 [02:05<00:00, 99.03it/s] \n"
     ]
    }
   ],
   "source": [
    "all_pd_US_pro = pickle.load(open('../Data/pandemic/all_pd_US_pro.pk', 'rb'))  # update the dataframe with ROR and Delta\n",
    "print(len(all_pd_US_pro))\n",
    "\n",
    "all_pd_US_pro_male = all_pd_US_pro[all_pd_US_pro.gender=='1']\n",
    "\n",
    "DF = all_pd_US_pro_male\n",
    "# reports in 2020 \n",
    "\"\"\"initialize the Data frame \"\"\"\n",
    "# SE_list = list(SE_dic_df.code)\n",
    "# male_matrix = pd.DataFrame({'SE': SE_list, 'name':list(SE_dic_df.index), 'medra_ID': list(SE_dic_df['medra_ID'])})\n",
    "\n",
    "SE_list = list(sorted(set(list(itertools.chain(*DF.SE)))))\n",
    "ind = [i in SE_list for i in meddra_pd_all.PT]\n",
    "whatever_ = meddra_pd_all[ind]\n",
    "male_matrix = pd.DataFrame({'SE': list(whatever_.PT), 'name':list(whatever_['PT_name']) }) \n",
    "\n",
    "\n",
    "yr_list = [2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020]\n",
    "for yr in yr_list:\n",
    "    st = str(yr)+'-03-10'\n",
    "    end = str(yr)+'-09-31'\n",
    "    ind = [st<i<end for i in DF['receipt_date']]\n",
    "    locals()['all_pd_US_pro_'+str(yr)]= DF[ind]\n",
    "    print('rows in {}:{}'.format(yr,len(locals()['all_pd_US_pro_'+str(yr)])))\n",
    "\n",
    "    n_report = len(locals()['all_pd_US_pro_'+str(yr)])\n",
    "    print('{} year has {} reports'.format(yr, n_report))\n",
    "    A =[]\n",
    "    for se in tqdm(SE_list):\n",
    "        name = locals()['all_pd_US_pro_'+str(yr)]\n",
    "        indx = [se in j for j in name.SE]\n",
    "        n_A = sum(indx)\n",
    "        A.append(n_A)\n",
    "    B = [n_report - i for i in A]\n",
    "    male_matrix[str(yr)+'_A'] = A\n",
    "    male_matrix[str(yr)+'_B'] = B"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>SE</th>\n",
       "      <th>name</th>\n",
       "      <th>2013_A</th>\n",
       "      <th>2013_B</th>\n",
       "      <th>2014_A</th>\n",
       "      <th>2014_B</th>\n",
       "      <th>2015_A</th>\n",
       "      <th>2015_B</th>\n",
       "      <th>2016_A</th>\n",
       "      <th>2016_B</th>\n",
       "      <th>2017_A</th>\n",
       "      <th>2017_B</th>\n",
       "      <th>2018_A</th>\n",
       "      <th>2018_B</th>\n",
       "      <th>2019_A</th>\n",
       "      <th>2019_B</th>\n",
       "      <th>2020_A</th>\n",
       "      <th>2020_B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000029</td>\n",
       "      <td>5-alpha-reductase deficiency</td>\n",
       "      <td>0</td>\n",
       "      <td>30468</td>\n",
       "      <td>0</td>\n",
       "      <td>33824</td>\n",
       "      <td>0</td>\n",
       "      <td>63073</td>\n",
       "      <td>0</td>\n",
       "      <td>58998</td>\n",
       "      <td>0</td>\n",
       "      <td>71237</td>\n",
       "      <td>0</td>\n",
       "      <td>79623</td>\n",
       "      <td>0</td>\n",
       "      <td>73675</td>\n",
       "      <td>1</td>\n",
       "      <td>72732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000044</td>\n",
       "      <td>abdomen crushing</td>\n",
       "      <td>0</td>\n",
       "      <td>30468</td>\n",
       "      <td>0</td>\n",
       "      <td>33824</td>\n",
       "      <td>0</td>\n",
       "      <td>63073</td>\n",
       "      <td>0</td>\n",
       "      <td>58998</td>\n",
       "      <td>0</td>\n",
       "      <td>71237</td>\n",
       "      <td>0</td>\n",
       "      <td>79623</td>\n",
       "      <td>0</td>\n",
       "      <td>73675</td>\n",
       "      <td>1</td>\n",
       "      <td>72732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000050</td>\n",
       "      <td>abdominal adhesions</td>\n",
       "      <td>5</td>\n",
       "      <td>30463</td>\n",
       "      <td>1</td>\n",
       "      <td>33823</td>\n",
       "      <td>7</td>\n",
       "      <td>63066</td>\n",
       "      <td>3</td>\n",
       "      <td>58995</td>\n",
       "      <td>9</td>\n",
       "      <td>71228</td>\n",
       "      <td>4</td>\n",
       "      <td>79619</td>\n",
       "      <td>4</td>\n",
       "      <td>73671</td>\n",
       "      <td>7</td>\n",
       "      <td>72726</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         SE                          name  2013_A  2013_B  2014_A  2014_B  \\\n",
       "0  10000029  5-alpha-reductase deficiency       0   30468       0   33824   \n",
       "1  10000044              abdomen crushing       0   30468       0   33824   \n",
       "2  10000050           abdominal adhesions       5   30463       1   33823   \n",
       "\n",
       "   2015_A  2015_B  2016_A  2016_B  2017_A  2017_B  2018_A  2018_B  2019_A  \\\n",
       "0       0   63073       0   58998       0   71237       0   79623       0   \n",
       "1       0   63073       0   58998       0   71237       0   79623       0   \n",
       "2       7   63066       3   58995       9   71228       4   79619       4   \n",
       "\n",
       "   2019_B  2020_A  2020_B  \n",
       "0   73675       1   72732  \n",
       "1   73675       1   72732  \n",
       "2   73671       7   72726  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "male_matrix.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "pickle.dump(male_matrix, open('../Data/pandemic/SE_male_raw.pk', 'wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>SE</th>\n",
       "      <th>name</th>\n",
       "      <th>2013_A</th>\n",
       "      <th>2013_B</th>\n",
       "      <th>2014_A</th>\n",
       "      <th>2014_B</th>\n",
       "      <th>2015_A</th>\n",
       "      <th>2015_B</th>\n",
       "      <th>2016_A</th>\n",
       "      <th>2016_B</th>\n",
       "      <th>2017_A</th>\n",
       "      <th>2017_B</th>\n",
       "      <th>2018_A</th>\n",
       "      <th>2018_B</th>\n",
       "      <th>2019_A</th>\n",
       "      <th>2019_B</th>\n",
       "      <th>2020_A</th>\n",
       "      <th>2020_B</th>\n",
       "      <th>2013_ROR</th>\n",
       "      <th>2014_ROR</th>\n",
       "      <th>2015_ROR</th>\n",
       "      <th>2016_ROR</th>\n",
       "      <th>2017_ROR</th>\n",
       "      <th>2018_ROR</th>\n",
       "      <th>2019_ROR</th>\n",
       "      <th>2013_Delta</th>\n",
       "      <th>2014_Delta</th>\n",
       "      <th>2015_Delta</th>\n",
       "      <th>2016_Delta</th>\n",
       "      <th>2017_Delta</th>\n",
       "      <th>2018_Delta</th>\n",
       "      <th>2019_Delta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000029</td>\n",
       "      <td>5-alpha-reductase deficiency</td>\n",
       "      <td>0</td>\n",
       "      <td>30468</td>\n",
       "      <td>0</td>\n",
       "      <td>33824</td>\n",
       "      <td>0</td>\n",
       "      <td>63073</td>\n",
       "      <td>0</td>\n",
       "      <td>58998</td>\n",
       "      <td>0</td>\n",
       "      <td>71237</td>\n",
       "      <td>0</td>\n",
       "      <td>79623</td>\n",
       "      <td>0</td>\n",
       "      <td>73675</td>\n",
       "      <td>1</td>\n",
       "      <td>72732</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000044</td>\n",
       "      <td>abdomen crushing</td>\n",
       "      <td>0</td>\n",
       "      <td>30468</td>\n",
       "      <td>0</td>\n",
       "      <td>33824</td>\n",
       "      <td>0</td>\n",
       "      <td>63073</td>\n",
       "      <td>0</td>\n",
       "      <td>58998</td>\n",
       "      <td>0</td>\n",
       "      <td>71237</td>\n",
       "      <td>0</td>\n",
       "      <td>79623</td>\n",
       "      <td>0</td>\n",
       "      <td>73675</td>\n",
       "      <td>1</td>\n",
       "      <td>72732</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "      <td>inf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000050</td>\n",
       "      <td>abdominal adhesions</td>\n",
       "      <td>5</td>\n",
       "      <td>30463</td>\n",
       "      <td>1</td>\n",
       "      <td>33823</td>\n",
       "      <td>7</td>\n",
       "      <td>63066</td>\n",
       "      <td>3</td>\n",
       "      <td>58995</td>\n",
       "      <td>9</td>\n",
       "      <td>71228</td>\n",
       "      <td>4</td>\n",
       "      <td>79619</td>\n",
       "      <td>4</td>\n",
       "      <td>73671</td>\n",
       "      <td>7</td>\n",
       "      <td>72726</td>\n",
       "      <td>0.586423</td>\n",
       "      <td>3.255521</td>\n",
       "      <td>0.867173</td>\n",
       "      <td>1.892789</td>\n",
       "      <td>0.761757</td>\n",
       "      <td>1.915866</td>\n",
       "      <td>1.772739</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.333333</td>\n",
       "      <td>-0.222222</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>0.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000059</td>\n",
       "      <td>abdominal discomfort</td>\n",
       "      <td>188</td>\n",
       "      <td>30280</td>\n",
       "      <td>176</td>\n",
       "      <td>33648</td>\n",
       "      <td>444</td>\n",
       "      <td>62629</td>\n",
       "      <td>482</td>\n",
       "      <td>58516</td>\n",
       "      <td>653</td>\n",
       "      <td>70584</td>\n",
       "      <td>806</td>\n",
       "      <td>78817</td>\n",
       "      <td>674</td>\n",
       "      <td>73001</td>\n",
       "      <td>582</td>\n",
       "      <td>72151</td>\n",
       "      <td>1.299208</td>\n",
       "      <td>1.542152</td>\n",
       "      <td>1.137819</td>\n",
       "      <td>0.979283</td>\n",
       "      <td>0.871914</td>\n",
       "      <td>0.788797</td>\n",
       "      <td>0.873674</td>\n",
       "      <td>2.095745</td>\n",
       "      <td>2.306818</td>\n",
       "      <td>0.310811</td>\n",
       "      <td>0.207469</td>\n",
       "      <td>-0.108729</td>\n",
       "      <td>-0.277916</td>\n",
       "      <td>-0.136499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000060</td>\n",
       "      <td>abdominal distension</td>\n",
       "      <td>120</td>\n",
       "      <td>30348</td>\n",
       "      <td>150</td>\n",
       "      <td>33674</td>\n",
       "      <td>234</td>\n",
       "      <td>62839</td>\n",
       "      <td>191</td>\n",
       "      <td>58807</td>\n",
       "      <td>280</td>\n",
       "      <td>70957</td>\n",
       "      <td>393</td>\n",
       "      <td>79230</td>\n",
       "      <td>304</td>\n",
       "      <td>73371</td>\n",
       "      <td>265</td>\n",
       "      <td>72468</td>\n",
       "      <td>0.924801</td>\n",
       "      <td>0.820924</td>\n",
       "      <td>0.982003</td>\n",
       "      <td>1.125888</td>\n",
       "      <td>0.926695</td>\n",
       "      <td>0.737219</td>\n",
       "      <td>0.882573</td>\n",
       "      <td>1.208333</td>\n",
       "      <td>0.766667</td>\n",
       "      <td>0.132479</td>\n",
       "      <td>0.387435</td>\n",
       "      <td>-0.053571</td>\n",
       "      <td>-0.325700</td>\n",
       "      <td>-0.128289</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         SE                          name  2013_A  2013_B  2014_A  2014_B  \\\n",
       "0  10000029  5-alpha-reductase deficiency       0   30468       0   33824   \n",
       "1  10000044              abdomen crushing       0   30468       0   33824   \n",
       "2  10000050           abdominal adhesions       5   30463       1   33823   \n",
       "3  10000059          abdominal discomfort     188   30280     176   33648   \n",
       "4  10000060          abdominal distension     120   30348     150   33674   \n",
       "\n",
       "   2015_A  2015_B  2016_A  2016_B  2017_A  2017_B  2018_A  2018_B  2019_A  \\\n",
       "0       0   63073       0   58998       0   71237       0   79623       0   \n",
       "1       0   63073       0   58998       0   71237       0   79623       0   \n",
       "2       7   63066       3   58995       9   71228       4   79619       4   \n",
       "3     444   62629     482   58516     653   70584     806   78817     674   \n",
       "4     234   62839     191   58807     280   70957     393   79230     304   \n",
       "\n",
       "   2019_B  2020_A  2020_B  2013_ROR  2014_ROR  2015_ROR  2016_ROR  2017_ROR  \\\n",
       "0   73675       1   72732       inf       inf       inf       inf       inf   \n",
       "1   73675       1   72732       inf       inf       inf       inf       inf   \n",
       "2   73671       7   72726  0.586423  3.255521  0.867173  1.892789  0.761757   \n",
       "3   73001     582   72151  1.299208  1.542152  1.137819  0.979283  0.871914   \n",
       "4   73371     265   72468  0.924801  0.820924  0.982003  1.125888  0.926695   \n",
       "\n",
       "   2018_ROR  2019_ROR  2013_Delta  2014_Delta  2015_Delta  2016_Delta  \\\n",
       "0       inf       inf         inf         inf         inf         inf   \n",
       "1       inf       inf         inf         inf         inf         inf   \n",
       "2  1.915866  1.772739    0.400000    6.000000    0.000000    1.333333   \n",
       "3  0.788797  0.873674    2.095745    2.306818    0.310811    0.207469   \n",
       "4  0.737219  0.882573    1.208333    0.766667    0.132479    0.387435   \n",
       "\n",
       "   2017_Delta  2018_Delta  2019_Delta  \n",
       "0         inf         inf         inf  \n",
       "1         inf         inf         inf  \n",
       "2   -0.222222    0.750000    0.750000  \n",
       "3   -0.108729   -0.277916   -0.136499  \n",
       "4   -0.053571   -0.325700   -0.128289  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "para_male = male_matrix\n",
    "yr_list = [2013, 2014, 2015, 2016, 2017, 2018, 2019]\n",
    "for yr in yr_list:  # calculate ROR  \n",
    "    para_male[str(yr)+'_ROR'] = (para_male['2020_A']*para_male[str(yr)+'_B'])/(para_male['2020_B']*para_male[str(yr)+'_A'])\n",
    "\n",
    "for yr in yr_list:  # calculate Delta: average difference  \n",
    "    para_male[str(yr)+'_Delta'] = (para_male['2020_A'] - para_male[str(yr)+'_A'])/para_male[str(yr)+'_A']\n",
    "\n",
    "pd.set_option('display.max_columns', None)\n",
    "\"\"\"Note: 0/0 = NaN, 1/0 = inf\"\"\"\n",
    "para_male.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "pickle.dump(para_male, open('../Data/pandemic/SE_male.pk', 'wb'))  # update the dataframe with ROR and Delta"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Female"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "  0%|          | 15/13433 [00:00<01:31, 147.32it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2013:50468\n",
      "2013 year has 50468 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 13433/13433 [01:23<00:00, 161.12it/s]\n",
      "  0%|          | 15/13433 [00:00<01:33, 143.87it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2014:55218\n",
      "2014 year has 55218 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 13433/13433 [01:31<00:00, 146.45it/s]\n",
      "  0%|          | 8/13433 [00:00<02:49, 79.31it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2015:97402\n",
      "2015 year has 97402 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 13433/13433 [02:42<00:00, 82.88it/s]\n",
      "  0%|          | 9/13433 [00:00<02:31, 88.65it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2016:91947\n",
      "2016 year has 91947 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 13433/13433 [02:32<00:00, 88.05it/s]\n",
      "  0%|          | 8/13433 [00:00<03:07, 71.58it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2017:114661\n",
      "2017 year has 114661 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 13433/13433 [03:09<00:00, 70.95it/s]\n",
      "  0%|          | 6/13433 [00:00<03:45, 59.52it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2018:141138\n",
      "2018 year has 141138 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 13433/13433 [03:41<00:00, 60.52it/s]\n",
      "  0%|          | 7/13433 [00:00<03:18, 67.61it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2019:117965\n",
      "2019 year has 117965 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 13433/13433 [03:15<00:00, 68.76it/s]\n",
      "  0%|          | 7/13433 [00:00<03:26, 65.08it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2020:113907\n",
      "2020 year has 113907 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 13433/13433 [03:14<00:00, 69.01it/s]\n"
     ]
    }
   ],
   "source": [
    "all_pd_US_pro_female = all_pd_US_pro[all_pd_US_pro.gender=='2']\n",
    "\n",
    "DF = all_pd_US_pro_female\n",
    "# reports in 2020 \n",
    "\"\"\"initialize the Data frame \"\"\"\n",
    "# SE_list = list(SE_dic_df.code)\n",
    "# female_matrix = pd.DataFrame({'SE': SE_list, 'name':list(SE_dic_df.index), 'medra_ID': list(SE_dic_df['medra_ID'])})\n",
    "SE_list = list(sorted(set(list(itertools.chain(*DF.SE)))))\n",
    "ind = [i in SE_list for i in meddra_pd_all.PT]\n",
    "whatever_ = meddra_pd_all[ind]\n",
    "female_matrix = pd.DataFrame({'SE': list(whatever_.PT), 'name':list(whatever_['PT_name']) }) \n",
    "\n",
    "yr_list = [2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020]\n",
    "for yr in yr_list:\n",
    "    st = str(yr)+'-03-10'\n",
    "    end = str(yr)+'-09-31'\n",
    "    ind = [st<i<end for i in DF['receipt_date']]\n",
    "    locals()['all_pd_US_pro_'+str(yr)]= DF[ind]\n",
    "    print('rows in {}:{}'.format(yr,len(locals()['all_pd_US_pro_'+str(yr)])))\n",
    "\n",
    "    n_report = len(locals()['all_pd_US_pro_'+str(yr)])\n",
    "    print('{} year has {} reports'.format(yr, n_report))\n",
    "    A =[]\n",
    "    for se in tqdm(SE_list):\n",
    "        name = locals()['all_pd_US_pro_'+str(yr)]\n",
    "        indx = [se in j for j in name.SE]\n",
    "        n_A = sum(indx)\n",
    "        A.append(n_A)\n",
    "    B = [n_report - i for i in A]\n",
    "    female_matrix[str(yr)+'_A'] = A\n",
    "    female_matrix[str(yr)+'_B'] = B"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "pickle.dump(female_matrix, open('../Data/pandemic/SE_female_raw.pk', 'wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>SE</th>\n",
       "      <th>name</th>\n",
       "      <th>2013_A</th>\n",
       "      <th>2013_B</th>\n",
       "      <th>2014_A</th>\n",
       "      <th>2014_B</th>\n",
       "      <th>2015_A</th>\n",
       "      <th>2015_B</th>\n",
       "      <th>2016_A</th>\n",
       "      <th>2016_B</th>\n",
       "      <th>2017_A</th>\n",
       "      <th>2017_B</th>\n",
       "      <th>2018_A</th>\n",
       "      <th>2018_B</th>\n",
       "      <th>2019_A</th>\n",
       "      <th>2019_B</th>\n",
       "      <th>2020_A</th>\n",
       "      <th>2020_B</th>\n",
       "      <th>2013_ROR</th>\n",
       "      <th>2014_ROR</th>\n",
       "      <th>2015_ROR</th>\n",
       "      <th>2016_ROR</th>\n",
       "      <th>2017_ROR</th>\n",
       "      <th>2018_ROR</th>\n",
       "      <th>2019_ROR</th>\n",
       "      <th>2013_Delta</th>\n",
       "      <th>2014_Delta</th>\n",
       "      <th>2015_Delta</th>\n",
       "      <th>2016_Delta</th>\n",
       "      <th>2017_Delta</th>\n",
       "      <th>2018_Delta</th>\n",
       "      <th>2019_Delta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000044</td>\n",
       "      <td>abdomen crushing</td>\n",
       "      <td>0</td>\n",
       "      <td>50468</td>\n",
       "      <td>0</td>\n",
       "      <td>55218</td>\n",
       "      <td>0</td>\n",
       "      <td>97402</td>\n",
       "      <td>0</td>\n",
       "      <td>91947</td>\n",
       "      <td>0</td>\n",
       "      <td>114661</td>\n",
       "      <td>0</td>\n",
       "      <td>141138</td>\n",
       "      <td>0</td>\n",
       "      <td>117965</td>\n",
       "      <td>0</td>\n",
       "      <td>113907</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000050</td>\n",
       "      <td>abdominal adhesions</td>\n",
       "      <td>9</td>\n",
       "      <td>50459</td>\n",
       "      <td>13</td>\n",
       "      <td>55205</td>\n",
       "      <td>9</td>\n",
       "      <td>97393</td>\n",
       "      <td>3</td>\n",
       "      <td>91944</td>\n",
       "      <td>12</td>\n",
       "      <td>114649</td>\n",
       "      <td>10</td>\n",
       "      <td>141128</td>\n",
       "      <td>10</td>\n",
       "      <td>117955</td>\n",
       "      <td>5</td>\n",
       "      <td>113902</td>\n",
       "      <td>0.246113</td>\n",
       "      <td>0.186412</td>\n",
       "      <td>0.475033</td>\n",
       "      <td>1.345367</td>\n",
       "      <td>0.419399</td>\n",
       "      <td>0.619515</td>\n",
       "      <td>0.517792</td>\n",
       "      <td>-0.444444</td>\n",
       "      <td>-0.615385</td>\n",
       "      <td>-0.444444</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>-0.583333</td>\n",
       "      <td>-0.500000</td>\n",
       "      <td>-0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000059</td>\n",
       "      <td>abdominal discomfort</td>\n",
       "      <td>344</td>\n",
       "      <td>50124</td>\n",
       "      <td>410</td>\n",
       "      <td>54808</td>\n",
       "      <td>881</td>\n",
       "      <td>96521</td>\n",
       "      <td>891</td>\n",
       "      <td>91056</td>\n",
       "      <td>1226</td>\n",
       "      <td>113435</td>\n",
       "      <td>1469</td>\n",
       "      <td>139669</td>\n",
       "      <td>1298</td>\n",
       "      <td>116667</td>\n",
       "      <td>1272</td>\n",
       "      <td>112635</td>\n",
       "      <td>1.645512</td>\n",
       "      <td>1.509642</td>\n",
       "      <td>1.237256</td>\n",
       "      <td>1.154103</td>\n",
       "      <td>1.044889</td>\n",
       "      <td>1.073722</td>\n",
       "      <td>1.015049</td>\n",
       "      <td>2.697674</td>\n",
       "      <td>2.102439</td>\n",
       "      <td>0.443814</td>\n",
       "      <td>0.427609</td>\n",
       "      <td>0.037520</td>\n",
       "      <td>-0.134105</td>\n",
       "      <td>-0.020031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000060</td>\n",
       "      <td>abdominal distension</td>\n",
       "      <td>237</td>\n",
       "      <td>50231</td>\n",
       "      <td>262</td>\n",
       "      <td>54956</td>\n",
       "      <td>500</td>\n",
       "      <td>96902</td>\n",
       "      <td>391</td>\n",
       "      <td>91556</td>\n",
       "      <td>562</td>\n",
       "      <td>114099</td>\n",
       "      <td>618</td>\n",
       "      <td>140520</td>\n",
       "      <td>564</td>\n",
       "      <td>117401</td>\n",
       "      <td>593</td>\n",
       "      <td>113314</td>\n",
       "      <td>1.109161</td>\n",
       "      <td>1.097703</td>\n",
       "      <td>1.014224</td>\n",
       "      <td>1.225409</td>\n",
       "      <td>1.062470</td>\n",
       "      <td>1.189928</td>\n",
       "      <td>1.089341</td>\n",
       "      <td>1.502110</td>\n",
       "      <td>1.263359</td>\n",
       "      <td>0.186000</td>\n",
       "      <td>0.516624</td>\n",
       "      <td>0.055160</td>\n",
       "      <td>-0.040453</td>\n",
       "      <td>0.051418</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000077</td>\n",
       "      <td>abdominal mass</td>\n",
       "      <td>6</td>\n",
       "      <td>50462</td>\n",
       "      <td>11</td>\n",
       "      <td>55207</td>\n",
       "      <td>8</td>\n",
       "      <td>97394</td>\n",
       "      <td>11</td>\n",
       "      <td>91936</td>\n",
       "      <td>7</td>\n",
       "      <td>114654</td>\n",
       "      <td>9</td>\n",
       "      <td>141129</td>\n",
       "      <td>15</td>\n",
       "      <td>117950</td>\n",
       "      <td>10</td>\n",
       "      <td>113897</td>\n",
       "      <td>0.738416</td>\n",
       "      <td>0.440645</td>\n",
       "      <td>1.068882</td>\n",
       "      <td>0.733805</td>\n",
       "      <td>1.438066</td>\n",
       "      <td>1.376770</td>\n",
       "      <td>0.690390</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>-0.090909</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>-0.090909</td>\n",
       "      <td>0.428571</td>\n",
       "      <td>0.111111</td>\n",
       "      <td>-0.333333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         SE                  name  2013_A  2013_B  2014_A  2014_B  2015_A  \\\n",
       "0  10000044      abdomen crushing       0   50468       0   55218       0   \n",
       "1  10000050   abdominal adhesions       9   50459      13   55205       9   \n",
       "2  10000059  abdominal discomfort     344   50124     410   54808     881   \n",
       "3  10000060  abdominal distension     237   50231     262   54956     500   \n",
       "4  10000077        abdominal mass       6   50462      11   55207       8   \n",
       "\n",
       "   2015_B  2016_A  2016_B  2017_A  2017_B  2018_A  2018_B  2019_A  2019_B  \\\n",
       "0   97402       0   91947       0  114661       0  141138       0  117965   \n",
       "1   97393       3   91944      12  114649      10  141128      10  117955   \n",
       "2   96521     891   91056    1226  113435    1469  139669    1298  116667   \n",
       "3   96902     391   91556     562  114099     618  140520     564  117401   \n",
       "4   97394      11   91936       7  114654       9  141129      15  117950   \n",
       "\n",
       "   2020_A  2020_B  2013_ROR  2014_ROR  2015_ROR  2016_ROR  2017_ROR  2018_ROR  \\\n",
       "0       0  113907       NaN       NaN       NaN       NaN       NaN       NaN   \n",
       "1       5  113902  0.246113  0.186412  0.475033  1.345367  0.419399  0.619515   \n",
       "2    1272  112635  1.645512  1.509642  1.237256  1.154103  1.044889  1.073722   \n",
       "3     593  113314  1.109161  1.097703  1.014224  1.225409  1.062470  1.189928   \n",
       "4      10  113897  0.738416  0.440645  1.068882  0.733805  1.438066  1.376770   \n",
       "\n",
       "   2019_ROR  2013_Delta  2014_Delta  2015_Delta  2016_Delta  2017_Delta  \\\n",
       "0       NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "1  0.517792   -0.444444   -0.615385   -0.444444    0.666667   -0.583333   \n",
       "2  1.015049    2.697674    2.102439    0.443814    0.427609    0.037520   \n",
       "3  1.089341    1.502110    1.263359    0.186000    0.516624    0.055160   \n",
       "4  0.690390    0.666667   -0.090909    0.250000   -0.090909    0.428571   \n",
       "\n",
       "   2018_Delta  2019_Delta  \n",
       "0         NaN         NaN  \n",
       "1   -0.500000   -0.500000  \n",
       "2   -0.134105   -0.020031  \n",
       "3   -0.040453    0.051418  \n",
       "4    0.111111   -0.333333  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "para_female = female_matrix\n",
    "yr_list = [2013, 2014, 2015, 2016, 2017, 2018, 2019]\n",
    "for yr in yr_list:  # calculate ROR  \n",
    "    para_female[str(yr)+'_ROR'] = (para_female['2020_A']*para_female[str(yr)+'_B'])/(para_female['2020_B']*para_female[str(yr)+'_A'])\n",
    "\n",
    "for yr in yr_list:  # calculate Delta: average difference  \n",
    "    para_female[str(yr)+'_Delta'] = (para_female['2020_A'] - para_female[str(yr)+'_A'])/para_female[str(yr)+'_A']\n",
    "\n",
    "pd.set_option('display.max_columns', None)\n",
    "\"\"\"Note: 0/0 = NaN, 1/0 = inf\"\"\"\n",
    "para_female.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "pickle.dump(para_female, open('../Data/pandemic/SE_female.pk', 'wb'))  # update the dataframe with ROR and Delta"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conditioned on Age\n",
    "\n",
    "Based on [WHO](https://www.who.int/hiv/pub/guidelines/arv2013/intro/keyterms/en/) and the [Men Ageing And Health](https://apps.who.int/iris/bitstream/handle/10665/66941/WHO_NMH_NPH_01.2.pdf;jsessionid=A48157B9B4DFAA3A9874176D8A7C2894?sequence=1), the age group:\n",
    "\n",
    "- Young: 1-19 \n",
    "- Adult: 20-65\n",
    "- Elderly: >65\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bin age into groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "age_US_df = pickle.load(open('../Data/pandemic/all_pd_US_pro.pk', 'rb'))  # update the dataframe with ROR and Delta\n",
    "\n",
    "# Bin age into groups \n",
    "age_US_df['age'] = pd.to_numeric(age_US_df['age'], errors='coerce')\n",
    "bins = [1, 20, 65, max(age_US_df.age)+1] \n",
    "age_labels = ['young', 'adult','elderly']\n",
    "age_US_df['age_group'] = pd.cut(age_US_df.age, bins, right = False, labels= age_labels)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Three age groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "  3%|▎         | 218/7105 [00:00<00:03, 2176.29it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2013:3567\n",
      "2013 year has 3567 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 7105/7105 [00:03<00:00, 2266.04it/s]\n",
      "  3%|▎         | 200/7105 [00:00<00:03, 1993.12it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2014:4091\n",
      "2014 year has 4091 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 7105/7105 [00:03<00:00, 2003.40it/s]\n",
      "  2%|▏         | 154/7105 [00:00<00:04, 1538.94it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2015:5391\n",
      "2015 year has 5391 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 7105/7105 [00:04<00:00, 1561.56it/s]\n",
      "  2%|▏         | 147/7105 [00:00<00:04, 1468.29it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2016:5708\n",
      "2016 year has 5708 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 7105/7105 [00:04<00:00, 1492.98it/s]\n",
      "  2%|▏         | 121/7105 [00:00<00:05, 1201.74it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2017:7029\n",
      "2017 year has 7029 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 7105/7105 [00:05<00:00, 1215.55it/s]\n",
      "  2%|▏         | 115/7105 [00:00<00:06, 1148.14it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2018:7159\n",
      "2018 year has 7159 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 7105/7105 [00:06<00:00, 1153.06it/s]\n",
      "  2%|▏         | 111/7105 [00:00<00:06, 1101.35it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2019:7602\n",
      "2019 year has 7602 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 7105/7105 [00:06<00:00, 1094.78it/s]\n",
      "  2%|▏         | 121/7105 [00:00<00:05, 1205.68it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2020:6606\n",
      "2020 year has 6606 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 7105/7105 [00:05<00:00, 1202.37it/s]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "young related data saved\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "  0%|          | 22/12594 [00:00<00:58, 216.64it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2013:38512\n",
      "2013 year has 38512 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 12594/12594 [00:57<00:00, 219.67it/s]\n",
      "  0%|          | 20/12594 [00:00<01:02, 199.98it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2014:40632\n",
      "2014 year has 40632 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 12594/12594 [01:04<00:00, 195.59it/s]\n",
      "  0%|          | 12/12594 [00:00<01:53, 110.69it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2015:64769\n",
      "2015 year has 64769 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 12594/12594 [01:54<00:00, 110.41it/s]\n",
      "  0%|          | 12/12594 [00:00<01:52, 111.93it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2016:63836\n",
      "2016 year has 63836 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 12594/12594 [01:48<00:00, 116.08it/s]\n",
      "  0%|          | 11/12594 [00:00<02:04, 101.14it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2017:69955\n",
      "2017 year has 69955 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 12594/12594 [02:04<00:00, 100.91it/s]\n",
      "  0%|          | 9/12594 [00:00<02:25, 86.33it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2018:81578\n",
      "2018 year has 81578 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 12594/12594 [02:24<00:00, 87.33it/s]\n",
      "  0%|          | 10/12594 [00:00<02:16, 92.17it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2019:74209\n",
      "2019 year has 74209 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 12594/12594 [02:16<00:00, 92.41it/s]\n",
      "  0%|          | 10/12594 [00:00<02:08, 97.91it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2020:66258\n",
      "2020 year has 66258 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 12594/12594 [02:08<00:00, 98.19it/s]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "adult related data saved\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "  0%|          | 45/10283 [00:00<00:22, 448.59it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2013:17999\n",
      "2013 year has 17999 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 10283/10283 [00:21<00:00, 481.50it/s]\n",
      "  0%|          | 42/10283 [00:00<00:24, 413.85it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2014:20848\n",
      "2014 year has 20848 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 10283/10283 [00:24<00:00, 411.72it/s]\n",
      "  0%|          | 19/10283 [00:00<00:55, 186.52it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2015:36916\n",
      "2015 year has 36916 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 10283/10283 [00:54<00:00, 188.92it/s]\n",
      "  0%|          | 19/10283 [00:00<00:55, 186.54it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2016:36118\n",
      "2016 year has 36118 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 10283/10283 [00:54<00:00, 187.75it/s]\n",
      "  0%|          | 15/10283 [00:00<01:11, 143.69it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2017:42320\n",
      "2017 year has 42320 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 10283/10283 [01:10<00:00, 145.47it/s]\n",
      "  0%|          | 12/10283 [00:00<01:26, 118.56it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2018:53489\n",
      "2018 year has 53489 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 10283/10283 [01:27<00:00, 117.38it/s]\n",
      "  0%|          | 14/10283 [00:00<01:17, 132.67it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2019:45917\n",
      "2019 year has 45917 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 10283/10283 [01:17<00:00, 132.07it/s]\n",
      "  0%|          | 13/10283 [00:00<01:20, 127.85it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows in 2020:46371\n",
      "2020 year has 46371 reports\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 10283/10283 [01:19<00:00, 128.62it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "elderly related data saved\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "for age in age_labels:\n",
    "    age_US_df_group = age_US_df[age_US_df.age_group==age]\n",
    "\n",
    "    DF = age_US_df_group\n",
    "    # reports in 2020 \n",
    "    \"\"\"initialize the Data frame \"\"\"\n",
    "#     SE_list = list(SE_dic_df.code)\n",
    "#     age_matrix = pd.DataFrame({'SE': SE_list, 'name':list(SE_dic_df.index), 'medra_ID': list(SE_dic_df['medra_ID'])})\n",
    "    \"\"\"Remember sort the SE set!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!11\"\"\"\n",
    "    SE_list = list(sorted(set(list(itertools.chain(*DF.SE)))))\n",
    "    ind = [i in SE_list for i in meddra_pd_all.PT]\n",
    "    whatever_ = meddra_pd_all[ind]\n",
    "    age_matrix = pd.DataFrame({'SE': list(whatever_.PT), 'name':list(whatever_['PT_name']) }) \n",
    "    \n",
    "    \n",
    "    yr_list = [2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020]\n",
    "    for yr in yr_list:\n",
    "        st = str(yr)+'-03-10'\n",
    "        end = str(yr)+'-09-31'\n",
    "        ind = [st<i<end for i in DF['receipt_date']]\n",
    "        locals()['all_pd_US_pro_'+str(yr)]= DF[ind]\n",
    "        print('rows in {}:{}'.format(yr,len(locals()['all_pd_US_pro_'+str(yr)])))\n",
    "\n",
    "        n_report = len(locals()['all_pd_US_pro_'+str(yr)])\n",
    "        print('{} year has {} reports'.format(yr, n_report))\n",
    "        A =[] \n",
    "        for se in tqdm(SE_list):\n",
    "            name = locals()['all_pd_US_pro_'+str(yr)]\n",
    "            indx = [se in j for j in name.SE]\n",
    "            n_A = sum(indx)\n",
    "            A.append(n_A)\n",
    "        B = [n_report - i for i in A]\n",
    "        age_matrix[str(yr)+'_A'] = A\n",
    "        age_matrix[str(yr)+'_B'] = B\n",
    "\n",
    "    pickle.dump(age_matrix, open('../Data/pandemic/SE_'+age+'_raw.pk', 'wb'))\n",
    "\n",
    "    para_age = age_matrix\n",
    "    yr_list = [2013, 2014, 2015, 2016, 2017, 2018, 2019]\n",
    "    for yr in yr_list:  # calculate ROR  \n",
    "        para_age[str(yr)+'_ROR'] = (para_age['2020_A']*para_age[str(yr)+'_B'])/(para_age['2020_B']*para_age[str(yr)+'_A'])\n",
    "\n",
    "    for yr in yr_list:  # calculate Delta: average difference  \n",
    "        para_age[str(yr)+'_Delta'] = (para_age['2020_A'] - para_age[str(yr)+'_A'])/para_age[str(yr)+'_A']\n",
    "\n",
    "    \n",
    "    \"\"\"Note: 0/0 = NaN, 1/0 = inf\"\"\"\n",
    "\n",
    "    pickle.dump(para_age, open('../Data/pandemic/SE_'+age+'.pk', 'wb'))  # update the dataframe with ROR and Delta\n",
    "    print(age,'related data saved')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>version</th>\n",
       "      <th>report_id</th>\n",
       "      <th>case_id</th>\n",
       "      <th>country</th>\n",
       "      <th>qualify</th>\n",
       "      <th>serious</th>\n",
       "      <th>s1</th>\n",
       "      <th>s2</th>\n",
       "      <th>s3</th>\n",
       "      <th>s4</th>\n",
       "      <th>s5</th>\n",
       "      <th>s6</th>\n",
       "      <th>receivedate</th>\n",
       "      <th>receiptdate</th>\n",
       "      <th>age</th>\n",
       "      <th>gender</th>\n",
       "      <th>weight</th>\n",
       "      <th>SE</th>\n",
       "      <th>drugs</th>\n",
       "      <th>indications</th>\n",
       "      <th>lastingdays</th>\n",
       "      <th>date</th>\n",
       "      <th>receipt_date</th>\n",
       "      <th>age_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7667816</th>\n",
       "      <td>3</td>\n",
       "      <td>10058132</td>\n",
       "      <td>US-PFIZER INC-2014093363</td>\n",
       "      <td>US</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5207</td>\n",
       "      <td>7013</td>\n",
       "      <td>74</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[10013700, 10028813]</td>\n",
       "      <td>[DB00482]</td>\n",
       "      <td>[10033371]</td>\n",
       "      <td>1806</td>\n",
       "      <td>2014-04-04</td>\n",
       "      <td>2019-03-15</td>\n",
       "      <td>elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7667849</th>\n",
       "      <td>2</td>\n",
       "      <td>15531775</td>\n",
       "      <td>US-BRISTOL-MYERS SQUIBB COMPANY-BMS-2018-095646</td>\n",
       "      <td>US</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6866</td>\n",
       "      <td>7018</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>69.39</td>\n",
       "      <td>[10060933, 10076476]</td>\n",
       "      <td>[DB00472, DB00572, DB00572, DB00996, DB01039, ...</td>\n",
       "      <td>[10030155, 10070592, 10070592, 10070592, 10070...</td>\n",
       "      <td>152</td>\n",
       "      <td>2018-10-19</td>\n",
       "      <td>2019-03-20</td>\n",
       "      <td>elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7667908</th>\n",
       "      <td>6</td>\n",
       "      <td>15505898</td>\n",
       "      <td>US-PFIZER INC-2018414114</td>\n",
       "      <td>US</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6863</td>\n",
       "      <td>7019</td>\n",
       "      <td>70</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>[10013709]</td>\n",
       "      <td>[DB00563, DB00563, DB00563]</td>\n",
       "      <td>[10039073, 10039073, 10039073]</td>\n",
       "      <td>156</td>\n",
       "      <td>2018-10-16</td>\n",
       "      <td>2019-03-21</td>\n",
       "      <td>elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7667948</th>\n",
       "      <td>4</td>\n",
       "      <td>15478711</td>\n",
       "      <td>US-PFIZER INC-2018399211</td>\n",
       "      <td>US</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6856</td>\n",
       "      <td>7027</td>\n",
       "      <td>65</td>\n",
       "      <td>2</td>\n",
       "      <td>99</td>\n",
       "      <td>[10005481, 10018356, 10038428]</td>\n",
       "      <td>[DB09073, DB09073]</td>\n",
       "      <td>[10035226, 10055113]</td>\n",
       "      <td>171</td>\n",
       "      <td>2018-10-09</td>\n",
       "      <td>2019-03-29</td>\n",
       "      <td>elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7668003</th>\n",
       "      <td>2</td>\n",
       "      <td>15545327</td>\n",
       "      <td>US-PFIZER INC-2018433974</td>\n",
       "      <td>US</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6871</td>\n",
       "      <td>7016</td>\n",
       "      <td>73</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>[10013700]</td>\n",
       "      <td>[DB00318, DB00533, DB01076]</td>\n",
       "      <td>[10037440, 10037440, 10037440]</td>\n",
       "      <td>145</td>\n",
       "      <td>2018-10-24</td>\n",
       "      <td>2019-03-18</td>\n",
       "      <td>elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7668074</th>\n",
       "      <td>3</td>\n",
       "      <td>15548251</td>\n",
       "      <td>US-ALNYLAM PHARMACEUTICALS, INC.-ALN-2018-000218</td>\n",
       "      <td>US</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6871</td>\n",
       "      <td>7017</td>\n",
       "      <td>75</td>\n",
       "      <td>2</td>\n",
       "      <td>71.4</td>\n",
       "      <td>[10007882]</td>\n",
       "      <td>[DB00162, DB00162, DB00181, DB00193, DB00211, ...</td>\n",
       "      <td>[10003553, 10003553, 10003553, 10003553, 10003...</td>\n",
       "      <td>146</td>\n",
       "      <td>2018-10-24</td>\n",
       "      <td>2019-03-19</td>\n",
       "      <td>elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7668086</th>\n",
       "      <td>2</td>\n",
       "      <td>15540366</td>\n",
       "      <td>US-PFIZER INC-2018429645</td>\n",
       "      <td>US</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6870</td>\n",
       "      <td>7017</td>\n",
       "      <td>66</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[10013709]</td>\n",
       "      <td>[DB00230]</td>\n",
       "      <td>[10029331]</td>\n",
       "      <td>147</td>\n",
       "      <td>2018-10-23</td>\n",
       "      <td>2019-03-19</td>\n",
       "      <td>elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7668169</th>\n",
       "      <td>4</td>\n",
       "      <td>13010086</td>\n",
       "      <td>US-CELGENEUS-USA-2016104725</td>\n",
       "      <td>US</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6186</td>\n",
       "      <td>7026</td>\n",
       "      <td>71</td>\n",
       "      <td>2</td>\n",
       "      <td>58.11</td>\n",
       "      <td>[10016256, 10034754, 10035528, 10037844, 10061...</td>\n",
       "      <td>[DB00480, DB00480, DB00480, DB00480]</td>\n",
       "      <td>[10028533, 10028533, 10070592, 10070592]</td>\n",
       "      <td>840</td>\n",
       "      <td>2016-12-08</td>\n",
       "      <td>2019-03-28</td>\n",
       "      <td>elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7668172</th>\n",
       "      <td>5</td>\n",
       "      <td>14285441</td>\n",
       "      <td>US-PFIZER INC-2017532858</td>\n",
       "      <td>US</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6557</td>\n",
       "      <td>7018</td>\n",
       "      <td>69</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>[10010264, 10022437, 10040851, 10076309]</td>\n",
       "      <td>[DB00175, DB00222, DB00230, DB00230, DB00331, ...</td>\n",
       "      <td>[10005423, 10012601, 10012601, 10012680, 10020...</td>\n",
       "      <td>461</td>\n",
       "      <td>2017-12-14</td>\n",
       "      <td>2019-03-20</td>\n",
       "      <td>elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7668189</th>\n",
       "      <td>2</td>\n",
       "      <td>15598934</td>\n",
       "      <td>US-PFIZER INC-2018454583</td>\n",
       "      <td>US</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6886</td>\n",
       "      <td>7010</td>\n",
       "      <td>70</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>[10013700, 10013968, 10019211, 10027175]</td>\n",
       "      <td>[DB00295]</td>\n",
       "      <td>[10072005]</td>\n",
       "      <td>124</td>\n",
       "      <td>2018-11-08</td>\n",
       "      <td>2019-03-12</td>\n",
       "      <td>elderly</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        version report_id                                           case_id  \\\n",
       "7667816       3  10058132                          US-PFIZER INC-2014093363   \n",
       "7667849       2  15531775   US-BRISTOL-MYERS SQUIBB COMPANY-BMS-2018-095646   \n",
       "7667908       6  15505898                          US-PFIZER INC-2018414114   \n",
       "7667948       4  15478711                          US-PFIZER INC-2018399211   \n",
       "7668003       2  15545327                          US-PFIZER INC-2018433974   \n",
       "7668074       3  15548251  US-ALNYLAM PHARMACEUTICALS, INC.-ALN-2018-000218   \n",
       "7668086       2  15540366                          US-PFIZER INC-2018429645   \n",
       "7668169       4  13010086                       US-CELGENEUS-USA-2016104725   \n",
       "7668172       5  14285441                          US-PFIZER INC-2017532858   \n",
       "7668189       2  15598934                          US-PFIZER INC-2018454583   \n",
       "\n",
       "        country qualify serious s1 s2 s3 s4 s5 s6  receivedate  receiptdate  \\\n",
       "7667816      US       1       2  0  0  0  0  0  0         5207         7013   \n",
       "7667849      US       1       2  0  0  0  0  0  0         6866         7018   \n",
       "7667908      US       3       2  0  0  0  0  0  0         6863         7019   \n",
       "7667948      US       1       2  0  0  0  0  0  0         6856         7027   \n",
       "7668003      US       3       2  0  0  0  0  0  0         6871         7016   \n",
       "7668074      US       1       1  0  0  1  0  0  1         6871         7017   \n",
       "7668086      US       3       2  0  0  0  0  0  0         6870         7017   \n",
       "7668169      US       1       1  0  0  0  0  0  1         6186         7026   \n",
       "7668172      US       1       2  0  0  0  0  0  0         6557         7018   \n",
       "7668189      US       3       2  0  0  0  0  0  0         6886         7010   \n",
       "\n",
       "         age gender weight                                                 SE  \\\n",
       "7667816   74      1      0                               [10013700, 10028813]   \n",
       "7667849   67      1  69.39                               [10060933, 10076476]   \n",
       "7667908   70      2      0                                         [10013709]   \n",
       "7667948   65      2     99                     [10005481, 10018356, 10038428]   \n",
       "7668003   73      2      0                                         [10013700]   \n",
       "7668074   75      2   71.4                                         [10007882]   \n",
       "7668086   66      1      0                                         [10013709]   \n",
       "7668169   71      2  58.11  [10016256, 10034754, 10035528, 10037844, 10061...   \n",
       "7668172   69      2      0           [10010264, 10022437, 10040851, 10076309]   \n",
       "7668189   70      2      0           [10013700, 10013968, 10019211, 10027175]   \n",
       "\n",
       "                                                     drugs  \\\n",
       "7667816                                          [DB00482]   \n",
       "7667849  [DB00472, DB00572, DB00572, DB00996, DB01039, ...   \n",
       "7667908                        [DB00563, DB00563, DB00563]   \n",
       "7667948                                 [DB09073, DB09073]   \n",
       "7668003                        [DB00318, DB00533, DB01076]   \n",
       "7668074  [DB00162, DB00162, DB00181, DB00193, DB00211, ...   \n",
       "7668086                                          [DB00230]   \n",
       "7668169               [DB00480, DB00480, DB00480, DB00480]   \n",
       "7668172  [DB00175, DB00222, DB00230, DB00230, DB00331, ...   \n",
       "7668189                                          [DB00295]   \n",
       "\n",
       "                                               indications  lastingdays  \\\n",
       "7667816                                         [10033371]         1806   \n",
       "7667849  [10030155, 10070592, 10070592, 10070592, 10070...          152   \n",
       "7667908                     [10039073, 10039073, 10039073]          156   \n",
       "7667948                               [10035226, 10055113]          171   \n",
       "7668003                     [10037440, 10037440, 10037440]          145   \n",
       "7668074  [10003553, 10003553, 10003553, 10003553, 10003...          146   \n",
       "7668086                                         [10029331]          147   \n",
       "7668169           [10028533, 10028533, 10070592, 10070592]          840   \n",
       "7668172  [10005423, 10012601, 10012601, 10012680, 10020...          461   \n",
       "7668189                                         [10072005]          124   \n",
       "\n",
       "               date receipt_date age_group  \n",
       "7667816  2014-04-04   2019-03-15   elderly  \n",
       "7667849  2018-10-19   2019-03-20   elderly  \n",
       "7667908  2018-10-16   2019-03-21   elderly  \n",
       "7667948  2018-10-09   2019-03-29   elderly  \n",
       "7668003  2018-10-24   2019-03-18   elderly  \n",
       "7668074  2018-10-24   2019-03-19   elderly  \n",
       "7668086  2018-10-23   2019-03-19   elderly  \n",
       "7668169  2016-12-08   2019-03-28   elderly  \n",
       "7668172  2017-12-14   2019-03-20   elderly  \n",
       "7668189  2018-11-08   2019-03-12   elderly  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "locals()['all_pd_US_pro_'+str(yr)].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.2"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "259.625px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}