The avatar for @jzavala-gonzalez
José M. Zavala González
Public
Edited
Mar 6
Fork of DuckDB Template
10
Insert cell
Insert cell
Insert cell
Insert cell
categoria_distribucion_labels = Object({
'junta': 'Consultores contratados\nfuera del proceso\nde Título III',
'tribunal': 'Consultores contratados\ndentro del proceso\nde Título III',
'examinador': 'Oficial examinador',
})
Insert cell
categoria_distribucion_labels_en = Object({
'junta': 'Consultants hired\noutside Title III\nproceedings',
'tribunal': 'Consultants hired\nwithin Title III\nproceedings',
'examinador': 'Fee Examiner',
})
Insert cell
aq.from(resumen_actualizacion).rollup({total: op.sum('total')}).view()
Insert cell
Insert cell
Insert cell
function formatDateLong(sample_date, loc) {
// const sample_date = data_by_fecha_orden_pago_with_date[0].fecha_orden_pago
const utc_date = new Date(sample_date.getUTCFullYear(), sample_date.getUTCMonth(), sample_date.getUTCDate())
// return utc_date
// 2018-03-07
return utc_date.toLocaleString(loc, {
month: 'long',
day: 'numeric',
year: 'numeric',
})
}
Insert cell
Insert cell
Insert cell
pleito_labels = Object({
'PREPA': 'Autoridad de\nEnergía Eléctrica',
'Consultores de la Junta': 'Consultores contratados\nfuera del proceso\nde Título III',
'Carreteras': 'Autoridad de\ncarreteras',
'Retiro': 'Sistema de retiro',
'Edificios': 'Edificios públicos',
'no_especificado': 'N/A',
})
Insert cell
pleito_labels_en = Object({
'PREPA': 'Puerto Rico\nElectric Power\nAuthority',
'Consultores de la Junta': 'Consultants hired\noutside the Title III\nproceedings',
'Carreteras': 'Highways and\nTransportation\nAuthority',
'Retiro': 'Retirement System',
'Edificios': 'Public Buildings\nAuthority',
'no_especificado': 'N/A',
'COFINA': 'Sales Tax\nFinancing Corporation\n(COFINA)',
'Gobierno de Puerto Rico': 'Commonwealth of\nPuerto Rico',
})
Insert cell
client
select sum(total)
from fomb_consultores_tribunal
Insert cell
client
from fomb_consultores_tribunal
where pleito is null
Insert cell
Insert cell
[10, 25, 50, "All of them"]
Insert cell
Math.min(2, 3)
Insert cell
(1_702_517_144 + 11_329_555) / 2_021_957_704
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
// Porque hay duplicados
applicant_replace = Object({
'Whyte, Bettina': 'Bettina Whyte',
"O'neill & Borges LLC": "O'Neill & Borges LLC",
'Ernst & Young Puerto Rico LLC': 'Ernst & Young LLP',
'Autoridad para el financiamiento de la Infraestructura de Puerto Rico (PR Office)': 'Autoridad para el financiamiento de la infraestructura (AFI)',
'Citigroup Global Markets Inc.': 'CITI Group Global Markets Inc.'
})
Insert cell
{
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))

return aq.from(thedata)
}
Insert cell
1_933_350_667.05
Insert cell
categoria_distribucion_labels
Insert cell
data_by_fecha_orden_pago_with_date[0]
Insert cell
data_by_fecha_orden_pago_with_date.filter(d => d.fecha_orden_pago_string === null)
Insert cell
data_by_fecha_orden_pago_with_date = {
let thedata = data_by_fecha_orden_pago.map(d => Object({
'fecha_orden_pago_string': d.fecha_orden_pago_string,
'total': d.total, 'acumulado': d.acumulado,
'categoria_distribucion': 'tribunal',
'num_orden_list': d.num_orden_list.toArray(),
}))
thedata.forEach(d => {
d.fecha_orden_pago = new Date(d.fecha_orden_pago_string)
})

return thedata

}
Insert cell
examiner_data_by_fecha_orden_pago_with_date = {
let thedata = examiner_data_by_fecha_orden_pago.map(d => Object({
'fecha_orden_pago_string': d.fecha_orden_pago_string,
'total': d.total, 'acumulado': d.acumulado,
'categoria_distribucion': 'examinador',
}))
thedata.forEach(d => {
d.fecha_orden_pago = new Date(d.fecha_orden_pago_string)
})

return thedata

}
Insert cell
junta_data_by_fecha_orden_pago_with_date = {
let thedata = junta_data_by_fecha_orden_pago.map(d => Object({
'fecha_orden_pago_string': d.fecha_orden_pago_string,
'total': d.total, 'acumulado': d.acumulado,
'categoria_distribucion': 'junta',
}))
thedata.forEach(d => {
d.fecha_orden_pago = new Date(d.fecha_orden_pago_string)
})

return thedata

}
Insert cell
client
select applicant.trim() as applicant, sum(total) as total
from fomb_consultores_tribunal
where applicant is not null
group by all
order by applicant
Insert cell
data_by_fecha_orden_pago.map(d => d.num_orden_list.toArray())
Insert cell
convertVectorToArray(data_by_fecha_orden_pago[0].num_orden_list)
Insert cell
Number(2685n)
Insert cell
function convertVectorToArray(input_vector) {
const as_array_dict = input_vector.toArray()
const array_length = Object.keys(as_array_dict).length
const output_vector = []
for (let i = 0; i < array_length; i++) {
output_vector.push(as_array_dict[i])
}
return output_vector
}
Insert cell
data_by_fecha_orden_pago
Insert cell
client
with initial as (
select "Fecha de orden de pago".strftime('%Y-%m-%d') as fecha_orden_pago_string, sum(total) as total,
list(distinct "# Orden"::VARCHAR order by "# Orden") as num_orden_list
from fomb_consultores_tribunal
group by all
order by all
)

