client
ctc_by_state_with_names = {
return aq.from(ctc_by_state)
.rename({'state': 'abbreviation'})
.join_left(aq.from(state_abbreviations), 'abbreviation')
.objects()
}
non_contiguous_states = ['Alaska', 'Guam', 'Hawaii', 'Virgin Islands',]
pan_snap_total
.filter(d => d.cost_per_household !== null)
client
client
client
wic_average_food_cost
Object.keys(wic_average_food_cost[0])
client
client
client
client
client
tanf_data = {
let tanf_caseload_data = aq.from(tanf_caseload).objects()
let tanf_table_a6_data = aq.from(tanf_table_a6).objects()
tanf_table_a6_data.forEach(d => {
if (d.state === 'DIST.OF COLUMBIA') d.state = 'District of Columbia'
})
tanf_table_a6_data = [...tanf_table_a6_data,
{state: 'Puerto Rico', transferred_to_ccdf: 0, transferred_to_ssbg: 0, total_federal_expenditures: 100_516_000+33_522_000}
]
tanf_table_a6_data = aq.from(tanf_table_a6_data)
.derive({'state': aq.escape(d => toTitleCase(d.state, ['of']))})
.objects()
// return aq.from(tanf_table_a6_data).view()
return aq.from(tanf_caseload_data)
.filter(d => d.state !== 'total')
.join_left(aq.from(tanf_table_a6_data), 'state')
.filter(d => d.total_federal_expenditures !== undefined)
.derive({monthly_cost_per_family: d => (d.total_federal_expenditures/12) / d.total_families})
.objects()
}
(100_516_000+33_522_000)/12 / 2_755
function toTitleCase(str, minorWords = []) {
const minorSet = new Set(minorWords.map(word => word.toLowerCase()));
return str
.toLowerCase()
.split(' ')
.map((word, index) => {
// Always capitalize the first word
if (index === 0 || !minorSet.has(word)) {
return word.charAt(0).toUpperCase() + word.slice(1);
}
return word;
})
.join(' ');
}
state_abbreviations = {
let data = await state_abbreviations_file.csv({typed: true})
data = aq.from(data)
.rename({
'State': 'state',
'Abbreviation': 'abbreviation',
})
.objects()
data.forEach(d => {
if (d.state === 'District Of Columbia') d.state = 'District of Columbia'
})
// data= [{
// state: 'Puerto Rico',
// percentage_of_federal_credit: .767679,
// refundable: true,
// }, ...data]
return data
}
fonts_link = "https://use.typekit.net/szl4srx.css"
htl.html`
<style>
figcaption {
max-width: 100%;
}
</style>
`
estilos = htl.html`
<style>
:root {
--primary-color: ${EA_COLORES_MAP['INDIGO']};
--black-color: ${EA_COLORES_MAP['DARK GREY']};
--selected-color: ${EA_COLORES_MAP['ORANGE']};
--hover-color: ${EA_COLORES_MAP['RED']};
}
#svg {
background-color: black;
}
.mapaprueba path:hover {
fill: green;
}
.outline-key path:hover {
stroke: var(--hover-color);
}
.outline-key rect:hover {
stroke: var(--hover-color);
}
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>
`
estilos_embed = htl.html`
<style>
figcaption {
margin-top: 1rem;
}
span[class$="-swatch"] {
font-size: 16px;
font-family: "jaf-bernino-sans", sans-serif;
font-weight: 400;
font-style: normal;
}
</style>
`
estilos_content = d3.select(estilos).select('style').node().textContent
fonts_css_content = await fetch(fonts_link).then(r => r.text())
function add_estilos(theplot, plotname = null) {
var styleElement = document.createElementNS('http://www.w3.org/2000/svg', 'style');
styleElement.textContent = fonts_css_content + '\n' + estilos_content;
let myplot = d3.select(theplot)
const toplevel_tag = myplot.node().tagName
// console.log('toplevel_tag', toplevel_tag)
if (toplevel_tag !== 'svg') {
myplot.selectAll("svg")
.each((d, i, nodes) => {
// if (plotname !== null) {
// console.log('add_estilos for ' + plotname + ': d, i, nodes:', d, i, nodes)
// }
d3.select(nodes[i]).node().insertBefore(
styleElement,
nodes[i].firstChild,
);
})
} else {
myplot.node().insertBefore(
styleElement,
myplot.firstChild,
);
}
}
db_instance.getVersion()
duckdb = import(
"https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.29.1-dev68.0/+esm"
)
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']
}
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
}
db_instance = {
// Initialize database
const db = await makeDB()
// Insert files directly into tables
// await insertFile(db, 'penguins_file', penguins_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 ctc_by_state_buffer = await ctc_by_state_file.arrayBuffer();
await db.registerFileBuffer(
'ctc_by_state.csv', // Choose filename to use inside db
new Uint8Array(ctc_by_state_buffer)
);
const snap_by_state_buffer = await snap_by_state_file.arrayBuffer();
await db.registerFileBuffer(
'snap_by_state.xlsx', // Choose filename to use inside db
new Uint8Array(snap_by_state_buffer)
);
const pan_buffer = await pan_file.arrayBuffer();
await db.registerFileBuffer(
'pan.xlsx', // Choose filename to use inside db
new Uint8Array(pan_buffer)
);
const medicaid_buffer = await medicaid_spending_file.arrayBuffer();
await db.registerFileBuffer(
'medicaid_spending.xlsx', // Choose filename to use inside db
new Uint8Array(medicaid_buffer)
);
const wic_food_buffer = await wic_average_food_cost_file.arrayBuffer();
await db.registerFileBuffer(
'wic_average_food_cost.xlsx', // Choose filename to use inside db
new Uint8Array(wic_food_buffer)
);
await register_buffer(db, ssi_table1_file, 'ssi_table1.xlsx')
await register_buffer(db, ssi_table2_file, 'ssi_table2.xlsx')
await register_buffer(db, medicaid_enrollment_file, 'medicaid_enrollment_by_state.csv')
await register_buffer(db, medicare_by_state_file, 'medicare_by_state.csv')
await register_buffer(db, tanf_table_a6_file, 'tanf_table_a6.csv')
await register_buffer(db, tanf_caseload_file, 'tanf_caseload.csv')
return db
}
async function register_buffer(db, file_attachment, file_name) {
const file_buffer = await file_attachment.arrayBuffer();
await db.registerFileBuffer(
file_name, // Choose filename to use inside db
new Uint8Array(file_buffer)
)
}
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 ctc_by_state as (
from 'ctc_by_state.csv'
)
`)
await c.query(`
create or replace table snap_by_state as (
select * replace (
participation_households.nullif('--')::DOUBLE as participation_households,
participation_persons.nullif('--')::DOUBLE as participation_persons,
cost.nullif('--')::BIGINT as cost,
cost_per_household.nullif('--')::DOUBLE as cost_per_household,
cost_per_person.nullif('--')::DOUBLE as cost_per_person,
)
from read_xlsx('snap_by_state.xlsx', all_varchar = TRUE, stop_at_empty = FALSE)
where state is not null
)
`)
await c.query(`
create or replace table pan as (
from read_xlsx('pan.xlsx')
)
`)
await c.query(`
create or replace table medicaid_spending as (
from read_xlsx('medicaid_spending.xlsx')
)
`)
await c.query(`
create or replace table wic_average_food_cost as (
from read_xlsx('wic_average_food_cost.xlsx')
)
`)
await c.query(`
create or replace table ssi_table1 as (
from read_xlsx('ssi_table1.xlsx')
)
`)
await c.query(`
create or replace table ssi_table2 as (
from read_xlsx('ssi_table2.xlsx')
)
`)
await c.query(`
create or replace table medicaid_enrollment_by_state as (
from 'medicaid_enrollment_by_state.csv'
)
`)
await c.query(`
create or replace table medicare_by_state as (
from 'medicare_by_state.csv'
)
`)
await c.query(`
create or replace table tanf_table_a6 as (
from 'tanf_table_a6.csv'
)
`)
await c.query(`
create or replace table tanf_caseload as (
from 'tanf_caseload.csv'
)
`)
await c.query(`
create or replace table iris as (
from 'iris.csv' -- Filename used as first arg to db.registerFileBuffer
)
`)
return c;
}
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();
}
}
};
}
}
getArrowTableSchema = observable_stdlib.getArrowTableSchema
convertArrowValue = mosaic_core.convertArrowValue
observable_stdlib = await import('https://cdn.jsdelivr.net/npm/@observablehq/stdlib@5.8.7/+esm');
mosaic_core = await import('https://cdn.jsdelivr.net/npm/@uwdata/mosaic-core@0.9.0/+esm');
penguins_file = FileAttachment("penguins.csv")
iris_file = FileAttachment("iris.csv")
ctc_by_state_file = FileAttachment("ctc_by_state@1.csv")
state_abbreviations_file = FileAttachment("states_abbreviations.csv")
snap_by_state_file = FileAttachment("snap_by_state_FY24.xlsx")
pan_file = FileAttachment("pan_FY24@1.xlsx")
medicaid_spending_file = FileAttachment("medicaid_spending_FY23.xlsx")
wic_average_food_cost_file = FileAttachment("wic_average_monthly_food_cost_fy24_preliminary@1.xlsx")
ssi_table1_file = FileAttachment("ssi_table1_fy23.xlsx")
ssi_table2_file = FileAttachment("ssi_table2_fy23.xlsx")
medicaid_enrollment_file = FileAttachment("medicaid_enrollment_by_state_fy23.csv")
medicare_by_state_file = FileAttachment("medicare_by_state_2022.csv")
tanf_table_a6_file = FileAttachment("fy2023_tanf_table_A6.csv")
tanf_caseload_file = FileAttachment("fy2023_tanf_caseload_fy23_families.csv")
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.
