This post is to socialize an API publishing COVID-19 spread in countries by date and to reform the datapoint to publish a GoogleSheet version to be consumed by Tableau.
DataPoints:
COUNTRY
- DATE
- COVID19-CONFIRMED
- COVID19-DEATHS
- COVID19-RECOVERED
We wrote a python code to consume the api json object and write it to google sheet using GoogleAPI everyday.
For now its running from my local server and has not been hosted anywhere, but you can easily use the code to host your own.
import json
import time
import requests
import gspread
import oauth2client
import csv
from datetime import datetime, date, timedelta
from oauth2client.service_account import ServiceAccountCredentials
scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("conf.json", scope)
print ("start running code");
response = requests.get('https://pomber.github.io/covid19/timeseries.json')
countries = response.json().keys()
count = 0
for key in countries :
#print(key)
entries = response.json().get(key)
for entry in entries:
#print(entry)
if(datetime.strptime(str(entry.get("date")), '%Y-%m-%d').date() == date.today() - timedelta(days=1)):
data = [ key, str(entry.get("date")), str(entry.get("confirmed")), str(entry.get("deaths")), str(entry.get("recovered")) ]
# print(data)
creds = ServiceAccountCredentials.from_json_keyfile_name("conf.json", scope)
client = gspread.authorize(creds)
sheet = client.open("Covid19DataStream").sheet1
sheet.insert_row(data)
count = count+1
if(count == 500):
print("sleeping")
time.sleep(110)
count = 0
The refernced conf.json stores your private.key for writing to the google sheet API.
Please you this reference to create your own
the source of data is :
With the code you will find the json data updated everyday in my google sheet which is open to public read access:
Countries covered:
Afghanistan
Albania
Algeria
Andorra
Angola
Antigua and Barbuda
Argentina
Armenia
Australia
Austria
Azerbaijan
Bahamas
Bahrain
Bangladesh
Barbados
Belarus
Belgium
Benin
Bhutan
Bolivia
Bosnia and Herzegovina
Brazil
Brunei
Bulgaria
Burkina Faso
Cabo Verde
Cambodia
Cameroon
Canada
Central African Republic
Chad
Chile
China
Colombia
Congo (Brazzaville)
Congo (Kinshasa)
Costa Rica
Cote d'Ivoire
Croatia
Diamond Princess
Cuba
Cyprus
Czechia
Denmark
Djibouti
Dominican Republic
Ecuador
Egypt
El Salvador
Equatorial Guinea
Eritrea
Estonia
Eswatini
Ethiopia
Fiji
Finland
France
Gabon
Gambia
Georgia
Germany
Ghana
Greece
Guatemala
Guinea
Guyana
Haiti
Holy See
Honduras
Hungary
Iceland
India
Indonesia
Iran
Iraq
Ireland
Israel
Italy
Jamaica
Japan
Jordan
Kazakhstan
Kenya
Korea, South
Kuwait
Kyrgyzstan
Latvia
Lebanon
Liberia
Liechtenstein
Lithuania
Luxembourg
Madagascar
Malaysia
Maldives
Malta
Mauritania
Mauritius
Mexico
Moldova
Monaco
Mongolia
Montenegro
Morocco
Namibia
Nepal
Netherlands
New Zealand
Nicaragua
Niger
Nigeria
North Macedonia
Norway
Oman
Pakistan
Panama
Papua New Guinea
Paraguay
Peru
Philippines
Poland
Portugal
Qatar
Romania
Russia
Rwanda
Saint Lucia
Saint Vincent and the Grenadines
San Marino
Saudi Arabia
Senegal
Serbia
Seychelles
Singapore
Slovakia
Slovenia
Somalia
South Africa
Spain
Sri Lanka
Sudan
Suriname
Sweden
Switzerland
Taiwan*
Tanzania
Thailand
Togo
Trinidad and Tobago
Tunisia
Turkey
Uganda
Ukraine
United Arab Emirates
United Kingdom
Uruguay
US
Uzbekistan
Venezuela
Vietnam
Zambia
Zimbabwe
Dominica
Grenada
Mozambique
Syria
Timor-Leste
Belize
Laos
Libya
West Bank and Gaza
Guinea-Bissau
Mali
Saint Kitts and Nevis
Kosovo
Burma
MS Zaandam
Botswana
Burundi
Sierra Leone
Malawi
Stay home stay safe !
Top comments (0)