tidyjson provides tools for turning complex json into tidy data.
Get the released version from CRAN:
install.packages("tidyjson")or the development version from github:
devtools::install_github("colearendt/tidyjson")The following example takes a character vector of 500 documents in the
worldbank dataset and spreads out all objects.
Every JSON object key gets its own column with types inferred, so long
as the key does not represent an array. When recursive=TRUE (the
default behavior), spread_all does this recursively for nested objects
and creates column names using the sep parameter (i.e. {"a":{"b":1}}
with sep='.' would generate a single column: a.b).
library(dplyr)
library(tidyjson)
worldbank %>% spread_all
#> # A tbl_json: 500 x 8 tibble with a "JSON" attribute
#> `attr(., "JSON"… document.id boardapprovalda… closingdate
#> <chr> <int> <chr> <chr>
#> 1 "{\"_id\":{\"$o… 1 2013-11-12T00:0… 2018-07-07…
#> 2 "{\"_id\":{\"$o… 2 2013-11-04T00:0… <NA>
#> 3 "{\"_id\":{\"$o… 3 2013-11-01T00:0… <NA>
#> 4 "{\"_id\":{\"$o… 4 2013-10-31T00:0… <NA>
#> 5 "{\"_id\":{\"$o… 5 2013-10-31T00:0… 2019-04-30…
#> 6 "{\"_id\":{\"$o… 6 2013-10-31T00:0… <NA>
#> 7 "{\"_id\":{\"$o… 7 2013-10-29T00:0… 2019-06-30…
#> 8 "{\"_id\":{\"$o… 8 2013-10-29T00:0… <NA>
#> 9 "{\"_id\":{\"$o… 9 2013-10-29T00:0… 2018-12-31…
#> 10 "{\"_id\":{\"$o… 10 2013-10-29T00:0… 2014-12-31…
#> # ... with 490 more rows, and 5 more variables: countryshortname <chr>,
#> # project_name <chr>, regionname <chr>, totalamt <dbl>, `_id.$oid` <chr>Some objects in worldbank are arrays, which are not handled by
spread_all. This example shows how to quickly summarize the top level
structure of a JSON collection
worldbank %>% gather_object %>% json_types %>% count(name, type)
#> # A tibble: 8 x 3
#> name type n
#> <chr> <fct> <int>
#> 1 _id object 500
#> 2 boardapprovaldate string 500
#> 3 closingdate string 370
#> 4 countryshortname string 500
#> 5 majorsector_percent array 500
#> 6 project_name string 500
#> 7 regionname string 500
#> 8 totalamt number 500In order to capture the data in the majorsector_percent array, we can
use enter_object to enter into that object, gather_array to stack
the array and spread_all to capture the object items under the array.
worldbank %>%
enter_object(majorsector_percent) %>%
gather_array %>%
spread_all %>%
select(-document.id, -array.index)
#> # A tbl_json: 1,405 x 2 tibble with a "JSON" attribute
#> `attr(., "JSON")` Name Percent
#> <chr> <chr> <dbl>
#> 1 "{\"Name\":\"Educat..." Education 46
#> 2 "{\"Name\":\"Educat..." Education 26
#> 3 "{\"Name\":\"Public..." Public Administration, Law, and Justice 16
#> 4 "{\"Name\":\"Educat..." Education 12
#> 5 "{\"Name\":\"Public..." Public Administration, Law, and Justice 70
#> 6 "{\"Name\":\"Public..." Public Administration, Law, and Justice 30
#> 7 "{\"Name\":\"Transp..." Transportation 100
#> 8 "{\"Name\":\"Health..." Health and other social services 100
#> 9 "{\"Name\":\"Indust..." Industry and trade 50
#> 10 "{\"Name\":\"Indust..." Industry and trade 40
#> # ... with 1,395 more rows-
spread_all()for spreading all object values into new columns, with nested objects having concatenated names -
spread_values()for specifying a subset of object values to spread into new columns using thejson_chr(),json_dbl()andjson_lgl()functions. It is possible to specify multiple parameters to extract data from nested objects (i.e.json_chr('a','b')).
-
enter_object()for entering into an object by name, discarding all other JSON (and rows without the corresponding object name) and allowing further operations on the object value -
gather_object()for stacking all object name-value pairs by name, expanding the rows of thetbl_jsonobject accordingly
gather_array()for stacking all array values by index, expanding the rows of thetbl_jsonobject accordingly
-
json_types()for identifying JSON data types -
json_length()for computing the length of JSON data (can be larger than1for objects and arrays) -
json_complexity()for computing the length of the unnested JSON, i.e., how many terminal leaves there are in a complex JSON structure -
is_jsonfamily of functions for testing the type of JSON data
-
json_structure()for creating a single fixed column data.frame that recursively structures arbitrary JSON data -
json_schema()for representing the schema of complex JSON, unioned across disparate JSON documents, and collapsing arrays to their most complex type representation
-
as.tbl_json()for converting a string or character vector into atbl_jsonobject, or for converting adata.framewith a JSON column using thejson.columnargument -
tbl_json()for combining adata.frameand associatedlistderived from JSON data into atbl_jsonobject -
read_json()for reading JSON data from a file
as.character.tbl_jsonfor converting the JSON attribute of atbl_jsonobject back into a JSON character string
-
commits: commit data for the dplyr repo from github API -
issues: issue data for the dplyr repo from github API -
worldbank: world bank funded projects from jsonstudio -
companies: startup company data from jsonstudio
The goal is to turn complex JSON data, which is often represented as nested lists, into tidy data frames that can be more easily manipulated.
-
Work on a single JSON document, or on a collection of related documents
-
Create pipelines with
%>%, producing code that can be read from left to right -
Guarantee the structure of the data produced, even if the input JSON structure changes (with the exception of
spread_all) -
Work with arbitrarily nested arrays or objects
-
Handle ‘ragged’ arrays and / or objects (varying lengths by document)
-
Allow for extraction of data in values or object names
-
Ensure edge cases are handled correctly (especially empty data)
-
Integrate seamlessly with
dplyr, allowingtbl_jsonobjects to pipe in and out ofdplyrverbs where reasonable
Tidyjson depends upon
- magrritr for the
%>%pipe operator - jsonlite for converting JSON strings into nested lists
- purrr for list operators
- tidyr for unnesting and spreading
Further, there are other R packages that can be used to better understand JSON data
- listviewer for viewing JSON data interactively