3e:null 3f:["$","title",null,{"children":"Data wrangling essentials: comparisons in JavaScript, Python, SQL, R tidyverse, R data.table, and Excel | Sharon Machlis | Observable"}] 45:T453,salesData = [ { //id: 1, date: "2022-01-01", product: "Product A", description: "Shirt - small", //category: "Category 1", unitsSold: 10, unitPrice: 50, totalRevenue: 500, region: "North" }, { //id: 2, date: "2022-01-02", product: "Product B", description: "Sweatshirt - large", //category: "Category 2", unitsSold: 15, unitPrice: 25, totalRevenue: 375, region: "South" }, { //id: 3, date: "2022-01-03", product: "Product C", description: "Jacket - small", //category: "Category 1", unitsSold: 8, unitPrice: 75, totalRevenue: 600, region: "West" }, { //id: 4, date: "2022-01-04", product: "Product A", description: "Shirt - medium", //category: "Category 2", unitsSold: 5, unitPrice: 100, totalRevenue: 500, region: "East" }, { //id: 5, date: "2022-01-05", product: "Product B", description: "Sweatshirt - large", //category: "Category 1", unitsSold: 20, unitPrice: 30, totalRevenue: 600, region: "North" }]46:T31fc,compareFunctions = [ { method: "Filter rows", language: "JavaScript", function: "Array filter", link: "https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/filter", example: '`salesData.filter(d => d.region === "West")`' }, { method: "Filter rows", language: "Python", function: "pandas query", link: "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.query.html", example: "`salesData.query('region==\"West\"')`" }, { method: "Filter rows", language: "SQL", function: "WHERE", link: "https://www.w3schools.com/sql/sql_where.asp", example: "`SELECT * FROM salesData WHERE region=\'West\'`" }, {method: "Filter rows", language: "R tidyverse", function: "dplyr filter", link: "https://dplyr.tidyverse.org/reference/filter.html", example: "`salesData` |`>` `filter(region == \"West\")`" }, {method: "Filter rows", language: "R data.table", function: "first position within DT bracket", link: "https://rdatatable.gitlab.io/data.table/articles/datatable-intro.html#subset-i-1c", example: "`salesData[region == \"West\"]`" }, { method: "Filter rows", language: 'Excel', function: 'FILTER', link: "https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759", example: '`=FILTER(A2:H12, H2:H12="West")`' }, { method: "Select columns", language: "JavaScript", function: "Array map", link: "https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/map", example: "`salesData.map(d => ({ date: d.date, product: d.product, totalRevenue: d.totalRevenue }))`" }, { method: "Select columns", language: "Python", function: "brackets [[]]", link: "https://pandas.pydata.org/docs/getting_started/intro_tutorials/03_subset_data.html", example: "`salesData[['date', 'product', 'totalRevenue']]`" }, { method: "Select columns", language: "SQL", function: "SELECT", link: "https://www.w3schools.com/sql/sql_select.asp", example: " `SELECT date, product, totalRevenue FROM salesData`" }, { method: "Select columns", language: "R tidyverse", function: "dplyr select", link: "https://dplyr.tidyverse.org/reference/select.html", example: "`salesData` |`>` `select(date, product, totalRevenue)`" }, { method: "Select columns", language: "R data.table", function: "", link: "", example: "`salesData[, .(date, product, totalRevenue)]`" }, { method: "Select columns", language: 'Excel', function: 'CHOOSECOLS', link: 'https://support.microsoft.com/en-gb/office/choosecols-function-bf117976-2722-4466-9b9a-1c01ed9aebff', example: '`=CHOOSECOLS(A1:H12, 1, 2, 6)` _(date, product and totalRevenue are columns 1, 2 and 6 in range)_' }, { method: "Group by variable", language: 'JavaScript', function: 'd3.rollup', link: 'https://github.com/d3/d3-array/blob/main/README.md#rollup', example: '`d3.rollup(salesData, v => d3.sum(v, d => d.totalRevenue), d => d.region)`' }, { method: "Group by variable", language: 'Python', function: 'pandas groupby', link: 'https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.groupby.html', example: '`salesData.groupby(\'region\')[\'totalRevenue\'].sum()`' }, { method: "Group by variable", language: 'SQL', function: 'GROUP BY', link: 'https://www.w3schools.com/sql/sql_groupby.asp', example: '`SELECT region, SUM(totalRevenue) AS regionRevenue FROM salesData GROUP BY region`' }, { method: "Group by variable", language: 'R tidyverse', function: 'dplyr group_by', link: 'https://dplyr.tidyverse.org/reference/group_by.html', example: '`salesData` |`>` `group_by(region)` |`>` `summarize(regionRevenue = sum(totalRevenue))`' }, { method: "Group by variable", language: 'R data.table', function: '', link: '', example: '`salesData[, regionRevenue = sum(totalRevenue), by = region]`' }, { method: "Group by variable", language: 'Excel', function: 'pivot table', link: 'https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576', example: '_(Insert > PivotTable)_' }, { method: "Add a column", language: 'JavaScript', function: 'Array map', link: 'https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/map', example: '`salesData.map(d => ({...d, discountedPrice: 0.9 * d.unitPrice }))`' }, { method: "Add a column", language: 'Python', function: 'brackets []', link: 'https://pandas.pydata.org/docs/getting_started/intro_tutorials/05_add_columns.html', example: '`salesData[\'discountedPrice\'] = 0.9 * salesData[\'unitPrice\']`' }, { method: "Add a column", language: 'SQL', function: 'AS', link: 'https://www.w3schools.com/sql/sql_ref_as.asp', example: '`SELECT *, unitPrice * .9 AS discountedPrice FROM salesData`' }, { method: "Add a column", language: 'R tidyverse', function: 'dplyr mutate', link: 'https://dplyr.tidyverse.org/reference/mutate.html', example: '`salesData` |`>` `mutate(discountedPrice = 0.9 * unitPrice)`' }, { method: "Add a column", language: 'Excel', function: 'add in spreadsheet', link: 'https://support.microsoft.com/en-us/office/use-calculated-columns-in-an-excel-table-873fbac6-7110-4300-8f6f-aafa2ea11ce8', example: '`= F2 * 0.9`' }, { method: "Sort data", language: 'JavaScript', function: 'Array sort', link: 'https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/sort', example: '`salesData.sort((a, b) => a.totalRevenue - b.totalRevenue)`' }, { method: "Sort data", language: 'Python', function: 'pandas sort_values', link: 'https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.sort_values.html', example: '`salesData.sort_values(by=[\'totalRevenue\'])`' }, { method: "Sort data", language: 'SQL', function: 'ORDER BY', link: 'https://www.w3schools.com/sql/sql_orderby.asp', example: '`SELECT * FROM salesData ORDER BY totalRevenue`' }, { method: "Sort data", language: 'R tidyverse', function: 'dplyr arrange', link: 'https://dplyr.tidyverse.org/reference/arrange.html', example: '`salesData` |`>` `arrange(totalRevenue)`' }, { method: "Sort data", language: 'R data.table', function: 'setorder', link: 'https://rdatatable.gitlab.io/data.table/reference/setorder.html', example: '`setorder(salesData, totalRevenue)`' }, { method: "Sort data", language: 'Excel', function: 'SORT', link: 'https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c', example: '`=SORT(A2:H12, 6)` _(totalRevenue is column 6 in range)_' }, { method: "Rename a column", language: 'JavaScript', function: 'Array map', link: 'https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/map', example: '`salesData.map(d => ({...d, saleDate: d.date }))`' }, { method: "Rename a column", language: 'Python', function: 'pandas rename', link: 'https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.rename.html', example: '`salesData.rename(columns={"date": "saleDate"})`' }, { method: "Rename a column", language: 'SQL', function: 'SELECT AS', link: 'https://www.w3schools.com/sql/sql_ref_as.asp', example: '`SELECT *, date AS saleDate FROM salesData`' }, { method: "Rename a column", language: 'R tidyverse', function: 'dplyr rename', link: 'https://dplyr.tidyverse.org/reference/rename.html', example: '`salesData` |`>` `rename(saleDate = date)`' }, { method: "Rename a column", language: 'R data.table', function: 'setnames', link: 'https://rdatatable.gitlab.io/data.table/reference/setattr.html', example: '`setnames(salesData, "date", "saleDate")`' }, { method: "Rename a column", language: 'Excel', function: 'update manually', link: 'https://support.microsoft.com/en-us/office/edit-cell-contents-9c8489f6-08c4-4932-a6bc-4de0a8cf86d6', example: '_(click in cell, type new name)_' }, { method: "Access a value", language: 'JavaScript', function: 'Array map', link: 'https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array', example: '`salesData.map(d => d.description)[3]`' }, { method: "Access a value", language: 'Python', function: 'loc, iloc', link: 'https://pandas.pydata.org/docs/user_guide/indexing.html', example: '`salesData.iloc[[3], [2]]`' }, { method: "Access a value", language: 'SQL', function: 'SELECT + LIMIT + OFFSET', link: 'https://www.w3schools.com/php/php_mysql_select_limit.asp', example: '`SELECT description FROM salesData LIMIT 1 OFFSET 3`' }, { method: "Access a value", language: 'R tidyverse', function: '$ []', link: 'http://www.cookbook-r.com/Basics/Indexing_into_a_data_structure/', example: '`salesData$description[4]`' }, { method: "Access a value", language: 'R data.table', function: '$ []', link: 'http://www.cookbook-r.com/Basics/Indexing_into_a_data_structure/', example: '`salesData$description[4]`' }, { method: "Access a value", language: 'Excel', function: 'A1 notation', link: 'https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/refer-to-cells-and-ranges-by-using-a1-notation', example: '`= C4`' }, { method: "Merge columns", language: 'JavaScript', function: 'Array map', link: 'https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/map', example: '`salesData.map(d => ({...d, fullDescription:` `` `${d.product} ${d.description}` ``}))`' }, { method: "Merge columns", language: 'Python', function: '+ (concatenation)', link: 'https://www.w3schools.com/python/gloss_python_string_concatenation.asp', example: '`salesData[\'fullDescription\'] = salesData\[\'product\'\] + \' \' + salesData\[\'description\'\]`' }, { method: "Merge columns", language: 'SQL', function: 'CONCAT', link: 'https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql?view=sql-server-ver16', example: '`SELECT *, CONCAT(product, \' \', description) AS fullDescription from salesData`' }, { method: "Merge columns", language: 'R tidyverse', function: 'tidyr unite', link: 'https://tidyr.tidyverse.org/reference/unite.html', example: '`salesData` |`>` `unite("fullDescription", c(product, description), sep = " ")`' }, { method: "Merge columns", language: 'Excel', function: '& or CONCAT', link: 'https://support.microsoft.com/en-us/office/combine-text-from-two-or-more-cells-into-one-cell-81ba0946-ce78-42ed-b3c3-21340eb164a6', example: '`=CONCAT(B2," ", C2)`' }, { method: "Join", language: 'JavaScript', function: 'Arquero join_left', link: 'https://uwdata.github.io/arquero/api/verbs#join_left', example: '`salesData.join_left(productDetails, [\'product\', \'product_id\'])` _(if salesData is an Arquero table)_' }, { method: "Join", language: 'Python', function: 'pandas merge', link: 'https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.merge.html', example: '`salesData.merge(productDetails, left_on=\'product\', right_on=\'product_id\', how=\'left\')`' }, { method: "Join", language: 'SQL', function: 'LEFT JOIN', link: 'https://www.w3schools.com/sql/sql_join.asp', example: '`SELECT * FROM salesData JOIN productDetails ON salesData.product = productDetails.product_id`' }, { method: "Join", language: 'R tidyverse', function: 'dplyr left_join', link: 'https://dplyr.tidyverse.org/reference/mutate-joins.html', example: '`salesData` |`>` `left_join(productDetails, by = c("product" = "product_id"))`' }, { method: "Join", language: 'Excel', function: 'Power Query merge', link: 'https://learn.microsoft.com/en-us/power-query/merge-queries-left-outer', example: '*(merge query)*' } ]47:Ta86,async function imageInput({ value = ["JavaScript", "Python", "SQL", "R tidyverse", "R data.table", "Excel"] } = {}) { const output = htl.html``; const node = htl.html`

Click on the icons to include or exclude languages in the tables below:

set(node, "JavaScript")} />

set(node, "Python")} />

