{"id":727,"date":"2020-04-18T14:38:41","date_gmt":"2020-04-18T18:38:41","guid":{"rendered":"http:\/\/www.aibistin.com\/?p=727"},"modified":"2023-03-12T16:05:08","modified_gmt":"2023-03-12T20:05:08","slug":"nyc-covid-19-infections-by-zip-code","status":"publish","type":"post","link":"https:\/\/www.aibistin.com\/?p=727","title":{"rendered":"NYC Covid-19 Infections by Zip Code, with Perl"},"content":{"rendered":"\n

The NYC Department of Health started publishing their Covid-19 test testing results on GitHub .<\/a> One of their datasets tests-by-zctascv<\/a> is, in their own words.<\/p>\n

\n

This file includes the cumulative count of New York City residents by ZIP code of residence who:
Were ever tested for COVID-19 (SARS-CoV-2)
Tested positive The cumulative counts are as of the date of extraction from the NYC Health Department’s disease surveillance database.<\/p>\n<\/blockquote>\n

\"tests-by-zcta.csv\"<\/a>
GitHub View of “tests-by-zcta.csv”<\/figcaption><\/figure>\n

This file is updated almost every day and shows the number of people tested, the number who are found to have Covid-19 in each New York City Zip code. It also shows the the cumulative percentage of those tested who have the virus. <\/p>\n

What I would like to add, is more detailed information for each Zip Code so that it makes more sense to me. For each zip code, I would like to add the borough, the town, or district in that borough.  To make things a little more complicated,  NYC boroughs are divided up differently. Manhattan addresses are “New York City”, Brooklyn, Bronx and Staten Island are their own cities for mailing address purposes. Queens however is different.  Queens is broken up into towns like Flushing and Long Island City, Woodside, Jamaica etc. <\/p>\n

In a previous post Creating A Simple JSON NYC Zip Code Database File With Perl and MooX::Options<\/a> , I created a little database file to match the zip codes with the neighbourhood.<\/p>\n

Now I created a new script to download the raw raw csv data<\/a> from the NYC Department Of Health GitHub page and merge it with my little Zip Code database.<\/p>\n

See the code on GitHub<\/a><\/p>\n\n\n

\nsub get_raw_covid_data_by_zip {\n    my $self = shift;\n    my @data =\n      map { _conv_zcta_rec_to_hash($_) }\n      split( \/\\r?\\n\/, get( $self->zcta_github_link ) );\n    shift @data\n      if ( $data[0]->{cumulative_percent_of_those_tested} =~ \/zcta_cum\/ )\n      ;    # Dont need that header\n    say "Got @{[ scalar @data ]} lines of covid data. Thanks Mr. Mayor";\n    return \\@data;\n}\n<\/pre><\/div>\n\n\n
The above function uses the CPAN module LWP::Simple<\/a> which exports the ‘get’ function to download the data from GitHub. The ‘split’ function breaks the data up into individual lines, which are fed into the ‘map’ function where each individual line of data is passed into ‘_conv_zcta_rec_to_hash’ which breaks the line into a Hash, which is enriched with some extra Zip Code location information.<\/p>\n
 <\/p>\n\n\n
\nsub _conv_zcta_rec_to_hash {\n    my $str = shift;\n    state $date_h = _get_date_h();\n    my %h;\n    (\n        $h{zip}, $h{positive}, $h{total_tested},\n        $h{cumulative_percent_of_those_tested}\n    ) = split \/\\s*,\\s*\/, $str;\n\n    ( $h{zip} ) = $h{zip} =~ \/(\\d+)\/;\n    $h{zip} ||= $NA_ZIP;    # There is one undef zip in test data\n    $h{yyyymmdd} = $date_h->{yyyymmdd};\n    return \\%h;\n}\n<\/pre><\/div>\n\n\n
Here’s a sample of one line of data as a hash element.<\/p>\n\n\n
\n{\n\u00a0\u00a0\u00a0\u00a0 cumulative_percent_of_those_tested => "42.44",\n\u00a0\u00a0\u00a0\u00a0 positive\u00a0\u00a0\u00a0\u00a0 => "337",\n\u00a0\u00a0\u00a0\u00a0 total_tested => "794",\n\u00a0\u00a0\u00a0\u00a0 yyyymmdd \u00a0 \u00a0 => "20200418",\n\u00a0\u00a0\u00a0\u00a0 zip \u00a0 \u00a0 \u00a0 \u00a0\u00a0 => "10003",\n},\n<\/pre><\/div>\n\n\n
The newly created array of hashes is then serialized to JSON format and printed to a file using File::Serialize<\/a> . This will be my file database that I can use to provide other useful information.<\/p>\n\n\n
\nsub create_latest_tests_by_ztca_file {\n    my $self       = shift;\n   \n my $covid_data = $self->get_raw_covid_data_by_zip();\n \n   serialize_file $self->tests_by_zcta_db_json_file => $covid_data;\n \n   say "Created a new " . $self->tests_by_zcta_db_json_file;\n    1;\n}\n<\/pre><\/div>\n\n\n
Printing the test results to a CSV file.<\/h3>\n\n\n\n
Printing this to a C.S.V file is easy enough with Perl and Text::CSV_XS.<\/a><\/p>\n
\"\"<\/a><\/p>\n\n\n
\nsub write_latest_zcta_to_csv {\n    my ($self) = @_;\n    my @col_headers = (\n        qw\/Zip Date City District Borough\/,\n        'Total Tested', 'Positive', '% of Tested'\n    );\n    my @col_names = (\n        qw\/zip yyyymmdd city district borough total_tested positive cumulative_percent_of_those_tested \/\n    );\n    my $csv       = Text::CSV_XS->new( { binary => 1, eol => $\/ } );\n    my $zcta_file = $self->get_todays_csv_file($ALL_ZCTA_DATA_CSV);\n    my $z_fh      = $zcta_file->openw;\n    $csv->print( $z_fh, \\@col_headers ) or $csv->error_diag;\n\n    for my $one_day_zip_rec (\n        sort { $b->{positive} <=> $a->{positive} || $a->{zip} <=> $b->{zip} }\n        @{ $self->tests_by_zcta_today } )\n    {\n        my $location_rec =\n          $self->zip_db->zip_db_hash->{ $one_day_zip_rec->{zip} }\n          || _get_filler_location_rec( $one_day_zip_rec->{zip} );\n        $self->zip_db->zip_db_hash->{ $one_day_zip_rec->{zip} } ||=\n          $location_rec;\n        my %csv_rec = ( %$one_day_zip_rec, %$location_rec );\n        $csv->print( $z_fh, [ @csv_rec{@col_names} ] );\n    }\n    close($z_fh) or warn "Failed to close $zcta_file";\n    say "Created a new $zcta_file";\n}\n<\/pre><\/div>\n\n\n
my $zcta_file = $self->get_todays_csv_file($ALL_ZCTA_DATA_CSV);<\/code><\/p>\n
Uses a Moo attribute to return a csv file path with the current days timestamp.<\/p>\n
for my $one_day_zip_rec (<\/code>
sort { $b->{positive} <=> $a->{positive} || $a->{zip} <=> $b->{zip} }<\/code>
@{ $self->tests_by_zcta_today } )<\/code>
{<\/code>...<\/code><\/p>\n
When reading the current days test results data, it is sorted by the positive results. Then it’s combined with the zip code location data for that zip code, and printed. <\/p>\n
 my %csv_rec = ( %$one_day_zip_rec, %$location_rec );<\/code>
$csv->print( $z_fh, [ @csv_rec{@col_names} ] );<\/code><\/p>\n
Below is a sample CSV file for April 17 2020.<\/p>\n\n\n\n
20200417_all_zcta_data<\/a>Download<\/a><\/div>\n\n\n\n
Next we can create nice Plotly charts to display the test results.<\/p>\n\n\n\n\n\n\n\n
Display The Data With Plotly<\/h3>\n\n\n\n
We can use the command line interface to the Perl script to create nice HTML charts to display the results. <\/p>\n\n\n
\nperl bin\\city_covid_data.pl -h\nUSAGE: city_covid_data.pl [-h] [long options ...]\n\n    -n --create_new_zcta_db     Create a new NYC Zip Cumulative Test 'A'\n                                JSON db for todays result\n    --show_zip_stats=[Strings]  Get the available statistics of a given zip\n                                code or codes\n    -v --verbose                Print details\n    -c --write_zcta_to_csv      Print latest ZCTA data to csv, 'output\/\n                                all_zcta_data.csv'\n\n    --usage                     show a short help message\n    -h                          show a compact help message\n    --help                      show a long help message\n    --man                       show the manual\n<\/pre><\/div>\n\n\n
 perl bin\\city_covid_data.pl --show_zip_stats 11368,10467,11373,11219<\/code><\/p>\n
The example here displays the test results for 4 New York City zip codes that happen to have the highest incidents of positive test results.  To help display the results in a graphical way, I found this great Perl module, Chart::Plotly<\/a> and Chart::Plotly::Trace::Bar<\/a> , which is a Perl interface to the Plotly<\/a>  JavaScript library.<\/p>\n\n\n
\nsub show_stats_for_zips {\n    my ( $self, $zip_codes ) = @_;\n\n    my @chart_names = ref($zip_codes) eq 'ARRAY' ? @{$zip_codes} : ($zip_codes);\n    my $date_conv_func   = $self->date_to_str_func();\n    my $stats_cache_func = _get_zip_chart_stats_cache_func();\n\n    my @charts;\n    for my $zip_code (@chart_names) {\n        my $zip_code_stats = $stats_cache_func->( $self, $zip_code );\n        my $chart = Chart::Plotly::Trace::Bar->new(\n            x => [\n                map { $date_conv_func->($_) }\n                  @{ $zip_code_stats->{dates} || [] }\n            ],\n            y => [ @{ $zip_code_stats->{positive} || [] } ],\n            name => $self->city_district($zip_code),\n            text => $zip_code,\n        );\n        push @charts, $chart;\n    }\n\t\n    my $bar_chart = Chart::Plotly::Plot->new(\n        traces => [@charts],\n        layout => { barmode => 'group' }\n    );\n\n    Chart::Plotly::show_plot($bar_chart);\n}\n<\/pre><\/div>\n\n\n
You can download the HTML chart below.<\/p>\n\n\n\n
razHLMp7kO<\/a>Download<\/a><\/div>\n\n\n\n\n\n
The results are displayed in a nice HTML file with the Plotly chart.<\/p>\n
\"\"<\/a><\/p>\n
The code: https:\/\/github.com\/aibistin\/covid<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
The NYC Department of Health started publishing their Covid-19 test testing results on GitHub . One of their datasets tests-by-zctascv is, in their own words. This file includes the cumulative count of New York City residents by ZIP code of residence who:Were ever tested for COVID-19 (SARS-CoV-2)Tested positive The cumulative counts are as of the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[105,41,103,101,99],"tags":[54,59,47,61,57,95,58,20,55,60,56,50],"class_list":["post-727","post","type-post","status-publish","format-standard","hentry","category-csv","category-mooxoptions-perl","category-new-york-city","category-perl","category-programming","tag-chartplotly","tag-covid-19","tag-fileserialize","tag-lwpsimple","tag-moo-mooxoptions","tag-new-york-city","tag-nyc","tag-perl","tag-plotly","tag-queens","tag-textcsv_xs","tag-zipcodes"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.aibistin.com\/index.php?rest_route=\/wp\/v2\/posts\/727","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.aibistin.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.aibistin.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.aibistin.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.aibistin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=727"}],"version-history":[{"count":25,"href":"https:\/\/www.aibistin.com\/index.php?rest_route=\/wp\/v2\/posts\/727\/revisions"}],"predecessor-version":[{"id":902,"href":"https:\/\/www.aibistin.com\/index.php?rest_route=\/wp\/v2\/posts\/727\/revisions\/902"}],"wp:attachment":[{"href":"https:\/\/www.aibistin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=727"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.aibistin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=727"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.aibistin.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=727"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}