{"id":128,"date":"2021-01-04T17:22:16","date_gmt":"2021-01-04T22:22:16","guid":{"rendered":"https:\/\/liberal-elite.org\/?page_id=128"},"modified":"2021-01-04T17:22:16","modified_gmt":"2021-01-04T22:22:16","slug":"cvdeaths-python-script","status":"publish","type":"page","link":"https:\/\/liberal-elite.org\/?page_id=128","title":{"rendered":"cvdeaths Python Script"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>#!\/usr\/bin\/env python\n\noutput_csv_file_name = 'covid19_deaths_by_country.csv'\n\ndeaths_csv_url = 'https:\/\/covid19.who.int\/WHO-COVID-19-global-table-data.csv'\npop_xls_url = 'https:\/\/population.un.org\/wup\/Download\/Files\/WUP2018-F05-Total_Population.xls'\n\nimport sqlite3\nimport pandas\nimport csv\n\n#  Ignore UserWarning messages from pandas library\nimport warnings\nwarnings.simplefilter(action='ignore', category=UserWarning)\n\n#  Define in-memory SQLite3 database\nconn = sqlite3.connect(':memory:')\ncur_1 = conn.cursor()\ncur_2 = conn.cursor()\n\n#  Read WHO Covid detahs spreadsheet into db table\ndf = pandas.read_csv(deaths_csv_url)\ndf.to_sql(name='deaths', con=conn)\n\n#  Read UN popluation data into db table\ndf = pandas.read_excel(pop_xls_url)\ndf.to_sql(name='population', con=conn)\n\n#  Create CSV with country name, deaths, population and \"1 death per NNN people\" calculation\nwith open(output_csv_file_name, 'w', newline='') as csvfile:\n\tcsvwriter = csv.writer(csvfile, quoting=csv.QUOTE_MINIMAL)\n\tcsvwriter.writerow(&#91;'Country', 'Deaths', 'Population', '1 Death For Every NNN People'])\n\t\n\t#  Get country name, cumulative coronavirus deaths\n\tcur_1.execute(\"SELECT `Name`,`Deaths - cumulative total` FROM 'deaths'\")\n\tfor row in cur_1:\n\n\t\t#  Manually fix mismatched country names\n\t\tcountry = row&#91;0]\n\t\tif country == 'Global':  #  Skip this row\n\t\t\tcontinue\n\t\telif country == 'The United Kingdom':\n\t\t\tcountry = 'United Kingdom'\n\t\telif country == 'North Macedonia':\n\t\t\tcountry = 'TFYR Macedonia'\n\t\telif 'Mariana Islands' in country:\n\t\t\tcountry = 'Northern Mariana Islands'\n\t\telif country == \"Democratic People's Republic of Korea\":\n\t\t\tcountry == \"Dem. People's Republic of Korea\"\n\n\t\t#  Get 2020 population when country names match across spreadsheets\n\t\tsql = \"SELECT `Unnamed: 18` FROM 'population' WHERE instr(`Unnamed: 1`, '{}') > 0\".format(country.replace(\"'\", \"''\"))\n\t\tpop = cur_2.execute(sql).fetchone()\n\t\tif pop:\n\t\t\t#  Calculate the \"1 person out of every NNN people\" number\n\t\t\tif row&#91;1] == 0:\n\t\t\t\tcalc = 0 \n\t\t\telse:\n\t\t\t\tcalc = pop&#91;0] * 1000 \/ row&#91;1]\n\t\t\tcsvwriter.writerow(&#91;country, row&#91;1], pop&#91;0] * 1000, calc])\n\t\telse:\n\t\t\tprint(\"Unable to match country name from WHO Covid deaths table: {} ({} deaths)\".format(country, row&#91;1]))\n\nprint('\\nCSV file is ready: {}'.format(output_csv_file_name))<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-128","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/liberal-elite.org\/index.php?rest_route=\/wp\/v2\/pages\/128","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/liberal-elite.org\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/liberal-elite.org\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/liberal-elite.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/liberal-elite.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=128"}],"version-history":[{"count":1,"href":"https:\/\/liberal-elite.org\/index.php?rest_route=\/wp\/v2\/pages\/128\/revisions"}],"predecessor-version":[{"id":129,"href":"https:\/\/liberal-elite.org\/index.php?rest_route=\/wp\/v2\/pages\/128\/revisions\/129"}],"wp:attachment":[{"href":"https:\/\/liberal-elite.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=128"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}