Update available columns in covid_hosp datasets

[krivard]

There have been quite a few columns added to the HHS datasets on patient impact and hospital capacity since we last updated our code, including:

• age stratifications added April 30
• covid deaths added June 30
• drug courses on hand added September 13
• influenza admissions, ICU prevalence, and deaths, added September 17

The above are the additions for state timeseries; state daily and facility weekly may have a different set.

The code for this dataset is in acquisition/covid_hosp. There are three variants: state timeseries, state daily, and facility (weekly).

Updating that code requires the following steps:

1. Look up the CSV column headers for all new columns on the appropriate website (state timeseries is here; you should be able to find links for the others in the Epidata docs on non-covidcast endpoints)
2. Add the new columns to the database definitions in covid_hosp.sql (possibly using an alias name to reduce column name length in the db)
3. List the mapping between CSV column header, DB column name, and data type in each dataset's database.py (example: state timeseries)
4. Update or extend the tests (unit; integration) with new sample data containing the new columns
5. Write a database migration script (in SQL) to add the new columns to the existing database. We haven't historically done this in a reviewable fashion, but we should really start. Make a new directory src/ddl/migrations/ to hold this script, and include the acquisition module (covid_hosp) in the name, as well as the starting and ending minor versions (we're currently in v0.2; this change includes a database migration so it will push us into v0.3. You can always see the current complete version on the repository releases page)
6. Open a PR and tag a reviewer.
7. Once your PR is approved, merge it, then ping Katie to schedule a minor-version release. Release should occur after the day's covid_hosp updates run in Automation, which are scheduled for 10am but have been delayed lately due to the covidcast meta cache running time problems. Avoid releasing on Fridays for everyone's health.
8. After release and before the next day's covid_hosp update jobs run in Automation, run the migration.

[krivard]

I've stashed changes in progress in branch krivard/covid_hosp_column_update. These are going to be more annoying than expected; there are a pile of column changes, there's no easily machine-readable copy of the data dictionary, and columns are listed in chronological order rather than alphabetical so finding anything is a challenge.

The starter commit there is me downloading the most recent CSV, extracting the column headers, diffing them against the ones we already have, then adding database.py entries and covid_hosp.sql entries for them, using the sample data to figure out rough widths for the new VARCHARs.

[krivard]

@chinandrew @alexcoda is this something you two might be willing to help with?

[chinandrew]

Yea I can work on this. Looks like your changes finished 1-2 and part of 3 for facility, and it needs to be finished along with all steps for state daily and state timeseries, is that right?

As a side note, I wonder how much value we're providing by actually maintaining, since HHS versions their data anyway and we're just duplicating all their information. the hhs_hosp indicator could just as easily pd.read_csv the one they host.

[krivard]

They version their data, but iirc their interface for figuring out which version to fetch for a particular as-of is nonexistent at best. I'd also be surprised if they integrated their daily and timeseries updates.

Something we might consider though is moving all the logic we have here in acquisition for that into the new delphi-epidata client libraries and just have them hit healthdata.gov directly. That would let us make the as-of stuff easy and invisible to users without having to store the HHS data and cope with the additional level of versioning.

[chinandrew]

I'd also be surprised if they integrated their daily and timeseries updates.

ah right I forgot about that, good point.

[krivard]

I've started a spreadsheet for these to make them easier to manage. We can do a thing like the advanced meta to pull them in and programmatically rewrite the .sql and database.py files if we are very careful about it

[chinandrew]

advanced meta

what's the procedure here? I thought since we removed the dependence on column order (also realized I forgot to rename the variable from ORDERED_CSV_COLUMNS to CSV_COLUMNS). we'd do something like add the columns to the database.py file, pause automation, run the migration script (which is basically alter table add column ...) and then restart automation? and then backfill would have to be handled separately

[chinandrew]

Did some work on state_daily and timeseries, didn't touch facility.

I've pushed updated database.py and .sql files to your branch, as well as added a covid_hosp_state_v0.2-v0.3.sql to ddl/migrations. Note that database.py file has been completely reordered to be alphabetical, except for the two KEY_COLS which are left at the top (could change them but would require updating a few tests). The .sql command just has the new columns added onto the end in the order they appear in the header with a comment delineating them from existing columns. Should probably standardize everything, but I had left it for now to align with the migration file (and partially due to laziness).

Tests have also been updated, though because the laptop Im currently using doesn't have docker I've only run the unit tests on CI which are passing except for facility ones. The integration tests should pass, and if not I imagine it's only a minor fix.

Open items TODO for state daily/timeseries:

• Add column descriptions to .sql docstring and to the column table
• Update the description table in the .sql file (which was previously generated after generating the table and describing it in SQL).

For this column, it appears to be all nans and i've just set the type to int even though it's a... "Point" datatype? Everything else appears to be ints .