set(node, "SQL")} />

set(node, "R tidyverse")} />

set(node, "R data.table")} />

set(node, "Excel")} />

`; // Update the display whenever the value changes Object.defineProperty(node, "value", { get() { return value; }, set(v) { if (!Array.isArray(v)) { const index = value.indexOf(v); if (index > -1) { // only splice array when item is found value.splice(index, 1); // 2nd parameter means remove one item only } else { value.push(v); } } const images = d3.select(node).selectAll("img"); images .style("border-color", "#f5f5f5") .filter(function () { return value.includes(d3.select(this).attr("value")); }) .style("border-color", "#b3b3b3") output.value = value; } }); // Set the initial value node.value = value; return node; }48:[{"id":"ffa56fdf09bdfa5ac11dc30f0e46d87a46fc5e827cb6004e1598c0862733d852b6f5b12dea6f5ce855e8902adaba6f4a9eb80aea0a2888b3c84d5ea4f2f9e0eb","href":"https://static.observableusercontent.com/files/ffa56fdf09bdfa5ac11dc30f0e46d87a46fc5e827cb6004e1598c0862733d852b6f5b12dea6f5ce855e8902adaba6f4a9eb80aea0a2888b3c84d5ea4f2f9e0eb","name":"select-sm.png","type":"image/png","lastModified":1679671514113.07,"size":74295},{"id":"8c396bef6878392f7a13a2da9228920f62cfb748faa80c36d0ac30082a45fb37ebe6be74af62ce88ea4e21a6ccff8bc0b2b0f2b3b98bdfd814f5acd5f9b0a2ae","href":"https://static.observableusercontent.com/files/8c396bef6878392f7a13a2da9228920f62cfb748faa80c36d0ac30082a45fb37ebe6be74af62ce88ea4e21a6ccff8bc0b2b0f2b3b98bdfd814f5acd5f9b0a2ae","name":"productDetails.csv","type":"text/csv","lastModified":1679671514113.07,"size":146},{"id":"e7882054b8abe71b752a8cc656f520eeddab440c58c5fd1d615083805f48b90138d5c9e8da7f81d06cf844c634332f05cb3b3797d33688eaff3c8c7aad2f0c71","href":"https://static.observableusercontent.com/files/e7882054b8abe71b752a8cc656f520eeddab440c58c5fd1d615083805f48b90138d5c9e8da7f81d06cf844c634332f05cb3b3797d33688eaff3c8c7aad2f0c71","name":"add-column-sm.png","type":"image/png","lastModified":1679671514113.07,"size":43208},{"id":"b2821493e7a8ea3e1908a65a68b98f00a6afb909e0a4f393f91a04b9ccd77cdbaa39085b0f8e1e6944dcc2a2c7b5dbe4fb24112198fab98a661ad55995e2a2c1","href":"https://static.observableusercontent.com/files/b2821493e7a8ea3e1908a65a68b98f00a6afb909e0a4f393f91a04b9ccd77cdbaa39085b0f8e1e6944dcc2a2c7b5dbe4fb24112198fab98a661ad55995e2a2c1","name":"filter-sml.png","type":"image/png","lastModified":1679671514113.07,"size":97679},{"id":"c207bb44f3c97fc7d2a7172457d72f6de6cc257e301e45484b1cdd256c778c1700e4bbac3bf214f723f474c4990993964294d45cf52030edac2ade8fc1de005a","href":"https://static.observableusercontent.com/files/c207bb44f3c97fc7d2a7172457d72f6de6cc257e301e45484b1cdd256c778c1700e4bbac3bf214f723f474c4990993964294d45cf52030edac2ade8fc1de005a","name":"rename-sm.png","type":"image/png","lastModified":1679671514113.07,"size":38408},{"id":"10574ba2e98e513d332410b15ab276e93ba450a6b1ac159bf1c78fdf7afde68e5ad1aa1cce65d2ec05c670d4012cf7574905099bb1cb0db0f67df136b52b569a","href":"https://static.observableusercontent.com/files/10574ba2e98e513d332410b15ab276e93ba450a6b1ac159bf1c78fdf7afde68e5ad1aa1cce65d2ec05c670d4012cf7574905099bb1cb0db0f67df136b52b569a","name":"index-array.png","type":"image/png","lastModified":1679671514113.07,"size":62412},{"id":"ac8920d21a4ff6c0604d637969f770500a5aa22235ab3509693cb230f604b7c504c48ec268bbaae3a85c1a6ee0ba476a938154b85886a741a31b21e139e92886","href":"https://static.observableusercontent.com/files/ac8920d21a4ff6c0604d637969f770500a5aa22235ab3509693cb230f604b7c504c48ec268bbaae3a85c1a6ee0ba476a938154b85886a741a31b21e139e92886","name":"group-summarize-sm.png","type":"image/png","lastModified":1679671514113.07,"size":65109},{"id":"41f020acd283c788cafd6b0d0e5670aa2120003babece1ac460dc73270f2effcc6d1f607fe965a49b0b28e1b834c2682d4f5bf3b9ddf675af54c38a5c1cffb8c","href":"https://static.observableusercontent.com/files/41f020acd283c788cafd6b0d0e5670aa2120003babece1ac460dc73270f2effcc6d1f607fe965a49b0b28e1b834c2682d4f5bf3b9ddf675af54c38a5c1cffb8c","name":"join-sm.png","type":"image/png","lastModified":1679671514113.07,"size":49401},{"id":"bdedbb2fe3fe8dedd0fec7415b00e3317647265c8ab02e497a1b48ad5498f1432071beb947f5bb69eb04d751eb12aa504282b1485f1c2909aff4936d4d59f405","href":"https://static.observableusercontent.com/files/bdedbb2fe3fe8dedd0fec7415b00e3317647265c8ab02e497a1b48ad5498f1432071beb947f5bb69eb04d751eb12aa504282b1485f1c2909aff4936d4d59f405","name":"btns-js.png","type":"image/png","lastModified":1679671514113.07,"size":69840},{"id":"09d55a7397d614dece1ef27e2b9bde778a74a6145de30dd47794c5eac6c7c3d8b0a06b2318481a793926724b8dcedb250c8b6c7e32842798f8e73913cc74b087","href":"https://static.observableusercontent.com/files/09d55a7397d614dece1ef27e2b9bde778a74a6145de30dd47794c5eac6c7c3d8b0a06b2318481a793926724b8dcedb250c8b6c7e32842798f8e73913cc74b087","name":"sort-sm.png","type":"image/png","lastModified":1679671514113.07,"size":37830},{"id":"17a37fdcae4047d41e1161db268aac1a2aef284bc7a09109519d8a545874559bde8572dda6f0a3c6eef1802741d34ca7941b5667967257dc82870aee61782b63","href":"https://static.observableusercontent.com/files/17a37fdcae4047d41e1161db268aac1a2aef284bc7a09109519d8a545874559bde8572dda6f0a3c6eef1802741d34ca7941b5667967257dc82870aee61782b63","name":"unite-sm.png","type":"image/png","lastModified":1679671514113.07,"size":107866},{"id":"2c0b3cf2dea5035fc8d563d5a6fbdc9bc2853ae051e7a4228e317fccf489ab0a3a4e41768e37326f188303b139b5cb1ffcf49bce5751586b26eb4467a2571f53","href":"https://static.observableusercontent.com/files/2c0b3cf2dea5035fc8d563d5a6fbdc9bc2853ae051e7a4228e317fccf489ab0a3a4e41768e37326f188303b139b5cb1ffcf49bce5751586b26eb4467a2571f53","name":"btns-excel.png","type":"image/png","lastModified":1679671514113.07,"size":62427},{"id":"ed71c7174d5d34cac58b3f10d462c70f01e687f4bbd62057cd3d54b2d8b931bfe8785da44a562e8855f1db71fc96b09ae4b5e1136230f81fc273e772b7f2cbd9","href":"https://static.observableusercontent.com/files/ed71c7174d5d34cac58b3f10d462c70f01e687f4bbd62057cd3d54b2d8b931bfe8785da44a562e8855f1db71fc96b09ae4b5e1136230f81fc273e772b7f2cbd9","name":"btns-py.png","type":"image/png","lastModified":1679671514113.07,"size":76195},{"id":"990bb004b5a673cd9affcc207a965272128fccbdca9fd5eb3ac83496dcbfe2f528a2f4d71c6dc311e93c057ff34e39ca1c1160d455bc40419ae8643d2ca45287","href":"https://static.observableusercontent.com/files/990bb004b5a673cd9affcc207a965272128fccbdca9fd5eb3ac83496dcbfe2f528a2f4d71c6dc311e93c057ff34e39ca1c1160d455bc40419ae8643d2ca45287","name":"btns-sql.png","type":"image/png","lastModified":1679671514113.07,"size":73037},{"id":"2c258761b17a3f8225f2e03a4460cf8d4135a92d09c852c10e4a5386913e32c1293069a5cef640128561bd3395d9d81e4810961e9da6dd6ddfe0c55a80be9498","href":"https://static.observableusercontent.com/files/2c258761b17a3f8225f2e03a4460cf8d4135a92d09c852c10e4a5386913e32c1293069a5cef640128561bd3395d9d81e4810961e9da6dd6ddfe0c55a80be9498","name":"btns-r.png","type":"image/png","lastModified":1679671514113.07,"size":70506}] 43:{"id":"25359c46284d292f","slug":null,"title":"Data wrangling essentials: comparisons in JavaScript, Python, SQL, R tidyverse, R data.table, and Excel","thumbnail":"1526b995d8c130a1792cac797e3f72d4194131efbffabe81f8021ed2fe4336b4","access_level":"public","create_time":"$D2023-03-24T15:25:14.113Z","update_time":"$D2023-03-24T16:19:30.005Z","view_time":null,"first_public_time":"$D2023-03-24T16:22:12.769Z","has_unpublished_edits":null,"fork":{"id":"a8bb05dc7a0ccac9","slug":"data-wrangling-translations","title":"Data wrangling essentials: comparisons in JavaScript, Python, SQL, R, and Excel","owner":{"id":"f35c755083683fe5","login":"observablehq","name":"Observable"}},"stars":2,"starred":false,"owner":{"type":"team","id":"e6626e0c554e7cd9","login":"smach","name":"Sharon Machlis","avatar_url":"https://avatars-next.observableusercontent.com/9c8b2ebc3314083debee9a2f5877cc4e485b983fe06b765f08075c4d7e67a4d4","tier":"starter_2024","bio":"Director, Editorial Data & Analytics at Foundry (formerly IDG Communications). Somewhat R-obsessed. Find me on Mastodon @smach@fosstodon.org","following":false},"version":2289,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"783027c963c28368","login":"smach","name":"Sharon Machlis","avatar_url":"https://avatars-next.observableusercontent.com/9c8b2ebc3314083debee9a2f5877cc4e485b983fe06b765f08075c4d7e67a4d4","tier":null,"bio":"Director, Editorial Data & Analytics at Foundry (formerly IDG Communications). Somewhat R-obsessed. Find me on Mastodon @smach@fosstodon.org","following":false}],"members":[{"type":"individual","id":"783027c963c28368","login":"smach","name":"Sharon Machlis","avatar_url":"https://avatars-next.observableusercontent.com/9c8b2ebc3314083debee9a2f5877cc4e485b983fe06b765f08075c4d7e67a4d4","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"titling":"auto","title":"Data wrangling essentials: comparisons in JavaScript, Python, SQL, R tidyverse, R data.table, and Excel","cells":[{"id":0,"value":"# Data wrangling essentials: comparisons in JavaScript, Python, SQL, R tidyverse, R data.table, and Excel","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":199,"value":"I hope to add data.table code to this notebook! Just starting.\n\nWhen adding JavaScript to your data work, it can be useful to see how it compares with other languages you’ve used before. Here, we show common data wrangling methods (like filtering, sorting, and adding columns) in JavaScript, Python, SQL, R, and Excel. All examples use the mock data below, stored as _salesData_:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":827,"value":"salesData","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":919,"value":"---\n## Comparison tables\nThe comparison tables below are not comprehensive; many good (in some cases, better) alternatives exist in each language, and we do not cover myriad caveats (e.g. handling missing values). In general, we use tidyverse functions for R examples and pandas functions for Python examples.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":536,"value":"viewof langCheckbox = imageInput()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":2273,"value":"---\n${await FileAttachment(\"filter-sml.png\").image({width: 120, align: \"right\"})}\n### Filter rows\nKeep rows for the West _region_ in _salesData_.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1142,"value":"$${comparisonTable(filteredFunctions.filter(d => d.method == \"Filter rows\"))}","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":2272,"value":"---\n${await FileAttachment(\"select-sm.png\").image({width: 120, align: \"right\"})}\n### Select columns\nCreate a subset of _salesData_ containing only _date_, _product_, and _totalRevenue_.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1153,"value":"$${comparisonTable(filteredFunctions.filter(d => d.method == \"Select columns\"))}","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":2271,"value":"---\n${await FileAttachment(\"sort-sm.png\").image({width: 120, align: \"right\"})}\n### Sort data\nReorder _salesData_ by _totalRevenue_ (low to high).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1286,"value":"$${comparisonTable(filteredFunctions.filter(d => d.method == \"Sort data\"))}","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":2270,"value":"---\n${await FileAttachment(\"add-column-sm.png\").image({width: 120, align: \"right\"})}\n### Add a new column\nAdd a new column to _salesData_ with a _discountedPrice_, which takes 10% off each _unitPrice_.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1259,"value":"$${comparisonTable(filteredFunctions.filter(d => d.method == \"Add a column\"))}","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":2269,"value":"---\n${await FileAttachment(\"group-summarize-sm.png\").image({width: 120, align: \"right\"})}\n### Operations by group\nReturn the sum of _totalRevenue_ for each _region_ in _salesData_.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1251,"value":"$${comparisonTable(filteredFunctions.filter(d => d.method == \"Group by variable\"))}","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1349,"value":"---\n${await FileAttachment(\"rename-sm.png\").image({width: 120, align: \"right\"})}\n### Rename a column\nStore a version of the _date_ with new name _saleDate_, while keeping all other columns. The JavaScript and SQL code below adds a new column called _saleDate_. The Python and R code rename the existing _date_ column as _saleDate_","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1354,"value":"$${comparisonTable(filteredFunctions.filter(d => d.method == \"Rename a column\"))}","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":2267,"value":"---\n${await FileAttachment(\"index-array.png\").image({width: 120, align: \"right\"})}\n### Access a value\nAccess the fourth value from the _description_ property in _salesData_.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1385,"value":"$${comparisonTable(filteredFunctions.filter(d => d.method == \"Access a value\"))}","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":2266,"value":"---\n${await FileAttachment(\"unite-sm.png\").image({width: 120, align: \"right\"})}\n### Merge values from two columns into one\nUnite the _product_ and _description_ columns into a single column called _fullDescription_, using a comma as a separator.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1490,"value":"$${comparisonTable(filteredFunctions.filter(d => d.method == \"Merge columns\"))}","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1530,"value":"---\n${await FileAttachment(\"join-sm.png\").image({width: 120, align: \"right\"})}\n### Join tables\nUse a left join to join information from a _productDetails_ table to _salesData_. Join on _product_ in _salesData_ and *product_id* in _productDetails_ .","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1542,"value":"$${comparisonTable(filteredFunctions.filter(d => d.method == \"Join\"))}","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":925,"value":"

\n---\n## Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1302,"value":"_Table template and styling:_","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":487,"value":"comparisonTable = (contents) => {\n\n const groupedResults = d3.flatGroup(contents, d => d.method);\n \n let returnMD = ``;\n\n groupedResults.forEach(thisGroup => {\n \n returnMD += `| Language | Method | Example |\n | :-- | :-- | :--\n `\n\n thisGroup[1].forEach(doc => {\n returnMD += `| ${doc.language}`\n returnMD += `| ${doc.function} `\n returnMD += `| ${doc.example} |\n `\n })\n \n returnMD += `\n`\n })\n \n \n \n return returnMD\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":522,"value":"","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":970,"value":"_salesData:_","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":34,"value":"$45","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":972,"value":"_Array with methods information: _","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":493,"value":"$46","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":974,"value":"_Filtered version of above, based on inputs:_","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":542,"value":"filteredFunctions = compareFunctions.filter(d => langCheckbox.includes(d.language))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1714,"value":"Data used for join section: ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1713,"value":"productDetails = await FileAttachment(\"productDetails.csv\").csv({typed: \"auto\"})","pinned":true,"hidden":true,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"productDetails","database":"$undefined"},{"id":1947,"value":"import {set} from \"@tophtucker/custom-input-example\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1975,"value":"$47","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W48","stdlib":"1"}} 44:"$43:body"