28:null 29:["$","title",null,{"children":"Results Malnutrition Act Cleaned Up | Marjolein Oostrom | Observable"}] 2f:T289d,function circle_chart(root, duns, highlight_me){ //colors used var background_color = "#f2f3f4" var highlight_color = "#D79922" var outline_color = "#4056A1" var center_circle_color = "#C5CBE3" var font_color = '#303C6C' var extra_highlight_color = '#56a140' var fontsize = d3.scaleSqrt() .domain([0, 150]) .range([5, 20]) .clamp(false) let focus = root; let view; var current_title = "Click to Zoom" const svg = d3.create("svg") .attr("viewBox", `-${width / 2} -${height /(2)} ${width} ${height}`) .style("display", "block") .style("margin", "0 0px") .style("background", background_color) //.style("cursor", "pointer") .on("mouseover", (event) => (svgMouseOut(event, root))) // tooltip + styling const toolTip = d3.select("body") .append("div") .attr("class", "toolTip") .style("position", "absolute") .style("display", "none") .style("border-radius", "1px") .style("width", width/3) .style("height", "auto") .style("background", background_color) .style("padding", "0px") .style("border", "1px solid #DDD") .style("font-size", "1.1rem") .style("text-align", "left") //https://stackoverflow.com/questions/149055/how-to-format-numbers-as-currency-strings // Create our number formatter. var formatter = new Intl.NumberFormat('en-US', { style: 'currency', currency: 'USD', notation: 'compact', // These options are needed to round to whole numbers if that's what you want. //minimumFractionDigits: 0, // (this suffices for whole numbers, but will print 2500.10 as $2,500.1) //maximumFractionDigits: 0, // (causes 2500.99 to be printed as $2,501) }); var startTime; var endTime; const node = svg.append("g") .selectAll("circle") //.data(root) .data(root.descendants().slice(1)) .join("circle") .attr("fill", d => d.children ? background_color : toTitleCase(String(d.data[0])) == toTitleCase(String(recipientdrop)) ? highlight_color : center_circle_color) .attr("stroke", d=> d.children ? highlight_color : toTitleCase(String(d.data[0])) == toTitleCase(String(recipientdrop)) ? highlight_color : outline_color) .attr("stroke-width", 2) .on("mouseover", (event, d) => (leafMouseOver(event, d), event.stopPropagation())) .on("mouseout", (event, d) => (leafMouseOut(event, d), event.stopPropagation())) .on("click", (event, d) => focus !== d ? (zoom(d)) : console.log(d)) .on("touchstart", (event, d) => focus !== d ? (leafMouseOver(event, d)) : console.log(d)) .on("touchend", (event, d) => focus !== d ? (leafMouseOut(event, d), zoom(d)) : console.log(d)) // show tooltip on mousehover function leafMouseOver(event, d){ d3.selectAll('.toolTip').style("display", "none" ) d3.select(event.target).attr("stroke-width", 10) // d3.select(event.target).attr("stroke", highlight_color) console.log(d) var formated_money =parseFloat(d.value)*parseFloat(dd3)/8992408699023 var formated_money = formatter.format(formated_money) var raw_money = formatter.format(parseFloat(d.value)) var descendents = d.ancestors().map(d => (d.data[0])).join(' -> ') if (duns == true) { var extra_info_duns = extra_info .filter(function(e, i) { return e.recipient_duns == d.data[0]}) var Reason = extra_info_duns.length == 0 ? "none": extra_info_duns[0]["Reason_in_list"] var Name = extra_info_duns.length == 0 ? "none": extra_info_duns[0]["recipient_name"] var Link = extra_info_duns.length == 0 ? "none": extra_info_duns[0]["Link"] var info_blurb = extra_info_duns.length == 0 ? "none": extra_info_duns[0]["blurb"] var html_test = extra_info_duns.length == 0 ? ` ${toTitleCase(String(d.data[0]))}
Your taxes provide : ${formated_money}
Total money : ${raw_money}
`: `${toTitleCase(Name)}
Your taxes provide : ${formated_money}
Total money : ${raw_money}
${Reason}
Blurb from link: ${info_blurb}` } else { var html_test = `${toTitleCase(String(d.data[0]))}
Your taxes provide : ${formated_money}
Total money : ${raw_money}
` } toolTip // .style("left", (width - d3.select(event.target).attr("cx")) < width/3 ? event.pageX - width/3 + "px": event.pageX + "px") .style("left", 0) .style("top", event.pageY + 10 + "px") .style("display", "block") .html(html_test) } function svgMouseOut(event, d){ d3.selectAll('.toolTip').style("display", "none" ) d3.selectAll('.title_desc').style("display", "none" ) } function leafMouseOut(event, d){ // d3.select(event.target).attr("stroke", d=> d.children ? highlight_color : d.data[0] == highlight_me ? highlight_color : outline_color) d3.select(event.target).attr("stroke-width", d=> d.data[0] == highlight_me ? 3 : 1) //toolTip.style("display", "none" ); } function get_name(d){ if (duns == true){ var extra_info_duns = extra_info .filter(function(e, i) { return e.recipient_duns == d}) var name = extra_info_duns.length == 0 ? toTitleCase(d).substring(0, 25) : toTitleCase(extra_info_duns[0].recipient_name.substring(0, 20)) } else { name = toTitleCase(d).substring(0, 25) } return name } function get_award(d){ if (duns == true){ var extra_info_duns = extra_info .filter(function(e, i) { return e.recipient_duns == d}) console.log(extra_info_duns) var name = extra_info_duns.length == 0 ? outline_color : extra_info_duns[0].Reason_in_list=="Nobel Prize Laureate"? extra_highlight_color : extra_info_duns[0].Reason_in_list==""? outline_color : highlight_color } else { name = outline_color } return name } node .attr("stroke", d => get_award(d.data[0])) const label = svg.append("g") .style("font", "10px sans-serif") .attr("text-anchor", "middle") .selectAll("text") .data(root.descendants()) .join("text") .text(d => get_name(String(d.data[0]))) const node_international_affairs = node //.attr("test", d => console.log(d.descendants().map(d => (d.data[0])))) .filter(function(d, i) { return d.data[0] == "International Affairs: Budget Function" }) .filter(function(d, i) { return i == 0 }) // if (first_item === recipientdrop) { zoom(root); // } // else { // node_international_affairs // .each(function(d) { // zoom(d); // d is datum // temp_highlight(recipientdrop) // }) // } function temp_highlight(highlight_this){ // node_international_affairs // .each(function(d) { // zoom(d) // d is datum // }) const node_selected = node .filter(function(d, i) { return d.data[0] == highlight_this}) node_selected .attr("stroke-width", 5) .attr('stroke-opacity', 1) .attr('stroke', highlight_color) } function zoomTo(v) { function grandparent_fun(d){ var grandparent_root = false if (d.parent == null | duns == false){ var grandparent_root = false } else { var grandparent_root = d.parent.parent == focus } return grandparent_root } const k = width / v[2]; view = v; //keep labels above circle so can have multipe labeling label.attr("transform", d => d == focus & duns == true ? `translate(-${0}, -${height })` : d.parent ==focus & duns == true ? `translate(${(d.x - v[0]) * k},${(d.y - v[1]) * k + d.r *k})`:`translate(${(d.x - v[0]) * k},${(d.y - v[1]) * k })`) .attr("font-size", d => fontsize(d.r * k)) // .style("fill-opacity", d =>d.parent == focus ? 1 : d == focus && d != root ? .9 : 0) .style("display", d => fontsize(d.r * k) < 10 ? "none": (d.parent == focus |grandparent_fun(d) ) ? "inline" : "none") // .style("display", d => fontsize(d.r * k) < 10 ? "none":"inline" ) node.attr("transform", d => `translate(${(d.x - v[0]) * k},${(d.y - v[1]) * k})`); node.attr("r", d => d.r * k) } function zoom(d) { const focus0 = focus; if (d != focus && d.height == 0){ d = d.parent.parent } if (d != focus && d.height == 1){ d = d.parent } focus = d; current_title = focus.data[0] console.log(current_title) if (focus0 !=focus | focus == root){ zoomTo([focus.x, focus.y, focus.r * 2])} } // var title1 = svg.append("text") // .attr("x", width / 2 - 100)//`-${width / 2} - 200`) // .attr("y", -height /2 + 100 )//`-${height / 2} - 200`) // .attr("text-anchor", "middle") // .style("cursor", "pointer") // .style("font-size", "18px") // // .on("click", (event, d) => (temp_highlight(), event.stopPropagation()) ) // .attr('font-weight', 500) // .style("display", d => recipientdrop != first_item ? "inline" : "none") // .text("Highlight Selected") // .on("mouseover", (event, d) => (d3.select(event.target).style("font-weight", "bold"), temp_highlight(), event.stopPropagation()) ) // .on("mouseout", (event, d) => d3.select(event.target).style("font-weight", "normal")) var title2 = svg.append("text") .attr("x", width / 2 - 100)//`-${width / 2} - 200`) .attr("y", -height /2 + 50 )//`-${height / 2} - 200`) .attr("text-anchor", "middle") .style("cursor", "pointer") .style("font-size", "18px") // .on("click", (event, d) => (zoom(root), event.stopPropagation()) ) .attr('font-weight', 600) .text("Zoom Out") .on("mouseover", (event, d) => (d3.select(event.target).style("font-weight", 900))) .on("click", (event, d) => ( zoom(root), event.stopPropagation())) .on("mouseout", (event, d) => d3.select(event.target).style("font-weight", 600)) temp_highlight(highlight_me) temp_highlight("482156317") return svg.node(); }30:T573,function combine_extra_info(extra_info, csv_breakdown_awards){ var csv_breakdown_awards_filtered = csv_breakdown_awards.filter(d => recip_list.includes(d.recipient_duns)) var left = d3.group(csv_breakdown_awards_filtered, d => d.recipient_duns); var right = d3.group(extra_info, d => d.recipient_duns); var sum_all_accounts = Array.from(left).flatMap(([key, lvalues]) => { return d3.cross(lvalues, right.get(key) || ["none"], (lvalue, rvalue) => { return {federal_account_symbol: lvalue.federal_account_symbol, federal_account_name: lvalue.federal_account_name, agency_name: lvalue.agency_name, budget_subfunction: lvalue.budget_subfunction, budget_function: lvalue.budget_function, value: Number(lvalue.value), recipient_name: lvalue.recipient_name, recipient_duns: lvalue.recipient_duns, Primary_Function: rvalue.Primary_Function, Sub_Function: rvalue.Sub_Function, Org_type: rvalue.Org_type, Reason_in_list: rvalue.Reason_in_list, Link: rvalue.Link, Blurb: rvalue.Blurb,} }) }); sum_all_accounts = sum_all_accounts.filter(d => d["value"] > 0) return sum_all_accounts } 31:T11d0,function combine_agencies_recipients(recipients_only, full_federal) { var recipients_only_mod = recipients_only.map(function(d) { d.value = d.transaction_obligated_amount, d.budget_function = "International Affairs", // d.budget_function = "International Affairs" + ": Budget Function", // d.agency_name = d.agency_name + ": Agency", // d.federal_account_name = d.federal_account_name + ": Account", // d.budget_subfunction = d.budget_subfunction + ": Budget Subfunction", d.recipient_name = d.recipient_duns == "71550813" ? "World Food Program" : d.recipient_duns == "" ? "No Recipient Given: Award Funds" : toTitleCase(d.recipient_name) d.top_100 = d.recipient_duns == "71550813" ? "World Food Program" : d.recipient_duns == "" ? "No Recipient Given: Award Funds" : recip_list.includes(d.recipient_name)?toTitleCase(d.recipient_name) : "Other" return d }); // total money for full federal budget var full_federal_mod = full_federal // .map(function(d) { // d.budget_function = d.budget_function + ": Budget Function", // d.agency_name = d.agency_name + ": Agency", // d.federal_account_name = d.federal_account_name + ": Account", // d.budget_subfunction = d.budget_subfunction + ": Budget Subfunction" // return d // }); //by federal_account get the sum of the transaction obligated amount spend on awards so can subtract this from total amount per federal account var subtract_all_awards = Array.from( d3.rollup( recipients_only_mod, D => d3.sum(D, d => d["transaction_obligated_amount"]) , d => d.federal_account_symbol, )).flatMap(([key, lvalues]) => { return {federal_account_symbol: key, transaction_obligated_amount: lvalues}; }); //to make sure each row represents a federal account, sum by federal account // there are five examples where the federal account is split into two rows because it has two different reporting agencies var federal_rollup = d3.rollup( full_federal_mod, D => d3.sum(D, d => d["obligations_incurred"]), d => d.federal_account_symbol, ) var federal_grouped = d3.group(full_federal_mod, d => d.federal_account_symbol); var federal_full_summed = Array.from(federal_rollup).flatMap(([key, lvalues]) => { var federal_full_key = federal_grouped.get(key)[0] return {federal_account_symbol: key, federal_account_name: federal_full_key.federal_account_name, obligations_incurred: lvalues, agency_name: federal_full_key.agency_name, budget_subfunction: federal_full_key.budget_subfunction, budget_function: federal_full_key.budget_function}; }) var left = d3.group(federal_full_summed, d => d.federal_account_symbol); var right = d3.group(subtract_all_awards, d => d.federal_account_symbol); //sum of account balances for all acounts, with sum awards for international affairs subtracted, resulting in recipients "federal_acount_name" for federal acounts not in international affaris, and "non-awards" for international affairs account obligations minus award obligations var sum_all_accounts = Array.from(left).flatMap(([key, lvalues]) => { //use "none" as placeholder for no match in awards summary // left join uisng https://talk.observablehq.com/t/how-to-left-join-csv-files/2604 return d3.cross(lvalues, right.get(key) || ["none"], (lvalue, rvalue) => { return {federal_account_symbol: lvalue.federal_account_symbol, federal_account_name: lvalue.federal_account_name, agency_name: lvalue.agency_name, budget_subfunction: lvalue.budget_subfunction, budget_function: lvalue.budget_function, value: rvalue.transaction_obligated_amount? Number(lvalue.obligations_incurred - rvalue.transaction_obligated_amount): Number(lvalue.obligations_incurred), recipient_name: rvalue.transaction_obligated_amount? "Non-Award Funds": lvalue.federal_account_name, recipient_duns: rvalue.transaction_obligated_amount? "Non-Award Funds": lvalue.federal_account_name}; }); }); var data_total = recipients_only.concat(sum_all_accounts).filter(d => d["value"] > 0) return data_total }32:T70d,SELECT account."federal_account_symbol", "federal_account_name", total_account, total_award, -- either the non-award amount of a ferderal account or just th etoal acount value CASE WHEN COALESCE(total_award, 0 ) >= 0 THEN COALESCE(total_account, 0 ) - COALESCE(total_award, 0 ) ELSE COALESCE(total_account, 0 ) END as value, CASE WHEN total_award is DISTINCT FROM NULL THEN 'Non-Award Funds' ELSE federal_account_name END as recipient_name, CASE WHEN total_award is DISTINCT FROM NULL THEN 'Non-Award Funds' ELSE federal_account_name END as recipient_duns, "agency_name", 'No awards given' as "awarding_agency_name", "budget_function", "budget_subfunction" FROM ( SELECT max("owning_agency_name") as "agency_name", "federal_account_symbol", max("federal_account_name") as "federal_account_name", max("federal_account_name") as federal_account_name, max("budget_function") as "budget_function", max("budget_subfunction") as "budget_subfunction", SUM("obligations_incurred") as total_account FROM "fy2021_pa" GROUP BY "federal_account_symbol") as account LEFT JOIN ( SELECT "federal_account_symbol", SUM("transaction_obligated_amount") as total_award FROM "fy2021_award" GROUP BY "federal_account_symbol") as award ON account."federal_account_symbol" = award."federal_account_symbol" UNION ALL SELECT "federal_account_symbol", "federal_account_name", 0 as total_account, 0 as total_award, "transaction_obligated_amount" as value, recipient_name, recipient_duns, "owning_agency_name" as agency_name, CASE WHEN awarding_agency_name is DISTINCT FROM NULL THEN awarding_agency_name ELSE concat('Owning ', owning_agency_name) END as awarding_agency_name, "budget_function", "budget_subfunction", FROM "fy2021_award" WHERE COALESCE(value, 0 ) > 0 33:[{"id":"eba5c4a6d3db6daa0406e9a4ee3f9d8e3c7b1c3ee46e7cc0a5139aada7037b4d99f51027645ca2473c91e363eac1912caedeeeadf2cc7303aa2e0f9b79a46721","href":"https://static.observableusercontent.com/files/eba5c4a6d3db6daa0406e9a4ee3f9d8e3c7b1c3ee46e7cc0a5139aada7037b4d99f51027645ca2473c91e363eac1912caedeeeadf2cc7303aa2e0f9b79a46721","name":"extra_info_recipient@4.csv","type":"text/csv","lastModified":1666676718079.622,"size":331901},{"id":"eea7d74fba75037c867ab29b48c1e66d617a0f3c53a5e66e2772f67fce82179c06e1fcddf1bb59578eb5625d61a83b3ff9e5a11e4d2793ff92aab13da84288e7","href":"https://static.observableusercontent.com/files/eea7d74fba75037c867ab29b48c1e66d617a0f3c53a5e66e2772f67fce82179c06e1fcddf1bb59578eb5625d61a83b3ff9e5a11e4d2793ff92aab13da84288e7","name":"humanitarian_aid_all.csv","type":"text/csv","lastModified":1666676718079.622,"size":45094092},{"id":"3ef46849b55d2535607bb17222ae10b0288a7696f4a113985147561fc5617dea5c04f94746239362710f5c4b3bdbdc7a98b2abc263ac307e9ea1ae6899973960","href":"https://static.observableusercontent.com/files/3ef46849b55d2535607bb17222ae10b0288a7696f4a113985147561fc5617dea5c04f94746239362710f5c4b3bdbdc7a98b2abc263ac307e9ea1ae6899973960","name":"FY2021P01-P12_All_FA_AccountBalances_2022-01-05_H17M11S14_1.csv","type":"text/csv","lastModified":1666676718079.622,"size":717380},{"id":"87522f831ccecbd8fff80114a676920bf244f3541d9b718546e7a7b87e4e8046b795704f52a3f057239480e25dc5d33bd258e282babb508368140a50baa9bc16","href":"https://static.observableusercontent.com/files/87522f831ccecbd8fff80114a676920bf244f3541d9b718546e7a7b87e4e8046b795704f52a3f057239480e25dc5d33bd258e282babb508368140a50baa9bc16","name":"extra_info_recipient.parquet","type":"application/octet-stream","lastModified":1666676718079.622,"size":189828},{"id":"cf5831d6fa8a7a314487f7ae2e274d02b118d4bdb474d9f9ff41648210be24ca0e66792252f2006e1267f72af3df11163a662cbb7de1659e4ce51aaf29fe5972","href":"https://static.observableusercontent.com/files/cf5831d6fa8a7a314487f7ae2e274d02b118d4bdb474d9f9ff41648210be24ca0e66792252f2006e1267f72af3df11163a662cbb7de1659e4ce51aaf29fe5972","name":"fy2021_pa.parquet","type":"application/octet-stream","lastModified":1666676718079.622,"size":11085620},{"id":"a802b3c4abddf9c9cd944572431a0c76291fc31b65f2233fa5a34d158d377fd637c38a1ca707988fd5b4d73adaedf97093b8a78c708bd832ffd4a571b08a12e4","href":"https://static.observableusercontent.com/files/a802b3c4abddf9c9cd944572431a0c76291fc31b65f2233fa5a34d158d377fd637c38a1ca707988fd5b4d73adaedf97093b8a78c708bd832ffd4a571b08a12e4","name":"fy2021_award.parquet","type":"application/octet-stream","lastModified":1666676718079.622,"size":3736130}] 2c:{"id":"2095cd10f9cb51cd","slug":null,"title":"Results Malnutrition Act Cleaned Up","thumbnail":"3c69fe376fa67b3246dea721d1bb5ef04735c9bd86d8c252bdffa02d39180227","access_level":"public","create_time":"$D2022-10-25T05:45:18.079Z","update_time":"$D2022-10-25T06:57:10.697Z","view_time":null,"first_public_time":"$D2022-10-25T05:45:18.079Z","fork":{"id":"51dd4d67ae9e42f2","slug":"results-malnutrition-act-cleaned-up","title":"Results Malnutrition Act Cleaned Up","owner":{"id":"83499e9a58cbfdf8","login":"marjolein","name":"Marjolein Oostrom"}},"stars":0,"starred":false,"owner":{"type":"team","id":"83499e9a58cbfdf8","login":"marjolein","name":"Marjolein Oostrom","avatar_url":"https://avatars-next.observableusercontent.com/5d583caf7e4d9b8af7462b6fadfb421624df1b34eaff2afa0d301bfc15578c89","tier":"starter_2024"},"version":2341,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"c98da6526bcb1390","login":"marjolein","name":"Marjolein Oostrom","avatar_url":"https://avatars-next.observableusercontent.com/5d583caf7e4d9b8af7462b6fadfb421624df1b34eaff2afa0d301bfc15578c89","tier":null}],"members":[{"type":"individual","id":"c98da6526bcb1390","login":"marjolein","name":"Marjolein Oostrom","avatar_url":"https://avatars-next.observableusercontent.com/5d583caf7e4d9b8af7462b6fadfb421624df1b34eaff2afa0d301bfc15578c89","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"title":"Results Malnutrition Act Cleaned Up","cells":[{"id":1783,"value":"md`# Results Malnutrition Act Cleaned Up`","pinned":true,"hidden":false,"mode":"ojs"},{"id":1911,"value":"duns = Boolean(1) ","pinned":true,"hidden":false,"mode":"ojs"},{"id":418,"value":"viewof dd3 = Inputs.text({label: \"Enter your tax bill: \", placeholder: \"Enter a tax bill\", value: 10000})","pinned":true,"hidden":false,"mode":"ojs"},{"id":1426,"value":"chart = circle_chart(packWithMapChildren, false, \"International development and humanitarian assistance\")\n// chart = circle_chart(packWithMapChildren, false, '37799178')","pinned":true,"hidden":false,"mode":"ojs"},{"id":1424,"value":"packWithMapChildren = d3.pack()\n .size([width - 2, height - 2])\n .padding(2)\n (d3.hierarchy(d3.rollup(all_data, D => d3.sum(D, d => d[\"value\"]) , ...[d => d.budget_function, d=> d.budget_subfunction, d => d.federal_account_name]))\n // (d3.hierarchy(d3.rollup(combined_award_info, D => d3.sum(D, d => d[\"value\"]) , ...[d => d.Org_type, \n // d =>d.recipient_duns]))\n .sum(([, value]) => value)\n .sort((a, b) => b.value - a.value))\n\n","pinned":true,"hidden":false,"mode":"ojs"},{"id":2101,"value":"d3.rollup(all_data, D => d3.sum(D, d => d[\"value\"]) , ...[d =>d.budget_function, d =>d.budget_subfunction])","pinned":true,"hidden":false,"mode":"ojs"},{"id":2078,"value":"chart_hum = circle_chart(packWithMapChildren_hum, true, 'Agency for International Development')","pinned":true,"hidden":false,"mode":"ojs"},{"id":2072,"value":"\npackWithMapChildren_hum = d3.pack()\n .size([width - 2, height - 2])\n .padding(2)\n (d3.hierarchy(d3.rollup(awards_budget, D => d3.sum(D, d => d[\"value\"]) , ...[d =>d.awarding_agency_name, d =>d.federal_account_name, d=> d.recipient_name]))\n // (d3.hierarchy(d3.rollup(combined_award_info, D => d3.sum(D, d => d[\"value\"]) , ...[d => d.Org_type, \n // d =>d.recipient_duns]))\n .sum(([, value]) => value)\n .sort((a, b) => b.value - a.value))","pinned":true,"hidden":false,"mode":"ojs"},{"id":2184,"value":" (d3.hierarchy(d3.rollup(awards_budget, D => d3.sum(D, d => d[\"value\"]) , ...[d => d.budget_function, d =>d.agency_name, d =>d.federal_account_name]))\n // (d3.hierarchy(d3.rollup(combined_award_info, D => d3.sum(D, d => d[\"value\"]) , ...[d => d.Org_type, \n // d =>d.recipient_duns]))\n .sum(([, value]) => value)\n .sort((a, b) => b.value - a.value)) ","pinned":true,"hidden":false,"mode":"ojs"},{"id":2029,"value":"chart2 = circle_chart(packWithMapChildren2, true, '37799178')","pinned":true,"hidden":false,"mode":"ojs"},{"id":2025,"value":"packWithMapChildren2 = d3.pack()\n .size([width - 2, height - 2])\n .padding(10)\n // (d3.hierarchy(d3.rollup(all_data, D => d3.sum(D, d => d[\"value\"]) , ...[d =>d.agency_name, d =>d.federal_account_name]))\n (d3.hierarchy(d3.rollup(combined_award_info, D => d3.sum(D, d => d[\"value\"]) , ...[d => d.Org_type, \n d =>d.recipient_duns, d=> d.agency_name]))\n .sum(([, value]) => value)\n .sort((a, b) => b.value - a.value))\n\n","pinned":true,"hidden":false,"mode":"ojs"},{"id":2104,"value":" (d3.hierarchy(d3.rollup(combined_award_info, D => d3.sum(D, d => d[\"value\"]) , ...[d => d.Org_type, \n d =>d.recipient_duns, d=> d.agency_name]))\n .sum(([, value]) => value)\n .sort((a, b) => b.value - a.value))\n","pinned":true,"hidden":false,"mode":"ojs"},{"id":1788,"value":"extra_info = FileAttachment(\"extra_info_recipient@4.csv\").csv()","pinned":true,"hidden":false,"mode":"ojs"},{"id":9,"value":"$2f","pinned":true,"hidden":false,"mode":"ojs"},{"id":0,"value":"md`# Zoomable Circle Packing\n\nClick to zoom in or out.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1521,"value":"$30","pinned":false,"hidden":false,"mode":"ojs"},{"id":1714,"value":" recip_list = [\"735229481\", \"37799178\", \"79623820\", \"482156317\", \"613831580\", \"963636951\", \"226500650\", \"40054827\"].concat(recipient_list)","pinned":true,"hidden":false,"mode":"ojs"},{"id":1691,"value":"// get top 20 recipient names by transaction obligated amount\nfunction get_recip_list( csv_breakdown_awards){\n\n const data = d3.rollup(\n csv_breakdown_awards,\n D => d3.sum(D, d => d[\"transaction_obligated_amount\"]), \n d => d.recipient_duns,\n)\n var recipient_account_sum = Array.from(data).flatMap(([key, lvalues]) => {\n return {recipient_name: key, value: lvalues};\n })\n var recip_list_initial = recipient_account_sum.sort(function(a, b){return a.value - b.value}).reverse().slice(0, 20).map(d=>d.recipient_name)\n\n var recip_list = recip_list_initial.filter(function(item) {\nreturn ![\"123456787\", \"\", \"790238638\", \"626534515\"].includes(item)\n})\n\n\n return recip_list\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2069,"value":"awards_budget = all_data.filter(d => d.awarding_agency_name !='No awards given')","pinned":true,"hidden":false,"mode":"ojs"},{"id":1534,"value":"// all_data = combine_agencies_recipients(csv_breakdown_awards, csv_account_balances) ","pinned":true,"hidden":false,"mode":"ojs"},{"id":1575,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":80,"value":"width = 932","pinned":true,"hidden":false,"mode":"ojs"},{"id":1252,"value":"height = width ","pinned":true,"hidden":false,"mode":"ojs"},{"id":198,"value":"import {select} from \"@jashkenas/inputs\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":41,"value":"format = d3.format(\",d\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":980,"value":" function onlyUnique(value, index, self) {\n return self.indexOf(value) === index;\n }","pinned":true,"hidden":false,"mode":"ojs"},{"id":1032,"value":" \n function toTitleCase(str) {\n\n return str.replace(\n /\\w\\S*/g,\n function(txt) {\n return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();\n }\n ).replace(/[.,\\/#!$%\\^&\\*;:{}=\\-_`~()]/g,\"\");\n }","pinned":false,"hidden":false,"mode":"ojs"},{"id":2222,"value":" function toshorten(str) {\n\n return str.substring(0,24) + \"...\"\n }","pinned":true,"hidden":false,"mode":"ojs"},{"id":45,"value":"color = d3.scaleLinear()\n .domain([0, 5])\n .range([\"hsl(152,80%,80%)\", \"hsl(228,30%,40%)\"])\n .interpolate(d3.interpolateHcl)","pinned":true,"hidden":false,"mode":"ojs"},{"id":2127,"value":"","pinned":true,"hidden":false,"mode":"ojs"},{"id":548,"value":"viewof recipientdrop = select({\n title: \"Recipients\",\n description: \"Select which which recipient to highlight (most funds recieved first)\",\n\n options:[first_item].concat(recip_list).map((d) => ({value: d, label:d.substring(0,30)})) ,\n value: \"repo\"\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":1105,"value":"first_item = \"Select One\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":1329,"value":"import {Treemap} from \"@d3/treemap\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":2110,"value":"import {DuckDBClient} from '@cmudig/duckdb'","pinned":true,"hidden":false,"mode":"ojs"},{"id":2113,"value":"client2 = DuckDBClient.of([\n await FileAttachment(\"fy2021_award.parquet\"),\n await FileAttachment(\"fy2021_pa.parquet\"),\n await FileAttachment(\"extra_info_recipient.parquet\")\n \n])","pinned":true,"hidden":false,"mode":"ojs"},{"id":2327,"value":"\nrecipient_list = recipient_table.map(function (el) { return el.recipient_duns.toString() })","pinned":true,"hidden":false,"mode":"ojs"},{"id":2142,"value":"SELECT COALESCE(SUM(transaction_obligated_amount), 0) as value, COALESCE(recipient_duns, 0) as recipient_duns\nFROM fy2021_award\nGROUP BY recipient_duns\nORDER BY COALESCE(SUM(transaction_obligated_amount), 0) DESC\nlimit 21\n\n\n\n\n","pinned":true,"hidden":false,"mode":"sql","output":"recipient_table","database":"var:client2"},{"id":2118,"value":"$32","pinned":false,"hidden":false,"mode":"sql","output":"all_data","database":"var:client2"},{"id":2115,"value":"SELECT *\n FROM (\nSELECT \"federal_account_name\", \"federal_account_symbol\", 0 as total_account, 0 as total_award, \n recipient_name, recipient_duns,\n \"awarding_agency_name\" as \"agency_name\", \"budget_function\", \"budget_subfunction\", \"transaction_obligated_amount\" as value\nFROM \"fy2021_award\") as t1\n right JOIN\n \n(SELECT *\nFROM extra_info_recipient\n limit 20)\nAS t2\nON t1.recipient_duns = t2.recipient_duns\n","pinned":false,"hidden":false,"mode":"sql","output":"combined_award_info","database":"var:client2"},{"id":6,"value":"d3 = require(\"d3@6\")","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W33","stdlib":"1","titling":"auto"}} 2d:"$2c:body"