21:null 22:["$","title",null,{"children":"Suivi mensuel des prestations de solidarité pendant la crise sanitaire | Éric Mauvière | Observable"}] 27:T558,md`# Suivi mensuel des prestations de solidarité pendant la crise sanitaire Dernière mise à jour : **15 décembre 2021**. Dans le contexte actuel de crise sanitaire, la [Direction de la recherche, des études, de l’évaluation et des statistiques (DREES)](https://drees.solidarites-sante.gouv.fr/etudes-et-statistiques/publications/etudes-et-resultats/article/plus-de-2-millions-d-allocataires-du-rsa-fin-octobre-2020-mise-en-place-d-un) publie désormais (décembre 2020) chaque mois un tableau de suivi des prestations de solidarité. [Le bulletin publié le 30 juillet 2021](https://drees.solidarites-sante.gouv.fr/communique-de-presse/suivi-mensuel-des-prestations-de-solidarite-pendant-la-crise-sanitaire-5) montre ainsi que **en mai, le nombre d’allocataires du RSA retrouve quasiment son niveau d’avant crise, fin avril, le nombre de bénéficiaires de la Garantie Jeunes atteint son plus haut niveau**. 6 indicateurs sont suivis dans ce nouveau tableau de bord : ### Minima sociaux d'insertion * Le revenu de solidarité active (RSA) * L’allocation de solidarité spécifique (ASS) * L’allocation aux adultes handicapés (AAH) ### Autres prestations sociales * La prime d’activité * Les aides au logement * La Garantie jeunes Voici de quoi consulter l'ensemble de ces données, France entière, par région ou par département. `28:T1607,chart = { vl.vega.timeFormatLocale(locale); vl.vega.formatLocale(locale); const compar = typrep == 'std' && curdep != 'FR', estimvar = 'estim_' + curpresta, curpresta_l = prestas.filter(d => d.value == curpresta)[0].label; let title = ["Évolution mensuelle du nb. d'allocataires " + curpresta_l + ' - source : DREES'] ; if (width < 700) { title = ["Évolution mensuelle du nb. d'allocataires", curpresta_l, 'Source : DREES'] } let subdata = data.params({curdep, curreg, compar}) .filter(d => d.codgeo == curdep || (compar && ( d.codgeo == curreg || d.codgeo == 'FR'))) ; const seuil_estim = subdata.params({estimvar}) .groupby(estimvar, 'codgeo').derive({rank: d => op.rank()}) .filter("d => d." + estimvar + "== 1 && d.rank == 1") .derive({[estimvar]: d => 0}) ; subdata = subdata.concat(seuil_estim).orderby('mois') ; //ajout de l'observation début de pointillés, qui doit être dupliquée const minmois = data.rollup({minmois: d=> op.min(d.mois)}).get('minmois', 0) ; const minmax = subdata.rollup({min: op.min(curpresta), max: op.max(curpresta)}) ; const minstat = minmax.get('min', 0), maxstat = minmax.get('max', 0) ; const plage = maxstat - minstat let yscale = typrep == 'adj' ? {domain:[minstat - 0.1 * plage, maxstat + 0.1 * plage]} : {} ; // ou + simple, mais un peu trop serré à mon goût : //let yscale = typrep == 'adj' ? { zero: false } : {} //ajutement de la légende, selon qu'il y a 1, 2 ou 3 courbes let colorscale = {domain:[curlibdep, curlibreg, 'France'], range: ['#f4b84d', '#1e9b9e', 'grey']} ; if (typrep !== 'std') { colorscale = {domain:[curlibdep], range: ['#f4b84d']} } else if (curlibdep == curlibreg) { colorscale = {domain:[curlibdep, 'France'], range: ['#f4b84d', 'grey']} } // base du graphique, avec axe x temporel const chart = vl .data(subdata) .encode( vl.x().fieldT("mois") .axis({labelOverlap: true, //format: "%b %y", tickCount:{"interval": "month"}, labelFlush:false, labelAngle: width < 600 && width > 400 ? -45 : 0, gridColor: {expr: "timeFormat(datum.value, '%m') % 2 == 0 ? '#ccc' : '#fff'"}, tickSize: {expr: "timeFormat(datum.value, '%m') % 2 == 0 ? 5 : 0"}, gridDash: {expr: "timeFormat(datum.value, '%m') == '12' ? [] : [2,2]"}, labelExpr: "timeFormat(datum.value, '%m') == '12' ?" + " +timeFormat(datum.value, '%Y') + 1 : timeFormat(datum.value, '%b')" }).title(null) ) .title({text: title, anchor: 'start', frame: 'group'}) //repère vertical glissant avec la souris const mouseover = vl .selectSingle("curmois") .on("mouseover") .encodings("x") .nearest(true) .init({ x: d3.utcMonth.offset(minmois, 2).getTime() }); //repère calé par défaut à 2 mois de l'origine const hiddenPoints = // couche de détection du mvt de la souris vl.markPoint({ opacity: 0 }) .select(mouseover) // courbes d'évolution let line = vl.markLine({ cursor: "pointer", tooltip: true, size: 2 }) if (compar) { // recalcul au survol de la base commune line = line.transform( vl.lookup("codgeo").from(mouseover.key("codgeo")), vl.calculate( "datum.curmois && datum.curmois." + curpresta + " > 0 ? (datum." + curpresta + " - datum.curmois." + curpresta + ")/datum.curmois." + curpresta + " : 0" ).as("indexed_presta") ) } line = line.encode( vl.y().fieldQ(compar ? "indexed_presta" : curpresta).title(null) .axis(compar ? { format: "%" } : {}).scale(yscale), vl.strokeDash().fieldN(estimvar).legend(null), // prise en compte estimations = pointillés vl.color().fieldN('libgeo') .legend({title:null, orient: 'top', symbolStrokeWidth: 3, labelFontSize: 12, symbolSize: 400}) .scale(colorscale), vl.tooltip({format: ' ,.0f'}).fieldQ(curpresta) ) const verticalRuleBase = vl.mark() .transform(vl.filter(mouseover)) .encode(vl.color().value("firebrick")); const verticalRule = vl.layer( verticalRuleBase.markRule({ strokeWidth: 2, opacity: compar ? 1 : 0 }), verticalRuleBase.markText({ align: "left", dx: 10, dy: 10, opacity: compar ? 1 : 0}) .encode( vl.y().value(0), vl.text().fieldT("mois").timeUnit("yearmonth") ) ); const lastFigureBase = vl.mark() .encode( vl.x().fieldT("mois").aggregate('max'), //position du dernier point de la courbe vl.y().fieldQ(curpresta).aggregate({argmax: "mois"}), ), lastFigureBaseText = lastFigureBase .encode( vl.text({"format": " ,.0f"}).fieldQ(curpresta).aggregate({argmax: "mois"}) //valeur du dernier point ), colText = '#F1A027', textOptions = {align: "right", dx: 3, dy: -15, size: 12, fontWeight: 'bold', color: colText}, white = {stroke: 'white', strokeWidth: 5}; const lastFigure = typrep == 'adj' ? vl.layer( lastFigureBaseText.markText(textOptions, white), lastFigureBaseText.markText( textOptions ), lastFigureBase.markCircle({size: 40, color: colText}) ) : vl.markPoint().data([{}]) ; return chart .layer(hiddenPoints, line, verticalRule, lastFigure) .width(cwidth - 30) .autosize({type: "fit-x", contains: "padding"}) .height(300) .render({renderer:'svg'}) }29:Tfb8,credits = md`

Code R (mis à jour le 30/06/2021, le format des données ayant un peu bougé :)

~~~r library(tidyverse) ; library(readxl) ; library(curl) ; library(janitor) # Chargement du fichier xlsx xlsfile <- 'data/drees_prestas_solidarite.xlsx' url_prestas <-'https://data.drees.solidarites-sante.gouv.fr/api/datasets/1.0/donnees-mensuelles-sur-les-prestations-de-solidarite/attachments/donnees_mensuelles_prestations_solidarite_juin_xlsx/' curl_download( url_prestas, xlsfile ) ## ETAPE 1 : lecture des données agrégées France entière tbl0 <- read_excel(xlsfile, sheet = 3, skip = 5) %>% clean_names() %>% select(1, starts_with('effectif'), starts_with('x')) indics = c('rsa','aah','ass','pac','alg','gjs','gja','gje') # liste des prestations stats = list( 'rsa'='effectif_2' ,'aah'='effectif_5' ,'ass'='effectif_8' , 'pac' = 'effectif_11', 'alg'='effectif_14' ,'gjs' = 'effectif_18', 'gja' = 'effectif_20','gje' = 'effectif_22' ) # liste des prestations estims = list( 'estim_rsa' ='x3' ,'estim_aah'='x6' ,'estim_ass'='x9' , 'estim_pac' = 'x12', 'estim_alg' = 'x15' ) # liste des estims tbl_fr <- tbl0 %>% filter(!is.na(strtoi(x1))) %>% mutate(across(starts_with('effectif_'), ~ as.numeric( str_replace_all(na_if(.x, 'nd'), c(" " = "", "[*]" = ""))))) %>% mutate(mois = as.Date(as.integer(x1), origin = "1899-12-30"), .before = 1, .keep = "unused") %>% mutate(dep = 'FR', .after = 1) %>% rename(!!!stats) %>% mutate(across(starts_with('x'), ~ replace_na(as.integer(. == "*"), 0 ))) %>% rename(!!!estims) %>% select(-x16) %>% mutate(estim_gjs = 0, estim_gja = 0, estim_gje = 0) ## ETAPE 2 : lecture des données par département # fonction de lecture de chaque onglet prestation par département et mois loadSheet <- function(xlsfile, idsheet, varstat, tbl_fr) { # recodage des abréviations de mois, en espérant que l'intuition est la bonne pour ceux ne figurant pas estimvar = str_c('estim_', varstat) tbl0 <- read_excel(xlsfile, sheet = idsheet, skip = 6) %>% clean_names() %>% select(1,estim=2, starts_with('effectif')) # lecture des lignes avec données valides, non-estimées (sans *) tbl1 <- tbl0 %>% filter(!is.na(strtoi(x1)) & is.na(estim)) %>% mutate(across(starts_with('effectif_'), ~ as.numeric( str_replace_all(na_if(.x, 'nd'), c(" " = "", "[*]" = ""))))) %>% mutate (mois = as.Date(as.integer(x1), origin = "1899-12-30"), .before = 1, .keep = "unused") %>% mutate({{estimvar}} := 0, .after = 1) # lecture des lignes avec données estimées (avec *) tbl2 <- tbl0 %>% filter(estim=='*') %>% mutate(across(starts_with('effectif_'), ~ as.numeric( str_replace_all(na_if(.x, 'nd'), c(" " = "", "[*]" = ""))))) %>% mutate (mois = as.Date(as.integer(x1), origin = "1899-12-30"), .before = 1, .keep = "unused") %>% mutate({{estimvar}} := 1, .after = 1) # assemblage avec un nouveau champ estim_ pour diférencier le statut des données tbl3 <- tbl1 %>% bind_rows(tbl2) %>% pivot_longer(cols = starts_with('effectif'), values_to = varstat, names_to = 'idgeo', names_pattern = "effectif_(\\d+)") %>% mutate(idgeo = (strtoi(idgeo)-1)/2) # correction des codes département (0 manquants) et transposition dep <- read_excel(xlsfile, sheet = 5, range="B4:GU5") %>% clean_names() %>% remove_empty() %>% pivot_longer(cols = everything(), values_to = 'dep', values_transform = list(dep = as.character)) %>% mutate(dep = str_pad(dep, 2, "left", "0")) %>% mutate(idgeo = row_number()) %>% select(-name) tbl <- tbl3 %>% left_join(dep) %>% select(mois, dep, estimvar, varstat) %>% bind_rows(tbl_fr %>% select(mois, dep, estimvar, varstat)) # export sous forme d'un fichier CSV par type de prestation write_csv(tbl, str_glue('data/tbl_{varstat}.csv')) } for (i in 5:11) { print(i) loadSheet(xlsfile, i, indics[i-4], tbl_fr) } ~~~ `2a:Tcfa,chart2 = { vl.vega.timeFormatLocale(locale); vl.vega.formatLocale(locale); const compar = false, estimvar = 'estim_' + curpresta let subdata = data.params({curdep, curreg, compar}) .filter(d => d.codgeo == curdep || (compar && ( d.codgeo == curreg || d.codgeo == 'FR'))) ; const seuil_estim = subdata.params({estimvar}) .groupby(estimvar, 'codgeo').derive({rank: d => op.rank()}) .filter("d => d." + estimvar + "== 1 && d.rank == 1") .derive({[estimvar]: d => 0}) ; subdata = subdata.concat(seuil_estim).orderby('mois').derive({fid: d => op.row_number()}) ; //ajout de l'observation début de pointillés, qui doit être dupliquée const n = subdata.numRows() //ajutement de la légende, selon qu'il y a 1, 2 ou 3 courbes let colorscale = {domain:[curlibdep], range: ['#f4b84d']} // base du graphique, avec axe x temporel const chart = vl .data(subdata) .encode( vl.x().fieldT("mois").axis({format: "%b %y", labelOverlap: true, labelFlush:false, labelAngle: width < 900 && width > 400 ? -45 : 0, gridDash: {expr: "timeFormat(datum.value, '%m') == '01' ? [] : [2,2]"}, labelExpr: "[timeFormat(datum.value, '%b'), timeFormat(datum.value, '%m') == '01' ?" + "timeFormat(datum.value, '%Y') : '']" }).title(null) ) // courbes d'évolution let line = vl.markLine({ cursor: "pointer", tooltip: true, size: 2 }) line = line.encode( vl.y().fieldQ(curpresta).title(null).scale({zero: false}), vl.strokeDash().fieldN(estimvar).legend(null), // prise en compte estimations = pointillés vl.color().fieldN('libgeo') .legend({title:null, orient: 'top', symbolStrokeWidth: 3, labelFontSize: 12, symbolSize: 400}) .scale(colorscale) ) const hover = vl.selectSingle() .encodings('x') // limit selection to x-axis value .on('mouseover') // select on mouseover events .nearest(true) // select data point nearest the cursor .empty('none'); // empty selection includes no data points return chart .layer( line, //vl.markRule({color: '#ddd'}) // .transform(vl.filter(hover)), line.markCircle({size: 60}) .select(hover) // use as anchor points for selection .encode( vl.opacity().if(hover, vl.value(1)).value(0), vl.color().fieldN('libgeo').legend(null) ), line.markText({color: '#D8870E', dx: 3, dy: -15, size: 12, align: {expr: "datum.fid <= 4 ? 'left' : " + "datum.fid <= " + (n - 4) + " ? 'center' : 'right'"} }) .transform(vl.filter(hover)) .encode( vl.color().legend(null), vl.text({format: " ,.0f"}).fieldQ(curpresta) ), ) .width(cwidth - 30) .autosize({type: "fit-x", contains: "padding"}) .height(300) .title({text:'Essai', subtitle:'Affichage de la valeur au survol', anchor:'start'}) .render({renderer: 'svg'}) }2b:[{"id":"b2c9ba9a3f70c5d35801f968527715177301b4c2987e3e880b152a76c971032beb371253a37d4e9bcaf81aa59e320c15cf2c60cab5723cbedbe3d14378036037","href":"https://static.observableusercontent.com/files/b2c9ba9a3f70c5d35801f968527715177301b4c2987e3e880b152a76c971032beb371253a37d4e9bcaf81aa59e320c15cf2c60cab5723cbedbe3d14378036037","name":"drees_rsa.png","type":"image/png","lastModified":1608808279107.079,"size":5520}] 25:{"id":"546c45301e129398","slug":"suivi-mensuel-des-prestations-de-solidarite-pendant-la-cri","title":"Suivi mensuel des prestations de solidarité pendant la crise sanitaire","thumbnail":"d63a077ad04fa0c318be961748991ff7ad9236264c893cad378ff9944a885122","access_level":"public","create_time":"$D2020-12-23T14:37:09.408Z","update_time":"$D2021-12-15T08:47:46.432Z","view_time":null,"first_public_time":"$D2020-12-24T14:17:10.128Z","has_unpublished_edits":null,"fork":false,"stars":2,"starred":false,"owner":{"type":"team","id":"cd09b02ddc62abda","login":"ericmauviere","name":"Éric Mauvière","avatar_url":"https://avatars-next.observableusercontent.com/a35a91b5ca23c5dd084a628d6c4898fb6eefb2ede192b1801b0efae516f49017","tier":"starter_2024","bio":"Statistician, map-maker, dataviz consultant and trainer, Observable ambassador & R instructor. I enjoy explaining things i love in detail, the most clearly i can, and building live analytical opendataflows. \n@ericmauviere"},"version":1293,"publish_version":1293,"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,"bio":"Dataviz, Semiology of graphics, Datastorytelling, DuckDB\n@ericmauviere"}],"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":0,"collection":null,"type":"notebook","body":{"titling":"auto","title":"Suivi mensuel des prestations de solidarité pendant la crise sanitaire","cells":[{"id":0,"value":"$27","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":78,"value":"viewof curpresta = {\n\n return select({\n title : \"Choix d'une prestation de solidarité\",\n options: prestas,\n value: 'rsa'\n})\n \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":262,"value":"viewof curreg = {\n const dl = reg_l\n return select({\n title : \"Choix de la région (ou France entière)\",\n //description: 'mois',\n options: dl,\n value: reg0 \n})\n \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":60,"value":"viewof curdep = {\n let dl ;\n if (curreg == 'FR') {\n dl = [{value:'FR', label:'France'}] ;\n \n } else {\n const codreg = curreg.substring(1) ;\n const depreg = n_dep.params({codreg})\n .filter(d => d.reg == codreg)\n .derive({libgeo: d => d.codgeo + ' - ' + d.libgeo})\n .select({codgeo:'value', libgeo:'label'}) ;\n\n dl = n_reg.params({codreg})\n .filter(d => d.codgeo == codreg)\n .derive({codgeo: d => 'R' + d.codgeo})\n .select({codgeo:'value', libgeo:'label'})\n .concat(depreg).objects() ;\n }\n \n return select({\n title : \"Région entière ou choix d'un département\",\n options: dl,\n value: dep0\n })\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":166,"value":"viewof typrep = {\n //curdep ;\n //curreg ;\n \n const r = radio({\n title: 'Évolution mensuelle avec choix possible de territoires de comparaison englobants',\n description: null,\n options: [\n { label: \"en valeurs absolues\", value: 'abs' },\n { label: \"avec échelle ajustée\", value: 'adj' },\n { label: 'comparaisons (base identique pour le mois en survol)', value: 'std' }\n ],\n value: typrep0\n })\n \n const lastradio = r.getElementsByTagName('input')[2] ;\n lastradio.disabled = curreg == 'FR';\n \n return r ;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":30,"value":"$28","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":431,"value":"buttons = html`\n${DOM.download(\n new Blob([d3.csvFormat(data.objects())], { type: \"text/csv\" }),\n \"data.csv\",\n \"Export CSV des données\"\n)}\n${DOM.download(await serialize(chart), \"opendata\", \"Export SVG\")}\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1143,"value":"note_alg = md `*Aides au logement : depuis le 1er janvier 2021 elles sont calculées sur la base des ressources perçues au cours des 12 derniers mois et non plus sur celles de l’année n-2. \nLes droits sont recalculés tous les trois mois (contre tous les ans auparavant). Les comparaisons des effectifs avant et après la réforme restent possibles mais doivent être faites avec précaution.*\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":514,"value":"note = md `_Note : les effectifs des derniers mois relèvent d'estimations faites par la DREES (pointillés)._\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":739,"value":"md ` ### Appel direct paramétré du graphique\n\nLes liens ci-dessous exposent 3 exemples d'appel de ce tableau de bord graphique avec les paramètres dep (code département), reg (code région ou FR pour France) et typrep (type de représentation : abs, adj ou std) :\n\n- [typrep=std®=32 (région Hauts-de-France comparée avec France)](https://observablehq.com/@ericmauviere/suivi-mensuel-des-prestations-de-solidarite-pendant-la-cri?typrep=std®=32#curpresta)\n- [typrep=abs&dep=95 (Val d'Oise en valeurs absolues)](https://observablehq.com/@ericmauviere/suivi-mensuel-des-prestations-de-solidarite-pendant-la-cri?typrep=abs&dep=95#curpresta)\n- [typrep=adj®=FR (France entière en valeurs absolues et échelle zoomée)](https://observablehq.com/@ericmauviere/suivi-mensuel-des-prestations-de-solidarite-pendant-la-cri?cell=viewof+chart®=FR&typrep=adj#curpresta)\n\nPour insérer le graphique seul dans une iframe, choisissez 'embed' dans le menu contextuel à gauche de la cellule du graphique.\n\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":489,"value":"md `## Méthodologie \n\nLes **séries mensuelles détaillées** (janvier 2017 => mars 2021) sont accessibles [ici](https://data.drees.solidarites-sante.gouv.fr/explore/dataset/donnees-mensuelles-sur-les-prestations-de-solidarite/information/). Il s'agit d'un tableau excel.\n\nEn tout état de cause, le format proposé **requiert quelques transformations** avant d'aboutir à des jeux de données aisément manipulables. Comme on peut le voir, la bonne appréhension des départements, des mois, voire la lecture des chiffres avec **\\\\*** n'est possible qu'**avec un peu de programmation** (R par exemple).\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":440,"value":"md `${await FileAttachment( \"drees_rsa.png\" ).image()}`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":444,"value":"md `Aussi ai-je converti tout cela en fichiers CSV structurés, que l'on peut trouver sur [cette page dédiée chez data.gouv.fr](https://www.data.gouv.fr/fr/datasets/suivi-mensuel-des-prestations-de-solidarite-pendant-la-crise-sanitaire-source-drees/)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":445,"value":"md `### Programme R\n\nCe code lit directement le fichier excel sur le site de la Drees, et génére les 6 fichiers CSV, ensuite placés sur data.gouv.fr.\n\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":663,"value":"$29","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":188,"value":"md `### Fonctions et variables\n\nTable constituée après chargement du fichier CSV et appariement avec les référentiels (départemental et régional) :`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":9,"value":"data_dep = {\n const url_csv = 'https://static.data.gouv.fr/resources/suivi-mensuel-des-prestations-de-solidarite-pendant-la-crise-sanitaire-source-drees/' \n + prestas.filter(d => d.value == curpresta)[0].idurl + '/tbl-' + curpresta + '.csv' ;\nconsole.log(url_csv)\n const f = fetch(url_csv)\n .then(response => response.text())\n .then(d => {\n let t = aq.fromCSV(d, {autoType: true, parse: {dep:String}} )\n .join_left(n_dep.select({codgeo:'dep'}, 'libgeo', 'reg'))\n .derive({libgeo: d => !d.libgeo ? 'France' : d.libgeo})\n .select('mois', {dep:'codgeo'}, 'libgeo', 'estim_' + curpresta, curpresta, 'reg')\n \n return t ;\n } \n ) ;\n \n return f ;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":311,"value":"data_reg = {\n let estim_var = 'estim_' + curpresta ;\n \n return data_dep.params({curpresta, estim_var})\n .filter(d => d.codgeo != 'FR')\n .groupby('mois','reg')\n .rollup({[curpresta]: op.sum(curpresta), [estim_var]: op.max(estim_var)})\n .join_left(n_reg, ['reg','codgeo'])\n .derive({codgeo : d => 'R' + d.reg})\n .select('mois', 'codgeo', 'libgeo', estim_var, curpresta) ;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":332,"value":"data = data_dep.select(aq.not('reg')).concat(data_reg)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":76,"value":"prestas = [\n {\n value: \"rsa\",\n label: \"RSA (revenu de solidarité active)\",\n idurl: \"20211215-094243\"\n },\n {\n value: \"ass\",\n label: \"ASS (allocation de solidarité spécifique)\",\n idurl: \"20211215-094603\"\n },\n {\n value: \"aah\",\n label: \"AAH (allocation aux adultes handicapés)\",\n idurl: \"20211215-094716\"\n },\n { value: \"pac\", label: \"Prime d'activité\", idurl: \"20211215-094429\" },\n { value: \"alg\", label: \"Aides au logement\", idurl: \"20211215-094644\" },\n { value: \"gjs\", label: \"Garantie jeunes (stocks)\", idurl: \"20211215-094526\" }\n]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":7,"value":"n_dep = aq.fromCSV(await fetch_txtfile_from_zip(\"https://static.data.gouv.fr/resources/contours-des-communes-de-france-simplifie-avec-regions-et-departement-doutre-mer-rapproches/20201215-192125/zonages-fra-2020.zip\", \"dept2020.csv\"), {delimiter: \";\", autoType: false})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":64,"value":"dep_l = [{value:'FR', label:'France', libgeo: 'France'}].concat(n_dep.objects().map(d=> { return {value:d.codgeo, label:d.codgeo + ' - ' + d.libgeo, libgeo: d.libgeo}}))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":264,"value":"reg_l = [{value:'FR', label:'France', libgeo: 'France'}].concat(n_reg.objects().map(d=> { return {value:'R' + d.codgeo, label:d.codgeo + ' - ' + d.libgeo, libgeo: d.libgeo}}))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":266,"value":"n_reg = aq.fromCSV(await fetch_txtfile_from_zip(\"https://static.data.gouv.fr/resources/contours-des-communes-de-france-simplifie-avec-regions-et-departement-doutre-mer-rapproches/20201215-192125/zonages-fra-2020.zip\", \"reg2020.csv\"), {delimiter:\";\", autoType:false})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":721,"value":"urlParams = new URLSearchParams(html``.search); ","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":368,"value":"curlibdep = {\n let a = dep_l.filter(d => d.value == curdep)\n \n let b = reg_l.filter(d => d.value == curdep)\n return a.length == 1 ? a[0].libgeo : b[0].libgeo ;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":375,"value":"curlibreg = {\n let b = reg_l.filter(d => d.value == curreg)\n return b[0].libgeo ;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":722,"value":"reg0 = urlParams.get('reg') ? 'R' + urlParams.get('reg') : dep0 ? ('R' + n_dep.params({dep0}).filter(d => d.codgeo == dep0).reify().get('reg',0)) : 'R11'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":725,"value":"dep0 = urlParams.get('dep') || '95'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":729,"value":"typrep0 = urlParams.get('typrep') || 'std'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":836,"value":"cwidth = Math.min(width, 900) ;","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":567,"value":"md `### Librairies`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":4,"value":"d3 = require(\"d3@6\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":5,"value":"import {normalize_table_names, normalize_column_name, rename_columns, rows_update, timeIt, aq, op, fetch_txtfile_from_zip, table_from_csvurl, getBrowserLocale, locale, titleCase} from '@ericmauviere/arquero-table-utils'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":61,"value":"import {radio, select} from \"@jashkenas/inputs\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1095,"value":"import {rasterize, serialize} from \"@mbostock/saving-svg\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":31,"value":"import { vl } from '@vega/vega-lite-api'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1027,"value":"md `Essai de survol` ","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":975,"value":"$2a","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1099,"value":"buttons2 = html`\n${DOM.download(new Blob([d3.csvFormat(data.objects())], {type: \"text/csv\"}), \"data.csv\", \"Export CSV des données\")}\n${DOM.download(await serialize(chart2), \"opendata\", \"Export SVG\")}\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":801,"value":"style = html`

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W2b","stdlib":"1"}} 26:"$25:body"