2c:null 2d:["$","title",null,{"children":"Cozy Collecting, Part 2: Working with Nested JSON in R | Periscopic | Observable"}] 32:[{"id":"b963f24bb3ab3f374578355af38c321056426a8fcee2ff089be68fd62bafc8afaf9dc48a5b9944762d4a42261c1100bd4acbbfbb80b39602a7fd61e693638ddd","href":"https://static.observableusercontent.com/files/b963f24bb3ab3f374578355af38c321056426a8fcee2ff089be68fd62bafc8afaf9dc48a5b9944762d4a42261c1100bd4acbbfbb80b39602a7fd61e693638ddd","name":"irina-blok-RLR9AfOd5hw-unsplash.jpeg","type":"image/jpeg","lastModified":1644007726975.203,"size":396621},{"id":"d9e887bc1f750aa17ce6eb759040f7d4747108824d01bf9eaa8f3a810800567724b0e3f1ff39a0104ec525f30da795b5db67ab6f79361596576ca9d697d50595","href":"https://static.observableusercontent.com/files/d9e887bc1f750aa17ce6eb759040f7d4747108824d01bf9eaa8f3a810800567724b0e3f1ff39a0104ec525f30da795b5db67ab6f79361596576ca9d697d50595","name":"p2-unpack-5.png","type":"image/png","lastModified":1644008115466.848,"size":134964},{"id":"c944493f278a73dc4e3bb0465c1c8ebccd65b229a06cf6700ca9618d61b4875c8478e6213986838b126ad5c0e702679bce33d4dd0c4d2b4d4d7423cefc47dfaf","href":"https://static.observableusercontent.com/files/c944493f278a73dc4e3bb0465c1c8ebccd65b229a06cf6700ca9618d61b4875c8478e6213986838b126ad5c0e702679bce33d4dd0c4d2b4d4d7423cefc47dfaf","name":"p2-weather-tibble.png","type":"image/png","lastModified":1644008115217.095,"size":57835},{"id":"1318aee6928ae2b855c85f318251eab4d4ddbaed42f38b971ca2278d8136c26153ec708ef2e1363e00cd26def4ecedd339a020fcd3eb5bc91372e8797d13ec9a","href":"https://static.observableusercontent.com/files/1318aee6928ae2b855c85f318251eab4d4ddbaed42f38b971ca2278d8136c26153ec708ef2e1363e00cd26def4ecedd339a020fcd3eb5bc91372e8797d13ec9a","name":"p2-unpack-4.png","type":"image/png","lastModified":1644008115466.27,"size":139620},{"id":"1b4ce19231f075716c1e84726cba1e28b13026999744b78f530cff60ddf4483a57a4e78581a14eba2145736fb2c6d08d2759ecea2124e350c5cd330134dbf0d9","href":"https://static.observableusercontent.com/files/1b4ce19231f075716c1e84726cba1e28b13026999744b78f530cff60ddf4483a57a4e78581a14eba2145736fb2c6d08d2759ecea2124e350c5cd330134dbf0d9","name":"p2-unpack-1.png","type":"image/png","lastModified":1644008115423.926,"size":122992},{"id":"4fbb3052a60c01fceb49ce7c8257a9ac7a843db9e79bd56ea45bcd2feff633ef5d6f5dbfb33f5368f372efd3a10c8daed01d1172bfba0bdb12cc28f81e632e16","href":"https://static.observableusercontent.com/files/4fbb3052a60c01fceb49ce7c8257a9ac7a843db9e79bd56ea45bcd2feff633ef5d6f5dbfb33f5368f372efd3a10c8daed01d1172bfba0bdb12cc28f81e632e16","name":"p2-listviewer.png","type":"image/png","lastModified":1644008115490.032,"size":181065},{"id":"7af4a0f9d0081be99ee49c51c5f331640933bb27af9b3dcbe7a7159d86075a0c9f028b1dc160b1b29e2d56d456c50fd5dae4d4e9bdf64ac54556dcc60fe60640","href":"https://static.observableusercontent.com/files/7af4a0f9d0081be99ee49c51c5f331640933bb27af9b3dcbe7a7159d86075a0c9f028b1dc160b1b29e2d56d456c50fd5dae4d4e9bdf64ac54556dcc60fe60640","name":"p2-name-check.png","type":"image/png","lastModified":1644008114952.687,"size":26895},{"id":"ba57c8f1d12e352b35254529e2402ae43abb008d1e4df37c47e3506a936925f80b6caefd17385328cbbc4c554497bc062dca2dab4ed3329973581213a7575044","href":"https://static.observableusercontent.com/files/ba57c8f1d12e352b35254529e2402ae43abb008d1e4df37c47e3506a936925f80b6caefd17385328cbbc4c554497bc062dca2dab4ed3329973581213a7575044","name":"p2-unpack-2.png","type":"image/png","lastModified":1644008115129.346,"size":85913},{"id":"65dfc7a36c1cc7ece91d80848ee2136cf2f706c6d53e7db08570436a86608a7477c955dc0cdcd1fb83132a6d9baee34990480886d689bf4d70ea716dd5b292e1","href":"https://static.observableusercontent.com/files/65dfc7a36c1cc7ece91d80848ee2136cf2f706c6d53e7db08570436a86608a7477c955dc0cdcd1fb83132a6d9baee34990480886d689bf4d70ea716dd5b292e1","name":"p2-unpack-3.png","type":"image/png","lastModified":1644008115361.756,"size":103911}] 30:{"id":"98c2bb8a91284518","slug":"cozy-collecting-part-2","title":"Cozy Collecting, Part 2: Working with Nested JSON in R","thumbnail":"f977b961ddb1f87f0bc947ff9a5a6dbbf34ca3ea11caae6e53dfa1822ef3ffe7","access_level":"public","create_time":"$D2022-02-04T20:45:48.683Z","update_time":"$D2022-02-15T22:52:33.243Z","view_time":null,"first_public_time":"$D2022-02-15T17:05:48.455Z","has_unpublished_edits":null,"fork":false,"stars":1,"starred":false,"owner":{"type":"team","id":"3a75712dbd8df3bb","login":"periscopic","name":"Periscopic","avatar_url":"https://avatars-next.observableusercontent.com/2f6e98f71dc2af94cfb5e4c3b77652cd448d7c9420fc850bfa00e052a63ec480","tier":"starter_2024","bio":"The socially conscious\ndata visualization firm. We turn data into\ninteractive visual experiences for empathy and understanding."},"version":236,"publish_version":236,"role":null,"authored":false,"authors":[{"type":"individual","id":"284cdf205719c86e","login":"zachperiscopic","name":"Zach Bogart","avatar_url":"https://avatars-next.observableusercontent.com/6f18b0401ce7dbd258bca1afb27a7a9d850d10197a42ad23e35e242ca095a4e9","tier":null,"bio":""}],"members":[{"type":"individual","id":"284cdf205719c86e","login":"zachperiscopic","name":"Zach Bogart","avatar_url":"https://avatars-next.observableusercontent.com/6f18b0401ce7dbd258bca1afb27a7a9d850d10197a42ad23e35e242ca095a4e9","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":1,"collection":null,"type":"notebook","body":{"titling":"auto","title":"Cozy Collecting, Part 2: Working with Nested JSON in R","cells":[{"id":0,"value":"# Cozy Collecting, Part 2: Working with Nested JSON in R","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":11,"value":"

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":3,"value":"In the tidy land of R, we prefer working with dataframes, a tabular format. While JSON can represent a dataframe through an array of objects, it is often highly nested, making it difficult to translate it to a usable dataframe. You often end up with the nesting having to be unpacked in complicated ways. This has proven challenging when it comes to interacting with APIs or parsing JavaScript outputs. But if we embrace the nesting, we will have a much easier time.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":23,"value":"You're now on Part 2 of this 3 part series:\n\n1. 👋 **[Pinging an API, Creating JSON](https://observablehq.com/@zachperiscopic/cozy-collecting-part-1)**: Get data from an API and extract as JSON\n2. ➡️ 🔨 **Working with JSON**: Use extracted JSON data in R\n3. 🚀 **[Case Study: Getting a Decade's Worth of Weather Data](https://observablehq.com/@zachperiscopic/cozy-collecting-case-study)**: Example of method with a larger data request","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":26,"value":"---\n# 🔨 Part 2: Working with JSON in R","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":50,"value":"

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":29,"value":"In [Part 1](https://observablehq.com/@zachperiscopic/cozy-collecting-part-1), we made some nice fresh JSON, but now we'd like to work with it in R. In this section, we are going to go through using rectangling methods to navigate our JSON output.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":32,"value":"*Note: To clarify where code is coming from, we will often include the longer format of package function names (`httr::GET()` versus `GET()`).*","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":36,"value":"```\n# imports\nlibrary(httr) # accessing APIs\nlibrary(glue) # pass variables into strings\nlibrary(tibble) # nicer dataframes\nlibrary(purrr) # apply functions to lists\nlibrary(dplyr) # data wrangling\nlibrary(tidyr) # creating tidy data\nlibrary(jsonlite) # JSON parsing\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":42,"value":"## ☁️ Unpacking our weather data","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":62,"value":"

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":43,"value":"In [Part 1](https://observablehq.com/@zachperiscopic/cozy-collecting-part-1), we went from a JSON output of zip codes to reading it back in as a `data.frame`. This worked because our data output didn't have extensive nesting. For a more robust setup, we would prefer using [tibbles](https://tibble.tidyverse.org/), but the data will often be nested, so we need to use rectangling methods to parse the results. [Rectangling](https://tidyr.tidyverse.org/articles/rectangle.html) is \"the art and craft of taking a deeply nested list (often sourced from wild caught JSON or XML) and taming it into a tidy data set of rows and columns.\" And it's fantastic.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":66,"value":"To begin, we will read in the JSON we created previously. You can also simply pass the nested list generated previously if you are working in the same session.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":68,"value":"```\nweather_list <- jsonlite::read_json(\"weather.json\")\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":73,"value":"The biggest trick is to leave things nested within a tibble, and then extract what you want. We create a new tibble, defining a new column with our R list.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":76,"value":"```\n# wrap list in a tibble\nweather <- tibble(place = weather_list)\n\nweather\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":83,"value":"

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":88,"value":"We end up with a column of nested lists. This can be thought of as the JSON data, but in an R/tibble format. So, the four named lists are the four calls we kept of the weather data. Similar to pivoting, we can \"unnest\" these named lists into the columns we want. Let's get the place, location, and weather description from each call. We can peel back a layer using `tidyr::unnest_wider`, expanding out the values in the selected column of named lists. This is akin to expanding out an object in the JSON:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":91,"value":"```\nweather %>% \n unnest_wider(place)\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":167,"value":"

Unpacking the nested lists using unnest_wider()

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":94,"value":"We now see some of the data unnested, specifically the data one level down. We can see that there is further nesting for each row. Let's select just the columns we need:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":97,"value":"```\nweather %>% \n unnest_wider(place) %>% \n select(name, coord, weather)\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":169,"value":"

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":104,"value":"The weather column is just a list, akin to an array in JSON. We want to unpack the weather for each row, so we will use `tidyr::unnest_longer`. Notice the `lists` in the weather column now have become `named lists`.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":107,"value":"```\nweather %>% \n unnest_wider(place) %>% \n select(name, coord, weather) %>% \n unnest_longer(weather)\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":172,"value":"

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":112,"value":"We can also extract items more selectively with `tidyr::hoist`, pulling items up to our current level. Notice after using `hoist`, the named lists in the weather column are smaller. We extracted the \"description\" field and pulled it out as a column called \"weather_description\".","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":115,"value":"```\nweather %>% \n unnest_wider(place) %>% \n select(name, coord, weather) %>% \n unnest_longer(weather) %>% \n hoist(\n weather,\n weather_description = \"description\"\n )\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":175,"value":"

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":119,"value":"For the coordinates, we want everything in that named list (JSON object), so we can use `tidyr::unnest_wider` again. Along with a little dplyr cleanup, we end up with a lovely little tibble of weather data:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":123,"value":"```\nweather %>% \n unnest_wider(place) %>% \n select(name, coord, weather) %>% \n unnest_longer(weather) %>% \n hoist(\n weather,\n weather_description = \"description\"\n ) %>% \n unnest_wider(coord) %>% \n select(-weather)\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":189,"value":"

Desired dataframe after unpacking nested JSON

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":127,"value":"By embracing the nesting, we were able to get at the data we wanted.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":213,"value":"

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":131,"value":"## 🗄️ Process Summary\n\nWe can use rectangling methods to parse nested JSON within a tibble. When interacting with the data, we will come across *lists* and *named lists*. These are akin to *arrays* and *objects* in JSON. To parse data, we will use [tidyr rectangle functions](https://tidyr.tidyverse.org/reference/hoist.html), usually following these rules:\n\n- If it's a column of *lists*, use `unnest_longer` to expand to new rows (`unnest_longer` is for expanding JSON arrays)\n- If it's a column of *named lists*, use `unnest_wider` to expand to new columns (`unnest_wider` is for expanding JSON objects)\n- To be more selective extracting nested named lists, use `hoist` (`hoist` is for selectively expanding JSON objects, even nested ones)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":135,"value":"## 🔬 Extra Tools\n\nFeeling a little lost with all those nested lists?\n\n- The listviewer package is an excellent companion when unpacking JSON data. You can pass the R list to see it's structure and peek inside nested lists.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":144,"value":"```\nlibrary(listviewer)\n\n# use listviewer to view nested structure\nlistviewer::jsonedit(weather_list)\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":201,"value":"

Using listviewer to see nested JSON structure

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":147,"value":"- If you are working with really large JSON files, listviewer may take a while to load, and you may consider only passing a subset of the nested list. Regardless, you can still \"peek\" inside of named lists by getting the names of a specific cell from the nested tibble.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":151,"value":"```\n# partially unpack data\npartially_unnested <- weather %>% \n unnest_wider(place)\n\n# select named list, get names \nnames(partially_unnested[[1, \"main\"]][[1]])\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":208,"value":"

Peeking at fields within specific nested JSON object

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":155,"value":"## 🤖 Wrap Up\n\nWe can now take JSON data and unpack it in the tidy land of R. By keeping the data nested in a tibble, we can use rectangling methods to extract whatever data we might need, preserving the nesting JSON structure.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":159,"value":"---\n# ✨Keep Going\n\nUp to this point, the data we have collected has been relatively small. In the accompanying [Case Study](https://observablehq.com/@zachperiscopic/cozy-collecting-case-study), we use what we learned in [Part 1](https://observablehq.com/@zachperiscopic/cozy-collecting-part-1) and Part 2 to go get weather data, but on a larger scale.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":226,"value":"[Cozy Collecting, Case Study: Getting a Decade's Worth of Weather Data in R](https://observablehq.com/@zachperiscopic/cozy-collecting-case-study)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"}],"files":"$W32","stdlib":"1"}} 31:"$30:body"