Last active
January 4, 2021 04:43
-
-
Save jufemaiz/2be7a7f09817c1fba868f6068fde94bc to your computer and use it in GitHub Desktop.
AU-NSW COVID-19 data aggregation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# frozen_string_literal: true | |
require 'csv' | |
require 'fileutils' | |
require 'open-uri' | |
TEMP_DIRECTORY = 'tmp' | |
# Create temp file | |
FileUtils.mkdir_p(File.join(Dir.getwd, TEMP_DIRECTORY)) | |
FILES = { | |
raw: 'https://data.nsw.gov.au/data/dataset/60616720-3c60-4c52-b499-751f31e3b132/resource/945c6204-272a-4cad-8e33-dde791f5059a/download/pcr_testing_table1_location.csv', | |
agg: 'https://data.nsw.gov.au/data/dataset/60616720-3c60-4c52-b499-751f31e3b132/resource/fb95de01-ad82-4716-ab9a-e15cf2c78556/download/pcr_testing_table1_location_agg.csv' | |
}.freeze | |
# Download files to work with | |
# FILES.each_pair do |_, url| | |
# filename = url.split('/').last | |
# File.open(File.join(Dir.getwd, TEMP_DIRECTORY, filename), 'wb') do |file| | |
# file.write(URI.open(url).read) | |
# end | |
# end | |
# Summarise by day | |
data_by_day = { raw: {}, agg: {} } | |
FILES.each_pair do |key, url| | |
filename = url.split('/').last | |
CSV.foreach(File.join(Dir.getwd, TEMP_DIRECTORY, filename), headers: true) do |row| | |
data_by_day[key][row['test_date']] ||= 0 | |
data_by_day[key][row['test_date']] += row['test_count'].nil? ? 1 : row['test_count'].to_i | |
end | |
end | |
dates = (data_by_day[:raw].keys | data_by_day[:agg].keys).sort | |
# Output a CSV with a column for agg and a column for raw | |
CSV.open(File.join(Dir.getwd, TEMP_DIRECTORY, 'summary.csv'), 'wb') do |csv| | |
csv << ['date', 'raw', 'agg'] | |
dates.each do |date| | |
csv << [date, data_by_day[:raw][date].to_i, data_by_day[:agg][date].to_i] | |
end | |
end |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
date | raw | agg | |
---|---|---|---|
2020-01-01 | 25 | 23 | |
2020-01-08 | 1 | 1 | |
2020-01-10 | 1 | 1 | |
2020-01-14 | 1 | 1 | |
2020-01-20 | 1 | 1 | |
2020-01-22 | 3 | 3 | |
2020-01-23 | 12 | 11 | |
2020-01-24 | 16 | 13 | |
2020-01-25 | 9 | 7 | |
2020-01-26 | 19 | 12 | |
2020-01-27 | 15 | 11 | |
2020-01-28 | 28 | 27 | |
2020-01-29 | 28 | 26 | |
2020-01-30 | 39 | 34 | |
2020-01-31 | 23 | 18 | |
2020-02-01 | 22 | 18 | |
2020-02-02 | 45 | 37 | |
2020-02-03 | 108 | 91 | |
2020-02-04 | 80 | 62 | |
2020-02-05 | 94 | 68 | |
2020-02-06 | 80 | 70 | |
2020-02-07 | 93 | 79 | |
2020-02-08 | 61 | 54 | |
2020-02-09 | 33 | 31 | |
2020-02-10 | 107 | 98 | |
2020-02-11 | 100 | 92 | |
2020-02-12 | 106 | 89 | |
2020-02-13 | 102 | 95 | |
2020-02-14 | 87 | 83 | |
2020-02-15 | 52 | 47 | |
2020-02-16 | 40 | 37 | |
2020-02-17 | 147 | 130 | |
2020-02-18 | 174 | 149 | |
2020-02-19 | 140 | 133 | |
2020-02-20 | 128 | 125 | |
2020-02-21 | 154 | 144 | |
2020-02-22 | 65 | 61 | |
2020-02-23 | 48 | 44 | |
2020-02-24 | 219 | 206 | |
2020-02-25 | 194 | 180 | |
2020-02-26 | 252 | 231 | |
2020-02-27 | 238 | 232 | |
2020-02-28 | 242 | 230 | |
2020-02-29 | 130 | 123 | |
2020-03-01 | 131 | 120 | |
2020-03-02 | 591 | 562 | |
2020-03-03 | 658 | 641 | |
2020-03-04 | 849 | 818 | |
2020-03-05 | 1142 | 1090 | |
2020-03-06 | 1397 | 1346 | |
2020-03-07 | 570 | 544 | |
2020-03-08 | 563 | 526 | |
2020-03-09 | 3899 | 3664 | |
2020-03-10 | 4108 | 3717 | |
2020-03-11 | 3394 | 3264 | |
2020-03-12 | 3529 | 3369 | |
2020-03-13 | 4517 | 4355 | |
2020-03-14 | 2084 | 2003 | |
2020-03-15 | 1754 | 1682 | |
2020-03-16 | 7352 | 7118 | |
2020-03-17 | 6912 | 6668 | |
2020-03-18 | 6374 | 6135 | |
2020-03-19 | 5935 | 5741 | |
2020-03-20 | 5692 | 5473 | |
2020-03-21 | 2651 | 2565 | |
2020-03-22 | 2089 | 1999 | |
2020-03-23 | 6634 | 6323 | |
2020-03-24 | 6477 | 6135 | |
2020-03-25 | 6010 | 5768 | |
2020-03-26 | 6360 | 6011 | |
2020-03-27 | 5934 | 5691 | |
2020-03-28 | 2785 | 2620 | |
2020-03-29 | 1992 | 1847 | |
2020-03-30 | 5483 | 5230 | |
2020-03-31 | 4307 | 4147 | |
2020-04-01 | 4435 | 4244 | |
2020-04-02 | 4311 | 4149 | |
2020-04-03 | 3902 | 3776 | |
2020-04-04 | 2020 | 1904 | |
2020-04-05 | 1509 | 1424 | |
2020-04-06 | 4475 | 4279 | |
2020-04-07 | 4964 | 4739 | |
2020-04-08 | 4318 | 4151 | |
2020-04-09 | 3866 | 3708 | |
2020-04-10 | 1521 | 1438 | |
2020-04-11 | 1525 | 1441 | |
2020-04-12 | 1203 | 1148 | |
2020-04-13 | 1687 | 1623 | |
2020-04-14 | 5279 | 5108 | |
2020-04-15 | 5923 | 5788 | |
2020-04-16 | 7291 | 7094 | |
2020-04-17 | 7197 | 7040 | |
2020-04-18 | 3788 | 3698 | |
2020-04-19 | 2993 | 2919 | |
2020-04-20 | 6665 | 6489 | |
2020-04-21 | 5932 | 5779 | |
2020-04-22 | 5453 | 5271 | |
2020-04-23 | 5573 | 5432 | |
2020-04-24 | 6630 | 6452 | |
2020-04-25 | 4560 | 4474 | |
2020-04-26 | 3874 | 3778 | |
2020-04-27 | 8789 | 8639 | |
2020-04-28 | 9123 | 8943 | |
2020-04-29 | 9662 | 9421 | |
2020-04-30 | 8706 | 8530 | |
2020-05-01 | 8798 | 8624 | |
2020-05-02 | 6045 | 5935 | |
2020-05-03 | 4922 | 4821 | |
2020-05-04 | 12262 | 11924 | |
2020-05-05 | 12984 | 12580 | |
2020-05-06 | 12407 | 11947 | |
2020-05-07 | 11347 | 10799 | |
2020-05-08 | 10893 | 10439 | |
2020-05-09 | 6492 | 5688 | |
2020-05-10 | 4195 | 4046 | |
2020-05-11 | 11966 | 11268 | |
2020-05-12 | 11696 | 11353 | |
2020-05-13 | 11663 | 11347 | |
2020-05-14 | 11373 | 11043 | |
2020-05-15 | 10212 | 9633 | |
2020-05-16 | 5733 | 5256 | |
2020-05-17 | 4889 | 4404 | |
2020-05-18 | 11081 | 10549 | |
2020-05-19 | 10967 | 10443 | |
2020-05-20 | 9813 | 9208 | |
2020-05-21 | 9676 | 9237 | |
2020-05-22 | 7805 | 7459 | |
2020-05-23 | 4507 | 4250 | |
2020-05-24 | 3855 | 3624 | |
2020-05-25 | 10251 | 9711 | |
2020-05-26 | 10638 | 10082 | |
2020-05-27 | 10767 | 10369 | |
2020-05-28 | 11199 | 10755 | |
2020-05-29 | 9618 | 9099 | |
2020-05-30 | 5381 | 4801 | |
2020-05-31 | 4878 | 4411 | |
2020-06-01 | 12900 | 12496 | |
2020-06-02 | 12171 | 11641 | |
2020-06-03 | 11089 | 10637 | |
2020-06-04 | 11066 | 10597 | |
2020-06-05 | 10034 | 9416 | |
2020-06-06 | 5357 | 5030 | |
2020-06-07 | 4788 | 4399 | |
2020-06-08 | 5541 | 5162 | |
2020-06-09 | 17054 | 16406 | |
2020-06-10 | 14834 | 14330 | |
2020-06-11 | 13258 | 12779 | |
2020-06-12 | 12400 | 11922 | |
2020-06-13 | 6560 | 6106 | |
2020-06-14 | 6695 | 6328 | |
2020-06-15 | 18700 | 17778 | |
2020-06-16 | 18185 | 17331 | |
2020-06-17 | 15831 | 15236 | |
2020-06-18 | 14866 | 14314 | |
2020-06-19 | 12921 | 12446 | |
2020-06-20 | 6800 | 6477 | |
2020-06-21 | 7713 | 7102 | |
2020-06-22 | 20674 | 19835 | |
2020-06-23 | 18940 | 17943 | |
2020-06-24 | 18087 | 17047 | |
2020-06-25 | 18455 | 17496 | |
2020-06-26 | 16964 | 16246 | |
2020-06-27 | 9282 | 8743 | |
2020-06-28 | 8059 | 7337 | |
2020-06-29 | 20639 | 19632 | |
2020-06-30 | 19022 | 17941 | |
2020-07-01 | 16887 | 15889 | |
2020-07-02 | 16090 | 14779 | |
2020-07-03 | 14909 | 13743 | |
2020-07-04 | 8312 | 7203 | |
2020-07-05 | 7464 | 6159 | |
2020-07-06 | 18010 | 16527 | |
2020-07-07 | 16777 | 15130 | |
2020-07-08 | 16567 | 15377 | |
2020-07-09 | 15926 | 14623 | |
2020-07-10 | 14589 | 13403 | |
2020-07-11 | 10645 | 9737 | |
2020-07-12 | 10371 | 8969 | |
2020-07-13 | 25624 | 23751 | |
2020-07-14 | 27560 | 25979 | |
2020-07-15 | 27669 | 25574 | |
2020-07-16 | 26942 | 25507 | |
2020-07-17 | 24037 | 22788 | |
2020-07-18 | 15865 | 15021 | |
2020-07-19 | 14622 | 13735 | |
2020-07-20 | 31685 | 30057 | |
2020-07-21 | 30857 | 29342 | |
2020-07-22 | 29102 | 27777 | |
2020-07-23 | 26842 | 25637 | |
2020-07-24 | 23738 | 22579 | |
2020-07-25 | 15782 | 14531 | |
2020-07-26 | 10557 | 9698 | |
2020-07-27 | 26564 | 24910 | |
2020-07-28 | 26077 | 24689 | |
2020-07-29 | 24626 | 23249 | |
2020-07-30 | 24157 | 22929 | |
2020-07-31 | 24331 | 22792 | |
2020-08-01 | 13571 | 12755 | |
2020-08-02 | 11955 | 11146 | |
2020-08-03 | 32690 | 31061 | |
2020-08-04 | 29292 | 27874 | |
2020-08-05 | 27599 | 25817 | |
2020-08-06 | 28981 | 27287 | |
2020-08-07 | 26280 | 24975 | |
2020-08-08 | 13173 | 12413 | |
2020-08-09 | 11657 | 10949 | |
2020-08-10 | 29214 | 27474 | |
2020-08-11 | 26968 | 25528 | |
2020-08-12 | 28064 | 26258 | |
2020-08-13 | 23685 | 22023 | |
2020-08-14 | 22806 | 21394 | |
2020-08-15 | 11919 | 11020 | |
2020-08-16 | 9946 | 9220 | |
2020-08-17 | 30388 | 28541 | |
2020-08-18 | 33827 | 32120 | |
2020-08-19 | 33202 | 31569 | |
2020-08-20 | 29514 | 28419 | |
2020-08-21 | 25520 | 24073 | |
2020-08-22 | 12468 | 11591 | |
2020-08-23 | 11225 | 10385 | |
2020-08-24 | 36150 | 34667 | |
2020-08-25 | 31079 | 29756 | |
2020-08-26 | 24733 | 23425 | |
2020-08-27 | 25679 | 24431 | |
2020-08-28 | 23531 | 22361 | |
2020-08-29 | 12026 | 11098 | |
2020-08-30 | 10870 | 9961 | |
2020-08-31 | 33971 | 32631 | |
2020-09-01 | 26301 | 24977 | |
2020-09-02 | 23737 | 22563 | |
2020-09-03 | 20804 | 19575 | |
2020-09-04 | 18510 | 17384 | |
2020-09-05 | 9825 | 8828 | |
2020-09-06 | 8845 | 7962 | |
2020-09-07 | 27320 | 26016 | |
2020-09-08 | 22356 | 21157 | |
2020-09-09 | 21469 | 20297 | |
2020-09-10 | 17437 | 16299 | |
2020-09-11 | 15594 | 14368 | |
2020-09-12 | 8446 | 7390 | |
2020-09-13 | 7628 | 6692 | |
2020-09-14 | 23313 | 22173 | |
2020-09-15 | 18339 | 17296 | |
2020-09-16 | 17022 | 16020 | |
2020-09-17 | 14918 | 13918 | |
2020-09-18 | 13051 | 12100 | |
2020-09-19 | 7534 | 6475 | |
2020-09-20 | 7234 | 6147 | |
2020-09-21 | 19156 | 18074 | |
2020-09-22 | 14524 | 13530 | |
2020-09-23 | 13559 | 12606 | |
2020-09-24 | 12654 | 11657 | |
2020-09-25 | 10899 | 9979 | |
2020-09-26 | 6801 | 5991 | |
2020-09-27 | 6082 | 5063 | |
2020-09-28 | 15202 | 14172 | |
2020-09-29 | 12402 | 11473 | |
2020-09-30 | 11758 | 10806 | |
2020-10-01 | 9916 | 9003 | |
2020-10-02 | 8862 | 7860 | |
2020-10-03 | 5678 | 4846 | |
2020-10-04 | 5053 | 4236 | |
2020-10-05 | 5617 | 4764 | |
2020-10-06 | 14356 | 13365 | |
2020-10-07 | 12431 | 11486 | |
2020-10-08 | 14516 | 13482 | |
2020-10-09 | 12153 | 11232 | |
2020-10-10 | 7462 | 6555 | |
2020-10-11 | 7065 | 6180 | |
2020-10-12 | 19202 | 18139 | |
2020-10-13 | 14105 | 13117 | |
2020-10-14 | 14928 | 13889 | |
2020-10-15 | 14295 | 13223 | |
2020-10-16 | 12177 | 11218 | |
2020-10-17 | 7021 | 6232 | |
2020-10-18 | 6701 | 5849 | |
2020-10-19 | 17507 | 16386 | |
2020-10-20 | 13850 | 12807 | |
2020-10-21 | 13089 | 12057 | |
2020-10-22 | 13981 | 12856 | |
2020-10-23 | 11406 | 10399 | |
2020-10-24 | 7038 | 6120 | |
2020-10-25 | 5785 | 4978 | |
2020-10-26 | 16543 | 15430 | |
2020-10-27 | 14553 | 13425 | |
2020-10-28 | 13956 | 12869 | |
2020-10-29 | 14548 | 13476 | |
2020-10-30 | 12296 | 11247 | |
2020-10-31 | 7349 | 6501 | |
2020-11-01 | 7205 | 6248 | |
2020-11-02 | 19958 | 18819 | |
2020-11-03 | 18761 | 17533 | |
2020-11-04 | 16319 | 15282 | |
2020-11-05 | 14649 | 13598 | |
2020-11-06 | 14853 | 13785 | |
2020-11-07 | 9038 | 8032 | |
2020-11-08 | 8594 | 7611 | |
2020-11-09 | 27200 | 25796 | |
2020-11-10 | 20390 | 19210 | |
2020-11-11 | 17704 | 16571 | |
2020-11-12 | 17002 | 15945 | |
2020-11-13 | 14671 | 13568 | |
2020-11-14 | 7782 | 6787 | |
2020-11-15 | 7541 | 6600 | |
2020-11-16 | 22268 | 20830 | |
2020-11-17 | 18168 | 16479 | |
2020-11-18 | 17172 | 15788 | |
2020-11-19 | 15269 | 13908 | |
2020-11-20 | 12206 | 11051 | |
2020-11-21 | 6688 | 5699 | |
2020-11-22 | 6496 | 5484 | |
2020-11-23 | 19243 | 17901 | |
2020-11-24 | 14021 | 12937 | |
2020-11-25 | 13906 | 12705 | |
2020-11-26 | 11345 | 10363 | |
2020-11-27 | 10468 | 9061 | |
2020-11-28 | 5511 | 4573 | |
2020-11-29 | 4946 | 4072 | |
2020-11-30 | 15984 | 14679 | |
2020-12-01 | 12197 | 11078 | |
2020-12-02 | 10911 | 9873 | |
2020-12-03 | 11676 | 10669 | |
2020-12-04 | 10426 | 9387 | |
2020-12-05 | 6472 | 5319 | |
2020-12-06 | 5506 | 4668 | |
2020-12-07 | 16302 | 14726 | |
2020-12-08 | 12668 | 11552 | |
2020-12-09 | 11846 | 10815 | |
2020-12-10 | 9491 | 8693 | |
2020-12-11 | 9017 | 8082 | |
2020-12-12 | 5626 | 4707 | |
2020-12-13 | 5292 | 4413 | |
2020-12-14 | 13010 | 11996 | |
2020-12-15 | 9658 | 8710 | |
2020-12-16 | 8982 | 8099 | |
2020-12-17 | 16225 | 15214 | |
2020-12-18 | 42465 | 40891 | |
2020-12-19 | 43099 | 41604 | |
2020-12-20 | 50050 | 48355 | |
2020-12-21 | 65613 | 63621 | |
2020-12-22 | 63580 | 61579 | |
2020-12-23 | 43233 | 41991 | |
2020-12-24 | 25183 | 24305 | |
2020-12-25 | 8535 | 7694 | |
2020-12-26 | 17169 | 16243 | |
2020-12-27 | 17246 | 16341 | |
2020-12-28 | 20282 | 19209 | |
2020-12-29 | 26538 | 25442 | |
2020-12-30 | 35539 | 34428 | |
2020-12-31 | 27976 | 26962 | |
2021-01-01 | 17829 | 16935 | |
2021-01-02 | 15857 | 15128 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment