2c:null 2d:["$","title",null,{"children":"Analysis of public data in Observable with SQL & Plot | Ernesto Laval | Observable"}] 32:[{"id":"552fb253c5d24e54bab5f787fcbf21cef55da94b8b726494f4b49e7c58d3cd40c03d28f26e7c3300d2efc6329009f593abb9b3d860342d13a65b2f35c351ef39","href":"https://static.observableusercontent.com/files/552fb253c5d24e54bab5f787fcbf21cef55da94b8b726494f4b49e7c58d3cd40c03d28f26e7c3300d2efc6329009f593abb9b3d860342d13a65b2f35c351ef39","name":"defunciones.parquet","type":"application/octet-stream","lastModified":1651522724859.533,"size":40412128},{"id":"a264195865a4bfb142d2e9d7ddcbb5dd149e9d2225d8b60605ea6e5cddc17ced9c505ea3416deb6ac5385a6a1be2919a75695857c5e2abc5aaecfae9a6da714a","href":"https://static.observableusercontent.com/files/a264195865a4bfb142d2e9d7ddcbb5dd149e9d2225d8b60605ea6e5cddc17ced9c505ea3416deb6ac5385a6a1be2919a75695857c5e2abc5aaecfae9a6da714a","name":"excessDeathsUSA_TheEconomist.sqlite","type":"application/x-sqlite3","lastModified":1651522724859.533,"size":909312},{"id":"43e5ba9903408b60a0242f17bc0178196846b24027ecb3a58ac7e7d57ef862f2717a09818ab627d5f1558c55cffa19acb9f3d51af47e060f16f90a93449176fb","href":"https://static.observableusercontent.com/files/43e5ba9903408b60a0242f17bc0178196846b24027ecb3a58ac7e7d57ef862f2717a09818ab627d5f1558c55cffa19acb9f3d51af47e060f16f90a93449176fb","name":"chapterblockdescription.csv","type":"text/csv","lastModified":1651524849991.672,"size":1454}] 30:{"id":"c7ee7e51192c2994","slug":"analysis-of-public-data-in-observable-with-sql-plot","title":"Analysis of public data in Observable with SQL & Plot","thumbnail":"46b26f9a2540b4070877aaef32701c866e5bcfd3d1e4050665cd86e9053cd44e","access_level":"public","create_time":"$D2022-05-02T20:18:44.859Z","update_time":"$D2022-05-02T21:44:43.452Z","view_time":null,"first_public_time":"$D2022-05-02T21:01:17.143Z","fork":{"id":"778e1bc81ee85e15","slug":"analisis-de-datos-publicos-en-observable-con-sql-y-plot","title":"Análisis de datos públicos en Observable con SQL y Plot","owner":{"id":"0843f5dc42ca68a0","login":"elaval","name":"Ernesto Laval"}},"stars":0,"starred":false,"owner":{"type":"team","id":"0843f5dc42ca68a0","login":"elaval","name":"Ernesto Laval","avatar_url":"https://avatars-next.observableusercontent.com/089f0414fc9025dadb05cc06d7a406bc9be10093187c46729cb4ba3a4098dc30","tier":"starter_2024"},"version":382,"publish_version":382,"role":null,"authored":false,"authors":[{"type":"individual","id":"e04cd7b5f4351ae9","login":"elaval","name":"Ernesto Laval","avatar_url":"https://avatars-next.observableusercontent.com/089f0414fc9025dadb05cc06d7a406bc9be10093187c46729cb4ba3a4098dc30","tier":null}],"members":[{"type":"individual","id":"e04cd7b5f4351ae9","login":"elaval","name":"Ernesto Laval","avatar_url":"https://avatars-next.observableusercontent.com/089f0414fc9025dadb05cc06d7a406bc9be10093187c46729cb4ba3a4098dc30","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"title":"Analysis of public data in Observable with SQL & Plot","cells":[{"id":0,"value":"# Analysis of public data in Observable with SQL & Plot","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":22,"value":"When we want to share data in Internet many times our effort is focused on IT tasks (configuring the database, setup servers, publishing a web page) and not in what should be the core of the task: explore data and present it in the right way. Observable is a tool that allows to reduce the effort on IT tasks and focus on information.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":30,"value":"You are reading an Observable \"notebook\" which is composed of a sequence of cells (or blocks). Each cell can have different type of content. For example:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":42,"value":"### Cell with Javascript code:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":38,"value":"{\n const a = `Three plus seven`;\n const b = 3;\n\n return `${a} is ${b + c}`;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":294,"value":"*Note: Cells can be named and its content can be used by other cells. For example:*","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":299,"value":"c = 7","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":46,"value":"### Cell with MD content:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":47,"value":"## Title\n* Item 1\n* Item 2 **highlighted**","pinned":true,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":52,"value":"### Celda with HTML content:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":66,"value":"\n \n \n \n

Name	Age
Anne	23
Bryan	18

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":103,"value":"### Cell with Latex content:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":83,"value":"\nx_i=\\sqrt[n]{\\frac{a_i}{b_i}}","pinned":true,"hidden":false,"mode":"tex","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":118,"value":"### Cell with SQL content","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":126,"value":"I will spend more time here.\n\nSQL is a language that allows you to query Databases (particularly relational databases composed by one or more data tables). In Observable it is possible to attach files to Notebooks, and particularly it is possible to attach a file of \".sqlite\" type which contains data from a SQLite database. This solution is very useful for \"relatively\" small databases since you don't need to setup a **server** to manage it. All the database operation can take place directly in the Web Browser.\n\nFor example, the following cell (named dbExcessDeaths) is associated to a SQLite database with data in a file attached to this notebook. This database contains data from Excess Deaths in different states from USA using data published by The Economist (https://github.com/TheEconomist/covid-19-excess-deaths-tracker).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":140,"value":"dbExcessDeaths = FileAttachment(\"excessDeathsUSA_TheEconomist.sqlite\").sqlite()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":156,"value":"We can query the database in a SQL type cell:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":163,"value":"SELECT * \nFROM united_states_excess_deaths\nWHERE region = 'California'\nAND start_date >= '2021-01-01'\nAND end_date <= '2021-12-31'","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"dataCalifornia","database":"var:dbExcessDeaths"},{"id":178,"value":"Then, we can plot a chart with this data using Plot, which is included in Observable\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":182,"value":"Plot.plot({\n marginLeft: 50,\n color: { legend: true },\n marks: [\n Plot.areaY(dataCalifornia, {\n x: (d) => new Date(d[\"start_date\"]),\n y: \"total_deaths\",\n fill: (d) => \"Total deaths\"\n }),\n Plot.areaY(dataCalifornia, {\n x: (d) => new Date(d[\"start_date\"]),\n y: \"expected_deaths\",\n fill: (d) => \"Expected deaths\"\n })\n ]\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":138,"value":"# Data for deaths in Chile by death cause","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":9,"value":"Next, I will use as an example a relatively big data file: data with deaths in Chile since 2016 to date (over 220Mb).\n\nThis data is published weekly by the Department for Health Statistics and Information (DEIS) from the Chilean Ministry of Health in their open data page (https://deis.minsal.cl/#datosabiertos)\n\nThis is a csv file with over 700 thousand records (one record per death person)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":15,"value":"The file size exceeds the limit allowed by Observable for files attached to a notebook (50Mb), but there are alternatives to compress the fiel size.\n\n**Parquet** (https://parquet.apache.org/) is a data format that allows to considerably compress the file size, reducing the original 220Mb file to a 40Mb file that I have attached to this notebook.\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":197,"value":"Even though SQLite cannot work with \"parquet\" files, there is a similar database engine that can handle it: DuckDB (https://duckdb.org/).\n\nNext cell has the dbDeaths database with data from the attached file \"defunciones.parquet\" *(Data published by DEIS on Abril 28th 2022)*\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":202,"value":"dbDeathsChile = DuckDBClient.of([FileAttachment(\"defunciones.parquet\")])","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":206,"value":"With this data it is possible to query, for example, all deaths by \"Diseases of the respiratory system\" since 2016.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":209,"value":"SELECT * \nFROM defunciones\nWHERE CAPITULO_DIAG1 = 'I00-I99'\nLIMIT 100","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:dbDeathsChile"},{"id":235,"value":"We can also query for the total number of daily deaths by a specific cause in the Metropolitan Region of Santiago","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":239,"value":"SELECT FECHA_DEF as date, count(*)::Int as deaths\nFROM defunciones\nWHERE GLOSA_REG_RES = 'Metropolitana de Santiago'\nAND CAPITULO_DIAG1 = ${causaSeleccionada.CAPITULO_DIAG1}\nGROUP BY FECHA_DEF","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"dataDeathsRegion","database":"var:dbDeathsChile"},{"id":244,"value":"viewof causaSeleccionada = {\n const opcionesCausas = (\n await dbDeathsChile.query(\n `SELECT DISTINCT CAPITULO_DIAG1, GLOSA_CAPITULO_DIAG1 FROM defunciones ORDER BY CAPITULO_DIAG1`\n )\n )\n .toArray()\n .map((d) => ({\n CAPITULO_DIAG1: d.CAPITULO_DIAG1,\n GLOSA_CAPITULO_DIAG1: dictEnglishNames[d.CAPITULO_DIAG1]\n }));\n\n return Inputs.select(opcionesCausas, {\n format: (d) => `${d.CAPITULO_DIAG1} - ${d.GLOSA_CAPITULO_DIAG1}`,\n label: \"Cause\",\n value: _.last(opcionesCausas)\n });\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":261,"value":"And we can plot this data","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":285,"value":"Deaths in the Metropolitan Region of Santiago with cause of death **${causaSeleccionada.CAPITULO_DIAG1 == \"U00-U99\" ? `COVID19 (${causaSeleccionada.GLOSA_CAPITULO_DIAG1})`: causaSeleccionada.GLOSA_CAPITULO_DIAG1}**\n ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":265,"value":"Plot.plot({\n marks: [\n Plot.areaY(\n dataDeathsRegion,\n Plot.windowY({\n k: 14,\n x: (d) => new Date(d[\"date\"]),\n y: \"deaths\",\n fill: \"grey\"\n })\n )\n ]\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":367,"value":"dictEnglishNames = {\n // Creates a dictionary with the english names associated to each ICD10 cause of death chapter\n const dict = {};\n\n const ICD_DiseaseChapter_EN = await FileAttachment(\n \"chapterblockdescription.csv\"\n ).csv();\n\n ICD_DiseaseChapter_EN.forEach((d) => {\n dict[d.block] = d.description;\n });\n\n return dict;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":6,"value":"import { DuckDBClient } from \"@cmudig/duckdb\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W32","stdlib":"1","titling":"auto"}} 31:"$30:body"