select *, sum(total) over (order by fecha_orden_pago_string rows between unbounded preceding and current row) as acumulado
from initial
Insert cell
client
select list(distinct "# Orden" order by "# Orden") as num_orden,
from fomb_consultores_tribunal
Insert cell
Insert cell
client
select sum(total.replace(',', '').replace('$','')::DOUBLE)
from 'fomb_consultores_tribunal.csv'
Insert cell
client
select ifnull(pleito, 'no_especificado') as pleito, sum(total) as total
from fomb_consultores_tribunal
group by all
order by total desc
Insert cell
client
select distinct categoría
from fomb_expenditures_detallados
Insert cell
(await client.query(`select distinct categoría.lower() as cat
from fomb_expenditures_detallados`)).map(d => d.cat)
Insert cell
client
with initial as (
select *, Categoría.lower() as categoria_normalized,
from fomb_expenditures_detallados
where YTD >= 0 -- Excluir valores negativos, no aplican a este caso
and ['legal services', 'professional services', 'investment banking', 'professional services promesa responsibilities',
'professional services non-recurring investigations', 'transportation, travel costs and reimbursable expenses'].list_contains(categoria_normalized) -- Solo nos interesan algunas categorías
)

select Detalle.trim() as applicant,
sum(YTD) as total
from initial
group by applicant
order by applicant
Insert cell
client
select "Order (Date)".strftime('%Y-%m-%d') as fecha_orden_pago_string,
compensation as compensation,
expenses as expenses, total as total, sum(total) over (order by fecha_orden_pago_string rows between unbounded preceding and current row) as acumulado,
from fomb_examiner
Insert cell
client
select
"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.
"Gastos Consultores" as total,
sum(total) over (order by fecha_orden_pago_string rows between unbounded preceding and current row) as acumulado,
from fomb_gasto_anual
Insert cell
Insert cell
import { aq, op } from '@uwdata/arquero'
Insert cell
resumen_actualizacion = [
{categoria_distribucion: 'junta', total: 308_111_005},
{categoria_distribucion: 'tribunal', total: 1_702_517_144},
{categoria_distribucion: 'examinador', total: 11_329_555},
]
Insert cell
Insert cell
Insert cell
fonts_css_content = await fetch(fonts_link).then(r => r.text())
Insert cell
fonts_css = htl.html`
<style>
${fonts_css_content}
<style/>
`
Insert cell
estilos_leyendas = htl.html`
<style>

span[class$="-swatch"] {
font-size: 16px;
}

*[class$="-ramp"] {
font-size: 12px;
}

label {
width: auto;
}

select[class$="-input"] {
margin-left: 30px;
}
</style>
`
Insert cell
estilos = htl.html`
<style>

span[class$="-swatch"] {
font-size: 16px;
}

*[class$="-ramp"] {
font-size: 12px;
}

h1 {
font-family: "jaf-bernino-sans-comp", sans-serif;
font-weight: 700;
font-style: normal;
}

h2 {
font-family: "jaf-bernino-sans-narrow", sans-serif;
font-weight: 700;
font-style: normal;
}

span, figcaption {
font-family: "jaf-bernino-sans", sans-serif;
font-weight: 400;
font-style: normal;
}

</style>
`
Insert cell
import {EA_COLORES_MAP} from "22dc6182207c44c8"
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
db_instance.getVersion()
Insert cell
duckdb = import(
"https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.1-dev232.0/+esm"
)
Insert cell
bundle = {
const bundles = duckdb.getJsDelivrBundles()
if (duckdb_bundle === 'Auto') {
return duckdb.selectBundle(bundles)
} else {
const bun = bundles[duckdb_bundle]
bun['pthreadWorker'] = null;
return bun;
}
// return bundles['mvp']
}
Insert cell
async function makeDB() {
const logger = new duckdb.ConsoleLogger();
const worker = await duckdb.createWorker(bundle.mainWorker);
const db = new duckdb.AsyncDuckDB(logger, worker);
await db.instantiate(bundle.mainModule);
return db
}
Insert cell
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
}
Insert cell
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;
}
Insert cell
Insert cell
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();
}
}
};
}
}
Insert cell
Insert cell
Insert cell
getArrowTableSchema = observable_stdlib.getArrowTableSchema
Insert cell
convertArrowValue = mosaic_core.convertArrowValue
Insert cell
observable_stdlib = await import('https://cdn.jsdelivr.net/npm/@observablehq/stdlib@5.8.7/+esm');
Insert cell
mosaic_core = await import('https://cdn.jsdelivr.net/npm/@uwdata/mosaic-core@0.9.0/+esm');
Insert cell
Insert cell
penguins_file = FileAttachment("penguins.csv")
Insert cell
iris_file = FileAttachment("iris.csv")
Insert cell
fomb_consultores_tribunal_file = FileAttachment("FOMB Budget to Actual - Consultores Tribunal - 2025_02_07@1.csv")
Insert cell
fomb_expenditures_detallado_file = FileAttachment("FOMB Budget to Actual - Expenditures detallado - 2025_02_06.csv")
Insert cell
fomb_examiner_file = FileAttachment("FOMB Budget to Actual - Examiner (CSV).csv")
Insert cell
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
Insert cell

Purpose-built for displays of data

Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
Learn more