32:T435,vega({ $schema: "https://vega.github.io/schema/vega-lite/v4.json", title: { text: ["Total Grant Value for the Top 20 Canadian Federal Recipients (2004 - 2020)"], fontSize: 24, subtitle: "Subnational Governments Removed" }, width: 300, data: { values: recipients.sort((a, b) => b.total - a.total).slice(0, 20) }, mark: { type: "bar", tooltip: true }, encoding: { y: { field: "recipient", type: "nominal", title: "Recipient", sort: { order: "descending" }, axis: { labelFontSize: 12, titleFontSize: 16, labelLimit: 300, minExtent: 320 } }, x: { field: "total", type: "quantitative", title: "Total Grant Value", axis: { formatType: "money", labelFontSize: 12, titleFontSize: 16 } }, color: { field: "cnt", type: "quantitative", title: "Number of Grants", legend: { formatType: "money", labelFontSize: 12, titleFontSize: 16 } } }, config: { customFormatTypes: true } })33:T44e,vega({ $schema: "https://vega.github.io/schema/vega-lite/v4.json", title: { text: [ "Total Number of Grants the Top 20 Canadian Federal Recipients (2004 - 2020)" ], fontSize: 24, subtitle: "Subnational Governments Removed" }, data: { values: recipients .filter(d => d.recipient !== "TSUUTINA NATION") .sort((a, b) => b.cnt - a.cnt) .slice(0, 20) }, width: 300, mark: { type: "bar", tooltip: true }, encoding: { y: { field: "recipient", type: "nominal", title: "Recipient", sort: { order: "descending" }, axis: { labelFontSize: 12, titleFontSize: 16, labelLimit: 300, minExtent: 320 } }, x: { field: "cnt", type: "quantitative", title: "Number of Grants", axis: { labelFontSize: 12, titleFontSize: 16 } }, color: { field: "total", type: "quantitative", title: "Value of Grants", legend: { formatType: "money", labelFontSize: 12, titleFontSize: 16 } } }, config: { customFormatTypes: true } })34:T422,vega({ $schema: "https://vega.github.io/schema/vega-lite/v4.json", title: { text: [ "Top 20 Recipients by Average Grant Value - Canadian Federal Grants (2004 - 2020)" ], subtitle: "Subnational Governments Removed", fontSize: 24 }, width: 300, data: { values: ammendData.sort((a, b) => b.avevalue - a.avevalue).slice(0, 20) }, mark: { type: "bar", tooltip: true }, encoding: { y: { field: "recipient", type: "nominal", title: "Recipient", sort: { order: "descending" }, axis: { labelFontSize: 12, titleFontSize: 16, labelLimit: 300, minExtent: 320 } }, x: { field: "avevalue", type: "quantitative", title: "Average Value of Grant", axis: { formatType: "money", labelFontSize: 12, titleFontSize: 16 } }, color: { field: "cnt", type: "quantitative", title: "Number of Grants", legend: { labelFontSize: 12, titleFontSize: 16 } } }, config: { customFormatTypes: true } })35:T478,vega({ $schema: "https://vega.github.io/schema/vega-lite/v4.json", title: { text: [ "Top 20 Recipients by Average Value per Year - Canadian Federal Grants (2004 - 2020)" ], subtitle: "Subnational Governments Removed", fontSize: 24 }, width: 300, data: { values: ammendData.sort((a, b) => b.aveperyear - a.aveperyear).slice(0, 20) }, mark: { type: "bar", tooltip: true }, encoding: { y: { field: "recipient", type: "nominal", title: "Recipient", sort: { order: "descending" }, axis: { labelFontSize: 12, titleFontSize: 16, labelLimit: 300, minExtent: 320 } }, x: { field: "aveperyear", type: "quantitative", title: "Average Value of Grant", axis: { formatType: "money" }, axis: { labelFontSize: 12, titleFontSize: 16, formatType: "money" } }, color: { field: "total", type: "quantitative", title: "Total Value of Grants - All Years", legend: { formatType: "money", labelFontSize: 12, titleFontSize: 16 } } }, config: { customFormatTypes: true } })36:T10b7,md` \`\`\`sql .mode csv .import "grants.csv" grants .schema grants create table recipients as select TRIM( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( UPPER( SUBSTR(recipient_legal_name, 1, CASE INSTR(recipient_legal_name, '|') WHEN 0 THEN LENGTH(recipient_legal_name) ELSE INSTR(recipient_legal_name, '|') - 1 END )), '.', ''), '"', ''), ' INC', ''), '#', ''), '''', ''), CHAR(13), ''), CHAR(10), '') ) as recipient, sum(agreement_value) as total, count(*) as cnt, min(agreement_start_date) min_date, max(agreement_start_date) max_date from grants WHERE recipient NOT LIKE '%GO%VERN%MENT%' AND recipient NOT LIKE '%MINISTRE%' AND recipient NOT LIKE '%PROVINCE OF%' AND recipient NOT LIKE '%BATCH REPORT%' AND recipient NOT LIKE '%PROVINCE OF%' AND recipient NOT LIKE '%MOHAWK COUNCIL%' AND recipient NOT LIKE '%CREE NATION%' AND recipient NOT LIKE '% BAND' AND recipient NOT LIKE '%CITY OF%' AND recipient NOT LIKE '%MINISTRY%' AND recipient NOT LIKE '%SCHOOL BOARD%' AND recipient NOT LIKE '%FIRST NATION%' AND recipient NOT LIKE '%BAND COUNCIL%' AND recipient NOT LIKE '%TRIBAL COUNCIL%' AND recipient NOT LIKE '%NATION' group by recipient; create table orgs as select owner_org_title, sum(agreement_value) as total, count(*) as cnt, strftime('%Y', agreement_start_date) as grant_date from grants WHERE UPPER(recipient_legal_name) NOT LIKE '%GO%VERN%MENT%' AND UPPER(recipient_legal_name) NOT LIKE '%MINISTRE%' AND UPPER(recipient_legal_name) NOT LIKE '%PROVINCE OF%' AND UPPER(recipient_legal_name) NOT LIKE '%BATCH REPORT%' AND UPPER(recipient_legal_name) NOT LIKE '%PROVINCE OF%' AND UPPER(recipient_legal_name) NOT LIKE '%MOHAWK COUNCIL%' AND UPPER(recipient_legal_name) NOT LIKE '%CREE NATION%' AND UPPER(recipient_legal_name) NOT LIKE '% BAND' AND UPPER(recipient_legal_name) NOT LIKE '%CITY OF%' AND UPPER(recipient_legal_name) NOT LIKE '%MINISTRY%' AND UPPER(recipient_legal_name) NOT LIKE '%SCHOOL BOARD%' AND UPPER(recipient_legal_name) NOT LIKE '%FIRST NATION%' AND UPPER(recipient_legal_name) NOT LIKE '%BAND COUNCIL%' AND UPPER(recipient_legal_name) NOT LIKE '%TRIBAL COUNCIL%' AND UPPER(recipient_legal_name) NOT LIKE '%NATION' group by owner_org_title, grant_date; create table distributions as select case when cast(agreement_value as int) < 1000 THEN '< 1000' when cast(agreement_value as int) < 10000 THEN '1000 - 9999' when cast(agreement_value as int) < 100000 THEN '10000 - 99999' when cast(agreement_value as int) < 1000000 THEN '100000 - 999999' when cast(agreement_value as int) < 10000000 THEN '1000000 - 9999999' when cast(agreement_value as int) < 100000000 THEN '10000000 - 99999999' else '>= 1000000000' end as category, owner_org_title, strftime('%Y', agreement_start_date) as grant_date, sum(cast(agreement_value as int)) as total_value, count(*) as cnt from grants WHERE UPPER(recipient_legal_name) NOT LIKE '%GO%VERN%MENT%' AND UPPER(recipient_legal_name) NOT LIKE '%MINISTRE%' AND UPPER(recipient_legal_name) NOT LIKE '%PROVINCE OF%' AND UPPER(recipient_legal_name) NOT LIKE '%BATCH REPORT%' AND UPPER(recipient_legal_name) NOT LIKE '%PROVINCE OF%' AND UPPER(recipient_legal_name) NOT LIKE '%MOHAWK COUNCIL%' AND UPPER(recipient_legal_name) NOT LIKE '%CREE NATION%' AND UPPER(recipient_legal_name) NOT LIKE '% BAND' AND UPPER(recipient_legal_name) NOT LIKE '%CITY OF%' AND UPPER(recipient_legal_name) NOT LIKE '%MINISTRY%' AND UPPER(recipient_legal_name) NOT LIKE '%SCHOOL BOARD%' AND UPPER(recipient_legal_name) NOT LIKE '%FIRST NATION%' AND UPPER(recipient_legal_name) NOT LIKE '%BAND COUNCIL%' AND UPPER(recipient_legal_name) NOT LIKE '%TRIBAL COUNCIL%' AND UPPER(recipient_legal_name) NOT LIKE '%NATION' AND AGREEMENT_VALUE > 0 group by category, owner_org_title, grant_date; .headers on .mode csv .output recipients.csv select recipient, total, cnt, strftime('%Y', min_date) as min_year, strftime('%Y', max_date) as max_year from recipients; .output stdout .headers on .mode csv .output orgs.csv select owner_org_title, total, cnt, grant_date from orgs; .output stdout .headers on .mode csv .output distributions.csv select category, owner_org_title, grant_date, total_value, cnt from distributions; .output stdout \`\`\` `37:[{"id":"73e5b66ce74dfde0ca1a0c6154330e50c595b00a1a6f35952df7e7742003ac3eea5cef955b62bef89cce2d885818d75651b9a72706066ccb731f53b6e68ab8b1","href":"https://static.observableusercontent.com/files/73e5b66ce74dfde0ca1a0c6154330e50c595b00a1a6f35952df7e7742003ac3eea5cef955b62bef89cce2d885818d75651b9a72706066ccb731f53b6e68ab8b1","name":"recipients@2.csv","type":"text/csv","lastModified":1609692124749.62,"size":9390720},{"id":"f2b380b0133db864f1abc9fc9f3f7535e34cdf7300f5b90912c25ffec6fc4cce89b29e1c0b4143eaed4d8016fc8f5c1dd8a93ca2cf1fc02cfe8e2831d9406114","href":"https://static.observableusercontent.com/files/f2b380b0133db864f1abc9fc9f3f7535e34cdf7300f5b90912c25ffec6fc4cce89b29e1c0b4143eaed4d8016fc8f5c1dd8a93ca2cf1fc02cfe8e2831d9406114","name":"orgs.csv","type":"text/csv","lastModified":1609716410298.291,"size":37389},{"id":"9e064129c98eb2de8df8ac0b85ebea50b4ab9fc2beca4fb823aeab18c6cff3a83a506eea98fbc3abede2f7870d9fbb8c3df53463c908492803821f89c84eb6cf","href":"https://static.observableusercontent.com/files/9e064129c98eb2de8df8ac0b85ebea50b4ab9fc2beca4fb823aeab18c6cff3a83a506eea98fbc3abede2f7870d9fbb8c3df53463c908492803821f89c84eb6cf","name":"distributions.csv","type":"text/csv","lastModified":1610242328517.028,"size":159393}] 30:{"title":"Canada - Federal Grants to NGOs and Other Private Organizations","cells":[{"id":0,"value":"md`# Canada - Federal Grants to NGOs and Other Private Organizations`","pinned":false,"hidden":false,"mode":"ojs"},{"id":16,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":89,"value":"$33","pinned":false,"hidden":false,"mode":"ojs"},{"id":100,"value":"$34","pinned":false,"hidden":false,"mode":"ojs"},{"id":111,"value":"$35","pinned":false,"hidden":false,"mode":"ojs"},{"id":127,"value":"vega({\n $schema: \"https://vega.github.io/schema/vega-lite/v4.json\",\n background: \"white\",\n title: {\n text: [\"Canadian Federal Grants By Department and Year\"],\n subtitle: \"Subnational Governments Removed\",\n fontSize: 24\n },\n data: { values: orgs },\n mark: {\n type: \"rect\",\n tooltip: true\n },\n encoding: {\n x: {\n field: \"grant_date\",\n type: \"ordinal\",\n axis: {\n title: \"Year\",\n labelFontSize: 14,\n titleFontSize: 16\n }\n },\n y: {\n field: \"owner_org_title\",\n type: \"ordinal\",\n axis: {\n title: \"Organization\",\n labelFontSize: 14,\n titleFontSize: 16,\n labelLimit: 300,\n minExtent: 320\n }\n },\n color: {\n field: \"total\",\n type: \"quantitative\",\n scale: { scheme: \"viridis\" },\n legend: {\n labelFontSize: 12,\n titleFontSize: 16,\n title: \"Grant Total Value\",\n format: \"$,.0f\"\n }\n }\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":140,"value":"vega({\n $schema: \"https://vega.github.io/schema/vega-lite/v4.json\",\n background: \"white\",\n title: {\n text: [\"Canadian Federal Grants By Department and Year\"],\n subtitle: \"Subnational Governments Removed\",\n fontSize: 24\n },\n data: { values: orgs },\n mark: {\n type: \"rect\",\n tooltip: true\n },\n encoding: {\n x: {\n field: \"grant_date\",\n type: \"ordinal\",\n axis: { title: \"Year\", labelFontSize: 14, titleFontSize: 16 }\n },\n y: {\n field: \"owner_org_title\",\n type: \"ordinal\",\n axis: {\n title: \"Organization\",\n labelFontSize: 14,\n titleFontSize: 16,\n labelLimit: 300,\n minExtent: 320\n }\n },\n color: {\n field: \"cnt\",\n type: \"quantitative\",\n scale: { scheme: \"viridis\" },\n legend: {\n labelFontSize: 12,\n title: \"Number of Grants\",\n titleFontSize: 16,\n format: \",.0f\"\n }\n }\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":149,"value":"vega({\n $schema: \"https://vega.github.io/schema/vega-lite/v4.json\",\n background: \"white\",\n title: {\n text: [\"Canadian Federal Grants By Department and Year\"],\n subtitle: \"Subnational Governments Removed\",\n fontSize: 24\n },\n data: { values: orgAmmend },\n mark: {\n type: \"rect\",\n tooltip: true\n },\n encoding: {\n x: {\n field: \"grant_date\",\n type: \"ordinal\",\n axis: { title: \"Year\", labelFontSize: 14, titleFontSize: 16 }\n },\n y: {\n field: \"owner_org_title\",\n type: \"ordinal\",\n axis: {\n title: \"Organization\",\n labelFontSize: 14,\n titleFontSize: 16,\n labelLimit: 300,\n minExtent: 320\n }\n },\n color: {\n field: \"avevalue\",\n type: \"quantitative\",\n scale: { scheme: \"viridis\" },\n legend: {\n labelFontSize: 12,\n title: \"Average Value per Grant\",\n titleFontSize: 16,\n format: \"$,.0f\"\n }\n }\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":279,"value":"viewof currentOrg = html``","pinned":false,"hidden":false,"mode":"ojs"},{"id":295,"value":"vega({\n $schema: \"https://vega.github.io/schema/vega-lite/v4.json\",\n data: {\n values: distributions.filter(d => d.owner_org_title === currentOrg)\n },\n mark: { type: \"line\", tooltip: true },\n title: [\n `Total Value of Grants for ${currentOrg}`,\n 'Subnational Governments Removed'\n ],\n encoding: {\n x: {\n field: \"grant_date\",\n type: \"nominal\",\n title: \"Year\",\n scale: {\n domain: [\n \"2004\",\n \"2005\",\n \"2006\",\n \"2007\",\n \"2008\",\n \"2009\",\n \"2010\",\n \"2011\",\n \"2012\",\n \"2013\",\n \"2014\",\n \"2015\",\n \"2016\",\n \"2017\",\n \"2018\",\n \"2019\",\n \"2020\",\n \"2021\"\n ]\n }\n },\n y: {\n field: \"total_value\",\n type: \"quantitative\",\n aggregate: \"sum\",\n title: \"Total Value of All Grants\"\n }\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":253,"value":"vega({\n $schema: \"https://vega.github.io/schema/vega-lite/v4.json\",\n data: { values: distributions.filter(d => d.owner_org_title === currentOrg) },\n mark: { type: \"bar\", tooltip: true },\n title: [\n `Distribution of the Number of Grants by Grant Value for ${currentOrg}`,\n \"Subnational Governments Removed\",\n \"x-axis logarithmic\"\n ],\n encoding: {\n x: {\n field: \"category\",\n type: \"nominal\",\n title: \"Individual Grant Value ($)\",\n scale: {\n domain: [\n '< 1000',\n '1000 - 9999',\n '10000 - 99999',\n '100000 - 999999',\n '1000000 - 9999999',\n '10000000 - 99999999'\n ]\n }\n },\n y: {\n field: \"total_value\",\n type: \"quantitative\",\n title: \"Total Value of All Grants\"\n },\n facet: {\n field: \"grant_date\",\n title: \"year\",\n type: \"ordinal\",\n columns: 4\n },\n color: {\n field: \"cnt\",\n type: \"quantitative\",\n title: \"Number of Grants\"\n }\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":370,"value":"md``","pinned":false,"hidden":false,"mode":"ojs"},{"id":373,"value":"md``","pinned":false,"hidden":false,"mode":"ojs"},{"id":342,"value":"md`## Appendix - Data Source and How Subsets were Derived`","pinned":false,"hidden":false,"mode":"ojs"},{"id":347,"value":"md`The data in this notebook was retrieved from the Open Data Canada portal at the following URL:\n\nhttps://open.canada.ca/data/en/dataset/432527ab-7aac-45b5-81d6-7597107a7013\n\nSince the data is too big it fit into a spreadsheet, the following sqlite script was used to process the data and create teh data subsets used in this notebook.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":359,"value":"$36","pinned":false,"hidden":false,"mode":"ojs"},{"id":379,"value":"md``","pinned":false,"hidden":false,"mode":"ojs"},{"id":382,"value":"md``","pinned":false,"hidden":false,"mode":"ojs"},{"id":385,"value":"md``","pinned":false,"hidden":false,"mode":"ojs"},{"id":95,"value":"ammendData = recipients.map(d => {\n d.avevalue = d.total / d.cnt;\n d.aveperyear = d.total / (d.max_year - d.min_year + 1);\n return d;\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":11,"value":"recipients = FileAttachment(\"recipients@2.csv\").csv({ typed: true })","pinned":false,"hidden":false,"mode":"ojs"},{"id":151,"value":"orgAmmend = orgs.map(d => {\n d.avevalue = d.total / d.cnt;\n return d;\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":129,"value":"orgs = FileAttachment(\"orgs.csv\").csv({ typed: true })","pinned":false,"hidden":false,"mode":"ojs"},{"id":280,"value":"orgnames = Object.keys(\n distributions.reduce((accum, d) => {\n accum[d.owner_org_title] = 1;\n return accum;\n }, {})\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":249,"value":"distributions = (await FileAttachment(\"distributions.csv\").csv({\n typed: true\n})).filter(d => d.grant_date > 2004 && d.grant_date < 2021)","pinned":false,"hidden":false,"mode":"ojs"},{"id":17,"value":"vega = {\n let vegabase = await require(\"vega@5\");\n vegabase.expressionFunction('money', function(datum, params) {\n if (datum >= 1000000000) {\n return \"$\" + datum / 1000000000 + \"B\";\n } else if (datum > 1000000) {\n return \"$\" + datum / 1000000 + \"M\";\n } else if (datum > 1000) {\n return \"$\" + datum / 1000 + \"k\";\n } else {\n return datum;\n }\n });\n return require(\"vega-embed@6\");\n}","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W37","stdlib":"1","titling":"auto"}