2b:null 2c:["$","title",null,{"children":"Programas de asistencia | José M. Zavala González | Observable"}] 31:T815,{ let thedata = ctc_by_state_with_names .filter(d => d.state !== undefined) // .filter(d => d['Año contributivo'] === year_select); let ctc_promedio = aq.from(thedata) .rollup({ 'promedio': op.mean('credito_menor_dependiente_promedio') }) .objects()[0]['promedio'] let theplot = Plot.plot({ marginLeft: 150, marginRight: 50, marginBottom: 50, height: 1500, // caption: 'ACTUALIZAR MOVER XAXIS LABEL AL CENTRO', // x: {percent: true,}, marks: [ Plot.axisX({ fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, fontSize: 12, tickFormat: '$,.0f', label: 'Crédito por menor dependiente - promedio', }), Plot.axisY({ fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 12, label: 'Estado o jurisdicción', }), Plot.ruleX([ctc_promedio]), Plot.barX(thedata, { x: 'credito_menor_dependiente_promedio', y: 'state', // fill: EA_COLORES_MAP['INDIGO'], fill: d => d.state === 'Puerto Rico' ? EA_COLORES_MAP['INDIGO'] : '#cdd1ec', // opacity: d => d['País'] === 'Puerto Rico (2023' sort: {y: "x", reverse: true}}), // Plot.ruleX([credito_promedio]), Plot.text(thedata, { fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', fontSize: 14.5, x: 'credito_menor_dependiente_promedio', y: 'state', fill: d => d.state === 'Puerto Rico' ? 'white' : 'black', text: d => d3.format('$,.0f')(d['credito_menor_dependiente_promedio']), dx: -5, textAnchor: 'end', sort: {y: "x", reverse: true}}), Plot.text([0], { x: ctc_promedio, dx: 5, y: d => 'New York', text: d => `Promedio\n${d3.format('$,.0f')(ctc_promedio)}`, textAnchor: 'start', lineAnchor: 'top', fontFamily: 'jaf-bernino-sans', fontWeight: 'bold', fontSize: 13, }), ] }) // console.log('ugh', theplot) add_estilos(theplot, 'somebar') return theplot }32:Tb32,grafica_nap = { let thedata = pan_snap_total .filter(d => d.cost_per_household !== null) // .filter(d => d.state !== undefined) // .filter(d => d['Año contributivo'] === year_select); let promedio = aq.from(thedata) .rollup({ 'promedio': op.mean('cost_per_household') }) .objects()[0]['promedio'] let theplot = Plot.plot({ caption: htl.html`Fuentes: (FY24) United States Department of Agriculture (USDA). "SNAP Data Tables"; (FY24) Departmento de la Familia de Puerto Rico. Análisis por Espacios Abiertos.`, marginLeft: 130, marginRight: 50, marginBottom: 50, height: 1500, // caption: 'ACTUALIZAR MOVER XAXIS LABEL AL CENTRO', // x: {percent: true,}, color: { legend: true, domain: ['PAN (Block Grant)', 'SNAP', 'SNAP en jurisdicciones no contiguas a Estados Unidos continental' // 'Federal - proyectado', 'Local - proyectado' ], range: [EA_COLORES_MAP['INDIGO'], '#cdd1ec', '#ff9f9c' ] }, marks: [ Plot.axisX({ fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, fontSize: 12, tickFormat: '$,.0f', label: 'PAN / SNAP - Beneficio mensual promedio por hogar', }), Plot.axisY({ fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 12, // label: 'Estado o jurisdicción', label: null, }), Plot.ruleX([promedio]), Plot.barX(thedata, { x: 'cost_per_household', y: 'state', // fill: EA_COLORES_MAP['INDIGO'], fill: d => d.state === 'Puerto Rico' ? EA_COLORES_MAP['INDIGO'] : ( non_contiguous_states.includes(d.state) ? '#ff9f9c' : '#cdd1ec'), // opacity: d => d['País'] === 'Puerto Rico (2023' sort: {y: "x", reverse: true}}), // Plot.ruleX([credito_promedio]), Plot.text(thedata, { fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', fontSize: 14.5, x: 'cost_per_household', y: 'state', fill: d => (d.state === 'Puerto Rico') ? 'white' : 'black', text: d => d3.format('$,.0f')(d['cost_per_household']), dx: -5, textAnchor: 'end', sort: {y: "x", reverse: true}}), Plot.text([0], { x: promedio, dx: 5, y: d => 'New York', text: d => `Promedio\n${d3.format('$,.0f')(promedio)}`, textAnchor: 'start', lineAnchor: 'top', fontFamily: 'jaf-bernino-sans', fontWeight: 'bold', fontSize: 13, }), ] }) // console.log('ugh', theplot) // add_estilos(theplot, 'somebar') return theplot }33:Td24,grafica_nap_blog = { let thedata = pan_snap_total .filter(d => d.cost_per_household !== null) // .filter(d => d.state !== undefined) // .filter(d => d['Año contributivo'] === year_select); let promedio = aq.from(thedata) .rollup({ 'promedio': op.mean('cost_per_household') }) .objects()[0]['promedio'] let theplot = Plot.plot({ style: 'overflow: visible', caption: htl.html`Fuentes: (FY24) United States Department of Agriculture (USDA). "SNAP Data Tables"; (FY24) USDA, "Puerto Rico Nutrition Assistance Program". Análisis por Espacios Abiertos.`, marginLeft: 130, marginRight: 50, marginBottom: 50, height: 1500, // caption: 'ACTUALIZAR MOVER XAXIS LABEL AL CENTRO', // x: {percent: true,}, color: { legend: true, domain: ['PAN (Block Grant)', 'SNAP', 'SNAP en jurisdicciones no contiguas a Estados Unidos continental' // 'Federal - proyectado', 'Local - proyectado' ], range: [EA_COLORES_MAP['INDIGO'], '#cdd1ec', '#ff9f9c' ] }, marks: [ Plot.axisX({ fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, fontSize: 12, tickFormat: '$,.0f', label: 'PAN / SNAP - Beneficio mensual promedio por hogar', }), Plot.axisY({ fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 12, // label: 'Estado o jurisdicción', label: null, }), Plot.ruleX([promedio]), Plot.barX(thedata, { x: 'cost_per_household', y: 'state', // fill: EA_COLORES_MAP['INDIGO'], fill: d => d.state === 'Puerto Rico' ? EA_COLORES_MAP['INDIGO'] : ( non_contiguous_states.includes(d.state) ? '#ff9f9c' : '#cdd1ec'), // opacity: d => d['País'] === 'Puerto Rico (2023' sort: {y: "x", reverse: true}}), // Plot.ruleX([credito_promedio]), Plot.text(thedata, { fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', fontSize: 14.5, x: 'cost_per_household', y: 'state', fill: d => (d.state === 'Puerto Rico') ? 'white' : 'black', text: d => d3.format('$,.0f')(d['cost_per_household']), dx: -5, textAnchor: 'end', sort: {y: "x", reverse: true}}), Plot.text([0], { x: promedio, dx: 5, y: d => 'New York', text: d => `Promedio\n${d3.format('$,.0f')(promedio)}`, textAnchor: 'start', lineAnchor: 'top', fontFamily: 'jaf-bernino-sans', fontWeight: 'bold', fontSize: 13, }), Plot.text([0], { x: promedio, dx: 5, y: d => 'Michigan', text: d => `Las jurisdicciones no contiguas a\nEstados Unidos continental\naumentan sus beneficios del SNAP\ndel 64% a 145% en comparación\ncon la subvención promedio del\nNAP de Puerto Rico.`, fill: '#d10074', textAnchor: 'start', lineAnchor: 'top', fontFamily: 'jaf-bernina-sans-comp', fontWeight: 'bold', fontSize: 30, }), ] }) // console.log('ugh', theplot) // add_estilos(theplot, 'somebar') return theplot }34:Tab9,grafica_nap_english = { let thedata = pan_snap_total .filter(d => d.cost_per_household !== null) // .filter(d => d.state !== undefined) // .filter(d => d['Año contributivo'] === year_select); let promedio = aq.from(thedata) .rollup({ 'promedio': op.mean('cost_per_household') }) .objects()[0]['promedio'] thedata = aq.from(thedata) .orderby(aq.desc('cost_per_household')) .objects() // .slice(0, 27) let theplot = Plot.plot({ caption: htl.html`Sources: (FY24) United States Department of Agriculture (USDA). "SNAP Data Tables"; (FY24) Department of the Family of Puerto Rico. Analysis by Espacios Abiertos.`, marginTop: 0, marginLeft: 160, marginRight: 50, marginBottom: 50, height: 1500, // caption: 'ACTUALIZAR MOVER XAXIS LABEL AL CENTRO', // x: {percent: true,}, color: { legend: true, domain: ['NAP (Block Grant)', 'SNAP', 'SNAP in non-contiguous jurisdictions' // 'Federal - proyectado', 'Local - proyectado' ], range: [EA_COLORES_MAP['INDIGO'], '#cdd1ec', '#ff9f9c' ] }, marks: [ Plot.axisX({ fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, fontSize: 16, tickFormat: '$,.0f', label: 'NAP / SNAP - Average monthly benefit per home', }), Plot.axisY({ fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 16, // label: 'Estado o jurisdicción', label: null, }), Plot.ruleX([promedio]), Plot.barX(thedata, { x: 'cost_per_household', y: 'state', // fill: EA_COLORES_MAP['INDIGO'], fill: d => d.state === 'Puerto Rico' ? EA_COLORES_MAP['INDIGO'] : ( non_contiguous_states.includes(d.state) ? '#ff9f9c' : '#cdd1ec'), // opacity: d => d['País'] === 'Puerto Rico (2023' sort: {y: "x", reverse: true}}), // Plot.ruleX([credito_promedio]), Plot.text(thedata, { fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', fontSize: 16, x: 'cost_per_household', y: 'state', fill: d => (d.state === 'Puerto Rico' ) ? 'white' : 'black', text: d => d3.format('$,.0f')(d['cost_per_household']), dx: -5, textAnchor: 'end', sort: {y: "x", reverse: true}}), Plot.text([0], { x: promedio, dx: 5, y: d => 'New York', text: d => `Average\n${d3.format('$,.0f')(promedio)}`, textAnchor: 'start', lineAnchor: 'top', fontFamily: 'jaf-bernino-sans', fontWeight: 'bold', fontSize: 16, }), ] }) // console.log('ugh', theplot) // add_estilos(theplot, 'somebar') return theplot }35:Td64,grafica_nap_english_blog = { let thedata = pan_snap_total .filter(d => d.cost_per_household !== null) // .filter(d => d.state !== undefined) // .filter(d => d['Año contributivo'] === year_select); let promedio = aq.from(thedata) .rollup({ 'promedio': op.mean('cost_per_household') }) .objects()[0]['promedio'] thedata = aq.from(thedata) .orderby(aq.desc('cost_per_household')) .objects() // .slice(0, 27) let theplot = Plot.plot({ style: 'overflow: visible', caption: htl.html`Sources: (FY24) United States Department of Agriculture (USDA). "SNAP Data Tables"; (FY24) USDA, "Puerto Rico Nutrition Assistance Program". Analysis by Espacios Abiertos.`, marginTop: 0, marginLeft: 160, marginRight: 50, marginBottom: 50, height: 1500, // caption: 'ACTUALIZAR MOVER XAXIS LABEL AL CENTRO', // x: {percent: true,}, color: { legend: true, domain: ['NAP (Block Grant)', 'SNAP', 'SNAP in non-contiguous jurisdictions' // 'Federal - proyectado', 'Local - proyectado' ], range: [EA_COLORES_MAP['INDIGO'], '#cdd1ec', '#ff9f9c' ] }, marks: [ Plot.axisX({ fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, fontSize: 16, tickFormat: '$,.0f', label: 'NAP / SNAP - Average monthly benefit per home', }), Plot.axisY({ fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 16, // label: 'Estado o jurisdicción', label: null, }), Plot.ruleX([promedio]), Plot.barX(thedata, { x: 'cost_per_household', y: 'state', // fill: EA_COLORES_MAP['INDIGO'], fill: d => d.state === 'Puerto Rico' ? EA_COLORES_MAP['INDIGO'] : ( non_contiguous_states.includes(d.state) ? '#ff9f9c' : '#cdd1ec'), // opacity: d => d['País'] === 'Puerto Rico (2023' sort: {y: "x", reverse: true}}), // Plot.ruleX([credito_promedio]), Plot.text(thedata, { fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', fontSize: 16, x: 'cost_per_household', y: 'state', fill: d => (d.state === 'Puerto Rico' ) ? 'white' : 'black', text: d => d3.format('$,.0f')(d['cost_per_household']), dx: -5, textAnchor: 'end', sort: {y: "x", reverse: true}}), Plot.text([0], { x: promedio, dx: 5, y: d => 'New York', text: d => `Average\n${d3.format('$,.0f')(promedio)}`, textAnchor: 'start', lineAnchor: 'top', fontFamily: 'jaf-bernino-sans', fontWeight: 'bold', fontSize: 16, }), Plot.text([0], { x: promedio, dx: 5, y: d => 'Maryland', text: d => `Non-contiguous\njurisdictions to the\ncontinental U.S. increase\ntheir SNAP benefits from\n64% to 145% compared to\nPuerto Rico's NAP block\ngrant.`, fill: '#d10074', textAnchor: 'start', lineAnchor: 'top', fontFamily: 'jaf-bernina-sans-comp', fontWeight: 'bold', fontSize: 30, }), ] }) // console.log('ugh', theplot) // add_estilos(theplot, 'somebar') return theplot }36:Tbf5,{ let thedata = pan_snap_total .filter(d => d.cost_per_household !== null) // .filter(d => d.state !== undefined) // .filter(d => d['Año contributivo'] === year_select); let promedio = aq.from(thedata) .rollup({ 'promedio': op.mean('cost_per_household') }) .objects()[0]['promedio'] thedata = aq.from(thedata) .orderby(aq.desc('cost_per_household')) .objects() // .slice(0, 27) let theplot = Plot.plot({ caption: htl.html`Sources: (FY24) United States Department of Agriculture (USDA). "SNAP Data Tables" https://www.fns.usda.gov/pd/supplemental-nutrition-assistance-program-snap; (FY24) Department of the Family of Puerto Rico https://fns-prod.azureedge.us/sites/default/files/resource-files/pr-nap-data-1.pdf. Analysis by Espacios Abiertos.`, // marginTop: 0, marginLeft: 130, marginRight: 50, marginBottom: 160, marginTop: 40, width: 1200, height: 500, // caption: 'ACTUALIZAR MOVER XAXIS LABEL AL CENTRO', // x: {percent: true,}, color: { legend: true, domain: ['NAP (Block Grant)', 'SNAP', // 'Federal - proyectado', 'Local - proyectado' ], range: [EA_COLORES_MAP['INDIGO'], '#cdd1ec', ] }, marks: [ Plot.axisY({ fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, fontSize: 16, tickFormat: '$,.0f', label: 'NAP / SNAP - Average monthly cost per home', }), Plot.axisX({ fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 16, // label: 'Estado o jurisdicción', label: null, tickRotate: -90, }), Plot.ruleY([promedio]), Plot.barY(thedata, { y: 'cost_per_household', x: 'state', // fill: EA_COLORES_MAP['INDIGO'], fill: d => d.state === 'Puerto Rico' ? EA_COLORES_MAP['INDIGO'] : '#cdd1ec', // opacity: d => d['País'] === 'Puerto Rico (2023' sort: {x: "y", reverse: true}}), // Plot.ruleX([credito_promedio]), Plot.text(thedata, { fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', fontSize: 16, rotate: -90, y: 'cost_per_household', x: 'state', fill: d => d.state === 'Puerto Rico' ? 'white' : 'black', text: d => d3.format('$,.0f')(d['cost_per_household']), dy: 5, textAnchor: 'end', sort: {x: "y", reverse: true}}), Plot.text([0], { y: promedio, dy: -5, x: d => 'Maine', text: d => `Average\n${d3.format('$,.0f')(promedio)}`, textAnchor: 'end', lineAnchor: 'bottom', fontFamily: 'jaf-bernino-sans', fontWeight: 'bold', fontSize: 16, }), ] }) // console.log('ugh', theplot) add_estilos(theplot, 'somebar') return theplot }37:Td4c,grafica_medicaid = { let thedata = medicaid_data_by_state .filter(d => d.total_medicaid_enrollment !== null) // .filter(d => d.state !== undefined) // .filter(d => d['Año contributivo'] === year_select); const variable_name = 'benefits_total_per_person' let promedio = aq.from(thedata) .rollup({ 'promedio': op.mean(variable_name) }) .objects()[0]['promedio'] let theplot = Plot.plot({ style: 'figcaption { font-size: 1.23rem }', // caption: htl.html`Fuentes: (FY24) United States Department of Agriculture (USDA). "SNAP Data Tables"; (FY24) Departamento de la Familia de Puerto Rico. Análisis por Espacios Abiertos.`, marginLeft: 130, marginRight: 50, marginBottom: 50, height: 1500, caption: htl.html`Fuentes: (FY23) Medicaid and CHIP Payment and Access Commission (MACPAC). "EXHIBIT 16. Medicaid Spending by State, Category, and Source of Funds". (junio de 2023) Medicaid. "State Medicaid and CHIP Applications, Eligibility Determinations, and Enrollment Data". (2023) Departamento de Salud de Puerto Rico. "2024 Annual Report to Congress Public Law 117-328: Consolidated Appropriations Act, 2023". Análisis por Espacios Abiertos.`, // x: {percent: true,}, // color: { // legend: true, // domain: ['PAN', 'SNAP', // // 'Federal - proyectado', 'Local - proyectado' // ], // range: [EA_COLORES_MAP['INDIGO'], // '#cdd1ec', // ] // }, marks: [ Plot.axisX({ fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, fontSize: 12, tickFormat: '$,.0f', label: 'Medicaid - Beneficio promedio por persona', }), Plot.axisY({ fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 12, // label: 'Estado o jurisdicción', label: null, }), Plot.ruleX([promedio]), Plot.barX(thedata, { x: variable_name, y: 'state', // fill: EA_COLORES_MAP['INDIGO'], fill: d => d.state === 'Puerto Rico' ? EA_COLORES_MAP['INDIGO'] : '#cdd1ec', // opacity: d => d['País'] === 'Puerto Rico (2023' sort: {y: "x", reverse: true}}), // Plot.ruleX([credito_promedio]), Plot.text(thedata, { fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', fontSize: 14.5, x: variable_name, y: 'state', fill: d => d.state === 'Puerto Rico' ? 'white' : 'black', text: d => d3.format('$,.0f')(d[variable_name]), dx: -5, textAnchor: 'end', sort: {y: "x", reverse: true}}), Plot.text([0], { x: promedio, dx: 5, y: d => 'Nevada', text: d => `Promedio\n${d3.format('$,.0f')(promedio)}`, textAnchor: 'start', lineAnchor: 'top', fontFamily: 'jaf-bernino-sans', fontWeight: 'bold', fontSize: 13, }), ] }) // console.log('ugh', theplot) // add_estilos(theplot, 'somebar') return theplot }38:T7d7,{ let thedata = wic_average_food_cost // .filter(d => d.cost_per_household !== null) // .filter(d => d.state !== undefined) // .filter(d => d['Año contributivo'] === year_select); let promedio = aq.from(thedata) .rollup({ 'promedio': op.mean('FY2023') }) .objects()[0]['promedio'] let theplot = Plot.plot({ marginLeft: 150, marginRight: 50, marginBottom: 50, height: 1500, // caption: 'ACTUALIZAR MOVER XAXIS LABEL AL CENTRO', // x: {percent: true,}, marks: [ Plot.axisX({ fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, fontSize: 12, tickFormat: '$,.0f', label: 'WIC - Costo mensual promedio de alimentos por persona', }), Plot.axisY({ fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 12, label: 'Estado o jurisdicción', }), Plot.ruleX([promedio]), Plot.barX(thedata, { x: 'FY2023', y: 'state', // fill: EA_COLORES_MAP['INDIGO'], fill: d => d.state === 'Puerto Rico' ? EA_COLORES_MAP['INDIGO'] : '#cdd1ec', // opacity: d => d['País'] === 'Puerto Rico (2023' sort: {y: "x", reverse: true}}), // Plot.ruleX([credito_promedio]), Plot.text(thedata, { fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', fontSize: 14.5, x: 'FY2023', y: 'state', fill: d => d.state === 'Puerto Rico' ? 'white' : 'black', text: d => d3.format('$,.0f')(d['FY2023']), dx: -5, textAnchor: 'end', sort: {y: "x", reverse: true}}), Plot.text([0], { x: promedio, dx: 5, y: d => 'New Mexico', text: d => `Promedio\n${d3.format('$,.0f')(promedio)}`, textAnchor: 'start', lineAnchor: 'top', fontFamily: 'jaf-bernino-sans', fontWeight: 'bold', fontSize: 13, }), ] }) // console.log('ugh', theplot) add_estilos(theplot, 'somebar') return theplot }39:Tc2c,grafica_ssi = { let thedata = [...ssi_by_state, {state: 'Puerto Rico', average_payment_per_recipient: 0}] // .filter(d => d.cost_per_household !== null) // .filter(d => d.state !== undefined) // .filter(d => d['Año contributivo'] === year_select); let promedio = aq.from(thedata) .rollup({ 'promedio': op.mean('average_payment_per_recipient') }) .objects()[0]['promedio'] let theplot = Plot.plot({ marginLeft: 150, marginRight: 50, marginBottom: 50, height: 1500, caption: htl.html`Fuentes: (2023) Social Security Administration (SSA). "SSI Recipients by State and County, 2023: Tables 1 and 2". Análisis por Espacios Abiertos.

Nota: Puerto Rico recibe fondos federales ($29.4M en FY2023) para costear un programa de asistencia a personas de edad avanzada, ciegas y/o incapacitadas (AABD). Sin embargo, se estima que, bajo el SSI, los participantes en Puerto Rico recibirían sobre $2.5 mil millones anualmente.`, // x: {percent: true,}, marks: [ Plot.axisX({ fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, fontSize: 12, tickFormat: '$,.0f', label: 'SSI - Costo promedio por persona', }), Plot.axisY({ fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 12, label: 'Estado o jurisdicción', }), Plot.ruleX([promedio]), Plot.barX(thedata, { x: 'average_payment_per_recipient', y: 'state', // fill: EA_COLORES_MAP['INDIGO'], fill: d => d.state === 'Puerto Rico' ? EA_COLORES_MAP['INDIGO'] : '#cdd1ec', // opacity: d => d['País'] === 'Puerto Rico (2023' sort: {y: "x", reverse: true}}), // Plot.ruleX([credito_promedio]), Plot.text(thedata, { filter: d => d.state !== 'Puerto Rico', fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', fontSize: 14.5, x: 'average_payment_per_recipient', y: 'state', fill: d => d.state === 'Puerto Rico' ? 'white' : 'black', text: d => d3.format('$,.0f')(d['average_payment_per_recipient']), dx: -5, textAnchor: 'end', sort: {y: "x", reverse: true}}), Plot.text(thedata, { filter: d => d.state === 'Puerto Rico', fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', fontSize: 14.5, x: 'average_payment_per_recipient', y: 'state', fill: 'black', text: d => d3.format('$,.0f')(d['average_payment_per_recipient']), dx: 0, textAnchor: 'start', sort: {y: "x", reverse: true}}), Plot.text([0], { x: promedio, dx: 5, y: d => 'New Mexico', text: d => `Promedio\n${d3.format('$,.0f')(promedio)}`, textAnchor: 'start', lineAnchor: 'top', fontFamily: 'jaf-bernino-sans', fontWeight: 'bold', fontSize: 13, }), ] }) // console.log('ugh', theplot) // add_estilos(theplot, 'somebar') return theplot }3a:Tc54,grafica_medicare = { let thedata = medicare_by_state .filter(d => d.total_medicaid_enrollment !== null) // .filter(d => d.state !== undefined) // .filter(d => d['Año contributivo'] === year_select); thedata = aq.from(thedata) .derive({'abbreviation': d => d.state}) .select(aq.not('state')) .join_left(aq.from(state_abbreviations), 'abbreviation') .objects(); thedata.forEach(d => { if (d.abbreviation === 'VI') d.state = 'Virgin Islands' }) const variable_name = 'standardized_per_capita_medicare_payment' let promedio = aq.from(thedata) .rollup({ 'promedio': op.mean(variable_name) }) .objects()[0]['promedio'] let theplot = Plot.plot({ // caption: htl.html`Fuentes: (FY24) United States Department of Agriculture (USDA). "SNAP Data Tables"; (FY24) Departamento de la Familia de Puerto Rico. Análisis por Espacios Abiertos.`, marginLeft: 130, marginRight: 50, marginBottom: 50, height: 1500, caption: htl.html`Fuentes: (2022) Centers for Medicare & Medicaid Services (CMS). "Medicare Geographic Variation - by National, State & County". Análisis por Espacios Abiertos.`, // x: {percent: true,}, // color: { // legend: true, // domain: ['PAN', 'SNAP', // // 'Federal - proyectado', 'Local - proyectado' // ], // range: [EA_COLORES_MAP['INDIGO'], // '#cdd1ec', // ] // }, marks: [ Plot.axisX({ fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, fontSize: 12, tickFormat: '$,.0f', label: 'Medicare - Beneficio estandardizado per capita', }), Plot.axisY({ fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 12, // label: 'Estado o jurisdicción', label: null, }), Plot.ruleX([promedio]), Plot.barX(thedata, { x: variable_name, y: 'state', // fill: EA_COLORES_MAP['INDIGO'], fill: d => d.state === 'Puerto Rico' ? EA_COLORES_MAP['INDIGO'] : '#cdd1ec', // opacity: d => d['País'] === 'Puerto Rico (2023' sort: {y: "x", reverse: true}}), // Plot.ruleX([credito_promedio]), Plot.text(thedata, { fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', fontSize: 14.5, x: variable_name, y: 'state', fill: d => d.state === 'Puerto Rico' ? 'white' : 'black', text: d => d3.format('$,.0f')(d[variable_name]), dx: -5, textAnchor: 'end', sort: {y: "x", reverse: true}}), Plot.text([0], { x: promedio, dx: 5, y: d => 'Puerto Rico', text: d => `Promedio\n${d3.format('$,.0f')(promedio)}`, textAnchor: 'start', lineAnchor: 'top', fontFamily: 'jaf-bernino-sans', fontWeight: 'bold', fontSize: 13, }), ] }) // console.log('ugh', theplot) // add_estilos(theplot, 'somebar') return theplot }3b:Tb9d,{ let thedata = tanf_data // .filter(d => d.state !== undefined) // .filter(d => d['Año contributivo'] === year_select); const variable_name = 'monthly_cost_per_family' let promedio = aq.from(thedata) .rollup({ 'promedio': op.mean(variable_name) }) .objects()[0]['promedio'] let theplot = Plot.plot({ // caption: htl.html`Fuentes: (FY24) United States Department of Agriculture (USDA). "SNAP Data Tables"; (FY24) Departamento de la Familia de Puerto Rico. Análisis por Espacios Abiertos.`, marginLeft: 130, marginRight: 50, marginBottom: 50, height: 1500, // caption: 'ACTUALIZAR MOVER XAXIS LABEL AL CENTRO', // x: {percent: true,}, // color: { // legend: true, // domain: ['PAN', 'SNAP', // // 'Federal - proyectado', 'Local - proyectado' // ], // range: [EA_COLORES_MAP['INDIGO'], // '#cdd1ec', // ] // }, marks: [ Plot.axisX({ fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', tickSize: 0, fontSize: 12, tickFormat: '$,.0f', label: 'TANF - Costo mensual promedio por familia', }), Plot.axisY({ fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 12, // label: 'Estado o jurisdicción', label: null, }), Plot.ruleX([promedio]), Plot.barX(thedata, { x: variable_name, y: 'state', // fill: EA_COLORES_MAP['INDIGO'], fill: d => d.state === 'Puerto Rico' ? EA_COLORES_MAP['INDIGO'] : '#cdd1ec', // opacity: d => d['País'] === 'Puerto Rico (2023' sort: {y: "x", reverse: true}}), // Plot.ruleX([credito_promedio]), Plot.text(thedata, { filter: d => d[variable_name] >= 1e3, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', fontSize: 14.5, x: variable_name, y: 'state', fill: d => d.state === 'Puerto Rico' ? 'white' : 'black', text: d => d3.format('$,.0f')(d[variable_name]), dx: -5, textAnchor: 'end', sort: {y: "x", reverse: true}}), Plot.text(thedata, { filter: d => d[variable_name] < 1e3, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', fontWeight: 'bold', fontSize: 14.5, fill: 'black', x: variable_name, y: 'state', fill: d => d.state === 'Puerto Rico' ? 'white' : 'black', text: d => d3.format('$,.0f')(d[variable_name]), dx: 5, textAnchor: 'start', sort: {y: "x", reverse: true}}), Plot.text([0], { x: promedio, dx: 5, y: d => 'Rhode Island', text: d => `Promedio\n${d3.format('$,.0f')(promedio)}`, textAnchor: 'start', lineAnchor: 'top', fontFamily: 'jaf-bernino-sans', fontWeight: 'bold', fontSize: 13, }), ] }) // console.log('ugh', theplot) add_estilos(theplot, 'somebar') return theplot }3c:T74f,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 }3d:T7bb,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; }3e: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(); } } }; } }3f: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(); } }40:[{"id":"c141dc0e98bfe6b61c22cb8625188fe06fc583bf2af3caa2d3fcc97d3d816b433e032005e3d4983edac2539f9cb71335c0608639aa5673955d83a3e21081d955","href":"https://static.observableusercontent.com/files/c141dc0e98bfe6b61c22cb8625188fe06fc583bf2af3caa2d3fcc97d3d816b433e032005e3d4983edac2539f9cb71335c0608639aa5673955d83a3e21081d955","name":"ssi_table1_fy23.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1741278176911.604,"size":14251},{"id":"e20f3c69e9cd6782b091cd4ff77bb6cf71d8408a9b2de98c06d4c9a00d35a0479b2b336267255ddb32ee905eb7cadff6a9493e601344a55bac08d44acc445d88","href":"https://static.observableusercontent.com/files/e20f3c69e9cd6782b091cd4ff77bb6cf71d8408a9b2de98c06d4c9a00d35a0479b2b336267255ddb32ee905eb7cadff6a9493e601344a55bac08d44acc445d88","name":"ssi_table2_fy23.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1741278176949.897,"size":13851},{"id":"9c948d0a1c2174b3e317d0d52364def4cdcf3529cf431f6d66ffea290e860b7b6324c43cabd7394cf268e6014dd47dbd5e5a49198d51fe024724049d4a36d477","href":"https://static.observableusercontent.com/files/9c948d0a1c2174b3e317d0d52364def4cdcf3529cf431f6d66ffea290e860b7b6324c43cabd7394cf268e6014dd47dbd5e5a49198d51fe024724049d4a36d477","name":"medicaid_spending_FY23.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1741026845345.346,"size":13175},{"id":"75e366f04c669acaaf82a735883252f33a4ff8dff14714bd584e6df7ddf2b1ae0eb29ee733f2d81cdf2270382a73fd098573ac9569cce6245af28da77e129d35","href":"https://static.observableusercontent.com/files/75e366f04c669acaaf82a735883252f33a4ff8dff14714bd584e6df7ddf2b1ae0eb29ee733f2d81cdf2270382a73fd098573ac9569cce6245af28da77e129d35","name":"fy2023_tanf_caseload_fy23_families.csv","type":"text/csv","lastModified":1741888618510.679,"size":1757},{"id":"a9f88b54e9322e2cfef038f094fcc0bc0f58f458f73bf657033db2ec91a9afa2ed0e5ddb7b848713779fe2c0c2b7301896443f023e00143fea907b9130dc0514","href":"https://static.observableusercontent.com/files/a9f88b54e9322e2cfef038f094fcc0bc0f58f458f73bf657033db2ec91a9afa2ed0e5ddb7b848713779fe2c0c2b7301896443f023e00143fea907b9130dc0514","name":"states_abbreviations.csv","type":"text/csv","lastModified":1740077848229.442,"size":892},{"id":"27d6a4d18f5cc24684331a0583a7bcd07ae70d6cc51234b93b0818f7b931b5794276c473ffc45b15880fba5f56df47921c8ac2a8a9e079530b2629abcb5fcb72","href":"https://static.observableusercontent.com/files/27d6a4d18f5cc24684331a0583a7bcd07ae70d6cc51234b93b0818f7b931b5794276c473ffc45b15880fba5f56df47921c8ac2a8a9e079530b2629abcb5fcb72","name":"iris.csv","type":"text/csv","lastModified":1740077319383.921,"size":2734},{"id":"c76bec5ad5baa31ccc846a5afdb1756d7bba2bba172cb9ca2306673e21ae607f83f6e9efaa93c253dd3c0aaf4a1a21b6497d7713235ddac8a1b63adfc5e881da","href":"https://static.observableusercontent.com/files/c76bec5ad5baa31ccc846a5afdb1756d7bba2bba172cb9ca2306673e21ae607f83f6e9efaa93c253dd3c0aaf4a1a21b6497d7713235ddac8a1b63adfc5e881da","name":"penguins.csv","type":"text/csv","lastModified":1740077319383.921,"size":13478},{"id":"6a88a7b2d66e468047786efc90e8a5d8a5343324f8c5fb05c4d9884ba5c7782799d93fa64c3e52e4bfc0a61a570001ff0217b021e74745f2c0c3c0fc810fd145","href":"https://static.observableusercontent.com/files/6a88a7b2d66e468047786efc90e8a5d8a5343324f8c5fb05c4d9884ba5c7782799d93fa64c3e52e4bfc0a61a570001ff0217b021e74745f2c0c3c0fc810fd145","name":"snap_by_state_FY24.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1740598925490.778,"size":55506},{"id":"0ee0524ae8e0583d770dad8c3f57f59d3b868f52a3570a09ecde3dbcc3909ac65e13262a05d21d63f4c63d5121b5d137b96223b26d91311b3479474e05d909ab","href":"https://static.observableusercontent.com/files/0ee0524ae8e0583d770dad8c3f57f59d3b868f52a3570a09ecde3dbcc3909ac65e13262a05d21d63f4c63d5121b5d137b96223b26d91311b3479474e05d909ab","name":"pan_FY24.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1740599532041.614,"size":10174},{"id":"6e15b78343f76f7c8059f1baddeb245d5a5d5b34d9135097b6cb01b0063570ed91283b2a0fe073e1cc5d5a8cdadea675b8775c1660b2b05cae77968d1f23d5ab","href":"https://static.observableusercontent.com/files/6e15b78343f76f7c8059f1baddeb245d5a5d5b34d9135097b6cb01b0063570ed91283b2a0fe073e1cc5d5a8cdadea675b8775c1660b2b05cae77968d1f23d5ab","name":"ctc_by_state@1.csv","type":"text/csv","lastModified":1740151542039.327,"size":1883},{"id":"c77863338f7a924f15dd788364f05ba5a108ca1b9eb007a58d644bcab22e589a681fb73d7fe72b21fa63b56b444058925f913a14ce4e6dd36bf63e353ae8dec5","href":"https://static.observableusercontent.com/files/c77863338f7a924f15dd788364f05ba5a108ca1b9eb007a58d644bcab22e589a681fb73d7fe72b21fa63b56b444058925f913a14ce4e6dd36bf63e353ae8dec5","name":"medicaid_enrollment_by_state_fy23.csv","type":"text/csv","lastModified":1741617134336.868,"size":1284},{"id":"ac66c5c8f24c6dc616938f40f03abde445c7235ae0533bcc74d8dcdac081b4f2e486b3e0479908e218c47640544683f809585e964ee005c294d9b91de732423e","href":"https://static.observableusercontent.com/files/ac66c5c8f24c6dc616938f40f03abde445c7235ae0533bcc74d8dcdac081b4f2e486b3e0479908e218c47640544683f809585e964ee005c294d9b91de732423e","name":"wic_average_monthly_food_cost_fy24_preliminary.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1741115938461.57,"size":14359},{"id":"df7a65099a9e1b92240ee445ae39dced09f988a4bbd172a0996ee92f7118ebe05d6834a26869b3112d60fb0de34ce05506887d5afeb0871d70b7f7cd6338c2f3","href":"https://static.observableusercontent.com/files/df7a65099a9e1b92240ee445ae39dced09f988a4bbd172a0996ee92f7118ebe05d6834a26869b3112d60fb0de34ce05506887d5afeb0871d70b7f7cd6338c2f3","name":"wic_average_monthly_food_cost_fy24_preliminary@1.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1741116452687.81,"size":14363},{"id":"f50daeed92c4dd56fdbeba779a995e418d4191442e0003d08f4921896ec8ee84229e693858b7a767cefe37825cb142745fe0e16993f8176db49d7811cc5d7698","href":"https://static.observableusercontent.com/files/f50daeed92c4dd56fdbeba779a995e418d4191442e0003d08f4921896ec8ee84229e693858b7a767cefe37825cb142745fe0e16993f8176db49d7811cc5d7698","name":"medicare_by_state_2022.csv","type":"text/csv","lastModified":1741633197846.432,"size":3402},{"id":"900447def3cc197d4c53d0489793d3ce91995409be1f2d0593a9a6a6c550d76b78d1ee21435dbce9b9b7b2fe0e7864d94191e6a46c0356bd150b95ed38d88910","href":"https://static.observableusercontent.com/files/900447def3cc197d4c53d0489793d3ce91995409be1f2d0593a9a6a6c550d76b78d1ee21435dbce9b9b7b2fe0e7864d94191e6a46c0356bd150b95ed38d88910","name":"pan_FY24@1.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1743522093169.742,"size":10194},{"id":"3cefe34c243993b0881a7d888936f5db2e6bd5f2bf949e864be19b8b5d848af8712de785378064e67c0a53aea486f35e8ed16682d13d7f77f229551a4414f514","href":"https://static.observableusercontent.com/files/3cefe34c243993b0881a7d888936f5db2e6bd5f2bf949e864be19b8b5d848af8712de785378064e67c0a53aea486f35e8ed16682d13d7f77f229551a4414f514","name":"fy2023_tanf_table_A6.csv","type":"text/csv","lastModified":1741725415007.704,"size":1771}] 2f:{"id":"cde2edfd051a4857","slug":null,"title":"Programas de asistencia","thumbnail":"0a0e54471286a0d6049d911bc40074e4cd70b24558702ab70f14c4ab5617c254","access_level":"public","create_time":"$D2025-02-20T18:48:39.383Z","update_time":"$D2025-04-01T15:47:35.461Z","view_time":null,"first_public_time":"$D2025-02-26T17:40:29.240Z","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":551,"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":4,"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":"Programas de asistencia","cells":[{"id":0,"value":"# Programas de asistencia\n\nPara comparar los desembolsos de distintos programas de asistencia a lo largo de los estados y Puerto Rico","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":44,"value":"select * from ctc_by_state\n-- select * from iris","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"ctc_by_state","database":"var:client"},{"id":116,"value":"$31","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":101,"value":"ctc_by_state_with_names = {\n return aq.from(ctc_by_state)\n .rename({'state': 'abbreviation'})\n .join_left(aq.from(state_abbreviations), 'abbreviation')\n .objects()\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":220,"value":"TODO: Leyenda dos colores: Azul oscuro PAN, Azul clarito SNAP\n\nTODO: Ingles\n\nTODO: caption de \"Sources: USDA... la de SNAP y Dept de Familia la de PAN. Y analisis de Espacios Abiertos...\"","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":473,"value":"non_contiguous_states = ['Alaska', 'Guam', 'Hawaii', 'Virgin Islands',]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":171,"value":"$32","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":530,"value":"$33","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":235,"value":"$34","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":508,"value":"$35","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":389,"value":"$36","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":240,"value":"pan_snap_total\n .filter(d => d.cost_per_household !== null)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":166,"value":"with pan_snap as (\n from pan\n union all by name\n from snap_by_state\n)\n\nfrom pan_snap\nwhere fiscal_year_month = 'Total'","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"pan_snap_total","database":"var:client"},{"id":181,"value":"select *,\n benefits_federal / benefits_total as benefits_federal_pct,\nfrom medicaid_spending","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:client"},{"id":303,"value":"$37","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":292,"value":"with medicaid_enrollment_with_puerto_rico as (\n from medicaid_enrollment_by_state\n union all by name\n (select 'Puerto Rico' as state, 1_400_000 as total_medicaid_enrollment)\n),\n\nmedicaid_spending_expanded as (\n select *,\n benefits_federal / benefits_total as benefits_federal_pct,\n from medicaid_spending\n),\n\nspending_enrollment_joined as (\n from medicaid_enrollment_with_puerto_rico\n full join medicaid_spending_expanded\n using (state)\n)\n\nselect *,\n benefits_total * 1e6 / total_medicaid_enrollment as benefits_total_per_person,\nfrom spending_enrollment_joined","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"medicaid_data_by_state","database":"var:client"},{"id":196,"value":"wic_average_food_cost","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":198,"value":"Object.keys(wic_average_food_cost[0])","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":194,"value":"$38","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":190,"value":"from wic_average_food_cost","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"wic_average_food_cost","database":"var:client"},{"id":273,"value":"$39","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":214,"value":"with ssi_joined as (\nfrom ssi_table1\nfull join ssi_table2\nusing (state)\norder by state\n)\n\nselect *,\n payments_total * 1000 / recipients_total as average_payment_per_recipient, -- Payments estaban en miles\nfrom ssi_joined\nwhere state != 'total'","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"ssi_by_state","database":"var:client"},{"id":318,"value":"$3a","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":314,"value":"from medicare_by_state","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"medicare_by_state","database":"var:client"},{"id":332,"value":"from tanf_table_a6","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"tanf_table_a6","database":"var:client"},{"id":340,"value":"from tanf_caseload","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"tanf_caseload","database":"var:client"},{"id":373,"value":"$3b","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":345,"value":"tanf_data = {\n let tanf_caseload_data = aq.from(tanf_caseload).objects()\n let tanf_table_a6_data = aq.from(tanf_table_a6).objects()\n\n tanf_table_a6_data.forEach(d => {\n if (d.state === 'DIST.OF COLUMBIA') d.state = 'District of Columbia'\n })\n\n tanf_table_a6_data = [...tanf_table_a6_data, \n {state: 'Puerto Rico', transferred_to_ccdf: 0, transferred_to_ssbg: 0, total_federal_expenditures: 100_516_000+33_522_000}\n ]\n\n tanf_table_a6_data = aq.from(tanf_table_a6_data)\n \n .derive({'state': aq.escape(d => toTitleCase(d.state, ['of']))})\n .objects()\n\n // return aq.from(tanf_table_a6_data).view()\n\n return aq.from(tanf_caseload_data)\n .filter(d => d.state !== 'total')\n .join_left(aq.from(tanf_table_a6_data), 'state')\n .filter(d => d.total_federal_expenditures !== undefined)\n .derive({monthly_cost_per_family: d => (d.total_federal_expenditures/12) / d.total_families})\n .objects()\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":379,"value":"(100_516_000+33_522_000)/12 / 2_755 ","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":356,"value":"function toTitleCase(str, minorWords = []) {\n const minorSet = new Set(minorWords.map(word => word.toLowerCase()));\n \n return str\n .toLowerCase()\n .split(' ')\n .map((word, index) => {\n // Always capitalize the first word\n if (index === 0 || !minorSet.has(word)) {\n return word.charAt(0).toUpperCase() + word.slice(1);\n }\n return word;\n })\n .join(' ');\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":86,"value":"# Apéndice","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":88,"value":"state_abbreviations = {\n let data = await state_abbreviations_file.csv({typed: true})\n data = aq.from(data)\n .rename({\n 'State': 'state',\n 'Abbreviation': 'abbreviation',\n \n })\n .objects()\n data.forEach(d => {\n if (d.state === 'District Of Columbia') d.state = 'District of Columbia'\n\n \n })\n // data= [{\n // state: 'Puerto Rico',\n // percentage_of_federal_credit: .767679,\n // refundable: true,\n // }, ...data]\n return data\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":121,"value":"## Estilos","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":125,"value":"fonts_link = \"https://use.typekit.net/szl4srx.css\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":415,"value":"htl.html`\n\n`","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":127,"value":"estilos = htl.html`\n\n`","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":426,"value":"estilos_embed = htl.html`\n\n`","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":123,"value":"estilos_content = d3.select(estilos).select('style').node().textContent","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":130,"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":119,"value":"function add_estilos(theplot, plotname = null) {\n var styleElement = document.createElementNS('http://www.w3.org/2000/svg', 'style');\n styleElement.textContent = fonts_css_content + '\\n' + estilos_content;\n \n let myplot = d3.select(theplot)\n const toplevel_tag = myplot.node().tagName\n // console.log('toplevel_tag', toplevel_tag)\n\n if (toplevel_tag !== 'svg') {\n myplot.selectAll(\"svg\")\n .each((d, i, nodes) => {\n // if (plotname !== null) {\n // console.log('add_estilos for ' + plotname + ': d, i, nodes:', d, i, nodes)\n // }\n d3.select(nodes[i]).node().insertBefore(\n styleElement,\n nodes[i].firstChild,\n );\n \n })\n } else {\n myplot.node().insertBefore(\n styleElement,\n myplot.firstChild,\n );\n }\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":94,"value":"## Extra imports","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":90,"value":"import {EA_COLORES_MAP} from \"22dc6182207c44c8\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":96,"value":"import { aq, op } from '@uwdata/arquero'","pinned":false,"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.29.1-dev68.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":"$3c","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":209,"value":"async function register_buffer(db, file_attachment, file_name) {\n const file_buffer = await file_attachment.arrayBuffer();\n await db.registerFileBuffer(\n file_name, // Choose filename to use inside db\n new Uint8Array(file_buffer)\n )\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":31,"value":"$3d","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":"$3e","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":52,"value":"$3f","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":82,"value":"ctc_by_state_file = FileAttachment(\"ctc_by_state@1.csv\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":84,"value":"state_abbreviations_file = FileAttachment(\"states_abbreviations.csv\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":148,"value":"snap_by_state_file = FileAttachment(\"snap_by_state_FY24.xlsx\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":157,"value":"pan_file = FileAttachment(\"pan_FY24@1.xlsx\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":177,"value":"medicaid_spending_file = FileAttachment(\"medicaid_spending_FY23.xlsx\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":185,"value":"wic_average_food_cost_file = FileAttachment(\"wic_average_monthly_food_cost_fy24_preliminary@1.xlsx\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":205,"value":"ssi_table1_file = FileAttachment(\"ssi_table1_fy23.xlsx\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":207,"value":"ssi_table2_file = FileAttachment(\"ssi_table2_fy23.xlsx\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":284,"value":"medicaid_enrollment_file = FileAttachment(\"medicaid_enrollment_by_state_fy23.csv\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":310,"value":"medicare_by_state_file = FileAttachment(\"medicare_by_state_2022.csv\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":328,"value":"tanf_table_a6_file = FileAttachment(\"fy2023_tanf_table_A6.csv\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":338,"value":"tanf_caseload_file = FileAttachment(\"fy2023_tanf_caseload_fy23_families.csv\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W40","stdlib":"1","titling":"auto"}} 30:"$2f:body"