2a:null 2b:["$","title",null,{"children":"Manipulating Covid-19 data | Hongtao Hao | Observable"}] 31:T4fd,dataNineNewRegion2 = { //dataNineNewRegion.find(element => element.country_code == 'AIA').region = 'Latin America & Caribbean'; //dataNineNewRegion.find(element => element.country_code == 'BES').region = 'Latin America & Caribbean'; // dataNineNewRegion.find(element => element.country_code == 'ESH').region = 'Middle East & North Africa'; //dataNineNewRegion.find(element => element.country_code == 'FLK').region = 'Latin America & Caribbean'; //dataNineNewRegion.find(element => element.country_code == 'GGY').region = 'Europe & Central Asia'; //dataNineNewRegion.find(element => element.country_code == 'JEY').region = 'Europe & Central Asia'; //dataNineNewRegion.find(element => element.country_code == 'MSR').region = 'Latin America & Caribbean'; dataNineNewRegion.find(element => element.country_code == 'TWN').region = 'East Asia & Pacific'; dataNineNewRegion.find(element => element.country_code == 'VAT').region = 'Europe & Central Asia'; //dataNineNewRegion.find(element => element.country_code == 'WLF').region = 'East Asia & Pacific'; dataNineNewRegion.find(element => element.country_code == 'WLD').region = 'World'; return dataNineNewRegion; } // From https://github.com/lukes/ISO-3166-Countries-with-Regional-Codes/blob/master/all/all.csv32:[{"id":"706a2965c972312bcf4a6f329b06ae75c10c0777da228ab74ae5a76aa41a8f2a140b0853a49854eea2dbfdb9ead6e8b9fd7ebbd8aa520ed7812e783d4daa8ead","href":"https://static.observableusercontent.com/files/706a2965c972312bcf4a6f329b06ae75c10c0777da228ab74ae5a76aa41a8f2a140b0853a49854eea2dbfdb9ead6e8b9fd7ebbd8aa520ed7812e783d4daa8ead","name":"metadata_country.csv","type":"text/csv","lastModified":1602440595205.661,"size":31101}] 2e:{"id":"1dff631c71665970","slug":"day-46-2020-10-08","title":"Manipulating Covid-19 data","thumbnail":"c06c26162586db7227b80b75222ca0fd0a8684e71b1317ac03ed4017681f7e73","access_level":"public","create_time":"$D2020-10-08T12:59:17.659Z","update_time":"$D2021-02-22T16:44:19.103Z","view_time":null,"first_public_time":"$D2020-10-08T15:11:56.216Z","fork":false,"stars":0,"starred":false,"owner":{"type":"team","id":"ac0e7b04fecfcd79","login":"hongtaoh","name":"Hongtao Hao","avatar_url":"https://avatars-next.observableusercontent.com/ee6259b27cf1635d95eeafe152f2a7982e66b35d5e447110f5c3371c231b7829","tier":"starter_2024"},"version":844,"publish_version":844,"role":null,"authored":false,"authors":[{"type":"individual","id":"534b8b5a17d1d792","login":"hongtaoh","name":"Hongtao Hao","avatar_url":"https://avatars-next.observableusercontent.com/275bdbd20c688c26dcac698202028ab781d97547864853f854ba4dc49f22a2bf","tier":null}],"members":[{"type":"individual","id":"534b8b5a17d1d792","login":"hongtaoh","name":"Hongtao Hao","avatar_url":"https://avatars-next.observableusercontent.com/275bdbd20c688c26dcac698202028ab781d97547864853f854ba4dc49f22a2bf","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"subscribed":null,"forks":0,"collection":{"id":"c4013b9fab62728c","slug":"vis","title":"Covid-vis","owner":{"id":"ac0e7b04fecfcd79","login":"hongtaoh","name":"Hongtao Hao"}},"type":"notebook","body":{"title":"Manipulating Covid-19 data","cells":[{"id":0,"value":"md`# Manipulating Covid-19 data`","pinned":true,"hidden":false,"mode":"ojs"},{"id":792,"value":"import {toc} from \"@mbostock/toc\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":794,"value":"toc()","pinned":false,"hidden":false,"mode":"ojs"},{"id":2,"value":"d3 = require(\"d3@6\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":710,"value":"md`\n## Step 0: Import data\n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":5,"value":"dataRaw = d3.json(\"https://covid.ourworldindata.org/data/owid-covid-data.json\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":696,"value":"md`\n## Step 1: Assign a country code to each object\n\nYou can see that in \\`data_raw\\`, each row has a name which is the country_code. What I'll do first is to move this name inside each object so that it can become a property within each object. \n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":10,"value":"countryCode = Object.keys(dataRaw)","pinned":true,"hidden":false,"mode":"ojs"},{"id":20,"value":"function getData(a) {\n let data = [];\n for (let i = 0; i < countryCode.length; i++) {\n let dataWithoutCode = dataRaw[countryCode[i]];\n data.push(dataWithoutCode);\n };\n return data;\n}\n// If you don't understand dataRaw[countryCode[i]]: for example, dataRaw['ABW'] will retrieve the object named 'ABW'.","pinned":true,"hidden":false,"mode":"ojs"},{"id":29,"value":"DataWithoutCountryCode = getData(dataRaw)","pinned":true,"hidden":false,"mode":"ojs"},{"id":35,"value":"DataWithoutCountryCode[2]","pinned":true,"hidden":false,"mode":"ojs"},{"id":701,"value":"md` ### Step 1.2\nI have gotten the same data as \\`data_raw\\` but without object names. What I am going to do next is to move this object name, i.e., country codes, inside each object. \n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":33,"value":"function AddCode2Object (a) {\n for (let i = 0; i < countryCode.length; i++) {\n a[i]['country_code'] = countryCode[i];\n }\n return a;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":42,"value":"codeAddedforEachObject = AddCode2Object(DataWithoutCountryCode)","pinned":true,"hidden":false,"mode":"ojs"},{"id":706,"value":"md`\n## Step 2: Add \\`confirmed\\` and \\`deaths\\` as first-level properties\n\nRight now, data about \\`confirmed\\` and \\`deaths\\` are stored within the first level property of \\`data\\`. What I will do next is to get them to the front and be parallel with \\`data\\`.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":59,"value":"[codeAddedforEachObject[1].data[1].date, codeAddedforEachObject[1].data[1].total_cases]","pinned":true,"hidden":false,"mode":"ojs"},{"id":47,"value":"function Add_confirmed_and_deaths (a) {\n\ta['confirmed'] = [];\n a['deaths'] = [];\n\tfor (let i = 0; i < a.data.length; i++) {\n\t\ta['confirmed'].push([a.data[i].date, a.data[i].total_cases]);\n a['deaths'].push([a.data[i].date, a.data[i].total_deaths]);\n\t}\n\treturn a;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":66,"value":"DataBeforeMapping = {\n for (let i = 0; i < countryCode.length; i++) {\n Add_confirmed_and_deaths(codeAddedforEachObject[i])\n }\n return codeAddedforEachObject\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":708,"value":"md`\n## Setp 3: Map data\n\nNow, \\`confirmed\\` and \\`deaths\\` were added. I will delete properties that I don't need. The result is called \\`DataAfterMapping\\`. \n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":90,"value":"DataAfterMapping = DataBeforeMapping.map(d => ({\n 'country_code': d.country_code,\n 'country_name': d.location,\n 'population': d.population,\n 'region': d.continent,\n 'confirmed': d.confirmed,\n 'deaths': d.deaths,\n}))","pinned":true,"hidden":false,"mode":"ojs"},{"id":146,"value":"m = DataAfterMapping[1]['confirmed'][66][1]","pinned":true,"hidden":false,"mode":"ojs"},{"id":149,"value":"function clearChange (a) {\n if (a == undefined)\n {a = 99};\n return a;\n};","pinned":true,"hidden":false,"mode":"ojs"},{"id":736,"value":"md` ## Step 4: Deal with missing data in \\`confirmed\\` and \\`deaths\\`. \n\nIn the original dataset, there are quite a few missing data in \\`confirmed\\` and \\`deaths\\`. How I solved this issue:\n\n1. For a day (a), if data for confirmed or deaths is missing, I filled the blank with data from the previous day (a - 1). If data for the previous date is also missing, I filled the blank in **Day a - 1** with data for **Day a - 2**, ...\n2. If the first day's data is missing, I filled it with \\`0\\`.\n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":157,"value":"function fillUndefined (a) {\n if (a[0][1] == undefined) {a[0][1] = 0};\n for (let t = 1; t < a.length; t++) {\n if (a[t][1] == undefined) {a[t][1] = a[t-1][1]};\n }\n return a;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":163,"value":"blankFilled = {\n for (let i = 0; i < countryCode.length; i++) {\n fillUndefined(DataAfterMapping[i]['confirmed']);\n fillUndefined(DataAfterMapping[i]['deaths'])\n }\n return DataAfterMapping\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":749,"value":"md`\n## Step 5: Normalize each country/area by using the same date range. \n\nIn the raw data, data for some countries/areas started from Dec. 31, 2019, whereas that for other places did not. In this step, I'll normalize each country/area by letting them have the same date range, i.e., from Dec. 31, 2019 to the current day. \n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":751,"value":"md`\n### 5.1. Check the most recent day in the dataset by looking at the bottom day for the first country/area.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":229,"value":"bottomDay = parseTime(blankFilled[0]['confirmed'][(blankFilled[0]['confirmed'].length)-1][0])","pinned":true,"hidden":false,"mode":"ojs"},{"id":277,"value":"bottomDayPlusOne = bottomDay.setDate(bottomDay.getDate() + 1)","pinned":true,"hidden":false,"mode":"ojs"},{"id":175,"value":"formatTime = d3.timeFormat(\"%Y-%m-%d\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":245,"value":"formatTime(bottomDay) == formatTime(new Date(Date.now()))","pinned":true,"hidden":false,"mode":"ojs"},{"id":182,"value":"parseTime = d3.timeParse(\"%Y-%m-%d\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":186,"value":"parseTime(\"2015-1-01\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":753,"value":"md`### 5.2 Crate the full date range\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":171,"value":"dateRange = d3.timeDay.range(new Date(2019, 11, 31), bottomDayPlusOne)","pinned":true,"hidden":false,"mode":"ojs"},{"id":262,"value":"formatTime(dateRange[dateRange.length - 1])","pinned":true,"hidden":false,"mode":"ojs"},{"id":296,"value":"blankFilled[0]['confirmed'][(blankFilled[0]['confirmed'].length)-1][0]","pinned":true,"hidden":false,"mode":"ojs"},{"id":757,"value":"md`### 5.3 Check which countries/areas do not have the most updated data.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":280,"value":"eachCodeLastDate_confirmed = {\n let falsebank = [];\n for (let i = 0; i < blankFilled.length; i++) {\n if (blankFilled[i]['confirmed'][(blankFilled[i]['confirmed'].length)-1][0] != blankFilled[0]['confirmed'][(blankFilled[0]['confirmed'].length)-1][0]) {\n let falsecountry = blankFilled[i]['country_code'];\n let falsecountry_lastdate = blankFilled[i]['confirmed'][(blankFilled[i]['confirmed'].length)-1][0];\n falsebank.push([falsecountry, falsecountry_lastdate])\n }\n }\n return falsebank;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":306,"value":"eachCodeLastDate_deaths = {\n let falsebank = [];\n for (let i = 0; i < blankFilled.length; i++) {\n if (blankFilled[i]['deaths'][(blankFilled[i]['deaths'].length)-1][0] != blankFilled[0]['deaths'][(blankFilled[0]['deaths'].length)-1][0]) {\n let falsecountry = blankFilled[i]['country_code'];\n let falsecountry_lastdate = blankFilled[i]['deaths'][(blankFilled[i]['deaths'].length)-1][0];\n falsebank.push([falsecountry, falsecountry_lastdate])\n }\n }\n return falsebank;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":195,"value":"function generateTS(a) {\n let dates = []\n for (let i = 0; i < a.length; i++) {\n let date = formatTime(a[i]);\n let zero = 0\n dates.push([date, zero])\n }\n return dates;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":208,"value":"datesAndZeros = generateTS(dateRange)","pinned":true,"hidden":false,"mode":"ojs"},{"id":336,"value":"blankFilled[1]['confirmed'][0][0] == formatTime(dateRange[0])","pinned":true,"hidden":false,"mode":"ojs"},{"id":762,"value":"md`### 5.3 Add the initial date, i.e., Dec. 31, 2019 to \\`confirmed\\` and \\`deaths\\` for each country/area. This step is necessary for the next step where I normalize each country/area by using the same date range.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":333,"value":"firstDateAdded = {\n for (let i = 0; i < blankFilled.length; i++) {\n if (blankFilled[i]['confirmed'][0][0] != formatTime(dateRange[0])){\n blankFilled[i]['confirmed'].unshift([formatTime(dateRange[0]), 0])\n }\n if (blankFilled[i]['deaths'][0][0] != formatTime(dateRange[0])){\n blankFilled[i]['deaths'].unshift([formatTime(dateRange[0]), 0])\n }\n }\n return blankFilled\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":376,"value":"benchMark = firstDateAdded[0]['confirmed']","pinned":true,"hidden":false,"mode":"ojs"},{"id":766,"value":"md`### 5.4 Normalize each country/area by using the same data range:\n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":391,"value":"function insertIt (a) {\n for (let i = 1; i < a.length; i++){\n if (a[i][0] != formatTime(dateRange[i])) {\n a.splice(i, 0, [formatTime(dateRange[i]), a[i-1][1]])\n }\n }\n return a;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":437,"value":"confirmedAndDeathInserted = {\n for (let i = 0; i < firstDateAdded.length; i++) {\n insertIt(firstDateAdded[i]['confirmed']);\n insertIt(firstDateAdded[i]['deaths'])\n }\n return firstDateAdded;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":768,"value":"md`\n## Step 6: Sort by the alphabetical order of country codes\n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":491,"value":"confirmedAndDeathInserted_sorted = confirmedAndDeathInserted.sort((a, b) => a.country_code.localeCompare(b.country_code))\n// From https://stackoverflow.com/a/35092754","pinned":true,"hidden":false,"mode":"ojs"},{"id":774,"value":"md`## Step 7: Finding country codes that are different from those in Professor YY's raw data\n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":445,"value":"function onlyUnique(value, index, self) {\n return self.indexOf(value) === index;\n}\n// From https://stackoverflow.com/questions/1960473/get-all-unique-values-in-a-javascript-array-remove-duplicates?page=1&tab=votes#tab-top","pinned":true,"hidden":false,"mode":"ojs"},{"id":470,"value":"originalData = d3.json(\"https://raw.githubusercontent.com/covid19-data/covid19-data/master/output/cntry_stat_owid.json\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":503,"value":"originalCountryCodeList = {\n let original_list = []\n originalData.forEach((element) => {\n original_list.push(element.country_code)\n })\n return original_list;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":507,"value":"currentCountryCodeList = {\n let current_list = []\n confirmedAndDeathInserted_sorted.forEach(element => \n current_list.push(element.country_code)\n )\n return current_list\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":776,"value":"md`\nProfessor YY's original raw data have two unique country codes: Wolrd and Kosovo. The current dataset has 10 country codes that are not found in Professor YY's raw data:\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":510,"value":"unique_to_original = originalCountryCodeList.filter(x => !currentCountryCodeList.includes(x))\n//https://stackoverflow.com/questions/1187518/how-to-get-the-difference-between-two-arrays-in-javascript","pinned":true,"hidden":false,"mode":"ojs"},{"id":512,"value":"unique_to_current = currentCountryCodeList.filter(x => !originalCountryCodeList.includes(x))","pinned":true,"hidden":false,"mode":"ojs"},{"id":623,"value":"dataBeforeEditingCode = confirmedAndDeathInserted_sorted","pinned":true,"hidden":false,"mode":"ojs"},{"id":780,"value":"md`### 7.1. I changed \\`OWID_KOS\\` to \\`XKX\\`, and \\`OWID_WRL\\` to \\`WLD\\` to be consistant with Professor YY's codes. \n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":626,"value":"dataAfterEditingCode = {\n dataBeforeEditingCode.find(element => element.country_code == 'OWID_KOS').country_code = 'XKX';\n dataBeforeEditingCode.find(element => element.country_code == 'OWID_WRL').country_code = 'WLD';\n return dataBeforeEditingCode;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":783,"value":"md`\n## Step 8: Add the \\`region\\` object for each country/area with data from the World Bank's country metadata. \n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":538,"value":"countryMetadata = FileAttachment(\"metadata_country.csv\").csv()","pinned":true,"hidden":false,"mode":"ojs"},{"id":636,"value":"countryMetadata[0][\"Country Code\"]","pinned":true,"hidden":false,"mode":"ojs"},{"id":632,"value":"WBCountryList = {\n let WB_List = []\n countryMetadata.forEach((element) => {\n WB_List.push(element[\"Country Code\"])\n })\n return WB_List;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":646,"value":"updatedCurrCountryList = {\n let updated_country_list = [];\n dataAfterEditingCode.forEach((element) => {\n updated_country_list.push(element.country_code)\n })\n return updated_country_list;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":785,"value":"md`\n### 8.1. Find out whether there are country codes in the current data that are not found in the World Bank data. \n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":648,"value":"unique_to_updated = updatedCurrCountryList.filter(x => !WBCountryList.includes(x))\n// The list of countries that were not found in the WB country metadata list. ","pinned":true,"hidden":false,"mode":"ojs"},{"id":787,"value":"md`\n### 8.2 Fill \\`region\\` with data from the World Bank. \n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":652,"value":"datawithWBCode = {\n for (let i = 0; i < dataAfterEditingCode.length; i ++) {\n countryMetadata.forEach((element) => {\n if (dataAfterEditingCode[i].country_code == element[\"Country Code\"]) {\n dataAfterEditingCode[i].WB_Region = element.Region}\n })\n }\n return dataAfterEditingCode;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":660,"value":"dataWBCodeAfterMapping = datawithWBCode.map(d => ({\n 'country_code': d.country_code,\n 'country_name': d.country_name,\n 'population': d.population,\n 'continent': d.region,\n 'region': d.WB_Region,\n 'confirmed': d.confirmed,\n 'deaths': d.deaths\n}))","pinned":true,"hidden":false,"mode":"ojs"},{"id":799,"value":"md`\n## Step 9: Fill \\`region\\` that is still blank with data from [here](https://github.com/lukes/ISO-3166-Countries-with-Regional-Codes/blob/master/all/all.csv)\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":664,"value":"dataWBCodeAfterMapping.filter(element => element.region == undefined)","pinned":true,"hidden":false,"mode":"ojs"},{"id":789,"value":"dataNineNewRegion = dataWBCodeAfterMapping","pinned":true,"hidden":false,"mode":"ojs"},{"id":683,"value":"$31","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W32","stdlib":"1","titling":"auto"}} 2f:"$2e:body"