29:null 2a:["$","title",null,{"children":"DuckDB gives new life to SQL | Éric Mauvière | Observable"}] 2f:T670,{ // Cette syntaxe utilise l'injection de paramètres, variante classique en programmation SQL, + sécurisée let data = await db.query(`SELECT preusuel, annais, SUM(nombre) AS eft FROM prenoms2021 WHERE preusuel = ? AND annais <> 'XXXX' GROUP BY ALL ORDER BY annais`, [prenom.preusuel]) // Je recode l'année sous forme d'un vrai type Date, pour que l'axe des x du graphique à venir soit joliment construit data = data.map(d => d.toJSON()).map(d => {d.annais = new Date(d.annais, 0); return d}) const pic = d3.greatest(data, d => d.eft).annais.getFullYear(), // année du pic col = prenom.sexe == '1' ? "firebrick" : "olivedrab", // couleur différenciée garçons/filles prenom_s = getTitleCase(prenom.preusuel) // conversion + élégante majuscule-minuscules du prénom return Plot.plot({ width: Math.min(600, width), marginBottom: 50, x: {label: "Birth year", labelAnchor: 'center', labelOffset: 40, tickPadding: 5}, y: {grid: true, tickFormat : d => d.toLocaleString(), label: '', tickSize: 0}, marks: [ Plot.ruleY([0]), Plot.lineY(data, {x: "annais", y: "eft", stroke: col, strokeWidth: 3}), Plot.text(data, Plot.selectMaxY({x: "annais", y: "eft", fill: col, text: d => "Peak " + prenom_s + ": " + pic, textAnchor: pic < 2000 ? "start" : "end", dx: pic < 2000 ? 10 : -10, fontSize: 14, fontWeight: 700})), Plot.dot(data, Plot.selectMaxY({x: "annais", y: "eft", stroke: col, strokeWidth: 2, r: 5})) ] }) }30:T4a5,viewof prenom_search_list = { let s = Inputs.select([{preusuel: 'Type above more than 2 letters...', eft: 0}], {format: d => d.preusuel, label: "Names found"}) if (prenom_search.length > 2) { const sql = `SELECT preusuel, sexe, SUM(nombre) AS eft FROM prenoms2021 WHERE ( contains(lower(strip_accents(preusuel)), lower(strip_accents('${prenom_search}'))) OR (${extend_search.length} = 1 AND levenshtein(lower(strip_accents(preusuel)), lower(strip_accents('${prenom_search}'))) < 2 )) AND annais <> 'XXXX' GROUP BY ALL HAVING(eft > 50) ORDER BY levenshtein(lower(strip_accents(preusuel)), lower(strip_accents('${prenom_search}'))), eft DESC` const sel = await db.query(sql) if (sel.length > 0 ) s = Inputs.select(sel, {format: d => `${getTitleCase(d.preusuel)} (${d.eft.toLocaleString()})`, label: "Names found (" + sel.length + ")"}) } s.style.setProperty('--label-width', "auto") s.style.setProperty('max-width', "350px") return s }31:T55f,{ if (prenom_search_list.eft == 0) { return svg`` } let data = await db.query(`SELECT preusuel, annais, SUM(nombre) AS eft FROM prenoms2021 WHERE preusuel = ? and annais <> 'XXXX' GROUP BY ALL ORDER BY annais`, [prenom_search_list.preusuel]) data = data.map(d => d.toJSON()).map(d => {d.annais = new Date(d.annais, 0); return d}) const pic = d3.greatest(data, d => d.eft).annais.getFullYear(), col = prenom_search_list.sexe == '1' ? "firebrick" : "olivedrab", prenom_s = getTitleCase(prenom_search_list.preusuel) return Plot.plot({ width: Math.min(600, width), marginBottom: 50, x: {label: "Année de naissance", labelAnchor: 'center', labelOffset: 40, tickPadding: 5}, y: {grid: true, tickFormat : d => d.toLocaleString(), label: ''}, marks: [ Plot.ruleY([0]), Plot.lineY(data, {x: "annais", y: "eft", stroke: col, strokeWidth: 3}), Plot.text(data, Plot.selectMaxY({x: "annais", y: "eft", fill: col, text: d => "Pic " + prenom_s + " : " + pic, textAnchor: pic < 2000 ? "start" : "end", dx: pic < 2000 ? 10 : -10, fontSize: 14, fontWeight: 700})), Plot.dot(data, Plot.selectMaxY({x: "annais", y: "eft", stroke: col, strokeWidth: 2, r: 5})) ] }) }32:T62e,{ let data = await db.query(`SELECT preusuel, annais, SUM(nombre) AS eft FROM prenoms2021 LEFT JOIN (SELECT codgeo, libgeo, lpad(reg, 2, '0') AS reg FROM dept) d ON prenoms2021.dpt = d.codgeo WHERE preusuel = '${prenom_f.preusuel}' AND annais <> 'XXXX' AND ('${sexe}' = '0' OR sexe = '${sexe}') AND ('${dpt}' = '' OR dpt = '${dpt}') AND ('${reg}' = '' OR reg = '${reg}') GROUP BY 1,2 ORDER BY annais`) data = data.map(d => d.toJSON()).map(d => {d.annais = new Date(d.annais, 0); return d}) const pic = d3.greatest(data, d => d.eft).annais.getFullYear(), col = prenom_f.sexe == '1' ? "firebrick" : "olivedrab", prenom_s = getTitleCase(prenom_f.preusuel) return Plot.plot({ width: Math.min(600, width), marginBottom: 50, x: {label: "Birth year", labelAnchor: 'center', labelOffset: 40, tickPadding: 5}, y: {grid: true, tickFormat : d => d.toLocaleString(), label: ''}, marks: [ Plot.ruleY([0]), Plot.lineY(data, {x: "annais", y: "eft", stroke: col, strokeWidth: 3}), Plot.text(data, Plot.selectMaxY({x: "annais", y: "eft", fill: col, text: d => "Peak " + prenom_s + ": " + pic, textAnchor: pic < 2000 ? "start" : "end", dx: pic < 2000 ? 10 : -10, fontSize: 14, fontWeight: 700})), Plot.dot(data, Plot.selectMaxY({x: "annais", y: "eft", stroke: col, strokeWidth: 2, r: 5})) ] }) }33:T419,credits = md`

R Code

~~~r library(curl); library(tidyverse); library(arrow); zipfile = curl_download("https://www.insee.fr/fr/statistiques/fichier/2540004/dpt2021_csv.zip", tempfile()) csvfile = unzip(zipfile) prenoms2021 = read_delim(csvfile, col_types = list(sexe = 'c')) # A tibble: 3,784,673 x 5 sexe preusuel annais dpt nombre 1 1 _PRENOMS_RARES 1900 02 7 2 1 _PRENOMS_RARES 1900 04 9 3 1 _PRENOMS_RARES 1900 05 8 4 1 _PRENOMS_RARES 1900 06 23 5 1 _PRENOMS_RARES 1900 07 9 6 1 _PRENOMS_RARES 1900 08 4 7 1 _PRENOMS_RARES 1900 09 6 8 1 _PRENOMS_RARES 1900 10 3 9 1 _PRENOMS_RARES 1900 11 11 10 1 _PRENOMS_RARES 1900 12 7 # ... with 3,784,663 more rows write_parquet(prenoms2021 |> mutate(across(where(is.character), as.factor)), 'data/prenoms2021.parquet') ~~~ `34:T46d,viewof prenom_search_list2 = { let s = Inputs.select([{preusuel: 'Type above more than 2 letters...', eft: 0}], {format: d => d.preusuel, label: "First names found"}) if (prenom_search2.length > 2) { const sql = `SELECT preusuel, SUM(nombre) AS eft FROM prenoms2021 WHERE ( contains(lower(strip_accents(preusuel)), lower(strip_accents('${prenom_search2}'))) OR ${extend_search.length} = 1 AND levenshtein(lower(strip_accents(preusuel)), lower(strip_accents('${prenom_search2}'))) < 2 ) AND annais <> 'XXXX' GROUP BY 1 HAVING(eft > 50) ORDER BY levenshtein(lower(strip_accents(preusuel)), lower(strip_accents('${prenom_search2}'))), eft DESC` const sel = await db.query(sql) if (sel.length > 0 ) s = Inputs.select(sel, {format: d => `${getTitleCase(d.preusuel)} (${d.eft.toLocaleString()})`, label: "First names found (" + sel.length + ")"}) } s.style.setProperty('--label-width', "auto") s.style.setProperty('max-width', "350px") return s }35:T9d6,map_svg = { const map_svg = svg`` const path2154 = d3.geoPath(proj_e4326_to_map_e2154), col = 'steelblue' d3.select(map_svg).select('.fra').selectAll('path') .data([a_fra]) .join('path') .attr('d', path2154) .attr("fill", '#F5F5F5') .attr("stroke", '#444') d3.select(map_svg).select('.depts').selectAll('path') .data([l_dep]) .join('path') .attr('d', path2154) .attr("stroke", '#CCC') .attr("fill", 'none') d3.select(map_svg).select('.regs').selectAll('path') .data([l_reg]) .join('path') .attr('d', path2154) .attr("stroke", '#999') .attr("fill", 'none') if (data_prenoms.rowmax) { d3.select(map_svg).select('.key') .append('circle') .attr('r', rdmax) .attr('cx', width/10) .attr('cy', width/2) .attr("stroke", '#FFF') .attr("fill", 'steelblue') d3.select(map_svg).select('.key') .append('text') .attr('x', width/10) .attr('y', width/2 - rdmax - 5) .attr('text-anchor', 'middle') .text(data_prenoms.rowmax.eft.toLocaleString('fr', {maximumSignificantDigits: 2})) } return Object.assign(map_svg, { update: function(an) { d3.select(map_svg).select('.ronds').selectAll('circle') .data(data_ronds(an), d => d.dpt) .join('circle') .attr('r', d => d.r) .attr('cx', d => maps_centroids.map_x.get(d.dpt)) .attr('cy', d => maps_centroids.map_y.get(d.dpt)) .attr("stroke", '#FFF') .attr("fill", col) .append('title') .text(d => d.eft.toLocaleString() + " " + getTitleCase(prenom_search_list2.preusuel) + " en " + an + " (dept " + d.dpt + ")" ) return an } }) }36:[{"id":"ec76bcab43aef11fbdb68f633e6570eba2dd6189ab507c6ad32f0a8185aa632bc85df8bb5daf9caa3b2ef4d6c7c05b3ac4eb533d9a97408f4395dfe30896d0e7","href":"https://static.observableusercontent.com/files/ec76bcab43aef11fbdb68f633e6570eba2dd6189ab507c6ad32f0a8185aa632bc85df8bb5daf9caa3b2ef4d6c7c05b3ac4eb533d9a97408f4395dfe30896d0e7","name":"a_dpt_topo.json","type":"application/json","lastModified":1668753664993.635,"size":524735},{"id":"1a06e850beddd09d1a06c6875e4efdaa330afb6ef59c87765e1603ca76c7fa6fac4cd022d883d39eb785a0229dc950f0d942c0ef5d18a97fb7e0f996714ba4bc","href":"https://static.observableusercontent.com/files/1a06e850beddd09d1a06c6875e4efdaa330afb6ef59c87765e1603ca76c7fa6fac4cd022d883d39eb785a0229dc950f0d942c0ef5d18a97fb7e0f996714ba4bc","name":"dep2020.csv","type":"text/csv","lastModified":1668753664993.635,"size":1781},{"id":"1ee81194ae045bd613c6218563460a2a59a78f7681da873fe2f052b63a1e0bfd628d569364c6c88ea2a1a2944ecdb983c6fa83da217fd89e4d644f91ebd17f61","href":"https://static.observableusercontent.com/files/1ee81194ae045bd613c6218563460a2a59a78f7681da873fe2f052b63a1e0bfd628d569364c6c88ea2a1a2944ecdb983c6fa83da217fd89e4d644f91ebd17f61","name":"prenoms2021_f.parquet","type":"application/octet-stream","lastModified":1668753664993.635,"size":7653508},{"id":"f9ccfe14744ced7fac904bbee1324ea14f46e0eeaaf03a73601f4e7d1432019d0e53f78d2ba5d9ff1b06f22be068c5ec88d67060dc3e473fdf5a237db5b62ccc","href":"https://static.observableusercontent.com/files/f9ccfe14744ced7fac904bbee1324ea14f46e0eeaaf03a73601f4e7d1432019d0e53f78d2ba5d9ff1b06f22be068c5ec88d67060dc3e473fdf5a237db5b62ccc","name":"reg2020.csv","type":"text/csv","lastModified":1668753664993.635,"size":343},{"id":"9259a0519bb64602e49aed0a159070931c90e3c003694db7e029e19136da8a785863b88fef9f7c860562aabaa9ee2863d9f239b7c876a30d04fde4778d4ddaaf","href":"https://static.observableusercontent.com/files/9259a0519bb64602e49aed0a159070931c90e3c003694db7e029e19136da8a785863b88fef9f7c860562aabaa9ee2863d9f239b7c876a30d04fde4778d4ddaaf","name":"reg2021.csv","type":"text/csv","lastModified":1668867330174.852,"size":343},{"id":"133686eda66df8b3804668fb3ff22ce57e12048e3a8a8b5ade90001b6e0f97a5370c5a5c67dd157a73578ff524b60c37c56d549378980c93cb7dc7e36c7f39b4","href":"https://static.observableusercontent.com/files/133686eda66df8b3804668fb3ff22ce57e12048e3a8a8b5ade90001b6e0f97a5370c5a5c67dd157a73578ff524b60c37c56d549378980c93cb7dc7e36c7f39b4","name":"dep2021.csv","type":"text/csv","lastModified":1668867330184.685,"size":1781}] 2d:{"id":"c6cc24bff717b403","slug":"duckdb-gives-new-life-to-sql","title":"DuckDB gives new life to SQL","thumbnail":"dc1aa86aca51ade3ff4330e95c9af8cb628273806543987bb7652c6e0e665444","access_level":"public","create_time":"$D2022-11-18T06:41:04.993Z","update_time":"$D2023-07-17T13:32:24.802Z","view_time":null,"first_public_time":"$D2022-11-18T06:55:38.616Z","has_unpublished_edits":null,"fork":{"id":"29f9f5cd9d2ff909","slug":"duckdb-redonne-nouvelle-vie-sql","title":"DuckDB redonne une belle jeunesse au langage SQL","owner":{"id":"cd09b02ddc62abda","login":"ericmauviere","name":"Éric Mauvière"}},"stars":31,"starred":false,"owner":{"type":"team","id":"cd09b02ddc62abda","login":"ericmauviere","name":"Éric Mauvière","avatar_url":"https://avatars-next.observableusercontent.com/a35a91b5ca23c5dd084a628d6c4898fb6eefb2ede192b1801b0efae516f49017","tier":"starter_2024"},"version":1439,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"c86281fcf8f7c7cc","login":"ericmauviere","name":"Éric Mauvière","avatar_url":"https://avatars-next.observableusercontent.com/a35a91b5ca23c5dd084a628d6c4898fb6eefb2ede192b1801b0efae516f49017","tier":null}],"members":[{"type":"individual","id":"c86281fcf8f7c7cc","login":"ericmauviere","name":"Éric Mauvière","avatar_url":"https://avatars-next.observableusercontent.com/a35a91b5ca23c5dd084a628d6c4898fb6eefb2ede192b1801b0efae516f49017","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":true,"trashed":false,"lapsed":null,"subscribed":null,"forks":8,"collection":null,"type":"notebook","body":{"title":"DuckDB gives new life to SQL","cells":[{"id":0,"value":"# DuckDB gives new life to SQL","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":602,"value":"Real SQL databases in your browser, leveraging new Arrow and Parquet formats? 📣 It's now possible with DuckDB, and in a great way!\n\nLet's demonstrate it with a concrete example: the french statistical institute (Insee) publishes every year the [baby names file in France](https://www.insee.fr/fr/statistiques/2540004?sommaire=4767262) in a CSV format. \n\nThis table is quite heavy, it includes 3,800,000 records in its last version, published in June 2022, covering the years of birth from 1900 to 2021. Here is an extract:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1006,"value":"Inputs.table(extrait, {height: 200, layout: \"auto\", maxWidth: 500})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1014,"value":"The CSV file weighs 80 Mb, the zipped version you can download 13 Mb. This table is easy to handle with R. But how to do it with a browser? \nThe answer is the extraordinary [DuckDB](https://duckdb.org/docs/)!\n\n**I'm going to load with DuckDB an optimized version of this dataset, in [Apache Parquet](https://parquet.apache.org/)** format: it is more compact (8 Mb) and much faster to read for Parquet is an intelligent, binary, compressed and \"column-oriented\" format. It responds very quickly to read-only queries. \n\n*CSV => Parquet conversion is very easy to run in R (see end of this document), or [with that online tool](https://observablehq.com/@observablehq/csv-to-parquet).* \n\nDifferent JavaScript libraries can read the Parquet format, i selected here DuckDB, whose version for JS is now [natively embedded within Observable](https://observablehq.com/@observablehq/duckdb).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":265,"value":"## DuckDB revolutionizes data processing in the browser","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":741,"value":"DuckDB has a lot of advantages: it allows you to create, in memory in the browser, a real database whose tables come from different data sources (CSV, JSON, Parquet, Arrow...) This database can be manipulated with the SQL language, and with DuckDB **a modern and enriched version of this good old SQL, full of very smart complements**.\n\nI am building here a **`db`** database including three tables, one in a Parquet format (our baby names database), two others from CSV files (these three source files are *attached* to this document).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":7,"value":"db = DuckDBClient.of({\n prenoms2021: FileAttachment(\"prenoms2021_f.parquet\"),\n dept: {\n file: FileAttachment(\"dep2021.csv\"),\n header: true\n },\n reg: {\n file: FileAttachment(\"reg2021.csv\"),\n header: true\n }\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1437,"value":"db.query('describe table dept')","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":748,"value":"It goes pretty fast for such a rich database! \n\n**DuckDb client version for JS is *pre-compiled* in Web Assembly (WASM)**, so it's low-level code that can be executed directly by the browser, and it's much more efficient than traditional JavaScript. Initial loads and subsequent requests are dramatically accelerated. \n\nParquet format contributes to this: a precursor of its cousin [Arrow](https://arrow.apache.org/), its structure makes it easier to load data in memory, practically without transformation. In fact, [DuckDB reads the Parquet file directly](https://duckdb.org/2021/06/25/querying-parquet.html), without import or conversion.\n\nHere is a preview of the first name table again, this is the result of our first SQL query:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":363,"value":"viewof extrait = {\n const data = await db.query(`SELECT * FROM prenoms2021 \n WHERE preusuel <> '_PRENOMS_RARES' AND annais <> 'XXXX'\n USING SAMPLE 100`)\n \n // backticks ` allow to write a SQL query on several lines\n // Writing SQL verbs in uppercase is not mandatory, but it's prettier!\n \n return Inputs.table(data, {height: 200, layout: \"auto\", maxWidth: 500})\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":779,"value":"**db.query()** executes the SQL sentence and returns a JavaScript dataset (an _iterable_), which I display with a standard Observable component. (*You can also use a richer component called [SQL Cell](https://observablehq.com/@observablehq/sql-cell), but it is resource intensive, so I'll leave it out here*).\n\n## Temporal trends for the 50 most given names\n\nThe following drop-down list is fed by a second SQL query. **It's a real joy of writing simplicity**, that I let you discover and adapt by unveiling the code using the arrow left side of the list:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":29,"value":"viewof prenom = {\n const sel = await db.query(\n `SELECT preusuel, sexe, SUM(nombre) AS eft FROM prenoms2021 \n WHERE preusuel <> '_PRENOMS_RARES' \n GROUP BY ALL \n ORDER BY eft desc \n LIMIT 50`\n )\n\n // Constitution de la liste déroulante avec prénom et effectif\n const s = Inputs.select(sel, {\n format: d => `${getTitleCase(d.preusuel)} (${d.eft.toLocaleString()})`,\n label: \"First name\"\n })\n\n s.style.setProperty('--label-width', \"auto\")\n s.style.setProperty('max-width', \"300px\")\n\n return s\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":813,"value":"The first name chosen in the list evolves over the years in the following way, with a subtle highlighting of the peak allowed by the amazing Observable Plot library:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":97,"value":"$2f","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":726,"value":"## Free search for first names","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":840,"value":"DuckDB provides [clever string processing functions](https://duckdb.org/docs/sql/functions/char). They allow for example to get rid of (french) accents, and thus to open more widely the field of a search on all or part of a first name:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":645,"value":"viewof input = html``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":657,"value":"$30","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1098,"value":"viewof extend_search = Inputs.checkbox([\"Expand search to light spelling variants\"])","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":849,"value":"The echoes in this list are elegantly sorted by [Levenshtein's distance](https://fr.wikipedia.org/wiki/Distance_de_Levenshtein), which measures the distance between two words (number of operations on the letters to get from one to the other). \n\nAnother example of the interest of Levenshtein: if you check the box above \"Expand search...\", and type \"antony\", you will also target Anthony (he will recognize himself)!","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":712,"value":"$31","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":689,"value":"prenom_search = debounce(viewof input)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":410,"value":"## Multi-parameter query","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":853,"value":"To take full advantage of the richness of this table of first names, let's multiply filtering criteria. \n\nI will use the two other tables, list of departments and regions in France, to enhance the presentation and allow aggregation at the regional level.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":951,"value":"viewof sexe = {\n \n const s = Inputs.radio(\n new Map([[\"all\", \"0\"], [\"male\", \"1\"], [\"female\", \"2\"]]),\n {value: \"0\", label: \"Gender\", format: ([name, value]) => `${name}`}\n )\n\n s.style.setProperty('--label-width', \"145px\")\n s.style.setProperty('max-width', \"500px\")\n\n return s\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":374,"value":"viewof annais = {\n\n const sql = `SELECT DISTINCT annais FROM prenoms2021 \n WHERE annais <> 'XXXX' \n ORDER BY 1`\n \n const sel = await db.query(sql)\n \n const s = Inputs.select(sel.map(d => d.annais), {\n label: \"Birth year >= \"\n })\n\n s.style.setProperty('--label-width', \"150px\")\n s.style.setProperty('max-width', \"400px\")\n\n return s\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":977,"value":"viewof reg = {\n\n const sql = `SELECT lpad(codgeo, 2, '0') AS codgeo, libgeo FROM reg \n ORDER BY codgeo`\n \n const sel = await db.query(sql)\n \n const s = Inputs.select([{codgeo: ''}].concat(sel), {\n label: \"Region of birth\", format: d => d.codgeo == '' ? 'All' : (d.codgeo + \" - \" + d.libgeo), valueof: d => d.codgeo\n })\n \n s.style.setProperty('--label-width', \"150px\")\n s.style.setProperty('max-width', \"400px\")\n\n return s\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":386,"value":"viewof dpt = {\n\n let sql = `SELECT * FROM (SELECT DISTINCT dpt FROM prenoms2021 WHERE dpt <>'XX') p \n LEFT JOIN \n (SELECT codgeo, libgeo, lpad(reg, 2, '0') AS reg FROM dept) d \n ON p.dpt = d.codgeo \n WHERE ('' = '${reg}' OR reg = '${reg}')\n ORDER BY dpt`\n // Les deux départements de Corse sont regroupés dans la base avec un code 20\n \n const sel = await db.query(sql)\n \n const s = Inputs.select([{codgeo: ''}].concat(sel), {\n label: \"Department of birth \", format: d => d.codgeo == '' ? 'All' : (d.codgeo + \" - \" + d.libgeo), valueof: d => d.codgeo\n })\n \n s.style.setProperty('--label-width', \"150px\")\n s.style.setProperty('max-width', \"400px\")\n\n return s\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":313,"value":"viewof prenom_f = {\n \n let sql = `SELECT preusuel, sexe, SUM(nombre) as eft FROM prenoms2021 \n LEFT JOIN (SELECT codgeo, libgeo, lpad(reg, 2, '0') AS reg FROM dept) d \n ON prenoms2021.dpt = d.codgeo\n WHERE preusuel <> '_PRENOMS_RARES' AND annais <> 'XXXX' `\n \n if (sexe != '0') {\n sql += ` AND sexe = '${sexe}'`\n }\n \n if (annais != '1900') {\n sql += ` AND annais > '${annais}'`\n }\n\n if (reg != '') {\n sql += ` AND reg = '${reg}' `\n }\n \n if (dpt != '') {\n sql += ` AND dpt = '${dpt}' `\n }\n\n sql += `GROUP BY 1, 2 \n ORDER BY 3 DESC \n LIMIT 50`\n\n const sel = await db.query(sql)\n \n const s = Inputs.select(sel, {format: (d) => `${getTitleCase(d.preusuel)} (${d.eft.toLocaleString()})`,\n label: \"Name\"\n })\n \n s.style.setProperty('--label-width', \"150px\")\n s.style.setProperty('max-width', \"400px\")\n\n return s\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":346,"value":"$32","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1274,"value":"## Most popular baby names by region and year","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1277,"value":"viewof annais_reg = {\n const s = Inputs.select(d3.range(1900, 2022).map(d => d.toString()), {\n label: \"Birth year \", value: \"2021\"\n })\n\n s.style.setProperty('--label-width', \"auto\")\n s.style.setProperty('max-width', \"200px\")\n\n return s\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1278,"value":"viewof sexe_reg = {\n \n const s = Inputs.radio(\n new Map([[\"male\", \"1\"], [\"female\", \"2\"]]),\n {value: \"1\", label: \"Gender\", format: ([name, value]) => `${name}`}\n )\n\n s.style.setProperty('--label-width', \"auto\")\n s.style.setProperty('max-width', \"500px\")\n\n return s\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1279,"value":"{\n const data = await db.query(\n `SELECT reg || ' - '|| libgeo AS Region, arg_max(preusuel, nombre) as 'Most popular' FROM \n ( SELECT reg, preusuel, SUM(nombre) AS nombre \n FROM prenoms2021 left join (SELECT codgeo, libgeo, lpad(reg, 2, '0') AS reg FROM dept) d\n ON prenoms2021.dpt = d.codgeo\n WHERE annais = ? AND sexe = ? AND preusuel <> '_PRENOMS_RARES' \n GROUP BY ALL \n ) p LEFT JOIN reg ON p.reg = reg.codgeo\n GROUP BY ALL ORDER BY ALL`, \n [annais_reg, sexe_reg])\n\n return Inputs.table(data.map(d => d.toJSON())\n .map(d => {d['Most popular'] = getTitleCase(d['Most popular']); return d}), {height: 440, layout: \"auto\", maxWidth: 400})\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1285,"value":"This table is built, in DuckDB SQL, by a _relatively simple_ query using the magic function ✨ `arg_max()` !","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":864,"value":"## How to convert a CSV file to Parquet format","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":866,"value":"You will need only 5 lines of R code to download the zip from INSEE and convert its content to Parquet format. So don't hesitate to use this format!","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":895,"value":"$33","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":947,"value":"Parquet stores each column of data separately, and describes the characteristics of each column in detail. For character types, for example, a _dictionary_ lists all distinct values, for example each department code in the `dpt` column.\n\n**In R, this is similar to _factors_ concept**. Converting character columns to factors speeds up future queries on the exported Parquet file. This is why i'd rather use R to generate Parquet files.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":903,"value":"## Miscellaneous resources","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":905,"value":"Very few things to load by hand so far, DuckDB, Plot, or the Input component library are part of Observable!","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":590,"value":"function getTitleCase(str) {\n return str.split('-').map(d => d.charAt(0).toUpperCase() + d.slice(1).toLowerCase()).join('-')\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":649,"value":"import {debounce} from \"@mbostock/debouncing-input\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1192,"value":"## Cartographic Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1193,"value":"This last section animates a map pertaining to a selected first name. The peak year is used as a reference. We can see that DuckDB is once again at the \"rendez-vous\" of fluidity.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1194,"value":"viewof input2 = html``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1195,"value":"$34","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1196,"value":"viewof an = Scrubber(d3.range(1900,2022), {initial: data_prenoms.an_pic_i, autoplay: false, loop: false, delay: 50})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1197,"value":"viewof rdmax = Inputs.range([20, 100], {label: \"Radius max\", step: 5, value: width/30})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1198,"value":"$35","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1225,"value":"**Functional logic**","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1199,"value":"data_ronds = function(an) {\n const alpha = rdmax / Math.sqrt(data_prenoms.rowmax.eft)\n \nreturn data_prenoms.data.filter(d => d.annais == an)\n .sort((a,b) => d3.descending(a.eft, b.eft))\n .map(d => d.toJSON())\n .map(d => {return {dpt: d.dpt, r: alpha * Math.sqrt(d.eft), eft: d.eft}})\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1200,"value":"data_prenoms = {\n if (prenom_search_list2.eft == 0) {\n return {data: [], rowmax: null, an_pic_i: 0}\n }\n \n let data = await db.query(`SELECT dpt, annais, SUM(nombre) AS eft FROM prenoms2021\n WHERE preusuel = ? AND annais <> 'XXXX'\n GROUP BY GROUPING SETS ((dpt, annais), (annais))\n ORDER BY dpt, annais`, \n [prenom_search_list2.preusuel])\n\n const row_an_max = d3.greatest(data.filter(d => d.dpt === null), d => d.eft)\n \n data = data.filter(d => d.dpt !== null)\n \n const row_dpt_an_max = d3.greatest(data, d => d.eft)\n\n return {data: data, rowmax: row_dpt_an_max, an_pic_i: +row_an_max.annais - 1900}\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1201,"value":"{\n if (prenom_search_list2.eft > 0) map_svg.update(an)\n\n return \"maj de la carte\"\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1202,"value":"maps_centroids = {\n const coords = a_dpt.features.map(d => [d.properties.dep].concat(proj_e4326_to_map_e2154([d.properties.xcl4326, d.properties.ycl4326])))\n\n coords.push(['20'].concat(proj_e4326_to_map_e2154([9.12, 42.15]))) // 20 = Corse, ajout manuel du centroïde\n\n const map_x = new Map( coords.map(d => [d[0], Math.round(d[1])]))\n const map_y = new Map( coords.map(d => [d[0], Math.round(d[2])]))\n\n return {map_x, map_y}\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1203,"value":"prenom_search2 = debounce(viewof input2)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1229,"value":"**External parameters and resources**","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1204,"value":"map_extent = [\n [margin, margin],\n [size.width - margin, size.height - margin]\n ]","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1205,"value":"size = {\n const d = Math.min(window.screen.width, window.screen.height)\n const w = Math.min(width, 900, d) \n const h = w\n\n return {width: w, height: h}\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1206,"value":"margin = 15","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1207,"value":"proj_e4326_to_map_e2154 = proj4d3(2154).fitExtent(map_extent, a_dpt)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1209,"value":"a_dpt_topo = FileAttachment('a_dpt_topo.json').json()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1208,"value":"a_dpt = topojson.feature(a_dpt_topo, a_dpt_topo.objects.a_dpt_topo)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1210,"value":"a_fra = topojson.merge(a_dpt_topo, a_dpt_topo.objects.a_dpt_topo.geometries)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1211,"value":"l_dep = topojson.mesh(a_dpt_topo, a_dpt_topo.objects.a_dpt_topo, \n (a, b) => a !== b )","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1212,"value":"l_reg = topojson.mesh(a_dpt_topo, a_dpt_topo.objects.a_dpt_topo, \n (a, b) => a.properties.reg !== b.properties.reg )","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1213,"value":"proj4 = {\n let proj4 = await require(\"proj4\")\n let epsg2154 = \"+proj=lcc +lat_1=49 +lat_2=44 +lat_0=46.5 +lon_0=3 +x_0=700000 +y_0=6600000 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs\"\n \n proj4.defs([ ['EPSG:2154', `+title=France Lambert93 ${epsg2154}`] ])\n \n return proj4\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1214,"value":"function proj4d3(proj4string) {\n let raw\n \n if (+proj4string == proj4string) \n raw = proj4('EPSG:' + proj4string)\n \n if (!raw)\n raw = proj4(proj4string)\n \n const degrees = 180 / Math.PI,\n radians = 1 / degrees,\n p = function(lambda, phi) {\n return raw.forward([lambda * degrees, phi * degrees])\n }\n p.invert = function(x, y) {\n return raw.inverse([x, y]).map(function(d) {\n return d * radians\n })\n }\n const projection = d3.geoProjection(p).scale(1)\n projection.raw = raw\n return projection\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1215,"value":"import {Scrubber} from \"@mbostock/scrubber\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W36","stdlib":"1","titling":"auto"}} 2e:"$2d:body"