rjsondataframemultiple-columns

R: Expanding a multilayered JSON


Let´s assume that my dataframe looks like this:

dataframe screenshot

I have this multi-layered json string response in a column of the data frame and I would like to expand or parse its multiple columns in the same dataframe.

"[
{\"question\":
 {\"id\":314123,
  \"text\":\"What did you see in this module?\",
  \"question_id\":151,
  \"requirements\":
  {\"answer_type\":\"text\",
   \"text_required\":true}
  },
 \"response\":
 {\"free_text\":
  {\"id\":\"314123_ft\",
   \"text\":\"Identifying critical knowledge\"}
 }
},
{\"question\":
 {\"id\":314124,
  \"text\":\"What do you feel is the gap?\",
  \"question_id\":152,
  \"requirements\":
  {\"answer_type\":\"text\",
   \"text_required\":true}
 },
 \"response\":
  {\"free_text\":
   {\"id\":\"314124_ft\",
    \"text\":\"Knowledge is clearly mapped out\"}
 }
},
{\"question\":
 {\"id\":314125,
  \"text\":\"Which of the modules you need to focus on\",
  \"question_id\":153,
  \"requirements\":
  {\"answer_type\":\"text\",
   \"text_required\":true}
 },
 \"response\":
 {\"free_text\":
  {\"id\":\"3141125_ft\",
   \"text\":\"preparation of key knowledge\"}
 }
}
]"

I apologise if the indentation is not totally correct, as I did indent it manually from a single-line string.

Following other entries here, here, and here I have tried several methods, but none of them could expand the whole thing into new columns, getting different errors that I show here (I intentionally only chose the options with smaller number of lines in the code to keep the post small).

> fromJSON(df) %>% 
+   unnest(c(response))
Error in (function (classes, fdef, mtable)  : 
  unable to find an inherited method for function ‘fromJSON’ for signature ‘"data.frame", "missing"’


>  df <- df %>% 
+    rowwise() %>%
+    do(data.frame(fromJSON(.$response, flatten = T))) %>%
+    ungroup() %>%
+    bind_cols(df %>% select(-response))
Error in (function (..., row.names = NULL, check.rows = FALSE, check.names = TRUE,  : 
  arguments imply differing number of rows: 1, 0

I would appreciate the help of any JSON expert on this.


Solution

  • Here is one possible approach using map to convert your column of json strings to a list column of dataframe plus some additional wrangling using tidyr to unnest the nested dfs:

    library(tidyverse)
    
    
    df <- tibble(
      id = 1:2,
      response = c(response, response)
    )
    
    df |> 
      rename(id1 = id) |> 
      mutate(
        response = map(response, jsonlite::fromJSON)
      ) |> 
      unnest_longer(response) |> 
      unnest_wider(response) |> 
      unnest_wider(c(response, question)) |> 
      unnest_wider(c(requirements, free_text), names_sep = "_")
    #> # A tibble: 6 × 8
    #>     id1     id text    question_id requirements_answer_…¹ requirements_text_re…²
    #>   <int>  <int> <chr>         <int> <chr>                  <lgl>                 
    #> 1     1 314123 What d…         151 text                   TRUE                  
    #> 2     1 314124 What d…         152 text                   TRUE                  
    #> 3     1 314125 Which …         153 text                   TRUE                  
    #> 4     2 314123 What d…         151 text                   TRUE                  
    #> 5     2 314124 What d…         152 text                   TRUE                  
    #> 6     2 314125 Which …         153 text                   TRUE                  
    #> # ℹ abbreviated names: ¹​requirements_answer_type, ²​requirements_text_required
    #> # ℹ 2 more variables: free_text_id <chr>, free_text_text <chr>
    

    DATA

    response <- "[
    {\"question\":
     {\"id\":314123,
      \"text\":\"What did you see in this module?\",
      \"question_id\":151,
      \"requirements\":
      {\"answer_type\":\"text\",
       \"text_required\":true}
      },
     \"response\":
     {\"free_text\":
      {\"id\":\"314123_ft\",
       \"text\":\"Identifying critical knowledge\"}
     }
    },
    {\"question\":
     {\"id\":314124,
      \"text\":\"What do you feel is the gap?\",
      \"question_id\":152,
      \"requirements\":
      {\"answer_type\":\"text\",
       \"text_required\":true}
     },
     \"response\":
      {\"free_text\":
       {\"id\":\"314124_ft\",
        \"text\":\"Knowledge is clearly mapped out\"}
     }
    },
    {\"question\":
     {\"id\":314125,
      \"text\":\"Which of the modules you need to focus on\",
      \"question_id\":153,
      \"requirements\":
      {\"answer_type\":\"text\",
       \"text_required\":true}
     },
     \"response\":
     {\"free_text\":
      {\"id\":\"3141125_ft\",
       \"text\":\"preparation of key knowledge\"}
     }
    }
    ]"