2b:null 2c:["$","title",null,{"children":"Consultores de la Junta 2024 | José M. Zavala González | Observable"}] 31:T896,grafica_totales = { let thedata = aq.from(resumen_actualizacion).orderby(aq.desc('total')).objects() let theplot = Plot.plot({ // width: 800, caption: htl.html`Fuentes: Junta de Supervisión Fiscal, Informes Anuales, 2017, 2018, 2019, 2020, 2021, 2022,2023 y 2024; In re Fin. Oversight & Mgmt. Bd. for P.R., Case No. 17-3283 y análisis de Espacios Abiertos.`, marginLeft: 70, marginTop: 30, marginBottom: 55, x: { // domain: [0,1,2,3], domain: ['tribunal', 'junta', 'examinador'] }, marks: [ Plot.axisX({ // label: 'Dependientes cualificados', label: null, tickFormat: d => categoria_distribucion_labels[d], tickSize: 0, fontFamily: 'jaf-bernino-sans', fontSize: 14, }), Plot.axisY({ label: 'Total gastado en consultores', fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, // tickFormat: "$,.0f", tickFormat: d => d3.format('$,.0f')(d/1e6) + 'M', fontSize: 14, }), Plot.barY(thedata, { // filter: (d => d['Estado Civil'] === estado_civil_mutable), x: 'categoria_distribucion', y: 'total', fill: EA_COLORES_MAP['INDIGO'], }), Plot.text(thedata, { filter: d => d.total > 100e6, x: 'categoria_distribucion', y: 'total', text: d => d3.format('$,.0f')(d['total']), fontSize: 16, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', lineAnchor: 'top', dy: 7, fill: 'white', // textAnchor: 'end', dx: -5, }), Plot.text(thedata, { filter: d => d.total <= 100e6, x: 'categoria_distribucion', y: 'total', text: d => d3.format('$,.0f')(d['total']), fontSize: 16, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', lineAnchor: 'bottom', dy: -7, fill: 'black', // textAnchor: 'end', dx: -5, }), ] }) return theplot }32:T8ad,grafica_totales_en = { let thedata = aq.from(resumen_actualizacion).orderby(aq.desc('total')).objects() let theplot = Plot.plot({ // width: 800, caption: htl.html`Sources: Fiscal Oversight and Management Board, Annual Reports, 2017, 2018, 2019, 2020, 2021, 2022,2023 y 2024; In re Fin. Oversight & Mgmt. Bd. for P.R., Case No. 17-3283 and analysis by Espacios Abiertos.`, marginLeft: 70, marginTop: 30, marginBottom: 55, x: { // domain: [0,1,2,3], domain: ['tribunal', 'junta', 'examinador'] }, marks: [ Plot.axisX({ // label: 'Dependientes cualificados', label: null, tickFormat: d => categoria_distribucion_labels_en[d], tickSize: 0, fontFamily: 'jaf-bernino-sans', fontSize: 14, }), Plot.axisY({ label: 'Total spending on consulting services', fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, // tickFormat: "$,.0f", tickFormat: d => d3.format('$,.0f')(d/1e6) + 'M', fontSize: 14, }), Plot.barY(thedata, { // filter: (d => d['Estado Civil'] === estado_civil_mutable), x: 'categoria_distribucion', y: 'total', fill: EA_COLORES_MAP['INDIGO'], }), Plot.text(thedata, { filter: d => d.total > 100e6, x: 'categoria_distribucion', y: 'total', text: d => d3.format('$,.0f')(d['total']), fontSize: 16, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', lineAnchor: 'top', dy: 7, fill: 'white', // textAnchor: 'end', dx: -5, }), Plot.text(thedata, { filter: d => d.total <= 100e6, x: 'categoria_distribucion', y: 'total', text: d => d3.format('$,.0f')(d['total']), fontSize: 16, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', lineAnchor: 'bottom', dy: -7, fill: 'black', // textAnchor: 'end', dx: -5, }), ] }) return theplot }33:Ta6a,grafica_temporal = { let thedata = [...data_by_fecha_orden_pago_with_date, ...examiner_data_by_fecha_orden_pago_with_date, ...junta_data_by_fecha_orden_pago_with_date].filter(d => d.fecha_orden_pago_string !== null) return Plot.plot({ style: 'overflow: visible', caption: htl.html`Fuentes: Junta de Supervisión Fiscal, Informes Anuales, 2017, 2018, 2019, 2020, 2021, 2022,2023 y 2024; In re Fin. Oversight & Mgmt. Bd. for P.R., Case No. 17-3283 y análisis de Espacios Abiertos.`, marginLeft: 60, color: { legend: true, domain: ['tribunal', 'junta', 'examinador'], range: [EA_COLORES_MAP['INDIGO'], EA_COLORES_MAP['RED'], EA_COLORES_MAP['GREEN']], tickFormat: d => categoria_distribucion_labels[d] ?? d, }, x: { // domain: [2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024,].map(d => new Date(`${d}-01-01`)) domain: [new Date("2017-01-01"), new Date("2024-01-01")], }, marks: [ Plot.ruleY([0]), Plot.axisX({ label: null, // label: 'Total acumulado ordenado a pagar', // tickFormat: d => d3.format('$,.0f')(d / 1e6)+'M', fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontSize: 14, // ticks: false, tickSize: 0, }), Plot.axisY({ label: 'Total gastado en consultores', tickFormat: d => d3.format('$,.0f')(d / 1e6)+'M', fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontSize: 14, // ticks: false, tickSize: 0, }), Plot.lineY(thedata, { x: "fecha_orden_pago", y: "acumulado", stroke: 'categoria_distribucion', // tip: true, marker: 'dot', strokeWidth: 2, }), Plot.text(thedata, Plot.selectLast({ x: "fecha_orden_pago", y: "acumulado", z: 'categoria_distribucion', text: d => d3.format('$,.0f')(d.acumulado), fontSize: 16, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', lineAnchor: 'bottom', dy: -10, })), Plot.tip(thedata, Plot.pointer({ x: d => d['fecha_orden_pago'], y: 'acumulado', z: 'categoria_distribucion', fontFamily: 'jaf-bernino-sans', fontSize: 16, title: d => `${formatDateLong(d.fecha_orden_pago, 'es-ES')}\nAcumulado: ${d3.format('$,.0f')(d.acumulado)}` + ( d.categoria_distribucion !== 'tribunal' ? '' : `\nDocket: ${d.num_orden_list.join(', ')}` ), })), ] }) }34:Ta83,grafica_temporal_en = { let thedata = [...data_by_fecha_orden_pago_with_date, ...examiner_data_by_fecha_orden_pago_with_date, ...junta_data_by_fecha_orden_pago_with_date].filter(d => d.fecha_orden_pago_string !== null) return Plot.plot({ style: 'overflow: visible', caption: htl.html`Sources: Fiscal Oversight and Management Board, Annual Reports, 2017, 2018, 2019, 2020, 2021, 2022,2023 y 2024; In re Fin. Oversight & Mgmt. Bd. for P.R., Case No. 17-3283 and analysis by Espacios Abiertos.`, marginLeft: 60, color: { legend: true, domain: ['tribunal', 'junta', 'examinador'], range: [EA_COLORES_MAP['INDIGO'], EA_COLORES_MAP['RED'], EA_COLORES_MAP['GREEN']], tickFormat: d => categoria_distribucion_labels_en[d] ?? d, }, x: { // domain: [2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024,].map(d => new Date(`${d}-01-01`)) domain: [new Date("2017-01-01"), new Date("2024-01-01")], }, marks: [ Plot.ruleY([0]), Plot.axisX({ label: null, // label: 'Total acumulado ordenado a pagar', // tickFormat: d => d3.format('$,.0f')(d / 1e6)+'M', fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontSize: 14, // ticks: false, tickSize: 0, }), Plot.axisY({ label: 'Total spending on consulting services', tickFormat: d => d3.format('$,.0f')(d / 1e6)+'M', fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontSize: 14, // ticks: false, tickSize: 0, }), Plot.lineY(thedata, { x: "fecha_orden_pago", y: "acumulado", stroke: 'categoria_distribucion', // tip: true, marker: 'dot', strokeWidth: 2, }), Plot.text(thedata, Plot.selectLast({ x: "fecha_orden_pago", y: "acumulado", z: 'categoria_distribucion', text: d => d3.format('$,.0f')(d.acumulado), fontSize: 16, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', lineAnchor: 'bottom', dy: -10, })), Plot.tip(thedata, Plot.pointer({ x: d => d['fecha_orden_pago'], y: 'acumulado', z: 'categoria_distribucion', fontFamily: 'jaf-bernino-sans', fontSize: 16, title: d => `${formatDateLong(d.fecha_orden_pago, 'en-US')}\nAccumulated: ${d3.format('$,.0f')(d.acumulado)}` + ( d.categoria_distribucion !== 'tribunal' ? '' : `\nDocket: ${d.num_orden_list.join(', ')}` ), })), ] }) }35:T964,grafica_pleitos = { let thedata = aq.from([...data_by_pleito, {pleito: 'Consultores de la Junta', total: 308_111_005}]).orderby(d => d.pleito === 'no_especificado', aq.desc('total')).objects() thedata.forEach(d => { if (d.pleito === 'Gobierno de Puerto Rico') { d.total += 11_329_555 } }) let theplot = Plot.plot({ caption: htl.html`Fuentes: Junta de Supervisión Fiscal, Informes Anuales, 2017, 2018, 2019, 2020, 2021, 2022,2023 y 2024; In re Fin. Oversight & Mgmt. Bd. for P.R., Case No. 17-3283 y análisis de Espacios Abiertos.`, width: 1100, marginLeft: 70, marginTop: 30, marginBottom: 55, x: { domain: thedata.map(d => d.pleito), }, marks: [ Plot.axisX({ // label: 'Dependientes cualificados', label: null, // tickFormat: d => categoria_distribucion_labels[d], tickFormat: d => pleito_labels[d] ?? d, tickSize: 0, fontFamily: 'jaf-bernino-sans', fontSize: 14, }), Plot.axisY({ label: 'Total gastado en consultores', fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, // tickFormat: "$,.0f", tickFormat: d => d3.format('$,.0f')(d/1e6) + 'M', fontSize: 14, }), Plot.barY(thedata, { // filter: (d => d['Estado Civil'] === estado_civil_mutable), x: 'pleito', y: 'total', fill: EA_COLORES_MAP['INDIGO'], }), Plot.text(thedata, { filter: d => d.total > 100e6, x: 'pleito', y: 'total', text: d => d3.format('$,.0f')(d.pleito === 'PREPA' ? d.total - 1 : d['total']), fontSize: 16, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', lineAnchor: 'top', dy: 7, fill: 'white', // textAnchor: 'end', dx: -5, }), Plot.text(thedata, { filter: d => d.total <= 100e6, x: 'pleito', y: 'total', text: d => d3.format('$,.0f')(d.total), fontSize: 16, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', lineAnchor: 'bottom', dy: -7, fill: 'black', // textAnchor: 'end', dx: -5, }), ] }) return theplot }36:T97b,grafica_pleitos_en = { let thedata = aq.from([...data_by_pleito, {pleito: 'Consultores de la Junta', total: 308_111_005}]).orderby(d => d.pleito === 'no_especificado', aq.desc('total')).objects() thedata.forEach(d => { if (d.pleito === 'Gobierno de Puerto Rico') { d.total += 11_329_555 } }) let theplot = Plot.plot({ caption: htl.html`Sources: Fiscal Oversight and Management Board, Annual Reports, 2017, 2018, 2019, 2020, 2021, 2022,2023 y 2024; In re Fin. Oversight & Mgmt. Bd. for P.R., Case No. 17-3283 and analysis by Espacios Abiertos.`, width: 1100, marginLeft: 70, marginTop: 30, marginBottom: 55, x: { domain: thedata.map(d => d.pleito), }, marks: [ Plot.axisX({ // label: 'Dependientes cualificados', label: null, // tickFormat: d => categoria_distribucion_labels[d], tickFormat: d => pleito_labels_en[d] ?? d, tickSize: 0, fontFamily: 'jaf-bernino-sans', fontSize: 14, }), Plot.axisY({ label: 'Total spending on consulting services', fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, // tickFormat: "$,.0f", tickFormat: d => d3.format('$,.0f')(d/1e6) + 'M', fontSize: 14, }), Plot.barY(thedata, { // filter: (d => d['Estado Civil'] === estado_civil_mutable), x: 'pleito', y: 'total', fill: EA_COLORES_MAP['INDIGO'], }), Plot.text(thedata, { filter: d => d.total > 100e6, x: 'pleito', y: 'total', text: d => d3.format('$,.0f')(d.pleito === 'PREPA' ? d.total - 1 : d['total']), fontSize: 16, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', lineAnchor: 'top', dy: 7, fill: 'white', // textAnchor: 'end', dx: -5, }), Plot.text(thedata, { filter: d => d.total <= 100e6, x: 'pleito', y: 'total', text: d => d3.format('$,.0f')(d.total), fontSize: 16, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', lineAnchor: 'bottom', dy: -7, fill: 'black', // textAnchor: 'end', dx: -5, }), ] }) return theplot }37:T935,{ let thedata = aq.from(data_by_pleito).orderby(d => d.pleito === 'no_especificado', aq.desc('total')).objects() thedata = [...thedata, {pleito: 'Consultores de la Junta', total: 308_111_005}] let theplot = Plot.plot({ // width: 1100, caption: htl.html`Fuentes: Junta de Supervisión Fiscal, Informes Anuales, 2017, 2018, 2019, 2020, 2021, 2022,2023 y 2024; In re Fin. Oversight & Mgmt. Bd. for P.R., Case No. 17-3283 y análisis de Espacios Abiertos.`, height: 300, marginLeft: 150, marginTop: 30, marginBottom: 50, y: { domain: thedata.map(d => d.pleito), }, marks: [ Plot.axisY({ // label: 'Dependientes cualificados', label: null, // tickFormat: d => categoria_distribucion_labels[d], tickFormat: d => d === 'no_especificado' ? 'No especificado' : d, tickSize: 0, fontFamily: 'jaf-bernino-sans', fontSize: 12, }), Plot.axisX({ label: 'Total ordenado a pagar', fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, // tickFormat: "$,.0f", tickFormat: d => d3.format('$,.0f')(d/1e6) + 'M', fontSize: 12, }), Plot.barX(thedata, { // filter: (d => d['Estado Civil'] === estado_civil_mutable), y: 'pleito', x: 'total', fill: EA_COLORES_MAP['INDIGO'], }), Plot.text(thedata, { filter: d => d.total > 200e6, y: 'pleito', x: 'total', text: d => d3.format('$,.0f')(d['total']), fontSize: 16, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', lineAnchor: 'middle', dx: -7, textAnchor: 'end', fill: 'white', // textAnchor: 'end', dx: -5, }), Plot.text(thedata, { filter: d => d.total <= 200e6, y: 'pleito', x: 'total', text: d => d3.format('$,.0f')(d['total']), fontSize: 16, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', lineAnchor: 'middle', dx: 7, textAnchor: 'start', fill: 'black', // textAnchor: 'end', dx: -5, }), ] }) return theplot }38:Tb92,grafica_consultores = { let thedata = [...data_by_applicant_tribunal, ...data_by_applicant_expenditures_detallados] thedata = aq.from(thedata).orderby(d => d.applicant === null, aq.desc('total')).objects() thedata = aq.from(thedata) .rename({'applicant': 'old_applicant'}) .derive({'applicant': aq.escape(d => applicant_replace[d.old_applicant] ?? d.old_applicant)}) .groupby('applicant') .rollup({'total': op.sum('total')}) .orderby(d => d.applicant === null, aq.desc('total')) .objects() thedata = thedata.filter(d => (d.applicant !== null) & (d.total > 0)) thedata = thedata.slice(0, num_consultores_show) thedata = [...thedata, {applicant: 'Otros no identificados', total: 2_021_957_704 - 1_933_350_667.05}] let theplot = Plot.plot({ caption: htl.html`Fuentes: Junta de Supervisión Fiscal, Informes Anuales, 2017, 2018, 2019, 2020, 2021, 2022,2023 y 2024; In re Fin. Oversight & Mgmt. Bd. for P.R., Case No. 17-3283 y análisis de Espacios Abiertos.`, width: 900, marginLeft: 360, height: 2400/125 * Math.min(125, num_consultores_show) + 30 + 50, marginTop: 30, marginBottom: 50, y: { domain: thedata.map(d => d.applicant), }, marks: [ Plot.axisY({ // label: 'Dependientes cualificados', label: null, // tickFormat: d => categoria_distribucion_labels[d], tickFormat: d => d === null ? 'No especificado' : d, tickSize: 0, fontFamily: 'jaf-bernino-sans', fontSize: 14, }), Plot.axisX({ label: 'Total gastado en consultores', fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, // tickFormat: "$,.0f", tickFormat: d => d3.format('$,.0f')(d/1e6) + 'M', fontSize: 14, }), Plot.barX(thedata, { // filter: (d => d['Estado Civil'] === estado_civil_mutable), y: 'applicant', x: 'total', fill: EA_COLORES_MAP['INDIGO'], }), Plot.text(thedata, { filter: d => d.total > 70e6, y: 'applicant', x: 'total', text: d => d3.format('$,.0f')(d['total']), fontSize: 16, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', lineAnchor: 'middle', dx: -7, textAnchor: 'end', fill: 'white', // textAnchor: 'end', dx: -5, }), Plot.text(thedata, { filter: d => d.total <= 70e6, y: 'applicant', x: 'total', text: d => d3.format('$,.0f')(d['total']), fontSize: 16, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', lineAnchor: 'middle', dx: 7, textAnchor: 'start', fill: 'black', // textAnchor: 'end', dx: -5, }), ] }) return theplot }39:Tbab,grafica_consultores_en = { let thedata = [...data_by_applicant_tribunal, ...data_by_applicant_expenditures_detallados] thedata = aq.from(thedata).orderby(d => d.applicant === null, aq.desc('total')).objects() thedata = aq.from(thedata) .rename({'applicant': 'old_applicant'}) .derive({'applicant': aq.escape(d => applicant_replace[d.old_applicant] ?? d.old_applicant)}) .groupby('applicant') .rollup({'total': op.sum('total')}) .orderby(d => d.applicant === null, aq.desc('total')) .objects() thedata = thedata.filter(d => (d.applicant !== null) & (d.total > 0)) thedata = thedata.slice(0, num_consultores_show_en) thedata = [...thedata, {applicant: 'Others not identified', total: 2_021_957_704 - 1_933_350_667.05}] let theplot = Plot.plot({ caption: htl.html`Sources: Fiscal Oversight and Management Board, Annual Reports, 2017, 2018, 2019, 2020, 2021, 2022,2023 y 2024; In re Fin. Oversight & Mgmt. Bd. for P.R., Case No. 17-3283 and analysis by Espacios Abiertos.`, width: 900, marginLeft: 360, height: 2400/125 * Math.min(125, num_consultores_show_en) + 30 + 50, marginTop: 30, marginBottom: 50, y: { domain: thedata.map(d => d.applicant), }, marks: [ Plot.axisY({ // label: 'Dependientes cualificados', label: null, // tickFormat: d => categoria_distribucion_labels[d], tickFormat: d => d === null ? 'No especificado' : d, tickSize: 0, fontFamily: 'jaf-bernino-sans', fontSize: 14, }), Plot.axisX({ label: 'Total spending on consulting services', fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, // tickFormat: "$,.0f", tickFormat: d => d3.format('$,.0f')(d/1e6) + 'M', fontSize: 14, }), Plot.barX(thedata, { // filter: (d => d['Estado Civil'] === estado_civil_mutable), y: 'applicant', x: 'total', fill: EA_COLORES_MAP['INDIGO'], }), Plot.text(thedata, { filter: d => d.total > 70e6, y: 'applicant', x: 'total', text: d => d3.format('$,.0f')(d['total']), fontSize: 16, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', lineAnchor: 'middle', dx: -7, textAnchor: 'end', fill: 'white', // textAnchor: 'end', dx: -5, }), Plot.text(thedata, { filter: d => d.total <= 70e6, y: 'applicant', x: 'total', text: d => d3.format('$,.0f')(d['total']), fontSize: 16, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', lineAnchor: 'middle', dx: 7, textAnchor: 'start', fill: 'black', // textAnchor: 'end', dx: -5, }), ] }) return theplot }3a:T579,db_instance = { // Initialize database const db = await makeDB() // Insert files directly into tables // await insertFile(db, 'penguins_file', penguins_file); // await insertFile(db, 'fomb_consultores_tribunal_file', fomb_consultores_tribunal_file) // Alternatively, register file into the db's filesystem const iris_buffer = await iris_file.arrayBuffer(); await db.registerFileBuffer( 'iris.csv', // Choose filename to use inside db new Uint8Array(iris_buffer) ); const tribunal_buffer = await fomb_consultores_tribunal_file.arrayBuffer(); await db.registerFileBuffer( 'fomb_consultores_tribunal.csv', // Choose filename to use inside db new Uint8Array(tribunal_buffer) ); const expenditures_detallado_buffer = await fomb_expenditures_detallado_file.arrayBuffer(); await db.registerFileBuffer( 'fomb_expenditures_detallado.csv', // Choose filename to use inside db new Uint8Array(expenditures_detallado_buffer) ); const examiner_buffer = await fomb_examiner_file.arrayBuffer(); await db.registerFileBuffer( 'fomb_examiner.csv', // Choose filename to use inside db new Uint8Array(examiner_buffer) ); const gasto_anual_buffer = await fomb_gasto_anual_file.arrayBuffer(); await db.registerFileBuffer( 'fomb_gasto_anual.csv', // Choose filename to use inside db new Uint8Array(gasto_anual_buffer) ); return db }3b:T632,client = { const client_class = (duckdb_client === 'Compatibility') ? DuckDBClientCompat : DuckDBClient; const c = new client_class(db_instance); // Create tables here await c.query(` create or replace table fomb_consultores_tribunal as ( select * replace ( Total.replace(',', '').replace('$', '')::DOUBLE as Total, ) -- from fomb_consultores_tribunal_file from 'fomb_consultores_tribunal.csv' ) `) await c.query(` create or replace table fomb_expenditures_detallados as ( select * replace ( YTD.regexp_replace('^-$', '0').replace('$','').replace(',','')::DOUBLE as YTD, Categoría.trim() as Categoría, ) from 'fomb_expenditures_detallado.csv' ) `) await c.query(` create or replace table fomb_examiner as ( select * replace ( Compensation.regexp_replace('^-$', '0').replace('$','').replace(',','')::DOUBLE as Compensation, Expenses.regexp_replace('^-$', '0').replace('$','').replace(',','')::DOUBLE as Expenses, Total.regexp_replace('^-$', '0').replace('$','').replace(',','')::DOUBLE as Total, ) from 'fomb_examiner.csv' ) `) await c.query(` create or replace table fomb_gasto_anual as ( select * replace ( "Año"::INT as "Año", "Gastos Consultores".regexp_replace('^-$', '0').replace('$','').replace(',','')::DOUBLE as "Gastos Consultores", ) from 'fomb_gasto_anual.csv' ) `) await c.query(` create or replace table iris as ( from 'iris.csv' -- Filename used as first arg to db.registerFileBuffer ) `) return c; }3c:T627,class DuckDBClientCompat extends DuckDBClient { async queryStream(query, params) { const connection = await this._db.connect(); let reader, batch; try { if (params?.length > 0) { const statement = await connection.prepare(query); reader = await statement.send(...params); } else { reader = await connection.send(query); } batch = await reader.next(); if (batch.done) throw new Error("missing first batch"); } catch (error) { await connection.close(); throw error; } // Mosaic utility: convert Arrow value to Javascript value const converters = {} batch.value.schema.fields.forEach(d => { console.log('Type for ', d.name, d.type) converters[d.name] = convertArrowValue(d.type) }) return { schema: getArrowTableSchema(batch.value), async *readRows() { try { while (!batch.done) { let batch_array = batch.value.toArray(); // Convert all values to Javascript version let object_array = [] for (let i = 0; i < batch_array.length; i++) { const d_proxy = batch_array[i]; const d_obj = {} for (let k of Object.keys(converters)) { d_obj[k] = converters[k](d_proxy[k]) } object_array.push(d_obj) } yield object_array; batch = await reader.next(); } } finally { await connection.close(); } } }; } }3d:T6f4,async function insertFile(database, name, file, options) { const url = await file.url(); if (url.startsWith("blob:")) { const buffer = await file.arrayBuffer(); await database.registerFileBuffer(file.name, new Uint8Array(buffer)); } else { await database.registerFileURL(file.name, new URL(url, location).href, 4); // duckdb.DuckDBDataProtocol.HTTP } const connection = await database.connect(); try { switch (file.mimeType) { case "text/csv": case "text/tab-separated-values": { return await connection.insertCSVFromPath(file.name, { name, schema: "main", ...options }).catch(async (error) => { // If initial attempt to insert CSV resulted in a conversion // error, try again, this time treating all columns as strings. if (error.toString().includes("Could not convert")) { return await insertUntypedCSV(connection, file, name); } throw error; }); } case "application/json": return await connection.insertJSONFromPath(file.name, { name, schema: "main", ...options }); default: if (/\.arrow$/i.test(file.name)) { const buffer = new Uint8Array(await file.arrayBuffer()); return await connection.insertArrowFromIPCStream(buffer, { name, schema: "main", ...options }); } if (/\.parquet$/i.test(file.name)) { return await connection.query( `CREATE VIEW '${name}' AS SELECT * FROM parquet_scan('${file.name}')` ); } throw new Error(`unknown file type: ${file.mimeType}`); } } finally { await connection.close(); } }3e:[{"id":"6126fa2ef60690062d9ae0f0784ed9ddb7d8f95eaa6d06cd28cfe28fcd135ca8931e1a524b2da1293a3ce263f2edd010b770f911684eb0e0be77d03b582da48d","href":"https://static.observableusercontent.com/files/6126fa2ef60690062d9ae0f0784ed9ddb7d8f95eaa6d06cd28cfe28fcd135ca8931e1a524b2da1293a3ce263f2edd010b770f911684eb0e0be77d03b582da48d","name":"FOMB Budget to Actual - Expenditures detallado - 2025_02_06.csv","type":"text/csv","lastModified":1738858357203.496,"size":64674},{"id":"c8c1c9ac471b8c5543fe0d7e5ef9f371c5d1e96a5a4a783d7a7ea0dbf45fcb52dbf03d5196d3c31f730dc9fe02abac65c43bcf6be41f8f669d67d85733bca3d8","href":"https://static.observableusercontent.com/files/c8c1c9ac471b8c5543fe0d7e5ef9f371c5d1e96a5a4a783d7a7ea0dbf45fcb52dbf03d5196d3c31f730dc9fe02abac65c43bcf6be41f8f669d67d85733bca3d8","name":"FOMB Budget to Actual - Consultores Tribunal - 2025_02_06@1.csv","type":"text/csv","lastModified":1738861826091.438,"size":197003},{"id":"05d09db8a17ecd9224952e84359bdd264b26477f44fdd710a0158161a97ea0bbcf9b7edcf6c9d78ff8bdbe958e693cb2e2d9eec5402215f5488c189434e22186","href":"https://static.observableusercontent.com/files/05d09db8a17ecd9224952e84359bdd264b26477f44fdd710a0158161a97ea0bbcf9b7edcf6c9d78ff8bdbe958e693cb2e2d9eec5402215f5488c189434e22186","name":"FOMB Budget to Actual - Consultores Tribunal - 2025_02_06@2.csv","type":"text/csv","lastModified":1738865293486.762,"size":197627},{"id":"b7c9afbbc1345d7b9dc5c2f595c441cc2758ead0148495fec8c9880213fa1b329ff986559a0a23d59b9d58607513b46974e40a547bac4d829a181ca998976b2b","href":"https://static.observableusercontent.com/files/b7c9afbbc1345d7b9dc5c2f595c441cc2758ead0148495fec8c9880213fa1b329ff986559a0a23d59b9d58607513b46974e40a547bac4d829a181ca998976b2b","name":"FOMB Budget to Actual - Examiner (CSV).csv","type":"text/csv","lastModified":1738865677325.133,"size":667},{"id":"03e0da6bc926dde50b3570e5969e6b31cf4caa6e89a3e43eb6069bd3603a742bd2da21789402ceb768d4939839b7fef83209656e3f93422564a09eab1f604115","href":"https://static.observableusercontent.com/files/03e0da6bc926dde50b3570e5969e6b31cf4caa6e89a3e43eb6069bd3603a742bd2da21789402ceb768d4939839b7fef83209656e3f93422564a09eab1f604115","name":"FOMB Budget to Actual - Gasto Anual JCF.csv","type":"text/csv","lastModified":1738867002197.145,"size":182},{"id":"548337221f20bd8fb79e5c41ffbd9912c91a21fd7ad0686ec45842416330a9760a359645a798e0d44bedfd79f16d9185c4919c75432bbf5a1a1a4b32a002228e","href":"https://static.observableusercontent.com/files/548337221f20bd8fb79e5c41ffbd9912c91a21fd7ad0686ec45842416330a9760a359645a798e0d44bedfd79f16d9185c4919c75432bbf5a1a1a4b32a002228e","name":"FOMB Budget to Actual - Consultores Tribunal - 2025_02_06@3.csv","type":"text/csv","lastModified":1738868699236.134,"size":197632},{"id":"373c5a864b7b5a01667024e57ddcbe3de024f9503122017dea96c144ab518f762340ff4b1c76b4c3018b13b8da10eb09d58158c95bbc5202a5afd72898fcc37c","href":"https://static.observableusercontent.com/files/373c5a864b7b5a01667024e57ddcbe3de024f9503122017dea96c144ab518f762340ff4b1c76b4c3018b13b8da10eb09d58158c95bbc5202a5afd72898fcc37c","name":"FOMB Budget to Actual - Consultores Tribunal - 2025_02_07.csv","type":"text/csv","lastModified":1738944012318.434,"size":197640},{"id":"37e36ea8193f127758064b4f60ae01dc036957529d873aa1378c3a4098e6983107be207e5db015375633316cd08aed82a406ac778249f285183f4f9f5dabf358","href":"https://static.observableusercontent.com/files/37e36ea8193f127758064b4f60ae01dc036957529d873aa1378c3a4098e6983107be207e5db015375633316cd08aed82a406ac778249f285183f4f9f5dabf358","name":"FOMB Budget to Actual - Consultores Tribunal - 2025_02_07@1.csv","type":"text/csv","lastModified":1738944460243.044,"size":197645},{"id":"27d6a4d18f5cc24684331a0583a7bcd07ae70d6cc51234b93b0818f7b931b5794276c473ffc45b15880fba5f56df47921c8ac2a8a9e079530b2629abcb5fcb72","href":"https://static.observableusercontent.com/files/27d6a4d18f5cc24684331a0583a7bcd07ae70d6cc51234b93b0818f7b931b5794276c473ffc45b15880fba5f56df47921c8ac2a8a9e079530b2629abcb5fcb72","name":"iris.csv","type":"text/csv","lastModified":1738691062441.71,"size":2734},{"id":"c76bec5ad5baa31ccc846a5afdb1756d7bba2bba172cb9ca2306673e21ae607f83f6e9efaa93c253dd3c0aaf4a1a21b6497d7713235ddac8a1b63adfc5e881da","href":"https://static.observableusercontent.com/files/c76bec5ad5baa31ccc846a5afdb1756d7bba2bba172cb9ca2306673e21ae607f83f6e9efaa93c253dd3c0aaf4a1a21b6497d7713235ddac8a1b63adfc5e881da","name":"penguins.csv","type":"text/csv","lastModified":1738691062441.71,"size":13478}] 2f:{"id":"336bef2cfcd02b39","slug":null,"title":"Consultores de la Junta 2024","thumbnail":"68601db794de96935a319fad7184ebdeba73d5665b5fc5eb0e08c1a5a595b367","access_level":"public","create_time":"$D2025-02-04T17:44:22.441Z","update_time":"$D2025-03-06T17:58:16.991Z","view_time":null,"first_public_time":"$D2025-02-06T17:56:17.473Z","fork":{"id":"f5f48bea1ca8e19b","slug":"duckdb-template","title":"DuckDB Template","owner":{"id":"247b19faa48b7d89","login":"jzavala-gonzalez","name":"José M. Zavala González"}},"stars":0,"starred":false,"owner":{"type":"team","id":"247b19faa48b7d89","login":"jzavala-gonzalez","name":"José M. Zavala González","avatar_url":"https://avatars-next.observableusercontent.com/c80357963553ac341ea90ed3d79296a72d004aa90d9b89afd76f17e04dcf9277","tier":"starter_2024"},"version":786,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"2c62f0243f8f45b0","login":"jzavala-gonzalez","name":"José M. Zavala González","avatar_url":"https://avatars-next.observableusercontent.com/c80357963553ac341ea90ed3d79296a72d004aa90d9b89afd76f17e04dcf9277","tier":null}],"members":[{"type":"team","id":"247b19faa48b7d89","login":"jzavala-gonzalez","name":"José M. Zavala González","avatar_url":"https://avatars-next.observableusercontent.com/c80357963553ac341ea90ed3d79296a72d004aa90d9b89afd76f17e04dcf9277","tier":"starter_2024","role":"editor"},{"type":"individual","id":"2c62f0243f8f45b0","login":"jzavala-gonzalez","name":"José M. Zavala González","avatar_url":"https://avatars-next.observableusercontent.com/c80357963553ac341ea90ed3d79296a72d004aa90d9b89afd76f17e04dcf9277","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":{"id":"6747c4e5c0f5d653","slug":"espacios-abiertos","title":"Espacios Abiertos","owner":{"id":"247b19faa48b7d89","login":"jzavala-gonzalez","name":"José M. Zavala González"}},"type":"notebook","body":{"title":"Consultores de la Junta 2024","cells":[{"id":0,"value":"# Consultores de la Junta 2024\n\nTrabajado en Febrero 2025","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":98,"value":"$31","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":711,"value":"$32","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":473,"value":"categoria_distribucion_labels = Object({\n 'junta': 'Consultores contratados\\nfuera del proceso\\nde Título III',\n 'tribunal': 'Consultores contratados\\ndentro del proceso\\nde Título III',\n 'examinador': 'Oficial examinador',\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":717,"value":"categoria_distribucion_labels_en = Object({\n 'junta': 'Consultants hired\\noutside Title III\\nproceedings',\n 'tribunal': 'Consultants hired\\nwithin Title III\\nproceedings',\n 'examinador': 'Fee Examiner',\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":689,"value":"aq.from(resumen_actualizacion).rollup({total: op.sum('total')}).view()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":329,"value":"$33","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":728,"value":"$34","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":560,"value":"function formatDateLong(sample_date, loc) {\n // const sample_date = data_by_fecha_orden_pago_with_date[0].fecha_orden_pago\n const utc_date = new Date(sample_date.getUTCFullYear(), sample_date.getUTCMonth(), sample_date.getUTCDate())\n \n // return utc_date\n // 2018-03-07\n return utc_date.toLocaleString(loc, {\n month: 'long',\n day: 'numeric',\n year: 'numeric',\n })\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":134,"value":"$35","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":742,"value":"$36","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":587,"value":"pleito_labels = Object({\n 'PREPA': 'Autoridad de\\nEnergía Eléctrica',\n 'Consultores de la Junta': 'Consultores contratados\\nfuera del proceso\\nde Título III',\n 'Carreteras': 'Autoridad de\\ncarreteras',\n 'Retiro': 'Sistema de retiro',\n 'Edificios': 'Edificios públicos',\n 'no_especificado': 'N/A',\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":740,"value":"pleito_labels_en = Object({\n 'PREPA': 'Puerto Rico\\nElectric Power\\nAuthority',\n 'Consultores de la Junta': 'Consultants hired\\noutside the Title III\\nproceedings',\n 'Carreteras': 'Highways and\\nTransportation\\nAuthority',\n 'Retiro': 'Retirement System',\n 'Edificios': 'Public Buildings\\nAuthority',\n 'no_especificado': 'N/A',\n 'COFINA': 'Sales Tax\\nFinancing Corporation\\n(COFINA)',\n 'Gobierno de Puerto Rico': 'Commonwealth of\\nPuerto Rico',\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":458,"value":"select sum(total)\nfrom fomb_consultores_tribunal","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:client"},{"id":609,"value":"from fomb_consultores_tribunal\nwhere pleito is null","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:client"},{"id":157,"value":"$37","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":601,"value":"[10, 25, 50, \"All of them\"]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":620,"value":"Math.min(2, 3)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":707,"value":"(1_702_517_144 + 11_329_555) / 2_021_957_704","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":604,"value":"viewof num_consultores_show = Inputs.select([10, 25, 50, 99999], {label: \"Consultores:\", value: 10, format: d => d > 100 ? 'Todos' : d})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":186,"value":"$38","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":768,"value":"viewof num_consultores_show_en = Inputs.select([10, 25, 50, 99999], {label: \"Consultants:\", value: 10, format: d => d > 100 ? 'Todos' : d})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":763,"value":"$39","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":213,"value":"// Porque hay duplicados\napplicant_replace = Object({\n 'Whyte, Bettina': 'Bettina Whyte',\n \"O'neill & Borges LLC\": \"O'Neill & Borges LLC\",\n 'Ernst & Young Puerto Rico LLC': 'Ernst & Young LLP',\n 'Autoridad para el financiamiento de la Infraestructura de Puerto Rico (PR Office)': 'Autoridad para el financiamiento de la infraestructura (AFI)',\n 'Citigroup Global Markets Inc.': 'CITI Group Global Markets Inc.'\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":652,"value":"{\n let thedata = [...data_by_applicant_tribunal, ...data_by_applicant_expenditures_detallados]\n thedata = aq.from(thedata).orderby(d => d.applicant === null, aq.desc('total')).objects()\n\n thedata = aq.from(thedata)\n .rename({'applicant': 'old_applicant'})\n .derive({'applicant': aq.escape(d => applicant_replace[d.old_applicant] ?? d.old_applicant)})\n .groupby('applicant')\n .rollup({'total': op.sum('total')})\n .orderby(d => d.applicant === null, aq.desc('total'))\n .objects()\n\n thedata = thedata.filter(d => (d.applicant !== null) & (d.total > 0))\n\n return aq.from(thedata)\n \n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":658,"value":"1_933_350_667.05","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":422,"value":"categoria_distribucion_labels","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":549,"value":"data_by_fecha_orden_pago_with_date[0]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":344,"value":"data_by_fecha_orden_pago_with_date.filter(d => d.fecha_orden_pago_string === null)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":335,"value":"data_by_fecha_orden_pago_with_date = {\n let thedata = data_by_fecha_orden_pago.map(d => Object({\n 'fecha_orden_pago_string': d.fecha_orden_pago_string,\n 'total': d.total, 'acumulado': d.acumulado,\n 'categoria_distribucion': 'tribunal',\n 'num_orden_list': d.num_orden_list.toArray(),\n }))\n thedata.forEach(d => {\n d.fecha_orden_pago = new Date(d.fecha_orden_pago_string)\n })\n\n return thedata\n\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":369,"value":"examiner_data_by_fecha_orden_pago_with_date = {\n let thedata = examiner_data_by_fecha_orden_pago.map(d => Object({\n 'fecha_orden_pago_string': d.fecha_orden_pago_string,\n 'total': d.total, 'acumulado': d.acumulado,\n 'categoria_distribucion': 'examinador',\n }))\n thedata.forEach(d => {\n d.fecha_orden_pago = new Date(d.fecha_orden_pago_string)\n })\n\n return thedata\n\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":400,"value":"junta_data_by_fecha_orden_pago_with_date = {\n let thedata = junta_data_by_fecha_orden_pago.map(d => Object({\n 'fecha_orden_pago_string': d.fecha_orden_pago_string,\n 'total': d.total, 'acumulado': d.acumulado,\n 'categoria_distribucion': 'junta',\n }))\n thedata.forEach(d => {\n d.fecha_orden_pago = new Date(d.fecha_orden_pago_string)\n })\n\n return thedata\n\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":179,"value":"select applicant.trim() as applicant, sum(total) as total\nfrom fomb_consultores_tribunal\nwhere applicant is not null\ngroup by all\norder by applicant","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"data_by_applicant_tribunal","database":"var:client"},{"id":510,"value":"data_by_fecha_orden_pago.map(d => d.num_orden_list.toArray())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":516,"value":"convertVectorToArray(data_by_fecha_orden_pago[0].num_orden_list)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":531,"value":"Number(2685n)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":514,"value":"function convertVectorToArray(input_vector) {\n const as_array_dict = input_vector.toArray()\n const array_length = Object.keys(as_array_dict).length\n const output_vector = []\n for (let i = 0; i < array_length; i++) {\n output_vector.push(as_array_dict[i])\n }\n return output_vector\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":320,"value":"data_by_fecha_orden_pago","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":313,"value":"with initial as (\n select \"Fecha de orden de pago\".strftime('%Y-%m-%d') as fecha_orden_pago_string, sum(total) as total,\n list(distinct \"# Orden\"::VARCHAR order by \"# Orden\") as num_orden_list\n from fomb_consultores_tribunal\n group by all\n order by all\n)\n\nselect *, sum(total) over (order by fecha_orden_pago_string rows between unbounded preceding and current row) as acumulado\nfrom initial","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"data_by_fecha_orden_pago","database":"var:client"},{"id":503,"value":"select list(distinct \"# Orden\" order by \"# Orden\") as num_orden,\nfrom fomb_consultores_tribunal","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:client"},{"id":226,"value":"select sum(total) as total\nfrom fomb_consultores_tribunal\n-- where applicant is not null","pinned":false,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:client"},{"id":235,"value":"select sum(total.replace(',', '').replace('$','')::DOUBLE)\nfrom 'fomb_consultores_tribunal.csv'","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:client"},{"id":44,"value":"select ifnull(pleito, 'no_especificado') as pleito, sum(total) as total\nfrom fomb_consultores_tribunal\ngroup by all\norder by total desc","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"data_by_pleito","database":"var:client"},{"id":251,"value":"select distinct categoría\nfrom fomb_expenditures_detallados","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:client"},{"id":275,"value":"(await client.query(`select distinct categoría.lower() as cat\nfrom fomb_expenditures_detallados`)).map(d => d.cat)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":270,"value":"with initial as (\nselect *, Categoría.lower() as categoria_normalized,\nfrom fomb_expenditures_detallados\nwhere YTD >= 0 -- Excluir valores negativos, no aplican a este caso\n and ['legal services', 'professional services', 'investment banking', 'professional services promesa responsibilities',\n 'professional services non-recurring investigations', 'transportation, travel costs and reimbursable expenses'].list_contains(categoria_normalized) -- Solo nos interesan algunas categorías\n)\n\nselect Detalle.trim() as applicant,\n sum(YTD) as total\nfrom initial\ngroup by applicant\norder by applicant","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"data_by_applicant_expenditures_detallados","database":"var:client"},{"id":354,"value":"select \"Order (Date)\".strftime('%Y-%m-%d') as fecha_orden_pago_string,\n compensation as compensation,\n expenses as expenses, total as total, sum(total) over (order by fecha_orden_pago_string rows between unbounded preceding and current row) as acumulado,\n \nfrom fomb_examiner","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"examiner_data_by_fecha_orden_pago","database":"var:client"},{"id":384,"value":"select \n \"Año\" || '-01-01' as fecha_orden_pago_string, -- EOY porque pagos ocurrent durante el año, no al inicio. En line chart daría ilusion incorrecta.\n \"Gastos Consultores\" as total,\n sum(total) over (order by fecha_orden_pago_string rows between unbounded preceding and current row) as acumulado,\nfrom fomb_gasto_anual","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"junta_data_by_fecha_orden_pago","database":"var:client"},{"id":88,"value":"# Apéndice","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":148,"value":"import { aq, op } from '@uwdata/arquero'","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":90,"value":"resumen_actualizacion = [\n {categoria_distribucion: 'junta', total: 308_111_005},\n {categoria_distribucion: 'tribunal', total: 1_702_517_144},\n {categoria_distribucion: 'examinador', total: 11_329_555},\n]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":109,"value":"## Estilos","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":111,"value":"fonts_link = \"https://use.typekit.net/szl4srx.css\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":113,"value":"fonts_css_content = await fetch(fonts_link).then(r => r.text())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":115,"value":"fonts_css = htl.html`\n\n`","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":443,"value":"estilos = htl.html`\n\n`","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":102,"value":"import {EA_COLORES_MAP} from \"22dc6182207c44c8\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":33,"value":"## Parameters","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":35,"value":"viewof duckdb_client = Inputs.radio(['Original', 'Compatibility'], {label: \"DuckDB Client\", value: \"Compatibility\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":38,"value":"viewof duckdb_bundle = Inputs.radio([\"Auto\", 'eh', 'mvp'], {label: \"DuckDB bundle\", value: \"eh\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":5,"value":"## Initializing DuckDB\nWe need a version different from the standard in Observable. Took a couple of lessons from [this notebook](https://observablehq.com/@jcolot/duckdb-in-webassembly-with-loadable-extensions). It also loads the TPC-H extension to make experimenting with the queries and answers simpler.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":9,"value":"db_instance.getVersion()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":11,"value":"duckdb = import(\n \"https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.1-dev232.0/+esm\"\n)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":13,"value":"bundle = {\n const bundles = duckdb.getJsDelivrBundles()\n if (duckdb_bundle === 'Auto') {\n return duckdb.selectBundle(bundles)\n } else {\n const bun = bundles[duckdb_bundle]\n bun['pthreadWorker'] = null;\n return bun;\n }\n // return bundles['mvp']\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":15,"value":"async function makeDB() {\n const logger = new duckdb.ConsoleLogger();\n const worker = await duckdb.createWorker(bundle.mainWorker);\n const db = new duckdb.AsyncDuckDB(logger, worker);\n await db.instantiate(bundle.mainModule);\n return db\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":17,"value":"$3a","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":31,"value":"$3b","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":19,"value":"## Modified DuckDBClient\n\nIt just inserts a couple of lines into the original queryStream method","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":21,"value":"$3c","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":52,"value":"$3d","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":55,"value":"async function insertUntypedCSV(connection, file, name) {\n const statement = await connection.prepare(\n `CREATE TABLE '${name}' AS SELECT * FROM read_csv_auto(?, ALL_VARCHAR=TRUE)`\n );\n return await statement.send(file.name);\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":23,"value":"getArrowTableSchema = observable_stdlib.getArrowTableSchema","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":25,"value":"convertArrowValue = mosaic_core.convertArrowValue","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":27,"value":"observable_stdlib = await import('https://cdn.jsdelivr.net/npm/@observablehq/stdlib@5.8.7/+esm');","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":29,"value":"mosaic_core = await import('https://cdn.jsdelivr.net/npm/@uwdata/mosaic-core@0.9.0/+esm');","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":59,"value":"## File attachments","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":61,"value":"penguins_file = FileAttachment(\"penguins.csv\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":68,"value":"iris_file = FileAttachment(\"iris.csv\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":79,"value":"fomb_consultores_tribunal_file = FileAttachment(\"FOMB Budget to Actual - Consultores Tribunal - 2025_02_07@1.csv\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":248,"value":"fomb_expenditures_detallado_file = FileAttachment(\"FOMB Budget to Actual - Expenditures detallado - 2025_02_06.csv\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":350,"value":"fomb_examiner_file = FileAttachment(\"FOMB Budget to Actual - Examiner (CSV).csv\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":377,"value":"fomb_gasto_anual_file = FileAttachment(\"FOMB Budget to Actual - Gasto Anual JCF.csv\") // Son los gastos con fecha para la categoria de distribucion de \"Consultores de la Junta\" o algo asi","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W3e","stdlib":"1","titling":"auto"}} 30:"$2f:body"