31:T555,md`Let’s break this down a bit. The format=json tells Wikipedia that we want the response [data format](https://www.mediawiki.org/wiki/API:Data_formats) to be JSON. JSON is the natural choice for working with data in the browser: we can parse the result using [*response*.json](https://developer.mozilla.org/en-US/docs/Web/API/Body/json). The browser’s [same-origin policy](https://developer.mozilla.org/en-US/docs/Web/Security/Same-origin_policy) means that, by default, only Wikipedia pages are allowed to talk to the Wikipedia API. Fortunately for us, Wikipedia also supports [cross-origin resource sharing (CORS)](https://developer.mozilla.org/en-US/docs/Web/HTTP/CORS): since our query includes origin=*, Wikipedia sets the Access-Control-Allow-Origin header so we can read the response. The action=parse query parameter tells Wikipedia that we want to parse the content of the specified page (page=List_of_highest-grossing_media_franchises) and section (section=1), and return the parsed wikitext (prop=text). Sections are numbered, starting with zero; if you forget the section parameter, you’ll get the entire page, which might be slow. You can click to inspect the object above, but here’s the part we care about: the text.`32:[{"id":"50c6037cf21e726c2fd353c5822d40a09738d03ce5b13f53a6f16a71bf37a1b2d158b0d51a161dc8c0d6ddbf3be07a1034be12dc3e2d7fb904624534670f79df","href":"https://static.observableusercontent.com/files/50c6037cf21e726c2fd353c5822d40a09738d03ce5b13f53a6f16a71bf37a1b2d158b0d51a161dc8c0d6ddbf3be07a1034be12dc3e2d7fb904624534670f79df","name":"List_of_highest-grossing_media_franchises.json","type":"application/json","lastModified":1575311344278.703,"size":827972}] 2f:{"title":"Working with Wikipedia Data","cells":[{"id":0,"value":"md`# Working with Wikipedia Data\n\nWikipedia has a fantastic array of tables on various subjects. Even better, Wikipedia has a CORS-accessible API! In this notebook, I’ll show how to load data directly from Wikipedia into an Observable notebook. We’ll parse and tidy the data, and then visualize it with Vega-Lite.\n\nOur subject of interest will be the [highest-grossing media franchises](https://en.wikipedia.org/wiki/List_of_highest-grossing_media_franchises). (For a challenge, try forking this notebook and adapting it to a different table!) To start, let’s fetch the table using [the parse action](https://www.mediawiki.org/wiki/API:Parsing_wikitext).`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2,"value":"// fetch(`https://en.wikipedia.org/w/api.php?format=json&origin=*&action=parse&prop=text&page=List_of_highest-grossing_media_franchises§ion=1`).then(response => response.json())","pinned":true,"hidden":false,"mode":"ojs"},{"id":728,"value":"md`(So that this notebook continues to work even if the format of the Wikipedia page changes, I downloaded the result of the above cell after uncommenting it, and then attached it as a file in the cell below.)`","pinned":false,"hidden":false,"mode":"ojs"},{"id":723,"value":"text = FileAttachment(\"List_of_highest-grossing_media_franchises.json\").json()","pinned":true,"hidden":false,"mode":"ojs"},{"id":332,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":378,"value":"text.parse.text[\"*\"]","pinned":true,"hidden":false,"mode":"ojs"},{"id":382,"value":"md`As you can see, this text is actually HTML. This means that we can embed the HTML directly below to recreate the table as it appears on Wikipedia.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":21,"value":"table = {\n const parser = new DOMParser();\n const external = parser.parseFromString(text.parse.text[\"*\"], \"text/html\");\n const table = document.adoptNode(external.querySelector(\"table\"));\n table.style = `\n display: block;\n position: relative;\n max-width: none;\n max-height: 600px;\n overflow-y: auto;\n -webkit-overflow-scrolling: touch;\n `;\n return table;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":397,"value":"md`There are a variety of ways to convert a string into DOM, but here I’m using a [DOMParser](https://developer.mozilla.org/en-US/docs/Web/API/DOMParser) to construct a new document and then pulling out the first table element using [*document*.querySelector](https://developer.mozilla.org/en-US/docs/Web/API/Document/querySelector). To display the table here in the notebook, I use [*document*.adoptNode](https://developer.mozilla.org/en-US/docs/Web/API/Document/adoptNode) to transfer the table from the parsed document (*external*) to the local document (*document*). Since this table is quite large, I also set a few styles so that it scrolls.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":394,"value":"md`Now that we’ve converted our text into a functioning HTML table, we can use the [*element*.querySelectorAll](https://developer.mozilla.org/en-US/docs/Web/API/Element/querySelectorAll) and other DOM APIs to extract the data of interest.\n\nHTML tables typically have both a head and a body. The head contains the column headers (such as “Franchise”, “Year of inception”, *etc.*), while the body contains a row for each item (each media franchise). So, if we pull out the first row of the body, we should expect to see the top media franchise, Pokémon.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":421,"value":"table.querySelector(\"tbody tr\").cloneNode(true)","pinned":true,"hidden":false,"mode":"ojs"},{"id":431,"value":"md`Oops, we got the header row instead. Apparently this table doesn’t have a head, so the header row is the first row of the body. No big deal. We can just skip the first row instead.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":442,"value":"table.querySelector(\"tr:not(:first-child)\").cloneNode(true)","pinned":true,"hidden":false,"mode":"ojs"},{"id":589,"value":"md`Success!\n\nNow we can extract the franchise names from the first cell in each row. We’ll also [*string*.trim](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/Trim) to remove the following newline.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":440,"value":"rows = table.querySelectorAll(\"tr:not(:first-child)\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":40,"value":"names = Array.from(rows, r => r.querySelector(\"td:nth-child(2)\").textContent.trim())","pinned":true,"hidden":false,"mode":"ojs"},{"id":410,"value":"md`Similarly, to extract the third column to pull out the total revenue figures:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":413,"value":"Array.from(rows, r => r.querySelector(\"td:nth-child(4)\").textContent.trim())","pinned":true,"hidden":false,"mode":"ojs"},{"id":465,"value":"md`Of course these figures aren’t yet numbers—they’re strings. And to make matters worse, these aren’t strings that can be simply coerced to numbers—they’re human-readable estimates. The simple approaches won’t work.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":466,"value":"+\"est. $90 billion\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":473,"value":"md`Instead, we need to write a custom parser. That may sound hard, but it’s not that bad: our parser only needs to understand the hundred or so inputs in our dataset. As long as our inputs are reasonably well-formed, we can probably get by with a [regular expression](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Regular_Expressions).\n\nHere’s the regular expression I came up with. It expects the input to have the form “est. $*X* billion” where *X* is a number. The *X* is a *capture group* (with parentheses), so that we can pull it out after matching to convert to a number.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":480,"value":"totalRe = /^(?:est\\. )?\\$(\\d+(?:\\.\\d+)?) billion/","pinned":true,"hidden":false,"mode":"ojs"},{"id":477,"value":"totalRe.exec(\"est. $90 billion\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":222,"value":"function parseTotal(total) {\n const match = totalRe.exec(total);\n if (!match) throw new Error(\"unable to parse total: \" + total);\n return +`${match[1]}e9`;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":549,"value":"md`And here’s the result:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":219,"value":"totals = Array.from(rows, r => parseTotal(r.querySelector(\"td:nth-child(4)\").textContent.trim()))","pinned":true,"hidden":false,"mode":"ojs"},{"id":503,"value":"md`The observant reader may wonder why I’m not coercing the number directly and then multiplying by a billion. The answer is: because floating point. If you coerce the billions figure to a decimal number and then multiply, you can introduce floating point error because the decimal figure cannot be represented exactly in JavaScript’s binary floating point.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":507,"value":"8.36 * 1e9","pinned":true,"hidden":false,"mode":"ojs"},{"id":518,"value":"md`If, however, you use JavaScript’s exponential notation prior to coercion, you are guaranteed to get an exact integer (assuming that the estimated total revenue is less than [Number.MAX_SAFE_INTEGER](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Number/MAX_SAFE_INTEGER), but even the venerable Pokémon can’t have made more than nine quadrillion dollars).`","pinned":false,"hidden":false,"mode":"ojs"},{"id":512,"value":"+\"8.36e9\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":485,"value":"md`Note that our parser throws an error if our regular expression doesn’t match. This helps avoid unexpected values creeping in as part of our data cleaning.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":541,"value":"parseTotal(\"est. $100 million\") // Error!","pinned":true,"hidden":false,"mode":"ojs"},{"id":544,"value":"md`To further confirm that our cleaned data looks reasonable, let’s visualize it!`","pinned":false,"hidden":false,"mode":"ojs"},{"id":246,"value":"vl.markBar()\n .data({values: d3.zip(names, totals)})\n .encode(\n vl.y().fieldN(\"0\").sort(null).axis({title: null}),\n vl.x().fieldQ(\"1\").axis({orient: \"top\", format: \"s\", title: \"Total revenue (est.)\"})\n )\n .width(width)\n .autosize({type: \"fit-x\", contains: \"padding\"})\n .render()","pinned":true,"hidden":false,"mode":"ojs"},{"id":569,"value":"md`This uses the [Vega-Lite JavaScript API](https://github.com/vega/vega-lite-api/), which is a very concise and declarative way of expressing visualizations. Again, let’s break it down.\n\nVega-Lite expects the input data to be tabular: an array of objects with named properties. In this notebook, we’ve instead been using columnar data: a separate array of values for each column (each dimension). Fortunately, we can convert columns to rows quickly using [d3.zip](/@d3/d3-zip).`","pinned":false,"hidden":false,"mode":"ojs"},{"id":581,"value":"d3.zip(names, totals)","pinned":true,"hidden":false,"mode":"ojs"},{"id":584,"value":"md`Next, we need to tell Vega-Lite how to visualize our data by providing a mapping from an abstract dimension of our data to a graphical dimension of our chart. This chart, like many charts, is two-dimensional. The *x*-dimension encodes the total revenue estimate (column \\`1\\`), and the *y*-dimension encodes the franchise name (column \\`0\\`). The revenue estimate is quantitative (vl.fieldQ) while the franchise name is nominal (vl.fieldN).\n\nBy default, Vega-Lite doesn’t preserve the input order of the data; instead, it would sort of nominal dimension alphabetically. We want to see the highest-grossing franchises at the top, preserving the original order on Wikipedia, so we disable sorting on the *y*-dimension. This actually reveals a minor error in the Wikipedia data: Sonic the Hedgehog is slightly too high up in the chart. You can fix this by making the sort order explicit.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":616,"value":"vl.markBar()\n .data({values: d3.zip(names, totals)})\n .encode(\n vl.y().fieldN(\"0\").sort({encoding: \"x\", order: \"descending\"}).axis({title: null}),\n vl.x().fieldQ(\"1\").axis({orient: \"top\", format: \"s\", title: \"Total revenue (est.)\"})\n )\n .width(width)\n .autosize({type: \"fit-x\", contains: \"padding\"})\n .render()","pinned":true,"hidden":false,"mode":"ojs"},{"id":621,"value":"md`So far, we’ve only visualized the total revenue figure. But this table also provides a breakdown by format, for example, telling us how much revenue comes from merchandising (“where the real money from the movie is made”, as Mel Brooks declared in *Spaceballs*).\n\nLet’s take a closer look at the revenue breakdown.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":57,"value":"breakdowns = Array.from(rows, r => r.querySelector(\"td:nth-child(5)\").textContent.trim())","pinned":true,"hidden":false,"mode":"ojs"},{"id":65,"value":"breakdowns[0]","pinned":true,"hidden":false,"mode":"ojs"},{"id":626,"value":"md`For each franchise, the revenue is broken down into separate lines by channel, with the channel name and corresponding revenue separated by hyphens. So we can start by just splitting by newline and then splitting by hypen.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":68,"value":"parseBreakdown(breakdowns[0])","pinned":true,"hidden":false,"mode":"ojs"},{"id":67,"value":"function parseBreakdown(breakdown) {\n return breakdown.split(/\\n/).map(b => {\n const [channel, revenue] = b.split(\" – \");\n return {channel, revenue};\n });\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":637,"value":"md`As before, the revenue per channel isn’t directly coercible to a number, but using a regular expression, we can implement a simple parser. Here are a few test cases I wrote to get interactive feedback as I was writing the parser.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":641,"value":"revenueRe = /^\\$([0-9,]+(?:\\.[0-9]+)?)(?:\\s(b|m)illion)?/","pinned":true,"hidden":false,"mode":"ojs"},{"id":103,"value":"revenueRe.exec(\"$61.1 billion[a]\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":109,"value":"revenueRe.exec(\"$520,000[az]\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":649,"value":"parseRevenue(\"$61.1 billion[a]\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":646,"value":"parseRevenue(\"$520,000[az]\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":76,"value":"function parseRevenue(revenue) {\n const match = revenueRe.exec(revenue);\n if (!match) throw new Error(\"unable to parse revenue: \" + revenue);\n return +`${match[1].replace(/,/g, \"\")}e${match[2] === \"b\" ? 9 : match[2] === \"m\" ? 6 : 0}`;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":653,"value":"md`Putting the two together, we can compute a parallel array of revenues per channel for each franchise.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":238,"value":"revenues = Array.from(rows, r => {\n const text = r.querySelector(\"td:nth-child(5)\").textContent.trim();\n const breakdown = parseBreakdown(text);\n return breakdown.map(({channel, revenue}) => {\n return {channel, revenue: parseRevenue(revenue)};\n });\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":660,"value":"md`And lastly plugging it into Vega-Lite, we can see the data is a little messy: the estimated revenue per channel doesn’t add up to the total estimated revenue, and the channel names are inconsistent. For example, “Manga magazine” and “Manga magazines” are treated as separate channels. To make this a useful visualization, we’d want to recategorize these channels into a smaller set for comparison.\n\nA slightly bigger problem is that the channel breakdown is actually hierarchical. For example, look closer at Power Rangers:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":682,"value":"rows[names.findIndex(n => n === \"Super Sentai (Power Rangers)\")].cloneNode(true)","pinned":true,"hidden":false,"mode":"ojs"},{"id":687,"value":"md`The “Retail sales” channel is further broken down into “Licensed merchandise” and “DVD & Blu-ray”. We’d need to make our parser smarter to handle this case, but for now we’ll press on with bad data.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":265,"value":"vl.markBar()\n .data({\n values: d3.zip(names, revenues).flatMap(([name, revenues]) => {\n return revenues.map(({channel, revenue}) => {\n return {name, channel, revenue};\n });\n })\n })\n .encode(\n vl.y().fieldN(\"name\").sort(\"x\").axis({title: null}),\n vl.x().fieldQ(\"revenue\").aggregate(\"sum\").axis({orient: \"top\", format: \"s\", title: \"Revenue (est.)\"}),\n vl.color().fieldN(\"channel\")\n )\n .width(width)\n .autosize({type: \"fit-x\", contains: \"padding\"})\n .render()","pinned":true,"hidden":false,"mode":"ojs"},{"id":667,"value":"md`Thanks for reading! 🙏\n\nIf I made any mistakes, or if you have comments or questions, please let me know or send a suggestion.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":664,"value":"md`---\n\n## Appendix`","pinned":false,"hidden":false,"mode":"ojs"},{"id":245,"value":"import {vl} from \"@vega/vega-lite-api\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":325,"value":"d3 = require(\"d3-array@2\")","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W32","stdlib":"1","titling":"auto"}