2b:null 2c:["$","title",null,{"children":"Analysis of Ukraine municipality financials | atsokol | Observable"}] 31:T482,Plot.plot({ title: `Change in ${selectIndicator.name}, ${month_max + 1}m ${selectYear} vs ${baseYear} YoY`, marginLeft: 100, marginRight: 100, label: null, x: { axis: null, percent: true }, color: { scheme: "PiYG", type: "ordinal" }, marks: [ Plot.barX(data_change, { x: "pct_change", y: "city", fill: (d) => d.pct_change > 0, sort: { y: "x" } }), Plot.barX(data_change.filter(d => d.city === selectCity), { x: "pct_change", y: "city", fill: "#1f77b4", sort: { y: "x" } }), Plot.gridX({ stroke: "white", strokeOpacity: 0.5 }), d3 .groups(data_change, (d) => d.pct_change > 0) .map(([growth, cities]) => [ Plot.axisY({ x: 0, ticks: cities.map((d) => d.city), tickSize: 0, anchor: growth ? "left" : "right" }), Plot.textX(cities, { x: "pct_change", y: "city", text: ((f) => (d) => f(d.pct_change))(d3.format("+.1%")), textAnchor: growth ? "start" : "end", dx: growth ? 4 : -4, }) ]), Plot.ruleX([0]) ] })32:T404,Plot.plot({ title: `${selectIndicator.name} (cumulative YTD) in ${selectCity}`, marginLeft: 50, marginRight: 50, x: { transform: (d) => new Date(2021, d, 1), tickFormat: "%b", line: true, label: null }, y: { nice: true, grid: true, zero: true, label: "million UAH" }, color: { scheme: "RdYlBu", label: "colder" }, marks: [ Plot.differenceY(data_pivot.filter(d => d.city == selectCity), { x: "month", y1: "current", y2: "base", positiveFill: () => "1", negativeFill: () => "2", curve: "catmull-rom", tip: true }), Plot.text( data_pivot.filter(d => d.city == selectCity), Plot.selectMaxY({ x: "month", y: "current", text: () => `Current year (${selectYear})` , dy: -6 }) ), Plot.text( data_pivot.filter(d => d.city == selectCity), Plot.selectMaxY({ x: "month", y: "base", text: () => `Base year (${baseYear})`, dy: -6 }) ), ] })33:T4aa,function wfd_plot(data, title) { const colorDomain = ["Increase", "Decrease", "Total"], colorRange = ["#649334", "#cc392b", "#1f77b4"]; return Plot.plot({ title: title, width: width, x: { align: 0, round: false, domain: data.map(d => d.key) }, y: { grid: true, nice: true, label: "million UAH", //tickFormat: d3.format(".2s") }, color: { domain: colorDomain, range: colorRange }, marks: [ Plot.barY(data, { x: "key", y1: "prior", y2: "accu", fill: (d,i) => (i === 0 || i === data.length - 1) ? "Total": d.value >= 0 ? "Increase" : "Decrease", title: d => `${d.key}\nProfit: ${fmt(d.value)}\nRunning Total: ${fmt(d.accu)}` }), Plot.ruleY(data, { x1: "key", x2: "nextKey", y: "accu", strokeDasharray: "1.5" }), Plot.ruleY([0], {strokeDasharray: "1.5"}), plotLabel(data.filter(d => d.value >= 0), -7), plotLabel(data.filter(d => d.value < 0), 7), Plot.axisX({label: null, lineWidth: 8.5, fontSize: 12, marginBottom: 70}), ] }) }34:T49b,function wf_plot(data, title) { const colorDomain = ["Increase", "Decrease", "Total"], colorRange = ["#649334", "#cc392b", "#1f77b4"]; return Plot.plot({ title: title, width: width, x: { align: 0, round: false, domain: data.map(d => d.key) }, y: { grid: true, nice: true, label: "million UAH", //tickFormat: d3.format(".2s") }, color: { domain: colorDomain, range: colorRange }, marks: [ Plot.barY(data, { x: "key", y1: "prior", y2: "accu", fill: (d,i) => i === data.length - 1 ? "Total": d.value >= 0 ? "Increase" : "Decrease", title: d => `${d.key}\nProfit: ${fmt(d.value)}\nRunning Total: ${fmt(d.accu)}` }), Plot.ruleY(data, { x1: "key", x2: "nextKey", y: "accu", strokeDasharray: "1.5" }), Plot.ruleY([0], {strokeDasharray: "1.5"}), plotLabel(data.filter(d => d.value >= 0), -7), plotLabel(data.filter(d => d.value < 0), 7), Plot.axisX({label: null, lineWidth: 8.5, fontSize: 12, marginBottom: 70}), ] }) }35:T411,data = { // Helper function to get the primary group code from ancestors const aggData = (tab) => { return aq.from(tab) .filter(d => d.FUND_TYP == "T") .groupby("CITY", "REP_PERIOD") .derive({value: d => d.FAKT_AMT / 1000000}) }; let inc_agg = aggData(inc) .rollup({income: op.sum("value")}) let inc_curr_agg = aggData(curr_inc) .rollup({income_curr: op.sum("value")}) let inc_core_agg = aggData(core_inc) .rollup({income_core: op.sum("value")}) let inc_transfer_agg = aggData(transfer_inc) .rollup({income_transfers: op.sum("value")}) let exp_agg = aggData(exp_e) .rollup({expense: op.sum("value")}) let exp_curr_agg = aggData(curr_exp) .rollup({expense_curr: op.sum("value")}) return inc_agg .join_left(inc_curr_agg) .join_left(inc_core_agg) .join_left(inc_transfer_agg) .join_left(exp_agg) .join_left(exp_curr_agg) .derive({curr_surplus: d => d.income_curr - d.expense_curr}) .orderby("CITY", "REP_PERIOD") .objects() }36:Tc6e,// Transform data into format for waterfall chart function waterfall_diff(data, c_table, codeString, selectCat, adjust = false) { // Convert table with codes into a tree let c_tree = d3.stratify() .id((d) => d.code) .parentId((d) => d.parentCode) (c_table); // Extract nodes with primary codes let c_leaves = c_tree.descendants().find(d => d.id == selectCat.code).copy().descendants(); // Helper function to get the primary group code from ancestors const getGroupCode = (node) => { const ancestor = node.ancestors().find(d => d.depth === 1); return ancestor ? ancestor.data.code : null; }; // construct a mapping of primary codes to their group codes let codeMap = c_leaves.map(d => ({ code: d.data.code, groupCode: getGroupCode(d) // check if relevant node has an ancestor in the first level of aggregation })) // Filter and transform the `data` array let data_select = data .filter(d => d.FUND_TYP == "T") .filter(d => d.CITY == selectCity) .map(d => ({...d, code: d[codeString], YEAR: d.REP_PERIOD ? new Date(d.REP_PERIOD).getUTCFullYear() : null, MONTH:d.REP_PERIOD ? new Date(d.REP_PERIOD).getUTCMonth() : null, })) .filter(d => codeMap.map(D => D.code).includes(d.code)) // Transform and aggregate data using Arquero let data_agg = aq.from(data_select) .params({ selectYear: selectYear, baseYear: baseYear, selectCity: selectCity, month_max: month_max, adjust: adjust}) .join_left(aq.from(codeMap), "code") .derive({ code: d => d.groupCode, // code aggregation value: d => d.FAKT_AMT / 1000000 / (adjust ? 2 : 1) // correcting the mistake of expense doubling in the program expense file }) .select("CITY", "YEAR", "MONTH", "code", "value") .filter(d => d.YEAR == selectYear || d.YEAR == baseYear) .groupby("code", "YEAR", "CITY") .filter(d => d.MONTH == month_max) .rollup({value: op.sum("value")}) .orderby("code") .join_left(aq.from(c_table), "code") // Calculate the sum by column for each year let sumByCol = data_agg .groupby("YEAR") .rollup({Total: op.sum("value")}) .derive({Total_lag: op.lag("Total")}) // Process the final array with labeled keys and accumulated values let arr = data_agg .groupby("code") .derive(({value_diff: d => d.value - op.lag(d.value)})) .groupby("YEAR") .pivot("name", "value_diff", {sort: false}) .join_left(sumByCol, "YEAR") .filter(d => d.YEAR == selectYear) .relocate("Total_lag", {before: 1}) .fold([aq.not("YEAR")]) .derive({nextKey: d => op.lead(d.key), accu: aq.rolling(d => op.sum(d.value))}) .derive({accu: d => d.key == "Total" ? d.value : d.accu, prior: d => d.key == "Total" ? 0 : op.lag(d.accu) ?? 0,}) .objects() .map(d => ({...d, key: d.key === "Total" ? `${selectCat.name} \n${month_max + 1}m ${selectYear}` : d.key === "Total_lag" ? `${selectCat.name} \n${month_max + 1}m ${baseYear}` : d.key })) // change labels to include relevant period return arr }37:Tb15,// Transform data into format for waterfall chart function waterfall(data, c_table, codeString, selectCat, adjust = false) { // Convert table with codes into a tree let c_tree = d3.stratify() .id((d) => d.code) .parentId((d) => d.parentCode) (c_table); // Extract nodes with primary codes let c_leaves = c_tree.descendants().find(d => d.id == selectCat.code).copy().descendants(); // Helper function to get the primary group code from ancestors const getGroupCode = (node) => { const ancestor = node.ancestors().find(d => d.depth === 1); return ancestor ? ancestor.data.code : null; }; // construct a mapping of primary codes to their group codes let codeMap = c_leaves.map(d => ({ code: d.data.code, groupCode: getGroupCode(d) // check if relevant node has an ancestor in the first level of aggregation })) // Filter and transform the `data` array let data_select = data .filter(d => d.FUND_TYP == "T") .filter(d => d.CITY == selectCity) .map(d => ({...d, code: d[codeString], YEAR: d.REP_PERIOD ? new Date(d.REP_PERIOD).getUTCFullYear() : null, MONTH:d.REP_PERIOD ? new Date(d.REP_PERIOD).getUTCMonth() : null, })) .filter(d => codeMap.map(D => D.code).includes(d.code)) // Transform and aggregate data using Arquero let data_agg = aq.from(data_select) .params({ selectYear: selectYear, selectCity: selectCity, month_max: month_max, adjust: adjust}) .join_left(aq.from(codeMap), "code") .derive({ code: d => d.groupCode, // code aggregation value: d => d.FAKT_AMT / 1000000 / (adjust ? 2 : 1) // correcting the mistake of expense doubling in the program expense file }) .select("CITY", "YEAR", "MONTH", "code", "value") .filter(d => d.YEAR == selectYear) .groupby("code", "YEAR", "CITY") .filter(d => d.MONTH == month_max) .rollup({value: op.sum("value")}) .orderby("code") .join_left(aq.from(c_table), "code") // Calculate the sum by column for each year let sumByCol = data_agg .groupby("YEAR") .rollup({Total: op.sum("value")}) // Process the final array with labeled keys and accumulated values let arr = data_agg .groupby("code") .groupby("YEAR") .pivot("name", "value", {sort: false}) .join_left(sumByCol, "YEAR") .fold([aq.not("YEAR")]) .derive({nextKey: d => op.lead(d.key), accu: aq.rolling(d => op.sum(d.value))}) .derive({accu: d => d.key == "Total" ? d.value : d.accu, prior: d => d.key == "Total" ? 0 : op.lag(d.accu) ?? 0,}) .objects() .map(d => ({...d, key: d.key === "Total" ? `${selectCat.name} \n${month_max + 1}m ${selectYear}` : d.key })) // change labels to include relevant period return arr }38:T7ae,new_combi_tree = d3.hierarchy( { name: "root", code: 0, children: [ { name: "Tax revenues", code: 100000000, children: [ { name: "Personal income tax", code: 110100000, children: get_codes_ex(inck_table, [11010000]) }, { name: "Unified income tax", code: 180500000, children: get_codes_ex(inck_table, [18050000]) }, { name: "Other tax revenues", code: 100000001, children: get_codes_ex(inck_table, [10000000], [11010000, 18050000]) } ] }, { name: "Non-tax revenues", code: 200000000, children: get_codes_ex(inck_table, [20000000, 50000000], [21010500, 21010700, 21050000, 24110000]) }, { name: "Capital revenues", code: 300000000, children: get_codes_ex(inck_table, [30000000, 42000000, 21010500, 21010700, 21050000, 24110000]) }, { name: "Incoming Transfers", code: 400000000, children: get_codes_ex(inck_table, [40000000], [42000000]) }, { name: "Staff costs", code: 21000, children: get_codes_ex(kek_table, [2100]) }, { name: "Purchase of materials", code: 22000, children: get_codes_ex(kek_table, [2210, 2220,2230,2240]) }, { name: "Discretionary expenditures", code: 22500, children: get_codes_ex(kek_table, [2250, 2260, 2282, 2800, 9000]) }, { name: "Utility payments", code: 22700, children: get_codes_ex(kek_table, [2270]) }, { name: "Capital expenditures", code: 30000, children: get_codes_ex(kek_table, [3100, 2281]) }, { name: "Capital transfers", code: 32000, children: get_codes_ex(kek_table, [3200]) }, { name: "Outgoing transfers", code: 26000, children: get_codes_ex(kek_table, [2600, 2700]) }, { name: "Interest paid", code: 24000, children: get_codes_ex(kek_table, [2400]) } ] } )39:[{"id":"960d05e23e4dfb885789d34dd436d73ad405b02e06a569691d9d87bd9f0a80b458dac3a4edaf3af994004d73587f6a37274ba2d517c81d00d4cc7700544a323a","href":"https://static.observableusercontent.com/files/960d05e23e4dfb885789d34dd436d73ad405b02e06a569691d9d87bd9f0a80b458dac3a4edaf3af994004d73587f6a37274ba2d517c81d00d4cc7700544a323a","name":"INC.json","type":"application/json","lastModified":1738072868374.917,"size":714435},{"id":"7fcef354ee2a87580af608681524653fa9410e8cd6a10f2cc7c3d5d5dc8d2469e99bd7fe30fdb4ab0497b1a7f4609094f85be67f5ab9215e02f8c756e3f6c1a8","href":"https://static.observableusercontent.com/files/7fcef354ee2a87580af608681524653fa9410e8cd6a10f2cc7c3d5d5dc8d2469e99bd7fe30fdb4ab0497b1a7f4609094f85be67f5ab9215e02f8c756e3f6c1a8","name":"KEK.json","type":"application/json","lastModified":1738072868374.917,"size":32024},{"id":"802df0eb68b3335a3e349aad5f322d7b69e7d214ef588c3c135150363da78dfa6907eb7523062c6853ca9095c8139b2c27d60fdc4878e7ecb2aebcc7bec27d20","href":"https://static.observableusercontent.com/files/802df0eb68b3335a3e349aad5f322d7b69e7d214ef588c3c135150363da78dfa6907eb7523062c6853ca9095c8139b2c27d60fdc4878e7ecb2aebcc7bec27d20","name":"KFK","type":"application/octet-stream","lastModified":1738072868374.917,"size":51658}] 2f:{"id":"af9e0aee4f465314","slug":null,"title":"Analysis of Ukraine municipality financials","thumbnail":"00315d4119ca9d72c44b0479d2dbbe915f6db940229f7e9ef65c2a3e2dbd2d59","access_level":"public","create_time":"$D2025-01-28T14:01:08.374Z","update_time":"$D2025-11-29T19:56:01.278Z","view_time":null,"first_public_time":"$D2025-02-09T19:22:08.916Z","has_unpublished_edits":null,"fork":{"id":"d7e1f2e45c8694ae","slug":null,"title":"Ukraine municipal budget analysis","owner":{"id":"67596cf83e89a91c","login":"atsokol","name":"atsokol"}},"stars":0,"starred":false,"owner":{"type":"team","id":"67596cf83e89a91c","login":"atsokol","name":"atsokol","avatar_url":"https://avatars-next.observableusercontent.com/377abaada84daeb487e417deeb4779c04a819d8e607bfcf58c388483815ab8e4","tier":"starter_2024"},"version":3504,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"1cc75fc7f25f3d6a","login":"atsokol","name":"Andrii T","avatar_url":"https://avatars-next.observableusercontent.com/e5b1ecc1785c25cd2a558eb586101a317755d10435b6e2122a918d53245d5ab0","tier":null}],"members":[{"type":"individual","id":"1cc75fc7f25f3d6a","login":"atsokol","name":"Andrii T","avatar_url":"https://avatars-next.observableusercontent.com/e5b1ecc1785c25cd2a558eb586101a317755d10435b6e2122a918d53245d5ab0","tier":null,"role":"editor"},{"type":"team","id":"67596cf83e89a91c","login":"atsokol","name":"atsokol","avatar_url":"https://avatars-next.observableusercontent.com/377abaada84daeb487e417deeb4779c04a819d8e607bfcf58c388483815ab8e4","tier":"starter_2024","role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":{"id":"0a4780b24b3316c1","slug":"infra-energy-data-viz","title":"Infra & Energy Data Viz","owner":{"id":"67596cf83e89a91c","login":"atsokol","name":"atsokol"}},"type":"notebook","body":{"title":"Analysis of Ukraine municipality financials","cells":[{"id":0,"value":"# Analysis of Ukraine municipality financials","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":523,"value":"Analysis is based on the data from Open Budget, an open resource maintained by the Ministry of Finance of Ukraine:\nhttps://openbudget.gov.ua\n\nData is automatically updated on a monthly basis using an R script deployed here: https://github.com/atsokol/openbudget-data-update","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":520,"value":"## Trends by city","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":50,"value":"viewof selectCity = Inputs.select(cities, {label: \"Select city\", value: \"Cherkasy\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":167,"value":"viewof selectIndicator = Inputs.select(indicators, {label: \"Select indicator\", format: d => d.name})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":160,"value":"viewof selectYear = Inputs.select([2022,2023,2024,2025], {label: \"Select year\", value: 2025, format: d => d.toString()})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":646,"value":"viewof baseYear = Inputs.select([2021,2022,2023, 2024], {label: \"Base year\", value: 2021, format: d => d.toString()})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":305,"value":"$31","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":406,"value":"Plot.plot({\n title: `Share of transfers in municipal revenues: ${selectYear} vs ${baseYear}`,\n marginLeft: 100,\n x: {\n axis: \"top\",\n grid: true,\n percent: true,\n label: \"Share of total (%)\",\n },\n y: {label: null},\n color: {\n scheme: \"BuRd\",\n reverse:true,\n //label: \"Change\",\n //legend: true,\n tickFormat: \".0%\"\n },\n marks: [\n Plot.ruleX([0]),\n Plot.arrow(transferShare, {\n x1: `${baseYear}`,\n y1: \"CITY\",\n x2: `${selectYear}`,\n y2: \"CITY\",\n bend: false,\n stroke: d => (d[selectYear] - d[baseYear])>0,\n sort: {y: \"x2\", reverse: true},\n }),\n Plot.dot(transferShare, {\n x: `${selectYear}`,\n y: \"CITY\",\n opacity: 0.7,\n r: 2.5,\n stroke: d => (d[selectYear] - d[baseYear])>0,\n tip: true\n })\n \n ]\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3411,"value":"selectCity4 = Inputs.bind(Inputs.select(cities, {label: \"Select city\", value: \"Cherkasy\"}), viewof selectCity)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":12,"value":"Plot.plot({\n title: `${selectIndicator.name} (cumulative) in ${selectCity}`,\n marginLeft: 50,\n marginRight: 30,\n x: {\n // map all dates to 2021\n transform: (d) => new Date(2021, d.getMonth(), d.getDay()),\n tickFormat: \"%b\",\n line: true,\n label: null\n },\n y: { nice: true, grid: true, zero: true, label: \"million UAH\"},\n color: {type: \"categorical\", legend: false},\n marks: [\n Plot.line(data.filter(d => d.CITY == selectCity), {\n x: \"REP_PERIOD\",\n y: selectIndicator.indicator,\n stroke: ({REP_PERIOD}) => REP_PERIOD.getUTCFullYear()\n }),\n Plot.text(\n data.filter(d => d.CITY == selectCity),\n Plot.selectLast({\n x: \"REP_PERIOD\",\n y: selectIndicator.indicator,\n text: d => `${d.REP_PERIOD.getUTCFullYear()}`,\n fill: d => d.REP_PERIOD.getUTCFullYear(),\n dy: -6,\n dx: 12\n })\n ),\n ]\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":113,"value":"$32","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3328,"value":"wf_plot(new_curr_surplus_wf, `Current surplus waterfall in\\ ${selectCity} ${selectYear}`)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3340,"value":"wfd_plot(new_curr_surplus_wfd, `Current surplus change in ${selectCity} ${selectYear} vs ${baseYear}`)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1656,"value":"$33","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3086,"value":"$34","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":300,"value":"
\n

Annex: calculations

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":3153,"value":"

Additional inputs

","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":3149,"value":"#### Select income categories to exclude from calculation of Current Surplus","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":3111,"value":"viewof selectCapitalIncomeCat = Inputs.table(inck_table.filter(d => d.level < 4), { \n columns: [\"level\", \"code\", \"name\"], \n value: inck_table.filter(\n d => \n [30000000, 42000000, 21050000, 24110000, 21010500, 21010700, 21010800, 21010900]\n .includes(d.code)\n ),\n multiple: true,\n required: false, \n sort: \"code\", \n layout: \"auto\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3147,"value":"#### Select expense categories to exclude from calculation of Current Surplus","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":3101,"value":"viewof selectCapitalExpenseCat = Inputs.table(kek_table, { \n columns: [\"level\", \"code\", \"name\"], \n value: kek_table.filter(\n d => \n [2281, 3000]\n .includes(d.code)\n ),\n multiple: true,\n required: false, \n sort: \"code\", \n layout: \"auto\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2581,"value":"### Horizontal comparison charts","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":566,"value":"data_yoy = aq.from(data_transform)\n .params({selectYear: selectYear, month_max: month_max})\n .groupby([\"CITY\", \"MONTH\"])\n .derive({income_yoy: d => d.income / op.first_value(d.income)})\n .groupby([\"CITY\", \"YEAR\"])\n .filter(d => d.MONTH == month_max && d.YEAR == selectYear)\n .objects()\n ","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":311,"value":"data_change = aq.from(data_pivot)\n .groupby(\"city\")\n .filter(d => d.current != undefined)\n .filter(d => d.month == op.max(d.month))\n .derive({pct_change: d => (d.current - d.base) / d.base})\n .objects()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":143,"value":"data_pivot = aq.from(data_transform.filter(d => d.YEAR == baseYear | d.YEAR == selectYear))\n .groupby([\"CITY\", \"MONTH\"])\n .pivot(['YEAR'], [selectIndicator.indicator])\n .rename(aq.names([\"city\", \"month\", 'base', 'current']))\n .objects()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":218,"value":"data_transform = data.map(d => ({...d, \n YEAR: d.REP_PERIOD.getUTCFullYear(), \n MONTH: d.REP_PERIOD.getMonth() }))\n","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":725,"value":"//plotLabel = (data, anchor) => \nplotLabel = (data, dy) => \n Plot.text(data, {\n x: \"key\",\n y: \"accu\",\n dy: dy, \n //frameAnchor: anchor,\n fontWeight: \"bold\",\n text: d => d3.format(\",d\")(d.value)\n })","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":731,"value":"fmt = n => d3.format(\",d\")(n)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":531,"value":"indicators = [\n {name: \"Revenues\", indicator: \"income\"},\n {name: \"Core revenues (without transfers)\", indicator: \"income_core\"},\n {name: \"Current surplus\", indicator: \"curr_surplus\"}\n]","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":66,"value":"cities = Array.from(new Set(data.map(d => d.CITY)))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2521,"value":"transferShare = {\n\n let data_share = data\n .map(d => ({...d, \n share: d.income_transfers / d.income,\n YEAR: d.REP_PERIOD.getUTCFullYear(), \n MONTH: d.REP_PERIOD.getMonth() }))\n\n return aq.from(data_share)\n .params({selectYear: selectYear, baseYear: baseYear})\n .groupby(\"CITY\", \"YEAR\")\n .filter(d => d.MONTH == op.max(d.MONTH))\n .filter(d => d.YEAR == selectYear || d.YEAR == baseYear)\n .select(\"CITY\", \"YEAR\", \"share\")\n .groupby(\"CITY\")\n .pivot(\"YEAR\", \"share\")\n .objects()\n}\n\n\n","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2408,"value":"$35","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2161,"value":"### Aggregate data for icicle and waterfall charts","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1637,"value":"inc_trtab = get_treetab(inc, inck_table, \"COD_INCO\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1312,"value":"exp_e_trtab = get_treetab(exp_e, kek_table, \"COD_CONS_EK\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1049,"value":"exp_f_trtab = get_treetab(exp_f, kfk_table, \"COD_CONS_MB_FK\", true)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2324,"value":"function get_treetab(data, c_table, codeString, adjust = false) {\n let data_select = data\n .filter(d => d.FUND_TYP == \"T\") \n .filter(d => d.CITY == selectCity)\n .map(d => ({...d, \n value: d.FAKT_AMT / 1000000 / (adjust ? 2 : 1), // correcting the mistake of expense doubling in the program expense file\n code: d[codeString],\n YEAR: d.REP_PERIOD.getUTCFullYear(), \n MONTH: d.REP_PERIOD.getMonth() }))\n .filter(d => d.YEAR == selectYear)\n\n\n let data_transform = aq.from(data_select)\n .params({month_max: month_max})\n .filter(d => d.MONTH == month_max)\n .select(\"code\", \"value\")\n .groupby(\"code\")\n .rollup({value: op.sum(\"value\")})\n .orderby(\"code\")\n\n return aq.from(c_table)\n .join_left(data_transform, \"code\")\n .objects()\n\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2772,"value":"curr_exp_wfd = waterfall_diff(curr_exp, kek_table, \"COD_CONS_EK\", {code: 2000, name: \"Total\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2750,"value":"curr_inc_wfd = waterfall_diff(curr_inc, inck_table, \"COD_INCO\", {code: 0, name: \"Total\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3334,"value":"new_curr_surplus_wfd = waterfall_diff(curr_surplus, new_combi_table, \"COD\", {code: 0, name: \"Current surplus\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2815,"value":"curr_surplus_wfd = waterfall_diff(curr_surplus, combi_table, \"COD\", {code: 0, name: \"Current surplus\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3305,"value":"new_curr_surplus_wf = waterfall(curr_surplus, new_combi_table, \"COD\", {code: 0, name: \"Current surplus\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3065,"value":"curr_surplus_wf = waterfall(curr_surplus, combi_table, \"COD\", {code: 0, name: \"Current surplus\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1615,"value":"$36","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3063,"value":"$37","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1588,"value":"month_max = {\n const months = inc\n .filter(d => d.REP_PERIOD.getUTCFullYear() == selectYear)\n .map(d => d.REP_PERIOD.getUTCMonth())\n \n return Math.max(...months)\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3004,"value":"### Data transforms for Current Surplus","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":2719,"value":"function get_codes(c_table, base_codes) {\n let tree = d3.stratify()\n .id((d) => d.code)\n .parentId((d) => d.parentCode)\n (c_table);\n\n let tree_codes = []; \n for (let code of base_codes) {\n let arr = tree.descendants().find(d => d.id == code).copy().descendants().map(d => d.data.code)\n\n tree_codes = tree_codes.concat(arr)\n }\n return tree_codes\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2755,"value":"curr_inc = {\n // get all codes descendant from \n let inc_cap_codes = get_codes(inck_table, selectCapitalIncomeCat.map(d => d.code));\n\n return inc.filter(d => !inc_cap_codes.includes(d.COD_INCO))\n \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2888,"value":"core_inc = {\n let base_codes = [10000000, 20000000, 50000000];\n \n let inc_core_codes = get_codes(inck_table, base_codes);\n\n return curr_inc.filter(d => inc_core_codes.includes(d.COD_INCO))\n \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2905,"value":"transfer_inc = {\n let base_codes = [40000000];\n \n let inc_trans_codes = get_codes(inck_table, base_codes);\n\n return curr_inc.filter(d => inc_trans_codes.includes(d.COD_INCO))\n \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2761,"value":"curr_exp = {\n let exp_cap_codes = get_codes(kek_table, selectCapitalExpenseCat.map(d => d.code));\n\n return exp_e.filter(d => !exp_cap_codes.includes(d.COD_CONS_EK))\n \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2817,"value":"curr_surplus = [\n ...curr_inc.map(d => ({...d, COD: d.COD_INCO, FAKT_AMT: +d.FAKT_AMT})),\n ...curr_exp.map(d => ({...d, COD: d.COD_CONS_EK, FAKT_AMT: -d.FAKT_AMT})),\n]","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2067,"value":"#### Data files\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1608,"value":"inc = d3.csv('https://raw.githubusercontent.com/atsokol/openbudget-data-update/main/data/incomes.csv', d3.autoType)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2124,"value":"exp_f = d3.csv('https://raw.githubusercontent.com/atsokol/openbudget-data-update/main/data/expenses_functional.csv', d3.autoType)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1302,"value":"exp_e = d3.csv('https://raw.githubusercontent.com/atsokol/openbudget-data-update/main/data/expenses.csv', d3.autoType)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2062,"value":"#### Budget classificators","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":2790,"value":"combi_table = [\n {code: 0, parentCode: null, name: \"Current surplus\", level: 0},\n ...inck_table.slice(1, inck_table.length),\n ...kek_table.slice(1, kek_table.length)\n .filter(d => d.code != 2000) \n .map(d => ({...d, parentCode: d.parentCode == 2000 ? 0 : d.parentCode})) \n]\n\n","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1604,"value":"inck_table = [\n {code: 0, parentCode: null, name: \"Загальні доходи\", level: 0},\n ...inck\n .filter(d => d.endDate == null) // current categorisation\n .map(d => ({code: +d.code, parentCode: +d.parentCode ?? 0, name: d.name, level: d.level}))\n .sort((a,b) => a.code - b.code)\n ]","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1601,"value":"inck = FileAttachment(\"INC.json\").json()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1359,"value":"kek_table = [\n {code: 0, parentCode: null, name: \"Загальні видатки\", level: 0},\n ...kek\n .filter(d => d.endDate == null) // current categorisation\n .map(d => ({code: +d.code, parentCode: +d.parentCode ?? 0, name: d.name, level: d.level}))\n .sort((a,b) => a.code - b.code)\n ]","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1353,"value":"kek = FileAttachment(\"KEK.json\").json()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1142,"value":"kfk_table = [\n {code: 0, parentCode: null, name: \"Загальні видатки\", level: 0},\n ...kfk\n .filter(d => d.endDate == null) // current categorisation\n .map(d => ({code: +d.code, parentCode: +d.parentCode ?? 0, name: d.name, level: d.level}))\n .sort((a,b) => a.code - b.code)\n ]","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1140,"value":"kfk = FileAttachment(\"KFK\").json()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3209,"value":"#### Download files","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":3285,"value":"new_combi_table = hierarchyToTable(new_combi_tree)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3239,"value":"$38","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3251,"value":"// Convert d3.hierarchy to a flat table format\nfunction hierarchyToTable(root) {\n // Initialize an empty array to hold the table data\n let table = [];\n\n // Use recursion or iteration to traverse the hierarchy\n root.each((node) => {\n // For each node, add an object to the table with the relevant properties\n table.push({\n code: node.data.code, // Unique ID of the node\n name: node.data.name, // Name or label of the node\n parentCode: node.parent ? node.parent.data.code : null, // ID of the parent node, or null if root\n // Add any other properties you need from node.data\n });\n });\n\n return table;\n}\n","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3243,"value":"function get_codes_ex(c_table, base_codes, exclude_codes = []) {\n let tree = d3.stratify()\n .id((d) => d.code)\n .parentId((d) => d.parentCode)\n (c_table);\n\n let tree_codes = []; \n for (let code of base_codes) {\n let arr = tree.descendants().find(d => d.id == code).copy().descendants().map(d => ({code: d.data.code, name: d.data.name}))\n\n tree_codes = tree_codes.concat(arr)\n }\n\n let ex_codes = [];\n for (let code of exclude_codes) {\n let arr = tree.descendants().find(d => d.id == code).copy().descendants().map(d => d.data.code)\n ex_codes = ex_codes.concat(arr)\n }\n \n return tree_codes.filter(d => !ex_codes.includes(d.code))\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3186,"value":"tab = xlsx.utils.json_to_sheet(data_yoy)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3189,"value":"write_sheet = out_sheet => {\n const wb = xlsx.utils.book_new()\n xlsx.utils.book_append_sheet(wb, out_sheet, 'output')\n xlsx.writeFile(wb, 'out.xlsx')\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3197,"value":"","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":2074,"value":"#### Import libraries","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1178,"value":"import {Icicle} from \"@d3/icicle-component\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":139,"value":"import { aq, op } from '@uwdata/arquero';","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3181,"value":"xlsx = require('https://bundle.run/xlsx@0.14.1')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W39","stdlib":"1","titling":"auto"}} 30:"$2f:body"