3_Record_linkage.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MACS30122 Final Project - Record Linkage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Background Infomation\n",
    "### Data sources\n",
    "\n",
    "For this project, we have 4 main data sources in 55 separate files in total.\n",
    "\n",
    "**Twitter Data**\n",
    "- CDC tweets → \"data/cdc_twitter_covid.json\"\n",
    "- State governors' tweets → \"data/governor\", 52 separate JSON files named by the state\n",
    "\n",
    "**Covid Data**\n",
    "- Daily number of cases by country → \"data/covid-data-US.csv\"\n",
    "- Daily number of cases by state → \"data/covid-data-by-state.csv\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sample output of the record linkage\n",
    "\n",
    "Our goal is to generate a \"big table\" to serve data for the dashboard.  \n",
    "Here is the sample logic for the \"big table\". The primary key will be the date, and the table is further partitioned by state.\n",
    "\n",
    "<img style=\"float: left;\" src=\"data/Sample_output_rl.png\"> "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before getting to the code, let's input some magic functions, packages, and data paths.  \n",
    "The helper functions of the record linkage process are stored in a file named Record_linkage_helper.py.  \n",
    "Here we also use two clean-up helper functions from covid_data_analysis.py."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The autoreload extension is already loaded. To reload it, use:\n",
      "  %reload_ext autoreload\n"
     ]
    }
   ],
   "source": [
    "# Magic functions\n",
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Packages and paths\n",
    "\n",
    "import pandas as pd\n",
    "import os\n",
    "\n",
    "import Record_linkage_helper as RL\n",
    "from covid_data_analysis import get_correct_date, clean_data\n",
    "\n",
    "p='data/governor/'\n",
    "p_covid_data = 'data/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 1: Generate a \"big table\" of the daily number of cases by country and state"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The first step of the record linkage is to merge covid data by country and by state.  \n",
    "We read in the daily number of cases by country and state from the CSV files, and preprocess them into readable Dataframes. Then, we merge the two sources of data to get a big table of covid data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Daily number of cases by country\n",
    "covid_data_US = get_correct_date(pd.read_csv(os.path.join(p_covid_data, \"covid-data-US.csv\"),encoding='utf-8'))\n",
    "\n",
    "# Daily number of cases by state\n",
    "covid_data_state = clean_data(pd.read_csv(os.path.join(p_covid_data, \"covid-data-by-state.csv\"),encoding='utf-8'))\n",
    "covid_data_state.loc[:, 'date'] = covid_data_state.loc[:, 'date'].apply(RL.replace_separator)\n",
    "covid_data_state = covid_data_state[['province_state','date','confirmed_state','deaths_state']]\n",
    "\n",
    "# Merge two sources of covid data\n",
    "merged_covid_data = pd.merge(left = covid_data_state,right = covid_data_US, on = 'date',how = 'outer')\n",
    "merged_covid_data.sort_values(['date','province_state'],inplace = True)\n",
    "merged_covid_data.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at what the initial big table looks like. Note that here we use an outer join to cover all available data either by country or by state."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>province_state</th>\n",
       "      <th>date</th>\n",
       "      <th>confirmed_state</th>\n",
       "      <th>deaths_state</th>\n",
       "      <th>confirmed</th>\n",
       "      <th>deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2020/01/22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2020/01/23</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2020/01/24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2020/01/25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2020/01/26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18877</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>2021/02/21</td>\n",
       "      <td>564115.0</td>\n",
       "      <td>7331.0</td>\n",
       "      <td>28134115</td>\n",
       "      <td>498897.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18878</th>\n",
       "      <td>Washington</td>\n",
       "      <td>2021/02/21</td>\n",
       "      <td>333794.0</td>\n",
       "      <td>4822.0</td>\n",
       "      <td>28134115</td>\n",
       "      <td>498897.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18879</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>2021/02/21</td>\n",
       "      <td>129616.0</td>\n",
       "      <td>2261.0</td>\n",
       "      <td>28134115</td>\n",
       "      <td>498897.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18880</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>2021/02/21</td>\n",
       "      <td>612240.0</td>\n",
       "      <td>6871.0</td>\n",
       "      <td>28134115</td>\n",
       "      <td>498897.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18881</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>2021/02/21</td>\n",
       "      <td>53795.0</td>\n",
       "      <td>662.0</td>\n",
       "      <td>28134115</td>\n",
       "      <td>498897.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>18882 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      province_state        date  confirmed_state  deaths_state  confirmed  \\\n",
       "0                NaN  2020/01/22              NaN           NaN          1   \n",
       "1                NaN  2020/01/23              NaN           NaN          1   \n",
       "2                NaN  2020/01/24              NaN           NaN          2   \n",
       "3                NaN  2020/01/25              NaN           NaN          2   \n",
       "4                NaN  2020/01/26              NaN           NaN          5   \n",
       "...              ...         ...              ...           ...        ...   \n",
       "18877       Virginia  2021/02/21         564115.0        7331.0   28134115   \n",
       "18878     Washington  2021/02/21         333794.0        4822.0   28134115   \n",
       "18879  West Virginia  2021/02/21         129616.0        2261.0   28134115   \n",
       "18880      Wisconsin  2021/02/21         612240.0        6871.0   28134115   \n",
       "18881        Wyoming  2021/02/21          53795.0         662.0   28134115   \n",
       "\n",
       "         deaths  \n",
       "0           NaN  \n",
       "1           NaN  \n",
       "2           NaN  \n",
       "3           NaN  \n",
       "4           NaN  \n",
       "...         ...  \n",
       "18877  498897.0  \n",
       "18878  498897.0  \n",
       "18879  498897.0  \n",
       "18880  498897.0  \n",
       "18881  498897.0  \n",
       "\n",
       "[18882 rows x 6 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_covid_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 2: Merge the CDC tweets data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Read in and clean CDC tweets\n",
    "\n",
    "cdc_tweets_df = pd.read_json(os.path.join(p_covid_data, 'cdc_twitter_covid.json'),lines=True,encoding='utf-8')\n",
    "\n",
    "cdc_tweets_df.loc[:, 'date'] = cdc_tweets_df.loc[:, 'date'].apply(RL.timestamp_to_str)\n",
    "cdc_tweets_df = cdc_tweets_df[['tweet','date']]\n",
    "cdc_tweets_df['tweet'] = cdc_tweets_df['tweet'].apply(RL.remove_u2069)\n",
    "cdc_tweets_df.rename(columns={'tweet': 'CDC_tweet'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We read in and clean the CDC tweets. Take a look at the data. And link it to the big table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>CDC_tweet</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>#DYK? COVID-19 and flu can both cause fever an...</td>\n",
       "      <td>2021/02/22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A new @CDCMMWR looks at #COVID19 in Georgia el...</td>\n",
       "      <td>2021/02/22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Officials are concerned about 3 #COVID19 varia...</td>\n",
       "      <td>2021/02/19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Getting a #COVID19 vaccine is important for ad...</td>\n",
       "      <td>2021/02/19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>#DYK? You need two #COVID19 mRNA vaccine doses...</td>\n",
       "      <td>2021/02/18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           CDC_tweet        date\n",
       "0  #DYK? COVID-19 and flu can both cause fever an...  2021/02/22\n",
       "1  A new @CDCMMWR looks at #COVID19 in Georgia el...  2021/02/22\n",
       "2  Officials are concerned about 3 #COVID19 varia...  2021/02/19\n",
       "3  Getting a #COVID19 vaccine is important for ad...  2021/02/19\n",
       "4  #DYK? You need two #COVID19 mRNA vaccine doses...  2021/02/18"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdc_tweets_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged_covid_cdc_tweet_df = pd.merge(left = merged_covid_data,right = cdc_tweets_df, on = 'date',how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 3: Merge the governors' tweets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To merge tweets from each governor, we create a function to read in and clean data from one JSON file, and then iterate the function on all JSON files we have.  \n",
    "First, let's see how the helper function works on one single JSON file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>governor_tweet</th>\n",
       "      <th>date</th>\n",
       "      <th>province_state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Great news! 🚨Alabama ranks 5th in Back-to-Norm...</td>\n",
       "      <td>2021/03/09</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A great corporate partner for Alabama during #...</td>\n",
       "      <td>2020/08/25</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Absolutely! And they will - I appreciate you f...</td>\n",
       "      <td>2020/05/15</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>There are several #COVID19 testing centers ava...</td>\n",
       "      <td>2020/05/14</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ICYMI: @ALWorksSystem has released the AL COVI...</td>\n",
       "      <td>2020/04/15</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>My priority as governor is making sure every A...</td>\n",
       "      <td>2020/04/09</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>It is with profound sadness that I confirm tha...</td>\n",
       "      <td>2020/03/25</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      governor_tweet        date  \\\n",
       "0  Great news! 🚨Alabama ranks 5th in Back-to-Norm...  2021/03/09   \n",
       "1  A great corporate partner for Alabama during #...  2020/08/25   \n",
       "2  Absolutely! And they will - I appreciate you f...  2020/05/15   \n",
       "3  There are several #COVID19 testing centers ava...  2020/05/14   \n",
       "4  ICYMI: @ALWorksSystem has released the AL COVI...  2020/04/15   \n",
       "5  My priority as governor is making sure every A...  2020/04/09   \n",
       "6  It is with profound sadness that I confirm tha...  2020/03/25   \n",
       "\n",
       "  province_state  \n",
       "0        Alabama  \n",
       "1        Alabama  \n",
       "2        Alabama  \n",
       "3        Alabama  \n",
       "4        Alabama  \n",
       "5        Alabama  \n",
       "6        Alabama  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "RL.read_in_one_governor_tweets(p,'Alabama.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Starting from there, we store all file names in a list called all_json_state_names, and apply the helper function on all JSON files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finish reading in tweets from the governor of Alaska\n",
      "Finish reading in tweets from the governor of Alabama\n",
      "Finish reading in tweets from the governor of Arkansas\n",
      "Finish reading in tweets from the governor of Arizona\n",
      "Finish reading in tweets from the governor of California\n",
      "Finish reading in tweets from the governor of Colorado\n",
      "Finish reading in tweets from the governor of Connecticut\n",
      "Finish reading in tweets from the governor of DC\n",
      "Finish reading in tweets from the governor of Delaware\n",
      "Finish reading in tweets from the governor of Florida\n",
      "Finish reading in tweets from the governor of Georgia\n",
      "Finish reading in tweets from the governor of Hawaii\n",
      "Finish reading in tweets from the governor of Iowa\n",
      "Finish reading in tweets from the governor of Idaho\n",
      "Finish reading in tweets from the governor of Illinois\n",
      "Finish reading in tweets from the governor of Indiana\n",
      "Finish reading in tweets from the governor of Kansas\n",
      "Finish reading in tweets from the governor of Kentucky\n",
      "Finish reading in tweets from the governor of Louisiana\n",
      "Finish reading in tweets from the governor of Massachussetts\n",
      "Finish reading in tweets from the governor of Maryland\n",
      "Finish reading in tweets from the governor of Maine\n",
      "Finish reading in tweets from the governor of Michigan\n",
      "Finish reading in tweets from the governor of Minnesota\n",
      "Finish reading in tweets from the governor of Missouri\n",
      "Finish reading in tweets from the governor of Mississippi\n",
      "Finish reading in tweets from the governor of Montana\n",
      "Finish reading in tweets from the governor of Nevada\n",
      "Finish reading in tweets from the governor of North Carolina\n",
      "Finish reading in tweets from the governor of North Dakota\n",
      "Finish reading in tweets from the governor of Nebraska\n",
      "Finish reading in tweets from the governor of New Hampshire\n",
      "Finish reading in tweets from the governor of New Jersey\n",
      "Finish reading in tweets from the governor of New Mexico\n",
      "Finish reading in tweets from the governor of New York\n",
      "Finish reading in tweets from the governor of Rhode Island\n",
      "Finish reading in tweets from the governor of Ohio\n",
      "Finish reading in tweets from the governor of Oklahoma\n",
      "Finish reading in tweets from the governor of Oregon\n",
      "Finish reading in tweets from the governor of Pennsylvania\n",
      "Finish reading in tweets from the governor of Puerto Rico\n",
      "Finish reading in tweets from the governor of South Carolina\n",
      "Finish reading in tweets from the governor of South Dakota\n",
      "Finish reading in tweets from the governor of Tennessee\n",
      "Finish reading in tweets from the governor of Texas\n",
      "Finish reading in tweets from the governor of Utah\n",
      "Finish reading in tweets from the governor of Virginia\n",
      "Finish reading in tweets from the governor of Vermont\n",
      "Finish reading in tweets from the governor of Washington\n",
      "Finish reading in tweets from the governor of Wisconsin\n",
      "Finish reading in tweets from the governor of West Virginia\n",
      "Finish reading in tweets from the governor of Wyoming\n",
      "Finish reading in tweets from 52 governors, getting 12095 tweets\n"
     ]
    }
   ],
   "source": [
    "# Read in tweets from all governors\n",
    "\n",
    "all_governor_tweets_lst = []\n",
    "for i in RL.all_json_state_names:\n",
    "    file_name = i+'.json'\n",
    "    print(\"Finish reading in tweets from the governor of \"+i)\n",
    "    t = RL.read_in_one_governor_tweets(p,file_name)\n",
    "    all_governor_tweets_lst.append(t)\n",
    "\n",
    "# Concat\n",
    "all_governor_tweets = pd.concat(all_governor_tweets_lst)\n",
    "all_governor_tweets.sort_values(['date','province_state'],inplace = True)\n",
    "all_governor_tweets.reset_index(drop=True, inplace=True)\n",
    "\n",
    "print(\"Finish reading in tweets from \"+str(len(all_governor_tweets_lst))+\" governors, getting \"+str(len(all_governor_tweets))+\" tweets\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remember the tweets from the governor of Alabama above, let's double-check them in the dataframe of all governors' tweets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>governor_tweet</th>\n",
       "      <th>date</th>\n",
       "      <th>province_state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1036</th>\n",
       "      <td>It is with profound sadness that I confirm tha...</td>\n",
       "      <td>2020/03/25</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1856</th>\n",
       "      <td>My priority as governor is making sure every A...</td>\n",
       "      <td>2020/04/09</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2132</th>\n",
       "      <td>ICYMI: @ALWorksSystem has released the AL COVI...</td>\n",
       "      <td>2020/04/15</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3346</th>\n",
       "      <td>There are several #COVID19 testing centers ava...</td>\n",
       "      <td>2020/05/14</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3395</th>\n",
       "      <td>Absolutely! And they will - I appreciate you f...</td>\n",
       "      <td>2020/05/15</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6140</th>\n",
       "      <td>A great corporate partner for Alabama during #...</td>\n",
       "      <td>2020/08/25</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11859</th>\n",
       "      <td>Great news! 🚨Alabama ranks 5th in Back-to-Norm...</td>\n",
       "      <td>2021/03/09</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          governor_tweet        date  \\\n",
       "1036   It is with profound sadness that I confirm tha...  2020/03/25   \n",
       "1856   My priority as governor is making sure every A...  2020/04/09   \n",
       "2132   ICYMI: @ALWorksSystem has released the AL COVI...  2020/04/15   \n",
       "3346   There are several #COVID19 testing centers ava...  2020/05/14   \n",
       "3395   Absolutely! And they will - I appreciate you f...  2020/05/15   \n",
       "6140   A great corporate partner for Alabama during #...  2020/08/25   \n",
       "11859  Great news! 🚨Alabama ranks 5th in Back-to-Norm...  2021/03/09   \n",
       "\n",
       "      province_state  \n",
       "1036         Alabama  \n",
       "1856         Alabama  \n",
       "2132         Alabama  \n",
       "3346         Alabama  \n",
       "3395         Alabama  \n",
       "6140         Alabama  \n",
       "11859        Alabama  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_governor_tweets[all_governor_tweets['province_state']=='Alabama']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 4: Final merge"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At this point, we have finished record linkage from almost all data sources. Let's generate a final dataframe and output it into a CSV file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged_df_final = pd.merge(left = merged_covid_cdc_tweet_df,right = all_governor_tweets, \\\n",
    "                                 on = ['date','province_state'],how = 'left')\n",
    "merged_df_final.to_csv(os.path.join(p_covid_data,\"merged_df_final.csv\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's try to print one of the tweets sent by the governor of Hawaii on Mar 17, 2020. We have kept the emojis, URLs and hashtags within the tweets to ensure that the tweets are still readable in the dashboard."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'#Live News Conference: COVID-19 re: 15 days to slow spread of #coronavirus ➡️ https://t.co/C5YJFYrPG0  https://t.co/srA9ghNMTd'"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_tweet = pd.read_csv(os.path.join(p_covid_data,\"merged_df_final.csv\"))\n",
    "test_tweet.governor_tweet[733]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we get the data ready. Let's move on to the the visualization and see how the data look like in the dashboard."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}