From de44fdf92a65c25f2aba6a445287a4572a8837cc Mon Sep 17 00:00:00 2001 From: Vincent Ambo Date: Fri, 27 Nov 2020 03:21:16 +0100 Subject: feat(tazjin/covid): Add jq script for wrangling CDC data Change-Id: Ia23f528d28126d6a2d0dd2d7327ec686c7e90df4 Reviewed-on: https://cl.tvl.fyi/c/depot/+/2158 Reviewed-by: tazjin Tested-by: BuildkiteCI --- users/tazjin/covid/us_mortality.jq | 36 ++++++++++++++++++++++++++++++++++++ 1 file changed, 36 insertions(+) create mode 100644 users/tazjin/covid/us_mortality.jq diff --git a/users/tazjin/covid/us_mortality.jq b/users/tazjin/covid/us_mortality.jq new file mode 100644 index 000000000000..584be3ef9afe --- /dev/null +++ b/users/tazjin/covid/us_mortality.jq @@ -0,0 +1,36 @@ +# This turns the CDC mortality data[0] into a format useful for my +# excess mortality spreadsheet. The US format is by far the worst one +# I have dealt with, as expected. +# +# This requires miller for transforming the CSV appropriately. +# +# Params: +# state: abbreviation of the state to extract ('US' for whole country) +# period: time period (either "2020" for current data, or anything else +# for historical averages) +# +# Call as: +# mlr --icsv --ojson cat weekly.csv | \ +# jq -rsf us_mortality.jq --arg state US --arg period 2020 +# +# [0]: https://www.cdc.gov/nchs/nvss/vsrr/covid19/excess_deaths.htm + +def filter_period(period): + if period == "2020" + then . | map(select(.["Time Period"] == 2020)) + else . | map(select(.["Time Period"] == "2015-2019")) + end; + +def collate_weeks(period): + (. | map(.["Number of Deaths"]) | add) as $count + | { + count: (if period == "2020" then $count else $count / 5 end), + week: .[0].Week, + }; + +. | map(select(.Type == "Predicted (weighted)")) + | map(select(.["State Abbreviation"] == $state)) + | filter_period($period) + | group_by(.Week) + | map(collate_weeks($period)) + | .[] | "week \(.week): \(.count)" -- cgit 1.4.1