<jupyter_start><jupyter_text>This notebook covers data scraping, cleaning, processing and updating of the [COVID-19 Singapore dataset](https://data.world/hxchua/covid-19-singapore/workspace/file?filename=Covid-19+SG.xlsx).Steps to run:1. Update `phase` (This phase will be inputted into the Google Sheets.)2. Restart and run all.Additional steps if you want to update a Google Sheet:1. Start a Google Cloud Platform Project. Get/download the service account credentials, copy the downloaded json into the same directory as this file and update the `KEYS_FILE` variable with the json name.2. Update Config for Google Sheets3. Set `update_google_sheets` to True  Import required packages<jupyter_code>import sys
from bs4 import BeautifulSoup
import requests
import urllib3
import io
import pandas as pd
from datetime import datetime, timedelta
import re
from google.oauth2.service_account import Credentials
import numpy as np

try: 
    import gspread
except ModuleNotFoundError:
    !{sys.executable} -m pip install gspread
    import gspread
    
try: 
    import pdfplumber
except ModuleNotFoundError:
    !{sys.executable} -m pip install pdfplumber    
    import pdfplumber<jupyter_output><empty_output><jupyter_text>Options<jupyter_code>phase = "Phase 3 (Heightened Alert)"
update_google_sheets = True # if True, will update Google Sheet directly<jupyter_output><empty_output><jupyter_text>Config for Google Sheets(Only required if update_google_sheets is True)<jupyter_code>SPREADSHEET_ID = '1gFTNs_GtnTIyyVWXmsQxwdZpGbyicZM2HJcXvCf4b3k'
KEYS_FILE = 'project-gsheets-c30a9f47e838.json'
SHEET_NAME = "Sheet1"
SCOPES = ['https://www.googleapis.com/auth/spreadsheets',
          'https://www.googleapis.com/auth/drive'] # If modifying these scopes, delete the file token.pickle.<jupyter_output><empty_output><jupyter_text>More options<jupyter_code># For web scraping
base_url = 'https://www.moh.gov.sg'
landing_page_url = base_url + '/covid-19/situation-report'
past_updates_page_url = 'https://www.moh.gov.sg/covid-19/past-updates'
info_link_string_to_find = "Update on Local COVID-19 Situation"
#info_link_string_to_find = "New Cases of Locally Transmitted COVID-19 Infection"
date_tag = "span"
unlinked_string_to_find = " are currently unlinked"
linked_string_to_find1 = " are linked"
linked_string_to_find2 = " is linked"
total_number_pre = "Of the "
total_number_post = " cases"

# PDF tables
tables = ['Figure 1.1', 'Figure 3.1']
headers1 = [
    'Date',
    'Daily Imported',
    'Local cases not residing in doms MOH report',
    'Local cases residing in dorms MOH report',
    'Daily Confirmed'
]
data1_col_index = [0,3,12,16,18]
headers2 = [
    'Date',
    'Intensive Care Unit (ICU)', 
    'General Wards MOH report',
    'In Isolation MOH report', 
    'Total Completed Isolation MOH report',
    'Total Hospital Discharged MOH report',
    'Cumulative Deaths'
]
data2_col_index = [0,1,2,3,4,5,6]
historical_columns = ['False Positives Found', 'Passed but not due to COVID', 'Tested positive demise'] # set all to zero

# Get past data
data_dot_world_url = 'https://query.data.world/s/3hxjhjvlkk7j4re3lzfghvu5sxpw5h'<jupyter_output><empty_output><jupyter_text>Prepare required data<jupyter_code>data_dot_world_df = pd.read_excel(data_dot_world_url)
data_dot_world_df.columns = data_dot_world_df.columns.str.strip()
data_dot_world_df['Date'] = pd.to_datetime(data_dot_world_df['Date'])
date = (data_dot_world_df.tail(1).reset_index(drop = True).loc[0, 'Date'] + timedelta(days=1)).strftime("%d %b %Y")
year = datetime.strptime(date, "%d %b %Y").strftime("%Y")<jupyter_output><empty_output><jupyter_text>Web Scraping<jupyter_code>def get_info_page_url(date_string):
    past_updates_page = requests.get(past_updates_page_url)
    past_updates_page_soup = BeautifulSoup(past_updates_page.text, "html.parser")
    
    for linebreak in past_updates_page_soup.find_all('br'):
        linebreak.extract()
        
    date_elems_list = past_updates_page_soup.find_all(date_tag, text=re.compile('^.*{}.*$'.format(date_string)))
    
    entries_list = [elem.find_parent("tr") for elem in date_elems_list if info_link_string_to_find in str(elem.find_parent("tr"))]
    
   # if len(entries_list) < 2:
   #     return None

    entry = entries_list[0]

    info_page_url = entry.find(href=True).get("href")
    print(info_page_url)
    
    return info_page_url.strip()
def get_num_of_unlinked_cases(date_string):
    info_page_url = get_info_page_url(date_string)
    info_page = requests.get(info_page_url) 
    info_page_soup = BeautifulSoup(info_page.text, "html.parser")
    
    number_unlinked = re.search('\d+(?={})'.format(unlinked_string_to_find), str(info_page_soup))
    number_linked1 = re.search('\d+(?={})'.format(linked_string_to_find1), str(info_page_soup))
    number_linked2 = re.search('\d+(?={})'.format(linked_string_to_find2), str(info_page_soup))
    total_number = re.search('(?<={})\d+(?={})'.format(total_number_pre, total_number_post), str(info_page_soup))
        
    if not number_unlinked:
        total_linked = 0
        if number_linked1:
            total_linked = total_linked + int(number_linked1.group(0))
        if number_linked2:
            total_linked = total_linked + int(number_linked2.group(0))
        return int(total_number.group(0)) - total_linked
    
    return int(number_unlinked.group(0))
#As of 3 July 2021, we have administered a total of 5,706,932 doses of COVID-19 vaccines under the national vaccination programme. 
#The total number of doses administered was 5,706,932, covering 3,550,181 individuals. 2,156,751 individuals have received their second dose and completed the full vaccination regimen.
total_vacc_doses_string_to_find1a = "we have administered a total of "
total_vacc_doses_string_to_find1b = " doses of COVID-19 vaccines under the national"

one_dose_count_string_to_find1a = "covering "
one_dose_count_string_to_find1b = " individuals. "
second_dose_count_string_to_find2 = " individuals have received their second dose"
def get_num_of_vacc(date_string):
    info_page_url = get_info_page_url(date_string)
    info_page = requests.get(info_page_url) 
    info_page_soup = BeautifulSoup(info_page.text, "html.parser")
    
    number_vacc = re.search('(?<={})\s*([^\n\r]*)(?={})'.format(total_vacc_doses_string_to_find1a,total_vacc_doses_string_to_find1b), str(info_page_soup))
    number_vacc1 = re.search('(?<={})\s*([^\n\r]*)(?={})'.format(one_dose_count_string_to_find1a, one_dose_count_string_to_find1b), str(info_page_soup))
    number_vacc2 = re.search('(?<={})\s*([^\n\r]*)(?={})'.format(one_dose_count_string_to_find1b, second_dose_count_string_to_find2), str(info_page_soup))

    total_vacc_doses = int(number_vacc.group(0).replace(',', ''))
    one_dose_count = int(number_vacc1.group(0).replace(',', ''))
    two_dose_count = int(number_vacc2.group(0).replace(',', ''))
    
    return total_vacc_doses, one_dose_count, two_dose_count
def get_pdf_link(date_string):
    
    landing_page = requests.get(landing_page_url)
    landing_page_soup = BeautifulSoup(landing_page.text, "html.parser")

    pdf_link = landing_page_soup.find("a", text=re.compile('^.*{}.*$'.format(date_string))).get("href")
    print(base_url + pdf_link.strip())
    
    return base_url + pdf_link.strip()
def retrieve_data1(raw_df):
    df = raw_df.iloc[:, lambda raw_df1:data1_col_index]
    df.columns = headers1
    return df
def retrieve_data2(raw_df):
    df = raw_df.iloc[:, lambda raw_df1:data2_col_index]
    df.columns = headers2
    return df
# https://stackoverflow.com/questions/62075033/read-pdf-from-url-to-memory-omitting-saving-file-to-local-file

def get_raw_data(date_string):
    pdf_link = get_pdf_link(date_string)
        
    if not pdf_link:
        print('Unable to get pdf link for {}.'.format(date_string))
        return None, None
        
    http = urllib3.PoolManager()
    temp = io.BytesIO()
    temp.write(http.request("GET", pdf_link).data)

    raw_df1, raw_df2 = pd.DataFrame(), pd.DataFrame()
    
    index = 1

    with pdfplumber.open(temp) as pdf: # pdf = pdfplumber.open(temp) 
        for pdf_page in pdf.pages:
            text = pdf_page.extract_text()
            
            if not any(substring in text for substring in tables):
                continue
                
            table = pdf_page.extract_table()
                        
            if index == 1:
                raw_df1 = pd.DataFrame(table).iloc[[21]].replace(r'^\s*$', np.nan, regex=True)
                raw_df1 = raw_df1.dropna(axis=1).reset_index(drop=True)
                raw_df1.columns = range(raw_df1.shape[1])
                index = 2
                continue
            
            raw_df2 = pd.DataFrame(table).iloc[[23]].replace(r'^\s*$', np.nan, regex=True)
            raw_df2 = raw_df2.dropna(axis=1).reset_index(drop=True)
            raw_df2.columns = range(raw_df2.shape[1])

    return raw_df1, raw_df2
# Consolidate data
current_data = get_raw_data(date)

# Combine retrieved data
current_df = pd.merge(retrieve_data1(current_data[0]), retrieve_data2(current_data[1]), on='Date')
current_df = pd.merge(current_df, pd.DataFrame(0, index=np.arange(1), columns=historical_columns), left_index=True, right_index=True)

store_date = year + datetime.strptime(current_df.loc[0, 'Date'], "%d-%b").strftime("-%m-%d")
current_df = current_df.drop(columns=['Date'])

current_df['Discharged to Isolation'] = current_df['In Isolation MOH report']
current_df['Daily Local transmission'] = int(current_df.loc[0,'Local cases not residing in doms MOH report']) + int(current_df.loc[0,'Local cases residing in dorms MOH report'])
current_df['Unlinked community cases'] = get_num_of_unlinked_cases(date)
#current_df['Unlinked community cases'] = 2   ### applicable when no unlinked cases as the sentence structure changes causing function to fail
current_df['Linked community cases'] = int(current_df.loc[0, 'Daily Local transmission']) - int(current_df.loc[0, 'Unlinked community cases'])
total_vacc_doses, one_dose_count, two_dose_count = get_num_of_vacc(date)
current_df['Cumulative Vaccine Doses'] = total_vacc_doses
current_df['Cumulative Individuals Vaccinated'] = one_dose_count
current_df['Cumulative Individuals Vaccination Completed'] = two_dose_count


current_df = current_df.replace(',','', regex=True)

current_df = current_df.apply(pd.to_numeric)

current_df['Phase'] = phase
current_df['Date'] = datetime.strptime(store_date, "%Y-%m-%d")

current_df<jupyter_output>https://www.moh.gov.sg/docs/librariesprovider5/local-situation-report/situation-report---07-jul-2021.pdf?sfvrsn=bdaff36e_0
https://www.moh.gov.sg/news-highlights/details/update-on-local-covid-19-situation-(7-july-2021)<jupyter_text>Combining Data<jupyter_code>#df = data_dot_world_df
#current_index = len(df)-1
df = data_dot_world_df.append(current_df, ignore_index = True)
current_index = len(df)-1
# # Check date 
                                             
# if df.loc[current_index, 'Date'] < df.loc[current_index - 1, 'Date']:
#     raise Exception("Current date is earlier than previous entry!")

# if df.loc[current_index, 'Date'] - df.loc[current_index - 1, 'Date'] != timedelta(days=1):
#     raise Exception("Have you skipped a date?")
# Compute extra fields
df.loc[current_index, 'Daily Discharged'] = df.loc[current_index, 'Total Completed Isolation MOH report'] - df.loc[current_index - 1, 'Total Completed Isolation MOH report'] + df.loc[current_index, 'Total Hospital Discharged MOH report'] - df.loc[current_index - 1, 'Total Hospital Discharged MOH report']
df.loc[current_index, 'Cumulative Discharged'] = df['Daily Discharged'].sum()
df.loc[current_index, 'Cumulative Confirmed'] = df['Daily Confirmed'].sum() - df['False Positives Found'].sum()
df.loc[current_index, 'Still Hospitalised'] = df.loc[current_index, 'Cumulative Confirmed'] - df['Daily Discharged'].sum() - df.loc[current_index, 'Cumulative Deaths'] - df.loc[current_index, 'Discharged to Isolation'] - df['Tested positive demise'].sum()
df.loc[current_index, 'Daily Deaths'] = df.loc[current_index, 'Cumulative Deaths'] - df.loc[current_index - 1, 'Cumulative Deaths']
pd.set_option('display.max_columns', None)
df.tail(5)<jupyter_output><empty_output><jupyter_text>Push new data to Google sheet<jupyter_code>if update_google_sheets:

    credentials = Credentials.from_service_account_file(KEYS_FILE, scopes=SCOPES)
    gc = gspread.authorize(credentials)

    ws = gc.open_by_key(SPREADSHEET_ID).worksheet(SHEET_NAME)

    df['Date'] = df['Date'].astype(str)
    df.fillna('', inplace=True)

    ws.update([df.columns.values.tolist()] + df.values.tolist())<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12426923684626323, 'fraction_numerical': 0.01738031284563122, 'mean_word_length': 4.7593266606005455, 'pattern_counts': {'":': 0, '<': 38, '<?xml version=': 0, '>': 32, 'https://': 9, 'lorem ipsum': 0, 'www.': 6, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '255584', 'license': 'MIT', 'n_tokens_mistral': 4597, 'n_tokens_neox': 4360, 'n_words': 855, 'path': 'notebooks/covid_sg_stats.ipynb', 'repo_name': 'huishun98/datakueh', 'size': '12658'}