Parse JSON column into R dataframe

February 4, 2022

I have this dataframe:

df <- data.frame(
  item = c("Box 1", "Tape", "Pen"),
  length = c(2, 10, NA),
  json = c(
    '{"size": "size(W x L)", "bubble_height": "bubble height"}',
    '{"size": "size(W x L)", "color": "tape color"}',
    "{}"
  )
)

   item length                                                      json
1 Box 1      2 {"size": "size(W x L)", "bubble_height": "bubble height"}
2  Tape     10            {"size": "size(W x L)", "color": "tape color"}
3   Pen     NA                                                        {}

Would like to extract the json data into a column like this:

   item length                                                      json    option_1      option_2
1 Box 1      2 {"size": "size(W x L)", "bubble_height": "bubble height"} size(W x L) bubble height
2  Tape     10            {"size": "size(W x L)", "color": "tape color"} size(W x L)    tape color
3   Pen     NA                                                        {}        <NA>          <NA>

I haven’t found a good solution for this since fromJSON accepts txt rather than a character vector. So I cannot do rowwise %>% to fromJSON.

>Solution :

Wrap with list on the output of fromJSON, then use unnest_wider to create separate columns from the named list, join the columns to ‘option_2’ by coalesceing the ‘bubble_height’ and ‘color’

library(dplyr)
library(jsonlite)
library(tidyr)
df %>%
   rowwise %>% 
   mutate(out = list(fromJSON(json))) %>%
  ungroup %>% 
  unnest_wider(out)%>%
  mutate(option_1 = size, option_2 = coalesce(bubble_height, color), 
       .keep = "unused")

-output

# A tibble: 3 × 5
  item  length json                                                                option_1    option_2     
  <chr>  <dbl> <chr>                                                               <chr>       <chr>        
1 Box 1      2 "{\"size\": \"size(W x L)\", \"bubble_height\": \"bubble height\"}" size(W x L) bubble height
2 Tape      10 "{\"size\": \"size(W x L)\", \"color\": \"tape color\"}"            size(W x L) tape color   
3 Pen       NA "{}"                                                                <NA>        <NA>