import os
import pandas as pd
entries = os.listdir('test_csvs/')
li = []
for entry in entries:
if '.csv' in entry:
num = entry.split('sitrep-')[1].split('-')[0]
date = entry.split('-')[0]
f = pd.read_csv('csvs/'+entry, index_col=None, header=0)
if f.shape[1] == 7:
if 'Total' in f.columns[0]:
f.columns = ['country', 'total_confirmed', 'total_new', 'total_deaths', 'total_new_deaths', 'transmission_class', 'days_since_report']
f['date'] = date
li.append(f)
frame = pd.concat(li, axis=0, ignore_index=True)
frame
| country | total_confirmed | total_new | total_deaths | total_new_deaths | transmission_class | days_since_report | date | |
|---|---|---|---|---|---|---|---|---|
| 0 | Western Pacific Region | NaN | NaN | NaN | NaN | NaN | NaN | 20200322 |
| 1 | China | 81498.0 | 82.0 | 3267.0 | 6.0 | Local transmission | 0.0 | 20200322 |
| 2 | Republic of Korea | 8897.0 | 98.0 | 104.0 | 2.0 | Local transmission | 0.0 | 20200322 |
| 3 | Malaysia | 1183.0 | 153.0 | 3.0 | 0.0 | Local transmission | 0.0 | 20200322 |
| 4 | Australia | 1081.0 | 208.0 | 7.0 | 0.0 | Local transmission | 0.0 | 20200322 |
| 5 | Japan | 1046.0 | 50.0 | 36.0 | 1.0 | Local transmission | 0.0 | 20200322 |
| 6 | Singapore | 432.0 | 47.0 | 2.0 | 2.0 | Local transmission | 0.0 | 20200322 |
| 7 | Philippines | 307.0 | 77.0 | 19.0 | 1.0 | Local transmission | 0.0 | 20200322 |
| 8 | Viet Nam | 94.0 | 3.0 | 0.0 | 0.0 | Local transmission | 0.0 | 20200322 |
| 9 | Brunei Darussalam | 83.0 | 5.0 | 0.0 | 0.0 | Local transmission | 0.0 | 20200322 |
| 10 | New Zealand | 66.0 | 13.0 | 0.0 | 0.0 | Local transmission | 0.0 | 20200322 |
| 11 | Cambodia | 53.0 | 2.0 | 0.0 | 0.0 | Local transmission | 0.0 | 20200322 |
| 12 | Mongolia | 10.0 | 4.0 | 0.0 | 0.0 | Imported cases only | 0.0 | 20200322 |
| 13 | Fiji | 2.0 | 1.0 | 0.0 | 0.0 | Local transmission | 0.0 | 20200322 |
| 14 | Papua New Guinea | 1.0 | 0.0 | 0.0 | 0.0 | Imported cases only | 1.0 | 20200322 |
| 15 | Territories** | NaN | NaN | NaN | NaN | NaN | NaN | 20200322 |
| 16 | French Polynesia \n15 \n4 \n0 \n0 \nImported c... | NaN | NaN | NaN | NaN | NaN | NaN | 20200322 |
| 17 | Guam \n15 \n1 \n0 \n0 \nLocal transmission \n0 | NaN | NaN | NaN | NaN | NaN | NaN | 20200322 |
| 18 | New Caledonia \n4 \n2 \n0 \n0 \nImported cases... | NaN | NaN | NaN | NaN | NaN | NaN | 20200322 |
| 19 | European Region ^ | NaN | NaN | NaN | NaN | NaN | NaN | 20200322 |
| 20 | Italy | 53578.0 | 6557.0 | 4827.0 | 795.0 | Local transmission | 0.0 | 20200322 |
| 21 | Spain | 24926.0 | 4946.0 | 1326.0 | 324.0 | Local transmission | 0.0 | 20200322 |
| 22 | Germany | 21463.0 | 3140.0 | 67.0 | 22.0 | Local transmission | 0.0 | 20200322 |
| 23 | France | 14296.0 | 1821.0 | 562.0 | 112.0 | Local transmission | 0.0 | 20200322 |
| 24 | Switzerland | 6077.0 | 1237.0 | 56.0 | 13.0 | Local transmission | 0.0 | 20200322 |
| 25 | The United Kingdom | 5018.0 | 1035.0 | 233.0 | 56.0 | Local transmission | 0.0 | 20200322 |
| 26 | Netherlands | 3631.0 | 637.0 | 136.0 | 30.0 | Local transmission | 0.0 | 20200322 |
| 27 | Austria | 3024.0 | 375.0 | 8.0 | 2.0 | Local transmission | 0.0 | 20200322 |
| 28 | Belgium | 2815.0 | 558.0 | 67.0 | 30.0 | Local transmission | 0.0 | 20200322 |
| 29 | Norway | 1926.0 | 184.0 | 7.0 | 0.0 | Local transmission | 0.0 | 20200322 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 110 | Guam \n14 \n2 \n0 \n0 \nLocal transmission \n0 | NaN | NaN | NaN | NaN | NaN | NaN | 20200321 |
| 111 | French Polynesia \n11 \n0 \n0 \n0 \nImported c... | NaN | NaN | NaN | NaN | NaN | NaN | 20200321 |
| 112 | New Caledonia \n2 \n0 \n0 \n0 \nImported cases... | NaN | NaN | NaN | NaN | NaN | NaN | 20200321 |
| 113 | European Region ^ | NaN | NaN | NaN | NaN | NaN | NaN | 20200321 |
| 114 | Italy | 47021.0 | 5986.0 | 4032.0 | 625.0 | Local transmission | 0.0 | 20200321 |
| 115 | Spain | 19980.0 | 2833.0 | 1002.0 | 235.0 | Local transmission | 0.0 | 20200321 |
| 116 | Germany | 18323.0 | 7324.0 | 45.0 | 25.0 | Local transmission | 0.0 | 20200321 |
| 117 | France | 12475.0 | 1598.0 | 450.0 | 78.0 | Local transmission | 0.0 | 20200321 |
| 118 | Switzerland | 4840.0 | 977.0 | 43.0 | 10.0 | Local transmission | 0.0 | 20200321 |
| 119 | The United Kingdom | 3983.0 | 706.0 | 177.0 | 33.0 | Local transmission | 0.0 | 20200321 |
| 120 | Netherlands | 2994.0 | 534.0 | 106.0 | 30.0 | Local transmission | 0.0 | 20200321 |
| 121 | Austria | 2649.0 | 806.0 | 6.0 | 1.0 | Local transmission | 0.0 | 20200321 |
| 122 | Belgium | 2257.0 | 462.0 | 37.0 | 23.0 | Local transmission | 0.0 | 20200321 |
| 123 | Norway | 1742.0 | 190.0 | 7.0 | 1.0 | Local transmission | 0.0 | 20200321 |
| 124 | Sweden | 1623.0 | 200.0 | 16.0 | 13.0 | Local transmission | 0.0 | 20200321 |
| 125 | Denmark | 1255.0 | 123.0 | 9.0 | 3.0 | Local transmission | 0.0 | 20200321 |
| 126 | Portugal | 1020.0 | 235.0 | 6.0 | 3.0 | Local transmission | 0.0 | 20200321 |
| 127 | Czechia | 904.0 | 210.0 | 0.0 | 0.0 | Local transmission | 0.0 | 20200321 |
| 128 | Israel | 712.0 | 183.0 | 1.0 | 1.0 | Local transmission | 0.0 | 20200321 |
| 129 | Ireland | 683.0 | 126.0 | 3.0 | 0.0 | Local transmission | 0.0 | 20200321 |
| 130 | Turkey | 670.0 | 479.0 | 9.0 | 7.0 | Local transmission | 0.0 | 20200321 |
| 131 | Greece | 495.0 | 77.0 | 8.0 | 3.0 | Local transmission | 0.0 | 20200321 |
| 132 | Luxembourg | 484.0 | 139.0 | 5.0 | 1.0 | Local transmission | 0.0 | 20200321 |
| 133 | Finland | 450.0 | 81.0 | 0.0 | 0.0 | Local transmission | 0.0 | 20200321 |
| 134 | Poland | 425.0 | 100.0 | 5.0 | 0.0 | Local transmission | 0.0 | 20200321 |
| 135 | Iceland | 409.0 | 79.0 | 1.0 | 1.0 | Local transmission | 0.0 | 20200321 |
| 136 | Slovenia | 341.0 | 22.0 | 1.0 | 0.0 | Local transmission | 0.0 | 20200321 |
| 137 | Romania | 308.0 | 48.0 | 0.0 | 0.0 | Local transmission | 0.0 | 20200321 |
| 138 | Estonia | 283.0 | 16.0 | 0.0 | 0.0 | Local transmission | 0.0 | 20200321 |
| 139 | Russian Federation | 253.0 | 54.0 | 0.0 | 0.0 | Imported cases only | 0.0 | 20200321 |
140 rows × 8 columns
grouped = pd.DataFrame(frame.groupby(['country', 'date','total_deaths']).sum())
grouped
| total_confirmed | total_new | total_new_deaths | days_since_report | |||
|---|---|---|---|---|---|---|
| country | date | total_deaths | ||||
| Australia | 20200320 | 6.0 | 709.0 | 199.0 | 0.0 | 0.0 |
| 20200321 | 7.0 | 873.0 | 164.0 | 1.0 | 0.0 | |
| 20200322 | 7.0 | 1081.0 | 208.0 | 0.0 | 0.0 | |
| Austria | 20200320 | 5.0 | 1843.0 | 197.0 | 1.0 | 0.0 |
| 20200321 | 6.0 | 2649.0 | 806.0 | 1.0 | 0.0 | |
| 20200322 | 8.0 | 3024.0 | 375.0 | 2.0 | 0.0 | |
| Belgium | 20200320 | 14.0 | 1795.0 | 309.0 | 0.0 | 0.0 |
| 20200321 | 37.0 | 2257.0 | 462.0 | 23.0 | 0.0 | |
| 20200322 | 67.0 | 2815.0 | 558.0 | 30.0 | 0.0 | |
| Brunei Darussalam | 20200320 | 0.0 | 73.0 | 17.0 | 0.0 | 0.0 |
| 20200321 | 0.0 | 78.0 | 5.0 | 0.0 | 0.0 | |
| 20200322 | 0.0 | 83.0 | 5.0 | 0.0 | 0.0 | |
| Cambodia | 20200320 | 0.0 | 47.0 | 12.0 | 0.0 | 0.0 |
| 20200321 | 0.0 | 51.0 | 4.0 | 0.0 | 0.0 | |
| 20200322 | 0.0 | 53.0 | 2.0 | 0.0 | 0.0 | |
| China | 20200320 | 3253.0 | 81300.0 | 126.0 | 11.0 | 0.0 |
| 20200321 | 3261.0 | 81416.0 | 116.0 | 8.0 | 0.0 | |
| 20200322 | 3267.0 | 81498.0 | 82.0 | 6.0 | 0.0 | |
| Croatia | 20200322 | 1.0 | 206.0 | 80.0 | 0.0 | 0.0 |
| Czechia | 20200320 | 0.0 | 694.0 | 172.0 | 0.0 | 0.0 |
| 20200321 | 0.0 | 904.0 | 210.0 | 0.0 | 0.0 | |
| 20200322 | 0.0 | 995.0 | 91.0 | 0.0 | 0.0 | |
| Denmark | 20200320 | 6.0 | 1132.0 | 88.0 | 2.0 | 0.0 |
| 20200321 | 9.0 | 1255.0 | 123.0 | 3.0 | 0.0 | |
| 20200322 | 13.0 | 1326.0 | 71.0 | 4.0 | 0.0 | |
| Estonia | 20200320 | 0.0 | 267.0 | 9.0 | 0.0 | 0.0 |
| 20200321 | 0.0 | 283.0 | 16.0 | 0.0 | 0.0 | |
| 20200322 | 0.0 | 306.0 | 23.0 | 0.0 | 0.0 | |
| Fiji | 20200320 | 0.0 | 1.0 | 1.0 | 0.0 | 0.0 |
| 20200321 | 0.0 | 1.0 | 0.0 | 0.0 | 1.0 | |
| ... | ... | ... | ... | ... | ... | ... |
| Romania | 20200322 | 0.0 | 367.0 | 59.0 | 0.0 | 0.0 |
| Russian Federation | 20200320 | 0.0 | 199.0 | 52.0 | 0.0 | 0.0 |
| 20200321 | 0.0 | 253.0 | 54.0 | 0.0 | 0.0 | |
| 20200322 | 0.0 | 306.0 | 53.0 | 0.0 | 0.0 | |
| San Marino | 20200320 | 14.0 | 126.0 | 17.0 | 0.0 | 0.0 |
| Serbia†† | 20200320 | 0.0 | 123.0 | 41.0 | 0.0 | 0.0 |
| Singapore | 20200320 | 0.0 | 345.0 | 32.0 | 0.0 | 0.0 |
| 20200321 | 0.0 | 385.0 | 40.0 | 0.0 | 0.0 | |
| 20200322 | 2.0 | 432.0 | 47.0 | 2.0 | 0.0 | |
| Slovenia | 20200320 | 1.0 | 319.0 | 33.0 | 0.0 | 0.0 |
| 20200321 | 1.0 | 341.0 | 22.0 | 0.0 | 0.0 | |
| 20200322 | 1.0 | 383.0 | 42.0 | 0.0 | 0.0 | |
| Spain | 20200320 | 767.0 | 17147.0 | 3431.0 | 169.0 | 0.0 |
| 20200321 | 1002.0 | 19980.0 | 2833.0 | 235.0 | 0.0 | |
| 20200322 | 1326.0 | 24926.0 | 4946.0 | 324.0 | 0.0 | |
| Sweden | 20200320 | 3.0 | 1423.0 | 144.0 | 0.0 | 0.0 |
| 20200321 | 16.0 | 1623.0 | 200.0 | 13.0 | 0.0 | |
| 20200322 | 20.0 | 1746.0 | 123.0 | 4.0 | 0.0 | |
| Switzerland | 20200320 | 33.0 | 3863.0 | 853.0 | 12.0 | 0.0 |
| 20200321 | 43.0 | 4840.0 | 977.0 | 10.0 | 0.0 | |
| 20200322 | 56.0 | 6077.0 | 1237.0 | 13.0 | 0.0 | |
| The United Kingdom | 20200320 | 144.0 | 3277.0 | 647.0 | 41.0 | 0.0 |
| 20200321 | 177.0 | 3983.0 | 706.0 | 33.0 | 0.0 | |
| 20200322 | 233.0 | 5018.0 | 1035.0 | 56.0 | 0.0 | |
| Turkey | 20200320 | 2.0 | 191.0 | 0.0 | 0.0 | 1.0 |
| 20200321 | 9.0 | 670.0 | 479.0 | 7.0 | 0.0 | |
| 20200322 | 21.0 | 947.0 | 277.0 | 12.0 | 0.0 | |
| Viet Nam | 20200320 | 0.0 | 85.0 | 19.0 | 0.0 | 0.0 |
| 20200321 | 0.0 | 91.0 | 6.0 | 0.0 | 0.0 | |
| 20200322 | 0.0 | 94.0 | 3.0 | 0.0 | 0.0 |
122 rows × 4 columns