2b:null 2c:["$","title",null,{"children":"OECD - All countries | José M. Zavala González | Observable"}] 31:T153c,{ let thedata = totals_by_year_data; let thevariable = cbc_variable_interes.filter(d => d.cbc === 'TAX_ACCRUED')[0] const plot_options = cbc_plot_options[thevariable.cbc] const region_list = ['PR', 'US', 'EU', 'OTHER']; let region_map = Object() region_labels.forEach(labels => { region_map[labels.region] = labels }) region_map = structuredClone(region_map); region_map['PR'] = {region_en: 'Current collections from foreign corporations', region_es: 'Recaudos actuales de corporaciones foráneas'} region_map['US']['region_es'] = 'Recaudos adicionales de corporaciones foráneas estadounidenses' region_map['EU']['region_es'] = 'Recaudos adicionales de corporaciones foráneas europeas' region_map['OTHER']['region_es'] = 'Recaudos adicionales de corporaciones foráneas de otros países' region_map['US']['region_en'] = 'Additional collections from US foreign corporations' region_map['EU']['region_en'] = 'Additional collections from European foreign corporations' region_map['OTHER']['region_en'] = "Additional collections from other jurisdictions' foreign corporations" const bar_threshold = 500e6; const outtext_spacing = 250e6; const projections_data = [...pr_iir_projections, ...iir_projections]; const yearly_positive_groups = d3.group( // thedata.filter(d => d[thevariable.cbc] > 0), projections_data.filter(d => (Math.abs(d['IIR']) < (bar_threshold ?? 0)) & (d['IIR'] >= 0)), d => d.time_period); yearly_positive_groups.keys().forEach(time_period => { yearly_positive_groups.set( time_period, d3.sort(yearly_positive_groups.get(time_period), d => -d['IIR']).map(d => d.region) // region_list ) }); const yearly_positive_totals = d3.rollup(projections_data.filter(d => d['IIR'] > 0), v => d3.sum(v, d => d['IIR']), g => g.time_period); console.log('yearly_positive_groups', yearly_positive_groups) console.log('yearly_positive_totals', yearly_positive_totals) const proj_string = lenguaje === 'english' ? 'Projected' : 'Proyectado'; let theplot = Plot.plot({ width: 900, style: 'overflow: visible', marginTop: 40, marginBottom: 40, marginLeft: plot_options?.marginLeft, color: { legend: true, domain: region_list, range: [ EA_COLORES_MAP['INDIGO'], EA_COLORES_MAP['RED'], EA_COLORES_MAP['TEAL'], EA_COLORES_MAP['ORANGE'] ], tickFormat: d => lenguaje === 'english' ? region_map[d].region_en : region_map[d].region_es, }, x: { }, y: { }, marks: [ // Plot.ruleY([0]), Plot.axisX({ // label: lenguaje === 'english' ? 'Year' : 'Año', label: null, interval: 1, tickFormat: d => d3.format('')(d) + (d > 2021 ? '\n'+proj_string : ''), fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 14, fontVariant: 'tabular-nums', }), Plot.axisY({ label: lenguaje === 'english' ? 'Collections in Puerto Rico' : 'Ganancia de recaudos en Puerto Rico', // label: 'Ganancia de recaudos en Puerto Rico', tickFormat: plot_options?.tickFormat, // tickPadding: plot_options?.tickPadding, fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 14, fontVariant: 'tabular-nums', }), Plot.barY(thedata, {x: "time_period", y: thevariable.cbc, fill: EA_COLORES_MAP['INDIGO']}), // Plot.text(thedata, { // x: 'time_period', y: thevariable.cbc, // text: d => ((plot_options?.tickFormat) ?? (e => e))(d[thevariable.cbc]), // dy: -10, // fontWeight: 'bold', // fontSize: 16, // fontFamily: 'jaf-bernino-sans', // fill: 'black', stroke: 'white' // }), Plot.text(thedata, Plot.stackY({ x: 'time_period', y: thevariable.cbc, text: d => ((plot_options?.tickFormat) ?? (e => e))(d[thevariable.cbc]), // dy: -10, fontWeight: 'bold', fontSize: 16, fontFamily: 'jaf-bernino-sans', fill: 'white', //stroke: 'white' })), Plot.barY(projections_data, Plot.stackY({ x: 'time_period', y: 'IIR', z: 'region', fill: 'region', })), Plot.text(projections_data, Plot.stackY({ filter: d => Math.abs(d['IIR']) >= (bar_threshold ?? 0) , x: 'time_period', y: 'IIR', z: 'region', text: d => ((plot_options?.tickFormat) ?? (e => e))(d['IIR']), order: 'value', // dy: -10, fontWeight: 'bold', fontSize: 16, fontFamily: 'jaf-bernino-sans', fill: d => d.region === 'PR' ? 'white' : 'black', //stroke: 'white' })), yearly_positive_groups.size > 0 ? Plot.text(projections_data, { filter: d => (Math.abs(d['IIR']) <= (bar_threshold ?? 0)) & (d['IIR'] >= 0) , x: 'time_period', y: d => yearly_positive_totals.get(d.time_period) + yearly_positive_groups.get(d.time_period)?.indexOf(d.region)*(outtext_spacing ?? 0), dy: -10, // y: d => yearly_positive_totals.get(d.time_period) + 10e9, // y: (d,i) => i === 0 ? 100e9 : 0, z: 'region', text: d => ((plot_options?.tickFormat) ?? (e => e))(d['IIR']), fontWeight: 'bold', fontSize: 16, fontFamily: 'jaf-bernino-sans', fill: 'region', // stroke: 'black' }) : null, ] }) return theplot }32:T12ff,{ let thedata = totals_by_year_data; let thevariable = cbc_variable_interes.filter(d => d.cbc === 'TAX_ACCRUED')[0] const plot_options = cbc_plot_options[thevariable.cbc] const region_list = ['PR', 'US', 'EU', 'OTHER']; const region_map = Object() region_labels.forEach(labels => { region_map[labels.region] = labels }) region_map['PR'] = {region_en: 'Puerto Rico', region_es: 'Puerto Rico'} const bar_threshold = 500e6; const outtext_spacing = 250e6; const projections_data = [...pr_iir_projections, ...iir_projections]; const yearly_positive_groups = d3.group( // thedata.filter(d => d[thevariable.cbc] > 0), projections_data.filter(d => (Math.abs(d['IIR_scaled']) < (bar_threshold ?? 0)) & (d['IIR_scaled'] >= 0)), d => d.time_period); yearly_positive_groups.keys().forEach(time_period => { yearly_positive_groups.set( time_period, d3.sort(yearly_positive_groups.get(time_period), d => -d['IIR_scaled']).map(d => d.region) // region_list ) }); const yearly_positive_totals = d3.rollup(projections_data.filter(d => d['IIR_scaled'] > 0), v => d3.sum(v, d => d['IIR_scaled']), g => g.time_period); console.log('yearly_positive_groups', yearly_positive_groups) console.log('yearly_positive_totals', yearly_positive_totals) const proj_string = lenguaje === 'english' ? 'Projected' : 'Proyectado'; let theplot = Plot.plot({ width: 350, style: 'overflow: visible', marginTop: 40, marginBottom: 40, marginLeft: plot_options?.marginLeft, color: { legend: true, domain: region_list.slice(1), range: [ EA_COLORES_MAP['INDIGO'], EA_COLORES_MAP['RED'], EA_COLORES_MAP['TEAL'], EA_COLORES_MAP['ORANGE'] ].slice(1), tickFormat: d => lenguaje === 'english' ? region_map[d].region_en : region_map[d].region_es, }, x: { }, y: { }, marks: [ // Plot.ruleY([0]), Plot.axisX({ // label: lenguaje === 'english' ? 'Year' : 'Año', label: null, interval: 1, tickFormat: d => d3.format('')(d) + (d > 2021 ? '\n'+proj_string : ''), fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 14, fontVariant: 'tabular-nums', }), Plot.axisY({ label: lenguaje === 'english' ? 'Collections in other jurisdictions' : 'Ganancia de recaudos en otras jurisdicciones', // label: 'Ganancia de recaudos en otras jurisdicciones', tickFormat: plot_options?.tickFormat, // tickPadding: plot_options?.tickPadding, fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 14, fontVariant: 'tabular-nums', }), // Plot.barY(thedata, {x: "time_period", y: thevariable.cbc, fill: EA_COLORES_MAP['INDIGO']}), // Plot.text(thedata, { // x: 'time_period', y: thevariable.cbc, // text: d => ((plot_options?.tickFormat) ?? (e => e))(d[thevariable.cbc]), // dy: -10, // fontWeight: 'bold', // fontSize: 16, // fontFamily: 'jaf-bernino-sans', // fill: 'black', stroke: 'white' // }), // Plot.text(thedata, Plot.stackY({ // x: 'time_period', y: thevariable.cbc, // text: d => ((plot_options?.tickFormat) ?? (e => e))(d[thevariable.cbc]), // // dy: -10, // fontWeight: 'bold', // fontSize: 16, // fontFamily: 'jaf-bernino-sans', // fill: 'black', //stroke: 'white' // })), Plot.barY(projections_data, Plot.stackY({ x: 'time_period', y: 'IIR_scaled', z: 'region', fill: 'region', })), Plot.text(projections_data, Plot.stackY({ filter: d => Math.abs(d['IIR_scaled']) >= (bar_threshold ?? 0) , x: 'time_period', y: 'IIR_scaled', z: 'region', text: d => ((plot_options?.tickFormat) ?? (e => e))(d['IIR_scaled']), order: 'value', // dy: -10, fontWeight: 'bold', fontSize: 16, fontFamily: 'jaf-bernino-sans', fill: 'black', //stroke: 'white' })), yearly_positive_groups.size > 0 ? Plot.text(projections_data, { filter: d => (Math.abs(d['IIR_scaled']) <= (bar_threshold ?? 0)) & (d['IIR_scaled'] >= 0) , x: 'time_period', y: d => yearly_positive_totals.get(d.time_period) + yearly_positive_groups.get(d.time_period)?.indexOf(d.region)*(outtext_spacing ?? 0), dy: -10, // y: d => yearly_positive_totals.get(d.time_period) + 10e9, // y: (d,i) => i === 0 ? 100e9 : 0, z: 'region', text: d => ((plot_options?.tickFormat) ?? (e => e))(d['IIR_scaled']), fontWeight: 'bold', fontSize: 16, fontFamily: 'jaf-bernino-sans', fill: 'region', // stroke: 'black' }) : null, ] }) return theplot }33:T580,totals_by_year_plot = { let thedata = totals_by_year_data; let thevariable = cbc_variable_radio_1 const plot_options = cbc_plot_options[thevariable.cbc] let theplot = Plot.plot({ style: 'overflow: visible', marginTop: 40, marginBottom: 40, marginLeft: plot_options?.marginLeft, x: { }, y: { }, marks: [ // Plot.ruleY([0]), Plot.axisX({ label: lenguaje === 'english' ? 'Year' : 'Año', interval: 1, tickFormat: '', fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 14, fontVariant: 'tabular-nums', }), Plot.axisY({ label: lenguaje === 'english' ? thevariable.variable_en : thevariable.variable_es, tickFormat: plot_options?.tickFormat, tickPadding: plot_options?.tickPadding, fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 14, fontVariant: 'tabular-nums', }), Plot.lineY(thedata, {x: "time_period", y: thevariable.cbc, marker: true}), Plot.text(thedata, { x: 'time_period', y: thevariable.cbc, text: d => ((plot_options?.tickFormat) ?? (e => e))(d[thevariable.cbc]), dy: -10, fontWeight: 'bold', fontSize: 16, fontFamily: 'jaf-bernino-sans', fill: 'black', stroke: 'white' }), ] }) return theplot }34:T84e,cbc_plot_options = Object({ 'TOT_REV': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 130, tickPadding: 70, bar_threshold: 1.8e9, outtext_spacing: 3.5e9, }, 'PROFIT': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 80, bar_threshold: 700e6, outtext_spacing: 1e9, }, 'TAX_ACCRUED': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 100, tickPadding: 55, bar_threshold: 15e6, outtext_spacing: 30e6, }, 'EMPLOYEES': { tickFormat: d => d3.format(',.0f')(d), marginLeft: 95, tickPadding: 55, bar_threshold: 1200, }, 'ENTITIES_COUNT': { marginLeft: 70, tickPadding: 45, bar_threshold: 17, }, 'ETR': { tickFormat: d => d3.format('.2%')(d), bar_threshold: 0.1, outtext_spacing: 0.12, }, 'ASSETS': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', bar_threshold: 5e6, marginLeft: 90, }, 'EARNINGS': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 140, tickPadding: 70, bar_threshold: 5000e6, outtext_spacing: 3000e6, }, 'PAYROLL': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 140, tickPadding: 70, bar_threshold: 5e6, }, 'TAX_BASE': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 140, tickPadding: 70, bar_threshold: 750e6, outtext_spacing: 1000e6, }, 'SBIE': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 140, tickPadding: 70, bar_threshold: 100e6, outtext_spacing: 1000e6, }, 'EXCESS_PROFIT': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 140, tickPadding: 70, bar_threshold: 3000e6, outtext_spacing: 3000e6, }, 'TTR': { tickFormat: d => d3.format('.2%')(d), bar_threshold: 0.1, outtext_spacing: 0.12, }, 'QDMTT': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 140, tickPadding: 70, bar_threshold: 150e6, outtext_spacing: 170e6, }, 'IIR': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 140, tickPadding: 70, bar_threshold: 100e6, outtext_spacing: 150e6, }, })35:T8a8,cbc_plot_options_profit_positive = Object({ 'TOT_REV': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 150, tickPadding: 70, bar_threshold: 2.5e9, outtext_spacing: 3.0e9, }, 'PROFIT': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 80, bar_threshold: 700e6, outtext_spacing: 1e9, }, 'TAX_ACCRUED': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 100, tickPadding: 55, bar_threshold: 25e6, outtext_spacing: 30e6, }, 'EMPLOYEES': { tickFormat: d => d3.format(',.0f')(d), marginLeft: 95, tickPadding: 55, bar_threshold: 1200, }, 'ENTITIES_COUNT': { marginLeft: 70, tickPadding: 45, bar_threshold: 10, outtext_spacing: 5, }, 'ETR': { tickFormat: d => d3.format('.2%')(d), bar_threshold: 0.02, outtext_spacing: 0.015, }, 'ASSETS': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', bar_threshold: 700e6, outtext_spacing: 700e6, marginLeft: 90, }, 'EARNINGS': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 140, tickPadding: 70, bar_threshold: 5000e6, outtext_spacing: 3000e6, }, 'PAYROLL': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 140, tickPadding: 70, bar_threshold: 90e6, outtext_spacing: 90e6, }, 'TAX_BASE': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 140, tickPadding: 70, bar_threshold: 750e6, outtext_spacing: 1000e6, }, 'SBIE': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 140, tickPadding: 70, bar_threshold: 70e6, outtext_spacing: 70e6, }, 'EXCESS_PROFIT': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 140, tickPadding: 70, bar_threshold: 3000e6, outtext_spacing: 3000e6, }, 'TTR': { tickFormat: d => d3.format('.2%')(d), bar_threshold: 0.02, outtext_spacing: 0.02, }, 'QDMTT': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 140, tickPadding: 70, bar_threshold: 150e6, outtext_spacing: 170e6, }, 'IIR': { tickFormat: d => d3.format('$,.1f')(d/1e6)+'M', marginLeft: 140, tickPadding: 70, bar_threshold: 100e6, outtext_spacing: 150e6, }, })36:T149b,totals_by_region_plot = { const thedata = totals_by_region_data; let thevariable = cbc_variable_radio_2 const plot_options = (profit_grouping === 'PANELAI' ? cbc_plot_options_profit_positive : cbc_plot_options)[thevariable.cbc]; const margin_bottom_cbcs = (profit_grouping === 'PANELAI' ? ['TAX_ACCRUED', 'ETR', 'EARNINGS', 'TAX_BASE', 'QDMTT', 'IIR'] : ['PROFIT', 'EARNINGS', 'TAX_BASE', 'TTR', 'IIR']) const region_map = Object() region_labels.forEach(labels => { region_map[labels.region] = labels }) const yearly_positive_groups = d3.group( // thedata.filter(d => d[thevariable.cbc] > 0), thedata.filter(d => (Math.abs(d[thevariable.cbc]) < (plot_options.bar_threshold ?? 0)) & (d[thevariable.cbc] > 0)), d => d.time_period); yearly_positive_groups.keys().forEach(time_period => { yearly_positive_groups.set( time_period, d3.sort(yearly_positive_groups.get(time_period), d => -d[thevariable.cbc]) .map(d => d.region) ) }) const yearly_negative_groups = d3.group( // thedata.filter(d => d[thevariable.cbc] > 0), thedata.filter(d => (Math.abs(d[thevariable.cbc]) < (plot_options.bar_threshold ?? 0)) & (d[thevariable.cbc] < 0)), d => d.time_period); yearly_negative_groups.keys().forEach(time_period => { yearly_negative_groups.set( time_period, d3.sort(yearly_negative_groups.get(time_period), d => d[thevariable.cbc]) .map(d => d.region) ) }) const yearly_positive_totals = d3.rollup(thedata.filter(d => d[thevariable.cbc] > 0), v => d3.sum(v, d => d[thevariable.cbc]), g => g.time_period) const yearly_negative_totals = d3.rollup(thedata.filter(d => d[thevariable.cbc] < 0), v => d3.sum(v, d => d[thevariable.cbc]), g => g.time_period) console.log('yearly_positive_groups', yearly_positive_groups) console.log('yearly_positive_totals', yearly_positive_totals) let theplot = Plot.plot({ style: 'overflow: visible', width: 850, marginTop: 40, marginBottom: margin_bottom_cbcs.includes(thevariable.cbc) ? 60 : 40, height: 800, marginLeft: plot_options?.marginLeft, x: { }, y: { }, color: { domain: ['US', 'EU', 'OTHER'], legend: true, tickFormat: d => lenguaje === 'english' ? region_map[d].region_en : region_map[d].region_es, }, marks: [ Plot.ruleY([0]), Plot.axisX({ // label: lenguaje === 'english' ? 'Year' : 'Año', label: null, tickPadding: margin_bottom_cbcs.includes(thevariable.cbc) ? 25 : undefined, interval: 1, tickFormat: '', fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 14, fontVariant: 'tabular-nums', }), Plot.axisY({ label: lenguaje === 'english' ? thevariable.variable_en : thevariable.variable_es, tickFormat: plot_options?.tickFormat, tickPadding: plot_options?.tickPadding, fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 14, fontVariant: 'tabular-nums', }), Plot.barY(thedata, Plot.stackY({ x: "time_period", y: thevariable.cbc, z: 'region', fill: 'region', order: '-value', marker: true})), Plot.text(thedata, Plot.stackY({ filter: d => Math.abs(d[thevariable.cbc]) >= (plot_options.bar_threshold ?? 0) , x: 'time_period', y: thevariable.cbc, z: 'region', text: d => ((plot_options?.tickFormat) ?? (e => e))(d[thevariable.cbc]), order: '-value', // dy: -10, fontWeight: 'bold', fontSize: 16, fontFamily: 'jaf-bernino-sans', fill: d => d.region === 'US' ? 'white' : 'black', //stroke: 'white' })), yearly_positive_groups.size > 0 ? Plot.text(thedata, { filter: d => (Math.abs(d[thevariable.cbc]) < (plot_options.bar_threshold ?? 0)) & (d[thevariable.cbc] > 0) , x: 'time_period', y: d => yearly_positive_totals.get(d.time_period) + yearly_positive_groups.get(d.time_period)?.indexOf(d.region)*(plot_options.outtext_spacing ?? 0), dy: -10, // y: d => yearly_positive_totals.get(d.time_period) + 10e9, // y: (d,i) => i === 0 ? 100e9 : 0, z: 'region', text: d => ((plot_options?.tickFormat) ?? (e => e))(d[thevariable.cbc]), fontWeight: 'bold', fontSize: 16, fontFamily: 'jaf-bernino-sans', fill: 'region', //stroke: 'white' }) : null, yearly_negative_groups.size > 0 ? Plot.text(thedata, { filter: d => (Math.abs(d[thevariable.cbc]) < (plot_options.bar_threshold ?? 0)) & (d[thevariable.cbc] < 0) , x: 'time_period', y: d => yearly_negative_totals.get(d.time_period) - yearly_negative_groups.get(d.time_period)?.indexOf(d.region)*(plot_options.outtext_spacing ?? 0), dy: 10, // y: d => yearly_positive_totals.get(d.time_period) + 10e9, // y: (d,i) => i === 0 ? 100e9 : 0, z: 'region', text: d => ((plot_options?.tickFormat) ?? (e => e))(d[thevariable.cbc]), fontWeight: 'bold', fontSize: 16, fontFamily: 'jaf-bernino-sans', fill: 'region', //stroke: 'white' }) : null, ] }) return theplot }37:T1642,m = { return new Map([ [ 2016, [ { "region": "EU", "time_period": 2016, "TOT_REV": 20230424, "PROFIT": -872946, "TAX_PAID": 127673, "EMPLOYEES": 68, "ENTITIES_COUNT": 8 }, { "region": "OTHER", "time_period": 2016, "TOT_REV": 1068655568, "PROFIT": -52996490, "TAX_PAID": 411554, "EMPLOYEES": 4182, "ENTITIES_COUNT": 17 }, { "region": "US", "time_period": 2016, "TOT_REV": 97835614361, "PROFIT": 38925940317, "TAX_PAID": 604667335, "EMPLOYEES": 66326, "ENTITIES_COUNT": 472 } ] ], [ 2017, [ { "region": "EU", "time_period": 2017, "TOT_REV": 3526844874, "PROFIT": 179963927, "TAX_PAID": 26037522, "EMPLOYEES": 8055, "ENTITIES_COUNT": 128 }, { "region": "NONE", "time_period": 2017, "TOT_REV": 384184635, "PROFIT": -219619690, "TAX_PAID": 2155645, "EMPLOYEES": 1064, "ENTITIES_COUNT": 6 }, { "region": "OTHER", "time_period": 2017, "TOT_REV": 2681498512, "PROFIT": -604983028, "TAX_PAID": 26291502, "EMPLOYEES": 6524, "ENTITIES_COUNT": 120 }, { "region": "US", "time_period": 2017, "TOT_REV": 98482114230, "PROFIT": 34335327569, "TAX_PAID": 557232405, "EMPLOYEES": 71619, "ENTITIES_COUNT": 607 } ] ], [ 2018, [ { "region": "EU", "time_period": 2018, "TOT_REV": 3554172188, "PROFIT": 273507774, "TAX_PAID": 56915541, "EMPLOYEES": 7089, "ENTITIES_COUNT": 136 }, { "region": "NONE", "time_period": 2018, "TOT_REV": 1279026083, "PROFIT": 96386477, "TAX_PAID": 13479907, "EMPLOYEES": 1179, "ENTITIES_COUNT": 7 }, { "region": "OTHER", "time_period": 2018, "TOT_REV": 6302355472, "PROFIT": 89813922, "TAX_PAID": 27313389, "EMPLOYEES": 15911, "ENTITIES_COUNT": 56 }, { "region": "US", "time_period": 2018, "TOT_REV": 113012194365, "PROFIT": 37764546169, "TAX_PAID": 587683454, "EMPLOYEES": 73835, "ENTITIES_COUNT": 630 } ] ], [ 2019, [ { "region": "EU", "time_period": 2019, "TOT_REV": 4012090643, "PROFIT": 634004418, "TAX_PAID": 75124311, "EMPLOYEES": 8581, "ENTITIES_COUNT": 129 }, { "region": "NONE", "time_period": 2019, "TOT_REV": 932071082, "PROFIT": 43423175, "TAX_PAID": 14301293, "EMPLOYEES": 1086, "ENTITIES_COUNT": 16 }, { "region": "OTHER", "time_period": 2019, "TOT_REV": 3129777652, "PROFIT": -54812545, "TAX_PAID": 18209226, "EMPLOYEES": 3762, "ENTITIES_COUNT": 41 }, { "region": "US", "time_period": 2019, "TOT_REV": 108942440830, "PROFIT": 33363980560, "TAX_PAID": 1217418788, "EMPLOYEES": 73556, "ENTITIES_COUNT": 663 } ] ], [ 2020, [ { "region": "EU", "time_period": 2020, "TOT_REV": 3463468009, "PROFIT": 868061439, "TAX_PAID": 71819748, "EMPLOYEES": 8006, "ENTITIES_COUNT": 132 }, { "region": "NONE", "time_period": 2020, "TOT_REV": 1146499545, "PROFIT": -26380520, "TAX_PAID": 9602144, "EMPLOYEES": 2367, "ENTITIES_COUNT": 13 }, { "region": "OTHER", "time_period": 2020, "TOT_REV": 1839670672, "PROFIT": 165530515, "TAX_PAID": 44139158, "EMPLOYEES": 818, "ENTITIES_COUNT": 31 }, { "region": "US", "time_period": 2020, "TOT_REV": 107307231704, "PROFIT": 34293776066, "TAX_PAID": 437855053, "EMPLOYEES": 81949, "ENTITIES_COUNT": 667 } ] ] ]) }38:T40a,iir_projections_scale_factors = { // For scaling down projections segun el // numero de paises por region que aplican IIR const region_data = [ {region: 'US', 2024: 0, 2025: .92746}, {region: 'EU', 2024: .8589, 2025: 7/7}, {region: 'OTHER', 2024: .9934, 2025: .9934}, // solo Japon tiene IIR // {region: 'US', 2024: 1, 2025: 1}, // {region: 'EU', 2024: 1, 2025: 1}, // {region: 'OTHER', 2024: 1, 2025: 1}, ] let thedata = []; for (let o of region_data) { const region = o['region']; thedata.push(...[ {region, time_period: 2024, scale_factor: o[2024]}, {region, time_period: 2025, scale_factor: o[2025]}, ]) } await db_client.query(` create or replace table iir_projections_scale_factors (region VARCHAR, time_period INT, scale_factor FLOAT) `) await db_client.query(`insert into iir_projections_scale_factors values ` + (thedata.map(d => `('${d.region}', ${d.time_period}, ${d.scale_factor})`)).join(', ') ) return thedata }39:T768,totals_by_country_plot = { const thedata = totals_by_country_data.filter(d => d.jur === cou_select.jur); let thevariable = cbc_variable_radio_3 const plot_options = cbc_plot_options[thevariable.cbc] // const region_map = Object() // region_labels.forEach(labels => { region_map[labels.region] = labels }) let theplot = Plot.plot({ style: 'overflow: visible', marginTop: 40, marginBottom: 40, marginLeft: plot_options?.marginLeft, x: { domain: [2016, 2017, 2018, 2019, 2020, 2021], }, y: { }, color: { // domain: ['US', 'EU', 'OTHER', 'NONE'], // legend: true, // tickFormat: d => lenguaje === 'english' ? region_map[d].region_en : region_map[d].region_es, }, marks: [ // Plot.ruleY([0]), Plot.axisX({ label: lenguaje === 'english' ? 'Year' : 'Año', interval: 1, tickFormat: '', fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 12, fontVariant: 'tabular-nums', }), Plot.axisY({ label: lenguaje === 'english' ? thevariable.variable_en : thevariable.variable_es, tickFormat: plot_options?.tickFormat, tickPadding: plot_options?.tickPadding, fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 12, fontVariant: 'tabular-nums', }), Plot.lineY(thedata, { x: "time_period", y: thevariable.cbc, z: 'cou', //stroke: 'cou', marker: true}), Plot.text(thedata, { x: 'time_period', y: thevariable.cbc, z: 'cou', text: d => ((plot_options?.tickFormat) ?? (e => e))(d[thevariable.cbc]), sort: {channel: 'y'}, dy: -10, fontWeight: 'bold', fontSize: 16, fontFamily: 'jaf-bernino-sans', fill: 'black', stroke: 'white' }), ] }) return theplot }3a:T7c2, undefined // { // const thedata = totals_by_country_data.filter(d => d.cou === 'USA'); // let thevariable = cbc_variable_radio_3 // const plot_options = cbc_plot_options[thevariable.cbc] // // const region_map = Object() // // region_labels.forEach(labels => { region_map[labels.region] = labels }) // let theplot = Plot.plot({ // style: 'overflow: visible', // marginTop: 40, // marginBottom: 40, // marginLeft: plot_options?.marginLeft, // x: { // }, // y: { // }, // color: { // // domain: ['US', 'EU', 'OTHER', 'NONE'], // legend: false, // // tickFormat: d => lenguaje === 'english' ? region_map[d].region_en : region_map[d].region_es, // }, // marks: [ // // Plot.ruleY([0]), // Plot.axisX({ // label: lenguaje === 'english' ? 'Year' : 'Año', // interval: 1, // tickFormat: '', // fontFamily: 'jaf-bernino-sans', // tickSize: 0, // fontSize: 12, // fontVariant: 'tabular-nums', // }), // Plot.axisY({ // label: lenguaje === 'english' ? thevariable.variable_en : thevariable.variable_es, // tickFormat: plot_options?.tickFormat, // tickPadding: plot_options?.tickPadding, // fontFamily: 'jaf-bernino-sans', // tickSize: 0, // fontSize: 12, // fontVariant: 'tabular-nums', // }), // Plot.lineY(thedata, { // x: "time_period", y: thevariable.cbc, z: 'cou', stroke: 'cou', // marker: true}), // Plot.text(thedata, { // x: 'time_period', y: thevariable.cbc, z: 'cou', // text: d => ((plot_options?.tickFormat) ?? (e => e))(d[thevariable.cbc]), // sort: {channel: 'y'}, // dy: -10, // fontWeight: 'bold', // fontSize: 16, // fontFamily: 'jaf-bernino-sans', // fill: 'black', stroke: 'white' // }), // ] // }) // return theplot // }3b:T76a,{ const thedata = totals_by_country_data.filter(d => d.jur !== 'USA'); let thevariable = cbc_variable_radio_3 const plot_options = cbc_plot_options[thevariable.cbc] // const region_map = Object() // region_labels.forEach(labels => { region_map[labels.region] = labels }) let theplot = Plot.plot({ style: 'overflow: visible', width: 800, marginTop: 40, marginBottom: 40, marginLeft: plot_options?.marginLeft, x: { }, y: { }, color: { // domain: ['US', 'EU', 'OTHER', 'NONE'], legend: true, // tickFormat: d => lenguaje === 'english' ? region_map[d].region_en : region_map[d].region_es, }, marks: [ // Plot.ruleY([0]), Plot.axisX({ label: lenguaje === 'english' ? 'Year' : 'Año', interval: 1, tickFormat: '', fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 12, fontVariant: 'tabular-nums', }), Plot.axisY({ label: lenguaje === 'english' ? thevariable.variable_en : thevariable.variable_es, tickFormat: plot_options?.tickFormat, tickPadding: plot_options?.tickPadding, fontFamily: 'jaf-bernino-sans', tickSize: 0, fontSize: 12, fontVariant: 'tabular-nums', }), Plot.barY(thedata, Plot.stackY({ x: "time_period", y: thevariable.cbc, z: 'jur', fill: 'jur', order: '-value', marker: true})), Plot.text(thedata, Plot.stackY({ x: 'time_period', y: thevariable.cbc, z: 'jur', text: d => ((plot_options?.tickFormat) ?? (e => e))(d[thevariable.cbc]), // sort: {channel: 'y'}, order: '-value', // dy: -10, fontWeight: 'bold', fontSize: 16, fontFamily: 'jaf-bernino-sans', fill: 'black', stroke: 'white' })), ] }) return theplot }3c:T863,{ const selected_jurisdictions = [ 'PRI', 'IRL', 'SGP', 'CHE', 'LUX', 'NLD', 'HKG', 'PAN', 'MLT', 'ARE', 'DOM', 'ESP', 'USA', 'BMU', ]; let thedata = totals_by_country_data .filter(d => (d.time_period === 2021) & ( selected_jurisdictions.includes(d.jur) ) ); thedata = d3.sort(thedata, d => d.ETR) const name_replace = { 'USA': 'Estados Unidos\nde América', 'DOM': 'República\nDominicana', 'ARE': 'Emiratos\nÁrabes Unidos', } let theplot = Plot.plot({ width: 900, marginBottom: 130, x: { domain: thedata.map(d => d.jur) }, marks: [ Plot.axisX({ // label: 'Jurisdicción de subsidiaria', label: null, tickFormat: d => name_replace[d] ?? cou_to_name_map[d]?.label_es, tickRotate: -90, tickSize: 0, fontSize: 14, fontFamily: 'jaf-bernino-sans', fontWeight: 'bold', }), Plot.axisY({ label: 'Tasa efectiva contributiva', tickSize: 0, tickFormat: '.0%', fontSize: 14, fontFamily: 'jaf-bernino-sans', }), Plot.barY(thedata, { x: 'jur', y: 'ETR', fill: d => { let color = chroma(EA_COLORES_MAP['INDIGO']) if (d.jur !== 'PRI') { color = color.brighten(1.5).desaturate(1) } return color.hex() } // order: '-x', }), Plot.text(thedata, { x: 'jur', y: 'ETR', text: d => d3.format('.2%')(d.ETR), fontWeight: 'bold', fontSize: 16, lineAnchor: 'bottom', dy: -5, fontFamily: 'jaf-bernino-sans', // order: '-x', }), // Plot.text(thedata, { // x: 'partner_jurisdiction', // y: 0, // text: 'partner_jurisdiction', // fontWeight: 'bold', // fontSize: 14, // rotate: -90, // textAnchor: 'start', // // lineAnchor: 'bottom', // // dy: -5, // fontFamily: 'jaf-bernino-sans', // // order: '-x', // }), ] }) return theplot; // return thedata; }3d:Tb00,{ const selected_jurisdictions = [ 'PRI', 'IRL', 'SGP', 'CHE', 'LUX', 'NLD', 'HKG', 'PAN', 'MLT', 'ARE', 'DOM', 'ESP', 'USA', 'BMU', ]; let thedata = totals_by_country_data .filter(d => (d.time_period === 2021) & ( selected_jurisdictions.includes(d.jur) ) ); thedata = d3.sort(thedata, d => d.QDMTT) const name_replace = { 'USA': 'Estados Unidos\nde América', 'DOM': 'República\nDominicana', 'ARE': 'Emiratos\nÁrabes Unidos', } let theplot = Plot.plot({ width: 1200, marginBottom: 160, marginLeft: 75, x: { domain: thedata.map(d => d.jur) }, marks: [ Plot.axisX({ // label: 'Jurisdicción de subsidiaria', label: null, tickFormat: d => name_replace[d] ?? cou_to_name_map[d]?.label_es, tickRotate: -90, tickSize: 0, fontSize: 16, fontFamily: 'jaf-bernino-sans', fontWeight: 'bold', tickPadding: 30, }), Plot.axisY({ label: 'Impuesto complementario mínimo nacional cualificado (QDMTT)', tickSize: 0, tickFormat: d => d3.format('$,.0f')(d / 1e6)+'M', fontSize: 14, fontFamily: 'jaf-bernino-sans', fontVariant: 'tabular-nums', }), Plot.barY(thedata, { x: 'jur', y: 'QDMTT', fill: d => { let color = chroma(EA_COLORES_MAP['INDIGO']) if (d.jur !== 'PRI') { color = color.brighten(1.5).desaturate(1) } if (d.QDMTT < 0 ) color = chroma(EA_COLORES_MAP['RED']).brighten().desaturate(0.5); return color.hex() } // order: '-x', }), Plot.text(thedata, { filter: d => !['DOM', 'USA'].includes(d.jur), x: 'jur', y: 'QDMTT', text: d => d3.format('$,.0f')(d.QDMTT / 1e6)+'M', fontWeight: 'bold', fontSize: 16, lineAnchor: 'bottom', dy: -5, fontFamily: 'jaf-bernino-sans', // order: '-x', }), Plot.text(thedata, { filter: d => ['DOM', 'USA'].includes(d.jur), x: 'jur', y: 'QDMTT', text: d => d3.format('$,.0f')(d.QDMTT / 1e6)+'M', fontWeight: 'bold', fontSize: 16, lineAnchor: 'top', dy: 5, fontFamily: 'jaf-bernino-sans', // order: '-x', }), // Plot.text(thedata, { // x: 'partner_jurisdiction', // y: 0, // text: 'partner_jurisdiction', // fontWeight: 'bold', // fontSize: 14, // rotate: -90, // textAnchor: 'start', // // lineAnchor: 'bottom', // // dy: -5, // fontFamily: 'jaf-bernino-sans', // // order: '-x', // }), ] }) return theplot; // return thedata; }3e:T8b6,Para extraer los datos incluidos aqui, hay que descargar el CSV completo usando el [OECD Data Explorer](https://data-explorer.oecd.org/vis?tm=corporate%20tax%20statistics&pg=0&snb=96&df[ds]=dsDisseminateFinalDMZ&df[id]=DSD_CBCR%40DF_CBCRI&df[ag]=OECD.CTP.TPS&df[vs]=1.0&dq=AUS..A....._T.&lom=LASTNPERIODS&lo=1&to[TIME_PERIOD]=false) y descargar en "Download" > "Unfiltered data in tabular text (CSV)". Debe hacer esto offline ya que el archivo original es muy grande. ```sql -- Table: oecd_puerto_rico with puerto_rico_filter as ( select REF_AREA as cou, "Reference area" as ultimate_parent_jurisdiction, COUNTERPART_AREA as jur, "Counterpart area" as partner_jurisdiction, PROFIT_GROUPING as profit_grouping, MEASURE as cbc, TIME_PERIOD as time_period, OBS_VALUE as obs_value from read_csv_auto('OECD.CTP.TPS,DSD_CBCR@DF_CBCRI,1.0+all.csv') where (REF_AREA = 'PRI' or COUNTERPART_AREA = 'PRI') and MEASURE != 'ETR' -- no ETR bc usa statistical_operation y "DECIMALS" ), oecd_puerto_rico__final as ( pivot puerto_rico_filter on cbc, using first(obs_value) order by cou, time_period ) from oecd_puerto_rico__final ``` Y puedes conseguir los nombres de las variables con el siguiente query: ```sql -- Table: cbc_variable select distinct measure as cbc, measure_1 as variable from read_csv_auto('OECD.CTP.TPS,DSD_CBCR@DF_CBCRI,1.0+all.csv') order by cbc ``` **QUERY VIEJO:** Filtre con el siguiente query usando DuckDB: ```sql -- Table: oecd_puerto_rico with puerto_rico_filter as ( select COU as cou, "Ultimate Parent Jurisdiction" as ultimate_parent_jurisdiction, JUR as jur, "Partner Jurisdiction" as partner_jurisdiction, CBC as cbc, TIME_PERIOD as time_period, OBS_VALUE as obs_value from read_csv_auto('OECD,DF_CBCR_TABLEI,+all.csv') where JUR = 'PRI' and PAN = 'PANELA' ), oecd_puerto_rico__final as ( pivot puerto_rico_filter on cbc, using first(obs_value) order by cou, time_period ) from oecd_puerto_rico__final; ``` Y puedes conseguir los nombres de las variables con el siguiente query: ```sql -- Table: cbc_variable select distinct CBC as cbc, Variable as variable from read_csv_auto('OECD,DF_CBCR_TABLEI,+all.csv') order by cbc ``` 3f:T7ce,db_instance = { // Pick a bundle const bundles = duckdb.getJsDelivrBundles(); const bundle = await duckdb.selectBundle(bundles); // Initialize the database const logger = new duckdb.ConsoleLogger(); const worker = await duckdb.createWorker(bundle.mainWorker); const db = new duckdb.AsyncDuckDB(logger, worker); await db.instantiate(bundle.mainModule); const connection = await db.connect() // Import dataset files // await insertFile(db, 'oecd_puerto_rico', oecd_puerto_rico_file) // await insertFile(db, 'cbc_variable', cbc_variable_file) await insertFile(db, 'oecd_all_countries_file', oecd_all_countries_file) // Insert object arrays manually await db.registerFileBuffer('oecd_puerto_rico.csv', encoder.encode( d3.csvFormat(await oecd_puerto_rico_file.csv({typed: true})) )) await connection.insertCSVFromPath('oecd_puerto_rico.csv', {name: 'oecd_puerto_rico_file'}) await db.registerFileBuffer('cbc_variable.csv', encoder.encode( d3.csvFormat(await cbc_variable_file.csv({typed: true})) )) await connection.insertCSVFromPath('cbc_variable.csv', {name: 'cbc_variable_file'}) await db.registerFileBuffer('ISO.csv', encoder.encode( d3.csvFormat(cou_iso) )) await connection.insertCSVFromPath('ISO.csv', {name: 'countries'}) await db.registerFileBuffer('cou_regions.csv', encoder.encode(d3.csvFormat(cou_regions))) await connection.insertCSVFromPath('cou_regions.csv', {name: 'cou_regions'}) await db.registerFileBuffer('monthly_earnings_simplified.csv', encoder.encode(d3.csvFormat(await monthly_earnings_simplified_file.csv({typed: true})) )) await connection.insertCSVFromPath('monthly_earnings_simplified.csv', {name: 'monthly_earnings_simplified_file'}) // const buffer = await oecd_cbcr_file.arrayBuffer(); // await db_instance.registerFileBuffer(oecd_cbcr_file.name, new Uint8Array(buffer)); // await client.query(`create or replace view 'oecd_cbcr' as select * from parquet_scan(${oecd_cbcr_file.name})`) return db }40:T774,db_client = { const client = await new DuckDBClientCompat(db_instance) await client.query(`create or replace table monthly_earnings_simplified as ( select * from monthly_earnings_simplified_file )`) await client.query(`insert into monthly_earnings_simplified values ('PRI', 'Puerto Rico', 2021, ${weighted_avg_salary / 12}) `) await client.query(`create or replace table oecd_all_countries as ( with oecd_all_countries_file_with_salary as ( select oecd.*, round(me.earnings * 12 * 1.20, 2) as average_salary from oecd_all_countries_file as oecd left join monthly_earnings_simplified as me on oecd.jur = me.ref_area ) select *, EMPLOYEES * average_salary as PAYROLL, PROFIT - .08*ASSETS - .10*PAYROLL as TAX_BASE, .10*PAYROLL + .08*ASSETS as SBIE, TOT_REV - SBIE as EXCESS_PROFIT, from (from oecd_all_countries_file_with_salary where profit_grouping = '${profit_grouping}') )`) await client.query(`create or replace table oecd_puerto_rico as ( select *, EMPLOYEES * ${weighted_avg_salary} as PAYROLL, PROFIT - .08*ASSETS - .10*PAYROLL as TAX_BASE, .10*PAYROLL + .08*ASSETS as SBIE, TOT_REV - SBIE as EXCESS_PROFIT, from (from oecd_puerto_rico_file where profit_grouping = '${profit_grouping}') )`) // await client.query(`create or replace table cbc_variable_backup as (from cbc_variable)`); await client.query(`create or replace table cbc_variable (cbc varchar primary key, variable varchar)`); await client.query(`insert into cbc_variable select * from cbc_variable_file`) await client.query(`insert into cbc_variable values ('PAYROLL', 'Payroll'), ('TTR', 'Top-up tax rate'), ('TAX_BASE', 'Tax base'), ('SBIE', 'Substance based income exclusion'), ('EXCESS_PROFIT', 'Excess profit'), ('QDMTT', 'Qualifying domestic minimum top-up tax'), ('IIR', 'Income inclusion rule') `) return client }41: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(); } }42:[{"id":"8b847f904392a3812c938788441ddca7bedde9c655d329f93004c363be6010cee09ddde3a27a1bf2c862786cb4d0d34e2e7100739a90b53e773fc8fa2389729d","href":"https://static.observableusercontent.com/files/8b847f904392a3812c938788441ddca7bedde9c655d329f93004c363be6010cee09ddde3a27a1bf2c862786cb4d0d34e2e7100739a90b53e773fc8fa2389729d","name":"cbc_variable@1.csv","type":"text/csv","lastModified":1729611486550.945,"size":1326},{"id":"14e8a6f5caed5f4cf2bdb4228b5d14a32f031a9517f896227ee0407c8af580b7ff220c9c3b37e3e37d4a817350bfb22469e82659ba313c3f2debfb564e92f7f5","href":"https://static.observableusercontent.com/files/14e8a6f5caed5f4cf2bdb4228b5d14a32f031a9517f896227ee0407c8af580b7ff220c9c3b37e3e37d4a817350bfb22469e82659ba313c3f2debfb564e92f7f5","name":"oecd_puerto_rico@4.csv","type":"text/csv","lastModified":1729611486550.945,"size":33763},{"id":"c1aa88d0c31fd55fc4d276c0a67faacee5c4f2a22e469aee6c4d02ed4c7712d1594146e7822951a730f5d8b22c508a778c2fe51149bd052402de3f870440d025","href":"https://static.observableusercontent.com/files/c1aa88d0c31fd55fc4d276c0a67faacee5c4f2a22e469aee6c4d02ed4c7712d1594146e7822951a730f5d8b22c508a778c2fe51149bd052402de3f870440d025","name":"ISO.csv","type":"text/csv","lastModified":1729611486550.945,"size":20658},{"id":"82b9a4705f69225dcbc5f572eac8ff314b48c2fc985a505484268773acbc467fff726785608d3744b75f6e5238e179215327e6bab747349d3ff2f7144fbe54f0","href":"https://static.observableusercontent.com/files/82b9a4705f69225dcbc5f572eac8ff314b48c2fc985a505484268773acbc467fff726785608d3744b75f6e5238e179215327e6bab747349d3ff2f7144fbe54f0","name":"paises.json","type":"application/json","lastModified":1729611486550.945,"size":12274},{"id":"a9f341dd02d0890658e8c62d9f4a4fcad740be560380ed0660f5b20b5b380c32683dfd1759e53a05fc9a122784cb6efec16e0c6284c6b24b117a879e032e3fad","href":"https://static.observableusercontent.com/files/a9f341dd02d0890658e8c62d9f4a4fcad740be560380ed0660f5b20b5b380c32683dfd1759e53a05fc9a122784cb6efec16e0c6284c6b24b117a879e032e3fad","name":"oecd_all_countries.parquet","type":"application/octet-stream","lastModified":1729611650428.396,"size":3609726},{"id":"3f9e2ce6d2fcc4a0fc99d1804f5b2b59f43d7b0f6cfefeace8569e9626876ef8df7de4773936b84e011fdba21b30ecfed52e11954ff111056ae312c808a441f2","href":"https://static.observableusercontent.com/files/3f9e2ce6d2fcc4a0fc99d1804f5b2b59f43d7b0f6cfefeace8569e9626876ef8df7de4773936b84e011fdba21b30ecfed52e11954ff111056ae312c808a441f2","name":"monthly_earnings_simplified.csv","type":"text/csv","lastModified":1729612286983.335,"size":4495},{"id":"a589d63a9facd69fcdb930a55703b34394b953e56d0b521c3a4fd33d162ee8948ee77b93f0d34123ce824be1546653885683e103cbffe4f141669cc1c40cacbe","href":"https://static.observableusercontent.com/files/a589d63a9facd69fcdb930a55703b34394b953e56d0b521c3a4fd33d162ee8948ee77b93f0d34123ce824be1546653885683e103cbffe4f141669cc1c40cacbe","name":"oecd_all_countries@1.parquet","type":"application/octet-stream","lastModified":1729695785813.532,"size":3386512}] 2f:{"id":"a9b8cb62797a8fb6","slug":null,"title":"OECD - All countries","thumbnail":"6709d45107ce4c3644b355c368835f1a9a9ac42add039693ef76c53f8194990e","access_level":"public","create_time":"$D2024-10-22T15:38:06.550Z","update_time":"$D2024-10-24T20:39:07.448Z","view_time":null,"first_public_time":"$D2024-10-22T15:38:06.550Z","fork":{"id":"01636c297bdd1d7b","slug":null,"title":"OECD Paper","owner":{"id":"247b19faa48b7d89","login":"jzavala-gonzalez","name":"José M. Zavala González"}},"stars":1,"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":1436,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"2c62f0243f8f45b0","login":"jzavala-gonzalez","name":"José M. Zavala González","avatar_url":"https://avatars-next.observableusercontent.com/c80357963553ac341ea90ed3d79296a72d004aa90d9b89afd76f17e04dcf9277","tier":null}],"members":[{"type":"team","id":"247b19faa48b7d89","login":"jzavala-gonzalez","name":"José M. Zavala González","avatar_url":"https://avatars-next.observableusercontent.com/c80357963553ac341ea90ed3d79296a72d004aa90d9b89afd76f17e04dcf9277","tier":"starter_2024","role":"editor"},{"type":"individual","id":"2c62f0243f8f45b0","login":"jzavala-gonzalez","name":"José M. Zavala González","avatar_url":"https://avatars-next.observableusercontent.com/c80357963553ac341ea90ed3d79296a72d004aa90d9b89afd76f17e04dcf9277","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":{"id":"6747c4e5c0f5d653","slug":"espacios-abiertos","title":"Espacios Abiertos","owner":{"id":"247b19faa48b7d89","login":"jzavala-gonzalez","name":"José M. Zavala González"}},"type":"notebook","body":{"title":"OECD - All countries","cells":[{"id":0,"value":"# OECD - All countries\n\nPara hacer un analisis breve del ETR para todos los paises.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":54,"value":"from cbc_variable","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db_client"},{"id":70,"value":"Las 5 que nos interesan son:\n\n| variable | cbc |\n|---------------------|----------------|\n| Total Revenue | TOT_REV |\n| Profit | PROFIT |\n| Taxes Paid | TAX_PAID |\n| Employees | EMPLOYEES |\n| Entities | ENTITIES_COUNT |\n\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":953,"value":"Inputs.table(cbc_labels)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":948,"value":"cbc_labels = {\n let thedata = [...cbc_variable_interes, iir_variable_interes]\n\n thedata = thedata.map(d => {\n let dnew = {};\n for (const k of ['cbc', 'variable_en', 'variable_es']) {\n dnew[k] = d[k]\n }\n return dnew\n })\n\n return thedata\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":111,"value":"cbc_variable_interes = {\n \n let thedata = await db_client.query(`\n from cbc_variable\n where ${cbc_variable_interes_sql_list}.list_contains(cbc)\n `)\n\n const variables_with_aggregate = ['ETR', 'TTR', 'QDMTT'];\n \n thedata.forEach(d => {\n d.needs_aggregate = variables_with_aggregate.includes(d.cbc),\n d.variable_en = (cbc_variable_en[d.cbc] ?? d.variable)\n d.variable_es = cbc_variable_es[d.cbc]\n })\n return d3.sort(thedata, d => cbc_variable_interes_list.indexOf(d.cbc))\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":865,"value":"iir_variable_interes = Object({\n cbc: 'IIR', variable: 'Income inclusion rule',\n needs_aggregate: true,\n variable_en: 'Income Inclusion Rule (IIR)', variable_es: 'Regla de inclusión de ingresos',\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":130,"value":"cbc_variable_interes_list = ['TOT_REV', 'PROFIT', 'TAX_ACCRUED', 'EMPLOYEES', 'ENTITIES_COUNT', 'ASSETS', 'ETR', 'EARNINGS', 'PAYROLL', 'TAX_BASE', 'SBIE', 'EXCESS_PROFIT', 'ETR', 'TTR', 'QDMTT']","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":820,"value":"cbc_variable_interes_no_aggregate_list = cbc_variable_interes.filter(d => !d.needs_aggregate).map(d => d.cbc)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":170,"value":"cbc_variable_interes_list.map((d,i) => [d,i])","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":824,"value":"cbc_variable_needs_aggregate_sql = 'tax_accrued / profit as ETR, .15 - ETR as TTR, TTR * TAX_BASE as QDMTT'","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":136,"value":"cbc_variable_interes_sql_list = '['+ cbc_variable_interes_list.map(d => `'${d}'`).join(', ') + ']'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":956,"value":"panel_to_profit_label = Object({\n '_T': 'Total', 'PANELAI': 'Positive', 'PANELAII': 'Negative'\n })","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":321,"value":"viewof lenguaje = Inputs.radio(['español', 'english'], {label: \"Lenguaje\", value: \"español\", })","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":884,"value":"viewof profit_grouping = Inputs.radio(['_T', 'PANELAI', 'PANELAII'], {label: \"Profit grouping\", value: \"PANELAI\", format: d => {\n return panel_to_profit_label[d]\n} })","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1413,"value":"## Corporaciones foráneas","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":890,"value":"$31","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1096,"value":"$32","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1011,"value":"pr_iir_projections = {\n const pr_2021 = totals_by_year_data.filter(d => d.time_period === 2021)[0]\n\n let thedata = []\n for (let y of [2024, 2025]) {\n thedata.push({\n region: 'PR', time_period: y, IIR: pr_2021['TAX_ACCRUED']\n })\n }\n return thedata\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1038,"value":"[...pr_iir_projections, ...iir_projections]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":102,"value":"## Totales por año","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":165,"value":"viewof cbc_variable_radio_1 = Inputs.radio(cbc_variable_interes, {label: \"Variable\", value: cbc_variable_interes.filter(d => d.cbc === 'TOT_REV')[0], format: d => (lenguaje === 'español' ? d.variable_es : d.variable_en) ?? d.cbc})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":107,"value":"$33","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":881,"value":"totals_by_year_data","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":946,"value":"downloadbuttonCSV(totals_by_year_data, `oecd_puerto_rico_totals_by_year_profit_${panel_to_profit_label[profit_grouping].toLowerCase()}.csv`)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":959,"value":"`oecd_puerto_rico_totals_by_year_profit_${panel_to_profit_label[profit_grouping].toLowerCase()}.csv`","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":879,"value":"totals_by_year_data = await db_client.query(`\\\nwith initial as (\n select time_period,\n -- sum(TOT_REV) as TOT_REV,\n ${cbc_variable_interes_no_aggregate_list.map(d => `sum(${d}) as ${d}`).join(', ')}\n from oecd_puerto_rico\n group by time_period\n order by time_period\n)\n\nfrom initial select *, ${cbc_variable_needs_aggregate_sql}\n `)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":934,"value":"Inputs.table(summary_variables_yearly)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":929,"value":"summary_variables_yearly = await db_client.query(`\\\nwith initial as (\n select time_period,\n -- sum(TOT_REV) as TOT_REV,\n ${cbc_variable_interes_no_aggregate_list.map(d => `sum(${d}) as ${d}`).join(', ')}\n from oecd_puerto_rico\n group by time_period\n order by time_period\n),\n\ntotals_by_year as (\n from initial select *, ${cbc_variable_needs_aggregate_sql}\n),\n\nfirst_unpivot as (\n\n unpivot totals_by_year\n on columns(* exclude (time_period))\n into\n name cbc\n value value\n)\n\nfrom first_unpivot\n `)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":216,"value":"$34","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":898,"value":"$35","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":326,"value":"## Totales por región","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":387,"value":"viewof cbc_variable_radio_2 = Inputs.radio([...cbc_variable_interes, iir_variable_interes], {label: \"Variable\", value: cbc_variable_interes.filter(d => d.cbc === 'TOT_REV')[0], format: d => (lenguaje === 'español' ? d.variable_es : d.variable_en) ?? d.cbc})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":390,"value":"$36","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":528,"value":"d3.sort(m.get(2016), d => -d['TOT_REV'])","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":523,"value":"$37","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":403,"value":"region_labels = {\n let themap = Object({\n 'EU': ['Europe', 'Europa'],\n 'US': ['United States', 'Estados Unidos'],\n 'OTHER': ['Other countries', 'Otros países'],\n })\n\n let thedata = Object.entries(themap).map(d => Object({region: d[0], region_en: d[1][0], region_es: d[1][1]}))\n return thedata\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":332,"value":"select distinct cou, ultimate_parent_jurisdiction, region\nfrom oecd_puerto_rico\nleft join cou_regions\nusing (cou)\norder by cou","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db_client"},{"id":385,"value":"totals_by_region_data","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":380,"value":"totals_by_region_data = {\n await db_client.query(`\ncreate or replace table totals_by_region_data as (\nwith initial as (\n select region, time_period,\n ${cbc_variable_interes_no_aggregate_list.map(d => `sum(${d}) as ${d}`).join(', ')}\n from oecd_puerto_rico\n left join cou_regions\n using (cou)\n group by all\n order by all\n)\n\nfrom initial select *, ${cbc_variable_needs_aggregate_sql},\n sum(tax_accrued) over (partition by time_period) / sum(profit) over (partition by time_period) as ETR_total,\n (.15 - ETR_total) * TAX_BASE as IIR,\norder by region, time_period\n)\n `)\n\n let thedata = await db_client.query(`from totals_by_region_data`)\n \n return thedata\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":968,"value":"Inputs.table(totals_by_region_data)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":983,"value":"iir_projections","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":978,"value":"iir_projections = {\n // Dependencies:\n totals_by_region_data;\n iir_projections_scale_factors;\n \n let thedata = await db_client.query(`\n with iir_2021 as (\n select region, time_period, iir,\n from totals_by_region_data\n where time_period = 2021\n ),\n\n data_with_scale_factors as (\n select iir_2021.*, sf.time_period as new_time_period, sf.scale_factor\n from iir_2021\n left join iir_projections_scale_factors as sf\n on iir_2021.region = sf.region\n )\n\n select region, new_time_period as time_period, IIR, IIR * scale_factor as IIR_scaled,\n from data_with_scale_factors\n `)\n\n return thedata\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":870,"value":"$38","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":994,"value":"await db_client.query('from iir_projections_scale_factors')","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":343,"value":"cou_regions = {\n \n const themap = Object({\n 'ARE': 'OTHER',\n 'SAU': 'OTHER',\n 'BMU': 'OTHER',\n 'CHE': 'EU',\n 'CYM': 'OTHER',\n 'DEU': 'EU',\n 'DNK': 'EU',\n 'ESP': 'EU',\n 'GRC': 'EU',\n 'HKG': 'OTHER',\n 'IND': 'OTHER',\n 'ITA': 'EU',\n 'JPN': 'OTHER',\n 'LUX': 'EU',\n 'MEX': 'OTHER',\n 'PAN': 'OTHER',\n 'PER': 'OTHER',\n 'USA': 'US',\n })\n\n const thedata = Object.entries(themap).map(d => Object({cou: d[0], region: d[1]}));\n\n return thedata\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":439,"value":"## Totales por país","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":653,"value":"viewof cou_select = Inputs.select(d3.sort(cou_to_name, d => lenguaje === 'english' ? d.label_en : d.label_es), {label: \"Jurisdicción de subsidiaria\",\n format: d => lenguaje === 'english' ? d.label_en : d.label_es, value: cou_to_name.filter(d => d.jur === 'USA')[0]\n })","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":445,"value":"viewof cbc_variable_radio_3 = Inputs.radio([...cbc_variable_interes, iir_variable_interes], {label: \"Variable\", value: cbc_variable_interes.filter(d => d.cbc === 'TOT_REV')[0], format: d => (lenguaje === 'español' ? d.variable_es : d.variable_en) ?? d.cbc})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":588,"value":"$39","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":443,"value":"$3a","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":593,"value":"$3b","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1152,"value":"{\n const cou_in_profit_making = new Set([\"CHE\", \"CYM\", \"DEU\", \"ESP\", \"GRC\", \"ITA\", \"JPN\", \"LUX\", \"SAU\", \"USA\"]);\n\n const cou_in_total = new Set([\"ARE\", \"BMU\", \"CHE\", \"CYM\", \"DEU\", \"ESP\", \"GRC\", \"HKG\", \"ITA\", \"JPN\", \"LUX\", \"MEX\", \"SAU\", \"USA\"]);\n\n return cou_in_profit_making\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1316,"value":"chroma(EA_COLORES_MAP['INDIGO']).brighten(2)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1354,"value":"cou_to_name_map","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1417,"value":"## Corporaciones foráneas: tasa efectiva contributiva","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1245,"value":"$3c","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1411,"value":"## Corporaciones foráneas: impuesto complementario mínimo nacional cualificado","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1359,"value":"$3d","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1423,"value":"","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1149,"value":"totals_by_country_data.filter(d => d.time_period === 2021).map(d => d.jur)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1087,"value":"totals_by_country_data","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":441,"value":"// totals_by_country_data =\n{\n let thedata = await db_client.query(`\n with initial as (\n select cou, time_period,\n ${cbc_variable_interes_no_aggregate_list.map(d => `sum(${d}) as ${d}`).join(', ')}\n from oecd_puerto_rico\n group by all\n order by all\n)\n\nselect *, ${cbc_variable_needs_aggregate_sql},\nsum(tax_accrued) over (partition by time_period) / sum(profit) over (partition by time_period) as ETR_total,\n (.15 - ETR_total) * TAX_BASE as IIR,\nfrom initial \nleft join cou_regions\nusing (cou)\norder by cou, time_period\n `)\n \n return thedata\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1220,"value":"totals_by_country_data =\n{\n let thedata = await db_client.query(`\n with initial as (\n select jur, partner_jurisdiction, time_period,\n ${cbc_variable_interes_no_aggregate_list.map(d => `sum(${d}) as ${d}`).join(', ')}\n from oecd_all_countries\n group by all\n order by all\n)\n\nselect initial.*, cou_regions.region, ${cbc_variable_needs_aggregate_sql},\nsum(tax_accrued) over (partition by time_period) / sum(profit) over (partition by time_period) as ETR_total,\n (.15 - ETR_total) * TAX_BASE as IIR,\nfrom initial \nleft join cou_regions\non initial.jur = cou_regions.cou\norder by cou, time_period\n `)\n \n return thedata\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":674,"value":"// cou_to_name =\n{\n let thedata = await db_client.query(`\n select distinct cou, ultimate_parent_jurisdiction as label_en, pais as label_es\n from oecd_puerto_rico\n left join countries\n on oecd_puerto_rico.cou = countries.\"alpha-3\"\n group by all\n order by all\n `)\n\n // const cou_labels_es = Object({\n // 'ARE': 'Reino de Arabia Saudita',\n // })\n // thedata.forEach(d => {\n // const cou = d.cou;\n // // if (cou_labels_es[d.cou] !== undefined) {\n // d.label_es = cou_labels_es[d.cou] ?? undefined;\n // // }\n // })\n \n return thedata\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1228,"value":"cou_to_name =\n{\n let thedata = await db_client.query(`\n select distinct jur, partner_jurisdiction as label_en, pais as label_es\n from oecd_all_countries\n left join countries\n on oecd_all_countries.jur = countries.\"alpha-3\"\n group by all\n order by all\n `)\n\n // const cou_labels_es = Object({\n // 'ARE': 'Reino de Arabia Saudita',\n // })\n // thedata.forEach(d => {\n // const cou = d.cou;\n // // if (cou_labels_es[d.cou] !== undefined) {\n // d.label_es = cou_labels_es[d.cou] ?? undefined;\n // // }\n // })\n \n return thedata\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1346,"value":"cou_to_name_map = {\n let data = Object()\n cou_to_name.forEach(d => {\n data[d.jur] = d\n })\n return data\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":771,"value":"select sum(entities_count) as entities_count,\n -- (ACT_ADMIN+ACT_DOR+ACT_FIN+ACT_HOLDING+ACT_IGF+ACT_INS+ACT_IP+ACT_MAN+ACT_OTHER+ACT_PUR+ACT_RD+ACT_SALES+ACT_SER) as total_in_act,\n sum(list_value(ACT_ADMIN,ACT_DOR,ACT_FIN,ACT_HOLDING,ACT_IGF,ACT_INS,ACT_IP,ACT_MAN,ACT_OTHER,ACT_PUR,ACT_RD,ACT_SALES,ACT_SER).list_aggregate('sum')) as total_in_act,\n sum(columns('ACT_*'))\n \nfrom oecd_puerto_rico\nwhere time_period = 2021","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db_client"},{"id":607,"value":"TODO: Repetir el chart del stacked bar chart pero xaxis es año y los stacks son actividades distintas. No hay necesidad de facetear por país.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":762,"value":"Plot.plot({\n projection: \"equirectangular\",\n r: {transform: (r) => Math.pow(10, r)}, // Richter to amplitude\n marks: [\n Plot.geo(land, {fill: \"currentColor\", fillOpacity: 0.2}),\n Plot.sphere(),\n // Plot.geo(earthquakes, {\n // r: \"mag\",\n // fill: \"red\",\n // fillOpacity: 0.2,\n // stroke: \"red\",\n // title: \"title\",\n // href: \"url\",\n // target: \"_blank\"\n // })\n ]\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":158,"value":"`\nselect time_period,\n sum(TOT_REV) as TOT_REV,\n ${cbc_variable_interes_list.map(d => `sum(${d}) as ${d},`)}\nfrom oecd_puerto_rico\ngroup by time_period\n`","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":109,"value":"db_client.query(`\nselect time_period,\n sum(TOT_REV) as TOT_REV,\n ${cbc_variable_interes_no_aggregate_list.map(d => `sum(${d}) as ${d}`).join(', ')}\nfrom oecd_puerto_rico\ngroup by time_period\n`)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":100,"value":"## Datos","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":50,"value":"from oecd_puerto_rico","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db_client"},{"id":616,"value":"from cbc_variable","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db_client"},{"id":667,"value":"from countries","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db_client"},{"id":633,"value":"cbc_variable_en = Object({\n 'ETR': 'Effective Tax Rate (ETR)',\n 'QDMTT': 'Qualifying Domestic Minimum Top-up Tax (QDMTT)',\n 'SBIE': 'Substance-Based Income Exclusion (SBIE)',\n 'TTR': 'Top-up Tax Rate (TTR)',\n 'IIR': 'Income Inclusion Rule (IIR)'\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":318,"value":"cbc_variable_es = Object({\n 'TOT_REV': 'Ingresos totales',\n 'PROFIT': 'Beneficios',\n 'TAX_ACCRUED': 'Impuestos devengados',\n 'EMPLOYEES': 'Empleos',\n 'ENTITIES_COUNT': 'Subsidiarias',\n 'ETR': 'Tasa efectiva contributiva',\n 'ASSETS': 'Activos tangibles',\n 'EARNINGS': 'Ganancias acumuladas',\n 'PAYROLL': 'Nómina',\n 'TAX_BASE': 'Base tributaria',\n 'SBIE': 'Exclusión de ingresos basada en la sustancia',\n 'EXCESS_PROFIT': 'Beneficio excedente',\n 'TTR': 'Impuesto complementario',\n 'QDMTT': 'Impuesto complementario mínimo nacional calificado (QDMTT)',\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":8,"value":"# Apéndice","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":5,"value":"$3e","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":13,"value":"## Database setup","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":15,"value":"duckdb = import(\n \"https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.1-dev232.0/+esm\"\n)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":371,"value":"from cou_regions","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db_client"},{"id":17,"value":"$3f","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":692,"value":"cou_iso = {\n const cou_iso = await iso_file.csv({typed: true});\n cou_iso.forEach(d => {\n d['pais'] = cou_alpha2_map[d['alpha-2']]\n })\n return cou_iso\n\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":696,"value":"cou_alpha2_map = {\n const paises = await paises_file.json()\n let thedata = {}\n paises.forEach(d => {\n thedata[d.code] = d.country\n })\n return thedata\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":765,"value":"import {world, land} from \"@observablehq/plot-live-earthquake-map\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":62,"value":"import {DuckDBClientCompat} from \"@jzavala-gonzalez/duckdb-tpc-h\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":793,"value":"weighted_avg_salary = {\n let salary = 41840.46 // el valor original calculado\n \n // salary = salary * 1.20 // prima de 20% // Leave for later en el SQL\n salary = Math.round(salary * 100) / 100 // round a dos decimales\n\n return salary\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1196,"value":"select oecd.*, round(me.earnings * 12 * 1.20, 2) as average_salary\nfrom oecd_all_countries_file as oecd\nleft join monthly_earnings_simplified_file as me\non oecd.jur = me.ref_area","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db_client"},{"id":1208,"value":"from oecd_all_countries","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db_client"},{"id":1238,"value":"from monthly_earnings_simplified","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db_client"},{"id":31,"value":"$40","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":805,"value":"from cbc_variable","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db_client"},{"id":42,"value":"$41","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":354,"value":"encoder.encode('abcd').buffer","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":45,"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":20,"value":"## File attachments","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":22,"value":"cbc_variable_file = FileAttachment(\"cbc_variable@1.csv\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":24,"value":"oecd_puerto_rico_file = FileAttachment(\"oecd_puerto_rico@4.csv\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1193,"value":"oecd_all_countries_file = FileAttachment(\"oecd_all_countries@1.parquet\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1199,"value":"monthly_earnings_simplified_file = FileAttachment(\"monthly_earnings_simplified.csv\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":664,"value":"iso_file = FileAttachment(\"ISO.csv\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":694,"value":"paises_file = FileAttachment(\"paises.json\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":356,"value":"encoder = new TextEncoder();","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":431,"value":"oecd_puerto_rico_file.csv({typed: true})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":86,"value":"## Estilos","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":88,"value":"fonts_link = \"https://use.typekit.net/szl4srx.css\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":90,"value":"fonts_link_element = \n// Importante para ver los fonts en el editor de Observable (o sea aqui)\nhtl.html`\n\n`","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":93,"value":"estilos = htl.html`\n\n`","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":96,"value":"import { EA_COLORES_MAP } from \"22dc6182207c44c8\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":941,"value":"import {downloadbuttonCSV} from \"@pavlos/download-buttons\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1319,"value":"chroma = require(\"chroma-js@2.4.2\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W42","stdlib":"1","titling":"auto"}} 30:"$2f:body"