{"id":6077,"date":"2020-03-27T12:15:15","date_gmt":"2020-03-27T12:15:15","guid":{"rendered":"https:\/\/max-drake.cc\/?p=6077"},"modified":"2020-03-27T12:15:19","modified_gmt":"2020-03-27T12:15:19","slug":"covid-by-region-globally-data-visualisation-part-3","status":"publish","type":"post","link":"https:\/\/max-drake.cc\/?p=6077","title":{"rendered":"Covid by region Globally -Data visualisation- Part 3"},"content":{"rendered":"\n

At the end of the last post I’d found that the original NZ regional dataset had changed. I was also finding it slow working in Google Sheets as the interwongle seems to be slow with so much demand. So I decided that I’d start working locally. <\/p>\n\n\n\n

My current process is to get the data, manipulate it in spreadsheet, export to HTML file initially then to a CSV that I uploaded to Datawrapper to show on their maps, then I embedded link into website. <\/p>\n\n\n\n

MS SQL database<\/h4>\n\n\n\n

Instead of just going to Excel, which I’ll do anyway, I’m initially going to upload into a MS SQL database and start writing queries on the data to export. Its taking me a while to set up the table and relearn SQL queries and exporting to .RPT files and then converting to TXT or CSV files for further information. <\/p>\n\n\n\n

On relearning the MS SQL bits I’m starting to get other data file. The John Hopkins sources referenced at bottom of dashboard go to GITHUB and have daily data and time series HERE<\/a><\/strong>. So I’ve downloaded them and am going to upload to some DB tables. <\/p>\n\n\n\n

I spent quite a lot of yesterday afternoon trying to import information into MS SQL database, not that successfully as I had issues with putting a primary key on the tables and date headers were going wrong.  I’m going to park that for a time. <\/p>\n\n\n\n

Excel<\/h4>\n\n\n\n

I’ve imported into Google Sheets, now I’ll try importing into Excel. Excel has a Data> from Web<\/strong> button, where you put in a URL and then select a table, but that didn’t work on the wiki page so it brought in all the page. I tried deleting text above and below the table but on refresh it just came back, so I linked the table to another sheet to work with the data.<\/p>\n\n\n\n

The link I’m using is Current cases from Wikipedia<\/a><\/strong> which uses the same source as Bing Tracker.<\/a><\/strong><\/p>\n\n\n\n

I’ve then put some percentage figures on the numbers, pulled in country populations from Wikipedia here<\/a><\/strong>. I’ve then done some basic conditional formatting on the columns, with World as reference at top (no conditional formatting for that otherwise it overwhelms the data<\/em>) and the following info comes out, AS OF 26th March 2020<\/span><\/strong>. This is static data, just comparing highs\/lows.<\/p>\n\n\n\n

\"\"
Highest number of cases in countrt- total count. US at top of list<\/figcaption><\/figure>\n\n\n\n
\"\"
Highest Active Cases- US again, followed by Italy & Spain, then Germany & France<\/figcaption><\/figure>\n\n\n\n
\"\"
Highest nuber of Deaths to date- Italy, Spain, China Iran, France & US all above 1000<\/figcaption><\/figure>\n\n\n\n

Use this chart<\/a><\/strong> to add countries for deaths and rates of change. <\/p>\n\n\n\n

\"\"
Number & % recoveries- China up to 91% recovered, S.Korea at 44% and Bahrain at 44% of a small number o total cases of 457 in a pop of 1.5 M.<\/figcaption><\/figure>\n\n\n\n

Percentage pop over 70 by country Data from here<\/a><\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

so, from above chart I’ll use 8% as likely death rate for over 70’s. A bit of a cock up linking data between sheets, on one page I had filters on, so it pulled data into the wrong Rows. OOPS!! (bring heater across to find link cell, then copy adjacent column across and use Find\/Replace on Column code<\/em> seems to be best way of doing this, you don’t have to mess around with aligning data on both sheets<\/em>)<\/span><\/p>\n\n\n\n

\"\"
European countries (& Japan) have largest % >70 population. Calculating Affected cases by %>70 and then 8% of those dying (based on chart above this one) There is a misalignment with those figures, Italy\/Spain have far higher deaths, and % >70 expected is far exceeded, whereas Germany expected cases is 552 and only 262 deaths. Finland also low, but numbers are low there at present.<\/figcaption><\/figure>\n\n\n\n

I want to get historic data now and see what I can do with that. There are rates of change over time to be explored.<\/p>\n\n\n\n

End comment<\/h3>\n\n\n\n

I just refreshed the page and brought in the data for today, there were a few errors that I had to clean up, but it went pretty well. I am pulling from 3 different tables off web pages, so not too bad. <\/p>\n","protected":false},"excerpt":{"rendered":"

At the end of the last post I’d found that the original NZ regional dataset had changed. I was also finding it slow working in Google Sheets as the interwongle seems to be slow with so much demand. So I decided that I’d start working locally. My current process is to get the data, manipulate […]<\/p>\n","protected":false},"author":1,"featured_media":6080,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40,3,36,32,5,12,29],"tags":[],"class_list":["post-6077","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-analysis","category-data-extraction","category-databases","category-power-bi","category-graphs","category-visualisation","category-web"],"featured_image_src":"https:\/\/max-drake.cc\/wp-content\/uploads\/2020\/03\/2020-03-27-12_10_23-WikiGlobalData20200326.xlsx-Excel.jpg","featured_image_src_square":"https:\/\/max-drake.cc\/wp-content\/uploads\/2020\/03\/2020-03-27-12_10_23-WikiGlobalData20200326.xlsx-Excel.jpg","author_info":{"display_name":"Max Drake","author_link":"https:\/\/max-drake.cc\/?author=1"},"_links":{"self":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/6077","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6077"}],"version-history":[{"count":0,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/6077\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/media\/6080"}],"wp:attachment":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6077"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6077"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6077"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}