geocoded_state | 61. The geocoded centroid of the given state. | Point |  

Also state timeseries and state daily have this lovely consistent header:

In [69]: set(header_daily) - set(header)
Out[69]: 
{'previous_day_admission_adult_covid_suspected_60_69',  (all the other columns use the dash)
 'previous_day_admission_adult_covid_suspected_80',
 'reporting_cutoff_start'}

In [70]: set(header) - set(header_daily)
Out[70]: 
{'date',
 'previous_day_admission_adult_covid_suspected_60-69', 
 'previous_day_admission_adult_covid_suspected_80+'}

I've chosen to standardize against the timeseries version in the sql column names.

Complete list of the 57 new columns (with timeseries headers):

[('deaths_covid', 'deaths_covid', int),
 ('deaths_covid_coverage', 'deaths_covid_coverage', int),
 ('geocoded_state', 'geocoded_state', int),
 ('icu_patients_confirmed_influenza', 'icu_patients_confirmed_influenza', int),
 ('icu_patients_confirmed_influenza_coverage',
  'icu_patients_confirmed_influenza_coverage',
  int),
 ('on_hand_supply_therapeutic_a_casirivimab_imdevimab_courses',
  'on_hand_supply_therapeutic_a_casirivimab_imdevimab_courses',
  int),
 ('on_hand_supply_therapeutic_b_bamlanivimab_courses',
  'on_hand_supply_therapeutic_b_bamlanivimab_courses',
  int),
 ('on_hand_supply_therapeutic_c_bamlanivimab_etesevimab_courses',
  'on_hand_supply_therapeutic_c_bamlanivimab_etesevimab_courses',
  int),
 ('previous_day_admission_adult_covid_confirmed_18-19',
  'previous_day_admission_adult_covid_confirmed_18-19',
  int),
 ('previous_day_admission_adult_covid_confirmed_18-19_coverage',
  'previous_day_admission_adult_covid_confirmed_18-19_coverage',
  int),
 ('previous_day_admission_adult_covid_confirmed_20-29',
  'previous_day_admission_adult_covid_confirmed_20-29',
  int),
 ('previous_day_admission_adult_covid_confirmed_20-29_coverage',
  'previous_day_admission_adult_covid_confirmed_20-29_coverage',
  int),
 ('previous_day_admission_adult_covid_confirmed_30-39',
  'previous_day_admission_adult_covid_confirmed_30-39',
  int),
 ('previous_day_admission_adult_covid_confirmed_30-39_coverage',
  'previous_day_admission_adult_covid_confirmed_30-39_coverage',
  int),
 ('previous_day_admission_adult_covid_confirmed_40-49',
  'previous_day_admission_adult_covid_confirmed_40-49',
  int),
 ('previous_day_admission_adult_covid_confirmed_40-49_coverage',
  'previous_day_admission_adult_covid_confirmed_40-49_coverage',
  int),
 ('previous_day_admission_adult_covid_confirmed_50-59',
  'previous_day_admission_adult_covid_confirmed_50-59',
  int),
 ('previous_day_admission_adult_covid_confirmed_50-59_coverage',
  'previous_day_admission_adult_covid_confirmed_50-59_coverage',
  int),
 ('previous_day_admission_adult_covid_confirmed_60-69',
  'previous_day_admission_adult_covid_confirmed_60-69',
  int),
 ('previous_day_admission_adult_covid_confirmed_60-69_coverage',
  'previous_day_admission_adult_covid_confirmed_60-69_coverage',
  int),
 ('previous_day_admission_adult_covid_confirmed_70-79',
  'previous_day_admission_adult_covid_confirmed_70-79',
  int),
 ('previous_day_admission_adult_covid_confirmed_70-79_coverage',
  'previous_day_admission_adult_covid_confirmed_70-79_coverage',
  int),
 ('previous_day_admission_adult_covid_confirmed_80+',
  'previous_day_admission_adult_covid_confirmed_80+',
  int),
 ('previous_day_admission_adult_covid_confirmed_80+_coverage',
  'previous_day_admission_adult_covid_confirmed_80+_coverage',
  int),
 ('previous_day_admission_adult_covid_confirmed_unknown',
  'previous_day_admission_adult_covid_confirmed_unknown',
  int),
 ('previous_day_admission_adult_covid_confirmed_unknown_coverage',
  'previous_day_admission_adult_covid_confirmed_unknown_coverage',
  int),
 ('previous_day_admission_adult_covid_suspected_18-19',
  'previous_day_admission_adult_covid_suspected_18-19',
  int),
 ('previous_day_admission_adult_covid_suspected_18-19_coverage',
  'previous_day_admission_adult_covid_suspected_18-19_coverage',
  int),
 ('previous_day_admission_adult_covid_suspected_20-29',
  'previous_day_admission_adult_covid_suspected_20-29',
  int),
 ('previous_day_admission_adult_covid_suspected_20-29_coverage',
  'previous_day_admission_adult_covid_suspected_20-29_coverage',
  int),
 ('previous_day_admission_adult_covid_suspected_30-39',
  'previous_day_admission_adult_covid_suspected_30-39',
  int),
 ('previous_day_admission_adult_covid_suspected_30-39_coverage',
  'previous_day_admission_adult_covid_suspected_30-39_coverage',
  int),
 ('previous_day_admission_adult_covid_suspected_40-49',
  'previous_day_admission_adult_covid_suspected_40-49',
  int),
 ('previous_day_admission_adult_covid_suspected_40-49_coverage',
  'previous_day_admission_adult_covid_suspected_40-49_coverage',
  int),
 ('previous_day_admission_adult_covid_suspected_50-59',
  'previous_day_admission_adult_covid_suspected_50-59',
  int),
 ('previous_day_admission_adult_covid_suspected_50-59_coverage',
  'previous_day_admission_adult_covid_suspected_50-59_coverage',
  int),
 ('previous_day_admission_adult_covid_suspected_60-69',
  'previous_day_admission_adult_covid_suspected_60-69',
  int),
 ('previous_day_admission_adult_covid_suspected_60-69_coverage',
  'previous_day_admission_adult_covid_suspected_60-69_coverage',
  int),
 ('previous_day_admission_adult_covid_suspected_70-79',
  'previous_day_admission_adult_covid_suspected_70-79',
  int),
 ('previous_day_admission_adult_covid_suspected_70-79_coverage',
  'previous_day_admission_adult_covid_suspected_70-79_coverage',
  int),
 ('previous_day_admission_adult_covid_suspected_80+',
  'previous_day_admission_adult_covid_suspected_80+',
  int),
 ('previous_day_admission_adult_covid_suspected_80+_coverage',
  'previous_day_admission_adult_covid_suspected_80+_coverage',
  int),
 ('previous_day_admission_adult_covid_suspected_unknown',
  'previous_day_admission_adult_covid_suspected_unknown',
  int),
 ('previous_day_admission_adult_covid_suspected_unknown_coverage',
  'previous_day_admission_adult_covid_suspected_unknown_coverage',
  int),
 ('previous_day_admission_influenza_confirmed',
  'previous_day_admission_influenza_confirmed',
  int),
 ('previous_day_admission_influenza_confirmed_coverage',
  'previous_day_admission_influenza_confirmed_coverage',
  int),
 ('previous_day_deaths_covid_and_influenza',
  'previous_day_deaths_covid_and_influenza',
  int),
 ('previous_day_deaths_covid_and_influenza_coverage',
  'previous_day_deaths_covid_and_influenza_coverage',
  int),
 ('previous_day_deaths_influenza', 'previous_day_deaths_influenza', int),
 ('previous_day_deaths_influenza_coverage',
  'previous_day_deaths_influenza_coverage',
  int),
 ('previous_week_therapeutic_a_casirivimab_imdevimab_courses_used',
  'previous_week_therapeutic_a_casirivimab_imdevimab_courses_used',
  int),
 ('previous_week_therapeutic_b_bamlanivimab_courses_used',
  'previous_week_therapeutic_b_bamlanivimab_courses_used',
  int),
 ('previous_week_therapeutic_c_bamlanivimab_etesevimab_courses_used',
  'previous_week_therapeutic_c_bamlanivimab_etesevimab_courses_used',
  int),
 ('total_patients_hospitalized_confirmed_influenza',
  'total_patients_hospitalized_confirmed_influenza',
  int),
 ('total_patients_hospitalized_confirmed_influenza_coverage',
  'total_patients_hospitalized_confirmed_influenza_coverage',
  int),
 ('total_patients_hospitalized_confirmed_influenza_covid',
  'total_patients_hospitalized_confirmed_influenza_covid', 
  int),
 ('total_patients_hospitalized_confirmed_influenza_covid_coverage',
  'total_patients_hospitalized_confirmed_influenza_covid_coverage', 
  int)]

[krivard]

advanced meta

what's the procedure here?

I don't know how to make sense of your question in the context of advanced meta; did you mean to quote something else?

If I ignore the quote though -- yeah it probably is wise to pause automation for this in case something goes wrong; I think I figured the 12 hour window between the 10am and 10pm HHS update jobs would be plenty to go without but it's always better to be in a safe state just in case something goes horribly wrong.

For backfill we can either write a custom script to fill just the missing columns from files already pulled, or do something like:

1. back up all the covid_hosp tables
2. truncate all the covid_hosp tables
3. run fill_covid_hosp_skipped_issue.py for all three datasets' entire history (is that in git somewhere? if not I can put it in)

[chinandrew]

Sorry for confusion, I was juts wondering what "We can do a thing like the advanced meta" meant, since I'm not sure what advanced meta does.