30:[] 2e:{"title":"Data Jiu-Jitsu (data wrangling) with Javascript or how to perform data science in a web browser","cells":[{"id":8,"value":"md `# Data Jiu-Jitsu (data wrangling) with Javascript or how to perform data science in a web browser\nI always wanted to be able to code from my ipad or from any browsers. By using the power of Javascript and the Observable notebooks it is now possible. Here I show the most common data wrangling operations that any data scientist needs to do all along the day. Most of the content used here is comming from the original [learn JS Data website](http://learnjsdata.com) that has been ported to [Observable](https://observablehq.com/@berkeleyvis/learn-js-data?collection=@berkeleyvis/learn-js-data).`","pinned":false,"hidden":false,"mode":"ojs"},{"id":377,"value":"## Loading D3 library","pinned":true,"hidden":false,"mode":"md"},{"id":77,"value":"d3 = require(\"d3@6\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":380,"value":"## Loading Lodash library","pinned":true,"hidden":false,"mode":"md"},{"id":85,"value":"_ = require(\"lodash@4\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":383,"value":"## Loading the Apache Arrow library\n\nFor more information see \n- [An Apache Arrow introduction](https://observablehq.com/@theneuralbit/introduction-to-apache-arrow)\n- [Manipulating Flat Arrays Arrow Style](https://observablehq.com/@lmeyerov/manipulating-flat-arrays-arrow-style)\n- [Rich data types in Apache Arrow](https://observablehq.com/@lmeyerov/rich-data-types-in-apache-arrow-js-efficient-data-tables-wit)","pinned":true,"hidden":false,"mode":"md"},{"id":275,"value":"arrow = require('apache-arrow@0.15.1')","pinned":false,"hidden":false,"mode":"ojs"},{"id":234,"value":"import {vl} from \"@vega/vega-lite-api\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":373,"value":"## Loading the Danfo library\nMore information on the Danfo library can be found [here](https://observablehq.com/@visnup/hello-danfo-js)","pinned":true,"hidden":false,"mode":"md"},{"id":360,"value":"dfd = require('danfojs@0.0.15/dist/index.min.js').catch(() => {\n window.dfd.Series.prototype.print = window.dfd.DataFrame.prototype.print = function() { return print(this) };\n return window.dfd;\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":362,"value":"function print(df) {\n const {col_types, series, columns, index, values} = df;\n const table = html`\n

\n \n \n \n \n ${series\n ? html``\n : columns.map((name, i) => html``)}\n \n \n \n ${values.map((row, i) => html`\n \n \n ${series\n ? html``\n : row.map((v, j) => html``)}\n \n `)}\n \n

	${columns}	${name}
${index[i]}	${row}	${v}

\n \n `;\n table.value = df;\n return table;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":364,"value":"function div(fn) {\n const d = html`

`;\n fn(d);\n return d;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":89,"value":"md `# Reading in Data\n## from a single internet csv file`","pinned":false,"hidden":false,"mode":"ojs"},{"id":6,"value":"data = d3.csv(\"https://cdn.rawgit.com/vlandham/js_data/master/data/cities.csv\", d3.autoType)","pinned":false,"hidden":false,"mode":"ojs"},{"id":368,"value":"df = dfd.read_csv(\"https://cdn.rawgit.com/vlandham/js_data/master/data/cities.csv\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":371,"value":"df.shape","pinned":true,"hidden":false,"mode":"ojs"},{"id":29,"value":"md `The autoType optional parameter try to guess the type of each column. It is also possible to explicitelly reformat the columns by loading the data. Of course here you need to know the column names.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":30,"value":"d3.csv(\"https://cdn.rawgit.com/vlandham/js_data/master/data/cities.csv\", d => ({\n city: d.city,\n state: d.state,\n population: +d.population,\n land_area: +d[\"land area\"]\n}))","pinned":false,"hidden":false,"mode":"ojs"},{"id":103,"value":"md `## from a Wikipedia table\nSee the original [notebook](https://observablehq.com/@mbostock/working-with-wikipedia-data) of [Mike Postock](http://bost.ocks.org/mike)\nhttps://en.wikipedia.org/wiki/List_of_largest_companies_in_the_United_States_by_revenue`","pinned":false,"hidden":false,"mode":"ojs"},{"id":106,"value":"text = fetch(`https://en.wikipedia.org/w/api.php?format=json&origin=*&action=parse&prop=text&page=List_of_largest_companies_in_the_United_States_by_revenue§ion=1`).then(response => response.json())","pinned":false,"hidden":false,"mode":"ojs"},{"id":111,"value":"text.parse.text[\"*\"]","pinned":false,"hidden":false,"mode":"ojs"},{"id":114,"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":false,"hidden":false,"mode":"ojs"},{"id":119,"value":"column_names_as_string = table.querySelector(\"tr:first-child\").textContent.trim()","pinned":false,"hidden":false,"mode":"ojs"},{"id":281,"value":"function string2Array( str ) {\n const one_line_string = str.replace( /[\\r\\n]+/gm, \"#\" );\n const array_of_strings = one_line_string.split(\"#\");\n return array_of_strings; \n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":179,"value":"column_names = string2Array(column_names_as_string)","pinned":true,"hidden":false,"mode":"ojs"},{"id":126,"value":"rows = table.querySelectorAll(\"tr:not(:first-child)\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":238,"value":"// Function to render a table\nrender_data_table = options => html`\n \n ${Object.keys(options[0]).map((d, i) => {\n let label = d;\n return ``;\n })}\n \n \n \n ${options.map(row => {\n return html`${Object.values(row).map(col => {\n return ``;\n })}`;\n })}\n \n

${label}
${col}

\n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":128,"value":"ranks = Array.from(rows, r => r.querySelector(\"td:nth-child(1)\").textContent.trim())","pinned":true,"hidden":false,"mode":"ojs"},{"id":156,"value":"// Rank\tName\tIndustry\tRevenue\n// (USD millions)\tRevenue growth\tEmployees\tHeadquarters\ncompany_names = Array.from(rows, r => r.querySelector(\"td:nth-child(2)\").textContent.trim())","pinned":true,"hidden":false,"mode":"ojs"},{"id":130,"value":"industries = Array.from(rows, r => r.querySelector(\"td:nth-child(3)\").textContent.trim())","pinned":true,"hidden":false,"mode":"ojs"},{"id":140,"value":"revenues = Array.from(rows, r => +r.querySelector(\"td:nth-child(4)\").textContent.trim().replace(/,/g, \"\"))","pinned":true,"hidden":false,"mode":"ojs"},{"id":163,"value":"revenue_growth = Array.from(rows, r => r.querySelector(\"td:nth-child(5)\").textContent.trim())","pinned":true,"hidden":false,"mode":"ojs"},{"id":165,"value":"employees = Array.from(rows, r => +r.querySelector(\"td:nth-child(6)\").textContent.trim().replace(/,/g, \"\"))","pinned":true,"hidden":false,"mode":"ojs"},{"id":161,"value":"headquarters = Array.from(rows, r => r.querySelector(\"td:nth-child(7)\").textContent.trim())","pinned":true,"hidden":false,"mode":"ojs"},{"id":170,"value":"data_without_header = d3.zip(ranks, company_names, industries, revenues, revenue_growth, employees, headquarters)","pinned":true,"hidden":false,"mode":"ojs"},{"id":247,"value":"data_without_header","pinned":true,"hidden":false,"mode":"ojs"},{"id":263,"value":"headers = new Array()","pinned":true,"hidden":false,"mode":"ojs"},{"id":269,"value":"headers[0] = column_names","pinned":true,"hidden":false,"mode":"ojs"},{"id":273,"value":"final_data = _.merge(headers, data_without_header)","pinned":true,"hidden":false,"mode":"ojs"},{"id":243,"value":"// Display a table showing a subset of the data\nrender_data_table(final_data)","pinned":true,"hidden":false,"mode":"ojs"},{"id":231,"value":"vl.markBar()\n .data({values: d3.zip(company_names, revenues)})\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":false,"hidden":false,"mode":"ojs"},{"id":290,"value":"run = (query) => fetch('https://query.wikidata.org/sparql?query=' + encodeURIComponent(query), {\n headers: {\n 'accept': 'application/sparql-results+json'\n }\n}).then(res => res.json())","pinned":false,"hidden":false,"mode":"ojs"},{"id":292,"value":"wikidata_queries = ({ \n query_metabolites:{\n description:\"\",\n type: \"sparql\",\n query: `SELECT ?compound ?compoundLabel ?speciesLabel ?sourceLabel ?doi ?wpid WHERE {\n ?compound wdt:P31 wd:Q11173.\n MINUS { ?compound wdt:P31 wd:Q8054. }\n ?compound p:P703 ?statement.\n ?statement rdf:type wikibase:BestRank.\n ?statement ps:P703 ?species.\n OPTIONAL {\n ?statement (prov:wasDerivedFrom/pr:P248) ?source.\n OPTIONAL { ?source wdt:P2410 ?wpid. }\n OPTIONAL { ?source wdt:P356 ?doi. }\n }\n SERVICE wikibase:label { bd:serviceParam wikibase:language \"en\". }\n}\nORDER BY ASC(?compound)\n`},\n query_montagnes: {\n description: \"\",\n type: \"sparql\",\n query: `#Les plus hautes montagnes de l’univers\n#Mountains over 8000 elevation\nSELECT ?subj ?label ?coord ?elev\nWHERE\n{\n ?subj wdt:P2044 ?elev filter(?elev > 2000) .\n ?subj wdt:P625 ?coord .\n SERVICE wikibase:label { bd:serviceParam wikibase:language \"en,zh\" . ?subj rdfs:label ?label }\n}`}\n}) ","pinned":false,"hidden":false,"mode":"ojs"},{"id":294,"value":"results = run(wikidata_queries.query_montagnes.query)","pinned":false,"hidden":false,"mode":"ojs"},{"id":299,"value":"results.results.bindings[0].label.value","pinned":false,"hidden":false,"mode":"ojs"},{"id":116,"value":"md `## Loading multiple files`","pinned":false,"hidden":false,"mode":"ojs"},{"id":20,"value":"Promise.all(\n[\n d3.csv(\"https://cdn.rawgit.com/vlandham/js_data/master/data/cities.csv\"), d3.tsv(\"https://cdn.rawgit.com/vlandham/js_data/master/data/animals.tsv\")\n]).then(\n function success(allData) {\n const cities = allData[0],\n animals = allData[1];\n return [cities[0], animals[0]];\n },\n function error(e) {\n throw e;\n }\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":33,"value":"md `for more information see [this notebook](https://observablehq.com/@berkeleyvis/reading-in-data-learn-js-data)`","pinned":false,"hidden":false,"mode":"ojs"},{"id":36,"value":"md `## Combining data sets\n### Lookup table or left outer join`","pinned":false,"hidden":false,"mode":"ojs"},{"id":37,"value":"articles = [\n {\n id: 1,\n name: \"vacuum cleaner\",\n weight: 9.9,\n price: 89.9,\n brand_id: 2\n },\n {\n id: 2,\n name: \"washing machine\",\n weight: 540,\n price: 230,\n brand_id: 1\n },\n {\n id: 3,\n name: \"hair dryer\",\n weight: 1.2,\n price: 24.99,\n brand_id: 2\n },\n {\n id: 4,\n name: \"super fast laptop\",\n weight: 400,\n price: 899.9,\n brand_id: 3\n }\n]","pinned":false,"hidden":false,"mode":"ojs"},{"id":40,"value":"brands = [\n {\n id: 1,\n name: \"SuperKitchen\"\n },\n {\n id: 2,\n name: \"HomeSweetHome\"\n }\n]","pinned":true,"hidden":false,"mode":"ojs"},{"id":42,"value":"function join(lookupTable, mainTable, lookupKey, mainKey, select) {\n const l = lookupTable.length,\n m = mainTable.length,\n output = [],\n // create an index for lookup table\n lookupIndex = new Map(lookupTable.map(d => [d[lookupKey], d]));\n\n return mainTable.map(d => select(d, lookupIndex.get(d[mainKey])));\n return output;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":46,"value":"md`Because above defined function creates an index for the \\`lookupTable\\` (in our case \\`brands\\`) in the first iteration, it runs fast. Also, via a callback, it allows us to directly define which keys (or \"attributes\") we want to retain in the resulting, joined array (\\`output\\`). It is used like so:\n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":44,"value":"join(brands, articles, \"id\", \"brand_id\", (article, brand) => ({\n id: article.id,\n name: article.name,\n weight: article.weight,\n price: article.price,\n brand: brand !== undefined ? brand.name : null\n}))","pinned":true,"hidden":false,"mode":"ojs"},{"id":49,"value":"md `### Add toghether rows from different data sets\nThe next common operation is the concatenation or merging of datasets. \n#### d3.merge\nWe can simply use the Promises implementation of D3 followed by the d3.merge function:`","pinned":true,"hidden":false,"mode":"ojs"},{"id":51,"value":"bigData_1 = Promise.all([\n d3.csv(\"https://cdn.rawgit.com/vlandham/js_data/master/data/big_data_1.csv\"),\n d3.csv(\"https://cdn.rawgit.com/vlandham/js_data/master/data/big_data_2.csv\"),\n d3.csv(\"https://cdn.rawgit.com/vlandham/js_data/master/data/big_data_3.csv\")\n]).then(big_datas => d3.merge(big_datas))","pinned":true,"hidden":false,"mode":"ojs"},{"id":71,"value":"dataset_1 = [\n {\n id: 1,\n name: \"SuperKitchen\"\n },\n {\n id: 2,\n name: \"HomeSweetHome\"\n }\n]","pinned":true,"hidden":false,"mode":"ojs"},{"id":74,"value":"dataset_2 = [\n {\n zip: 18793873,\n lastname: \"Logean\"\n },\n {\n zip: 2893973,\n lastname: \"Rion\"\n }\n]","pinned":true,"hidden":false,"mode":"ojs"},{"id":69,"value":"bigData_2 = _.merge(dataset_1, dataset_2)","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W30","stdlib":"1","titling":"auto"}