31:[{"id":"3fb065369498e64f7ec5edb58c649d34bee8422fe69e189a218a6ff9d3616a50e8fb018f458c884723ebcede31969f4e73b841f7ebf3ca409901997b0f7263f7","href":"https://static.observableusercontent.com/files/3fb065369498e64f7ec5edb58c649d34bee8422fe69e189a218a6ff9d3616a50e8fb018f458c884723ebcede31969f4e73b841f7ebf3ca409901997b0f7263f7","name":"openphil.xls","type":"application/octet-stream","lastModified":1572462442003.918,"size":226538},{"id":"3fb065369498e64f7ec5edb58c649d34bee8422fe69e189a218a6ff9d3616a50e8fb018f458c884723ebcede31969f4e73b841f7ebf3ca409901997b0f7263f7","href":"https://static.observableusercontent.com/files/3fb065369498e64f7ec5edb58c649d34bee8422fe69e189a218a6ff9d3616a50e8fb018f458c884723ebcede31969f4e73b841f7ebf3ca409901997b0f7263f7","name":"openphil@1.xls","type":"application/octet-stream","lastModified":1572462459321.297,"size":226538}] 2f:{"title":"Analyzing OpenPhilanthropy Grants Database","cells":[{"id":0,"value":"md`# Analyzing OpenPhilanthropy Grants Database\n\nThis will be an example of one path through basic data analysis. I'm checking out the [OpenPhilanthropy Grants Database](https://www.openphilanthropy.org/giving/grants) and want to keep this as simple and straightfoward as possible. So, let's try using raw data. I'd love to directly use fetch() to grab the data from openphilanthropy.org, but they don't support CORS, so I'll leave that for another day. So, I've prepped by\n\n- Downloading \\`databasexls.xls\\` from [OpenPhilanthropy](https://www.openphilanthropy.org/giving/grants)\n- Dragging & dropping it onto [Zeit Now](https://zeit.co/now)`","pinned":false,"hidden":false,"mode":"ojs"},{"id":129,"value":"html``","pinned":false,"hidden":false,"mode":"ojs"},{"id":100,"value":"md`### Including xlsx\n\n[xlsx](https://github.com/SheetJS/js-xlsx) is a JavaScript library that has tackled the very challenging task of parsing XLSX (Excel) files. I include it here, and notice the workaround: the library doesn't support [AMD](https://en.wikipedia.org/wiki/Asynchronous_module_definition) module loading, and instead it sets a XLSX global variable. So I swoop in, attach to the Promise returned by require(), and expose that value as the module.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2,"value":"xlsx = require('xlsx/dist/xlsx.core.min.js').then(() => window.XLSX)","pinned":true,"hidden":false,"mode":"ojs"},{"id":115,"value":"md`Let's get a HTML view of this sheet to confirm its contents:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":117,"value":"html`

${xlsx.utils.sheet_to_html(\n xlsx.read(await FileAttachment(\"openphil.xls\").text(), { type: 'string' })\n .Sheets.grants_db\n)}

`","pinned":true,"hidden":false,"mode":"ojs"},{"id":102,"value":"md`From here on, it's not _too_ complicated, but a little verbose: I'm checking out the available sheets in this Excel file, then parsing the file and using the sheet_to_json method to read the sheet as a nice array of objects, instead of the A1, B1, A2, B2 kind of cell names that are common in Excel.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":240,"value":"[0, 0].splice(1, 0, 2)","pinned":false,"hidden":false,"mode":"ojs"},{"id":66,"value":"sheet = xlsx.utils\n .sheet_to_json(\n xlsx.read(await FileAttachment(\"openphil@1.xls\").text(), { type: 'string' })\n .Sheets.grants_db\n )\n .map(r => {\n // reformat grant amounts as numbers, not as dollar amounts with dollar signs and commas\n r['Grant Amount'] = parseInt(r['Grant Amount'].replace(/[^\\d]/g, ''), 10);\n // reformat dates as JavaScript date objects. This data only includes the month and year,\n // so set all donations to the first of the month.\n r['Date'] = new Date(r['Date'].replace('/', '/1/'));\n return r;\n })","pinned":true,"hidden":false,"mode":"ojs"},{"id":220,"value":"md`### Analyzing the data\n\nFirst, let's check out some of the data. I'm using [Vega Lite](https://vega.github.io/vega-lite/) to explore this data.\n\nBinned, we see that there are lots of grants between 0 and $5M, and only one grant at $25M: to [GiveWell](https://www.givewell.org/).`","pinned":false,"hidden":false,"mode":"ojs"},{"id":177,"value":"vega({\n width: width,\n height: 300,\n autosize: { type: 'fit', contains: 'padding' },\n \"$schema\": \"https://vega.github.io/schema/vega-lite/v2.json\",\n \"data\": { values: sheet },\n \"mark\": \"bar\",\n \"encoding\": {\n \"color\": {\n \"value\": \"#798234\"\n },\n \"x\": {\n \"bin\": true,\n \"field\": \"Grant Amount\",\n \"type\": \"quantitative\"\n },\n \"y\": {\n \"aggregate\": \"count\",\n \"type\": \"quantitative\"\n }\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":227,"value":"md`## Grants over time\n\nOpenPhilanthropy's first grant was in 2012: let's check out giving per year. I used the [Mean Precipation](https://vega.github.io/vega-lite/examples/layer_precipitation_mean.html) example to figure out the time-based scales.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":229,"value":"vega({\n width: width,\n height: 300,\n autosize: { type: 'fit', contains: 'padding' },\n \"$schema\": \"https://vega.github.io/schema/vega-lite/v2.json\",\n \"data\": { values: sheet },\n \"mark\": \"bar\",\n \"encoding\": {\n \"color\": {\n \"value\": \"#d46780\"\n },\n \"x\": {\n \"timeUnit\": 'yearmonth',\n \"field\": \"Date\",\n \"type\": \"ordinal\"\n },\n \"y\": {\n \"aggregate\": \"sum\",\n field: 'Grant Amount',\n \"type\": \"quantitative\"\n }\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":175,"value":"vega = require('@observablehq/vega-lite')","pinned":false,"hidden":false,"mode":"ojs"},{"id":270,"value":"md`### Keep reading\n\nThis notebook uses Vega Lite for a bunch of stuff. Check out my [Vega Lite Protips](https://beta.observablehq.com/@tmcw/vega-lite-protips) for some notes on how you can use Vega too.\n`","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W31","stdlib":"1","titling":"auto"}