Parsing clean data from intricate JSON structures in the R programming language

Question

Parsing clean data from intricate JSON structures in the R programming language

Utilizing the tidyjson package makes extracting neat data from a simple JSON effortless ()

However, when it comes to dealing with a more intricate nested JSON structure, applying this method becomes challenging. Specific questions like this one (how do you extract data from nested json data in R) are too limited in scope to be extrapolated to other situations.

A broader example can be found within this structure (view working reproducible examples here: 1.4 Example requests: )

{
        "data": {
          "type": "WIDGET TYPE",
          "id": "WIDGET_ID",
          "attributes": {
            "title": "WIDGET NAME",
            "last-update": "2019-02-01T08:26:34.000+01:00",
            "description": "WIDGET DESCRIPTION",
          },
          "meta": {
            "cache-control": {
              "cache": "HIT",
              "expireAt": "2019-03-01T17:18:22"
            }
          }
        },
        "included": [
          {
            "type": "INDICATOR_1 TYPE",
            "id": "INDICADOR_1_ID",
            "groupId": null,
            "attributes": {
              "title": "INDICADOR_1 NAME",
              "description": "INDICADOR_1 DESCRIPTION",
              "color": "#2fa688",
              "type": "INDICADOR_1 TYPE",
              "magnitude": "INDICADOR_1 MAGNITUDE",
              "composite": false,
              "last-update": "2019-02-19T08:26:34.000+01:00",
              "values": [
                {
                  "value": 12345,
                  "percentage": "VALUE BETWEEN 0 AND 1",
                  "datetime": "2019-02-04T20:44:00.000+01:00"
                }
              ]
            },
           {
            "type": "INDICATOR_2 TYPE",
            "id": "INDICADOR_1_ID",
            "groupId": null,
            "attributes": {
               …
            }
          }
        ]
       }
      }

The primary level consists of an object "data" and an array "included"
The array "included" contains one object for each indicator
Within each of these objects lies an "attributes" object with a "values" array where the crucial data is stored: "value", "percentage", and "datetime"

The objective is to transform this data into a structured dataframe with columns "type", "title", "value", "percentage", and "datetime"

r json tidyverse

Answer 1

Answer №1

Through a series of trials and errors, I finally discovered the solution. I decided to share my findings here in case it may assist others working with the json object:

library(tidyjson)

json %>% # our json object
  enter_object(included) %>% # to enter the object containing the data
  gather_array() %>% spread_all() %>% # manipulating the array
  select(attributes.title) %>% # retaining this variable
  enter_object(values) %>&%gt; # accessing the array where the final data is stored
  gather_array() %>% spread_all() %>% # similar process for working with the array
  select(indicator = attributes.title, value, percentage, datetime) # selecting final data

The process essentially involves using

enter_object %>% gather_array %>% spread_all %>% select

twice. You simply need to specify the objects you wish to access at each level, as well as the information you want to extract.

Answer 2

Through a series of trials and errors, I finally discovered the solution. I decided to share my findings here in case it may assist others working with the json object:

library(tidyjson)

json %>% # our json object
  enter_object(included) %>% # to enter the object containing the data
  gather_array() %>% spread_all() %>% # manipulating the array
  select(attributes.title) %>% # retaining this variable
  enter_object(values) %>&%gt; # accessing the array where the final data is stored
  gather_array() %>% spread_all() %>% # similar process for working with the array
  select(indicator = attributes.title, value, percentage, datetime) # selecting final data

The process essentially involves using

enter_object %>% gather_array %>% spread_all %>% select

twice. You simply need to specify the objects you wish to access at each level, as well as the information you want to extract.

Parsing clean data from intricate JSON structures in the R programming language

Answer №1

Similar questions

Retrieving information from an ajax array in PHP

Changing a Unique JSON Structure into a VB.NET Object

Retrieving various values with identical paths from JSON files using the JSON map feature in SAS

Deciphering JSON information in RAILS 3.0.6 that is provided via an HTTP POST request using the content-type application/x-www-form-urlencoded

Tips for dynamically creating column headings and table values using JSON arrays in AngularJS

Transform pandas JSON data into a custom JSON structure using Python

Verify if the parsed JSON data is equal to NSNULL

Displaying jsp variable in the browser console

Converting intricate JSON documents into .csv format

Would it be wise to store JSON files for my iPhone app on external servers?

Using Lua to access indices from a table that was generated from JSON data

AngularJs is being used to extract data from Firebase with the help of $value, $id, and

Extract data from a DataFrame column using Spark Scala and convert it into an RDD with multiple columns

Is it possible to transform multiple input values into a JSON array using PHP?

Convert HTML code into a customized JSON structure

Using Python to Transform Nested JSON Data into an Unordered List in HTML

Using Java to implement an embedded document structure in MongoDB through REST APIs

Issue with JSON data not functioning properly in AJAX request

Setting parameters to Labels in Objective-C it is important to have unique

The && symbol in JSONPath: combining conditions efficiently