This project retrieves and inserts into MongoDB the Johns Hopkins University COVID-19 dataset provided on Github.
Please read the blog post associated to this repository.
This database contains a few collections that have been carefully engineered to be as useful and as convenient as possible to work with.
In each of these collections:
5 collections are available:
This collection contains only one single document. It contains the list of all the values (obtained with mongodb distinct function) for the major fields along with the first and last dates.
{
_id : "metadata",
countries : [ "Afghanistan", "Albania", "Algeria", "..." ],
states : [ "Alabama", "Alaska", "Alberta", "..." ],
states_us : [ "Alabama", "Alaska", "American Samoa", "..." ],
counties : [ "Abbeville", "Acadia", "Accomack", "..." ],
iso3s : [ "ABW", "AFG", "AGO", "..." ],
uids : [ 4, 8, 12, ... ],
first_date : 2020-01-22T00:00:00.000+00:00,
last_date : 2020-04-24T00:00:00.000+00:00
}
This collection contains the equivalent of what is in the 3 documents:
time_series_covid19_confirmed_global.csv
time_series_covid19_deaths_global.csv
time_series_covid19_recovered_global.csv
Each document is also joined with its associated line from the UID_ISO_FIPS_LookUp_Table.csv
file.
In this collection we have nb_entries(time_series_covid19_confirmed_global.csv) * number_days
documents.
Here is an example document:
{
"_id" : ObjectId("5ea45f2a8049cddb8cfa3822"),
"uid" : 4,
"country_iso2" : "AF",
"country_iso3" : "AFG",
"country_code" : 4,
"country" : "Afghanistan",
"combined_name" : "Afghanistan",
"population" : 38928341,
"loc" : {
"type" : "Point",
"coordinates" : [
67.71,
33.9391
]
},
"date" : ISODate("2020-01-22T00:00:00Z"),
"confirmed" : 0,
"deaths" : 0,
"recovered" : 0
}
This collection contains the equivalent of what is in the 2 documents:
time_series_covid19_confirmed_US.csv
time_series_covid19_deaths_US.csv
Each document is also joined with its associated line from the UID_ISO_FIPS_LookUp_Table.csv
file.
In this collection we have nb_entries(time_series_covid19_confirmed_US.csv) * number_days
documents.
Here is an example document:
{
"_id" : ObjectId("5ea45f2b8049cddb8cfa9912"),
"uid" : 16,
"country_iso2" : "AS",
"country_iso3" : "ASM",
"country_code" : 16,
"fips" : 60,
"state" : "American Samoa",
"country" : "US",
"combined_name" : "American Samoa, US",
"population" : 55641,
"loc" : {
"type" : "Point",
"coordinates" : [
-170.132,
-14.271
]
},
"date" : ISODate("2020-01-22T00:00:00Z"),
"confirmed" : 0,
"deaths" : 0
}
Note: JHU does not provide recovered data for the US files. It's currently only available in the global files.
This collection is calculated using the data from the global
collection. It's the same collection but the countries are grouped together into a single document for each date.
So in this collection, you will find nb_countries * nb_days
documents.
Also, because all the states are grouped into a single doc for each countries, some fields are arrays in this collection.
You can see the detailed aggregation pipeline in the 2-smart-insert.py
file in the data-import
folder.
Here is an example for France:
{
"_id" : ObjectId("5eb1e2bdeb5fc5a3a38a33f8"),
"uids" : [ 312, 175, 638, 666, 258, 250, 663, 254, 652, 540, 474 ],
"confirmed" : 169583,
"deaths" : 25204,
"country" : "France",
"date" : ISODate("2020-05-04T00:00:00Z"),
"country_iso2s" : [ "GF", "MF", "PF", "YT", "GP", "RE", "PM", "MQ", "FR", "BL", "NC" ],
"country_iso3s" : [ "SPM", "NCL", "REU", "BLM", "MAF", "MYT", "MTQ", "PYF", "GLP", "FRA", "GUF" ],
"country_codes" : [ 652, 474, 666, 175, 250, 258, 254, 663, 312, 638, 540 ],
"combined_names" : [
"Saint Pierre and Miquelon, France",
"Guadeloupe, France",
"Reunion, France",
"New Caledonia, France",
"Saint Barthelemy, France",
"France",
"Martinique, France",
"Mayotte, France",
"French Polynesia, France",
"French Guiana, France",
"St Martin, France"
],
"population" : 298682,
"recovered" : 51476,
"states" : [
"French Guiana",
"French Polynesia",
"Guadeloupe",
"Mayotte",
"New Caledonia",
"Reunion",
"Saint Barthelemy",
"St Martin",
"Martinique",
"Saint Pierre and Miquelon"
]
}
This collection is the most complete collection in this database. This collection basically contains all the documents from the collections:
global
us_only
But with a little trick on top: the US cases are counted in both collections respectively:
So to take advantages of both collections, I just removed the confirmed and deaths counts from the US documents which comes from the global
collection.
This allow me to keep track of the recovered cases in the US while also keeping track of the confirmed and deaths cases at a more detailed level. This is really the best we can do here because JHU don't reported recovered cases at a detailed level for the US.
With this trick, the count for confirmed, deaths and recovered persons for a given date is correct.
This is the collection I'm using to build my charts in my charts blog posts:
The documents in this collection are exactly the same than in the collections mentioned above.
global
collection:{
"_id" : ObjectId("5ea49768865a48ecca6d5ccb"),
"uid" : 250,
"country_iso2" : "FR",
"country_iso3" : "FRA",
"country_code" : 250,
"country" : "France",
"combined_name" : "France",
"population" : 65273512,
"loc" : {
"type" : "Point",
"coordinates" : [
2.2137,
46.2276
]
},
"date" : ISODate("2020-04-24T00:00:00Z"),
"confirmed" : 158636,
"deaths" : 22245,
"recovered" : 43493
}
us_only
collection:{
"_id" : ObjectId("5ea4976b865a48ecca70df4d"),
"uid" : 84042101,
"country_iso2" : "US",
"country_iso3" : "USA",
"country_code" : 840,
"fips" : 42101,
"county" : "Philadelphia",
"state" : "Pennsylvania",
"country" : "US",
"combined_name" : "Philadelphia, Pennsylvania, US",
"population" : 1584064,
"loc" : {
"type" : "Point",
"coordinates" : [
-75.1379,
40.0034
]
},
"date" : ISODate("2020-04-24T00:00:00Z"),
"confirmed" : 11877,
"deaths" : 449
}
global
collection and represents the US entire country (the one with the trick):{
"_id" : ObjectId("5ea49768865a48ecca6d84d1"),
"uid" : 840,
"country_iso2" : "US",
"country_iso3" : "USA",
"country_code" : 840,
"country" : "US",
"combined_name" : "US",
"population" : 329466283,
"loc" : {
"type" : "Point",
"coordinates" : [
-100,
40
]
},
"date" : ISODate("2020-04-24T00:00:00Z"),
"recovered" : 99079
}
Just pay attention: the documents which come from the US detailed source don't have a recovered
field because JHU doesn't provide this data. Only the documents (one for each date) that represents the US at a country level contain the recovered
field.
Also JHU doesn't provide the recovered count for all the countries and states in the global files.
In this collection, we have (nb_entries(time_series_covid19_confirmed_global.csv) + nb_entries(time_series_covid19_confirmed_global.csv)) * number_days
.
This database contains the raw CSV files imported with the mongoimport tool.
This database is updated by the 1-mongoimport-everything.sh
in the data-import
folder.
This script imports all the files matching these names with wildcards:
And it creates these collections:
Note: These collections are not clean and the schema designs are not great to work with because they come from raw and flat CSV files but at least they contain exactly what JHU is delivering.