Is it Safe to Go Outside? Data Investigation With MongoDB

1
export MDB_URI="mongodb+srv://username:password@cluster0.abcde.mongodb.net/covid?retryWrites=true&w=majority"

1
import sys
2
3
!{sys.executable} -m pip install pymongo[srv]==3.11.0 urllib3==1.25.10

1
from datetime import datetime
2
import json
3
import os
4
from urllib.parse import urljoin
5
import pymongo
6
import urllib3
7
8
# Historical COVID stats endpoint:
9
daily_url = 'https://www.opendata.nhs.scot/api/3/action/datastore_search?resource_id=427f9a25-db22-4014-a3bc-893b68243055'
10
11
# Latest, one-day COVID stats endpoint:
12
latest_url = 'https://www.opendata.nhs.scot/api/3/action/datastore_search?resource_id=e8454cf0-1152-4bcb-b9da-4343f625dfef'
13
14
http = urllib3.PoolManager()
15
16
client = pymongo.MongoClient(os.environ["MDB_URI"])
17
db = client.get_database('covid')

1
data = json.loads(http.request('GET', daily_url).data)
2
pprint(data['result']['records'])

1
{'CA': 'S12000005',                   
2
'CAName': 'Clackmannanshire',         
3
'CrudeRateDeaths': 0,                 
4
'CrudeRateNegative': 25.2231276678308,
5
'CrudeRatePositive': 0,               
6
'CumulativeDeaths': 0,                
7
'CumulativeNegative': 13,             
8
'CumulativePositive': 0,              
9
'DailyDeaths': 0,                     
10
'DailyPositive': 0,                   
11
'Date': 20200228,                     
12
'PositivePercentage': 0,              
13
'PositiveTests': 0,                   
14
'TotalPillar1': 6,                    
15
'TotalPillar2': 0,                    
16
'TotalTests': 6,                      
17
'_id': 1}
18
  -  

1
{'CA': 'S12000005',
2
'CAName': 'Clackmannanshire',
3
'CrudeRateDeaths': 73.7291424136593,       
4
'CrudeRateNegative': 27155.6072953046,
5
'CrudeRatePositive': 1882.03337213815,
6
'Date': 20201216,
7
'NewDeaths': 1,
8
'NewPositive': 6,
9
'TotalCases': 970,
10
'TotalDeaths': 38,
11
'TotalNegative': 13996,
12
'_id': 1}

1
db.daily.create_index([('Date', pymongo.ASCENDING), ('CA', pymongo.ASCENDING)], unique=True)
2
db.daily.create_index([('CAName', pymongo.ASCENDING)])

1
def upload_record(record):
2
   del record['_id']
3
   record['Date'] = datetime.strptime(str(record['Date']), "%Y%m%d")
4
   if 'NewPositive' in record:
5
      record['DailyPositive'] = record['NewPositive']
6
      del record['NewPositive']
7
   if 'NewDeaths' in record:
8
      record['DailyDeaths'] = record['NewDeaths']
9
      del record['NewDeaths']
10
   db.daily.replace_one({'Date': record['Date'], 'CA': record['CA']}, record, upsert=True)

1
for record in data['result']['records']:
2
   upload_record(record)

1
pprint(data['result']['_links'])
2
3
{'next':
4
'/api/3/action/datastore_search?offset=100&resource_id=e8454cf0-1152-4bcb-b9da-4343f625dfef',
5
'start':
6
'/api/3/action/datastore_search?resource_id=e8454cf0-1152-4bcb-b9da-4343f625dfef'}

1
def paged_wrapper(starting_url):
2
   url = starting_url
3
   while url is not None:
4
      print(url)
5
      try:
6
            response = http.request('GET', url)
7
            data = response.data
8
            page = json.loads(data)
9
      except json.JSONDecodeError as jde:
10
            print(f"""
11
Failed to decode invalid json at {url} (Status: {response.status}
12
13
{response.data}
14
""")
15
            raise
16
      records = page['result']['records']
17
      if records:
18
            for record in records:
19
               yield record
20
      else:
21
            return
22
23
      if n := page['result']['_links'].get('next'):
24
            url = urljoin(url, n)
25
      else:
26
            url = None

1
# This gets the latest figures, released separately:
2
records = paged_wrapper(latest_url)
3
for record in records:
4
   upload_record(record)
5
6
# This backfills, and updates with revised figures:
7
records = paged_wrapper(daily_url)
8
for record in records:
9
   upload_record(record)

1
[ { $addFields: { CAName: { $replaceOne: { input: "$CAName", find: " & ", replacement: " and "} } }}]