31:T5dd,md` You might be able to parse this out by yourself, but fortunately we have a data dictionary handy: 1. The first three letters of each column denote whether the column contains new or old cases of TB. In this dataset, each column contains new cases. 2. The next two letters describe the type of TB: * \`rel\` stands for cases of relapse * \`ep\` stands for cases of extrapulmonary TB * \`sn\` stands for cases of pulmonary TB that could not be diagnosed by a pulmonary smear (smear negative) * \`sp\` stands for cases of pulmonary TB that could be diagnosed by a pulmonary smear (smear positive) 3. The sixth letter gives the sex of TB patients. The dataset groups cases by males (\`m\`) and females (\`f\`). 4. The remaining numbers gives the age group. The dataset groups cases into seven age groups: * \`014\` = 0 – 14 years old * \`1524\` = 15 – 24 years old * \`2534\` = 25 – 34 years old * \`3544\` = 35 – 44 years old * \`4554\` = 45 – 54 years old * \`5564\` = 55 – 64 years old * \`65\` = 65 or older We need to make a minor fix to the format of the column names: unfortunately the names are slightly inconsistent because instead of new_rel we have newrel (it’s hard to spot this here but if you don’t fix it we’ll get errors in subsequent steps). Using the [\`op.replace\`](https://uwdata.github.io/arquero/api/op#replace) function, we will replace the characters \`'newrel'\` with \`'new_rel'\`. This makes all variable names consistent. `32:T11ff,function crime_csv() { return `Reported crime in Alabama, , 2004,4029.3 2005,3900 2006,3937 2007,3974.9 2008,4081.9 , Reported crime in Alaska, , 2004,3370.9 2005,3615 2006,3582 2007,3373.9 2008,2928.3 , Reported crime in Arizona, , 2004,5073.3 2005,4827 2006,4741.6 2007,4502.6 2008,4087.3 , Reported crime in Arkansas, , 2004,4033.1 2005,4068 2006,4021.6 2007,3945.5 2008,3843.7 , Reported crime in California, , 2004,3423.9 2005,3321 2006,3175.2 2007,3032.6 2008,2940.3 , Reported crime in Colorado, , 2004,3918.5 2005,4041 2006,3441.8 2007,2991.3 2008,2856.7 , Reported crime in Connecticut, , 2004,2684.9 2005,2579 2006,2575 2007,2470.6 2008,2490.8 , Reported crime in Delaware, , 2004,3283.6 2005,3118 2006,3474.5 2007,3427.1 2008,3594.7 , Reported crime in District of Columbia, , 2004,4852.8 2005,4490 2006,4653.9 2007,4916.3 2008,5104.6 , Reported crime in Florida, , 2004,4182.5 2005,4013 2006,3986.2 2007,4088.8 2008,4140.6 , Reported crime in Georgia, , 2004,4223.5 2005,4145 2006,3928.8 2007,3893.1 2008,3996.6 , Reported crime in Hawaii, , 2004,4795.5 2005,4800 2006,4219.9 2007,4119.3 2008,3566.5 , Reported crime in Idaho, , 2004,2781 2005,2697 2006,2386.9 2007,2264.2 2008,2116.5 , Reported crime in Illinois, , 2004,3174.1 2005,3092 2006,3019.6 2007,2935.8 2008,2932.6 , Reported crime in Indiana, , 2004,3403.6 2005,3460 2006,3464.3 2007,3386.5 2008,3339.6 , Reported crime in Iowa, , 2004,2904.8 2005,2845 2006,2870.3 2007,2648.6 2008,2440.5 , Reported crime in Kansas, , 2004,4015.5 2005,3806 2006,3858.5 2007,3693.8 2008,3397 , Reported crime in Kentucky, , 2004,2540.2 2005,2531 2006,2621.9 2007,2524.6 2008,2677.1 , Reported crime in Louisiana, , 2004,4419.1 2005,3696 2006,4088.5 2007,4196.1 2008,3880.2 , Reported crime in Maine, , 2004,2413.7 2005,2419 2006,2546.1 2007,2448.3 2008,2463.7 , Reported crime in Maryland, , 2004,3640.7 2005,3551 2006,3481.2 2007,3431.5 2008,3516 , Reported crime in Massachusetts, , 2004,2468.2 2005,2358 2006,2396 2007,2399.2 2008,2402 , Reported crime in Michigan, , 2004,3066.1 2005,3098 2006,3226 2007,3057.8 2008,2945.7 , Reported crime in Minnesota, , 2004,3041.6 2005,3088 2006,3088.8 2007,3045 2008,2858.1 , Reported crime in Mississippi, , 2004,3481.1 2005,3274 2006,3213 2007,3137.8 2008,2941.7 , Reported crime in Missouri, , 2004,3900.1 2005,3929 2006,3828.4 2007,3828.2 2008,3663.6 , Reported crime in Montana, , 2004,2936.1 2005,3146 2006,2863.4 2007,2863.6 2008,2720.9 , Reported crime in Nebraska, , 2004,3519.6 2005,3432 2006,3364.9 2007,3142.8 2008,2878.3 , Reported crime in Nevada, , 2004,4210 2005,4246 2006,4099.6 2007,3785.1 2008,3456.4 , Reported crime in New Hampshire, , 2004,2051.9 2005,1839 2006,2061.8 2007,1968.6 2008,2132.1 , Reported crime in New Jersey, , 2004,2433 2005,2337 2006,2278.4 2007,2205.5 2008,2293.4 , Reported crime in New Mexico, , 2004,4198.4 2005,4132 2006,3947.5 2007,3846.7 2008,3817.4 , Reported crime in New York, , 2004,2192.5 2005,2102 2006,2063.2 2007,1992.1 2008,1993.7 , Reported crime in North Carolina, , 2004,4160.5 2005,4080 2006,4119.5 2007,4101.8 2008,4041.1 , Reported crime in North Dakota, , 2004,1963.4 2005,2025 2006,2088.6 2007,1996.8 2008,2016.3 , Reported crime in Ohio, , 2004,3662.3 2005,3668 2006,3716.2 2007,3461.6 2008,3419.2 , Reported crime in Oklahoma, , 2004,4242.1 2005,4047 2006,3625 2007,3549.8 2008,3456.6 , Reported crime in Oregon, , 2004,4635.4 2005,4402 2006,3719.1 2007,3530.1 2008,3299.2 , Reported crime in Pennsylvania, , 2004,2417.3 2005,2422 2006,2451 2007,2364.4 2008,2412.4 , Reported crime in Rhode Island, , 2004,2886 2005,2728 2006,2614.6 2007,2602.2 2008,2845 , Reported crime in South Carolina, , 2004,4536.9 2005,4370 2006,4277.1 2007,4282.6 2008,4241.2 , Reported crime in South Dakota, , 2004,1931.6 2005,1767 2006,1811.1 2007,1774 2008,1706.1 , Reported crime in Tennessee, , 2004,4326.8 2005,4300 2006,4137.7 2007,4092.2 2008,4048.3 , Reported crime in Texas, , 2004,4497.7 2005,4319 2006,4084.2 2007,4121.6 2008,3987 , Reported crime in Utah, , 2004,4038.6 2005,3837 2006,3505.3 2007,3510.1 2008,3374 , Reported crime in Vermont, , 2004,2343.6 2005,2370 2006,2368 2007,2339 2008,2559.8 , Reported crime in Virginia, , 2004,2678.2 2005,2649 2006,2479.6 2007,2480 2008,2531.8 , Reported crime in Washington, , 2004,4846.7 2005,4890 2006,4483.3 2007,4026 2008,3785 , Reported crime in West Virginia, , 2004,2555.8 2005,2633 2006,2639.9 2007,2543.6 2008,2554.4 , Reported crime in Wisconsin, , 2004,2665.7 2005,2669 2006,2820 2007,2842.8 2008,2761.1 , Reported crime in Wyoming, , 2004,3338.5 2005,3158 2006,2989.1 2007,2883.2 2008,2724.2 `; }33:[{"id":"afddd408b20ef62c6bf231507b17fb5d11c105fbec348ed4fc3a7bc31f425c179775dd1f4bb0e97c42a9331dd22756bb0226aba2b623f910ea724668d2e49800","href":"https://static.observableusercontent.com/files/afddd408b20ef62c6bf231507b17fb5d11c105fbec348ed4fc3a7bc31f425c179775dd1f4bb0e97c42a9331dd22756bb0226aba2b623f910ea724668d2e49800","name":"who.csv","type":"text/csv","lastModified":1607642460471.444,"size":1484361}] 2f:{"title":"Tidy Data in JavaScript","cells":[{"id":0,"value":"md`# Tidy Data in JavaScript`","pinned":false,"hidden":false,"mode":"ojs"},{"id":70,"value":"md`\"Clean\" data is a deceptive fiction. In reality, we must regularly wrangle, cajole, prepare, and format data for downstream use. This notebook explores how to transform messy datasets into a more usable _tidy_ format.\n\nThis notebook is a translation of Hadley Wickham's [Tidy Data (Chapter 12, R for Data Science)](https://r4ds.had.co.nz/tidy-data.html) to JavaScript using [Arquero](https://observablehq.com/@uwdata/introducing-arquero). Please see the original for more context and explanation!\n\nFor a quick overview of Arquero, see [An Illustrated Guide to Arquero Verbs](https://observablehq.com/@uwdata/an-illustrated-guide-to-arquero-verbs).\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":617,"value":"md`\n### Table of Contents\n\n* [Tidy Data](#tidy_data)\n* [Reshape Data with Fold and Pivot](#reshape_with_fold_and_pivot)\n* [Spreading and Merging Values](#spreading_and_merging)\n* [Handling Missing Values](#handling_missing_values)\n* [Case Study: Tuberculosis Cases](#case_study_tuberculosis)\n* [Case Study: Housing Crime](#case_study_housing_crime)\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":73,"value":"tidy_data = md`

\n## Tidy Data`","pinned":false,"hidden":false,"mode":"ojs"},{"id":781,"value":"md`Here are the same data organized in four different ways. Each dataset shows the same values of the variables \\`country\\`, \\`year\\`, \\`cases\\` and \\`population\\`, but each with a different table layout. The first table uses a _tidy_ organization where each column corresponds to a different measurement:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":344,"value":"table1.view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":356,"value":"md`The second table has a _folded_ organization, in which \\`cases\\` and \\`population\\` are intermingled:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":347,"value":"table2.view({ height: 400 })","pinned":true,"hidden":false,"mode":"ojs"},{"id":360,"value":"md`The third table combines both \\`cases\\` and \\`population\\` into a string-valued \\`rate\\`:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":349,"value":"table3.view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":364,"value":"md`Finally, the fourth version involves two tables: one for \\`cases\\` (\\`table4a\\`) and one for \\`population\\` (\\`table4b\\`):`","pinned":false,"hidden":false,"mode":"ojs"},{"id":367,"value":"table4a.view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":369,"value":"table4b.view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":375,"value":"md`Our goal is to put data in a _tidy_ format, in which:\n* Each variable has its own column.\n* Each observation has its own row.\n* Each value has its own cell.\n\nAbove, only \\`table1\\` is in a _tidy_ format. We would like our data to be tidy not just for consistency, but because a tidy format is typically the most convenient for modeling and transforming our data.\n\nFor example, here are some straightforward transformations of \\`table1\\`:\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":60,"value":"// Compute rate per 10,000\ntable1\n .derive({ rate: d => d.cases / d.population * 10000 })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":62,"value":"// Compute cases per year\ntable1\n .groupby('year')\n .rollup({ cases: op.sum('cases') })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":65,"value":"// Visualize changes over time\nvl.markLine({ point: true })\n .data(table1)\n .encode(\n vl.x().fieldQ('year').axis({ tickMinStep: 1, format: 'd' }),\n vl.y().fieldQ('cases'),\n vl.color().fieldN('country')\n )\n .render()","pinned":true,"hidden":false,"mode":"ojs"},{"id":77,"value":"reshape_with_fold_and_pivot = md`

\n## Reshape Data with Fold and Pivot`","pinned":false,"hidden":false,"mode":"ojs"},{"id":396,"value":"md`\nHere are two common reasons that a dataset may not be tidy:\n\n1. A variable is spread across multiple columns.\n2. An observation is scattered across multiple rows.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":91,"value":"md`### Fold (a.k.a. Pivot Longer)`","pinned":false,"hidden":false,"mode":"ojs"},{"id":403,"value":"md`When variables are spread over multiple columns, we can _fold_ the data, such that multiple columns are combined into a longer table with more rows. In other tools, this operation is also referred to as _pivot longer_ or _unpivot_.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":406,"value":"md`For example, \\`table4a\\` has \\`cases\\` per \\`year\\` spread over multiple columns:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":100,"value":"table4a.view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":410,"value":"md`We can [\\`fold()\\`](https://uwdata.github.io/arquero/api/verbs#fold) the data to a tidy format with a \\`year\\` column and a \\`cases\\` column:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":80,"value":"viewof fold4a = table4a\n .fold(['1999', '2000'], { as: ['year', 'cases'] })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":415,"value":"md`And similarly for \\`table4b\\`:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":82,"value":"viewof fold4b = table4b\n .fold(['1999', '2000'], { as: ['year', 'population'] })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":418,"value":"md`To combine our tables into a single tidy table, apply the [\\`join_left()\\`](https://uwdata.github.io/arquero/api/verbs#join_left) verb:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":89,"value":"fold4a\n .join_left(fold4b)\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":95,"value":"md`### Pivot (a.k.a. Pivot Wider)`","pinned":false,"hidden":false,"mode":"ojs"},{"id":425,"value":"md`Now let's consider how to handle observations that are scattered across multiple rows. Recall that \\`table2\\` has \\`cases\\` and \\`population\\` intermingled:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":98,"value":"table2.view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":428,"value":"md`We can apply a [\\`pivot()\\`](https://uwdata.github.io/arquero/api/verbs#pivot) transform to turn the values in \\`table2\\`'s \\`type\\` column into column names, and use the values of the \\`count\\` column to populate these new columns. In other tools, this operation is also referred to as _pivot wider_ or _unfold_.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":102,"value":"table2\n .groupby('country', 'year')\n .pivot('type', 'count')\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":432,"value":"md`Much tidier!`","pinned":false,"hidden":false,"mode":"ojs"},{"id":107,"value":"spreading_and_merging = md`

\n## Spreading and Merging Values`","pinned":false,"hidden":false,"mode":"ojs"},{"id":436,"value":"md`We've tidied \\`table2\\` and \\`table4\\`, but what about \\`table3\\`? This table has a different problem: multiple variables are encoded in a single column within a string of text.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":115,"value":"table3.view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":112,"value":"md`### Split and Spread (a.k.a. Separate)`","pinned":false,"hidden":false,"mode":"ojs"},{"id":441,"value":"md`To tidy \\`table3\\`, we need to split up the text and extract the numbers on each side of the \\`'/'\\` character. We can do this in two steps:\n\n1. Split the text into an array of desired values using the [\\`op.split\\`](https://uwdata.github.io/arquero/api/op#split) function.\n2. Distribute the values across multiple columns using the [\\`spread()\\`](https://uwdata.github.io/arquero/api/verbs#spread) verb.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":117,"value":"table3\n .spread({ rate: d => op.split(d.rate, '/') }, { as: ['cases', 'population' ] })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":444,"value":"md`Above we split the \\`rate\\` column using \\`'/'\\` as a delimiter, then spread the resulting values into new \\`cases\\` and \\`population\\` columns. The [\\`spread()\\`](https://uwdata.github.io/arquero/api/verbs#spread) transformation verb is flexible, supporting any array inputs. The verb can generate an arbitrary number of new columns (depending on the input array size), but will limit the output to a set of target column names when the \\`as\\` option is provided.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":447,"value":"md`However, we still have a problem: the split values above are text strings. (Notice how the \\`cases\\` and \\`population\\` columns are left-aligned in the table view?) We might want to additionally coerce the split values to ensure they are numeric:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":127,"value":"// this time coercing split values to numbers\ntable3\n .spread({ rate: d => op.split(d.rate, '/') }, { as: ['cases', 'population' ] })\n .derive({ cases: d => +d.cases, population: d => +d.population })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":453,"value":"md`For a different example, imagine that we instead want to split the \\`year\\` column, separating the \\`century\\` from the last two digits of the \\`year\\`. For this case, we can use the [\\`op.match\\`](https://uwdata.github.io/arquero/api/op#match) function with a [regular expression](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Regular_Expressions) that matches each two-character sequence and returns a \\`spread\\`-able array:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":130,"value":"viewof table5 = table3\n .spread({ year: d => op.match(d.year, /.{2}/g) }, { as: ['century', 'year'] })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":144,"value":"md`### Merge (a.k.a. Unite)`","pinned":false,"hidden":false,"mode":"ojs"},{"id":461,"value":"md`The inverse of spreading values is merging multiple columns together. We can flexibly merge values using the [\\`derive()\\`](https://uwdata.github.io/arquero/api/verbs#derive) verb, for example using standard [template strings](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals) or string concatenation. Here we create a \\`new\\` column that merges \\`table5\\`'s \\`century\\` and \\`year\\` columns, with an underscore (\\`'_'\\`) character between them. We also \\`select()\\` away the columns we no longer need:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":148,"value":"table5\n .derive({ new: d => `${d.century}_${d.year}` }, { before: 'year' })\n .select(aq.not('year', 'century'))\n .view();","pinned":true,"hidden":false,"mode":"ojs"},{"id":468,"value":"md`To recover the full year, we can concatenate the year components together directly, and coerce the result to a number:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":154,"value":"table5\n .derive({ new: d => +`${d.century}${d.year}` }, { before: 'year' })\n .select(aq.not('year', 'century'))\n .view();","pinned":true,"hidden":false,"mode":"ojs"},{"id":158,"value":"handling_missing_values = md`

\n## Handling Missing Values`","pinned":false,"hidden":false,"mode":"ojs"},{"id":472,"value":"md`Handling missing values is another common data wrangling challenge. To make matters even more complicated, values can be missing in multiple ways:\n\n1. _Explicit_ missing values within a table cell. Arquero treats both \\`null\\` and \\`undefined\\` as explicit missing values.\n2. _Implicit_ missing observations, where entire rows of interest may be omitted.\n\nTo make this clearer, let's consider a simple dataset of \\`stocks\\`:\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":161,"value":"stocks = aq.table({\n year: [2015, 2015, 2015, 2015, 2016, 2016, 2016],\n qtr: [ 1, 2, 3, 4, 2, 3, 4],\n return: [1.88, 0.59, 0.35, null, 0.92, 0.17, 2.66]\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":480,"value":"stocks.view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":483,"value":"md`There are two forms of missing values in this dataset:\n\n* The \\`return\\` for the fourth quarter of 2015 is an _explicit_ missing value: the cell where its value should be instead contains \\`null\\`.\n* Data for the first quarter of 2016 is an _implicit_ missing observation: it simply does not appear in the dataset.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":487,"value":"md`A different organization can make implicit values explicit. Here we can make the implicit missing observation explicit by _pivoting_ the \\`year\\` values into new columns:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":167,"value":"stocks\n .groupby('qtr')\n .pivot('year', 'return')\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":501,"value":"md`If we then _fold_ the data back to its original format, we gain an _explicit_ missing value for our previously _implicit_ missing observation for Q1 2016:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":499,"value":"stocks\n .groupby('qtr')\n .pivot('year', 'return')\n .fold(['2015', '2016'], { as: ['year', 'return'] })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":495,"value":"md`In some cases, explicit missing values may not be important for our analysis. To remove those records, we can [\\`filter()\\`](https://uwdata.github.io/arquero/api/verbs#filter) them away:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":172,"value":"stocks\n .groupby('qtr')\n .pivot('year', 'return')\n .fold(['2015', '2016'], { as: ['year', 'return'] })\n .filter(d => d.return != null)\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":506,"value":"md`While the trick of performing a _pivot_ and then a _fold_ helped us above, Arquero provides a more powerful transformation verb for handling both explicit and implicit missing values: [\\`impute()\\`](https://uwdata.github.io/arquero/api/verbs#impute).`","pinned":false,"hidden":false,"mode":"ojs"},{"id":509,"value":"md`To impute _explicit_ missing values, we can provide a replacement expression. Below we simply set missing values to zero; however, \\`impute()\\` also supports more complex expressions including aggregates (e.g., set all missing values to their group mean).`","pinned":false,"hidden":false,"mode":"ojs"},{"id":511,"value":"stocks\n .groupby('qtr')\n .impute({ return: () => 0 })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":521,"value":"md`The \\`impute()\\` verb can also _expand_ a table to add _implicit_ missing observations. Below we use the \\`expand\\` option to consider all combinations of the \\`year\\` and \\`qtr\\` columns, adding new rows for combinations that are missing from the input table:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":314,"value":"// expand rows to include all year x qtr combinations\nstocks\n .impute({ return: () => null }, { expand: ['year', 'qtr'] })\n .orderby('year', 'qtr')\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":527,"value":"md`### Filling In Missing Sequential Values`","pinned":false,"hidden":false,"mode":"ojs"},{"id":530,"value":"md`In some cases, considering the sequence of values within a table allows us to _fill_ in explicit missing values. Consider the table below, where the empty \\`person\\` cells correspond to the value \\`'Derrick Whitmore'\\`.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":182,"value":"viewof treatment = aq.table({\n person: ['Derrick Whitmore', null, null, 'Katherine Burke'],\n treatment: [1, 2, 3, 1],\n response: [7, 10, 9, 4]\n}).view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":536,"value":"md`We can use the [\\`op.fill_down\\`](https://uwdata.github.io/arquero/api/op#fill_down) window function to impute the missing values: \\`fill_down\\` replaces missing values with the most recently observed non-missing value. Here, we derive a new \\`person\\` column with the missing values filled in.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":299,"value":"// fill missing values in a downward direction\ntreatment\n .derive({ person: op.fill_down('person') })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":542,"value":"md`The analagous [\\`op.fill_up\\`](https://uwdata.github.io/arquero/api/op#fill_up) function instead returns the _next_ non-missing value in the table (inclusive of the current value) rather than the previously observed value.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":203,"value":"case_study_tuberculosis = md`

\n## Case Study: Tuberculosis Cases`","pinned":false,"hidden":false,"mode":"ojs"},{"id":562,"value":"md`To put the above examples into context, let's consider a case study with tuberculosis (TB) cases broken down by year, country, age, gender, and diagnosis method. The data comes from the 2014 World Health Organization Global Tuberculosis Report, available at http://www.who.int/tb/country/data/download/en/.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":566,"value":"md`The data is valuable, but messy: it contains redundant columns, odd variable codes, and many missing values. Here's what it looks like upon load. As the data was originally exported from R, missing values are represented as \\`'NA'\\` strings, rather than \\`null\\` or \\`undefined\\` values.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":207,"value":"// Note: NA values from R show up here as 'NA' strings\nwho.view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":570,"value":"md`\nWhat can we see?\n\n* It looks like \\`country\\`, \\`iso2\\`, and \\`iso3\\` are three variables that redundantly specify the country.\n* \\`year\\` is clearly also a variable.\n* We don’t know what all the other columns are yet, but given the structure in the variable names (e.g., \\`new_sp_m014\\`, \\`new_ep_m014\\`, \\`new_ep_f014\\`) these are likely to be values, not variables.\n\nSo we need to gather together all the columns from \\`new_sp_m014\\` to \\`newrel_f65\\`. We don’t know what those values represent yet, so we’ll give them the generic name \\`'key'\\`. We know the cells represent the count of cases, so we’ll use the variable \\`cases\\`. There are a lot of missing values; for now we can just filter them out and focus on the values that are present.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":209,"value":"viewof who1 = who\n .fold(aq.range('new_sp_m014', 'newrel_f65'), { as: ['key', 'cases' ] })\n .filter(d => d.cases !== 'NA')\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":574,"value":"md`We can get a hint of the structure of the values in the \\`key\\` column by counting them:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":217,"value":"who1\n .groupby('key')\n .count()\n .orderby('key')\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":579,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":224,"value":"viewof who2 = who1\n .derive({ key: d => op.replace(d.key, 'newrel', 'new_rel') })\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":584,"value":"md`We can separate the values in each code with two passes of \\`split\\` and \\`spread\\`. The first pass splits the codes at each underscore (\\`'_'\\`).`","pinned":false,"hidden":false,"mode":"ojs"},{"id":228,"value":"viewof who3 = who2\n .spread({ key: d => op.split(d.key, '_') }, { as: ['new', 'type', 'sexage'] })\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":587,"value":"md`We might as well drop the \\`new\\` column because it is constant in this dataset. While we’re dropping columns, we can also drop \\`iso2\\` and \\`iso3\\` since they are redundant.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":235,"value":"// check count of unique new values -- it matches the total row count!\nwho3\n .groupby('new')\n .count()\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":237,"value":"// drop the new, iso2, and iso3 columns\nwho4 = who3\n .select(aq.not('new', 'iso2', 'iso3'))","pinned":true,"hidden":false,"mode":"ojs"},{"id":592,"value":"md`Next, let's split \\`sexage\\` into \\`sex\\` and \\`age\\` by separating the first character:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":239,"value":"viewof who5 = who4\n .derive({\n sex: d => d.sexage[0],\n age: d => op.slice(d.sexage, 1)\n })\n .select(aq.not('sexage'))\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":595,"value":"md`\nThe \\`who\\` dataset is now tidy!\n\nWhile we built our transformations above step-by-step, in practice you will often work interactively to gradually build up a pipeline of transformations. Let's consolidate our work into a single set of transformations:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":242,"value":"// collapse operations above into a single pipeline\nwho\n .fold(aq.range('new_sp_m014', 'newrel_f65'), { as: ['key', 'cases' ] })\n .filter(d => d.cases !== 'NA')\n .derive({ key: d => op.replace(d.key, 'newrel', 'new_rel') })\n .spread({ key: d => op.split(d.key, '_') }, { as: ['new', 'type', 'sexage'] })\n .derive({\n sex: d => d.sexage[0],\n age: d => op.slice(d.sexage, 1)\n })\n .select(aq.not('new', 'iso2', 'iso3', 'sexage'))\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":600,"value":"md`Upon review, we can also find opportunities to further optimize our pipeline:\n\n1. Arquero's [\\`filter()\\`](https://uwdata.github.io/arquero/api/verbs#filter) verb does not actually remove data: it creates a \"mask\" that indicates which filtered rows to skip. This approach can make processing more performant, as we avoid unnecessarily copying data into new data structures. However, if we will use only the filtered data for subsequent operations, we can compress our filtered data into a new table using the [\\`reify()\\`](https://uwdata.github.io/arquero/api/verbs#reify) verb.\n\n2. At the cost of using a more complicated [regular expression](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Regular_Expressions), we can combine the \\`spread\\` and \\`derive\\` calls into a single \\`spread\\` call using [\\`op.match\\`](https://uwdata.github.io/arquero/api/op#match).`","pinned":false,"hidden":false,"mode":"ojs"},{"id":305,"value":"// further compress using regular expression matching\nwho\n .fold(aq.range('new_sp_m014', 'newrel_f65'), { as: ['key', 'cases' ] })\n .filter(d => d.cases !== 'NA')\n .reify() // reallocates new table without filtered rows\n .derive({ key: d => op.replace(d.key, 'newrel', 'new_rel') })\n .spread({ key: d => op.match(d.key, /new_([a-z]{2})_(m|f)(\\d+)/) }, { as: ['new', 'type', 'sex', 'age'] })\n .select(aq.not('new', 'iso2', 'iso3'))\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":632,"value":"case_study_housing_crime = md`

\n## Case Study: Housing Crime`","pinned":false,"hidden":false,"mode":"ojs"},{"id":635,"value":"md`Finally, let's examine one additional case study, wrangling a strangely formatted dataset into a more usable form. The dataset contains housing crime statistics published by the U.S. government, taken from the [Data Wrangler demo](http://vis.stanford.edu/wrangler/).\n\nWe will build up our transformation script step-by-step.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":640,"value":"md`### Load Data\nFirst let's load the data using [\\`fromCSV()\\`](https://uwdata.github.io/arquero/api/#fromCSV). We specify the \\`{ header: false }\\` option as the CSV text lacks a header row.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":643,"value":"aq.fromCSV(crime_csv(), { header: false }) // <- parse CSV data, provided by the crime_csv method\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":692,"value":"md`The data is in a messy format with separate blocks for each state and with intermingled state and year values that should be in separate columns. Before going forward, we need to think about what steps are needed to arrive at a tidy format.\n\nWe need to:\n\n* rename our columns\n* filter out empty rows\n* extract state names into their own column\n* place year values into a clean, dedicated column\n\n_How much progress can you make on your own, without referring to the steps below?_`","pinned":false,"hidden":false,"mode":"ojs"},{"id":652,"value":"md`### Rename Columns\n\nTo start, let's rename the default column names to something more meaningful.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":654,"value":"aq.fromCSV(crime_csv(), { header: false, names: ['year', 'rate'] }) // <- provide column names\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":662,"value":"md`### Filter Empty Rows\n\nNext, let's filter out all the empty rows in between blocks.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":664,"value":"aq.fromCSV(crime_csv(), { header: false, names: ['year', 'rate'] })\n .filter(d => d.year != null || d.rate != null) // <-- remove if both columns are empty\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":666,"value":"md`### Extract State Name to New Column\n\nA major issue with this dataset is that the state names and years are intermingled in the same column. Moreover, the state names are buried within a descriptive text string. We can extract state names using a [regular expression pattern](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Regular_Expressions) in [\\`op.match\\`](https://uwdata.github.io/arquero/api/op#match), selecting all text after the prefix \\`\"Reported crime in \"\\`.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":668,"value":"aq.fromCSV(crime_csv(), { header: false, names: ['year', 'rate'] })\n .filter(d => d.year != null || d.rate != null)\n .derive({\n state: d => op.match(d.year, /Reported crime in (.*)/, 1) // <- extract state name\n }, { before: 0 }) // <- make new column the first column in the output table\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":709,"value":"md`The pattern \\`(.*)\\` selects all text after our prefix, and the parentheses assign this text to a [capture group](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Regular_Expressions/Groups_and_Ranges). The third argument (\\`1\\`) to \\`op.match\\` indicates that we want the results of this first (and in this case, only) capture group.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":670,"value":"md`### Fill In Missing State Names\nThe step above extracts the state name but leaves us with a number of explicit missing values. The window function [\\`op.fill_down\\`](https://uwdata.github.io/arquero/api/op#fill_down) fills in missing values by using the last observed valid value. We add this function within our previous step to fill in state names from top to bottom.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":672,"value":"aq.fromCSV(crime_csv(), { header: false, names: ['year', 'rate'] })\n .filter(d => d.year != null || d.rate != null)\n .derive({\n state: d => op.fill_down(op.match(d.year, /Reported crime in (.*)/, 1)) // <- also fill in state names\n }, { before: 0 })\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":675,"value":"md`### Filter Unneeded Rows\n\nFinally, we can filter out the rows we no longer need: those missing a \\`rate\\` value (or equivalently, those in which the \\`year\\` column starts with \\`'Reported crime in'\\`).\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":677,"value":"aq.fromCSV(crime_csv(), { header: false, names: ['year', 'rate'] })\n .filter(d => d.year != null || d.rate != null)\n .derive({\n state: d => op.fill_down(op.match(d.year, /Reported crime in (.*)/, 1)) // <- extract state name\n }, { before: 0 })\n .filter(d => d.rate != null) // <-- or, we could delete when year column starts with \"Reported crime in\"\n .view(100)","pinned":true,"hidden":false,"mode":"ojs"},{"id":745,"value":"md`Now the dataset is tidy and ready for downstream use!`","pinned":false,"hidden":false,"mode":"ojs"},{"id":16,"value":"md`

\n## Appendix\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":67,"value":"import {vl} from '@vega/vega-lite-api'","pinned":false,"hidden":false,"mode":"ojs"},{"id":7,"value":"import {aq, op} from '@uwdata/arquero'","pinned":false,"hidden":false,"mode":"ojs"},{"id":10,"value":"table1 = aq.table({\n country: ['Afghanistan', 'Afghanistan', 'Brazil', 'Brazil', 'China', 'China'],\n year: [1999, 2000, 1999, 2000, 1999, 2000],\n cases: [745, 2666, 37737, 80488, 212258, 213766],\n population: [19987071, 20595360, 172006362, 174504898, 1272915272, 1280428583]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":19,"value":"table2 = table1\n .fold(['cases', 'population'], { as: ['type', 'count'] })","pinned":false,"hidden":false,"mode":"ojs"},{"id":26,"value":"table3 = table1\n .derive({ rate: d => `${d.cases}/${d.population}` })\n .select(aq.not('cases', 'population'))","pinned":false,"hidden":false,"mode":"ojs"},{"id":31,"value":"table4a = table1\n .groupby('country')\n .pivot('year', 'cases')","pinned":false,"hidden":false,"mode":"ojs"},{"id":56,"value":"table4b = table1\n .groupby('country')\n .pivot('year', 'population')","pinned":false,"hidden":false,"mode":"ojs"},{"id":193,"value":"who = aq\n .fromCSV(await FileAttachment(\"who.csv\").text())\n .select(aq.not('index'))","pinned":false,"hidden":false,"mode":"ojs"},{"id":645,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W33","stdlib":"1","titling":"auto"}