JSON and Web Data

In lessons 1 and 2 you read data out of files: a .csv, an Excel workbook, an SPSS file. But a growing amount of data never arrives as a file at all. Maria's bakery just joined a food-delivery app. The app does not email her a spreadsheet; it exposes her orders through a web API that answers in a text format called JSON. And to keep an eye on the competition, Maria wants the price list from a rival bakery's website, but the rival has no API, so its numbers sit in a table on a web page.

This lesson brings both into R as the same tidy tibble you already know, with nothing to download. By the end you will be able to:

• Read JSON's shape, objects and arrays, and say what each becomes in R
• Parse a JSON response into a data frame with jsonlite, and flatten it when it nests
• Scrape a table off a web page into a tibble with rvest, and pick out single values with a CSS selector
• Explain the one idea that ties it together: fetch the data, then parse it

Prerequisites: lessons 1 and 2 (you know what a tibble is, that columns have a type, and how to load a package with library()). Every new term is defined as it appears. The map below is the whole lesson in one picture: which tool meets which web source.

What JSON looks like

JSON (JavaScript Object Notation) is just text, the format almost every web API answers in. It is built from two pieces:

• An object, written in curly braces { }, is a set of "key": value pairs, exactly like an R named list.
• An array, written in square brackets [ ], is an ordered list of values, like an R vector or unnamed list.

A value can be text, a number, true/false, or another object or array, so JSON nests. Here is what Maria's delivery app sends back when she asks for today's orders:

{
  "shop": "Maria Bakery",
  "open": true,
  "orders": [
    { "item": "croissant", "qty": 14 },
    { "item": "muffin",    "qty": 11 }
  ]
}

You can read its structure straight off the indentation: one object at the top, with an orders key whose value is an array of two more objects. The whole point of a JSON reader is to turn that text into R objects you can compute on, following a simple mapping:

Parse JSON in one call

The jsonlite package turns JSON text into R objects. Its reader is fromJSON(). We will hand it the exact text the API returns, so you can see the parsing happen. (In a moment we will cover where that text comes from in a real project.)

Watch what happens when the JSON is an array of flat objects, the happy case from the table above:

No loops, no manual splitting. fromJSON() saw an array of objects that all share the same keys and did the obvious thing: one row per object, one column per key. You are back in tibble-and-columns territory, the same place every reader in this course lands.

Where does orders_json come from in real life? You usually do not paste it; you point fromJSON() at the API's web address and it fetches the text for you:

# In production, the app lives at a URL and fromJSON downloads it for you:
orders <- fromJSON("https://api.mariasbakery.example/orders/today")

We are using a string here so the parsing runs live in your browser. Hold on to that fetch-versus-parse split; it is the idea that ties this whole lesson together.

What comes back?

Maria's app returns an array of flat objects: each order is {"id": ..., "item": ..., "qty": ...} with no nesting. She runs fromJSON(orders_json). What does she get back?

Flatten a nested response

Real APIs rarely stay flat. Maria's app can return a richer order where each order carries a nested totals object. Plain fromJSON() is faithful to that shape: it makes totals a column that is itself a table (a data frame tucked inside the data frame), which is awkward to work with.

See the totals line: a data frame inside a data frame. To pull those buried numbers up into ordinary, flat columns, set flatten = TRUE. jsonlite then renames each one parent.child, so totals becomes totals.qty and totals.eur:

The widget below shows that exact move: the single nested totals column is spread into two flat columns you can sum, filter and plot.

Flatten the order

Here is the nested response again. Plain fromJSON() would leave totals as a column that is itself a table. Add the one argument that spreads it into flat totals.qty and totals.eur columns, then check it.

library(jsonlite)
rich_json <- '[{"id":1,"totals":{"qty":14,"eur":35.0}},{"id":2,"totals":{"qty":11,"eur":35.75}}]'
fromJSON(rich_json, flatten = TRUE)

Scrape a table off a web page

The rival bakery has no API. Its prices simply live in a <table> on its web page. Scraping means reading that page's HTML and lifting the data out. The tool is the rvest package: read_html() parses the page into a document you can navigate, and html_table() finds every table and hands each one back as a tibble.

We will parse the page's HTML from a string, the exact text read_html() would download, so it runs live here:

Two things to notice. html_table() returns a list, because a page can hold several tables, so you index the one you want with [[1]]. And it was smart about types: it read the <th> cells as column names and converted price to a number (<dbl>) for you.

When the data is not in a neat table, you reach for a CSS selector, a short pattern that names which HTML elements you want. html_elements() finds every match; html_text2() pulls the text out of each. The |> below is R's pipe: it feeds the result on its left into the next function, so you read the steps top to bottom in the order they run:

span.price reads as "a span element whose class is price". Unlike html_table(), a selector gives you the raw text, so "2.40" is a character string; you would wrap it in as.numeric() when you need a number.

Pick the element with a selector

The rival's page puts its name in an <h1> element. The page is already parsed into doc. Fill in the CSS selector that targets the heading, then check it. (Both "h1" and the class selector ".shop-name" work.)

library(rvest)
snippet <- "<html><body>
  <h1 class='shop-name'>Old Town Bakery</h1>
  <p>Open daily from 7am</p>
</body></html>"
doc <- read_html(snippet)
doc |> html_elements("h1") |> html_text2()

Fetch, then parse

Step back and the two halves of this lesson are the same shape. Whether the source is a JSON API or an HTML page, you do four things: point at the source, read it, get a tibble, tidy the types.

The split that matters is fetch versus parse. Everything you ran above was the parsing half, working on text we already had, which is why it runs in your browser. The fetch half, downloading that text, is what needs a live internet connection. The good news: the same readers do both. Hand fromJSON() or read_html() a URL instead of a string and they fetch the page first, then parse it exactly as you saw.

From string to live URL

Every example here parsed text held in a string, so it ran in your browser. In a real project the data lives at a web address instead. How do you fetch it?

References

A few authoritative places to take this further:

• jsonlite: getting started (CRAN vignette) - how fromJSON maps JSON objects and arrays onto R lists and data frames.
• Ooms (2014), The jsonlite Package (arXiv) - the precise, consistent rules behind that mapping, including flattening.
• rvest package home (tidyverse) - the full reference for read_html, html_table, html_elements and friends.
• R for Data Science (2e): Web scraping - the canonical free walkthrough, including scraping etiquette and the law.
• rvest: SelectorGadget vignette - how to find the CSS selector for the element you actually want.

Lesson 3 complete

You can now bring data into R when there is no file to open. You read JSON's shape (objects and arrays), parsed it with fromJSON() into a tidy data frame, and flattened a nested response with flatten = TRUE. You scraped a price table off a web page with read_html() and html_table(), and picked single values out with a CSS selector. And you have the idea that unifies it all: fetch the text from a URL, then parse it into a tibble, with the same readers doing both.

Next, Lesson 4: Databases and big files. When the data is too large to fit in memory, or already lives in a database, you query it instead of reading it whole, and that is the last source this course teaches you to open.