Cleaning_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing the dependencies\n",
    "import requests\n",
    "from config import API_KEY\n",
    "import pandas as pd\n",
    "import datetime\n",
    "import numpy as np\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2.25.1'"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requests.__version__\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "#importing the vaccine data\n",
    "vax_data = pd.read_csv(\"../Project--COVID-19/Resources/EditedCovidDeath.csv\", low_memory=False)\n",
    "vax_type = pd.read_csv(\"../Project--COVID-19/Resources/2021VAERSVAX.csv\", low_memory=False)\n",
    "vax_symptoms = pd.read_csv(\"../Project--COVID-19/Resources/2021VAERSSYMPTOMS.csv\", low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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>VAERS_ID</th>\n",
       "      <th>SYMPTOM1</th>\n",
       "      <th>SYMPTOMVERSION1</th>\n",
       "      <th>SYMPTOM2</th>\n",
       "      <th>SYMPTOMVERSION2</th>\n",
       "      <th>SYMPTOM3</th>\n",
       "      <th>SYMPTOMVERSION3</th>\n",
       "      <th>SYMPTOM4</th>\n",
       "      <th>SYMPTOMVERSION4</th>\n",
       "      <th>SYMPTOM5</th>\n",
       "      <th>SYMPTOMVERSION5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>916600</td>\n",
       "      <td>Dysphagia</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Epiglottitis</td>\n",
       "      <td>23.1</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>916601</td>\n",
       "      <td>Anxiety</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Dyspnoea</td>\n",
       "      <td>23.1</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>2</th>\n",
       "      <td>916602</td>\n",
       "      <td>Chest discomfort</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Dysphagia</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Pain in extremity</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Visual impairment</td>\n",
       "      <td>23.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>916603</td>\n",
       "      <td>Dizziness</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Mobility decreased</td>\n",
       "      <td>23.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>916604</td>\n",
       "      <td>Injection site erythema</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Injection site pruritus</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Injection site swelling</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Injection site warmth</td>\n",
       "      <td>23.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48105</th>\n",
       "      <td>1113920</td>\n",
       "      <td>Nasal discomfort</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Night sweats</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Platelet count</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Platelet count decreased</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Pyrexia</td>\n",
       "      <td>23.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48106</th>\n",
       "      <td>1113920</td>\n",
       "      <td>Rash</td>\n",
       "      <td>23.1</td>\n",
       "      <td>SARS-CoV-2 test</td>\n",
       "      <td>23.1</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>48107</th>\n",
       "      <td>1113963</td>\n",
       "      <td>Haemophagocytic lymphohistiocytosis</td>\n",
       "      <td>23.1</td>\n",
       "      <td>SARS-CoV-2 test</td>\n",
       "      <td>23.1</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>48108</th>\n",
       "      <td>1115045</td>\n",
       "      <td>Death</td>\n",
       "      <td>23.1</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>48109</th>\n",
       "      <td>1115348</td>\n",
       "      <td>Death</td>\n",
       "      <td>23.1</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>48110 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       VAERS_ID                             SYMPTOM1  SYMPTOMVERSION1  \\\n",
       "0        916600                            Dysphagia             23.1   \n",
       "1        916601                              Anxiety             23.1   \n",
       "2        916602                     Chest discomfort             23.1   \n",
       "3        916603                            Dizziness             23.1   \n",
       "4        916604              Injection site erythema             23.1   \n",
       "...         ...                                  ...              ...   \n",
       "48105   1113920                     Nasal discomfort             23.1   \n",
       "48106   1113920                                 Rash             23.1   \n",
       "48107   1113963  Haemophagocytic lymphohistiocytosis             23.1   \n",
       "48108   1115045                                Death             23.1   \n",
       "48109   1115348                                Death             23.1   \n",
       "\n",
       "                      SYMPTOM2  SYMPTOMVERSION2                 SYMPTOM3  \\\n",
       "0                 Epiglottitis             23.1                      NaN   \n",
       "1                     Dyspnoea             23.1                      NaN   \n",
       "2                    Dysphagia             23.1        Pain in extremity   \n",
       "3                      Fatigue             23.1       Mobility decreased   \n",
       "4      Injection site pruritus             23.1  Injection site swelling   \n",
       "...                        ...              ...                      ...   \n",
       "48105             Night sweats             23.1           Platelet count   \n",
       "48106          SARS-CoV-2 test             23.1                      NaN   \n",
       "48107          SARS-CoV-2 test             23.1                      NaN   \n",
       "48108                      NaN              NaN                      NaN   \n",
       "48109                      NaN              NaN                      NaN   \n",
       "\n",
       "       SYMPTOMVERSION3                  SYMPTOM4  SYMPTOMVERSION4 SYMPTOM5  \\\n",
       "0                  NaN                       NaN              NaN      NaN   \n",
       "1                  NaN                       NaN              NaN      NaN   \n",
       "2                 23.1         Visual impairment             23.1      NaN   \n",
       "3                 23.1                       NaN              NaN      NaN   \n",
       "4                 23.1     Injection site warmth             23.1      NaN   \n",
       "...                ...                       ...              ...      ...   \n",
       "48105             23.1  Platelet count decreased             23.1  Pyrexia   \n",
       "48106              NaN                       NaN              NaN      NaN   \n",
       "48107              NaN                       NaN              NaN      NaN   \n",
       "48108              NaN                       NaN              NaN      NaN   \n",
       "48109              NaN                       NaN              NaN      NaN   \n",
       "\n",
       "       SYMPTOMVERSION5  \n",
       "0                  NaN  \n",
       "1                  NaN  \n",
       "2                  NaN  \n",
       "3                  NaN  \n",
       "4                  NaN  \n",
       "...                ...  \n",
       "48105             23.1  \n",
       "48106              NaN  \n",
       "48107              NaN  \n",
       "48108              NaN  \n",
       "48109              NaN  \n",
       "\n",
       "[48110 rows x 11 columns]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                      Dysphagia\n",
       "1                        Anxiety\n",
       "2               Chest discomfort\n",
       "3                      Dizziness\n",
       "4        Injection site erythema\n",
       "                  ...           \n",
       "48105                    Pyrexia\n",
       "48106                        NaN\n",
       "48107                        NaN\n",
       "48108                        NaN\n",
       "48109                        NaN\n",
       "Length: 240550, dtype: object"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_symptoms = pd.concat([vax_symptoms['SYMPTOM' + str(n)] for n in range(1,6)], axis=0)\n",
    "df_symptoms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['SYMPTOM1', 'SYMPTOM2', 'SYMPTOM3', 'SYMPTOM4', 'SYMPTOM5']"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "['SYMPTOM' + str(n) for n in range(1,6)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Bordetella test negative                                   1\n",
       "Intestinal gangrene                                        1\n",
       "Cytomegalovirus test positive                              1\n",
       "Glucose tolerance test abnormal                            1\n",
       "Epstein-Barr virus test positive                           1\n",
       "Hepatitis C virus test                                     1\n",
       "Enzyme level test                                          1\n",
       "Biopsy small intestine normal                              1\n",
       "Capillary nail refill test abnormal                        1\n",
       "Mycobacterium test negative                                1\n",
       "Intestinal infarction                                      1\n",
       "Abscess intestinal                                         1\n",
       "Klebsiella test positive                                   1\n",
       "Amniotic membrane rupture test positive                    1\n",
       "Gastrointestinal hypomotility                              1\n",
       "Gastrointestinal stoma complication                        1\n",
       "Visual acuity tests abnormal                               1\n",
       "Helicobacter test                                          1\n",
       "Radioallergosorbent test negative                          1\n",
       "Pregnancy test urine positive                              1\n",
       "Exercise test                                              1\n",
       "Small intestinal resection                                 1\n",
       "Gastrointestinal pathogen panel                            1\n",
       "Amniotic membrane rupture test negative                    1\n",
       "SARS-CoV-2 test false positive                             1\n",
       "Activated protein C resistance test positive               1\n",
       "Walking distance test abnormal                             1\n",
       "Small intestinal haemorrhage                               1\n",
       "Biopsy small intestine                                     1\n",
       "Respiratory syncytial virus test positive                  1\n",
       "Skin test negative                                         1\n",
       "Large intestine infection                                  1\n",
       "Parasite stool test                                        1\n",
       "Echovirus test negative                                    1\n",
       "Sexually transmitted disease test                          1\n",
       "X-ray with contrast upper gastrointestinal tract normal    1\n",
       "Gastrointestinal scan                                      1\n",
       "Large intestine polyp                                      1\n",
       "Skin test positive                                         1\n",
       "Endoscopy gastrointestinal normal                          1\n",
       "Haemodynamic test normal                                   1\n",
       "Slit-lamp tests abnormal                                   1\n",
       "Foetal non-stress test abnormal                            1\n",
       "Microbiology test normal                                   1\n",
       "Mast cell degranulation test                               1\n",
       "Autoantibody test                                          1\n",
       "Intestinal resection                                       1\n",
       "Blood testosterone                                         1\n",
       "Parasite blood test                                        1\n",
       "HIV test false positive                                    1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_symptoms[df_symptoms.str.lower().str.find('test') != -1].value_counts().tail(50)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SARS-CoV-2 test    815\n",
       "dtype: int64"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_symptoms[df_symptoms == 'SARS-CoV-2 test'].value_counts().tail(50)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "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>VAERS_ID</th>\n",
       "      <th>SYMPTOM1</th>\n",
       "      <th>SYMPTOMVERSION1</th>\n",
       "      <th>SYMPTOM2</th>\n",
       "      <th>SYMPTOMVERSION2</th>\n",
       "      <th>SYMPTOM3</th>\n",
       "      <th>SYMPTOMVERSION3</th>\n",
       "      <th>SYMPTOM4</th>\n",
       "      <th>SYMPTOMVERSION4</th>\n",
       "      <th>SYMPTOM5</th>\n",
       "      <th>SYMPTOMVERSION5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>916600</td>\n",
       "      <td>Dysphagia</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Epiglottitis</td>\n",
       "      <td>23.1</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>916601</td>\n",
       "      <td>Anxiety</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Dyspnoea</td>\n",
       "      <td>23.1</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>2</th>\n",
       "      <td>916602</td>\n",
       "      <td>Chest discomfort</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Dysphagia</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Pain in extremity</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Visual impairment</td>\n",
       "      <td>23.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>916603</td>\n",
       "      <td>Dizziness</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Mobility decreased</td>\n",
       "      <td>23.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>916604</td>\n",
       "      <td>Injection Site Symptoms</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Injection Site Symptoms</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Injection Site Symptoms</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Injection Site Symptoms</td>\n",
       "      <td>23.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48105</th>\n",
       "      <td>1113920</td>\n",
       "      <td>Nasal discomfort</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Night sweats</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Platelet count</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Platelet count decreased</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Pyrexia</td>\n",
       "      <td>23.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48106</th>\n",
       "      <td>1113920</td>\n",
       "      <td>Rash</td>\n",
       "      <td>23.1</td>\n",
       "      <td>SARS-CoV-2 test</td>\n",
       "      <td>23.1</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>48107</th>\n",
       "      <td>1113963</td>\n",
       "      <td>Haemophagocytic lymphohistiocytosis</td>\n",
       "      <td>23.1</td>\n",
       "      <td>SARS-CoV-2 test</td>\n",
       "      <td>23.1</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>48108</th>\n",
       "      <td>1115045</td>\n",
       "      <td>Death</td>\n",
       "      <td>23.1</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>48109</th>\n",
       "      <td>1115348</td>\n",
       "      <td>Death</td>\n",
       "      <td>23.1</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>48110 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       VAERS_ID                             SYMPTOM1  SYMPTOMVERSION1  \\\n",
       "0        916600                            Dysphagia             23.1   \n",
       "1        916601                              Anxiety             23.1   \n",
       "2        916602                     Chest discomfort             23.1   \n",
       "3        916603                            Dizziness             23.1   \n",
       "4        916604              Injection Site Symptoms             23.1   \n",
       "...         ...                                  ...              ...   \n",
       "48105   1113920                     Nasal discomfort             23.1   \n",
       "48106   1113920                                 Rash             23.1   \n",
       "48107   1113963  Haemophagocytic lymphohistiocytosis             23.1   \n",
       "48108   1115045                                Death             23.1   \n",
       "48109   1115348                                Death             23.1   \n",
       "\n",
       "                      SYMPTOM2  SYMPTOMVERSION2                 SYMPTOM3  \\\n",
       "0                 Epiglottitis             23.1                      NaN   \n",
       "1                     Dyspnoea             23.1                      NaN   \n",
       "2                    Dysphagia             23.1        Pain in extremity   \n",
       "3                      Fatigue             23.1       Mobility decreased   \n",
       "4      Injection Site Symptoms             23.1  Injection Site Symptoms   \n",
       "...                        ...              ...                      ...   \n",
       "48105             Night sweats             23.1           Platelet count   \n",
       "48106          SARS-CoV-2 test             23.1                      NaN   \n",
       "48107          SARS-CoV-2 test             23.1                      NaN   \n",
       "48108                      NaN              NaN                      NaN   \n",
       "48109                      NaN              NaN                      NaN   \n",
       "\n",
       "       SYMPTOMVERSION3                  SYMPTOM4  SYMPTOMVERSION4 SYMPTOM5  \\\n",
       "0                  NaN                       NaN              NaN      NaN   \n",
       "1                  NaN                       NaN              NaN      NaN   \n",
       "2                 23.1         Visual impairment             23.1      NaN   \n",
       "3                 23.1                       NaN              NaN      NaN   \n",
       "4                 23.1   Injection Site Symptoms             23.1      NaN   \n",
       "...                ...                       ...              ...      ...   \n",
       "48105             23.1  Platelet count decreased             23.1  Pyrexia   \n",
       "48106              NaN                       NaN              NaN      NaN   \n",
       "48107              NaN                       NaN              NaN      NaN   \n",
       "48108              NaN                       NaN              NaN      NaN   \n",
       "48109              NaN                       NaN              NaN      NaN   \n",
       "\n",
       "       SYMPTOMVERSION5  \n",
       "0                  NaN  \n",
       "1                  NaN  \n",
       "2                  NaN  \n",
       "3                  NaN  \n",
       "4                  NaN  \n",
       "...                ...  \n",
       "48105             23.1  \n",
       "48106              NaN  \n",
       "48107              NaN  \n",
       "48108              NaN  \n",
       "48109              NaN  \n",
       "\n",
       "[48110 rows x 11 columns]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sym_cols = ['SYMPTOM' + str(n) for n in range(1,6)]\n",
    "sym_clean = vax_symptoms[sym_cols].applymap(\n",
    "    lambda x: 'Pain' if isinstance(x, str) and 'pain' in x.lower() else x\n",
    ")\n",
    "sym_clean = vax_symptoms[sym_cols].applymap(\n",
    "    lambda x: 'Injection Site Symptoms' if isinstance(x, str) and 'injection site' in x.lower() else x\n",
    ")\n",
    "symptoms_to_delete = ['SARS-CoV-2 test']\n",
    "sym_clean = sym_clean.applymap(lambda x: pd.NA if pd.notna(x) in symptoms_to_delete else x)\n",
    "\n",
    "vax_symptoms[sym_cols] = sym_clean\n",
    "vax_symptoms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'DataFrame' object has no attribute 'pd'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-95-bfc5a764c52d>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mvax_symptoms\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mprofile_report\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/opt/anaconda3/envs/PythonTest/lib/python3.7/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36m__getattr__\u001b[0;34m(self, name)\u001b[0m\n\u001b[1;32m   5463\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_info_axis\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_can_hold_identifiers_and_holds_name\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5464\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 5465\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mobject\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__getattribute__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   5466\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   5467\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__setattr__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mstr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m->\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mAttributeError\u001b[0m: 'DataFrame' object has no attribute 'pd'"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "\n",
    "vax_symptoms.pd.profile_report\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>VAERS_ID</th>\n",
       "      <th>RECVDATE</th>\n",
       "      <th>STATE</th>\n",
       "      <th>AGE_YRS</th>\n",
       "      <th>CAGE_YR</th>\n",
       "      <th>CAGE_MO</th>\n",
       "      <th>SEX</th>\n",
       "      <th>RPT_DATE</th>\n",
       "      <th>SYMPTOM_TEXT</th>\n",
       "      <th>DIED</th>\n",
       "      <th>...</th>\n",
       "      <th>CUR_ILL</th>\n",
       "      <th>HISTORY</th>\n",
       "      <th>PRIOR_VAX</th>\n",
       "      <th>SPLTTYPE</th>\n",
       "      <th>FORM_VERS</th>\n",
       "      <th>TODAYS_DATE</th>\n",
       "      <th>BIRTH_DEFECT</th>\n",
       "      <th>OFC_VISIT</th>\n",
       "      <th>ER_ED_VISIT</th>\n",
       "      <th>ALLERGIES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>916600</td>\n",
       "      <td>1/1/21</td>\n",
       "      <td>TX</td>\n",
       "      <td>33.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Right side of epiglottis swelled up and hinder...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>1/1/21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pcn and bee venom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>916601</td>\n",
       "      <td>1/1/21</td>\n",
       "      <td>CA</td>\n",
       "      <td>73.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Approximately 30 min post vaccination administ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Patient residing at nursing facility. See pati...</td>\n",
       "      <td>Patient residing at nursing facility. See pati...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>1/1/21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\"Dairy\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>916602</td>\n",
       "      <td>1/1/21</td>\n",
       "      <td>WA</td>\n",
       "      <td>23.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>About 15 minutes after receiving the vaccine, ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>1/1/21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>Shellfish</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>916603</td>\n",
       "      <td>1/1/21</td>\n",
       "      <td>WA</td>\n",
       "      <td>58.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>extreme fatigue, dizziness,. could not lift my...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>kidney infection</td>\n",
       "      <td>diverticulitis, mitral valve prolapse, osteoar...</td>\n",
       "      <td>got measles from measel shot, mums from mumps ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>1/1/21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Diclofenac, novacaine, lidocaine, pickles, tom...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>916604</td>\n",
       "      <td>1/1/21</td>\n",
       "      <td>TX</td>\n",
       "      <td>47.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Injection site swelling, redness, warm to the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Na</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>1/1/21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Na</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   VAERS_ID RECVDATE STATE  AGE_YRS  CAGE_YR  CAGE_MO SEX RPT_DATE  \\\n",
       "0    916600   1/1/21    TX     33.0     33.0      NaN   F      NaN   \n",
       "1    916601   1/1/21    CA     73.0     73.0      NaN   F      NaN   \n",
       "2    916602   1/1/21    WA     23.0     23.0      NaN   F      NaN   \n",
       "3    916603   1/1/21    WA     58.0     58.0      NaN   F      NaN   \n",
       "4    916604   1/1/21    TX     47.0     47.0      NaN   F      NaN   \n",
       "\n",
       "                                        SYMPTOM_TEXT DIED  ...  \\\n",
       "0  Right side of epiglottis swelled up and hinder...  NaN  ...   \n",
       "1  Approximately 30 min post vaccination administ...  NaN  ...   \n",
       "2  About 15 minutes after receiving the vaccine, ...  NaN  ...   \n",
       "3  extreme fatigue, dizziness,. could not lift my...  NaN  ...   \n",
       "4  Injection site swelling, redness, warm to the ...  NaN  ...   \n",
       "\n",
       "                                             CUR_ILL  \\\n",
       "0                                               None   \n",
       "1  Patient residing at nursing facility. See pati...   \n",
       "2                                               None   \n",
       "3                                   kidney infection   \n",
       "4                                                 Na   \n",
       "\n",
       "                                             HISTORY  \\\n",
       "0                                               None   \n",
       "1  Patient residing at nursing facility. See pati...   \n",
       "2                                               None   \n",
       "3  diverticulitis, mitral valve prolapse, osteoar...   \n",
       "4                                                NaN   \n",
       "\n",
       "                                           PRIOR_VAX SPLTTYPE  FORM_VERS  \\\n",
       "0                                                NaN      NaN          2   \n",
       "1                                                NaN      NaN          2   \n",
       "2                                                NaN      NaN          2   \n",
       "3  got measles from measel shot, mums from mumps ...      NaN          2   \n",
       "4                                                NaN      NaN          2   \n",
       "\n",
       "  TODAYS_DATE BIRTH_DEFECT OFC_VISIT ER_ED_VISIT  \\\n",
       "0      1/1/21          NaN         Y         NaN   \n",
       "1      1/1/21          NaN         Y         NaN   \n",
       "2      1/1/21          NaN       NaN           Y   \n",
       "3      1/1/21          NaN       NaN         NaN   \n",
       "4      1/1/21          NaN       NaN         NaN   \n",
       "\n",
       "                                           ALLERGIES  \n",
       "0                                  Pcn and bee venom  \n",
       "1                                            \"Dairy\"  \n",
       "2                                          Shellfish  \n",
       "3  Diclofenac, novacaine, lidocaine, pickles, tom...  \n",
       "4                                                 Na  \n",
       "\n",
       "[5 rows x 35 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "34121"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(vax_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>VAERS_ID</th>\n",
       "      <th>RECVDATE</th>\n",
       "      <th>STATE</th>\n",
       "      <th>AGE_YRS</th>\n",
       "      <th>CAGE_YR</th>\n",
       "      <th>CAGE_MO</th>\n",
       "      <th>SEX</th>\n",
       "      <th>RPT_DATE</th>\n",
       "      <th>SYMPTOM_TEXT</th>\n",
       "      <th>DIED</th>\n",
       "      <th>...</th>\n",
       "      <th>CUR_ILL</th>\n",
       "      <th>HISTORY</th>\n",
       "      <th>PRIOR_VAX</th>\n",
       "      <th>SPLTTYPE</th>\n",
       "      <th>FORM_VERS</th>\n",
       "      <th>TODAYS_DATE</th>\n",
       "      <th>BIRTH_DEFECT</th>\n",
       "      <th>OFC_VISIT</th>\n",
       "      <th>ER_ED_VISIT</th>\n",
       "      <th>ALLERGIES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22301</th>\n",
       "      <td>1016617</td>\n",
       "      <td>2/9/21</td>\n",
       "      <td>NY</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>He almost passed out a couple times; Fever; we...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medical History/Concurrent Conditions: Lupus e...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USPFIZER INC2021075939</td>\n",
       "      <td>2</td>\n",
       "      <td>2/9/21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2469</th>\n",
       "      <td>919443</td>\n",
       "      <td>1/4/21</td>\n",
       "      <td>GA</td>\n",
       "      <td>58.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S: Employee received Covid vaccination on 12/2...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>ICD-10-CM Priority Class Noted - Resolved MENO...</td>\n",
       "      <td>See Item #11 above</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>1/4/21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Lortab &amp; Morphine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26018</th>\n",
       "      <td>1047524</td>\n",
       "      <td>2/22/21</td>\n",
       "      <td>NM</td>\n",
       "      <td>28.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1 week post-vaccine, my deltoid (injection sit...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2/22/21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20244</th>\n",
       "      <td>986055</td>\n",
       "      <td>1/29/21</td>\n",
       "      <td>NJ</td>\n",
       "      <td>56.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>close to passing out; felt \"heat waves\" throug...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medical History/Concurrent Conditions: GERD; H...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USPFIZER INC2021035819</td>\n",
       "      <td>2</td>\n",
       "      <td>1/29/21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28863</th>\n",
       "      <td>1058203</td>\n",
       "      <td>2/26/21</td>\n",
       "      <td>OH</td>\n",
       "      <td>89.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>right arm is sore; Fever; tired; This is a spo...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Arthritis (Diagnosed about 5 or 6 years ago); ...</td>\n",
       "      <td>Medical History/Concurrent Conditions: COVID-1...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USPFIZER INC2021156605</td>\n",
       "      <td>2</td>\n",
       "      <td>2/26/21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       VAERS_ID RECVDATE STATE  AGE_YRS  CAGE_YR  CAGE_MO SEX RPT_DATE  \\\n",
       "22301   1016617   2/9/21    NY     70.0      NaN      NaN   M      NaN   \n",
       "2469     919443   1/4/21    GA     58.0     58.0      NaN   F      NaN   \n",
       "26018   1047524  2/22/21    NM     28.0     28.0      NaN   F      NaN   \n",
       "20244    986055  1/29/21    NJ     56.0      NaN      NaN   F      NaN   \n",
       "28863   1058203  2/26/21    OH     89.0      NaN      NaN   F      NaN   \n",
       "\n",
       "                                            SYMPTOM_TEXT DIED  ...  \\\n",
       "22301  He almost passed out a couple times; Fever; we...  NaN  ...   \n",
       "2469   S: Employee received Covid vaccination on 12/2...  NaN  ...   \n",
       "26018  1 week post-vaccine, my deltoid (injection sit...  NaN  ...   \n",
       "20244  close to passing out; felt \"heat waves\" throug...  NaN  ...   \n",
       "28863  right arm is sore; Fever; tired; This is a spo...  NaN  ...   \n",
       "\n",
       "                                                 CUR_ILL  \\\n",
       "22301                                                NaN   \n",
       "2469   ICD-10-CM Priority Class Noted - Resolved MENO...   \n",
       "26018                                               None   \n",
       "20244                                                NaN   \n",
       "28863  Arthritis (Diagnosed about 5 or 6 years ago); ...   \n",
       "\n",
       "                                                 HISTORY PRIOR_VAX  \\\n",
       "22301  Medical History/Concurrent Conditions: Lupus e...       NaN   \n",
       "2469                                  See Item #11 above       NaN   \n",
       "26018                                               None       NaN   \n",
       "20244  Medical History/Concurrent Conditions: GERD; H...       NaN   \n",
       "28863  Medical History/Concurrent Conditions: COVID-1...       NaN   \n",
       "\n",
       "                     SPLTTYPE  FORM_VERS TODAYS_DATE BIRTH_DEFECT OFC_VISIT  \\\n",
       "22301  USPFIZER INC2021075939          2      2/9/21          NaN       NaN   \n",
       "2469                      NaN          2      1/4/21          NaN         Y   \n",
       "26018                     NaN          2     2/22/21          NaN       NaN   \n",
       "20244  USPFIZER INC2021035819          2     1/29/21          NaN       NaN   \n",
       "28863  USPFIZER INC2021156605          2     2/26/21          NaN       NaN   \n",
       "\n",
       "      ER_ED_VISIT          ALLERGIES  \n",
       "22301         NaN                NaN  \n",
       "2469            Y  Lortab & Morphine  \n",
       "26018         NaN               None  \n",
       "20244         NaN                NaN  \n",
       "28863         NaN                NaN  \n",
       "\n",
       "[5 rows x 35 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# all the data has imported \n",
    "vax_data.sample(5)"
   ]
  },
  {
   "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>VAERS_ID</th>\n",
       "      <th>RECVDATE</th>\n",
       "      <th>STATE</th>\n",
       "      <th>AGE_YRS</th>\n",
       "      <th>CAGE_YR</th>\n",
       "      <th>CAGE_MO</th>\n",
       "      <th>SEX</th>\n",
       "      <th>RPT_DATE</th>\n",
       "      <th>SYMPTOM_TEXT</th>\n",
       "      <th>DIED</th>\n",
       "      <th>...</th>\n",
       "      <th>CUR_ILL</th>\n",
       "      <th>HISTORY</th>\n",
       "      <th>PRIOR_VAX</th>\n",
       "      <th>SPLTTYPE</th>\n",
       "      <th>FORM_VERS</th>\n",
       "      <th>TODAYS_DATE</th>\n",
       "      <th>BIRTH_DEFECT</th>\n",
       "      <th>OFC_VISIT</th>\n",
       "      <th>ER_ED_VISIT</th>\n",
       "      <th>ALLERGIES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>34116</th>\n",
       "      <td>1113917</td>\n",
       "      <td>3/19/21</td>\n",
       "      <td>FL</td>\n",
       "      <td>88.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>throat closure three weeks after second dose; ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medical History/Concurrent Conditions: Aortic ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USMODERNATX, INC.MOD20210</td>\n",
       "      <td>2</td>\n",
       "      <td>3/12/21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34117</th>\n",
       "      <td>1113920</td>\n",
       "      <td>3/19/21</td>\n",
       "      <td>PA</td>\n",
       "      <td>83.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>positive COVID-19 test with no symptoms; Asymp...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medical History/Concurrent Conditions: Atrial ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USPFIZER INC2021231682</td>\n",
       "      <td>2</td>\n",
       "      <td>3/16/21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34118</th>\n",
       "      <td>1113963</td>\n",
       "      <td>3/19/21</td>\n",
       "      <td>MS</td>\n",
       "      <td>59.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HLH (hemophagocyticlymphohistiocytosis); This ...</td>\n",
       "      <td>Y</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medical History/Concurrent Conditions: Type II...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USPFIZER INC2021257356</td>\n",
       "      <td>2</td>\n",
       "      <td>3/12/21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34119</th>\n",
       "      <td>1115045</td>\n",
       "      <td>3/19/21</td>\n",
       "      <td>AZ</td>\n",
       "      <td>57.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Death; A spontaneous report was received from ...</td>\n",
       "      <td>Y</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medical History/Concurrent Conditions: No adve...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USMODERNATX, INC.MOD20210</td>\n",
       "      <td>2</td>\n",
       "      <td>3/15/21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34120</th>\n",
       "      <td>1115348</td>\n",
       "      <td>3/19/21</td>\n",
       "      <td>NC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>85.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>death; A spontaneous report was received from ...</td>\n",
       "      <td>Y</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Comments: List of non-encoded Patient Relevant...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USMODERNATX, INC.MOD20210</td>\n",
       "      <td>2</td>\n",
       "      <td>3/12/21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       VAERS_ID RECVDATE STATE  AGE_YRS  CAGE_YR  CAGE_MO SEX RPT_DATE  \\\n",
       "34116   1113917  3/19/21    FL     88.0     87.0      NaN   F      NaN   \n",
       "34117   1113920  3/19/21    PA     83.0      NaN      NaN   F      NaN   \n",
       "34118   1113963  3/19/21    MS     59.0      NaN      NaN   F      NaN   \n",
       "34119   1115045  3/19/21    AZ     57.0      NaN      NaN   M      NaN   \n",
       "34120   1115348  3/19/21    NC      NaN     85.0      NaN   F      NaN   \n",
       "\n",
       "                                            SYMPTOM_TEXT DIED  ... CUR_ILL  \\\n",
       "34116  throat closure three weeks after second dose; ...  NaN  ...     NaN   \n",
       "34117  positive COVID-19 test with no symptoms; Asymp...  NaN  ...     NaN   \n",
       "34118  HLH (hemophagocyticlymphohistiocytosis); This ...    Y  ...     NaN   \n",
       "34119  Death; A spontaneous report was received from ...    Y  ...     NaN   \n",
       "34120  death; A spontaneous report was received from ...    Y  ...     NaN   \n",
       "\n",
       "                                                 HISTORY PRIOR_VAX  \\\n",
       "34116  Medical History/Concurrent Conditions: Aortic ...       NaN   \n",
       "34117  Medical History/Concurrent Conditions: Atrial ...       NaN   \n",
       "34118  Medical History/Concurrent Conditions: Type II...       NaN   \n",
       "34119  Medical History/Concurrent Conditions: No adve...       NaN   \n",
       "34120  Comments: List of non-encoded Patient Relevant...       NaN   \n",
       "\n",
       "                        SPLTTYPE  FORM_VERS TODAYS_DATE BIRTH_DEFECT  \\\n",
       "34116  USMODERNATX, INC.MOD20210          2     3/12/21          NaN   \n",
       "34117     USPFIZER INC2021231682          2     3/16/21          NaN   \n",
       "34118     USPFIZER INC2021257356          2     3/12/21          NaN   \n",
       "34119  USMODERNATX, INC.MOD20210          2     3/15/21          NaN   \n",
       "34120  USMODERNATX, INC.MOD20210          2     3/12/21          NaN   \n",
       "\n",
       "      OFC_VISIT ER_ED_VISIT ALLERGIES  \n",
       "34116       NaN           Y       NaN  \n",
       "34117         Y         NaN       NaN  \n",
       "34118       NaN         NaN       NaN  \n",
       "34119       NaN         NaN       NaN  \n",
       "34120       NaN         NaN       NaN  \n",
       "\n",
       "[5 rows x 35 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_data.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "VAERS_ID            0\n",
       "RECVDATE            0\n",
       "STATE            5571\n",
       "AGE_YRS          3188\n",
       "CAGE_YR          7330\n",
       "SEX                 0\n",
       "RPT_DATE        34058\n",
       "SYMPTOM_TEXT        2\n",
       "DIED            32164\n",
       "DATEDIED        32323\n",
       "L_THREAT        32862\n",
       "ER_VISIT        34110\n",
       "HOSPITAL        29734\n",
       "HOSPDAYS        31264\n",
       "X_STAY          34069\n",
       "DISABLE         33251\n",
       "RECOVD           2857\n",
       "VAX_DATE         1499\n",
       "ONSET_DATE       1863\n",
       "NUMDAYS          2927\n",
       "LAB_DATA        19041\n",
       "V_ADMINBY           0\n",
       "V_FUNDBY        34057\n",
       "OTHER_MEDS      13882\n",
       "CUR_ILL         18052\n",
       "HISTORY         11746\n",
       "PRIOR_VAX       32687\n",
       "SPLTTYPE        25898\n",
       "FORM_VERS           0\n",
       "TODAYS_DATE       199\n",
       "BIRTH_DEFECT    34070\n",
       "OFC_VISIT       28717\n",
       "ER_ED_VISIT     28592\n",
       "ALLERGIES       15534\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for null values in the data\n",
    "vax_data[['VAERS_ID', 'RECVDATE', 'STATE', 'AGE_YRS', 'CAGE_YR', 'SEX',\n",
    "       'RPT_DATE', 'SYMPTOM_TEXT', 'DIED', 'DATEDIED', 'L_THREAT', 'ER_VISIT',\n",
    "       'HOSPITAL', 'HOSPDAYS', 'X_STAY', 'DISABLE', 'RECOVD', 'VAX_DATE',\n",
    "       'ONSET_DATE', 'NUMDAYS', 'LAB_DATA', 'V_ADMINBY', 'V_FUNDBY',\n",
    "       'OTHER_MEDS', 'CUR_ILL', 'HISTORY', 'PRIOR_VAX', 'SPLTTYPE',\n",
    "       'FORM_VERS', 'TODAYS_DATE', 'BIRTH_DEFECT', 'OFC_VISIT', 'ER_ED_VISIT',\n",
    "       'ALLERGIES']].isnull().sum()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(vax_data.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['RECVDATE',\n",
       " 'CAGE_YR',\n",
       " 'CAGE_MO',\n",
       " 'RPT_DATE',\n",
       " 'SYMPTOM_TEXT',\n",
       " 'DATEDIED',\n",
       " 'L_THREAT',\n",
       " 'ER_VISIT',\n",
       " 'HOSPDAYS',\n",
       " 'X_STAY',\n",
       " 'DISABLE',\n",
       " 'LAB_DATA',\n",
       " 'V_FUNDBY',\n",
       " 'PRIOR_VAX',\n",
       " 'SPLTTYPE',\n",
       " 'BIRTH_DEFECT',\n",
       " 'OFC_VISIT',\n",
       " 'ER_ED_VISIT']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Removing columns with more 90% of the data as NULL\n",
    "unuseful_cols= [ column for column in vax_data.columns if column in [\"RECVDATE\",\"CAGE_YR\",\"CAGE_MO\",\"RPT_DATE\",\"SYMPTOM_TEXT\",\"DATEDIED\",\"L_THREAT\",\"ER_VISIT\",\"HOSPDAYS\",\"X_STAY\",\"DISABLE\",\"LAB_DATA\",\"V_FUNDBY\",\"HISTORY \",\"PRIOR_VAX\",\"SPLTTYPE\",\"BIRTH_DEFECT\",\"OFC_VISIT\", \"ER_ED_VISIT\"]]\n",
    "unuseful_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the columns \n",
    "vax_data = vax_data.drop(unuseful_cols, axis=1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(vax_data.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['VAERS_ID', 'STATE', 'AGE_YRS', 'SEX', 'DIED', 'HOSPITAL', 'RECOVD',\n",
       "       'VAX_DATE', 'ONSET_DATE', 'NUMDAYS', 'V_ADMINBY', 'OTHER_MEDS',\n",
       "       'CUR_ILL', 'HISTORY', 'FORM_VERS', 'TODAYS_DATE', 'ALLERGIES'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "VAERS_ID         int64\n",
       "STATE           object\n",
       "AGE_YRS        float64\n",
       "SEX             object\n",
       "DIED            object\n",
       "HOSPITAL        object\n",
       "RECOVD          object\n",
       "VAX_DATE        object\n",
       "ONSET_DATE      object\n",
       "NUMDAYS        float64\n",
       "V_ADMINBY       object\n",
       "OTHER_MEDS      object\n",
       "CUR_ILL         object\n",
       "HISTORY         object\n",
       "FORM_VERS        int64\n",
       "TODAYS_DATE     object\n",
       "ALLERGIES       object\n",
       "dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": 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>VAERS_ID</th>\n",
       "      <th>STATE</th>\n",
       "      <th>AGE_YRS</th>\n",
       "      <th>SEX</th>\n",
       "      <th>DIED</th>\n",
       "      <th>HOSPITAL</th>\n",
       "      <th>RECOVD</th>\n",
       "      <th>VAX_DATE</th>\n",
       "      <th>ONSET_DATE</th>\n",
       "      <th>NUMDAYS</th>\n",
       "      <th>V_ADMINBY</th>\n",
       "      <th>OTHER_MEDS</th>\n",
       "      <th>CUR_ILL</th>\n",
       "      <th>HISTORY</th>\n",
       "      <th>FORM_VERS</th>\n",
       "      <th>TODAYS_DATE</th>\n",
       "      <th>ALLERGIES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>916600</td>\n",
       "      <td>TX</td>\n",
       "      <td>33.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>2020-12-28</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2.0</td>\n",
       "      <td>PVT</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>Pcn and bee venom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>916601</td>\n",
       "      <td>CA</td>\n",
       "      <td>73.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SEN</td>\n",
       "      <td>Patient residing at nursing facility. See pati...</td>\n",
       "      <td>Patient residing at nursing facility. See pati...</td>\n",
       "      <td>Patient residing at nursing facility. See pati...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>\"Dairy\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>916602</td>\n",
       "      <td>WA</td>\n",
       "      <td>23.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>U</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SEN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>Shellfish</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>916603</td>\n",
       "      <td>WA</td>\n",
       "      <td>58.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>2020-12-23</td>\n",
       "      <td>2020-12-23</td>\n",
       "      <td>0.0</td>\n",
       "      <td>WRK</td>\n",
       "      <td>none</td>\n",
       "      <td>kidney infection</td>\n",
       "      <td>diverticulitis, mitral valve prolapse, osteoar...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>Diclofenac, novacaine, lidocaine, pickles, tom...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>916604</td>\n",
       "      <td>TX</td>\n",
       "      <td>47.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>2020-12-22</td>\n",
       "      <td>2020-12-29</td>\n",
       "      <td>7.0</td>\n",
       "      <td>PUB</td>\n",
       "      <td>Na</td>\n",
       "      <td>Na</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>Na</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34116</th>\n",
       "      <td>1113917</td>\n",
       "      <td>FL</td>\n",
       "      <td>88.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>U</td>\n",
       "      <td>2021-01-13</td>\n",
       "      <td>2021-02-25</td>\n",
       "      <td>43.0</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medical History/Concurrent Conditions: Aortic ...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-03-12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34117</th>\n",
       "      <td>1113920</td>\n",
       "      <td>PA</td>\n",
       "      <td>83.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>2021-02-09</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>ELIQUIS; SYNTHROID; PRAVASTATIN; DILTIAZEM; DO...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medical History/Concurrent Conditions: Atrial ...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-03-16</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34118</th>\n",
       "      <td>1113963</td>\n",
       "      <td>MS</td>\n",
       "      <td>59.0</td>\n",
       "      <td>F</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>2021-02-05</td>\n",
       "      <td>2021-02-15</td>\n",
       "      <td>10.0</td>\n",
       "      <td>PVT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medical History/Concurrent Conditions: Type II...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-03-12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34119</th>\n",
       "      <td>1115045</td>\n",
       "      <td>AZ</td>\n",
       "      <td>57.0</td>\n",
       "      <td>M</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>2021-03-02</td>\n",
       "      <td>2021-03-02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medical History/Concurrent Conditions: No adve...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-03-15</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34120</th>\n",
       "      <td>1115348</td>\n",
       "      <td>NC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>2021-03-05</td>\n",
       "      <td>2021-03-06</td>\n",
       "      <td>1.0</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Comments: List of non-encoded Patient Relevant...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-03-12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>34121 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       VAERS_ID STATE  AGE_YRS SEX DIED HOSPITAL RECOVD   VAX_DATE ONSET_DATE  \\\n",
       "0        916600    TX     33.0   F  NaN      NaN      Y 2020-12-28 2020-12-30   \n",
       "1        916601    CA     73.0   F  NaN      NaN      Y 2020-12-31 2020-12-31   \n",
       "2        916602    WA     23.0   F  NaN      NaN      U 2020-12-31 2020-12-31   \n",
       "3        916603    WA     58.0   F  NaN      NaN      Y 2020-12-23 2020-12-23   \n",
       "4        916604    TX     47.0   F  NaN      NaN      N 2020-12-22 2020-12-29   \n",
       "...         ...   ...      ...  ..  ...      ...    ...        ...        ...   \n",
       "34116   1113917    FL     88.0   F  NaN        Y      U 2021-01-13 2021-02-25   \n",
       "34117   1113920    PA     83.0   F  NaN      NaN      Y 2021-02-09        NaT   \n",
       "34118   1113963    MS     59.0   F    Y      NaN      N 2021-02-05 2021-02-15   \n",
       "34119   1115045    AZ     57.0   M    Y      NaN      N 2021-03-02 2021-03-02   \n",
       "34120   1115348    NC      NaN   F    Y      NaN      N 2021-03-05 2021-03-06   \n",
       "\n",
       "       NUMDAYS V_ADMINBY                                         OTHER_MEDS  \\\n",
       "0          2.0       PVT                                               None   \n",
       "1          0.0       SEN  Patient residing at nursing facility. See pati...   \n",
       "2          0.0       SEN                                               None   \n",
       "3          0.0       WRK                                               none   \n",
       "4          7.0       PUB                                                 Na   \n",
       "...        ...       ...                                                ...   \n",
       "34116     43.0       UNK                                                NaN   \n",
       "34117      NaN       UNK  ELIQUIS; SYNTHROID; PRAVASTATIN; DILTIAZEM; DO...   \n",
       "34118     10.0       PVT                                                NaN   \n",
       "34119      0.0       UNK                                                NaN   \n",
       "34120      1.0       UNK                                                NaN   \n",
       "\n",
       "                                                 CUR_ILL  \\\n",
       "0                                                   None   \n",
       "1      Patient residing at nursing facility. See pati...   \n",
       "2                                                   None   \n",
       "3                                       kidney infection   \n",
       "4                                                     Na   \n",
       "...                                                  ...   \n",
       "34116                                                NaN   \n",
       "34117                                                NaN   \n",
       "34118                                                NaN   \n",
       "34119                                                NaN   \n",
       "34120                                                NaN   \n",
       "\n",
       "                                                 HISTORY  FORM_VERS  \\\n",
       "0                                                   None          2   \n",
       "1      Patient residing at nursing facility. See pati...          2   \n",
       "2                                                   None          2   \n",
       "3      diverticulitis, mitral valve prolapse, osteoar...          2   \n",
       "4                                                    NaN          2   \n",
       "...                                                  ...        ...   \n",
       "34116  Medical History/Concurrent Conditions: Aortic ...          2   \n",
       "34117  Medical History/Concurrent Conditions: Atrial ...          2   \n",
       "34118  Medical History/Concurrent Conditions: Type II...          2   \n",
       "34119  Medical History/Concurrent Conditions: No adve...          2   \n",
       "34120  Comments: List of non-encoded Patient Relevant...          2   \n",
       "\n",
       "      TODAYS_DATE                                          ALLERGIES  \n",
       "0      2021-01-01                                  Pcn and bee venom  \n",
       "1      2021-01-01                                            \"Dairy\"  \n",
       "2      2021-01-01                                          Shellfish  \n",
       "3      2021-01-01  Diclofenac, novacaine, lidocaine, pickles, tom...  \n",
       "4      2021-01-01                                                 Na  \n",
       "...           ...                                                ...  \n",
       "34116  2021-03-12                                                NaN  \n",
       "34117  2021-03-16                                                NaN  \n",
       "34118  2021-03-12                                                NaN  \n",
       "34119  2021-03-15                                                NaN  \n",
       "34120  2021-03-12                                                NaN  \n",
       "\n",
       "[34121 rows x 17 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dates = ['VAX_DATE', 'ONSET_DATE','TODAYS_DATE']\n",
    "\n",
    "for date in dates:\n",
    "    vax_data[date] = pd.to_datetime(vax_data[date])\n",
    "\n",
    "vax_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "VAERS_ID                int64\n",
       "STATE                  object\n",
       "AGE_YRS               float64\n",
       "SEX                    object\n",
       "DIED                   object\n",
       "HOSPITAL               object\n",
       "RECOVD                 object\n",
       "VAX_DATE       datetime64[ns]\n",
       "ONSET_DATE     datetime64[ns]\n",
       "NUMDAYS               float64\n",
       "V_ADMINBY              object\n",
       "OTHER_MEDS             object\n",
       "CUR_ILL                object\n",
       "HISTORY                object\n",
       "FORM_VERS               int64\n",
       "TODAYS_DATE    datetime64[ns]\n",
       "ALLERGIES              object\n",
       "dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1957"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# total deaths in the data\n",
    "#Certain deaths are prior to the ivention of the vaccine\n",
    "\n",
    "\n",
    "vax_data[\"DIED\"].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df = vax_data.groupby([\"STATE\"]).count()[\"DIED\"]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filter the deaths by only keeping the vaccine date from 12/1/21\n",
    "vax_data = vax_data.loc[(vax_data['VAX_DATE'] >= '2020-12-1')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>VAERS_ID</th>\n",
       "      <th>STATE</th>\n",
       "      <th>AGE_YRS</th>\n",
       "      <th>SEX</th>\n",
       "      <th>DIED</th>\n",
       "      <th>HOSPITAL</th>\n",
       "      <th>RECOVD</th>\n",
       "      <th>VAX_DATE</th>\n",
       "      <th>ONSET_DATE</th>\n",
       "      <th>NUMDAYS</th>\n",
       "      <th>V_ADMINBY</th>\n",
       "      <th>OTHER_MEDS</th>\n",
       "      <th>CUR_ILL</th>\n",
       "      <th>HISTORY</th>\n",
       "      <th>FORM_VERS</th>\n",
       "      <th>TODAYS_DATE</th>\n",
       "      <th>ALLERGIES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>916600</td>\n",
       "      <td>TX</td>\n",
       "      <td>33.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>2020-12-28</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2.0</td>\n",
       "      <td>PVT</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>Pcn and bee venom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>916601</td>\n",
       "      <td>CA</td>\n",
       "      <td>73.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SEN</td>\n",
       "      <td>Patient residing at nursing facility. See pati...</td>\n",
       "      <td>Patient residing at nursing facility. See pati...</td>\n",
       "      <td>Patient residing at nursing facility. See pati...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>\"Dairy\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>916602</td>\n",
       "      <td>WA</td>\n",
       "      <td>23.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>U</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SEN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>Shellfish</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>916603</td>\n",
       "      <td>WA</td>\n",
       "      <td>58.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>2020-12-23</td>\n",
       "      <td>2020-12-23</td>\n",
       "      <td>0.0</td>\n",
       "      <td>WRK</td>\n",
       "      <td>none</td>\n",
       "      <td>kidney infection</td>\n",
       "      <td>diverticulitis, mitral valve prolapse, osteoar...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>Diclofenac, novacaine, lidocaine, pickles, tom...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>916604</td>\n",
       "      <td>TX</td>\n",
       "      <td>47.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>2020-12-22</td>\n",
       "      <td>2020-12-29</td>\n",
       "      <td>7.0</td>\n",
       "      <td>PUB</td>\n",
       "      <td>Na</td>\n",
       "      <td>Na</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>Na</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VAERS_ID STATE  AGE_YRS SEX DIED HOSPITAL RECOVD   VAX_DATE ONSET_DATE  \\\n",
       "0    916600    TX     33.0   F  NaN      NaN      Y 2020-12-28 2020-12-30   \n",
       "1    916601    CA     73.0   F  NaN      NaN      Y 2020-12-31 2020-12-31   \n",
       "2    916602    WA     23.0   F  NaN      NaN      U 2020-12-31 2020-12-31   \n",
       "3    916603    WA     58.0   F  NaN      NaN      Y 2020-12-23 2020-12-23   \n",
       "4    916604    TX     47.0   F  NaN      NaN      N 2020-12-22 2020-12-29   \n",
       "\n",
       "   NUMDAYS V_ADMINBY                                         OTHER_MEDS  \\\n",
       "0      2.0       PVT                                               None   \n",
       "1      0.0       SEN  Patient residing at nursing facility. See pati...   \n",
       "2      0.0       SEN                                               None   \n",
       "3      0.0       WRK                                               none   \n",
       "4      7.0       PUB                                                 Na   \n",
       "\n",
       "                                             CUR_ILL  \\\n",
       "0                                               None   \n",
       "1  Patient residing at nursing facility. See pati...   \n",
       "2                                               None   \n",
       "3                                   kidney infection   \n",
       "4                                                 Na   \n",
       "\n",
       "                                             HISTORY  FORM_VERS TODAYS_DATE  \\\n",
       "0                                               None          2  2021-01-01   \n",
       "1  Patient residing at nursing facility. See pati...          2  2021-01-01   \n",
       "2                                               None          2  2021-01-01   \n",
       "3  diverticulitis, mitral valve prolapse, osteoar...          2  2021-01-01   \n",
       "4                                                NaN          2  2021-01-01   \n",
       "\n",
       "                                           ALLERGIES  \n",
       "0                                  Pcn and bee venom  \n",
       "1                                            \"Dairy\"  \n",
       "2                                          Shellfish  \n",
       "3  Diclofenac, novacaine, lidocaine, pickles, tom...  \n",
       "4                                                 Na  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_data.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1858"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# No. of people died post vaccination\n",
    "vax_data[\"DIED\"].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for duplicates\n",
    "vax_data.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>VAERS_ID</th>\n",
       "      <th>VAX_TYPE</th>\n",
       "      <th>VAX_MANU</th>\n",
       "      <th>VAX_LOT</th>\n",
       "      <th>VAX_DOSE_SERIES</th>\n",
       "      <th>VAX_ROUTE</th>\n",
       "      <th>VAX_SITE</th>\n",
       "      <th>VAX_NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>916600</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>037K20A</td>\n",
       "      <td>1</td>\n",
       "      <td>IM</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>916601</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>025L20A</td>\n",
       "      <td>1</td>\n",
       "      <td>IM</td>\n",
       "      <td>RA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>916602</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>EL1284</td>\n",
       "      <td>1</td>\n",
       "      <td>IM</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>916603</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>unknown</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>916604</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>IM</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VAERS_ID VAX_TYPE         VAX_MANU  VAX_LOT VAX_DOSE_SERIES VAX_ROUTE  \\\n",
       "0    916600  COVID19          MODERNA  037K20A               1        IM   \n",
       "1    916601  COVID19          MODERNA  025L20A               1        IM   \n",
       "2    916602  COVID19  PFIZER\\BIONTECH   EL1284               1        IM   \n",
       "3    916603  COVID19          MODERNA  unknown             UNK       NaN   \n",
       "4    916604  COVID19          MODERNA      NaN               1        IM   \n",
       "\n",
       "  VAX_SITE                             VAX_NAME  \n",
       "0       LA          COVID19 (COVID19 (MODERNA))  \n",
       "1       RA          COVID19 (COVID19 (MODERNA))  \n",
       "2       LA  COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "3      NaN          COVID19 (COVID19 (MODERNA))  \n",
       "4       LA          COVID19 (COVID19 (MODERNA))  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_type.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "34630"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(vax_type)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['VAERS_ID', 'VAX_TYPE', 'VAX_MANU', 'VAX_LOT', 'VAX_DOSE_SERIES',\n",
       "       'VAX_ROUTE', 'VAX_SITE', 'VAX_NAME'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_type.columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "VAERS_ID               0\n",
       "VAX_TYPE               0\n",
       "VAX_MANU               0\n",
       "VAX_LOT            11117\n",
       "VAX_DOSE_SERIES       62\n",
       "VAX_SITE            7943\n",
       "VAX_NAME               0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_type[['VAERS_ID', 'VAX_TYPE', 'VAX_MANU', 'VAX_LOT', 'VAX_DOSE_SERIES',\n",
    "       'VAX_SITE', 'VAX_NAME']].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop unrequired columns\n",
    "vax_type = vax_type.drop(['VAX_ROUTE'],  axis = 1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "37"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_type.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": 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>VAERS_ID</th>\n",
       "      <th>VAX_TYPE</th>\n",
       "      <th>VAX_MANU</th>\n",
       "      <th>VAX_LOT</th>\n",
       "      <th>VAX_DOSE_SERIES</th>\n",
       "      <th>VAX_SITE</th>\n",
       "      <th>VAX_NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2453</th>\n",
       "      <td>919453</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>EL1284</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4939</th>\n",
       "      <td>922377</td>\n",
       "      <td>VARZOS</td>\n",
       "      <td>GLAXOSMITHKLINE BIOLOGICALS</td>\n",
       "      <td>UNK</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ZOSTER (SHINGRIX)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5765</th>\n",
       "      <td>923331</td>\n",
       "      <td>UNK</td>\n",
       "      <td>UNKNOWN MANUFACTURER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VACCINE NOT SPECIFIED (NO BRAND NAME)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6294</th>\n",
       "      <td>923893</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>EL0142</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12391</th>\n",
       "      <td>930664</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>037K20A</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13675</th>\n",
       "      <td>932017</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>026L20A</td>\n",
       "      <td>1</td>\n",
       "      <td>RA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13845</th>\n",
       "      <td>932199</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>026L20A</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14413</th>\n",
       "      <td>932800</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14885</th>\n",
       "      <td>933330</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>EL3246</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15076</th>\n",
       "      <td>933540</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15412</th>\n",
       "      <td>933901</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15539</th>\n",
       "      <td>934042</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16352</th>\n",
       "      <td>937480</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>011J20A</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18018</th>\n",
       "      <td>957227</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>037K20A</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18189</th>\n",
       "      <td>959928</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>012120a</td>\n",
       "      <td>2</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18570</th>\n",
       "      <td>965658</td>\n",
       "      <td>UNK</td>\n",
       "      <td>UNKNOWN MANUFACTURER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VACCINE NOT SPECIFIED (NO BRAND NAME)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18672</th>\n",
       "      <td>968195</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19341</th>\n",
       "      <td>977100</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>RA</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21791</th>\n",
       "      <td>1007357</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>RA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22083</th>\n",
       "      <td>1011075</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>ENS318</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22578</th>\n",
       "      <td>1017843</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24276</th>\n",
       "      <td>1041385</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>037K20A</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24411</th>\n",
       "      <td>1044252</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>RA</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24420</th>\n",
       "      <td>1044420</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>AR</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24628</th>\n",
       "      <td>1045638</td>\n",
       "      <td>UNK</td>\n",
       "      <td>UNKNOWN MANUFACTURER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VACCINE NOT SPECIFIED (NO BRAND NAME)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24892</th>\n",
       "      <td>1046006</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24893</th>\n",
       "      <td>1046006</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>RA</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25061</th>\n",
       "      <td>1046183</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>023M20A</td>\n",
       "      <td>2</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27041</th>\n",
       "      <td>1049065</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>RA</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27835</th>\n",
       "      <td>1049944</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>EN5318</td>\n",
       "      <td>2</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29253</th>\n",
       "      <td>1058447</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>038K20A</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30124</th>\n",
       "      <td>1065712</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30741</th>\n",
       "      <td>1069051</td>\n",
       "      <td>UNK</td>\n",
       "      <td>UNKNOWN MANUFACTURER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VACCINE NOT SPECIFIED (NO BRAND NAME)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32034</th>\n",
       "      <td>1079930</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>JANSSEN</td>\n",
       "      <td>1805022</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (JANSSEN))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33518</th>\n",
       "      <td>1088457</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>042L20A</td>\n",
       "      <td>2</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33584</th>\n",
       "      <td>1089592</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33840</th>\n",
       "      <td>1093421</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       VAERS_ID VAX_TYPE                     VAX_MANU  VAX_LOT  \\\n",
       "2453     919453  COVID19              PFIZER\\BIONTECH   EL1284   \n",
       "4939     922377   VARZOS  GLAXOSMITHKLINE BIOLOGICALS      UNK   \n",
       "5765     923331      UNK         UNKNOWN MANUFACTURER      NaN   \n",
       "6294     923893  COVID19              PFIZER\\BIONTECH   EL0142   \n",
       "12391    930664  COVID19                      MODERNA  037K20A   \n",
       "13675    932017  COVID19                      MODERNA  026L20A   \n",
       "13845    932199  COVID19                      MODERNA  026L20A   \n",
       "14413    932800  COVID19              PFIZER\\BIONTECH      NaN   \n",
       "14885    933330  COVID19              PFIZER\\BIONTECH   EL3246   \n",
       "15076    933540  COVID19              PFIZER\\BIONTECH      NaN   \n",
       "15412    933901  COVID19              PFIZER\\BIONTECH      NaN   \n",
       "15539    934042  COVID19              PFIZER\\BIONTECH      NaN   \n",
       "16352    937480  COVID19                      MODERNA  011J20A   \n",
       "18018    957227  COVID19                      MODERNA  037K20A   \n",
       "18189    959928  COVID19                      MODERNA  012120a   \n",
       "18570    965658      UNK         UNKNOWN MANUFACTURER      NaN   \n",
       "18672    968195  COVID19                      MODERNA      NaN   \n",
       "19341    977100  COVID19              PFIZER\\BIONTECH      NaN   \n",
       "21791   1007357  COVID19                      MODERNA      NaN   \n",
       "22083   1011075  COVID19              PFIZER\\BIONTECH   ENS318   \n",
       "22578   1017843  COVID19                      MODERNA      NaN   \n",
       "24276   1041385  COVID19                      MODERNA  037K20A   \n",
       "24411   1044252  COVID19              PFIZER\\BIONTECH      NaN   \n",
       "24420   1044420  COVID19              PFIZER\\BIONTECH      NaN   \n",
       "24628   1045638      UNK         UNKNOWN MANUFACTURER      NaN   \n",
       "24892   1046006  COVID19              PFIZER\\BIONTECH      NaN   \n",
       "24893   1046006  COVID19              PFIZER\\BIONTECH      NaN   \n",
       "25061   1046183  COVID19                      MODERNA  023M20A   \n",
       "27041   1049065  COVID19              PFIZER\\BIONTECH      NaN   \n",
       "27835   1049944  COVID19              PFIZER\\BIONTECH   EN5318   \n",
       "29253   1058447  COVID19                      MODERNA  038K20A   \n",
       "30124   1065712  COVID19                      MODERNA      NaN   \n",
       "30741   1069051      UNK         UNKNOWN MANUFACTURER      NaN   \n",
       "32034   1079930  COVID19                      JANSSEN  1805022   \n",
       "33518   1088457  COVID19                      MODERNA  042L20A   \n",
       "33584   1089592  COVID19              PFIZER\\BIONTECH      NaN   \n",
       "33840   1093421  COVID19                      MODERNA      NaN   \n",
       "\n",
       "      VAX_DOSE_SERIES VAX_SITE                               VAX_NAME  \n",
       "2453                1       LA    COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "4939              UNK      NaN                      ZOSTER (SHINGRIX)  \n",
       "5765              UNK      NaN  VACCINE NOT SPECIFIED (NO BRAND NAME)  \n",
       "6294                1      NaN    COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "12391               1       LA            COVID19 (COVID19 (MODERNA))  \n",
       "13675               1       RA            COVID19 (COVID19 (MODERNA))  \n",
       "13845               1       LA            COVID19 (COVID19 (MODERNA))  \n",
       "14413             UNK       LA    COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "14885               1       LA    COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "15076             UNK      NaN    COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "15412             UNK      NaN    COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "15539             UNK      NaN    COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "16352               1       LA            COVID19 (COVID19 (MODERNA))  \n",
       "18018               1       LA            COVID19 (COVID19 (MODERNA))  \n",
       "18189               2       LA            COVID19 (COVID19 (MODERNA))  \n",
       "18570             UNK      NaN  VACCINE NOT SPECIFIED (NO BRAND NAME)  \n",
       "18672               1       LA            COVID19 (COVID19 (MODERNA))  \n",
       "19341             UNK       RA    COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "21791               1       RA            COVID19 (COVID19 (MODERNA))  \n",
       "22083               1      NaN    COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "22578               1      NaN            COVID19 (COVID19 (MODERNA))  \n",
       "24276               1       LA            COVID19 (COVID19 (MODERNA))  \n",
       "24411               1       RA    COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "24420               2       AR    COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "24628             UNK      NaN  VACCINE NOT SPECIFIED (NO BRAND NAME)  \n",
       "24892             UNK       LA    COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "24893             UNK       RA    COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "25061               2       LA            COVID19 (COVID19 (MODERNA))  \n",
       "27041               2       RA    COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "27835               2       LA    COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "29253               1       LA            COVID19 (COVID19 (MODERNA))  \n",
       "30124             UNK      NaN            COVID19 (COVID19 (MODERNA))  \n",
       "30741             UNK      NaN  VACCINE NOT SPECIFIED (NO BRAND NAME)  \n",
       "32034               1       LA            COVID19 (COVID19 (JANSSEN))  \n",
       "33518               2       LA            COVID19 (COVID19 (MODERNA))  \n",
       "33584               1      NaN    COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "33840             UNK      NaN            COVID19 (COVID19 (MODERNA))  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_type[vax_type.duplicated() == True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "vax_type = vax_type.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_type.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['COVID19 (COVID19 (MODERNA))',\n",
       "       'COVID19 (COVID19 (PFIZER-BIONTECH))',\n",
       "       'VACCINE NOT SPECIFIED (NO BRAND NAME)',\n",
       "       'INFLUENZA (SEASONAL) (FLUZONE HIGH-DOSE QUADRIVALENT)',\n",
       "       'DTAP + HEPB + IPV (PEDIARIX)', 'HIB (ACTHIB)',\n",
       "       'ROTAVIRUS (ROTARIX)', 'VACCINE NOT SPECIFIED (OTHER)',\n",
       "       'INFLUENZA (SEASONAL) (AFLURIA QUADRIVALENT)',\n",
       "       'INFLUENZA (SEASONAL) (NO BRAND NAME)', 'HEP B (NO BRAND NAME)',\n",
       "       'MEASLES + MUMPS + RUBELLA (MMR II)', 'ZOSTER (SHINGRIX)',\n",
       "       'DTAP (NO BRAND NAME)', 'ZOSTER (NO BRAND NAME)',\n",
       "       'PNEUMO (PNEUMOVAX)', 'HPV (GARDASIL 9)', 'ZOSTER LIVE (ZOSTAVAX)',\n",
       "       'CHOLERA (NO BRAND NAME)', 'DT ADSORBED (NO BRAND NAME)',\n",
       "       'TETANUS TOXOID (NO BRAND NAME)', 'INFLUENZA (SEASONAL) (FLUZONE)',\n",
       "       'INFLUENZA (SEASONAL) (FLUCELVAX)',\n",
       "       'INFLUENZA (SEASONAL) (FLUCELVAX QUADRIVALENT)',\n",
       "       'HEP B (ENGERIX-B)', 'INFLUENZA (SEASONAL) (FLUZONE QUADRIVALENT)',\n",
       "       'PNEUMO (PREVNAR13)',\n",
       "       'INFLUENZA (SEASONAL) (FLUBLOK QUADRIVALENT)',\n",
       "       'MENINGOCOCCAL CONJUGATE (MENVEO)',\n",
       "       'MENINGOCOCCAL CONJUGATE (MENACTRA)', 'HPV (GARDASIL)',\n",
       "       'TDAP (NO BRAND NAME)', 'INFLUENZA (SEASONAL) (FLUAD)',\n",
       "       'TDAP (ADACEL)', 'HEP A (VAQTA)', 'VARICELLA (VARIVAX)',\n",
       "       'DTAP (DAPTACEL)', 'INFLUENZA (SEASONAL) (FLUAD QUADRIVALENT)',\n",
       "       'HEP B (RECOMBIVAX HB)',\n",
       "       'INFLUENZA (SEASONAL) (FLUARIX QUADRIVALENT)',\n",
       "       'PNEUMO (NO BRAND NAME)', 'COVID19 (COVID19 (UNKNOWN))',\n",
       "       'ANTHRAX (BIOTHRAX)', 'POLIO VIRUS, INACT. (IPOL)',\n",
       "       'TYPHOID VI POLYSACCHARIDE (TYPHIM VI)', 'HEP A (HAVRIX)',\n",
       "       'HPV (NO BRAND NAME)',\n",
       "       'INFLUENZA (SEASONAL) (FLULAVAL QUADRIVALENT)',\n",
       "       'DTP (NO BRAND NAME)', 'INFLUENZA (SEASONAL) (FLUZONE HIGH-DOSE)',\n",
       "       'DTAP + IPV + HIB (PENTACEL)', 'ROTAVIRUS (ROTATEQ)',\n",
       "       'MENINGOCOCCAL B (TRUMENBA)', 'HEP B (HEPLISAV-B)',\n",
       "       'TDAP (BOOSTRIX)', 'DTAP (INFANRIX)', 'HIB (PEDVAXHIB)',\n",
       "       'RABIES (RABAVERT)', 'MENINGOCOCCAL B (BEXSERO)',\n",
       "       'INFLUENZA (SEASONAL) (FLUMIST QUADRIVALENT)', 'PNEUMO (PREVNAR)',\n",
       "       'TYPHOID LIVE ORAL TY21A (VIVOTIF)', 'COVID19 (COVID19 (JANSSEN))',\n",
       "       'POLIO VIRUS, ORAL (NO BRAND NAME)', 'YELLOW FEVER (YF-VAX)'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_type['VAX_NAME'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['COVID19', 'UNK', 'FLU4', 'DTAPHEPBIP', 'HIBV', 'RV1', 'FLUX',\n",
       "       'HEP', 'MMR', 'VARZOS', 'DTAP', 'PPV', 'HPV9', 'CHOL', 'DT',\n",
       "       'TTOX', 'FLU3', 'FLUC3', 'FLUC4', 'PNC13', 'FLUR4', 'MNQ', 'HPV4',\n",
       "       'TDAP', 'FLUA3', 'HEPA', 'VARCEL', 'FLUA4', 'ANTH', 'IPV', 'TYP',\n",
       "       'HPVX', 'DTP', 'DTAPIPVHIB', 'RV5', 'MENB', 'RAB', 'FLUN4', 'PNC',\n",
       "       'OPV', 'YF'], dtype=object)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_type['VAX_TYPE'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>VAERS_ID</th>\n",
       "      <th>VAX_TYPE</th>\n",
       "      <th>VAX_MANU</th>\n",
       "      <th>VAX_LOT</th>\n",
       "      <th>VAX_DOSE_SERIES</th>\n",
       "      <th>VAX_SITE</th>\n",
       "      <th>VAX_NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>916600</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>037K20A</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>916601</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>025L20A</td>\n",
       "      <td>1</td>\n",
       "      <td>RA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>916602</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>EL1284</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>916603</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>unknown</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>916604</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34625</th>\n",
       "      <td>1113917</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>01120A</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34626</th>\n",
       "      <td>1113920</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34627</th>\n",
       "      <td>1113963</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>EL9264</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34628</th>\n",
       "      <td>1115045</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34629</th>\n",
       "      <td>1115348</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>026A21A</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>34142 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       VAERS_ID VAX_TYPE         VAX_MANU  VAX_LOT VAX_DOSE_SERIES VAX_SITE  \\\n",
       "0        916600  COVID19          MODERNA  037K20A               1       LA   \n",
       "1        916601  COVID19          MODERNA  025L20A               1       RA   \n",
       "2        916602  COVID19  PFIZER\\BIONTECH   EL1284               1       LA   \n",
       "3        916603  COVID19          MODERNA  unknown             UNK      NaN   \n",
       "4        916604  COVID19          MODERNA      NaN               1       LA   \n",
       "...         ...      ...              ...      ...             ...      ...   \n",
       "34625   1113917  COVID19          MODERNA   01120A               1      NaN   \n",
       "34626   1113920  COVID19  PFIZER\\BIONTECH  Unknown               1      NaN   \n",
       "34627   1113963  COVID19  PFIZER\\BIONTECH   EL9264               2      NaN   \n",
       "34628   1115045  COVID19          MODERNA      NaN               2      NaN   \n",
       "34629   1115348  COVID19          MODERNA  026A21A               1       LA   \n",
       "\n",
       "                                  VAX_NAME  \n",
       "0              COVID19 (COVID19 (MODERNA))  \n",
       "1              COVID19 (COVID19 (MODERNA))  \n",
       "2      COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "3              COVID19 (COVID19 (MODERNA))  \n",
       "4              COVID19 (COVID19 (MODERNA))  \n",
       "...                                    ...  \n",
       "34625          COVID19 (COVID19 (MODERNA))  \n",
       "34626  COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "34627  COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "34628          COVID19 (COVID19 (MODERNA))  \n",
       "34629          COVID19 (COVID19 (MODERNA))  \n",
       "\n",
       "[34142 rows x 7 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_type = vax_type[vax_type['VAX_TYPE'] == 'COVID19']\n",
    "vax_type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "VAERS_ID            int64\n",
       "VAX_TYPE           object\n",
       "VAX_MANU           object\n",
       "VAX_LOT            object\n",
       "VAX_DOSE_SERIES    object\n",
       "VAX_SITE           object\n",
       "VAX_NAME           object\n",
       "dtype: object"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_type.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>VAERS_ID</th>\n",
       "      <th>SYMPTOM1</th>\n",
       "      <th>SYMPTOMVERSION1</th>\n",
       "      <th>SYMPTOM2</th>\n",
       "      <th>SYMPTOMVERSION2</th>\n",
       "      <th>SYMPTOM3</th>\n",
       "      <th>SYMPTOMVERSION3</th>\n",
       "      <th>SYMPTOM4</th>\n",
       "      <th>SYMPTOMVERSION4</th>\n",
       "      <th>SYMPTOM5</th>\n",
       "      <th>SYMPTOMVERSION5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>916600</td>\n",
       "      <td>Dysphagia</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Epiglottitis</td>\n",
       "      <td>23.1</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>916601</td>\n",
       "      <td>Anxiety</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Dyspnoea</td>\n",
       "      <td>23.1</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>2</th>\n",
       "      <td>916602</td>\n",
       "      <td>Chest discomfort</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Dysphagia</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Pain in extremity</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Visual impairment</td>\n",
       "      <td>23.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>916603</td>\n",
       "      <td>Dizziness</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Mobility decreased</td>\n",
       "      <td>23.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>916604</td>\n",
       "      <td>Injection site erythema</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Injection site pruritus</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Injection site swelling</td>\n",
       "      <td>23.1</td>\n",
       "      <td>Injection site warmth</td>\n",
       "      <td>23.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VAERS_ID                 SYMPTOM1  SYMPTOMVERSION1  \\\n",
       "0    916600                Dysphagia             23.1   \n",
       "1    916601                  Anxiety             23.1   \n",
       "2    916602         Chest discomfort             23.1   \n",
       "3    916603                Dizziness             23.1   \n",
       "4    916604  Injection site erythema             23.1   \n",
       "\n",
       "                  SYMPTOM2  SYMPTOMVERSION2                 SYMPTOM3  \\\n",
       "0             Epiglottitis             23.1                      NaN   \n",
       "1                 Dyspnoea             23.1                      NaN   \n",
       "2                Dysphagia             23.1        Pain in extremity   \n",
       "3                  Fatigue             23.1       Mobility decreased   \n",
       "4  Injection site pruritus             23.1  Injection site swelling   \n",
       "\n",
       "   SYMPTOMVERSION3               SYMPTOM4  SYMPTOMVERSION4 SYMPTOM5  \\\n",
       "0              NaN                    NaN              NaN      NaN   \n",
       "1              NaN                    NaN              NaN      NaN   \n",
       "2             23.1      Visual impairment             23.1      NaN   \n",
       "3             23.1                    NaN              NaN      NaN   \n",
       "4             23.1  Injection site warmth             23.1      NaN   \n",
       "\n",
       "   SYMPTOMVERSION5  \n",
       "0              NaN  \n",
       "1              NaN  \n",
       "2              NaN  \n",
       "3              NaN  \n",
       "4              NaN  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_symptoms.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['VAERS_ID', 'SYMPTOM1', 'SYMPTOMVERSION1', 'SYMPTOM2',\n",
       "       'SYMPTOMVERSION2', 'SYMPTOM3', 'SYMPTOMVERSION3', 'SYMPTOM4',\n",
       "       'SYMPTOMVERSION4', 'SYMPTOM5', 'SYMPTOMVERSION5'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_symptoms.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "VAERS_ID        0\n",
       "SYMPTOM1        0\n",
       "SYMPTOM2     8759\n",
       "SYMPTOM3    16568\n",
       "SYMPTOM4    23666\n",
       "SYMPTOM5    29550\n",
       "dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_symptoms[['VAERS_ID', 'SYMPTOM1', 'SYMPTOM2', 'SYMPTOM3', 'SYMPTOM4', 'SYMPTOM5']].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "vax_symptoms = vax_symptoms.drop([\"SYMPTOMVERSION1\",\"SYMPTOMVERSION2\",\"SYMPTOMVERSION3\",\"SYMPTOMVERSION4\",\"SYMPTOMVERSION5\", \"SYMPTOM4\", \"SYMPTOM5\"], axis =1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "VAERS_ID     int64\n",
       "SYMPTOM1    object\n",
       "SYMPTOM2    object\n",
       "SYMPTOM3    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_symptoms.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vax_symptoms.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = vax_data.merge(vax_type,on='VAERS_ID').merge(vax_symptoms,on='VAERS_ID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>VAERS_ID</th>\n",
       "      <th>STATE</th>\n",
       "      <th>AGE_YRS</th>\n",
       "      <th>SEX</th>\n",
       "      <th>DIED</th>\n",
       "      <th>HOSPITAL</th>\n",
       "      <th>RECOVD</th>\n",
       "      <th>VAX_DATE</th>\n",
       "      <th>ONSET_DATE</th>\n",
       "      <th>NUMDAYS</th>\n",
       "      <th>...</th>\n",
       "      <th>ALLERGIES</th>\n",
       "      <th>VAX_TYPE</th>\n",
       "      <th>VAX_MANU</th>\n",
       "      <th>VAX_LOT</th>\n",
       "      <th>VAX_DOSE_SERIES</th>\n",
       "      <th>VAX_SITE</th>\n",
       "      <th>VAX_NAME</th>\n",
       "      <th>SYMPTOM1</th>\n",
       "      <th>SYMPTOM2</th>\n",
       "      <th>SYMPTOM3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>916600</td>\n",
       "      <td>TX</td>\n",
       "      <td>33.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>2020-12-28</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Pcn and bee venom</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>037K20A</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "      <td>Dysphagia</td>\n",
       "      <td>Epiglottitis</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>916601</td>\n",
       "      <td>CA</td>\n",
       "      <td>73.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>\"Dairy\"</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>025L20A</td>\n",
       "      <td>1</td>\n",
       "      <td>RA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "      <td>Anxiety</td>\n",
       "      <td>Dyspnoea</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>916602</td>\n",
       "      <td>WA</td>\n",
       "      <td>23.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>U</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Shellfish</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>EL1284</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "      <td>Chest discomfort</td>\n",
       "      <td>Dysphagia</td>\n",
       "      <td>Pain in extremity</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>916603</td>\n",
       "      <td>WA</td>\n",
       "      <td>58.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>2020-12-23</td>\n",
       "      <td>2020-12-23</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Diclofenac, novacaine, lidocaine, pickles, tom...</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>unknown</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "      <td>Dizziness</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>Mobility decreased</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>916604</td>\n",
       "      <td>TX</td>\n",
       "      <td>47.0</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>2020-12-22</td>\n",
       "      <td>2020-12-29</td>\n",
       "      <td>7.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Na</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>LA</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "      <td>Injection site erythema</td>\n",
       "      <td>Injection site pruritus</td>\n",
       "      <td>Injection site swelling</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   VAERS_ID STATE  AGE_YRS SEX DIED HOSPITAL RECOVD   VAX_DATE ONSET_DATE  \\\n",
       "0    916600    TX     33.0   F  NaN      NaN      Y 2020-12-28 2020-12-30   \n",
       "1    916601    CA     73.0   F  NaN      NaN      Y 2020-12-31 2020-12-31   \n",
       "2    916602    WA     23.0   F  NaN      NaN      U 2020-12-31 2020-12-31   \n",
       "3    916603    WA     58.0   F  NaN      NaN      Y 2020-12-23 2020-12-23   \n",
       "4    916604    TX     47.0   F  NaN      NaN      N 2020-12-22 2020-12-29   \n",
       "\n",
       "   NUMDAYS  ...                                          ALLERGIES VAX_TYPE  \\\n",
       "0      2.0  ...                                  Pcn and bee venom  COVID19   \n",
       "1      0.0  ...                                            \"Dairy\"  COVID19   \n",
       "2      0.0  ...                                          Shellfish  COVID19   \n",
       "3      0.0  ...  Diclofenac, novacaine, lidocaine, pickles, tom...  COVID19   \n",
       "4      7.0  ...                                                 Na  COVID19   \n",
       "\n",
       "          VAX_MANU  VAX_LOT  VAX_DOSE_SERIES VAX_SITE  \\\n",
       "0          MODERNA  037K20A                1       LA   \n",
       "1          MODERNA  025L20A                1       RA   \n",
       "2  PFIZER\\BIONTECH   EL1284                1       LA   \n",
       "3          MODERNA  unknown              UNK      NaN   \n",
       "4          MODERNA      NaN                1       LA   \n",
       "\n",
       "                              VAX_NAME                 SYMPTOM1  \\\n",
       "0          COVID19 (COVID19 (MODERNA))                Dysphagia   \n",
       "1          COVID19 (COVID19 (MODERNA))                  Anxiety   \n",
       "2  COVID19 (COVID19 (PFIZER-BIONTECH))         Chest discomfort   \n",
       "3          COVID19 (COVID19 (MODERNA))                Dizziness   \n",
       "4          COVID19 (COVID19 (MODERNA))  Injection site erythema   \n",
       "\n",
       "                  SYMPTOM2                 SYMPTOM3  \n",
       "0             Epiglottitis                      NaN  \n",
       "1                 Dyspnoea                      NaN  \n",
       "2                Dysphagia        Pain in extremity  \n",
       "3                  Fatigue       Mobility decreased  \n",
       "4  Injection site pruritus  Injection site swelling  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "def change_col_names (old_name,new_name,data = final_df):\n",
    "    '''\n",
    "    change column names and add it to the dataframe\n",
    "    '''\n",
    "    if old_name in data.columns:\n",
    "        data[new_name] = data.pop(old_name)\n",
    "        \n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>STATE</th>\n",
       "      <th>SEX</th>\n",
       "      <th>DIED</th>\n",
       "      <th>HOSPITAL</th>\n",
       "      <th>RECOVD</th>\n",
       "      <th>VAX_DATE</th>\n",
       "      <th>ONSET_DATE</th>\n",
       "      <th>NUMDAYS</th>\n",
       "      <th>V_ADMINBY</th>\n",
       "      <th>OTHER_MEDS</th>\n",
       "      <th>...</th>\n",
       "      <th>VAX_LOT</th>\n",
       "      <th>VAX_SITE</th>\n",
       "      <th>SYMPTOM1</th>\n",
       "      <th>SYMPTOM2</th>\n",
       "      <th>SYMPTOM3</th>\n",
       "      <th>ID</th>\n",
       "      <th>AGE</th>\n",
       "      <th>MANUFACTURER</th>\n",
       "      <th>DOSE</th>\n",
       "      <th>VACCINE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TX</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>2020-12-28</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2.0</td>\n",
       "      <td>PVT</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>037K20A</td>\n",
       "      <td>LA</td>\n",
       "      <td>Dysphagia</td>\n",
       "      <td>Epiglottitis</td>\n",
       "      <td>NaN</td>\n",
       "      <td>916600</td>\n",
       "      <td>33.0</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CA</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SEN</td>\n",
       "      <td>Patient residing at nursing facility. See pati...</td>\n",
       "      <td>...</td>\n",
       "      <td>025L20A</td>\n",
       "      <td>RA</td>\n",
       "      <td>Anxiety</td>\n",
       "      <td>Dyspnoea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>916601</td>\n",
       "      <td>73.0</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WA</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>U</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SEN</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>EL1284</td>\n",
       "      <td>LA</td>\n",
       "      <td>Chest discomfort</td>\n",
       "      <td>Dysphagia</td>\n",
       "      <td>Pain in extremity</td>\n",
       "      <td>916602</td>\n",
       "      <td>23.0</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WA</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>2020-12-23</td>\n",
       "      <td>2020-12-23</td>\n",
       "      <td>0.0</td>\n",
       "      <td>WRK</td>\n",
       "      <td>none</td>\n",
       "      <td>...</td>\n",
       "      <td>unknown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dizziness</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>Mobility decreased</td>\n",
       "      <td>916603</td>\n",
       "      <td>58.0</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>UNK</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TX</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>2020-12-22</td>\n",
       "      <td>2020-12-29</td>\n",
       "      <td>7.0</td>\n",
       "      <td>PUB</td>\n",
       "      <td>Na</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LA</td>\n",
       "      <td>Injection site erythema</td>\n",
       "      <td>Injection site pruritus</td>\n",
       "      <td>Injection site swelling</td>\n",
       "      <td>916604</td>\n",
       "      <td>47.0</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46521</th>\n",
       "      <td>PA</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>2021-02-09</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>ELIQUIS; SYNTHROID; PRAVASTATIN; DILTIAZEM; DO...</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Nasal discomfort</td>\n",
       "      <td>Night sweats</td>\n",
       "      <td>Platelet count</td>\n",
       "      <td>1113920</td>\n",
       "      <td>83.0</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46522</th>\n",
       "      <td>PA</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>2021-02-09</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>ELIQUIS; SYNTHROID; PRAVASTATIN; DILTIAZEM; DO...</td>\n",
       "      <td>...</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rash</td>\n",
       "      <td>SARS-CoV-2 test</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1113920</td>\n",
       "      <td>83.0</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46523</th>\n",
       "      <td>MS</td>\n",
       "      <td>F</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>2021-02-05</td>\n",
       "      <td>2021-02-15</td>\n",
       "      <td>10.0</td>\n",
       "      <td>PVT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>EL9264</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Haemophagocytic lymphohistiocytosis</td>\n",
       "      <td>SARS-CoV-2 test</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1113963</td>\n",
       "      <td>59.0</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>2</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46524</th>\n",
       "      <td>AZ</td>\n",
       "      <td>M</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>2021-03-02</td>\n",
       "      <td>2021-03-02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Death</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1115045</td>\n",
       "      <td>57.0</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>2</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46525</th>\n",
       "      <td>NC</td>\n",
       "      <td>F</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>2021-03-05</td>\n",
       "      <td>2021-03-06</td>\n",
       "      <td>1.0</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>026A21A</td>\n",
       "      <td>LA</td>\n",
       "      <td>Death</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1115348</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>46526 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      STATE SEX DIED HOSPITAL RECOVD   VAX_DATE ONSET_DATE  NUMDAYS V_ADMINBY  \\\n",
       "0        TX   F  NaN      NaN      Y 2020-12-28 2020-12-30      2.0       PVT   \n",
       "1        CA   F  NaN      NaN      Y 2020-12-31 2020-12-31      0.0       SEN   \n",
       "2        WA   F  NaN      NaN      U 2020-12-31 2020-12-31      0.0       SEN   \n",
       "3        WA   F  NaN      NaN      Y 2020-12-23 2020-12-23      0.0       WRK   \n",
       "4        TX   F  NaN      NaN      N 2020-12-22 2020-12-29      7.0       PUB   \n",
       "...     ...  ..  ...      ...    ...        ...        ...      ...       ...   \n",
       "46521    PA   F  NaN      NaN      Y 2021-02-09        NaT      NaN       UNK   \n",
       "46522    PA   F  NaN      NaN      Y 2021-02-09        NaT      NaN       UNK   \n",
       "46523    MS   F    Y      NaN      N 2021-02-05 2021-02-15     10.0       PVT   \n",
       "46524    AZ   M    Y      NaN      N 2021-03-02 2021-03-02      0.0       UNK   \n",
       "46525    NC   F    Y      NaN      N 2021-03-05 2021-03-06      1.0       UNK   \n",
       "\n",
       "                                              OTHER_MEDS  ...  VAX_LOT  \\\n",
       "0                                                   None  ...  037K20A   \n",
       "1      Patient residing at nursing facility. See pati...  ...  025L20A   \n",
       "2                                                   None  ...   EL1284   \n",
       "3                                                   none  ...  unknown   \n",
       "4                                                     Na  ...      NaN   \n",
       "...                                                  ...  ...      ...   \n",
       "46521  ELIQUIS; SYNTHROID; PRAVASTATIN; DILTIAZEM; DO...  ...  Unknown   \n",
       "46522  ELIQUIS; SYNTHROID; PRAVASTATIN; DILTIAZEM; DO...  ...  Unknown   \n",
       "46523                                                NaN  ...   EL9264   \n",
       "46524                                                NaN  ...      NaN   \n",
       "46525                                                NaN  ...  026A21A   \n",
       "\n",
       "      VAX_SITE                             SYMPTOM1                 SYMPTOM2  \\\n",
       "0           LA                            Dysphagia             Epiglottitis   \n",
       "1           RA                              Anxiety                 Dyspnoea   \n",
       "2           LA                     Chest discomfort                Dysphagia   \n",
       "3          NaN                            Dizziness                  Fatigue   \n",
       "4           LA              Injection site erythema  Injection site pruritus   \n",
       "...        ...                                  ...                      ...   \n",
       "46521      NaN                     Nasal discomfort             Night sweats   \n",
       "46522      NaN                                 Rash          SARS-CoV-2 test   \n",
       "46523      NaN  Haemophagocytic lymphohistiocytosis          SARS-CoV-2 test   \n",
       "46524      NaN                                Death                      NaN   \n",
       "46525       LA                                Death                      NaN   \n",
       "\n",
       "                      SYMPTOM3       ID   AGE     MANUFACTURER DOSE  \\\n",
       "0                          NaN   916600  33.0          MODERNA    1   \n",
       "1                          NaN   916601  73.0          MODERNA    1   \n",
       "2            Pain in extremity   916602  23.0  PFIZER\\BIONTECH    1   \n",
       "3           Mobility decreased   916603  58.0          MODERNA  UNK   \n",
       "4      Injection site swelling   916604  47.0          MODERNA    1   \n",
       "...                        ...      ...   ...              ...  ...   \n",
       "46521           Platelet count  1113920  83.0  PFIZER\\BIONTECH    1   \n",
       "46522                      NaN  1113920  83.0  PFIZER\\BIONTECH    1   \n",
       "46523                      NaN  1113963  59.0  PFIZER\\BIONTECH    2   \n",
       "46524                      NaN  1115045  57.0          MODERNA    2   \n",
       "46525                      NaN  1115348   NaN          MODERNA    1   \n",
       "\n",
       "                                   VACCINE  \n",
       "0              COVID19 (COVID19 (MODERNA))  \n",
       "1              COVID19 (COVID19 (MODERNA))  \n",
       "2      COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "3              COVID19 (COVID19 (MODERNA))  \n",
       "4              COVID19 (COVID19 (MODERNA))  \n",
       "...                                    ...  \n",
       "46521  COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "46522  COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "46523  COVID19 (COVID19 (PFIZER-BIONTECH))  \n",
       "46524          COVID19 (COVID19 (MODERNA))  \n",
       "46525          COVID19 (COVID19 (MODERNA))  \n",
       "\n",
       "[46526 rows x 26 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "change_col_names(\"VAERS_ID\", \"ID\")\n",
    "\n",
    "change_col_names(\"AGE_YRS\", \"AGE\")\n",
    "\n",
    "\n",
    "change_col_names(\"VAX_MANU\", \"MANUFACTURER\")\n",
    "\n",
    "change_col_names(\"VAX_DOSE_SERIES\", \"DOSE\")\n",
    "\n",
    "change_col_names(\"VAX_NAME\", \"VACCINE\")\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reaaranging columns \n",
    "\n",
    "cols = ['ID','AGE', 'MANUFACTURER', 'DOSE','VACCINE','VAX_TYPE', 'VAX_LOT', 'VAX_SITE','SYMPTOM1', 'SYMPTOM2', 'SYMPTOM3','STATE', 'SEX', 'DIED', 'HOSPITAL', 'RECOVD', 'VAX_DATE', 'ONSET_DATE','NUMDAYS', 'V_ADMINBY', 'OTHER_MEDS', 'CUR_ILL', 'HISTORY', 'FORM_VERS','TODAYS_DATE', 'ALLERGIES']\n",
    "\n",
    "\n",
    "final_df = final_df[cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>ID</th>\n",
       "      <th>AGE</th>\n",
       "      <th>MANUFACTURER</th>\n",
       "      <th>DOSE</th>\n",
       "      <th>VACCINE</th>\n",
       "      <th>VAX_TYPE</th>\n",
       "      <th>VAX_LOT</th>\n",
       "      <th>VAX_SITE</th>\n",
       "      <th>SYMPTOM1</th>\n",
       "      <th>SYMPTOM2</th>\n",
       "      <th>...</th>\n",
       "      <th>VAX_DATE</th>\n",
       "      <th>ONSET_DATE</th>\n",
       "      <th>NUMDAYS</th>\n",
       "      <th>V_ADMINBY</th>\n",
       "      <th>OTHER_MEDS</th>\n",
       "      <th>CUR_ILL</th>\n",
       "      <th>HISTORY</th>\n",
       "      <th>FORM_VERS</th>\n",
       "      <th>TODAYS_DATE</th>\n",
       "      <th>ALLERGIES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>916600</td>\n",
       "      <td>33.0</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>037K20A</td>\n",
       "      <td>LA</td>\n",
       "      <td>Dysphagia</td>\n",
       "      <td>Epiglottitis</td>\n",
       "      <td>...</td>\n",
       "      <td>2020-12-28</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2.0</td>\n",
       "      <td>PVT</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>Pcn and bee venom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>916601</td>\n",
       "      <td>73.0</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>025L20A</td>\n",
       "      <td>RA</td>\n",
       "      <td>Anxiety</td>\n",
       "      <td>Dyspnoea</td>\n",
       "      <td>...</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SEN</td>\n",
       "      <td>Patient residing at nursing facility. See pati...</td>\n",
       "      <td>Patient residing at nursing facility. See pati...</td>\n",
       "      <td>Patient residing at nursing facility. See pati...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>\"Dairy\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>916602</td>\n",
       "      <td>23.0</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>EL1284</td>\n",
       "      <td>LA</td>\n",
       "      <td>Chest discomfort</td>\n",
       "      <td>Dysphagia</td>\n",
       "      <td>...</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SEN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>Shellfish</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>916603</td>\n",
       "      <td>58.0</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>UNK</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>unknown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dizziness</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>...</td>\n",
       "      <td>2020-12-23</td>\n",
       "      <td>2020-12-23</td>\n",
       "      <td>0.0</td>\n",
       "      <td>WRK</td>\n",
       "      <td>none</td>\n",
       "      <td>kidney infection</td>\n",
       "      <td>diverticulitis, mitral valve prolapse, osteoar...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>Diclofenac, novacaine, lidocaine, pickles, tom...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>916604</td>\n",
       "      <td>47.0</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LA</td>\n",
       "      <td>Injection site erythema</td>\n",
       "      <td>Injection site pruritus</td>\n",
       "      <td>...</td>\n",
       "      <td>2020-12-22</td>\n",
       "      <td>2020-12-29</td>\n",
       "      <td>7.0</td>\n",
       "      <td>PUB</td>\n",
       "      <td>Na</td>\n",
       "      <td>Na</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>Na</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       ID   AGE     MANUFACTURER DOSE                              VACCINE  \\\n",
       "0  916600  33.0          MODERNA    1          COVID19 (COVID19 (MODERNA))   \n",
       "1  916601  73.0          MODERNA    1          COVID19 (COVID19 (MODERNA))   \n",
       "2  916602  23.0  PFIZER\\BIONTECH    1  COVID19 (COVID19 (PFIZER-BIONTECH))   \n",
       "3  916603  58.0          MODERNA  UNK          COVID19 (COVID19 (MODERNA))   \n",
       "4  916604  47.0          MODERNA    1          COVID19 (COVID19 (MODERNA))   \n",
       "\n",
       "  VAX_TYPE  VAX_LOT VAX_SITE                 SYMPTOM1  \\\n",
       "0  COVID19  037K20A       LA                Dysphagia   \n",
       "1  COVID19  025L20A       RA                  Anxiety   \n",
       "2  COVID19   EL1284       LA         Chest discomfort   \n",
       "3  COVID19  unknown      NaN                Dizziness   \n",
       "4  COVID19      NaN       LA  Injection site erythema   \n",
       "\n",
       "                  SYMPTOM2  ...   VAX_DATE ONSET_DATE NUMDAYS V_ADMINBY  \\\n",
       "0             Epiglottitis  ... 2020-12-28 2020-12-30     2.0       PVT   \n",
       "1                 Dyspnoea  ... 2020-12-31 2020-12-31     0.0       SEN   \n",
       "2                Dysphagia  ... 2020-12-31 2020-12-31     0.0       SEN   \n",
       "3                  Fatigue  ... 2020-12-23 2020-12-23     0.0       WRK   \n",
       "4  Injection site pruritus  ... 2020-12-22 2020-12-29     7.0       PUB   \n",
       "\n",
       "                                          OTHER_MEDS  \\\n",
       "0                                               None   \n",
       "1  Patient residing at nursing facility. See pati...   \n",
       "2                                               None   \n",
       "3                                               none   \n",
       "4                                                 Na   \n",
       "\n",
       "                                             CUR_ILL  \\\n",
       "0                                               None   \n",
       "1  Patient residing at nursing facility. See pati...   \n",
       "2                                               None   \n",
       "3                                   kidney infection   \n",
       "4                                                 Na   \n",
       "\n",
       "                                             HISTORY FORM_VERS  TODAYS_DATE  \\\n",
       "0                                               None         2   2021-01-01   \n",
       "1  Patient residing at nursing facility. See pati...         2   2021-01-01   \n",
       "2                                               None         2   2021-01-01   \n",
       "3  diverticulitis, mitral valve prolapse, osteoar...         2   2021-01-01   \n",
       "4                                                NaN         2   2021-01-01   \n",
       "\n",
       "                                           ALLERGIES  \n",
       "0                                  Pcn and bee venom  \n",
       "1                                            \"Dairy\"  \n",
       "2                                          Shellfish  \n",
       "3  Diclofenac, novacaine, lidocaine, pickles, tom...  \n",
       "4                                                 Na  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.index = final_df.index+1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>ID</th>\n",
       "      <th>AGE</th>\n",
       "      <th>MANUFACTURER</th>\n",
       "      <th>DOSE</th>\n",
       "      <th>VACCINE</th>\n",
       "      <th>VAX_TYPE</th>\n",
       "      <th>VAX_LOT</th>\n",
       "      <th>VAX_SITE</th>\n",
       "      <th>SYMPTOM1</th>\n",
       "      <th>SYMPTOM2</th>\n",
       "      <th>...</th>\n",
       "      <th>VAX_DATE</th>\n",
       "      <th>ONSET_DATE</th>\n",
       "      <th>NUMDAYS</th>\n",
       "      <th>V_ADMINBY</th>\n",
       "      <th>OTHER_MEDS</th>\n",
       "      <th>CUR_ILL</th>\n",
       "      <th>HISTORY</th>\n",
       "      <th>FORM_VERS</th>\n",
       "      <th>TODAYS_DATE</th>\n",
       "      <th>ALLERGIES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>916600</td>\n",
       "      <td>33.0</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>037K20A</td>\n",
       "      <td>LA</td>\n",
       "      <td>Dysphagia</td>\n",
       "      <td>Epiglottitis</td>\n",
       "      <td>...</td>\n",
       "      <td>2020-12-28</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2.0</td>\n",
       "      <td>PVT</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>Pcn and bee venom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>916601</td>\n",
       "      <td>73.0</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>025L20A</td>\n",
       "      <td>RA</td>\n",
       "      <td>Anxiety</td>\n",
       "      <td>Dyspnoea</td>\n",
       "      <td>...</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SEN</td>\n",
       "      <td>Patient residing at nursing facility. See pati...</td>\n",
       "      <td>Patient residing at nursing facility. See pati...</td>\n",
       "      <td>Patient residing at nursing facility. See pati...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>\"Dairy\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>916602</td>\n",
       "      <td>23.0</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>EL1284</td>\n",
       "      <td>LA</td>\n",
       "      <td>Chest discomfort</td>\n",
       "      <td>Dysphagia</td>\n",
       "      <td>...</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SEN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>Shellfish</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>916603</td>\n",
       "      <td>58.0</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>UNK</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>unknown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dizziness</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>...</td>\n",
       "      <td>2020-12-23</td>\n",
       "      <td>2020-12-23</td>\n",
       "      <td>0.0</td>\n",
       "      <td>WRK</td>\n",
       "      <td>none</td>\n",
       "      <td>kidney infection</td>\n",
       "      <td>diverticulitis, mitral valve prolapse, osteoar...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>Diclofenac, novacaine, lidocaine, pickles, tom...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>916604</td>\n",
       "      <td>47.0</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LA</td>\n",
       "      <td>Injection site erythema</td>\n",
       "      <td>Injection site pruritus</td>\n",
       "      <td>...</td>\n",
       "      <td>2020-12-22</td>\n",
       "      <td>2020-12-29</td>\n",
       "      <td>7.0</td>\n",
       "      <td>PUB</td>\n",
       "      <td>Na</td>\n",
       "      <td>Na</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>Na</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46522</th>\n",
       "      <td>1113920</td>\n",
       "      <td>83.0</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Nasal discomfort</td>\n",
       "      <td>Night sweats</td>\n",
       "      <td>...</td>\n",
       "      <td>2021-02-09</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>ELIQUIS; SYNTHROID; PRAVASTATIN; DILTIAZEM; DO...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medical History/Concurrent Conditions: Atrial ...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-03-16</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46523</th>\n",
       "      <td>1113920</td>\n",
       "      <td>83.0</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rash</td>\n",
       "      <td>SARS-CoV-2 test</td>\n",
       "      <td>...</td>\n",
       "      <td>2021-02-09</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNK</td>\n",
       "      <td>ELIQUIS; SYNTHROID; PRAVASTATIN; DILTIAZEM; DO...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medical History/Concurrent Conditions: Atrial ...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-03-16</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46524</th>\n",
       "      <td>1113963</td>\n",
       "      <td>59.0</td>\n",
       "      <td>PFIZER\\BIONTECH</td>\n",
       "      <td>2</td>\n",
       "      <td>COVID19 (COVID19 (PFIZER-BIONTECH))</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>EL9264</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Haemophagocytic lymphohistiocytosis</td>\n",
       "      <td>SARS-CoV-2 test</td>\n",
       "      <td>...</td>\n",
       "      <td>2021-02-05</td>\n",
       "      <td>2021-02-15</td>\n",
       "      <td>10.0</td>\n",
       "      <td>PVT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medical History/Concurrent Conditions: Type II...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-03-12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46525</th>\n",
       "      <td>1115045</td>\n",
       "      <td>57.0</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>2</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Death</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2021-03-02</td>\n",
       "      <td>2021-03-02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medical History/Concurrent Conditions: No adve...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-03-15</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46526</th>\n",
       "      <td>1115348</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MODERNA</td>\n",
       "      <td>1</td>\n",
       "      <td>COVID19 (COVID19 (MODERNA))</td>\n",
       "      <td>COVID19</td>\n",
       "      <td>026A21A</td>\n",
       "      <td>LA</td>\n",
       "      <td>Death</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2021-03-05</td>\n",
       "      <td>2021-03-06</td>\n",
       "      <td>1.0</td>\n",
       "      <td>UNK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Comments: List of non-encoded Patient Relevant...</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-03-12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>46526 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ID   AGE     MANUFACTURER DOSE  \\\n",
       "1       916600  33.0          MODERNA    1   \n",
       "2       916601  73.0          MODERNA    1   \n",
       "3       916602  23.0  PFIZER\\BIONTECH    1   \n",
       "4       916603  58.0          MODERNA  UNK   \n",
       "5       916604  47.0          MODERNA    1   \n",
       "...        ...   ...              ...  ...   \n",
       "46522  1113920  83.0  PFIZER\\BIONTECH    1   \n",
       "46523  1113920  83.0  PFIZER\\BIONTECH    1   \n",
       "46524  1113963  59.0  PFIZER\\BIONTECH    2   \n",
       "46525  1115045  57.0          MODERNA    2   \n",
       "46526  1115348   NaN          MODERNA    1   \n",
       "\n",
       "                                   VACCINE VAX_TYPE  VAX_LOT VAX_SITE  \\\n",
       "1              COVID19 (COVID19 (MODERNA))  COVID19  037K20A       LA   \n",
       "2              COVID19 (COVID19 (MODERNA))  COVID19  025L20A       RA   \n",
       "3      COVID19 (COVID19 (PFIZER-BIONTECH))  COVID19   EL1284       LA   \n",
       "4              COVID19 (COVID19 (MODERNA))  COVID19  unknown      NaN   \n",
       "5              COVID19 (COVID19 (MODERNA))  COVID19      NaN       LA   \n",
       "...                                    ...      ...      ...      ...   \n",
       "46522  COVID19 (COVID19 (PFIZER-BIONTECH))  COVID19  Unknown      NaN   \n",
       "46523  COVID19 (COVID19 (PFIZER-BIONTECH))  COVID19  Unknown      NaN   \n",
       "46524  COVID19 (COVID19 (PFIZER-BIONTECH))  COVID19   EL9264      NaN   \n",
       "46525          COVID19 (COVID19 (MODERNA))  COVID19      NaN      NaN   \n",
       "46526          COVID19 (COVID19 (MODERNA))  COVID19  026A21A       LA   \n",
       "\n",
       "                                  SYMPTOM1                 SYMPTOM2  ...  \\\n",
       "1                                Dysphagia             Epiglottitis  ...   \n",
       "2                                  Anxiety                 Dyspnoea  ...   \n",
       "3                         Chest discomfort                Dysphagia  ...   \n",
       "4                                Dizziness                  Fatigue  ...   \n",
       "5                  Injection site erythema  Injection site pruritus  ...   \n",
       "...                                    ...                      ...  ...   \n",
       "46522                     Nasal discomfort             Night sweats  ...   \n",
       "46523                                 Rash          SARS-CoV-2 test  ...   \n",
       "46524  Haemophagocytic lymphohistiocytosis          SARS-CoV-2 test  ...   \n",
       "46525                                Death                      NaN  ...   \n",
       "46526                                Death                      NaN  ...   \n",
       "\n",
       "        VAX_DATE ONSET_DATE NUMDAYS V_ADMINBY  \\\n",
       "1     2020-12-28 2020-12-30     2.0       PVT   \n",
       "2     2020-12-31 2020-12-31     0.0       SEN   \n",
       "3     2020-12-31 2020-12-31     0.0       SEN   \n",
       "4     2020-12-23 2020-12-23     0.0       WRK   \n",
       "5     2020-12-22 2020-12-29     7.0       PUB   \n",
       "...          ...        ...     ...       ...   \n",
       "46522 2021-02-09        NaT     NaN       UNK   \n",
       "46523 2021-02-09        NaT     NaN       UNK   \n",
       "46524 2021-02-05 2021-02-15    10.0       PVT   \n",
       "46525 2021-03-02 2021-03-02     0.0       UNK   \n",
       "46526 2021-03-05 2021-03-06     1.0       UNK   \n",
       "\n",
       "                                              OTHER_MEDS  \\\n",
       "1                                                   None   \n",
       "2      Patient residing at nursing facility. See pati...   \n",
       "3                                                   None   \n",
       "4                                                   none   \n",
       "5                                                     Na   \n",
       "...                                                  ...   \n",
       "46522  ELIQUIS; SYNTHROID; PRAVASTATIN; DILTIAZEM; DO...   \n",
       "46523  ELIQUIS; SYNTHROID; PRAVASTATIN; DILTIAZEM; DO...   \n",
       "46524                                                NaN   \n",
       "46525                                                NaN   \n",
       "46526                                                NaN   \n",
       "\n",
       "                                                 CUR_ILL  \\\n",
       "1                                                   None   \n",
       "2      Patient residing at nursing facility. See pati...   \n",
       "3                                                   None   \n",
       "4                                       kidney infection   \n",
       "5                                                     Na   \n",
       "...                                                  ...   \n",
       "46522                                                NaN   \n",
       "46523                                                NaN   \n",
       "46524                                                NaN   \n",
       "46525                                                NaN   \n",
       "46526                                                NaN   \n",
       "\n",
       "                                                 HISTORY FORM_VERS  \\\n",
       "1                                                   None         2   \n",
       "2      Patient residing at nursing facility. See pati...         2   \n",
       "3                                                   None         2   \n",
       "4      diverticulitis, mitral valve prolapse, osteoar...         2   \n",
       "5                                                    NaN         2   \n",
       "...                                                  ...       ...   \n",
       "46522  Medical History/Concurrent Conditions: Atrial ...         2   \n",
       "46523  Medical History/Concurrent Conditions: Atrial ...         2   \n",
       "46524  Medical History/Concurrent Conditions: Type II...         2   \n",
       "46525  Medical History/Concurrent Conditions: No adve...         2   \n",
       "46526  Comments: List of non-encoded Patient Relevant...         2   \n",
       "\n",
       "       TODAYS_DATE                                          ALLERGIES  \n",
       "1       2021-01-01                                  Pcn and bee venom  \n",
       "2       2021-01-01                                            \"Dairy\"  \n",
       "3       2021-01-01                                          Shellfish  \n",
       "4       2021-01-01  Diclofenac, novacaine, lidocaine, pickles, tom...  \n",
       "5       2021-01-01                                                 Na  \n",
       "...            ...                                                ...  \n",
       "46522   2021-03-16                                                NaN  \n",
       "46523   2021-03-16                                                NaN  \n",
       "46524   2021-03-12                                                NaN  \n",
       "46525   2021-03-15                                                NaN  \n",
       "46526   2021-03-12                                                NaN  \n",
       "\n",
       "[46526 rows x 26 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.to_csv(\"Final_Vaccine_Data.csv\", index_label = \"Number\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing final_df to sql database \"Project_COVID_19\"\n",
    "\n",
    "from sqlalchemy import create_engine\n",
    "from config import db_password\n",
    "\n",
    "\n",
    "\n",
    "db_string = f\"postgresql://postgres:{db_password}@127.0.0.1:5432/Project_COVID_19\"\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(db_string)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "Table 'vaccinedata' already exists.",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-54-fe0efb635c78>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mfinal_df\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_sql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'vaccinedata'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcon\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/opt/anaconda3/envs/PythonTest/lib/python3.7/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36mto_sql\u001b[0;34m(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)\u001b[0m\n\u001b[1;32m   2787\u001b[0m             \u001b[0mchunksize\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2788\u001b[0m             \u001b[0mdtype\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2789\u001b[0;31m             \u001b[0mmethod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2790\u001b[0m         )\n\u001b[1;32m   2791\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/envs/PythonTest/lib/python3.7/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[0;34m(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)\u001b[0m\n\u001b[1;32m    608\u001b[0m         \u001b[0mchunksize\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    609\u001b[0m         \u001b[0mdtype\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 610\u001b[0;31m         \u001b[0mmethod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    611\u001b[0m     )\n\u001b[1;32m    612\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/envs/PythonTest/lib/python3.7/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[0;34m(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method)\u001b[0m\n\u001b[1;32m   1404\u001b[0m             \u001b[0mdtype\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1405\u001b[0m         )\n\u001b[0;32m-> 1406\u001b[0;31m         \u001b[0mtable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcreate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1407\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1408\u001b[0m         \u001b[0;32mfrom\u001b[0m \u001b[0msqlalchemy\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/envs/PythonTest/lib/python3.7/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mcreate\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    736\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexists\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    737\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mif_exists\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m\"fail\"\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 738\u001b[0;31m                 \u001b[0;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mf\"Table '{self.name}' already exists.\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    739\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mif_exists\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m\"replace\"\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    740\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpd_sql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdrop_table\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mschema\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mValueError\u001b[0m: Table 'vaccinedata' already exists."
     ]
    }
   ],
   "source": [
    "final_df.to_sql(name='vaccinedata', con=engine)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PythonTest",
   "language": "python",
   "name": "pythontest"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}