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.
@ericmauviere
Public
Edited
Dec 15, 2022
1 fork
3 stars
{
cls0
return Inputs.table(idbanks_dt.derive({INDICATEUR: d => op.split(d.list_mod, '.')
[op.indexof(op.split(d.list_var, '.'), 'INDICATEUR')]})
.derive({spread: d => op.split(d.list_mod, '.')})
.spread('spread')
.groupby('famille','INDICATEUR', 'spread_1').count()
.lookup(codelist_map.get('CL_INDICATEUR'), ['INDICATEUR','id'], 'lib')
.select('famille', {'spread_1':'FREQ'}, {'count':'SERIES'},{'lib':'INDICATEUR'})
.rename({famille:'DATASET'})
.orderby(0,1)
, {locale: 'fr', layout: 'auto'})
}
dataflows_tb.view()
dataflows_tb = {
const url = "https://bdm.insee.fr/series/sdmx/dataflow"//"https://api.insee.fr/series/BDM/V1/dataflow/FR1/all"
let i = 0, dtfs
// 1 - sécurisation en cas de réponse dilatoire du serveur
while (i++ <= 5) {
dtfs = await fetch(url)//, get_fetch_options())
.then( response => {
if (response.status == 429) { return {error: response.status, msg: response.text() } }
return response.text() //we only get here if there is no error
})
if (typeof dtfs == 'string') i = 10
}
// 2 - conversion de la réponse XML en graphe JavaScript
let nodes = d3.hierarchy((new DOMParser).parseFromString(dtfs, "application/xml"))
let a = nodes.descendants().filter(d => d.data.tagName == "str:Dataflow")
.map(d => {
let lib = d.children.filter(e => e.data.tagName == "com:Name"
&& e.data.getAttribute('xml:lang') == 'fr')
let serie = d.descendants().filter(e => e.data.tagName == "com:AnnotationText"
&& e.data.getAttribute('xml:lang') == 'fr')
let dst = d.descendants().filter(e => e.data.tagName == "Ref")
let url = d.descendants().filter(e => e.data.tagName == "com:AnnotationURL")
return { id_dtf: d.data.getAttribute('id'),
lib_dtf: lib.length == 1 ? lib[0].data.innerHTML : '',
series: serie.length >= 1 ? +serie[0].data.innerHTML.match(/(?<x>\d+)/, 'x')[0] : '',
id_dst: dst.length >= 1 ? dst[0].data.getAttribute('id') : null,
url : url.length >= 1 ? url[0].data.innerHTML : null
}
})
// 3 - exclusion de datasets comportant surtout des séries arrêtées
return aq.from(a).orderby(aq.desc('series'))
.filter(d => d.id_dtf != 'SERIES_BDM' && d.lib_dtf != '')
.filter(d => !op.includes(d.id_dtf, 'CNA-2010'))
.filter(d => !op.includes(d.id_dtf, 'CNT-2010'))
.filter(d => !op.includes(d.id_dtf, 'CONSO-MENAGES-2010'))
.filter(d => !op.includes(d.id_dtf, 'ICA-2005'))
.filter(d => !op.includes(d.id_dtf, 'ICA-2010'))
.filter(d => !op.includes(d.id_dtf, 'ICT-2012'))
.filter(d => !op.includes(d.id_dtf, 'IP-PROD-CONS-N-HAB-2010'))
.filter(d => !op.includes(d.id_dtf, 'IC-PROD-CONS-2010'))
.filter(d => !op.includes(d.id_dtf, 'IPC-19'))
.filter(d => d.id_dtf !== 'IPAGRI')
.filter(d => !op.includes(d.id_dtf, 'IPCH-200'))
.filter(d => !op.includes(d.id_dtf, 'IPEA-2010'))
.filter(d => !op.includes(d.id_dtf, 'IPGD-19'))
.filter(d => !op.includes(d.id_dtf, 'IPI-19') && !op.includes(d.id_dtf, 'IPI-200')
&& !op.includes(d.id_dtf, 'IPI-2010'))
.filter(d => d.id_dtf !== 'IPLA-IPLNA-2010')
.filter(d => !op.includes(d.id_dtf, 'IPPI-200') && !op.includes(d.id_dtf, 'IPPI-2010'))
.filter(d => d.id_dtf !== 'IPPS-2010')
.filter(d => d.id_dtf !== 'IPPMP')
.filter(d => d.id_dtf !== 'IPPS-2015')
.filter(d => d.id_dtf !== 'SALAIRES-ACEMO')
.filter(d => d.id_dtf !== 'SERIES-LOYERS')
.filter(d => d.id_dtf !== 'DETTE-TRIM-APU')
.filter(d => d.id_dtf !== 'INDEX-BT-TP-DIV-ANCIENNES-BASES')
.filter(d => d.id_dtf !== 'TCRED-ECONOMIE-PIB-REG-2014')
}
get_fetch_options()
get_dst_def = async function(id_dtf) {
let id_dst = dtf_to_dst.get(id_dtf)
const url = "https://api.insee.fr/series/BDM/V1/datastructure/FR1/" + id_dst
// 1 - chargement et sécurisation
let i = 0, xml_dst_bdm
while (i++ <= 5) {
xml_dst_bdm = await fetch(url, get_fetch_options())
.then( response => {
if (response.status == 429) { return {error: response.status, msg: response.text() } }
return response.text() //we only get here if there is no error
})
if (typeof xml_dst_bdm == 'string') i = 10
}
// 2 - lecture de la réponse XML
let nodes = d3.hierarchy((new DOMParser).parseFromString(xml_dst_bdm, "application/xml"))
let codelists2 = nodes.descendants()
.filter(d => d.data.tagName == "Ref" && d.data.getAttribute('class') == 'Codelist')
let codelists = codelists2.map(d => d.data.getAttribute('id'))
let indic_to_codelist = new Map( codelists2
.map(d => [d.parent.parent.parent.data.getAttribute('id'), d.data.getAttribute('id')]) )
let dimensions = nodes.descendants().filter(d => d.data.tagName == "str:Dimension" && d.data.getAttribute('id') )
.map(d => d.data.getAttribute('id'))
let attributes = nodes.descendants().filter(d => d.data.tagName == "str:Attribute" )
.map(d => d.data.getAttribute('id'))
// 3 - assemblage des divers concepts dans un objet JS
return {id_dst, codelists, dimensions, attributes, indic_to_codelist}
}
data_id_bank = {
let idbank = selected_idbank
if (!idbank) return {}
const url = "https://api.insee.fr/series/BDM/V1/data/SERIES_BDM/"
+ (Array.isArray(idbank) ? idbank.join('%2B') : idbank)
// 1 - métadonnées relatives aux idbanks passés en entrée
let dt = idbanks_dt.params({idbank})
.derive({idbank: d => op.padstart(d.idbank, 9, '0')})
.filter(d => op.includes(idbank, d.idbank))
let dst_def = await get_dst_def(dt.get('famille'))
// 2 - accès aux dimensions du dataset contenant ces idbanks
let dims = dst_def.dimensions, // safe
list_var = dt.get('list_var').split('.') // unsafe, fichiers csv idbanks
dims = dims.slice(0, list_var.length) // dims a pu s'étendre...
let dt2 = dt.derive({spread: d => op.split(d.list_mod, '.')})
.spread('spread', { as: dims })
// 3 - recherche de la dimension selon laquelle on a sélectionné des idbanks multiples (ex : tranches d'âge)
let spread_dimension = Array.isArray(idbank) && idbank.length > 1 ?
dims.filter(d => dt2.params({d}).dedupe(d).numRows() > 1)[0] : null
// 4 - chargement de la nomenclature (codelist) associée à cette dimension
if (spread_dimension)
await get_codelist(dst_def.indic_to_codelist.get(spread_dimension))
let idbank_map = spread_dimension ?
dt2.params({spread_dimension})
.rollup({map: op.map_agg('idbank', spread_dimension)}).get('map')
: null
return {tb: await get_bdm_data(url, false, spread_dimension, idbank_map), spread_dimension, idbank_map, dst_def}
}
get_bdm_data = async function(url_sdmx, multi_geo, spread_dimension, idbank_map) {
// 1 - acquisition des données, gestion des erreurs potentielles ou du quota de 30 requêtes par minute
let i = 0, xml_data_bdm
while (i++ <= 5) {
xml_data_bdm = await fetch(url_sdmx, get_fetch_options())
.then( response => {
if (response.status == 429) { return {error: response.status, msg: response.text() } }
return response.text() //we only get here if there is no error
})
if (typeof xml_data_bdm == 'string') i = 10
}
// 2 - analyse de la réponse XML
let xml_tree = (new DOMParser).parseFromString(xml_data_bdm, "application/xml")
let nodes = d3.hierarchy(xml_tree)
let attrs_err = nodes.descendants().filter(d => d.data.tagName == "mes:Error")
if (attrs_err.length == 1) {
let errMsg = nodes.descendants().filter(d => d.data.tagName == "com:Text")
console.log('error', errMsg[0].data.innerHTML)
return {tb: aq.table({errMsg: [errMsg[0].data.innerHTML]}), errMsg:"Aucun résultat ne correspond à cette requête"}
}
// 3 - lecture des métadonnées générales (titre, unité...)
let series = nodes.descendants().filter(d => d.data.tagName == "Series"),
meta_headers = chart_headers(series, !!spread_dimension)
// 4 - lecture des données de base (TIME_PERIOD et OBS_VALUE) enrichies de quelques attributs
let a = ((spread_dimension || multi_geo) ? nodes.descendants() : series[series.length-1].children)
.filter(d => d.data.tagName == "Obs")
.map(d=> { return {TIME_PERIOD:d.data.getAttribute("TIME_PERIOD"),
OBS_VALUE: +d.data.getAttribute("OBS_VALUE"),
REF_AREA: d.parent.data.getAttribute('REF_AREA'),
IDBANK: d.parent.data.getAttribute('IDBANK'),
DATASET: idbank_to_dts_map.get(d.parent.data.getAttribute('IDBANK')),
[spread_dimension]: !spread_dimension ?
null : d.parent.data.getAttribute(spread_dimension)
|| idbank_map.get(d.parent.data.getAttribute('IDBANK')),
TITLE: d.parent.data.getAttribute("TITLE_FR"),
UNIT_MULT: Math.pow(10, d.parent.data.getAttribute("UNIT_MULT"))
}})
// 5 - résultat final : table de données et métadonnées
return {tb:aq.from(a).select(aq.not('null')).orderby(aq.desc('TIME_PERIOD'), 'REF_AREA')
.filter(d => +d.OBS_VALUE == d.OBS_VALUE), ...meta_headers}
}
get_bdm_chart = async function(data, spread_dimension, dst_def) {
vl.vega.formatLocale(locale); // prise en compte du formatage français des nombres (axe y)
vl.vega.timeFormatLocale(locale);
let idbank_row =
idbanks_list[viewof selected_idbank.querySelector('select').selectedIndex];
// 1 - gestion du zoom automatique sur l'axe y en fonction de idbank_row.zoom == 1
let zoom_ckb = viewof chart_zoom;
if (idbank_row.zoom && !chart_zoom[0]) {
zoom_ckb.value = [1];
zoom_ckb.dispatchEvent(new Event("input"));
zoom_ckb.querySelector('input').setAttribute('disabled', 'disabled');
return;
}
if (!idbank_row.zoom)
zoom_ckb.querySelector('input').removeAttribute('disabled');
let yscale = chart_zoom[0] == 1 ? { zero: false } : {};
// 2 - gestion de l'éclatement en plusieurs modalités (ex différentes courbes par tranche d'âge)
let cl_tb,
cl_eclat_map,
fmt_eclat,
lib_eclat,
isOrigin = vl.selectPoint('isOrigin');
if (spread_dimension) {
cl_tb = codelist_map.get(dst_def.indic_to_codelist.get(spread_dimension));
(fmt_eclat = id =>
cl_tb
.params({ id })
.filter(d => d.id == id)
.get('lib')
.split(', ')),
(lib_eclat = codelist_lib_map
.get(dst_def.indic_to_codelist.get(spread_dimension))
.split(' ')
.filter(d => d.trim() != ''));
if (lib_eclat.length > 2)
lib_eclat = [lib_eclat.slice(0, lib_eclat.length / 2).join(' ')].concat(
lib_eclat.slice(lib_eclat.length / 2).join(' ')
);
isOrigin = vl
.selectPoint('isOrigin')
.fields(spread_dimension)
.bind('legend');
}
if (fmt_eclat) vl.vega.expressionFunction('fmt_eclat', fmt_eclat);
// 3 - gestion adaptative du titre et de la source
let title = data.extrait_title.split('-').filter(d => d.trim() != '');
if (title.length > 2)
title = [title.slice(0, title.length / 2).join(' - ')].concat(
title.slice(title.length / 2).join(' - ')
);
let source = ['Source : Insee BDM', data.unite_str.substring(2)];
if (source[1].trim() == '') source.pop();
if (width > 600) source = source.join(' - ');
// 4 - gestion dynamique des libellés de l'axe temporel
aq.addFunction('convert_to_date', convert_to_date, { override: true });
let dt = data.tb.derive({ t: d => op.convert_to_date(d.TIME_PERIOD) }); // t est un vrai champ de type Date
let plage_an = d3.extent(dt.array('t'), d => d.getFullYear()),
delta_an = plage_an[1] - plage_an[0];
// libelle l'axe X avec l'année ou le mois de changement de semestre (ou de trimestre) : 2020 juin(5)
let label_fn = function(v) {
if (v.getMonth() == 11) return v.getFullYear() + 1;
if ((v.getMonth() + 1) % 6 == 0 && delta_an <= 15 && width > 500)
return v.toLocaleString('fr', { month: 'short' });
if ((v.getMonth() + 1) % 3 == 0 && delta_an <= 8 && width > 500)
return v.toLocaleString('fr', { month: 'short' });
return '';
};
vl.vega.expressionFunction('periodicite', () =>
delta_an > 15 || width < 500 ? 12 : 3
);
vl.vega.expressionFunction('label_fn', label_fn);
let period = 'infra_an';
let x = vl
.x()
.fieldT('t')
.axis({
labelAngle: 0,
labelOverlap: true,
labelFlush: false,
tickCount: { interval: "month", step: 1 },
gridColor: {
expr:
"timeFormat(datum.value, '%m') % periodicite() == 0 ? '#eee' : '#fff'"
},
tickOpacity: { expr: "label_fn(datum.value) != '' ? 1 : 0" },
gridDash: { expr: "timeFormat(datum.value, '%m') == '12' ? [] : [2,2]" },
labelExpr: "label_fn(datum.value)"
});
if (parseInt(dt.get('TIME_PERIOD')) == dt.get('TIME_PERIOD')) {
// périodicité annuelle
period = 'an';
x = vl
.x()
.fieldT('t')
.axis({
labelAngle: 0,
labelOverlap: true,
grid: true,
gridColor: {
expr: "timeFormat(datum.value, '%m') == '01' ? '#eee' : null"
},
tickColor: {
expr: "timeFormat(datum.value, '%m') == '01' ? '#ccc' : null"
},
labelColor: {
expr: "timeFormat(datum.value, '%m') == '01' ? 'black' : null"
}
});
}
// 5 - gestion de la couleur des courbes, de la légende ordonnée et cliquable
let c = vl.color();
if (spread_dimension) {
let cx = vl
.color()
.fieldN(spread_dimension)
.scale({
range: [
'#f4b84d',
'#1e9b9e',
'firebrick',
'steelblue',
'#AD7AA1',
'hotpink',
'olivedrab'
]
})
.legend({
title: lib_eclat,
titleAnchor: 'middle',
symbolStrokeWidth: 3,
symbolSize: 400,
labelLimit: width < 500 ? 150 : 300,
orient: width < 500 ? 'bottom' : 'right',
columns: width < 500 ? 2 : 1,
labelFontSize: 11,
labelExpr: "fmt_eclat(datum.value)"
});
if (idbank_row.sort) cx = cx.sort("-y");
c = c.if(isOrigin, cx).value('#ccc');
}
// 5 - ajout d'une droite horizontale de référence (ex : indice base 100)
let y0 = idbank_row.y0;
let y_rule = idbank_row.hasOwnProperty('y0')
? vl.markRule({ size: 1, color: '#aaa' }).encode(vl.y().datum(y0))
: null;
// 6 - assemblage des couches du graphique : courbes d'évolution et droite de référence
let layers = [
vl
.markLine({
point:
data.tb.dedupe('TIME_PERIOD').numRows() < 20 &&
data.tb.dedupe('TIME_PERIOD').numRows() > 10,
interpolate: 'monotone',
size: 2,
color: '#1e9b9e'
})
.params(isOrigin) // add selections to plot
.encode(
x.title(null),
vl
.y()
.fieldQ('OBS_VALUE')
.title(null)
.scale(yscale)
.axis({ grid: true }),
c,
vl
.opacity()
.if(isOrigin, vl.value(1.0))
.value(0.4),
vl.tooltip([
vl
.tooltip()
.fieldT('t')
.title('période ')
.format(period == 'an' ? '%Y' : '%m-%Y'),
vl
.tooltip()
.fieldQ('OBS_VALUE')
.format(',')
.title('valeur ')
])
)
];
if (y_rule) layers.unshift(y_rule);
// 7 - rendu final
return vl
.layer(layers)
.data(dt)
.title({
anchor: width < 500 ? 'start' : 'middle',
subtitlePadding: 10,
text: title,
subtitle: source,
limit: width
})
.width(width)
.autosize({
type: "fit",
contains: "padding"
})
.height(350)
.render({ renderer: 'svg' });
}
chart_headers = function(series, has_spread_dimension) {
let last_serie = series[series.length-1]
let extrait_title = last_serie.data.getAttribute("TITLE_FR")
let extrait_last_update = new Date(last_serie.data.getAttribute("LAST_UPDATE"))
let extrait_unite = last_serie.data.getAttribute("UNIT_MEASURE")
let extrait_unite_mult = Math.pow(10, +last_serie.data.getAttribute("UNIT_MULT")).toLocaleString()
if (extrait_unite_mult == 1)
extrait_unite_mult = ''
let cun = codelist_map.get('CL_UNITE')
extrait_unite = cun.params({extrait_unite}).filter('d.id==extrait_unite').get('lib')
let unite_str = (' - unité : ' + extrait_unite_mult + ' ' + extrait_unite).toLowerCase()
if (['individus', 'sans objet', 'unités', 'nombre'].includes(extrait_unite.toLowerCase().trim()) )
unite_str = extrait_unite_mult == '' ? '' : (' - unité : ' + extrait_unite_mult )
if (has_spread_dimension && series.length > 1) {
let extrait_title0 = series[0].data.getAttribute("TITLE_FR"),
a0 = extrait_title0.split(' '), a1 = extrait_title.split(' '), a = []
a0.forEach((d,i)=> {
if (d == a1[i]) a.push(d)
})
if (a.join('') == "--") {
a0 = extrait_title0.split('-').reverse(),
a1 = extrait_title.split('-').reverse(), a = []
a0.forEach((d,i)=> {
if (d == a1[i]) a.push(d)
})
a = a.reverse().join('-').split(' ')
}
extrait_title = a.join(' ').replace(/\s+/g, ' ')
.replace('Ensemble des ans', '')
.replace('Ensemble des', '')
.replace('Ensemble de', '')
.replace('de ans de', '')
.replace('à ans', '')
.replace('ans', '')
.replace('d la', 'dans la')
.replace('d le', 'dans le')
.replace('des femmes de', '')
.replace('de à ans', "par classe d'âge")
.replace('- -', '-')
}
return {extrait_title, extrait_last_update, extrait_unite, extrait_unite_mult, unite_str}
}
One platform to build and deploy the best data apps
Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.