2d:null 2e:["$","title",null,{"children":"Week 3a - Data Management with Apache Arrow | Yue Wu | Observable"}] 33:T45a,migration_arr = (function () { const arr = []; for (var i = 0; i < migration.length; i++){ arr.push({'Code': migration.get(i).toJSON()[12], 'Country Name': migration.get(i).toJSON()[13], 'Year 1962': migration.get(i).toJSON()[0], 'Year 1967': migration.get(i).toJSON()[1], 'Year 1972': migration.get(i).toJSON()[2], 'Year 1977': migration.get(i).toJSON()[3], 'Year 1982': migration.get(i).toJSON()[4], 'Year 1987': migration.get(i).toJSON()[5], 'Year 1992': migration.get(i).toJSON()[6], 'Year 1997': migration.get(i).toJSON()[7], 'Year 2002': migration.get(i).toJSON()[8], 'Year 2007': migration.get(i).toJSON()[9], 'Year 2012': migration.get(i).toJSON()[10], 'Year 2017': migration.get(i).toJSON()[11], }); }; return arr; }())34:[] 31:{"id":"37a22a3f10ae6b22","slug":"week-3-introduction-to-apache-arrow","title":"Week 3a - Data Management with Apache Arrow","thumbnail":"a793e4f2cb3b8f4c50517837ca37f8eaf22a60ba072ada98c818937db255af7b","access_level":"public","create_time":"$D2019-01-27T00:40:45.664Z","update_time":"$D2019-02-24T03:53:28.732Z","view_time":null,"first_public_time":"$D2019-01-28T05:18:45.152Z","has_unpublished_edits":null,"fork":false,"stars":4,"starred":false,"owner":{"type":"team","id":"37272db3a273693f","login":"wuyueqz","name":"Yue Wu","avatar_url":"https://avatars-next.observableusercontent.com/281aac8919cd8285a910c3503c2c8a7246760e62b04ee267d72bde536e9db314","tier":"starter_2024"},"version":1487,"publish_version":1487,"role":null,"authored":false,"authors":[{"type":"individual","id":"63fbae11b968202b","login":"wuyueqz","name":"Yue Wu","avatar_url":"https://avatars-next.observableusercontent.com/281aac8919cd8285a910c3503c2c8a7246760e62b04ee267d72bde536e9db314","tier":null}],"members":[{"type":"individual","id":"63fbae11b968202b","login":"wuyueqz","name":"Yue Wu","avatar_url":"https://avatars-next.observableusercontent.com/281aac8919cd8285a910c3503c2c8a7246760e62b04ee267d72bde536e9db314","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":4,"collection":{"id":"3c8b25c936e2cc07","slug":"big-data-visualization-and-society","title":"Big Data, Visualization and Society","owner":{"id":"37272db3a273693f","login":"wuyueqz","name":"Yue Wu"}},"type":"notebook","body":{"title":"Week 3a - Data Management with Apache Arrow","cells":[{"id":0,"value":"md`# Week 3a - Data Management with Apache Arrow`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4,"value":"md`## 0. Importing the Apache Arrow\n\nArrow is a platform for in-memory analytics. It defines a columnar in-memory format that is designed to be language independent, and provides reference implementations in multiple languages, meaning that the exact same memory layout used in other languages can also be used in Javascript, allowing data to be moved between those contexts without any serialization or copies on read.\n\nThe columnar layout means that data can be processed very quickly.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":8,"value":"arrow = require('@apache-arrow/es5-umd@0.3.1')","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":19,"value":"md`## 1. Arrow Tables\n\nArrow tables describe their data with *schema* objects. Let's setup a really simple table.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":37,"value":"md`### 1.1 Typed Schema objects for arrow table\n\nWe first define two schema objects indicating each row in our arrow table having a *precipitation* and a *date*, each with attributes *name, \"type\", \"nullable\" and \"children\"*. \n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":13,"value":"fields = [\n {\n name: 'precipitation',\n type: { name: 'floatingpoint', precision: 'SINGLE'},\n nullable: false, children: []\n },\n {\n name: 'date',\n type: { name: 'date', unit: 'MILLISECOND' },\n nullable: false, children: []\n }\n]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":42,"value":"md`### 1.2 Loading data into arrow table\n\nData matching a schema can be quickly ingested in a variety of ways. That includes from a file, from a process, or as below from JavaScript arrays.\n\nLet's see how it loads data from existing two JavaScript arrays *rainAmounts and rainDates* with length 3.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":101,"value":"LENGTH = 3","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":47,"value":"rainAmounts = [\n 18.3,\n 19,\n 14.1\n]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":74,"value":"rainDates = Array.from({length: LENGTH}, (_, i) => Date.now() - 1000 * 60 * 60 * 24 * i)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":117,"value":"md`\nNow we create a table *rainfall*, loading the data into the arrow table we have created in *1.1*.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":77,"value":"rainfall = arrow.Table.from({\n schema: { fields: fields },\n batches: [{ \n count: LENGTH,\n columns: [\n {name: \"precipitation\", count: LENGTH, VALIDITY: [], DATA: rainAmounts },\n {name: \"date\", count: LENGTH, VALIDITY: [], DATA: rainDates } \n ]\n }]\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":114,"value":"md`### 1.3 Reading the arrow table we created\n\nWhen done working with a dataset, we can always bring it back to regular, less optimized JavaScript. Lets access the table as following:\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":120,"value":"rainfall.length","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":130,"value":"rainfall.get(1).toString()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":133,"value":"rainfall.getColumn('date').toArray().slice()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":139,"value":"rainfall.toString()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":148,"value":"md`## 2. Reading Files\n\nThis is cool, but you don't normally load data like this though, it gets tedious!\n\nTo read a blob of data in one of Arrow's IPC formats, you can use Table.from. Note that this operation is very low overhead - no data is copied, and not much processing is happening. It just reads the file's headers to find pointers to all of the data columns, and returns an instance of Table that encapsulates that information.\n\nSince we have already imported \\`apache-arrow\\`, to start us off, lets look at a migration status data frame file by country. I've taken [Migration 1962 to 2017](https://gist.github.com/cesandoval/b834ac93c07e03ec5205843b97f68017#file-migration-txt) - we have already turned it into an [Arrow file](https://gist.githubusercontent.com/cesandoval/b834ac93c07e03ec5205843b97f68017/raw/fec3985294c49a37bd502f1f6df6a250715e22f1/migration.arrow) and load this file into the browser, which allows us to read it in order to do some analytics.\n\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1065,"value":"migration = {\n // Get the arrow file from GitHub gist as an ArrayBuffer\n const buf = await d3.buffer(migrationUrl);\n \n // Create an arrow Table with it\n return arrow.Table.from([new Uint8Array(buf)]);\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":243,"value":"md`\nJust like that let's examine the shape of the data (how many records does our data hold).\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":195,"value":"rowCount = migration.length","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1234,"value":"md`\nThe following operations allow us to access Arrow Table's row data *(first row, last row, and random row)*.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1225,"value":"firstRow = migration.get(0).toString()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1227,"value":"lastRow = migration.get(rowCount-1).toString()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1232,"value":"randomRow = migration.get(Math.random() * rowCount)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":217,"value":"md`\nNow we can start to inspect it. A critical part of every arrow file is the **Schema**, which gives us information about each column in the file, including it's name, type, bit width (for integers), whether or not it's dictionary-encoded, etc.\n\nLet's take a look at the names of each column.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":255,"value":"migration.schema.fields.map((a) => a.name)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":258,"value":"md`\nThere are **17** columns in total. We can also know the types of each column.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":263,"value":"migration.schema.fields.map((a) => a.name + \": \"+ a.type)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":283,"value":"md`\nWe can access an individual column over a table column as a regular JavaScript iterable by using *getColumn()*. For example, the following function get the **Country Name** column.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1453,"value":"migration.getColumn(\"Country Name\").toJSON()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":692,"value":"md`\nOr by the following loop. For example, the following loop get the **Country Code** column.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":269,"value":"countryCode = (function () {\n const arr = [];\n for (const x of migration.getColumn(\"Country Code\")) {\n arr.push(x)\n }\n return arr;\n}())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":428,"value":"md`\nWe can also get multiple columns of the data that could be used later. For example, we can collect statistics of **Country Name** and **Country Code** as following.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":414,"value":"[12,13].map((i) => migration.getColumnAt(i).toArray().slice())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":697,"value":"md`\nWe can also use the following custom __column stats function__ to access our column data. Let's get the stats for the migration in year **1962** column.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":628,"value":"function columnStats(columnName) {\n const column = migration.getColumn(columnName);\n let max = column.get(0);\n let min = max;\n for (let value of column) {\n if (value > max) {\n max = value;\n }\n else if (value < min) {\n min = value;\n }\n } \n return {min, max, range: max-min};\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":699,"value":"columnStats('1962')","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1429,"value":"md`\nWe can also calculate the frequency of each value within one Column by method **countBy**.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1425,"value":"migration.countBy(\"Indicator Code\").toJSON()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":508,"value":"md` ## 3. Slicing and Plot\n\nHere we customize arrow data range stepper for sampling data.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":501,"value":"function range(start, end, step) {\n let slice = [];\n for (let i=start; i ({\n 'Country Name': d[13], \n 'Year 1962': d[0],\n 'Year 1972': d[2],\n 'Year 1982': d[4],\n 'Year 1992': d[6],\n 'Year 2002': d[8],\n 'Year 2012': d[10]\n }));\n \n const table = d3_format.select(renderTable(tableData));\n \n // Right align numbers (all but the first column)\n table.selectAll('tr').selectAll('td, th')\n \t.style('text-align', (d, i) => (i === 0 ? 'left' : 'right'));\n \n return table.node();\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":163,"value":"md` ## 4. Querying\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":583,"value":"md`\nLet's do some filtering and querying. The output of this is an array. The following query tells us whether the migration number of the country in **1962** is **equal to zero** or not.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":588,"value":"migration_1962_bool = (function (){\n const arr = [];\n for (var i = 0; i < migration.length; i++){\n arr.push(migration.get(i).toJSON()[0] == 0)\n }\n return arr;\n}())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":594,"value":"md`\nTo get the table with only the rows we want, we can apply a filter. Let's take a look again at year **1962**. We can get the \"index\" into the data frame to get the rows we want as following.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":476,"value":"migration_1962 = (function () {\n const arr = [];\n for (var i =0; i < migration.length; i++){\n if (migration.get(i).toJSON()[0]==0) {\n arr.push(\"Index: \" + i + \": \" + migration.get(i).toJSON())\n }\n }\n return arr;\n}())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":597,"value":"md`\nWe can query based on multiple conditions, by using **&&** *(and)*, **( | )** *(or)* condition. The following identifies the countries which has positive migration number in 1962 but negative number in 2012.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":482,"value":"migration_1962_and_2012 = (function () {\n const arr = [];\n for (var i =0; i < migration.length; i++){\n if (migration.get(i).toJSON()[0] > 0 && migration.get(i).toJSON()[10] < 0) {\n arr.push(\"Index: \" + i + \": \" + migration.get(i).toJSON())\n }\n }\n return arr;\n}())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":766,"value":"md`\nWe can loop the data to summarize the migration status over all the years across date frame as following.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":762,"value":"migration_sum = (function () { \n var sum_migration = []; \n for (var i = 0; i < migration.length; i++){\n var sum = 0; \n for (var j = 0; j < 12; j ++){\n sum += migration.get(i).toJSON()[j]\n } \n sum_migration[i] = sum; \n } \n return sum_migration;\n}())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":621,"value":"md`\nWe can perform operations to locate which rows (ie which countries) have positive cumulative migration increase till 2017.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":625,"value":"migration_positive = (function () {\n const arr = [];\n for (var i = 0; i < migration.length; i++){\n if (migration_sum[i] > 0) {\n arr.push(\"Index: \" + i + \": \" + migration.get(i).toJSON())\n }\n }\n return arr;\n}())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":168,"value":"md` ## 5. Cleaning\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":555,"value":"md`\nA common cause of errors is having **Null** or **'None' values**. This usually means data was missing. For example, let's look into **No.50** record *(which is the 51 record)* of our dataset that represents the migration status of **Cayman Islands**.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":546,"value":"migration.get(50).toJSON()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":532,"value":"migration_null = (function () {\n const arr = [];\n for (var i =0; i < migration.length; i++){\n if (migration.get(i).toJSON()[11]==null) {\n arr.push(\"Index: \" + i + \": \" + migration.get(i).toJSON())\n }\n }\n return arr;\n}())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":566,"value":"md`\nBy using the query above (we choose the most recent record year *2017* to operate our query, identifying whether this value is null), we can find that we had some incomplete data *(24 records)*! We can get rid of it.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":635,"value":"migration_valid = (function () {\n const arr = [];\n for (var i =0; i < migration.length; i++){\n if (migration.get(i).toJSON()[11]!=null) {\n arr.push(migration.get(i).toJSON())\n }\n }\n return arr;\n}())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":657,"value":"migration_valid.length","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":660,"value":"md`\nWe removed those 24 rows.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":172,"value":"md` ## 6. Joining\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":833,"value":"md`\nIn this section, let's look at [another data set], and do some grouping, joins, and comparisons. This time we are going to reference another \\`apache-arrow\\` from our github repo like the following.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1273,"value":"migrationMeta = {\n // Get the arrow file from GitHub gist as an ArrayBuffer\n const buf = await d3.buffer(migrationMetaUrl);\n \n // Create an arrow Table with it\n return arrow.Table.from([new Uint8Array(buf)]);\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":868,"value":"md`\nFirst, always remember to check the **length** of our data and the **fields** of the data. For example, in this case the length of our new data set and the previous one does not totally match.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":846,"value":"migrationMeta.length","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":849,"value":"migrationMeta.schema.fields.map((a) => a.name)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":839,"value":"md`\nSo how can we perform the join? Notice that both tables have the field of **Country Code** which can allow us to join them together. Let's get the value of the **Country Code**.\n\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":829,"value":"[...new Set(migrationMeta.getColumn(\"Country Code\"))]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1314,"value":"md`\nIn order to perform the join, we first build two tables and extract **Country Code** as our joint key.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":944,"value":"migrationMeta_arr = (function () {\n const arr = [];\n for (var i = 0; i < migrationMeta.length; i++){\n arr.push({'Code': migrationMeta.get(i).toJSON()[0],\n 'IncomeGroup': migrationMeta.get(i).toJSON()[1],\n 'Region': migrationMeta.get(i).toJSON()[2],\n 'SpecialNotes': migrationMeta.get(i).toJSON()[3],\n 'TableName': migrationMeta.get(i).toJSON()[4]\n });\n };\n return arr;\n}())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1325,"value":"md`\nIn the following case we just use every 10 years' data from 1962 to 2012 for demonstration.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1319,"value":"$33","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":956,"value":"md`\nRun a join!\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1331,"value":"migration_all = migration_arr.map(x => Object.assign(x, migrationMeta_arr.find(y => y.Code == x.Code)));","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1360,"value":"migration_join_fail = (function () {\n const arr = [];\n for (var i = 0; i < migration_all.length; i++){\n if (migration_all[i].Region==null) {\n arr.push(\"Index: \" + i + \": \" + migration_all[i])\n }\n }\n return arr;\n}())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1365,"value":"migration_all[108]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":987,"value":"md`\nBy accessing this row we realize that this record with Country Name **Not classified** does not exist in the our new dataset with 263 records. That's why these two tables have length discrepancy. Here we modify our query and finally get the valid records.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1436,"value":"migration_join_valid = (function () {\n const arr = [];\n for (var i = 0; i < migration_all.length; i++){\n if (migration_all[i].Region!=null) {\n arr.push(migration_all[i])\n }\n }\n return arr;\n}())","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1476,"value":"md`\nWe can then perform sorting and other manipulations based on it using the following sorting function *(this is a descending version)*.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1464,"value":"sortByKey = function(array, key) {\n return array.sort(function(a, b) {\n var x = a[key]; var y = b[key];\n return ((x > y) ? -1 : ((x < y) ? 1 : 0));\n });\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1469,"value":"sortByKey(migration_join_valid, \"Year 2017\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":160,"value":"md`---\n\n## Appendix`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1278,"value":"md`\n### Dataset\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1063,"value":"migrationUrl = \"https://gist.githubusercontent.com/cesandoval/b834ac93c07e03ec5205843b97f68017/raw/fec3985294c49a37bd502f1f6df6a250715e22f1/migration.arrow\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1270,"value":"migrationMetaUrl = \"https://gist.githubusercontent.com/cesandoval/b834ac93c07e03ec5205843b97f68017/raw/f61bb251cdd04f791b7689d81bb62f6f9a756004/migration_metadata.arrow\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1281,"value":"md`\n### D3 Function\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1059,"value":"d3 = require('d3-fetch')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1112,"value":"d3_format=require(\"d3@5\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1284,"value":"md`\n### Format\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1109,"value":"md`\nThis is the function for rendering a table with good format.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1107,"value":"import { renderTable } from '@tmcw/fancy-tables'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W34","stdlib":"1","titling":"auto"}} 32:"$31:body"