Also publish NY Times counties/states data, refs #6

simonw · simonw · commit 56e1644390e5 · 2020-03-28T08:38:29.000-07:00
diff --git a/.github/workflows/scheduled.yml b/.github/workflows/scheduled.yml
@@ -17,6 +17,11 @@ jobs:
       with:
         repository: CSSEGISandData/COVID-19
         path: COVID-19
+    - name: Check out nytimes/covid-19-data
+      uses: actions/checkout@v2
+      with:
+        repository: nytimes/covid-19-data
+        path: covid-19-data
     - name: Set up Python
       uses: actions/setup-python@v1
       with:
diff --git a/build_database.py b/build_database.py
@@ -3,12 +3,13 @@
 import csv
 
 base_path = (Path(__file__) / "..").resolve()
-covid_base = base_path / "COVID-19"
+jhu_csse_base = base_path / "COVID-19"
+nytimes_base = base_path / "covid-19-data"
 
 
 def load_daily_reports():
     daily_reports = list(
-        (covid_base / "csse_covid_19_data" / "csse_covid_19_daily_reports").glob(
+        (jhu_csse_base / "csse_covid_19_data" / "csse_covid_19_daily_reports").glob(
             "*.csv"
         )
     )
@@ -66,11 +67,28 @@ def add_missing_latitude_longitude(db):
             )
 
 
+def load_csv(filepath):
+    with filepath.open() as fp:
+        yield from csv.DictReader(fp)
+
+
 if __name__ == "__main__":
     db = sqlite_utils.Database(base_path / "covid.db")
-    # Drop table if it exists
+
+    # Load John Hopkins CSSE daily reports
     table = db["daily_reports"]
     if table.exists():
         table.drop()
     table.insert_all(load_daily_reports())
     add_missing_latitude_longitude(db)
+
+    # Now do the NYTimes data
+    nyt_counties = db["ny_times_us_counties"]
+    if nyt_counties.exists():
+        nyt_counties.drop()
+    nyt_counties.insert_all(load_csv(nytimes_base / "us-counties.csv"))
+
+    nyt_states = db["ny_times_us_states"]
+    if nyt_states.exists():
+        nyt_states.drop()
+    nyt_states.insert_all(load_csv(nytimes_base / "us-states.csv"))
diff --git a/metadata.json b/metadata.json
@@ -1,7 +1,5 @@
 {
-    "title": "COVID-19 cases, using data from Johns Hopkins CSSE",
-    "source": "Johns Hopkins CSSE",
-    "source_url": "https://github.com/CSSEGISandData/COVID-19",
+    "title": "COVID-19 cases, using data from Johns Hopkins CSSE and the New York Times",
     "about_url": "https://github.com/simonw/covid-19-datasette",
     "description_html": "<div style='border: 2px solid red; padding: 1em'>⚠️ Consult <a href='https://github.com/simonw/covid-19-datasette/blob/master/README.md'>the README</a> for warnings about using and building on this data. Also review <a href='https://link.medium.com/fOScH1HUN4'>Ten Considerations Before You Create Another Chart About COVID-19</a>.</div>",
     "databases": {
@@ -11,7 +9,27 @@
                 "daily_reports": {
                     "sort_desc": "day",
                     "description_html": "<div style='border: 2px solid red; padding: 1em'>⚠️ Consult <a href='https://github.com/simonw/covid-19-datasette/blob/master/README.md'>the README</a> for warnings about using and building on this data. Also review <a href='https://link.medium.com/fOScH1HUN4'>Ten Considerations Before You Create Another Chart About COVID-19</a>.</div>",
-                    "facets": ["province_or_state", "country_or_region"]
+                    "facets": ["province_or_state", "country_or_region"],
+                    "source": "Johns Hopkins CSSE",
+                    "source_url": "https://github.com/CSSEGISandData/COVID-19"
+                },
+                "ny_times_us_states": {
+                    "sort_desc": "date",
+                    "description_html": "<div style='border: 2px solid red; padding: 1em'>⚠️ Consult <a href='https://github.com/simonw/covid-19-datasette/blob/master/README.md'>the README</a> for warnings about using and building on this data. Also review <a href='https://link.medium.com/fOScH1HUN4'>Ten Considerations Before You Create Another Chart About COVID-19</a>.</div>",
+                    "facets": ["state"],
+                    "source": "The New York Times",
+                    "source_url": "https://github.com/nytimes/covid-19-data",
+                    "license": "LICENSE",
+                    "license_url": "https://github.com/nytimes/covid-19-data/blob/master/LICENSE"
+                },
+                "ny_times_us_counties": {
+                    "sort_desc": "date",
+                    "description_html": "<div style='border: 2px solid red; padding: 1em'>⚠️ Consult <a href='https://github.com/simonw/covid-19-datasette/blob/master/README.md'>the README</a> for warnings about using and building on this data. Also review <a href='https://link.medium.com/fOScH1HUN4'>Ten Considerations Before You Create Another Chart About COVID-19</a>.</div>",
+                    "facets": ["state"],
+                    "source": "The New York Times",
+                    "source_url": "https://github.com/nytimes/covid-19-data",
+                    "license": "LICENSE",
+                    "license_url": "https://github.com/nytimes/covid-19-data/blob/master/LICENSE"
                 }
             }
         }
