db
aq.from(cambios_por_area)
.fold(['aumento','decremento'], {as: ['tipo_cambio', 'cantidad']})
.view()
80.23 / 396.23
aq.from(all_items)
.filter(d => d.area_observatorio === 'Educación')
.orderby('cantidad')
.view()
aq.from(cambios_por_area).view()
aq
Math.sign(0)
import {DuckDBClientKL} from "@kimmolinna/duckdbclient"
// db = DuckDBClientKL.of({
db = DuckDBClient.of({
sabana: FileAttachment("sabana_with_area.parquet")
})
comparativo_area = aq.from(comparativo_area_agencia)
.groupby('area_observatorio')
.rollup({
'2025_version_01': op.sum('2025_version_01'),
'2025_version_02': op.sum('2025_version_02'),
'variacion_dol': op.sum('variacion_dol'),
})
.derive({
'variacion_pct': d => d.variacion_dol / d['2025_version_01']
})
.orderby('variacion_dol')
.objects()
comparativo_area_agencia = {
let workbook = await FileAttachment('fondo_general_area_observatorio_agencia.xlsx').xlsx()
let data = workbook.sheet('Sheet1', {headers: true})
data = aq.from(data)
.rename({
'Nombre Area Observatorio': 'area_observatorio',
'Nombre Agencia': 'nombre',
'Variacion': 'variacion_dol',
'Variacion %': 'variacion_pct',
})
.derive({
'2025_version_01': d => d['2025_version_01'] / 1e3,
'2025_version_02': d => d['2025_version_02'] / 1e3,
'variacion_dol': d => d.variacion_dol / 1e3, // cambiar de miles a millones
})
.objects();
return data
}
comparativo_concepto = aq.from(comparativo_concepto_descripcion)
.groupby('nombre_concepto')
.rollup({
'2025_version_01': op.sum('2025_version_01'),
'2025_version_02': op.sum('2025_version_02'),
'variacion_dol': op.sum('variacion_dol'),
})
.derive({
'variacion_pct': d => d.variacion_dol / d['2025_version_01']
})
.orderby('variacion_dol')
.objects()
comparativo_concepto_descripcion = {
let workbook = await FileAttachment('fondo_general_concepto_descripcion.xlsx').xlsx()
let data = workbook.sheet('Sheet1', {headers: true})
data = aq.from(data)
.rename({
'Nombre Concepto': 'nombre_concepto',
'DESCRIPCION': 'descripcion',
'Variacion': 'variacion_dol',
'Variacion %': 'variacion_pct',
})
.derive({
'2025_version_01': d => d['2025_version_01'] / 1e3,
'2025_version_02': d => d['2025_version_02'] / 1e3,
'variacion_dol': d => d.variacion_dol / 1e3, // cambiar de miles a millones
})
.objects();
return data
}
workbook = FileAttachment("Analysis_FOMB_NoV_05212024@5.xlsx").xlsx()
workbook.sheetNames
denied_items = {
let data = workbook.sheet('denied', {
headers: true,
// range: "A1:J10"
})
data.forEach(d => {
d.status = 'denied';
d.label = d.nombre
if (d.bajo_custodia) {
d.label += '*'
}
})
return data
}
partially_approved_items = {
let data = workbook.sheet('partially_approved', {
headers: true,
// range: "A1:J10"
})
data.forEach(d => {
d.status = 'partially_approved';
d.label = d.nombre
if (d.bajo_custodia) {
d.label += '*'
}
})
return data
}
approved_items = {
let data = workbook.sheet('approved', {
headers: true,
// range: "A1:J10"
})
data.forEach(d => {
d.status = 'approved';
d.label = d.nombre
if (d.bajo_custodia) {
d.label += '*'
}
})
return data
}
readjusted_items = {
let data = workbook.sheet('reajustes', {
headers: true,
// range: "A1:J10"
})
data.forEach(d => {
d.status = 'readjusted';
d.label = d.nombre
if (d.bajo_custodia) {
d.label += '*'
}
})
return data
}
all_items = [
...denied_items,
...partially_approved_items,
...approved_items,
...readjusted_items,
]
fmt = n => d3.format(",d")(n)
plotLabel = (data, dy) =>
Plot.text(data, {
x: "partida",
y: "accu",
dy: dy,
//frameAnchor: anchor,
fontWeight: "bold",
text: d => d3.format("$,.1f")(d.accu)+'M',
fontSize: 16, // stroke: 'none', fill: 'white',
fontVariant: 'tabular-nums',
fontFamily: 'jaf-bernino-sans',
})
waterfall = function f(numbers,accu_start, name) {
let last = 0, accu = accu_start;
let waterfall = numbers.map((d, i) => {
last = accu;
accu += d.profit;
return {
[name]: d[name],
nextDay: i < (numbers.length - 1) ? numbers[i + 1][name] : "Total",
prior: last,
accu: accu,
profit: d.profit
};
});
waterfall = [
{
[name]: "Inicial",
nextDay: numbers[0][name],
prior: accu_start,
accu: accu_start,
profit: 0
},
...waterfall,
{
[name]: "Total",
nextDay: null,
prior: accu_start,
accu: accu,
profit: 0
}
]
// waterfall.push({
// partida: "Total",
// nextDay: null,
// prior: accu_start,
// accu: accu,
// profit: 0
// });
return waterfall;
}
waterfall(
fomb_adjustment.filter(d => (d.partida !== 'Partial') | show_partial)
.map(d => Object({...d, profit: d.cantidad})), 13062.30 ,'partida')
fomb_adjustment = [
{partida: 'En cumplimiento', cantidad: 130.70},
{partida: 'Revisado con aumento', cantidad: 291.09},
{partida: 'Revisado con decremento', cantidad: -20.72},
{partida: 'Denegado', cantidad: -396.96},
{partida: 'Parcialmente aprobado', cantidad: -118.90, partial: true},
]
fonts_link = "https://use.typekit.net/szl4srx.css"
fonts_link_element =
// Importante para ver los fonts en el editor de Observable (o sea aqui)
htl.html`
<link rel="stylesheet" href="${fonts_link}">
`
estilos = htl.html`
<style>
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 {
font-family: "jaf-bernino-sans", sans-serif;
font-weight: 400;
font-style: normal;
}
span[class$="-swatch"] {
font-size: 16px;
}
</style>
`
import { EA_COLORES_MAP} from "22dc6182207c44c8"
import {aq, op} from '@uwdata/arquero';
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.
